CIS200 Homework #7 Simple Queries s1

SP12 CSE200 QUIZ#5

Name ______Seat# ______

Check One… Lab è ______1:30pm ______3:30pm

Instructions:

·  Put away all books, papers, and calculators. Turn off all beepers and cell phones.

·  Answers must be legible or they will be marked incorrect.

·  Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query. Remember to put quotes on the like and text values and pound signs around dates; also, don’t use the word “like” in a criteria unless you are required to use a wildcard character(s).

·  You do not need to sort unless the problem specifically says to sort.

·  Aggregate functions: Group By, Sum, Avg, Min, Max, Count, Where, Expression.

·  REMEMBER that all the data is not shown. Be sure your queries will work when additional records are added.

·  Notice the name of the query is given for each design view grid.

YOU CAN ASSUME THAT ALL OF THE DATA INTEGRITY ERRORS (IF ANY) HAVE BEEN CORRECTED ONCE YOU START TO WRITE THE QUERIES

*******

IF YOU DO NOT PUT YOUR NAME ON BOTH OF THE PAGES OF THIS

EXAM BEFORE YOU GET OUT OF YOUR CHAIR,

YOU WILL GET A GRADE OF ZERO!!!

SCRATCH:

The tables given represent the Neilson Rating DBMS. The following tables include a sample collection of information (but not all of it) to help start up this data base:

The SHOWS table is a list of shows that Neilson is gathering ratings information about. Each show has fields associated with it that include the network, type of show, year that the first show aired and the length of the show in minutes.

The NETWORK table defines each network, whether or not they have a high definition channel and a description of the type of TV station they are.

The COMCOST table defines the different types of commercials and their associated cost per minute.

The COMMERCIALS table defines, for each commercial per episode and show, the type of the commercial and the length of the commercial in minutes. NOTE: not every show has commercials.

The SHOWCOST table lists each episode number per show and the related costs per episode as well as the reason for any additional costs that might have occurred during that particular episode. NOTE: Some shows do not have costs associated with them because donations were made or people are working for free for a cause.

FYI: Every show has an assigned order for the episode number starting from one and proceeding sequentially. You will only see parts and pieces of them here because not all of the data is shown. Depending on the show, there may be anywhere from 12 to 20 to 30 shows per season, but the episode number does not start over per season but continues sequentially through all of the seasons. That is, if a show has 12 episodes in the first season, then the episodes are numbered 1 to 12 and the start of season 2 (if there is a season 2) starts with episode number 13, etc.

NOTE: all fields that look like numbers are defined to be of numeric type.

Name ______Lab=> Friday ______1:30pm______3:30pm

1.  (12 pts) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) and foreign key (s) (if any) in the boxes given; then, for each table, draw relationship lines between tables (exactly the same as you would see in Access).

2.  A. (3 pts) There is one Data Integrity error shown on the given tables; specify which table it is on.

SHOWCOST – all or nothing

B. (3 pts) Assuming that Data Integrity is designated as well as Cascade Update and Cascade Delete, when possible, what changes would be made to the database if the show# value 11 on the SHOWS table is changed to 21?

The two records with shnum 11 on the COMMERICALS table would be changed to 21.

3.  (5 pts) Write a Access query in the design view given below to create a list of all the shows (including show number and show name with no commercial interruption.

QUERY NAME: Q3 TABLE(S):

JOIN TYPE:

RELATIVE TO:

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for Expressions (if needed)

4.  (7 pts) Write an Access query in the design view given below to create a list of all of the commercials and the cost of each. The resulting dynaset should include two fields only; the show number and the cost of each commercial with a calculated field name of “cost”.

QUERY NAME: Q4 TABLE(S):

JOIN TYPE:

RELATIVE TO:

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for Expressions (if needed)

QUERY NAME: Q5A TABLE(S):

JOIN TYPE:

RELATIVE TO:

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for Expressions (if needed)

QUERY NAME: Q5B TABLE(S):

JOIN TYPE:

RELATIVE TO:

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for Expressions (if needed)

QUERY NAME: Q5C TABLE(S):

JOIN TYPE:

RELATIVE TO:

Field

Table

Total

Sort

Show

Criteria

OR

OR

Additional room for Expressions (if needed)

SCORE ______/50

CSE 200 KReeves Quiz5 SP12 Page 4