Database

Succeeding in Business with Access 2010
Chapter 3 Analyzing Data for Effective Decision Making

Case Problem Level 1 - managing Customer Information for NHD Development Group Inc.
Antiques.accdb complete solution from Step 1 - Step 12.

Case Problem Level 2 - Retrieving Employee Information for MovinOn Inc.
MovinOn.accdb complete solution from Step 1 - Step 12.

MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

True/False (2 points each)
Indicate whether the sentence or statement is true or false.
1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
2. A table name can consist of numbers, letters, and blank spaces.
3. A constraint can only be created as part of the CREATE TABLE command.
4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
6. By default, the lowest value that can be generated by a sequence is 0.
7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
10. By default, the column headings displayed in a report are in upper-case characters.

Multiple Choice (3 points each)
Identify the letter of the choice that best completes the statement or answers the question.
11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
a. one-to-many c. one-to-all
b. many-to-many d. one-to-one

Contents of the BOOKS table
12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
a. SELECT title, cost+.20 "New Retail Price" FROM books;
b. SELECT title, cost*.20 "New Retail Price" FROM books;
c. SELECT title, cost*1.20 "New Retail Price" FROM books;
d. none of the above

Structure of the CUSTOMERS table
13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
a. ALTER TABLE customers
MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
b. ALTER TABLE customers
MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
c. ALTER TABLE customers
MODIFY (city (+8), lastname (+4));
d. ALTER TABLE customers
MODIFY (city (20), lastname (14));

14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
a. The constraint exists between two tables, called the parent table and the child table.
b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.

15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
a. ALTER USER rthomas PASSWORD EXPIRE ;
b. ALTER USER rthomas CHANGE PASSWORD;
c. ALTER USER rthomas UPDATE PASSWORD;
d. ALTER USER rthomas EXPIRE PASSWORD;

16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
a. Asc c. ascending
b. A d. either a or c

17. Which of the following is an accurate statement?
a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
d. none of the above

18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
a. NVL c. IFNVL
b. IFNL d. NVL2

Contents of the ORDERS table
19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
a. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
b. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL
GROUP BY order#;
c. SELECT COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
d. SELECT COUNT(*)
FROM orders
WHERE shipdate IS NULL;

20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
a. Z
b. 9
c. ,
d. .

Completion (4 points each)
Complete each sentence or statement.
21. A(n) ____________________ is a group of interrelated files.
22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
25. The ____________________ function is used to round numeric fields to a stated position.

SQL
26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
EMPLOYEE (employee name, street, city)
WORKS (employee name, company_name, salary)
A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation

27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
DEPTLOCATION (DNumber, DLocation)
PROJECT (PName, PNumber, PLocation, DNum)
WORKSON (ESSN, PNO, Hours)
DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)

Integrity Constraints:
Primary key = Foreign Key
EMPLOYEE.SSN = DEPENDENT.ESSN
EMPLOYEE.SSN = WORKSON.ESSN
EMPLOYEE.SSN = DEPARTMENT.MGRSSN
EMPLOYEE.SSN = EMPLOYEE.SuperSSN
DEPARTMENT.DNumber = EMPLOYEE.DNO
DEPARTMENT.DNumber = DEPTLOCATION.DNumber
DEPARTMENT.DNumber = PROJECT.DNum
PROJECT.PNumber = WORKSON.PNO

28. (18 points) Write SQL syntax to resolve the following queries.
- Find the names of all employees who are directly supervised by the employee named “John Doe”
- List the name of employees whose salary is greater than the average salary of his or her corresponding department
- For each department, retrieve the department name and the average salary of all employees working in that department.

Microsoft Access 2010 Chapter 5 Apply Your Knowledge
Reinforce the skills and apply the concepts you learned in this chapter.

Adding Date/Time and OLE Fields, Using an Input Mask Wizard, and Querying Date/Time Fields

Instructions: Start Access. If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Babbage CPA Firm database that you used in Chapter 4. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Add the Start Date and Picture fields to the Bookkeeper table structure, as shown in Figure 5 – 84. Create an input mask for the Start Date field. Use the Short Date input mask type.
2. Add the data shown in Table 5–1 to the Bookkeeper table.
3. Query the Bookkeeper table to find all bookkeepers who started after January 1, 2012. Include the Bookkeeper Number, First Name, Last Name, Hourly Rate, and Earnings YTD in the query results. Save the query as Start Date Query.
4. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

