Week3 Practice Quiz

​​​​​​​

 

1. How many albums does the artist Led Zeppelin have?

select count(*)
from (select * 
from artists
left join albums on artists.ArtistId = albums.ArtistID )
where name = 'Led Zeppelin'

解析：问led zeppelin专辑有多少张，首先count*算多少专辑，然后找出来led zepplin。

看上图得知，专辑的数量在albumid里来自表格albums，专辑艺术家的名字在表格artists的name里。用left join合并，再计算数量

这段到现在也没有消化，全部背下来吧

2. Create a list of album titles and the unit prices for the artist "Audioslave".

Select UnitPrice, Name

from tracks inner join albums ON tracks.Albumid = albums.Albumid

WHERE Artistid = (SELECT Artistid FROM artists WHERE name = 'Audioslave')

解析：需要一个list包含Audioslave这位艺术家的价钱和专辑名字，艺术家的专辑名字来自表格artists的name，价钱来自tracks的unitprice。

用inner join，其执行步骤是：

确认主表，使用from tableName，在这里我的主表是tracks

确认连接表，用inner join tableName，在这里连接的表格是artists

确认连接条件，使用on Condition，这里是tracks.Albumid = albums.Albumid来合并

最后可以加上where或者order by等语句作筛选，我需要Audioslave的专辑，就再写上WHERE Artistid = (SELECT Artistid FROM artists WHERE name = 'Audioslave')

括号里是subquery

3.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.FirstName,customers.LastName
,invoices.InvoiceId
from customers
left join invoices on  customers.CustomerId = invoices.CustomerId 
where Invoices.Invoiceid is null

解析：这里需要找顾客的姓名，还有哪些顾客没有invocie。

首先，在customers表格里找到顾客的firstname，lastname；再在invoices表格里找到invoceid

再作left join，两张表共同的列是customerid，用left join invoices on  customers.CustomerId = invoices.CustomerId 合并

最后注意，null不用“=”来判定，用where xx is null

4. Find the total price for each album. What is the total price for the album “Big Ones”?

SELECT SUM(UNITPRICE) AS TOTAL_PRICE
,tracks.albumid
,albums.albumid
,albums.title
FROM TRACKS
left join albums on albums.albumid = tracks.albumid
where albums.title = 'Big Ones'

解析：计算专辑big ones所有单曲的价钱，首先用sum而不是count来计算unitprce。再用left join的语法，依次列出tracks.albumid,albums.albumid,albums.title，再用albumid连接album和tracks两张表。最后用where找到专辑big ones。

5. How many records are created when you apply a Cartesian join to the invoice and invoice items table?

select invoices.invoiceid
from invoices
cross join  invoice_items

解析：直接套用笛卡尔合并的语法

select column...

from table1 cross join table2