数据库系统概论

信息学院 2015 级,2017-2~6, 教 2221 数据库系统概论 孟小峰中国人民大学 xfmeng@ruc.edu.cn http://idke.ruc.edu.cn

数据库系统概论 An Introduction to Database Systems 第三章 SQL 语言 ( 之高级查询部分 ) 2017, 3, 21

Replay Time SELECT 语句结构 SELECT FROM WHERE 投影 卡氏积 选择 基本查询 SELECT all vs. SELECT distinct Computation in SELECT 连接查询, 外连接 集合查询, 交, 并, 差 嵌套查询,IN

嵌套查询 (1) Example: Find the names of all students who take at least one course offered by the CS department. select Name from Students s, Enrollment e where s.ssn = e.ssn and e.course_no in (select Course_no from Courses where Dept_Name = 'CS')

嵌套查询 (2) The previous query is a nested query. The query outside is an outer query and the query nested under the outer query is an inner query. Multiple nestings are allowed. The semantics of the above query is to evaluate the inner query first and evaluate the outer query last. Many nested queries have equivalent nonnested versions.

嵌套查询 (3) The previous query is equivalent to: (1) select Name from Students s, Enrollment e, Courses c where s.ssn = e.ssn and e.course_no = c.course_no and c.dept_name = 'CS (2) select Name from Students where SSN in (select SSN from Enrollment where Course_no in (select Course_no from Courses where Dept_Name = 'CS'))

嵌套查询 (4) Example: Consider the relations: Employees (SSN, Name, Age), Dependents (Name, Sex, ESSN) Find the names of all employees who has a dependent with the same name. select Name from Employees where Name in (select Name from Dependents where ESSN = SSN) Evaluation of correlated queries: for each tuple in the outer query, evaluate the inner query once.

嵌套查询 (5) Definition: If some attributes of a relation declared in the from clause of an outer query are referenced in the where clause of an inner query, then the two queries are said to be correlated, and the inner query is called a correlated inner query.

嵌套查询 (6) The scoping rule of attribute names: select... from R1,..., Rk where... (select... from S1,..., Sm where S1.A = R2.B and C = D and... ) (1) If attribute C is not an attribute in S1,..., Sm, and C is an attribute in some Ri, then C = Ri.C. (2) If C is in both Sj and Ri, then C = Sj.C.

嵌套查询 (7) In Oracle, in accepts multi-column list. Example: Find all enrollments that have 25 years old students taking CS courses. select * from Enrollment where (SSN, Course_no) in (select s.ssn, c.course_no from Students s, Courses c where s.age = 25 and c.dept_name = 'CS')

嵌套查询 (8) Example: Find the names of those students who are 18 or younger and whose GPA is higher than the GPA of some students who are 25 or older. select Name from Students where Age <= 18 and GPA >some (select GPA from Students where Age >= 25)

嵌套查询 (9) Other set comparison operators: <some, <=some, >=some, =some, <>some, >any, <any, <=any, >=any, =any, <>any, >all, <all, <=all, >=all, =all, <>all some and any have identical meaning.

嵌套查询 (10) =some is equivalent to in <>some is not equivalent to not in. <>all is equivalent to not in. Let x = a and S = {a, b}. Then x <>some S is true but x not in S is false. x <>all S is also false.

嵌套查询 -EXISTS exists ( ) is true if the set ( ) is not empty. exists ( ) is false if the set ( ) is empty. not exists ( ) is true if the set ( ) is empty. not exists ( ) is false if the set ( ) is not empty.

嵌套查询 -EXISTS(1) Example: Find all students who take at least one course. select * from Students s where SSN in (select * from Enrollment ) select * from Students s where exists (select * from Enrollment where SSN = s.ssn)

嵌套查询 -EXISTS(2) The previous query is equivalent (if redundancy is ignored) to: (1) select s.* from Students s, Enrollment e where s.ssn = e.ssn (2) select * from Students where SSN in (select SSN from Enrollment) Question: Which query is likely to have redundancy?

举例 Students Enrollment Result(1) s1 s1 c1 s1 s2 s1 c2 s1 s3 s2 c1 s2 s2 c3 s2 Result(2) s1 s2

