西北工业大学数据库系统实验一SQL练习

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, 2023-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 GROUP BY dNo;

（2）查询每个学生选修课程的平均分；
SELECT sNo, AVG(score) FROM course GROUP BY sNo;

（3）查询各课程的平均分；
SELECT cNo, AVG(score) FROM course GROUP BY cNo;

（4）查询各学院开设的课程门数；
SELECT dNo, COUNT(cNo) FROM course GROUP BY dNo;

（5）查询各门课程选修人数。
SELECT cNo, COUNT(dNo) 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 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 sName = '离散数学');

（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 '移动计算');

（10）查询平均分大于等于90分的所有课程名称；
SELECT cName FROM course WHERE cNo IN(SELECT cNo FROM sc WHERE (SELECT AVG(score) FROM course GROUP BY cNo) >= 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 course.cNo = sc.cNo AND student.sNo = (SELECT sNo FROM sudent WHERE sName='王兵');

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

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

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

四、综合
（1）查询所有学生及其选课信息（包括没有选课的学生）；
SELECT s.sName,s.sNo,c.cName,c.cNo
FROM student s,course c,sc
WHERE s.sNo=sc.sNo and c.cNo=sc.cNo;

（2）查询“形式语言与自动机”先修课的课程名称；
SELECT cName
FROM course
WHERE cNo IN(
              SELECT cPNo
              FROM course
              WHERE cName = '形式语言与自动机'
);

（3）查询“形式语言与自动机”间接先修课课程名称；
SELECT cName
FROM course
WHERE cNo IN(
              SELECT cPNo 
              FROM course
              WHERE cNo in(
                           SELECT cPNo
                           FROM course
                           WHERE cName = '形式语言与自动机'
              )
);

（4）查询先修课为编译原理数学的课程名称；
SELECT cName
FROM course
WHERE cPNo IN(
            SELECT cNo
            FROM course
            WHERE cName = '编译原理数学'
);

（5）查询间接先修课为离散数学的课程名称；
SELECT cName
FROM course
WHERE cPNo IN(
              SELECT cNo 
              FROM course
              WHERE cPNo in(
                           SELECT cNo
                           FROM course
                           WHERE cName = '离散数学'
              )
);

（6）查询所有没有先修课的课程名称；
SELECT cName FROM course WHERE cPNo IS NULL;

（7）查询所有没选修“形式语言与自动机”课程的学生姓名；
SELECT sName
FROM student
WHERE sNo IN(
            SELECT sNo
            FROM sc
            WHERE cNo NOT 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 NOT IN(
                        SELECT cNo
                        FROM course
                        WHERE cName = '离散数学'
            )
)；

（9）查询所有选修了“形式语言与自动机”但没选修其先修课的学生姓名；
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 = '形式语言与自动机'
            )
)；

（10）查询选修课程总学分大于等于28的学生姓名及其选修课程总学分；
SELECT s.sName,sum(credit)
FROM student s,course c,sc
WHERE s.sNo = sc.sNo and sc.cNo = c.cNo
GROUP BY s.sNo
Having sum(credit)>=28                            

（11）查询选修了3门以上课程且成绩都大于85分的学生学号与姓名；
SELECT sNo,sName
FROM student
WHERE sNo IN(
            SELECT sNo
            FROM sc
            WHERE score >= 85
            GROUP BY sNo
            Having count(cNo) > 3
);    

（12）查询恰好选修了3门课并且都及格的学生姓名；
SELECT sName
FROM student
WHERE sNo IN(
            SELECT sNo
            FROM sc
            WHERE score >= 60
            GROUP BY sNo
            Having count(cNo) = 3 AND count(cNo) = count(score >= 60)
);    

（13）查询人数多于6的学院名称及其学生人数；
SELECT d.dName, count(s.sNo)
FROM department d, student s, sc
WHERE d.dno = s.dno
GROUP BY d.dno
Having count(s.sNo) > 6;

（14）查询平均成绩高于王兵的学生姓名；
SELECT s.sName
FROM student s, sc
WHERE s.sNo = sc.sNo
GROUP BY s.sNo
Having avg(score) > (
                SELECT avg(score)
                FROM student INNER JOIN sc
                ON student.sNo = sc.sNo AND sName = '王兵'
)；
    
（15）查询所有选修了离散数学并且选修了编译原理课程的学生姓名
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 = '编译原理'
            )
)；

（16）查询软件学院离散数学课程平均分；
SELECT avg(score)
FROM sc
WHERE cNo IN (
            SELECT cNo
            FROM course
            WHERE cName='离散数学'
) 
AND sNo IN (
        SELECT sNo
        FROM student
        WHERE dNo IN (
                    SELECT dNo
                          FROM department
                          WHERE dName = '软件学院'
          )
);

（17）查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院；
SELECT sName,age,dName
FROM student LEFT OUTER JOIN department
ON student.dNo = department.dNo
WHERE age NOT IN (
                  SELECT age
                  FROM student
                  WHERE dNo IN (
                        SELECT dNo
                                FROM department 
                                WHERE dName ='软件学院'
                  )
);

