department(dNo,dName,officeRoom,homepage)
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)
sc(sNo,cNo,score,recordDate)
Query
一、单表
(1)查询所有年龄大于等于20岁的学生学号、姓名;
select sNo,sName
from student
where age>=20
(2)查询所有姓钱的男生学号、姓名、出生年份;
select sNo,sName,2018-age
from student
where sName like '钱%' and sex='男';
(3)查询所有学分大于3的课程名称;
select cName
from course
where credit>3
(4)查询所有没有被分配到任何学院的学生姓名;
select sName
from student
where dNo is null
(5)查询所有尚未设置主页的学院名称。
select dName
from department
where homepage is null
二、聚集
(1)查询各个学院的平均年龄;
select d.dname,abc.avgage
from department d,(select d.dno,avg(age)
from student s right outer join department d
on s.dno=d.dno
group by d.dno) as abc(dno, avgage)
where d.dno=abc.dno;
(2)查询每个学生选修课程的平均分;
select student.sName,avg(score)
from student left outer join sc on student.sNo = sc.sNo
group by student.sNo
(3)查询各课程的平均分;
select cName,avg(score)
from course left outer join sc on course.cNo = sc.cNo
group by course.cNo
(4)查询各学院开设的课程门数;
select dName,count(distinct cNo)
from department,course
where department.dNo = course.dNo
group by department.dName
(5)查询各门课程选修人数。
select cName,count(sNo)
from course,sc
where course.cNo = sc.cNo
group by cName
三、多表
(1)查询“信息学院”所有学生学号与姓名;
select sNo,sName
from department,student
where student.dNo = department.dNo and dName = '信息学院'
(2)查询“软件学院”开设的所有课程号与课程名称;
select cNo,cName
from department,course
where department.dNo = course.dNo and dName = '软件学院';
(3)查询与“陈丽”在同一个系的所有学生学号与姓名;
select sNo,sName
from student
where dNo = ( select dNo
from student
where sName = '陈丽' )
(4)查询与“张三”同岁的所有学生学号与姓名;
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)
sc(sNo,cNo,score,recordDate)
Query
一、单表
(1)查询所有年龄大于等于20岁的学生学号、姓名;
select sNo,sName
from student
where age>=20
(2)查询所有姓钱的男生学号、姓名、出生年份;
select sNo,sName,2018-age
from student
where sName like '钱%' and sex='男';
(3)查询所有学分大于3的课程名称;
select cName
from course
where credit>3
(4)查询所有没有被分配到任何学院的学生姓名;
select sName
from student
where dNo is null
(5)查询所有尚未设置主页的学院名称。
select dName
from department
where homepage is null
二、聚集
(1)查询各个学院的平均年龄;
select d.dname,abc.avgage
from department d,(select d.dno,avg(age)
from student s right outer join department d
on s.dno=d.dno
group by d.dno) as abc(dno, avgage)
where d.dno=abc.dno;
(2)查询每个学生选修课程的平均分;
select student.sName,avg(score)
from student left outer join sc on student.sNo = sc.sNo
group by student.sNo
(3)查询各课程的平均分;
select cName,avg(score)
from course left outer join sc on course.cNo = sc.cNo
group by course.cNo
(4)查询各学院开设的课程门数;
select dName,count(distinct cNo)
from department,course
where department.dNo = course.dNo
group by department.dName
(5)查询各门课程选修人数。
select cName,count(sNo)
from course,sc
where course.cNo = sc.cNo
group by cName
三、多表
(1)查询“信息学院”所有学生学号与姓名;
select sNo,sName
from department,student
where student.dNo = department.dNo and dName = '信息学院'
(2)查询“软件学院”开设的所有课程号与课程名称;
select cNo,cName
from department,course
where department.dNo = course.dNo and dName = '软件学院';
(3)查询与“陈丽”在同一个系的所有学生学号与姓名;
select sNo,sName
from student
where dNo = ( select dNo
from student
where sName = '陈丽' )
(4)查询与“张三”同岁的所有学生学号与姓名;