Site is too busy. Please visit the index page again in a moment.

 Oracle DBA FAQ - Understanding SQL DML Statements Home >> FAQs/Tutorials >> Oracle DBA
FAQ >> Index Oracle DBA FAQ - Understanding SQL
DML Statements By: FYIcenter.com Part:   1   2  3  A collection of 15 FAQs
on Oracle SQL DML statements. Clear answers are provided with tutorial
exercises on inserting, updating and deleting rows from database tables.
Topics included in this FAQ are: What Are DML Statements? How To Create a
Testing Table? How To Set Up SQL*Plus Output Format? How To Insert a New
Row into a Table? How To Specify Default Values in INSERT Statement? How
To Omit Columns with Default Values in INSERT Statement? How To Insert
Multiple Rows with One INSERT Statement? How To Update Values in a Table?
How To Update Values on Multiple Rows? How To Use Existing Values in
UPDATE Statements? How To Use Values from Other Tables in UPDATE
Statements? What Happens If the UPDATE Subquery Returns Multiple Rows? How
To Delete an Existing Row from a Table? How To Delete Multiple Rows from a
Table? How To Delete All Rows a Table? Sample scripts used in this FAQ
assumes that you are connected to the server with the HR user account on
the default database instance XE. See other FAQ collections on how to
connect to the server. Some sample scripts requires database tables
created by other samples in the beginning of the collection. What Are DML
Statements? DML (Data Manipulation Language) statements are statements to
change data values in database tables. The are 3 primary DML statements:
INSERT - Inserting new rows into database tables. UPDATE - Updating
existing rows in database tables . DELETE - Deleting existing rows from
database tables. How To Create a Testing Table? If you want to practice
DML statements, you should create a testing table as shown in the script
below: CREATE TABLE fyi_links (id NUMBER(4) PRIMARY KEY, url VARCHAR2(80)
NOT NULL, notes VARCHAR2(1024), counts NUMBER, created DATE DEFAULT
(sysdate)); Table created. You should keep this table for to practice
other tutorial exercises presented in this collection. How To Set Up
SQL*Plus Output Format? If you want to practice SQL statements with
SQL*Plus, you need to set up your SQL*Plus output formatting parameter
properly. The following SQL*Plus commands shows you some examples: COLUMN
id FORMAT 9999; COLUMN url FORMAT A24; COLUMN notes FORMAT A12; COLUMN
counts FORMAT 999999; SET NULL 'NULL'; How To Insert a New Row into a
Table? To insert a new row into a table, you should use the INSERT INTO
statement with values specified for all columns as shown in the following
example: INSERT INTO fyi_links VALUES (101, 'http://dev.fyicenter.com',
NULL, 0, '30-Apr-2006'); 1 row created. SELECT * FROM fyi_links; ID URL
NOTES COUNTS CREATED ----- ------------------------ -------- -------
--------- 101 http://dev.fyicenter.com NULL 0 30-Apr-06 How To Specify
Default Values in INSERT Statement? If a column is defined with a default
value in a table, you can use the key word DEFAULT in the INSERT statement
to take the default value for that column. The following tutorial exercise
gives a good example: INSERT INTO fyi_links VALUES (102,
'http://dba.fyicenter.com', NULL, 0, DEFAULT); 1 row created. SELECT *
FROM fyi_links; ID URL NOTES COUNTS CREATED ----- ------------------------
-------- ------- --------- 101 http://dev.fyicenter.com NULL 0 30-Apr-06
102 http://dba.fyicenter.com NULL 0 07-MAY-06 (Continued on next part...)
Part:   1   2  3  Selected Developer
Jobs: More...