CREATE TABLE student ( sid INTEGER, 
                       sname VARCHAR,
                       sex CHAR(1),
                       age INTEGER,
                       gpa REAL,
                       PRIMARY KEY (sid) );

CREATE TABLE dept ( dname VARCHAR,
                    numphds INTEGER,                    
                    PRIMARY KEY (dname) );

CREATE TABLE prof ( pname VARCHAR,
                    dname VARCHAR,                    
                    PRIMARY KEY (pname),
                    FOREIGN KEY (dname) REFERENCES dept );

CREATE TABLE course ( cno INTEGER,
                      cname VARCHAR,
                      dname VARCHAR,
                      PRIMARY KEY (cno, dname),
                      FOREIGN KEY (dname) REFERENCES dept );

CREATE TABLE major ( dname VARCHAR,
                     sid INTEGER,
                     PRIMARY KEY (dname, sid),
                     FOREIGN KEY (dname) REFERENCES dept,
                     FOREIGN KEY (sid) REFERENCES student );

CREATE TABLE section ( dname VARCHAR,
                       cno INTEGER,
                       sectno INTEGER,
                       pname VARCHAR,
                       PRIMARY KEY (dname, cno, sectno),
                       FOREIGN KEY (dname) REFERENCES dept,
                       FOREIGN KEY (cno, dname) REFERENCES course,
                       FOREIGN KEY (pname) REFERENCES prof );

CREATE TABLE enroll ( sid INTEGER,
                      grade CHAR(1),
                      dname VARCHAR,
                      cno INTEGER,
                      sectno INTEGER,
                      PRIMARY KEY (sid, dname, cno, sectno),
                      FOREIGN KEY (sid) REFERENCES student,
                      FOREIGN KEY (dname) REFERENCES dept, 
                      FOREIGN KEY (cno, dname) REFERENCES course,
                      FOREIGN KEY (dname, cno, sectno) REFERENCES section );

