sql08 - 9 Manipulating Data Objectives After completing...

Info icon This preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
9 9 Manipulating Data
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
9-2 Objectives After completing this lesson, you should After completing this lesson, you should be able to do the following: be able to do the following: Describe each DML statement Insert rows into a table Update rows in a table Delete rows from a table Control transactions
Image of page 2
9-3 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.
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
9-4 Adding a New Row to a Table DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON New row New row 50 DEVELOPMENT DETROIT DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON “… “… insert a new row insert a new row into DEPT table…” into DEPT table…” 50 DEVELOPMENT DETROIT
Image of page 4
9-5 The INSERT Statement Add new rows to a table by using the INSERT statement. Only one row is inserted at a time with this syntax. INSERT INTO table [( column [ , column... ])] VALUES (value [ , value... ]) ;
Image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
9-6 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 within single quotation marks. SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT'); 1 row created. 1 row created.
Image of page 6
9-7 Inserting Rows with Null Values Implicit method: Omit the column from the column list. SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS'); 1 row created. 1 row created. Explicit method: Specify the NULL keyword. SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL); 1 row created. 1 row created.
Image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
9-8 Inserting Special Values The SYSDATE function records the The SYSDATE function records the current date and time. current date and time. SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10); 1 row created. 1 row created.
Image of page 8
9-9 Inserting Specific Date Values Add a new employee. SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE('FEB 3,97', 'MON DD, YY'), 4 1300, NULL, 10); 1 row created. 1 row created. Verify your addition. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10
Image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
9-10 Inserting Values by Using Substitution Variables Create an interactive script by using Create an interactive script by using SQL*Plus substitution parameters. 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 80 Enter value for department_name: EDUCATION EDUCATION Enter value for location: ATLANTA ATLANTA 1 row created.
Image of page 10
9-11 Creating a Script with Customized Prompts ACCEPT stores the value into a variable.
Image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern