Transcript Slide 1

15 Structured Query Language (SQL)
Objectives
After completing this section, you should be able to:
• Understand Structured Query Language (SQL) and its
purpose
• Create and execute a basic SELECT statement
• Create and execute a basic INSERT statement
• Create and execute a basic UPDATE statement
• Create and execute a basic DELETE statement
• Execute COMMIT and ROLLBACK on transaction
2
What is SQL?
SQL (Structured Query Language) is a standard
interactive programming language for getting information
from and updating to a database.
Statements take the form of a command language that
lets you:
•
•
•
•
SELECT data
INSERT data
UPDATE data
DELETE data
3
Guidelines in writing SQL Statements
•
•
•
•
•
•
•
SQL statements are not case-sensitive
SQL statements can be on one or more lines
SQL statements are optionally ended with “;”
Keywords cannot be abbreviated or split across lines
Clauses are usually placed on separate lines
Indents are used to enhance readability
Keywords are typically entered in uppercase; all other
words such as table name and columns are entered in
lower case
4
Basic SELECT Statement
Use the SELECT statement to retrieve data from one or more tables:
SELECT <column(s) >
FROM <table>
[WHERE <condition>]
[ORDER BY <column(s) [ASC|DESC]>]
table
is the name of the table
column
is the name of the column in the table to be
selected
condition
identifies the rows to be selected and is composed
of column names, expressions, constraints,
sub-queries and comparison operators
column
(order by)
is the name of the column(s) used for sorting
5
Choosing Columns
• To choose all the columns of the table for display, you can
use the asterisk (*)
SELECT *
FROM TABLE_A
• To choose specific columns on the table for display, you
specify each column separated by a comma (,)
SELECT COLUMN_1,
COLUMN_2
FROM TABLE_A
• It is best to put each column chosen in a separate line
6
Limiting Rows
• The method of restriction is the basis of the
WHERE clause in SQL
• Character strings and dates in the WHERE clause
must be enclosed in Single Quotation Marks (‘)
• Numeric Values do not need the Single Quotation
marks(‘)
7
Rows may be limited by:
• EQUALS CONDITION
• Display rows based on an exact match of values.
SELECT last_name,
salary
FROM
employee
WHERE salary = 30000
SELECT employee_id,
last_name
FROM
employee
WHERE manager_name = ‘RAYMOND’
8
Rows may be limited by:
• >, < or <> CONDITION
SELECT last_name
FROM
employee
WHERE salary > 30000
SELECT employee_id
FROM
employee
WHERE manager_name <= ‘RAYMOND’
SELECT employee_id
FROM
employee
WHERE status <> ‘ACTIVE’
9
Rows may be limited by:
• BETWEEN CONDITION
• Display rows based on a range of values
SELECT last_name
FROM
employee
WHERE salary BETWEEN 30000 AND 50000
• IN CONDITION
• Display rows based on a list of values
SELECT employee_id
FROM
employee
WHERE manager_id IN (100, 200, 300)
10
Rows may be limited by:
• LIKE CONDITION
• Perform wildcard searches of valid search string values
• Can contain either literal characters or numbers
• % denotes zero or many characters
• _ denotes one character
• Use ESCAPE identifier to search for the actual % and
_symbols. Identifies the backslash(\) as the escape
character
SELECT last_name
FROM employee
WHERE last_name LIKE ‘%a’
11
Rows may be limited by:
• LOGICAL CONDITION
• AND, OR, NOT
SELECT last_name,
job_id
FROM
employee
WHERE job_id NOT IN ('SSE', 'TL')
SELECT last_name,
job_id
FROM
employee
WHERE salary NOT between 10000 AND 15000
12
Rows may be limited by:
• LOGICAL CONDITION
• AND, OR, NOT
SELECT last_name,
job_id
FROM
employee
WHERE last_name NOT LIKE 'A%'
AND last_name NOT LIKE 'B%‘
SELECT last_name,
job_id
FROM
employee
WHERE commission_pct IS NOT NULL
13
Sorting Rows
• ORDER BY clause
• ASC specifies an ascending order
• DESC specifies a descending order
SELECT last_name,
salary,
job_id
FROM
employee
ORDER BY salary DESC, job_id ASC
• Display the result in descending order by the attribute
salary. If two records have the same attribute value, the
salary sorting criteria is in ascending order according to the
attribute values of job_id
14
Basic INSERT Statement
INSERT INTO <table>
[ (column
[, column…] ) ]
VALUES
(value
[, value…] )
table
column
value
is the name of the table
is the name of the column in the table to
populate
is the corresponding value for the column
Note: This statement with the VALUES
clause adds only one row at a time to a table.
15
Basic INSERT Statement
•
•
•
•
Insert a new row containing values for each column
List values in the default order of the columns in the table
Option: list the columns in the INSERT clause
Enclose character and date values within single quotation
marks
INSERT INTO departments ( department_id,
department_name,
current_date)
VALUES (
70,
‘Public Relations’,
’10-OCT-04’)
16
Inserting 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
SELECT
FROM
WHERE
sales_reps ( id,
name,
salary)
employee_id,
last_name,
salary
employees
job_id LIKE ‘%REP%’
17
Basic UPDATE Statement
UPDATE
SET
table
column = value
[, column = value, …]
[WHERE condition]
table
is the name of the table
column
name of the column in the table to populate
value
corresponding value for the column
condition
identifies the rows to be updated and is
composed of column names, expressions,
constraints, sub-queries, and comparison
operators
18
Updating Rows in a Table
• Specific row or rows are modified if you specify the
WHERE clause
UPDATE employees
SET
department_id = 70
WHERE employee_id = 113
• All rows in the table are modified if you omit the WHERE
clause
19
Updating Rows Based on Another Table
• Use subqueries in UPDATE statements to update rows in a
table based on values from another table
UPDATE
SET
copy_emp
department_id =
WHERE
job_id
=
(SELECT department_id
FROM employees
WHERE emp_id =100)
(SELECT job_id
FROM employees
WHERE emp_id = 200)
20
Basic DELETE Statement
DELETE
[WHERE
[FROM] table
condition] ;
table
is the name of the table
condition
identifies the rows to be updated and is
composed of column names, expressions,
constraints, sub-queries, and comparison
operators
21
Deleting Rows in a Table
• A specific row or specific rows are deleted if you specify
the WHERE clause
DELETE FROM employees
WHERE employee_id = 113 ;
• All rows in the table are deleted if you omit the WHERE
clause
22
Deleting Rows Based on Another Table
• Use subqueries in DELETE statements to delete rows in a
table based on values from another table
DELETE FROM employees
WHERE department_id = (SELECT
FROM
WHERE
department_id
departments
dept_type = ‘CST’)
23
What is a Transaction?
• A transaction usually means a sequence of information
exchange and related work (such as database updating) that is
treated as a unit for the purposes of satisfying a request and
for ensuring database integrity
• For a transaction to be completed and database changes to be
made permanent, a transaction has to be completed in its
entirety. A program that manages or oversees the sequence of
events that are part of a transaction is sometimes called a
transaction monitor
• Transactions are supported by SQL. When a transaction
completes successfully, database changes are said to be
committed; when a transaction does not complete, changes
are rolled back
24
Transaction Control
• Transaction Control Statements
• COMMIT
• ROLLBACK
25
COMMIT
• Use the COMMIT statement to end your current transaction
and make permanent all changes performed in the
transaction
• If you do not explicitly commit the transaction and the
program terminates abnormally, then the last uncommitted
transaction is automatically rolled back
• SYNTAX:
COMMIT;
26
ROLLBACK
• Use the ROLLBACK statement to undo work done in the
current transaction, or to manually undo the work done by
an in-doubt distributed transaction
• SYNTAX:
ROLLBACK;
27
Key Points
• SQL is an industry standard language for updating, and
getting information from, a database
• The basic and most common SQL statements are:
SELECT, INSERT, UPDATE, DELETE
• Transaction management is implemented in SQL using
COMMIT and ROLLBACK
28