广西民族大学数据库课后习题

1.代码：
select*from SC;
2.代码：
select Sname,Sage from Student where Sdept='计算机系';
3.代码：
select Sno,Cno,Grade from SC where Grade between 70 and 80；
4.代码：
select Sname,Sage from Student where Sdept='计算机系' and Ssex='男'and Sage between 18 and 20;
5.代码：
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(*);
10.
Select Sno,sum(Grade) 总成绩 from SC group by Sno having sum(Grade)>200;
11.代码：
Select Sname,Sdept from Student join SC on Student.Sno=SC.Sno and Cno='c002';
12.代码;
Select Sname,Cno,Grade from student,SC where Student.Sno=SC.Sno and Grade>80 order by grade deSC;
13.代码：
Select Student.Sno,Student.Sname,Student.Sdept from Student
left join SC on SC.Sno = Student.Sno where SC.Cno IS NULL;

14.
Select C2.Cname,C2.Semester from Course C1 join Course C2 on C1.Semester= C2.Semester where C1.Cname='Java' and C2.Cname!='Java';
15.代码：
Select Sname,Sdept,Sage from student where Sage=(select Sage from student where Sname ='李勇')
16.
（1）：代码：
Select sname,sdept from student where sno in(select sno from sc where cno = 'C001')
(2):代码
Select sno,sname from student where sno in(select sno from sc where grade >80 and sdept='数学系')
（3）代码：
select sname from student Student join SC on Student.Sno =SC.Sno where sdept='计算机系' and grade =(select max(grade) from SC join Student Student on Student.Sno=SC.Sno where sdept='计算机系')
(4)代码：
select top 1 Student.Sname,Student.Sdept,SC.Grade from SC
join Student on SC.Sno=Student.Sno
join Course on SC.Cno=Course.Cno
 where SC.Cno in(select Cno from Course where Cname='数据结构')
order by SC.Grade desc
17. 代码：
select Sname,Sdept from Student s
join SC on S.Sno=SC.Sno
join Course on Course.Cno=SC.Cno 
where Cname !='Java';
18.代码：
select Sname,Ssex from Student S
join SC on S.Sno=SC.Sno
where Grade is NULL and Sdept='计算机系';
19.代码：
create table test_t(
COL1 int null,
COL2 char(10) not null,
COL3 char (10) null
);
insert into test_t values (null,'B1',null),(1,'B2','C2'),(2,'B3',null);

select*from test_t


23.代码：
Update Course set Credit=Credit+2 
where Semester=2 
select*from Course；
24.代码：
Update Course set Credit=3 where Cname='Java' 
select*from Course 
where Cname='Java';
25.代码：
Update student set sage =sage+1
Where sdept ='计算机系';
运行后的结果：

26.代码：
Update SC set grade=grade+5
From SC 
join student  S on SC.Sno=S.Sno
join Course C on C.Cno=SC.Cno
where Sdept ='信息系'and Cname='计算机文化学';
27.代码：
Select Sdept,COUNT(Sage) 学生人数 into Dept_Age from student 
Where Sage>=20
Group by Sdept
28.代码：
Select S.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 '较差'
When Grade <60 then '差'
End as 成绩情况
From student S join SC on S.Sno=SC.Sno
Join Course C on SC.Cno=C.Cno
Where Sdept='计算机系'and Cname='Java'
29.代码：
Select S.Sno,COUNT(SC.Cno) 选课门数,case
when count(SC.Cno)>=6 then '多'
when count(SC.Cno) between 3 and 5 then '一般'
when count(SC.Cno) between 1 and 2 then '偏少'
when count(SC.Cno)=0 then '未选课'
end as 选课情况
from Student S left join SC on S.Sno=SC.Sno
group by S.Sno


30.代码：
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 C
join SC on C.Cno=SC.Cno
join Student S on S.Sno=SC.Sno
where Sname ='李勇'
union
select Cname,Semester,Credit from Course C
join SC on C.Cno=SC.Cno
join Student S on S.Sno=SC.Sno
where Sname ='王大力';
32.代码：
select Cname,Credit from Course C
join SC on C.Cno=SC.Cno
join Student S on S.Sno=SC.Sno
where Sname ='李勇'and Semester=3
except
select Cname,Credit from Course C
join SC on C.Cno=SC.Cno
join Student S on S.Sno=SC.Sno
where Sname ='王大力'and Semester=3;
33.代码：
select Cname,Credit 
from Student S join SC on S.Sno=SC.Sno
join Course C on C.Cno=SC.Cno
where Sname ='李勇'and Credit>3
intersect
select Cname,Credit 
from Student S join SC on S.Sno=SC.Sno
join Course C on C.Cno=SC.Cno
where Sname ='王大力'and Credit>3;