7 short labs in Database

GEIT 3341 DATABASE I LAB 4

GEIT 3341 Database I

Lab 4

Writing & Executing SQL Queries

Due Date:

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

queries.

Name

ID

Section

Instruction:

Save this file as Lab4_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:

GEIT 3341 DATABASE I LAB 4

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 ordered by the number of

dependents in descending order. That is, your query should display this output: (1.25 point)

GEIT 3341 DATABASE I LAB 4

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

----------------------------------------------------------------------------------------- John Smith 3

Franklin Wong 3