土豆烤肉_数据库作业3（增删改查）

土豆烤肉_
已于 2024-07-04 21:38:37 修改
阅读量194
点赞数 2
文章标签：数据库 oracle 服务器
于 2024-07-03 15:29:17 首次发布
本文链接：https://blog.csdn.net/2302_76940053/article/details/140153546
版权
-- 学院表Department03，外键Dheader，参照Teacher03表的Tno
ALTER TABLE department03
ADD CONSTRAINT fk_Dheader FOREIGN KEY (Dheader) REFERENCES teacher03(Tno);

-- 专业表Major03，外键Dno，参照Department03表的Dno
ALTER TABLE major03
ADD CONSTRAINT fk_Dno FOREIGN KEY (Dno) REFERENCES department03(Dno);

-- 学生表Students03，外键Mno，参照Major03表的Mno
ALTER TABLE students03
ADD CONSTRAINT fk_Mno FOREIGN KEY (Mno) REFERENCES major03(Mno);

-- 学生表Students03，外键Dno，参照Department03表的Dno
ALTER TABLE students03
ADD CONSTRAINT fk_Student_Dno FOREIGN KEY (Dno) REFERENCES department03(Dno);

-- 教师表Teacher03，外键Dno，参照Department03表的Dno
ALTER TABLE teacher03
ADD CONSTRAINT fk_Teacher_Dno FOREIGN KEY (Dno) REFERENCES department03(Dno);

-- 选课表Report03，外键Sno，参照Students03表的Sno
ALTER TABLE report03
ADD CONSTRAINT fk_Sno FOREIGN KEY (Sno) REFERENCES students03(Sno);

-- 选课表Reports03，外键Cno，参照Courses03表的Cno
ALTER TABLE report03
ADD CONSTRAINT fk_Cno FOREIGN KEY (Cno) REFERENCES courses03(Cno);

-- 课程表Courses03，外键Pre_Cno，参照Courses03表的Cno
ALTER TABLE courses03
ADD CONSTRAINT fk_Pre_Cno FOREIGN KEY (Pre_Cno) REFERENCES courses03(Cno);

-- 授课表Tutors03，外键Tno，参照Teacher03表的Tno
ALTER TABLE tutors03
ADD CONSTRAINT fk_Tno FOREIGN KEY (Tno) REFERENCES teacher03(Tno);

-- 授课表Tutors03，外键Cno，参照Courses03表的Cno
ALTER TABLE tutors03
ADD CONSTRAINT fk_Tutor_Cno FOREIGN KEY (Cno) REFERENCES courses03(Cno);



/*实验四-T2.请写出下列操作的SQL语句："在学院表中增加一个新的学院，外国语学院，学院编号为15，院长暂时不设置。"*/
INSERT INTO department03 (Dname,Dno)
VALUES ('外国语学院', 15);

/*T3.请写出下列操作的SQL语句："学号为2014112103的学生的数据结构课程的补考成绩为63分，请用补考成绩更新原有的考试成绩。"
请根据要求完成操作，并将操作结果截图（可用SELECT语句查询更新后的结果），截图的右下角要求标上你的学号最后两位
 */
UPDATE report03
JOIN courses03 c on report03.Cno = c.Cno
SET Grade = 63
WHERE Sno = '2014112103' AND Cname = '数据结构';

SELECT * FROM report03
JOIN courses03 c on report03.Cno = c.Cno
WHERE Sno = '2014112103' AND Cname = '数据结构';

/*T4.请写出下列操作的SQL语句："为表Tutors增加新列Tutid，将该列设置为第一列，并将Tutid列设置为自动增长的整数列，同时设置该列为主键”。
请根据要求完成操作，并将操作后的结果截图（可打开Tutors表，将最后5条信息截图），截图的右下角要求标上你的学号最后两位*/
ALTER TABLE tutors03
ADD COLUMN Tutid INT AUTO_INCREMENT PRIMARY KEY FIRST;

/*T5.请写出下列操作的SQL语句："2018学年第1学期，为20171151班的所有学生开设了计算机网络课程，并由“许永军”教授进行授课"。
请根据要求完成操作，并将操作结果截图（可打开各表，将新增加的信息截图），截图的右下角要求标上你的学号最后两位
提示：（1）以上操作需要对多个数据表进行更新操作，所用到的数据必须是根据要求中提供的数据完成；（2）某一个属性列的取值可以由子查询获得*/
/*插入许永军老师的授课信息到Tutors03表中：*/
UPDATE tutors03 O
JOIN courses03 C on O.Cno = C.Cno
JOIN teacher03 T on O.Tno = T.Tno
SET O.Tacademicyear=2018 ,O.Tterm=1 ,O. Sclass=20171151
WHERE C.Cname='计算机网络'AND T.Tname='许永军';
/*班级编号为20171151的学生，插入他们的选课信息到Report03表中 */
UPDATE report03 R
JOIN students03 s on R.Sno = s.Sno
JOIN courses03 c on R.Cno = c.Cno
SET R.Racademicyear=2018,R.Rterm=1
WHERE S.Sclass='20171151'AND C.Cname='计算机网络';


