数据库练习

一、单表

(1)查询所有年龄大于等于20岁的学生学号、姓名:
SELECT sNo, sName
FROM student
WHERE age >= 20;

(2)查询所有姓钱的男生学号、姓名、出生年份:
SELECT sNo, sName, YEAR(CURDATE()) - age AS sBirthday
FROM student
WHERE sName LIKE '钱%';

(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 AVG(s.age), d.dName
FROM student s, department d
WHERE s.dNo = d.dNo
GROUP BY d.dNo;

(2)查询每个学生选修课程的平均分:
SELECT AVG(b.score) AS aveScore, a.sName
FROM student a, sc b
WHERE a.sNo = b.sNo
GROUP BY a.sNo;

(3)查询各课程的平均分:
SELECT AVG(b.score) AS avgScore, a.cName
FROM course a, sc b
WHERE a.cNo = b.cNo
GROUP BY b.cNo;

(4)查询各学院开设的课程门数:
SELECT COUNT(c.cNo) AS courseNum, d.dName
FROM course c, department d
WHERE c.dNo = d.dNo
GROUP BY c.dNo;

(5)查询各门课程选修人数:
SELECT COUNT(sc.sNo) AS studentNum, course.cName
FROM course, sc
WHERE sc.cNo = course.cNo
GROUP BY sc.cNo;

三、多表

(1)查询“信息学院”所有学生学号与姓名:
SELECT student.sNo, student.sName
FROM student, department
WHERE department.dName = '信息学院'
    AND student.dNo = department.dNo;

(2)查询与“陈丽”在同一个系的所有学生学号与姓名:
SELECT course.cNo, course.cName
FROM course, department
WHERE department.dName = '软件学院'
    AND department.dNo = course.dNo;

(3)查询与“张三”同岁的所有学生学号与姓名:
SELECT sNo, sName
FROM student
WHERE dNo = (
    SELECT dNo
    FROM student
    WHERE sName = '陈丽'
    LIMIT 1
);

(4)查询与“张三”同岁的所有学生学号与姓名:
SELECT sNo, sName
FROM student
WHERE age = (
    SELECT age
    FROM student
    WHERE sName = '张三'
    LIMIT 1
);

(5)查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名:
SELECT sNo, sName
FROM student
WHERE age = (
        SELECT age
        FROM student
        WHERE sName = '张三'
        LIMIT 1
    )
    AND dNo != (
        SELECT dNo
        FROM student
        WHERE sName = '张三'
        LIMIT 1
    );
(6)查询学分大于“离散数学”的所有课程名称:
SELECT cName
FROM course
WHERE credit > (
    SELECT credit
    FROM course
    WHERE cName = '离散数学'
);

(7)查询选修了课程名为“组合数学”的学生人数:
SELECT COUNT(*)
FROM sc
WHERE cNo = (
    SELECT cNo
    FROM course
    WHERE cName = '组合数学'
);

(8)查询没有选修“离散数学”的学生姓名:
SELECT sName
FROM student
WHERE sNo NOT IN (
    SELECT sNo
    FROM sc
    WHERE cNo = (
        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 student.sName, sc.score
FROM student, sc
WHERE student.sNo = sc.sNo
    AND sc.cNo = (
        SELECT cNo
        FROM course
        WHERE cName = '离散数学'
    );

(12)查询“王兵”所选修的所有课程名称及成绩:
SELECT course.cName, sc.score
FROM course, sc
WHERE sc.sNo = (
        SELECT sNo
        FROM student
        WHERE sName = '王兵'
        LIMIT 1
    )
    AND sc.cNo = course.cNo;

(13)查询所有具有不及格课程的学生姓名、课程名与成绩:
SELECT student.sName, course.cName, sc.score
FROM student, course, sc
WHERE sc.score < 60
    AND sc.sNo = student.sNo
    AND sc.cNo = course.cNo;

(14)查询选修了“文学院”开设课程的所有学生姓名:
SELECT sName
FROM student
WHERE sNo IN (
    SELECT sc.sNo
    FROM course, sc
    WHERE sc.cNo = course.cNo
        AND course.dNo = (
            SELECT dNo
            FROM department
            WHERE dName = '文学院'
        )
);

(15)查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称:
SELECT student.sName, course.cName
FROM course, student, sc
WHERE sc.sNo = student.sNo
    AND sc.cNo = course.cNo
    AND student.dNo = (
        SELECT dNo
        FROM department
        WHERE dName = '信息学院'
    )
    AND course.dNo = (
        SELECT dNo
        FROM department
        WHERE dName = '信息学院'
    );

四、综合

(1)查询所有学生及其选课信息(包括没有选课的学生):
SELECT *
FROM student, sc
WHERE student.sNo = sc.sNo;

(2)查询“形式语言与自动机”先修课的课程名称:
SELECT first.cName
FROM course first, course second
WHERE second.cName = '形式语言与自动机'
    AND first.cNo = second.cPno;

(3)查询“形式语言与自动机”间接先修课课程名称:
SELECT first.cName
FROM course first, course second, course third
WHERE third.cName = '形式语言与自动机'
    AND second.cPno = third.cNo
    AND first.cPno = second.cNo;

(4)查询先修课为编译原理数学的课程名称:
SELECT first.cName
FROM course first, course second
WHERE second.cName = '编译原理数学'
    AND first.cPno = second.cNo;

(5)查询间接先修课为离散数学的课程名称:
SELECT first.cName
FROM course first, course second, course third
WHERE third.cName = '离散数学'
    AND second.cPno = third.cNo
    AND first.cPno = second.cNo;

(6)查询所有没有先修课的课程名称:
SELECT cName
FROM course
WHERE cPno IS NULL;

(7)查询所有没选修“形式语言与自动机”课程的学生姓名:
SELECT sName
FROM student
WHERE sNo NOT IN (
    SELECT sNo
    FROM sc
    WHERE cNo = (
        SELECT cNo
        FROM course
        WHERE cName = '形式语言与自动机'
    )
);

(8)查询所有选修了“形式语言与自动机”但没选修其先修课的学生姓名:
SELECT sName
FROM student
WHERE sNo IN (
        SELECT sNo
        FROM sc
        WHERE cNo = (
            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 sName, COUNT(credit)
FROM student, course, sc
WHERE course.cNo = sc.cNo
    AND sc.sNo = student.sNo
GROUP BY student.sNo
HAVING COUNT(course.credit) >= 28;

(10)查询选修了3门以上课程且成绩都大于85分的学生学号与姓名:
SELECT student.sNo, student.sName
FROM student, sc
WHERE student.sNo = sc.sNo
GROUP BY student.sNo
HAVING COUNT(*) > 3
AND MIN(score) > 85;

(11)查询恰好选修了3门课并且都及格的学生姓名:
SELECT student.sName
FROM student, sc
WHERE student.sNo = sc.sNo
GROUP BY student.sNo
HAVING COUNT(*) = 3
AND MIN(score) >= 60;

(12)查询人数多于6的学院名称及其学生人数:
SELECT COUNT(*) AS num, department.dName
FROM student, department
WHERE student.dNo = department.dNo
GROUP BY department.dNo
HAVING COUNT(*) > 6;

(13)查询平均成绩高于王兵的学生姓名:
SELECT sName
FROM student, sc
WHERE sc.sNo = student.sNo
GROUP BY student.sNo
HAVING AVG(sc.score) > (
    SELECT AVG(score)
    FROM sc
    WHERE sNo = (
        SELECT sNo
        FROM student
        WHERE sName = '王兵'
        LIMIT 1
    )
);

(14)查询所有选修了离散数学并且选修了编译原理课程的学生姓名:
SELECT sName
FROM student
WHERE student.sNo IN (
        SELECT sNo
        FROM sc
        WHERE cNo = (
            SELECT cNo
            FROM course
            WHERE cName = '离散数学'
        )
    )
    AND student.sNo IN (
        SELECT sNo
        FROM sc
        WHERE cNo = (
            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, dName
FROM student, department
WHERE student.age != ALL (
        SELECT age
        FROM student
        WHERE dNo IN (
                SELECT dNo
                FROM department
                WHERE dName = '软件学院'
            )
            AND age IS NOT NULL
    )
    AND department.dNo = student.dNo;

(17)查询各学院选修同一门课人数大于4的学院、课程及选课人数:
SELECT d.dName, c.cName, v.num
FROM course c, department d, (
        SELECT dNo, cNo, COUNT(s.sNo) AS num
        FROM student s, sc
        WHERE s.sNo = sc.sno
        GROUP BY dNo, cNo
        HAVING COUNT(sc.sNo) > 4
    ) v
WHERE c.cNo = v.cNo
    AND d.dNo = v.dNo;

(18)查询仅仅选修了“高等数学”一门课程的学生姓名:(学号、姓名及所在学院名称)
SELECT sc.sNo, s.sName, d.dName
FROM sc
    NATURAL JOIN student s
    LEFT JOIN department d ON s.dNo = d.dNo
WHERE sc.sNo NOT IN (
    SELECT sNo
    FROM sc
    WHERE cNo != (
        SELECT cNo
        FROM course
        WHERE cName = '高等数学'
    )
);

(19)查询平均学分积小于70分的学生姓名:
SELECT student.sName
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 sumcre < 70
    ) sub
WHERE sub.ssno = student.sNo;

(20)查询选修了“信息学院”开设全部课程的学生姓名:
SELECT student.sName
FROM student
WHERE NOT EXISTS (
    SELECT course.cNo
    FROM course
    WHERE NOT EXISTS (
            SELECT sc.cNo
            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 s
WHERE NOT EXISTS (
        SELECT *
        FROM sc sc2
        WHERE sc2.sno = (
                SELECT sno
                FROM student
                WHERE sname = '杨佳伟'
            )
            AND NOT EXISTS (
                SELECT *
                FROM sc sc3
                WHERE s.sno = sc3.sno
                    AND sc2.cno = sc3.cno
            )
    )
    AND s.sname != '杨佳伟';

五、DDL练习

1、创建2张表,信息如下:
      图书(编号,书名,作者,ISBN,出版社编号,版本,出版日期)。主码为编号,ISBN唯一。出版社编号为外码,参照出版社编号。
      出版社(编号,名称,地址,电话)。主码为编号。
要求:(1)创建表的同时创建约束;
      (2)删除所创建的表;
      (3)重新创建表,在表创建之后增加约束。
create table publisher(
       publisher_no               varchar(20)  not null,
       publisher_name             varchar(200) not null,
       publisher_address          varchar(200),
       publisher_tel              varchar(20),
       primary key(publisher_no)
);

create table book(
       book_no                    varchar(20)      not null unique,
       book_name                  varchar(200)     not null,
       book_author                varchar(200)     not null,
       book_ISBN                  varchar(200)     not null unique,
       publisher_no               varchar(20),
       book_version               varchar(10),
       book_date                  date,
       primary key(book_no),
       foreign key(publisher_no) references publisher(publisher_no)      
);

drop table book;
drop table publisher;

create table publisher(
       publisher_no               varchar(20)  not null,
       publisher_name             varchar(200) not null,
       publisher_address          varchar(200),
       publisher_tel              varchar(20)
);

create table book(
       book_no                    varchar(20)      not null unique,
       book_name                  varchar(200)     not null,
       book_author                varchar(200)     not null,
       book_ISBN                  varchar(200)     not null unique,
       publisher_no               varchar(20),
       book_version               varchar(10),
       book_date                  date
);
alter table publisher add primary key(publisher_no);
alter table book add primary key(book_no);
alter table book add foreign key(publisher_no) references publisher(publisher_no);

2、
(1)分别向两张表中各插入2行数据。
insert into publisher values('01','人民邮电出版社','北京','10010');
insert into publisher values('02','高等教育出版社','上海','10086');

insert into book values('01','第一行代码','Tom','1433223','01','1.3',to_date('2016-03-02','yyyy-mm-dd'));
insert into book values('02','复变函数','jerry','2333233','02','6.3',to_date('2014-03-02','yyyy-mm-dd'));

(2)将其中一个出版社地址变更一下。
update publisher
set publisher_address = '安徽'
where publiser_no = '01';

(3)删除所插入数据。
delete 
from publisher cascade;
delete
from book cascade;

3、
(1)创建一个软件学院所有选修了“离散数学”课程的学生视图,并通过视图插入一行数据。
--drop view other cascade;
create view other
as
select student.sNo, student.sName
from student join sc on student.sNo = sc.sNo join course on sc.cNo = course.cNo join department on student.dNo = department.dNo
where department.dName = '软件学院' and course.cName = '离散数学';

drop view other cascade;
create view other
as
SELECT student.sNo, student.sName
FROM student
WHERE student.dNo in
                    (SELECT department.dNo
                    FROM department
                    WHERE department.dName = '软件学院') and student.sNo in 
                                                                        (SELECT sc.sNo
                                                                        FROM sc
                                                                        where sc.sNo = student.sNo and sc.cNo in 
                                                                                                                (SELECT course.cNo
                                                                                                                FROM course
                                                                                                                WHERE course.cName = '离散数学'));
                                                             insert
into other
values('12315', '蔡徐坤');

(2)创建一个各门课程平均分视图。
drop view AVERAGE;
create view AVERAGE as
select sc.cNo, course.cName, avg(sc.score)
from course join sc on course.cNo = sc.cNo
where sc.score is not null
group by sc.cNo, course.cName;

4、创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插入所有学生数据。
--drop view s_score;
create view s_score as
select student.sNo, student.sName, avg(sc.score)
from student left join sc on student.sNo = sc.sNo
where sc.score is not null
group by student.sNo, student.sName;

DCL

begin transaction;
select *
from publisher
where publisher_no='01';
insert into publisher values('03','高等教育出版社','上海','10086');
commit;
添加新评论