Lesson Agenda
• Adding new rows in a table
– INSERT statement
• Changing data in a table
– UPDATE statement
• Removing rows from a table:
– DELETE statement
– TRUNCATE statement
• Database transactions control using COMMIT, ROLLBACK,
and SAVEPOINT
• Read consistency
• FOR UPDATE clause in a SELECT statement
9 -3 Copyright © 2009, Oracle. All rights reserved.
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 Copyright © 2009, Oracle. All rights reserved.
Insert
• Insert a new department named “Support” in location ID
2500:
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (10,'Support', 2500);
11 - 29 Copyright © 2009, Oracle. All rights reserved.
Adding a New Row to a Table
New
DEPARTMENTS row
Insert new row
into the
DEPARTMENTS table.
9 -5 Copyright © 2009, Oracle. All rights reserved.
INSERT Statement Syntax
• Add new rows to a table by using the INSERT statement:
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
• With this syntax, only one row is inserted at a time.
9 -6 Copyright © 2009, Oracle. All rights reserved.
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.
INSERT INTO departments(department_id,
department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
• Enclose character and date values within single quotation
marks.
9 -7 Copyright © 2009, Oracle. All rights reserved.
Dropping a Table
• Moves a table to the recycle bin
• Removes the table and all its data entirely if the PURGE
clause is specified
• Invalidates dependent objects and removes object
privileges on the table
DROP TABLE dept80;
10 - 38 Copyright © 2009, Oracle. All rights reserved.
TRUNCATE Statement
• Removes all rows from a table, leaving the table empty
and the table structure intact
• Is a data definition language (DDL) statement rather than a
DML statement; cannot easily be undone
• Syntax:
TRUNCATE TABLE table_name;
• Example:
TRUNCATE TABLE copy_emp;
9 - 24 Copyright © 2009, Oracle. All rights reserved.
Retrieving Data Using
the SQL SELECT Statement
Copyright © 2009, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the
following:
• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
1 -2 Copyright © 2009, Oracle. All rights reserved.
Lesson Agenda
• Basic SELECT statement
• Arithmetic expressions and NULL values in the SELECT
statement
• Column aliases
• Use of concatenation operator, literal character strings,
alternative quote operator, and the DISTINCT keyword
• DESCRIBE command
1 -3 Copyright © 2009, Oracle. All rights reserved.
Basic SELECT Statement
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
• SELECT identifies the columns to be displayed.
• FROM identifies the table containing those columns.
1 -5 Copyright © 2009, Oracle. All rights reserved.
Selecting All Columns
SELECT *
FRO departments;
M
1 -6 Copyright © 2009, Oracle. All rights reserved.
Selecting Specific Columns
SELECT department_id, location_id
FROM departments;
1 -7 Copyright © 2009, Oracle. All rights reserved.
Writing SQL Statements
• SQL statements are not case-sensitive.
• SQL statements can be entered on one or more lines.
• Keywords cannot be abbreviated or split across lines.
• Clauses are usually placed on separate lines.
• Indents are used to enhance readability.
• In SQL Developer, SQL statements can optionally be
terminated by a semicolon (;). Semicolons are required
when you execute multiple SQL statements.
• In SQL*Plus, you are required to end each SQL statement
with a semicolon (;).
1 -8 Copyright © 2009, Oracle. All rights reserved.
Arithmetic Expressions
Create expressions with number and date data by using
arithmetic operators.
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
1 - 11 Copyright © 2009, Oracle. All rights reserved.
Using Arithmetic Operators
SELECT last_name, salary, salary + 300
FROM employees;
1 - 12 Copyright © 2009, Oracle. All rights reserved.
Operator Precedence
SELECT last_name, salary, 12*salary+100
FROM employees; 1
…
SELECT last_name, salary, 12*(salary+100)
FROM employees;
2
1 - 13 Copyright © 2009, Oracle. All rights reserved.
Defining a Null Value
• Null is a value that is unavailable, unassigned, unknown,
or inapplicable.
• Null is not the same as zero or a blank space.
SELECT last_name, job_id, salary,
commission_pct
FROM employees;
1 - 14 Copyright © 2009, Oracle. All rights reserved.
Null Values in Arithmetic Expressions
Arithmetic expressions containing a null value evaluate to null.
SELECT last_name, 12*salary*commission_pct
FROM employees;
1 - 15 Copyright © 2009, Oracle. All rights reserved.
Lesson Agenda
• Basic SELECT statement
• Arithmetic expressions and NULL values in the SELECT
statement
• Column aliases
• Use of concatenation operator, literal character strings,
alternative quote operator, and the DISTINCT keyword
• DESCRIBE command
1 - 16 Copyright © 2009, Oracle. All rights reserved.
Defining a Column Alias
A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name (There can also be
the optional AS keyword between the column name and
alias.)
• Requires double quotation marks if it contains spaces or
special characters, or if it is case-sensitive
1 - 17 Copyright © 2009, Oracle. All rights reserved.
Using Column Aliases
SELECT last_name AS name, commission_pct AS comm
FROM employees;
…
SELECT last_name "Name" , salary*12 "Annual Salary"
FROM employees;
1 - 18 Copyright © 2009, Oracle. All rights reserved.
Lesson Agenda
• Basic SELECT Statement
• Arithmetic Expressions and NULL values in SELECT
statement
• Column Aliases
• Use of concatenation operator, literal character strings,
alternative quote operator, and the DISTINCT keyword
• DESCRIBE command
1 - 19 Copyright © 2009, Oracle. All rights reserved.
Concatenation Operator
A concatenation operator:
• Links columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression
SELECT last_name||job_id "Employees"
FROM employees;
1 - 20 Copyright © 2009, Oracle. All rights reserved.
