Viết mã dễ đọc

Ứng dụng SQL vào các bài toán thực tế

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

Viết hoa lệnh SQL

Bản gốc

select 
  title as film_title, 
  description as film_description
from rental as r
join inventory as i
  on r.inventory_id = i.inventory_id
join film as f
  on i.film_id = f.film_id
where f.length > 90
  and f.replacement_cost > 20;

Cải tiến

SELECT 
  title AS film_title, 
  description AS film_description
FROM rental AS r
JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length > 90
  AND f.replacement_cost > 20;

Dùng dòng mới & thụt lề

Bản gốc

SELECT title, description
FROM rental AS r
JOIN inventory AS i
ON r.inventory_id = i.inventory_id
JOIN film AS f ON i.film_id = f.film_id
WHERE f.length > 90 AND f.replacement_cost > 20;

Cải tiến

SELECT title, 
       description 
FROM rental AS r
JOIN inventory AS i
  ON r.inventory_id = i.inventory_id
JOIN film AS f
  ON i.film_id = f.film_id
WHERE f.length > 90
  AND f.replacement_cost > 20;

Dùng snake_case

Bản gốc

SELECT 
  title expensivelongtitle, 
  description expensivelongdescription
FROM rental AS r
...

Cải tiến

SELECT 
  title expensive_long_title, 
  description expensive_long_description
FROM rental AS r
...

Dùng IN thay cho nhiều OR

Bản gốc

SELECT 
    address_id,  
    district 
FROM address
WHERE district = 'Texas'
  OR district = 'Bihar'
  OR district = 'Chiba'
  OR district = 'Chiayi'
  OR district = 'Gois';

Cải tiến

SELECT 
    address_id,  
    district 
FROM address
WHERE district IN ('Texas', 'Bihar', 'Chiba', 
                   'Chiayi', 'Gois');

Dùng BETWEEN khi có thể

Bản gốc

SELECT 
  title, 
  description
FROM film
WHERE replacement_cost > 15
  AND replacement_cost < 25;

Cải tiến

SELECT 
  title, 
  description
FROM film
WHERE replacement_cost BETWEEN 15 AND 25;

Ayo berlatih!

Ứng dụng SQL vào các bài toán thực tế