CREATE TABLE

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);

INSERT

The INSERT statement inserts a new row into a table.

INSERT INTO celebs (id, name, age) 
VALUES (1, 'Justin Bieber', 22);

SELECT

SELECT statements are used to fetch data from a database. In the statement below, SELECT returns all data in the name column of the celebs table.

SELECT statements always return a new table called the result set.

SELECT name FROM celebs;

SELECT column1, column2 
FROM table_name;

AS

AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes. Here we renamed the name column as Titles.

SELECT name AS 'Titles'
FROM movies;

Although it’s not always necessary, it’s best practice to surround your aliases with single quotes.
When using AS, the columns are not being renamed in the table. The aliases only appear in the result.

Distinct

DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).

SELECT tools 
FROM inventory;

// output might be
tools
Hammer
Nails
Nails
Nails

SELECT DISTINCT tools 
FROM inventory;
// output might be
tools
Hammer
Nails

where

We can restrict our query results using the WHERE clause in order to obtain only the information we want.

WHERE clause filters the result set to only include rows where the following condition is true.

like (1)

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

SELECT * 
FROM movies
WHERE name LIKE 'Se_en';

lile (2)

% is a wildcard character that matches zero or more missing letters in the pattern. For example:

A% matches all movies with names that begin with letter ‘A’

%a matches all movies that end with ‘a’

SELECT * 
FROM movies
WHERE name LIKE 'A%';

// any movie that contains the word ‘man’ in its name will be returned in the result.
SELECT * 
FROM movies 
WHERE name LIKE '%man%';

LIKE is not case sensitive. ‘Batman’ and ‘Man of Steel’ will both appear in the result of the query above.

SELECT * 
FROM movies
WHERE name LIKE 'The %';

There is also a space in between because we don’t want words like ‘There’, ‘They’, etc.

SELECT *
FROM nomnom
WHERE cuisine = 'Italian'
   AND price LIKE '%$$$%';

SELECT *
FROM nomnom
WHERE cuisine = 'Italian'
   AND price = '$$$';

NULL

It is not possible to test for NULL values with comparison operators, such as = and !=.

Instead, we will have to use these operators:

IS NULL
IS NOT NULL

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

BETWEEN

The BETWEEN operator is used in a WHERE clause to filter the result set within a certain range. It accepts two values that are either numbers, text or dates.

// BETWEEN two numbers is inclusive of the second number.
SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

In this statement, BETWEEN filters the result set to only include movies with names that begin with the letter ‘A’ up to, but not including ones that begin with ‘J’.

BETWEEN is case-sensitive

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

AND

AND operator displays a row if all the conditions are true.

OR

OR operator displays a row if any condition is true.

Order By

We can sort the results using ORDER BY, either alphabetically or numerically

SELECT *
FROM movies
ORDER BY name;

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

DESC is a keyword used in ORDER BY to sort the results in descending order (high to low or Z-A).
ASC is a keyword used in ORDER BY to sort the results in ascending order (low to high or A-Z).
### Limit
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. This saves space on our screen and makes our queries run faster.

LIMIT always goes at the very end of the query. Also, it is not supported in all SQL databases.

SELECT *
FROM movies
LIMIT 10;

ALTER

The ALTER TABLE statement adds a new column to a table. You can use this command when you want to add columns to a table. The statement below adds a new column twitter_handle to the celebs table.

NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL (∅) values for twitter_handle.

ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

Update

use the UPDATE statement when you want to change existing records.

UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4;

Delete

The DELETE FROM statement deletes one or more rows from a table

DELETE FROM celebs 
WHERE twitter_handle IS NULL;

Constraints

They can be used to tell the database to reject inserted data that does not adhere to a certain restriction

PRIMARY KEY columns can be used to uniquely identify the row.(具備唯一性)
UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
NOT NULL columns must have a value. Attempts to insert a row without a value for a NOT NULL column will result in a constraint violation and the new row will not be inserted.
DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

sum up (1)

A statement is a string of characters that the database recognizes as a valid command.

CREATE TABLE creates a new table.
INSERT INTO adds a new row to a table.
SELECT queries data from a table.
ALTER TABLE changes an existing table.
UPDATE edits a row in a table.
DELETE FROM deletes rows from a table.

Case

Each WHEN tests a condition and the following THEN gives us the string if the condition is true.
The ELSE gives us the string if all the above conditions are false.

The CASE statement must end with END.

SELECT name,
CASE
WHEN imdb_rating > 8 THEN 'Fantastic'
WHEN imdb_rating > 6 THEN 'Poorly Received'
ELSE 'Avoid at All Costs'
END
FROM movies;

SELECT name,
  CASE
    WHEN genre = 'romance' THEN 'Chill'
    WHEN genre = 'comedy' THEN 'Chill'
    ELSE 'Intense'
  END AS 'Mood'
FROM movies

sum up (2)

SELECT is the clause we use every time we want to query information from a database.
AS renames a column or table.
DISTINCT return unique values.
WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
LIKE and BETWEEN are special operators.
AND and OR combines multiple conditions.
ORDER BY sorts the result.
LIMIT specifies the maximum number of rows that the query will return.
CASE creates different outputs.

AGGREGATE FUNCTIONS

COUNT(): count the number of rows
SUM(): the sum of the values in a column
MAX()/MIN(): the largest/smallest value
AVG(): the average of the values in a column
ROUND(): round the values in the column

Count

