Structured Query Language

(結構化查詢語言 )

Database Programming

• CREATE TABLE …

• ALTER TABLE… ADD/DROP/MODIFY (…)

• DESC …

• DROP TABLE …

• RENAME … TO …

• INSERT INTO … VALUES (…)

• SELECT * FROM …

Lesson Review:

Updating Data in a Table

UPDATE TableNameSET Column1 = NewValue1, Column2 = NewValue2WHERE Condition(s)

The WHERE clause is optional.

Delete Records from a Table

DELETE FROM TableNameWHERE Condition(s)

The WHERE clause is optional.

What is S Q L?

• When a user wants to get some information from a database file, he can issue a _______.

• A query is a user–request to retrieve data or information with a certain ___________.

• Structured Query Language(SQL)

(結構化查詢語言 )• Most ___________________________ (DBMS)

support SQL. e.g. Oracle

query

condition

Database Management System

Basic structure of an SQL queryBasic structure of an SQL query

SELECT * / Column1, Column2, …

FROM TableNames

Retrieve data with specified selection criteria

SELECT Column1, Column2, …

FROM TableNames

WHERE 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, salaryFROM employeesWHERE salary <= 3000

table

Aliases – Column Headings

Concatenation – Link columns

Retrieve value(s) without DuplicationRetrieve value(s) without Duplication

SELECT DISTINCT Column1, Column2, …

FROM TableNames

WHERE Conditions

SELECT title, yearFROM d_cdsWHERE year BETWEEN '1999' AND '2001‘

Comparison Operators

BETWEEN…AND

SELECT title, type_codeFROM d_songsWHERE type_code IN ( 77, 12 )

IN

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_nameFROM employeesWHERE last_name LIKE '_o%'

Which of the following last names could have been returned from the above query?1. Sommersmith2. Oog3. Fong4. 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_idFROM employeesWHERE 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_partnersWHERE auth_expense_amt IS NOT NULL

LOGICAL CONDITIONSLogical 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.

ANDIn the query below, the results returned will be rows that satisfy BOTH conditions specified in the WHERE clause.

SELECT id, title, duration, type_codeFROM d_songsWHERE id > 40AND 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_codeFROM d_songsWHERE id > 40OR type_code = 77

SELECT title, type_codeFROM d_songsWHERE type_code NOT IN 77

NOT

SELECT title, yearFROM d_cdsORDER BY year

ORDER BY

e.g. ROUND(45.926, 2) -> 45.93 TRUNC (45.926, 2) -> 45.92 MOD( 1600 / 300) -> 100

Tutorial Exercise Example:Database (stud.dbf)Tutorial Exercise Example:Database (stud.dbf)

General StructureGeneral StructureII List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.

11B Girls ?B Girls ?

Condition for "1B Condition for "1B Girls":Girls":

1)1) class = class = "1B""1B"

2)2) sex = sex = "F""F"

3)3) Both ( AND operator)Both ( AND operator)

General StructureGeneral StructureIIList the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.

General StructureGeneral StructureII List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.

What is "age"?What is "age"?

Functions:Functions:

# days :# days : SYSDATE – dobSYSDATE – dob

# years :(SYSDATE – dob) / 365# years :(SYSDATE – dob) / 365

1 d.p.:1 d.p.: ROUND(__ , 1)ROUND(__ , 1)

General StructureGeneral StructureII List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.

List the names and ages (1 d.p.) of 1B girls.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'

General StructureGeneral StructureII

Tutorial Exercise TimeTutorial Exercise Time

SELECT last_name, specialty, auth_expense_amtFROM d_partnersWHERE specialty ='All Types'OR specialty IS NULLAND auth_expense_amt = 300000

SELECT last_name, specialty, auth_expense_amtFROM d_partnersWHERE (specialty ='All Types'OR specialty IS NULL)AND auth_expense_amt = 300000

ComparisonComparisonIIIIexprexpr IN ( IN ( value1value1, , value2value2, , value3value3))

exprexpr BETWEEN BETWEEN value1value1 AND AND value2value2

exprexpr LIKE "%_" LIKE "%_"

ComparisonComparisonIIIIeg. 6eg. 6 List the 1A students whose Math test score is List the 1A students whose Math test score is

between 80 and 90 (incl.)between 80 and 90 (incl.)

