1 / 44

Manipulating Data

Manipulating Data. Objectives. After completing this lesson, you should be able to do the following: Describe each data manipulation language (DML) statement Insert rows into a table Update rows in a table Delete rows from a table Control transactions. Manipulating Data. SQL Statements.

jheller
Télécharger la présentation

Manipulating Data

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Manipulating Data

  2. Objectives After completing this lesson, you should be able to do the following: • Describe each data manipulation language (DML) statement • Insert rows into a table • Update rows in a table • Delete rows from a table • Control transactions

  3. Manipulating Data

  4. SQL Statements • SELECT • INSERT • UPDATE • DELETE • CREATE • ALTER • DROP • RENAME • TRUNCATE • COMMIT • ROLLBACK • SAVEPOINT • GRANT • REVOKE Data retrieval Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)

  5. Data Manipulation Language (DML)

  6. Data Manipulation Language • A DML statement is executed when you: • Add new rows to a table • Modify existing rows in a table • Remove existing rows from a table • A transaction consists of a collection of DML statements that form a logical unit of work.

  7. Adding Rows

  8. Adding a New Row to a Table New row DEPARTMENTS “Insert a new rowinto theDEPARTMENTStable” DEPARTMENTS

  9. INSERT Statement Syntax • Add new rows to a table by using the INSERT statement. • With this syntax, only one row is inserted at a time. INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

  10. Inserting New Rows • Insert a new row containing values for each column. • List values in the default order of the columns in the table. • Optionally, list the columns in the INSERT clause. • Enclose character and date values in single quotation marks. INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); Insert into hire_dates(id) Values(20)

  11. Practice insert into dept80(deptno,dname,loc,create_date) values(11,'abcd','south',sysdate) insert into dept80 values(12,'qwe','west',sysdate) select * from dept80

  12. Try • INSERT INTO departments VALUES (71, ‘IT’, 100, 1900); • INSERT INTO departments VALUES (70, ‘IT’, 100, 1900); • INSERT INTO departments VALUES (77, ‘IT’, 100, 1999); • INSERT INTO departments VALUES (77, ‘IT’, null, 1900);

  13. Try • INSERT INTO departments VALUES (‘A’, ‘IT’, null, 1900); • INSERT INTO departments VALUES (76, null, null, 1900); • INSERT INTO departments VALUES (74,'IT'); • INSERT INTO departments( DEPARTMENT_ID, DEPARTMENT_NAME)VALUES (74,'IT'); • INSERT INTO departments( DEPARTMENT_NAME) VALUES ('IT');

  14. Inserting Rows with Null Values INSERT INTO departments (department_id, department_name ) VALUES (30, 'Purchasing'); 1 row created. • Implicit method: Omit the column from the column list. • Explicit method: Specify the NULL keyword in the VALUES clause. INSERT INTO departments VALUES (100, 'Finance', NULL, NULL); 1 row created.

  15. Inserting Special Values The SYSDATE function records the current date and time. INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100); 1 row created.

  16. Try • INSERT INTO JOB_HISTORY VALUES (114,'22-3 99', SYSDATE ,'IT_PROG‘ ,NULL); Another time: • INSERT INTO JOB_HISTORY VALUES (114,'22-3-99', SYSDATE, 'IT_PROG‘ ,NULL);

  17. Inserting Values by Using Substitution Variables Create an interactive script by using SQL*Plus substitution parameters. SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (&department_id, 3 '&department_name', '&location'); Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created.

  18. Copying Rows from Another Table • Write your INSERT statement with a subquery: • Do not use the VALUES clause. • Match the number of columns in the INSERT clause to those in the subquery. INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%'; 4 rows created.

  19. INSERT INTO table name (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees) VALUES (999, ‘Ahmad', ‘A@yahoo',sysdate,'ST_CLERK', 5000, 50); Using a Subquery in an INSERT Statement use a subquery in place of the table name & column list

  20. Update Rows

  21. Changing Data in a Table EMPLOYEES Update rows in the EMPLOYEES table

  22. UPDATE Statement Syntax • Modify existing rows with the UPDATE statement: • Update more than one row at a time (if required). UPDATE table SET column = value [, column = value, ...] [WHERE condition];

  23. Updating Rows in a Table • Specific row or rows are modified if you specify the WHERE clause: • All rows in the table are modified if you omit the WHERE clause: UPDATE dept80 SET deptno = 10 where deptno=11 UPDATE dept80 SET deptno = 10

  24. Try UPDATE employees SET department_id = 10,salary=9000 WHERE employee_id = 119; UPDATE employees SET employee_id = 205 WHERE employee_id = 125;

  25. Updating two Columns with a Subquery Update employee 114’s job and salary to match that of employee 205. UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114;

  26. Another solution UPDATE employees SET (job_id ,salary) = (SELECT job_id ,salary FROM employees WHERE employee_id = 205) WHERE employee_id = 124;

  27. Updating Rows Based on Another Table Use subqueries in UPDATE statements to update rows in a table based on values from another table: UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated.

  28. Updating Rows: Integrity Constraint Error Department number 55 does not exist SQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10; UPDATE emp * ERROR at line 1: ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not found

  29. Delete Rows

  30. Removing a Row from a Table DEPARTMENTS Delete a row from the DEPARTMENTS table:

  31. DELETE Statement You can remove existing rows from a table by using the DELETE statement: DELETE [FROM] table [WHERE condition];

  32. Deleting Rows from a Table • Specific rows are deleted if you specify the WHERE clause: • All rows in the table are deleted if you omit the WHERE clause: DELETE FROM dept80 WHERE dname = ‘abcd'; DELETE FROM dept80

  33. Deleting Rows Based on Another Table Use subqueries in DELETE statements to remove rows from a table based on values from another table: DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');

  34. Try • You can’t delete a row that contains a primary key that is used as a foreign key in another table DELETE FROM departments WHERE department_id =10;

  35. Database Transactions A database transaction consists of one of the following: • DML statements that constitute one consistent change to the data • One DDL statement • One data control language (DCL) statement

  36. Controlling Transactions Time COMMIT Transaction DELETE SAVEPOINTA INSERT UPDATE SAVEPOINTB INSERT ROLLBACK to SAVEPOINT B ROLLBACK to SAVEPOINT A ROLLBACK

  37. You can control the logic of transactions by using the COMMIT,SAVEPOINT, and ROLLBACK statements.

  38. Rolling Back Changes to a Marker • Create a marker in a current transaction by using the SAVEPOINT statement. • Roll back to that marker by using the ROLLBACK TOSAVEPOINT statement. UPDATE... SAVEPOINT update_done; Savepoint created. INSERT... ROLLBACK TO update_done; Rollback complete.

  39. State of the Data After ROLLBACK DELETE FROM test; 25,000 rows deleted. ROLLBACK; Rollback complete. DELETE FROM test WHERE id = 100; 1 row deleted. SELECT * FROM test WHERE id = 100; No rows selected. COMMIT; Commit complete.

  40. Committing Data DELETE FROM employees WHERE employee_id = 99999; INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); • Make the changes: • Commit the changes: COMMIT; Commit complete.

  41. Adding Comments to a Table • You can add comments to a table or column by using the COMMENT statement: • Comments can be viewed through the data dictionary views: • ALL_COL_COMMENTS • USER_COL_COMMENTS • ALL_TAB_COMMENTS • USER_TAB_COMMENTS COMMENT ON TABLE employees IS 'Employee Information';

  42. Comments can be viewed through the data dictionary views: • Example: SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'HR' ORDER BY TABLE_NAME;

  43. Summary In this lesson, you should have learned how to use the following statements:

More Related