COUNT() is a function that takes the name of a column as an argument and counts the number of non-empty values in that column.

we want to count every row, so we pass * as an argument inside the parenthesis.

SELECT COUNT(*)
FROM table_name;

Sum

SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

SELECT SUM(downloads)
FROM fake_apps;

Max / Min

MAX() takes the name of a column as an argument and returns the largest value in that column. Here, we returned the largest value in the downloads column.

MIN() works the same way but it does the exact opposite; it returns the smallest value.

SELECT MAX(downloads)
FROM fake_apps;

Average

The AVG() function works by taking a column name as an argument and returns the average value for that column

SELECT AVG(downloads)
FROM fake_apps;

Round

By default, SQL tries to be as precise as possible without rounding. We can make the result table easier to read using the ROUND() function.

ROUND() function takes two arguments inside the parenthesis:

a column name
an integer

SELECT name, ROUND(price, 0)
FROM fake_apps;

// 小數點第二位
SELECT ROUND(AVG(price), 2)
FROM fake_apps;

Group By I

GROUP BY is a clause in SQL that is used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY statement comes after any WHERE statements, but before ORDER BY or LIMIT.

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 1999;

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2000;

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2001;

We can use GROUP BY to do this in a single step:

SELECT year,
   AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;

Group By II

Sometimes, we want to GROUP BY a calculation done on a column.

SQL lets us use column reference(s) in our GROUP BY that will make our lives easier.

1 is the first column selected
2 is the second column selected
3 is the third column selected

SELECT category, 
   price,
   AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;

Having

SQL also allows you to filter which groups to include and which to exclude.

HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can be used with HAVING.

HAVING statement always comes after GROUP BY, but before ORDER BY and LIMIT.

When we want to limit the results of a query based on values of the individual rows, use WHERE.
When we want to limit the results of a query based on an aggregate property, use HAVING

SELECT year,
   genre,
   COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 10;

sum up 3

COUNT(): count the number of rows
SUM(): the sum of the values in a column
MAX()/MIN(): the largest/smallest value
AVG(): the average of the values in a column
ROUND(): round the values in the column
Aggregate functions combine multiple rows together to form a single value of more meaningful information.
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
HAVING limit the results of a query based on an aggregate property.

note：how to hack hacker news 可以多做幾次

MULTIPLE TABLES

JOIN

SELECT *
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

The third line uses JOIN to say that we want to combine information from orders with customers.
The fourth line tells us how to combine the two tables. We want to match orders table’s customer_id column with customers table’s customer_id column.

The WHERE clause goes after the JOIN!

SELECT *
FROM orders
JOIN subscriptions
  ON orders.subscription_id = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';

Inner Joins

When we perform a simple JOIN (often called an inner join) our result only includes rows that match our ON condition.

SELECT  COUNT(*)
FROM newspaper;

SELECT COUNT(*)
FROM online;

SELECT COUNT(*)
FROM newspaper
JOIN online
ON newspaper.id = online.id

Left Joins

What if we want to combine two tables and keep some of the un-matched rows?

A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table.
codecademy-LEFT JOINS

SELECT * FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id;

SELECT * FROM newspaper
LEFT JOIN online
ON newspaper.id = online.id
WHERE online.id IS NULL;

Primary Key vs Foreign Key

Primary keys have a few requirements:

None of the values can be NULL.
Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table).
A table can not have more than one primary key column.

When the primary key for one table appears in a different table, it is called a foreign key.

So customer_id is a primary key when it appears in customers, but a foreign key when it appears in orders.

Why is this important? The most common types of joins will be joining a foreign key from one table with the primary key from another table. For instance, when we join orders and customers, we join on customer_id, which is a foreign key in orders and the primary key in customers.

Cross Join

Sometimes, we just want to combine all rows of one table with all rows of another table.

SELECT shirts.shirt_color,
   pants.pants_color
FROM shirts
CROSS JOIN pants;

Notice that cross joins don’t require an ON statement. You’re not really joining on any columns!

A more common usage of CROSS JOIN is when we need to compare each row of a table to a list of values.

Union

Sometimes we just want to stack one dataset on top of the other. Well, the UNION operator allows us to do that.

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

SQL has strict rules for appending data:

Tables must have the same number of columns.
The columns must have the same data types in the same order as the first table.

With

Often times, we want to combine two tables, but one of the tables is the result of another calculation.

The WITH statement allows us to perform a separate query (such as aggregating customer’s subscriptions)
previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause
We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table)

Essentially, we are putting a whole first query inside the parentheses () and giving it a name. After that, we can use this name as if it’s a table and write a new query using the first query.

WITH previous_results AS (
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM previous_results
JOIN customers
  ON _____ = _____;

Do not include ; inside of the () of your WITH statement.

WITH previous_query AS (
SELECT customer_id,
   COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id
)
SELECT customers.customer_name, previous_query.subscriptions
FROM previous_query
JOIN customers 
ON previous_query.customer_id = customers.customer_id;

sum up (3)

JOIN will combine rows from different tables if the join condition is true.
LEFT JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
Primary key is a column that serves a unique identifier for the rows in the table. Foreign key is a column that contains the primary key to another table.
CROSS JOIN lets us combine all rows of one table with all rows of another table.
UNION stacks one dataset on top of another.
WITH allows us to define one or more temporary tables that can be used in the final query.

LEARNING FROM

codecademy Learn SQL