Comp 305 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 SQL 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.
Indexes will not be on 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
Solutions are 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.