嵌套查询 -EXISTS(3) Find all students who do not take CS532. select * from Students s where not exists (select * from Enrollment where SSN = s.ssn and Course_no = 'CS532') This query is equivalent to: select * from Students where SSN not in (select SSN from Enrollment where Course_no = 'CS532')

嵌套查询 -EXISTS(4) Find all the students who take all courses. Students (Enrollment Course): select * from Students s where not exists (select * from Courses c where not exists (select * from Enrollment where SSN = s.ssn and Course_no = c.course_no))

嵌套查询 -EXISTS(5) Students Enrollment Courses s1 s1 c1 c1 s2 s1 c2 c2 s1 c3 c3 s2 c1 s2 c3

NOT EXISTS 比较举例 查询选修了全部课程的学生号码和姓名 关系代数 元组关系演算 SQL (π Sno,Cno (SC) π Cno (Course)) π Sno,Sname (Student) RANGE OF c is Course RANGE OF sc is SC RANGE OF s is Student {XS.Sname, s.sno c sc (s.sno=sc.sno sc.cno=c.cno) } select Sno, Sname from Student s where not exists (select * from Couse c where not exists (select * from SC sc where s.sno = sc.sno and sc.cno = c.cno)) 74

嵌套查询 -EXISTS(4) Find all the students who take all courses. select * from Students s where not exists (select * from Courses c where not exists (select * from Enrollment where SSN = s.ssn and Course_no = c.course_no)) Interpretation: A student si takes all courses if and only if for this student, we can not find a course cj such that si does not take cj.

嵌套查询 -EXISTS(8) Find all the students who take all courses. Students (Enrollment Course): select * from Students s where not exists (select * from Courses c where not exists (select * from Enrollment where SSN = s.ssn and Course_no = c.course_no))

