/* SQL Tuning by
by alul-cholil.blogspot.com
*/

--Penggunaan nama kolom lebih cepat dari '*'
SELECT id, nama_depan, nama_belakang, umur
FROM mahasiswa;
    --daripada
SELECT*FROM mahasiswa;


--Hati-hati dalam menggunakan WHERE
SELECT id, first_name, age FROM student_details WHERE age > 10;
    --daripada
SELECT id, first_name, age FROM student_details WHERE age != 10;
------------------------------------------
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
    --daripada
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';
------------------------------------------
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
    --daripada
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
------------------------------------------
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) AND MIN(unit_price)
    --daripada
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
AND unit_price <= MIN(unit_price)
------------------------------------------

--Having digunakan untuk mensortir baris setelah semua baris di select
--lebih baik menggunakan query
SELECT mapel, COUNT(mapel)
FROM detil_siswa
WHERE mapel != 'Kimia'
AND mapel != 'Matematika'
GROUP BY mapel;
    --daripada
SELECT mapel, COUNT(mapel)
FROM detil_siswa
GROUP BY mapel
HAVING mapel!= 'Kimia' AND mapel!= 'Matematika';


--Gunakan EXISTS untuk seleksi kriteria yg bukan sub-query
SELECT * FROM product p
WHERE EXISTS (SELECT * FROM order_items o
WHERE o.product_id = p.product_id)
    --daripada
SELECT * FROM product p
WHERE product_id IN
(SELECT product_id FROM order_items


--Gunakan UNION ALL daripada UNION
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
    --daripada
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;