数据库经典题目 1-10

 

 

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT Student.*,A.s_score AS 01score,B.s_score AS 02score FROM Student,
(SELECT s_score,s_id FROM score WHERE c_id='01')A, -- 分别查找包含01，02分数作为两个表
(SELECT s_score,s_id FROM score WHERE c_id='02')B
WHERE A.s_score>B.s_score AND A.s_id=B.s_id AND A.s_id=Student.`s_id`;

-- 2、查询平均成绩大于60分的学生的学号和平均成绩
SELECT Student.`s_name`,Student.s_id,AVG(s_score) 
FROM Student JOIN score ON Student.`s_id`=score.`s_id`
GROUP BY Student.s_id -- 查询平均成绩大于60的，将id分组
HAVING AVG(s_score)>60 -- having条件大于60

-- 3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)left/right join on
SELECT ROUND(AVG(s_score)),Student.`s_id`,Student.`s_name`
FROM Student LEFT JOIN  score ON
Student.`s_id`=score.`s_id`
GROUP BY Student.`s_id`
HAVING ROUND(AVG(s_score))<60 OR ROUND(AVG(s_score)) IS NULL

-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩显示null)
SELECT student.s_id,s_name,COUNT(*),SUM(s_score)
FROM student LEFT JOIN score ON
score.`s_id`=student.`s_id`
GROUP BY student.s_id


-- 5.查询姓“李”的老师的个数
SELECT COUNT(*),t_name
FROM teacher
WHERE t_name LIKE "李%"

-- 6.查询没学过“张三”老师课的学生的学号、姓名
SELECT s_name,student.`s_id`
FROM student
WHERE s_name NOT IN
	(SELECT s_name FROM student,score,course,teacher
	WHERE student.`s_id`=score.`s_id` AND score.`c_id`=course.`c_id`
	AND course.`t_id`=teacher.`t_id` AND t_name="张三")

-- 7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
-- 先查询出学过1和2的学生学号，再用in嵌套子查询
SELECT s_id,s_name FROM student
WHERE s_id IN
	(SELECT s_id FROM score
	WHERE c_id='01' OR c_id='02'
	GROUP BY s_id
	HAVING COUNT(c_id)>=2)

-- 8.查询课程编号为“02”的总成绩
SELECT SUM(s_score) 
FROM score
WHERE c_id='02'
	
-- 9.查询没有学全所有课的学生的学号、姓名	
SELECT s_name,student.`s_id`
FROM student JOIN score ON
student.`s_id`=score.`s_id`
GROUP BY score.`s_id`
HAVING COUNT(c_id)<(
	SELECT COUNT(DISTINCT c_id )FROM score) -- 先查出一共由几门课
	
-- 10.查询至少有一门课与学号为“1001”的学生所学课程相同的学生的学号和姓名
SELECT DISTINCT(student.`s_id`),s_name
FROM score JOIN student ON
student.`s_id`=score.`s_id`
WHERE c_id IN
	(
	SELECT c_id FROM score WHERE s_id='1001'
	)
	AND student.s_id!='1001'

复试加油加油！