数据库的应用（四）：书上习题

书上原表

 

 --1.查询选课表的全部内容
 select * from SC;
 --2.查询计算机系的学生的姓名、年龄
 SELECT Sname,Sage FROM Student where Sdept='计算机系';
 --3.查询成绩在70~80分之间的学生的学号、课程号和成绩
 select Sno,Cno,Grade from SC where Grade between 70 and 80;
 --4.查询计算机系学生年龄在18~20之间且性别为男的学生的姓名、年龄
 select Sname,Sage from Student where Sage between 18 and 20 and Sdept='计算机系' and Ssex='男';
 --5.查询课程号为‘c001’的课程号的最高分数
 select max(Grade) from SC where Cno='c001';
 --6.查询计算机系学生的最大年龄和最小年龄
 select max(Sage),min(Sage) from Student where Sdept='计算机系';
 --7.统计每个系的学生人数
 select Sdept,count(*)人数 from Student Group by Sdept;
 --8.统计每门课程的选课人数和考试最高分。
 select Cno,count(*)选课人数,max(Grade) from SC Group by Cno;
 --9．统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。
 select Sno,count(*)选课门数,sum(Grade)总成绩 from SC  Group by Sno order by count(*)asc;
--10．查询总成绩超过200分的学生，要求列出学号、总成绩。
select Sno,sum(Grade)总成绩 from SC Group by Sno  having sum(Grade)>200;
--11.查询选修“C002”号课程的学生的姓名·所在系。
select Sname,Sdept from Student  s join SC on s.Sno=SC.Sno where Cno='c002';
--12．查询成绩80分以上:的学生的姓名。课程号和成绩，并按成绩降序排列结果。
select Sname,Cno,Grade from Student,SC where Student.Sno=SC.Sno and Grade>80 order by Grade;
select Sname,Cno,Grade from Student s join SC on s.Sno=SC.Sno where Grade>80 order by Grade;
--13．查询哪些课程没有学生选修，要求列出课程号和课程名。
--select Cname,Course.Cno from Course,SC  where Course.Cno!=SC.Cno;
SELECT Student.Sno, Student.Sname, Student.Sdept FROM Student
	LEFT JOIN SC ON SC.Sno = Student.Sno
	WHERE SC.Cno IS NULL 
--14、查询与Java在同一学期开设的课程的课程名和开课学期。
SELECT Cname, Semester FROM Course
	WHERE Semester IN (
		SELECT Semester FROM Course WHERE Cname = 'Java')
	AND Cname != 'Java'
