数据库SSD7实验二

使用的是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

上一篇:基于IDEA和Tomcat的 Ajax— —Servlet 交互


下一篇:Oracle merge into 详解