CREATE TABLE Album (
    AlbumId NUMBER PRIMARY KEY,
    Title TEXT,
    ArtistId NUMBER,
    FOREIGN KEY ("ArtistId") REFERENCES Artist("ArtistId")
);
CREATE TABLE Artist (
    ArtistId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Customer (
    CustomerId NUMBER PRIMARY KEY,
    FirstName TEXT,
    LastName TEXT,
    Company TEXT,
    Address TEXT,
    City TEXT,
    State TEXT,
    Country TEXT,
    PostalCode TEXT,
    Phone TEXT,
    Fax TEXT,
    Email TEXT,
    SupportRepId NUMBER,
    FOREIGN KEY ("SupportRepId") REFERENCES Employee("EmployeeId")
);
CREATE TABLE Employee (
    EmployeeId NUMBER PRIMARY KEY,
    LastName TEXT,
    FirstName TEXT,
    Title TEXT,
    ReportsTo NUMBER,
    BirthDate TIME,
    HireDate TIME,
    Address TEXT,
    City TEXT,
    State TEXT,
    Country TEXT,
    PostalCode TEXT,
    Phone TEXT,
    Fax TEXT,
    Email TEXT,
    FOREIGN KEY ("ReportsTo") REFERENCES Employee("EmployeeId")
);
CREATE TABLE Genre (
    GenreId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Invoice (
    InvoiceId NUMBER PRIMARY KEY,
    CustomerId NUMBER,
    InvoiceDate TIME,
    BillingAddress TEXT,
    BillingCity TEXT,
    BillingState TEXT,
    BillingCountry TEXT,
    BillingPostalCode TEXT,
    Total NUMBER,
    FOREIGN KEY ("CustomerId") REFERENCES Customer("CustomerId")
);
CREATE TABLE InvoiceLine (
    InvoiceLineId NUMBER PRIMARY KEY,
    InvoiceId NUMBER,
    TrackId NUMBER,
    UnitPrice NUMBER,
    Quantity NUMBER,
    FOREIGN KEY ("TrackId") REFERENCES Track("TrackId"),
    FOREIGN KEY ("InvoiceId") REFERENCES Invoice("InvoiceId")
);
CREATE TABLE MediaType (
    MediaTypeId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Playlist (
    PlaylistId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE PlaylistTrack (
    PlaylistId NUMBER PRIMARY KEY,
    TrackId NUMBER,
    FOREIGN KEY ("TrackId") REFERENCES Track("TrackId"),
    FOREIGN KEY ("PlaylistId") REFERENCES Playlist("PlaylistId")
);
CREATE TABLE Track (
    TrackId NUMBER PRIMARY KEY,
    Name TEXT,
    AlbumId NUMBER,
    MediaTypeId NUMBER,
    GenreId NUMBER,
    Composer TEXT,
    Milliseconds NUMBER,
    Bytes NUMBER,
    UnitPrice NUMBER,
    FOREIGN KEY ("MediaTypeId") REFERENCES MediaType("MediaTypeId"),
    FOREIGN KEY ("GenreId") REFERENCES Genre("GenreId"),
    FOREIGN KEY ("AlbumId") REFERENCES Album("AlbumId")
);

Question: What are the first names and support rep ids for employees serving 10 or more customers?
SQL: SELECT T1.FirstName ,  T1.SupportRepId FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId GROUP BY T1.SupportRepId HAVING COUNT(*)  >=  10

Question: Please show the employee last names that serves no more than 20 customers.
SQL: SELECT T1.LastName FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId GROUP BY T1.SupportRepId HAVING COUNT(*)  <=  20

Question: What are the last names of employees who serve at most 20 customers?
SQL: SELECT T1.LastName FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId GROUP BY T1.SupportRepId HAVING COUNT(*)  <=  20

Question: Please list all album titles in alphabetical order.
SQL: SELECT Title FROM ALBUM ORDER BY Title

Question: What are all the album titles, in alphabetical order?
SQL: SELECT Title FROM ALBUM ORDER BY Title

Question: Please list the name and id of all artists that have at least 3 albums in alphabetical order.
SQL: SELECT T2.Name ,  T1.ArtistId FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistID GROUP BY T1.ArtistId HAVING COUNT(*)  >=  3 ORDER BY T2.Name

Question: What are the names and ids of artists with 3 or more albums, listed in alphabetical order?
SQL: SELECT T2.Name ,  T1.ArtistId FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistID GROUP BY T1.ArtistId HAVING COUNT(*)  >=  3 ORDER BY T2.Name

Question: Find the names of artists that do not have any albums.
SQL: SELECT Name FROM ARTIST EXCEPT SELECT T2.Name FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId

Question: What are the names of artists who have not released any albums?
SQL: SELECT Name FROM ARTIST EXCEPT SELECT T2.Name FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId

Question: What is the average unit price of rock tracks?
SQL: SELECT AVG(T2.UnitPrice) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Rock"

Question: Find the average unit price of tracks from the Rock genre.
SQL: SELECT AVG(T2.UnitPrice) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Rock"

Question: What are the duration of the longest and shortest pop tracks in milliseconds?
SQL: SELECT max(Milliseconds) ,  min(Milliseconds) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Pop"

Question: Find the maximum and minimum millisecond lengths of pop tracks.
SQL: SELECT max(Milliseconds) ,  min(Milliseconds) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Pop"

Question: What are the birth dates of employees living in Edmonton?
SQL: SELECT BirthDate FROM EMPLOYEE WHERE City  =  "Edmonton"

Question: Find the birth dates corresponding to employees who live in the city of Edmonton.
SQL: SELECT BirthDate FROM EMPLOYEE WHERE City  =  "Edmonton"

Question: What are the distinct unit prices of all tracks?
SQL: SELECT distinct(UnitPrice) FROM TRACK

Question: Find the distinct unit prices for tracks.
SQL: SELECT distinct(UnitPrice) FROM TRACK

Question: How many artists do not have any album?
SQL: SELECT count(*) FROM ARTIST WHERE artistid NOT IN(SELECT artistid FROM ALBUM)

Question: Cound the number of artists who have not released an album.
SQL: SELECT count(*) FROM ARTIST WHERE artistid NOT IN(SELECT artistid FROM ALBUM)

Question: What are the album titles for albums containing both 'Reggae' and 'Rock' genre tracks?
SQL: SELECT T1.Title FROM Album AS T1 JOIN Track AS T2 ON T1.AlbumId  =  T2.AlbumId JOIN Genre AS T3 ON T2.GenreID  =  T3.GenreID WHERE T3.Name  =  'Reggae' INTERSECT SELECT T1.Title FROM Album AS T1 JOIN Track AS T2 ON T1.AlbumId  =  T2.AlbumId JOIN Genre AS T3 ON T2.GenreID  =  T3.GenreID WHERE T3.Name  =  'Rock'

Question: Find the titles of albums that contain tracks of both the Reggae and Rock genres.
SQL: SELECT T1.Title FROM Album AS T1 JOIN Track AS T2 ON T1.AlbumId  =  T2.AlbumId JOIN Genre AS T3 ON T2.GenreID  =  T3.GenreID WHERE T3.Name  =  'Reggae' INTERSECT SELECT T1.Title FROM Album AS T1 JOIN Track AS T2 ON T1.AlbumId  =  T2.AlbumId JOIN Genre AS T3 ON T2.GenreID  =  T3.GenreID WHERE T3.Name  =  'Rock'