Search results for 'oracle'

Bigger Vision of Athens Lab 4 Create Table in Oracle

For this lab we will be taking the final set of 3NF tables developed from our normalization process in Lab 3 and creating them into Oracle Database (through installing or using an online tool).

(Please review the tutorials posted in Week 9 to check your work from Lab 3 and get it into the correct form)
You will use SQL and the CREATE TABLE command to create each table with its various attributes (columns).
You will then use the INSERT INTO command to add 5 rows of sample data to populate each table.
After creating the tables and entering the data, use the SELECT * FROM {tablename} command to list all of the data in each table. You can do a screen capture to save the table data.

Turn in the following:
*A text file containing all the SQL commands you used to create each tables, insert the data, and select it in order.
*Screenshots for each of your tables with all of the data

Scenario:
Bigger Vision of Athens You have been asked to develop a database system for the Bigger Vision of Athens Emergency Shelter.
BVoA is a small, community shelter with approximately 142 beds.
The basic goal is to provide hot meals, showers, laundry services, and a place to stay at night for the surrounding community in North Georgia.
The various business functions that the non-profit currently has are:
1. Guest management software: Stores information about guests of the shelter
2. Volunteer management software: Records information about the shelter's volunteers
3. Scheduling software: Assigns guests and volunteers to specific days of the month
4. Financial management software: Tracks the shelter inventory, financial resources, and operations of the shelter, including donor information
5. Administrative services software: Provide general management and support services for board members not directly related to shelter operations

Examples of shelter software operations include: scheduling guests for the night, scheduling volunteers in advance for certain days, clocking guests and volunteers in and out, recording meals, showers, and laundry services, writing up guests for rule violations, reviewing inventory levels of needed supplies, putting in requests for new supplies, logging board meeting notes, and saving donor information.
The staff at the shelter include 80 full-time personnel and 30 part-time personnel, such as: the executive director, board members, administrative staff, day workers, night workers, job counselors, social services staff, technical support staff, cooking staff, janitorial staff, and security staff.

PRG 421 Week 3 Individual Coding Assignment

For this assignment, you will develop "starter" code. After you finish, your code should access an existing text file that you have created, create an input stream, read the contents of the text file, sort and store the contents of the text file into an ArrayList, then write the sorted contents via an output stream to a separate output text file.
Copy and paste the following Java™ code into a JAVA source file in NetBeans:
 
import java.io.BufferedReader;
import java.io.BufferedWriter;
 
public class Datasort {
 
public static void main (String [] args)  {
 
File fin =      // input  file
File fout =    // create an out file
 
// Java FileInputStream class obtains input bytes from a file
FileInputStream fis = new FileInputStream(fin);    
 
// buffering characters so as to provide for the efficient reading of characters, arrays, and lines
BufferedReader in = new BufferedReader(new InputStreamReader(fis));
 
// declare an array in-line, ready for the sort
String aLine;
ArrayList al = new ArrayList ();
 
int i = 0;
while ((aLine = in.readLine()) != null) {
// set the sort  for values is greater than 0
 
Collections.sort(al);    // sorted content to the output  file
{
System.out.println(s);
              
}
 // close the 2 files
                      
}
}
 
Add code as indicated in the comments.
Note: Refer to this week's Individual assignment, "Week Three Analyze Assignment," and to Ch. 8, "IO," in OCP: Oracle® Certified Professional Java® SE 8 Programmer II Study Guide.
Run and debug your modified program in NetBeans until it satisfies the requirements described above.
Save your finalized JAVA file with a .txt extension.
Submit your TXT file to the Assignment Files tab.

ITSE 2309 LAB 2 More SQL Queries and Modification

Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
You will continuing using items created in Lab 1

Lab 2a -- Problems 1–4,
1. For each customer, list each stock item ordered,
1) the manufacturer,
2) the quantity ordered, and
3) the total price paid.
Include the following columns in the order given below:
- From Customer Table: Company
- From Stock Table: Description
- From the Manufact Table: Manu_Name
- From the Items Table: Quantity, Total Price
Order the output by Company and Description.
Submit/hand in Output from SQL query

