Chapter 7 SQL HUANG XUEHUA

Chapter 7 SQL HUANG XUEHUA

SQL

Select-From-Where Statements SELECT desired attributes FROM one or more tables WHERE condition about tuples of the tables; For example: SELECT title, length FROM movies WHERE year =1994;

Select-From-Where Statements • A typical SQL query has the form:selectA1, A2, ..., Anfromr1, r2, ..., rmwhereC • Ais represent attributes • ris represent relations • C is a condition. • This query is equivalent to the relational algebra expression. A1, A2, ..., An(c (r1 x r2 x ... x rm)) • The result of an SQL query is a relation.

SELECT GRAMMER SELECT [ALL | DISTINCT] <column> [alias] [，<column> [alias] ]… FROM <table>[， <table>]… [WHERE <condition>] [GROUP BY <column1> [HAVING <condition> ]] [ORDER BY <column2 >[ASC | DESC]]；

SELECT GRAMMER 1. Column can have this pattern：（1）* （2）<table>.* （3）COUNT([ALL | DISTINCT] * ) （4）[<table>.]<column expressions> [alias] [， [<table>.]<column expressions> [alias]]… 2. WHERE condition expression is very flexible 3. GROUP BY: SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-clause and each subgroup of tuples consists of the set of tuples that have the same value for the grouping attribute(s) HAVING <condition> only that group satisfy the condition can output 4. [ORDER BY <column2 >[ASC | DESC]]：order

SELECT SEARCH • 1. search based on single table （1）choose some columns：some columns；all columns；computed columns （2）choose some rows：eliminate some rows；the tuples satisify the condition（比较大小，确定范围，确定集合，字符匹配，空值，多条件）；（3）make the result in order。（4）use the aggregate function。（5）group • 2. join search（等值与非等值连接；自身连接；外连接；复合条件连接） • 3. nest search（用IN子查询；用=；用ANY和ALL；用EXISTS）

Search the Student Database • Tables: student，course，sc • （1）student（sno，sname，ssex，sage,sdept）， • （2）Course (cno，cname，cpno，ccredit)， • （3）SC (sno，cno，grade)

STUDENT RELATION

SC RELATION

1) Search all the student’s information • 2) Find the name and the number of the boys who study in ‘IS’ department? Select * from student Select sno， sname from student where ssex=‘m’ and sdept=‘IS’;

3)Search the students who have chosen courses. Select sno from sc

Search the Tuples Satisfy Condition

Search the Tuples Satisfy Condition • 4)Search the name and sex of the student whose age is between 25 and 30 • 5)Search the student whose family name is’欧阳’ • 6)Search the student who is from ‘IS’,’MATH’,’CS’ • 7)Aggregate function

Use the Aggregate Function • Aggregate function includes： COUNT（[DISTINCT | ALL] *）the numbers of the tuples COUNT（[DISTINCT | ALL] <列名>）the numbers of the columns SUM（[DISTINCT | ALL] <列名>）the total value of a column AVG（[DISTINCT | ALL] <列名>）the average value of a column MAX（[DISTINCT | ALL] <列名>）the maximum value of a column MAX（[DISTINCT | ALL] <列名>）the minimum value of a column • 8). Search the total numbers of the students。 select count(*) from student; • 9). Search the numbers of the student who has chosen the courses select count(distinct sno) from sc;

10) Search the numbers of the students for each course. • 11)Search the index number of the student who has chosen more than 4 courses SELECT Cno, COUNT(Sno) FROM sc GROUP BY Cno; SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>4

Join • 12) Search the student’s information and include the courses they have chosen and the grade they have got

Join Search

Join Search • 12a: • 12b: Select student.*,sc.* From student,sc Where student.sno=sc.sno（等值连接）

Join Search • 13) Search the course name of each course’s direct prerequisites • 14)Search the student who have chosen the 2 course and the grade is large than 90

Summary • From this lecture you can learn the basic syntax of data definition language. • search • condition • Column expression • join

Any Questions? If there are any outstanding questions you can ask me one-to-one after the lecture OR privately in my office.

Exercises • Do the search exercises on the machine.

Chapter 7 SQL HUANG XUEHUA