lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the first names of all professors in the Accounting department who is teaching some course and the class room.

### SQL
SELECT T2.emp_fname ,  T1.class_room FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Accounting'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names of all Accounting professors who teach and what are the classrooms of the courses they teach?

### SQL
SELECT T2.emp_fname ,  T1.class_room FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Accounting'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the first names and degree of all professors who are teaching some class in Computer Info. Systems department.

### SQL
SELECT DISTINCT T2.emp_fname ,  T3.prof_high_degree FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Computer Info. Systems'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the different first names and highest degree attained for professors teaching in the Computer Information Systems department?

### SQL
SELECT DISTINCT T2.emp_fname ,  T3.prof_high_degree FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num JOIN professor AS T3 ON T2.emp_num  =  T3.emp_num JOIN department AS T4 ON T4.dept_code  =  T3.dept_code WHERE T4.dept_name  =  'Computer Info. Systems'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the last name of the student who got a grade A in the class with code 10018.

### SQL
SELECT T1.stu_lname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE T2.enroll_grade  =  'A' AND T2.class_code  =  10018
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the last name of the student who received an A in the class with the code 10018?

### SQL
SELECT T1.stu_lname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num WHERE T2.enroll_grade  =  'A' AND T2.class_code  =  10018
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the first name and office of history professor who did not get a Ph.D. degree.

### SQL
SELECT T2.emp_fname ,  T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T1.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History' AND T1.prof_high_degree != 'Ph.D.'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names and offices of history professors who don't have Ph.D.s?

### SQL
SELECT T2.emp_fname ,  T1.prof_office FROM professor AS T1 JOIN employee AS T2 ON T1.emp_num  =  T2.emp_num JOIN department AS T3 ON T1.dept_code  =  T3.dept_code WHERE T3.dept_name  =  'History' AND T1.prof_high_degree != 'Ph.D.'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the first names of professors who are teaching more than one class.

### SQL
SELECT T2.emp_fname FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num GROUP BY T1.prof_num HAVING count(*)  >  1
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names of all professors who teach more than one class?

### SQL
SELECT T2.emp_fname FROM CLASS AS T1 JOIN employee AS T2 ON T1.prof_num  =  T2.emp_num GROUP BY T1.prof_num HAVING count(*)  >  1
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the first names of students who took exactly one class.

### SQL
SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num GROUP BY T2.stu_num HAVING count(*)  =  1
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What are the first names of student who only took one course?

### SQL
SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num GROUP BY T2.stu_num HAVING count(*)  =  1
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
Find the name of department that offers the class whose description has the word "Statistics".

### SQL
SELECT T2.dept_name FROM course AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.crs_description LIKE '%Statistics%'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the name of the department that offers a course that has a description including the word "Statistics"?

### SQL
SELECT T2.dept_name FROM course AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.crs_description LIKE '%Statistics%'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the first name of the student whose last name starting with the letter S and is taking ACCT-211 class?

### SQL
SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T2.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211' AND T1.stu_lname LIKE 'S%'
### Database Schema
CREATE TABLE CLASS (
CLASS_CODE varchar(5) PRIMARY KEY,
CRS_CODE varchar(10),
CLASS_SECTION varchar(2),
CLASS_TIME varchar(20),
CLASS_ROOM varchar(8),
PROF_NUM int,
FOREIGN KEY (CRS_CODE) REFERENCES COURSE(CRS_CODE)
FOREIGN KEY (PROF_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE COURSE (
CRS_CODE varchar(10) PRIMARY KEY,
DEPT_CODE varchar(10),
CRS_DESCRIPTION varchar(35),
CRS_CREDIT float(8),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE DEPARTMENT (
DEPT_CODE varchar(10) PRIMARY KEY,
DEPT_NAME varchar(30),
SCHOOL_CODE varchar(8),
EMP_NUM int,
DEPT_ADDRESS varchar(20),
DEPT_EXTENSION varchar(4),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM)
)

CREATE TABLE EMPLOYEE (
EMP_NUM int PRIMARY KEY,
EMP_LNAME varchar(15),
EMP_FNAME varchar(12),
EMP_INITIAL varchar(1),
EMP_JOBCODE varchar(5),
EMP_HIREDATE datetime,
EMP_DOB datetime
)

CREATE TABLE ENROLL (
CLASS_CODE varchar(5),
STU_NUM int,
ENROLL_GRADE varchar(50),
FOREIGN KEY (CLASS_CODE) REFERENCES CLASS(CLASS_CODE)
FOREIGN KEY (STU_NUM) REFERENCES STUDENT(STU_NUM)
)

CREATE TABLE PROFESSOR (
EMP_NUM int,
DEPT_CODE varchar(10),
PROF_OFFICE varchar(50),
PROF_EXTENSION varchar(4),
PROF_HIGH_DEGREE varchar(5),
FOREIGN KEY (EMP_NUM) REFERENCES EMPLOYEE(EMP_NUM),
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

CREATE TABLE STUDENT (
STU_NUM int PRIMARY KEY,
STU_LNAME varchar(15),
STU_FNAME varchar(15),
STU_INIT varchar(1),
STU_DOB datetime,
STU_HRS int,
STU_CLASS varchar(2),
STU_GPA float(8),
STU_TRANSFER numeric,
DEPT_CODE varchar(18),
STU_PHONE varchar(4),
PROF_NUM int,
FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT(DEPT_CODE)
)

### Question
What is the first name of the student whose last name starts with the letter S and is taking ACCT-211?

### SQL
SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T2.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211' AND T1.stu_lname LIKE 'S%'
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
How many clubs are there?

### SQL
SELECT count(*) FROM club
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What is the total number of clubs?

### SQL
SELECT count(*) FROM club
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
List the distinct region of clubs in ascending alphabetical order.

### SQL
SELECT DISTINCT Region FROM club ORDER BY Region ASC
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the different regions of clubs in ascending alphabetical order?

### SQL
SELECT DISTINCT Region FROM club ORDER BY Region ASC
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What is the average number of gold medals for clubs?

### SQL
SELECT avg(Gold) FROM club_rank
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What is the average number of gold medals for a club?

### SQL
SELECT avg(Gold) FROM club_rank
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the types and countries of competitions?

### SQL
SELECT Competition_type ,  Country FROM competition
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the types of every competition and in which countries are they located?

### SQL
SELECT Competition_type ,  Country FROM competition
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the distinct years in which the competitions type is not "Tournament"?

### SQL
SELECT DISTINCT YEAR FROM competition WHERE Competition_type != "Tournament"
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the different years for all competitions that are not of type equal to tournament?

### SQL
SELECT DISTINCT YEAR FROM competition WHERE Competition_type != "Tournament"
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the maximum and minimum number of silver medals for clubs.

### SQL
SELECT max(Silver) ,  min(Silver) FROM club_rank
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What are the maximum and minimum number of silver medals for all the clubs?

### SQL
SELECT max(Silver) ,  min(Silver) FROM club_rank
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
How many clubs have total medals less than 10?

### SQL
SELECT count(*) FROM club_rank WHERE Total  <  10
### Database Schema
CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
)

CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
)

CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
)

CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_ID`)
)

### Question
What is the total number of clubs that have less than 10 medals in total?

### SQL
SELECT count(*) FROM club_rank WHERE Total  <  10