CS 122

CS 122. Introduction Relational Database Systems and SQL

Syllabus

Please email your homework assignments to me each week at RAbbott@CalStateLA.edu.

Week 1: Introduction and basic SQL queries (SELECT ... FROM ... ORDER BY). (Fehily, Chapter 3 pages 34 - 39 and Chapter 4 through page 74)

Homework: Write queries to retrieve the following information.

List the countries that have publishers. Do not allow duplicates in the list. Ensure that the list is in alphabetical order.
List the book titles ordered by date published. Which was the earliest title listed? Why was it first? Considering the meaning of the Contract column, do you think it should be listed first.
List the book categories (i.e., types, but call it "category") in order of sales, most sales first, but don't list the sales figures. Is it possible to list these categories without duplicates?

Week 2: Constrained queries (SELECT ... FROM ... WHERE ... ORDER BY ). (Fehily, Chapter 4, pages 75 - 95)

Homework: Write queries to retrieve the following information.

List the authors from east of the Mississippi, i.e., with zip code less than 50000. When you do this query, you have to know how zip code is stored in the authors table: as a number or as a character string. You can't compare numbers to character strings. When using a character string constant in a query, the string must be in quotes. When using a number in a query, the number is not in quotes. See, for example, Listing 4.22 on page 79 and notice which constants are quoted.

List the book titles published in this century that have between 500 and 1000 pages. List them in order of length, longest one first. Construct two queries, one using BETWEEN and one not using BETWEEN.

List the book titles that include the first person singular pronoun I . [Access 2000 seems to use * instead of % as a wildcard character for any string of characters and ? instead of _ for a wildcard character for a single character.]

List the books with single word titles.

List the authors from cities that have two words in their names and where the first word is exactly 3 letters long, e.g., New York and San Francisco, but not Palo Alto.

List the titles that are neither children's books nor computer books. Use NOT IN.

Week 3: Derived Columns and functions. (Fehily, Chapter 5,)

Homework: Write queries to retrieve the following information.

List the authors in the form: <first initial>, period, space, <last name> (e.g., R. Abbott) ordered by last name, and within that by first name. Don't list authors (like Kellsey) who don't have both a first and last name. Is Kellsey's first name in the database a NULL or an empty string? How did you find out?
List the titles ordered by the length of the title. If two titles have the same length, order them alphabetically.
List the titles that have an even number of pages. List the number of pages in the second column. Order the listing by number of pages (ascending).
List all the titles, but list those with an even number of pages first followed by those with an odd number of pages.
List the authors (first and last names) along with their phone numbers and zip codes. List only those authors whose phone numbers contain the last digit in their zip code. For example, Sarah Bachman would be listed (because 8 is in her phone number) but Hallie Hull would not (because 3 is not in her phone number).
List summer reading, i.e., titles that were published in July or August. Use Extract (or in MS Access DatePart ).
List all the titles, but list the Children's books in upper case. Use a case (or in MS Access a switch) statement. Note that you can use true as a condition. Write the same query using IIF .
List the titles that were published more than 1000 days ago. Use Current_Date (or in MS Access Date()). Order by how recently the book was published, most recent last.
List the titles whose sales end with a pair of identical digits. For example, 200 Years of German Humor would be listed but Ask Your System Administrator would not.

Week 4: Aggregate functions. (Fehily, Chapter 6,)

Homework: Write queries to retrieve the following information.

List the average number of pages in books in each category.


type	avgPages
biography	437.666666666667
children	54
computer	1226
history	307.666666666667
psychology	512.333333333333

List the average number of pages in books in each category, but only list those categories where the average number of pages is greater than 200.


type	avgPages
biography	437.666666666667
computer	1226
history	307.666666666667
psychology	512.333333333333

List the average number of pages in books in each category, but only list those categories where the average price is greater than $20.00.


type	avgPages	avgPrice
computer	1226	39.95
history	307.666666666667	23.9766666666666666666667

Week 5: Midterm (including the answers)

Week 6: Relational Database Theory. (Fehily, Chapter 2,)

Homework:

Consider the following table.


