GEIT 3341 Database I

Lab 2

Objective(s)	Target CLO(s)	Reference
To practice some SQL DML commands by writing and executing SELECT SQL queries.	6	Chapter 6

Instruction:

Save this file as Lab2_YOURID_SectionID and for each query below, express the query in SQL, run it, and include the output. That is, for each query, include the query as expressed in SQL and the output generated from running it as shown in the example below. When no specific columns are specified, list all columns.

Example: Select the employee first name, last name, and address of all employees in

department 5.

Query in SQL:

SELECT Fname,Lname,Address

FROM EMPLOYEE

WHERE Dno=5;

Query Output:

FNAME LNAME ADDRESS

------------------------------ ------------------------------ ------------------------------

Franklin Wong 638 Voss,Houston,TX

John Smith 731 Fondren,Houston,TX

Ramesh Narayan 975 Fire Oak,Humble,TX

Joyce English 5631 Rice,Houston,TX

Important Note: Your queries should always work without modification if the underlying

data is changed via insert/update/delete operations.

1. Select all rows in the table DEPT_LOCATIONS. (0.5 point)
2. Select first name, middle initial and last name of all employees

supervised by the employee whose Social Security Number (SSN) is 333445555. (0.5 point)

3. Select the first name and salary of all female employees that work in department 5. (0.5 point)
4. Select project name and project number of all projects controlled by department 5. (0.5 point)
5. Select the department name and project name managed by the department. (1 point)
6. Use the LIKE operator and write a query to show the dependents whose name starts with the letter A (include all columns). (1 point)
7. Produce a relation showing the employee first name, name of his/her dependent, and relationship to the employee. (1 point)
8. Find the first and last name of all employees whose salary is above the average. (1 point)
9. Find the first and last name of all employees with no dependents. (1 point)
10. Produce a relation showing the department number and how many locations in each department ordered by the number of locations in descending order. Hint: you must use GROUP BY. (1.25 point)
11. For employees with dependents, produce a relation showing the first and last name of the employee, and the number of dependents he/she has. That is, your query should display this output: (1.25 point)

FNAME LNAME Number of Dependents

------------------------------ ------------------------------ --------------------

Franklin Wong 3

John Smith 3

Jennifer Wallace 1

12. Modify the previous query to only show employees whose number of dependents is greater than 1. That is, your query should display the following output: (0.5 point)

FNAME LNAME Number of Dependents

--------------------------------------------------------------------------------

Franklin Wong 3

John Smith 3