Full Name: 
KGS CP207 Midterm - Each question is worth 10%.

1. List at least three SQL statements which cannot 
   be rolled back.

COMMIT
CREATE TABLE
DROP TABLE
ALTER TABLE
ROLLBACK

2. What subset of SQL do we use to define the database 
   schema?

DDL - DATA DEFINITION LANGUAGE

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.

CREATE TABLE PERSON (
    SSN       CHAR(9),
    FIRSTNAME VARCHAR(10),
    LASTNAME  VARCHAR(10),
    DOB       DATE,
    PRIEMP    VARCHAR(20),
    SALARY    NUMBER(8,2),
    PRIMARY KEY(SSN)
);

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.)

CREATE TABLE PHONE (
    PHONENUM   VARCHAR(30),
    SSN        CHAR(9),
    TYPE       CHAR(10),
    PRIMARY KEY(SSN,PHONENUM,TYPE)
);

5. Give each Person a 10% raise on their base salary.

UPDATE PERSON SET SALARY = SALARY * 1.1;

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.

SELECT S_FIRST, S_LAST, S_DOB
FROM STUDENT
WHERE S_CLASS = 'JR' OR S_CLASS = 'SR';

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.

SELECT S_ID, C_SEC_ID, GRADE 
FROM ENROLLMENT
WHERE C_SEC_ID = 1000
ORDER BY GRADE;

8. Write a query to calculate the total number of 
   students taught by John Blanchard during the 
   Spring 2004 term.

SELECT COUNT(S.S_ID)
FROM 
    FACULTY F, 
    COURSE_SECTION CS, 
    TERM T, 
    ENROLLMENT E,
    STUDENT S
WHERE F.F_FIRST = 'JOHN' AND
F.F_LAST = 'BLACHARD' AND
CS.F_ID = F.F_ID AND
T.TERM_ID = CS.TERM_ID AND
T.TERM_DESC = 'SPRING 2004' AND
E.C_SEC_ID = CS.C_SEC_ID AND
E.S_ID = S.S_ID;

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).

(SELECT L.BLDG_CODE, L.ROOM
FROM LOCATION L, FACULTY F
WHERE F.LOC_ID = L.LOC_ID)
UNION
(SELECT L.BLDG_CODE, L.ROOM
FROM LOCATION L, COURSE_SECTION CS, TERM T
WHERE L.LOC_ID = CS.LOC_ID AND
CS.TERM_ID = T.TERM_ID
T.TERM_DESC = 'SUMMER 2004');

10. Write a query that retrieves a list of students 
   who have never been taught by John Blanchard.

TIP: GET ALL STUDENTS... SUBTRACT (MINUS) ALL 
     THE STUDENTS TAUGHT BY JOHN BLANCHARD

(SELECT S_ID, S_FIRST, S_LAST
FROM STUDENT)
MINUS
(SELECT S.S_ID, S.S_FIRST, S.S_LAST
 FROM 
    FACULTY F, 
    COURSE_SECTION CS, 
    ENROLLMENT E,
    STUDENT S
WHERE F.F_FIRST = 'JOHN' AND
F.F_LAST = 'BLACHARD' AND
CS.F_ID = F.F_ID AND
E.C_SEC_ID = CS.C_SEC_ID AND
E.S_ID = S.S_ID);