把书中的SQL查询语误句调一下（《数据库系统概论--高等教育出版社》）

 

学而不思则惘，思而不学则怠。

关系数据库标准语言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 下载
http://www.baigoogledu.com/search.asp?q=Visual+FoxPro+V6.0+下载&num=10

如果你不放心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

 

T(教师关系)

TNO 教师号	TN 姓名	SEX 性别	AGE 年龄	PROF 职称	SAL 工资	COMM 岗位津贴	DEPT 系别
T1	李力	男	47	教授	1500	3000	计算机
T2	王平	女	28	讲师	800	1200	信息
T3	刘伟	男	30	讲师	900	1200	计算机
T4	张雪	女	51	教授	1600	3000	自动化
T5	张兰	女	39	副教授	1300	2000	信息

S(学生关系)

SNO 学号	SN 姓名	SEX 性别	AGE 年龄	DEPT 系别
S1	赵亦	女	17	计算机
S2	钱尔	男	18	信息
S3	孙珊	女	20	信息
S4	李思	男	21	自动化
S5	周武	男	19	计算机
S6	吴丽	女	20	自动化

CL(课程关系)

CNO 课程号	CN 课程名	CT 课时
C1	程序设计	60
C2	微机原理	80
C3	数字逻辑	60
C4	数据结构	80
C5	数据库	60
C6	编译原理	60
C7	操作系统	60

SC(选课关系) SNO 学号 CNO 课程号 SCORE 成绩 S1 C1 90 S1 C2 85 S2 C5 57 S2 C6 80 S2 C7   S2 C4 70 S3 C1 75 S3 C2 70 S3 C4 85 S4 C1 93 S4 C2 85 S4 C3 83 S5 C2 89

TC(授课关系) TNO CNO T1 C1 T1 C4 T2 C5 T3 C1 T3 C5 T4 C2 T4 C3 T5 C5 T5 C7

 

