CIS 207 Oracle - Database Design and SQL

HOMEWORK: # 6

This assignment is in the Database Programming with SQL unit.

Review the resources in Section 0.

-  Database Programming with SQL – Course Resources. This will give you an outline of the material that will be covered in this unit.

-  Database Programming with SQL – APEX 5 Script and User Guide. This will give you The APEX Student Guide, Script to Create Tables, and PDF Resource Data for this Course.

-  It is VERY IMPORTANT that you become familiar with the APEX User guide. This will help you get started using APEX which you will need to run SQL.

-  Before you can do anything in APEX you MUST refer to Page 3 of the APEX student Guide to find out how to run the Script to Create Tables and Data for This Course.

-  You will need to down load the Scripts that you will need to run in the “Script to Create Tables and Data for this Course.”

-  I suggest that you print out the Excel spreadsheet which has the tables you will be using in this class. These are best printed in color. These are located on the class website in the Student Resources link. They are located in the “all of the tables in Application Express (APEX)”. You may fine the ERDs below this link also helpful.

-  Note that you might see reference to wf_countries, but there is no table of this name in APEX. It is named COUNTRIES, rather than WF_COUNTRIES. Thus, if you see the ERDs for wf_countries it is really countries.

Section 1-2, Exercise # 1, 2, 4, 5, 7

The objective of this activity is to familiarize students with the basic structure of a database table and to introduce the Fast Foods tables they will be using. Students should be able to relate primary to foreign keys and identify and name examples of column, row, and field.

Reinforce the importance of being able to easily relate tables of information together. Students can use Oracle Application Express and “SELECT * FROM tablename;” to view each table in the Fast Foods database

1. The Global Fast Foods database consists of how many tables? ____ tables

2. How is the F_SHIFTS table related to the F_STAFFS table?

4. How many rows of data have been entered in the F_PROMOTIONAL_MENUS table?

5. In the F_FOOD_ITEMS table, column ______is a foreign-key column. What table and column is this key referencing?

7. Which table(s) contains null values?

Section 1-3, Exercise # 2, 3, 4, 6, 7, 8, 9

Run the following queries in Oracle Application Express. Paste a copy of each query on a word document or notepad .txt file, save and return via Canvas:

2. Write a query that displays the last_name and email addresses for all the people in the DJs on Demand d_client table. The column headings should appear as “Client” and “Email Address.”

3. The manager of Global Fast Foods decided to give all employees at 5%/hour raise + a $.50 bonus/hour. However, when he looked at the results, he couldn't figure out why the new raises were not as he predicted. Ms. Doe should have a new salary of $7.59, Mr. Miller's salary should be $11.00, and Monique Tuttle should be $63.50. He used the following query. What should he have done?

SELECT last_name, salary *.05 +.50

FROM f_staffs;

4. Which of the following would be the easiest way to see all rows in the d_songs table? a. SELECT id, title, duration, artist, type_code

b. SELECT columns

c. SELECT *

d. SELECT all

Solution: SELECT *

6. In the example below, identify the keywords, the clause(s), and the statement(s): SELECT employee_id, last_name

FROM employees

Solution:

2 Keywords: SELECT & FROM

2 Clauses: SELECT employee_id, last_name & FROM employees

1 Statement: Both lines combined

7. Label each example as SELECTION, PROJECTION or JOIN.

a. Please give me Mary Adam's email address.

b. I will need each customer's name and the order_total for their order.

c. I would like only the manager_id column, not any of the other columns.

Solution:

a. Please, give me Mary Adam's email address. SELECTION

b. I will need each customer's name and the order_total for their order. JOIN

c. I would like only the manager_id column, not any of the other columns. PROJECTION

8. Which of the following statements are true?

a. null * 25 = 0;

b. null * 6.00 = 6.00

c. null * .05 = null

d. (null + 1.00) + 5.00 = 5.00

Solution: null * .05 = null

9. How will the column headings be labeled in the following example?

SELECT bear_id bears, color AS Color, age “age”

FROM animals;

a. bears, color, age

b. BEARS, COLOR, AGE

c. BEARS, COLOR, age

d. Bears, Color, Age

Solution: BEARS, COLOR, age

Homework_6_Sp17_207.doc