Transcript Slide 1

6
Chapter 6
Introduction to Structured Query
Language (SQL)
Database Systems:
Design, Implementation, and Management,
Sixth Edition, Rob and Coronel
1
6
In this chapter, you 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 to extract
useful information
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
2
6
Introduction to SQL
• SQL functions fit into two broad categories:
• Data definition language
– SQL includes commands to create
• Database objects such as tables, indexes, and
views
• Commands to define access rights to those
database objects
• Data manipulation language
– Includes commands to insert, update, delete,
and retrieve data within the database tables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
3
6
Introduction to SQL (continued)
• SQL is relatively easy to learn
• Basic command set has a vocabulary of less
than 100 words
• Nonprocedural language
• American National Standards Institute (ANSI)
prescribes a standard SQL
• Several SQL dialects exist
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
4
6
SQL Data Definition Commands
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
5
6
Data Manipulation Commands
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
6
6
Data Definition Commands
• Examine the simple database model and the
database tables that will form the basis for the
many SQL examples
• Understand the data environment
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
7
6
The Database Model
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
8
6
Creating the Database
• Two tasks must be completed
– create the database structure
– create the tables that will hold the end-user
data
• First task
– RDBMS creates the physical files that will hold
the database
– Tends to differ substantially from one RDBMS
to another
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
9
6
The Database Schema
• Authentication
– Process through which the DBMS verifies that
only registered users are able to access the
database
– Log on to the RDBMS using a user ID and a
password created by the database
administrator
• Schema
– Group of database objects—such as tables
and indexes—that are related to each other
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
10
6
Data Types
• Data type selection is usually dictated by the
nature of the data and by the intended use
• Pay close attention to the expected use of
attributes for sorting and data retrieval
purposes
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
11
6
Some Common SQL Data Types
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
12
6
Creating Table Structures
• Use one line per column (attribute) definition
• Use spaces to line up the attribute characteristics and
constraints
• Table and attribute names are capitalized
• NOT NULL specification
• UNIQUE specification
• 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 a semicolon
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
13
6
Other SQL Constraints
• NOT NULL constraint
– Ensures that a column does not accept nulls
• UNIQUE constraint
– Ensures that all values in a column are unique
• DEFAULT constraint
– Assigns a value to an attribute when a new row is
added to a table
• CHECK constraint
– Validates data when an attribute value is entered
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
14
6
SQL Indexes
• When a primary key is declared, DBMS
automatically creates a unique index
• Often need additional indexes
• Using the CREATE INDEX command, SQL
indexes can be created on the basis of any
selected attribute
• Composite index
– Index based on two or more attributes
– Often used to prevent data duplication
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
15
6
A Duplicated TEST Record
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
16
6
Data Manipulation Commands
• Adding table rows
• Saving table changes
• Listing table rows
• Updating table rows
• Restoring table contents
• Deleting table rows
• Inserting table rows with a select subquery
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
17
6
Common SQL Data Manipulation
Commands
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
18
6
A Data View and Entry Form
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
19
6
Saving Table Changes
• Changes made to table contents are not
physically saved on disk until
– Database is closed
– Program is closed
– COMMIT command is used
• Syntax
– COMMIT [WORK]
• Will permanently save any changes made to
any table in the database
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
20
6
Listing Table Rows
• 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: Design, Implementation, & Management, 6th Edition, Rob & Coronel
21
6
Updating Table Rows
• UPDATE
– Modify data in a table
• Syntax
– UPDATE tablename
SET columnname = expression [, columname
= expression]
[WHERE conditionlist];
• If more than one attribute is to be updated in
the row, separate corrections with commas
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
22
6
Restoring Table Contents
• ROLLBACK
– Used restore the database to its previous
condition
– Only applicable if COMMIT command has not
been used to permanently store the changes
in the database
• Syntax
– ROLLBACK;
• COMMIT and ROLLBACK only work with
data manipulation commands that are used to
add, modify, or delete table rows
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
23
6
Deleting Table Rows
• DELETE
– Deletes a table row
• Syntax
– DELETE FROM tablename
[WHERE conditionlist ];
• WHERE condition is optional
• If WHERE condition is not specified, all rows
from the specified table will be deleted
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
24
6
Inserting Table Rows with a Select
Subquery
• INSERT
– Inserts multiple rows from another table
(source)
– Uses SELECT subquery
• Query that is embedded (or nested) inside
another query
• Executed first
• Syntax
– INSERT INTO tablename SELECT columnlist
FROM tablename
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
25
6
Selecting Rows with Conditional
Restrictions
• Select partial table contents by placing
restrictions on rows to be included in output
– Add conditional restrictions to the SELECT
statement, using WHERE clause
• Syntax
– SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
26
6
Selected PRODUCT Table Attributes for
VENDOR Code 21344
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
27
6
The Microsoft Access QBE and its SQL
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
28
6
Comparison Operators
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
29
6
Selected PRODUCT Table Attributes for
VENDOR Codes Other than 21344
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
30
6
Selected PRODUCT Table Attributes
with a P_PRICE Restriction
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
31
6
Selected PRODUCT Table Attributes:
The ASCII Code Effect
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
32
6
Selected PRODUCT Table Attributes:
Date Restriction
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
33
6
SELECT Statement
with a Computed Column
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
34
6
SELECT Statement with a Computed
Column and an Alias
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
35
6
Arithmetic Operators:
The Rule of Precedence
• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
36
6
Selected PRODUCT Table Attributes:
The Logical OR
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
37
6
Selected PRODUCT Table Attributes:
The Logical AND
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
38
6
Selected PRODUCT Table Attributes:
The Logical AND and OR
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
39
6
Special Operators
• BETWEEN
– Used to check whether attribute value is within a
range
• IS NULL
– Used to check whether attribute value is null
• LIKE
– Used to check whether attribute value matches a
given string pattern
• IN
– Used to check whether attribute value matches
any value within a value list
• EXISTS
– Used to check if a subquery returns any rows
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
40
6
Advanced Data Definition Commands
• All changes in the table structure are made
by using the ALTER command
– Followed by a keyword that produces specific
change
– Three options are available
• ADD
• MODIFY
• DROP
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
41
6
Changing a Column’s Data Type
• ALTER can be used to change data type
• Some RDBMSs (such as Oracle) do not
permit changes to data types unless the
column to be changed is empty
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
42
6
Changing a Column’s Data
Characteristics
• Use ALTER to change data characteristics
• If the column to be changed already contains
data, changes in the column’s characteristics
are permitted if those changes do not alter
the data type
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
43
6
Adding or Dropping a Column
• Use ALTER to add a column
– Do not include the NOT NULL clause for new
column
• Use ALTER to drop a column
– Some RDBMSs impose restrictions on the
deletion of an attribute
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
44
6
The Effect of Data Entry into the New
P_SALECODE Column
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
45
6
Update of the P_SALECODE Column in
Multiple Data Rows
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
46
6
The Effect of Multiple Data Updates in the
PRODUCT Table (MS Access)
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
47
6
Copying Parts of Tables
• SQL permits copying contents of selected
table columns so that the data need not be
reentered manually into newly created
table(s)
• First create the PART table structure
• Next add rows to new PART table using
PRODUCT table rows
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
48
6
PART Attributes Copied
from the PRODUCT Table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
49
6
Advanced Select Queries
• SQL provides useful functions
– Count
– Find minimum and maximum values
– Calculate averages
• SQL allows the user to limit queries to only
those entries having no duplicates or entries
whose duplicates may be grouped
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
50
6
Selected PRODUCT Table Attributes:
Ordered by (Ascending) P_PRICE
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
51
6
Partial Listing of
EMPLOYEE Table Contents
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
52
6
Telephone List Query Results
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
53
6
A Query Based on Multiple Restrictions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
54
6
A Listing of Distinct (Different) V_CODE
Values in the PRODUCT Table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
55
6
Some Basic SQL Aggregate Functions
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
56
6
COUNT Function Output Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
57
6
MAX and MIN Function Output Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
58
6
The Total Value of All Items
in the PRODUCT Table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
59
6
AVG Function Output Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
60
6
GROUP BY Clause Output Examples
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
61
6
Incorrect and Correct Use
of the GROUP BY Clause
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
62
6
An Application of the HAVING Clause
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
63
6
Virtual Tables: Creating a View
• View is a virtual table based on a SELECT
query
– Can contain columns, computed columns,
aliases, and aggregate functions from one or
more tables
• Base tables are tables on which the view is
based
• Create a view by using the CREATE VIEW
command
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
64
6
Creating a Virtual Table
with the CREATE VIEW Command
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
65
6
Joining Database Tables
• Ability to combine (join) tables on common
attributes is most important distinction
between a relational database and other
databases
• Join is performed when data are retrieved
from more than one table at a time
• Join is generally composed of an equality
comparison between the foreign key and the
primary key of related tables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
66
6
Creating Links Through Foreign Keys
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
67
6
The Results of a Join
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
68
6
An Ordered and Limited Listing
After a JOIN
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
69
6
The Contents of the EMP Table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
70
6
Using an Alias to Join a Table to Itself
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
71
6
The Left Outer Join Results
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
72
6
The Right Outer Join Results
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
73
6
Converting an ER Model
into a Database Structure
• Requires following specific rules that govern
such a conversion
• Decisions made by the designer to govern
data integrity are reflected in the foreign key
rules
• Implementation decisions vary according to
the problem being addressed
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
74
6
The Ch06_Artist Database ERD
and Tables
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
75
6
A Data Dictionary
for the Ch06_Artist Database
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
76
6
A Summary of Foreign Key Rules
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
77
6
Summary
• SQL commands can be divided into two
overall categories:
– Data definition language commands
– Data manipulation language commands
• Basic data definition commands allow you to
create tables, indexes, and views
• Many SQL constraints can be used with
columns
• Aggregate functions
– Special functions that perform arithmetic
computations over a set of rows
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
78
6
Summary (continued)
• ORDER BY clause
– Used to sort output of a SELECT statement
– Can sort by one or more columns and use
either an ascending or descending order
• Join output of multiple tables with SELECT
statement
• Natural join uses join condition to match only
rows with equal values in specified columns
• Right outer join and left outer join used to
select rows that have no matching values in
other related table
Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
79