2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
Include
1) the Order Number,
2) Order Date,
3) Customer company name, and
4) Shipping Date.
Order by
Customer Company Name and Order Number.
Submit/hand in Output from SQL query

3. Count the number of customers who do not have any orders placed.
Submit/hand in Output from SQL query

4. List all customers –
I) Who are ordering equipment whose description begins with 'tennis' or 'volleyball'.
II )Include
1) Customer number,
2) Stock number, and
3) Description.
Submit/hand in Output from SQL query
Do not repeat any rows.

Lab 2b Problems 5, 6, 7 and 8
5. Use the following SQL CREATE commands to CREATE the following tables in your
CREATE TABLE Professor
(Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
Prof_Lname VARCHAR2(15) NOT NULL,
Prof_Hiredate DATE,
Prof_Sal NUMBER(8,2),
Prof_Dept CHAR(6),
);
CREATE TABLE Student
(Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
Stu_Lname VARCHAR2(15) NOT NULL,
Stu_Major CHAR(6),
Stu_CredHrs NUMBER(4),
Stu_GradePts NUMBER(5),
Prof_ID NUMBER(3),
CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
REFERENCES Professor
);
Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.

6. Insert the following data into the tables created above using SQL INSERT commands.
Professor Table:
Prof_ID Prof_Lname Prof_Hiredate Prof_Sal Prof_Dept
123 Hilbert 20-MAY-1992 58000.00 MATH
243 Newell 15-JUL-1997 65500.00 CMPSCI
389 Lessing 04-APR-1988 40250.00 ENG
Student Table:
Stu_ID Stu_Lname Stu_Major Stu_CredHrs Stu_GradePts Prof_ID
2001 Parker CMPSCI 52 160 243
2166 Smith ENG 30 75 389
3200 Garcia MATH 62 248 123
4520 Smith CMPSCI 45 157 NULL
BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
Submit a
Listing of each INSERT command,
The systems response and the resulting tables after the INSERTS are completed
(Example: SELECT * FROM Student;).

7. Perform the following SQL DELETE statements. Be sure to do them in order.
Issue a COMMIT command after all DELETEs have run.
a. Try to delete Professor 389. What message do you get? ___________________________
b. Delete Student 2166.
c. Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
Submit/hand in : A listing of the DELETE statements.
The answers to questions a. b. and c.
A listing of the two tables after the deletes have run.

8. Perform the following UPDATE commands.
Issue a COMMIT command after all UPDATEs have run.
a. Replace the value of the Prof_ID for Student 4520 with 243.
b. Add 10% to the salary for each professor
Submit/hand in : A listing of the UPDATE statements
A listing of the two tables after the UPDATEs have run.

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).

Penn foster Graded Project 5 TicTacToe Game GUI Java

In this project, you'll create the GUI front-end for the TicTacToe game. This application will leverage the classes you wrote in the graded project for Lesson 3. You'll copy code from Graded Project 3 for this project.
Figure 20 is a guide for the completed user interface.