例10
在S表中增加一个班号列和住址列
alter table s add class_no char(6) address char(40)
(###这个问题解决不了,看看你有什么办法)

eg18
select sno,sn,age from s

eg19
select * from s

eg20
select distinct sno from sc

eg21
select sn name,sno,age from s
select sn as name,sno age from s

eg22
select sno,score from sc where cno='C1'
(C1要大写)

eg23
select sno, cno, score from sc where score>85

eg24
select sno,cno,score from sc where (cno='C1' or cno='C2') and (score >= 85)

eg25
select tno,tn,prof from t where sal between 1000 and 1500
select tno,tn,prof from t where sal >= 1000 and sal<=1500

eg26
select tno,tn,prof from t where sal not between 1000 and 1500

eg27
select sno,cno,sc.score from sc where cno in('C1','C2')
select sno,cno,score from sc where cno='C1' or cno='C2'

eg28
select sno,cno,score from sc where cno not in('C1','C2')
select sno,cno,score from sc where (cno<>'C1') and (cno<>'C2')

eg29
select tno,tn from t where tn like "张%"

eg30
select tno,tn from t where tn like "_力"
select tno,tn from t where tn like '_力'
(###这个我试过了,在VF中单,双引号不区别,都可以用,一个'_'代表一个字符,但是并非大家认为的汉字要两个字符!!试试下面的例子)
select tno,tn,prof from t where prof like '__授'
select tno,tn,prof from t where prof like '_授'

eg31
select sno,cno from sc where score is not null
select sno,cno from sc where score is null
(###在建立表时,在null列上要打个勾)

eg32
select sum(score) as TotalScore,avg(score) as AveScore from sc where (sno='S1')
(###表中的score属性要求是numeric型,这样avg(score)才会有小数点)

eg33
select max(score) as MaxScore, min(score) as MinScore, max(score)-min(score) as Diff from sc where cno='C1'

eg34
select count (sno) from s where dept='计算机'

eg35
select count(distinct dept) as DeptNum from s

eg36
select count(score) from sc

eg37
select count(*) from s where dept='计算机'

eg38
select count(*) from s where dept='计算机' and sex='女'

eg39
select tno,count(*) as C_NUM from tc group by tno

eg40
select sno, count(*) as SC_NUM from sc group by sno having (count(*)>=2)
(###书上的结果有错,漏掉了S1)

eg41
select sno ,score from sc where (cno='C1') order by score desc

eg42
select sno,cno,score from sc where(cno in ('C2','C3','C4','C5')) order by sno, score desc

eg43
select sno,sum(score) as TotalScore from sc where (score >= 60) group by sno having(count(*)>=3) order by sum(score)desc

eg44
select t.tno,tn,cno from t,tc where (t.tno=tc.tno) and (tn='刘伟')
select t.tno,tn,cno from t inner join tc on t.tno=tc.tno where (tn='刘伟')
select r2.tn, r1.cno from (select tno,cno from tc)as r1 inner join (select tno,tn from t where tn='刘伟') as r2 on r1.tno=r2.tno
(###第三个方法还有些问题)

eg45
select s.sno,sn,cn,score from s,cl,sc where s.sno=sc.sno and sc.cno=cl.cno

eg46
select cn,tn,prof,count(sc.sno) from cl,t,tc,sc where t.tno=tc.tno and cl.cno=tc.cno and sc.cno=cl.cno group by sc.cno

eg47
select x.tn,x.sal as SAL_a, y.sal as SAl_b from t as x, t as y where x.sal>y.sal and y.tn='刘伟'
select x.tn,x.sal,y.sal from t as x inner join t as y on x.sal>y.sal and y.tn='刘伟'
select r1.tn,r1.sal,r2.sal from (select tn,sal from s) as r1 inner join (select sal from t where tn='刘伟') as r2 on r1.sal > r2.sal
(###上面的这个自身连接查询还一下子看不出来是哪里错了)

eg48
select sn,age,cn from s,cl,sc where s.sno=sc.sno and sc.cno=cl.cno
select r3.sno,r3.sn,r3.age,r4.cn from (select sno,sn,age from s) as r3 inner join (select r2.sno,r1.cn from (select cno,cn from cl) as r1 inner join (select sno,cno from sc) as r2 on r1.cno=r2.cno) as r4 on r3.sno=r4.sno
(###还是有错,郁闷啊)

eg49
select s.sno,sn,cn,score from s left outer join sc on s.sno=sc.sno left outer join cl on cl.cno=sc.cno

eg50
select tno,tn from t where prof=(select prof from t where tn='刘伟')

eg51
select tn from t,tc where t.tno=tc.tno and tc.cno='C5'
select tn from t where(tno=any(select tno from tc where cno='C5'))

eg52
select tn,sal from t where(sal>any(select sal from t where dept='计算机')) and (dept<>'计算机')
select tn,sal from t where sal>(select min(sal)from t where dept='计算机' and dept='计算机') and dept<>'计算机'

eg53
select tn from t where (tno in (select tno from tc where cno='C5'))

eg54
select tn,sal from t where(sal> all(select sal from t where dept='计算机')) and (dept<>'计算机')
select tn,sal from t where(sal> (select max(sal)from t where dept='计算机')) and (dept<>'计算机')

eg55
select distinct tn from t where ('C5'<>all(select cno from tc where tno=t.tno))
(###SQL:Queries of this type are not supported.)

eg56
select tn from t where exists (select * from tc where tno=t.tno and cno='C5')

eg57
select tn from t where(not exists (select * from tc where tno=t.tno and cno='C5'))

eg58
select sn from s where (not exists (select * from c where not exists (select * from sc where sno=s.sno and cno=cl.cno)))
(###查得太深)

eg59
select sno as 学号, sum(score) as 总分 from sc where (sno='S1') group by sno union select sno as 学号, sum(score) as 总分 from sc where (sno='S5') group by sno

eg60
select sno as 学号, sum(score) as 总分 into #cal_table from sc group by sno

 

下载代码