Sıralama ve Filtreleme

SQL Server'a Giriş

John MacKintosh

Instructor

Sıra! Sıra!

Tablolar satır ve sütunlardan oluşur
Sorgular kümeler veya alt kümeler döndürür

Kümelerin doğuştan bir sırası yoktur
Sıra önemliyse ORDER BY kullanın

SELECT TOP (10) prod_id, year_intro 
FROM products 
-- Artan sırada sırala
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 
-- year_intro'yu azalan sırada sırala
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 
-- Farklı yönlerde sırala
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 
-- Her iki sütun da azalan sırada
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
-- Metni sıralama (Artan sıra)
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
-- Metni sıralama (Azalan sıra)
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         |
+-------------+----------------+

Yalnızca belirli ölçütleri sağlayan satırları döndürmek istersek?

SELECT customer_id, total
FROM invoice 
WHERE total > 15;

Fatura tutarı > 15 olan ilk 3 müşteri

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

-- 10'dan büyük puanlı satırlar
WHERE points > 10

-- 10'dan küçük puanlı satırlar
WHERE points  < 10

-- 10'a büyük eşit puanlı satırlar
WHERE points >= 10

-- 20'ye küçük eşit puanlı satırlar
WHERE points  <= 20

-- Karakter veri türü
WHERE country = 'Spain'

-- Tarih veri türü 
WHERE event_date = '2012-01-02'

SELECT customer_id, total 
FROM invoice 
-- Eşit olmama testi
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   |
+------------+--------+

NULL nedir?

NULL, o kayıtta değer olmadığını gösterir
NULL'lar verideki boşlukları vurgular

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            |
+-------+----------------+

Hadi sıralayalım!

SQL Server'a Giriş