sql08 - 9 9 Manipulating Data 9-2 Objectives After...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 9 9 Manipulating Data 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 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. 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 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. .. ]) ; 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. 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. 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. 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 9-10 Inserting Values by Using Substitution Variables Create an interactive script by using Create an interactive script by using SQL*Plus substitution parameters....
View Full Document

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 39

sql08 - 9 9 Manipulating Data 9-2 Objectives After...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online