Chap 6

naivenorthΤεχνίτη Νοημοσύνη και Ρομποτική

8 Νοε 2013 (πριν από 3 χρόνια και 9 μήνες)

111 εμφανίσεις

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

1

CHAPTER 6:

INTRODUCTION TO SQL

Modern Database Management

11
th

Edition

Jeffrey A. Hoffer, V. Ramesh,

Heikki

Topi


Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

2

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

3

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

4

HISTORY OF SQL



1970

E. Codd develops relational database
concept


1974
-
1979

System R with Sequel (later SQL)
created at IBM Research Lab


1979

Oracle markets first relational DB with
SQL


1986

ANSI SQL standard released


1989, 1992, 1999, 2003

Major ANSI standard
updates


Current

SQL is supported by most major
database vendors



Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

5

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

6

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

7

SQL ENVIRONMENT


Catalog



A set of schemas that constitute the description of a database


Schema


The structure that contains descriptions of objects created by
a user (base tables, views, constraints)


Data Definition Language (DDL)


Commands that define a database, including creating,
altering, and dropping tables and establishing constraints


Data Manipulation Language (DML)


our focus


Commands that maintain and query a database


Data Control Language (DCL)


Commands that control a database, including administering
privileges and committing data

Chapter 6

© 2013 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

8

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

FIG 6
-
2 QUERY SYNTAX


SELECT Major,
AVERAGE(GPA)
,
ExpGraduateYr


WHERE
ExpGraduateYr

= “2015”


GROUP BY Major


HAVING AVERAGE(GPA) >=3.0


ORDER BY Major

9

Just a quick example
to show sequence of
clauses

Display order can be
specified here

Cond for
record
s

Cond for
group
s

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

10

SQL DATA TYPES

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

Figure 6
-
4

DDL, DML, DCL, and the database development process

11

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

12

SQL DATABASE DEFINITION


Data Definition Language (DDL)


Major CREATE statements:


CREATE SCHEMA

defines a portion of the database
owned by a particular user


CREATE TABLE

defines a new table and its columns


CREATE VIEW

defines a logical table from one or more
tables or views


Other CREATE statements: CHARACTER SET,
COLLATION, TRANSLATION, ASSERTION, DOMAIN


Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

13

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

6.
Identify constraints on
columns (domain
specifications)

7.
Create the table and
associated indexes

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

14

THE FOLLOWING SLIDES CREATE TABLES
FOR THIS ENTERPRISE DATA MODEL

(from Chapter 1, Figure 1
-
3)

14

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

FIGURE 6
-
3: SAMPLE PVFC DATA

15

15

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

16

Figure 6
-
6 SQL database definition commands for Pine Valley Furniture
Company (Oracle 11g)

Overall table
definitions

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

Defining attributes and their data types

Constraint name Cnstr Type Field

Column constraint:
validation rule

17

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

Non
-
nullable

specification

Identifying primary key

Primary keys can never have NULL
values

18

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

Non
-
nullable specifications

Primary key

Some primary keys are composite


composed of multiple attributes

19

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

Default value

Domain constraint


Controlling the values in attributes

20

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

21

Primary key of
parent table

Identifying foreign keys and establishing relationships

Foreign key of
dependent
table

Constraint name
Cnstr

Type
Field
REFERENCES

Tbl
(Key)

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

22

DATA INTEGRITY CONTROLS


Referential integrity

constraint 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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

23

Relational
integrity is
enforced via
the primary
-
key to
foreign
-
key
match

Figure 6
-
7 Ensuring data integrity through updates

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

24

CHANGING TABLES


ALTER TABLE statement allows you to
change column
specifications:



Table Actions:




Example
(adding a new column with a default value)
:


ADD COLUMN Name Type Default

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

25

REMOVING TABLES



DROP TABLE statement allows you to remove
tables from your schema:



DROP TABLE CUSTOMER_T

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

26

INSERT STATEMENT


