SQL (Udemy)

--PostgreSQL--

Databases

• <query> is a resquest to batabase to fetch the information
• systems that allows user to store and organize data
• Spreadsheets vs Databases

Platform options

• PostgreSQL, MySQL,MariaSQL, Microsoft Access,..

SELECT

• SELECT column_name FROM table_name

DISTINCT

• SELECT DISTINCT column_name FROM table_name

COUNT

SELECT COUNT (DISTINCT(district)) FROM address

SELECT WHERE

SELECT column1, column2

FROM table

WHERE conditions;

ORDER BY

SELECT * FROM customer ORDER BY first_name DESC;

LIMIT

SELECT * FROM payment
ORDER BY payment_date
LIMIT 5

BETWEEN

same as:

≥ low and ≤high

BETWEEN low AND high

• can be used with dates

IN

instead of using many OR

SELECT * from payment WHERE amount IN (0.99,1.98)

LIKE and ILIKE using pattern matching

• wildcard characters:
  • % : matches any sequences of characters
  • _ ( underscore): matches any single character
• LIKE: case-sensitive
• ILIKE: case-insensitive

SELECT COUNT (*) FROM customer
WHERE first_name LIKE 'J%'

SELECT COUNT (*) FROM customer
WHERE first_name ILIKE 'j%'

Aggregation function

• multiple inputs → single output
• AVG(), COUNT(), MAX()m MIN(), SUM()
  • null 무시한다
• SELECT , HAVING clause

SELECT MIN(replacement_cost) FROM film;

SELECT ROUND(AVG(replacement_cost),2) FROM film;

GROUP BY

• DEVIDE ROWS INTO GROUPS

SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id
ORDER BY SUM(amount)

HAVING

• filer after an aggregation has already taken place

SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100

SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) >= 100

JOIN

Combine columns from one or more tables into a new table.

• INNER JOINS, OUTER JOINS, FULL JOINS, UNIONS
• AS statement: only exist at data outputSELECT * FROM payment (table1) INNER JOIN customer(table2) ON payment.customer_id = customer.customer_idLeft Outer Joins
  • Order does matter

  SELECT * FROM tableA
  LEFT OUTER JOIN tableB
  ON tableA.column = tableB.column
  

  • using WHERE

  SELECT * FROM tableA
  LEFT OUTER JOIN tableB
  ON tableA.cl_match = tableB.col_match
  WHERE tableB.id IS null
  

UNION

To combine the result-set of two or more SELECT statements

• If there is no match with the right table, the resulrs are null
• Full Outer Joins , Inner Joins