Chapter 7:
Introduction to SQL
Modern Database
Management
8th Edition
Jeffrey A. Hoffer, Mary B. Prescott,
Fred R. McFadden
by Prentice Hall
Objectives
Definition of terms
Interpret history and role of SQL
Define a database using SQL data
definition language
Write single table queries using SQL
Establish referential integrity using SQL
Discuss SQL:1999 and SQL:2003
standards
Chapter 7
by Prentice Hall
SQL Overview
Structured Query Language
The standard for relational database
management systems (RDBMS)
RDBMS: A database management system
that manages data as a collection of
tables in which all relationships are
represented by common values in related
tables
Chapter 7
by Prentice Hall
History of SQL
1970E. Codd develops relational database
concept
1974-1979System R with Sequel (later SQL)
created at IBM Research Lab
1979Oracle markets first relational DB with SQL
1986ANSI SQL standard released
1989, 1992, 1999, 2003Major ANSI standard
updates
CurrentSQL is supported by most major
database vendors
Chapter 7
by Prentice Hall
Purpose of SQL Standard
Specify syntax/semantics for data
definition and manipulation
Define data structures
Enable portability
Specify minimal (level 1) and complete
(level 2) standards
Allow for later growth/enhancement to
standard
Chapter 7
by Prentice Hall
Benefits of a Standardized
Relational Language
Reduced training costs
Productivity
Application portability
Application longevity
Reduced dependence on a single
vendor
Cross-system communication
Chapter 7
by Prentice Hall
Catalog
Commands that define a database, including creating, altering, and
dropping tables and establishing constraints
Data Manipulation Language (DML)
The structure that contains descriptions of objects created by a user
(base tables, views, constraints)
Data Definition Language (DDL)
A set of schemas that constitute the description of a database
Schema
SQL Environment
Commands that maintain and query a database
Data Control Language (DCL)
Commands that control a database, including administering
privileges and committing data
Chapter 7
by Prentice Hall
Figure 7-1
A simplified schematic of a typical SQL environment, as
described by the SQL-2003 standard
Chapter 7
by Prentice Hall
Some SQL Data types
Chapter 7
by Prentice Hall
Figure 7-4
DDL, DML, DCL, and the database development process
Chapter 7
by Prentice Hall
10
SQL Database Definition
Data Definition Language (DDL)
Major CREATE statements:
CREATE SCHEMAdefines a portion of the
database owned by a particular user
CREATE TABLEdefines a table and its
columns
CREATE VIEWdefines a logical table from
one or more views
Other CREATE statements: CHARACTER
SET, COLLATION, TRANSLATION,
ASSERTION, DOMAIN
Chapter 7
by Prentice Hall
11
Table Creation
Figure 7-5 General syntax for CREATE TABLE
Steps in table
creation:
1. Identify data types for
attributes
2. Identify columns that
can and cannot be
null
3. Identify columns that
must be unique
(candidate keys)
4. Identify primary key
foreign key mates
5. Determine default
values
Chapter 7
by Prentice Hall
6. Identify constraints on
columns (domain
specifications) 12
The following slides create tables
for this enterprise data model
Chapter 7
by Prentice Hall
13
Figure 7-6 SQL database definition commands for Pine Valley Furniture
Overall table
definitions
Chapter 7
by Prentice Hall
14
Defining attributes and their data types
Chapter 7
by Prentice Hall
15
Non-nullable specification
Identifying primary key
Chapter 7
by Prentice Hall
Primary keys
can never have
NULL values
16
Non-nullable specifications
Primary key
Some primary keys are composite
composed of multiple attributes
Chapter 7
by Prentice Hall
17
Controlling the values in attributes
Default value
Domain constraint
Chapter 7
by Prentice Hall
18
Identifying foreign keys and establishing relationships
Primary key of
parent table
Foreign key of
dependent table
Chapter 7
by Prentice Hall
19
Data Integrity Controls
Referential integrityconstraint that
ensures that foreign key values of a
table must match primary key
values of a related table in 1:M
relationships
Restricting:
Deletes of primary records
Updates of primary records
Inserts of dependent records
Chapter 7
by Prentice Hall
20
Figure 7-7 Ensuring data integrity through updates
Relational
integrity is
enforced via
the primarykey to foreignkey match
Chapter 7
by Prentice Hall
21
Changing and Removing
Tables
ALTER TABLE statement allows you to
change column specifications:
ALTER TABLE CUSTOMER_T ADD (TYPE
VARCHAR(2))
DROP TABLE statement allows you to
remove tables from your schema:
DROP TABLE CUSTOMER_T
Chapter 7
by Prentice Hall
22
Schema Definition
Control processing/storage efficiency:
Choice of indexes
File organizations for base tables
File organizations for indexes
Data clustering
Statistics maintenance
Creating indexes
Speed up random/sequential access to base table data
Example
CREATE INDEX NAME_IDX ON CUSTOMER_T(CUSTOMER_NAME)
This makes an index for the CUSTOMER_NAME field of the
CUSTOMER_T table
Chapter 7
by Prentice Hall
23
Insert Statement
Adds data to a table
Inserting into a table
Inserting a record that has some null attributes
requires identifying the fields that actually get data
INSERT INTO CUSTOMER_T VALUES (001, Contemporary
Casuals, 1355 S. Himes Blvd., Gainesville, FL, 32601);
INSERT INTO PRODUCT_T (PRODUCT_ID,
PRODUCT_DESCRIPTION,PRODUCT_FINISH, STANDARD_PRICE,
PRODUCT_ON_HAND) VALUES (1, End Table, Cherry, 175, 8);
Inserting from another table
INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T WHERE
STATE = CA;
Chapter 7
by Prentice Hall
24
Creating Tables with Identity
Columns New with SQL:2003
Inserting into a table does not require explicit customer ID
entry or field list
INSERT INTO CUSTOMER_T VALUES ( Contemporary
Casuals, 1355 S. Himes Blvd., Gainesville, FL, 32601);
Chapter 7
by Prentice Hall
25
Delete Statement
Removes rows from a table
Delete certain rows
DELETE FROM CUSTOMER_T WHERE
STATE = HI;
Delete all rows
DELETE FROM CUSTOMER_T;
Chapter 7
by Prentice Hall
26
Update Statement
Modifies data in existing rows
UPDATE PRODUCT_T SET UNIT_PRICE =
775 WHERE PRODUCT_ID = 7;
Chapter 7
by Prentice Hall
27
Merge Statement
Makes it easier to update a tableallows combination of Insert
and Update in one statement
Useful for updating master tables with new data
Chapter 7
by Prentice Hall
28
SELECT Statement
Used for queries on single or multiple tables
Clauses of the SELECT statement:
SELECT
FROM
Indicate categorization of results
HAVING
Indicate the conditions under which a row will be included in the result
GROUP BY
Indicate the table(s) or view(s) from which data will be obtained
WHERE
List the columns (and expressions) that should be returned from the query
Indicate the conditions under which a category (group) will be included
ORDER BY
Sorts the result according to specified criteria
Chapter 7
by Prentice Hall
29
Figure 7-10
SQL statement
processing
order (adapted
from van der
Lans, p.100)
Chapter 7
by Prentice Hall
30
SELECT Example
Find products with standard price less
than $275
SELECT PRODUCT_NAME, STANDARD_PRICE
FROM PRODUCT_V
WHERE STANDARD_PRICE < 275;
Table 7-3: Comparison Operators in SQL
Chapter 7
by Prentice Hall
31
SELECT Example Using Alias
Alias is an alternative column or table
name
SELECT CUST.CUSTOMER AS NAME,
CUST.CUSTOMER_ADDRESS
FROM CUSTOMER_V CUST
WHERE NAME = Home Furnishings;
Chapter 7
by Prentice Hall
32
SELECT Example
Using a Function
Using the COUNT aggregate function
to find totals
SELECT COUNT(*) FROM ORDER_LINE_V
WHERE ORDER_ID = 1004;
Note: with aggregate functions you cant have
single-valued columns included in the
SELECT clause
Chapter 7
by Prentice Hall
33
SELECT ExampleBoolean
Operators
AND, OR, and NOT Operators for customizing
conditions in WHERE clause
SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH,
STANDARD_PRICE
FROM PRODUCT_V
WHERE (PRODUCT_DESCRIPTION LIKE %Desk
OR PRODUCT_DESCRIPTION LIKE %Table)
AND UNIT_PRICE > 300;
Note: the LIKE operator allows you to compare strings using
wildcards. For example, the % wildcard in %Desk indicates that
all strings that have any number of characters preceding the
word Desk will be allowed
Chapter 7
by Prentice Hall
34
Venn Diagram from Previous
Query
Chapter 7
by Prentice Hall
35
SELECT Example
Sorting Results with the ORDER BY
Clause
Sort the results first by STATE, and
within a state by CUSTOMER_NAME
SELECT CUSTOMER_NAME, CITY, STATE
FROM CUSTOMER_V
WHERE STATE IN (FL, TX, CA, HI)
ORDER BY STATE, CUSTOMER_NAME;
Note: the IN operator in this example allows you to include rows whose
STATE value is either FL, TX, CA, or HI. It is more efficient than separate
OR conditions
Chapter 7
by Prentice Hall
36
SELECT Example
Categorizing Results Using the GROUP BY
Clause
For use with aggregate functions
Scalar aggregate: single value returned from SQL query with
aggregate function
Vector aggregate: multiple values returned from SQL query
with aggregate function (via GROUP BY)
SELECT CUSTOMER_STATE, COUNT(CUSTOMER_STATE)
FROM CUSTOMER_V
GROUP BY CUSTOMER_STATE;
Note: you can use single-value fields with aggregate
functions if they are included in the GROUP BY
clause
Chapter 7
by Prentice Hall
37
SELECT Example
Qualifying Results by Categories
Using the HAVING Clause
For use with GROUP BY
SELECT CUSTOMER_STATE,
COUNT(CUSTOMER_STATE)
FROM CUSTOMER_V
GROUP BY CUSTOMER_STATE
HAVING COUNT(CUSTOMER_STATE) > 1;
Like a WHERE clause, but it operates on groups (categories), not
on individual rows. Here, only those groups with total numbers
greater than 1 will be included in final result
Chapter 7
by Prentice Hall
38
Using and Defining Views
Views provide users controlled access to tables
Base Tabletable containing the raw data
Dynamic View
A virtual table created dynamically upon request by a user
No data actually stored; instead data from base table made
available to user
Based on SQL SELECT statement on base tables or other views
Materialized View
Copy or replication of data
Data actually stored
Must be refreshed periodically to match the corresponding base
tables
Chapter 7
by Prentice Hall
39
Sample CREATE VIEW
CREATE VIEW EXPENSIVE_STUFF_V AS
SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE
FROM PRODUCT_T
WHERE UNIT_PRICE >300
WITH CHECK_OPTION;
View has a name
View is based on a SELECT statement
CHECK_OPTION works only for
updateable views and prevents updates
that would create rows not included in
the view
Chapter 7
by Prentice Hall
40
Advantages of Views
Simplify query commands
Assist with data security (but don't rely on
views for security, there are more
important security measures)
Enhance programming productivity
Contain most current base table data
Use little storage space
Provide customized view for user
Establish physical data independence
Chapter 7
by Prentice Hall
41
Disadvantages of Views
Use processing time each time view
is referenced
May or may not be directly
updateable
Chapter 7
by Prentice Hall
42
