数据库查询 - 选课系统

 /*

仅供参考:切勿躺尸！！！

*/

第1关：数据库数据的插入

USE School;

#请在此添加实现代码
########## Begin ##########
########## 插入学生表（Student）相应数据 ##########
insert into student values('9512101','李勇   ','男','19','计算机系'),
('9512102','刘晨','男','20','计算机系'),
('9512103','王敏','女','20','计算机系'),
('9521101','张立','男','22','信息系'),
('9521102','吴宾','女','21','信息系'),
('9521103','张海','男','20','信息系'),
('9531101','钱小平','女','18','数学系'),
('9531102','王大力','男','19','数学系');
########## 插入课程表（Course）相应数据 ##########
insert into course values('C01','计算机文化学','3','1','41'),
('C02','VB','2','3','61'),
('C03','计算机网络','4','7','14'),
('C04','数据库基础','6','6','24'),
('C05','高等数学','8','2','19'),
('C06','数据结构','5','4','55');
########## 插入学生选课表（DBSC）相应数据 ##########
insert into dbsc values('1','9512101','c01','90','必修'),
('2','9512101','c02','86','选修'),
('3','9512101','c06','45','必修'),
('4','9512102','c02','78','选修'),
('5','9512102','c04','66','必修'),
('6','9521102','c01','82','选修'),
('7','9521102','c02','75','选修'),
('8','9521102','c04','92','必修'),
('9','9521102','c05','50','必修'),
('10','9521103','c02','68','选修'),
('11','9521103','c06','56','必修'),
('12','9531101','c01','80','选修'),
('13','9531101','c05','95','必修'),
('14','9531102','c05','85','必修');
########## End ##########


########## 查询表数据 ##########
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM dbsc;

第2关：简单查询

#********* Begin *********#
echo "


select Sname,Sdept from student where Sdept = '计算机系';
select Sno from dbsc where Grade < 60; 
select Sname,Sdept,Sage from student where Sage >= 20 and Sage < 23 and Sdept =  '信息系';
select Sno,Grade from dbsc where Cno = 'c02';
select count(*) from student ;


"
#********* End *********#

第3关：进阶查询

#********* Begin *********#
echo "

select Sno,Sname,Ssex,Sage,Sdept from student where Sname LIKE '张%';

select Sname,Ssex,Sdept from student where Sdept = '计算机系' or Sdept = '数学系' or Sdept = '信息系';

select Cno,count(*) from dbsc where istec ='选修' and (cno='c01' or cno ='c02') group by cno;

select Sno from dbsc group by sno having count(*)>3 ;

select Sname,Cno,Grade from dbsc left join student on student.Sno=dbsc.Sno where student.Sdept='计算机系';


"
#********* End *********#

第4关：复杂查询

#********* Begin *********#
echo "

select distinct student.Sno,student.Sname from student left join dbsc on student.Sno = dbsc.Sno where dbsc.istec = '选修';

select Sname,count(*),avg(Grade) from student right join dbsc on student.Sno = dbsc.Sno group by dbsc.Sno ;

select avg(Grade),count(*) from student left join dbsc on student.Sno = dbsc.Sno 
group by dbsc.Sno having count(*)>=4;

select Sname,Cno,Grade from student left join dbsc on student.Sno = dbsc.Sno where student.Sdept = '信息系' and dbsc.Cno = 'c02' and istec = '选修';

update dbsc set grade = grade+5 where grade < 60;
"
#********* End *********#