lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
What is the partition id of the user named "Iron Man".

### SQL
SELECT partitionid FROM user_profiles WHERE name  =  'Iron Man'
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
How many users are there?

### SQL
SELECT count(*) FROM user_profiles
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
How many followers does each user have?

### SQL
SELECT count(*) FROM follows
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the number of followers for each user.

### SQL
SELECT count(*) FROM follows GROUP BY f1
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the number of tweets in record.

### SQL
SELECT count(*) FROM tweets
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the number of users who posted some tweets.

### SQL
SELECT count(DISTINCT UID) FROM tweets
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name and email of the user whose name contains the word ‘Swift’.

### SQL
SELECT name ,  email FROM user_profiles WHERE name LIKE '%Swift%'
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the names of users whose emails contain ‘superstar’ or ‘edu’.

### SQL
SELECT name FROM user_profiles WHERE email LIKE '%superstar%' OR email LIKE '%edu%'
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Return the text of tweets about the topic 'intern'.

### SQL
SELECT text FROM tweets WHERE text LIKE '%intern%'
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name and email of the users who have more than 1000 followers.

### SQL
SELECT name ,  email FROM user_profiles WHERE followers  >  1000
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the names of the users whose number of followers is greater than that of the user named "Tyler Swift".

### SQL
SELECT T1.name FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f1 GROUP BY T2.f1 HAVING count(*)  >  (SELECT count(*) FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f1 WHERE T1.name  =  'Tyler Swift')
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name and email for the users who have more than one follower.

### SQL
SELECT T1.name ,  T1.email FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f1 GROUP BY T2.f1 HAVING count(*)  >  1
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the names of users who have more than one tweet.

### SQL
SELECT T1.name FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid HAVING count(*)  >  1
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the id of users who are followed by Mary and Susan.

### SQL
SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Mary" INTERSECT SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Susan"
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the id of users who are followed by Mary or Susan.

### SQL
SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Mary" OR T1.name  =  "Susan"
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name of the user who has the largest number of followers.

### SQL
SELECT name FROM user_profiles ORDER BY followers DESC LIMIT 1
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name and email of the user followed by the least number of people.

### SQL
SELECT name ,  email FROM user_profiles ORDER BY followers LIMIT 1
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
List the name and number of followers for each user, and sort the results by the number of followers in descending order.

### SQL
SELECT name ,  followers FROM user_profiles ORDER BY followers DESC
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
List the names of 5 users followed by the largest number of other users.

### SQL
SELECT name FROM user_profiles ORDER BY followers DESC LIMIT 5
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
List the text of all tweets in the order of date.

### SQL
SELECT text FROM tweets ORDER BY createdate
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name of each user and number of tweets tweeted by each of them.

### SQL
SELECT T1.name ,  count(*) FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name and partition id for users who tweeted less than twice.

### SQL
SELECT T1.name ,  T1.partitionid FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid HAVING count(*)  <  2
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the name of the user who tweeted more than once, and number of tweets tweeted by them.

### SQL
SELECT T1.name ,  count(*) FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid HAVING count(*)  >  1
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the average number of followers for the users who do not have any tweet.

### SQL
SELECT avg(followers) FROM user_profiles WHERE UID NOT IN (SELECT UID FROM tweets)
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the average number of followers for the users who had some tweets.

### SQL
SELECT avg(followers) FROM user_profiles WHERE UID IN (SELECT UID FROM tweets)
### Database Schema
CREATE TABLE `follows` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`),
  FOREIGN KEY (`f1`) REFERENCES `user_profiles`(`uid`),
  FOREIGN KEY (`f2`) REFERENCES `user_profiles`(`uid`)

)

CREATE TABLE `tweets` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `text` char(140) NOT NULL,
  `createdate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`uid`) REFERENCES `user_profiles`(`uid`)
)

CREATE TABLE "user_profiles" (`uid` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `partitionid` int(11) DEFAULT NULL, `followers` int(11) DEFAULT NULL, PRIMARY KEY (`uid`))

### Question
Find the maximum and total number of followers of all users.

### SQL
SELECT max(followers) ,  sum(followers) FROM user_profiles
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find the names of all the catalog entries.

### SQL
SELECT distinct(catalog_entry_name) FROM catalog_contents
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
What are all the catalog entry names?

### SQL
SELECT distinct(catalog_entry_name) FROM catalog_contents
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find the list of attribute data types possessed by more than 3 attribute definitions.

### SQL
SELECT attribute_data_type FROM Attribute_Definitions GROUP BY attribute_data_type HAVING count(*)  >  3
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
What are the attribute data types with more than 3 attribute definitions?

### SQL
SELECT attribute_data_type FROM Attribute_Definitions GROUP BY attribute_data_type HAVING count(*)  >  3
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
What is the attribute data type of the attribute with name "Green"?

### SQL
SELECT attribute_data_type FROM Attribute_Definitions WHERE attribute_name  =  "Green"
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find the attribute data type for the attribute named "Green".

### SQL
SELECT attribute_data_type FROM Attribute_Definitions WHERE attribute_name  =  "Green"
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find the name and level of catalog structure with level between 5 and 10.

### SQL
SELECT catalog_level_name ,  catalog_level_number FROM Catalog_Structure WHERE catalog_level_number BETWEEN 5 AND 10
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
What are the name and level of catalog structure with level number between 5 and 10

### SQL
SELECT catalog_level_name ,  catalog_level_number FROM Catalog_Structure WHERE catalog_level_number BETWEEN 5 AND 10
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find all the catalog publishers whose name contains "Murray"

### SQL
SELECT distinct(catalog_publisher) FROM catalogs WHERE catalog_publisher LIKE "%Murray%"
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Which catalog publishers have substring "Murray" in their names?

### SQL
SELECT distinct(catalog_publisher) FROM catalogs WHERE catalog_publisher LIKE "%Murray%"
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Which catalog publisher has published the most catalogs?

### SQL
SELECT catalog_publisher FROM catalogs GROUP BY catalog_publisher ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find the catalog publisher that has the most catalogs.

### SQL
SELECT catalog_publisher FROM catalogs GROUP BY catalog_publisher ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
Find the names and publication dates of all catalogs that have catalog level number greater than 5.

### SQL
SELECT t1.catalog_name ,  t1.date_of_publication FROM catalogs AS t1 JOIN catalog_structure AS t2 ON t1.catalog_id  =  t2.catalog_id WHERE catalog_level_number  >  5
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
What are the name and publication date of the catalogs with catalog level number above 5?

### SQL
SELECT t1.catalog_name ,  t1.date_of_publication FROM catalogs AS t1 JOIN catalog_structure AS t2 ON t1.catalog_id  =  t2.catalog_id WHERE catalog_level_number  >  5
### Database Schema
CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
)

CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
)

CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
)

CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
)

### Question
What are the entry names of catalog with the attribute possessed by most entries.

### SQL
SELECT t1.catalog_entry_name FROM Catalog_Contents AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.catalog_entry_id  =  t2.catalog_entry_id WHERE t2.attribute_value  =  (SELECT attribute_value FROM Catalog_Contents_Additional_Attributes GROUP BY attribute_value ORDER BY count(*) DESC LIMIT 1)