数据库实验二：数据库的SQL基本操作

数据库和表的基本信息：

1. 查询全体学生的学号和姓名

select sno,sname
from student

2. 查询选修了课程名为’数据库原理’ 的学生的学号和姓名

select student.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname='数据库原理';

3. 查询全体学生的姓名, 出生年份,和所在系, 并用小写字母表示所有系名，并给各列指定列名。

select sno id,2020-sage birth,LOWER(sdept) dept
from student

4. 查询有多少名学生的数据库课程成绩不及格

select count(sno)
from sc,course
where sc.cno = course.cno and cname = '数据库' and grade < 60;

5. 查找所有姓’李’的学生的姓名, 学号和性别

select sname,sno,sage
from student
where sname like '李%';

6. 求没有选修数学课程的学生学号 求差

select sno 
from sc
except
select sno
from sc,course
where cname = '数学' and course.cno = sc.cno;

7. 查询选修了课程的学生的学号

select distinct sno
from sc

8. 计算1号课程的学生的平均成绩, 最高分和最低分

select avg(grade),max(grade),min(grade)
from sc
where cno = 1;

9. 查询数学系和信息系的学生的信息;

select *
from student
where sdept = 'IS' or sdept = 'MA';

10. 将年龄为19岁的学生的成绩置零

update student
set sage = 0
where sage = 19;

11. 查询所有选修了1号课程的学生姓名

select sname
from sc,student
where sc.sno = student.sno and cno = 1;

12. 对每一个性别，求学生的平均年龄，并把结果存入数据库
    先创建表，再插入数据

create table sex_age
(sex char(4),
Avg_age INT);

insert 
into sex_age(sex,Avg_age)
select ssex,avg(sage)
from student
group by ssex

13. 查询每个学生已获得的学分

select sno,sum(credit)
from course,sc
where course.cno = sc.cno
group by sno;

(14) 将所有女生的记录定义为一个视图

create view Girl_student
as
select *
from student
where ssex = '女';

(15) 查询没有选修了1号课程的学生姓名

select distinct sname 
from student
where not exists
(select * 	
from sc 
where sc.sno=student.sno and cno = 1);

(16) 将所有选修了数据库课程的学生的成绩加5分

update sc
set grade = grade + 5
where cno in
(select cno 
from course
where course.cno = sc.cno and cname = '数据库');

(17) 查询各系的男女生学生总数, 并按系别升序排列, 女生排在前

select sdept,ssex,count(sno)
from student
group by ssex,sdept
order by sdept ASC,ssex DESC;

(18) 查询’信息系’(IS)学生”数据结构”课程的平均成绩

select avg(grade)
from student,sc,course
where student.sno = sc.sno and course.cno = sc.cno and sdept='IS' and cname='数据结构';

(19) 创建一个反映学生出生年份的视图

create view Birth_age(birth)
as
select 2020-sage
from student;

(20) 查询与’王田’在同一个系学习的学生的信息

select *
from student
where sdept in
(select sdept
from student
where sname = '王田');

(21) 查询年龄在20岁以下的学生的姓名及其年龄

select sname,sage
from student
where sage < 20;

Tips:之前的操作把年龄置零了
(22) 查询当前至少选修数据库和信息系统其中一门课的学生的学号

select distinct sno
from sc
where exists(
select *
from course
where course.cno = sc.cno and (cname = '数据库' or cname = '信息系统'));

(23) 查询每个学生的学号, 姓名, 选修的课程名和成绩:

select sc.sno,sname,cname,grade
from sc,student,course
where sc.sno = student.sno and sc.cno = course.cno;

(24) 查找名字中包括“俊”的学生的姓名, 学号，选课课程和成绩

select sname,sc.sno,cname,grade
from sc,student,course
where sc.sno = student.sno and sc.cno = course.cno and sname like '%俊%';

(25) 查询学分大于8的学生，输出学生的学号和学分

select sno,sum(credit)
from sc,course
where sc.cno = course.cno
group by sno having sum(credit) > 8;

(26) 查询IS,CS,MA系的所有学生的姓名和性别

select sname,ssex
from student
where sdept = 'IS' or sdept = 'CS' or sdept = 'MA';

(27) 查询至少选修了2门课程的学生的平均成绩

select sno,avg(grade)
from sc
group by sno having count(cno) >= 2;

(28) 查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数

select cno,avg(grade),max(grade),min(grade),count(cno)
from sc
group by cno;

(29) 删除年龄大于21岁所有学生的选课记录

delete 
from sc
where sno in
(select sno 
from student 
where sage > 21);

(30) 查询没有先行课的课程的课程号cno和课程名cname

select cno,cname
from course
where pcno is NULL;