Write SQL queries to retrieve data from the specified database. Use the Northwoods University database by running the script file (Northwoods_mySQL_working.sql)

Study the metadata carefully before you start writing the query. Queries:

1) Retrieve the first and last name values for all faculty who are either full professors or instructors.

2) * Write the above query in another way.

3) Retrieve the different classes of students. No duplicates allowed.

4) Retrieve the first and last names and DOB values for all students who were born in 1985.

5) * Write the above query in another way.

6) Retrieve all students who have a middle initial.

7) Retrieve all enrollment records of students who do NOT have a grade yet.

8) Retrieve the faculty IDs who are advisors. No duplicates, please

9) * Write the above query in another way.

10)Retrieve the list of location IDs that are used as faculty offices. (ie loc_id appears in the faculty table). No duplicates, please...

11)Retrieve the list of location IDs that are used as classrooms. (ie loc_id appears in the course_section table). No duplicates, please

12)Retrieve the course section ID of those sections that do NOT meet on Wednesdays. FYI - Do not work off the raw data from the table. You can only use the information from the question in the query. You could, for example, have an entry in the course sections c_sec_day for MTR or TF, or RF, WF, MF etc in the future.

13)Retrieve the course section ID of those sections that do NOT meet on Wednesdays or Fridays.

14)Retrieve the average maximum enrollment (max_enrl column on course_section) for all sections of course ID 1 during term 4.

15)Retrieve the number of B's assigned for course section 6.

16) Retrieve the faculty IDs of those advisors along with the number of students they are advising. Can this query be written in two ways? If yes, show the way . If no, explain why not possible?

17)Retrieve all records from the enrollment table where the grade is either C or not assigned yet.

18)Retrieve the number of course sections scheduled in each location sorted from high to low.

19) Modify the above query to show all location(s) that have more than three (3) course sections scheduled in it.

FILE IS HERE :

/* Northwoods_mySQL.sql

Create the Northwoods University database.

*/

/* Drop all tables if they already exist. */ DROP TABLE IF EXISTS enrollment; DROP TABLE IF EXISTS course_section; DROP TABLE IF EXISTS term; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS faculty; DROP TABLE IF EXISTS location;

/* Create the tables. */ CREATE TABLE location ( loc_id NUMERIC(6) NOT NULL, bldg_code VARCHAR(10), room VARCHAR(6), capacity NUMERIC(5), PRIMARY KEY (loc_id) ) ENGINE InnoDB;

CREATE TABLE faculty ( f_id NUMERIC(6) NOT NULL, f_last VARCHAR(30), f_first VARCHAR(30), f_mi CHAR(1), loc_id NUMERIC(5) NOT NULL, f_phone VARCHAR(10), f_rank VARCHAR(8), f_pin NUMERIC(4), f_image VARCHAR(20), PRIMARY KEY(f_id), INDEX (loc_id), FOREIGN KEY (loc_id) REFERENCES location(loc_id) ) ENGINE InnoDB;

CREATE TABLE student ( s_id NUMERIC(6) NOT NULL, s_last VARCHAR(30), s_first VARCHAR(30), s_mi CHAR(1), s_address VARCHAR(25), s_city VARCHAR(20), s_state CHAR(2), s_zip VARCHAR(10), s_phone VARCHAR(10), s_class CHAR(2), s_dob DATE, s_pin NUMERIC(4), f_id NUMERIC(6), time_enrolled VARCHAR(10), PRIMARY KEY (s_id), INDEX (f_id), FOREIGN KEY (f_id) REFERENCES faculty(f_id) ) ENGINE InnoDB;

CREATE TABLE term ( term_id NUMERIC(6) NOT NULL, term_desc VARCHAR(20), status VARCHAR(20), PRIMARY KEY (term_id) ) ENGINE InnoDB;

CREATE TABLE course ( course_id NUMERIC(6) NOT NULL, call_id VARCHAR(10), course_name VARCHAR(25), credits NUMERIC(2), PRIMARY KEY(course_id) ) ENGINE InnoDB;

CREATE TABLE course_section ( c_sec_id NUMERIC(6) NOT NULL, course_id NUMERIC(6) NOT NULL, term_id NUMERIC(6) NOT NULL, sec_num NUMERIC(2) NOT NULL, f_id NUMERIC(6), c_sec_day VARCHAR(10), c_sec_time DATE, c_sec_duration VARCHAR(20), loc_id NUMERIC(6), max_enrl NUMERIC(4) NOT NULL, PRIMARY KEY (c_sec_id), INDEX (course_id), FOREIGN KEY (course_id) REFERENCES course(course_id), INDEX (loc_id), FOREIGN KEY (loc_id) REFERENCES location(loc_id), INDEX (term_id), FOREIGN KEY (term_id) REFERENCES term(term_id), INDEX (f_id), FOREIGN KEY (f_id) REFERENCES faculty(f_id) ) ENGINE InnoDB;

CREATE TABLE enrollment ( s_id NUMERIC(6) NOT NULL, c_sec_id NUMERIC(6) NOT NULL, grade CHAR(1), PRIMARY KEY (s_id, c_sec_id), INDEX (s_id), FOREIGN KEY (s_id) REFERENCES student(s_id), INDEX (c_sec_id), FOREIGN KEY (c_sec_id) REFERENCES course_section (c_sec_id) ) ENGINE InnoDB;

