lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find names of all colleges whose enrollment is greater than that of all colleges in the FL state.

### SQL
SELECT cName FROM college WHERE enr  >  (SELECT max(enr) FROM college WHERE state  =  'FL')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all colleges with a larger enrollment than the largest college in Florida?

### SQL
SELECT cName FROM college WHERE enr  >  (SELECT max(enr) FROM college WHERE state  =  'FL')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the total number of enrollment of schools that do not have any goalie player?

### SQL
SELECT sum(enr) FROM college WHERE cName NOT IN (SELECT cName FROM tryout WHERE pPos  =  "goalie")
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the total number of students enrolled in schools without any goalies?

### SQL
SELECT sum(enr) FROM college WHERE cName NOT IN (SELECT cName FROM tryout WHERE pPos  =  "goalie")
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the number of states that has some college whose enrollment is larger than the average enrollment?

### SQL
SELECT count(DISTINCT state) FROM college WHERE enr  >  (SELECT avg(enr) FROM college)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many states have a college with more students than average?

### SQL
SELECT count(DISTINCT state) FROM college WHERE enr  >  (SELECT avg(enr) FROM college)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the number of states that has some colleges whose enrollment is smaller than the average enrollment?

### SQL
SELECT count(DISTINCT state) FROM college WHERE enr  <  (SELECT avg(enr) FROM college)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many states have smaller colleges than average?

### SQL
SELECT count(DISTINCT state) FROM college WHERE enr  <  (SELECT avg(enr) FROM college)
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
How many devices are there?

### SQL
SELECT count(*) FROM device
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Count the number of devices.

### SQL
SELECT count(*) FROM device
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
List the carriers of devices in ascending alphabetical order.

### SQL
SELECT Carrier FROM device ORDER BY Carrier ASC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the different carriers for devices, listed in alphabetical order?

### SQL
SELECT Carrier FROM device ORDER BY Carrier ASC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the carriers of devices whose software platforms are not "Android"?

### SQL
SELECT Carrier FROM device WHERE Software_Platform != 'Android'
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Return the device carriers that do not have Android as their software platform.

### SQL
SELECT Carrier FROM device WHERE Software_Platform != 'Android'
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the names of shops in ascending order of open year?

### SQL
SELECT Shop_Name FROM shop ORDER BY Open_Year ASC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Return the names of shops, ordered by year of opening ascending.

### SQL
SELECT Shop_Name FROM shop ORDER BY Open_Year ASC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What is the average quantity of stocks?

### SQL
SELECT avg(Quantity) FROM stock
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Give the average quantity of stocks.

### SQL
SELECT avg(Quantity) FROM stock
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the names and location of the shops in ascending alphabetical order of name.

### SQL
SELECT Shop_Name ,  LOCATION FROM shop ORDER BY Shop_Name ASC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Return the names and locations of shops, ordered by name in alphabetical order.

### SQL
SELECT Shop_Name ,  LOCATION FROM shop ORDER BY Shop_Name ASC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
How many different software platforms are there for devices?

### SQL
SELECT count(DISTINCT Software_Platform) FROM device
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Count the number of different software platforms.

### SQL
SELECT count(DISTINCT Software_Platform) FROM device
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
List the open date of open year of the shop named "Apple".

### SQL
SELECT Open_Date ,  Open_Year FROM shop WHERE Shop_Name  =  "Apple"
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the open dates and years for the shop named Apple?

### SQL
SELECT Open_Date ,  Open_Year FROM shop WHERE Shop_Name  =  "Apple"
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
List the name of the shop with the latest open year.

### SQL
SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What is the shop name corresponding to the shop that opened in the most recent year?

### SQL
SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Show names of shops and the carriers of devices they have in stock.

### SQL
SELECT T3.Shop_Name ,  T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID JOIN shop AS T3 ON T1.Shop_ID  =  T3.Shop_ID
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the names of device shops, and what are the carriers that they carry devices in stock for?

### SQL
SELECT T3.Shop_Name ,  T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID JOIN shop AS T3 ON T1.Shop_ID  =  T3.Shop_ID
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Show names of shops that have more than one kind of device in stock.

### SQL
SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the names of shops that have more than a single kind of device in stock?

### SQL
SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Show the name of the shop that has the most kind of devices in stock.

### SQL
SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What is the name of the shop that has the most different kinds of devices in stock?

### SQL
SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Show the name of the shop that have the largest quantity of devices in stock.

### SQL
SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY SUM(T1.quantity) DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What is the name of the shop that has the greatest quantity of devices in stock?

### SQL
SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY SUM(T1.quantity) DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Please show different software platforms and the corresponding number of devices using each.

