SQL 作業

2020-10-26

SQL Join

SQL case

SQL view

SQL view-drop

SQL 作業

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