嵌套查询 -EXISTS(9) Example: Find the names and GPAs of those students who take all courses taken by a student with SSN = 123456789. select Name, GPA from Students s where not exists (select Course_no from Enrollment e1 where SSN = '123456789 and not exists (select * from Enrollment e2 where SSN = s.ssn and Course_no = e1.course_no))

嵌套查询 -EXISTS(10) Exercises: Consider relations: Employees (SSN, Name, Age, Salary) Projects (Proj_no, Name, Manager_name) Works (SSN, Proj_no, Start-Date) Find the names of those employees who participate in all projects. Find the names of those projects managed by Smith that are participated in by all employees under 40.

嵌套查询 -EXISTS(11) Find the names of those employees who participate all projects. select name from Employees e where not exists (select * from Projects p where not exists (select * from Works w where e.ssn = w.ssn and w.proj_no = p.proj_no))

嵌套查询 -EXISTS(12) Find the names of those projects managed by Smith that are participated by all employees under 40. m-name=smith Project (Works SSN ( age<40 Employees): select name from Projects p where manager_name = Smith and not exists (select * from Employees e where Age < 40 and not exists (select * from Works w where e.ssn = w.ssn and w.proj_no = p.proj_no))

Set (Aggregate) Functions (1) SQL supports five aggregate functions: Name Argum. type Result type Description avg numeric numeric average count any numeric count min char or num. same as arg minimum max char or num. same as arg maximum sum numeric numeric sum Oracle also supports stddev and variance.

Set (Aggregate) Functions (2) Example: Find the average, the minimum and the maximum GPAs among all students' GPAs. select avg(gpa), min(gpa), max(gpa) from Students Example: Find the number of students who are 17 years old. select count(*) from Students where Age = 17

Set (Aggregate) Functions (3) The last query is equivalent to select count(ssn) from Students where Age = 17 but may not be equivalent to select count(name) from Students where Age = 17 Set functions, except count(*), ignore null values.

Set (Aggregate) Functions (4) Example: Find the number of courses offered. select count(distinct Course_no) from Enrollment Note that the above is different from select distinct count(course_no) from Enrollment count(distinct *) is not allowed.

Set (Aggregate) Functions (5) Example: Find the SSNs and names of all students who take 5 or more courses. select SSN, Name from Students s where 5 <= (select count(*) from Enrollment where SSN = s.ssn)

Question Time Example: Find the names of all students who have the highest GPA. select Name from Students where GPA= (select max(gpa) from Students)

Set (Aggregate) Functions (6) GROUPING TUPLES Find the average GPA for students of different age groups. select Age, avg(gpa) from Students group by Age One tuple will be generated for each distinct value of age.

Set (Aggregate) Functions (7) STUDENTS SSN Name Age GPA 123456789 John 19 3.6 234567891 Tom 20 3.2 345678912 Tom 19 3.8 456789123 Bill 21 2.8 567891234 Mary 20 3.8

Set (Aggregate) Functions (7) STUDENTS RESULT SSN Name Age GPA Age avg(gpa) 123456789 John 19 3.6 19 3.7 345678912 Tom 19 3.8 20 3.5 234567891 Tom 20 3.2 21 2.8 567891234 Mary 20 3.8 456789123 Bill 21 2.8

Set (Aggregate) Functions (8) When group by is used, each attribute in the select clause must have a single atomic value for each group of common group by values. Each attribute in the select clause should be either a grouping attribute or an attribute on which a set function is applied. Every grouping attribute should be listed in the select clause.

Set (Aggregate) Functions (9) Example: The following is an illegal query: select Age, SSN, avg(gpa) from Students group by Age

Set (Aggregate) Functions STUDENTS RESULT SSN Name Age GPA Age SSN avg(gpa) 123456789 John 19 3.6 19? 3.7 234567891 Tom 20 3.2 20? 3.5 345678912 Tom 19 3.8 21? 2.8 456789123 Bill 21 2.8 567891234 Mary 20 3.8

Set (Aggregate) Functions (9) Example: The following is an illegal query: select SSN, Age, avg(gpa) from Students group by Age

Set (Aggregate) Functions (9) Example: The following is an illegal query: select SSN, Age, avg(gpa) from Students group by SSN, Age

Set (Aggregate) Functions (10) Example: Find the SSN, name and the number of credit hours each student still needs to graduate. Courses (Course_no, Title, Dept_Name, Credit_Hour) Enrollment (SSN, Course_no, Grade, Semester)

Set (Aggregate) Functions (11) Assume that each student needs 120 credit hours to graduate. select SSN, Name, 120 - sum(credit_hour) Credit-Needed from Students s, Enrollment e, Courses c where s.ssn = e.ssn and e.course_no = c.course_no group by s.ssn, Name

Set (Aggregate) Functions (12) Example: Find the number of students of each age group and output only those groups having more than 50 members. select Age, count(*) from Students group by Age having count(*) > 50 Conditions on set functions are specified in the having clause.

Set (Aggregate) Functions (12-1) Example: Find the number of students in department CS of each age group and output only those groups having more than 50 members. select Age, count(*) from Students where Dept_Name= CS and count(*) > 50 group by Age Wrong!!!!!!!! Conditions on set functions are specified in the having clause.

Set (Aggregate) Functions (12-2) Example: Find the number of students in department CS of each age group and output only those groups having more than 50 members. select Age, count(*) from Students where Dept_Name= CS group by Age having count(*) > 50 Conditions on set functions are specified in the having clause.

Set (Aggregate) Functions (13) Example: Find the SSNs and names of all students who take 5 or more courses. select SSN, Name from Students s where 5 <= (select count(*) from Enrollment where SSN = s.ssn) select SSN, Name from Students s, Enrollment e where e.ssn = s.ssn group by SSN, Name having count(*) >=5

Set (Aggregate) Functions (14) Aggregate functions can be nested in two levels. Example: Find the largest average student GPA among all departments. select max(avg(gpa)) from Students group by dept_name

Set (Aggregate) Functions (15) Aggregate functions can not be nested. select avg(gpa) from Students group by dept_name having avg(gpa) >=all (select avg(gpa) from Students group by dept_name )

Derived Relations (1) SQL-92 allows a subquery expression to be used in from clause. If such an expression is used, then the result relation must be given a name, and the attributes can be renamed ( select dept_name, avg(gpa) from Students group by dept_name ) as result (dept_name, avg-gpa)

Derived Relations(2) Example Select dept_name, avg-gpa From ( select dept_name, avg(gpa) from Students group by dept_name ) as result (dept_name, avg-gpa)

Derived Relations(3) 有了导出关系, having 子句可以省略. 我们可以在 from 子句计算临时关系 result Select dept_name From ( select dept_name, avg(gpa) from Students group by dept_name ) as result (dept_name, avg-gpa) Where avg-gpa> 3.0

Ordering Tuples in Output (1) Example: Find the names of all students and order the names in ascending order. select Name from Students order by Name asc ascending order is the default in order by clause.

Ordering Tuples in Output (2) Example: Find all the students whose GPA is higher than 3.5 and order the result in descending order by GPA, and for students having the same GPA, order them in ascending order by their names. select * from Students where GPA > 3.5 order by GPA desc, Name asc

Six Clauses of SQL Queries (1) select target-attributes from table-list where regular-conditions group by grouping-attributes having conditions-on-set-functions order by attribute-list Only the first two clauses are mandatory.

Six Clauses of SQL Queries (2) Evaluation of a six-clause query: 1. Form all combinations of tuples from the relations in the from clause (Cartesian product). 2. Among all the tuples generated in step 1, find those that satisfy the conditions in the where clause. 3. Group the remaining tuples based on the grouping attributes.

Six Clauses of SQL Queries (3) 4. Among all the tuples generated in step 3, find those that satisfy the conditions in the having clause. 5. Using the order by clause to order the tuples produced in step 4. 6. Project on the desired attribute values as specified in the select clause.

Six Clauses of SQL Queries (4) Find the average GPAs of students of different age groups. Only students younger than 35 are considered and only groups with the average GPAs higher than 3.2 are listed. The listing should be in ascending age values. select Age, avg(gpa) from Students where Age < 35 group by Age having avg(gpa) > 3.2 order by Age

Full Select Statement Syntax Select General Form: Subselect {union [all] Subselect} [order by result_column [asc desc] {, result_column [asc desc]}] Subselect General Form: select [all distinct] expression {, expression} from tablename [corr_name] {, tablename [corr_name]} [where search_condition] [group by column {, column}] [having search_condition_on_set_function]

An Interesting SQL Query (1) Example: Consider two tables: Agents(aid, aname, city, percent) Orders(ordno, month, cid, aid, pid, qty, dollars) Find the aid and total commission of each agent. An agent s total commission is computed based on his/her commission rate and total sale (in dollars).

An Interesting SQL Query (2) Solution 1: select a.aid, a.percent*sum(dollars)*0.01 commission from agents a, orders o where a.aid = o.aid group by a.aid, a.percent

An Interesting SQL Query (3) Solution 2: select a.aid, temp.total*percent*0.01 commission from (select aid, sum(dollars) total from orders group by aid) temp, agents a where a.aid = temp.aid A select query in the from clause is known as an inline view.

Top-N Query Top-N query finds the N records with the largest or smallest values under an attribute. Example: Find the three students with the highest GPA. (Top 3 students) select rownum, name, GPA from (select * from students order by GPA desc) where rownum <= 3 rownum is a pseudo-column associated with the table in the from clause.

Top-N Query Top-N query finds the N records with the largest or smallest values under an attribute. Example: Find the three students with the highest GPA. (Top 3 students) select * from students order by GPA desc LIMIT 0, 3

Dynamic Query Allow condition values to be provided on the fly SQL> select name, GPA from students 2 where dept_name = &cname and GPA <= &cgpa; Enter value for cname: CS Enter value for cgpa: 3.5 show result You can save the query in a file and run it in SQL*Plus.

The Expressive Power of SQL Query Language (1) Definition: A relational query language L is relationally complete if every query that can be expressed in the relational algebra can also be expressed in L. Theorem: SQL is relationally complete.

The Expressive Power of SQL Query Language (2) SQL is strictly more powerful than the relational algebra since SQL also supports set functions and the ordering of tuples. sum(), count(), avg() and order by cannot be expressed in the relational algebra.

The Expressive Power of SQL (3) SQL is considered to be a non-procedural language. Students.SSN, Name, Title (( Students.Dept-Name = `CS (Students) Enrollment) Enrollment.Course_no = Courses.Course_no Courses) select s.ssn, s.name, c.title from Students s, Enrollment e, Courses c where s.dept-name = 'CS' and s.ssn = e.ssn and e.course_no = c.course_no

The Expressive Power of SQL (4) SQL is less powerful than programming language. For example, SQL cannot handle recursive queries. Example: Consider Employees(SSN, Name, Salary, Manager-SSN). A recursive query: Find the names of all managers, direct or indirect, of John.

第三章习题 5, 补充习题 dbhw2 中的查询练习