Chapter 6:
Introduction to SQL
Modern Database
Management
10th Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
2011 Pearson Education, Inc. Publishing as
Prentice Hall
Objectives
Define 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:200n
standards
Chapter 6
2011 Pearson Education, Inc. Publishing as 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 6
2011 Pearson Education, Inc. Publishing as 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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
Purpose of SQL Standard
Specify syntax/semantics for data
definition and manipulation
Define data structures and basic
operations
Enable portability of database definition
and application modules
Specify minimal (level 1) and complete
(level 2) standards
Allow for later growth/enhancement to
standard
Chapter 6
2011 Pearson Education, Inc. Publishing as 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 6
2011 Pearson Education, Inc. Publishing as 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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
Figure 6-1
A simplified schematic of a typical SQL environment, as
described by the SQL: 200n standard
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
SQL Data Types
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
Figure 6-4
DDL, DML, DCL, and the database development process
Chapter 6
2011 Pearson Education, Inc. Publishing as 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 new table and its
columns
CREATE VIEWdefines a logical table from one
or more tables or views
Other CREATE statements: CHARACTER
SET, COLLATION, TRANSLATION,
ASSERTION, DOMAIN
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
11
Table Creation
Figure 6-5 General syntax for CREATE
TABLE statement used in data
definition language
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 6
6. Identify constraints on
columns (domain
specifications)
2011 Pearson Education, Inc. Publishing as Prentice Hall
12
The following slides create tables
for this enterprise data model
(from Chapter 1, Figure 1-3)
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
13
Figure 6-6 SQL database definition commands for Pine Valley Furniture
Company (Oracle 11g)
Overall table
definitions
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
14
Defining attributes and their data types
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
15
Non-nullable specification
Primary keys
can never have
NULL values
Identifying primary key
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
16
Non-nullable specifications
Primary key
Some primary keys are composite
composed of multiple attributes
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
17
Controlling the values in attributes
Default value
Domain constraint
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
18
Identifying foreign keys and establishing relationships
Primary key of
parent table
Foreign key of dependent table
Chapter 6
2011 Pearson Education, Inc. Publishing as 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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
20
Figure 6-7 Ensuring data integrity through updates
Relational
integrity is
enforced via
the primarykey to foreignkey match
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
21
Changing Tables
ALTER TABLE statement allows you to change column specifications:
Table Actions:
Example (adding a new column with a default value):
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
22
Removing Tables
DROP TABLE statement allows you to
remove tables from your schema:
DROP TABLE CUSTOMER_T
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
23
Insert Statement
Adds one or more rows to a table
Inserting into a table
Inserting a record that has some null attributes
requires identifying the fields that actually get
data
Inserting from another table
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
24
Creating Tables with Identity
Columns
Introduced with SQL:200n
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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
25
Delete Statement
Removes rows from a table
Delete certain rows
DELETE FROM CUSTOMER_T WHERE
CUSTOMERSTATE = HI;
Delete all rows
DELETE FROM CUSTOMER_T;
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
26
Update Statement
Modifies data in existing rows
Chapter 6
2011 Pearson Education, Inc. Publishing as 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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
28
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(CUSTOMERNAME)
This makes an index for the CUSTOMERNAME field of the
CUSTOMER_T table
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
29
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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
30
Figure 6-10
SQL statement
processing
order (adapted
from van der
Lans, 2006
p.100)
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
31
SELECT Example
Find products with standard price less
than $275
Table 6-3: Comparison Operators in SQL
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
32
SELECT Example Using Alias
Alias is an alternative column or table
name
SELECT [Link] AS
NAME, [Link]
FROM CUSTOMER_V CUST
WHERE NAME = Home Furnishings;
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
33
SELECT Example
Using a Function
Using the COUNT aggregate function to
find totals
SELECT COUNT(*) FROM ORDERLINE_T
WHERE ORDERID = 1004;
Note: with aggregate functions you cant have
single-valued columns included in the SELECT
clause, unless they are included in the GROUP BY
clause
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
34
SELECT ExampleBoolean
Operators
AND, OR, and NOT Operators for customizing
conditions in WHERE clause
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 6 2011 Pearson Education, Inc. Publishing as Prentice Hall
35
Figure 6-9 Boolean
query without use of
parentheses
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
36
SELECT ExampleBoolean
Operators
With parenthesesthese override the normal
precedence of Boolean operators
Note: by default, the AND operator takes precedence over the
OR operator. With parentheses, you can make the OR take place
before the AND.
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
37
Figure 6-9 Boolean
query with use of
parentheses
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
38
SELECT Example
Sorting Results with the ORDER BY
Clause
Sort the results first by STATE, and
within a state by the CUSTOMER NAME
Note: the IN operator in this example allows you to include rows whose
CustomerState value is either FL, TX, CA, or HI. It is more efficient than
separate OR conditions.
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
39
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)
Note: you can use single-value fields with
aggregate functions if they are included in the
GROUP BY clause
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
40
SELECT Example
Qualifying Results by Categories
Using the HAVING Clause
For use with GROUP BY
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 6 2011 Pearson Education, Inc. Publishing as Prentice Hall
41
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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
42
Sample CREATE VIEW
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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
43
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 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
44
Disadvantages of Views
Use processing time each time view
is referenced
May or may not be directly
updateable
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
45
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright 2011 Pearson Education, Inc. Publishing as Prentice
Hall
Chapter 6
2011 Pearson Education, Inc. Publishing as Prentice Hall
46