--15．查询与李勇年龄相同的学生的姓名。所在系和年龄。
select Sname,Sdept,Sage from Student where Sage =(select Sage from Student where Sname='李勇');
--( 1)查询选修了“c001”号课程的学生的姓名·所在系。
select Sname,Sdept from Student where Sno in(select Sno from SC where Cno='c001');--in
--(2）查询数学系成绩80分以上的学生的学号、姓名。课程号和成绩。
--select SC.Sno,Student.Sname,SC.Cno,SC.Grade from SC,Student where Student.Sno=SC.Sno and Student.Sdept='数学系' and SC.Grade>=80;
SELECT S.sno,sname,SC.Cno,SC.Grade
FROM student S
JOIN Sc ON S.sno=Sc.sno
WHERE sdept='数学系' and Grade>=80
--(3)查询计算机系考试成绩最高的学生的姓名.
--select Sname from Student,SC where Student.Sdept='计算机系' and SC.Grade=(select max(Grade) from SC ) and Student.Sno=SC.Sno;
SELECT S.sname FROM Student S
JOIN Sc ON S.sno=Sc.sno
where  grade = (
	select MAX(grade) from sc where S.Sdept = '计算机系'
)
--(4)查询年龄最大的男学生的姓名·年龄。
select Sname,Sage from Student where Ssex='男' and Sage= (select max(Sage) from Student);
--(5）查询数据结构考试成绩最高的学生的姓名。所在系、性别和成绩。
select Sname,Sdept,Ssex,Grade from Student,SC where  Student.Sno=SC.Sno and Grade=(select max(Grade)from SC,Course where SC.Cno=Course.Cno and Course.Cname='数据结构') ;
--17.查询没有选修Java课程的学生的姓名·所在系。
select Sname,Sdept from Student,SC,Course where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Course.Cname='java';
--18．查询计算机系没有选课的学生的姓名·性别。
select Sname, Ssex from Student S
left join SC on S.Sno = SC.Sno
where Sdept = '计算机系' and SC.Cno is NULL
--select Sname,Ssex from Student where Sdept='计算机系' and (Sno  not in (select Sno from SC));
--19．创建一个新表，表名为test_t，其结构为（COLl，COL2, COL 3)，其中，
-------COL1:整型，允许空值。
-------COL2:字符型，长度为10，不允许空值。
-------COL3:字符型，长度为10，允许空值。
------试写出按行插入如下数据的语句

Create table test_t(
	COL1	int,
	COL2	char(10) not null,
	COL3	char(10)
	)
insert into test_t(COL2) values('B1');
insert into test_t values(1,'B2','C2');
insert into test_t values(2,'B3',null);
drop table test_t;------------------------------还原
--20．删除考试成绩低于50分的学生的选课记录。
delete from SC where Grade<50;
insert into SC values(1531101, 'c007', 45);------还原
--21．删除没人选的课程的基本信息。
delete from Course where Cno not in (select Cno from SC);---c010没了
insert  into    Course values('c004','大学英语',6,2)----------------------还原
insert  into    Course values('c010','计算机网络',5,6)
--22．删除讣算机系Java成绩不及格学生的Java选课记录。
delete from SC where Grade<60 and Sno in(select Sno from Student where Sdept='计算机系') and Cno in(select Cno from Course where Cname='java');
--------无需复原
--23．将第2学期开设的所有课程的学分增加2分
update Course set Credit=Credit+2 where Semester =2;
update Course set Credit=Credit-2 where Semester=2;----还原
--24．将Java 课程的学分改为3分。
update Course set Credit=3 where Cname='java';
update Course set Credit=2 where Cname='java';-------还原
--25.将计算机系学生的年龄增加1岁。
update Student set Sage=Sage+1 where Sdept='计算机系';
update Student set Sage=Sage-1 where Sdept='计算机系';---还原
--26．将信息系学生的“计算机文化学”课程的考试成绩加5分。
update SC set Grade=Grade+5 where Cno =(select Cno from Course where Cname='计算机文化学') and Sno in(select Sno from Student where Sdept='信息系');
update SC set Grade=Grade-5 where Cno =(select Cno from Course where Cname='计算机文化学') and Sno in(select Sno from Student where Sdept='信息系');---还原
--多表连接
--update SC set Grade = Grade+ 5
--from SC join Student S on SC.Sno = S.Sno
--join Course C on SC.Cno = C.Cno
--where Sdept = '信息系' and Cname = '计算机文化学'

--27．查询每个系年龄大于等于20的学生人数，并将结果保存到一个新永久表Dept_Age 中。
/*
select Sdept, count(*) 学生人数 into Dept_Age from Student
where Sage>= 20
group by Sdept
drop table Dept_Age;*/
--28．查询计算机系每个学生的JAVA考试情况，列出学号、姓名。成绩和成绩情况，其中成绩
--------情况的显示规则为:
--------如果成绩大于等于90，则成绩情况为“好”;
--------如果成绩在80~89，则成绩情况为“较好”;
--------如果成绩在7O~79，则成绩情况为“一殷”;
--------如果成绩在60~-69，则成绩情况为“较差”;
--------如果成绩小于60，则成绩情况为“差”。

select Student.Sno, Sname, Grade, 成绩情况=
    case 
        when grade>= 90 then '好'
        when grade between 80 and 89    then '较好'
        when grade between 70 and 79    then '一般'
        when grade between 60 and 69    then '较差'
        else '差'
    end
	from Student,SC 
	where Student.Sno=SC.Sno and Sdept='计算机系' and SC.Cno=(select Cno from Course where Cname='java');
    --from Student right join SC on Student.Sno = SC.Sno
    --join Course on Course.Cno = SC.Cno
    --where Sdept = '计算机系' and Cname = 'Java'


--29．统计每个学生的选课门数（包括没有选课的学生)，列出学号、选课门数和选课情况，其
--中选课情况显示规则为:
----如果选课门数大于等于6门，则选课情况为“多”;
----如果选课门数超过在3~~5门，则选课情况为“般”;
----如果选课门数在1~2门，则选课情况为“偏少”。
----如果没有选课，则选课情况为“未选课”。