SELECT name, mtest FROM student ;

WHERE class="1A" AND ;

mtest BETWEEN 80 AND 90

name mtestLuke 86Aaron 83Gigi 84

Result

ComparisonComparisonIIIIeg. 7eg. 7 List the students whose names start with "T".List the students whose names start with "T".

SELECT name, class FROM student ;

WHERE name LIKE "T%"

name classTobe 1BTeddy 1BTim 2A

Result

ComparisonComparisonIIII eg. 8eg. 8 List the Red house members whose names contain List the Red house members whose names contain "a" as "a" as

the 2nd letter.the 2nd letter.

SELECT name, class, hcode FROM student ;

WHERE name LIKE "_a%" AND hcode="R"

name class hcodeAaron 1A RJanet 1B RPaula 2A R

Result

GroupingGroupingIIIIIISELECT ...... FROM ...... WHERE SELECT ...... FROM ...... WHERE conditioncondition ; ;GROUP BY GROUP BY groupexprgroupexpr [HAVING [HAVING requirementrequirement]]

Group functions:Group functions:

COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )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.

GroupingGroupingIIIIII eg. 11eg. 11 List the number of students of each class.List the number of students of each class.

COUNT( )COUNT( )

Group By ClassGroup By Class

11AA

COUNT( )COUNT( )11BB

COUNT( )COUNT( )11CC

11AA

11BB

11CCStudentStudent

class11AA

11AA

11AA

11BB

11BB

11BB

11BB

11BB

11BB

11CC

11CC

11CC

GroupingGroupingIIIIIISELECT class, COUNT(*) FROM student ;

GROUP BY class

class cnt1A 101B 91C 92A 82B 82C 6

eg. 11eg. 11 List the number of students of each class.List the number of students of each class.

Result

GroupingGroupingIIIIII eg. 12eg. 12 List the average Math test score of each class.List the average Math test score of each class.

Group By ClassGroup By Class

AVG( )AVG( )

AVG( )AVG( )

AVG( )AVG( )

11AA

11BB

11CCStudentStudent

class11AA

11AA

11AA

11BB

11BB

11BB

11BB

11BB

11BB

11CC

11CC

11CC

GroupingGroupingIIIIII eg. 12eg. 12 List the average Math test score of each class.List the average Math test score of each class.

SELECT class, AVG(mtest) FROM student ;GROUP BY class

class avg_mtest1A 85.901B 70.331C 37.892A 89.382B 53.132C 32.67

Result

GroupingGroupingIIIIII eg. 13eg. 13 List the number of girls of each district.List the number of girls of each district.

SELECT dcode, COUNT(*) FROM student ;

WHERE sex="F" GROUP BY dcode

dcode cntHHM 6KWC 1MKK 1SSP 5TST 4YMT 8

Result

GroupingGroupingIIIIII eg. 14eg. 14 List the max. and min. test score of Form 1 List the max. and min. test score of Form 1

students of each district.students of each district.

SELECT MAX(mtest), MIN(mtest), dcode ;

FROM student ;

WHERE class LIKE "1_" GROUP BY dcode

max_mtest min_mtest dcode92 36 HHM91 19 MKK91 31 SSP92 36 TST75 75 TSW88 38 YMT

Result

GroupingGroupingIIIIII eg. 15eg. 15 List the average Math test score of the boys in List the average Math test score of the boys in each each

class. The list should not contain class with class. The list should not contain class with less than 3 boys.less than 3 boys.

SELECT AVG(mtest), class FROM student ;

WHERE sex="M" GROUP BY class ;

HAVING COUNT(*) >= 3

avg_mtest class86.00 1A77.75 1B35.60 1C86.50 2A56.50 2B

Result

Display OrderDisplay OrderIVIVSELECT ...... FROM ...... WHERE ...... SELECT ...... FROM ...... WHERE ......

GROUP BY ..... ;GROUP BY ..... ;

ORDER BY ORDER BY colnamecolname ASC / DESC ASC / DESC

Display OrderDisplay OrderIVIVSELECT name, id FROM student ;

WHERE sex="M" AND class="1A" ORDER BY name

eg. 16eg. 16 List the boys of class 1A, order by their names.List the boys of class 1A, order by their names.

name idPeter 9801Johnny 9803Luke 9810Bobby 9811Aaron 9812Ron 9813