Adds one or more rows to a table


Inserting into a table





VALUE


“s” Sequence!!!



Inserting a record that
has some null attributes
requires
identifying the fields
that
actually get
data

(field
-
list)

VALUES
(value
-
list)

INSERT INTO
Table

VALUES
(value
-
list)

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

27

INSERT STATEMENT (CONT)


Inserting
from another table


A SUBSET from another table


Interpretation?

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

28

CREATING TABLES WITH IDENTITY COLUMNS

Inserting into a table does not require explicit customer ID entry or
field list (
No “001” as compared w slide #
26
)
--


INSERT INTO CUSTOMER_T VALUES ( ‘Contemporary Casuals’,
‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);

Introduced with SQL:200n

28

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

29

DELETE STATEMENT


Removes rows from a table


Delete certain rows


DELETE FROM CUSTOMER_T


WHERE CUSTOMERSTATE = ‘HI’;


Delete all rows


DELETE FROM CUSTOMER_T;


Careful!!!


Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

30

UPDATE STATEMENT



Modifies data in existing rows








Note: the WHERE clause may be a subquery
(Chap 7)

UPDATE
Table
-
name

SET
Attribute

=
Value

WHERE
Criteria
-
to
-
apply
-
the
-
update

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

RECAP OF “THREE MODIFICATIONS”

31

KEY WORD

/

Change what

SYNTAX / Example

ALTER:


INSERT:


UPDATE:


31

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

32

MERGE STATEMENT

Makes it easier to update a table…allows combination of Insert
and Update in one statement


Useful for updating master tables with new data

Interpretation?

32

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

33

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

34

SELECT EXAMPLE


Find products with standard price less than










$275


Table 6
-
3: Comparison Operators in SQL

Every SESLECT statement returns a result
table
-

Can be used as part of another query

SELECT
DISTINCT
: no duplicate rows

SELECT
*

: all columns selected

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

35

SELECT EXAMPLE USING ALIAS


Alias is an alternative column or table name


SELECT
CUST
.CUSTOMER_NAME
AS
NAME
,

CUST.CUSTOMER_ADDRESS

FROM CUSTOMER_V [
AS
]
CUST



WHERE
NAME

= ‘Home Furnishings’;


Note1:
Specifying source table
, P. 262

Note2:
Use of alias
, P. 263

The two alias have different usage:

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

USING
EXPRESSIONS (PP. 263
-
264)


P. 264


ProductStandardPrice
*1.1 AS Plus10Percent


What does it look like what we learned in
Access (in IS 312)?


Observe the result on P. 264

36

36

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

37

SELECT EXAMPLE USING A FUNCTION


Using the COUNT
aggregate function

to find
totals


SELECT
COUNT(*)

FROM ORDERLINE_T



WHERE ORDERID = 1004;


Note 1:


SELECT
PRODUCT_ID
,
COUNT(*)



FROM ORDER_LINE_V




WHERE ORDER_ID = 1004;

Note 2: COUNT (*) and COUNT
-

different


More functions:

P.
264~5

More
exmpl
:

P.
266

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

38

SELECT EXAMPLE USING FUNCTIONS


What is average standard price for each
product in inventory?


SELECT
AVG (STANDARD_PRICE)

AS AVERAGE
FROM
PROCUCT_V;



AVG, COUNT, MAX, MIN, SUM;


LN, EXP, POWER, SQRT


More functions


P. 264


Issues about set value and aggregates: 265
-
6


Wildcards: 267, top


Null values, 268, top

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

ISSUES ABOUT SET VALUE AND AGGREGATES


Last line on P. 265: SQL cannot return both a
row value (such as ID) and a set value (such as
COUNT/AVG/SUM of a group); users must run
two separate queries, one that returns row info
and one that returns set info

1.
SELECT S_ID FROM STUDENT

2.
SELECT
AVG(GPA
) FROM
STUDENT

