Mysql exercise questions,

Mysql exercise questions,

Table Structure and data: http://www.cnblogs.com/wupeiqi/articles/5748496.html

Question:
2. query the student ID of all students whose score is higher than that of the "physical" course;
1)
Select A. student_id, biological score, physical score from
(Select student_id, num as biological score
From score
Where course_id = (select cid from course where cname = 'bio') as
Left join
(Select student_id, num as physical score
From score
Where course_id = (select cid from course where cname = 'physical ') as B
On A. student_id = B. student_id where biological score> if (isnull (physical score), 0, physical score );
2)
Select A. student_id, biological score, physical score from
(Select student_id, num as biological score from score left join course on score. course_id = course. cid where course. cname = 'bio') as
Left join
(Select student_id, num as physical score from score left join course on score. course_id = course. cid where course. cname = 'physical ') as B
On A. student_id = B. student_id where biological score> if (isnull (physical score), 0, physical score );

3. query the student ID and average score of students whose average score is greater than 60;
Select student_id, avg (num) as average score from score group by student_id having average score> 60;


4. query the student ID, name, number of course selections, and total score of all students;
Select student. sid, sname, count (student_id), sum (num)
From score right join student
On student. sid = student_id
Group by student_id;


5. query the number of teachers surnamed "Li;
Select count (*) from teacher where tname like 'Lee % ';

6. query the student ID and name of the student who has not learned the course "instructor Li Ping;
Select distinct student. sid, sname from student where sid not in (
Select distinct student_id
From score
Where course_id in (
Select cid
From course left join teacher
On course. teacher_id = teacher. tid
Where tname = 'instructor Liping ')
);

7. query the student ID and name of the student who has learned the course 001 and has also learned the course 002;
1)
Select A. sid, A. sname from
(Select student. sid, sname from score right join student
On student. sid = student_id
Where course_id = '001') as
Inner join
(Select student. sid, sname from score right join student
On student. sid = student_id
Where course_id = '002 ') as B
On A. sid = B. sid
2)
Select student_id, sname from
(Select student_id, course_id from score where course_id = 1 or course_id = 2) as B
Left join student on B. student_id = student. sid group by student_id HAVING count (student_id)> 1

8. query the student ID and name of all students who have learned the courses taught by instructor ye Ping;
Select student. sid, sname from score right join student
On student. sid = student_id
Where course_id in (
Select cid from course left join teacher
On course. teacher_id = teacher. tid
Where tname = 'instructor Liping'
) Group by student. sid HAVING count (student. sid) = (
Select count (cid) from course left join teacher
On course. teacher_id = teacher. tid
Where tname = 'instructor Liping'
);

9. query the student ID and name of all students whose score is lower than the score of course no. "002" than the course No. "001;
Select sname, sid from student inner join (
Select A. student_id from
(Select student_id, num as s1 from score left join course on score. course_id = course. cid where course. cid = '001') as
Left join
(Select student_id, num as s2 from score left join course on score. course_id = course. cid where course. cid = '002') as B
On A. student_id = B. student_id where s1> if (isnull (s2), 0, s2)
) As C
On C. student_id = student. sid;

10. query the student ID and name of a student with a course score less than 60;
Select distinct student. sid, sname from student left join score
On student. sid = score. student_id
Where num <60;

11. query the student ID and name of the student who has not completed all the courses;
Select student. sid, sname from score right join student
On student. sid = score. student_id
Group by student_id
Having count (student_id )! = (
Select count (*) from course
);

12. query the student ID and name of at least one course with the student ID "001;
Select student. sid, sname, count (sname) from score right join student
On student. sid = student_id
Where student_id! = '001' and course_id in (
Select course_id from score where student_id = '001'
) Group by student_id;

13. query the student ID and name of at least one student whose student ID is 001;
Select student. sid, sname from score right join student
On student. sid = student_id
Where student_id! = '001' and course_id in (
Select course_id from score where student_id = '001'
) Group by student_id
Having count (sname)> = (
Select count (*) from score
Where student_id = '001 ');

14. query the student ID and name of other students whose courses are identical to those of "002;

Select student. sid, sname from score right join student
On student. sid = student_id where student_id in (
Select student_id from score where student_id! = 2 group by student_id HAVING count (course_id) = (select count (course_id) from score where student_id = 2 ))
And course_id in (
Select course_id from score where student_id = '002'
) Group by student_id
Having count (course_id) = (
Select count (course_id) from score
Where student_id = '002 ');

15. Delete the score Table record for learning "ye ping;
Delete from score where course_id in (
Select cid from course left join teacher on course. teacher_id = tid where tname = 'instructor Liping'
)

16. insert some records into the SC table. These records must meet the following requirements: ① students who have not passed the "002" course number; ② Insert the average score of the "002" course;
Insert into score (student_id, course_id, num) select sid, 2, (select avg (num) from score where course_id = 2)
From student where sid not in (
Select student_id from score where course_id = 2
)

