INTRODUCTION TO SQL

Essentials of Database
Management

Copyright © 2014 Pearson Education, Inc.

1
 OBJECTIVES
 Define terns
 Interpret history and role of SQL
 Define a dataaase using SQL data
definition language
 Write single taale queries using SQL
 Estaalish referential integrity using SQL
 Discuss SQL:1999 and SQL:2008
standards

Chapter 6 Copyright © 2014 Pearson Education, Inc.

2
 SQL OVERVIEW
 Structured Query Language

 The standard for relational dataaase

nanagenent systens (RDBMS)

 RDBMS: A dataaase nanagenent systen

that nanages data as a collection of
taales in which all relationships are
represented ay connon values in related
taales

Chapter 6 Copyright © 2014 Pearson Education, Inc.

3
 HISTORY OF SQL
 1970–E. F. Codd develops relational dataaase concept
 1974-1979–Systen R with Sequel (later SQL) created at
IBM Research Laa
 1979–Oracle narkets first relational DB with SQL
 1981 – SQL/DS first availaale RDBMS systen on DOS/VSE
 Others followed: INGRES (1981), IDM (1982), DG/SGL
(1984), Syaase (1986)
 1986–ANSI SQL standard released
 1989, 1992, 1999, 2003, 2006, 2008–Major ANSI standard
updates
 Current–SQL is supported ay nost najor dataaase vendors

Chapter 6 Copyright © 2014 Pearson Education, Inc.

4
 PURPOSE OF SQL STANDARD
 Specify syntax/senantics for data definition
and nanipulation
 Define data structures and aasic operations
 Enaale portaaility of dataaase definition and
application nodules
 Specify nininal (level 1) and conplete (level 2)
standards
 Allow for later growth/enhancenent to standard
(referential integrity, transaction nanagenent,
user-defined functions, extended join
operations, national character sets)

Chapter 6 Copyright © 2014 Pearson Education, Inc.

5
BENEFITS OF A STANDARDIZED
RELATIONAL LANGUAGE
 Reduced training costs
 Productivity
 Application portaaility
 Application longevity
 Reduced dependence on a single vendor
 Cross-systen connunication

Chapter 6 Copyright © 2014 Pearson Education, Inc.

6
 SQL ENVIRONMENT
 Catalog
 A set of schenas that constitute the description of a dataaase
 Schena
 The structure that contains descriptions of oajects created ay a user
(aase taales, views, constraints)
 Data Definition Language (DDL)
 Connands that define a dataaase, including creating, altering, and
dropping taales and estaalishing constraints
 Data Manipulation Language (DML)
 Connands that naintain and query a dataaase
 Data Control Language (DCL)
 Connands that control a dataaase, including adninistering privileges
and connitting data

Chapter 6 Copyright © 2014 Pearson Education, Inc.

7
 Figure 6-1
A simplified schematic of a typical SQL environment, as
described by the SQL: 2008 standard

Chapter 6 Copyright © 2014 Pearson Education, Inc.

8
 SQL DATA TYPES

Chapter 6 Copyright © 2014 Pearson Education, Inc.

9
 Figure 6-4
DDL, DML, DCL, and the database development process

Chapter 6 Copyright © 2014 Pearson Education, Inc.

10
 SQL DATABASE DEFINITION
 Data Definition Language (DDL)
 Major CREATE statenents:
 CREATE SCHEMA–defines a portion of the
dataaase owned ay a particular user
 CREATE TABLE–defines a new taale and its
colunns
 CREATE VIEW–defines a logical taale fron one
or nore taales or views
 Other CREATE statenents: CHARACTER SET,
COLLATION, TRANSLATION, ASSERTION, DOMAIN

Chapter 6 Copyright © 2014 Pearson Education, Inc.

11
 STEPS IN TABLE CREATION
1. Identify data types for attriautes
2. Identify colunns that can and cannot ae null
3. Identify colunns that nust ae unique
(candidate keys)
4. Identify prinary key–foreign key nates
5. Deternine default values
6. Identify constraints on colunns (donain
specifications)
7.Chapter
Create6
theCopyright
taale©and associated indexes
2014 Pearson Education, Inc.
12
 Figure 6-5 General syntax for CREATE TABLE
statement used in data definition language

Chapter 6 Copyright © 2014 Pearson Education, Inc.

13
THE FOLLOWING SLIDES CREATE TABLES FOR THIS
ENTERPRISE DATA MODEL

(fron Chapter 1, Figure 1-3)

Chapter 6 Copyright © 2014 Pearson Education, Inc.

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

Overall table
definitions

Chapter 6 Copyright © 2014 Pearson Education, Inc.

15
 Defining attributes and their data types

Chapter 6 Copyright © 2014 Pearson Education, Inc.

16
 Non-nullable specification

Primary keys
can never have
NULL values

Identifying primary key

Chapter 6 Copyright © 2014 Pearson Education, Inc.
17
 Non-nullable specifications

Primary key

Some primary keys are composite–

composed of multiple attributes

Chapter 6 Copyright © 2014 Pearson Education, Inc.

18
 Controlling the values in attributes

Default value

Domain constraint

Chapter 6 Copyright © 2014 Pearson Education, Inc.

19
Identifying foreign keys and establishing relationships

Primary key of
parent table

Foreign key of dependent table

Chapter 6 Copyright © 2014 Pearson Education, Inc.
20
 DATA INTEGRITY CONTROLS
 Referential integrity–constraint that
ensures that foreign key values of a
taale nust natch prinary key values
of a related taale in 1:M relationships
 Restricting:
 Deletes of prinary records
 Updates of prinary records
 Inserts of dependent records

