
CREATE TABLE advisor (
	"s_ID" VARCHAR(5), 
	"i_ID" VARCHAR(5), 
	PRIMARY KEY ("s_ID"), 
	FOREIGN KEY("i_ID") REFERENCES instructor ("ID"), 
	FOREIGN KEY("s_ID") REFERENCES student ("ID")
)


CREATE TABLE classroom (
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	capacity NUMERIC(4, 0), 
	PRIMARY KEY (building, room_number)
)


CREATE TABLE course (
	course_id VARCHAR(8), 
	title VARCHAR(50), 
	dept_name VARCHAR(20), 
	credits NUMERIC(2, 0), 
	PRIMARY KEY (course_id), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (credits > 0)
)


CREATE TABLE department (
	dept_name VARCHAR(20), 
	building VARCHAR(15), 
	budget NUMERIC(12, 2), 
	PRIMARY KEY (dept_name), 
	CHECK (budget > 0)
)


CREATE TABLE instructor (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	salary NUMERIC(8, 2), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (salary > 29000)
)


CREATE TABLE prereq (
	course_id VARCHAR(8), 
	prereq_id VARCHAR(8), 
	PRIMARY KEY (course_id, prereq_id), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(prereq_id) REFERENCES course (course_id)
)


CREATE TABLE section (
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	time_slot_id VARCHAR(4), 
	PRIMARY KEY (course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(building, room_number) REFERENCES classroom (building, room_number), 
	CHECK (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	CHECK (year > 1701 and year < 2100)
)


CREATE TABLE student (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	tot_cred NUMERIC(3, 0), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (tot_cred >= 0)
)


CREATE TABLE takes (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	grade VARCHAR(2), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES student ("ID")
)


CREATE TABLE teaches (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES instructor ("ID")
)


CREATE TABLE time_slot (
	time_slot_id VARCHAR(4), 
	day VARCHAR(1), 
	start_hr NUMERIC(2), 
	start_min NUMERIC(2), 
	end_hr NUMERIC(2), 
	end_min NUMERIC(2), 
	PRIMARY KEY (time_slot_id, day, start_hr, start_min), 
	CHECK (start_hr >= 0 and start_hr < 24), 
	CHECK (start_min >= 0 and start_min < 60), 
	CHECK (end_hr >= 0 and end_hr < 24), 
	CHECK (end_min >= 0 and end_min < 60)
)

CREATE TABLE advisor (
	"s_ID" VARCHAR(5), 
	"i_ID" VARCHAR(5), 
	PRIMARY KEY ("s_ID"), 
	FOREIGN KEY("i_ID") REFERENCES instructor ("ID"), 
	FOREIGN KEY("s_ID") REFERENCES student ("ID")
)

/*
2 rows from advisor table:
s_ID	i_ID
24746	19368
79352	95030
*/


CREATE TABLE classroom (
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	capacity NUMERIC(4, 0), 
	PRIMARY KEY (building, room_number)
)

/*
2 rows from classroom table:
building	room_number	capacity
Lamberton	134	10
Chandler	375	10
*/


CREATE TABLE course (
	course_id VARCHAR(8), 
	title VARCHAR(50), 
	dept_name VARCHAR(20), 
	credits NUMERIC(2, 0), 
	PRIMARY KEY (course_id), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (credits > 0)
)

/*
2 rows from course table:
course_id	title	dept_name	credits
787	C  Programming	Mech. Eng.	4
238	The Music of Donovan	Mech. Eng.	3
*/


CREATE TABLE department (
	dept_name VARCHAR(20), 
	building VARCHAR(15), 
	budget NUMERIC(12, 2), 
	PRIMARY KEY (dept_name), 
	CHECK (budget > 0)
)

/*
2 rows from department table:
dept_name	building	budget
Civil Eng.	Chandler	255041.46
Biology	Candlestick	647610.55
*/


CREATE TABLE instructor (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	salary NUMERIC(8, 2), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (salary > 29000)
)

/*
2 rows from instructor table:
ID	name	dept_name	salary
63395	McKinnon	Cybernetics	94333.99
78699	Pingr	Statistics	59303.62
*/


CREATE TABLE prereq (
	course_id VARCHAR(8), 
	prereq_id VARCHAR(8), 
	PRIMARY KEY (course_id, prereq_id), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(prereq_id) REFERENCES course (course_id)
)

/*
2 rows from prereq table:
course_id	prereq_id
376	130
760	169
*/


CREATE TABLE section (
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	building VARCHAR(15), 
	room_number VARCHAR(7), 
	time_slot_id VARCHAR(4), 
	PRIMARY KEY (course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id) REFERENCES course (course_id), 
	FOREIGN KEY(building, room_number) REFERENCES classroom (building, room_number), 
	CHECK (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	CHECK (year > 1701 and year < 2100)
)

/*
2 rows from section table:
course_id	sec_id	semester	year	building	room_number	time_slot_id
313	1	Fall	2010	Chandler	804	N
747	1	Spring	2004	Gates	314	K
*/


CREATE TABLE student (
	"ID" VARCHAR(5), 
	name VARCHAR(20) NOT NULL, 
	dept_name VARCHAR(20), 
	tot_cred NUMERIC(3, 0), 
	PRIMARY KEY ("ID"), 
	FOREIGN KEY(dept_name) REFERENCES department (dept_name), 
	CHECK (tot_cred >= 0)
)

/*
2 rows from student table:
ID	name	dept_name	tot_cred
24746	Schrefl	History	4
79352	Rumat	Finance	100
*/


CREATE TABLE takes (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	grade VARCHAR(2), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES student ("ID")
)

/*
2 rows from takes table:
ID	course_id	sec_id	semester	year	grade
65901	401	1	Fall	2003	C-
24932	802	1	Spring	2003	B-
*/


CREATE TABLE teaches (
	"ID" VARCHAR(5), 
	course_id VARCHAR(8), 
	sec_id VARCHAR(8), 
	semester VARCHAR(6), 
	year NUMERIC(4, 0), 
	PRIMARY KEY ("ID", course_id, sec_id, semester, year), 
	FOREIGN KEY(course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year), 
	FOREIGN KEY("ID") REFERENCES instructor ("ID")
)

/*
2 rows from teaches table:
ID	course_id	sec_id	semester	year
34175	747	1	Spring	2004
3199	169	1	Spring	2007
*/


CREATE TABLE time_slot (
	time_slot_id VARCHAR(4), 
	day VARCHAR(1), 
	start_hr NUMERIC(2), 
	start_min NUMERIC(2), 
	end_hr NUMERIC(2), 
	end_min NUMERIC(2), 
	PRIMARY KEY (time_slot_id, day, start_hr, start_min), 
	CHECK (start_hr >= 0 and start_hr < 24), 
	CHECK (start_min >= 0 and start_min < 60), 
	CHECK (end_hr >= 0 and end_hr < 24), 
	CHECK (end_min >= 0 and end_min < 60)
)

/*
2 rows from time_slot table:
time_slot_id	day	start_hr	start_min	end_hr	end_min
A	M	8.0000000000	0E-10	8.0000000000	50.0000000000
A	W	8.0000000000	0E-10	8.0000000000	50.0000000000
*/
Find the name of instructors who are advisors of the students from the Math department, and sort the results by students' total credit.
SELECT T2.name FROM advisor AS T1 JOIN instructor AS T2 ON T1.i_id  =  T2.id JOIN student AS T3 ON T1.s_id  =  T3.id WHERE T3.dept_name  =  'Math' ORDER BY T3.tot_cred
[{'name': 'Gutierrez'}, {'name': 'Pingr'}, {'name': 'Morris'}, {'name': 'Gutierrez'}, {'name': 'Luo'}, {'name': 'Murata'}, {'name': 'Hau'}, {'name': 'Valtchev'}, {'name': 'Bawa'}, {'name': 'Lent'}, {'name': 'Vicentino'}, {'name': 'DAgostino'}, {'name': 'Arinb'}, {'name': 'Konstantinides'}, {'name': 'Pimenta'}, {'name': 'Dusserre'}, {'name': 'Bancilhon'}, {'name': 'Yin'}, {'name': 'Soisalon-Soininen'}, {'name': 'Konstantinides'}, {'name': 'Bietzk'}, {'name': 'Jaekel'}, {'name': 'Shuming'}, {'name': 'Queiroz'}, {'name': 'Mird'}, {'name': 'Dusserre'}, {'name': 'Jaekel'}, {'name': 'Luo'}, {'name': 'Bawa'}, {'name': 'Sarkar'}, {'name': 'Tung'}, {'name': 'Valtchev'}, {'name': 'Romero'}, {'name': 'Sullivan'}, {'name': 'Soisalon-Soininen'}, {'name': 'Mird'}, {'name': 'Pimenta'}, {'name': 'Mahmoud'}, {'name': 'Arias'}, {'name': 'Vicentino'}, {'name': 'Bancilhon'}, {'name': 'Bertolino'}, {'name': 'Murata'}, {'name': 'Levine'}, {'name': 'Kean'}, {'name': 'Atanassov'}, {'name': 'Choll'}, {'name': 'Bawa'}, {'name': 'Lent'}, {'name': 'Bourrier'}, {'name': 'Bancilhon'}, {'name': 'Liley'}, {'name': 'Mahmoud'}, {'name': 'Hau'}, {'name': 'Bietzk'}, {'name': 'Ullman '}, {'name': 'Romero'}, {'name': 'Sullivan'}, {'name': 'Ullman '}, {'name': 'Yazdi'}, {'name': 'Vicentino'}, {'name': 'Yin'}, {'name': 'Wieland'}, {'name': 'Kean'}, {'name': 'Sullivan'}, {'name': 'Vicentino'}, {'name': 'Sullivan'}, {'name': 'Pingr'}, {'name': 'Bawa'}, {'name': 'Mingoz'}, {'name': 'Sakurai'}, {'name': 'Luo'}, {'name': 'Levine'}, {'name': 'Lembr'}, {'name': 'Yazdi'}, {'name': 'Luo'}, {'name': 'Arinb'}, {'name': 'Levine'}, {'name': 'Yin'}, {'name': 'Liley'}, {'name': 'Arinb'}, {'name': 'Hau'}, {'name': 'Liley'}, {'name': 'Atanassov'}, {'name': 'Romero'}, {'name': 'Sullivan'}, {'name': 'Bietzk'}, {'name': 'Arias'}, {'name': 'Wieland'}, {'name': 'Dusserre'}, {'name': 'Sarkar'}]