SQL专栏——sql语句的基础操作（七）题目练习

grade表

student表

result表

subject表

1.查询密码长度不足6位的学院编号、姓名、密码、年级名

select s.studentno,s.studentname,s.loginpwd,g.gradename
from student s
join grade g
on s.gradeid=g.gradeid
where length(loginpwd)<6;

2.查找郑环环的成绩

select s.subjectname,r.studentresult
from result r
join subject s
on r.subjectid=s.subjectid
where studentno=(select studentno from student where studentname='郑环环');

另一种

select s.subjectname,r.studentresult
from result r
join subject s
on r.subjectid=s.subjectid
join student j
on r.studentno=j.studentno
where studentname='郑环环';

3.使用表连接方式查询所有U1年级学员学号、姓名及年级号

select s.studentno,s.studentname,s.gradeid
from student s
join grade g
on s.gradeid=g.gradeid
 where gradename='U1';

4.使用子查询方式查询所有的U1年级学员学号，姓名、年级ID

select studentno,studentname,gradeid
from student s
where gradeid=(select gradeid from grade where gradename='U1'); 

5.使用表连接方式查询“Java”课程至少一次考试刚好等于60分的学生姓名

SELECT st.StudentName
FROM result re 
JOIN SUBJECT su
ON su.`SubjectId`=re.`SubJectId`
JOIN student st
ON st.`StudentNo`=re.`StudentNo`
WHERE  su.`SubjectName`='java' AND re.StudentResult=60;

6.使用子查询方式查询“Java”课程至少一次考试刚好等于60分的学生姓名

select studentname from student where studentno in (
select studentno from result where subjectid=(
select subjectid from subject where subjectname='Java') and studentresult=60);

7.使用子查询方式+表连接方式混合查询“Java”课程至少一次考试刚好等于60分的学生

SELECT studentname FROM student s
JOIN result r
ON s.studentno=r.studentno
WHERE subjectid=(
SELECT subjectid FROM SUBJECT WHERE subjectname='Java') AND studentresult=60;