INSERT INTO student (sid, sname, sex, age, gpa) VALUES (1, "Jim Hunt", "M", 25, 4.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (2, "Ryan Peterson", "M", 24, 2.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (3, "Yisong Yue", "M", 24, 4.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (4, "Kristel Keegan", "F", 20, 4.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (5, "Scott Tucker", "M", 50, 3.8);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (6, "Aubrey de Grey", "M", 143, 4.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (7, "Paris Hilton", "F", 26, 2.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (8, "Bob Saget", "M", 51, 3.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (9, "Albert Weinstein", "M", 23, 4.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (10, "Freddy Mercury", "M", 21, 4.0);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (11, "Edward Scissorhands", "M", 19, 3.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (12, "Ezra Cornell", "M", 21, 2.6);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (13, "Madonna", "F", 22, 4.7);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (14, "Britney Spears", "M", 24, 3.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (15, "Michael Luo", "M", 28, 2.2);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (16, "Regan Morris", "M", 19, 2.1);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (17, "Lisa Woods", "F", 18, 4.8);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (18, "Michelle Faul", "F", 21, 3.3);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (19, "David Johnston", "M", 21, 2.8);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (20, "Laura Holson", "F", 24, 4.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (21, "Kim Dixon", "F", 21, 3.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (22, "Scott Helman", "M", 19, 4.4);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (23, "Eric Haselton", "M", 21, 4.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (24, "Sarah Auchard", "F", 20, 4.1);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (25, "Andrea Letzig", "F", 19, 2.9);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (26, "Jill Ellsworth", "F", 21, 3.1);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (27, "William Holson", "M", 18, 3.8);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (28, "Sean O'Grady", "M", 20, 2.1);
INSERT INTO student (sid, sname, sex, age, gpa) VALUES (29, "Dahleen Royse", "F", 18, 3.8);

INSERT INTO dept (dname, numphds) VALUES ("Computer Science", 40);
INSERT INTO dept (dname, numphds) VALUES ("Biology", 65 );
INSERT INTO dept (dname, numphds) VALUES ("Music", 22);
INSERT INTO dept (dname, numphds) VALUES ("Mathematics", 21);
INSERT INTO dept (dname, numphds) VALUES ("Physics", 38);
INSERT INTO dept (dname, numphds) VALUES ("Economics", 45);

INSERT INTO prof (pname, dname) VALUES ("Wolfgang Finkelstein", "Physics");
INSERT INTO prof (pname, dname) VALUES ("William Keck", "Physics");
INSERT INTO prof (pname, dname) VALUES ("Stephanie Zacharek", "Music");
INSERT INTO prof (pname, dname) VALUES ("Stephen Hunter", "Music");
INSERT INTO prof (pname, dname) VALUES ("Patrick Dean", "Biology");
INSERT INTO prof (pname, dname) VALUES ("Jason Singer", "Biology");
INSERT INTO prof (pname, dname) VALUES ("Cornelia von Zielbauer", "Mathematics");
INSERT INTO prof (pname, dname) VALUES ("Yvonne Rauwald", "Mathematics");
INSERT INTO prof (pname, dname) VALUES ("Kareem Fahim", "Computer Science");
INSERT INTO prof (pname, dname) VALUES ("Francesco O'Grady", "Computer Science");
INSERT INTO prof (pname, dname) VALUES ("Jennifer Waters", "Economics");
INSERT INTO prof (pname, dname) VALUES ("Emmanuel Keoun", "Economics");

INSERT INTO course (cno, cname, dname) VALUES (111, "Operating Systems", "Computer Science");
INSERT INTO course (cno, cname, dname) VALUES (222, "Artificial Intelligence", "Computer Science");
INSERT INTO course (cno, cname, dname) VALUES (333, "Algorithms", "Computer Science");
INSERT INTO course (cno, cname, dname) VALUES (123, "Physiology", "Biology");
INSERT INTO course (cno, cname, dname) VALUES (233, "Microbiology", "Biology");
INSERT INTO course (cno, cname, dname) VALUES (332, "Botany", "Biology");
INSERT INTO course (cno, cname, dname) VALUES (122, "Topology", "Mathematics");
INSERT INTO course (cno, cname, dname) VALUES (224, "Differential Geometry", "Mathematics");
INSERT INTO course (cno, cname, dname) VALUES (312, "Algebra", "Mathematics");
INSERT INTO course (cno, cname, dname) VALUES (154, "Tonal Theory", "Music");
INSERT INTO course (cno, cname, dname) VALUES (342, "Musicianship", "Music");
INSERT INTO course (cno, cname, dname) VALUES (453, "Music of the Romantic Era", "Music");
INSERT INTO course (cno, cname, dname) VALUES (221, "Quantum Mechanics", "Physics");
INSERT INTO course (cno, cname, dname) VALUES (332, "General Relativity", "Physics");
INSERT INTO course (cno, cname, dname) VALUES (543, "Electrodynamics", "Physics");
INSERT INTO course (cno, cname, dname) VALUES (221, "Microeconomic Theory", "Economics");
INSERT INTO course (cno, cname, dname) VALUES (112, "Intermediate Macroeconomicd Theory", "Economics");
INSERT INTO course (cno, cname, dname) VALUES (111, "Quantitative Methods in Economics", "Economics");

INSERT INTO section (dname, cno, sectno, pname) VALUES ("Biology", 332, 1, "Patrick Dean");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Biology", 332, 2, "Patrick Dean");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Biology", 233, 1, "Jason Singer");

INSERT INTO section (dname, cno, sectno, pname) VALUES ("Physics", 221, 1, "Wolfgang Finkelstein");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Physics", 332, 1, "William Keck");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Physics", 332, 2, "William Keck");

INSERT INTO section (dname, cno, sectno, pname) VALUES ("Music", 342, 1, "Stephanie Zacharek");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Music", 342, 2, "Stephanie Zacharek");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Music", 154, 1, "Stephen Hunter");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Music", 154, 2, "Stephen Hunter");

INSERT INTO section (dname, cno, sectno, pname) VALUES ("Mathematics", 312, 1, "Cornelia von Zielbauer");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Mathematics", 312, 2, "Cornelia von Zielbauer");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Mathematics", 224, 1, "Yvonne Rauwald");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Mathematics", 224, 2, "Yvonne Rauwald");

INSERT INTO section (dname, cno, sectno, pname) VALUES ("Computer Science", 111, 1, "Kareem Fahim");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Computer Science", 333, 1, "Francesco O'Grady");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Computer Science", 333, 2, "Francesco O'Grady");

INSERT INTO section (dname, cno, sectno, pname) VALUES ("Economics", 112, 1, "Jennifer Waters");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Economics", 112, 2, "Jennifer Waters");
INSERT INTO section (dname, cno, sectno, pname) VALUES ("Economics", 111, 1, "Emmanuel Keoun");


INSERT INTO major (dname, sid) VALUES ("Computer Science", 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (1, "A", "Computer Science", 111, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (1, "A", "Computer Science", 333, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (1, "A", "Mathematics", 312, 2);

INSERT INTO major (dname, sid) VALUES ("Computer Science", 7);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (7, "A", "Computer Science", 111, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (7, "B", "Computer Science", 333, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (7, "B", "Physics", 332, 2);

INSERT INTO major (dname, sid) VALUES ("Computer Science", 13);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (13, "A", "Computer Science", 111, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (13, "C", "Music", 154, 2);

INSERT INTO major (dname, sid) VALUES ("Computer Science", 19);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (19, "A", "Computer Science", 111, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (19, "D", "Economics", 111, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (19, "A", "Biology", 332, 1);

INSERT INTO major (dname, sid) VALUES ("Computer Science", 25);

INSERT INTO major (dname, sid) VALUES ("Mathematics", 4);

INSERT INTO major (dname, sid) VALUES ("Mathematics", 16);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (16, "A", "Mathematics", 312, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (16, "B", "Mathematics", 224, 1);

INSERT INTO major (dname, sid) VALUES ("Mathematics", 10);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (10, "B", "Mathematics", 312, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (10, "B", "Mathematics", 224, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (10, "B", "Physics", 332, 2);

INSERT INTO major (dname, sid) VALUES ("Mathematics", 22);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (22, "A", "Mathematics", 224, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (22, "C", "Physics", 332, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (22, "A", "Computer Science", 333, 1);

INSERT INTO major (dname, sid) VALUES ("Mathematics", 28);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (28, "A", "Mathematics", 224, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (28, "B", "Biology", 332, 1);

INSERT INTO major (dname, sid) VALUES ("Biology", 2);
INSERT INTO major (dname, sid) VALUES ("Biology", 8);

INSERT INTO major (dname, sid) VALUES ("Biology", 14);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (14, "A", "Biology", 332, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (14, "B", "Biology", 233, 1);

INSERT INTO major (dname, sid) VALUES ("Biology", 20);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (20, "A", "Biology", 332, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (20, "C", "Biology", 233, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (20, "B", "Economics", 111, 1);

INSERT INTO major (dname, sid) VALUES ("Biology", 26);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (26, "A", "Biology", 332, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (26, "A", "Music", 154, 1);

INSERT INTO major (dname, sid) VALUES ("Music", 3);

INSERT INTO major (dname, sid) VALUES ("Music", 9);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (9, "A", "Music", 154, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (9, "B", "Music", 342, 2);

INSERT INTO major (dname, sid) VALUES ("Music", 15);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (15, "A", "Music", 154, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (15, "B", "Music", 342, 2);

INSERT INTO major (dname, sid) VALUES ("Music", 21);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (21, "A", "Music", 342, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (21, "A", "Economics", 112, 2);

INSERT INTO major (dname, sid) VALUES ("Music", 27);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (27, "B", "Music", 342, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (27, "C", "Economics", 112, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (27, "B", "Mathematics", 224, 2);

INSERT INTO major (dname, sid) VALUES ("Physics", 5);

INSERT INTO major (dname, sid) VALUES ("Physics", 11);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (11, "A", "Physics", 221, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (11, "B", "Physics", 332, 2);

INSERT INTO major (dname, sid) VALUES ("Physics", 17);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (17, "A", "Physics", 221, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (17, "A", "Physics", 332, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (17, "C", "Computer Science", 333, 1);

INSERT INTO major (dname, sid) VALUES ("Physics", 23);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (23, "A", "Physics", 221, 1);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (23, "B", "Mathematics", 312, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (23, "B", "Mathematics", 224, 2);

INSERT INTO major (dname, sid) VALUES ("Physics", 29);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (29, "A", "Physics", 221, 1);

INSERT INTO major (dname, sid) VALUES ("Economics", 6);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (6, "A", "Economics", 112, 2);

INSERT INTO major (dname, sid) VALUES ("Economics", 12);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (12, "A", "Economics", 112, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (12, "B", "Economics", 111, 1);

INSERT INTO major (dname, sid) VALUES ("Economics", 18);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (18, "A", "Economics", 112, 2);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (18, "B", "Economics", 111, 1);

INSERT INTO major (dname, sid) VALUES ("Economics", 24);
INSERT INTO enroll (sid, grade, dname, cno, sectno) VALUES (24, "A", "Economics", 112, 1);