1 / 25

SQL Select-From-Where Statements Explained

Understand the syntax and usage of SELECT-FROM-WHERE statements in SQL. Learn how to query data from tables based on conditions and expressions, using SQL grammar like SELECT and GROUP BY. Explore search techniques and aggregate functions for querying databases effectively.

samuru
Télécharger la présentation

SQL Select-From-Where Statements Explained

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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.

More Related