IT 468
/*

SQL 2

Student Name:

Write the SQL statements that perform the following operations. Submit the SQL statements in this SQL file.

*************

Q1. Write a SELECT statement that will list the names of any employee that has a dependent. Use a subquery in your statement, but do not use a correlated subquery. In other words, no attribute from the table in the outer query should be used in the inner query.

This will return 3 rows.

SQL Statement: */

/*

Q2. Write a SELECT statement that will list the names of any employee that doesn’t have a dependent. Use a subquery in your statement, but do not use a correlated subquery. In other words, no attribute from the table in the outer query should be used in the inner query.

This will return 5 rows.

SQL Statement: */

/*

Q3. For any employee with a dependent, write a SELECT statement that will list the employee’s name and the dependent’s name. (If the employee has more than one dependent, the employee’s name will appear on every line.) Do not use a subquery in your statement.

This will return 7 rows.

SQL Statement: */

/*

Q4. Write a SELECT statement that will list the names of the employees who have worked on project number 2. Do not use a subquery in your statement.

This will return 3 rows.

SQL Statement: */

/*

Q5. Write a SELECT statement that will list the names of the employees who have worked on the project named Computerization (NOT the project number = 10). Do not use a subquery in your statement.

This will return 3 rows.

SQL Statement: */

/*

Q6. For each employee, write a SELECT statement that will list their name, the name of any project they have worked on, and the number of hours they worked on each project. Do not use a subquery in your statement.

This will return 16 rows.

SQL Statement: */

/*

Q7. Write a SELECT statement that will list each department name and the average salary of the employees in the department. Do not use a subquery in your statement.

This will return 3 rows.

SQL Statement: */

/*

Q8. Write a SELECT statement that will list the name of each employee and their supervisor. Do not use a subquery in your statement. The columns should be labeled as Employee First Name, Employee Last Name, Supervisor First Name, and Supervisor Last Name.

This will return 7 rows.

Hint: You will need to join the employee table to itself. You will need to use a different alias for each copy of the employee table…one alias for the employee copy of the table…one alias for the supervisor copy of the table.