/*T6.请写出下列操作的SQL语句："李桂清教授光荣退休了，需要将他在2018学年第2学期给20161121班的学生所上的数字图像制作课程将改由李力授课"。
请根据要求完成操作，并将操作结果截图（可用SELECT语句查询更新后的结果），截图的右下角要求标上你的学号最后两位
提示：（1）所用到的数据必须是根据要求中提供的数据完成；（2）某一个属性列的取值可以由子查询获得 */
/*删除李桂清教授所授的数字图像制作课程*/
DELETE FROM Report03
WHERE Cno = (SELECT Cno FROM Courses03 WHERE Cname = '数字图像制作')
AND Racademicyear = '2018'
AND Rterm = '2'
AND Sno IN (SELECT Sno FROM Students03 WHERE Sclass = '20161121');

/* 将这门课程改由李力教授授课：*/
UPDATE Tutors03
SET Tno = (SELECT Tno FROM Teacher03 WHERE Tname = '李力')
WHERE Cno = (SELECT Cno FROM Courses03 WHERE Cname = '数字图像制作')
AND Tno = (SELECT Tno FROM Teacher03 WHERE Tname = '李桂清')
AND Tacademicyear = '2018'
AND Tterm = '2'
AND Sclass = '20161121';

/* 插入新的选课记录，反映李力教授接手数字图像制作课程： */
INSERT INTO Report03 (Sno, Cno, Racademicyear, Rterm)
VALUES ((SELECT Sno FROM Students03 WHERE Sclass = '20161121'),
        (SELECT Cno FROM Courses03 WHERE Cname = '数字图像制作'),
        '2018',
        '2');
/*用SELECT语句查询更新后的结果 */
SELECT * FROM Report03
WHERE Cno = (SELECT Cno FROM Courses03 WHERE Cname = '数字图像制作')
AND Racademicyear = '2018'
AND Rterm = '2'
AND Sno IN (SELECT Sno FROM Students03 WHERE Sclass = '20161121');


/*T7.请写出下列操作的SQL语句："李桂清教授光荣退休了，需要将他的信息从教师表中删除"。*/
DELETE FROM Teacher03
WHERE Tname = '李桂清';


/*T9.请写出下列操作的SQL语句："学号为2017120202的学生因病退学了，请从学生表中将他的信息删除"。*/
DELETE FROM Students03
WHERE Sno = '2017120202';


/*T10.请写出下列操作的SQL语句："① 将选课表中对Sno的外键声明重新设置可为级联删除；② 再从学生表中将学号2017120202的学生信息删除"。
请根据要求完成操作，并回答以下问题：① 能删除成功吗？② 若删除失败，请说明原因；若删除成功，请说明删除后的结果是怎样的。*/
/*① 将选课表中对Sno的外键声明重新设置可为级联删除*/
ALTER TABLE report03
DROP FOREIGN KEY fk_Sno;

ALTER TABLE report03
ADD CONSTRAINT fk_Sno
FOREIGN KEY (Sno)
REFERENCES students03(Sno)
ON DELETE CASCADE;

DELETE FROM students03
WHERE Sno = '2017120202';

/*T11.请写出下列操作的SQL语句："由于人才培养方案的变更，数学与统计学院的数据科学与大数据技术（非师范）专业的编号将变更为编号1203，
  请直接将专业表中的专业编号1202更改为1203的信息"。
若更新成功，请用SELECT语句在专业表中查询数据科学与大数据技术（非师范）专业的相关信息*/
UPDATE major03
SET Mno = '1203'
WHERE Mno = '1202';


SELECT * FROM major03
WHERE Mname = '数据科学与大数据技术（非师范）';

/*T12.请写出下列操作的SQL语句："① 将学生表中对Mno的外键声明重新设置可为级联更新；
  ② 再重新更新专业表中数据科学与大数据技术（非师范）专业的专业编号"。*/
ALTER TABLE students03
DROP FOREIGN KEY fk_Mno;

ALTER TABLE students03
ADD CONSTRAINT fk_Mno
FOREIGN KEY (Mno) REFERENCES major03 (Mno)
ON UPDATE CASCADE;

UPDATE major03
SET Mno = '1203'
WHERE Mname = '数据科学与大数据技术（非师范）';


