西工大数据库课后练习答案_2019最新版_无错版

共享精神万岁！！  望后来者借鉴完毕后继续更新下去               ——————来自某学长

表结构为

department(dNo,dName,officeRoom,homepage)
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)
sc(sNo,cNo,score,recordDate)

一、
（1）查询所有年龄大于等于20岁的学生学号、姓名；

select sNo,sName
from student
where age>=20

（2）查询所有姓钱的男生学号、姓名、出生年份；

select sNo,sName,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 dNO,avg(age)
from student
where age is not null and dNo is not null
group by dNo

（2）查询每个学生选修课程的平均分；

select sno,avg(score)
from sc
where score is not null
group by sno

（3）查询各课程的平均分；

select cno,avg(score)
from sc
where score is not null
group by cno

（4）查询各学院开设的课程门数；

select dno,count(cno)
from course
group by dno

（5）查询各门课程选修人数。

select cno,count(sno)
from sc
group by cno

三、多表
（1）查询“信息学院”所有学生学号与姓名；
select sno,sname
from student
where dno in 
        (select dno 
        from department
        where dname='信息学院'
        );
（2）查询“软件学院”开设的所有课程号与课程名称；
select cno,cname
from course
where dno in 
        (select dno 
        from department
        where dname='软件学院'
        );
（3）查询与“陈丽”在同一个系的所有学生学号与姓名；
select sno,sname
from student 
where dno in 
        (select dno 
        from student 
        where sname='陈丽'
        );
（4）查询与“张三”同岁的所有学生学号与姓名；
select sno,sname
from student 
where sname!='钱多多'and age in 
        (select age 
        from student 
        where sname='钱多多'
        );
（5）查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名；
select sno,sname
from student 
where  age in 
        (select age 
        from student 
        where sname='钱多多'
        )
    and dno not in 
        (select dno 
        from student 
        where sname='钱多多'
        )
（6）查询学分大于“离散数学”的所有课程名称；
select cname 
from course
where credit >
        (select credit 
        from course
        where cname='离散数学'
        )
（7）查询选修了课程名为“组合数学”的学生人数；
select count(sno)
from sc
where cno in
    (select cno 
     from course 
     where cname='组合数学'
    )
（8）查询没有选修“离散数学”的学生姓名；
select sname
from student 
where sno not in
      (select sno
      from sc
      where cno in
                (select cno 
                 from course 
                 where cname='离散数学'
                )
         )
（9）查询与“算法设计与分析”、“移动计算”学分不同的所有课程名称；
select cname 
from course
where credit not in
            (select credit
            from course
            where cname='算法设计与分析'or cname='移动计算'
            )
（10）查询平均分大于等于90分的所有课程名称；
select cname 
from course
where cno in
        (select cno
        from sc
        group by cno
        having avg(score)>=90
        )
（11）查询选修了“离散数学”课程的所有学生姓名与成绩；
select sname,score
from student,sc
where student.sno=sc.sno and
        cno in
        (select cno
        from course
        where cname='离散数学'
        )
（12）查询“王兵”所选修的所有课程名称及成绩；
select cname,score
from sc,course
where sno in
    (select sno
     from student
     where sname='尹江月'
    )
    and sc.cno=course.cno
（13）查询所有具有不及格课程的学生姓名、课程名与成绩；
select sname,cname,score
from sc,course,student
where sc.score<60 and sc.sno=student.sno
    and sc.cno=course.cno
（14）查询选修了“文学院”开设课程的所有学生姓名；
select sname
from student
where sno in
    (select sno
     from sc,course
     where sc.cno=course.cno and
     course.dno = 
         (select dno
        from department
        where dname='文学院'
        )
    )
（15）查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称。
select sname,cname
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
    and student.dno in
                (select dno 
                from department
                where dname='信息学院'
                )
    and course.dno in
                (select dno 
                from department
                where dname='信息学院'
                )

四、
（1）查询所有学生及其选课信息（包括没有选课的学生）；
select *
from student ,sc where student.sno=sc.sno

（2）查询“形式语言与自动机”先修课的课程名称；

select second.cname
from course first, course second 
where first.cpno=second.cno
    and first.cname='形式语言与自动机'

（3）查询“形式语言与自动机”间接先修课课程名称；

select third.cname
from course first, course second ,course third
where first.cpno=second.cno and second.cpno=third.cno
    and first.cname='形式语言与自动机'

（4）查询先修课为‘编译原理’的课程名称；

select first.cname
from course first, course second
where first.cpno=second.cno
    and second.cname='编译原理'

（5）查询间接先修课为离散数学的课程名称；
select first.cname
from course first, course second ,course third
where first.cpno=second.cno and second.cpno=third.cno
    and third.cname='离散数学'