ORDER BY

dcode

name idAaron 9812Bobby 9811Johnny 9803Luke 9810Peter 9801Ron 9813

Result

Display OrderDisplay OrderIVIVSELECT name, id, class, dcode FROM

student ;

WHERE class="2A" ORDER BY dcode

eg. 17eg. 17 List the 2A students by their residential district.List the 2A students by their residential district.

name id class dcodeJimmy 9712 2A HHMTim 9713 2A HHMSamual 9714 2A SHTRosa 9703 2A SSPHelen 9702 2A TSTJoseph 9715 2A TSWPaula 9701 2A YMTSusan 9704 2A YMT

Result

Display OrderDisplay OrderIVIV

SELECT COUNT(*) AS cnt, dcode FROM student ;

GROUP BY dcode ORDER BY cnt DESC

eg. 18eg. 18 List the number of students of each districtList the number of students of each district

(in desc. order).(in desc. order).

cnt docode11 YMT10 HHM10 SSP9 MKK5 TST2 TSW1 KWC1 MMK1 SHT

Result

Display OrderDisplay OrderIVIV

SELECT name, class, hcode FROM student ;

WHERE sex="M" ORDER BY hcode, class

eg. 19eg. 19 List the boys of each house order by theList the boys of each house order by the classes. (2-level ordering)classes. (2-level ordering)

Display OrderDisplay OrderIVIVname hcode classBobby B 1ATeddy B 1BJoseph B 2AZion B 2BLeslie B 2CJohnny G 1ALuke G 1AKevin G 1CGeorge G 1C: : :

Result

Order by class

BlueHouse

GreenHouse

::

Order by hcode

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

A B

The union of A and B (AB)

A table containing all the rows from A and B.

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

The intersection of A and B (AB)

A table containing only rows that appear in both A and B.

A B

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

The difference of A and B (A–B)

A table containing rows that appear in A but not in B.

A B

33Consider the members of the Bridge Club and Consider the members of the Bridge Club and the Chess Club. The two database files have the Chess Club. The two database files have the same structure:the same structure:

The Situation: Bridge Club & Chess ClubThe Situation: Bridge Club & Chess Club

field type width contentsid numeric 4 student id numbername character 10 namesex character 1 sex: M / Fclass character 2 class

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

Bridge [A] Chess [B]

id name sex class id name sex class

1 9812 Aaron M 1A 1 9802 Mary F 1A

2 9801 Peter M 1A 2 9801 Peter M 1A

3 9814 Kenny M 1B 3 9815 Eddy M 1B

4 9806 Kitty F 1B 4 9814 Kenny M 1B

5 9818 Edmond M 1C 5 9817 George M 1C: : : : : : : :

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

SELECT * FROM bridge ;

UNION ;

SELECT * FROM chess ;

ORDER BY class, name INTO TABLE party

eg. 22eg. 22 The two clubs want to hold a joint party.The two clubs want to hold a joint party. Make Make a list of all students. (Union)a list of all students. (Union)

SELECT ...... FROM ...... WHERE ...... ;SELECT ...... FROM ...... WHERE ...... ;

UNION ;UNION ;

SELECT ...... FROM ...... WHERE ......SELECT ...... FROM ...... WHERE ......

Result

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

SELECT * FROM bridge ;

WHERE id IN ( SELECT id FROM chess ) ;

TO PRINTER

eg. 23eg. 23 Print a list of students who are members of both Print a list of students who are members of both clubs. clubs. (Intersection)(Intersection)

SELECT ...... FROM SELECT ...... FROM table1table1 ; ;

WHERE WHERE colcol IN ( SELECT IN ( SELECT colcol FROM FROM table2table2 ) )

Result

Union, Intersection and Union, Intersection and Difference of TablesDifference of Tables33

SELECT * FROM bridge ;

WHERE id NOT IN ( SELECT id FROM chess ) ;

INTO TABLE diff

eg. 24eg. 24 Make a list of students who are members of the Make a list of students who are members of the Bridge Bridge Club but not Chess Club. (Difference)Club but not Chess Club. (Difference)

SELECT ...... FROM SELECT ...... FROM table1table1 ; ;

WHERE WHERE colcol NOT IN ( SELECT NOT IN ( SELECT colcol FROM FROM table2table2 ) )

Result