[SQL] WHERE & AND & OR & BETWEEN & IN & IS (NOT) NULL & LIKE

---

SQL Übung

• SQL 문법 정리(2)
• WHERE을 통해 filter
  • WHERE는 FROM 이후에 작성
  • WHERE 조건이 여러개일 경우 조건을(AND, OR) 모두 작성해줘야함
  • AND & OR은 괄호로 묶어주는 것이 좋음
  • BETWEEN을 통해 범위 filter
    • 가장 처음과 마지막은 포함
  • IN을 통해 (여러개의 OR) 간소화
  • IS NULL을 통해 NULL filter
  • IS NOT NULL을 통해 NULL이 아닌 값을 filter
  • LIKE을 통해 pattern을 활용하여 filter
    • %을 통해 한 개 이상의 문자를 대체
    • _을 통해 한 개의 문자를 대체
      
      
• 연산자
  • = equal
  • <> not equal
  • < less than
  • > greater than
  • <= less than or equal to
  • >= greater than or equal to

---

WHERE

1-1) Simple filtering of numeric values

• Get all details for all films released in 2016.

SELECT *
FROM films
WHERE release_year = 2016;

1-2) Simple filtering of numeric values

• Get the title and release year of films released after 2000.

SELECT title, release_year
FROM films
WHERE release_year > 2000;

2-1) Simple filtering of text

• Get the number of Hindi language films.

SELECT COUNT(language)
FROM films
WHERE language = 'Hindi';

2-2) Simple filtering of text

• Get all details for all films with an R certification.

SELECT *
FROM films
WHERE certification = 'R'

---

AND

3-1) WHERE AND

• Get the title and release year for all Spanish language films released before 2000.

SELECT title,release_year
FROM films
WHERE release_year < 2000
AND language = 'Spanish';

3-2) WHERE AND

• Get all details for Spanish language films released after 2000, but before 2010.

SELECT *
FROM films
WHERE language = 'Spanish'
AND release_year > 2000
AND release_year < 2010;

---

OR

4) WHERE AND OR

• get the title and release year of films released in the 90s which were in French or Spanish and which took in more than $2M gross

SELECT title, release_year
FROM films
WHERE (release_year >= 1990 AND release_year < 2000)
AND (language = 'French' OR language = 'Spanish')
AND gross > 2000000;

---

AND & OR

5) WHERE AND OR

• EMP에서 CLERK 업무를 하면서 RESEARCH 인 직원과 MANAGER 업무를 하면서 ACCOUNTING 부서인 직원들의 이름, 사번, job, 부서명은?

SELECT e.ename, e.empno, e.job, d.dname
FROM emp e, dept d 
WHERE (e.deptno = d.deptno)
AND (e.job, d.dname) IN (('CLERK', 'RESEARCH'), 
						 ('MANAGER', 'ACCOUNTING'));

# 위와 같음
SELECT e.ename, e.empno, e.job, d.dname
FROM emp e, dept d 
WHERE (e.deptno = d.deptno) 
AND (
	   (e.job = 'CLERK') AND (d.dname = 'RESEARCH') 
	OR (e.job = 'MANAGER') AND (d.dname = 'ACCOUNTING') 
)

---

BETWEEN

6) BETWEEN

• get the title and release year of all Spanish language films released between 1990 and 2000 (inclusive) with budgets over $100 million

SELECT title, release_year
FROM films
WHERE release_year BETWEEN 1990 AND 2000
AND budget > 100000000
AND language = 'Spanish';

---

IN

7) WHERE IN

• Get the title and release year of all films released in 1990 or 2000 that were longer than two hours

SELECT title, release_year
FROM films
WHERE release_year IN (1990, 2000)
AND duration > 120;

---

NULL & IS NULL

8) NULL and IS NULL

• NULL은 반드시 IS NULL 혹은 IS NOT NULL로 사용해야 함
• Get the number of films which don’t have a language associated with them.

SELECT COUNT(*)
FROM films
WHERE language IS NULL;

---

LIKE & NOT LIKE

9-1) LIKE and NOT LIKE

• Get the names of all people whose names begin with ‘B’.

SELECT name
FROM people
WHERE name LIKE 'B%';

9-2) LIKE and NOT LIKE

• Get the names of people whose names have ‘r’ as the second letter

SELECT name
FROM people
WHERE name LIKE '_r%';

9-3) LIKE and NOT LIKE

• Get the names of people whose names don’t start with A

SELECT name
FROM people
WHERE name NOT LIKE 'A%';

# Get the names of people whose names doesnt start with a(case-sensitive)
SELECT name
FROM people
WHERE name NOT LIKE Binary 'a%';

---