select.txt

数据库的一个代码

数据类型
bit bigint int smallint tinyint numeric decimal float real money smallmoney datetime smalldatatime char varchar test nchar nvachar ntext binary varbinary image timestamp uniqueidentifier sql_variant table

例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
查询选修课程号为C1的学生的学号和成绩.
select sno,score from sc where cno='C1'
(C1要大写)

eg23
查询成绩高于85分的学生的学号,课程号和成绩.
select sno, cno, score from sc where score>85

eg24
查询选修C1或C2且分数大于85分的学生的学号,课程号和成绩.
select sno,cno,score from sc where (cno='C1' or cno='C2') and (score >= 85)

eg25
查询工资工1000至1500元之间的教师的教师号,姓名及职称.
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
查询工资不在1000至1500之间的教师和教师号,姓名和职称.
select tno,tn,prof from t where sal not between 1000 and 1500

eg27
查询选修C1或C2的学生的学号,课程号和成绩.
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
查询没有选修C1,也没有选修C2的学生的学号,课程号和成绩.
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
求学号为S1学生的总分和平均分.
select sum(score) as TotalScore,avg(score) as AveScore from sc where (sno='S1')
(###表中的score属性要求是numeric型,这样avg(score)才会有小数点)

eg33
求选修C1号课程的最主分,最低分及之间相差的分数.
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
利用特殊函数COUNT(*)求计算机系学生的总数.
select count(*) from s where dept='计算机'

eg38
利用特殊函数COUNT(*)求计算机系女学生的总数.
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
查询选修C1的学生学号和成绩,并按成绩降序排列.
select sno ,score from sc where (cno='C1') order by score desc

eg42
查询选修C2,C3,C4或C5课程的学号,课程号,和成绩,查询结果按学号升序排列,学号相同再按成绩降序排列.
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
查询讲授课程号为C5的教师姓名.
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
查询讲授课程号为C5的教师姓名(使用IN)
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
查询不讲授课程号为C5的教师姓名.
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
用含有EXISTS的语句完成例51的查询.
select tn from t where exists (select * from tc where tno=t.tno and cno='C5')

eg57
查询没有讲授课程号C5的教师姓名.
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
从SC数据表中查询出学叼为S1同学的学号和总分,再从SC数据表中查询出学号为S5的同学的学号和总分,然后将两个查询结果合并成一个结果集.
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
从SC数据表中查询出所有同学的学号和总分,并将查询结果存放到一个新的数据表cal_table中.
select sno as 学号, sum(score) as 总分 into #cal_table from sc group by sno