STRUCTURED QUERY LANGUAGE SECTION 6

1
STRUCTURED QUERY LANGUAGESECTION 6

• The Core of SQL (1992)

2
Introduction

• Most important relational data manipulation
  language

• ANSI
• Used by many commercial databases

3

• SEQUEL

• 1992 ANSI standard
• SQL3

4

• An interactive query language

• Or embedded in other languages
• SQL is not a programming language

5
Querying a Single Table

• SQL is a transform-oriented language

• Accepts as input
• The result?

6

• Relations used for SQL examples

• JUNIOR (Snum, Name, Major)
• HONOUR-STUDENT (Number, Name, Interest)
• STUDENT (SID, Name, Major, GradeLevel, Age)
• CLASS (Name, Time, Room)
• ENROLLMENT ( StudentNumber, ClassName,
  PositionNumber)
• FACULTY (FID, Name, Department)

Is there a problem with the ENROLLMENT table and
the primary key?
7

• Sample Data

STUDENT STUDENT STUDENT STUDENT STUDENT
SID Name Major GradeLevel Age
100 JONES HISTORY GR 21
150 PARKS ACCOUNTING SO 19
200 BAKER MATH GR 50
250 GLASS HISTORY SN 50
300 BAKER ACCOUNTING SN 41
350 RUSSELL MATH JR 20
400 RYE ACCOUNTING FR 18
450 JONES HISTORY SN 24
8
ENROLLMENT ENROLLMENT
StudentNumber ClassName PositionNumber
100 BD445 1
150 BA200 1
200 BD445 2
200 CS250 1
300 CS150 1
400 BA200 2
400 BF410 1
400 CS250 2
450 BA200 3
CLASS CLASS
Name Time Room
BA200 M-F9 SC110
BD445 MWF3 SC213
BF410 MWF8 SC213
CS150 MWF3 EA304
CS250 MWF12 EB210
9

• Projections Using SQL

• The projection
• STUDENT SID, NAME, MAJOR

• Specified in SQL
• SELECT SID, Name, Major
• FROM STUDENT

10

• The result

100 JONES HISTORY
150 PARKS ACCOUNTING
200 BAKER MATH
250 GLASS HISTORY
300 BAKER ACCOUNTING
350 RUSSELL MATH
400 RYE ACCOUNTING
450 JONES HISTORY

• The word SELECT

11

• Another Example

SELECT Major FROM STUDENT
A HISTORY
A ACCOUNTING
A MATH
A HISTORY
A ACCOUNTING
A MATH
A ACCOUNTING
A HISTORY
SELECT DISTINCT Major FROM STUDENT
B HISTORY
B ACCOUNTING
B MATH
12

• Selections Using SQL

• The Relational Algebra selection operator is also
  performed

• Specified in SQL
• SELECT SID, Name, Major, GradeLevel, Age
• FROM STUDENT
• WHERE Major Math

13

• SELECT - FROM - WHERE

SELECT FROM STUDENT WHERE Major Math
200 BAKER MATH GR 50
350 RUSSELL MATH JR 20
14

• Combination of Selection and Projection

SELECT Name, Age FROM STUDENT WHERE Major
Math
BAKER 50
RUSSELL 20
15

• Conditions in a WHERE clause

SELECT Name, Age FROM STUDENT WHERE Major
Math AND Age gt 21
BAKER 50
16

• Conditions in a WHERE clause can refer to a set
  of values

SELECT Name FROM STUDENT WHERE Major IN
(Math, ACCOUNTING)
PARKS
BAKER
BAKER
RUSSELL
RYE
17

• The following expression

SELECT Name FROM STUDENT WHERE Major NOT IN
(Math, ACCOUNTING)
JONES
GLASS
JONES
18

• WHERE clause and ranges of values

SELECT Name, Major FROM STUDENT WHERE Age
BETWEEN 19 AND 30
JONES HISTORY
RUSSELL MATH
JONES HISTORY

• Equivalent to
• SELECT Name, Major
• FROM STUDENT
• WHERE Age gt 19 AND Age lt 30

19

• WHERE clause and the LIKE keyword

SELECT Name, GradeLevel FROM STUDENT WHERE Grad
eLevel LIKE _R
JONES GR
BAKER GR
RUSSELL JR
RYE FR
20

• Finding last names ending with S

