Full Name: KGS CP207 Midterm - Each question is worth 10%. 1. List at least three SQL statements which cannot be rolled back. 2. What subset of SQL do we use to define the database schema? In the next three questions, please ensure that all constraints (primary and foreign keys are properly set/preserved). 3. Write SQL to create a Person table that has a first name, a last name, social security number, a date of birth, primary employer, and a base salary. 4. Write SQL to create a Phone table that has a phone number, a person id to whom it belongs, and the type of phone (fax, mobile, home, office, etc.) 5. Give each Person a 10% raise on their base salary. Please refer to the Northwoods University Student Registration Database mentioned in your book. Briefly, the database is this: location(loc_id, bldg_code, room, capacity); faculty(f_id,f_last,f_first,f_mi,loc_id,f_phone, f_rank,f_pin,f_image); student(s_id,s_last,s_first,s_mi,s_add,s_city,s_state, s_zip,s_phone,s_class,s_dob,s_pin,f_id); term(term_id,term_desc,status); course(course_id,call_id,course_name,credits); course_section(c_sec_id,course_id,,term_id,sec_num, f_id,day,time,loc_id,max_enrl); enrollment(s_id,c_sec_id,grade); There is also an online copy (available for printing) of the schema for this database (northwoods.sql) 6. Write a query that returns student's full name and their date of birth who are either juniors or seniors. 7. Write a query that returns student id, course section id, and grade for section id 1000. Order the records by grade, showing the A's first, then the B's, etc. 8. Write a query to calculate the total number of students taught by John Blanchard during the Spring 2004 term. 9. Write a query that lists the building code and room of every room that is either currently in use as a facutly office or in use as a classroom during the Summer 2004 term. (Hint: You MAY use the UNION). 10. Write a query that retrieves a list of students who have never been taught by John Blanchard.