使用的是mysql,有些操作和sql server不同。
(一)在表S,C,SC上完成以下查询:
1. 查询学生的基本信息;
select * from S
2. 查询“CS”系学生的基本信息;
select * from S where Sdept='CS';
3. 查询“CS”系学生年龄不在19到21之间的学生的学号、姓名;
select Sno,Sname from S where (Sage not between 19 and 21) and Sdept='CS';
4. 找出“CS”系年龄最大的学生,显示其学号、姓名;
select Sno,Sname from S where Sdept='CS' order by Sage desc limit 1;
5. 找出各系年龄最大的学生,显示其学号、姓名;
SELECT * from S a where Sage = (SELECT MAX(Sage) from S b where a.sdept=b.sdept );
6. 统计“CS”系学生的人数;
select count(*) from S where Sdept='CS'
7. 统计各系学生的人数,结果按升序排列;
select Sdept,count(*) from S group by Sdept order by count(*);
8. 按系统计各系学生的平均年龄,结果按降序排列;
select Sdept,AVG(Sage) from S group by Sdept order by AVG(Sage) desc;
9. 查询无先修课的课程的课程名和学分数;
select Cname,Ccredit from C where Cpno is null;
10.统计每位学生选修课程的门数、学分及其平均成绩;
select SC.Sno,count(SC.Sno),SUM(C.Ccredit),AVG(grade) from SC,C where SC.Cno=C.Cno group by SC.Sno;
11.统计选修每门课程的学生人数及各门课程的平均成绩;
select Cno,AVG(grade),count(Sno) from SC group by Cno;
12.找出平均成绩在85分以上的学生,结果按系分组,并按平均成绩的升序排列;
select S.Sno,Sdept,AVG(grade) from SC,S where SC.Sno=S.Sno group by S.Sdept,S.Sno having AVG(grade) >85 order by AVG(grade) asc;
13.查询选修了“1”或“2”号课程的学生学号和姓名;
select SC.Sno,S.Sname from SC,S where SC.Cno=1 and SC.Sno=S.Sno union select SC.Sno,S.Sname from SC,S where SC.Cno=2 and SC.Sno=S.Sno;
14.查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩;
select SC.Sno,Sname,grade from C,SC,S where SC.Cno = S.Sno and SC.Cno = C.Cno and Cname='数据库系统' and grade<60;
15.查询每位学生选修了课程的学生信息(显示:学号,姓名,课程号,课程名,成绩);
select SC.Sno,Sname,SC.Cno,Cname,grade from C,SC,S where SC.Sno=S.Sno and SC.Cno=C.Cno;
16.查询没有选修课程的学生的基本信息;
select S.Sno,Sname,Ssex,Sage,Sdept from S left outer join SC on (S.Sno=SC.Sno) where Cno is null;
17.查询选修了3门以上课程的学生学号;
select Sno from SC group by Sno having count(Cno) >= 3;
18.查询选修课程成绩至少有一门在80分以上的学生学号;
select Sno from SC where grade > 80;
19.查询选修课程成绩均在80分以上的学生学号;
select Sno from SC group by Sno having min(grade) > 80;
(二)在表S、C、SC中完成下列更新:
1. 将数据分别插入表S、C、SC;
insert into S(Sno,Sname,Ssex,Sage,Sdept) values('05','小周',1,18,'Computerdept');
insert into C(Cno,Cname,Cpno,Ccredit) values(5,'LINUX',NULL,3);
insert into SC(Sno,Cno,grade) values('05',5,100);
2. 将表S、C、SC中的数据保存在磁盘上。
(直接在bin目录下操作)
mysqldump -u用户名 -p 数据库名 > 备份文件位置
3. 在表S、C、SC上练习数据的插入、修改、删除操作。(比较在表上定义/未定义主码(Primary Key)或外码(Foreign Key)时的情况)
insert into S(Sno,Sname,Ssex,Sage,Sdept) values('5','E',0,18,'CS');
delete from S where Sname='E';
update S set Sage=21 where Sno='4';
4. 将表S、C、SC中的数据全部删除,再利用磁盘上备份的数据来恢复数据。
source D:/student.sql
5. 如果要在表SC中插入某个学生的选课信息(如:学号为“2007001005”,课程号为“c123”,成绩待定),应如何进行?
insert into SC(Sno,Cno,grade) values ('200213121','c123',NULL);
6. 求各系学生的平均成绩,并把结果存入数据库;
- 建新表
create table AVGG(Sdept varchar(30), AVG_age double);
- 插入数据
insert into AVGG(Sdept,AVG_age) select Sdept,AVG(grade) from SC,S where SC.Sno=S.Sno group by Sdept;
7. 将“CS”系全体学生的成绩置零;
update SC set grade=0 where 'CS'=(select Sdept from S where S.Sno=SC.Sno);
8. 删除“CS”系全体学生的选课记录;
delete from SC where 'CS'=(select Sdept from S where S.Sno=SC.Sno);
9. 删除学号为“S1”的相关信息;
delete from S where Sno='S1';
delete from SC where Sno='S1';
10.将学号为“S1”的学生的学号修改为“S001”;
update S set Sno='S001' where Sno='S1';
update SC set Sno='S001' where Sno='S1';
11.把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——GRADE(SNO,AVG_GRADE);
- 建表
create table S__GRADE(Sno varchar(10),AVG_GRADE double);
- 存入数据
insert into S__GRADE(Sno,AVG_GRADE) select SC.Sno,AVG(grade) from S,SC,C where S.Sno=SC.Sno and SC.Cno=C.Cno group by SC.Sno,Ssex having AVG(grade)>80 and Ssex like 1;
12. 把选修了课程名为“数据结构”的学生的成绩提高10%;
update SC set grade=grade*1.1 where SC.Cno in(select SC.Cno from(select SC.Cno from SC,C where SC.Cno=C.Cno and Cname='数据结构')SC);
13. 把选修了“C2”号课程,且成绩低于该门课程的平均成绩的学生成绩删除掉。
- 查询平均成绩
select AVG(grade) from SC where Cno='C2';
- 删除低于平均值的记录
delete from SC where Cno='C2' and grade<70;
参考:https://wenku.baidu.com/view/778943c4910ef12d2af9e743.html