How many albums does the artist Led Zeppelin have?
SELECT count(albums.artistid) FROM albums WHERE albums.artistid IN ( SELECT artists.artistid FROM artists WHERE artists.name = 'Led Zeppelin' )
SELECT count(albums.artistid) FROM artists INNER JOIN albums ON albums.artistid = artists.artistid WHERE artists.name = 'Led Zeppelin'
Create a list of album titles and the unit prices for the artist “Audioslave”.
SELECT Albums.Title ,Tracks.UnitPrice FROM Albums INNER JOIN Tracks ON Albums.AlbumId = Tracks.AlbumId INNER JOIN Artists ON Artists.ArtistID = Albums.ArtistID WHERE Artists.Name = 'Audioslave'
SELECT a.Title, t.UnitPrice FROM Albums a, Tracks t WHERE t.AlbumId IN (SELECT a.AlbumId FROM Albums a WHERE a.ArtistID IN (SELECT ar.ArtistID FROM Artists ar WHERE ar.Name = 'Audioslave')) AND a.AlbumId = t.AlbumId
Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?
SELECT customers.customerid FROM customers LEFT JOIN invoices ON customers.customerid = invoices.customerid WHERE invoices.customerid IS NULL
Find the total price for each album. What is the total price for the album “Big Ones”?
SELECT sum(tracks.UnitPrice) FROM tracks INNER JOIN albums ON tracks.albumid = albums.albumid WHERE albums.Title = "Big Ones"
How many records are created when you apply a Cartesian join to the invoice and invoice items table?
SELECT count(*) FROM invoices CROSS JOIN invoice_items
Using a subquery, find the names of all the tracks for the album “Californication”.
SELECT name FROM tracks WHERE albumid IN ( SELECT albumid FROM albums WHERE Title = 'Californication' )
Find the total number of invoices for each customer along with the customer’s full name, city and email.
SELECT invoices.invoiceid ,customers.FirstName ,customers.LastName ,customers.Email FROM customers INNER JOIN invoices WHERE customers.customerid = invoices.customerid GROUP BY customers.Customerid
Retrieve the track name, album, artistID, and trackID for all the albums.
SELECT tracks.Name ,albums.Title ,albums.artistID ,tracks.trackid FROM albums LEFT JOIN tracks WHERE albums.Albumid = tracks.Albumid
Retrieve a list with the managers last name, and the last name of the employees who report to him or her.
SELECT M.LastName AS Manager ,E.LastName AS Employee FROM Employees E INNER JOIN Employees M ON E.ReportsTo = M.EmployeeID
Find the name and ID of the artists who do not have albums.
SELECT artists.Name ,artists.Artistid ,Albums.Title FROM artists LEFT JOIN albums ON artists.Artistid = albums.Artistid WHERE Albums.Title IS NULL
Use a UNION to create a list of all the employee’s and customer’s first names and last names ordered by the last name in descending order.
SELECT FirstName ,LastName FROM Employees UNION SELECT FirstName ,LastName FROM Customers ORDER BY LastName DESC
See if there are any customers who have a different city listed in their billing city versus their customer city.
SELECT C.FirstName ,C.LastName ,C.City AS CustomerCity ,I.BillingCity FROM Customers C INNER JOIN Invoices I ON C.CustomerId = I.CustomerId WHERE CustomerCity != BillingCity
Pull a list of customer ids with the customer’s full name, and address, along with combining their city and country together. Be sure to make a space in between these two and make it UPPER CASE. (e.g. LOS ANGELES USA)
SELECT CustomerId, FirstName || " " || LastName AS FullName, Address, UPPER(City || " " || Country) AS CityCountry FROM Customers
Create a new employee user id by combining the first 4 letters of the employee’s first name with the first 2 letters of the employee’s last name. Make the new field lower case and pull each individual step to show your work.
SELECT FirstName ,LastName ,LOWER(SUBSTR(FirstName, 1, 4)) AS FirstName_first_4_letters ,LOWER(SUBSTR(LastName, 1, 2)) AS LastName_first_2_letters ,LOWER(SUBSTR(FirstName, 1, 4)) || LOWER(SUBSTR(LastName, 1, 2)) AS userId FROM Employees
Show a list of employees who have worked for the company for 15 or more years using the current date function. Sort by lastname ascending.
SELECT FirstName ,LastName ,HireDate ,(STRFTIME('%Y', 'now') - STRFTIME('%Y', HireDate)) - (STRFTIME('%m-%d', 'now') < STRFTIME('%m-%d', HireDate)) AS YearsWorked FROM Employees WHERE YearsWorked >= 15 ORDER BY LastName ASC
Profiling the Customers table, answer the following question. Are there any columns with null values? Indicate any below. (Fax ….. )
SELECT COUNT(*) FROM Customers WHERE Fax IS NULL
Find the cities with the most customers and rank in descending order.
SELECT City ,COUNT(*) FROM Customers GROUP BY City ORDER BY COUNT(*) DESC
Create a new customer invoice id by combining a customer’s invoice id with their first and last name while ordering your query in the following order: firstname, lastname, and invoiceID. (Select all of the correct “AstridGruber” entries that are returned in your results below. Select all that apply.)
SELECT C.FirstName, C.LastName, I.InvoiceId, C.FirstName || C.LastName || I.InvoiceID AS NewId FROM Customers C INNER JOIN Invoices I ON C.CustomerId = I.CustomerID GROUP BY NewId HAVING NewId LIKE 'AstridGruber%' ORDER by NewId