Transcript Select sno from sc

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:
select A1, A2, ..., An
from r1, r2, ..., rm
where C
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 SELECTclause 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
Select * from student

2) Find the name and the number of the boys who
study in ‘IS’ department?
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
condition
operators
comparision
=，>，<，>=，<=，!=，<>，!<，!>， NOT+上述
符号
scope
BETWEEN AND， NOT BETWEEN AND
sets
IN，NOT IN
Letter match
LIKE， NOT LIKE
Null values
IS NULL， IS NOT NULL
Multconditions
AND，OR
(%， _ )
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.
SELECT Cno, COUNT(Sno)
FROM sc
GROUP BY Cno;

11)Search the index number of the student who
has chosen more than 4 courses
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
Join Search

12a:
Select student.*,sc.*
From student,sc
Where student.sno=sc.sno（等值连接）

12b:
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.