Skip to content

Latest commit

 

History

History
447 lines (393 loc) · 7.67 KB

sql-exercises.md

File metadata and controls

447 lines (393 loc) · 7.67 KB

Round 1

In these exercises we focus solely on writing SQL. Use the online SQLite viewer, upload our sample database file and start writing those queries!

Select

1. Show all artists 
SELECT
    *
FROM
    Artist;
2. Show all artists' names 
SELECT
    Name
FROM
    Artist;
3. Show all tracks 
SELECT
    *
FROM
    Track;
4. Show all tracks' name and price 
SELECT
    Name,
    UnitPrice
FROM
    Track;

Count

5. Show the number of artists 
SELECT count(*)
FROM Artist;

Order By

6. Show all tracks' name and duration, sorted from longest to shortest duration 
SELECT
    Name,
    Milliseconds
FROM
    Track
ORDER BY
    Milliseconds DESC;
7. Show all tracks' name and duration in minutes, sorted from longest to shortest duration 
SELECT
    Name,
    Milliseconds / 1000 / 60 AS Minutes
FROM
    Track
ORDER BY
    Milliseconds DESC;
8. Show all invoices' id and total, sorted from lowest to highest total 
SELECT
    InvoiceId,
    Total
FROM
    Invoice
ORDER BY
    Total;

Select Distinct

9. Show all unique track composers' names 
SELECT DISTINCT
    Composer
FROM
    Track;
10. Show all unique unit prices of tracks 
SELECT DISTINCT
    UnitPrice
FROM
    Track;
11. Show the number of unique track composers 
SELECT
    count(DISTINCT Composer)
FROM
    Track;

Select Limit

12. Show the id and total of the ten highest totalling invoices 
SELECT
    InvoiceId,
    Total
FROM
    Invoice
ORDER BY
    Total DESC
LIMIT 10;

Where

13. Show all tracks where Philip Glass is the composer 
SELECT
    *
FROM
    Track
WHERE Composer = 'Philip Glass'
14. Show all customers' first name and country who live not in USA 
SELECT
    FirstName,
    Country
FROM
    Customer
WHERE
    Country != 'USA';

-- OR

SELECT
    FirstName,
    Country
FROM
    Customer
WHERE
    Country IS NOT 'USA';
15. Show the customer's first name for whom we do not have the phone number 
SELECT
    FirstName
FROM
    Customer
WHERE
    Phone IS NULL;
16. Show all employees' first name, last name and title whose title is IT Staff 
SELECT
    FirstName,
    LastName,
    Title
FROM
    Employee
WHERE
    Title = 'IT Staff';
17. Show all invoices' id and total with a total bigger than 20 
SELECT
    InvoiceId,
    Total
FROM
    Invoice
WHERE
    Total > 20;
18. Show the number of tracks where the unit price is not 0.99 
SELECT count(*)
FROM
    Track
where UnitPrice != '0.99';
19. Show all tracks' name and size in megabytes, of which the size is smaller than 1 megabyte 
SELECT
    Name,
    cast(Bytes AS REAL) / 1000 / 1000 AS Megabytes
FROM
    Track
WHERE
    Bytes / 1000 / 1000 <= 1;

---

-- Using the column alias we avoid repeating ourselves
SELECT
    Name,
    cast(Bytes AS REAL) / 1000 / 1000 AS Megabytes
FROM
    Track
WHERE
    Megabytes <= 1;

Where Like

20. Show all employees' first name, last name and title whose title mentions IT 
SELECT
    FirstName,
    LastName,
    Title
FROM
    Employee
WHERE
    Title LIKE '%IT%';
21. Show all customers' full name and email, whose email address is of the gmail.com domain 
-- The || operator is "concatenate" - it joins together the two strings of its operands.
SELECT
    FirstName || ' ' || LastName as FullName,
    Email
FROM
    Customer
WHERE
    Email LIKE '%gmail.com';

Where And

22. Show all employees' first name, last name, title and city whose title mentions IT and that live in the city of Calgary 
SELECT
    FirstName,
    LastName,
    Title,
    City
FROM
    Employee
WHERE
    Title LIKE '%IT%'
    AND City = 'Calgary';

Where Or

23. Show all employees' first name, last name, title and city whose title mentions IT and that live in the city of Calgary or Lethbridge 
SELECT
    FirstName,
    LastName,
    Title,
    City
FROM
    Employee
WHERE
    Title LIKE '%IT%'
    AND(City = 'Calgary'
        OR City = 'Lethbridge');

Where Between

24. Show all invoices' id and invoice date that got invoiced in the first quarter of 2009 
SELECT
    InvoiceId,
    InvoiceDate
FROM
    Invoice
WHERE
    InvoiceDate BETWEEN '2009-01-01' AND '2009-03-31';

Where In

25. Show the number of customers who live in Argentina, Brazil, Canada, Chile and USA 
SELECT
    count(*)
FROM
    Customer
WHERE
    Country IN('Argentina', 'Brazil', 'Canada', 'Chile', 'USA');
26. Show the number of customers who do not live in Argentina, Brazil, Canada, Chile and USA 
SELECT
    count(*)
FROM
    Customer
WHERE
    Country NOT IN('Argentina', 'Brazil', 'Canada', 'Chile', 'USA');

Min, Sum, Max, Avg

27. Show the minimum duration of all tracks 
SELECT
    min(Milliseconds) AS MinimumDuration
FROM
    Track;
28. Show the sum of all invoice totals 
SELECT
    sum(Total)
FROM
    Invoice;
29. Show the maximum size of all tracks in megabytes 
SELECT
    max(Bytes / 1000 / 1000) AS MaximumInMegabytes
FROM
    Track;
30. Show the average track duration in minutes 
SELECT
    avg(Milliseconds / 1000 / 60) AS AverageInMinutes
FROM
    Track;
31. Show the rounded average invoice total (rounded to 2 digits to the right of the decimal point) 
SELECT
    round(avg(Total), 2) AS AverageTotal
FROM
    Invoice;

Group By

32. Show the number of tracks per playlist 
SELECT
    PlaylistId,
    count(*)
FROM
    PlaylistTrack
GROUP BY
    PlaylistId;
33. Show the number of invoice lines per invoice 
SELECT
    InvoiceId,
    count(*)
FROM
    InvoiceLine
GROUP BY
    InvoiceId;
34. Show the sum of all line items' total price per invoice 
SELECT
    InvoiceId,
    sum(UnitPrice * Quantity) AS AverageLineItemTotal
FROM
    InvoiceLine
GROUP BY
    InvoiceId;
35. Show the average line item total per invoice 
SELECT
    InvoiceId,
    avg(UnitPrice * Quantity) AS AverageLineItemTotal
FROM
    InvoiceLine
GROUP BY
    InvoiceId;
36. Show the rounded average duration in minutes of tracks per composer 
SELECT
    Composer,
    round(avg(Milliseconds / 1000 / 60), 2) AS AverageDurationInMinutes
FROM
    Track
WHERE
    Composer IS NOT NULL
GROUP BY
    Composer;