Intro of SQL | #1 Basic Keyword

Meaning of Keyword
: Reserved words that significance in SQL
: Normally write in Capitals for distinguising with Query

This post is based on PostgreSQL syntax

1. Basic Keyword

KEYWORD - BASIC	DESCRIPTION
SELECT	List of columns or expressions that must be returned
AS	Adjust column name as following words
FROM	Table to get the data
WHERE	Filter based on both text & numeric values Always show up after 'FROM' Must use single quotes with 'WHERE' WHERE cluase only filter by specifying the exact text in PostgreSQL must use single quotes ; with WHERE
NOT IN ( )	Allows specifying multiple values in WHERE Always with WHERE
LIMIT	Extract Limited value
DISTINCT	Extract Unique value
AND	Combining multiple conditions
OR	Sort multiple conditions
BETWEEN	Inclusive, meaning the beginning and end values are included Always with AND
INTO	Build New table
IS NULL	Null Check (IS NOT NULL) With WHERE query

SELECT COUNT(DISTINCT due_date)
FROM people;


SELECT title
FROM films
WHERE 
	year IN (2015, 2020)
	AND (certification = 'A' OR certification = 'B');
-- be sure to enclose the individual clauses in parentheses


SELECT human_name AS name
FROM people
WHERE due_date IS NOT NULL;


-- SELECT every columns data
SELECT *
FROM people;


-- USING INTO
SELECT country_code, size,
  CASE WHEN size > 50000000
            THEN 'large'
       WHEN size > 1000000
            THEN 'medium'
       ELSE 'small' END
       AS popsize_group
INTO pop_plus       
FROM populations
WHERE year = 2015;

2. BY Keyword

KEYWORD - BY	DESCRIPTION
ORDER BY DESC	BY default ORDER BY will sort in ascending order If want descending order put in DESC ORDER BY clause at the end of the query
GROUP BY	Grouping by following words GROUP BY always goes after the FROM clause, not FROM
HAVING	Aggregate functions can't be used in WHERE clauses Always use with GROUP BY

SELECT birthdate, name
FROM people
ORDER BY birthdate, name

SELECT release_year
FROM films
GROUP BY release_year
WHERE COUNT(title) > 10
-- Above code occurring error

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10
-- This works

3. Aggregate Function

Aggregate Function	DESCRIPTION
COUNT(*)	Num of rows in one or more columns
AVG	Calculate Average 'SELECT' value
MAX	Extract Maximum 'SELECT' value
MIN	Extract Minimum 'SELECT' value

4. LIKE Keyword

KEYWORD - LIKE	DESCRIPTION
NOT LIKE	WHERE clause only filter by specifying the exact text LIKE operator can be used in a WHERE clause
HAVING	Aggregate functions can't be used in WHERE clauses
%	Will match zero, one, or many characters in text Use with LIKE
_	Will match single character Use with LIKE

SELECT company_name
FROM companies
WHERE company_name LIKE 'SAMSUNG%';
-- Will extract SAMSUNG, SUMSUNG1, SAMSUNG_Electronics and on..

SELECT company_name
FROM companies
WHERE company_name LIKE 'SAMS_NG';
-- _ : will match single character : 'SAMSUNG', 'SAMSONG', 'SAMSING'

SELECT human_name
FROM people
WHERE human_name LIKE'_r%
-- human_names have 'r' as the second letter

5. Filter

****	DESCRIPTION
`.	`
=	equal
<>	not equal
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

6. Arithmetic

Arithmetic	DESCRIPTION
/	devide

SELECT (4/3)
-- Conclusion 1 
-- SQL assuumes that if divide an integer by an integer, 
-- user want to get an integer back

SELECT (4.0 / 3.0) AS result


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;

7. ETC

Format	DESCRIPTION
ISO Date	1927-11-11
/* */	multi line comments
--	single line comment