SQL assignment 2

Originally from EN6 chapter 5 / EN7 chapter 7

RULES:

0. Submit as a text file! (see also Rule 4)

1. Your queries should work even if additional data is added to the tables.

2. Every table referred to in the FROM clause should be named with a "table alias", that is e and d in:
    select e.lname, d.dname from employee e, department d ...

3. All answers should be in the form of a single query; do not retrieve a value with one query and then manually plug that value into a second query. Similarly, data appearing in the query should be from the exercise itself, and not from a "visual" lookup.

4. All SQL should be entered in a format that I can copy and paste directly into a command window. In particular, make sure:

    All "prompt" characters such as "->" have been stripped out.
    You use regular quotation marks, not special unicode quotation marks. Unicode-quoted ‛foo’ is not the same as 'foo'. (A sure way to convert inadvertently to unicode quotes is to paste your work into a Word document; try installing Notepad++. Or, better yet, Atom.) Postgres requires single quotation marks for strings: 'string'.
    There are no leading tabs or spaces 

5. Use explicit join notation for joins 
	select e.lname from EMPLOYEE E JOIN DEPARTMENT D ON E.DNO = D.DNUMBER where...

========================

5: Specify the following queries on the COMPANY database in SQL. Give the query results.

a. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.

b. Suppose that we want the dept name and the number of *male* employees, in each department for which the average departmental salary is more than $30,000 (rather than the number of *all* employees, as in part a). Give an SQL query. 

Warning: make sure you don't take the average salary of the male employees only! You will probably need a nested query.

========================

6: Specify the following queries in SQL for the UNIVERSITY database. For parts (a) and (b), an A- is *not* the same as an A.

a. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses; that is, students who do NOT have a grade that is <> 'A').

b. Retrieve the names and major departments of all students who do not have a grade of A in any of their courses (students for whom there does NOT EXIST a grade = 'A').

c. For each section taught by Professor King, list the course number and the number of students who took the section.

========================

7: In SQL, specify the following queries on the COMPANY database using nested queries where appropriate.

a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.

b. Retrieve the names of all employees whose supervisor's supervisor has '888665555' for SSN .

c. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.

========================

8: Specify the following views in SQL on the COMPANY database schema. Remember that a view is wrapped around an underlying select statement: create view foo as select ....

a. A view that has the department name, manager name, and manager salary for every department.

b. A view that has the employee name, supervisor name, and employee salary for each employee who works in the 'Research' department.

c. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it.