17. Show the four course scores of "biology", "physics", "Sports", and "art" for all students based on the average score, as shown below: student ID, biology, physics, sports, fine arts, number of valid courses, effective average score; (this query syntax is not used)
Select student_id,
(Select num from score s left join course c on s. course_id = c. cid where cname = 'bio' and SC. student_id = s. student_id) as biological score,
(Select num from score s left join course c on s. course_id = c. cid where cname = 'physical 'and SC. student_id = s. student_id) as physical score,
(Select num from score s left join course c on s. course_id = c. cid where cname = 'Sports 'and SC. student_id = s. student_id) as sports score,
(Select num from score s left join course c on s. course_id = c. cid where cname = 'artbe' and SC. student_id = s. student_id) as art score,
Count (course_id), avg (num)
From score SC
Group by student_id
Order by avg (num );

18. query the highest score and lowest score of each subject. The score is displayed as follows: course ID, highest score, and lowest score;
Select course_id, max (num), min (num) from score group by course_id;

19. The average score of each subject ranges from low to high and the percentage of pass rate from high to low;
1)
Select avg (num), score. course_id, number of students, number of passing students, (Number of passing students/number of students * 100) as pass rate from score left join
(Select course_id, count (student_id) as number of students from score group by course_id order by course_id) as
On score. course_id = A. course_id
Left join
(Select course_id, count (student_id) as course pass count from score where num> = 60 group by course_id order by course_id) as B
On A. course_id = B. course_id
Group by score. course_id
Having
Order by avg (num), pass rate desc;
2)
Select course_id, avg (num) as avgnum, sum (case when score. num> 60 then 1 else 0 END)/count (1) x 100 as percent from score group by course_id order by avgnum asc, percent desc;

20. The average score of the course is displayed from high to low (real instructor );
Select tname, avg (average course score) from
(Select tname, c. cid from teacher t right join course c on t. tid = c. teacher_id) as
Left join
(Select avg (num) as average course score, course_id from score group by course_id order by avg (num) desc) as B
On A. cid = B. course_id
Group by tname
Order by avg (average course score) desc;

21. query the records of the top five scores of each subject: (parallel scores are not considered );
Select distinct cname, course_id,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 0, 1) as first_num,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 1, 1) as second_num,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 2, 1) as third_num,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 3, 1) as fourth_num,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 4, 1) as sort th_num
From score s1 left join course c on c. cid = s1.course _ id;

22. query the number of students selected for each course;
Select course_id, count (student_id) from score group by course_id;

23. Check the student ID and name of all students who have selected only one course;
Select student_id, sname from student st left join score SC on st. sid = SC. student_id
Group by student_id
Having count (course_id) = 1;

27. query the average scores of each course. The results are sorted in ascending order based on the average scores. The average scores are the same and the course numbers are sorted in descending order;
Select course_id, avg (if (isnull (num), 0, num) from score group by course_id order by avg (num), course_id desc;

28. query the student ID, name, and average score of all students whose average score is greater than 85;
Select student_id, sname, avg (num) from score SC left join student st on SC. student_id = st. sid group by student_id;

29. query the names and scores of students whose course names are "biology" and whose scores are less than 60;
Select sname, num from score SC left join student st on SC. student_id = st. sid
Left join course c on c. cid = SC. course_id
Where c. cname = 'bio' and num <60;

31. Number of students selected for the course
Select count (distinct student_id) from score

32. query the names of the students with the highest scores and their scores of the students who take the course taught by the "Teacher Zhang Lei;
Select sname, max (num) from score SC
Left join student st on SC. student_id = st. sid
Where course_id in (
Select cid from course c
Left join teacher t on c. teacher_id = t. tid
Where tname = 'Mr. Zhang Lei'
);

33. query each course and the number of optional students;
Select cname, count (student_id) from score SC
Left join course c on c. cid = SC. course_id
Group by course_id;

34. query the student's student ID, course number, and Student Score for different courses with the same score;
Select student_id, course_id, num from score where sid in (
Select s1.sid from score s1
Inner join score s2 on s1.num = s2.num and s1.course _ id! = S2.course _ id)

35. query the first two of the best scores of each course;
Select distinct cname, course_id,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 0, 1) as first_num,
(Select num from score as s2 where s2.course _ id = s1.course _ id order by num desc limit 1, 1) as second_num
From score s1 left join course c on c. cid = s1.course _ id;

36. Search for student IDs that take at least two courses;
Select student_id from score SC group by student_id having count (course_id)> 1;

37. query the course number and name of all optional courses for all students;
Select course_id, cname from score SC
Right join course c on c. cid = SC. course_id
Group by course_id
Having count (student_id) = (
Select count (distinct sid) from student
);

38. query the names of students who have not completed any course taught by instructor ye Ping;
Select dictinct sname from score SC
Left join student st on SC. student_id = st. sid
Where student_id not in (
Select student_id from score where course_id in (
Select cid from course c left join teacher t on c. teacher_id = t. tid where tname = 'Mr Zhang Lei'
)
);

39. query the student ID and average score of two or more failed courses;
Select student_id, avg (num) from score where student_id in (
Select student_id from score where num <60 group by student_id having count (course_id)> 1
) Group by student_id;

40. Search for students whose scores of the "004" course are less than 60 in descending order of scores;
Select student_id from score where course_id = 4 and num <60 order by num desc;