/*T13.请写出下列操作的SQL语句："分别任命李力、张兰和张雪为本学院的院长"。要求：使用一条更新语句完成次操作。
请根据要求完成操作，并将操作结果截图（可用SELECT语句查询更新后的结果），截图的右下角要求标上你的学号最后两位
提示：（1）所用到的数据必须是根据要求中提供的数据完成；（2）可以先用子关系查询获得三位教师的教师编号和所在学院编号，
  然后再用关联子关系的更新语句修改Department中的数据。*/
UPDATE department03
SET Dheader = CASE
                WHEN Dno = (SELECT Dno FROM teacher03 WHERE Tname='李力') THEN (SELECT Tno FROM teacher03 WHERE Tname='李力')
                WHEN Dno = (SELECT Dno FROM teacher03 WHERE Tname='张兰') THEN (SELECT Tno FROM teacher03 WHERE Tname='张兰')
                WHEN Dno = (SELECT Dno FROM teacher03 WHERE Tname='张雪') THEN (SELECT Tno FROM teacher03 WHERE Tname='张雪')
            END
WHERE Dno IN (SELECT Dno FROM teacher03 WHERE Tname IN ('李力', '张兰', '张雪'));

SELECT
    d.Dname AS Department_Name,
    t.Tname AS Department_Head
FROM
    department03 AS d
JOIN
    teacher03 AS t ON d.Dheader = t.Tno;

/*T14.请写出下列操作的SQL语句："创建一个教师工作量统计表TeaQuantity，其中包含统计编号Qid、教师编号Tno、
  学年Tacademicyear和工作量Tquantity，其中Tno和Tacademicyear的数据类型与Tutors表中的设置相同，
  Qid的数据类型定义为Binary(16)，默认输入UNIQUEINDETIFIER(UUID)的二进制数，且设置为该表的主键，
  Tquantity的数据类型设置为整数，并且在属性列Tno和Tacademicyear上设置唯一性约束，即约束该表中每个教师编号，
  每个学年只统计一个工作量"
请根据要求完成操作，并将操作结果截图（可对左端列表中TeaQuantity表的相关信息截图），截图的右下角要求标上你的学号最后两位*/
CREATE TABLE IF NOT EXISTS TeaQuantity(
    Qid VARCHAR(36) PRIMARY KEY DEFAULT (UUID()) COMMENT '统计编号',
    Tno CHAR(4) COMMENT '教师编号',
    Tacademicyear year COMMENT '学年',
    Tquantity INT,
    UNIQUE(Tno,Tacademicyear)
);
-- BINARY(16)
/*T15.请写出下列操作的SQL语句："对所有教师的授课工作量按学年进行统计，即统计出每个学年该位教师所上课程的总课时量，
  并将统计结果添加到TeaQuantity表中"。
请根据要求完成操作，并将操作结果截图（将TeaQuantity表中编号为'T004'的教师的工作量的查询结果截图）*/
ALTER TABLE TeaQuantity MODIFY Qid CHAR(36);

INSERT INTO TeaQuantity(Qid, Tno, Tacademicyear, Tquantity)
SELECT UUID(), Tno, Tacademicyear, SUM(C.Chours)
FROM tutors03 AS Tut
JOIN courses03 AS C ON Tut.Cno = C.Cno
GROUP BY Tno, Tacademicyear;

SELECT *
FROM TeaQuantity
WHERE Tno='T004';

/*T16.请写出下列操作的SQL语句："① 2019学年第1学期，为20171202班的所有学生开设了数据库系统原理，
  并由“王平”老师进行授课；② 重新对“王平”老师的授课工作量按学年进行统计，再次将统计结果添加到TeaQuantity表中"。
提示：① 第一步操作与前面的操作类似，需对多个表完成操作；② “将统计结果添加到TeaQuantity表中”，指的是执行数据插入语句*/
-- Qid字段已经是主键，并且你想要修改它的属性（比如添加自增）
ALTER TABLE TeaQuantity MODIFY Qid VARCHAR(36);
-- 多次执行插入后的删除语句
DELETE FROM report03
WHERE Cno = '112p0046' AND Sno IN (SELECT Sno FROM students03 WHERE Sclass = '20171202');
-- 1. 找出王平老师的教师编号
SET @TeacherId = (SELECT Tno FROM teacher03 WHERE Tname = '王平');

-- 2. 找出数据库系统原理的课程编号
SET @CourseId = (SELECT Cno FROM courses03 WHERE Cname = '数据库系统原理');