-- inserting into LOCATION table INSERT INTO location VALUES (1, 'CR', '101', 150), (2, 'CR', '202', 40), (3, 'CR', '103', 35), (4, 'CR', '105', 35), (5, 'BUS', '105', 42), (6, 'BUS', '404', 35), (7, 'BUS', '421', 35), (8, 'BUS', '211', 55), (9, 'BUS', '424', 1), (10, 'BUS', '402', 1), (11, 'BUS', '433', 1), (12, 'LIB', '217', 2), (13 , 'LIB', '222', 1);

-- inserting records into FACULTY INSERT INTO faculty VALUES (1, 'Cox', 'Kim', 'J', 9, '7155551234', 'ASSO', 1181, NULL), (2, 'Blanchard', 'John', 'R', 10, '7155559087', 'FULL', 1075, NULL), (3, 'Williams', 'Jerry', 'F', 12, '7155555412', 'ASST', 8531, NULL), (4, 'Sheng', 'Laura', 'M', 11, '7155556409', 'INST', 1690, NULL), (5, 'Brown', 'Philip', 'E', 13, '7155556082', 'ASSO', 9899, NULL);

-- inserting records into STUDENT INSERT INTO student VALUES (1, 'Miller', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire', 'WI', '54703', '7155559876', 'SR', '1985/7/14', 8891, 1, '3-2'), (2, 'Umato', 'Brian', 'D', '454 St. John''s Place', 'Eau Claire', 'WI', '54702', '7155552345', 'SR', '1985/8/19', 1230, 1, '4-2'), (3, 'Black', 'Daniel', NULL, '8921 Circle Drive', 'Bloomer', 'WI', '54715', '7155553907', 'JR', '1982/10/10', 1613, 1, '3-0') , (4, 'Mobley', 'Amanda', 'J', '1716 Summit St.', 'Eau Claire', 'WI', '54703', '7155556902', 'SO', '1986/9/24', 1841, 2, '2-2'), (5, 'Sanchez', 'Ruben', 'R', '1780 Samantha Court', 'Eau Claire', 'WI', '54701', '7155558899', 'SO', '1986/11/20', 4420, 4, '1-11' ), (6, 'Connoly', 'Michael', 'S', '1818 Silver Street', 'Elk Mound', 'WI', '54712', '7155554944', 'FR', '1986/4/12', 9188, 3, '0-4');

-- inserting records into TERM INSERT INTO term VALUES (1, 'Fall 2005', 'CLOSED'), (2, 'Spring 2006', 'CLOSED'), (3, 'Summer 2006', 'CLOSED'), (4, 'Fall 2006', 'CLOSED'), (5, 'Spring 2007', 'CLOSED'), (6, 'Summer 2007', 'OPEN');

-- inserting records into COURSE INSERT INTO course VALUES (1, 'MIS 101', 'Intro. to Info. Systems', 3), (2, 'MIS 301', 'Systems Analysis', 4), (3, 'MIS 441', 'Database Management', 4), (4, 'CS 155', 'Programming in C++', 3), (5, 'MIS 451', 'Web-Based Systems', 2);

-- new row to have double quotation INSERT INTO course VALUES (6, 'CS 451', 'Programmer''s Approach', 3);

-- inserting records into COURSE_SECTION INSERT INTO course_section VALUES (1, 1, 4, 1, 3, 'MWF', '0000-00-00', '1800', 1, 140), (2, 1, 4, 2, 3, 'TR', '0000-00-00','0 00:01:15.00', 7, 35), (3, 1, 4, 3, 3, 'MWF', '0000-00-00', '0 00:00:50.00', 2, 35), (4, 2, 4, 1, 4, 'TR', '0000-00-00', '0 00:01:15.00', 6, 35), (5, 2, 5, 2, 4, 'TR', '0000-00-00', '0 00:01:15.00', 6, 35), (6, 3, 5, 1, 1, 'MWF', '0000-00-00','0 00:00:50.00', 5, 30), (7, 3, 5, 2, 1, 'MWF', '0000-00-00','0 00:00:50.00', 5, 30), (8, 4, 5, 1, 5, 'TR', '0000-00-00', '0 00:01:15.00', 3, 35), (9, 5, 5, 1, 2, 'MWF', '0000-00-00','0 00:00:50.00', 5, 35), (10, 5, 5, 2, 2, 'MWF', '0000-00-00','0 00:00:50.00', 5, 35), (11, 1, 6, 1, 1, 'MTWRF', '0000-00-00','0 00:01:30.00', 1, 50), (12, 2, 6, 1, 2, 'MTWRF', '0000-00-00','0 00:01:30.00', 6, 35), (13, 3, 6, 1, 3, 'MTWRF', '0000-00-00','0 00:01:30.00', 5, 35);

-- inserting records into ENROLLMENT

INSERT INTO enrollment VALUES (1, 1, 'A'), (1, 4, 'A'), (1, 6, 'B'), (1, 9, 'B'), (2, 1, 'C'), (2, 5, 'B'), (2, 6, 'A'), (2, 9, 'B'), (3, 1, 'F'), (3, 12, NULL), (3, 13, NULL), (4, 11, NULL), (4, 12, NULL), (5, 1, 'B'), (5, 5, 'C'), (5, 9, 'C'), (5, 11, NULL), (5,13, NULL), (6, 11, NULL), (6, 12, NULL);