INSTRUCTIONS
1. In NetBeans, create a new Java Application project named TicTacToeGUIGame.
2. Copy the games.board package from the Lesson 3 project named BoardGameTester.
- Right-click on the game.board package in the BoardGameTester project of the Projects pane panel.
- Choose the Copy option from the context menu or use the keyboard shortcut CTRL+C.
- Paste it in the TicTacToeGUIGame project using the Paste option in the context menu or the keyboard shortcut CTRL+V.
Make sure to copy and paste the package in the Source Packages folder.
3. In the Cell.java file, have the Cell class extend the JButton class. This action will ensure that each cell on the board has the look and feel of a standard Java button.
4. Override the paintComponent method in the Cell class as follows:
public void paintComponent(Graphics g) {
        //paint the basic button first
        super.paintComponent(g);
        int offset = 5;
        Graphics2D g2 = (Graphics2D) g;
        g2.setStroke(new BasicStroke(5));
        switch (content) {
            case NOUGHT:
                g2.setColor(Color.RED);
                //Draw O
                g2.drawOval(offset, offset, this.getWidth()
                        - offset * 2, this.getHeight() - offset * 2);
                break;
            case CROSS:
                g2.setColor(Color.BLACK);
                //Draw X
                g2.drawLine(offset, offset, this.getWidth() - offset,
                        this.getHeight() - offset);
                g2.drawLine(this.getWidth() - offset, offset, offset,
                        this.getHeight() - offset);
                break;
        }
    }
This code uses the enhanced Graphics2D class to set the stroke thickness to more than one pixel. The Oracle documentation provides more guidance on creating complex geometric shapes using the Graphics2D class at http://docs.oracle.com/javase/tutorial/2d/geometry/index.html. Remember to import the java.awt and javax.swing packages!
5. In the Board.java file, have the Board class extend the JPanel class. This action will ensure that the board can lay out each cell and process its UI events.
6. Make the following modifications to the Board constructor:
public Board(int rows, int columns, ActionListener ah) {
cells = new Cell[rows][columns];
this.setLayout(new GridLayout());
for( int r = 0; r < cells.length; r++ ) {
for (int c = 0; c < cells[r].length; c++) {
cells[r][c] = new Cell(r,c);
this.add(cells[r][c]);
cells[r][c].addActionListener(ah);
}
}
}
These changes will add each cell to the UI and assign an ActionListener object to each cell.
Note: Remember to import the java.awt, java.awt.event, and javax.swing packages.
7. In the TicTacToeGUIGame.java file, have the TicTacToeGUIGame class extend JFrame. This action will ensure that the game is hosted in a Java window.
8. Import the games.board, java.awt, and javax.swing packages.
9. Add the following code to the main method:
SwingUtilities.invokeLater( new Runnable () {
public void run() { new TicTacToeGUIGame(); }
});
10. Declare the following instance variables in TicTacToeGUIGame:
private Board gb;
private int turn;
11. Add the following method to handle each turn:
private void takeTurn(Cell c) {
Mark curMark = (turn++ % 2 == 0)?Mark.NOUGHT
: Mark.CROSS;
gb.setCell(curMark,c.getRow(),c.getColumn());
}
12. Define the following constructor to create the board, provide the event listener, and display the board in the window:
private TicTacToeGUIGame() {
gb = new Board(3, 3, new ActionListener() {
public void actionPerformed(ActionEvent ae) {
Cell c = (Cell) ae.getSource();
takeTurn(c);
}
});
this.add(gb);
this.setDefaultCloseOperation(EXIT_ON_CLOSE);
this.setTitle(“TIC-TAC-TOE”);
this.setSize(300, 300);
this.setVisible(true);
}
13. Compile and run the project. How did you do? Test to make sure that each button displays a nought or cross when clicked.

Penn foster Graded Project 3 Cell-based Board Games Java

In this project, you'll create data types in a class structure for cell-based board games similar to Tic-Tac-Toe. Games like Connect Four and Mastermind also use boards divided by rows and columns. The Board and Cell classes represent the board, while the Player, Mark, and Outcome enumerations track the game.
You’ll use the classes and enumerations created in this project in future graded projects. You use the NetBeans project in the next lesson

