博客

数据库原理及应用习题三

  • Post author:
  • Post category:其他


— 例3-8向Student表增加“入学时间”列，其数据类型为日期型。 

ALTER TABLE student 
       ADD S_entrance DATE;


— 例3-9将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。 

ALTER TABLE student ALTER COLUMN Sage INT;


— 例3-10增加课程名称必须取唯一值的约束条件。 

alter table course add unique cname;


— 例3-11删除Student表。

drop table student;

/*例3-13为学生-课程数据库中的Student、course和sc表建立索引。其中student表按照学号升序建唯一索引，

course表按照课程号升序建立唯一索引，sc表按学号升序和课程号降序建唯一索引。*/ 

create unique index stuson on student(sno asc);  /*asc:升序*/
create unique index coucno on course(cno);             /*默认升序*/
create unique index snocno on sc(sno asc,cno desc);     /*desc降序*/


— 例3-14将sc表的SCno索引改名为scsno 

alter index SCno rename SCSno;


— 例3-15删除student表的stusname索引

drop index Stusname;


— 例3-16查询全体学生的学号与姓名。

select Sno,Sname from student;

— 例3-17查询全体学生的姓名、学号、所在系。

select Sname,Sno,Sdept from student;

— 例3-18查询全体学生的详细记录。 

select * from student;

/*等价于:
        select Sno,Sname,Ssex,Sage,Sdept from student;
*/


— 例3-19查询全体学生的姓名及其出生年份 

select sname,2022-sage from student;


— 例3-20查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示系名。 

select sname NAME,'Year of Birth' BIRTH,2022-Sage BIRTHDAY,lower(Sdept) DPATEMENT 
from student;


— 例3-21查询选修了课程的学生学号

select distinct sno from sc;

— 例3-22查询计算机科学系全体学生的名单

select sname from student where sdept='CS';

— 例3-23查询年龄在20岁以下的学生姓名及其年龄

select sname,sage
 from student 
 where sage<=20;

— 例3-24查询考试成绩不及格的学生的学号

select distinct sno 
from sc 
where grade<60;

— 例3-25查询年龄在20-23之间的学生的姓名、系别和年龄 

select sname,sdept,Sage
from student
where sage between 20 and 23;

等价于/*

select sname,sdept,Sage
from student
where sage>=20 and sage<=23;

*/


— 例3-26查询年龄不在20-23之间的学生姓名、系别和年龄 

select sname,sdept,Sage
from student
where sage not between 20 and 23;
等价于/*

select sname,sdept,Sage
from student
where sage<20 or sage>23;
*/

— 例3-27查询计算机系(CS),数学系(MA)和信息系(IS)学生的姓名和性别。 

select sname,Ssex
from student
where sdept in('CS','MA','IS');
/*等价于
select sname,Ssex
from student
where sdept='CS' or sdept='MA' or Sdept='IS';
*/

— 例3-28查询既不是计算机科学系、数学系和信息系的学生的姓名和性别 

select sname,Ssex
from student 
where sdept not in ('CS','MA','IS');
/*等价于
select sname,Ssex
from student
where sdept!='CS' and sdept!='MA' and sdept!='IS';
*/

— 例3-29查询学号为201215121的学生的详细情况 

select*
from student
where sno='201215121';

select*
from course
where sno like '201215121';

— 例3-30查询所有性刘的学生的姓名、学号和性别 

SELECT Sname,Snono,Ssex 
FROM student
where sname like '刘%';

— 例3-31查询姓“欧阳”并且全名为三个汉字的学生的姓名 

select Sname
from student 
where sname like '欧阳_';
-- 3.31
select Sname,Sno,Ssex
from student
where Sname Like'叶%';

— 例3-32查询名字中第二个字为“阳”的学生的姓名 

select Sname
from student 
where sname like '_阳%';

— 例3-33查询所有不姓刘的学生的姓名、学号和性别 

select sname,sno,ssex
from student
where sname not like '刘%';

