lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the first and last names of people who payed more than the rooms' base prices.

### SQL
SELECT T1.firstname ,  T1.lastname FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE T1.Rate - T2.basePrice  >  0
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the first and last names of people who payed more than the rooms' base prices?

### SQL
SELECT T1.firstname ,  T1.lastname FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE T1.Rate - T2.basePrice  >  0
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
How many rooms are there?

### SQL
SELECT count(*) FROM Rooms;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What is the total number of rooms available in this inn?

### SQL
SELECT count(*) FROM Rooms;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the number of rooms with a king bed.

### SQL
SELECT count(*) FROM Rooms WHERE bedType  =  "King";
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
How many rooms have a king bed?

### SQL
SELECT count(*) FROM Rooms WHERE bedType  =  "King";
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the number of rooms for each bed type.

### SQL
SELECT bedType ,  count(*) FROM Rooms GROUP BY bedType;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the number of rooms for each bed type?

### SQL
SELECT bedType ,  count(*) FROM Rooms GROUP BY bedType;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the name of the room with the maximum occupancy.

### SQL
SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What is the name of the room that can accommodate the most people?

### SQL
SELECT roomName FROM Rooms ORDER BY maxOccupancy DESC LIMIT 1;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the id and name of the most expensive base price room.

### SQL
SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Which room has the highest base price?

### SQL
SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 1;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
List the type of bed and name of all traditional rooms.

### SQL
SELECT roomName ,  bedType FROM Rooms WHERE decor = "traditional";
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the bed type and name of all the rooms with traditional decor?

### SQL
SELECT roomName ,  bedType FROM Rooms WHERE decor = "traditional";
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the number of rooms with king bed for each decor type.

### SQL
SELECT decor ,  count(*) FROM Rooms WHERE bedType = "King" GROUP BY decor;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
How many rooms have king beds? Report the number for each decor type.

### SQL
SELECT decor ,  count(*) FROM Rooms WHERE bedType = "King" GROUP BY decor;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the average and minimum price of the rooms in different decor.

### SQL
SELECT decor , avg(basePrice) ,  min(basePrice) FROM Rooms GROUP BY decor;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What is the average minimum and price of the rooms for each different decor.

### SQL
SELECT decor , avg(basePrice) ,  min(basePrice) FROM Rooms GROUP BY decor;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
List the name of all rooms sorted by their prices.

### SQL
SELECT roomName FROM Rooms ORDER BY basePrice;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Sort all the rooms according to the price. Just report the room names.

### SQL
SELECT roomName FROM Rooms ORDER BY basePrice;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the number of rooms with price higher than 120 for different decor.

### SQL
SELECT decor , count(*) FROM Rooms WHERE basePrice  >  120 GROUP BY decor;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
How many rooms cost more than 120, for each different decor?

### SQL
SELECT decor , count(*) FROM Rooms WHERE basePrice  >  120 GROUP BY decor;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
For each bed type, find the average room price.

### SQL
SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What is the average base price of rooms, for each bed type?

### SQL
SELECT bedType ,  avg(basePrice) FROM Rooms GROUP BY bedType;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
List the name of rooms with king or queen bed.

### SQL
SELECT roomName FROM Rooms WHERE bedType  =  "King" OR bedType  =  "Queen";
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the names of rooms that have either king or queen bed?

### SQL
SELECT roomName FROM Rooms WHERE bedType  =  "King" OR bedType  =  "Queen";
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
How many different types of beds are there?

### SQL
SELECT count(DISTINCT bedType) FROM Rooms;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the number of distinct bed types available in this inn.

### SQL
SELECT count(DISTINCT bedType) FROM Rooms;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the name and id of the top 3 expensive rooms.

### SQL
SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the name and id of the three highest priced rooms?

### SQL
SELECT RoomId ,  roomName FROM Rooms ORDER BY basePrice DESC LIMIT 3;
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the name of rooms whose price is higher than the average price.

### SQL
SELECT roomName FROM Rooms WHERE basePrice  >  ( SELECT avg(basePrice) FROM Rooms );
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the name of rooms that cost more than the average.

### SQL
SELECT roomName FROM Rooms WHERE basePrice  >  ( SELECT avg(basePrice) FROM Rooms );
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the number of rooms that do not have any reservation.

### SQL
SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
How many rooms have not had any reservation yet?

### SQL
SELECT count(*) FROM rooms WHERE roomid NOT IN (SELECT DISTINCT room FROM reservations)
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Return the name and number of reservations made for each of the rooms.

### SQL
SELECT T2.roomName ,  count(*) ,  T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
For each room, find its name and the number of times reservations were made for it.

### SQL
SELECT T2.roomName ,  count(*) ,  T1.Room FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the names of rooms that have been reserved for more than 60 times.

### SQL
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room HAVING count(*)  >  60
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the names of rooms whose reservation frequency exceeds 60 times?

### SQL
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId GROUP BY T1.Room HAVING count(*)  >  60
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the name of rooms whose base price is between 120 and 150.

### SQL
SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Which rooms cost between 120 and 150? Give me the room names.

