DataCamp课程：Introduction to SQL Server
Introduction to SQL Server
1. SELECTion Box
</> Simple selections
SELECT the country column FROM the eurovision table.
SELECT country
FROM eurovision;
country
Israel
France
Sweden
Croatia
...
Amend your query to return the points column instead of the country column.
SELECT
points
FROM
eurovision;
points
53
107
33
45
...
Use TOP to change the existing query so that only the first 50 rows are returned.
SELECT
TOP (50) points
FROM
eurovision;
points
53
107
33
45
...
Return a list of unique countries using DISTINCT. Give the results an alias of unique_country.
SELECT
DISTINCT country AS unique_country
FROM
eurovision;
unique_country
Albania
Andorra
Armenia
Austria
...
</> More selections
SELECT the country and event_year columns from the eurovision table.
SELECT
country,
event_year
FROM
eurovision;
country event_year
Israel 2009
France 2009
Sweden 2009
Croatia 2009
...
Use a shortcut to amend the current query, returning ALL rows from ALL columns in the table.
SELECT
*
FROM
eurovision;
euro_id event_year country gender group_type place points host_country host_region is_final sf_number song_in_english
1 2009 Israel Female Group 16 53 Away Away 1 null 1
2 2009 France Female Solo 8 107 Away Away 1 null 0
3 2009 Sweden Female Solo 21 33 Away Away 1 null 1
4 2009 Croatia Both Group 18 45 Away Away 1 null 0
...
Return all columns, but only include the top half of the table - in other words, return 50 percent of the rows.
SELECT
TOP (50) PERCENT *
FROM
eurovision;
euro_id event_year country gender group_type place points host_country host_region is_final sf_number song_in_english
1 2009 Israel Female Group 16 53 Away Away 1 null 1
2 2009 France Female Solo 8 107 Away Away 1 null 0
3 2009 Sweden Female Solo 21 33 Away Away 1 null 1
4 2009 Croatia Both Group 18 45 Away Away 1 null 0
...
</> Order by
Select description and event_date from grid. Your query should return the first 5 rows, ordered by event_date.
SELECT
TOP (5) description,
event_date
FROM
grid
ORDER BY
event_date;
description event_date
Electrical Fault at Generator 2011-01-11
Winter Storm 2011-01-12
Firm System Load Shed 2011-01-13
Vandalism 2011-01-18
Vandalism 2011-01-23
Modify your code based on the comments provided on the right.
SELECT
TOP (20) description,
nerc_region,
event_date
FROM
grid
ORDER BY
nerc_region,
affected_customers,
event_date desc;
description nerc_region event_date
Suspected Physical Attack ERCOT 2014-06-12
Fuel Supply Emergency Coal ERCOT 2014-06-06
Physical Attack Vandalism ERCOT 2014-06-03
Suspected Physical Attack FRCC 2013-03-18
...
</> Where
Select the description and event_year columns.
Return rows WHERE the description is ‘Vandalism’.
SELECT
description,
event_year
FROM
grid
WHERE
description = 'Vandalism';
description event_year
Vandalism 2014
Vandalism 2013
Vandalism 2013
Vandalism 2013
...
</> Where again
Select the nerc_region and demand_loss_mw columns, limiting the results to those where affected_customers is greater than or equal to 500000.
SELECT
nerc_region,
demand_loss_mw
FROM
grid
WHERE
affected_customers >= 500000;
nerc_region demand_loss_mw
WECC 3900
WECC 3300
WECC 9750
RFC null
...
Update your code to select description and affected_customers, returning records where the event_date was the 22nd December, 2013.
SELECT
description,
affected_customers
FROM
grid
WHERE
event_date = '2013-12-22';
description affected_customers
Severe Weather IceSnow 59000
Severe Weather IceSnow 50000
Severe Weather IceSnow 140735
Limit the results to those where the affected_customers is BETWEEN 50000 and 150000, and order in descending order of event_date.
SELECT
description,
affected_customers,
event_date
FROM
grid
WHERE
affected_customers BETWEEN 50000 AND 150000
description affected_customers event_date
Severe Weather Thunderstorms 127000 2014-06-30
Severe Weather Thunderstorms 120000 2014-06-30
Severe Weather Thunderstorms 138802 2014-06-18
Severe Weather Thunderstorms 55951 2014-06-15
...
</> Working with NULL values
Use a shortcut to select all columns from grid. Then filter the results to only include rows where demand_loss_mw is unknown or missing.
SELECT
*
FROM
grid
WHERE
demand_loss_mw IS NULL;
grid_id description event_year event_date restore_date nerc_region demand_loss_mw affected_customers
1 Severe Weather Thunderstorms 2014 2014-06-30 2014-07-01 RFC null 127000
3 Fuel Supply Emergency Coal 2014 2014-06-27 null MRO null null
4 Physical Attack Vandalism 2014 2014-06-24 2014-06-24 SERC null null
...
Adapt your code to return rows where demand_loss_mw is not unknown or missing.
SELECT
*
FROM
grid
WHERE
demand_loss_mw IS NOT NULL;
grid_id description event_year event_date restore_date nerc_region demand_loss_mw affected_customers
2 Severe Weather Thunderstorms 2014 2014-06-30 2014-07-01 MRO 424 120000
14 Severe Weather Thunderstorms 2014 2014-06-07 2014-06-08 SERC 217 65000
16 Severe Weather Thunderstorms 2014 2014-06-05 2014-06-07 SERC 494 38500
...
</> Exploring classic rock songs
Retrieve the song, artist, and release_year columns from the songlist table.
SELECT
song,
artist,
release_year
FROM
songlist;
song artist release_year
Keep On Loving You REO Speedwagon 1980
Keep Pushin 1977 REO Speedwagon null
Like You Do REO Speedwagon null
...
Make sure there are no NULL values in the release_year column.
SELECT
song,
artist,
release_year
FROM
songlist
WHERE
release_year IS NOT NULL;
song artist release_year
Keep On Loving You REO Speedwagon 1980
Take It on the Run REO Speedwagon 1981
Jessies Girl Rick Springfield 1981
...
Order the results by artist and release_year.
SELECT
song,
artist,
release_year
FROM
songlist
WHERE
release_year IS NOT NULL
ORDER BY
artist,
release_year;
song artist release_year
Rockin Into the Night .38 Special 1980
Hold On Loosely .38 Special 1981
Caught Up in You .38 Special 1982
...
</> Exploring classic rock songs - AND/OR
Extend the WHERE clause so that the results are those with a release_year greater than or equal to 1980 and less than or equal to 1990.
SELECT
song,
artist,
release_year
FROM
songlist
WHERE
release_year >= 1980
AND release_year <= 1990
ORDER BY
artist,
release_year;
song artist release_year
Rockin Into the Night .38 Special 1980
Hold On Loosely .38 Special 1981
Caught Up in You .38 Special 1982
...
</> Using parentheses in your queries
Select all artists beginning with B who released tracks in 1986, but also retrieve any records where the release_year is greater than 1990.
SELECT
artist,
release_year,
song
FROM
songlist
WHERE
(
artist LIKE 'B%'
AND release_year = 1986
)
OR release_year > 1990
ORDER BY
release_year,
artist,
song;
artist release_year song
Beastie Boys 1986 (You Gotta) Fight for Your Right (To Party)
Beastie Boys 1986 No Sleep Till *lyn
Bon Jovi 1986 Livin On A Prayer
...
2. Groups, strings, and counting things
</> Summing
Obtain a grand total of the demand_loss_mw column by using the SUM function, and alias the result as MRO_demand_loss.
Only retrieve rows WHERE demand_loss_mw is not NULL and nerc_region is ‘MRO’.
SELECT
SUM(demand_loss_mw) AS MRO_demand_loss
FROM
grid
WHERE
demand_loss_mw IS NOT NULL
AND nerc_region = 'MRO';
MRO_demand_loss
916
</> Counting
Return the COUNT of the grid_id column, aliasing the result as grid_total.
SELECT
COUNT(grid_id) AS grid_total
FROM
grid;
grid_total
806
Make the count more meaningful by restricting it to records where the nerc_region is ‘RFC’. Name the result RFC_count.
SELECT
COUNT(grid_id) AS RFC_count
FROM
grid
WHERE
nerc_region = 'RFC';
RFC_count
246
</> MIN, MAX and AVG
Find the minimum value from the affected_customers column, but only for rows where demand_loss_mw has a value. Name the result min_affected_customers.
SELECT
MIN(affected_customers) AS min_affected_customers
FROM
grid
WHERE
demand_loss_mw IS NOT NULL;
min_affected_customers
0
Amend the query to return the maximum value from the same column, this time aliasing as max_affected_customers.
SELECT
MAX(affected_customers) AS max_affected_customers
FROM
grid
WHERE
demand_loss_mw IS NOT NULL;
max_affected_customers
4645572
Return the average value from the affected_customers column, this time aliasing as avg_affected_customers.
SELECT
AVG(affected_customers) AS avg_affected_customers
FROM
grid
WHERE
demand_loss_mw IS NOT NULL;
avg_affected_customers
128240
</> LEN’gth of a string
Retrieve the length of the description column, returning the results as description_length.
SELECT
LEN (description) AS description_length
FROM
grid;
description_length
29
29
27
...
</> Left and right
Retrieve the first 25 characters from the description column in the grid table. Name the results first_25_left.
SELECT
LEFT(description, 25) AS first_25_left
FROM
grid;
first_25_left
Severe Weather Thunderst
Severe Weather Thunderst
Fuel Supply Emergency Co
...
Amend the query to retrieve the last 25 characters from the description. Name the results last_25_right.
SELECT
RIGHT(description, 25) AS last_25_right
FROM
grid;
last_25_right
re Weather Thunderstorms
re Weather Thunderstorms
el Supply Emergency Coal
...
</> Stuck in the middle with you
You can use CHARINDEX to find a specific character or pattern within a column. Edit the query to return the CHARINDEX of the string ‘Weather’ whenever it appears within the description column.
SELECT
description,
CHARINDEX('Weather', description)
FROM
grid
WHERE description LIKE '%Weather%';
description
Severe Weather Thunderstorms 8
Severe Weather Thunderstorms 8
Severe Weather Thunderstorms 8
Severe Weather Thunderstorms 8
...
We now know where ‘Weather’ begins in the description column. But where does it end? We could manually count the number of characters, but, for longer strings, this is more work, especially when we can also find the length with LEN.
SELECT
description,
CHARINDEX('Weather', description) AS start_of_string,
LEN('Weather') AS length_of_string
FROM
grid
WHERE description LIKE '%Weather%';
description start_of_string length_of_string
Severe Weather Thunderstorms 8 7
Severe Weather Thunderstorms 8 7
Severe Weather Thunderstorms 8 7
...
Now we use SUBSTRING to return everything after Weather for the first ten rows. The start index here is 15, because the CHARINDEX for each row is 8, and the LEN of Weather is 7.
SELECT TOP (10)
description,
CHARINDEX('Weather', description) AS start_of_string,
LEN ('Weather') AS length_of_string,
SUBSTRING(
description,
15,
LEN(description)
) AS additional_description
FROM
grid
WHERE description LIKE '%Weather%';
description start_of_string length_of_string additional_description
Severe Weather Thunderstorms 8 7 Thunderstorms
Severe Weather Thunderstorms 8 7 Thunderstorms
Severe Weather Thunderstorms 8 7 Thunderstorms
...
</> GROUP BY
Select nerc_region and the sum of demand_loss_mw for each region.
Exclude values where demand_loss_mw is NULL.
Group the results by nerc_region.
Arrange in descending order of demand_loss.
SELECT
nerc_region,
SUM(demand_loss_mw) AS demand_loss
FROM
grid
WHERE
demand_loss_mw IS NOT NULL
GROUP BY
nerc_region
ORDER BY
demand_loss DESC;
nerc_region demand_loss
WECC 72019
SERC 51737
RFC 31037
...
</> Having
Modify the provided query to remove the WHERE clause.
Replace it with a HAVING clause so that only results with a total demand_loss_mw of greater than 10000 are returned.
SELECT
nerc_region,
SUM (demand_loss_mw) AS demand_loss
FROM
grid
GROUP BY
nerc_region
HAVING
SUM(demand_loss_mw) > 10000
ORDER BY
demand_loss DESC;
nerc_region demand_loss
WECC 72019
SERC 51737
RFC 31037
</> Grouping together
Use MIN and MAX to retrieve the minimum and maximum values for the place and points columns respectively.
SELECT
MIN(place) AS min_place,
MAX(place) AS max_place,
MIN(points) AS min_points,
MAX(points) AS max_points
FROM
eurovision;
min_place max_place min_points max_points
1 28 0 387
Let’s obtain more insight from our results by adding a GROUP BY clause. Group the results by country.
SELECT
MIN(place) AS min_place,
MAX(place) AS max_place,
MIN(points) AS min_points,
MAX(points) AS max_points
FROM
eurovision
GROUP BY
country;
min_place max_place min_points max_points
2 17 43 167
12 23 8 80
2 12 54 198
...
The previous query results did not identify the country. Let’s amend the query, returning the count of entries per country and the country column. Complete the aggregate section by finding the average place for each country.
SELECT
COUNT(country) AS country_count,
country,
AVG(place) AS average_place,
AVG(points) AS avg_points,
MIN(points) AS min_points,
MAX(points) AS max_points
FROM
eurovision
GROUP BY
country;
country_count country average_place avg_points min_points max_points
14 Albania 10 81 43 167
6 Andorra 17 26 8 80
10 Armenia 6 122 54 198
...
Finally, our results are skewed by countries who only have one entry. Apply a filter so we only return rows where the country_count is greater than 5. Then arrange by avg_place in ascending order, and avg_points in descending order.
SELECT
country,
COUNT (country) AS country_count,
AVG (place) AS avg_place,
AVG (points) AS avg_points,
MIN (points) AS min_points,
MAX (points) AS max_points
FROM
eurovision
GROUP BY
country
HAVING
COUNT(country) > 5
ORDER BY
avg_place,
avg_points DESC;
country country_count avg_place avg_points min_points max_points
Azerbaijan 9 3 150 96 221
Serbia 10 6 145 60 298
Russia 18 6 134 37 266
...
3. Joining tables
</> Inner Joins - a perfect match
Perform an inner join between album and track using the album_id column.
SELECT
track_id,
name AS track_name,
title AS album_title
FROM track
INNER JOIN album on album.album_id = track.album_id;
track_id track_name album_title
1 For Those About To Rock (We Salute You) For Those About To Rock We Salute You
2 Balls to the Wall Balls to the Wall
3 Fast As a Shark Restless and Wild
4 Restless and Wild Restless and Wild
...
</> Inner Joins (II)
Select the album_id and title columns from album (the main source table name).
Select the name column from artist and alias it as artist.
Identify a common column between the album and artist tables and perform an inner join.
SELECT
album_id,
title,
name AS artist
FROM album
INNER JOIN artist on album.artist_id = artist.artist_id;
album_id title artist
1 For Those About To Rock We Salute You AC/DC
2 Balls to the Wall Accept
3 Restless and Wild Accept
...
</> Inner Join (III) - Join 3 tables
Qualify the name column by specifying the correct table prefix in both cases.
Complete both INNER JOIN clauses to join album with track, and artist with album.
SELECT track_id,
track.name AS track_name,
title as album_title,
artist.name AS artist_name
FROM track
INNER JOIN album on album.album_id = track.album_id
INNER JOIN artist on artist.artist_id = album.artist_id;
track_id track_name album_title artist_name
1 For Those About To Rock (We Salute You) For Those About To Rock We Salute You AC/DC
2 Balls to the Wall Balls to the Wall Accept
3 Fast As a Shark Restless and Wild Accept
...
</> LEFT join
Complete the LEFT JOIN, returning all rows from the specified columns from invoiceline and any matches from invoice.
SELECT
invoiceline_id,
unit_price,
quantity,
billing_state
FROM invoiceline
LEFT JOIN invoice
ON invoiceline.invoice_id = invoice.invoice_id;
invoiceline_id unit_price quantity billing_state
1 0.99 1 NULL
2 0.99 1 NULL
3 0.99 1 NULL
...
</> RIGHT JOIN
SELECT the fully qualified column names album_id from album and name from artist. Then, join the tables so that only matching rows are returned (non-matches should be discarded).
SELECT
album_id,
title,
album.artist_id,
name as artist
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id
WHERE album.album_id IN (213,214)
album_id title artist_id artist
213 Pure Cult: The Best Of The Cult (For Rockers Ravers Lovers & Sinners) UK 139 The Cult
214 The Doors 140 The Doors
To complete the query, join the album table to the track table using the relevant fully qualified album_id column. The album table is on the left-hand side of the join, and the additional join should return all matches or NULLs.
SELECT
album.album_id,
title,
album.artist_id,
artist.name as artist
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id
LEFT JOIN track on album.album_id = track.album_id
WHERE album.album_id IN (213,214)
album_id title artist_id artist
213 Pure Cult: The Best Of The Cult (For Rockers Ravers Lovers & Sinners) UK 139 The Cult
213 Pure Cult: The Best Of The Cult (For Rockers Ravers Lovers & Sinners) UK 139 The Cult
213 Pure Cult: The Best Of The Cult (For Rockers Ravers Lovers & Sinners) UK 139 The Cult
...
</> UNION ALL Check
Which of the following options correctly describes what happens when 2 queries are combined with UNION ALL?
- All rows from the first query are returned, along with any non-duplicated rows from the second query.
- Only rows that exist in both queries are returned, with duplicate rows discarded.
- All rows from both queries are returned, including duplicates.
- All rows from the second query are returned, along with non-duplicated rows from the first query.
</> Join the UNION
Make the first selection from the album table. Then join the results by providing the relevant keyword and selecting from the artist table.
SELECT
album_id AS ID,
title AS description,
'Album' AS Source
-- Complete the FROM statement
FROM album
UNION
SELECT
artist_id AS ID,
name AS description,
'Artist' AS Source
FROM artist;
ID description Source
1 AC/DC Artist
1 For Those About To Rock We Salute You Album
2 Accept Artist
...
4. You’ve got the power
</> CRUD operations
When we talk about ‘CRUD’ operations on the records of a database, what do we mean - what do those letters stand for?
- Create, Review, Update, Destroy.
- Create, Read, Update, Delete.
- Create, Record, Update, Destroy.
- Create, Read, Upsert, Delete.
</> Create tables
Create a table named ‘results’ with 3 VARCHAR columns called track, artist, and album, with lengths 200, 120, and 160, respectively.
CREATE TABLE results (
track VARCHAR(200),
artist VARCHAR(120),
album VARCHAR(160),
);
Create one integer column called track_length_mins.
CREATE TABLE results (
track VARCHAR(200),
artist VARCHAR(120),
album VARCHAR(160),
track_length_mins INT,
);
SELECT all the columns from your new table. No rows will be returned, but you can confirm that the table has been created.
CREATE TABLE results (
track VARCHAR(200),
artist VARCHAR(120),
album VARCHAR(160),
track_length_mins INT,
);
SELECT
track,
artist,
album,
track_length_mins
FROM
results;
</> Insert
Create a table called tracks with 2 VARCHAR columns named track and album, and one integer column named track_length_mins. Then, select all columns from the new table using the * notation.
CREATE TABLE tracks(
track VARCHAR(200),
album VARCHAR(160),
track_length_mins INT
);
SELECT
*
FROM
tracks;
Insert the track ‘Basket Case’, from the album ‘Dookie’, with a track length of 3, into the appropriate columns.
CREATE TABLE tracks(
track VARCHAR(200),
album VARCHAR(160),
track_length_mins INT
);
INSERT INTO tracks
(track, album, track_length_mins)
VALUES
('Basket Case', 'Dookie', 3);
SELECT
*
FROM
tracks;
track album track_length_mins
Basket Case Dookie 3
</> Update
Select the title column from the album table where the album_id is 213.
-- Select the album
SELECT
title
FROM
album
WHERE
album_id = 213;
title
Pure Cult: The Best Of The Cult (For Rockers Ravers Lovers & Sinners) UK
That’s a very long album title, isn’t it? Use an UPDATE statement to modify the title to ‘Pure Cult: The Best Of The Cult’.
-- Select the album
SELECT
title
FROM
album
WHERE
album_id = 213;
-- UPDATE the album table
UPDATE
album
SET
title = 'Pure Cult: The Best Of The Cult'
WHERE album_id = 213;
Hit ‘Submit Answer’ to see whether or not the album title was shortened!
-- Select the album
SELECT
title
FROM
album
WHERE
album_id = 213;
-- UPDATE the title of the album
UPDATE
album
SET
title = 'Pure Cult: The Best Of The Cult'
WHERE
album_id = 213;
-- Run the query again
SELECT
title
FROM
album;
title
For Those About To Rock We Salute You
Balls to the Wall
Restless and Wild
...
</> Delete
Hit ‘Submit Answer’ to run the query and view the existing data.
SELECT
*
FROM
album;
album_id title artist_id
1 For Those About To Rock We Salute You 1
2 Balls to the Wall 2
3 Restless and Wild 2
...
DELETE the record from album where album_id is 1 and then hit ‘Submit Answer’.
-- Run the query
SELECT
*
FROM
album
-- DELETE the record
DELETE FROM
album
WHERE
album_id = 1
-- Run the query again
SELECT
*
FROM
album;
album_id title artist_id
2 Balls to the Wall 2
3 Restless and Wild 2
4 Let There Be Rock 1
...
</> DECLARE and SET a variable
DECLARE the variable @region, which has a data type of VARCHAR and length of 10.
DECLARE @region VARCHAR(10)
SET your newly defined variable to ‘RFC’.
DECLARE @region VARCHAR(10)
SET @region = 'RFC'
Hit ‘Submit Answer’ to see the results!
DECLARE @region VARCHAR(10)
SET @region = 'RFC'
SELECT description,
nerc_region,
demand_loss_mw,
affected_customers
FROM grid
WHERE nerc_region = @region;
description nerc_region demand_loss_mw affected_customers
Severe Weather Thunderstorms RFC null 127000
Severe Weather Thunderstorms RFC null 138802
Severe Weather Thunderstorms RFC null 66383
...
</> Declare multiple variables
Declare a new variable called @start of type DATE.
DECLARE @start DATE
SET @start = '2014-01-24'
Declare a new variable called @stop of type DATE.
DECLARE @start DATE
DECLARE @stop DATE
SET @start = '2014-01-24'
SET @stop = '2014-07-02'
Declare a new variable called @affected of type INT.
DECLARE @start DATE
DECLARE @stop DATE
DECLARE @affected INT
SET @start = '2014-01-24'
SET @stop = '2014-07-02'
SET @affected = 5000
Retrieve all rows where event_date is BETWEEN @start and @stop and affected_customers is greater than or equal to @affected.
DECLARE @start DATE
DECLARE @stop DATE
DECLARE @affected INT;
SET @start = '2014-01-24'
SET @stop = '2014-07-02'
SET @affected = 5000 ;
SELECT
description,
nerc_region,
demand_loss_mw,
affected_customers
FROM
grid
WHERE event_date BETWEEN @start AND @stop
AND affected_customers >= @affected;
description nerc_region demand_loss_mw affected_customers
Severe Weather Thunderstorms RFC null 127000
Severe Weather Thunderstorms MRO 424 120000
Severe Weather Thunderstorms RFC null 138802
...
</> Ultimate Power
Create a temporary table called maxtracks. Make sure the table name starts with #.
Join album to artist using artist_id, and track to album using album_id.
Run the final SELECT statement to retrieve all the columns from your new table.
SELECT album.title AS album_title,
artist.name as artist,
MAX(track.milliseconds / (1000 * 60) % 60 ) AS max_track_length_mins
INTO #maxtracks
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id
JOIN track ON album.album_id = track.album_id
GROUP BY artist.artist_id, album.title, artist.name,album.album_id
SELECT album_title, artist, max_track_length_mins
FROM #maxtracks
ORDER BY max_track_length_mins DESC, artist;
album_title artist max_track_length_mins
Battlestar Galactica (Classic) Season 1 Battlestar Galactica (Classic) 49
Battlestar Galactica Season 3 Battlestar Galactica 47
Lost Season 2 Lost 47
...