— 例3-34查询DB_Design课程的课程号和学分 

select cno,Ccredit 
from course 
where cname like 'DB\_Design';

— 例3-35查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况 

select*
from course
where cname like 'DB\_%i__';

— 例3-36某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程。 

select sno,cno
from sc
where grade is NULL;

— 例3-37查询所有有成绩的学生学号和课程号 

select sno,Cno
from student
where grade not is NULL;

— 例3-38查询计算机科学系年龄在20岁以下的学生姓名 

select Sname
from student 
where sdept='CS' and sage<20;

— 例3-39查询选修了3号课程的学生的学号及其成绩，查询结果按照分数降序排列。 

select Sno,grade
from sc
where cno='3'
order by grade desc;

— 例3-40查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按照年龄降序排列 

select*
from student
order by sdept asc,sage desc;
-- 聚集函数
-- COUNT(*)                    统计元组个数
-- COUNT([DISTINCT|ALL]<列名>) 统计一列中值的个数
-- SUM([DISTINCT|ALL]<列名>)   计算一列值的总和(此列必须是数值型)
-- AVG([DISTINCT|ALL]<列名>)   计算一列值的平均值(此列必须是数值型)
-- MAX([DISTINCT|ALL]<列名>)   求一列值的最大值
-- MIN([DISTINCT|ALL]<列名>)   求一列值的最小值 


— 3.41查询学生总人数 

SELECT count(*)
FROM student;


— 3.42查询许选修了课程的学生人数 

SELECT COUNT(DISTINCT Sno)
FROM SC;


— 3.43计算选修1号课程的学生平均成绩 

SELECT AVG(Grade)
FROM SC
WHERE Cno='1';


— 3.44查询选修1号课程的学生最高分数 

SELECT MAX(Grade)
FROM sc
WHERE Cno='1';


— 3.44查询选修1号课程的学生最低分数 

SELECT MIN(Grade)
FROM sc
WHERE Cno='1';


— 3.45查询学生201215129选修课程的总学分数 

SELECT SUM(Ccredit)
FROM SC,course
WHERE Sno='201215129'AND sc.Cno=course.Cno;


— 3.46求各个课程号及相应的选课人数 

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;


— 3.47查询选修了三门以上课程的学生学号 

SELECT Sno
FROM sc
GROUP BY Sno
HAVING COUNT(*)>3;


— 3.48查询平均成绩大于等于90分的学生学号和平均成绩 

SELECT Sno,AVG(Grade)
FROM sc
GROUP BY Sno
HAVING AVG(Grade)>=90;


— 连接查询

— 3.49查询每个学生及其选修课程的情况 

SELECT student.*,SC.*
FROM student,SC
WHERE student.Sno=sc.Sno;


— 自然连接

— 3.50查询每个学生及其选修课程的情况 

SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM student,sc
WHERE student.Sno=sc.Sno;


— 3.51查询选修2号课程且成绩在90分以上的所有学生的学号和姓名 

SELECT student.Sno,Sname
FROM student,SC
WHERE student.Sno=sc.Sno AND
       sc.Cno='2' AND sc.Grade>90;


— 3.52查询每一门课的间接先修课(即先修课的先修课) 

SELECT FIRST.Cno,SECOND.Cpno
FROM course FIRST,course SECOND
WHERE FIRST.Cpno=SECOND.Cno;


— 3.53查询每个学生及其选修课程的情况 

SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM student LEFT OUTER JOIN sc ON(student.Sno=sc.Sno);


— 3.54查询每个学生的学号、姓名、选修的课程名及成绩。 

SELECT student.Sno,Sname,Cname,Grade
FROM student,sc,course
WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno;


— 嵌套查询

— 一个SELECT-FROM-WHERE语句称为一个查询块

— 将一个查询块嵌套在另一个查寻块的WHERE子句或HAVING短句的条件中的查询称为嵌套查询

