《sql 数据库上机实验二》.doc
文本预览下载声明
实目的：
掌握数据查询操作。
内容：1）创建学生表student、课程表course和选课表SC,并输入数据注意数据的完整性。;（可以使用实验一中已经建立的表和数据）
2) 对各表中的数据进行不同条件的查询；‘年龄为’+cast(sage as char(2))+’岁’
From student
查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
查询学生总人数。
查询选修了课程的学生人数。
查询选修了7号课程的学生总人数和平均成绩
查询选修6号课程学生的最好成绩
查询每个系的系名及学生人数。
查找每门课的选修人数及平均成绩
查找没有先修课的课程情况
附录：
语句清单
--sql查询实验二的代码
SELECT sno,sname --1
from student
select * ------------2
from student
select sname,sage,sdept -----3
from student
where student.sdept=IS
select distinct student.sno -----4
from student,sc
where sc.sno=student.sno
select distinct student.sno ----5
from student,sc
where sc.sno=student.sno and grade60
select ssex,sage,sdept -----------6
from student
where sno not in
(select sno
from student
where sdept=IS)
/* 这是exists的用法
select ssex,sage,sdept
from student as y
where not exists(select *
from student
where Y.sdept=CS)
*/
select sno,sname,sage,sdept -------7
from student
where sage between 18 and 20;
select * ---------------8
from student
where sname like刘%
select * -----------------9
from student
where sname like[刘李]%
select sname ----------------10
from student
where sname like刘_ _--中文占用两个字符，所以这里用两个_
select sname -------------------11
from student
where 2013-sage1983
-- select year(sage) 这是取单个人的出生年份的用法，调用year函数
select year(getdate())-s.sage+1
from student as s -------12
where sdept=CS
Select sname + 年龄为+cast(sage as char(2))+岁
From student ---------13
select *
from student ---------------------14
order by sdept,sage desc
select COUNT(sno)----------------15
from student
select COUNT(distinct sno)
from sc --------------16
/*
SELECT COUNT(*)
FROM student ----------16题解法二
WHERE EXISTS( SELECT * FROM SC WHERE sc.sno = student.sno )
*/
select COUNT(student.sno),avg(sc.grade)
from student,sc ------------17
where student.sno=sc.sno and cno=7
select max(grade)
from sc -------------------18
where cno=6
select sdept,COUNT(sno)
from student -------------------19
group by sdept
select C
显示全部