月落丹枫

# one column
SELECT title
FROM films

# multiple columns
SELECT title, release_year
FROM films

# All columns
SELECT *
FROM people
LIMIT 10;

#unique values from a column
SELECT DISTINCT country
FROM films

The following operators can be used in the WHERE clause:

SELECT *
FROM films
WHERE budget > 10000;

SELECT *
FROM films
WHERE language='French'

SELECT title,release_year
FROM films
WHERE release_year < 2000 AND language='Spanish';

SELECT title
FROM films
WHERE (release_year = 1994 OR release_year = 1995)
AND (certification = 'PG' OR certification = 'R');

# BETWEEN is inclusive, meaning the beginning and end values are included in the results!
WHERE release_year BETWEEN 1990 AND 2000

WHERE release_year IN (1990,2000)

WHERE language IS NULL

Start with 'B': WHERE name LIKE 'B%'
Ends with 'A' : WHERE name LIKE '%A'
Second letter is r: WHERE name LIKE '_r%'
Have "or" in any position: WHERE name LIKE '%r%'
Not start with A: WHERE name NOT LIKE 'A%'

Aggregate Function returns a value

SELECT COUNT(column_name)
SELECT AVG(column_name)
SELECT SUM(column_name)
SELECT MIN(column_name)
SELECT MAX(column_name)

Commonly, GROUP BY is used with aggregate functions like COUNT() or MAX()

SELECT sex, count(*)
FROM employees
GROUP BY sex;

Define a new category

#Transfer the numerical column to category column, then name the new column as geosize_group.
#INTO Clause is used to create a new table

SELECT name, continent, code, surface_area,
CASE
    WHEN surface_area >2000000 THEN 'large'
    WHEN surface_area >350000 AND surface_area <200000000  THEN  'medium'
    ELSE  'small'
END
AS geosize_group
INTO pop_plus
FROM countries

If you want to filter on aggregate functions like COUNT(),you need to use HAVING clause, WHERE is invalid.

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10;

If you want to query multiple tables, you need joining tables first

Based on matching values selects rows from both tables.

SELECT c.code AS country_code, year, inflation_rate, c.name
FROM countries AS c
Inner JOIN economies AS e
ON c.code = e.code;
#when the matching column name is same in both table can use USING (coded) as a shortcut

Return all the records from the left table and the matched records from the right table

# one to many relationship: one customer match many order ID

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Return all the records from the right table and the matched records from the left table

# many to one relationship: many OrderID match one employees

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

SELECT c.name AS city, l.name AS language
FROM cities AS c
CROSS JOIN languages AS l
WHERE  c.name LIKE 'Hyder%';

SELECT p1. country_code,
       p1.size AS size2010,
       p2.size AS size2015,  (p2.size-p1.size)/p1.size*100.0 AS growth_perc
FROM populations AS p1
INNER JOIN populations AS P2
ON  P1. country_code = P2.country_code AND p1.year=p2.year-5;

If the subquery is one or multiple value or word

# Calculate the avg life_expectancy
SELECT AVG(life_expectancy)
FROM populations
WHERE year = 2015;

---
# Then get those who live longer 1.15 times than the avg life_expectancy
SELECT *
FROM populations
WHERE life_expectancy > 1.15 * (SELECT AVG(life_expectancy) FROM populations WHERE year = 2015)
AND year = 2015;

SELECT countries.name AS country,
  (SELECT COUNT(*)
   FROM cities
   WHERE countries.code = cities.country_code) AS cities_num
FROM countries
ORDER BY cities_num DESC, country
LIMIT 9;

If subquery is one or multiple column then can be regarded as a new table

SELECT name,subquery.count
FROM countries,(SELECT country_code, COUNT(*)
FROM cities
GROUP BY country_code
) AS subquery
WHERE countries.code = subquery.country_code;