### SQL
SELECT Software_Platform ,  COUNT(*) FROM device GROUP BY Software_Platform
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the different software platforms for devices, and how many devices have each?

### SQL
SELECT Software_Platform ,  COUNT(*) FROM device GROUP BY Software_Platform
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Please show the software platforms of devices in descending order of the count.

### SQL
SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the different software platforms for devices, ordered by frequency descending?

### SQL
SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
List the software platform shared by the greatest number of devices.

### SQL
SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What is the software platform that is most common amongst all devices?

### SQL
SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
List the names of shops that have no devices in stock.

### SQL
SELECT Shop_Name FROM shop WHERE Shop_ID NOT IN (SELECT Shop_ID FROM stock)
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the names of shops that do not have any devices in stock?

### SQL
SELECT Shop_Name FROM shop WHERE Shop_ID NOT IN (SELECT Shop_ID FROM stock)
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Show the locations shared by shops with open year later than 2012 and shops with open year before 2008.

### SQL
SELECT LOCATION FROM shop WHERE Open_Year  >  2012 INTERSECT SELECT LOCATION FROM shop WHERE Open_Year  <  2008
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Which locations contains both shops that opened after the year 2012 and shops that opened before 2008?

### SQL
SELECT LOCATION FROM shop WHERE Open_Year  >  2012 INTERSECT SELECT LOCATION FROM shop WHERE Open_Year  <  2008
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
List the carriers of devices that have no devices in stock.

### SQL
SELECT Carrier FROM device WHERE Device_ID NOT IN (SELECT Device_ID FROM stock)
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the carriers of devices that are not in stock anywhere?

### SQL
SELECT Carrier FROM device WHERE Device_ID NOT IN (SELECT Device_ID FROM stock)
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
Show the carriers of devices in stock at more than one shop.

### SQL
SELECT T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID GROUP BY T1.Device_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
)

CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
)

### Question
What are the carriers of devices that are in stock in more than a single shop?

### SQL
SELECT T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID GROUP BY T1.Device_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
How many bands are there?

### SQL
SELECT count(*) FROM Band
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
Find the number of bands.

### SQL
SELECT count(*) FROM Band
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
What are all the labels?

### SQL
SELECT DISTINCT label FROM Albums
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
What are the different album labels listed?

### SQL
SELECT DISTINCT label FROM Albums
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
Find all the albums in 2012.

### SQL
SELECT * FROM Albums WHERE YEAR  =  2012
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
return all columns of the albums created in the year of 2012.

### SQL
SELECT * FROM Albums WHERE YEAR  =  2012
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
Find all the stage positions of the musicians with first name "Solveig"

### SQL
SELECT DISTINCT T1.stageposition FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate  =  T2.id WHERE Firstname  =  "Solveig"
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
What are the different stage positions for all musicians whose first name is "Solveig"?

### SQL
SELECT DISTINCT T1.stageposition FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate  =  T2.id WHERE Firstname  =  "Solveig"
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
How many songs are there?

### SQL
SELECT count(*) FROM Songs
### Database Schema
CREATE TABLE "Songs" ( 
	"SongId" INTEGER PRIMARY KEY, 
	"Title" TEXT 
)

CREATE TABLE "Albums" ( 
	"AId" INTEGER PRIMARY KEY, 
	"Title" TEXT, 
	"Year" INTEGER, 
	"Label" TEXT, 
	"Type" TEXT )

CREATE TABLE "Band" ( 
	"Id" INTEGER PRIMARY KEY, 
	"Firstname" TEXT, 
	"Lastname" TEXT )

CREATE TABLE "Instruments" ( 
	"SongId" INTEGER, 
	"BandmateId" INTEGER, 
	"Instrument" TEXT ,
	PRIMARY KEY(SongId, BandmateId, Instrument),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (BandmateId) REFERENCES Band(Id)
)

CREATE TABLE "Performance" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"StagePosition" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

CREATE TABLE "Tracklists" ( 
	"AlbumId" INTEGER, 
	"Position" INTEGER, 
	"SongId" INTEGER ,
	PRIMARY KEY(AlbumId, Position),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (AlbumId) REFERENCES Albums(AId)
)

CREATE TABLE "Vocals" ( 
	"SongId" INTEGER, 
	"Bandmate" INTEGER, 
	"Type" TEXT,
	PRIMARY KEY(SongId, Bandmate),
	FOREIGN KEY (SongId) REFERENCES Songs(SongId),
	FOREIGN KEY (Bandmate) REFERENCES Band(Id)
)

### Question
Count the number of songs.

### SQL
SELECT count(*) FROM Songs