SQL server 查询语句 作业1

--1、按照出生年份升序显示所有的学生的学号、姓名、性别、出生年份及院系信息。(提示:出生年份为计算列)
select * from student
select sno,sname,ssex,sdept,(year(getdate()))- sage 出生年份 from student order by 出生年份 
delete from student where sno='s0006';

--2、可以在student表中添加一些新记录，然后统计各院系学生人数。
INSERT INTO student(sno,sname,ssex,sage,sdept) VALUES ('s0006' ,'小路','男',19,'信息系')
select * from student
select sdept, count(*) 人数 from student group by sdept

--3、按照课程号、成绩降序显示课程成绩在70到80之间的学生学号、课程号及成绩。
select * from sc
select * from sc  where grade>70 and grade<80 order by cno desc,grade desc

--4、向sc表中添加一些新记录，查询选修课程门数大于3的各个学生的学号和选修课程门数。
insert into sc(sno,cno,grade) values('S0005','c001',80)
insert into sc(sno,cno,grade) values('S0005','c002',80)
select * from sc
select sno 学号,count(sno) 选修课程门数 from sc group by sno having count(sno)>=3;

--5、查询所有学生的数据库原理课程成绩信息(若表中没有该记录，自己手工添加几条记录，方便测试代码），展示学号、姓名、所在系、课程名称和成绩。
select * from student
select * from course
select * from sc
select student.sno 学号,sname 姓名,sdept 所在系,cname 课程名,grade 成绩 
from student,sc,course 
where student.sno=sc.sno and sc.cno=course.cno and cname='数据库原理'

--6、按课程号降序显示选修各个课程的总人数、最高分、最低分和平均分。
select * from sc
select cno 课程名,count(cno) 人数,max(grade) 最高分,min(grade) 最低分,avg(grade) 平均分 from sc group by cno

--7、显示平均成绩大于“S0001”学生平均成绩的各个学生的学号、平均成绩。
select sno 学号, avg(grade) 平均成绩 from sc group by sno having avg(grade) > (select avg(grade) from sc where sno='s0001')

--8、查询各门课程的选修及格人数、及格比率。
select * from sc
select cno 课程, count(case when grade>=60 then 1 end )  及格人数 ,
round(cast( count( case when grade>=60 then 1 end )as float) / cast(count(*)as float)+0.00004,4) 及格比率
from sc group by cno

--9、查询选修课头数最多的学生学号及选修课头数最少的学生学号。
select * from sc
select sno 学号, courses  选课最多和选课最少的课程数 
from (select sno ,count(sno) courses from sc group by sno) t
where courses = (select max(courses) from (select sno,count(sno) courses from sc group by sno) t )
or courses = (select min(courses) from (select sno,count(sno) courses from sc group by sno) t )

--10、显示各个院系男女生人数，其中在结果集中列标题分别指定“院系名称、男生人数、女生人数”。
select * from student
select sdept 院系名称,sum(case when ssex='男' then 1 else 0 end) 男生人数 ,sum(case when ssex='女' then 1 else 0 end) 女生人数 
from student group by sdept

--11、列出有两门以上课程（含两门）不及格的学生的学号及该学生的平均成绩。
select sc.sno 学号,avg(grade) 平均成绩
from sc,(select sno from sc where grade<60 group by sno having count(distinct cno)>=2) A
where sc.sno=A.sno 
group by sc.sno

--12、以“查询选修各门课程的选修及格人数、及格比率”建立一个视图，并测试该视图的查询结果。
select * from sc
select cno,count(case when grade>60 then 1 end) 选修及格人数, round( cast(count(case when grade>=60 then 1 end) as float)/cast(count(*) as float),3 )及格比率
from sc group by cno
--修改s0001同学的c001课程成绩为50，测试结果
update sc set grade=50 where sno='s0001' and cno='c001'
select * from sc
select cno,count(case when grade>60 then 1 end) 选修及格人数, round( cast(count(case when grade>=60 then 1 end) as float)/cast(count(*) as float),3 )及格比率
from sc group by cno

--13、查询信息系专业每个学生的学号、姓名、选修课程的平均成绩。
select sdept,sc.sno 学号, sname 姓名, avg(grade) 平均成绩
from sc left join student
on sc.sno=student.sno
where sdept = '信息系'
group by sc.sno,sdept,sname

--14、查询同时选修了c003和c004号课程的学生学号、姓名。
select sc.sno 学号,sname 姓名
from sc ,student
where sc.sno in (
    select sno from sc where cno='c003'
        and sno in (
        select sno from sc where cno='c004'
        )
    )and sc.sno=student.sno
    group by sc.sno,sname