mysql sql query case

1. Required table

The student TABLE tbl [student (ID, sname) ID is the primary key

Curriculum (ID, CNAME) ID is the primary key

Course (id, sid,cid,score)

 

2. Query cases

##Query the name of the student whose course id=1

select s.sname from tbl_student s INNER JOIN  tbl_stu_course sc on s.id=sc.sid where sc.cid=1;

##Query the user name of the student without course id=4

select id,sname from tbl_student where id not in (select sid from tbl_stu_course where cid =4 GROUP BY sid);

##Query students who are not full of courses

select sname from tbl_student where id IN
(select sid from tbl_stu_course group by sid having count(*) < (select count(*) from tbl_course ))

##Query students of all courses

SELECT sname FROM tbl_student WHERE NOT EXISTS(
     SELECT *FROM tbl_course WHERE NOT EXISTS(
       SELECT * FROM tbl_stu_course WHERE sid = tbl_student.id AND cid = tbl_course.id
)
)
select a.sid,b.sname,a.cnt as ctotal from 
(select sid,count(1) as cnt from tbl_stu_course group by sid
         having count(1)=(select count(1) from tbl_course)) a,
tbl_student b where a.sid=b.id;

 

##Check which courses are not selected for student id=3

select coursename from tbl_course  where not exists 
(select * from tbl_stu_course where tbl_course.id = cid and sid = 3)

##Check which courses are selected for student id=3
 

select cname from tbl_course  where  exists 
(select * from tbl_stu_course where tbl_course.id = cid and sid = 3)

###For each student who scores more than 60 points in each course, the score will be reduced by 10%, and for those who score 30 to 50 points, the score will be doubled

select cid,score,(case when score>=60 then score*0.9 
  when score>=30 and score<50 then score*2 
  else score end) as newscore from tbl_stu_course

##Query the names of students who only choose one course

select id,sname from tbl_student where id IN (
select sc.sid from tbl_stu_course sc
 INNER JOIN
 tbl_course c on sc.cid=c.id   GROUP BY sc.sid HAVING count(*)=1
)

##Query the average score of each course

select c.id,c.coursename,sum(tsc.score)/count(tsc.sid) as avgscore from tbl_course c
INNER JOIN tbl_stu_course tsc on c.id=tsc.cid  GROUP BY cid

##Query the total score of each student's course selection
 

select s.id,s.sname,sum(t.score) from tbl_stu_course t
INNER JOIN tbl_student s where s.id=t.sid
GROUP BY sid

##Count the total score of students (the total score of no selected course is 0)
 

select s.id,s.sname,IFNULL(sum(t.score),0) as total from tbl_student s
LEFT JOIN tbl_stu_course t on s.id=t.sid
GROUP BY sid

##Score to rank

select cid,score,(case when score<60 then 'E'
                when score>=60 and score<70 then 'C'
                when score>=70 and score<80 then 'B'
                when score>=80 then 'A'
                else 'N' end ) as class from tbl_stu_course

###Query student's scores in each subject and turn the row into column

select sname,
    MAX(CASE cname WHEN 'Mathematics' THEN score ELSE 0 END ) Mathematics,
    MAX(CASE cname WHEN 'Chinese' THEN score ELSE 0 END ) Chinese,
    MAX(CASE cname WHEN 'English?' THEN score ELSE 0 END ) English?,
    MAX(CASE cname WHEN 'Sports' THEN score ELSE 0 END ) Sports
from (
select s.sname,c.coursename,t.score from tbl_student s 
INNER JOIN tbl_stu_course t on s.id=t.sid
INNER JOIN tbl_course c on t.cid=c.id
) as m group by sname 

MAX function is used to get the maximum value in group by group

##Query the highest score of each course

select cname,sname,b.sid,score
from tbl_student a,tbl_stu_course b,tbl_course c
where a.id =b.sid and b.cid =c.id and 
score=(select MAX(score) from tbl_stu_course where cid =c.id )

##Query the student whose course id=1 and the student's course exceeds the average score

select cname,sname,b.cid,score
from tbl_student a,tbl_stu_course b,tbl_course c
where a.id =b.sid and b.cid =c.id and c.id=1 and 
score>(select avg(score) from  tbl_stu_course where cid =1)

 

Posted on Sun, 03 Nov 2019 03:03:43 -0800 by daphreeek