INSTRUCTIONS
1. In NetBeans, create a new Java Application project named BoardGameTester.
2. Create a new package named games and a sub-package of games named board. The easiest way is simply to create a package named games.board.
3. Add an enumeration named Player to the games.board package. You could add Empty Java File or choose Java Enum as the file type. This enumeration represents the current player in a turn-based game. Use the following code:
public enum Player {FIRST,SECOND}
Note: Although Tic-Tac-Toe and Mastermind allow only two players, Connect Four can be played with up to four players. For simplicity, our code will handle only two players.
4. Add an enumeration named Outcome to the games.board package. This enumeration represents the result when the turn is completed. Use the following code:
public enum Outcome {PLAYER1_WIN, PLAYER2_WIN, CONTINUE, TIE}
5. Add an enumeration named Mark to the games.board package. This enumeration represents the result when the game is completed. Use the following code:
public enum Mark {EMPTY, NOUGHT, CROSS, YELLOW, RED, BLUE, GREEN, MAGENTA, ORANGE}
Keep in mind that only yellow and red are used in Connect Four, while Mastermind uses all six colors.
6. Add the Cell class to the games.board package. It should have the private variables content, row, and column, and the public methods getContent, setContent, getRow, and getColumn. Use the following code as a guide:
public class Cell {
private Mark content;
private int row, column;
public Cell(int row, int column) {
this.row = row;
this.column = column;
content = Mark.EMPTY;
}
public Mark getContent() { return content; }
public void setContent(Mark content) { this.content = content;
}
public int getRow() { return row; }
public int getColumn() { return column; }
}
Take note that all classes that support direct instantiation should have a constructor. In this case, the constructor will be used by the Board class to create each of its cells.
7. Add the Board class to the games.board package. It should have a two-dimensional array of Cell objects. The Board class should initialize a board with a specified number of columns and rows, provide access to Cell objects, and display all of its cells correctly. Use the following code as a guide:
public class Board {
private Cell[][] cells;
public Board(int rows, int columns) {
cells = new Cell[rows][columns];
for( int r = 0; r < cells[0].length; r++) {
for (int c = 0; c < cells[1].length;c++) {
cells[r][c] = new Cell(r,c);
}
}
}
public void setCell(Mark mark, int row, int
column) throws IllegalArgumentException {
if (cells[row][column].getContent() == Mark.EMPTY)
cells[row][column].setContent(mark);
else throw new IllegalArgumentException("Player already there!");
}
public Cell getCell(int row, int column) {
return cells[row][column];
}
public String toString() {
StringBuilder str = new StringBuilder();
for( int r = 0; r < cells.length; r++ ) {
str.append("|");
for (int c = 0; c < cells[r].length; c++) {
switch(cells[r][c].getContent())
{
case NOUGHT:
str.append("O");
break;
case CROSS:
str.append("X");
break;
case YELLOW:
str.append("Y");
break;
case RED:
str.append("R");
break;
case BLUE:
str.append("B");
break;
case GREEN:
str.append("G");
break;
case MAGENTA:
str.append("M");
break;
case ORANGE:
str.append("M");
break;
default: //Empty
str.append(" ");
}
str.append("|");
}
str.append("\n");
}
return str.toString();
}
}

This code should seem familiar to you. The methods in the TicTacToeGame class are similar to those in the Board class. The StringBuilder class was used instead of the String class for better performance. You can learn more about the StringBuilder class by visiting the Oracle Website at http://docs.oracle.com/javase/tutorial/java/data/buffers.html.

8. Add the following import statement to the BoardGameTester class:
import games.boards.*;

9. In the main() method of BoardGameTester, perform the following actions:
a. Create a 3 × 3 board for a Tic-Tac-Toe game.
b. Create a 6 × 7 board for a Connect Four game.
c. Create a 5 × 8 board for a game of Mastermind.
d. Set a cell to a nought or cross on the Tic-Tac-Toe board.
e. Set a cell to yellow or red on the Connect Four board.
f. Set a cell to yellow, red, green, blue, magenta, or orange on the Mastermind board.
g. Display the boards for Tic-Tac-Toe, Connect Four, and Mastermind.

10. Compile and run the project to ensure it works as you expected it to.

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.