[分享]SQL查询语句复习题

SQL查询语句复习题! c8 }0 K7 y; e; e* i
新建学生-课程数据库的三个表:$ ^) v) Z3 B1 [' o
学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码;9 g8 J6 U# b  p* p* L! `
课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码;
学生选修表:SC(Sno,Cno,Grade) Sno,Cno,为主码;) _( A7 G! b" W& V1 M
Student
学号5 Y7 h) q  h8 e5 C+ D
  Sno姓名- \: P2 {: \1 E* G
Sname性别
Ssex年龄5 c% y: W; z) y) x" f
Sage所在系! G" o" E; u; {2 K- B" E
Sdept
95001李勇男20CS( l, P1 j" h$ K
95002刘晨女19IS+ ]8 ]- F# x1 |, @
95003王敏女18MA
95004张立男19IS9 N" ^  ~  J, i" `/ {
课程号4 ?! ~) x' e2 Z
Sno课程名
Cname先行课
Cpno学分
Credit# _9 D3 ^, L) K' k, j6 d3 q
1数据库54
2数学2
3信息系统14
4操作系统63
5数据结构74
6数据处理2
7Pascal语言64
Course:
# @' i7 [! m9 G5 A
$ R% r% P0 l6 O/ V; N# D$ v! P

SC: ) k( D1 g* y$ K! S& D  x* z1 \
学号
Sno课程号- Y: g  S! A/ ]0 B/ y; B, Z* T$ B
Cno成绩. g* q8 g0 w5 k9 r6 `( o
Grade
95001192
95001285) t# @- }$ _5 Y; ~# Z& l- x
95001388
95002290
95002380
7 O$ @  y$ N- z& w2 Q, p
" r( n, {2 u. _" E$ {
7 {3 h( c& J+ i  }

一:查询表中的列和行
1:查询全体学生的学与姓名/ P3 h! `7 L7 D8 B8 B& }- L6 I, v
sele sno,sname from student
2:查询全体学生的姓名、学号、所在系。% {# Q/ Y; t# V& P
sele sno,sname,sdept from student 3 M* l; b4 \- s# ?7 W' b
3:查询全体学生的详细记录8 ]( ]- X* S- f- t6 k4 S
sele * from student& g" T$ X0 ~% ^/ W1 i% W% ~. \
4:查询全体学生的姓名及出生年份
sele sno,sage from student; C/ T8 \& y1 ]2 D3 K
5:查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名0 W, G0 H! `* @! o( r4 V  w& B
6:查询选修了课程的学生学号/ r  T5 Z" u" p$ N
sele sno,cno from sc2 I- i5 l7 j# ~, ^9 B
7:查询选修了课程的学生姓名
sele distinct sname from student,sc where student.sno=sc.sno9 T4 h5 R9 Y% y$ T' O% c
二:条件查询:
常用的查询条件: I& S0 `( q, h+ `/ W$ j) _" }

查询条件谓词
比较=,<,>,>=,<=,!=,<>,!>,!<;
not+上述比较运算符8 y- {7 p- h, X$ ^: i  k1 E3 Z
确定范围Between and,Not between And,
确定集合IN,not IN$ V1 u' n% q) k9 z  S9 _: ~9 X
字符匹配Like,Not Like 6 \5 ]! \0 N0 {, N5 n- N
空值IsNull,ISNOTNULL9 M+ E% X4 R- [$ n, M; h2 u
多重条件AND,OR: U! j: `6 [0 l+ x
1:查询计算机系全体学生的姓名8 z+ y/ U- @5 a1 P' `% f# H
sele sname from student where sdept=”CS”6 G5 S/ L$ M. H3 r' R0 e
2:查询所有年龄在20岁以下的学生姓名及其年龄( }6 ]2 r" }- [; ^, B9 m  n; w9 r
sele sname,sage from student where sage<206 N% g6 l+ \; _- H. B
3:查询考试成绩有不及格的学生的学号
sele student.sno from student,sc where student.sno=sc.sno and grade<60
4:查询年龄在20到23间的学生的姓名,系别及年龄* A0 c# @! z- \2 D' e
sele sname,sdept,sage from student where sage between 20 and 23
5: 查询年龄不在20到23间的学生的姓名,系别及年龄1 Z# N) X  o/ u3 W) ]
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") 8 A8 x( Q3 P% h; I; b
7:查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别' @! Q7 i' g0 d  \4 t: O/ G3 N
sele sname,ssex from student where sdept not in("IS","MA","CS")
8:查询学号为”95001”的学生详细情况
sele * from student where sno=95001: I5 f" m2 e$ S/ J+ B* }$ d
9:查询所有姓刘的学生的姓名,学号和性别(where name like ‘刘%’)5 }. q( Q& \% F- q0 q+ W; |
sele sname,sno,ssex from student where sname like ';刘%';
10:查询姓”欧阳”且命名为三个汉字的学生的姓名
sele sname from student where sname like ';欧阳_';* `3 O6 Z" w3 u( L
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’\’/ x- e! O% `4 n& e' q9 K
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:查询缺少成绩的学生的学号和相应的课程号( P4 i1 w+ z' f9 V8 M
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 desc5 T  P. Q  ]" P. }9 [4 x
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 F6 u4 @% @. d5 ?' L7 G
5:求各个课程号及相应的选课人数( selsect cno,count (sno); from sc; group by cno)
6:查询选修了3门以上的课程的学生学号
  select sno% S5 T" h$ r% ^7 p
  from sc
group by sno . S& z, {3 H( h" L: f
having count(*)>3
四:连接查询:
<1>等值与非等值的连接查询
  在连接查询中用来连接两个有的条件称为连接条件或连接谓词,,当连接运算符号为”=”时,称为等值连接,使用如,=,<,>,<=,>=,!=连接时称非等值连接
1:查询每个学生及其选修课程的情况! j. Q2 l/ E6 p6 b0 b; M3 Y4 v
select student.*,sc.*
from student,sc$ Q9 Z7 `4 z4 b, N$ u
where student.sno=sc.sno
<2>自身连接6 C5 T6 f# B! }6 r* T9 w3 j7 G  X% E
连接操作在同一个表中进行连接查询
2:查询每一门课的间接先修课(即先修课的先修课)2 z* W' z/ h5 F' v3 q9 Z! b7 C  y( q
select first .cno,second.cno2 s% j/ ?1 Z& L6 Z+ E- c
from course first ,course second- w5 V% X- `+ i
where first.cno=second.cno
五：复合条件连接
1:查询选修2号课程且成绩在90分以上的所有学生。% b2 f5 d# {6 ~- ~) p" v) @7 N  z
Select student,sname/ J! R1 _7 L1 k( N
Form student, sc  V0 G$ t; g% g
Where student.sno=sc.sno And $ a9 O3 s" ~) N; [6 Z3 F; y
Sc.cno=’2’ and sc.grade>904 ]! p5 K+ d& u5 ~& p7 h- M
六：嵌套查询  b- F9 x" e6 v( Z0 M5 o) P
1：带有谓词in的子查询2 P" K5 N7 r* q; y' L1 c! D" L: B
<1>查询与“刘晨”在同一个系学习的学生
select sno,sname,sdept
from student : |' k0 y5 M( I; j& `# d
where sdept in(. i1 r9 k$ M" ~( n: J' X, A. ~1 w- J
  select sdept ; a2 a2 q! {4 G
  from student
  where sname=”刘晨”）. Z4 s8 r7 a0 J9 b3 a5 L2 L
或：select s1.sname,s1.sdept9 t. t) A, u. l% U1 N
from student s1,student s2
where s1.dept=s2.dept and s2.name=”刘晨”6 w; Z  r; b. y% F( |. }
<2>查询选修了课程名为“信息系统”的学生学号和姓名' g1 z8 e6 w; @" b. F/ m; X
select sno,sname ' y' Y& P  b4 O. Q- p) n
from student2 @$ G; _! Z8 M2 G
where sno in; Y0 D4 a, a& J- ^
( select sno
  from sc
where cno in
   (select cno
      from course! g. @; a7 T% j$ v7 R0 p
         where cname-“信息系统”)' {6 m) E+ J" U% z
或：select sno,sname. l1 G6 Y" h( O4 n
from student,sc,course
where student.sno=sc.sno and$ l2 C  O& K1 k: ^
      sc.cno=course.cno and
      course.cname=’信息系统’): U) N  A& Q9 r" i! V7 [
2:带有Any 或all谓词的子查询, C" ?% ~8 B3 {; p1 _( d
<1>查询其他系中比信息系中某一学生年龄小的学生姓名和年龄2 J. M0 u' X1 M0 J7 I" G& ~  [5 l
select sname, sage
from student3 Y" _0 B4 y9 u, }. f9 L
where sage <any(select  sage8 Y7 F3 v: r+ l6 E! N  k' l6 O+ S+ p
      from student
where sdept=’is’
and sdept<>’is’
或用集函数：select sname, sage$ c( r3 G; G7 x. U
from student4 }* g1 e. X9 V1 D; j
where sage<
(select max(sage)
  from student
  where sdept=’is’)6 H8 d+ W* I5 v) P2 k
and sdept<>’is’
<2> 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄3 x6 A! b8 x- }' h
select sname, sage
from student
where sage<all: X, h" m3 C: ~& N" h/ K9 _
      (select sage
      from student- ^8 D+ b4 g3 z0 _7 s- c! @
      where sdept=’is’)
   and sdept<>’is’
3 带有Exitst谓词的子查询
<1>查询所有选修了1号课程的学生姓名
select sname
from student4 u. Y" D; w/ X; z0 W# L. v
where exists ) M# J0 M4 N; ?, b0 N
      (select *' s( H: ^6 D& x8 y) C
      from sc# `; O# a: r; s0 b' k$ u2 Z
      where sno=student.sno and cno=’1’)
<2>查询没有选修1号课程的学生姓名
select sname/ }& E( v/ X) e5 p! C# K0 x
form student% h  k& W3 S( U1 X, ?
where not exists ! p  v% u6 {) f0 W
         (select *
         form sc
            where sno=stuedent.sno and cno=’1’)  }8 ]3 t4 M* X% q' w/ g
<2>查询选修所有全部课程的学生姓名
select sname
from student+ Y' P0 w8 |* s
  where not exists
      (select * 8 m" b0 @, k( @
         from course$ H6 k  L+ j2 ], f4 n
         where not exists
               (select * " |8 I: V! s' ~4 V# {
                  from sc
                     where sno=student.sno( P4 \5 V9 T" ]/ d/ v
            and cno=course.cno)
<3>查询到少选修了学生95002选修的全部课程的学生号码
select distinct sno
  from sc scx& }( u* v9 u8 }1 ~0 [0 o9 X: P! f
  where not exists
      ( select *
         from sc scy- K# }6 [. `6 G
         where scy.sno=’95002’ and
            not exists
               ( select * 1 n# n5 f& h1 M/ q. Y
                  from sc scz
                     where scz.sno=scx.sno and* z( ?. x9 w7 S4 F" o8 F% ]" x
                        scz.cno=scy.cno)/ E& t) I' F6 q% l

收藏分享评分

回复引用

订阅 TOP

五月花香

入门网管

2^#

发表于 2005-4-3 17:26 | 只看该作者

[分享]SQL查询语句复习题

错误太多了) q: R8 Y$ |% I- V& h

回复引用

TOP

返回列表