Transcript Chapter 9: Structured Query Language

Database Processing
Chapter 9
Structured Query Language
David M. Kroenke
© 2000 Prentice Hall
Chapter 9
SQL
“Structured Query Language; standard
language for relational data
manipulation”
DB2, SQL/DS, Oracle
INGRES, SYBASE, SQL Server
dBase/Win, Paradox, Access
Page 217
© 2000 Prentice Hall
Chapter 9
Introduction to SQL
• IBM in the mid-1970s as SEQUEL
• SQ92 = 1992 ANSI standard
• data access language that is
embedded in application programs
• result of an SQL statement is a
relation
Page 217
© 2000 Prentice Hall
Sample Data
Figure 9-2
© 2000 Prentice Hall
Chapter 9
Projection
SELECT SID, Name, Major
FROM STUDENT
Page 219
© 2000 Prentice Hall
Chapter 9
Selection
SELECT SID, Name, Major, GradeLevel, Age
FROM STUDENT
WHERE Major = ‘MATH’
Page 220
© 2000 Prentice Hall
Chapter 9
Selection
SELECT *
FROM STUDENT
WHERE Age Between 19 and 30
Page 222
© 2000 Prentice Hall
Chapter 9
Sorting
SELECT Name, Major, Age
FROM STUDENT
WHERE Major = ‘ACCOUNTING’
ORDER BY Name
Page 223
© 2000 Prentice Hall
Chapter 9
SQL Built-In Functions
SELECT COUNT(Major)
FROM STUDENT
Page 223
© 2000 Prentice Hall
Chapter 9
Grouping
SELECT Major, COUNT(*)
FROM STUDENT
GROUP BY Major
HAVING COUNT(*) > 1
Page 224
© 2000 Prentice Hall
Chapter 9
Querying Multiple Tables
• Retrieval Using Subquery
• Joining with SQL
Page 226
© 2000 Prentice Hall
Chapter 9
Subquery (the second SELECT)
SELECT Name
FROM STUDENT
WHERE SID IN
(SELECT StudentNumber
FROM ENROLLMENT
WHERE ClassName = ‘BD445’)
Page 226
© 2000 Prentice Hall
Chapter 9
Joining with SQL
SELECT STUDENT.SID,
STUDENT.Name,
ENROLLMENT.ClassName
FROM STUDENT, ENROLLMENT
WHERE STUDENT.SID =
ENROLLMENT.StudentNumber
Page 227
© 2000 Prentice Hall
Chapter 9
Outer Join
SELECT Name, ClassName
FROM STUDENT LEFT JOIN
ENROLLMENT ON SID =
StudentNumber;
(note semicolon notation)
Page 230
© 2000 Prentice Hall
Chapter 9
Exists and Not Exists
“Logical operators whose value is either
true or false depending on the
presence or absence of rows that fit
the qualifying conditions”
Page 230
© 2000 Prentice Hall
Chapter 9
Exist
SELECT DISTINCT StudentNumber
FROM ENROLLMENT A
WHERE EXISTS
(SELECT *
FROM ENROLLMENT B
WHERE A.StudentNumber = B.StudentNumber
AND A.ClassName NOT = B.ClassName)
Page 230
© 2000 Prentice Hall
Chapter 9
Changing Data
• Inserting Data
INSERT INTO ENROLLMENT
VALUES (400, ‘BD445’, 44)
• Deleting Data
• Modifying Data
UPDATE ENROLLMENT
SET PositionNumber = 44
WHERE SID = 400
DELETE STUDENT
WHERE STUDENT.SID = 100
Page 232
© 2000 Prentice Hall