SQL查詢語句複習題

SQL查詢語句複習題

建立學生-課程資料庫的三個表:

學生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno為主碼;

課程表:Course(Cno,Cname,Cpno,Credeit) Cno為主碼;

學生選修表:SC(Sno,Cno,Grade) Sno,Cno,為主碼;

Student

學號

Sno姓名

Sname性別

Ssex年齡

Sage所在系

Sdept

95001李勇男20CS

95002劉晨女19IS

95003王敏女18MA

95004張立男19IS

課程號

Sno課程名

Cname先行課

Cpno學分

Credit

1資料庫54

2數學2

3資訊系統14

4作業系統63

5資料結構74

6資料處理2

7Pascal語言64

Course:

SC:

學號

Sno課程號

Cno成績

Grade

95001192

95001285

95001388

95002290

95002380

一:查詢表中的列和行

1:查詢全體學生的學與姓名

sele sno,sname from student

2:查詢全體學生的姓名、學號、所在系。

sele sno,sname,sdept from student

3:查詢全體學生的詳細記錄

sele * from student

4:查詢全體學生的姓名及出生年份

sele sno,sage from student

5:查詢全體學生的姓名,出生年份及所在系,要用小寫字母表示系名

6:查詢選修了課程的學生學號

sele sno,cno from sc

7:查詢選修了課程的學生姓名

sele distinct sname from student,sc where student.sno=sc.sno

二:條件查詢:

常用的查詢條件

查詢條件謂詞

比較=,<,>,>=,<=,!=,<>,!>,!<;

not+上述比較子

確定範圍Between and,Not between And,

確定集合IN,not IN

字元匹配Like,Not Like

空值IsNull,ISNOTNULL

多重條件AND,OR

1:查詢電腦系全體學生的姓名

sele sname from student where sdept=”CS”

2:查詢所有年齡在20歲以下的學生姓名及其年齡

sele sname,sage from student where sage<20

3:查詢考試成績有不及格的學生的學號

sele student.sno from student,sc where student.sno=sc.sno and grade<60

4:查詢年齡在20到23間的學生的姓名,系別及年齡

sele sname,sdept,sage from student where sage between 20 and 23

5: 查詢年齡不在20到23間的學生的姓名,系別及年齡

sele sname,sdept,sage from student where sage not between 20 and 23

6:查詢資訊系(IS),數學系(MA)和電腦系(CS)學生的姓名和性別

sele sname,ssex from student where sdept in("IS","MA","CS")

7:查詢不是資訊系(IS),數學系(MA)和電腦系(CS)學生的姓名和性別

sele sname,ssex from student where sdept not in("IS","MA","CS")

8:查詢學號為”95001”的學生詳細情況

sele * from student where sno=95001

9:查詢所有姓劉的學生的姓名,學號和性別(where name like ‘劉%’)

sele sname,sno,ssex from student where sname like '劉%'

10:查詢姓”歐陽”且命名為三個漢字的學生的姓名

sele sname from student where sname like '歐陽_'

11:查詢名字中第2個字為”陽”字的學生姓名和學號(where sname like ‘_ _陽%’)

sele sname,sno from student where sname like '_ _陽%'

12:查詢所有不姓劉的學生姓名

sele sname from student where sname not like '劉%'

13:查詢DB_Design課程的課程號和學分(where cname like ‘Db_Design’Escape’’)

sele cno,gredit from course where cname like ‘Db_Design’Escape’’

14:查詢以”DB_”開頭,且倒數第3個字元為i的課程的詳細情況(where cname like ‘DB_%i__’escape’’)

‘DB_%i__’escape’’) sele cno,gredit from course where cname like ‘Db_%i__’escape’’

15:查詢缺少成績的學生的學號和相應的課程號

sele student.sno,cno from student,sc where grade is null

16:查詢所有成績的學生學號和課程號(where grade is not null)

sele student.sno,cno from student,sc where grade is not null

17:查詢電腦系年齡在20歲以下的學生姓名
sele sname from student where sdept=”CS” and sage<20
18:查詢選修了3號課程的學生的學號及其成績,分數降序排列
sele student.sno,grade from student,sc where student.sno=sc.sno and sc.cno=3 order by grade desc

19:查詢全體學生情況,結果按所在系的號升序排列,同一系中的學生按年齡降序
sele * from student order by sdept,sage desc

三:使用集合函式
count,sum,avg,max,min
1:查詢學生的總人數
2:查詢選修了課程的學生人數(select count(distinct sno))
3:計算1號課程的學生平均成績
4:查詢選修1號課程的學生最高分數
5:求各個課程號及相應的選課人數( selsect cno,count (sno); from sc; group by cno)
6:查詢選修了3門以上的課程的學生學號
select sno
from sc
group by sno
having count(*)>3
四:串連查詢:
<1>等值與非等值的串連查詢
在串連查詢中用來串連兩個有的條件稱為串連條件或串連謂詞,,當串連運算子號為”=”時,稱為等值串連,使用如,=,<,>,<=,>=,!=串連時稱非等值串連
1:查詢每個學生及其選修課程的情況
select student.*,sc.*
from student,sc
where student.sno=sc.sno
<2>自身串連
串連操作在同一個表中進行串連查詢
2:查詢每一門課的間接先修課(即先修課的先修課)
select first .cno,second.cno
from course first ,course second
where first.cno=second.cno
五：複合條件串連
1:查詢選修2號課程且成績在90分以上的所有學生。
Select student,sname
form student, sc
Where student.sno=sc.sno And
Sc.cno=’2’ and sc.grade>90
六：巢狀查詢
1：帶有謂詞in的子查詢
<1>查詢與“劉晨”在同一個系學習的學生
select sno,sname,sdept
from student
where sdept in(
select sdept
from student
where sname=”劉晨”）
或：select s1.sname,s1.sdept
from student s1,student s2
where s1.dept=s2.dept and s2.name=”劉晨”
<2>查詢選修了課程名為“資訊系統”的學生學號和姓名
select sno,sname
from student
where sno in
( select sno
from sc
where cno in
(select cno
from course
where cname-“資訊系統”)
或：select sno,sname
from student,sc,course
where student.sno=sc.sno and
sc.cno=course.cno and
course.cname=’資訊系統’)
2:帶有Any 或all謂詞的子查詢
<1>查詢其他系中比資訊系中某一學生年齡小的學生姓名和年齡
select sname, sage
from student
where sage <any(select sage
from student
where sdept=’is’
and sdept<>’is’
或用集合函式：select sname, sage
from student
where sage<
(select max(sage)
from student
where sdept=’is’)
and sdept<>’is’
<2> 查詢其他系中比資訊系所有學生年齡都小的學生姓名及年齡
select sname, sage
from student
where sage<all
(select sage
from student
where sdept=’is’)
and sdept<>’is’
3 帶有Exitst謂詞的子查詢
<1>查詢所有選修了1號課程的學生姓名
select sname
from student
where exists
(select *
from sc
where sno=student.sno and cno=’1’)
<2>查詢沒有選修1號課程的學生姓名
select sname
form student
where not exists
(select *
form sc
where sno=stuedent.sno and cno=’1’)
<2>查詢選修所有全部課程的學生姓名
select sname
from student
where not exists
(select *
from course
where not exists
(select *
from sc
where sno=student.sno
and cno=course.cno)
<3>查詢到少選修了學生95002選修的全部課程的學生號碼
select distinct sno
from sc scx
where not exists
( select *
from sc scy
where scy.sno=’95002’ and
not exists
( select *
from sc scz
where scz.sno=scx.sno and
scz.cno=scy.cno)