（6）查询所有没有先修课的课程名称；

select first.cname
from course first
where first.cpno is null

（7）查询所有没选修“形式语言与自动机”课程的学生姓名；
select sname
from student
where sno not in
(
     select sno
     from sc
     where cno in
    (
        select cno
    from course
    where cname='形式语言与自动机'
    )
 )

（8）查询所有选修了“概率论”但没选修其先修课的学生姓名；
select sname
from student
where sno in
        (select sno
        from sc
        where cno in
            (select cno
            from course
            where cname='概率论'
            )
        )
and  sno not in
        (select sno
        from sc
        where cno  in
            (select cpno
            from course
            where cname='概率论'
            )
        )

（9）查询选修课程总学分大于等于28的学生姓名及其选修课程总学分；

select s.sNo,s.sName,v.sumCredit
from student s, (select sNo,SUM(credit)
                 from sc, course c
                 where sc.cNo=c.cNo
                 group by sc.sNo
                 having SUM(credit)>=28) as v(sNo,sumCredit)
where s.sNo=v.sNo;

（10）查询选修了3门以上课程且成绩都大于85分的学生学号与姓名；

select sno,sname 
from student 
where sno in
    (select sno  
    from sc
    group by sno 
    having count(*)>3 and min(score)>85)

（11）查询恰好选修了3门课并且都及格的学生姓名；
select sname 
from student 
where sno in
    (select sno  
    from sc
    group by sno 
    having count(*)=3 and min(score)>60

)

（12）查询人数多于6的学院名称及其学生人数；

select dname,dpt_count 
from department ,
    (select dno,count(*)  
    from student 
    group by dno  
    having count(*)>6) 
    as dpt(dpt_dno,dpt_count)
where department.dno=dpt.dpt_dno 
（13）查询平均成绩高于王兵的学生姓名；

select sname 
from student 
where sno in
    (select sno 
     from sc
     group by sno 
     having avg(score)> ALL
             (select avg(score) 
             from sc
             group by sno 
             having sno in
                    (select sno 
                    from student 
                    where sname='王兵'
                    )
            )
    )
    and sname<>'王兵'

（14）查询所有选修了离散数学并且选修了编译原理课程的学生姓名；
select sname 
from student 
where sno in
        (select  sno 
        from sc
        where cno in 
                (select cno 
                from course
                where cname='离散数学'
                )
            
        )
and sno in
        (select  sno 
        from sc
        where cno in 
                (select cno 
                from course
                where cname='编译原理'
                )
            
        )

    
（15）查询软件学院离散数学课程平均分；

select avg(score)
from student,sc
where student.sno=sc.sno 
        and sc.cno in 
            (select cno 
            from course
            where cname='离散数学'
            )
        and student.dno in
            (select dno 
            from department 
            where dname='软件学院'
            )

（16）查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院；
select sname,age,dno
from student
where age <>all
    (select age
    from student 
    where dno in 
        (select dno 
         from department 
         where dname='软件学院'
        )
         and age is not null
    )
    

（17）查询各学院选修同一门课人数大于4的学院、课程及选课人数；
select d.dname,c.cname,v.countOf  
from course c,department d, 
    (select dno,cno,count(s.sno)  
    from student s, sc  
    where s.sno=sc.sno  
    group by dno,cno  
    having count(sc.sno)>4) 
    as v(dno,cno,countOf)  
 where c.cno=v.cno and d.dno=v.dno; 

（18）查询仅仅选修了“高等数学”一门课程的学生姓名；（学号、姓名及所在学院名称）

SELECT
    student.sname,
    student.sno,
    department.dname 
FROM
    student
    LEFT JOIN department ON student.sno NOT IN (
    SELECT
        sno 
    FROM
        sc 
    WHERE
        cno IN ( SELECT cno FROM course WHERE cname <> '高等数学' ) 
    ) 
    AND student.sno IN (
    SELECT
        sno 
    FROM
        sc 
    WHERE
        cno IN ( SELECT cno FROM course WHERE cname = '高等数学' ) 
    ) 
    AND student.dno = department.dno

（19）查询平均学分积小于70分的学生姓名。

 SELECT student.sName,sub.sumcre 
 FROM student,
    (SELECT sc.sNo AS ssno,
     SUM(sc.score * course.credit)/SUM(course.credit) AS sumcre 
     FROM sc,course
     WHERE course.cNo=sc.cNo AND sc.score IS NOT NULL 
     GROUP BY sc.sNo 
     HAVING SUM(sc.score * course.credit)/SUM(course.credit)<70
      )
    AS sub(ssno,sumcre)