SELECT Name FROM STUDENT WHERE Name LIKE S
JONES
PARKS
GLASS
JONES
21

• IS NULL keyword

• SELECT Name
• FROM STUDENT
• WHERE GradeLevel IS NULL
• What will this return?

22

• Sorting

• Rows of a result can be sorted
• SELECT Name, Major, Age
• FROM STUDENT
• WHERE Major Accounting
• ORDER BY Name

BARKER ACCOUNTING 41
PARKS ACCOUNTING 19
RYE ACCOUNTING 18
23

• Sorting more than one column

SELECT Name, Major, Age FROM STUDENT WHERE Grad
eLevel IN (FR, SO, SN) ORDER BY Major ASC,
Age DESC
BARKER ACCOUNTING 41
PARKS ACCOUNTING 19
RYE ACCOUNTING 18
GLASS HISTORY 50
JONES HISTORY 24
24

• SQL Built-In Functions

• Five functions
• COUNT, SUM, AVG, MAX, and MIN
• SELECT COUNT ()
• FROM STUDENT

8
25

• Consider the following
• SELECT COUNT (Major)
• FROM STUDENT
• vs.
• SELECT COUNT (DISTINCT Major)
• FROM STUDENT

8
3
26

• Built-In Functions and Grouping

• To increase built-in function utility
• Formed by collecting rows that have the same
  value of a specified column
• GROUP BY

27

• An example

SELECT Major, COUNT () FROM STUDENT GROUP
BY Major
HISTORY 3
ACCOUNTING 3
MATH 2
28

• Grouping subsets

SELECT Major, COUNT () FROM STUDENT GROUP
BY Major HAVING COUNT () gt 2
HISTORY 3
ACCOUNTING 3
29

• Greater generality using WHERE

SELECT Major, AVG (Age) FROM STUDENT WHERE Grad
eLevel SN GROUP BY Major HAVING COUNT () gt 1
HISTORY 37
30
Querying Multiple Tables

• Study now extended to two or more tables

• Will use the data from the following tables
• STUDENT (SID, Name, Major, GradeLevel, Age)
• CLASS (Name, Time, Room)
• ENROLLMENT ( StudentNumber, ClassName,
  PositionNumber)

31

• Retrieval Using Subquery

• Want to know the names of the students enrolled
  in class BD445

SELECT StudentNumber FROM ENROLLMENT WHERE Clas
sName BD445
100
200
32

• Use it as a subquery

SELECT Name FROM STUDENT WHERE SID
IN (SELECT StudentNumber FROM ENROLLMEN
T WHERE ClassName BD445)
JONES
BAKER
33

• Subqueries can consist of three or more tables

SELECT ENROLLMENT.StudentNumber FROM ENROLLMENT
WHERE ENROLLMENT.ClassName IN (SELECT CLASS
.Name FROM CLASS WHERE ClassTime
MWF3)
100
200
300
34

• To get the names of the students

SELECT STUDENT.Name FROM STUDENT WHERE STUDENT.
SID IN (SELECT ENROLLMENT.StudentNumber
FROM ENROLLMENT WHERE ENROLLMENT.ClassNam
e IN (SELECT CLASS.Name FROM CLASS
WHERE ClassTime MWF3))
JONES
BAKER
BAKER
35

• Joining With SQL

• Want to produce SID, StudentName, and ClassName
  for every student
• What two tables must be joined?

SELECT STUDENT.SID, STUDENT.Name,
ENROLLMENT.ClassName FROM STUDENT,
ENROLLMENT WHERE STUDENT.SID
ENROLLMENT.StudentNumber
36

• What operations make up a join?

• FROM equates to
• WHERE expresses

100 JONES BD445
150 PARKS BA200
200 BAKER BD445
200 BAKER CS250
300 BAKER CS125
400 RYE BA200
400 RYE BF410
400 RYE CS250
450 JONES BA200
37

• The WHERE clause and qualifiers

SELECT STUDENT.SID, ENROLLMENT.ClassName FROM STU
DENT, ENROLLMENT WHERE STUDENT.SID
ENROLLMENT.StudentNumber AND STUDENT.Name
RYE AND ENROLLMENT.PositionNumber 1
400 RYE BF410
38

• Data from more than two tables

