The SQL Advanced course assumes that attendees have mastered the content covered in the foundation andintermediate courses. Here are a few questions. You should only join the advanced course if you are confident in your understanding of these concepts. You may be asked to answer these at the start of the course.
You should also complete the intermediate pre-course questionnaire if you have not already done so.
Can you explain exactly and precisely what this SQL statement does?
SELECT
ar.Name ArtistName
, COUNT(*) NumberOfTracks
, SUM(il.UnitPrice) Price
FROM
InvoiceLine il
JOIN Track t ON
il.TrackId = t.TrackId
JOIN Album ab ON
ab.AlbumId = t.AlbumId
JOIN Artist ar ON
ar.ArtistId = ab.ArtistId
GROUP BY
ar.Name
HAVING
COUNT(*) > 10
ORDER BY
NumberOfTracks DESC;
Can you explain exactly and precisely what this SQL statement does?
WITH streetCTE
AS (
SELECT
pp.TransactionDate
, pp.PAON
, pp.SAON
, pp.Price
, pp.Street
, (SELECT AVG(cast(p1.Price AS BIGINT))
FROM PricePaidSW12 p1
WHERE p1.street = pp.Street
) AS AveragePriceInStreet
FROM PricePaidSW12 pp)
SELECT *
FROM streetCTE
WHERE streetCTE.Price > streetCTE.AveragePriceInStreet
ORDER BY streetCTE.Price;
Can you write a SQL statement based on the instructions below? Assume that the database contains a FootballMatch table with columns: Date, Time, HomeTeam, AwayTeam, FullTime Result). Here is a full description on this dataset.
On which dates, between the date of the earliest and latest match, were no matches played?