Transcript Document

Database Application
SAK 3408
Chapter 3
Query Language
What is SQL?
SQL or Structured Query Language is an English-like
language used to create and manipulate databases.
SQL is an ANSI (American National Standards Institute)
standard for accessing database systems. SQL statements
are used to retrieve and update data in a database.
SQL works with database programs like MS Access, DB2,
Informix, MS SQL Server, Oracle, Sybase, etc.
With SQL, we can query a database and have a
result set returned.
Types of Statements
Three types of statements:
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Data Definition Language creates and manipulates
the structure, delete or define indexes of table in
database
Data Manipulation Language manipulates data in the
table
Data Control Language determines who is allowed to
what within the database
SQL Grammar
SQL statements are always terminated by
a semi-colon (;)
SELECT CUSTOMER_ID
FROM CUSTOMER;
SQL Grammar
SQL statements can be entered on a
single line or split across multiple lines
(preferred)
SELECT CUSTOMER_ID, CUSTOMER_NAME, CREDIT_LINE
FROM CUSTOMER WHERE CREDIT_LINE > 1000000
AND CUSTOMER_ID IN [1000,2000];
SELECT CUSTOMER_ID, CUSTOMER_NAME, CREDIT_LINE
FROM CUSTOMER
WHERE CREDIT_LINE > 1000000 AND
CUSTOMER_ID IN [1000,2000];
SQL Grammar
SQL statements are not case sensitive;
however data is.
SeLEct *
fROm REQueST
wHErE aCcT_sT In [‘GA’,’WA’,’NJ’];
Returns data for all requests where the state is either GA, WA
or NJ. No records would have been returned if the where
clause had been:
wHErE aCcT_sT In [‘ga’,’wA’,’Nj’];
Qualifying a Field Name
When the same field name occurs in
two or more tables used in a single SQL
statement, you must qualify the field
name much like you would include the
last name to avoid confusion if two
people have the same first name.
Tablename.fieldname
Customer.last_name
Data Definition Language
(DDL)
Used to create and modify database
objects
Create
Drop
Alter
Data Manipulation Language
(DML)
Used to create, modify and retrieve data
Insert
Select
Update
Delete
Data Control Language
(DCL)
Used to control database privileges
Grant
Revoke
DDL - Creating a Table
Use the Create keyword and specify:
table name
field (column) name(s)
field type
constraints
primary key - unique identifier for a record
foreign key - establishes relationship
check - value must be in the specified list
not null - must have a value
unique - value must be unique
Table with Primary Key Only
CREATE TABLE room(
roomID
number,
bldg
char(1) CHECK (bldg IN ('A','B')),
roomNo
varchar2(10),
maxCapacity
number,
stylevarchar2(15) CHECK (style IN
('LECTURE','LECTURE/LAB','LAB','OFFICE')),
CONSTRAINT room_pk PRIMARY KEY (roomID));
Constraint_name
Constraint_type
Constraint_attributes
Table with Foreign Key
CREATE TABLE faculty(
facultyID
number,
lname
varchar2(30) NOT NULL,
fname
varchar2(20) NOT NULL,
dept
varchar2(5),
officeID
number,
phone
varchar2(15),
email
varchar2(75) UNIQUE,
rank
char(4) CHECK (rank IN ('INST',
'ASOC','ASST','FULL','SENR')),
CONSTRAINT faculty_pk PRIMARY KEY (facultyID),
CONSTRAINT faculty_fk FOREIGN KEY (officeID)
REFERENCES room(roomID));
Table with Compound Primary Key
create table participation(
eventID
number,
memberID number,
constraint participation_pk primary key
(eventID, memberID),
constraint participation_event_fk foreign key
(eventID) references event(eventID),
constraint participation_member_fk foreign key
(memberID) references member(memberID));
DDL - Altering a Table
Use the Alter and Add/Modify keywords
and specify:
table name
new or existing field name(s)
field type
alter table invoice
add(sent_dt date);
alter table invoice
modify (invoice_nbr varchar2(5));
DDL - Removing a Table
Use the Drop keyword and specify:
table name
drop table invoice;
If this table is referenced by a foreign
key, use the cascade constraints
clause
drop table invoice cascade constraints;
Indexes
Conceptually similar to book index
Increases data retrieval efficiency
Automatically assigns record numbers
Used by DBMS, not by users
Fields on which index built called Index Key
Have a sorted order
Can guarantee uniqueness
Can include one or more fields
Indexes – Syntax
CREATE UNIQUE INDEX cust_num_ind ON
customer(Name);
CREATE INDEX CustomerName ON Customer
(CustomerNum);
CREATE INDEX CreditLimitRep_ind ON
Customer(CreditLimit, RepNum);
DROP INDEX RepBal;
Customer Table with Record Numbers
Figure 4.10
Customer Table Index on CustomerNum
Figure 4.11
Table Indexes on CreditLimit, RepNum
Figure 4.12
Indexes
PROs
Faster/more efficient data retrieval
CONs
Requires additional space
Increased overhead
Data Manipulation Language
(DML)
Used to create, modify and retrieve data
Insert
Select
Update
Delete
DML - Adding Data to a Table
Use the Insert keyword and specify:
table name
field names - optional
values for each field
insert into customer
values(‘Teplow’,’MA’,23445.67);
OR
insert into customer (last_name,
state_cd, sales) values (‘Teplow’
,’MA’,23445.67);
DML - Updating Data in a Table
Use the Update and Set keywords and
specify:
table name
field name(s)
where clause (optional)
update inventory
set price = price*1.05;
update inventory
set price = price*1.05
where product_id = 'P103';
DML-Deleting Records
Use the Delete From keywords and
specify:
table name
where clause (optional)
delete from customer;
delete from customer
where sales < 10000;
Parts of a DML Select
Statement
Select
From
Where (optional)
Order By (optional)
Group By (optional)
Having (optional)
SELECT
Which fields do you want retrieved?
* is used to select all fields in the table
FROM
Which table(s) are these fields in?
Two Sample Tables
customer table
last_name
Teplow
Abbey
Porter
Martin
Laursen
Bambi
McGraw
state_cd sales
MA
23445.67
CA
6969.96
CA
6989.99
CA
2345.45
CA
34.34
CA
1234.55
NJ
123.45
state table
state_name
Massachusetts
California
New Jersey
state_cd
MA
CA
NJ
Select - Simplest
Show everything in a single table
SELECT *
FROM customer;
Returns
LAST_NAME
----------------Teplow
Abbey
Porter
Martin
Laursen
Bambi
McGraw
STATE_CD
-------MA
CA
CA
CA
CA
CA
NJ
SALES
---------------23445.67
6969.96
6989.99
2345.45
34.34
1234.55
123.45
Select Statement - Simple
SELECT last_name, state_cd
FROM customer;
Returns
LAST_NAME
----------------Teplow
Abbey
Porter
Martin
Laursen
Bambi
McGraw
STATE_CD
------MA
CA
CA
CA
CA
CA
NJ
WHERE - Optional
Use to:
specify how to join two tables
restrict the data returned
SQL Operators
Logical Operators
and
or
Comparison Operators
=
!= or <>
Like
In
Between
equality
inequality
string search
list of values
range of values
Select Statement - Equals
SELECT *
FROM customer
WHERE state_cd = ‘CA’;
Returns
LAST_NAME
----------------Abbey
Porter
Martin
Laursen
Bambi
ST
-CA
CA
CA
CA
CA
SALES
--------6969.96
6989.99
2345.45
34.34
1234.55
Select Statement – Not Equals
SELECT *
FROM customer
WHERE state_cd <> ‘CA’;
Returns
LAST_NAME
-------------Teplow
McGraw
ST
-MA
NJ
SALES
---------23445.67
123.44
Select Statement - Like
SELECT last_name, state_cd, sales
FROM customer
WHERE upper(state_cd) LIKE ‘%A’;
Returns
LAST_NAME
--------------Teplow
Abbey
Porter
Martin
Laursen
Bambi
ST
-MA
CA
CA
CA
CA
CA
SALES
---------------23445.67
6969.96
6989.99
2345.45
34.34
1234.55
Select Statement - In
SELECT last_name, state_cd
FROM customer
WHERE state_cd IN (’MA’,’NJ’);
Returns
LAST_NAME
------------Teplow
McGraw
ST
-MA
NJ
Select Statement – Between
SELECT *
FROM customer
WHERE sales BETWEEN 6500 AND 25000;
Returns
LAST_NAME
-------------Teplow
Abbey
Porter
ST
-MA
CA
CA
SALES
---------23445.67
6969.96
6989.99
Select Statement – Multiple
Conditions Using OR
SELECT *
FROM customer
WHERE state_cd <> ‘CA’ OR
sales BETWEEN 6500 AND 25000;
Returns
LAST_NAME
-------------Teplow
Abbey
Porter
McGraw
ST
-MA
CA
CA
NJ
SALES
---------23445.67
6969.96
6989.99
123.44
Select Statement – Multiple
Conditions Using AND
SELECT *
FROM customer
WHERE state_cd <> ‘CA’ AND
sales BETWEEN 6500 AND 25000;
Returns
LAST_NAME
-------------Teplow
ST
-MA
SALES
---------23445.67
Select Statement – Calculated
Field
SELECT last_name, sales, sales*05 as Tax
FROM customer;
Returns
LAST_NAME
----------Teplow
Abbey
Porter
Martin
Laursen
Bambi
SALES
---------23445.67
6969.96
6989.99
2345.45
34.34
1234.55
TAX
-------1172.28
348.50
349.50
117.27
1.71
61.73
ORDER BY - Optional
Used to specify sorting order
Can sort
by multiple fields
in ascending or descending order
Select Statement - Sorting
select state_name, last_name, sales
from customer, state
where customer.state_cd = state.state_cd
and state_cd in (‘CA’,’MA’,’NJ’)
order by state_name;
Returns
STATE_NAME
------------------California
California
Massachusetts
New Jersey
LAST_NAME
------------------------Abbey
Porter
Teplow
McGraw
SALES
-------6969.96
6989.99
23445.67
123.45
Overview
Select Statements continued
Table Joins
Distinct
Group By
Having
Decode function
Sequence numbers
Subqueries
Insert
Update
Delete
Select - Table Join
SELECT state_name, last_name, sales
FROM customer, state
WHERE customer.state_cd = state.state_cd;
Returns
STATE_NAME
-------------California
California
Massachusetts
New Jersey
LAST_NAME
----------------Abbey
Porter
Teplow
McGraw
SALES
-------6969.96
6989.99
23445.67
123.45
Table Joins
Process of combining data from two or
more tables, normally using primary
and/or foreign keys.
Basic types of joins:
Equijoin (Equal or Inner Join)
Left join (Outer Join)
Right join
Equi Joins
Most common type of join
Look for records which have matching
values of the join fields
Join Processing
Table joins are done by matching the first
record from the primary table’s first record
with each record in the secondary table, then
matching the second record in the primary
table with each record in the secondary
table.
Continue until each record from the primary
table has been combined with each record
from the secondary table. This is called a
Cartesian product.
Join Processing - cont
Oracle then goes through the Cartesian
product, discarding combined records
that have non-matching join fields.
The remaining records are returned.
Cartesian Product Example
Select ename, emp.deptno dept.deptno, dname
FROM emp, dept
ename
Smith
Smith
Smith
Smith
Allen
Allen
Allen
Allen
Ward
Ward
Ward
Ward
emp.deptno
20
20
20
20
30
30
30
30
30
30
30
30
dept.deptno
10
20
30
40
10
20
30
40
10
20
30
40
dname
Accounting
Research
Sales
Operations
Accounting
Research
Sales
Operations
Accounting
Research
Sales
Operations
Note: This example is only showing a few fields from the first
three employees but all fields and all records are used to
create the Cartesian product. The Cartesian product has 56
rows (14 employees * 4 depts)
Cartesian Product Example cont
ename
emp.deptno
dept.deptno dname
Smith
Smith
Smith
Smith
Allen
Allen
Allen
Allen
Ward
Ward
Ward
Ward
20
20
20
20
30
30
30
30
30
30
30
30
10
20
30
40
10
20
30
40
10
20
30
40
Accounting
Research
Sales
Operations
Accounting
Research
Sales
Operations
Accounting
Research
Sales
Operations
Cartesian Product Example
cont.
ename
emp.deptno dept.deptno dname
Smith
20
20
Research
Allen
Ward
30
30
30
30
Sales
Sales
Left Joins
Used when you may not have matching
data in the secondary table, but still
want to include the data you have in
the primary table.
NOTE: The primary table is normally listed on the left
side of the equation, the secondary on the right side.
Left Join Example
Create a brand new dept (deptno=50),
but there are no employees in that dept
yet
Select dname, dept.deptno, emp.deptno, ename
FROM dept, emp
WHERE + dept.deptno = emp.deptno;
dname
Research
Sales
Sales
Finance
dept.deptno emp.deptno ename
20
30
30
50
20 Smith
30 Allen
30 Ward
Right Joins
Not as common as left joins. Used
when you may have data in the
secondary table with no matching data
in the primary table.
Right Join Example
Assign an employee to a brand new dept,
deptno=50, but there’s no record in dept yet
Select ename, dept.deptno deptno, dname
FROM emp, dept
WHERE + emp.deptno = dept.deptno;
ename
Smith
Allen
emp.deptno dept.deptno dname
20
20
Research
30
30
Sales
Ward
Wilson
30
50
30
Sales
Advanced Select - Distinct
Used to omit duplicate values in a select
statement
Select distinct(student_id)
From course_section
Where term = '1001';
DISTINCT – example
SELECT Category
FROM Animal;
Category
Fish
Dog
Fish
Cat
Cat
Dog
Fish
Dog
Bird
Dog
Fish
Cat
Dog
SELECT DISTINCT Category
FROM Animal;
Category
Bird
Cat
Dog
Fish
Advanced Select- Group By
Clause
Allow you to group data together for
summary calculations
avg (column)
count (*)
max (column)
min (column)
sum (column)
Advanced Select- Group By
Clause
Group by clause must be included in a
select statement that uses a group
function (count, sum, min, max, and avg)
All fields in the select clause which are
not part of a group function must be
included in the Group By clause.
Advanced Select- Group By
Clause
avg (column)
1 select deptno, avg(sal)
2 from scott.emp
3* group by deptno;
DEPTNO AVG(SAL)
-------------- ---------------10
2916.6667
20
2175
30
1566.6667
Advanced Select - Having Clause
Allows you to specify conditions for a record set
instead of a single record
Must have a group by clause
1
2
3
4*
select deptno, avg(sal)
from scott.emp
group by deptno
having avg(sal) > 2000
DEPTNO AVG(SAL)
------------- ---------------10
2916.6667
20
2175
Where vs Having
Where - a constraint on individual
records.
Having - a constraint on a group
of records.
They can be used together in the
same SQL statement
Select ename
From emp
Where upper(loc) = 'NEW YORK'
Group by deptno
Having avg(sal) > 2000
Subquery Characteristics
Can be used in the SELECT, CREATE,
INSERT, UPDATE and DELETE
statements
Can be used as part of a condition in
the WHERE clause
Can be used in multiple AND or OR
predicates of the same SQL statement
Subquery Characteristics - Cont
Is enclosed in parenthesis and must
appear on the right in a WHERE clause
condition
May or may not retrieve data from a
table used in the main, or outer, SQL
statement in which it’s embedded.
Cannot include an ORDER BY clause
Subquery Characteristics - Cont
The number of rows returned by the
subquery must match the number
expected by the main query
The number of columns returned must
also match the number expected
Combining Subqueries
Multiple subqueries can be used to
check for more than one condition in a
statement.
Same or different types can be nested.
NOTE: Nested subqueries are executed from the most
deeply nested to the least deeply nested subquery.
Subquery - Example
Show all customers who have placed an order
SELECT CUSTOMER_NAME FROM CUSTOMER_T
WHERE CUSTOMER_ID IN
(SELECT DISTINCT CUSTOMER_ID FROM ORDER_T);
Subquery is embedded in
parentheses. In this case it
returns a list that will be used
in the WHERE clause of the
outer query
Subquery for Calculation
Which cats sold for more than the average sale price of cats?
Assume we know the average price is $170.
Usually we need to compute it first.
SELECT SaleAnimal.AnimalID, Animal.Category,
SaleAnimal.SalePrice
FROM Animal
INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE ((Animal.Category="Cat") AND (SaleAnimal.SalePrice>170));
SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice
FROM Animal
INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE ((Animal.Category="Cat") AND (SaleAnimal.SalePrice>
( SELECT AVG(SalePrice)
FROM Animal
INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE (Animal.Category=“Cat”)
)
) );
Using IN with a Sub-query
List all customers who bought items for cats.
SELECT Customer.LastName, Customer.FirstName, SaleItem.ItemID
FROM (Customer
INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID)
INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID
WHERE (SaleItem.ItemID In
(SELECT ItemID FROM Merchandise WHERE
Category="Cat")
);
Using NOT IN with a Sub-query
Which animals have not been sold?
Start with list of all animals.
Subtract out list of those who were sold.
SELECT Animal.AnimalID, Animal.Name,
Animal.Category
FROM Animal
WHERE (Animal.AnimalID Not In
(SELECT AnimalID From SaleAnimal));
Subqueries vs Table Joins
Always use subqueries instead of table
joins when possible. Subqueries are
significantly less resource intensive.
Table joins are only required when the
Select clause contains fields from
multiple tables.
UNION, INTERSECT, EXCEPT
A
T1
B
List the name of any employee
who has worked for both the
East and West regions.
C
T2
T1 UNION T2
T1 INTERSECT T2
T1 EXCEPT T2
A+B+C
B
A
SELECT EID, Name
FROM EmployeeEast
INTERSECT
SELECT EID, Name
FROM EmployeeWest
UNION Operator
SELECT EID, Name, Phone, Salary, ‘East’ AS Office
FROM EmployeeEast
UNION
SELECT EID, Name, Phone, Salary, ‘West’ AS Office
FROM EmployeeWest
EID
352
876
372
Name
Jones
Inez
Stoiko
Phone
3352
8736
7632
Salary
45,000
47,000
38,000
Office
East
East
East
890
361
Smythe
Kim
9803
7736
62,000
73,000
West
West
Offices in Los Angeles and New York.
Each has an Employee table (East and West).
Need to search data from both tables.
Columns in the two SELECT lines must match.
Reflexive Join
SQL
SELECT Employee.EID,
Employee.Name,
Employee.Manager, E2.Name
FROM Employee INNER JOIN
Employee AS E2
Employee
EID
115
462
523
765
Name
...
Sanchez
Miller
Hawk
Munoz
Manager
765
115
115
886
Result
ON Employee.Manager = E2.EID
EID
115
462
523
Name
Sanchez
Miller
Hawk
Manager
765
115
115
Name
Munoz
Sanchez
Sanchez
Need to connect a table to itself.
Common example: Employee(EID, Name, . . ., Manager)
A manager is also an employee.
Use a second copy of the table and an alias.
CASE Function
Not available in Microsoft Access. It is in SQL Server and Oracle.
Select AnimalID,
CASE
WHEN Date()-DateBorn < 90 Then “Baby”
WHEN Date()-DateBorn >= 90
AND Date()-DateBorn < 270 Then “Young”
WHEN Date()-DateBorn >= 270
AND Date()-DateBorn < 365 Then “Grown”
ELSE “Experienced”
END
FROM Animal;
Used to change data to a different context.
Example: Define age categories for the animals.
Less than 3 months
Between 3 months and 9 months
Between 9 months and 1 year
Over 1 year
Inequality Join
AccountsReceivable
Categorize by Days Late
30, 90, 120+
Three queries?
New table for business rules
AR(TransactionID, CustomerID, Amount, DateDue)
LateCategory(Category, MinDays, MaxDays, Charge, …)
Month
Quarter
Overdue
30
90
120
90
120
9999
3%
5%
10%
SELECT *
FROM AR INNER JOIN LateCategory
ON ((Date() - AR.DateDue) >= LateCategory.MinDays)
AND ((Date() - AR.DateDue) < LateCategory.MaxDays)