COEN 178 Intro to Database Systems  Lab 3

COEN 178 Intro to Database Systems 
Lab 3 (100 pts)
Objectives: Learn
• SQL queries using more than one table (joining tables).
• Aggregate functions
Part 1 (50 pts)
Create a folder structure called COEN178\labs\lab3.
Create two tables and load them and load them with values given.
Creating Tables
Create the tables, L_EMP and L_DEPT using the DDL statements below:
Create table L_EMP (empNo Integer Primary Key, empname
VARCHAR(10),deptId VARCHAR(5));
Create table L_DEPT (deptId VARCHAR(5) Primary Key, deptname
VARCHAR(10),budget NUMBER(10,2));
Note: We have not defined any foreign key constraint in these tables.
Inserting Tuples
Add the following tuples into the tables (use a script file to add the data).
insert into L_EMP values(1,'smith','d1');
insert into L_EMP values(2,'jones','d2');
insert into L_EMP values(3,'wayne','d1');
insert into L_EMP values(4,'moor','d3');
insert into L_EMP values(5,'king','d1');
insert into L_EMP values(6,'chen','d1');
insert into L_EMP values(7,'winger','d3');
insert into L_DEPT values('d1','Research',200000);
insert into L_DEPT values('d2','Devt',600000);
insert into L_DEPT values('d3','Testing',100000);
insert into L_DEPT values('d4','Advert',700000);
insert into L_DEPT values('d9','HR',700000);
Before you do the exercises, you want to show the data in your files.
Write and execute the queries as below:
a) Select * from L_EMP;
b) Select * from L_Dept;
Exericse 1 (5 pts)
The query below is an attempt to show the empno and names of employees who work in the
dept with name “Research”. Since the deptname is in L_Dept table, we have to join L_EMP and
L_Dept tables.
Select empno,empname
from L_EMP,L_Dept
Where deptname='Research';
What are the results of the query? Are the results correct?
Exercise 2 (5 pts)
We will write the SQL query correctly.
Select empno,empname
from L_EMP,L_Dept
Where L_EMP.deptid = L_Dept.deptid and deptname='Research';
Do you see the correct results?
The part that is highlighted in the query is called the bridge condition that connects the two
tables we are using.
Exercise 3 (5 pts)
We will write the query differently, using a query and a subquery.
Select empno,empname
from L_EMP
Where L_EMP.deptid = (Select deptid from L_Dept where
deptname='Research');
Run the query. Do you see correct results?
This query works correctly because there is a single value that is returned from the subquery. If
multiple values are returned from the subquery, we must use a “in” clause as is shown in the
next exercises.
Exercise 4 (5 pts)
We want to find the empnos and names of employees who work in depts with budget >
100000. Let us write the SQL query similar to query in exercise 3.
Select empno,empname
from L_EMP
Where L_EMP.deptid = ( Select deptid from L_Dept where budget >
100000);
Run the query. Did it work?
If it did not, give your reasons. ---
Exercise 5 (5 pts)
Let us write the SQL query correctly.
Select empno,empname
from L_EMP
Where L_EMP.deptid in (Select deptid from L_Dept where budget >
100000);
Run the query. Did it work? Did it show correct results?
In exercises 6-10, we will use SQL aggregate functions .
Exercise 6 (5 pts)
Write a SQL query to show the dept.names with maximum budget (budget >= budgets of
every other dept). This can be written using a subquery. Complete the SQL query.
--- where budget --(Select max(budget) from L_Dept);
Exercise 7 (5 pts)
The query in 6 can be written without using an aggregate function. Here, we compare budget
of each dept. with budget of every other dept. We choose the budget that is >= all the
budgets of depts. Complete the query.
--- where budget >= all(Select budget from L_Dept);
Exercise 8 (5 pts)
In this exercise, we will find the no. of depts with budget > 100000. We have to use the
aggregate function count. Complete the query and run it.
Select count(*) LargeBudgetDepts from L_Dept
Where ------;
Exercise 9 (5 pts)
We will find the no. of depts with budget > 100000, grouped by budget amount.
Select budget Budget, count(*) HowMany from L_Dept
where budget >100000
Group by (budget);
Run the query and check the output.
Insert at least 4 rows into L_Dept with values of your choice (give some budgets > 10000). Run
this query again and check if the results are correct.
Exercise 10 (5 pts)
In this query, we will find find the no. of depts with budget > 100000, grouped by budget
amount and where a group has 2 or more dept. In this query, we will use an aggregate
function (count), group by and having.
Note: A HAVING clause is like a WHERE clause that can operate on the outputs of aggregate
functions.
Select budget Budget, count(*) EqualOrGreaterThan2 from L_Dept
where budget >100000
Group by (budget)
having count(*) >= 2;
Run the query and check the output.
Part 2 (50 pts)
In this part, you will use Staff table with the data you have loaded in Lab1.
The exercises 11-15 require SQL queries similar to the ones you have executed in Part1.
Exercise 11 (10 pts)
Write a SQL query to show the full names of employees with maximum salary.
Exercise 12 (10 pts)
Using the query below, find the last names of people with the same salary as “Zichal”.
Select last, salary
from Staff
where salary = (select salary from Staff where last = 'Zichal');
a) Rewrite and run the query so that the last name comparison will work (), whether it is stored
in uppercase, lowercase or mixed case.
b) Substitute the last name “Young” for “Zichal” and run the query again. Did it work? If it did
not work, why?
Fix the query (do NOT change the subquery’s where clause beyond the initial change to ‘Young’)
and re run the query.
What is the output?
Exercise 13 (10 pts)
Write a SQL query to find the number of people with salaries greater than 100,000 and
grouped by a salary number. See the example output below (the count may vary for your table).
SALARY SALARIES_100K_ABOVE
-------- -------------------
140000 2
120000 8
105211 1
179700 2
150000 3
110000 2
102829 1
144868 1
107770 1
Exercise 14 (10 pts)
Write and run a query to find the number of people with salaries greater than 100,000,
grouped by a salary number, where the no. of people in the group is >= 10. See the example
output below:
SALARY SALARIES_100K_ABOVE
---------- -------------------
130500 26
172200 23
Exercise 15 (10 pts)
Write and run a query to find the deptid of the department with maximum number of employees.