lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Return the names of artists and the themes of their exhibitions that had a ticket price higher than average.

### SQL
SELECT T1.theme ,  T2.name FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id WHERE T1.ticket_price  >  (SELECT avg(ticket_price) FROM exhibition)
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show the average, minimum, and maximum ticket prices for exhibitions for all years before 2009.

### SQL
SELECT avg(ticket_price) ,  min(ticket_price) ,  max(ticket_price) FROM exhibition WHERE YEAR  <  2009
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the average, minimum, and maximum ticket prices for exhibitions that happened prior to 2009?

### SQL
SELECT avg(ticket_price) ,  min(ticket_price) ,  max(ticket_price) FROM exhibition WHERE YEAR  <  2009
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show theme and year for all exhibitions in an descending order of ticket price.

### SQL
SELECT theme ,  YEAR FROM exhibition ORDER BY ticket_price DESC
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the themes and years for exhibitions, sorted by ticket price descending?

### SQL
SELECT theme ,  YEAR FROM exhibition ORDER BY ticket_price DESC
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What is the theme, date, and attendance for the exhibition in year 2004?

### SQL
SELECT T2.theme ,  T1.date ,  T1.attendance FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T2.year  =  2004
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Return the themes, dates, and attendance for exhibitions that happened in 2004.

### SQL
SELECT T2.theme ,  T1.date ,  T1.attendance FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T2.year  =  2004
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show all artist names who didn't have an exhibition in 2004.

### SQL
SELECT name FROM artist EXCEPT SELECT T2.name FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id WHERE T1.year  =  2004
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the names of artists who did not have an exhibition in 2004?

### SQL
SELECT name FROM artist EXCEPT SELECT T2.name FROM exhibition AS T1 JOIN artist AS T2 ON T1.artist_id  =  T2.artist_id WHERE T1.year  =  2004
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show the theme for exhibitions with both records of an attendance below 100 and above 500.

### SQL
SELECT T2.theme FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T1.attendance  <  100 INTERSECT SELECT T2.theme FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T1.attendance  >  500
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Which themes have had corresponding exhibitions that have had attendance both below 100 and above 500?

### SQL
SELECT T2.theme FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T1.attendance  <  100 INTERSECT SELECT T2.theme FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T1.attendance  >  500
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
How many exhibitions have a attendance more than 100 or have a ticket price below 10?

### SQL
SELECT count(*) FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T1.attendance  >  100 OR T2.ticket_price  <  10
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Count the number of exhibitions that have had an attendnance of over 100 or a ticket prices under 10.

### SQL
SELECT count(*) FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id WHERE T1.attendance  >  100 OR T2.ticket_price  <  10
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
Show all artist names with an average exhibition attendance over 200.

### SQL
SELECT T3.name FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id JOIN artist AS T3 ON T3.artist_id  =  T2.artist_id GROUP BY T3.artist_id HAVING avg(T1.attendance)  >  200
### Database Schema
CREATE TABLE "artist" (
"Artist_ID" int,
"Name" text,
"Country" text,
"Year_Join" int,
"Age" int,
PRIMARY KEY ("Artist_ID")
)

CREATE TABLE "exhibition" (
"Exhibition_ID" int,
"Year" int,
"Theme" text,
"Artist_ID" int,
"Ticket_Price" real,
PRIMARY KEY ("Exhibition_ID"),
FOREIGN KEY (`Artist_ID`) REFERENCES `artist`(`Artist_ID`)
)

CREATE TABLE "exhibition_record" (
"Exhibition_ID" int,
"Date" text,
"Attendance" int,
PRIMARY KEY ("Exhibition_ID","Date"),
FOREIGN KEY (`Exhibition_ID`) REFERENCES `exhibition`(`Exhibition_ID`)
)

### Question
What are the names of artist whose exhibitions draw over 200 attendees on average?

### SQL
SELECT T3.name FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id JOIN artist AS T3 ON T3.artist_id  =  T2.artist_id GROUP BY T3.artist_id HAVING avg(T1.attendance)  >  200
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the id of the item whose title is "orange".

### SQL
SELECT i_id FROM item WHERE title  =  "orange"
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
List all information in the item table.

### SQL
SELECT * FROM item
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the number of reviews.

### SQL
SELECT count(*) FROM review
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
How many users are there?

### SQL
SELECT count(*) FROM useracct
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the average and maximum rating of all reviews.

### SQL
SELECT avg(rating) ,  max(rating) FROM review
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the highest rank of all reviews.

### SQL
SELECT min(rank) FROM review
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
How many different users wrote some reviews?

### SQL
SELECT count(DISTINCT u_id) FROM review
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
How many different items were reviewed by some users?

### SQL
SELECT count(DISTINCT i_id) FROM review
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the number of items that did not receive any review.

### SQL
SELECT count(*) FROM item WHERE i_id NOT IN (SELECT i_id FROM review)
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the names of users who did not leave any review.

### SQL
SELECT name FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the names of goods that receive a rating of 10.

### SQL
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  =  10
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the titles of items whose rating is higher than the average review rating of all items.

### SQL
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  >  (SELECT avg(rating) FROM review)
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the titles of items that received any rating below 5.

### SQL
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  <  5
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the titles of items that received both a rating higher than 8 and a rating below 5.

### SQL
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  >  8 INTERSECT SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  <  5
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the names of items whose rank is higher than 3 and whose average rating is above 5.

