Chapter Seven DDL

1 / 40

# Chapter Seven DDL - PowerPoint PPT Presentation

Chapter Seven DDL. Objectives Insert Data into tables Create Query files to insert data into tables Make changes to the data in the tables Extract data from tables and insert them into another table Delete rows from tables. CREATE TABLE student ( First VARCHAR2(40)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Chapter Seven DDL' - raine

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Chapter SevenDDL

Objectives

Insert Data into tables

Create Query files to insert data into tables

Make changes to the data in the tables

Extract data from tables and insert them into another table

Delete rows from tables

CREATE TABLE student (

First VARCHAR2(40)

,Last VARCHAR2(30)

,ID NUMBER

CONSTRAINT ID_CK CHECK (ID>11111 AND ID<99999)

,GPA NUMBER(3,2) DEFAULT 0

,B_Date DATE

,Major CHAR(4)

);

Inserting New Rows into a Table:

(General format)

• INSERT INTO table (col1, col2, ...) VALUES (value1, value2, .... );

Or

• INSERT INTO table(col1, col2, col3,….)` Subquery;
Inserting a New Row
• Add a New Student with ID=123456 and Name= Sarah, GPA=0.0, and no Major:
• INSERT INTOStudent (Name, ID, GPA) VALUES(\'Sarah\', 123456, 0.0 );

Or

• INSERT INTO Student (Name, ID, GPA, Major) VALUES(\'Sarah\', 123456, 0.0, NULL);
Inserting a New Row With no Column Names:

INSERT INTOStudent

VALUES(\'Sarah\', 123456, 0.0, NULL, NULL);

Inserting Special Values:

INSERT INTO student (Id, Name, B_date, Gpa)

VALUES (234567, ‘GREEN’, SYSDATE, 3.90);

Inserting a Date Value:

INSERT INTO student (Id, Name, B_date, GPA)

VALUES (22222, ‘BULUE’, ’03-JAN-79’, 2.90);

Inserting a Date Value:

INSERT INTO

student (Id, Name, B_date, GPA)

VALUES

(22222, ‘BULUE’, TO_DATE(‘JAN 03, 79’, ‘MON DD,YY’), 2.90);

Inserting a Date Value:

INSERT INTO

student (Id, Name, B_date, GPA)

VALUES

(22222, ‘BULUE’, TO_DATE(’03/01/1979 2:30’, ‘DD/MM/YYYY HH:MI’), 2.90);

Inserting With Default Value:
• INSERT INTO student (Id, Name, b_date, GPA)
• VALUES (22222, ‘BULUE’, ‘JAN 03, 79’,DEFAULT);
Inserting Values By Using Substitution Variables:
• INSERT INTOstudent (Id, Name, Gpa)

VALUES (&student_id, ‘&student_name’, &Gpa_input);

Creating a script customized prompts:

ACCEPT studentId

PROMPT ‘Please enter a student ID: ‘;

ACCEPT studentName

PROMPT ‘Please enter a student Name: ‘;

ACCEPT GpaInput

PROMPT ‘Please enter a Gpa: ‘;

Creating a script customized prompts:

INSERT INTO

student (Id, Name, Gpa)

VALUES

(&studentId,‘&studentName’, &GpaInput);

Inserting Rows Using Subquery:

Create a Temp Table to Store COSC Students

INSERT INTO Temp

(Name, ID, GPA)

(SELECT Name, ID, GPA

FROM Student

WHERE Major=\'COSC\');

• Note: you must have Temp table created before inserting data into it.
Insertion:

INSERT INTO student (ID,NAME, B_Date, GPA)

VALUES (22222, ’BULUE’, ’03-JAN-79’,2.90);

The following has the same effect:

INSERT INTO

(SELECT ID, Name, B_Date, GPA

FROM student)

VALUES (22222,’BULUE’, ’03-JAN-79’,2.90);

Inserting Rows With a Hint:

INSERT /*+ APPEND */ INTO Temp

(Name, ID, GPA)

(SELECT Name, ID, GPA

FROM Student

WHERE Major=\'COSC\');

Inserting with Error Logging Table:

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘Temp2’, ’errlog’);

INSERT INTO Temp2

SELECT Name, ID*100

FROM student

WHERE GPA > 2.0

REJECT LIMIT 10;

Inserting with Error Logging Table:

SELECT ORA_ERR_MESG\$, ORA_ERR_TAG\$, Name, ID

FROM errlog;

ORA_ERR_MESG\$ ORA_ERR_TAG\$ Name ID

-----------------------------------------------------------------------

ORA-02290:Check Constraint bad_data John Smith 11115

SYS_C004266 violated

Insert into remote database:

INSERT INTO [email protected] (ID, Name, B_Date, GPA)

VALUES (22222, ‘BULUE’, ‘3-JAN-79’, 2.90)

Multitable Inserts:

SELECT Product_ID, Customer_ID, Weekly_Sale, Mon_Sale, Tue_Sale, Wed_Sale,Thu_Sale, Fri_Sale, Sat_Sale, Sun_Sale

FROM Weekly_Sales;

Product_ID Customer_ID Weekly_Sale Mon_Sale Tue_Sale

-----------------------------------------------------------------------------------

111 999 01-JAN-05 100 200

222 888 08-JAN-05 300 400

Wed_Sale Thu_Sale Fri_Sale Sat_Sale Sun_Sale

-----------------------------------------------------------------------------------

300 400 500 600 700

500 600 700 800 900

Multitable Inserts:

INSERT ALL

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale, Mon_Sale)

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale+1, Tue_Sale)

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale+2, Wed_Sale)

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale+3, Thu_Sale)

Multitable Inserts:

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale+4, Fri_Sale)

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale+5, Sat_Sale)

INTO Sales (P_ID, C_ID, Day, Amount)

VALUES (Product_ID, Customer_ID, Weekly_Sale+6, Sun_Sale)

SELECT Product_ID, Customer_ID, Weekly_Sale, Mon_Sale, Tue_Sale, Wed_Sale,Thu_Sale, Fri_Sale, Sat_Sale, Sun_Sale

FROM Weekly_Sales;

Multitable Inserts:

SELECT *

FROM Sales;

Product_ID Customer_ID Day Amount

------------------------------------------------------------------------------------------------

111 999 01-JAN-05 100

111 999 02-JAN-05 200

111 999 03-JAN-05 300

111 999 04-JAN-05 400

111 999 05-JAN-05 500

111 999 06-JAN-05 600

111 999 07-JAN-05 700

222 888 08-JAN-05 200

222 888 09-JAN-05 400

222 888 10-JAN-05 500

222 888 11-JAN-05 600

222 888 12-JAN-05 700

222 888 13-JAN-05 800

222 888 14-JAN-05 900

Multitable Inserts:

CREATE TABLE Small_Order

( O_ID NUMBER(6) NOT NULL,

C_ID NUMBER(4) NOT NULL,

Total_Order NBUMBER(6)

);

CREATE TABLE Mid_Order AS

SELECT *

FROM Small_Order;

CREATE TABLE Large_Order AS

SELECT *

FROM Small_Order;

Multitable Inserts:

CREATE TABLE Order_Product

( Order_ID NUMBER(6) NOT NULL,

Customer_ID NUMBER(4) NOT NULL,

Total_Order NUMBER(6),

Sales_ID NUMBER(5),

Customer_Phone CHAR(10)

);

Multitable Inserts:

INSERT ALL

WHEN Total_Order < 100 THEN

INTO Small_Order

WHEN Total_Order >=100 AND Total_Order < 1000

INTO Mid_Order

WHEN Total_Order >=1000

INTO Large_Order

SELECT Order_ID, Customer_ID, Total_Order

FROM Order_Product;

Multitable Inserts:

INSERT ALL

WHEN X < 100 THEN

INTO Small_Order

VALUES (A, B, X)

WHEN X >=100 AND X < 1000

INTO Mid_Order

VALUES (A, B, X)

WHEN X >=1000

INTO Large_Order

VALUES (A, B, X)

SELECT Order_ID A,

Customer_ID B,

Total_Order X

FROM Order_Product;

Update (Modify a Row)

(General Format)

• UPDATE table

SET Column = Expression

[WHERE Condition];

Example

Changing ID of every one; (Multiply by 100) (Add two digits to student ID)

UPDATE Student

SET ID = ID * 100;

Update:

Remove majors of ‘COSC’ and set them to NULL:

UPDATE student

SET major = NULL

WHERE major = ‘COSC’;

Update

Change major of all COSC students to MATH .

UPDATE Student

SET Major = \'MATH\'

WHERE Major = \'COSC\';

Update:

Change name of Sandy Smith to Sandy Olson:

UPDATE student

SET last = ‘OLSON’

WHERE first = ‘SANDY’

AND last = ‘SMITH’ ;

Update

What is the outcome this command?

UPDATE Student

SET Major = \'MATH‘;

UPDATE Faculty

SET Salary = Salary+2000, Rank=‘Professor’

WHERE ID=11111;

Update table from another table:

UPDATE Table1 p

SET VALUE (p) = (SELECT VALUE (q)

FROM Table2 q

WHERE p.ID = q.ID)

WHERE p.ID = 111;

Deleting Rows:
• DELETE

FROM table

[WHERE Condition];

Deleting Rows:

DELETE

FROM (SELECT *

FROM student)

WHERE ID = 11111;

DELETE

FROM student

WHERE ID = 111;

Deleting Rows:
• Delete all the student\'s courses of student with ID=999999999
• DELETE

FROM Student

WHERE ID=999999999;

• DELETE

FROM department

WHERE dept=’MATH’;

Deleting Rows:
• What is the outcome of this statement:
• DELETE

FROM Student;

Transaction Control:
• COMMIT
• ROLLBACK
• SAVEPOINT
Transaction Control:

INSERT ……..

;

SAVEPOINT a;

INSERT ……..

;

SAVEPOINT b;

INSERT ………..

;

ROLLBACK TO SAVEPOINT a