WHERE the wild things are
Introduction to 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 again
- Returns 3 rows:
SELECT *
FROM songlist
WHERE
release_year = 1994
AND artist = 'Green Day';
- Returns 1 row:
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 |
| ... |
| ... |
+-------------------------------------+
What went wrong?
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;
OR
SELECT *
FROM songlist
WHERE
release_year > 2000;
SELECT song
FROM songlist
WHERE
artist = 'Green Day'
AND (
release_year = 1994
OR release_year > 2000
);
Another way of writing the query:
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 |
+---------------+
Let's practice!
Introduction to SQL Server
