SQL plus chapter 7

Create a table called Secretary with the attributes dcode CHAR(4) (for department code) and name VARCHAR2(20) (for the secretary name). Populate the table as follows:

Secretary dCode name

ACCT Sally

COSC Chris

ENGL Maria

a.) Create a query that lists the names of departments that have secretaries (use IN and the Secretary table in a subquery with the Department_to_major table in the outer query). Save this query as q77a.

b.)Create a query that lists the names of departments that do not have secretaries (use NOT.. IN). Save this query as q77b.

c.) Add one more row to the Secretary table that contains (This could be a situation in which we have hired Brenda but have not yet assigned her to a department.)

d.) Recall q77a and re-run it.

e.) Recall q77b and re-run it.

f.) Add the phrase WHERE dcode IS NOT NULL to the subquery in the IN and NOT..IN cases and run them again