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.
The exam will be closed-note/closed-book, but I will give you a
sheet of examples, probably this. I'll also
give you a diagram of the Company database, eg this.
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 8 on Extended ER diagrams will not be on the exam.
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 to these are now here.
In the SQL demo examples below,
1. Describe in English what is being asked for in demo 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 employee2 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. Also include
the department number.
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);