Comp 353 midterm study guide

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

Here are some examples of SQL statements that you will be given on the exam. I will extend this list if other types of statements (eg UPDATE) are needed. I've not included examples of GROUP BY or nested, noncorrelated subqueries, but you may still need to know about these.

CREATE TABLE FOO (
    NAME	varchar(20)  not null,
    PHONE   varchar(12),
    SALARY  number(9,2),
    VCOUNT  number
);

-- examples relating to Supplementary Examples section
-- 218.8
SELECT itemname, itemprice
FROM item
WHERE itemprice >= 5;

-- invoices by name, state, date
SELECT c.custname, c.state, i.invdate, i.invno
FROM customer c, invoice i
WHERE  c.custNo = i.custNo
ORDER BY c.state;

-- customers who have ordered something (can you do this as a join?)

SELECT c.custname, c.state
FROM customer c
WHERE EXISTS (
    SELECT * FROM invoice i
    WHERE c.custno = i.custno
);

The primary database for the exam problems will be that from the SQL Review: Supplementary Examples section of the book, which follows chapter 9. I strongly recommend reviewing the SELECT examples in that chapter.

The database itself used in the Supplementary Examples is here.

Topics

The exam will cover material up through chapter 8. Important topics are:

basics of tables and databases
primary keys
foreign keys
other constraints: not null, check, unique (alternative key)
joins: 2-way, 3-way, and more
normalization
Entity-Relationship diagrams
SQL
- select
- joins
- group by / having / group functions
- multiple queries joined by SET operations
- scalar subqueries
- list subqueries
- correlated subqueries

You will not need to know the built-in functions of chapter 6, except for the "grouping" functions SUM(), COUNT(), MAX(), AVG(), MIN(). You will not have to know about string or date manipulation for the exam. And you will not have to know about

Sample problems

Partial book exercise solutions are here.

Here are some more substantial problems; you might also consider some of the "Exercise Questions" at the chapter ends of the book. I recommend against the True-False questions, as I find them confusing.

E-R Diagrams: page 36, "E-R Diagram Exercise"

Dependency: page 36, "Normalization Exercise". The preceding "Dependency Diagram Exercise" is optional. You will not have to do the normalization in the two-step 1NF-2NF and then 2NF-3NF form; you can convert directly to 3NF (as we did informally in one example done in class).

Foreign (and Primary) Keys: review Programming Assignment 1. You will likely be given a question along these lines, and will be provided with a sample CREATE TABLE statement that contains no key specifications.

SQL chapter 5: see the LAB ACTIVITY on pp 130-131

SQL chapter 6: p 155, problems 3, 4, 6, 7, 9, 10, 13

SQL chapter 7: p 172. Be sure you're comfortable with multi-way joins, as in Programming Assignment 2, #2, 3, 7, 8, 10

SQL chapter 8: p 189-190

SQL examples from the Supplementary Examples:

Numbering starts at 1 for each page. Starred problems are more important and/or a little harder (probably "and")

Page 218: 1, 2, 3, 6, 7, 8
Page 219: 1, 5, 6, 7
Page 220: 1, 5, 6, *7, *8
Page 221: *2, *3, *5, 6, 7
Page 222: *1, *2 ("Chapter" should be "Cheaper"), 6 (but do it directly, without using the NYNJ_Customer table)
Page 223: 2, 3