Oracle 11g Sql Chapter 1 Hands On Assignments Answers

Presentation on theme: "Chapter 5 Data Manipulation and Transaction Control"— Presentation transcript:

1 Chapter 5 Data Manipulation and Transaction Control
Jason C. H. Chen, Ph.D.Professor of MISSchool of BusinessGonzaga UniversitySpokane, WA USA

2 Objectives Use the INSERT command to add a record to an existing table
Manage virtual columns in data manipulationsUse quotes in data valuesUse a subquery to copy records from an existing tableUse the UPDATE command to modify the existing rows of a tableUse substitution variables with an UPDATE command

3 Objectives (continued)
Delete recordsManage transactions with transaction control commands COMMIT, ROLLBACK, and SAVEPOINTDifferentiate between a shared lock and an exclusive lockUse the SELECT…FOR UPDATE command to create a shared lock

4 1. Run the following script file
Refresh the Database1. Run the following script fileStart c:\oradata\chapter5\JLDB_Build_5.sql

5 SQL Components ORACLE (SQL Components) DDL DML DCL D.B.
(Create Table structure and insert database)

6 DDL CommandsUsed to create and modify the structure of database objectsCREATEALTERDROPDDL commands execute as soon as they are issued, and do not need to be explicitly saved

7 DML Commands Used to insert, view, and modify database data
UPDATEDELETESELECTDML commands need to be explicitly saved or rolled backCOMMITROLLBACKSAVEPOINT

8 DCL CommandsUsed to control the privileges and security of database objectsGRANTREVOKE

9 Security - Granting Table Privileges
Security is the prevention of unauthorized access to the database. Within an organization, the database administrator determines the types of access various users need for the database.Some users might be able to retrieve and update data in the database. Other users might be able to retrieve any data from the database but not make any changes to it. Still other users might be able to access only a portion of the database.

10 INSERT Command Used to add rows to existing tables
Identify the table in the INSERT INTO clauseSpecify data in the VALUES clauseCan only add one row at a time to a tableEnclose nonnumeric data in single quotesIf a column list is not provided, a value must be assigned to each column in the tableFigure Syntax of the INSERT command

11 Re-run a new script file not including acctmanager table
Your Turn …Re-run a new script file not including acctmanager tableSQL> start c:\oradata\chapter5\JLDB_Build_5.sqlYou now are able tocreate acctmanager table, andinsert new rows (data value) into the acctmanager table

