数据库查询练习及答案
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；(成绩表自连接)
SELECT * FROM tblScore ts1, tblScore ts2
WHERE ts1.stuid = ts2.stuid AND ts1.courseid = '001' AND ts2.courseid = '002'
AND ts1.score > ts2.score
--2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT stu.stuid, AVG(score.score)FROM tblstudent stu, tblscore score
WHERE stu.stuid = score.stuid GROUP BY stu.stuid
HAVING AVG(score.score) > 60
--3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT stu.stuid, stu.stuname,COUNT(score.courseid),SUM(score.score)
FROM tblstudent stu, tblCourse course, tblScore score
WHERE stu.stuid = score.stuid AND course.courseid = score.courseid
GROUP BY stu.stuid,stu.stuname ORDER BY stuid
--4、查询姓“李”的老师的个数；
SELECT COUNT(1) FROM tblteacher t
WHERE t.teaname LIKE '李%'
SELECT * FROM tblstudent
SELECT * FROM tblCourse
SELECT * FROM tblScore
SELECT * FROM tblteacher
--5、查询没学过“叶平”老师课的同学的学号、姓名；
SELECT stu2.stuid ,stu2.stuname FROM tblstudent stu2 WHERE stu2.stuid NOT IN
(SELECT DISTINCT stu.stuid FROM tblstudent stu, tblCourse course,tblteacher tea,tblScore score
WHERE stu.stuid = score.stuid AND course.courseid = score.courseid
AND tea.teaid = course.teaid AND tea.teaname = '叶平' )
SELECT * FROM tblstudent
SELECT * FROM tblCourse
SELECT * FROM tblScore
SELECT * FROM tblteacher
--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT stu.stuid,stu.stuname from tblstudent stu,tblScore score1, tblScore score2
WHERE score1.stuid = score2.stuid AND score1.courseid = '001'
AND score2.courseid = '002' AND stu.stuid = score1.stuid
SELECT * FROM tblstudent
SELECT * FROM tblCourse
SELECT * FROM tblScore
SELECT * FROM tblteacher
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT s2.stuname,s2.stuid FROM(
SELECT COUNT( s1.stuid) num,s1.stuname,s1.stuid FROM (
SELECT stu.* FROM tblstudent stu, tblScore score
WHERE stu.stuid = score.stuid AND score.courseid IN (
SELECT course.courseid FROM tblCourse course, tblteacher tea
WHERE course.teaid = tea.teaid
AND tea.teaname = '叶平' ) )s1 GROUP BY s1.stuid,s1.stuname )s2
WHERE s2.num =
(
SELECT COUNT(1) FROM tblteacher tea,tblCourse course
WHERE tea.teaid = course.teaid AND tea.teaname = '叶平'
)
--叶平老师授课的 总数
SELECT COUNT(1) FROM tblteacher tea,tblCourse course
WHERE tea.teaid = course.teaid AND tea.teaname = '叶平'
--学习过叶平老师课的学员的学号，名字，学习叶平老师的课程数
SELECT COUNT(s1.stuid) num,s1.stuname,s1.stuid FROM(
SELECT stu.* FROM tblstudent stu, tblScore score
WHERE stu.stuid = score.stuid AND score.courseid IN (
SELECT course.courseid FR
OM tblCourse course, tblteacher tea
WHERE course.teaid = tea.teaid
AND tea.teaname = '叶平'))s1 GROUP BY s1.stuid,s1.stuname
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT DISTINCT stu.stuid, stu.stuname FROM tblScore score1, tblScore score2, tblstudent stu
WHERE score1.stuid = score2.stuid AND stu.stuid = score1.stuid
AND score1.courseid = '001' AND score2.courseid = '002'
AND score1.score > score2.score
--9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT stu.stuid FROM tblScore score, tblstudent stu
WHERE stu.stuid = score.stuid GROUP BY stu.stuid ,stu.stuname
HAVING MAX(score.score) < 60
--10、查询没有学全所有课的同学的学号、姓名；
SELECT s1.stuid,s1.stuname FROM (
SELECT count(score.courseid) num,stu.stuid,stu.stuname
FROM tblstudent stu LEFT JOIN tblScore score
ON stu.stuid = score.stuid
GROUP BY stu.stuid,stu.stuname )s1
WHERE s1.num < (
SELECT COUNT(1) FROM tblCourse course )
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
SELECT DISTINCT stu.stuid,stu.stuname,score2.courseid FROM tblstudent stu
INNER JOIN tblScore score2 ON stu.stuid = score2.stuid
WHERE score2.courseid IN (
SELECT score.courseid FROM tblScore score WHERE score.stuid = '1001')
--12、查询学过学号为“1001”同学所有课程的其他同学学号和姓名；
SELECT stu.stuid,stu.stuname,COUNT(score2.courseid) FROM tblstudent stu INNER JOIN tblscore score2
ON stu.stuid = score2.stuid WHERE score2.courseid IN (
SELECT courseid FROM tblscore score WHERE score.stuid = '1001' )
GROUP BY stu.stuid,stu.stuid,stu.stuname
HAVING COUNT(score2.courseid) = (
SELECT COUNT(1) FROM tblscore score WHERE score.stuid = '1001' )
--外层查询同学学号和姓名，其中符合内循环的条件
SELECT stu.stuid, stu.stuname FROM tblstudent stu
WHERE EXISTS (
--内层条件：在分数表通过课程编号自连接，通过学号分组，
--筛选出 学生1001选修的课，不是学生1001选修的课剔除掉
--最后使用计数函数，再次筛选出其中字表的课程数和1001选修的课相同的 学生学号
SELECT t1.stuid FROM tblscore t1
INNER JOIN tblscore t2
ON t1.courseid = t2.courseid
WHERE t1.stuid <> 1001 AND t2.stuid = 1001
AND stu.stuid = t1.stuid
GROUP BY t1.stuid
HAVING COUNT(1) >= (SELECT COUNT(1) FROM tblscore WHERE stuid = 1001 )
)
SELECT stuid FROM tblscore t2
WHERE courseid IN
(SELECT courseid FROM tblscore t1
WHERE stuid = 1001)
AND t2.stuid <> 1001
GROUP BY stuid
HAVING COUNT(cours
eid) = (SELECT COUNT(1) FROM tblscore WHERE stuid = 1001)
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
-- “叶平”老师教的课课程号
SELECT courseid FROM tblCourse course ,tblteacher tea
WHERE course.teaid = tea.teaid AND tea.teaname = '叶平'
-- “叶平”老师教的课课程号的平均成绩
SELECT AVG(score2.score) FROM tblScore score2 WHERE score2.courseid IN (
SELECT courseid FROM tblCourse course ,tblteacher tea
WHERE course.teaid = tea.teaid AND tea.teaname = '叶平' )
GROUP BY score2.courseid
UPDATE tblscore t1
SET t1.score = (
--平均成绩
SELECT AVG(score) FROM tblscore t2 WHERE EXISTS (
SELECT DISTINCT sc.courseid FROM tblscore sc, tblcourse c, tblteacher tc
WHERE tc.teaname = '叶平' AND sc.courseid = c.courseid
AND c.teaid = tc.teaid
AND t2.courseid = sc.courseid
))
WHERE EXISTS (
--查找到叶平老师的课程号
SELECT sc.courseid FROM tblscore sc, tblcourse c, tblteacher tc
WHERE tc.teaname = '叶平' AND sc.courseid = c.courseid
AND c.teaid = tc.teaid
AND t1.courseid = sc.courseid
)
UPDATE tblscore t1
SET t1.score = (
--平均成绩
SELECT AVG(score) FROM tblscore t2 WHERE EXISTS (
SELECT DISTINCT sc.courseid FROM tblscore sc, tblcourse c, tblteacher tc
WHERE tc.teaname = '叶平' AND sc.courseid = c.courseid
AND c.teaid = tc.teaid
AND t2.courseid = sc.courseid
))
WHERE t1.courseid IN (
--查找到叶平老师的课程号
SELECT sc.courseid FROM tblscore sc, tblcourse c, tblteacher tc
WHERE tc.teaname = '叶平' AND sc.courseid = c.courseid
AND c.teaid = tc.teaid
)
--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
--最后指向性select语句，所以select选择语句中的别名无效
SELECT stu.stuid,stu.stuname,COUNT(score.courseid) num FROM tblstudent stu
INNER JOIN tblScore score ON stu.stuid = score.stuid
AND score.courseid IN (
SELECT score.courseid FROM tblScore score WHERE score.stuid = '1002')
GROUP BY stu.stuid,stu.stuname
HAVING COUNT(score.courseid) = (
SELECT COUNT(1) num FROM tblScore score WHERE score.stuid = '1002'
)
SELECT stu.stuid ,stu.stuname FROM tblstudent stu WHERE EXISTS(
SELECT sc2.stuid FROM tblscore sc2 WHERE sc2.courseid IN (
--至少包括这些1002学过的课程
SELECT DISTINCT courseid FROM tblscore sc1 WHERE sc1.stuid = 1002)
AND stuid <> 1002
AND stu.stuid = sc2.stuid
GROUP BY stuid
HAVING COUNT(sc2.courseid) = (SELECT COUNT(1) FROM tblscore WHERE stuid = 1002)
)
AND EXISTS (
--课程数目相等，排除大于的
SELECT sc3.stuid FROM tblscore sc3
WHERE stu.stuid = sc3.stuid
GROUP BY stuid HAVING COUNT(sc3.courseid) =
(SELECT Count(1) FROM tblscore WHERE stuid = 1002 GROUP BY stuid )
)
--15、删除学习“叶平”老师课的SC表记录；
--“叶平”老师所有课的课程ID
SELECT courseid FROM tblCourse course INNER JOIN tblteacher tea
ON course.teaid = tea.teaid AND tea.teaname = '叶平'
DELETE FROM tblscore WHERE courseid IN (
--找到叶平老师的ID
SELECT DISTINCT sc.courseid FROM tblscore sc, tblcourse cu, tblteacher tc
WHERE sc.courseid = cu.courseid AND cu.teaid = tc.teaid
AND tc.teaname = '叶平')