SELECT STUDENT.SID, CLASS.Name,
CLASS.Time ENROLLMENT.ClassName FROM STUDENT,
ENROLLMENT, CLASS WHERE STUDENT.SID
ENROLLMENT.StudentNumber AND ENROLLMENT.ClassName
CLASS.Name AND STUDENT.Name BAKER
200 BD445 MWF3 2
200 CS250 MWF12 1
300 CS150 MWF3 1
39

• Comparison of SQL Subquery and Join

• A join is an alternative to expressing many
  subqueries
• For example, Classes taken by undergrads?
• SELECT DISTINCT ENROLLMENT.ClassName
• FROM ENROLLMENT, STUDENT
• WHERE ENROLLMENT.StudentNumber STUDENT.SID
• AND STUDENT.Grade.Level NOT GR

40

• As a subquery

SELECT DISTINCT Class.Name FROM ENROLLMENT WHERE S
tudentNumber IN (SELECT SID FROM
STUDENT WHERE GradeLevel NOT GR)
41

• If we want to know
• Class names and grade levels of undergrads?

SELECT DISTINCT ENROLLMENT.ClassName,
SUDENT.GradeLevel FROM ENROLLMENT,
STUDENT WHERE ENROLLMENT.StudentNumber
STUDENT.SID AND STUDENT.GradeLevel NOT GR
BA200 SO
CS150 SN
BA200 FR
BF410 FR
CS250 FR
BA200 SN

• Why cannot a subquery be used?

42

• Thus in the previous subquery example

SELECT DISTINCT Class.Name FROM ENROLLMENT WHERE
StudentNumber IN (SELECT SID FROM
STUDENT WHERE GradeLevel NOT GR)

• This would not work
• SELECT DISTINCT ENROLLMENT.ClassName,
  STUDENT.GradeLevel
• FROM ENROLLMENT
• WHERE StudentNumber IN
• (SELECT SID
• FROM STUDENT
• WHERE GradeLevel NOT GR)

43
EXISTS and NOT EXISTS

• Logical operators

• SELECT DISTINCT StudentNumber
• FROM ENROLLMENT A
• WHERE EXISTS
• (SELECT
• FROM ENROLLMENT B
• WHERE A.StudentNumber B.StudentNumber
• AND A.ClassName NOT B.ClassName)

44

• Both query and subquery refer to the same table

• Two uses assigned a different name
• What is the meaning of the subquery?

45

• General procedure

• First row in A is compared with first row in B
• First Row in A compared with second row in B

200
400
46

• NOT EXISTS operator

• SELECT STUDENT.Name
• FROM STUENT
• WHERE NOT EXISTS
• (SELECT
• FROM ENROLLMENT
• WHERE NOT EXISTS
• (SELECT
• FROM CLASS
• WHERE CLASS.Name ENROLLMENT.ClassName
• AND ENROLLMENT.StudentNumber
  STUDENT.SID))

• Query has three parts

47
Changing Data

• Inserting Data

• Inserting a single row
• INSERT INTO ENROLLMENT
• VALUES (400, BD445, 44)

48

• Some data in a row not known

INSERT INTO ENROLLMENT (StudentNumber,
ClassName) VALUES (400, BD445)

• Mass copy rows
• INSERT INTO JUNIOR
• VALUES
• (SELECT SID, Name, Major
• FROM STUDENT
• WHERE GradeLevel JR)

49

• Deleting Data

• Can be deleted one at a time
• DELETE STUDENT
• WHERE STUDENT.SID 100

50

• Groups of rows can be deleted

DELETE ENROLLMENT WHERE ENROLLMENT.StudentNum
ber IN (SELECT STUDENT.SID FROM STUDE
NT WHERE STUDENT.Major
Accounting) DELETE STUDENT WHERE STUDENT.Maj
or Accounting
51

• Modifying Data

• Rows can be modified one at a time
• UPDATE ENROLLMENT
• SET PositionNumber 44
• WHERE SID 400
• UPDATE ENROLLMENT
• SET PositionNumber MAX (PositionNumber) 1
• WHERE SID 400

52

• Mass Updates

UPDATE ENROLLMENT SET ClassName
BD564 WHERE ClassName BD445 UPDATE CLASS
SET ClassName BD564 WHERE ClassName
BD445
53
Summary

• Importance of SQL

• Operations on a single table
• Operations on two or more tables