Assignment:
Database Tables
Branch
BranchNo Street City State Zip Telephone
B1 23 Washington St. Davis CA 96516 916-994-2393
B2 411 Smith St. Woodland CA 84546 818-995-2111
B3 99 Harold St. Crystal City CA 95443 916-887-6654
B4 5656 Holland St. San Mateo CA 94341 907-776-8754
B5 1456 Cathy Lane Redwood CA 94328 907-665-9987
Staff
Name Position Salary StaffNo BranchNo
Michael Berg Manager 35,000 S11 B1
Anne Johnson Sales 20,000 S9 B2
Tom Jones Sales 22.000 S7 B1
Mary Thompson Secretary 27,000 S6 B1
Tim Feeley Assist. Mang 30,000 S10 B2
Alison Tan Sales 22,000 S8 B3
Clancy Finey Manager 36,000 S4 B4
Terran Michaels Sales 23,000 S5 B4
Video Catalog
CatalogNo Title Category-name DayRent Cost
V29 Terminator SciFi 3 3.50
V12 28 Days Later Hor 3 3.50
V35 Snow White Chil 5 2.00
V6 Blade Runner SciFi 5 2.00
V40 Scary Movie 2 Com 5 2.00
V3 Lion King Chil 5 2.00
V15 Ben Hur Dram 5 2.00
V14 Halloween 5 Hor 5 2.00
V8 Star Wars I SciFi 5 2.00
Video
BranchNo CatalogNo VideoNo
B1 V29 112
B2 V3 20
B4 V12 100
B3 V3 26
B3 V15 85
B2 V6 203
B1 V29 113
B2 V3 21
B3 V35 95
B5 V14 30
B4 V12 99
B5 V8 42
Customer
FName LName Addr DateRegist MemberNo
Andy Greene 213 W. Glen St., Santa Clara, CA 94333 4/2/02 315
Marci Smith 45 Harold St., San Mateo, CA 94025 3/15/02 525
Mark Thomas 223 4th St., Redwood City, CA 94036 10/30/03 1036
Steve James 112 Woods Lane, Redwood City, CA 94036 1/5/03 1055
Jean Jones 2156 Cyprus Ave., Woodland Hills, CA 95456 4/30/03 612
Harry White 916 S. Porter Street, San Mateo, CA 94025 2/8/02 923
Rented
VideoNo Title MemberNo DateOut DateIn
112 Terminator 525 11/1/03 11/4/03
20 Lion King 525 11/1/03 11/4/03
95 Snow White 612 11/2/03 11/4/03
203 Blade Runner 315 11/5/03 11/6/03
113 Terminator 525 11/5/03
36 Scary Movie 2 525 11/6/03
100 28 Days Later 1036 11/6/03
30 Halloween 5 1036 11/6/03
85 Ben Hur 1036 11/6/03
Queries
Use SQL to complete the following Queries
1. Find the catalog number, title and category name of all videos
2. List the staff table
3. Find all the science fiction videos - list out the catalog number, title and days rent
4. List the customer table sorted by last name
5. Count how many videos are in the Horror - Hor category
6. Create a list of the video
Include in the list: catalog number, video number, title, category name, days rent, cost
7. Print all staff with salaries of at least $30,000 and up
8. For each branch, print out the sum of all staff salaries along with the branch number
9. How many staff are there from the "Davis" branch?
10. How many videos are there still out? List the video number and title
11. Change the video catalog number from V6 to V9
12. Change the salary of the manager of branch B4 to $39,000
13. Add .50 to all the video costs
14. How many videos does customer "1036" have out? Print the customer first and last names and the member number and the titles of the videos
15. Delete from the video catalog the video with catalog number V8