###### tags: `sql`
# SQL queries(course2)
### 1. **As** (rename columns)

SELECT name AS '______'  
FROM movies;

### 2. **Distinct** (return unique values in the output)

SELECT DISTINCT column_name
FROM table_name;


### 3. **Where**

SELECT column_name
FROM table_name
WHERE conditions;
-- ex:
SELECT * 
FROM movies 
WHERE imdb_rating < 5;

> = equal to
!= not equal to
< less than, > greater than
>= greater than or equal to
<= less than or equal to
### 4. **Like**
timing: compare similar values
#### (1)

SELECT * 
FROM movies
WHERE name LIKE 'Se_en'; 

LIKE : 與WHERE搭配使用，尋找column中的特定值；除了TYPE，也可以使用在數字上
_ : 可用任何字母替代，這裡Seven與Se7en皆符合此條件
#### (2)

SELECT * 
FROM movies
WHERE name LIKE 'A%';

A% : 所有名字是A開頭的電影
%a : 所有名字是a結尾的電影
%man% : 所有名字裡面包含man的電影

### 5. **Is NULL**

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;


### 6. **Between**
在使用WHERE時，用BETWEEN定義範圍(數字範圍或字母)

SELECT *
FROM movies
WHERE name BETWEEN 'D' AND 'G'; #顯示D到F開頭的結果


### 7. To make the result set more specific
(1) **And**
交集
(2) **Or**
聯集

SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

SELECT *
FROM movies
WHERE year > 2014
   OR genre = 'action';

### 8. **Order by**

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

<font color="#900C3F">DESC</font> : 升序排列
<font color="#900C3F">ASC</font> : 降序排列

### 9. **Limit**
To specify the maximum number of rows the result set will have.(makes our queries run faster)

SELECT *
FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;  #找出分數前三高的電影 

如果要找前四到前六順位的電影，則改為limit 3 offset 3

### 10. **Case**
類似if-then的邏輯式

SELECT name,
 CASE
  WHEN genre = 'romance' THEN 'Chill'
  WHEN genre = 'comedy' THEN 'Chill'
  ELSE 'Intense'
 END AS 'Mood'
FROM movies;

<font color="#900C3F">END AS</font> : 縮短if-then找出的column並命名
<font color="#900C3F">CASE</font> statement must end with <font color="#900C3F">END</font>

| Syntax | meaning |
| ------------- | -------------------------------------------------------------------- |
| SELECT | query info from a database |
| AS | rename |
| DISTINCT | return unique values |
| WHERE | filter the results of the query based on conditions that you specify |
| LIKE、BETWEEN | special order |
| AND、OR | combines multiple conditions |
| ORDER BY | sort the result |
| LIMIT | specifies the max number of rows that the query will return |
| CASE | creates different outputs (if-then) |