Oracle

ITSE 2309 LAB 1 Database and SQL Queries

Submit: Query Statements Query Output
Chapters 2,3,4,5 11g SQL book
Objectives: Be able to access ORACLE/SQL Plus Be able to do simple SQL operations

Steps:
- Log onto the sample Oracle 11g database created.
- See Chapter 7 in "Database Systems" for information on coding, saving, and executing your queries.
- Save the output of the queries by using the SPOOL option ( see chpt 14 in 11g SQL in lab resources tab)
- Save both the queries code and the output of the queries as "Lab1_LastName.txt and submit via Blackboard—using the - Attach file – Upload function

Do the following queries:
1. List all columns and rows in the stock table.
2. List the last name, first name, and company of all customers (List the columns in that order). Place the list in alphabetical order by company name.
3. List the company names for all customers from Sunnyvale, Redwood City, or San Francisco.
4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List order number, order date, customer number, ship date, and paid date. (Hint: Specify year in single quotes 'DD-MMM-YYYY')
5. List the order number, order date, and shipping charges for all orders that are not on backlog and for which the shipping charge is over $15.00.
6. List all stock items which are baseball items which have a unit price greater than $200.00 and a manufacturer code which starts with 'H'. (Hint: use LIKE)
7. List the company name for all customers who have orders. Don not list a company more than once.
8. List the customer number and the description (from the stock table) of all items ordered by customers with customer numbers 104-108. Order the output by customer number and description. (There should be no duplicate rows in your output).
9. List the number of (distinct) customers having an order. Label the column "Total_Customers_with_Orders".
10. For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. (Hint: You must use a GROUP BY clause in this query).

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

Assignment 9-5: Processing Discount
Brewbean's is offering a new discount for return shoppers. Every fifth completed order gets a 10% discount. The count of orders for a shopper is placed in a packaged variable named pv_disc_num during the ordering process. The count needs to be tested at checkout to determine whether a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is changed from 0 to 1), the pv_disc_num packaged variable is checked. If it's equal to 5, set a second variable named pv_disc_txt to Y. This variable is used in calculating the order summary so that a discount is applied, if necessary.
create a package specification named DISC_PKG containing the necessary packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger with the following UPDATE statement:
UPDATE bb_basket
SET orderplaced = 1
WHERE idBasket = 13;
If you need to test the trigger multiple times, simply reset the ORDERPLACED column to 0 for basket 13 and then run the UPDATE again. Also, disable this trigger when you're finished so that it doesn't affect other assignments.


Assignment 9-6: Use Triggers to Maintain Referential Integrity
At times, Brewbean's has changed the ID numbers for existing products. In the past, developers had to add a new product row with the new id to the BB_PRODUCT table, modify all the corresponding BB_BASKETITEM and BB_PRODUCTOPTION table rows, and then delete the original product row. Can a trigger be developed to avoid all these steps and handle the update of the BB_BASKETITEM and BB_PRODUCTOPTION table rows automatically for a given change in product ID? If so, create the trigger and test by issuing an update statement, which changes the IDPRODUCT of 7 to 22. Do a rollback to return the data back to its original state. Also, disable the new trigger after you have completed the assignment.


Assignment 9-7: Updating Summary Data Tables
The Brewbean's owner uses several summary sales data tables every day to monitor business activity. The BB_SALES_SUM table holds the product ID, total sales in dollars, and total quantity sold for each product. A trigger is needed so that every time an order is confirmed or the ORDERPLACED column is updated to 1, the BB_SALES_SUM table is updated accordingly. Create a trigger named BB_SALESUM_TRG that perform this task. Before testing, reset the ORDERPLACED column to 0 for basket 3, as shown in the following code, and use this basket to test the trigger.
UPDATE bb_basket
SET orderplaced = 0
WHERE idBasket = 3;
Notice that the BB_SALES_SUM table already contains some data. Test the trigger with the following UPDATE statement, and confirm that the trigger is working correctly:
UPDATE bb_basket
SET orderplaced = 1
WHERE idBasket = 3;
Do a rollback and disable the trigger when you're finished so it doesn't affect the other assignments.


