---
# System prepended metadata

title: SQL queries(course2)
tags: [sql]

---

###### tags: `sql`
# SQL queries(course2)
### 1. **As** (rename columns)
``` sql=
SELECT name AS '______'  
FROM movies;
```
### 2. **Distinct** (return unique values in the output)
``` sql=
SELECT DISTINCT column_name
FROM table_name;
```
![](https://i.imgur.com/VUXY2Zc.jpg =20%x)

### 3.  **Where**
``` sql=
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) 
``` sql=
SELECT * 
FROM movies
WHERE name LIKE 'Se_en'; 
```
LIKE : 與WHERE搭配使用，尋找column中的特定值；除了TYPE，也可以使用在數字上
_ : 可用任何字母替代，這裡Seven與Se7en皆符合此條件
![](https://i.imgur.com/FYHX40J.jpg =70%x)
#### (2)
``` sql= 
SELECT * 
FROM movies
WHERE name LIKE 'A%';
```
A% : 所有名字是A開頭的電影
%a : 所有名字是a結尾的電影
%man% : 所有名字裡面包含man的電影

### 5. **Is NULL**
``` sql=
SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;
```
![](https://i.imgur.com/9syQlcL.jpg =30%x)

### 6. **Between**
在使用WHERE時，用BETWEEN定義範圍(數字範圍或字母)
``` sql=
SELECT *
FROM movies
WHERE name BETWEEN 'D' AND 'G'; #顯示D到F開頭的結果
```
![](https://i.imgur.com/3sl6ABF.jpg =70%x)

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

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

SELECT *
FROM movies
WHERE year > 2014
   OR genre = 'action';
``` 
### 8. **Order by**
``` sql=
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)
``` sql=
SELECT *
FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;  #找出分數前三高的電影 
```
![](https://i.imgur.com/lxYExGq.jpg =70%x)
如果要找前四到前六順位的電影，則改為limit 3 offset 3

### 10. **Case**
類似if-then的邏輯式
``` sql=
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)                                            |
