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

EN6 Chapter 3 / EN7 Chapter 5:

relational model; basics of tables and databases
constraints (§3.2), including keys, foreign keys, and not null
insert, delete, and update (§3.3)

EN6 Chapter 4 / EN7 Chapter 6

CREATE TABLE
SELECT ... FROM ... WHERE
joins
insert, delete, and update again

EN6 Chapter 5 / EN7 Chapter 7: §1, §2

NULL logic
nested queries and correlated queries
outer joins
GROUP BY and HAVING

Chapter 6/8 on relational algebra will not be on the exam.

EN6 Chapter 7 / EN7 Chapter 3:

entities
relationships

EN6 Chapter 8 / EN7 Chapter 4, on Extended ER diagrams, will not be on the exam.

Chapter 9 (EN6 or EN7)

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.

EN6 Chapter 7 / EN7 Chapter 3 (creating ER diagrams)

exercise 16
exercise 22

EN6/EN7 Chapter 9

exercise 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})
exercise 5 (as necessary)

Additional exercises

Solutions will appear in the Resources folder on Sakai.

In the SQL demo examples on this page,

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.