rose tinted glasses

JOIN : combine information from multiple tables in one query JOIN 5줄 요약 조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다. INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다. OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다. CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다. SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다. 1. Inner join Combin the two tables in one que..

1. Mathematical Functions and Operators Syntax SELECT film_id, rental_rate as old_rental_rate, CEILING(rental_rate * 1.1) as new_rental_rate FROM film https://www.postgresql.org/docs/16/functions-math.html 9.3. Mathematical Functions and Operators 9.3. Mathematical Functions and Operators # Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical con..

6. EXTRACT Used to extract parts of timestamp/date Syntax EXTRACT (field from date/time/interval) SELECT EXTRACT(day from rental_date), COUNT(*) FROM rental GROUP BY EXTRACT(day from rental_date) ORDER BY COUNT(*) DESC * Challenge Write a query to find out the following: 1) What's the month with the highest total payment amount? 2) What's the day of week with the highest total payment amount?(0 ..

1. LOWER & UPPER, LENGTH Syntax SELECT LOWER (email) AS email_lower, email, LENGTH(email) FROM customer WHERE LENGTH(email) < 30 * Challenge Write a query to find customers whose either the first name or the last name is more than 10 characters long. Output the list of these first and last names in all lower case. SELECT LOWER(first_name), LOWER(last_name), LOWER(email) FROM customer WHERE LENGT..

1. Aggregate Functions Aggregate values in multiple rows to one value SUM, AVG, MIN, MAX, COUNT Syntax SELECT SUM (amount), ROUND(AVG (amount), 3) AS AVERAGE -- 3 decimal places FROM payment * Challenge Write a query to see the minimum, maximum, average(rounded), sum of the replacement cost of the films SELECT MIN (replacement_cost), MAX (replacement_cost), ROUND(AVG(replacement_cost), 3) AS AVG..

1. WHERE Used to filter the data in the output always after FROM SELECT * FROM payment WHERE amount = 10.99; -- end of query SELECT first_name, last_name FROM customer WHERE first_name = 'ADAM' * Challenge 1. How many payment were made by the customer with customer_id = 100? 2. What is the last name of our customer with first name 'ERICA'? SELECT COUNT(*) FROM payment WHERE customer_id = 100; SE..

ORDER BY ORDER BY 'COLUMN' ASC - default, DESC using index number instead of column names SELECT DISTINCT Eliminate duplicated values more than two columns - considering all columns and show only unique values Find out unique values in columns *Challenge 1. show different prices that have been paid 2. order the prices from high to low LIMIT Used to limit the number of rows in the output Always a..