Comp 353 midterm study guide
The primary databases for the exam problems will be the COMPANY and UNIVERSITY databases from E&N that we have used in the
past.
Topics
Chapter 3:
- relational model; basics of tables and databases
- constraints (§3.2), including keys, foreign keys, and not null
- insert, delete, and update (§3.3)
Chapter 4:
- CREATE TABLE
- SELECT ... FROM ... WHERE
- joins
- insert, delete, and update again
Chapter 5: §5.1, §5.2
- NULL logic
- nested queries and correlated queries
- outer joins
- GROUP BY and HAVING
Chapter 6 on relational algebra will not be on the exam.
Chapter 7:
Chapter 9:
- ER-to-relation mapping (§9.1)
You will not have to know about string or date manipulation for the exam.
Sample problems
Review the two SQL assignments; that material
(except for the part on views) will be the most important part of the
exam. Those assignments covered the material on their respective
chapters, chapter 4 and chapter 5.
Chapter 7 (creating ER diagrams)
Chapter 9:
- 9.4 (note that the defining relationships for the weak entity PORT are those to the right,
not those above. I would not have made PORT a weak entity; I would have
made it an entity with attributes {country, state, city, body_of_water})
- 9.5 (as necessary)
Additional exercises.
Answers are now here.
In the SQL demo examples below,
1. Describe in English what is being asked for in example 8.
2. Give an alternative solution for example 8 that does not involve an inner query.
3. In English, who is getting the raise in example 5? Is this fair?
4. Write query 7a using an inner query instead of a join.
5. Write insert statements so the following query returns (2, 30000, 40000):
select count(*), avg(e.salary), max(e.salary) from employee e
where e.dno = 5;
6. Write a query to print the number of employees, average salary and
max salary for all departments with an average salary >= 30000.
SQL demo examples
You will be provided with these on the exam.
1. create table employee2 (
fname varchar(15) not null,
lname varchar(15) not null,
ssn char(9) not null,
salary decimal(10,2),
super_ssn char(9),
dno int not null,
primary key (ssn),
foreign key (super_ssn) references employee(ssn),
foreign key (dno) references department(dnumber)
);
2. create table department2 (
dname
varchar(15) not null,
dnumber
int
not null,
mgr_ssn
char(9) not null,
primary key (dnumber),
foreign key (mgr_ssn) references employee(ssn)
);
3. insert into employee2 values
('peter', 'dordal', '123456789', 29000.01, '012345678', 55);
4. delete from employee2 where fname='peter';
5. update employee2 set salary = 1.10 * salary where salary >= 50000;
6. select e.lname from employee2 e where e.dno = 5;
7a. select e.lname from employee2 e, department2 d
where e.dno = d.dnumber and d.dname="maintenance";
Same as previous:
7b. select e.lname from employee2 e join department2 d on e.dno = d.dnumber
where d.dname="maintenance";
8. select e.lname from employee2 e
where e.salary in (select e.salary from employee2 e where e.dno = 5);