数据库实验4 SQL语言-SELECT查询操作

数据库实验4 SQL语言-SELECT查询操作

1.首先按照第三章的jxgl数据库的模板创建jxgl数据库并插入数据：

创建数据库jxgl:

create database jxgl;

创建相应的表：

创建student表，student(sno,sname,ssex,sage,sdept)：以sno为主键primary key:

create table student  
( sno CHAR(7) primary key,  
  sname varchar(20),  
  sage INT,  
  ssex varchar(2),  
  sdept varchar(2)
);  

创建course表，course(cno,cname,cpno,ccredit): 以cno为主键primary key，cpno默认为NULL：

create table course  
( cno CHAR(2) primary key,  
  cname varchar(20),  
  cpno char(2) default NULL,  
  ccredit INT
);  

创建sc表，sc(sno,cno,grade):

以(sno,cno)为主键，sno为外码参照student表中的sno,cno为外码参照course表中的cno :

create table sc  
( sno CHAR(7)  ,  
  cno CHAR(2),  
  grade INT,  
  primary key(sno,cno),  
  foreign key(sno) references student,  
  foreign key(cno) references course  
)  

插入数据：

插入student的数据：

insert into student  
values('2005001','钱横',18,'男','Cs'),  
      ('2005002','王林',19,'女','Cs'),  
      ('2005003','李民',20,'男','Is'),  
      ('2005004','赵欣然',16,'女','Ma');  

插入course的数据：

insert into course  
values('1','数据库系统','5',4),  
      ('2','数学分析',null,2),  
      ('3','信息系统导论','1',3),  
      ('4','操作系统原理','6',3),  
      ('5','数据结构','7',4),  
      ('6','数据处理基础',null,4),  
      ('7','C语言','6',3);  

插入表sc的数据：

insert into sc  
values('2005001','1',87),  
      ('2005001','2',67),  
      ('2005001','3',90),  
      ('2005002','2',95),  
      ('2005003','3',88);  

1.检索年龄大于23岁的男学生的学号和姓名

select sno,sname  
from student  
where ssex='男' and sage>23;   

2.检索至少选修一门课的女学生姓名

#在选课数据库中存在的且性别为女即为所求  
select sname  
from student natural join sc  
where ssex='女';  

3.检索王林不学的课程的课程号

#求出王林选的课程号,不在这个课程号集合中的课程即为没选
select cno  
from course  
where cno not in    
(select cno                     
from student natural join sc  
where sname='王林');  

4.检索至少选修两门课的学生学号

select distinct sno  
from sc  
group by sno  #通过学号进行分类  
having count(sno)>1;  #筛选出每个类别中数量大于1的组的学号  

5.检索全部学生都选修的课程的课程号和课程名

select  cno,cname  
from sc natural join course     #将sc和course进行自然连接  
group by cno                      #用cno分组  
having count(cno)=(select count(*)from student);  #组内元组数量等于 学生总数的才保留  

6.检索选修了所有3学分课程的学生平均成绩

#找到所有筛选了3学分课程的学生，然后求所有符合题意的学生的平均成绩
with tgc(cno,nums) as  
(select cno,count(cno)  
from course  
where ccredit=3)     #用with语句筛选出所有三学分课程  
select avg(grade)  
from sc             #从sc中选出所有选修了三门课的学生的成绩   
where sno in     #下面的select语句筛选出选择所有三学分课程的学生  
(select sno   
from sc natural join tgc    #自然连接，只保留三学分课程  
group by sno  #通过学号分组，如果分组后元组数目等于三学分课程数目则说明全修  
having count(cno)=(select count(*) from course where ccredit=3))
group by sno;     #再用学号分组;  

1.统计有学生选修的课程门数

select count(distinct cno) #sc里的cno都是有人选取的课程，故去重算count即可  
from sc;   

2.求选修4号课程的学生的平均年龄

select avg(sage)    
from student  natural join sc #将student和sc进行自然连接  
where cno='4' ;    #选出选择4号课的学生  

3.求学分为3的每门课程的学生平均成绩

select avg(grade)  
from sc natural join course #使sc和course自然连接  
where ccredit=3   #筛选出学分为3的课程  
group by cno;    #通过课程号进行分组并求avg(grade)  

4.统计每门课程的学生选修人数，要求超过三人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cno,count(cno)  
from sc  
group by cno   #通过cno课程号进行分组  
having count(cno)>3  #筛选掉选课人数少于3的课程  
order by count(cno) desc ,cno asc;  #以选修人数降序排序,相同时则cno升序排序  

5.检索学号比王林同学大而年龄比王林同学小的学生姓名

#先用with语句筛选出王林的学号和年龄  
with wanglin(age,no) as  
(select sage,sno from student where sname="王林")  
select sname  
from student,wanglin   #筛选出学号大于王林且年龄小于王林的同学名字  
where sno>no and sage<age;  

6.检索姓名以’王’开头的所有学生的姓名和年龄

select sname,sage  
from student  
where sname like '王%';  # %匹配任意字符串，故表示以王开头  

7.在sc表中检索成绩为空值的学生的学号和课程号

select sno,cno  
from sc  
where grade is null;  

8.求年龄大于女学生平均年龄的男同学的姓名和年龄

with girl_age(age) as  #用with语句求出女同学平均年龄并存到girl_age中  
(select avg(sage)  
from student  
where ssex="女")  
select sname,sage  
from student,girl_age    #筛选出男性且年龄大于girl_age  
where ssex='男' and sage>age;  

9.求年龄大于所有女学生年龄的男学生的姓名和年龄

with girl_max_age(age) as  #用with语句求出女同学最大年龄并存到girl_max_age中  
(select max(sage)  
from student  
where ssex="女")  
select sname,sage          #大于所有女性年龄等价于大于最大的女性年龄  
from student,girl_max_age    #筛选出男性且年龄大于girl_max_age  
where ssex='男' and sage>age;  

10.检索选修了4门以上课程的学生总成绩(不统计不及格课程)，并要求按总成绩的降序排列出来。

select sum(grade)  
from sc  
group by sno     #通过sno进行分组，将同一学生的课程汇集到一起  
having count(cno)>4   #筛选出课数大于4的学生  
ORDER BY sum(grade) DESC;  #按照总成绩的降序排列