Question

SQL——数据库“学生成绩.mdb”的题目

学生表S（学号 SNO、姓名 SNAME、系名 DEPART、性别 SEX、出生日期 DDATE)
课程表C（课程编号 CNO、课程名称 CNAME)
成绩表SC(学号 SNO、课程编号 CNO、成绩GRADE)
====================================================================
1、根据S、C和SC表，查询所有男生的成绩，要求输出SNO、SNAME、CNAME、GRADE
2、根据S表，查询各系男女学生数，要求输出DEPART、SEX、人数
3、根据S、C和SC表，查询“计算机”系所有学生的成绩，要求输出SNO、SNAME、CNAME、GRADE，并按成绩降序排序
4、根据S表，查询各系学生数，要求输出DEPART、人数
5、根据S表，查询所有女学生记录，要求输出全部字段
6、根据C和SC表，查询各课程均分，要求输出CNO、CNAME、均分，并按CNO升序排序
7、根据S表，查询1983-01-01以后出生的学生记录，要求输出全部字段
8、根据S、C和SC表，查询所有不及格（GRADE小于60）学生成绩，要求输出SNO、SNAME、CNAME、GRADE，并按SNO及CNO升序排序
====================================================================
8个问题，请逐条写代码谢谢！
我自己做了1、7、8题，不知道对不对。如果对请按照此格式，逐条给出另外的，谢谢啦！继续提高悬赏ing...
1、
SELECT S.SNO, S.SNAME, C.CNAME, SC.GRADE
FROM (C INNER JOIN SC ON C.CNO = SC.CNO) INNER JOIN S ON SC.SNO = S.SNO
WHERE (((S.SEX)="男"));
7、
SELECT S.SNO, S.SNAME, S.DEPART, S.SEX, S.DDATE
FROM S
WHERE (((S.DDATE)>#1/1/1983#));
8、
SELECT S.SNO, S.SNAME, C.CNAME, SC.GRADE
FROM (C INNER JOIN SC ON C.CNO = SC.CNO) INNER JOIN S ON SC.SNO = S.SNO
WHERE (((SC.GRADE)<60))
ORDER BY S.SNO, C.CNO;
=======================================================================
你们的有些句子提示语法错误。这样，我提供“学生成绩.mdb”下载
你们用Access数据表视图做完，然后选到SQL视图把代码复制出来

Answer 1

很辛苦哟
1.
select s.sno,s.sname,c.cname,sc.grade
from s
join sc on s.sno = sc.sno
join c on sc.cno=c.cno
where s.sex='世升男'

2.
select DEPART,SEX,count(*) 人拍返仿数
from s
group by DEPART,SEX

3.
select s.SNO,s.SNAME,c.CNAME,sc.GRADE
from s
join sc on s.SNO = sc.SNO
join c on sc.CNO=c.CNO
where s.DEPART='计算机'
order by GRADE desc

4.
select DEPART,count(*) 人数
from s
group by DEPART

5.
select *
from s
where s.sex='女'

6.
select CNO,CNAME, 均分袭纤
from c
join (select CNO,avg(sc.GRADE) 均分 from sc group by CNO) aa on c.CNO=aa.CNO
group by CNO asc

7.
select *
from s
where convert(varchar(20),DDATE,23)>'1983-01-01'

8.
select s.SNO,s.SNAME,c.CNAME,sc.GRADE
from s
join sc on s.SNO = sc.SNO
join c on sc.CNO=c.CNO
where sc.GRADE<60
order by c.SNO,c.CNO asc

Answer 2

1 select sno,sname,cname,grade from s,c,sc where s.sno=sc.sno and
s.cno=c.cno
2 select depart ,sex, count(depart) from s group by depart ,sex
3根据S、C和SC表，查询“计算机”裤野败系所有学生的成绩，要求输出SNO、脊冲SNAME、CNAME、GRADE，并按成绩降序胡颤排序
select sno,sname,cname,grade from s,c,sc where s.sno=sc.sno and
s.cno=c.cno and s.depart='计算机' order by sc.grade desc

Answer 3

1 select sno,sname,cname,grade
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno and sex=0
2 select depart,sex,count(*)as'链圆人数'
from s
group by depart,sex
3 select sno,sname,cname,grade
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno and depart='计算机'
4 select depart,count(*)as'棚陵塌人数'
from s
group by depart
5 select *
from s
where sex=1
6 select cno,cname,avg(grade)as'均汪手分'
from c,sc
where c.cno=sc.cno
group by cno
7 select *
from s
where convert(varchar(20),DDATE,23)>'1983-01-01'
8 select sno,sname,cname,grade
from s c, sc
where s.sno=sc.sno and c.cno=sc.cno and grade<60
order by sno,cno

Answer 4

1. select sno,sname,cname,grade from s,c,sc where s.sno=sc.sno and
s.cno=c.cno and s.sex='亩物男迅汪液'

2 . select DEPART,SEX,count(depart) 人数陵败 from s
group by DEPART,SEX

Answer 5

1
select s.sno,s.sname,c.cname,sc.grade From s ,sc ,c
where s.sno=sc.sno and sc.cno=c.cno and s.sex='男'

2
select depart,sex,count(*) from s
group by depart,sex
3
select s.sno,s.sname,c.cname,sc.grade from s,sc ,c
where s.sno=sc.sno and sc.cno=c.cno and s.DEPART='计算机'
order by sc.grade desc
4
select depart,count(*) from s
group by depart
5
select * from s
where sex='女'
6
select c.cno,c.cname,avg(sc.grade) from sc
,c
where sc.cno=c.cno
group by c.cno,c.cname
order by c.cno
7
select * From s
where DDATE>#1983-01-01#
8
select s.sno,s.sname,c.cname,sc.grade from s
,sc
,c
where sc.grade <60 and s.sno=sc.sno and sc.cno=c.cno
order by s.sno,sc.cno