— 3.55查询与”刘晨”在同一个系的学习的学生(不相关子查询) 

-- 嵌套查询
SELECT Sno,Sname,Sdept
FROM student
WHERE Sdept IN
            (SELECT Sdept
            FROM student
            WHERE Sname='刘晨');
-- 连接查询
SELECT Sno,Sname,Sdept
FROM student
WHERE Sdept IN
            (SELECT Sdept
            FROM student
            WHERE Sname='刘晨');


— 3.56查询选修了课程名为”信息系统”的学生学号和姓名 

-- 连接查询                         
SELECT student.Sno,Sname
FROM student,sc,course
WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno AND Cname='信息系统';
-- 嵌套查询(不相关子查询)
SELECT Sno,Sname
FROM student
WHERE Sno IN
        (SELECT Sno
         FROM sc
         WHERE Cno=
               (SELECT Cno
                FROM course
                WHERE Cname='信息系统'));


— 3.57找出每个学生超过他自己选修课程平均成绩的课程号 

SELECT Sno,Cno
FROM sc x
WHERE Grade>=(SELECT AVG(Grade)
              FROM SC y
                            WHERE y.Sno=x.Sno);    
--                             派生表查询
SELECT sc.Sno,Cno
FROM sc,(SELECT Sno,avg(grade) avg_grade FROM sc GROUP BY Sno) AS sc_avg
WHERE sc.Sno=sc_avg.sno AND grade>avg_grade


— 3.58查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄 

SELECT Sname,Sage
FROM student
WHERE Sage<ANY(SELECT Sage
                            FROM student
                            WHERE Sdept='CS');
AND Sdept <>'CS';


— 3.59查询非计算机科学系中比计算机科学系所有一个学生年龄小的学生姓名和年龄 

SELECT Sname,Sage
FROM student
WHERE Sage<ALL
             (SELECT Sage
                            FROM student
                            WHERE Sdept='CS')
AND Sdept <>'CS';


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

-- 嵌套查询
SELECT Sname
FROM student
WHERE EXISTS
       (SELECT *
             FROM sc
             WHERE Sno=student.Sno AND Cno='1');
--              派生表查询
SELECT Sname
FROM student,(SELECT * FROM sc WHERE Cno='1') AS sc1
WHERE student.Sno=sc1=Sno;


— 3.61查询没有选修一号课程的学生姓名 

SELECT Sname
FROM student
WHERE NOT EXISTS
       (SELECT *
             FROM sc
             WHERE Sno=student.Sno AND Cno='1');


—  3.62查询选修了全部课程的学生姓名 

SELECT Sname
FROM student
WHERE NOT EXISTS
                 (SELECT *
                  FROM course
                    WHERE NOT EXISTS
                              (SELECT *
                                        FROM sc
                                        WHERE Sno=student.Sno
                                        AND Cno=course.Cno));


— 3.63查询至少选修了学生201215122选修的全部课程的学生号码 

SELECT DISTINCT Sno
FROM sc SCX
WHERE NOT EXISTS
          (SELECT *
                    FROM sc SCY
                    WHERE SCY.Sno='201215122'AND
                    NOT EXISTS
                    (SELECT *
                    FROM sc SCZ
                    WHERE SCZ.Sno=SCX.Sno AND
                           SCZ.Cno=SCY.Cno));


—  3.64查询计算机科学系的学生及年龄不大于19岁的学生 

-- 单表查询
SELECT *
FROM student 
WHERE Sdept='CS' OR Sage<='19';
-- 集合查询
SELECT *
FROM student
WHERE Sdept='CS'
UNION
SELECT *
FROM student
WHERE Sage<=19;


— 3.65查询选修了课程1或者课程2的学生 

-- 单表查询
SELECT DISTINCT Sno
FROM sc 
WHERE Cno='1'OR Cno='2';
-- 集合查询
SELECT Sno
FROM sc
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';


— 3.66查询计算机科学系的学生与年龄不大于19岁的学生的交集

