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 Brooklyn
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
...