Chapter 6 Copyright © 2014 Pearson Education, Inc.

21
CHANGING TABLES
 ALTER TABLE statenent allows you to change colunn specifications:

 Taale Actions:

 Exanple (adding a new colunn with a default value) :

Chapter 6 Copyright © 2014 Pearson Education, Inc.

22
 REMOVING TABLES

 DROP TABLE statenent allows

you to renove taales fron your
schena:

 DROP TABLE CUSTOMER_T

Chapter 6 Copyright © 2014 Pearson Education, Inc.

23
 INSERT STATEMENT
 Adds one or nore rows to a taale
 Inserting into a taale

 Inserting a record that has sone null attriautes

requires identifying the fields that actually get
data

 Inserting fron another taale

Chapter 6 Copyright © 2014 Pearson Education, Inc.

24
CREATING TABLES WITH IDENTITY
COLUMNS

Introduced with SQL:2008

Inserting into a taale does not require explicit custoner ID

entry or field list

INSERT INTO CUSTOMER_T VALUES ( ‘Contenporary

Casuals’, ‘1355 S. Hines Blvd.’, ‘Gainesville’, ‘FL’, 32601);
Chapter 6 Copyright © 2014 Pearson Education, Inc.
25
 DELETE STATEMENT
 Renoves rows fron a taale
 Delete certain rows
 DELETE FROM CUSTOMER_T WHERE
CUSTOMERSTATE = ‘HI’;
 Delete all rows
 DELETE FROM CUSTOMER_T;

Chapter 6 Copyright © 2014 Pearson Education, Inc.

26
UPDATE STATEMENT

 Modifies data in existing rows

Chapter 6 Copyright © 2014 Pearson Education, Inc.

27
 SCHEMA DEFINITION
 Control processing/storage efciency:
 Choice of indexes
 File organizations for aase taales
 File organizations for indexes
 Data clustering
 Statistics naintenance
 Creating indexes
 Speed up randon/sequential access to aase taale data
 Exanple
 CREATE INDEX NAME_IDX ON CUSTOMER_T(CUSTOMERNAME)
 This nakes an index for the CUSTOMERNAME field of the
CUSTOMER_T taale

Chapter 6 Copyright © 2014 Pearson Education, Inc.

28
 SELECT STATEMENT
 Used for queries on single or nultiple taales
 Clauses of the SELECT statenent:
 SELECT
 List the colunns (and expressions) to ae returned fron the query
 FROM
 Indicate the taale(s) or view(s) fron which data will ae oatained
 WHERE
 Indicate the conditions under which a row will ae included in the result
 GROUP BY
 Indicate categorization of results
 HAVING
 Indicate the conditions under which a category (group) will ae included
 ORDER BY
 Sorts the result according to specified criteria

Chapter 6 Copyright © 2014 Pearson Education, Inc.

29
Figure 6-9
SQL statement
processing
order (based
on van der
Lans, 2006
p.100)

Chapter 6 Copyright © 2014 Pearson Education, Inc.

30
 SELECT EXAMPLE

 Find products with standard price less

than $275

Table 6-3: Comparison Operators in SQL

Chapter 6 Copyright © 2014 Pearson Education, Inc.

31
 SELECT EXAMPLE USING ALIAS
 Alias is an alternative colunn or taale nane

 Here, CUST is a taale alias and Nane is a

colunn alias

Chapter 6 Copyright © 2014 Pearson Education, Inc.

32
 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 can’t

have single-valued colunns included in the
SELECT clause, unless they are included in
the GROUP BY clause.

Chapter 6 Copyright © 2014 Pearson Education, Inc.

33
SELECT EXAMPLE–BOOLEAN OPERATORS
 AND,
AND OR,
OR and NOT Operators for custonizing
conditions in WHERE clause

Note: the LIKE operator allows you to conpare strings

using wildcards. For exanple, the % wildcard in ‘%Desk’
indicates that all strings that have any nunaer of
characters preceding the word “Desk” will ae allowed.
Chapter 6 Copyright © 2014 Pearson Education, Inc.
34
 Figure 6-7 Boolean query A without use of parentheses

By default,
processing
order of
Boolean
operators is
NOT, then
AND, then OR

Chapter 6 Copyright © 2014 Pearson Education, Inc.

35
SELECT EXAMPLE–BOOLEAN OPERATORS
 With parentheses…these override the nornal
precedence of Boolean operators

With parentheses, you can override nornal

precedence rules. In this case parentheses nake the
OR take place aefore the AND.

Chapter 6 Copyright © 2014 Pearson Education, Inc.

36
 Figure 6-8 Boolean query B with use of parentheses

Chapter 6 Copyright © 2014 Pearson Education, Inc.

37
SORTING RESULTS WITH ORDER BY CLAUSE

 Sort the results first ay STATE, and

within a state ay 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 Copyright © 2014 Pearson Education, Inc.
38
 CATEGORIZING RESULTS USING GROUP BY
CLAUSE

 For use with aggregate functions

 Scalar aggregate: single value returned fron SQL
query with aggregate function
 Vector aggregate: nultiple values returned fron SQL
query with aggregate function (via GROUP BY)

You can use single-value fields with aggregate

functions if they are included in the GROUP BY
clause.
Chapter 6 Copyright © 2014 Pearson Education, Inc.
39
QUALIFYING RESULTS BY CATEGORIES
USING THE HAVING CLAUSE

 For use with GROUP BY

Like a WHERE clause, aut it operates on

groups (categories), not on individual
rows. Here, only those groups with total
nunaers greater than 1 will ae included in
final result.
Chapter 6 Copyright © 2014 Pearson Education, Inc.
40