Chapter Seven DDL

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

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

3. Inserting New Rows into a Table: (General format) • INSERT INTO table (col1, col2, ...) VALUES (value1, value2, .... ); Or • INSERT INTO table(col1, col2, col3,….)` Subquery;

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

5. Inserting a New Row With no Column Names: INSERT INTOStudent VALUES('Sarah', 123456, 0.0, NULL, NULL);

6. Inserting Special Values: INSERT INTO student (Id, Name, B_date, Gpa) VALUES (234567, ‘GREEN’, SYSDATE, 3.90);

7. Inserting a Date Value: INSERT INTO student (Id, Name, B_date, GPA) VALUES (22222, ‘BULUE’, ’03-JAN-79’, 2.90);

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

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

10. Inserting With Default Value: • INSERT INTO student (Id, Name, b_date, GPA) • VALUES (22222, ‘BULUE’, ‘JAN 03, 79’,DEFAULT);

11. Inserting Values By Using Substitution Variables: • INSERT INTOstudent (Id, Name, Gpa) VALUES (&student_id, ‘&student_name’, &Gpa_input);

12. 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: ‘;

13. Creating a script customized prompts: INSERT INTO student (Id, Name, Gpa) VALUES (&studentId,‘&studentName’, &GpaInput);

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

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

16. Inserting Rows With a Hint: INSERT /*+ APPEND */ INTO Temp (Name, ID, GPA) (SELECT Name, ID, GPA FROM Student WHERE Major='COSC');

17. 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;

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

19. Insert into remote database: INSERT INTO student@remote (ID, Name, B_Date, GPA) VALUES (22222, ‘BULUE’, ‘3-JAN-79’, 2.90)

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

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

22. 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;

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

24. 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;

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

26. 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;

27. 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;

28. Update (Modify a Row) (General Format) • UPDATE table SET Column = Expression [WHERE Condition];

29. Example Changing ID of every one; (Multiply by 100) (Add two digits to student ID) UPDATE Student SET ID = ID * 100;

30. Update: Remove majors of ‘COSC’ and set them to NULL: UPDATE student SET major = NULL WHERE major = ‘COSC’;

31. Update Change major of all COSC students to MATH . UPDATE Student SET Major = 'MATH' WHERE Major = 'COSC';

32. Update: Change name of Sandy Smith to Sandy Olson: UPDATE student SET last = ‘OLSON’ WHERE first = ‘SANDY’ AND last = ‘SMITH’ ;

33. Update What is the outcome this command? UPDATE Student SET Major = 'MATH‘; UPDATE Faculty SET Salary = Salary+2000, Rank=‘Professor’ WHERE ID=11111;

34. 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;

35. Deleting Rows: • DELETE FROM table [WHERE Condition];

36. Deleting Rows: DELETE FROM (SELECT * FROM student) WHERE ID = 11111; DELETE FROM student WHERE ID = 111;

37. 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’;

38. Deleting Rows: • What is the outcome of this statement: • DELETE FROM Student;

39. Transaction Control: • COMMIT • ROLLBACK • SAVEPOINT

40. Transaction Control: INSERT …….. ; SAVEPOINT a; INSERT …….. ; SAVEPOINT b; INSERT ……….. ; ROLLBACK TO SAVEPOINT a