MySQL(11)-Subqueries

Introduction to subqueries

Use subqueries as derived tables

select sname,cname,score from (select sno,sname from student) as k,sc,course where k.sno=sc.sno and sc.cno=course.cno

select sname,cname,score from student as k,sc,course where k.sno=sc.sno and sc.cno=course.cno

Use subqueries as expressions

select sno,score,(select avg(score) from sc),score-(select avg(score) from sc) from sc

Use subqueries to correlate data

Calculate a correlated subquery

Simulate JOIN clause

Simulate HAVING clause

Use EXISTS and NOT EXISTS clauses

Recommended actions

Use subqueries to complete the following tasks

select sname,cname from student,course where 60 <(select score from sc where sc.sno=student.sno and sc.cno=course.cno)

select student.sno,sname from student where sno in

(select sno from sc where score in

(select max(score) from sc where cno in

(select cno from course where cname='data structure')) and cno in

(select cno from course where cname='data structure'))

select student.sno,sname from student where sno in (select sno from sc where score in (select max(score) from sc where cno in (select cno from course where cname='data structure')) and cno in (select cno from course where cname='data structure'))

union

select student.sno,sname from student where sno in (select sno from sc where score in (select min(score) from sc where cno in (select cno from course where cname='data structure')) and cno in (select cno from course where cname='data structure'))

select sname,cname,score from (select sno,sname from student) as k,sc,course where k.sno=sc.sno and sc.cno=course.cno ORDER BY score desc limit 1

select t.sname,t.cname,t.score from (select sc.sno,sname,cname,score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno) as t ORDER BY t.score desc limit 1

select cno from course where exists (select * from teacher where course.tno=teacher.tno and tname='Ye Ping')

select cno from course where exists (select * from teacher where course.tno=teacher.tno and tname='陈国华')

select sname,cname,score from (select sno,sname from student) as k,sc,course where k.sno=sc.sno and sc.cno=course.cno ORDER BY score desc limit 1

select sno,sname,ssex,sage from student where sno in (select sno from sc where score in (select max(score) from sc))

select tname from teacher

where tno in(select tno from course where cno in (select cno from sc where sc.cno=course.cno GROUP BY sc.cno ORDER BY score))

select tno,tname from teacher where tno in (select tno from course where cno in (select cno from sc where score in (select min(score) from sc)))

select GROUP_CONCAT(tname) from teacher where tno in (select tno from course where cno in (select cno from sc where sno='1001'))

  • Query the information of the teacher of a student with a given student ID, and display the names of all teachers in one line at the same time
  • Query the information of the teacher of the student with the lowest score in the database
  • Query the information of the student with the highest score in the database
  • Query whether the given teacher name teaches a course
  • Use the method of subquery derivation table to find the name, course name and score of all students with the highest score
  • Query the highest and lowest scores of a given course name, and their corresponding student names
  • Query the name of the failed student and the name of the course