Pengurutan dan Pemfilteran

Pengantar SQL Server

John MacKintosh

Instructor

Urut! Urut!

Tabel terdiri dari baris dan kolom
Kueri mengembalikan himpunan atau subhimpunan

Himpunan tidak memiliki urutan bawaan
Jika urutan penting, gunakan ORDER BY

SELECT TOP (10) prod_id, year_intro 
FROM products 
-- Urutan naik
ORDER BY year_intro, product_id;

+------------+-------------+
| product_id | year_intro  |
|------------+-------------|
| 36         |  1981       |
| 37         |  1982       |
| 38         |  1983       |
| 39         |  1984       |
| 40         |  1984       |
| 41         |  1984       |
| 52         |  1985       |
| 43         |  1986       |
| 44         |  1987       |
| 54         |  1987       |
+------------+-------------+

SELECT TOP (10) product_id, year_intro 
FROM products 
-- Urutkan year_intro menurun
ORDER BY year_intro DESC, product_id;

+------------+-------------+
| product_id | year_intro  |
|------------+-------------|
| 158        |  2015       |
| 173        |  2015       |
| 170        |  2014       |
| 171        |  2014       |
| 172        |  2014       |
| 144        |  2013       |
| 146        |  2013       |
| 147        |  2013       |
| 148        |  2013       |
| 149        |  2013       |
+------------+-------------+

SELECT 
  TOP (10) appearances, 
  year_intro 
FROM products 
-- Urutkan dengan arah berbeda
ORDER BY 
  year_intro DESC, 
  appearances;

+-------------+------------+
| appearances | year_intro |
|-------------+------------|
| 35          | 2015       |
| 74          | 2015       |
| 29          | 2014       |
| 45          | 2014       |
| 48          | 2014       |
| 12          | 2013       |
| 13          | 2013       |
| 14          | 2013       |
| 22          | 2013       |
| 24          | 2013       |
+-------------+------------+

SELECT 
  TOP (10) appearances, 
  year_intro 
FROM products 
-- Kedua kolom menurun
ORDER BY 
  year_intro DESC, 
  appearances DESC;

+-------------+------------+
| appearances | year_intro |
|-------------+------------|
| 74          | 2015       |
| 35          | 2015       |
| 48          | 2014       |
| 45          | 2014       |
| 29          | 2014       |
| 837         | 2013       |
| 642         | 2013       |
| 561         | 2013       |
| 491         | 2013       |
| 198         | 2013       |
+-------------+------------+

SELECT city_id, name_alias
FROM invoice
-- Mengurutkan teks (naik)
ORDER BY name_alias;

+-------------+----------------+
| city_id     | name_alias     |
|-------------+----------------|
|  48         | Amsterdam      |
|  59         | Bangalore      |
|  36         | Berlin         |
|  38         | Berlin         |
|  42         | Bordeaux       |
|  23         | Boston         |
|  13         | Brasília       |
|   8         | Brussels       |
|  45         | Budapest       |
|  56         | Buenos Aires   |
+-------------+----------------+

SELECT city_id, name_alias 
FROM invoice
-- Mengurutkan teks (turun)
ORDER BY name_alias DESC;

+-------------+----------------+
| city_id     | name_alias     |
|-------------+----------------|
|  33         | Yellowknife    |
|  32         | Winnipeg       |
|  49         | Warsaw         |
|   7         | Vienne         |
|  15         | Vancouver      |
|  27         | Tucson         |
|  29         | Toronto        |
|   2         | Stuttgart      |
|  51         | Stockholm      |
|  55         | Sydney         |
+-------------+----------------+

Bagaimana jika kita hanya ingin mengembalikan baris yang memenuhi kriteria tertentu?

SELECT customer_id, total
FROM invoice 
WHERE total > 15;

3 pelanggan pertama dengan nilai invoice > 15

+-------------+------------+
| customer_id | total      |
|-------------+------------|
| 57          | 17.91      |
|  7          | 18.86      |
| 45          | 21.86      |
+-------------+------------+

-- Baris dengan points lebih dari 10
WHERE points > 10

-- Baris dengan points kurang dari 10
WHERE points  < 10

-- Baris dengan points lebih dari atau sama dengan 10
WHERE points >= 10

-- Baris dengan points kurang dari atau sama dengan 20
WHERE points  <= 20

-- Tipe data karakter
WHERE country = 'Spain'

-- Tipe data tanggal 
WHERE event_date = '2012-01-02'

SELECT customer_id, total 
FROM invoice 
-- Menguji ketidak-samaan
WHERE total <> 10;

+------------+-------+
| customer_id | total |
|------------+-------|
| 2          | 1.98  |
| 4          | 3.96  |
| 8          | 5.94  |
| 14         | 8.91  |
| 23         | 13.86 |
| 37         | 0.99  |
+------------+-------+

Between

SELECT customer_id, total 
FROM invoice 
WHERE total BETWEEN 20 AND 30;

+------------+--------+
| customer_id | total  |
|------------+--------|
| 45         | 21.86  |
| 46         | 21.86  |
| 26         | 23.86  |
| 6          | 25.86  |
+------------+--------+

SELECT customer_id, total 
FROM invoice 
WHERE total NOT BETWEEN 20 AND 30;

+------------+--------+
| customerid | total  |
|------------+--------|
|  2         | 1.98   |
|  4         | 3.96   |
|  8         | 5.94   |
| 14         | 8.91   |
+------------+--------+

Apa itu NULL?

NULL menunjukkan tidak ada nilai untuk rekaman itu
NULL membantu menyoroti celah data

SELECT 
  TOP (6) total, 
  billing_state 
FROM invoice 
WHERE billing_state IS NULL;

+-------+---------------+
| total | billing_state |
|-------+---------------|
| 1.98  | NULL          |
| 3.96  | NULL          |
| 5.94  | NULL          |
| 0.99  | NULL          |
| 1.98  | NULL          |
| 1.98  | NULL          |
+-------+---------------+

SELECT 
  TOP (6) total, 
  billing_state 
FROM invoice 
WHERE billing_state IS NOT NULL;

+--------+---------------+
| total  | billing_state |
|--------+---------------|
|  8.91  | AB            |
| 13.96  | MA            |
|  5.94  | Dublin        |
|  0.99  | CA            |
|  1.98  | WA            |
|  1.98  | CA            |
+-------+----------------+

Ayo kita urutkan!

Pengantar SQL Server