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;

Last one should be:

SELECT a.InvoiceId D FROM invoices a CROSS JOIN invoice_items b;

In last query there is one mistake
a.invoiceid must be there
I is missing

Last Query can be written as " select count(*) as TotalRecords from Invoices cross join invoice_items "

The last query can be much more effective using :
select count(*)
from invoices CROSS JOIN invoice_items

last one can be
SELECT a.invoiceid
FROM invoices a CROSS JOIN invoice_items b;

last one should be SELECT a.InvoiceId D FROM invoices a CROSS JOIN invoice_items b;

Last one: SELECT inv.invoiceid FROM invoices inv CROSS JOIN invoice_items it;

alternative last one;

SELECT i.invoiceid
FROM invoices i CROSS JOIN invoice_items i2;

hey, thanks for uploading this material. how did you get the color for the SQL code, mine is all black and I would like to create a similar page you created. Thank you, V

First one can be:

SELECT artists.Name
	,albums.ArtistId
	,count(albums.AlbumId) AS sumAlbums
FROM artists
INNER JOIN albums
	ON artists.ArtistId = albums.ArtistId
WHERE artists.Name = 'Led Zeppelin'

nice!thanx

…

---------------------------- El lunes, 26 de julio de 2021 13:27:10 ART, Logic2Paradigm ***@***.***> escribió: @Logic2Paradigm commented on this gist. First one can be: select artists.Name, albums.ArtistId, count(albums.AlbumId) as sumAlbums from artists inner join albums on artists.ArtistId = albums.ArtistId where artists.Name = 'Led Zeppelin' — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

Here's how I approached the questions for this test. All the answers came out correct. I attempted to keep the statements as readable as possible (for me)

SPOILER ALERT includes answers.

QN 1 - How many albums does the artist Led Zeppelin
have?
-> 14

SELECT COUNT (albumid) AS TotalAlbums
FROM albums, artists
WHERE (artists.artistid = albums.artistid)
AND artists.name = 'Led Zeppelin';

QN 2- Create a list of album titles and the unit prices for the artist "Audioslave".
-> 40 returned

SELECT albums.title, tracks.unitprice
FROM albums, artists, tracks
WHERE (artists.artistid = albums.artistid)
AND albums.albumid = tracks.albumid
AND artists.name = 'Audioslave';

QN 3 - Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?
-> none returned

SELECT customers.firstname, customers.lastname
FROM customers, invoices
WHERE customers.customerid
NOT IN (
SELECT invoices.customerid
FROM invoices
INNER JOIN customers
ON customers.customerid = invoices.customerid);

QN 4 - Find the total price for each album.
-> Big Ones = 14.85

SELECT albums.title, SUM(tracks.unitprice) AS Total_Price
FROM albums, tracks
WHERE albums.albumid = tracks.albumid
GROUP BY albums.title;

QN 5 - How many records are created when you apply a Cartesian join to the invoice and invoice items table?
-> 922880

SELECT COUNT(*) AS Total_Records
FROM invoices
CROSS JOIN
invoice_items;

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