Transcript Oracle1

Structured Query Language
(結構化查詢語言)
Database Programming
Lesson Review:
•
•
•
•
•
•
•
CREATE TABLE …
ALTER TABLE… ADD/DROP/MODIFY (…)
DESC …
DROP TABLE …
RENAME … TO …
INSERT INTO … VALUES (…)
SELECT * FROM …
Updating Data in a Table
UPDATE TableName
SET Column1 = NewValue1,
Column2 = NewValue2
WHERE Condition(s)
The WHERE clause is optional.
Delete Records from a Table
DELETE FROM TableName
WHERE Condition(s)
The WHERE clause is optional.
What is S Q L?
• Structured Query Language(SQL)
(結構化查詢語言)
Database Management System (DBMS)
• Most ___________________________
support SQL. e.g. Oracle
• When a user wants to get some information
from a database file, he can issue a _______.
query
• A query is a user–request to retrieve data or
information with a certain ___________.
condition
Basic structure of an SQL query
SELECT * / Column1, Column2, …
FROM
TableNames
Retrieve data with specified selection criteria
SELECT Column1, Column2, …
FROM
WHERE
TableNames
Conditions
Operator
Description
=
Equal to
<> or != or ^=
Not Equal to
>
Greater/ Larger than
<
Less/ Smaller than
>=
Greater or equal to
<=
Less or equal to
BETWEEN
Within the range
LIKE
Match the pattern
Comparison operators Examples
•
•
•
•
WHERE event_date = ' 01-JAN-04'
WHERE rental_fee >=2000
WHERE cd_title = ' White Rose'
You may also see the <> (not equal to) symbol
written as != or ^ =
In the example shown from the DJ on Demand
database, which rows will be selected? Will
salaries of 3000 be included in the results set?
SELECT last_name, salary
FROM employees
WHERE salary <= 3000
Aliases – Column Headings
table
Concatenation – Link columns
Retrieve value(s) without Duplication
SELECT DISTINCT Column1, Column2, …
FROM
TableNames
WHERE
Conditions
Comparison Operators
BETWEEN…AND
SELECT title, year
FROM d_cds
WHERE year BETWEEN '1999' AND '2001‘
IN
SELECT title, type_code
FROM d_songs
WHERE type_code IN ( 77, 12 )
LIKE
• The % symbol is used to represent any sequence of
zero or more characters. The underscore (_ )
symbol is used to represent a single character.
In the example shown below, all employees with last names beginning
with any letter followed by an "o" and then followed by any other
number of letters will be returned.
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%'
Which of the following last names could have been returned from the
above query?
1. Sommersmith
2. Oog
3. Fong
4. Mo
If you said 1, 2, 3, and 4, you're correct!
IS NULL, IS NOT NULL
The IS NULL condition tests for unavailable, unassigned, or
unknown data. IS NOT NULL tests for data that is present in the
database. In this example, the WHERE clause is written to retrieve
all the last names and manager IDs of those employees who do not
have a manager.
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL
Read the following and explain what you expect will be returned:
SELECT first_name, last_name, auth_expense_amt
FROM d_partners
WHERE auth_expense_amt IS NOT NULL
LOGICAL CONDITIONS
Logical conditions combine the result of two component
conditions to produce a single result based on them. For
example, to attend a rock concert, you need to buy a ticket AND
have transportation to get there. If both conditions are met, you
go to the concert.
AND
In the query below, the results returned will be rows that satisfy
BOTH conditions specified in the WHERE clause.
SELECT id, title, duration, type_code
FROM d_songs
WHERE id > 40
AND type_code = 77
OR
If the WHERE clause uses the OR condition, the
results returned from a query will be rows that
satisfy either one of the OR conditions. In other
words, all rows returned have an ID greater than
40 OR they have a type_code equal to 77.
SELECT id, title, duration, type_code
FROM d_songs
WHERE id > 40
OR type_code = 77
NOT
SELECT title, type_code
FROM d_songs
WHERE type_code NOT IN 77
ORDER BY
SELECT title, year
FROM d_cds
ORDER BY year
e.g. ROUND(45.926, 2) -> 45.93
TRUNC (45.926, 2) -> 45.92
MOD( 1600 / 300) -> 100
Tutorial Exercise Example:
Database (stud.dbf)
I
General Structure
List the names and ages (1 d.p.) of 1B girls.
1B Girls ?
I
General Structure
List the names and ages (1 d.p.) of 1B girls.
Condition for "1B Girls":
1) class = "1B"
2) sex = "F"
3) Both ( AND operator)
I
General Structure
List the names and ages (1 d.p.) of 1B girls.
What is "age"?
I
General Structure
List the names and ages (1 d.p.) of 1B girls.
Functions:
# days : SYSDATE – dob
# years :(SYSDATE – dob) / 365
1 d.p.: ROUND(__ , 1)
I
General Structure
List the names and ages (1 d.p.) of 1B girls.
SELECT class, sex, name,
ROUND((SYSDATE-dob)/365,1) AS "age"
FROM stud WHERE class='1B' AND
sex='F'
Tutorial Exercise Time
SELECT last_name, specialty, auth_expense_amt
FROM d_partners
WHERE specialty ='All Types'
OR specialty IS NULL
AND auth_expense_amt = 300000
SELECT last_name, specialty, auth_expense_amt
FROM d_partners
WHERE (specialty ='All Types'
OR specialty IS NULL)
AND auth_expense_amt = 300000
II
Comparison
expr IN ( value1, value2, value3)
expr BETWEEN value1 AND value2
expr LIKE "%_"
II
eg. 6
Comparison
List the 1A students whose Math test score is
between 80 and 90 (incl.)
SELECT name, mtest FROM student ;
WHERE class="1A" AND ;
mtest BETWEEN 80 AND 90
Result
name
Luke
Aaron
Gigi
mtest
86
83
84
II
eg. 7
Comparison
List the students whose names start with "T".
SELECT name, class FROM student ;
WHERE name LIKE "T%"
Result
name
Tobe
Teddy
Tim
class
1B
1B
2A
II
eg. 8
Comparison
List the Red house members whose names contain
"a" as the 2nd letter.
SELECT name, class, hcode FROM student ;
WHERE name LIKE "_a%" AND hcode="R"
Result
name
Aaron
Janet
Paula
class
1A
1B
2A
hcode
R
R
R
III Grouping
SELECT ...... FROM ...... WHERE condition ;
GROUP BY groupexpr [HAVING requirement]
Group functions:
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
– groupexpr specifies the related rows to be grouped
as one entry. Usually it is a column.
– WHERE condition specifies the condition of
individual rows before the rows are group.
HAVING requirement specifies the condition
involving the whole group.
III Grouping
eg. 11
List the number of students of each class.
Group By Class
class
1A
1A
1A
1A
COUNT( )
1B
1B
1B
1B
1B
COUNT( )
1B
1B
1C
1C
1C
1C
Student
COUNT( )
III Grouping
eg. 11
List the number of students of each class.
SELECT class, COUNT(*) FROM student ;
GROUP BY class
Result
class
1A
1B
1C
2A
2B
2C
cnt
10
9
9
8
8
6
III Grouping
eg. 12
List the average Math test score of each class.
Group By Class
class
1A
1A
1A
1A
AVG( )
1B
1B
1B
1B
1B
AVG( )
1B
1B
1C
1C
1C
1C
Student
AVG( )
III Grouping
eg. 12
List the average Math test score of each class.
SELECT class, AVG(mtest) FROM student ;
GROUP BY class
Result
class
1A
1B
1C
2A
2B
2C
avg_mtest
85.90
70.33
37.89
89.38
53.13
32.67
III Grouping
eg. 13
List the number of girls of each district.
SELECT dcode, COUNT(*) FROM student ;
WHERE sex="F" GROUP BY dcode
Result
dcode
HHM
KWC
MKK
SSP
TST
YMT
cnt
6
1
1
5
4
8
III Grouping
eg. 14
List the max. and min. test score of Form 1
students of each district.
SELECT MAX(mtest), MIN(mtest), dcode ;
FROM student ;
WHERE class LIKE "1_" GROUP BY dcode
Result
max_mtest min_mtest dcode
92
36
HHM
91
19
MKK
91
31
SSP
92
36
TST
75
75
TSW
88
38
YMT
III Grouping
eg. 15
List the average Math test score of the boys in
each class. The list should not contain class with
less than 3 boys.
SELECT AVG(mtest), class FROM student ;
WHERE sex="M" GROUP BY class ;
HAVING COUNT(*) >= 3
Result
avg_mtest class
86.00
1A
77.75
1B
35.60
1C
86.50
2A
56.50
2B
IV Display Order
SELECT ...... FROM ...... WHERE ......
GROUP BY ..... ;
ORDER BY colname ASC / DESC
IV Display Order
eg. 16
List the boys of class 1A, order by their names.
SELECT name, id FROM student ;
WHERE sex="M" AND class="1A" ORDER BY name
name
Peter
Johnny
Luke
Bobby
Aaron
Ron
id
9801
9803
9810
9811
9812
9813
Result
ORDER BY
dcode
name
Aaron
Bobby
Johnny
Luke
Peter
Ron
id
9812
9811
9803
9810
9801
9813
IV Display Order
eg. 17
List the 2A students by their residential district.
SELECT name, id, class, dcode FROM student ;
WHERE class="2A" ORDER BY dcode
Result
name
Jimmy
Tim
Samual
Rosa
Helen
Joseph
Paula
Susan
id
9712
9713
9714
9703
9702
9715
9701
9704
class
2A
2A
2A
2A
2A
2A
2A
2A
dcode
HHM
HHM
SHT
SSP
TST
TSW
YMT
YMT
IV Display Order
eg. 18
List the number of students of each district
(in desc. order).
SELECT COUNT(*) AS cnt, dcode FROM student ;
GROUP BY dcode ORDER BY cnt DESC
Result
cnt
11
10
10
9
5
2
1
1
1
docode
YMT
HHM
SSP
MKK
TST
TSW
KWC
MMK
SHT
IV Display Order
eg. 19
List the boys of each house order by the
classes. (2-level ordering)
SELECT name, class, hcode FROM student ;
WHERE sex="M" ORDER BY hcode, class
IV Display Order
Result
Blue
House
Order
by
hcode
Green
House
:
:
name
Bobby
Teddy
Joseph
Zion
Leslie
Johnny
Luke
Kevin
George
:
hcode
B
B
B
B
B
G
G
G
G
:
class
1A
1B
2A
2B
2C
1A
1A
1C
1C
:
Order
by
class
3
Union, Intersection and
Difference of Tables
The union of A and B (AB)
A
B
A table containing all the rows from A and B.
3
Union, Intersection and
Difference of Tables
The intersection of A and B (AB)
A
B
A table containing only rows that appear in both A and B.
3
Union, Intersection and
Difference of Tables
The difference of A and B (A–B)
A
B
A table containing rows that appear in A but not in B.
3
The Situation:
Bridge Club & Chess Club
Consider the members of the Bridge Club and
the Chess Club. The two database files have
the same structure:
field
id
name
sex
class
type
numeric
character
character
character
width
4
10
1
2
contents
student id number
name
sex: M / F
class
3
Union, Intersection and
Difference of Tables
Bridge [A]
id
name
1
2
3
4
5
9812
9801
9814
9806
9818
:
Aaron
Peter
Kenny
Kitty
Edmond
:
sex
class
M
M
M
F
M
:
1A
1A
1B
1B
1C
:
Chess [B]
id
name
1
2
3
4
5
9802
9801
9815
9814
9817
:
Mary
Peter
Eddy
Kenny
George
:
sex
class
F
M
M
M
M
:
1A
1A
1B
1B
1C
:
3
Union, Intersection and
Difference of Tables
SELECT ...... FROM ...... WHERE ...... ;
UNION ;
SELECT ...... FROM ...... WHERE ......
eg. 22
Result
The two clubs want to hold a joint party.
Make a list of all students. (Union)
SELECT * FROM bridge ;
UNION ;
SELECT * FROM chess ;
ORDER BY class, name INTO TABLE party
3
Union, Intersection and
Difference of Tables
SELECT ...... FROM table1 ;
WHERE col IN ( SELECT col FROM table2 )
eg. 23
Result
Print a list of students who are members of both
clubs. (Intersection)
SELECT * FROM bridge ;
WHERE id IN ( SELECT id FROM chess ) ;
TO PRINTER
3
Union, Intersection and
Difference of Tables
SELECT ...... FROM table1 ;
WHERE col NOT IN ( SELECT col FROM table2 )
eg. 24
Result
Make a list of students who are members of the
Bridge Club but not Chess Club. (Difference)
SELECT * FROM bridge ;
WHERE id NOT IN ( SELECT id FROM chess ) ;
INTO TABLE diff