Database Systems:
Design, Implementation, and
Management
Ninth Edition
Chapter 7
Introduction to Structured Query
Language (SQL)
Objectives
2
In this chapter, students will learn:
The basic commands and functions of
SQL
How to use SQL for data administration
(to create tables, indexes, and views)
How to use SQL for data manipulation (to
add, modify, delete, and retrieve data)
How to use SQL to query a database for
useful information
Database Systems, 9th Edition
(Contents of topic 7, according to the
syllabus)
3
Introduction to SQL
Data definition commands
Data manipulation commands
Select queries
Advanced data definition commands
Virtual tables: creating a View
Joining database tables
Database Systems, 9th Edition
Introduction to SQL
4
SQL functions fit into two broad
categories:
Data definition language
Data manipulation language
Basic command set has vocabulary of
fewer than 100 words
American National Standards Institute
(ANSI) prescribes a standard SQL
Several SQL dialects exist
Database Systems, 9th Edition
Data Definition Commands
5
The database model
In this chapter, a simple database with
these tables is used to illustrate
commands:
CUSTOMER
INVOICE
LINE
PRODUCT
VENDOR
Focus on PRODUCT and VENDOR tables
Database Systems, 9th Edition
Database Systems, 9th Edition
Creating the Database
7
Two tasks must be completed:
Create database structure
Create tables that will hold end-user data
First task:
RDBMS creates physical files that will hold
database
Differs substantially from one RDBMS to
another
Database Systems, 9th Edition
The Database Schema
8
Authentication
DBMS verifies that only registered users are
able to access database
Log on to RDBMS using user ID and
password created by database
administrator
Schema
Group of database objects that are related
to each other
Database Systems, 9th Edition
Data Types
9
Data type selection is usually dictated by
nature of data and by intended use
Supported data types:
Number(L,D), Integer, Smallint,
Decimal(L,D)
Char(L), Varchar(L), Varchar2(L)
Date, Time, Timestamp
Real, Double, Float
Interval day to hour
Many other types
Database Systems, 9th Edition
Creating Table Structures
10
Use one line per column (attribute)
definition
Use spaces to line up attribute
characteristics and constraints
Table and attribute names are
capitalized
NOT NULL specification
UNIQUE specification
Database Systems, 9th Edition
11
Creating Table Structures
(contd.)
Primary key attributes contain both a
NOT NULL and a UNIQUE specification
RDBMS will automatically enforce
referential integrity for foreign keys
Command sequence ends with
semicolon
Database Systems, 9th Edition
SQL Constraints
12
NOT NULL constraint
UNIQUE constraint
Ensures that all values in column are
unique
DEFAULT constraint
Ensures that column does not accept nulls
Assigns value to attribute when a new row
is added to table
CHECK constraint
Validates data when attribute value is
Database Systems, 9th Edition
entered
SQL Indexes
13
When primary key is declared, DBMS
automatically creates unique index
Often need additional indexes
Using CREATE INDEX command, SQL
indexes can be created on basis of any
selected attribute
Composite index
Index based on two or more attributes
Often used to prevent data duplication
Database Systems, 9th Edition
14
Data Manipulation
Commands
INSERT
SELECT
COMMIT
UPDATE
ROLLBACK
DELETE
Database Systems, 9th Edition
Adding Table Rows
15
INSERT
Used to enter data into table
Syntax:
INSERT
INTO columnname
VALUES (value1, value2, , valueN);
Database Systems, 9th Edition
Adding Table Rows (contd.)
16
When entering values, notice that:
Row contents are entered between
parentheses
Character and date values are entered
between apostrophes
Numerical entries are not enclosed in
apostrophes
Attribute entries are separated by commas
A value is required for each column
Use NULL for unknown values
Database Systems, 9th Edition
Saving Table Changes
17
Changes made to table contents are not
physically saved on disk until:
Syntax:
Database is closed
Program is closed
COMMIT command is used
COMMIT [WORK];
Will permanently save any changes
made to any table in the database
Database Systems, 9th Edition
Listing Table Rows
18
SELECT
Used to list contents of table
Syntax:
SELECT
columnlist
FROM tablename;
Columnlist represents one or more
attributes, separated by commas
Asterisk can be used as wildcard
character to list all attributes
Database Systems, 9th Edition
Updating Table Rows
19
UPDATE
Modify data in a table
Syntax:
UPDATE tablename
SET columnname = expression [, columnname
= expression]
[WHERE conditionlist];
If more than one attribute is to be
updated in row, separate corrections
with commas
Database Systems, 9th Edition
Restoring Table Contents
20
ROLLBACK
Syntax:
Undoes changes since last COMMIT
Brings data back to prechange values
ROLLBACK;
COMMIT and ROLLBACK only work with
commands to add, modify, or delete
table rows
Database Systems, 9th Edition
Deleting Table Rows
21
DELETE
Deletes a table row
Syntax:
DELETE FROM tablename
[WHERE conditionlist ];
WHERE condition is optional
If WHERE condition is not specified, all
rows from specified table will be deleted
Database Systems, 9th Edition
Inserting Table Rows with a
SELECT Subquery
22
INSERT
Inserts multiple rows from another table
(source)
Uses SELECT subquery
Subquery: query embedded (or nested or
inner) inside another query
Subquery executed first
Syntax:
INSERT INTO tablename SELECT columnlist
FROM tablename;
Database Systems, 9th Edition
SELECT Queries
23
Fine-tune SELECT command by adding
restrictions to search criteria using:
Conditional restrictions
Arithmetic operators
Logical operators
Special operators
Database Systems, 9th Edition
Selecting Rows with
Conditional Restrictions
24
Select partial table contents by placing
restrictions on rows to be included in
output
Add conditional restrictions to SELECT
statement, using WHERE clause
Syntax:
SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;
Database Systems, 9th Edition
25
Database Systems, 9th Edition
Arithmetic Operators:
The Rule of Precedence
26
Perform operations within
parentheses
Perform power operations
Perform multiplications and divisions
Perform additions and subtractions
Database Systems, 9th Edition
Logical Operators: AND, OR, and NOT
27
Searching data involves multiple
conditions
Logical operators: AND, OR, and NOT
Can be combined
Parentheses enforce precedence order
Conditions
in parentheses are always executed
first
Boolean algebra: mathematical field
dedicated to use of logical operators
NOT negates result of conditional
Database Systems, 9th Edition
expression
Special Operators
28
BETWEEN: checks whether attribute
value is within a range
IS NULL: checks whether attribute value
is null
LIKE: checks whether attribute value
matches given string pattern
IN: checks whether attribute value
matches any value within a value list
EXISTS: checks if subquery returns any
rows
Database Systems, 9th Edition
Advanced Data Definition Commands
29
All changes in table structure are made
by using ALTER command
Three options:
ADD adds a column
MODIFY changes column characteristics
DROP deletes a column
Can also be used to:
Add table constraints
Remove table constraints
Database Systems, 9th Edition
30
Changing a Columns Data
Type
ALTER can be used to change data type
Some RDBMSs do not permit changes to
data types unless column is empty
Database Systems, 9th Edition
Changing a Columns Data Characteristics
31
Use ALTER to change data
characteristics
Changes in columns characteristics are
permitted if changes do not alter the
existing data type
Database Systems, 9th Edition
32
Adding a Column
Dropping a Column
Use ALTER to add column
Do not include the NOT NULL clause for
new column
Use ALTER to drop column
Some RDBMSs impose restrictions on the
deletion of an attribute
Database Systems, 9th Edition
Advanced Data Updates
33
UPDATE command updates only data in
existing rows
If relationship between entries and
existing columns, can assign values to
slots
Arithmetic operators are useful in data
updates
In Oracle, ROLLBACK command undoes
changes made by last two UPDATE
statements
Database Systems, 9th Edition
34
Database Systems, 9th Edition
Copying Parts of Tables
35
SQL permits copying contents of
selected table columns
Data need not be reentered manually into
newly created table(s)
First create the table structure
Next add rows to new table using table
rows from another table
Database Systems, 9th Edition
36
Database Systems, 9th Edition
Adding Primary and Foreign Key
Designations
37
When table is copied, integrity rules do
not copy
Primary and foreign keys are manually
defined on new table
User ALTER TABLE command
Syntax:
ALTER TABLE tablename ADD PRIMARY
KEY(fieldname);
For foreign key, use FOREIGN KEY in place
of PRIMARY KEY
Database Systems, 9th Edition
Deleting a Table from the Database
38
DROP
Deletes table from database
Syntax:
DROP TABLE tablename;
Can drop a table only if it is not the
one side of any relationship
Otherwise, RDBMS generates an error
message
Foreign key integrity violation
Database Systems, 9th Edition
39
Additional SELECT Query
Keywords
Logical operators work well in the query
environment
SQL provides useful functions that:
Count
Find minimum and maximum values
Calculate averages, etc.
SQL allows user to limit queries to:
Entries having no duplicates
Entries whose duplicates may be grouped
Database Systems, 9th Edition
Ordering a Listing
40
ORDER BY clause is useful when listing
order is important
Syntax:
SELECT columnlist
FROM tablelist
[WHERE conditionlist]
[ORDER BY columnlist [ASC | DESC]];
Ascending order by default
Database Systems, 9th Edition
Listing Unique Values
41
DISTINCT clause produces list of only
values that are different from one
another
Example:
SELECT DISTINCT V_CODE
FROM PRODUCT;
Access places nulls at the top of the list
Oracle places it at the bottom
Placement of nulls does not affect list
contents
Database Systems, 9th Edition
Aggregate Functions
42
COUNT function tallies number of nonnull values of an attribute
MAX and MIN find highest (lowest) value
in a table
Takes one parameter: usually a column
name
Compute MAX value in inner query
Compare to each value returned by the
query
SUM computes total sum for any
specified attribute
Database Systems, 9th Edition
AVG function format
is similar to MIN and
Grouping Data
43
Frequency distributions created by
GROUP BY clause within SELECT
statement
Syntax:
SELECT columnlist
FROM
tablelist
[WHERE
conditionlist]
[GROUP BY columnlist]
[HAVING
conditionlist]
[ORDER BY columnlist [ASC | DESC] ] ;
Database Systems, 9th Edition
44
Database Systems, 9th Edition
45
Virtual Tables: Creating a
View
View is virtual table based on SELECT
query
Create view by using CREATE VIEW
command
Special characteristics of relational view:
Name of view can be used anywhere a
table name is expected
View dynamically updated
Restricts users to only specified columns
and rows
Database Systems, 9th Edition
Views may be used
as basis for reports
Joining Database Tables
46
Joining tables is the most important
distinction between relational database
and other DBs
Join is performed when data are
retrieved from more than one table at a
time
Equality comparison between foreign key
and primary key of related tables
Join tables by listing tables in FROM
clause of SELECT statement
Database Systems, 9th Edition
DBMS creates Cartesian
product of every
Joining Tables with an Alias
47
Alias identifies the source table from
which data are taken
Alias can be used to identify source table
Any legal table name can be used as
alias
Add alias after table name in FROM
clause
FROM tablename alias
Database Systems, 9th Edition
48
Recursive Joins
Outer Joins
Alias is especially useful when a table
must be joined to itself
Recursive query
Use aliases to differentiate the table from
itself
Two types of outer join
Left outer join
Right outer join
Database Systems, 9th Edition
Summary
49
SQL commands can be divided into two
overall categories:
Data definition language commands
Data manipulation language commands
The ANSI standard data types are
supported by all RDBMS vendors in
different ways
Basic data definition commands allow
you to create tables, indexes, and views
Database Systems, 9th Edition
Summary (contd.)
50
DML commands allow you to add,
modify, and delete rows from tables
The basic DML commands:
SELECT, INSERT, UPDATE, DELETE,
COMMIT, and ROLLBACK
SELECT statement is main data retrieval
command in SQL
Database Systems, 9th Edition
Summary (contd.)
51
WHERE clause can be used with SELECT,
UPDATE, and DELETE statements
Aggregate functions
Special functions that perform arithmetic
computations over a set of rows
ORDER BY clause
Used to sort output of SELECT statement
Can sort by one or more columns
Ascending or descending order
Database Systems, 9th Edition
Summary (contd.)
52
Join output of multiple tables with
SELECT statement
Join performed every time you specify two
or more tables in FROM clause
If no join condition is specified, DBMX
performs Cartesian product
Natural join uses join condition to match
only rows with equal values in specified
columns
Right outer join and left outer join select
Database Systems, 9thvalues
Edition
rows with no matching
in other