philikai/SQL_Spider_DDL huggingface dataset:
Database: company_1
Schema:
CREATE TABLE works_on(
Essn INTEGER,
Pno INTEGER,
Hours REAL,
PRIMARY KEY(Essn, Pno))

CREATE TABLE employee(
Fname TEXT,
Minit TEXT,
Lname TEXT,
Ssn INTEGER PRIMARY KEY,
Bdate TEXT,
Address TEXT,
Sex TEXT,
Salary INTEGER,
Super_ssn INTEGER, 
Dno INTEGER)

CREATE TABLE department(
Dname TEXT,
Dnumber INTEGER PRIMARY KEY,
Mgr_ssn INTEGER,
Mgr_start_date TEXT)

CREATE TABLE project(
Pname Text,
Pnumber INTEGER PRIMARY KEY,
Plocation TEXT,
Dnum INTEGER)

CREATE TABLE dependent(
Essn INTEGER,
Dependent_name TEXT,
Sex TEXT,
Bdate TEXT,
Relationship TEXT,
PRIMARY KEY(Essn, Dependent_name))

CREATE TABLE dept_locations(
Dnumber INTEGER,
Dlocation TEXT,
PRIMARY KEY(Dnumber, Dlocation))

Question: List all department names ordered by their starting date.
SQL: SELECT dname FROM department ORDER BY mgr_start_date

Question: find all dependent names who have a spouse relation with some employee.
SQL: SELECT Dependent_name FROM dependent WHERE relationship  =  'Spouse'

Question: how many female dependents are there?
SQL: SELECT count(*) FROM dependent WHERE sex  =  'F'

Question: Find the names of departments that are located in Houston.
SQL: SELECT t1.dname FROM department AS t1 JOIN dept_locations AS t2 ON t1.dnumber  =  t2.dnumber WHERE t2.dlocation  =  'Houston'

Question: Return the first names and last names of employees who earn more than 30000 in salary.
SQL: SELECT fname ,  lname FROM employee WHERE salary  >  30000

Question: Find the number of employees of each gender whose salary is lower than 50000.
SQL: SELECT count(*) ,  sex FROM employee WHERE salary  <  50000 GROUP BY sex

Question: list the first and last names, and the addresses of all employees in the ascending order of their birth date.
SQL: SELECT fname ,  lname ,  address FROM employee ORDER BY Bdate
Database: decoration_competition
Schema:
CREATE TABLE "college" (
"College_ID" int,
"Name" text,
"Leader_Name" text,
"College_Location" text,
PRIMARY KEY ("College_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Country" text,
"College_ID" int,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("College_ID") REFERENCES `college`("College_ID")
)

CREATE TABLE "round" (
"Round_ID" int,
"Member_ID" int,
"Decoration_Theme" text,
"Rank_in_Round" int,
PRIMARY KEY ("Member_ID","Round_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID")
)

Question: How many members are there?
SQL: SELECT count(*) FROM member

Question: List the names of members in ascending alphabetical order.
SQL: SELECT Name FROM member ORDER BY Name ASC

Question: What are the names and countries of members?
SQL: SELECT Name ,  Country FROM member

Question: Show the names of members whose country is "United States" or "Canada".
SQL: SELECT Name FROM member WHERE Country  =  "United States" OR Country  =  "Canada"

Question: Show the different countries and the number of members from each.
SQL: SELECT Country ,  COUNT(*) FROM member GROUP BY Country

Question: Show the most common country across members.
SQL: SELECT Country FROM member GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1

Question: Which countries have more than two members?
SQL: SELECT Country FROM member GROUP BY Country HAVING COUNT(*)  >  2

Question: Show the leader names and locations of colleges.
SQL: SELECT Leader_Name ,  College_Location FROM college

Question: Show the names of members and names of colleges they go to.
SQL: SELECT T2.Name ,  T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID

Question: Show the names of members and the locations of colleges they go to in ascending alphabetical order of member names.
SQL: SELECT T2.Name ,  T1.College_Location FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID ORDER BY T2.Name ASC

Question: Show the distinct leader names of colleges associated with members from country "Canada".
SQL: SELECT DISTINCT T1.Leader_Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID WHERE T2.Country  =  "Canada"

Question: Show the names of members and the decoration themes they have.
SQL: SELECT T1.Name ,  T2.Decoration_Theme FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID

Question: Show the names of members that have a rank in round higher than 3.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID WHERE T2.Rank_in_Round  >  3

Question: Show the names of members in ascending order of their rank in rounds.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID ORDER BY Rank_in_Round ASC

Question: List the names of members who did not participate in any round.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM round)