Comp 353 midterm study guide

The midterm will be Tuesday, March 22. There will be some lecture for the first part of class, and then the exam will begin.

The primary databases for the exam problems will be the COMPANY [OFFICE] and STUDENT 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:

entities
relationships

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)

7.16
7.22

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:

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);