Midterm study guide - selected solutions


Chapter 6
#3: count the total number of rooms in LOCATION (Indo-US College DB)

    count * from LOCATION

#4  count the number of distinct buildings:
Here's one way to do it:
    select count (*) from
        (select distinct building from location);

#6 find the average, highest, and lowest age for NN employees

    select avg(salary), max(salary), min(salary) from EMPLOYEE;

#7 display the total number of dependents for each NN employee, for employees who have at least two dependents

    select e.employeeID, count(*)
     from Employee e, dependent d
    where e.employeeid = d.employeeid
    group by e.employeeid
    having count(*) >= 2;

#9 find the average employee commission
(a) ignoring nulls
(b) not ignoring nulls

(a): select avg(commission) from employee;
(This just ignores rows with nulls)

To do b, we need to create a computed column that replaces the commission null values with zeroes.
The NVL function (p 141) does exactly this.
(b): select avg(nvl(commission, 0)) from employee;

#10 find the sum of maximum count by term by course

    I can't figure out what he's asking for here; sorry.

#13 count number of faculty members by each department

    Select  deptid, count(*) from faculty
    group by deptID;

---

Chapter 7

1b: find the name of the supervisor for employee 433 (self-join example)

Select s.lname, s.fname from employee s, employee e
where e.employeeid = 433 and s.employeeid = e.supervisor;

1d. Find each employee's salary information and level (note level must be computed from salary)
This is a non-equijoin example:

    select e.lname, e.salary, l.levelno  from employee e, emplevel l
    where e.salary BETWEEN l.lowsalary AND l.highsalary;

1f: Employees in sales:
Here's how to do it if you don't want to look up the deptid for "Sales"
    select e.lname, e.fname from employee e, dept d
    where e.deptid = d.deptid and lower (d.deptname) = 'sales';

1i: Who works in John Smith's dept?
Note how we do a self-join here:
    select e.lname, e.fname from employee e, employee js
    where lower(js.lname) = 'smith' and lower(js.fname) = 'john' and e.deptid = js.deptid;

2a student + major description

      select s.first, s.last, m.majordesc from student s, major m
    where s.majorid = m.majorid;

2b students who got grade of F in WN03:

    select s.first, s.last from student s, crssection c, registration r
    where s.studentid = r.studentid and c.csid = r.csid
    and c.termid = 'WN03' and r.final = 'F';

2d  Spring 2003 sections with instructor, or blank if no instructor is assigned
Note the magic (+) (this will NOT be on the exam)
    SELECT c.csid, c.courseid, c.day, c.starttime, f.name from crssection c, faculty f
    where c.facultyid = f.facultyid(+);

2e  Display course titles along with prereqs. Display courses without prereqs as well.
At first this looks like another mysterious outer-join problem, but it's not:

    select c.title, c.prereq from course c;

---

Chapter 8

2a: select jose diaz's advisor's name and number:

select f.name, f.phone from faculty f where f.facultyid =
    (select facultyid from student where lower(last) = 'diaz' and lower(first) = 'jose');

2b. Find all rooms with the bottom-two (two smallest) capacities; don't include offices.
The two-smallest sizes are in fact 30 and 35. We find the list of those in the innermost SELECT,
take the two smallest in the middle SELECT, and apply <=ANY in the outermost SELECT.

select * from location where roomtype != 'O' and capacity <= ANY
    (select capacity from
    (select capacity from location where roomtype != 'O' group by capacity order by capacity)
where rownum <= 2);

Don't worry about the table-creation problems.

g. Faculty who do not teach any course in SP03
The inner query will be courses the faculty member DOES teach; it should NOT EXIST.

SELECT f.name from faculty f
WHERE NOT EXISTS (
    select * from crssection c where c.facultyid = f.facultyid and c.termid = 'SP03'
);