Transcript chap03

David M. Kroenke
3
Database Concepts 1e
Chapter 3
Structured Query
Language
© 2002 by Prentice Hall
1
Chapter Objectives
• Learn basic SQL statements for creating
database constructs
• Learn basic SQL SELECT statements and
options for processing a single table
• Learn basic SQL SELECT statements for
processing multiple tables with subqueries
• Learn basic SQL SELECT statements for
processing multiple tables with joins
• Learn SQL statements to add, modify, and
delete data
© 2002 by Prentice Hall
2
Structured Query Language
• Structured Query Language is known as
either:
– Its acronym, SQL, or
– SEQUEL, the name of the original version
of SQL
• SEQUEL was developed by IBM in the mid1970s.
© 2002 by Prentice Hall
3
SQL Defined
• SQL is not a programming language,
but rather a data sub-language
• SQL has several functions
– Data Definition
– Data Retrieval (Queries)
– Data Manipulation/Updates
– And others (not covered in this chapter)
• Data control
• Transaction control
© 2002 by Prentice Hall
4
SQL for Data Definition
• The SQL data definition statements
include
– CREATE
• To create database objects
– ALTER
• To modify the structure and/or characteristics
of database objects
– DROP
• To delete database objects
© 2002 by Prentice Hall
5
SQL for Data Definition: CREATE
• Creating database tables
– The SQL CREATE TABLE statement
CREATE TABLE Employee (
EmpID Integer Not Null,
EmpName Char(25));
© 2002 by Prentice Hall
6
Primary Key Constraint: ALTER
•
Adding primary key constraints to an
existing table
– The SQL ALTER statement
ALTER TABLE Employee
ADD CONSTRAINT EmpPK PRIMARY KEY(EmpID);
© 2002 by Prentice Hall
7
Composite Primary Key Constraints:
ALTER
• The SQL ALTER statement may also be used
to create a composite primary key constraint
CREATE TABLE Empl_Skill (
EmpID Integer Not Null,
SkillID Integer Not Null,
SkillLevel Integer);
ALTER TABLE Empl_Skill
ADD CONSTRAINT EmpSkillPK
PRIMARY KEY(EmpID, SkillID);
© 2002 by Prentice Hall
8
Foreign Key Constraint: ALTER
• Adding foreign key constraints to an
existing table
– The SQL ALTER statement
ALTER TABLE Employee
ADD CONSTRAINT EmpFK FOREIGN KEY(DeptID)
REFERENCES Dept;
© 2002 by Prentice Hall
9
Defining the Cascade Rules: ALTER
• Using the SQL ALTER statement to
apply cascading rules
ALTER TABLE Employee
ADD CONSTRAINT EmpFK FOREIGN KEY(DeptID)
REFERENCES Dept ON DELETE CASCADE;
© 2002 by Prentice Hall
10
Deleting Database Objects: DROP
• To remove unwanted database objects
from the database, use the SQL DROP
statement
• Warning… The DROP statement will
permanently remove the object and all
data
DROP TABLE Employee;
© 2002 by Prentice Hall
11
Removing a Constraint: ALTER &
DROP
• To change the constraints on existing
tables, you may need to remove the
existing constraints before adding new
constraints
ALTER TABLE Employee DROP CONSTRAINT
EmpFK;
© 2002 by Prentice Hall
12
SQL for Data Retrieval (Queries)
• The most well known SQL statement…
SELECT
• SELECT will retrieve information from
the database that matches the specified
criteria
SELECT EmpName FROM Emp;
© 2002 by Prentice Hall
13
The Results of a Query is a Relation
• A query pulls information from several
relations and creates (temporarily) a
new relation
• This allows for a query to:
– Create a new relation
– Feed information to another query (subqueries)
© 2002 by Prentice Hall
14
Showing a Row Only Once:
DISTINCT
• A qualifier may be added to the SELECT
statement to inhibit duplicate rows from
displaying
SELECT DISTINCT DeptID from Emp;
© 2002 by Prentice Hall
15
Specifying Search Criteria: WHERE
• The WHERE clause stipulates the
matching criteria for the record that are
to be displayed
SELECT EmpName FROM Emp
WHERE DeptID = 15;
© 2002 by Prentice Hall
16
Match Criteria
• The WHERE clause match criteria may
include
– Equals “=“
– Not Equals “<>”
– Greater than “>”
– Less than “<“
– Greater than or Equal to “>=“
– Less than or Equal to “<=“
© 2002 by Prentice Hall
17
Match Operators
• Multiple matching criteria may be
specified using
– AND
• Representing an intersection of the data sets
– OR
• Representing a union of the data sets
© 2002 by Prentice Hall
18
Operator Examples
SELECT EmpName FROM Emp
WHERE DeptID < 7 OR
DeptID > 12;
SELECT EmpName FROM Emp
WHERE DeptID = 9 AND
SalaryCode <= 23;
© 2002 by Prentice Hall
19
A List of Values
• The WHERE clause may specify that a
particular column value must be included in a
list of values
SELECT EmpName FROM Emp
WHERE DeptID IN (4, 8, 9);
© 2002 by Prentice Hall
20
The Logical NOT Operator
• Any criteria statement may be preceded
by a NOT operator which is to say that
all information will be shown except
that information matching the specified
criteria
SELECT EmpName FROM Emp
WHERE DeptID NOT IN (4, 8, 9);
© 2002 by Prentice Hall
21
Finding Data Matching a Range of
Values: BETWEEN
• SQL provides a BETWEEN statement that
allows a user to specify a minimum and
maximum value on one line
SELECT EmpName FROM Emp
WHERE SalaryCode BETWEEN 10 AND 45;
© 2002 by Prentice Hall
22
Allowing for Wildcard Searches: LIKE
• Sometimes it may be advantageous to
find rows matching a string value using
wildcards
– Single character wildcard character is an
underscore (_)
– Multiple character wildcard character is a
percent sign (%)
© 2002 by Prentice Hall
23
Wildcard Search Examples
SELECT EmpID FROM Emp
WHERE EmpName LIKE ‘Kr%’;
SELECT EmpID FROM Emp
WHERE Phone LIKE ‘616-___-____’;
© 2002 by Prentice Hall
24
Displaying All Columns: *
• To show all of the column values for the
rows that match the specified criteria,
use an *
SELECT * FROM Emp
WHERE EmpName LIKE ‘%land’;
© 2002 by Prentice Hall
25
Sorting the Results: ORDER BY
• The results may be sorted using the
ORDER BY clause
SELECT * FROM Emp
ORDER BY EmpName;
© 2002 by Prentice Hall
26
Built-in SQL Functions
• SQL provides several built-in functions
– COUNT
• Counts the number of rows that match the
specified criteria
– MIN
• Finds the minimum value for a specific column
for those rows matching the criteria
– MAX
• Finds the maximum value for a specific column
for those rows matching the criteria
© 2002 by Prentice Hall
27
Built-in SQL Functions (continued)
• SUM
– Calculates the sum for a specific column
for those rows matching the criteria
• AVG
– Calculates the numerical average of a
specific column for those rows matching
the criteria
© 2002 by Prentice Hall
28
Built-in Function Examples
SELECT COUNT(DISTINCT DeptID) FROM Emp;
SELECT MIN(Hours), MAX(Hours), AVG(Hours)
FROM Project
WHERE ProjID > 7;
© 2002 by Prentice Hall
29
Providing Subtotals: GROUP BY
• Subtotals may be calculated by using
the GROUP BY clause
SELECT DeptID, COUNT(*), FROM Emp
GROUP BY DeptID
HAVING Count(*) > 3;
© 2002 by Prentice Hall
30
Retrieving Information from Multiple
Tables
• SubQueries
– As stated earlier, the result of a query is a relation.
As a result, a query may feed another query. This
is called a subquery
• Joins
– Another way of combining data is by using a Join
• Equi-Join
• Left Outer Join
• Right Outer Join
© 2002 by Prentice Hall
31
Subquery
SELECT EmpName FROM Emp
WHERE DeptID in
(SELECT DeptID from Department
WHERE DeptName LIKE ‘Accounts%’);
© 2002 by Prentice Hall
32
Equi-Join
SELECT EmpName FROM Emp, Department
WHERE Emp.DeptID = Department.DeptID AND
Department.DeptName LIKE ‘Account%’;
© 2002 by Prentice Hall
33
Outer Join
• Right Outer Join
SELECT EmpName FROM Department RIGHT JOIN
Emp
WHERE Emp.DeptID = Department.DeptID AND
Department.DeptName LIKE ‘Account%’;
• Left Outer Join
SELECT EmpName FROM Emp LEFT JOIN
Department
WHERE Emp.DeptID = Department.DeptID AND
Department.DeptName LIKE ‘Account%’;
© 2002 by Prentice Hall
34
Modifying Data using SQL
• Insert
– Will add a new row in a table
• Update
– Will update the data in a table that
matches the specified criteria
• Delete
– Will delete the data in a table that matches
the specified criteria
© 2002 by Prentice Hall
35
Adding Data: INSERT
• To add a row to an existing table, use
the INSERT statement
INSERT INTO Emp VALUES (91, ‘Smither’, 12);
INSERT INTO Emp (EmpID, SalaryCode)
VALUES (62, 11);
© 2002 by Prentice Hall
36
Changing Data Values: UPDATE
• To change the data values in an existing row
(or set of rows) use the Update statement
UPDATE Emp
SET Phone ‘791-555-1234’
WHERE EmpID = 29;
UPDATE Emp
SET DeptID = 44
WHERE EmpName LIKE ‘Kr%’;
© 2002 by Prentice Hall
37
Deleting Data: DELETE
• To delete a row or set of rows from a
table using the DELETE statement
DELETE FROM Emp
WHERE EmpID = 29;
DELETE FROM Emp
WHERE EmpName LIKE ‘Kr%’;
© 2002 by Prentice Hall
38
David M. Kroenke
3
Database Concepts 1e
Chapter 3
Structured Query
Language
© 2002 by Prentice Hall
39