数据库练习2

时间:2023-02-13 21:58:43

数据库练习2(基于数据库练习1)

1.查询“计算机导论”课程比“操作系统”课程成绩高的所有学生的学号;
  SELECT sno,degree FROM score WHERE cno =(SELECT cno FROM course WHERE cname ='计算机导论' )
  AND  degree > ALL(SELECT degree FROM score WHERE cno=(SELECT cno FROM course WHERE cname ='操作系统') GROUP BY degree )
2、查询所有同学的学号、姓名、选课数、总成绩;
SELECT student.sno ,student.sname,COUNT(score.cno),SUM(score.degree) FROM
 score LEFT JOIN student ON student.sno=score.sno

 GROUP BY score.sno LIMIT 5;

3、查询没学过“刘冰”老师课的同学的学号、姓名;
SELECT t3.sno,t3.sname FROM student t3 ,score t4 
WHERE t3.sno=t4.sno AND t4.cno 
NOT IN(SELECT t1.cno FROM course t1,teacher t2 WHERE  t1.tno=t2.tno AND t2.tname='刘冰')

GROUP BY t3.sno

4、查询学过“6-166”并且也学过编号“3-105”课程的同学的学号、姓名;                          
SELECT  a.sno,a.sname FROM (SELECT  student.sno,student.sname FROM student ,score WHERE student.sno = score.sno
 AND score.cno='6-166') a ,(SELECT  student.sno,student.sname FROM student ,score WHERE student.sno = score.sno
 AND score.cno='3-245' )b WHERE a.sno = a.sno GROUP BY sno

SELECT a.sno,a.sname FROM student a,(SELECT sno FROM score  
WHERE cno='6-166' OR cno='3-105' GROUP BY sno HAVING COUNT(1)>=2) b WHERE a.sno=b.sno 

5.查询至少有一门课与学号为“103”的同学所学相同的同学的学号和姓名;

SELECT a.sno,a.sname FROM student a,
(SELECT DISTINCT sno FROM score WHERE sno<>'103' AND cno IN (SELECT cno FROM score WHERE sno='103')
) b 
WHERE a.sno =b.sno GROUP BY a.sno



练习所用,若有不妥,还请指教!!