Chapter seven ddl
Download
1 / 40

Chapter Seven DDL - PowerPoint PPT Presentation


  • 105 Views
  • Uploaded on

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)

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

PowerPoint Slideshow about 'Chapter Seven DDL' - raine


An Image/Link below is provided (as is) to download presentation

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 seven ddl

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


Chapter seven ddl

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
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
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
Inserting a New Row With no Column Names:

INSERT INTOStudent

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


Inserting special values
Inserting Special Values:

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

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


Inserting a date value
Inserting a Date Value:

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

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


Inserting a date value1
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 value2
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
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
Inserting Values By Using Substitution Variables:

  • INSERT INTOstudent (Id, Name, Gpa)

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


Creating a script customized prompts
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 prompts1
Creating a script customized prompts:

INSERT INTO

student (Id, Name, Gpa)

VALUES

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


Inserting rows using subquery
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
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
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
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

LOG ERRORS INTO errlog (‘bad_data’)

REJECT LIMIT 10;


Inserting with error logging table1
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 remote database:

INSERT INTO student@remote (ID, Name, B_Date, GPA)

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


Multitable inserts
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 inserts1
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 inserts2
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 inserts3
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 inserts4
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 inserts5
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 inserts6
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 inserts7
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
Update (Modify a Row)

(General Format)

  • UPDATE table

    SET Column = Expression

    [WHERE Condition];


Example
Example

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

UPDATE Student

SET ID = ID * 100;


Update
Update:

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

UPDATE student

SET major = NULL

WHERE major = ‘COSC’;


Update1
Update

Change major of all COSC students to MATH .

UPDATE Student

SET Major = 'MATH'

WHERE Major = 'COSC';


Update2
Update:

Change name of Sandy Smith to Sandy Olson:

UPDATE student

SET last = ‘OLSON’

WHERE first = ‘SANDY’

AND last = ‘SMITH’ ;


Update3
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 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
Deleting Rows:

  • DELETE

    FROM table

    [WHERE Condition];


Deleting rows1
Deleting Rows:

DELETE

FROM (SELECT *

FROM student)

WHERE ID = 11111;

DELETE

FROM student

WHERE ID = 111;


Deleting rows2
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 rows3
Deleting Rows:

  • What is the outcome of this statement:

  • DELETE

    FROM Student;


Transaction control
Transaction Control:

  • COMMIT

  • ROLLBACK

  • SAVEPOINT


Transaction control1
Transaction Control:

INSERT ……..

;

SAVEPOINT a;

INSERT ……..

;

SAVEPOINT b;

INSERT ………..

;

ROLLBACK TO SAVEPOINT a