INSERT INTO It is used to add one or more rows to a table Attribute values should be listed in the same order as the attributes were specified in the CREATE TABLE command INSERT INTO tablename [(column [. colum....DJ VALUES (value I, value...): Insert into student Values (12, 34) If you insert a new row that contains values for each column, the column list is not required in the INSERT clause. However, if you do not use the column list, the values must be listed according to the default order of the columns in the table, and a value must be provided for each column. Insert the following data in the table departments: 100 department_id department_name manager_id location_id 30 Purchasing 70 Public relations 100 1700 Finance INSERT INTO departments (department_id, department_name) VALUES (30, 'Purchasing'); INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES(70, 'Public Relations', 100, 1700); INSERT INTO departments VALUES (0, 'Public Relations', 100, 1700); INSERT INTO departments VALUES (100, "Finance', NULL, NULL); records information for employee Raphe in the EMPLOYEES table. EMAIL EMPLOYEE ID FIRST NAME LAST_NAME JOR ID HIRE DATE SALARY PHONE NUMBER COMMISSION PC MANAGER ID 11 Den Raphe DRAPHE 03-02-99 AC_ACCOUNT 11000 100 30 INSERT INTO employees VALUES (11, 'Den', 'Raphe', 'DRAPHE', NULL, '03-feb-99', 'AC_ACCOUNT", 11000, NULL, 100, 30); Check that your data was inserted: COMMIT O Committing a transaction is similar to saving a file in Microsoft word. The COMMIT statement: commits a transaction makes permanent all the data changes made since the execution of previous COMMIT. COMMIT To Commit the changes type COMMIT. COMMIT; Commit complete. ROLLBACK Discard all pending changes by using the ROLLBACK statement: Data changes are undone. Previous state of the data is restored. Rolling Back Changes to a Marker Create a marker in a current transaction by using the SAVEPOINT statement. Rollback to that marker by using the ROLLBACK TO SAVEPOINT statement. UPDATE dept set loc = 'BOSTON' where deptno =30; // 1 row updated. SAVEPOINT update_done; //Savepoint created. INSERT INTO dept VALUES (60, "CS', 'Denver'); // 1 row created. select * from dept; ROLLBACK TO update_done; //Rollback complete. Statement purpose To understand how to insert new rows into a table make them permanently stored (COMMIT statement). To Drop a table, run a script file (using the START sql*plus command) and check table content using SELECT. Activity Outcomes Students will be able to insert rows and save them permanently, Delete rows from a tables, Commit / Rollback a transaction and execute a script file containing SQL DDL-commands. Instructor Note Implement the question in the Exercise at the end of the Lab and submit the echo (i.e., result displayed by Oracle after each execution). 1) Add the following department to the DEP Table: DEPTNO = 'CM' and DNAME = "Chemistry 2) Add the following COURSE: (301, Chemistry I', 'CM', 40). 3) Try to add the COURSE: (101, 'Introduction to DOS', 'CP', 30). Discuss the result. 4) Try to add the COURSE: (101, "Introduction to DOS', 'CP", 30). Discuss the result. 5) How many rows are inserted correctly into each of the COURSE and DEP tables? 6) Execute the following SQL Query: SELECT * FROM DEP; How many rows you get?...... 7) Execute the following SQL Query: SELECT * FROM COURSE; How many rows you get? 8) Right now, do the rows are permanently inserted into the tables? Discuss. 9) ROLLBACK the transaction. What is happened? 10) If the rows are not permanently inserted, execute a statement to save the inserted rows. Now suppose we want to empty the two tables DEP and COURSE. 11) DELETE the rows in the DEP table first. Discuss the result and solve the problem to empty the two tables properly. 12) Check that the two tables are empty. 13) Extend the table DEP with a new column NBEMP to store the NUMBER(2) of employees in each department. 14) Remove the two tables DEP and COURSE. CREATE TABLE STUDENT ( STDNO Number (8) Primary key, SNAME Varchar2 (30), DEPTNO Varchar2 (2), STid Number (8) ); CREATE TABLE DEP DEPTNO Varchar2 (2) Primary key, DNAME Varchar2 (20)); CREATE TABLE COURSE ( CORSNO Number (3) Primary key, CNAME Varchar2 (30), DEPTNO Varchar2 (2), CMAX Number (2)); CREATE TABLE ENROLMENT ( STDNO Number (8), CORSNO Number (3), GRADE Number (2), EDATE DATE, Primary key (STDNO, CORSNO) ); ALTER TABLE STUDENT add CONSTRAINT S_fk FOREIGN KEY (DEPTNO)REFERENCES DEP (DEPTNO); ALTER TABLE COURSE add CONSTRAINT c_fk FOREIGN KEY (DEPTNO) REFERENCES DEP (DEPTNO); ALTER TABLE ENROLMENT add CONSTRAINT e_fk1 FOREIGN KEY (CORSNO) REFERENCES COURSE (CORSNO); ALTER TABLE ENROLMENT add CONSTRAINT e_fk2 FOREIGN KEY (STDNO) REFERENCES STUDENT (STDNO)