-- 3. 为20171202班的所有学生注册这门课程
INSERT INTO report03 (Sno, Cno, Racademicyear,Rterm)
SELECT Sno, @CourseId, '2019','1'
FROM students03
WHERE Sclass = '20171202';
-- 插入统计结果到TeaQuantity表
INSERT INTO TeaQuantity(Tno, Tacademicyear, Tquantity)
SELECT @TeacherId AS Tno, O.Tacademicyear AS Tacademicyear, SUM(C.Chours) AS Tquantity
FROM tutors03 O
JOIN courses03 C ON O.Cno = C.Cno
WHERE O.Tno = @TeacherId -- 确保是王平老师授课的课程
GROUP BY O.Tno, O.Tacademicyear;


/*T17.请写出下列操作的SQL语句："重新对“王平”老师的授课工作量按学年进行统计，并将统计结果更新到TeaQuantity表中"。
请根据要求完成操作，并将操作结果截图（用SELECT语句在TeaQuantity表中查询王平的工作量），截图的右下角要求标上你的学号最后两位*/
INSERT INTO TeaQuantity (Tno, Tacademicyear, Tquantity)
SELECT t.Tno, c.Tacademicyear, 0 -- 初始化为0或NULL，取决于你的业务逻辑
FROM (
    SELECT DISTINCT Tacademicyear
    FROM tutors03
) c
CROSS JOIN (
    SELECT Tno
    FROM teacher03
    WHERE Tname = '王平'
) t
LEFT JOIN TeaQuantity tq ON t.Tno = tq.Tno AND c.Tacademicyear = tq.Tacademicyear
WHERE tq.Tno IS NULL; -- 只插入不存在的记录

UPDATE TeaQuantity tq
SET Tquantity = (
    SELECT SUM(c.Chours)
    FROM tutors03 t
    JOIN courses03 c on t.Cno = c.Cno
    WHERE t.Tno = (SELECT Tno FROM teacher03 WHERE Tname = '王平')
      AND t.Tacademicyear = tq.Tacademicyear
)
WHERE Tno = (SELECT Tno FROM teacher03 WHERE Tname = '王平')
  AND EXISTS (
    SELECT 1
    FROM courses03 c
    JOIN tutors03 t ON c.Cno = t.Cno
    WHERE t.Tno = tq.Tno
      AND t.Tacademicyear = tq.Tacademicyear
  );

/*T18.请写出下列操作的SQL语句："① 在学生表中增加CHECK约束：Sno的长度必须为10个字符长度；
  ② 分别向学生表中添加一条正确的学生信息和一条错误的学生信息，验证CHECK约束是否能正确执行。"*/
ALTER TABLE students03
MODIFY Sno CHAR(10) CHECK ( LENGTH(Sno)=10 );

INSERT INTO students03(Sno,Sname)
VALUES ('2021051295','李华');

INSERT INTO students03(Sno,Sname)
VALUES ('202503005103','巴勃罗毕加索');

DELETE FROM students03
WHERE Sno = '2021051295';

/*T19.请写出下列操作的SQL语句："在教师表中增加CHECK约束：
若教师的职称是教授，则其工资Tsal要在3500元以上；
若教师的职称是副教授，则其工资要在2600元到3500元之间；
若教师的职称是讲师，则其工资要在1600元到2600元之间；
若教师的职称是助教，则其工资要在800元到1600元之间。"*/
ALTER TABLE teacher03
ADD CONSTRAINT Check_Tsal CHECK (
    (Tprof='教授' AND Tsal>'3500')OR
    (Tprof='副教授' AND Tsal>'2600' AND Tsal<'3500')OR
    (Tprof='讲师' AND Tsal>'1600' AND Tsal<'2600')OR
    (Tprof='助教' AND Tsal>'800' AND Tsal<'1600')
    );

/*T20.请写出下列操作的SQL语句："张兰老师因评上教授，需将其职称(Tprof)由副教授改为教授，
  并将工资(Tsal)改为3300，岗位津贴(Tcomm)改为2400。"
请根据要求完成操作，并回答以下问题：① 能更新成功吗？
② 若更新失败，请说明原因；若更新成功，请用SELECT语句在教师表中查询该位教师的相关信息*/
UPDATE teacher03
SET Tprof='教授',Tsal='3300',Tcomm='2400'
WHERE Tname='张兰';

/*T21.请写出下列操作的SQL语句："许红霞老师评上了副教授，需将其职称(Tprof)由讲师改为副教授，
  并将工资(Tsal)改为2700，岗位津贴(Tcomm)改为2000。"
请根据要求完成操作，并回答以下问题：① 能更新成功吗？
② 若更新失败，请说明原因；若更新成功，请用SELECT语句在教师表中查询该位教师的相关信息*/
UPDATE teacher03
SET Tprof='副教授',Tsal='2700',Tcomm='2000'
WHERE Tname='许红霞';

SELECT * FROM teacher03 WHERE Tname = '许红霞';