Question

SQL 语句查询所有参加考试的学生，从Stu表中和Sco表中

这个是表的结构，另外我把其他几问也告诉大家，大家帮帮我！！
(1) 查询stu表中年龄的平均值，最大值，最小值。
(2) 查询stu表中学生人数。
(3) 查询stu表中李姓学生的信息。（
(4) 查询stu表中李姓学生的姓名和笔试成绩
(5) 查询比‘李斯文’年龄大的学员信息（
(6) 查询实验成绩及格的学生姓名
(7) 查询所有参加考试的学生信息
(8) 查询所有没参加考试的学生信息

Answer 1

select * from score(成绩表) where stuno(考号) in (select stuno from student)
思路是这样的，学生表中有的考号在成绩表中出现，就叫做参加考试了,更详细的就是说，机试和笔试成绩都不为null

Answer 2

select * from stu,sco and stu.stuNo=sco.stuNo
1.select avg(stuAg), max(stuAg),min(stuAg) from stu
2.select count(*) from stu
3.select * from stu where stuName like ‘李%’
4。select stuName writtenExam from stu,sco where stuName like ‘李%’and stu.stuNo=sco.stuNo
5.select * from stu where (stuAge>(select lstuAge from stu where stuName='李斯文' ))//试一下，应该可以
6.select stuName from stu,sco where LabExam>=60 and stu.stuNo=sco.stuNo
7.select * from stu,sco where stu.stuNo=sco.stuNo
8.select * from stu,sco where not(stu.stuNo=sco.stuNo)

Answer 3

1
select avg(stuAge),max(stuAge),min(stuAge) from Stu

2
select count(stuNo) from Stu

3
select * from Stu where stuName='李%'

4
select Stu.stuName,Sco.virttenExam from Stu,Sco where Stu.stuNo=Sco.stuNo and Stu.stuName='李%'

5
select * from Stu where stuAge>(select stuAge from Stu where stuName='李斯文'

6
select Stu.stuName from Stu,Sco where Sco.LabExam>=60

7
select Stu.stuName,Stu.stuNo,Stu.stuSex,Stu.stuSeat,Stu.stuAddress from Sco,Stu
where Sco.stuNO=Stu.stuNo and (vrittenExam is null or LabExam is null)

8
select Stu.stuName,Stu.stuNo,Stu.stuSex,Stu.stuSeat,Stu.stuAddress from Sco,Stu
where Sco.stuNO=Stu.stuNo and vrittenExam is null and LabExam is null

Answer 4

select sname
from stu,sc
where stu.sno=sc.sno and grade is not null