select S.Sno, count(Cno) 选课门数, case
    when count(Cno)>= 6        then '多'
    when count(Cno)>= 3        then '一般'
    when count(Cno)>= 1        then '偏少'
    else '未选课'
end 选课情况
from Student S left join SC on S.Sno = SC.Sno
group by S.Sno
order by count(Cno) desc

--30．修改全部课程的学分，修改规则如下:
-------如果是第1~-2学期开设的课程，则学分增加5分:
-------如果是第3~-4学期开设的课程，则学分培加3分;
-------如果是第5~6学期开设的课程，则学分培加l分;
-------对其他学期开设的课程，学分不变。

update Course set Credit = Credit+ case
        when Semester between 1 and 2    then '5'
        when Semester between 3 and 4    then '3'
        when Semester between 5 and 6    then '1'
        else '0'
    end

update Course set Credit = Credit- case  -------还原
        when Semester between 1 and 2    then '5'
        when Semester between 3 and 4    then '3'
        when Semester between 5 and 6    then '1'
        else '0'
    end



--31．查询“李勇”和“王大力”所选的全部课程，列出课程名、开课学期和学分，不包括重复的结果。

--select Cname,Semester,Credit from Course
--where Cno in (select Cno from SC where Sno in(select Sno from Student where Sname='李勇' or Sname='王大力'))

select Cname, Semester, Credit
from Course join SC on Course.Cno = SC.Cno
join Student on Student.Sno = SC.Sno
where Sname = '李勇'
union
select Cname, semester, Credit
from Course join SC on Course.Cno = SC.Cno
join Student on Student.Sno = SC.Sno
where Sname = '王大力'

--32．查询在第3学期开设的课程中，“李勇”选了但“王大力”没选的课程，列出课程名和学分。

--select Cname,Credit from Course
--where Semester=3 and Cno in(select Cno from SC where Sno =(select Sno from Student where Sname='李勇' ))
--and Cno not in(select Cno from SC where Sno =(select Sno from Student where Sname='王大力'))
select Cname, Credit
from Course join SC on Course.Cno = SC.Cno
join Student on Student.Sno = SC.Sno
where Sname = '李勇' and Semester= 3
except
select Cname, Credit
from Course join SC on Course.Cno = SC.Cno
join Student on Student.Sno = SC.Sno
where Sname = '王大力'

--33．查询在学分大于3分的课程中，“李勇”和“王大力”所选的相同课程，列出课程名和学分。

--select Cname,Credit from Course
--where Credit>3 and Cno in(select Cno from SC where Sno =(select Sno from Student where Sname='李勇'))
--and Cno  in(select Cno from SC where Sno =(select Sno from Student where Sname='王大力'))

select Cname, Credit 
from Course join SC on Course.Cno = SC.Cno
join Student on Student.Sno = SC.Sno
where Sname = '李勇' and Credit> 3
intersect
select Cname, Credit 
from Course join SC on Course.Cno = SC.Cno
join Student on Student.Sno = SC.Sno
where Sname = '王大力' 

不一定对，但请大家有误必纠