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;