12 The acctmanaer Table Creation
AmidAmfirstAmlastAmeDateAmsalAmcommRegionVARCHAR2(4)VARCHAR2(12)DATENUMBER(8,2)NUMBER(7,2)CHAR(2)CREATE TABLE acctmanager(amid CHAR(4),amfirst VARCHAR2(12) NOT NULL,amlast VARCHAR2(12) NOT NULL,amedate DATE DEFAULT SYSDATE,amsal NUMBER(8,2),amcomm NUMBER(7,2) DEFAULT 0,region CHAR(2),CONSTRAINT acctmanager_amid_pk PRIMARY KEY (amid),CONSTRAINT acctmanager_region_ckCHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')));Figure The ACCTMANAGER table creation

13 Inserting New Records Type the following commands:
IDNameEmployment DateSalaryCommissionRegionT500Nick TaylorSeptember 5, 2009$42,000$3,500NEL500Mandy LopezOctober 1, 2009$47,000$1,500J500Sammie JonesToday$39,5000$2,000NWTable 5-2 Data for Account ManagersType the following commands:-- chapter 5, Figure 5-3; p. 140INSERT INTO acctmanagerVALUES ('T500', 'NICK', 'TAYLOR', '05-SEP-09', 42000, 3500, 'NE');SELECT * FROM acctmanager;

14 Inserting New Records (Your Turn …)
-- chapter 5, Figure 5-5; p. 142INSERT INTO acctmanager (amid, amfirst, amlast, amedate, amsal, amcomm)VALUES ('L500', 'MANDY', 'LOPEZ', '01-OCT-09', 47000, 1500);SELECT *FROM acctmanager;-- chapter 5, Figure 5-9; p. 143INSERT INTO acctmanager (amid, amfirst, amlast, amedate, amsal, amcomm, region)VALUES ('J500', 'Sammie', 'Jones', ‘DEFAULT’, 39500, 2000, 'NW');--[ ERROR OCCURS – why?]--[ (NO ' ')]VALUES ('J500', 'Sammie', 'Jones', DEFAULT, 39500, 2000, 'NW');-- orINSERT INTO acctmanager (amid, amfirst, amlast, amsal, amcomm, region)VALUES ('J500', 'Sammie', 'Jones', 39500, 2000, 'NW');-- chapter 5, Figure 5-10; p. 144

15 INSERT Command Examples
No Column ListFigure The INSERT command for Nick TaylorColumn ListFigure Use an INSERT statement that applies a DEFAULT column option

16 Three Ways Inserting NULL Value
Omit column name from INSERT INTO clause column listSubstitute two single quotation marksUse NULL keywordRun script file on SQL PLUS-- chapter 5, Figure 5-5; p. 142INSERT INTO acctmanager (amid, amfirst, amlast, amedate, amsal, amcomm)VALUES ('L500', 'MANDY', 'LOPEZ', '01-OCT-09', 47000, 1500);INSERT INTO acctmanagerVALUES ('L500', 'MANDY', 'LOPEZ', '01-OCT-09', 47000, 1500, '');VALUES ('L500', 'MANDY', 'LOPEZ', '01-OCT-09', 47000, 1500, NULL);NULL value inputNULL value inputFigure The INSERT INTO command for Mandy Lopez

17 Three Ways Inserting “SYSDATE” Value (Note: errors in the text book; p
Insert the current date ‘SYSDATE’ in the Amedate columnUse the keyword ‘DEFAULT’ for the column value in the VALUES clauseInclude a column list in the INSERT INTO clause that omits the Amedate column-- chapter 5, Figure 5-7; 5-8; p. 143-- note that no ‘ ‘ is required, or, error occurredINSERT INTO acctmanager (amid, amfirst, amlast, amedate, amsal, amcomm, region)VALUES ('J500', 'Sammie', 'Jones', SYSDATE, 39500, 2000, 'NW');-- orVALUES ('J500', 'Sammie', 'Jones', DEFAULT, 39500, 2000, 'NW');-- chapter 5, Figure 5-9; p. 143INSERT INTO acctmanager (amid, amfirst, amlast, amsal, amcomm, region)VALUES ('J500', 'Sammie', 'Jones', 39500, 2000, 'NW');

18 Handling Virtual Columns
Create an Amearn column as indicated in Fig (also in Fig. 3-2; p The column is called “virtual column” (or derived/computed), which is generated from other column values.-- chapter 5, Figure 5-10; p. 144SELECT *FROM acctmanager;-- chapter 5, Figure 5-11; p. 144ALTER TABLE acctmanagerADD (amearn AS (amsal + amcomm));-- chapter 5, Figure 5-12; p. 145SELECT *FROM acctmanager;

19 Handling Virtual Columns (cont.)
-- chapter 5, Figure 5-12; p. 145SELECT *FROM acctmanager;virtual (derived) column

20 Manage Virtual Column Input
Figure Error caused by using a virtual column in an INSERT statement

21 Handling Single Quotes in an INSERT Value
-- chapter 5, Figure 5-15; p. 146INSERT INTO acctmanager (amid, amfirst, smlast, amsal, amcomm, region)VALUES ('M500', 'Peg', 'O'hara', 46000, 2000, 'SW');-- chapter 5, Figure 5-16; p. 147VALUES ('M500', 'Peg', 'O''hara', 46000, 2000, 'SW');-- chapter 5, Figure 5-17; p. 147SELECT *FROM acctmanager;

22 Constraint Violations
When you add or modify table data, the data is checked for compliance with any applicable constraintsYou should practice all examples (more commands such as UPDATE on p ))

23 Inserting Data from an Existing Table
Substitute subquery for VALUES clauseNote: make sure you have re-run a new script file (i.e., JLDB_Build_5.sql) with acctbonus table created.SubqueryFigure INSERT INTO command with a subquery-- chapter 5, Figure 5-20; p. 149SELECT *FROM acctbonus;

24 Modifying Existing Rows
Modify rows using UPDATE commandUse UPDATE command to:Add values to an existing row (replace NULL values)Change existing valuesUPDATE clause identifies tableSET clause identifies column(s) being changed and new value(s)Optional WHERE clause specifies row(s) to be changed – if omitted, all rows will be updated!

25 UPDATE Command Examples
UPDATE Command SyntaxFigure Syntax of the UPDATE commandUPDATE Command Examples-- chapter 5, Figure 5-22; p. 151UPDATE acctmanagerSET amedate = '01-AUG-09'WHERE amid = 'J500';-- chapter 5, Figure 5-23; p. 151SET region = 'W'WHERE region IN ('NE', 'NW');-- chapter 5, Figure 5-24; p. 152UPDATE acctmanagerSET amedate = '10-OCT-09',region = 'S'WHERE amid = 'L500';SELECT * FROM acctmanager;

26 Substitution Variables
Prompts user for valueIdentified by ampersand (&) preceding variable nameCan be used to create interactive scripts-- chapter 5, Figure 5-26; p. 153UPDATE customersSET region = 'W'WHERE state = 'CA';-- chapter 5, Figure 5-27; p. 154UPDATE customersSET region = '&Region'WHERE state = '&State';

27 Substitution Variable Example
Figure Prompt for substitution variable input

28 Figure 5-28 Verify UPDATE results

29 DELETE command removes a row from a table
Deleting RowsDELETE command removes a row from a tableWHERE clause determines which row(s) are removedFigure DELETE command to remove a row from the ACCTMANAGER table

30 DELETE Command – Omitting WHERE Clause
Omitting WHERE clause removes all rowsExample below removes all rows from the acctmanager tableFigure DELETE command without the WHERE clause

31 Transaction Control Statements
Results of data manipulation language (DML) are not permanently updated to a table until explicit or implicit COMMIT occursTransaction control statements can:Commit data through COMMIT commandUndo data changes through ROLLBACK command

32 COMMIT CommandExplicit COMMIT occurs by executing COMMIT;Implicit COMMIT occurs when DDL command is executed or user properly exits systemPermanently updates table(s) and allows other users to view changes

33 Transaction Control Example

34 Transaction Control Example

35 Transaction Control Example (continued)
Figure Database before using ROLLBACK TO ONE;-- chapter 5, Figure 5-36; p. 161ROLLBACK TO ONE;Only undo DML actions after SAVEPOINTFigure Undo changes to SAVEPOINT ONE

36 Transaction Control Example (continued)
Figure Database before using ROLLBACK-- chapter 5, Figure 5-38; p. 162ROLLBACK;Figure Verify the ROLLBACK results

37 Practice all the examples in the text.
A Script file is available on the Bb (file name: Ch5Queries.sql)After completing all examples, do the HW.

38 Homework - Hands-On Assignments
me with one attachment(Oracle_ch5_Spool_Lname_Fname.) to:with subject title ofBmis441-01_Oracle_ch45(or Bmis441-02_Oracle_ch5)Read and Practice all examples on Chapters 51. Run the script files (in the folder \oradata\chapter5\): JLDB_Build_5.sql2. Read Oracle assignment and create a script file Oracle_ch5_Lname_Fname.sql for questions (#1 to #5, #9,#10; p.170) on “Hands-on Assignments”. .3. Execute and test one problem at a time and make sure they are all running successfully.4. When you done, spool the script files (see next slide for spooling instructions) and the file (Oracle_ch5_Spool_Lname_Fname.txt) to me by the midnight before the next class.

39 How to Spool your Script and Output Files
After you tested the script file of Oracle_ch5_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files:Step 0. Run the following script file from SQL*Plus (since you have created JLDB tables)Start c:\oradata\chapter5\JLDB_Build_5.sql1. type the following on SQL>Spool c:\oradata\Oracle_ch5_Spool_Lname_Fname.txt (make sure your name is entered)2. open Oracle_ch5_Lname_Fname.sql that you already tested3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS4. type Spool Off on the SQL>The output should contain your personal information, all SQL commands and their solution on the .txt file and saved in C: drive (oradata\ folder)me with the spooled file (.txt) with attachment to:with subject title ofBmis441-01_Oracle_ch5 (or Bmis441-02_Oracle_ch5)

40 SummaryData manipulation language (DML) includes the INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK commandsThe INSERT INTO command is used to add new rows to an existing tableThe column list specified in the INSERT INTO clause must match the order of data entered in the VALUES clauseA virtual column must be ignored in all DML actions because the database system generates this column value automaticallyYou can use a NULL value in an INSERT INTO command by including the keyword NULL, omitting the column from the column list of the INSERT INTO clause, or entering two single quotes (without a space) in the position of the NULL value

41 Summary (continued)To assign a DEFAULT option value, a column must be excluded from the column list in an INSERT statement or the keyword DEFAULT must be included as the value for the columnIn a DML statement, two single quotes together must be used to represent a single quote in a valueIf rows are copied from a table and entered in an existing table by using a subquery in the INSERT INTO command, the VALUES clause must be omitted because it’s irrelevantYou can change the contents of a row or group of rows with the UPDATE commandYou can use substitution variables to allow you to execute the same command several times with different data values

42 Summary (continued)DML operations aren’t stored permanently in a table until a COMMIT command is issued implicitly or explicitlyA transaction consists of a set of DML operations committed as a blockUncommitted DML operations can be undone by issuing the ROLLBACK commandA SAVEPOINT serves as a marker for a point in a transaction and allows rolling back only a portion of the transactionUse the DELETE command to remove records from a table; if the WHERE clause is omitted, all rows in the table are deletedTable locks can be used to prevent users from mistakenly overwriting changes made by other users

43 Summary (continued) Table locks can be in SHARE mode or EXCLUSIVE mode
EXCLUSIVE mode is the most restrictive table lock and prevents any other user from placing any locks on the same tableA lock is released when a transaction control statement is issued, a DDL statement is executed, or the user exits the system by using the EXIT commandSHARE mode allows other users to place shared locks on other portions of the table, but it prevents users from placing an exclusive lock on the tableThe SELECT FOR UPDATE command can be used to place a shared lock for a specific row or rows; the lock isn’t released unless a DDL command is issued or the user exits the system

44 End of Chapter 5

Unformatted text preview: Chapter Hands-On Assignments 1. Which tables and fields would you access to determine which book titles have been purchased by a customer and when the order shipped? CUSTOMERS: Customer#; ORDERS: Order#, Shipdate, Customer#; ORDERITEMS: Order#, ISBN; BOOKS: ISBN, Title 2. How would you determine which orders have not yet been shipped to the customer? Identify all the orders that do not have an entry for the date shipped. 3. If management needed to determine which book category generated the most sales in April 2009, which tables and fields would they consult to derive this information? ORDERS: Orderdate, Order#; ORDERITEMS: Order#, ISBN, Quantity, Paideach; BOOKS: ISBN, Category 4. Explain how you would determine how much profit was generated from orders placed in April 2009. Determine the amount of profit generated by each book on the order. Multiply the profit for each book by the quantity purchased. Lastly, total the amount of profit generated by all of the orders that were placed in the quantity purchased....
View Full Document

0 Thoughts to “Oracle 11g Sql Chapter 1 Hands On Assignments Answers

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *