Commande et filtrage

Introduction à SQL Server

John MacKintosh

Instructor

Organiser ! Organiser !

Les tables sont constituées de lignes et de colonnes
Les requêtes renvoient des ensembles ou des sous-ensembles

Les ensembles n'ont pas d'ordre inhérent
Si l'ordre est important, utiliser ORDER BY

SELECT TOP (10) prod_id, year_intro 
FROM products 
-- Order in ascending order
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 
-- Order year_intro in descending order
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 
-- Order in different directions
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 
-- Both columns in descending order
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
-- Ordering text (Ascending order)
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
-- Ordering text (Descending order)
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         |
+-------------+----------------+

Que faire si nous souhaitons uniquement renvoyer les lignes qui répondent à certains critères ?

SELECT customer_id, total
FROM invoice 
WHERE total > 15;

Les trois premiers clients dont la valeur de la facture est supérieure à 15

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

-- Rows with points greater than 10
WHERE points > 10

-- Rows with points less than 10
WHERE points  < 10

-- Rows with points greater than or equal to 10
WHERE points >= 10

-- Rows with points less than or equal to 20
WHERE points  <= 20

-- Character data type
WHERE country = 'Spain'

-- Date data type 
WHERE event_date = '2012-01-02'

SELECT customer_id, total 
FROM invoice 
-- Testing for non-equality
WHERE total <> 10;

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

Entre

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

Qu'est-ce que NULL ?

NULL indique qu'il n'y a aucune valeur pour cet enregistrement
Les valeurs NULLES permettent de mettre en évidence les lacunes dans nos données

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

Faites du tri !

Introduction à SQL Server