Scrieți cod lizibil

Aplicarea SQL în probleme din lumea reală

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

Scrieți comenzile SQL cu majuscule

Original

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;

Îmbunătățit

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;

Folosiți linii noi și indentare

Original

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;

Îmbunătățit

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;

Folosiți snake_case

Original

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

Îmbunătățit

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

Folosiți IN în loc de mai multe instrucțiuni OR

Original

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

Îmbunătățit

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

Folosiți BETWEEN când este posibil

Original

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

Îmbunătățit

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

Să exersăm!

Aplicarea SQL în probleme din lumea reală