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