Transcript Chapter 9: Structured Query Language

Structured Query Language
(SQL)
AIMS 3710
R. Nakatsu
Chapter 9
SQL
“Structured Query Language; standard language for
relational data manipulation”
Objectives:
1. Create the database and relation structures
2. Perform database updates, deletions, and
insertions
3. Perform both simple and complex queries to
transform the raw data into meaningful
information
Chapter 9
SQL Characteristics
1.
2.
3.
4.
5.
SQL is a non-procedural language
SQL is free-format
SQL consists of standard English words
SQL is case-insensitive
SQL can be used by a variety of users including
DBA’s, application programmers, and other types
of end-users.
Chapter 9
The Syntax
SELECT [Distinct | All] {*| column_expression [As new_name] ] [,…] }
FROM table_name [alias] [,…]
[WHERE condition]
[GROUP BY column list] [HAVING condition]
[ORDER BY column list]
Chapter 9
Clauses in a Select Statement
SELECT: specifies which columns are to appear in
the output
FROM: specifies the table or tables to be used
WHERE: filters the rows subject to some condition
ORDER BY: specifies the sorting condition
GROUP BY: forms groups of rows with the same
column value
HAVING: filters the groups subject to some
condition
Chapter 9
Types of Select Queries
Simple queries: one table, one select
Grouped queries: groups data by a column
value and produces a summary row
Subqueries: the use of a select statement
embedded within another select statement
Multi-table queries: combines columns from
several tables (i.e., the join operation)
Chapter 9
Projection
SELECT SID, Name, Major
FROM STUDENT
Chapter 9
Selection
SELECT SID, Name, Major, GradeLevel, Age
FROM STUDENT
WHERE Major = ‘MATH’
Chapter 9
Sorting
SELECT Name, Major, Age
FROM STUDENT
WHERE Major = ‘ACCOUNTING’
ORDER BY Name
Chapter 9
Using Subquery with Equality
SELECT sno, fname, lname, position
FROM staff
WHERE bno =
(SELECT bno
FROM branch
WHERE street = ‘163 Main St.’)
Chapter 9
Nested Subqueries; use of IN
SELECT pno, street, area, city, type, rooms, rent
FROM property_for_rent
WHERE sno IN
(SELECT sno
FROM staff
WHERE bno =
(SELECT bno
FROM branch
WHERE street = ‘163 Main St.’) )
Using a Subquery with
an aggregate function
SELECT sno, fname, lname, position
FROM staff
WHERE salary >
(SELECT AVG(salary)
FROM staff)
Chapter 9
Using ALL with a Subquery
SELECT sno, fname, lname, position
FROM staff
WHERE salary > ALL
(SELECT salary
FROM staff
WHERE bno = ‘B3’)
Chapter 9
Chapter 9
Using EXISTS with a Subquery
SELECT EmployeeID, firstname, lastname, salary
FROM Employee
WHERE EmployeeID=[Enter Employee ID:] AND
Exists
(SELECT *
FROM Password
WHERE Password.EmployeeID =
[Enter Employee ID:] AND
Password = [Enter Password:]);
Sample Data
Figure 9-2
© 2000 Prentice Hall
Chapter 9
Joining with SQL
SELECT student.sid, student.name,
enrollment.classname
FROM student, enrollment
WHERE student.sid =
enrollment.studentnumber
Chapter 9
Using Aliases
SELECT S.sid, S.name, E.classname
FROM student S, enrollment E
WHERE S.sid = E.studentnumber
Chapter 9
Three Table Join
SELECT S.sid, S.name, E.classname, C.room
FROM student S, enrollment E, course C
WHERE S.sid = E.studentnumber AND
E.classname = C.name
Chapter 9
Action Queries
This type of query goes beyond simple
searches: it makes changes to the
data.
Insert: Insert data into a table
Update: Update data in a table
Delete: Delete data from a table
Chapter 9
Changing Data
• Inserting Data
INSERT INTO ENROLLMENT
VALUES (400, ‘BD445’, 44)
• Deleting Data
DELETE FROM STUDENT
WHERE STUDENT.SID = 100
• Modifying Data
UPDATE ENROLLMENT
SET PositionNumber = 44
WHERE SID = 400