Structured Query Language 結構化查詢語言

• 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 Table. Name SET Column 1 = New. Value 1, Column 2 = New. Value 2 WHERE Condition(s) The WHERE clause is optional.

Delete Records from a Table DELETE FROM Table. Name WHERE Condition(s) The WHERE clause is optional.

What is. S Q L? • Structured Query Language(SQL) (結構化查詢語言) Database Management System • Most ______________ (DBMS) 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 * / Column 1, Column 2, … FROM Table. Names

Retrieve data with specified selection criteria SELECT Column 1, Column 2, … FROM WHERE Table. Names 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 Column 1, Column 2, … FROM Table. Names 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 1 B girls. 1 B Girls ?

I General Structure List the names and ages (1 d. p. ) of 1 B girls. Condition for "1 B Girls": 1) class = "1 B" 2) sex = "F" 3) Both ( AND operator)

I General Structure List the names and ages (1 d. p. ) of 1 B girls. What is "age"?

I General Structure List the names and ages (1 d. p. ) of 1 B 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 1 B girls. SELECT class, sex, name, ROUND((SYSDATE-dob)/365, 1) AS "age" FROM stud WHERE class='1 B' 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 ( value 1, value 2, value 3) expr BETWEEN value 1 AND value 2 expr LIKE "%_"

II eg. 6 Comparison List the 1 A students whose Math test score is between 80 and 90 (incl. ) SELECT name, mtest FROM student ; WHERE class="1 A" AND ; mtest BETWEEN 80 AND 90 Result

II eg. 7 Comparison List the students whose names start with "T". SELECT name, class FROM student ; WHERE name LIKE "T%" Result

II eg. 8 Comparison List the Red house members whose names contain "a" as the 2 nd letter. SELECT name, class, hcode FROM student ; WHERE name LIKE "_a%" AND hcode="R" Result

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 1 A 1 A COUNT( ) 1 B 1 B 1 B COUNT( ) 1 B 1 C 1 C 1 C Student COUNT( )

III Grouping eg. 11 List the number of students of each class. SELECT class, COUNT(*) FROM student ; GROUP BY class Result

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

Group By Class class 1 A 1 A AVG( ) 1 B 1 B 1 B AVG( ) 1 B 1 C 1 C 1 C 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

III Grouping eg. 13 List the number of girls of each district. SELECT dcode, COUNT(*) FROM student ; WHERE sex="F" GROUP BY dcode Result

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

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

IV Display Order SELECT. . . FROM. . . WHERE. . . GROUP BY. . . ; ORDER BY colname ASC / DESC

IV Display Order eg. 16 List the boys of class 1 A, order by their names. SELECT name, id FROM student ; WHERE sex="M" AND class="1 A" ORDER BY name Result ORDER BY dcode

IV Display Order eg. 17 List the 2 A students by their residential district. SELECT name, id, class, dcode FROM student ; WHERE class="2 A" ORDER BY dcode Result

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

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 : : 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 width 4 10 1 2 contents student id number name sex: M / F class

3 Union, Intersection and Difference of Tables

3 eg. 22 Result Union, Intersection and Difference of Tables SELECT. . . FROM. . . WHERE. . . ; UNION ; SELECT. . . FROM. . . WHERE. . . 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 eg. 23 Result Union, Intersection and Difference of Tables SELECT. . . FROM table 1 ; WHERE col IN ( SELECT col FROM table 2 ) 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 table 1 ; WHERE col NOT IN ( SELECT col FROM table 2 ) 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