Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 7 SQL HUANG XUEHUA PowerPoint Presentation
Download Presentation
Chapter 7 SQL HUANG XUEHUA

Chapter 7 SQL HUANG XUEHUA

75 Views Download Presentation
Download Presentation

Chapter 7 SQL HUANG XUEHUA

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Chapter 7 SQL HUANG XUEHUA

  2. SQL

  3. 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;

  4. 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.

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

  6. 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

  7. 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)

  8. 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)

  9. STUDENT RELATION

  10. SC RELATION

  11. 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’;

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

  13. Search the Tuples Satisfy Condition

  14. 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

  15. 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;

  16. 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

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

  18. Join Search

  19. Join Search

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

  21. 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

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

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

  24. Exercises • Do the search exercises on the machine.