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: How many albums are there?
SQL: SELECT count(*) FROM ALBUM

Question: Find the number of albums.
SQL: SELECT count(*) FROM ALBUM

Question: List the names of all music genres.
SQL: SELECT Name FROM GENRE

Question: What are the names of different music genres?
SQL: SELECT Name FROM GENRE

Question: Find all the customer information in state NY.
SQL: SELECT * FROM CUSTOMER WHERE State  =  "NY"

Question: What is all the customer information for customers in NY state?
SQL: SELECT * FROM CUSTOMER WHERE State  =  "NY"

Question: What are the first names and last names of the employees who live in Calgary city.
SQL: SELECT FirstName ,  LastName FROM EMPLOYEE WHERE City  =  "Calgary"

Question: Find the full names of employees living in the city of Calgary.
SQL: SELECT FirstName ,  LastName FROM EMPLOYEE WHERE City  =  "Calgary"

Question: What are the distinct billing countries of the invoices?
SQL: SELECT distinct(BillingCountry) FROM INVOICE

Question: Find the different billing countries for all invoices.
SQL: SELECT distinct(BillingCountry) FROM INVOICE

Question: Find the names of all artists that have "a" in their names.
SQL: SELECT Name FROM ARTIST WHERE Name LIKE "%a%"

Question: What are the names of artist who have the letter 'a' in their names?
SQL: SELECT Name FROM ARTIST WHERE Name LIKE "%a%"

Question: Find the title of all the albums of the artist "AC/DC".
SQL: SELECT Title FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId WHERE T2.Name  =  "AC/DC"

Question: What are the titles of albums by the artist "AC/DC"?
SQL: SELECT Title FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId WHERE T2.Name  =  "AC/DC"

Question: Hom many albums does the artist "Metallica" have?
SQL: SELECT COUNT(*) FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId WHERE T2.Name  =  "Metallica"

Question: Find the number of albums by the artist "Metallica".
SQL: SELECT COUNT(*) FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId WHERE T2.Name  =  "Metallica"

Question: Which artist does the album "Balls to the Wall" belong to?
SQL: SELECT T2.Name FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId WHERE T1.Title  =  "Balls to the Wall"

Question: Find the name of the artist who made the album "Balls to the Wall".
SQL: SELECT T2.Name FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId WHERE T1.Title  =  "Balls to the Wall"

Question: Which artist has the most albums?
SQL: SELECT T2.Name FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId GROUP BY T2.Name ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the name of the artist with the greatest number of albums?
SQL: SELECT T2.Name FROM ALBUM AS T1 JOIN ARTIST AS T2 ON T1.ArtistId  =  T2.ArtistId GROUP BY T2.Name ORDER BY COUNT(*) DESC LIMIT 1

Question: Find the names of all the tracks that contain the word "you".
SQL: SELECT Name FROM TRACK WHERE Name LIKE '%you%'

Question: What are the names of tracks that contain the the word you in them?
SQL: SELECT Name FROM TRACK WHERE Name LIKE '%you%'

Question: What is the average unit price of all the tracks?
SQL: SELECT AVG(UnitPrice) FROM TRACK

Question: Find the average unit price for a track.
SQL: SELECT AVG(UnitPrice) FROM TRACK

Question: What are the durations of the longest and the shortest tracks in milliseconds?
SQL: SELECT max(Milliseconds) ,  min(Milliseconds) FROM TRACK

Question: Find the maximum and minimum durations of tracks in milliseconds.
SQL: SELECT max(Milliseconds) ,  min(Milliseconds) FROM TRACK

Question: Show the album names, ids and the number of tracks for each album.
SQL: SELECT T1.Title ,  T2.AlbumID ,  COUNT(*) FROM ALBUM AS T1 JOIN TRACK AS T2 ON T1.AlbumId  =  T2.AlbumId GROUP BY T2.AlbumID

Question: What are the names and ids of the different albums, and how many tracks are on each?
SQL: SELECT T1.Title ,  T2.AlbumID ,  COUNT(*) FROM ALBUM AS T1 JOIN TRACK AS T2 ON T1.AlbumId  =  T2.AlbumId GROUP BY T2.AlbumID

Question: What is the name of the most common genre in all tracks?
SQL: SELECT T1.Name FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId GROUP BY T2.GenreId ORDER BY COUNT(*) DESC LIMIT 1

Question: Find the name of the genre that is most frequent across all tracks.
SQL: SELECT T1.Name FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId GROUP BY T2.GenreId ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the least common media type in all tracks?
SQL: SELECT T1.Name FROM MEDIATYPE AS T1 JOIN TRACK AS T2 ON T1.MediaTypeId  =  T2.MediaTypeId GROUP BY T2.MediaTypeId ORDER BY COUNT(*) ASC LIMIT 1

Question: What is the name of the media type that is least common across all tracks?
SQL: SELECT T1.Name FROM MEDIATYPE AS T1 JOIN TRACK AS T2 ON T1.MediaTypeId  =  T2.MediaTypeId GROUP BY T2.MediaTypeId ORDER BY COUNT(*) ASC LIMIT 1

Question: Show the album names and ids for albums that contain tracks with unit price bigger than 1.
SQL: SELECT T1.Title ,  T2.AlbumID FROM ALBUM AS T1 JOIN TRACK AS T2 ON T1.AlbumId  =  T2.AlbumId WHERE T2.UnitPrice  >  1 GROUP BY T2.AlbumID

Question: What are the titles and ids for albums containing tracks with unit price greater than 1?
SQL: SELECT T1.Title ,  T2.AlbumID FROM ALBUM AS T1 JOIN TRACK AS T2 ON T1.AlbumId  =  T2.AlbumId WHERE T2.UnitPrice  >  1 GROUP BY T2.AlbumID

Question: How many tracks belong to rock genre?
SQL: SELECT COUNT(*) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Rock"