### SQL
SELECT roomname FROM rooms WHERE baseprice BETWEEN 120 AND 150
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
Find the name of rooms booked by some customers whose first name contains ROY.

### SQL
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE firstname LIKE '%ROY%'
### Database Schema
CREATE TABLE "Rooms" ( 
	"RoomId" TEXT PRIMARY KEY,
	"roomName" TEXT, 
	"beds" INTEGER, 
	"bedType" TEXT, 
	"maxOccupancy" INTEGER, 
	"basePrice" INTEGER, 
	"decor" TEXT

)

CREATE TABLE "Reservations" ( 
	"Code" INTEGER PRIMARY KEY, 
	"Room" TEXT, 
	"CheckIn" TEXT, 
	"CheckOut" TEXT, 
	"Rate" REAL, 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Adults" INTEGER, 
	"Kids" INTEGER,
	FOREIGN KEY (Room) REFERENCES Rooms(RoomId)
)

### Question
What are the name of rooms booked by customers whose first name has "ROY" in part?

### SQL
SELECT T2.roomName FROM Reservations AS T1 JOIN Rooms AS T2 ON T1.Room  =  T2.RoomId WHERE firstname LIKE '%ROY%'
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
what are the details of the cmi masters that have the cross reference code 'Tax'?

### SQL
SELECT T1.cmi_details FROM Customer_Master_Index AS T1 JOIN CMI_Cross_References AS T2 ON T1.master_customer_id  =  T2.master_customer_id WHERE T2.source_system_code  =  'Tax'
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
What is the cmi cross reference id that is related to at least one council tax entry? List the cross reference id and source system code.

### SQL
SELECT T1.cmi_cross_ref_id ,  T1.source_system_code FROM CMI_Cross_References AS T1 JOIN Council_Tax AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id GROUP BY T1.cmi_cross_ref_id HAVING count(*)  >=  1
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
How many business rates are related to each cmi cross reference? List cross reference id, master customer id and the n

### SQL
SELECT T2.cmi_cross_ref_id ,  T2.master_customer_id ,  count(*) FROM Business_Rates AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id GROUP BY T2.cmi_cross_ref_id
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
What is the tax source system code related to the benefits and overpayments? List the code and the benifit id, order by benifit id.

### SQL
SELECT T1.source_system_code ,  T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Benefits_Overpayments AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id ORDER BY T2.council_tax_id
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
Wat is the tax source system code and master customer id of the taxes related to each parking fine id?

### SQL
SELECT T1.source_system_code ,  T1.master_customer_id ,  T2.council_tax_id FROM CMI_Cross_References AS T1 JOIN Parking_Fines AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
What are the renting arrears tax ids related to the customer master index whose detail is not 'Schmidt, Kertzmann and Lubowitz'?

### SQL
SELECT T1.council_tax_id FROM Rent_Arrears AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id JOIN Customer_Master_Index AS T3 ON T3.master_customer_id  =  T2.master_customer_id WHERE T3.cmi_details != 'Schmidt ,  Kertzmann and Lubowitz'
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
What are the register ids of electoral registries that have the cross reference source system code 'Electoral' or 'Tax'?

### SQL
SELECT T1.electoral_register_id FROM Electoral_Register AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id  =  T2.cmi_cross_ref_id WHERE T2.source_system_code  =  'Electoral' OR T2.source_system_code  =  'Tax'
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
How many different source system code for the cmi cross references are there?

### SQL
SELECT count(DISTINCT source_system_code) FROM CMI_cross_references
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
List all information about customer master index, and sort them by details in descending order.

### SQL
SELECT * FROM customer_master_index ORDER BY cmi_details DESC
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
List the council tax ids and their related cmi cross references of all the parking fines.

### SQL
SELECT council_tax_id ,  cmi_cross_ref_id FROM parking_fines
### Database Schema
CREATE TABLE Customer_Master_Index (
master_customer_id INTEGER NOT NULL,
cmi_details VARCHAR(255),
PRIMARY KEY (master_customer_id)
)

CREATE TABLE CMI_Cross_References (
cmi_cross_ref_id INTEGER NOT NULL,
master_customer_id INTEGER NOT NULL,
source_system_code CHAR(15) NOT NULL,
PRIMARY KEY (cmi_cross_ref_id),
FOREIGN KEY (master_customer_id) REFERENCES Customer_Master_Index (master_customer_id)

)

CREATE TABLE Council_Tax (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Business_Rates (
business_rates_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (business_rates_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Benefits_Overpayments (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Parking_Fines (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Rent_Arrears (
council_tax_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (council_tax_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

CREATE TABLE Electoral_Register (
electoral_register_id INTEGER NOT NULL,
cmi_cross_ref_id INTEGER NOT NULL,
PRIMARY KEY (electoral_register_id),
FOREIGN KEY (cmi_cross_ref_id) REFERENCES CMI_Cross_References (cmi_cross_ref_id)
)

### Question
How many council taxes are collected for renting arrears ?

### SQL
SELECT count(*) FROM rent_arrears