第三章 关系数据库标准语言 SQL (2)
9/27
Basic Structure of an SQL Query (1)
select target-attribute-list from table-list where conditions Relational algebra correspondence: select-clause projection (π) from-clause where-clause Cartesian product (×) selection (σ)
Basic Structure of an SQL Query (1)
Relation schemas under consideration: Students (SSN, Name, GPA, Age) Courses (Course_no, Title, Dept_Name) Enrollment (SSN, Course_no, Grade)
Basic Structure of an SQL Query (2)
Find the SSN, Name and GPA of all students whose GPA is higher than 3.8. SQL> select SSN, Name, GPA 2 from Students 3 where GPA > 3.8;
Basic Structure of an SQL Query (3) To retrieve all attributes of a relation, use the shorthand * . Find all students whose GPA is higher than 3.8. select * from Students where GPA > 3.8 The where-clause may be absent. Find the Names of all students. select Name from Students
Basic Structure of an SQL Query (4) The select command does not remove duplicate rows in the result table. Duplicate rows can provide additional information. It may be costly to remove duplicate rows. To remove duplicate rows, select distinct should be used.
Basic Structure of an SQL Query (5)
Example: Find the Names of all students without duplicate rows. select distinct Name from Students select all can be used to explicitly request that all duplicate rows are kept. Questions: How select distinct is implemented Is the following query an efficient query select distinct SSN, Name from Students
Basic Structure of an SQL Query (6) Find the names and GPAs of all students who take database systems. select Name, GPA from Students, Enrollment, Courses where Title = `database systems' and Students.SSN = Enrollment.SSN and Enrollment.Course_no = Courses.Course_no
Basic Structure of an SQL Query (7)
The semantics of the previous query can be expressed (almost correctly!) as:
π Name, GPA (σ Title = `database systems' and Students.SSN

下一页