sqlite ucdavis quiz using ER diagram (https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/UAPENoOVEei4RQ5L9j9nDA_5042a1f0839511e8beb2b5b4ae9fa29a_ER-Diagram.png?expiry=1532131200000&hmac=MPlvku13AThiVQ5zkHL7tcQwp7ftKeWMgVj4r3zB9Z4)

ucdavis.sql

/* How many albums does the artist Led Zepplin have? */
SELECT COUNT(Albumid) AS totalAlbums 
FROM albums
WHERE Artistid  = (SELECT Artistid FROM artists WHERE name = 'Led Zeppelin')

/* Create a list of album titles and the unit prices for the artist Audioslave */
SELECT n.Name, u.UnitPrice
FROM ((albums t INNER JOIN artists n
				ON t.Artistid = n.Artistid)
INNER JOIN tracks u ON t.Albumid = u.Albumid)
WHERE n.Name = 'Audioslave'

/* Find the first and last name of any customer who does not have 
an invoice. Are they any customers returned from the query? */ 
SELECT n.FirstName, n.LastName, i.Invoiceid
FROM customers n 
	LEFT JOIN invoices i ON n.Customerid = i.Customerid
WHERE InvoiceId IS NULL

/* Find the total price of each album. What is the total price for
the album Big Ones? */
SELECT t.Title, SUM(p.UnitPrice)
FROM albums t
	INNER JOIN tracks p ON t.Albumid = p.Albumid
WHERE t.Title = 'Big Ones'
GROUP BY t.Title

/* How many records are created when you apply a Cartesian 
join to the invoice and invoice items table? */
SELECT a.invoice D
FROM invoices a CROSS JOIN invoice_items b;

These Queries are from Chinook Database
### Query-1. --How many albums does the artist Led Zeppelin
have?
SELECT artists.Name , COUNT(albums.AlbumID)
FROM artists
INNER JOIN albums
ON artists.ArtistID=albums.ArtistID
WHERE artists.Name='Led Zeppelin'
### Query-2.--Create a list of album titles and the unit prices for the artist "Audioslave".
SELECT albums.Title, tracks.UnitPrice, artists.Name
FROM ((albums LEFT JOIN tracks
ON albums.AlbumID=tracks.AlbumID)
LEFT JOIN artists
ON albums.ArtistID=artists.ArtistID)
WHERE artists.Name='Audioslave'
### Query-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.CustomerID, invoices.InvoiceID
FROM customers LEFT JOIN invoices
ON customers.CustomerID=invoices.CustomerID
WHERE invoices.InvoiceID IS NULL
### Query-4.--Find the total price for each album.
SELECT albums.AlbumID, albums.Title, SUM(tracks.UnitPrice)
FROM tracks INNER JOIN albums
ON tracks.AlbumID=albums.AlbumID
WHERE albums.Title='Big Ones'
GROUP BY albums.Title
### Query-5.-- How many records are created when you apply a Cartesian join to the invoice and invoice items table?
SELECT a.InvoiceId D
FROM invoices a CROSS JOIN
invoice_items b;

SELECT album.Title, sum(track.UnitPrice) as TotalPrice
from album
inner join track
on album.AlbumId = track.AlbumId
group by album.Title
having album.Title = 'Big Ones'

-- => 14.85