Question

Workshop: SQL Complex Retrieval and Aggregate Operations

The following exercises pertain to the COURSE and FACULTY tables. The FACULTY table has the following columns: FNO, FNAME, FADDR, FHIRE_DATE, FNUM_DEP, FSALARY, and FDEPT. The COURSE table has the following columns: CNO, CNAME, CDESCP, CLABFEE, CRED, and CDEPT. Both tables are owned by the user STUDENT.

1.For each department offering courses, display the name of the department and the number of courses offered by the department. The output should list the courses in alphabetic order.

2.Consider only three-credit courses. Display the name of the department and total lab fee for all courses offered by each department only if the total lab fee is less than or equal to $150.

3.Do not consider courses with a lab fee over $400. Display the name of the department and the maximum lab fee charged by the department only if that maximum exceeds $175.

4.Consider only faculty whose salary exceeds $35000.00. For each department which has such a faculty member, display the name of the department followed by the total amount paid to these members. Sort the result by total salary amounts.

Answer 1

1. SELECT CNAME, CNO FROM COURSE ORDER BY CNAME ASC;

2. SELECT CDEPT, CLABFEE FROM COURSE WHERE CLABFEE =< 150 AND CRED=3;

3. SELECT CDEPT, CLABFEE FROM COURSE WHERE MAX( CLABFEE ) >75 AND CLABFEE=400;

4.SELECT FDEPT, SUM( FSALARY ) FROM FACULTY WHERE FSALARY> 35000.00 ORDER BY FSALARY ASC;