Contoh
Halaman ini menampilkan contoh praktis penggunaan SQL Prettify pada kueri SQL yang umum. Blok SQL dibiarkan tetap sama agar perbandingan antara input dan hasil format lebih mudah dilihat.
Kueri SELECT dasar
SELECT sederhana
Input:
select id, name, email from users where active = 1;
Output yang diformat:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
SELECT dengan ORDER BY
Input:
select u.id, u.name, u.email from users u where u.active = 1 order by u.name asc, u.created_at desc;
Output yang diformat:
SELECT
u.id,
u.name,
u.email
FROM users u
WHERE u.active = 1
ORDER BY
u.name ASC,
u.created_at DESC;
Kueri JOIN
INNER JOIN
Input:
select u.id, u.name, u.email, p.title, p.content from users u inner join posts p on u.id = p.user_id where u.active = 1 and p.published = 1;
Output yang diformat:
SELECT
u.id,
u.name,
u.email,
p.title,
p.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.active = 1
AND p.published = 1;
Kueri agregasi
GROUP BY dengan agregasi
Input:
select u.id, u.name, count(p.id) as post_count, sum(p.views) as total_views from users u left join posts p on u.id = p.user_id where u.active = 1 group by u.id, u.name having count(p.id) > 0 order by total_views desc;
Output yang diformat:
SELECT
u.id,
u.name,
COUNT(p.id) AS post_count,
SUM(p.views) AS total_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.active = 1
GROUP BY
u.id,
u.name
HAVING COUNT(p.id) > 0
ORDER BY total_views DESC;
Subkueri
Subkueri di WHERE
Input:
select id, name, email from users where id in (select user_id from posts where published = 1) and active = 1;
Output yang diformat:
SELECT
id,
name,
email
FROM users
WHERE id IN (
SELECT user_id
FROM posts
WHERE published = 1
)
AND active = 1;
Subkueri berkorelasi
Input:
select u.id, u.name, u.email, (select count(*) from posts p where p.user_id = u.id and p.published = 1) as post_count from users u where u.active = 1;
Output yang diformat:
SELECT
u.id,
u.name,
u.email,
(
SELECT COUNT(*)
FROM posts p
WHERE p.user_id = u.id
AND p.published = 1
) AS post_count
FROM users u
WHERE u.active = 1;
Modifikasi data
Pernyataan INSERT
Input:
insert into users (name, email, active, created_at) values ('John Doe', 'john@example.com', 1, now()), ('Jane Smith', 'jane@example.com', 1, now());
Output yang diformat:
INSERT INTO users (
name,
email,
active,
created_at
)
VALUES (
'John Doe',
'john@example.com',
1,
NOW()
), (
'Jane Smith',
'jane@example.com',
1,
NOW()
);
Pernyataan UPDATE
Input:
update users set name = 'John Updated', email = 'john.updated@example.com', updated_at = now() where id = 1 and active = 1;
Output yang diformat:
UPDATE users
SET
name = 'John Updated',
email = 'john.updated@example.com',
updated_at = NOW()
WHERE id = 1
AND active = 1;
Pernyataan DELETE
Input:
delete from posts where user_id in (select id from users where active = 0) and published = 0;
Output yang diformat:
DELETE FROM posts
WHERE user_id IN (
SELECT id
FROM users
WHERE active = 0
)
AND published = 0;
Definisi data
CREATE TABLE
Input:
create table users (id int primary key auto_increment, name varchar(255) not null, email varchar(255) unique not null, active boolean default true, created_at timestamp default current_timestamp, updated_at timestamp default current_timestamp on update current_timestamp);
Output yang diformat:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Fungsi window
ROW_NUMBER()
Input:
select id, name, email, row_number() over (partition by department_id order by created_at desc) as row_num from users where active = 1;
Output yang diformat:
SELECT
id,
name,
email,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY created_at DESC
) AS row_num
FROM users
WHERE active = 1;
Common Table Expressions (CTE)
Klausa WITH
Input:
with active_users as (select id, name, email from users where active = 1), user_posts as (select user_id, count(*) as post_count from posts where published = 1 group by user_id) select au.id, au.name, au.email, coalesce(up.post_count, 0) as post_count from active_users au left join user_posts up on au.id = up.user_id order by post_count desc;
Output yang diformat:
WITH active_users AS (
SELECT
id,
name,
email
FROM users
WHERE active = 1
),
user_posts AS (
SELECT
user_id,
COUNT(*) AS post_count
FROM posts
WHERE published = 1
GROUP BY user_id
)
SELECT
au.id,
au.name,
au.email,
COALESCE(up.post_count, 0) AS post_count
FROM active_users au
LEFT JOIN user_posts up ON au.id = up.user_id
ORDER BY post_count DESC;
Opsi pemformatan
Ukuran indentasi berbeda
2 spasi:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
4 spasi:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
Gaya huruf kata kunci
HURUF BESAR:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
huruf kecil:
select
id,
name,
email
from users
where active = 1;
Contoh-contoh ini memperlihatkan bagaimana SQL Prettify membuat kueri lebih rapi, lebih mudah dibaca, dan lebih konsisten untuk kerja tim.