Clause WHERE

Introduction à SQL Server

John MacKintosh

Instructor

SELECT song, artist 
FROM songlist 
WHERE 
  artist = 'AC/DC';

+-------------------------+--------+
| song                    | artist |
|-------------------------+--------|
| Baby, Please Don't Go   | AC/DC  |
| Back In Black           | AC/DC  |
| Big Gun                 | AC/DC  |
| CAN'T STOP ROCK'N'ROLL  | AC/DC  |
| Girls Got Rhythm        | AC/DC  |
| Hard As A Rock          | AC/DC  |
| Have a Drink On Me      | AC/DC  |
| Hells Bells             | AC/DC  |
+-------------------------+--------+

SELECT song, artist 
FROM songlist 
WHERE 
  artist = 'AC/DC' 
  AND release_year < 1980;

+-----------------------------+--------+
| song                        | artist |
|-----------------------------+--------|
| Dirty Deeds Done Dirt Cheap | AC/DC  |
| Highway To Hell             | AC/DC  |
| It's A Long Way To The Top  | AC/DC  |
| Let There Be Rock           | AC/DC  |
| Night Prowler               | AC/DC  |
| T.N.T.                      | AC/DC  |
| Touch Too Much              | AC/DC  |
| Whole Lotta Rosie           | AC/DC  |
+-----------------------------+--------+

AND encore

Renvoie 3 lignes :

SELECT * 
FROM songlist 
WHERE 
  release_year = 1994 
  AND artist = 'Green Day';

Renvoie 1 ligne :

SELECT * 
FROM songlist 
WHERE 
  release_year = 1994 
  AND artist = 'Green Day' 
  AND song = 'Basket Case';

SELECT 
  song, 
  artist, 
  release_year 
FROM songlist 
WHERE release_year = 1994;

+----------------------+---------------------+--------------+
| song                 | artist              | release_year |
|----------------------+---------------------+---------------
| Black Hole Sun       | Soundgarden         | 1994         |
| Fell On Black Days   | Soundgarden         | 1994         |
| Spoonman             | Soundgarden         | 1994         |
| Big Empty            | Stone Temple Pilots | 1994         |
| Interstate Love Song | Stone Temple Pilots | 1994         |
| Vasoline             | Stone Temple Pilots | 1994         |
+----------------------+---------------------+--------------+

SELECT 
  song,
  artist,
  release_year
FROM songlist 
WHERE 
  release_year = 1994 
  OR release_year > 2000;

+----------------------+---------------------+--------------+
| song                 | artist              | release_year |
|----------------------+---------------------+---------------
| Doom And Gloom       | Rolling Stones      | 2012         |
| Remedy               | Seether             | 2005         |
| 45                   | Shinedown           | 2003         |
| Black Hole Sun       | Soundgarden         | 1994         |
| Fell On Black Days   | Soundgarden         | 1994         |
| Spoonman             | Soundgarden         | 1994         |
| It's Been Awhile     | Staind              | 2001         |
| Big Empty            | Stone Temple Pilots | 1994         |
| Interstate Love Song | Stone Temple Pilots | 1994         |
| Vasoline             | Stone Temple Pilots | 1994         |
+----------------------+---------------------+--------------+

SELECT song 
FROM songlist 
WHERE 
  artist = 'Green Day' 
  AND release_year = 1994;


+--------------------+
| song               |
|--------------------|
| Basket Case        |
| Longview           |
| When I Come Around |
+--------------------+

SELECT song 
FROM songlist 
WHERE 
  artist = 'Green Day' 
  AND release_year > 2000;


+----------------------------+
| song                       |
|----------------------------|
| Boulevard Of Broken Dreams |
| Holiday (Live)             |
| Holiday                    |
+----------------------------+

SELECT song 
FROM songlist 
WHERE 
  artist = 'Green Day' 
  AND release_year = 1994 
  OR release_year > 2000;

+-------------------------------------+
| song                                |
|-------------------------------------|
| Doom And Gloom                      |
| Remedy                              |
| 45                                  |
| It's Been Awhile                    |
| Goodbye Daughters of the Revolution |
| Gold On The Ceiling                 |
| Lonely Boy                          |
| Seven Nation Army                   |
| Get Together                        |
| Vertigo                             |
| When I'm Gone                       |
| ...                                 |
| ...                                 |
+-------------------------------------+

Qu'est-ce qui n'a pas fonctionné ?

SELECT * 
FROM songlist 
WHERE 
  artist = 'Green Day' 
  AND release_year = 1994 
  OR release_year > 2000;

SELECT * 
FROM songlist 
WHERE 
  artist = 'Green Day' 
  AND release_year = 1994;


SELECT * 
FROM songlist 
WHERE 
  release_year > 2000;

SELECT song 
FROM songlist 
WHERE 
  artist = 'Green Day' 
  AND (
    release_year = 1994 
    OR release_year > 2000
  );

Une autre manière de formuler la requête :

SELECT song 
FROM songlist 
WHERE 
  (
    artist = 'Green Day' 
    AND release_year = 1994
  ) 
  OR (
    artist = 'Green Day' 
    AND release_year > 2000
  );

+--------------------------------------+
| song                                 |
|--------------------------------------|
| Basket Case                          |
| Boulevard Of Broken Dreams           |
| Holiday (Live)                       |
| Holiday / Boulevard of Broken Dreams |
| Longview                             | 
| When I Come Around                   |
+--------------------------------------+

SELECT song, artist 
FROM songlist 
WHERE 
  artist IN ('Van Halen', 'ZZ Top') 
ORDER BY song;

+----------------------------------+-----------+
| song                             | artist    |
|----------------------------------+-----------|
| (Oh) Pretty Woman                | Van Halen |
| 1984/jump                        | Van Halen |
| A Fool for Your Stockings        | ZZ Top    |
| Ain't Talkin' 'bout Love         | Van Halen |
| And the Cradle Will Rock...      | Van Halen |
| Arrested For Driving While Blind | ZZ Top    |
| Atomic Punk                      | Van Halen |
+----------------------------------+-----------+

SELECT song, release_year 
FROM songlist 
WHERE 
  release_year IN (1985, 1991, 1992);

+------------------------+--------------+
| song                   | release_year |
|------------------------+--------------|
| Addicted to Love       | 1985         |
| Don't You              | 1985         |
| Come As You Are        | 1991         |
| Money for Nothing      | 1985         |
| Walk of Life           | 1985         |
| Man On the Moon        | 1992         |
| Breaking the Girl      | 1992         |
| You Belong to the City | 1985         |
| Enter Sandman          | 1991         |
| In Bloom               | 1991         |
+------------------------+--------------+

SELECT song 
FROM songlist 
WHERE song LIKE 'a%';


+------------------------+
| song                   |
|------------------------|
| Addicted to Love       |
| Ain't Too Proud to Beg |
+------------------------+

SELECT artist
FROM songlist 
WHERE artist LIKE 'f%';


+---------------+
| artist        |
|---------------|
| Faces         |
| Faith No More |
+---------------+

Passons à la pratique !

Introduction à SQL Server