3.
SELECT S_ID, AVG(GPA)
FROM
STUDENT


39

One of the above is not legitimate

39

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

40

SELECT EXAMPLE

BOOLEAN 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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

41

Figure 6
-
9 Boolean
query without use of
parentheses

41

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

42

SELECT EXAMPLE

BOOLEAN OPERATORS


With parentheses…these 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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

43

Figure 6
-
9 Boolean
query with use of
parentheses

43

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

44

SELECT EXAMPLE



USING
DISTINCT

VALUES


Compare:


SELECT ORDER_ID



FROM ORDER_LINE_V;


and



SELECT
DISTINCT

ORDER_ID



FROM ORDER_LINE_V;

But:

SELECT
DISTINCT ORDER_ID, ORDER_QUANTITY



FROM ORDER_LINE_V;

Compare three figures on 272 & 273


Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

45

SELECT EXAMPLE



SORTING RESULTS WITH THE ORDER BY CLAUSE


Sort the results
first by
STATE,
and then
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.

The opposite is NOT IN

Can order by multiple
fields: primary sort,
2ndry, …

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

46

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


Q: Can we do SELECT
CustCity
, COUNT(
CustState
)?


One value for a group


“vector”

Result: P. 275

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

SINGLE
-
VALUE FIELDS
VS

AGGREGATE
FUNCTIONS

1.
SELECT S_ID FROM STUDENT

2.
SELECT
MAJOR, AVG(GPA
) FROM
STUDENT
GROUP BY MAJOR

3.
SELECT S_ID, AVG(GPA) FROM
STUDENT
GROUP BY MAJOR


47

One of the above is not legitimate.
Compare Slide #39

47

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

48

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.

See 275
-
276

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

49

SELECT STATEMENT WITH GROUP BY


Used for queries on single or multiple tables


Clauses of the SELECT statement: (
Sequence!!
)


SELECT


List the columns (and expressions) that should be returned from the query


FROM


Indicate the table(s) or view(s) from which data will be obtained


WHERE


Indicate the conditions under which a row will be included in the result


GROUP BY


Indicate categorization of results


HAVING


Indicate the conditions under which a category (group) will be included


ORDER BY


Sorts the result according to specified criteria

Read closely
PP
.
275
-
276

Can have multiple sorts


primary, 2ndry, …

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

50

Figure 6
-
10

SQL statement
processing
order (adapted
from van der
Lans, 2006
p.100)

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

51


The HAVING clause acts like a WHERE clause,
but it identifies
groups that meet a criterion
,
rather than rows.


Therefore, you will usually see a HAVING clause …


WHERE qualifies a set of rows,
while
HAVING qualifies a set of …

HAVING clause

Interpretation: example on P. 276

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

52

USING AND DEFINING VIEWS


Views provide users controlled access to tables


Base Table

table 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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

53

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

54


Syntax of CREATE VIEW:


CREATE VIEW
view
-
name

AS


SELECT (
that provides the rows and columns
of the view
)


Example:


CREATE VIEW ORDER_TOTALS_V AS



SELECT PRODUCT_ID
PRODUCT
,



SUM(STANDARD_PRICE*QUANTITY)
TOTAL



FROM INVOICE_V




GROUP BY
PRODUCT_ID
;

Views

Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

55

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

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

56

DISADVANTAGES OF VIEWS


Use processing time each time view is
referenced


May or may not be directly updateable



Chapter 6

© 2013 Pearson Education, Inc.

Publishing as Prentice Hall

ADDENDUM: COMPARISON OF ALTER,
INSERT, AND UPDATE


ALTER: changing the columns of the table


ALTER TABLE CUSTOMER_T
ADD COLUMN



INSERT: adding records based on the existing
table


INSERT INTO CUTTOME_T
VALUES (… )


UPDATE: changing the values of
some fields

in
existing records


UPDATE PRODUCT_T
SET …
WHERE…

57