WHERE sub.ssno=student.sNo;   

（20）查询选修了“信息学院”开设全部课程的学生姓名。

（没有一门信息学院开的课他是不选修的  双重否定  全称量词转为存在量词）
SELECT student.sNo,student.sname
FROM student
WHERE  NOT EXISTS
    ( SELECT * 
     FROM course   
    WHERE NOT EXISTS
             (SELECT *
            FROM sc 
            WHERE sc.sNo=student.sNo AND sc.cNo=course.cNo )  
             AND course.dNo=
                 (SELECT department.dNo 
                FROM department 
                WHERE department.dName='信息学院') 
    ) 
（21）查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。

select sname
from  student 
where sno in
    (select sno
    from sc scx
    where not exists 
        (select *
         from sc scy
         where scy.sno=
                     (select sno 
                    from student 
                    where sname='杨佳伟'
                    )
         and not exists 
                 (select *
                from sc scz
                where scz.sno=scx.sno and
                    scz.cno=scy.cno
                )
        )
     )
     and sname!='杨佳伟'
五、DDL练习
要求：(1)创建表的同时创建约束；
create table press
(Pnumber char(30)primary key,   /*Press number  出版社编号 主码*/
 pname   char(30),                /* pname 名称*/
 address char(30),                /*adress 地址*/
 phone  char(30)                /*phone  电话*/ 
);
create table book
(bno char(9) primary key,      /*bno是编号 主码*/
 bname char(20),              /*banme是书名*/
 author char(20),              /*author是作者名*/
 ISBN char(30) unique,               /*ISBN唯一*/
 Pnumber char(30),             /*Press number  出版社编号*/
 vs  char(20),                /*version 版本*/
 dateofpub char(20) ,                /*Date of publication出版日期*/
 foreign key(Pnumber) references press(Pnumber)   /*出版社编号(Pnumber)为外码，参照出版社表的编号(Pnumber)*/
);
      (2)删除所创建的表;
drop table book cascade;
drop table press cascade;

      (3)重新创建表，在表创建之后增加约束。
create table press
(Pnumber char(30),   /*Press number  出版社编号 主码*/
 pname   char(30),                /* pname 名称*/
 address char(30),                /*adress 地址*/
 phone  char(30)                /*phone  电话*/ 
);
create table book
(bno char(9) ,      /*bno是编号 主码*/
 bname char(20),              /*banme是书名*/
 author char(20),              /*author是作者名*/
 ISBN char(30) ,               /*ISBN唯一*/
 Pnumber char(30),             /*Press number  出版社编号*/
 vs  char(20),                /*version 版本*/
 dateofpub char(20)                /*Date of publication出版日期*/
);
alter table press
add primary key(Pnumber);
alter table book
add primary key(bno),
add unique (ISBN),
add foreign key(Pnumber) references press(Pnumber) 
;

2、
(1)分别向两张表中各插入2行数据。
insert 
into press(pnumber,pname,address,phone)
values('A1','蓝天出版社','西安','955626275');
insert 
into press(pnumber,pname,address,phone)
values('B1','白云出版社','上海','915626565');
insert 
into book(bno,bname,author,isbn,pnumber,vs,dateofpub)
values('001','软件工程概论','Mr.Join','djfa8892','A1','第3版','2020.05.20');
insert 
into book(bno,bname,author,isbn,pnumber,vs,dateofpub)
values('002','思修','Mr.Boss','889adfjn','B1','第10版','2020.09.20');
(2)将其中一个出版社地址变更一下。
update press
set address='成都'
where pnumber='A1';
(3)删除所插入数据。

delete
from book;
delete
from press;
3、
(1)创建一个软件学院所有选修了“离散数学”课程的学生视图，并通过视图插入一行数据。
create view is_s1(sno,sname)
as
select sno,sname,dno
from student
where student.sno in
(        
    select sno
    from sc
    where cno in 
    (    select cno
        from course
        where cname='离散数学'
    )
)
and dno in
(
        select dno
        from department
        where dname='软件学院'
)
insert
into is_s1(sno,sname)
values('154612','张三');
(2)创建一个各门课程平均分视图。
create view is_s2(cno,cname,avgscore)
as
select sc.cno,course.cname,avg(score)
from  sc,course
where sc.score is not null and
    sc.cno=course.cno
group by sc.cno,course.cname
4、创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插入所有学生数据。
create table student_score
(
    sNo char(6),
    sName varchar(20),
    avgscore int 
);
insert 
into student_score
select student.sno,student.sname,avg(sc.score)
from  sc,student
where sc.score is not null and
    sc.sno=student.sno
group by student.sno,student.sname

5.回滚操作  见我的navicat上面的用法