name	phone	address
Sarah Buchman	718-496-7223	75 West 205 St, Bronx, NY 10468
Wendy Heydemark	303-986-7020	2922 Baseline Rd, Boulder, CO 80303
Hallie Hull	415-549-4278	3800 Waldo Ave, #14F, San Francisco, CA 94123
Klee Hull	415-549-4278	3800 Waldo Ave, #14F, San Francisco, CA 94123
Christian Kells	212-771-4680	114 Horatio St, New York, NY 10014
Kellsey	650-836-7128	390 Serra Mall, Palo Alto, CA 94305
Paddy O'Furniture	941-925-0752	1442 Main St, Sarasota, FL 34236

Is it in First Normal Form? Why or why not?
What is the key? Does every table have a key?

Consider the following table.


title_id	title_name	au_id	au_fname	au_lname
T01	1977!	A01	Sarah	Buchman
T02	200 Years of German Humor	A01	Sarah	Buchman
T03	Ask Your System Administrator	A05	Christian	Kells
T04	But I Did It Unconsciously	A03	Hallie	Hull
T04	But I Did It Unconsciously	A04	Klee	Hull
T05	Exchange of Platitudes	A04	Klee	Hull
T06	How About Never?	A02	Wendy	Heydemark
T07	I Blame My Mother	A02	Wendy	Heydemark
T07	I Blame My Mother	A04	Klee	Hull
T08	Just Wait Until After School	A06		Kellsey
T09	Kiss My Boo-Boo	A06		Kellsey
T10	Not Without My Faberge Egg	A02	Wendy	Heydemark
T11	Perhaps It's a Glandular Problem	A03	Hallie	Hull
T11	Perhaps It's a Glandular Problem	A04	Klee	Hull
T11	Perhaps It's a Glandular Problem	A06		Kellsey
T12	Spontaneous, Not Annoying	A02	Wendy	Heydemark
T13	What Are The Civilian Applications?	A01	Sarah	Buchman

Assuming that title_id is a unique identifier for titles and au_id is a unique identifier for authors, what is the key?
What are the functional dependencies?
Is the table in Second Normal Form? Why or why not?

Consider the following table (the authors table from the book)


au_id	au_fname	au_lname	phone	address	city	state	zip
A01	Sarah	Buchman	718-496-7223	75 West 205 St	Bronx	NY	10468
A02	Wendy	Heydemark	303-986-7020	2922 Baseline Rd	Boulder	CO	80303
A03	Hallie	Hull	415-549-4278	3800 Waldo Ave, #14F	San Francisco	CA	94123
A04	Klee	Hull	415-549-4278	3800 Waldo Ave, #14F	San Francisco	CA	94123
A05	Christian	Kells	212-771-4680	114 Horatio St	New York	NY	10014
A06		Kellsey	650-836-7128	390 Serra Mall	Palo Alto	CA	94305
A07	Paddy	O'Furniture	941-925-0752	1442 Main St	Sarasota	FL	34236

Assuming that au_id is a unique identifier for authors, what is the (presumed) key?
It appears that phone is functionally dependent on au_lname. Is that a reasonable claim to make about the world?
If phone is not functionally dependent on au_id, what is the key? What would it mean (about the world) for phone not to be functionally dependent on au_id ? What would you do if you wanted au_id alone, to be the key?
Is au_fname functionally dependent on phone?
It appears that au_lname is functionally dependent on phone, i.e., that phone -> au_lname. Is that a reasonable claim to make about the world?
Considering the fact that zip -> city, state, is the table in Third Normal Form? If not, how could you put it into Third Normal Form?

Week 7: Queries involving more than one table (Fehily, Chapter 7, through p. 217)

Homework:

Assuming that publishers make a gross profit of 10% on gross sales (prices x sales), list the title, price, sales, gross sales, gross profit, advance, and net profit (after deducting the advance from gross profit) of all books that have earned enough to pay off their advances.


title_name	price	sales	"gross sales"	"gross profit"	advance	"net profit"
200 Years of German Humor	19.95	9566	190841.7	19084.17	1000	18084.17
Ask Your System Administrator	39.95	25667	1025396.65	102539.665	15000	87539.665
Exchange of Platitudes	6.95	201440	1400008	140000.8	100000	40000.8
How About Never?	19.95	11320	225834	22583.4	20000	2583.4
I Blame My Mother	23.95	1500200	35929790	3592979	1000000	2592979
Just Wait Until After School	10	4095	40950	4095	0	4095
Kiss My Boo-Boo	13.95	5000	69750	6975	0	6975
Spontaneous, Not Annoying	12.99	100001	1299012.99	129901.299	50000	79901.299
What Are The Civilian Applications?	29.99	10467	313905.33	31390.533	20000	11390.533

