接着学习SQL查询
数据库系统概论--高等教育出版社
高育的这本书不错,在语言上比较通顺.例子也不难,但很能说明问题,我把它们都在VFP里调试了一下,当然出问题是必然的,放在这里大家一起来探讨一下.
一些有用的网站：
想要明明白白了解SQL，不看ANSI的白皮书怎么行
http://www.contrib.andrew.cmu.edu/~shadow/sql/
编写 SQL 查询：让我们从基础知识开始
http://www.microsoft.com/china/msdn/library/data/sqlserver/ssequerybasics.mspx
没有VFP怎么行？Visual FoxPro V6.0 下载
如果你不放心D版可以试试MS SQL 2005它是免费的，可以从MS的网站下载
MS SQL 2005 Express Edition
http://msdn.microsoft.com/vstudio/express/sql/register/default.aspx
http://msdn.microsoft.com/vstudio/express/sql/default.aspx
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
eg1
查询全体学事情的学号与姓名.
select sno,sname from student
eg2
查询全体不生的姓名,学号,所在系.
select sname,sno,sdept from student
eg3
查询全体学生的详细记录.
select * from student
select sno,sname,ssex,sage,sdept from student
eg4
查全体学生的姓名其及出生年份.
select sname,2006-sage from student
eg5
查询全体学生的姓名,出生年份和所有系,要求用小写字母表示所有系名.
select sname,'Year of birth:',2006-Sage,ISLOWER(sdept) from student
select sname,'Year of birth:',2006-Sage,LOWER(sdept) from student
(###又被我发现一个错误,书上是上一个语句,它的返回值为T(ture)或F(false).
select sname NAME, 'Year of Birth:' BIRTH, 2006-sage BIRTHDAY, lower(sdept) DEPARTMENT from student
eg6
查询选修了课程的学生学号.
select sno from sc
eg7
查询计算机系全体学生的名单.
select sname from student where sdept='CS'
eg8
查询所有年龄在20岁以下的学生姓名及其年龄.
select sname,sage from student where sage<20
select sname,sage from student where not sage>=20
eg9
查询考试成绩有不及格的学生的学号.
select distinct sno from sc where grade<60
(###书上又错了,你看看书)
eg10
查询年龄在20-23岁(包括20岁和23岁)之间的学生的姓名,系别和年龄.
select sname,sdept,sage from student where sage between 20 and 23
eg11
查询年龄不在20-23岁之间的学生姓名,系别和年龄.
select sname,sdept,sage from student where sage not between 20 and 23
eg12
查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别.
select sname,ssex from student where sdept in ('IS','MA','CS')
eg13
查询既不是信息系,数学系,也不是计算机科学系的学生的姓名和性别.
select sname,ssex from student where sdept in ('IS','MA','CS')
eg14
查询学号为95001的学生的详细情况.
select * from student where sno like '95001'
select * from student where sno='95001'
eg15
查询所有姓刘的学生的姓名,学号和姓别.
select sname,sno,ssex from student where sname like '刘%'
eg16
查询姓'欧阳'且全为三个字的学生的姓名.
select sname from student where sname like '刘_'
select sname from student where sname like '欧阳__'
(###这书也是想当然)
eg17
查询名字中第2个字为阳字的学生的姓名和学号.
select sname,sno from student where sname like '_敏'
(###只有这样才查得出来)
eg18
查询所有不姓刘的学生姓名.
select sname from student where sname not like '刘%'
eg19
查询DB_Design课程的课程号和学分.
select cno,ccredit from course where cname like 'DB/_Design' escape'/'
eg20
查询以"DB_"开头,且倒数第3个字会为i的课程的详细情况.
select * from course where cname like 'DB/_%i__'ESCAPE'/'
eg21
某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩.查询缺少成绩的学生的学号和相应的课程号.
select sno,cno from sc where grade is null
eg22
查询所有有成绩的学生学号和课程号.
select sno,cno from sc where grade is not null
eg23
查询计算机系年龄在20岁以下的学生姓名.
select sname from student where sdept='CS' and sage<20
eg24
查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列.
select sno,grade from sc where cno='3' order by grade asc
select sno,grade from sc where cno='3' order by grade desc
eg25
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列.
select * from student order by sdept,sage desc
eg26
查询学生总人数.
select count(*) from student
eg27
查询选修了课程的学生人数.
select count(sno) from sc
select count(distinct sno) from sc
eg28
计算1号课程的学生平均成绩.
select avg(grade) from sc where cno='1'
eg29
查询选修课程的学生最高分数.
select max(grade) from sc where cno='1'
eg30
求各个课程号及相应的选课人数.
select cno,count(sno) from sc group by cno
eg31
查询选修了3门以上课程的学生学号.
select sno from sc group by sno having count(*)>3
eg32
查询每个学生及其选修课程的情况.
select student.*, sc.* from student,sc where student.sno=sc.sno
eg33
对例32用自然连接完成.
select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno
eg34
查询每一门课的间接选修课(即先修课的先修课).
select first.cno, second.cpno from course first,course second where first.cpno=second.cno
(###在VFP中查询的结果和书中的不一样,它把NULL也当也先修课.
eg35
查询选修2号课程且成绩在90分以上的所有学生.
select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90
eg36
查询每个学生的学号,姓名,选修课程名及成绩.
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
eg37
查询与刘晨在同一个系学习的学生.
select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨')
eg38
查询选修了课程名为信息系统的学生学号和姓名.
select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'))
(###查得太深)
eg39
查询其他系中比信息系某一学生年龄小的学生姓名和年龄.
select sname,sage from student where sage<any (select sage from student where sdept='IS')
eg40
查询其他系中比信息系所有学生年龄都小的学生姓名及年龄.
select sname,sage from student where sage<all(select sage from student where sdept='IS') and sdept<>'IS'
eg41
查询所有选修了1号课程的学生姓名.
select sname from student where exists (select* from sc where sno=student.sno and cno='1')
eg42
查询没有选修1号课程的学生姓名.
select sname from student where not exists(select * from sc where sno=student.sno and cno='1')
eg43
查询选修了全部课程的学生姓名.
select sname from student where not exists (select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno))
(###查得太深)
eg44
查询至少选修了学生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=scz.sno and scz.cno=scy.cno))
(###查得太深)
eg45
查询计算机科学系的学生及年龄不大于19岁的学生.
select * fro mstudent where sdept='CS' union select * from student where sage<=19
eg46
查询选修了课程1或者选修了课程2的学生
select sno from sc where cno='1' union select sno from sc where cno='2'
eg47
查询计算机科学系的学生与年龄不大于19岁的学生的交集,这实际上就是查谒计算机科学系中年龄不大于19岁的学生.
select * from student where sdept='CS' and sage<=19
eg48
查询选修课程1的学生集合与选修课程2的学生集合的交集.
本例实际上是查询既选修了课程1又选修了课程2的学生.
select sno from sc where cno='1' and sno in (select sno from sc where cno='2')
eg49
查询计算机科学系的学生与年龄不大于19岁的学生.
select * from student where sdept='CS' and sage>19
eg50
没有了