Assignment 9-8: Maintaining an Audit Trail of Product Table Changes
The accuracy of product table data is critical and the Brewbean's. owner wants to have an audit file that containing information on all DML activity on the BB_PRODUCT table. This information should indicate the ID of the user performing a DML action, the date, the original values of the changed row, and the new values. This audit table needs to track specific columns of concern, including PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Create a table named BB_PRODCHG_AUDIT that can hold the relevant data. Then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever one of the specified columns in the BB_PRODUCT table is changed.
Be sure to issue the following command. If you created the SALES_DATE_TRG trigger in the chapter, it conflicts with this assignment.
ALTER TRIGGER SALES_DATE_TRG DISABLE;
Use the following update statement to test your trigger:
UPDATE bb_product
SET salestart = '05-MAY-03', saleend = '12-MAY-03', saleprice = 9
WHERE idproduct = 10;
When you have finished, do a rollback and disable the trigger so that it does not affect other assignments.

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;

Oracle 11g SQL Joan Casteel Chapter 13 Hands-On Assignments

To perform the following activities, refer to the tables in the JustLee Books database.
1. Create a view that lists the name and phone number of the contact person at each publisher. Don't include the publisher's ID in the view. Name the view CONTACT.
2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
3. Create a view called HOMEWORK13 that includes the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make sure the view is created even if the FIRSTATTEMPT table doesn't exist.
4. Attempt to view the structure of the HOMEWORK13 view.
5. Create a view that lists the ISBN and title for each book in inventory along with the name and phone number of the person to contact if the book needs to be reordered. Name the view
REORDERINFO.
6. Try to change the name of a contact person in the REORDERINFO view to your name. Was an error message displayed when performing this step? If so, what was the cause of the error message?
7. Select one of the books in the REORDERINFO view and try to change its ISBN. Was an error message displayed when performing this step? If so, what was the cause of the error message?
8. Delete the record in the REORDERINFO view containing your name. (If you weren't able to perform #6 successfully, delete one of the contacts already listed in the table.) Was an error message displayed when performing this step? If so, what was the cause of the error message?
9. Issue a rollback command to undo any changes made with the preceding DML operations.
10. Delete the REORDERINFO view.

Oracle 11g SQL Joan Casteel Chapter 12 Hands-On Assignments

To perform these activities, refer to the tables in the JustLee Books database. Use a subquery to accomplish each task. Make sure you execute the query you plan to use as the subquery to verify the results before writing the entire query.

1. List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books.
2. Determine which books cost less than the average cost of other books in the same category.
3. Determine which orders were shipped to the same state as order 1014.
4. Determine which orders had a higher total amount due than order 1008.
5. Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books.
6. List the title of all books in the same category as books previously purchased by customer 1007. Don't include books this customer has already purchased.
7. List the shipping city and state for the order that had the longest shipping delay.
8. Determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by JustLee Books.
9. Determine the number of different customers who have placed an order for books written or cowritten by James Austin.
10. Determine which books were published by the publisher of The Wok Way to Cook.

Oracle 11g SQL Joan Casteel Chapter 11 Hands-On Assignments

To perform these assignments, refer to the tables in the JustLee Books database.
1. Determine how many books are in the Cooking category.
2. Display the number of books with a retail price of more than $30.00.
3. Display the most recent publication date of all books sold by JustLee Books.
4. Determine the total profit generated by sales to customer 1017. Note: Quantity should be reflected in the total profit calculation.
5. List the retail price of the least expensive book in the Computer category.
6. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.
7. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn't recently placed an order with JustLee Books.
8. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
9. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.
10. What's the retail price of the most expensive book written by Lisa White?

Oracle 11g SQL Joan Casteel Chapter 10 Hands-On Assignments

To perform the following assignments, refer to the tables in the JustLee Books database.
1. Produce a list of all customer names in which the first letter of the first and last names is in uppercase and the rest are in lowercase.

2. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text "NOT REFERRED" if the customer wasn't
referred to JustLee Books by another customer or "REFERRED" if the customer was referred.

3. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.

4. Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amounts as a percent of the cost.)

5. Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you're using.

6. Create a list of all book titles and costs. Precede each book's cost with asterisks so that the width of the displayed Cost field is 12.

7. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).

8. Using today's date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title,
publication date, current date, and age.

9. Determine the calendar date of the next occurrence of Wednesday, based on today's date.

10. Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists.

CMIS420 Advanced Relational Database Final Programming Project

In this project, you will design a secure database with several tables using good relational database design techniques.

Project Objectives:
After completing this Project, you should be able to:
Explain the purposes of advanced database objects including indexes, clusters, sequences, and views
Develop and implement functions, triggers, and stored procedures/packages

Project Requirements:
1. Using the Oracle database engine, design and develop a relational database to store user information to support a large online shipping company. Information that needs to be stored includes user name, detailed mailing address, and shipping information including number of packages, weight, cost, destination, origin, and tracking information allowing users to determine where a package is, and when and who signed for the package once it arrives. Other business data should be present showing how much the shipment costs and invoicing capability to send bills to customers. Your design should be normalized and well-designed for scalability for additional customers and shipment locations.

2. Your design should allow for inserting, updating, selecting and deleting information

3. A trigger should be used to generate invoices once a customer reaches $200 in shipping costs.

4. An instead-of trigger should be employed to update and/or populate multi-table views.

5. You should include packages, indexes, views and other helpful database components that will assist you in your design.

6. Your scripts should work perfectly allowing the creation of all of the tables, triggers, views and other database components and provide test scripts that demonstrate the use of the system.

7. You do not need a GUI component but your scripts should demonstrate all of the functionality of the system.

Deliverables:
All sql scripts and run scripts and instructions for successfully building your database and testing your database.
An ERD diagram of your database design showing primary keys, foreign keys and other constraints.
3. Well-written word document describing:
a. The database design
b. How to set up and test your database
c. The test data and results you used for your database,
d. Your approach, lessons learned, design strengths, limitations and suggestions for future improvement and alternative approaches, the most challenging aspects of developing this application.

CMIS420 Project 3 Star Schema for OVS Database

1) Complete Project #2 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIME (or TIMES if you desire) dimension tables and the SALES_FACTS fact table are created and populated.
2) Create a user-defined function called VEHICLES_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total vehicles sold by that combination. Execute your function for a sample input value of your choosing to demonstrate that it works correctly.
3) Create a user-defined function called DOLLARS_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total gross sales amount of the sales by that combination. Execute your function for a sample input value of your choosing to demonstrate that it works correctly.
4) Create a stored procedure called STATS_BY_VEHICLE_TYPE that receives an input parameter of the concatenated make and model and then calls your two user-defined functions VEHICLES_BY_VEHICLE_TYPE and DOLLARS_BY_VEHICLE_TYPE. Execute your stored procedure for the same sample input values used earlier to demonstrate that it works correctly.
5) Develop an SQL query to determine which holiday had the most sales and then drill down via another query to determine for that holiday which dealership, by zip code, had the most sales. Then drill down by another query to determine for that holiday which dealership, by zip code, and make had the most sales. Describe any correlation to the results of the query in Step #9 of Project #1 to see if dealership location matches customer location.
6) Develop an SQL query to determine how many days, by day type, didn’t have more than 5 vehicles and $100,000 total sales. In order to achieve a fair comparison, analyze your results by the total number of weekdays, weekend days, and holidays in your TIMES table.

All SQL and PL/SQL should be executed via one or more SQL script files. The best approach is to have separate SQL script files for each step and then an SQL script file that calls all of them in sequence. Submit SQL*Plus SPOOL files produced by your SQL and PL/SQL showing all your SQL and PL/SQL code and the results, or if using iSQL*Plus or other GUI (e.g. SQL Developer), a single Word or PDF file of screen snapshots showing both your SQL and PL/SQL and the results. Do NOT submit your SQL script file.