— 单表查询 

SELECT *
FROM student
WHERE Sdept='CS' AND Sage<='19'; 
-- 集合查询
SELECT *
FROM student
WHERE Sdept='CS'
-- UNION
INTERSECT
SELECT *
FROM student
WHERE Sage<=19;
-- 
SELECT *
FROM student
WHERE Sdept='CS' AND
       Sage<=19;


— 3.67查询既选修了课程1又选修了课程2的学生

— 集合查询 

SELECT Sno
FROM sc
WHERE Cno='1' AND Sno IN
                                     (SELECT Sno
                                     FROM sc
                                     WHERE Cno='2');


— –嵌套查询(不相关子查询） 

SELECT Sno
FROM sc
WHERE Cno='1' AND Sno IN
                                     (SELECT Sno
                                     FROM sc
                                     WHERE Cno='2');


— 3.68查询计算机科学系的学生与年龄不大于19岁的学生的差集

— 单表查询 

SELECT *
FROM student
WHERE Sdept='CS' AND Sage>'19';


— 嵌套查询(不相关子查询） 

SELECT *
FROM student
WHERE Sdept='CS' AND Sno NOT IN(SELECT *
                                FROM student
                                WHERE Sage<='19';


— 嵌套查询(相关子查询） 

SELECT *
FROM student 
WHERE Sdept='CS' AND NOT EXISTS(SELECT * FROM student y WHERE y.Sno=x.Sno AND Sage<='19');                                                  


— 集合查询 

SELECT *
FROM student
WHERE Sdept='CS'
EXCEPT
-- UNION
SELECT *
FROM student
WHERE Sage<='19';
-- 
SELECT *
FROM student
WHERE Sdept='CS' AND Sage>19;


— 3.69将一个新学生元组(学号：201215135，姓名:陈冬,性别:男,所在系:IS ,年龄：18岁)插入到student表中 

INSERT
INTO student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215135','陈冬','男'，'IS',18);


— 3.5.1插入数据

— 1.插入元组

— 3.70将学生张成民的信息插入到student表中。

INSERT INTO student
    VALUES
    ( '201215126', '张成民', '男'， 18, 'CS' );


—     3.71插入一条选课记录(‘201215128′,’1’)。 

INSERT 
INTO sc(Sno,Cno)
VALUES('201215128','1');
-- 
INSERT 
INTO sc
VALUES('201215128','1',NULL);


— 2.插入子查询结果

— 3.72对每一个系,求学生的平均年龄,再把系名和平均年龄存入新表中. 

CREATE TABLE Dept_age
             (Sdept CHAR(15)
              Avg_age SMALLINT);
                INSERT
                INTO Dept_age(Sdept,AVG_age)
                SELECT Sdept,AVG(Sage)
                FROM student
                GROUP BY Sdept;


— 3.5.2修改数据

— 修改某一个元组的值

— 3.73将学生201215121的年龄改为22岁 

UPDATE student
SET Sage=22
WHERE Sno='201215121';    


— 2.修改多个元组的值

— 3.74将所有学生的年龄增加1岁

UPDATE student
        SET Sage=Sage+1;


—         3.带子查询的修改语句

— 3.75将计算机科学系全体学生的成绩置零 

UPDATE sc
SET Gra de=0
WHERE Sno IN
         (SELECT Sno
         FROM student
         WHERE Sdept='CS');


—          3.5.3删除数据

— 1.删除某一个元组的值

— 3.76删除学号为201215128的学生记录 

DELETE 
FROM student
WHERE Sno='201215128';


— 2.删除多个元组的值

— 3.77删除所有的学生选课记录 

DELETE 
FROM sc;


— 3.带子查询的删除语句

— 3.78删除计算机科学系所有的学生选课记录 

DELETE
FROM sc;
WHERE Sno IN
      (SELECT Sno
            FROM student
            WHERE Sdept='CS');


— 3.6空值的处理

— 3.79向sc表中插入一个元组，学生号是”201215126″，课程号是”1″,成绩为空 

INSERT INTO sc(Sno,Cno,Grade)
        VALUES('201215126','1',NULL);         /*在插入时该学生还没有考试成绩，取空值*/
                或
INSERT INTO sc(Sno,Cno)
        VALUES('201215126','1');                    /*在插入语句中没有赋值的属性，其值为空值*/


— 3.80将student表中学生号为”201215200″的学生所属的系改为空值 

UPDATE student
SET Sdept=NULL
WHERE Sno='201215200';


— 2.空值的判断

— 3.81从student表中找出漏填了数据的学生信息 

SELECT *
FROM student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;


— 3.82找出选修1号课程的不及格的学生 

SELECT Sno
FROM sc
WHERE Grade<60 AND Cno='1';


— 3.83找出选修1号课程的不及格的学生以及缺考的学生 

SELECT Sno
FROM sc
WHERE Grade<60 AND Cno='1'
UNION 
SELECT Sno
FROM sc
WHERE Grade IS NULL AND Cno='1';


— 3.84建立信息系学生的视图 

CREATE VIEW IS_student 
AS 
SELECT Sno, Sname,Sage 
FROM student 
WHERE Sdept = 'IS';


— 3.85建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。 

CREATE VIEW a_gtx
AS
SELECT Sno,Sname,Sage
FROM student
WHERE Sdept='IS'
WITH CHECK OPTION;


— 3.86建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩）。 

CREATE VIEW V_DSL(Sno,Sname,Grade)
as
SELECT student.Sno,Sname,Grade
FROM  student ,sc
WHERE Sdept='IS' AND
student.Sno=sc.Sno.AND
sc.Cno=1;


— 3.88定义一个反映学生出生年份的视图 

CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM student;


— 3.89将学生的学号及平均成绩定义为一个视图 

CREATE VIEW S_G(Sno,Gavg)
AS 
SELECT Sno,AVG(Grade)
FROM sc
GROUP BY Sno;


— 3.90将student表中所有女生记录定义为一个视图 

CREATE VIEW F_Stundent(F_sno,name,sex,age,dept)
AS
SELECT *
FROM student
WHERE Ssex='女';


— 3.91删除视图BT_S和视图IS_SI 

DROP VIEW BT_S;
DROP VIEW IS_SI;


— 3.92在信息系学生的视图中找出年龄小于20岁的学生 

SELECT Sno,Sage
FROM is_student
WHERE Sage<20;
-- 
SELECT Sno,Sage
FROM student
WHERE Sdept='IS' AND Sage<20;


— 3.93查询选修了1号课程的信息系学生。 

SELECT is_student.Sno,Sname
FROM is_student
WHERE is_student.Sno=sc.Sno AND sc.Sno='1';


— 3.94在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩 

SELECT *
FROM S_G
WHERE Gavg>=90;
-- 
SELECT *
FROM (SELECT Sno,AVG(Grade)
       FROM sc
             GROUP BY Sno) AS S_G(Sno,Gavg)
 WHERE Gavg>=90;


— 3.95将信息系学生视图IS-Student中学号为”201215122″的学生姓名改为”刘辰” 

UPDATE is_student
SET Sname='刘辰'
WHERE Sno='201215122';
-- 转换后的更新语句为
UPDATE student
SET Sname='刘辰'
WHERE Sno='201215122' AND Sdept='IS';


— 3.96向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为”201215129″,姓名为”赵新”,年龄为20岁。 

INSERT 
INTO is_student
VALUES('201215129','赵新',20);
-- 
INSERT
INTO student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');


— 3.97删除信息系学生视图IS-Student中学号为”201215129″的记录。 

DELETE 
FROM is_student
WHERE Sno='201215129';
-- 
DELETE
FROM student
WHERE Sno='201215129' AND Sdept='IS';


版权声明：本文为CNSA183_20原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。