SQL is a declarative language:
- Imperative languages (e.g., Python) require defining the specific processing steps to obtain a result.
- Declarative languages (e.g., SQL) do not care about the processing steps, we simply define the result we'd like and an engine under the hoods takes care of the necessary steps to obtain that result.
Most common SQL operators: SELECT, FROM, WHERE, AS, GROUP BY, COUNT(), AVG(), etc.
-- Get all the contents from the table my_table: we get a table
SELECT * FROM my_table;
-- Get specified columns and compute a new column value: we get a table
SELECT origin, dest, air_time / 60 FROM flights;
-- Filter according to value in column: we get a table
SELECT * FROM students
WHERE grade = 'A';
-- Get the table which contains the destination and tail number of flights that last +10h
SELECT dest, tail_num FROM flights WHERE air_time > 600;
-- Group by: group by category values and apply an aggregation function for each group
-- In this case: number of flights for each unique origin
SELECT COUNT(*) FROM flights
GROUP BY origin;
-- Group by all unique combinations of origin and dest columns
SELECT origin, dest, COUNT(*) FROM flights
GROUP BY origin, dest;
-- Group by unique origin-carrier combinations and for each
-- compute average air time in hrs
SELECT AVG(air_time) / 60 FROM flights
GROUP BY origin, carrier;
-- Flight duration in hrs, new column name
SELECT air_time / 60 AS duration_hrs
FROM flights
-- INNER JOIN: note it is symmetrical, we can interchange TableA and B
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.col_match = TableB.col_match;
-- FULL OUTER JOIN
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = Table_B.col_match
-- LEFT OUTER JOIN
-- Left table: TableA; Right table: TableB
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = Table_B.col_match
-- RIGHT OUTER JOIN
-- Left table: TableA; Right table: TableB
SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = Table_B.col_match