Problem: The management of ECO Clothesline wants to learn more about SQL and has determined a number of questions it wants SQL to answer. You must obtain answers to the questions posed by management.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Find all customers where the customer type is SAL. Include the Customer Number, Customer Name, and Sales Rep Number fields in the result. Save the query as Lab 7-1 Step 1 Query.
2. Find all customers located in Tennessee (TN) with a paid amount greater than $1,500.00. Include the Customer Number, Customer Name, and Amount Paid fields in the result. Save the query as Lab 7-1 Step 2 Query.
3. Find all customers whose names begin with the letter, C. Include the Customer Number, Customer Name, and City fields in the result. Save the query as Lab 7-1 Step 3 Query.
4. List all cities in descending order. Each city should appear only once. Save the query as Lab 7-1 Step 4 Query.
5. Display the customer number, name, sales rep number, first name, and last name for all customers. Sort the results in ascending order by sales rep number and customer number. Save the query as Lab 7-1 Step 5 Query.
6. List the average balance amount grouped by sales rep number. Name the average balance as Average Billed. Save the query as Lab 7-1 Step 6 Query.
7. Find the customer number and name for every pair of customers who are located in the same city. Save the query as Lab 7-1 Step 7 Query.
8. Find the customer numbers, names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once. Save the query as Lab 7-1 Step 8 Query.
9. Use a subquery to find all sales reps whose customers are located in Pineville. Save the query as Lab 7-1 Step 9 Query.
10. Find the average balance amount for sales rep 44. Save the query as Lab 7-1 Step 10 Query.
11. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 8 Lab 1 Applying Advanced Form Techniques to the ECO Clothesline Database

Lab 1: Applying Advanced Form Techniques to the ECO Clothesline Database
Problem: ECO Clothesline needs a form for the Customer table that will allow users to update data in the table ECO also needs a form to display open orders data for sales reps.

Instructions: If you are using Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 7. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Create a query that includes the Sale Rep Number field from the Sales Rep tan;e, the Customer Number field from the Customer table and the Order Number and Amount fields from the Open Orders table. Sort the query in ascending order by Sales Rep Number, Customer Number, and Order NUmber Save the query as Sales Rep and Orders.

2. Create the form shown in Figure 8-95. total Amount is a calculated control and is the sum of the Balance and Amount Paid amounts. Format the Total Amount control as Currency with two decimal places. (Hint: Use the property sheet for the control.) the form includes command buttons, a combo box for the Sales Rep Number field, and a combo box to search for customers by name. Be sure to sort the customer by names in ascending order, place a rectangle around the combo box, and update the combo box. The user should not be able to tab to the combo box. When the Add Record button is clicked, the insertion point should be in the customerNumber field. The form is similar in style to the form shown in Figure 8-1 on page AC 466.

3. Create the Sales Rep Order Data form shown in Figure 8-96. The subform that appears in the Datasheet tab uses the Sales Reps and Orders query (Figure 8-96a). The chart ghat appears in the Chart tab uses the same query (Figure 8-96b).

4. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine

Lab 1: Designing a Database for Phil’s Limousine
Instructions: Consider the following set of requirements for Phil’s Limousine Service:
• For each driver, the company keeps track of the driver’s name (first name and last name), driver’s address (street, city, state, postal code), driver’s license number, home telephone number, and cell telephone number.
• For each limousine, the company keeps track of a unique limousine ID number, limousine license number, limousine color, and number of passengers the limousine can hold.
• A driver can be assigned to more than one limousine. A limousine will have multiple drivers but only one driver at a time.
Based on these requirements, do the following:
1. Identify and list the entities and the attributes of those entities.
2. Identify and list the functional dependencies.
3. Create a set of 3NF relations using the shorthand notation given in the chapter. Be sure to identify all primary keys and foreign keys appropriately.
Submit your database design in the format specified by your instructor.

Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

Assignment 9-1: creating a Tigger to Handle Product Restocking
Brewbean's has a couple of columns in the Product table to assist in inventory tracking. The REORDER column contains the stock level at which the product should be reordered. If the stock fall to this level. Brewbean's wants the application to insert a row in the BB_PRODUCT_REQUEST table automatically to alert the ordering clerk that additional inventory is needed. Brewbean's currently uses the reorder level amount as the quantity that should be ordered. This task can be handled by using a trigger.
1. Take out some scrap paper and a pencil. Think about the tasks the triggers needs to perform. Including checking. whether the new stock level falls below the reorder point. If so, check whether the product is already on order by viewing the product request table; if not, enter a new product request. Try to write the trigger code on paper. Even though you learn a lot by reviewing code, you improve your skills faster when you create the code on your own.
2. Open the c9reorder.txt file in the Chapter09 folder. Review this trigger code, and determine how it compares with your code.
3. In SQL Developer, create the trigger with the provided code.
4. Test the trigger with product ID 4. First, run the query shown in Figure 9-36 to verify the current stock data for this product. Notice that a sale of one more item should initiate a reorder.
5. Run the UPDATE statement shown in Figure 9-37. It should cause the trigger to fire. Notice the query to check whether the trigger fired and weather a product stock request was inserted in the BB_PRODUCT_REQUEST table.
6. Issue a ROLLBACK statement to undo these DML actions to restore data to its original state for use in later assignments.
7. Run the following statement to disable this trigger so that it doesn't affect other projects:
ALTER TRIGGER bb_reorder_trg DISABLE;


