Structured Query Language II 20211024 SQL query Grouping

Structured Query Language (II) (結構化查詢語言) 2021/10/24

SQL query – 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. 2021/10/24

Example Table: stud field id name dob sex class hcode dcode remission mtest type width numeric 4 character 10 date 8 character 1 character 2 character 1 character 3 logical 1 numeric 2 2021/10/24 contents student id number name date of birth sex: M / F class house code: R, Y, B, G district code fee remission (學費減免) Math test score

e. g. 1 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 2021/10/24 1 B 1 C 1 C 1 C Stud COUNT( )

Grouping eg. 1 List the number of students of each class. SELECT class, COUNT(*) FROM stud GROUP BY class Result 2021/10/24

Grouping e. g. 2 List the average Math test score of each class. SELECT class, Round(AVG(mtest), 2) FROM stud GROUP BY class Result 2021/10/24

Grouping e. g. 3 List the number of girls of each district. SELECT dcode, COUNT(*) FROM stud WHERE sex=‘F’ GROUP BY dcode Result 2021/10/24

Grouping e. g. 4 List the max. and min. test score of Form 1 students of each district. SELECT MAX(mtest), MIN(mtest), dcode FROM stud WHERE class LIKE ‘ 1_’ GROUP BY dcode Result 2021/10/24

Grouping List the average Math test score of the boys in each class. The list should not contain class with less than 3 boys. e. g. 5 SELECT AVG(mtest), class FROM stud WHERE sex=‘M’ GROUP BY class HAVING COUNT(*) >= 3 Result 2021/10/24

Grouping 2021/10/24

Multiple Tables – Join by Columns: u SQL provides a convenient operation to retrieve information from multiple tables. u This operation is called Join (接合). u The join operation will combine the tables into one large table by columns (欄). u An Inner Join is a join operation that joins two tables by their common column (共通欄. ) u It combines records from two tables only if the values of the joined fields meet a specified condition. u. The join operation is also called Equi-Join 2021/10/24 .

1. Inner Join (內接合) – Syntax SELECT a. comcol, a. col 1, b. col 2, expr 1, expr 2 ; FROM table 1 a, table 2 b ; WHERE a. comcol = b. comcol and filter. Condition 2021/10/24

1. Equi-Join / Inner Join Table : d_play_list_items 2021/10/24 Table : d_track_listings

Table: stud field id name dob sex class hcode dcode remission mtest type width numeric 4 character 10 date 8 character 1 character 2 character 1 character 3 logical 1 numeric 2 Table: music field id type 2021/10/24 type numeric character contents student id number name date of birth sex: M / F class house code: R, Y, B, G district code fee remission (學費減免) Math test score width 4 10 contents student id number type of the music instrument Join by The common field: id

eg. 1 Make a list of students and the instruments they learn. (Inner Join) id name class id Same id 9801 Join Student id name class 9801 Product 2021/10/24 type 9801 Music type

eg. 1 Make a list of students and the instruments they learn. (Inner Join) SELECT s. class, s. name, s. id, m. type FROM stud s, music m WHERE s. id=m. id ORDER BY class, name Result 2021/10/24

eg. 2 Find the number of students learning piano in each class. Three Parts : (1) Inner Join. (2) Condition: m. type="Piano" (3) GROUP BY class 2021/10/24

eg. 2 Find the number of students learning piano in each class. Join Student Condition m. type= "Piano" Product Music 2021/10/24 Group By class

eg. 2 Find the number of students learning piano in each class. SELECT s. class, COUNT(*) FROM stud s, music m WHERE s. id=m. id AND m. type=‘Piano’ GROUP BY class ORDER BY class Result 2021/10/24

Other Examples of Inner Join In a DBMS of a Musical Instrumental Co. , 3 tables are used: Salorder m Client 1 m Salstaff 2021/10/24 1

e. g 1 Display the names of the contact person and client, corresponding to the same order. (Two Tables) SELECT O. order_no, C. client, C. name FROM sal. Order O, Client C WHERE O. client_id =C. client_id Result 2021/10/24

e. g. 2 Display the sales orders made between May and June , with the corresponding salespersons in the same cursor (list by date). SELECT O. order_no, S. name as salesperson, O. order_date FROM sal. Order O, sal. Staff S WHERE O. staff_id=S. staff_id and order_date between '1 -5月-2006' and '30 -6月-2006' ORDER BY 3 Result 2021/10/24

e. g. 3 Display the names of the contact person , client and the salesperson corresponding to the same sales order. (3 Tables) SELECT O. order_no, C. client, C. name as contact, S. name as salesperson FROM sal. Order O, client C, sal. Staff S WHERE O. staff_id=S. staff_id and O. client_id=C. client_id From sal. Order From client Result 2021/10/24 From sal. Staff

2. Nonequi-Join SELECT d_packages. code, d_events. cost FROM d_packages, d_events WHERE d_events. cost BETWEEN d_packages. low_range AND d_packages. high_range 2021/10/24

3. Outer Join (外接合) u. An Outer Join is a join operation that selects all the rows from the first table including those match and those do not match the rows in the second table. u Those not matching join condition will be a null (-) value. 2021/10/24

3. Left Outer Join – Syntax SELECT a. comcol, a. col 1, b. col 2, expr 1, expr 2 ; FROM table 1 a LEFT OUTER JOIN table 2 b ; ON a. comcol = b. comcol WHERE filter. Condition ORDER BY … SELECT a. comcol, a. col 1, b. col 2, expr 1, expr 2 ; FROM table 1 a, table 2 b ; WHERE a. comcol = b. comcol (+) and filter. Condition ORDER BY … (+) sign 2021/10/24 indicates the table has missing data.

