Part 1
Using the tables created in week 2:
Question ( 4 pts per question) SQL Statement or Answer
a. Using the EMP and DEPT tables, show a list of all department names and the names of all employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join.
b. Using the EMP and DEPT tables, select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join.
Using the student schema:
Question ( 3 pts per question) SQL Statement or Answer
c. Write a query that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax (JOIN ON).
d. Write a query that performs an inner join of the grade, student, and grade_type tables using any Oracle inner join convention.
e. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a RELATIONAL SET OPERATOR.
f. Write a SQL statement using a RELATIONAL SET OPERATOR to show which students enrolled in a section that are not enrolled in any classes. Exclude students with a student id less than 300.