Assignment 9-2: Updating Stock Information When a Product Is Filled
Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock level was inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table's DTRECD and COST columns. they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline:
1. In SOL Developer, run the following INSERT statement to create a product request you can use in this assignment:
INSERT INTO bb_product_requect (idRequest, idProduct, dtRequest, qty)
VALUES (3, 5, SYSDATE, 45);
COMMIT;
2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.
3. Now test the trigger. First, query the stock and reorder data for product 5. as shown in Figure 9-38.
4. Now update the product request to record it as fulfilled by using UPDATE statement shown in figure 9-39.
5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue the ROLLBACK statement to undo modifications.
6. If you aren't doing assignment 9-3, disable the trigger so that it doesn't affect other assignments.


Assignment 9-3: Updating the Stock Level If a Product Fulfillment is Cancelled
The Brewbean's developers have made progress on the inventory-handling processes; however, they hit a snag when a store clerk incorrectly recorded a product request as fulfilled. When the product request was updated to record a DTRECD value, the product stock level was updated automatically via an existing trigger, BB_REQFILL_TRG. If the clerk empties the DTRECD column to indicate that the product request has been filled, the product stock level need to be corrected or reduced, too. Modify the BB_REQFILL_TRG to solve this problem.
1. Modify the trigger code from Assignment 9-2 as needed. Add code to check whether the DTRECD column already has a data in it and is now being set to NULL.
2. Issue the following DML actions to create or update rows that you can use to test the trigger:
INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty, dtRecd, cost)
VALUES (4, 5, SYSDATE, 45, '15-JUN-2012', 225);
UPDATE bb_product
SET stock = 86
WHERE idProduct = 5;
COMMIT;
3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
UPDATE bb_product_request
SET dtRecd = NULL
WHERE idRequest = 4;
4. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
ALTER TRIGGER bb_reqfill_trg DISABLE;


Assignment 9-4: Updating Stock Levels When an Order ls Cancelled
At times, customers make mistakes in submitting orders and call to cancel an order. Brewbean's wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasn't completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:
The trigger need to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been cancelled.
Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each item's stock level
Keep in mind that coffee can be ordered in half or whole pounds.
Use basket 6, which contains two items, for testing.
1.Run this INSERT statement to test the trigger:
INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage)
VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);
2. Issue the queries to confirm that the trigger has modified the basket's order status and product stock level correctly.
3. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
ALTER TRIGGER BB_ORDCANCEL_TRG DISABLE;

Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL

Problem: The manager of the Walburg Energy Alternatives store would like to learn more about SQL and has determined a number of questions he wants SQL to answer. You must obtain answers to the questions posed by the manager.
Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Find all records in the Item table where the difference between the cost of the item and the selling price of the item is less than $0.25 (25 cents). Display the item number, description, cost, and selling price in the query result. Save the query as Lab 7-2 Step 1 Query.
2. Display the item number, description, and profit (selling price – cost) for all items. Name the computed field Profit. Save the query as Lab 7-2 Step 2 Query.
3. Find all items where the description begins with the letter, W. Include the item number and description in the result. Save the query as Lab 7-2 Step 3 Query.
4. Display the vendor name, item number, description, and cost for all items where the number on hand is less than 10. Sort the results in ascending order by vendor name and description. Save the query as Lab 7-2 Step 4 Query.
5. Find the average cost by vendor. Name the computed field Average Cost. Save the query as Lab 7-2 Step 5 Query.
6. Find the total number of reordered items for each item in the Reorder table. Name the computed field Total Ordered. Include the item number in the result. Save the query as Lab 7-2 Step 6 Query.
7. Add the following record to the Reorder table.
Item Number Date Ordered Number Ordered
8590 4/12/2012 3
Save the steps to add the record as Lab 7-2 Step 7 Query.
8. Update the Number Ordered field to 5 for those records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to update the record as Lab 7-2 Step 8 Query.
9. Delete all records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to delete the record as Lab 7-2 Step 9 Query.
10. Submit the revised database in the format specified by your instructor.

Microsoft Access 2010 Chapter 4 Lab 2 Presenting Data in the Walburg Energy Alternatives Database

Problem: The management of Walburg Energy Alternatives already has realized the benefits from the database of items and vendors that you created. The management now would like to prepare reports and forms for the database.
Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Open in Layout view the Inventory Status Report that you created in Chapter 1. Add a total for the Inventory Value field. Be sure the total is completely displayed. Display the average cost. If there are fewer than 10 items on hand, the value should appear in a red bold font. Filter the report for all items
where the number on hand is 5 or less. Save the filtered report as Filtered Inventory Status Report.

2. Create the Items by Vendor report shown in Figure 4 – 84.

3. Create the form shown in Figure 4 – 85. If there are fewer than 10 items on hand, the value should appear in a red bold font. Save the form as Item Update Form.

4. Filter the Item Update Form for all items where the cost is less than $3.00 and sort the results in descending order by cost. Save the form as Filtered Item Update Form.

5. Submit the revised database in the format specified by your instructor.