List all pairs of authors who live in the same state as each other. Avoid duplicate listings, i.e., the same pair listed in two different orders or an author listed with himself or herself.


au1.au_fname	au1.au_lname	au1.city	au1.state	au2.au_fname	au2.au_lname	au2.city	au2.state
Hallie	Hull	San Francisco	CA	Klee	Hull	San Francisco	CA
Sarah	Buchman	Bronx	NY	Christian	Kells	New York	NY
Klee	Hull	San Francisco	CA		Kellsey	Palo Alto	CA
Hallie	Hull	San Francisco	CA		Kellsey	Palo Alto	CA

List all authors and the publishers who are located in the same cities as they are. If no publisher is located in an author's city, leave the publisher entry blank.


au_fname	au_lname	city	pub_name
Sarah	Buchman	Bronx
Wendy	Heydemark	Boulder
Hallie	Hull	San Francisco	Core Dump Books
Klee	Hull	San Francisco	Core Dump Books
Christian	Kells	New York	Abatis Publishers
	Kellsey	Palo Alto
Paddy	O'Furniture	Sarasota

List authors and the publishers who have published books they have written.


au_fname	au_lname	title_name	pub_name
Sarah	Buchman	1977!	Abatis Publishers
Sarah	Buchman	200 Years of German Humor	Schadenfreude Press
Christian	Kells	Ask Your System Administrator	Core Dump Books
Hallie	Hull	But I Did It Unconsciously	Abatis Publishers
Klee	Hull	But I Did It Unconsciously	Abatis Publishers
Klee	Hull	Exchange of Platitudes	Abatis Publishers
Wendy	Heydemark	How About Never?	Abatis Publishers
Wendy	Heydemark	I Blame My Mother	Schadenfreude Press
Klee	Hull	I Blame My Mother	Schadenfreude Press
	Kellsey	Just Wait Until After School	Abatis Publishers
	Kellsey	Kiss My Boo-Boo	Abatis Publishers
Wendy	Heydemark	Not Without My Faberge Egg	Abatis Publishers
Hallie	Hull	Perhaps It's a Glandular Problem	Abatis Publishers
Klee	Hull	Perhaps It's a Glandular Problem	Abatis Publishers
	Kellsey	Perhaps It's a Glandular Problem	Abatis Publishers
Wendy	Heydemark	Spontaneous, Not Annoying	Abatis Publishers
Sarah	Buchman	What Are The Civilian Applications?	Schadenfreude Press

List the number of books each publisher has published.


pub_name	books
Abatis Publishers	9
Core Dump Books	1
Schadenfreude Press	3

Do the same as in the previous exercise, but include publishers who have not published any books. [Hint: Use a RIGHT (or LEFT) OUTER JOIN.]


pub_name	books
Abatis Publishers	9
Core Dump Books	1
Schadenfreude Press	3
Tenterhooks Press	0

Getting the last row to have an entry of 0 instead of 1 requires that you use count(title_id) instead of count(*). Try it both ways and see what you get.

Week 8: Queries involving unions and nested queries (Fehily, finish Chapter 7 and Chapter 8)

Homework: Write queries to retrieve the following information.

Find the longest book in each category.


type	title_id	pages
computer	T03	1226
children	T08	86
psychology	T11	826
biography	T12	507
history	T13	802

Find the total number of pages in books written by each author.


au_id	totalPages
A01	923
A02	1313
A03	1336
A04	1870
A05	1226
A06	934

Find the total number of pages in books in each category, cumulatively.


type	PageTotalForType	CumulativePages
biography	1313	1313
children	108	1421
computer	1226	2647
history	923	3570
psychology	1537	5107

Use listing 8.21 on page 253 as a starting point. Use a nested query in the main FROM clause instead of the titles table. The nested query should tell you the page total for each type. In other words, the nested query should give you the first two columns of the answer. Then use the technique in 8.21 to produce the third column.

Find the author/s who wrote the greatest number of books, and determine how many books that/those author/s wrote.


au_id	au_fname	au_lname	titles
A02	Wendy	Heydemark	4
A04	Klee	Hull	4

It turned out that two authors were tied at four books each. Use listing 8.22 on page 253 as a starting point.

Week 9:

Week 10:

Final

My home page