lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
What is the full name of each student who is not allergic to any type of food.

### SQL
SELECT fname ,  lname FROM Student WHERE StuID NOT IN (SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
Find the number of male (sex is 'M') students who have some food type allery.

### SQL
SELECT count(*) FROM Student WHERE sex  =  "M" AND StuID IN (SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
How many male students (sex is 'M') are allergic to any type of food?

### SQL
SELECT count(*) FROM Student WHERE sex  =  "M" AND StuID IN (SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
Find the different first names and cities of the students who have allergy to milk or cat.

### SQL
SELECT DISTINCT T1.fname ,  T1.city_code FROM Student AS T1 JOIN Has_Allergy AS T2 ON T1.stuid  =  T2.stuid WHERE T2.Allergy  =  "Milk" OR T2.Allergy  =  "Cat"
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
What are the distinct first names and cities of the students who have allergy either to milk or to cat?

### SQL
SELECT DISTINCT T1.fname ,  T1.city_code FROM Student AS T1 JOIN Has_Allergy AS T2 ON T1.stuid  =  T2.stuid WHERE T2.Allergy  =  "Milk" OR T2.Allergy  =  "Cat"
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
Find the number of students who are older than 18 and do not have allergy to either food or animal.

### SQL
SELECT count(*) FROM Student WHERE age  >  18 AND StuID NOT IN ( SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" OR T2.allergytype  =  "animal")
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
How many students are over 18 and do not have allergy to food type or animal type?

### SQL
SELECT count(*) FROM Student WHERE age  >  18 AND StuID NOT IN ( SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" OR T2.allergytype  =  "animal")
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
Find the first name and major of the students who are not allegry to soy.

### SQL
SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")
### Database Schema
CREATE TABLE Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
)

CREATE TABLE Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

### Question
What are the first name and major of the students who are able to consume soy?

### SQL
SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
How many editors are there?

### SQL
SELECT count(*) FROM editor
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
List the names of editors in ascending order of age.

### SQL
SELECT Name FROM editor ORDER BY Age ASC
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
What are the names and ages of editors?

### SQL
SELECT Name ,  Age FROM editor
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
List the names of editors who are older than 25.

### SQL
SELECT Name FROM editor WHERE Age  >  25
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the names of editors of age either 24 or 25.

### SQL
SELECT Name FROM editor WHERE Age  =  24 OR Age  =  25
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
What is the name of the youngest editor?

### SQL
SELECT Name FROM editor ORDER BY Age ASC LIMIT 1
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
What are the different ages of editors? Show each age along with the number of editors of that age.

### SQL
SELECT Age ,  COUNT(*) FROM editor GROUP BY Age
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Please show the most common age of editors.

### SQL
SELECT Age FROM editor GROUP BY Age ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the distinct themes of journals.

### SQL
SELECT DISTINCT Theme FROM journal
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the names of editors and the theme of journals for which they serve on committees.

### SQL
SELECT T2.Name ,  T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
For each journal_committee, find the editor name and the journal theme.

### SQL
SELECT T2.Name ,  T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the names and ages of editors and the theme of journals for which they serve on committees, in ascending alphabetical order of theme.

### SQL
SELECT T2.Name ,  T2.age ,  T3.Theme FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID ORDER BY T3.Theme ASC
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the names of editors that are on the committee of journals with sales bigger than 3000.

### SQL
SELECT T2.Name FROM journal_committee AS T1 JOIN editor AS T2 ON T1.Editor_ID  =  T2.Editor_ID JOIN journal AS T3 ON T1.Journal_ID  =  T3.Journal_ID WHERE T3.Sales  >  3000
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the id, name of each editor and the number of journal committees they are on.

### SQL
SELECT T1.editor_id ,  T1.Name ,  COUNT(*) FROM editor AS T1 JOIN journal_committee AS T2 ON T1.Editor_ID  =  T2.Editor_ID GROUP BY T1.editor_id
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
Show the names of editors that are on at least two journal committees.

### SQL
SELECT T1.Name FROM editor AS T1 JOIN journal_committee AS T2 ON T1.Editor_ID  =  T2.Editor_ID GROUP BY T1.Name HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
List the names of editors that are not on any journal committee.

### SQL
SELECT Name FROM editor WHERE editor_id NOT IN (SELECT editor_id FROM journal_committee)
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
List the date, theme and sales of the journal which did not have any of the listed editors serving on committee.

### SQL
SELECT date ,  theme ,  sales FROM journal EXCEPT SELECT T1.date ,  T1.theme ,  T1.sales FROM journal AS T1 JOIN journal_committee AS T2 ON T1.journal_ID  =  T2.journal_ID
### Database Schema
CREATE TABLE "journal" (
"Journal_ID" int,
"Date" text,
"Theme" text,
"Sales" int,
PRIMARY KEY ("Journal_ID")
)

CREATE TABLE "editor" (
"Editor_ID" int,
"Name" text,
"Age" real,
PRIMARY KEY ("Editor_ID")
)

CREATE TABLE "journal_committee" (
"Editor_ID" int,
"Journal_ID" int,
"Work_Type" text,
PRIMARY KEY ("Editor_ID","Journal_ID"),
FOREIGN KEY ("Editor_ID") REFERENCES `editor`("Editor_ID"),
FOREIGN KEY ("Journal_ID") REFERENCES `journal`("Journal_ID")
)

### Question
What is the average sales of the journals that have an editor whose work type is 'Photo'?

### SQL
SELECT avg(T1.sales) FROM journal AS T1 JOIN journal_committee AS T2 ON T1.journal_ID  =  T2.journal_ID WHERE T2.work_type  =  'Photo'
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
How many accounts do we have?

### SQL
SELECT count(*) FROM Accounts
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Count the number of accounts.

### SQL
SELECT count(*) FROM Accounts
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Show ids, customer ids, names for all accounts.

### SQL
SELECT account_id ,  customer_id ,  account_name FROM Accounts
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
What are the account ids, customer ids, and account names for all the accounts?

### SQL
SELECT account_id ,  customer_id ,  account_name FROM Accounts
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Show other account details for account with name 338.

### SQL
SELECT other_account_details FROM Accounts WHERE account_name  =  "338"
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
What are the other account details for the account with the name 338?

### SQL
SELECT other_account_details FROM Accounts WHERE account_name  =  "338"
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
What is the first name, last name, and phone of the customer with account name 162?

### SQL
SELECT T2.customer_first_name ,  T2.customer_last_name ,  T2.customer_phone FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.account_name  =  "162"
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Give the full name and phone of the customer who has the account name 162.

### SQL
SELECT T2.customer_first_name ,  T2.customer_last_name ,  T2.customer_phone FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.account_name  =  "162"
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
How many accounts does the customer with first name Art and last name Turcotte have?

### SQL
SELECT count(*) FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Art" AND T2.customer_last_name  =  "Turcotte"
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Return the number of accounts that the customer with the first name Art and last name Turcotte has.

### SQL
SELECT count(*) FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.customer_first_name  =  "Art" AND T2.customer_last_name  =  "Turcotte"
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Show all customer ids and the number of accounts for each customer.

### SQL
SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
How many accounts are there for each customer id?

### SQL
SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Show the customer id and number of accounts with most accounts.

### SQL
SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
What is the customer id of the customer with the most accounts, and how many accounts does this person have?

### SQL
SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
What is the customer first, last name and id with least number of accounts.

### SQL
SELECT T2.customer_first_name ,  T2.customer_last_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) ASC LIMIT 1
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Give the full name and customer id of the customer with the fewest accounts.

### SQL
SELECT T2.customer_first_name ,  T2.customer_last_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) ASC LIMIT 1
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Show the number of all customers without an account.

### SQL
SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
How many customers do not have an account?

### SQL
SELECT count(*) FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Accounts)
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
Show the first names and last names of customers without any account.

### SQL
SELECT customer_first_name ,  customer_last_name FROM Customers EXCEPT SELECT T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id
### Database Schema
CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(20),
`customer_last_name` VARCHAR(20),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(255),
`customer_email` VARCHAR(255),
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customers_Cards` (
`card_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`card_type_code` VARCHAR(15) NOT NULL,
`card_number` VARCHAR(80),
`date_valid_from` DATETIME,
`date_valid_to` DATETIME,
`other_card_details` VARCHAR(255)
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`previous_transaction_id` INTEGER,
`account_id` INTEGER NOT NULL,
`card_id` INTEGER NOT NULL,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DOUBLE NULL,
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`card_id` ) REFERENCES `Customers_Cards`(`card_id` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

### Question
What are the full names of customers who do not have any accounts?

### SQL
SELECT customer_first_name ,  customer_last_name FROM Customers EXCEPT SELECT T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Accounts AS T2 ON T1.customer_id  =  T2.customer_id