2.1Filtering Records

Intro

Query를 filter를 통회 조회하는 방법

“Where” filter

sql
SELECT name, area FROM ~~cities~~ 
WHERE area > 4000;

쿼리를 이해할 때, 왼쪽부터 오른쪽으로 읽으면 혼란스러울 수 있음

: 실제로 쿼리를 postgres가 이해하는 것은 아래의 순서

1. FROM cities (data source)
2. WHERE area > 4000
3. SELECT name, area

• Comparison Math Operators
  • = : 같은 값인지
  • <, >, >=, <= : 값 비교
  • IN : is the value present in a list?
  • <> : not equal
  • =! : not equal
  • BETWEEN : between two values
  • NOT IN : not present in a list

Compound filter

1. BETWEEN

sql
SELECT name, area FROM cities 
WHERE area BETWEEN 2000 AND 4000;

1. IN

sql
SELECT name, area FROM cities 
WHERE name IN ('Delhi', 'Shanghai');

1. NOT IN

sql
SELECT name, area FROM cities 
WHERE name NOT IN ('Delhi', 'Shanghai');

1. compound comparison

• AND

sql
SELECT name, area FROM cities 
WHERE 
	area NOT IN (3043, 8223)
	AND name = 'Delhi';

• OR

sql
SELECT name, area FROM cities 
WHERE 
	area NOT IN (3043, 8223)
	OR name = 'Delhi';

• calculation filter

sql
SELECT name, population / area AS population_density
FROM
	cities
WHERE
	population / area > 6000;

Updating the row

sql
UPDATE cities SET population = 39505000
WHERE name = 'TOKYO';

Deleting the row

sql
DELETE FROM cities WHERE name = 'Tokyo';