eg. Make a checking list of students and the instruments they learn. The list should also contain the students without an instrument. (Outer Join) Inner Join Outer Join No Match 2021/10/24

eg. Make a checking list of students and the instruments they learn. The list should also contain the students without an instrument. (Outer Join) SELECT s. class, s. name, s. id, m. type FROM stud s LEFT OUTER JOIN music m ON s. id=m. id ORDER BY 1, 2 Result SELECT s. class, s. name, s. id, m. type FROM stud s, music m where s. id=m. id(+) order by 1, 2 2021/10/24 empty Outer Join

3. Outer Join Null value Select e. employee_id, e. last_name, d. department_name From employees e, departments d Where e. department_id=d. department_id(+) 2021/10/24

3. Outer Join (Right Outer Join) Select e. last_name, e. department_id, d. department_name From employees e, departments d Where e. department_id (+) =d. department_id 2021/10/24 The right outer join would return all department IDs and department names even if no employees were assigned to them.

3. Outer Join (Full Outer Join) The results set of a full outer join includes all rows in both tables even if there is no match in the other table. Remember, in this form of an outer join, it was not possible to put a (+) on both sides of the WHERE clause. 2021/10/24

4. Cartesian Product / Cross Join 1. Select * from stud 2. Select * from music 3. Select * from stud, music 4. Select * from stud cross join music How many records before and after the join? 2021/10/24

5. SELF JOIN Table : Employees Select worker. last_name ||' works for '|| manager. last_name From employees worker, employees manager Where worker. manager_id = 2021/10/24 manager. employee_id

6. NATURAL JOIN A natural join is based on all columns in the two tables that have the same name and selects rows from the two tables that have equal values in all matched columns. It is possible to join the tables without having to explicitly specify the columns in the corresponding table. However, the names and data types in both columns must be the same. Table : d_play_list_items Table : d_track_listings SELECT event_id, song_id, cd_number FROM d_play_list_items NATURAL JOIN d_track_listings WHERE event_id = 105 2021/10/24

JOIN (USING Clause) 2021/10/24

JOIN (ON Clause) 2021/10/24

Subqueries There are two types of subqueries: -Single-row subqueries that use single-row operators (>, =, >=, < <>, <=) and return only one row from the inner query e. g. SELECT name from stud where mtest <= (select avg(mtest) from stud) -Multiple-row subqueries that use multiple-row operators (IN, ANY, ALL) 2021/10/24 and return more than one row from the inner query

Subqueries - IN SELECT title, year FROM d_cds WHERE year IN (SELECT year FROM d_cds WHERE cd_number < 94) IN (= ANY) NOT IN (<> ALL) 2021/10/24

Subqueries - ANY SELECT title, producer, year FROM d_cds WHERE year = ANY (SELECT year FROM d_cds WHERE producer = 'The Music Man') 2021/10/24

Subqueries - ALL SELECT title, producer, year FROM d_cds WHERE year > ALL (SELECT year FROM d_cds WHERE producer = 'The Music Man') 2021/10/24

Tutorial Exercise Time 2021/10/24

The End 2021/10/24

Lesson Review Two Tables with same structure: Table A and Table B What is Union in Multiple Tables? The union (數據庫聯合) of A and B (A B) A table containing all the rows from A and B. A 2021/10/24 B

Real Case: Bridge Club & Chess Club Consider the members of the Bridge Club and the Chess Club. The two database files have the same structure: List members of either clubs order by class, name 2021/10/24 Union

Lesson Review Two Tables with same structure: Table A and Table B What is Intersection in Multiple Tables? The intersection (數據庫相交) of A and B (A B) A table containing only rows that appear in both A and B. A 2021/10/24 B

Real Case: Bridge Club & Chess Club Consider the members of the Bridge Club and the Chess Club. The two database files have the same structure: Members of both clubs 2021/10/24 Intersection

Lesson Review Two Tables with same structure: Table A and Table B What is Difference in Multiple Tables? The difference (數據庫差分) of A and B (A–B) A table containing rows that appear in A but not in B. A 2021/10/24 B

Real Case: Bridge Club & Chess Club Consider the members of the Bridge Club and the Chess Club. The two database files have the same structure: Members of Bridge Club but Not Chess Club 2021/10/24 Difference

Lesson Review Syntax of SQL 1. Union SELECT. . . FROM. . . WHERE. . . ; UNION ; SELECT. . . FROM. . . WHERE. . . 2021/10/24

Lesson Review Syntax of SQL 2. Intersection SELECT. . . FROM table 1 ; WHERE col IN ( SELECT col FROM table 2 ) Note: col stands for one of the common column in the two tables 2021/10/24

Lesson Review Syntax of SQL 3. Difference SELECT. . . FROM table 1 ; WHERE col NOT IN ( SELECT col FROM table 2 ) Note: col stands for one of the common column in the two tables 2021/10/24

This Lesson eg. 9 List the students who have not yet chosen an instrument. (No match) id 9801 Student 2021/10/24 id name class type No match Music

eg. 9 List the students who have not yet chosen an instrument. No match (Difference) SELECT class, name, id FROM student ; WHERE id NOT IN ( SELECT id FROM music ) ; ORDER BY class, name Result 2021/10/24