Database query - course selection system

 /*

For reference only : Do not lie on the corpse ！！！

*/

The first 1 Turn off ： Database data insertion

USE School;

# Please add the implementation code here 
########## Begin ##########
##########  Insert student table （Student） Corresponding data  ##########
insert into student values('9512101',' Li Yong    ',' male ','19',' Department of Computer Science '),
('9512102',' Liu Chen ',' male ','20',' Department of Computer Science '),
('9512103',' Wang min. ',' Woman ','20',' Department of Computer Science '),
('9521101',' Zhang Li ',' male ','22',' Information Department '),
('9521102',' Wu Bin ',' Woman ','21',' Information Department '),
('9521103',' Zhang Hai ',' male ','20',' Information Department '),
('9531101',' Qian Xiaoping ',' Woman ','18',' Department of mathematics '),
('9531102',' Wang Dali ',' male ','19',' Department of mathematics ');
##########  Insert course schedule （Course） Corresponding data  ##########
insert into course values('C01',' Computer culture ','3','1','41'),
('C02','VB','2','3','61'),
('C03',' computer network ','4','7','14'),
('C04',' Database foundation ','6','6','24'),
('C05',' Advanced mathematics ','8','2','19'),
('C06',' data structure ','5','4','55');
##########  Insert student course selection table （DBSC） Corresponding data  ##########
insert into dbsc values('1','9512101','c01','90',' Compulsory '),
('2','9512101','c02','86',' Elective '),
('3','9512101','c06','45',' Compulsory '),
('4','9512102','c02','78',' Elective '),
('5','9512102','c04','66',' Compulsory '),
('6','9521102','c01','82',' Elective '),
('7','9521102','c02','75',' Elective '),
('8','9521102','c04','92',' Compulsory '),
('9','9521102','c05','50',' Compulsory '),
('10','9521103','c02','68',' Elective '),
('11','9521103','c06','56',' Compulsory '),
('12','9531101','c01','80',' Elective '),
('13','9531101','c05','95',' Compulsory '),
('14','9531102','c05','85',' Compulsory ');
########## End ##########


##########  Query table data  ##########
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM dbsc;

The first 2 Turn off ： Simple query

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


select Sname,Sdept from student where Sdept = ' Department of Computer Science ';
select Sno from dbsc where Grade < 60; 
select Sname,Sdept,Sage from student where Sage >= 20 and Sage < 23 and Sdept =  ' Information Department ';
select Sno,Grade from dbsc where Cno = 'c02';
select count(*) from student ;


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

The first 3 Turn off ： Refine Query

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

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

select Sname,Ssex,Sdept from student where Sdept = ' Department of Computer Science ' or Sdept = ' Department of mathematics ' or Sdept = ' Information Department ';

select Cno,count(*) from dbsc where istec =' Elective ' 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=' Department of Computer Science ';


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

The first 4 Turn off ： Complex queries

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

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

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 = ' Information Department ' and dbsc.Cno = 'c02' and istec = ' Elective ';

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