-- 1.查询密码长度不足6位的学员编号、姓名、密码、年级名
SELECT stu_id, stu_name, b.`gradeName`,PASSWORD
FROM student a,grade b
WHERE LENGTH(PASSWORD)<6
AND a.`gradeId`=b.`gradeId`;
-- 2.查找郑环环所有考试的成绩(科目名称、考试成绩)
-- 重复数据太多,取均值好了
SELECT b.`sbj_name`, ROUND(AVG(a.`score`),2) score
FROM result a , `subject` b
WHERE a.`sbj_no`=b.`sbj_no`
AND a.`stu_id` IN
(SELECT stu_id
FROM student
WHERE stu_name='郑环环')
GROUP BY b.`sbj_name`;
-- 3.使用表连接方式查询所有U1年级学员学号、姓名及年级名
SELECT a.`stu_id`,a.`stu_name`,b.`gradeName`
FROM student a
JOIN grade b
ON a.`gradeId`=b.`gradeId`
AND b.`gradeName`='大一';
-- 4.使用子查询方式查询所有U1年级学员学号、姓名、年级ID
SELECT a.`stu_id`,a.`stu_name`,a.`gradeId`
FROM student a
WHERE gradeId IN
(SELECT gradeId
FROM grade
WHERE gradeName='大一');
-- 5.使用表连接方式查询“Java”课程至少一次考试刚好等于60分的学生姓名
SELECT t1.`stu_name`
FROM student t1
JOIN result t2
ON t1.`stu_id`=t2.stu_id
AND t2.`score`=60
JOIN `subject`t3
ON t2.`sbj_no`=t3.`sbj_no`
AND t3.sbj_name='java';
-- 6.使用子查询方式查询“Java”课程至少一次考试刚好等于60分的学生
SELECT `stu_name`
FROM student
WHERE stu_id IN
(SELECT stu_id
FROM result
WHERE score=60
AND sbj_no IN
(SELECT sbj_no
FROM `subject`
WHERE sbj_name='java'));
-- 7.使用子查询方式+表连接方式混合查询“Java”课程至少一次考试刚好等于60分的学生
SELECT stu_name
FROM student
WHERE stu_id IN
(SELECT stu_id
FROM result a
JOIN `subject` b
ON a.`sbj_no`=b.`sbj_no`
AND b.sbj_name='java'
AND a.score=60);