### SQL
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rank  >  3 INTERSECT SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id GROUP BY T2.i_id HAVING avg(T2.rating)  >  5
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name of the item with the lowest average rating.

### SQL
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id GROUP BY T2.i_id ORDER BY avg(T2.rating) LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
List the titles of all items in alphabetic order .

### SQL
SELECT title FROM item ORDER BY title
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name of the user who gives the most reviews.

### SQL
SELECT T1.name FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id GROUP BY T2.u_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name and id of the item with the highest average rating.

### SQL
SELECT T1.title ,  T1.i_id FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id GROUP BY T2.i_id ORDER BY avg(T2.rating) DESC LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name and id of the good with the highest average rank.

### SQL
SELECT T1.title ,  T1.i_id FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id GROUP BY T2.i_id ORDER BY avg(T2.rank) DESC LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
For each user, return the name and the average rating of reviews given by them.

### SQL
SELECT T1.name ,  avg(T2.rating) FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id GROUP BY T2.u_id
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
For each user, find their name and the number of reviews written by them.

### SQL
SELECT T1.name ,  count(*) FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id GROUP BY T2.u_id
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name of the user who gave the highest rating.

### SQL
SELECT T1.name FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id ORDER BY T2.rating DESC LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name of the source user with the highest average trust score.

### SQL
SELECT T1.name FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id  =  T2.source_u_id GROUP BY T2.source_u_id ORDER BY avg(trust) DESC LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find each target user's name and average trust score.

### SQL
SELECT T1.name ,  avg(trust) FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id  =  T2.target_u_id GROUP BY T2.target_u_id
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the name of the target user with the lowest trust score.

### SQL
SELECT T1.name FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id  =  T2.target_u_id ORDER BY trust LIMIT 1
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the names of the items that did not receive any review.

### SQL
SELECT title FROM item WHERE i_id NOT IN (SELECT i_id FROM review)
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the number of users who did not write any review.

### SQL
SELECT count(*) FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)
### Database Schema
CREATE TABLE `item` (
  `i_id` integer NOT NULL
,  `title` varchar(20) DEFAULT NULL
,  PRIMARY KEY (`i_id`)
)

CREATE TABLE `review` (
  `a_id` integer NOT NULL PRIMARY KEY
,  `u_id` integer NOT NULL
,  `i_id` integer NOT NULL
,  `rating` integer DEFAULT NULL
,  `rank` integer DEFAULT NULL
, 	FOREIGN KEY (`u_id`) REFERENCES `useracct`(`u_id`)
, 	FOREIGN KEY (`i_id`) REFERENCES `item`(`i_id`)
)

CREATE TABLE `useracct` (
  `u_id` integer NOT NULL
,  `name` varchar(128) DEFAULT NULL
,  PRIMARY KEY (`u_id`)
)

CREATE TABLE "trust" (`source_u_id` integer NOT NULL, `target_u_id` integer NOT NULL, `trust` integer NOT NULL, FOREIGN KEY (`source_u_id`) REFERENCES `useracct`(`u_id`), FOREIGN KEY (`target_u_id`) REFERENCES `useracct`(`u_id`))

### Question
Find the number of items without any review.

### SQL
SELECT count(*) FROM item WHERE i_id NOT IN (SELECT i_id FROM review)
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
How many players are there?

### SQL
SELECT count(*) FROM player
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
List the names of players in ascending order of votes.

### SQL
SELECT Player_name FROM player ORDER BY Votes ASC
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
What are the gender and occupation of players?

### SQL
SELECT Gender ,  Occupation FROM player
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
List the name and residence for players whose occupation is not "Researcher".

### SQL
SELECT Player_name ,  residence FROM player WHERE Occupation != "Researcher"
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
Show the names of sponsors of players whose residence is either "Brandon" or "Birtle".

### SQL
SELECT Sponsor_name FROM player WHERE Residence  =  "Brandon" OR Residence  =  "Birtle"
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
What is the name of the player with the largest number of votes?

### SQL
SELECT Player_name FROM player ORDER BY Votes DESC LIMIT 1
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
Show different occupations along with the number of players in each occupation.

### SQL
SELECT Occupation ,  COUNT(*) FROM player GROUP BY Occupation
### Database Schema
CREATE TABLE "player" (
"Player_ID" int,
"Sponsor_name" text,
"Player_name" text,
"Gender" text,
"Residence" text,
"Occupation" text,
"Votes" int,
"Rank" text,
PRIMARY KEY ("Player_ID")
)

CREATE TABLE "club" (
"Club_ID" int,
"Club_name" text,
"Region" text,
"Start_year" int,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "coach" (
"Coach_ID" int,
"Coach_name" text,
"Gender" text,
"Club_ID" int,
"Rank" int,
PRIMARY KEY ("Coach_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player_coach" (
"Player_ID" int,
"Coach_ID" int,
"Starting_year" int,
PRIMARY KEY ("Player_ID","Coach_ID"),
FOREIGN KEY (`Player_ID`) REFERENCES `player`(`Player_ID`),
FOREIGN KEY (`Coach_ID`) REFERENCES `coach`(`Coach_ID`)
)

CREATE TABLE "match_result" (
"Rank" int,
"Club_ID" int,
"Gold" int,
"Big_Silver" int,
"Small_Silver" int,
"Bronze" int,
"Points" int,
PRIMARY KEY ("Rank","Club_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

### Question
Please show the most common occupation of players.

### SQL
SELECT Occupation FROM player GROUP BY Occupation ORDER BY COUNT(*) DESC LIMIT 1