（18）查询各学院选修同一门课人数大于4的学院、课程及选课人数；
SELECT department.dNo,dName,course.cNo,cName,count(student.sNo)
FROM department JOIN student
ON department.dNo = student.dNo AND student.sno = sc.sno AND sc.cNo = course.cNo
GROUP BY department.dNo,course.cNo
HAVING count(student.sNo) > 4

（19）查询仅仅选修了“高等数学”一门课程的学生姓名；（学号、姓名及所在学院名称）
SELECT sName(sNo, sName, dNo)
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 NOT IN(
                        SELECT cNo
                        FROM course
                        WHERE cName = '高等数学'
            )
)；
同第八题

（20）查询平均学分积小于70分的学生姓名。
SELECT sName,sum(sc.score*course.credit)/sum(course.credit)
FROM student INNER JOIN sc INNER JOIN course
GROUP BY Nno
HAVING (sum(sc.score*course.credit)/sum(course.credit)) < 70;

（21）查询选修了“信息学院”开设全部课程的学生姓名。                                        
SELECT sName
FROM student
WHERE NOT EXISTS(
　　SELECT *
　　FROM course
　　WHERE course.dNo = '信息学院' AND NOT EXISTS(
　　　　SELECT *
　　　　FROM sc
　　　　WHERE sc.sNo = student.sNo AND sc.cNo = course.cNo
　　)
);

（21）查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。
SELECT sName
FROM student
WHERE NOT EXISTS(　　　　　　　　　　
　　SELECT *
　　FROM course
　　WHERE course.cNo IN (SELECT cNo FROM sc WHERE sNo IN(SELECT sNo FROM student WHERE sName = '杨佳伟'))NOT EXISTS(
　　　　SELECT *
　　　　FROM sc
　　　　WHERE sc.sNo = student.sNo AND sc.cNo = course.cNo
　　)
);

五、DDL练习
1、创建2张表，信息如下：
      图书（编号，书名，作者，ISBN，出版社编号，版本，出版日期）。主码为编号，ISBN唯一。出版社编号为外码，参照出版社编号。
      出版社（编号，名称，地址，电话）。主码为编号。
要求：(1)创建表的同时创建约束；
creat table book
(
    bNo int,
    bName varchar(32),
    bAuthor varchar(32),
    bISBN varchar(32),
    pNo varchar(32),
    bVersion varchar(32),
    bPublicDate date,
    PRIMARY KEY (bNo),
    FOREIGN KEY (pNo) REFERENCES publicHouse(pNo)
);
creat table publisher(
    pNo int,
    pName varchar(32),
    pAddress varchar(32),
    pTelephone varchar(32),
    PRIMARY KEY (pNo)
);

      (2)删除所创建的表;
drop table book CASCADE;
drop table publisher CASCADE;

      (3)重新创建表，在表创建之后增加约束。
creat table book
(
    bNo int,
    bName varchar(32),
    bAuthor varchar(32),
    bISBN varchar(32),
    pNo varchar(32),
    bVersion varchar(32),
    bPublicDate date,
    --PRIMARY KEY (bNo),
    --FOREIGN KEY (pNo) REFERENCES publisher(pNo)
);
creat table publisher(
    pNo int,
    pName varchar(32),
    pAddress varchar(32),
    pTelephone varchar(32),
    PRIMARY KEY (pNo)
    --PRIMARY KEY (pNo)
);

alter table publisher add PRIMARY KEY (pNo);
alter table book add PRIMARY KEY (bNo);
alter table book add FOREIGN KEY (pNo) REFERENCES publisher (pNo);

2、
(1)分别向两张表中各插入2行数据。
insert into book values ('01','001','李希凡','ISBN-123456789','01','1.0.0','2023-05-07');
insert into book values ('02','002','李希凡','ISBN-987654321','02','1.0.0','2023-05-07');
insert into publisher values ('01','01出版社','长安','18605825979');
insert into publisher values ('02','02出版社','长安','18605825979');

(2)将其中一个出版社地址变更一下。
update publisher set pAddress='太仓' where pName='02出版社';

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

3、
(1)创建一个软件学院所有选修了“离散数学”课程的学生视图，并通过视图插入一行数据。
CREATE VIEW test as
SELECT *
FROM student
WHERE sNo in(
            SELECT s.sNo
            FROM student s,department d,course c,sc
            WHERE d.dName='软件学院' and d.dNo=s.dNo and c.cName='离散数学' and c.cNo=sc.cNo and sc.sNo=s.sNo);

insert into test VALUES('2020303002','李希凡','男',21,'01');

(2)创建一个各门课程平均分视图。
CREATE VIEW avg_score as
SELECT c.*,avg(score)
FROM sc INNER JOIN course c
GROUP BY c.cNo;

4、创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插入所有学生数据。
create table s_score(
        sNo CHAR(6),
        sName CHAR(20),
        avgscore INT);
INSERT into s_score(sNo,sName,avgscore)
SELECT sNo,sName,avg(score)
FROM sc INNER JOIN student s
GROUP BY sNo, sName;

DCL
尝试将多条SQL语句组成一个事务执行，体验提交和回滚操作。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！