实验题目:polarDB云数据库创建和数据操作
1.试验任务
- 写出所给8张表的DDL定义语句;(因为外键冲突建议用以下顺序建立表,当然,我是小白所以只能这样的本办法)建议一个表一个表建立,也就是下面的代码一块一块跑,然后可以右侧看一下有没有建好,报错在下面。
create table department
(
dno char(2) not null ,
`name` VARCHAR(20) not null ,
dean char(8) null ,
addr varchar(40) null ,
phone varchar(12) null ,
PRIMARY KEY (dno)
#FOREIGN KEY (dean)REFERENCES teacher(tno)
);
create table teacher
(
tno char(8) not null ,
`name` varchar(8) not null ,
sex char(2) null ,
birthday DATE null ,
`rank` VARCHAR(8) null ,
salary int null ,
home varchar(40) null ,
zipcode char(6) null ,
tel varchar(12) null ,
email varchar(32) null ,
dno char(2) null,
PRIMARY KEY (tno),
FOREIGN KEY (dno)REFERENCES department(dno)
);
create table cousetype
(
ctno char(1)not null ,
name varchar(16)not null ,
PRIMARY KEY (ctno)
);
create table speciality
(
spno char(2) not null ,
dno char(2) not null ,
name varchar(20) not null ,
PRIMARY KEY (spno),
FOREIGN KEY (dno)REFERENCES department(dno)
);
create table student
(
sno char(20) not null ,
sname varchar(8) not null ,
sex char(2) null ,
nation varchar(20) null ,
birthday date null ,
spno char(4) null ,
classno char(8) null ,
entime char(4) null ,
home varchar(40) null ,
tel varchar(40) null ,
dno char(2) null ,
PRIMARY KEY (sno),
FOREIGN KEY (spno)REFERENCES speciality(spno)
);
create table course
(
cno char(10) not null ,
name VARCHAR(20) not null ,
spno char(20) null ,
ctno char(1) null ,
experiment smallint null ,
hours smallint null ,
semester smallint null ,
credit smallint null ,
`duty_tno` char(8) null ,
dno char(2) null ,
PRIMARY KEY (cno),
FOREIGN KEY (dno)REFERENCES department(dno),
FOREIGN KEY (spno)REFERENCES speciality(spno),
FOREIGN KEY (ctno)REFERENCES cousetype(ctno),
FOREIGN KEY (`duty_tno`)REFERENCES teacher(tno)
);
create table teach
(
tno char(8)not null ,
cno char(10)not null ,
year_semester char(6)not null ,
classno char(1)not null ,
PRIMARY KEY (tno,cno,year_semester,classno),
FOREIGN KEY (tno)REFERENCES teacher(tno),
FOREIGN KEY (cno)REFERENCES course(cno)
);
create table enroll
(
sno char(20) not null ,
cno char(20) not null ,
year_semester char(6) not null ,
tno char(8) null ,
classno char(1) null ,
score float null,
PRIMARY KEY (sno,cno,year_semester),
FOREIGN KEY (tno)REFERENCES teacher(tno),
FOREIGN KEY (sno)REFERENCES student(sno),
FOREIGN KEY (cno)REFERENCES course(cno),
FOREIGN KEY (tno)REFERENCES teacher(tno)
);
这里呢,第一个表和第二个表互有来自对方的外键,所以在创建时不能直接给第一个表加外键,最后要加一句设置主键,但是也建议在加完一二两个表的数据之后再运行,不然你是插不进第一个表的数据的。
alter table department add foreign key (dean)REFERENCES teacher(tno);
2. 写出所给8张表中数据行的添加DML语句;
(原来的数据有一些问题,我们的助教改了一些,也可以把有问题的数据删掉,我选的后面这种,删了不少)
INSERT INTO department(dno, name, dean, addr, phone) VALUES
('24','信息科学与工程学院','2011120','软件楼','8305424'),
('27','数学院','2007004','数学楼','8304551'),
('28','工商管理学院','2009007','MBA楼','8304530'),
('29','会计学院','1995008', '逸夫楼','8304578'),
('31','政治理论学院','20011024', '红叶楼','8304517'),
('32','机械学院','20001021','宇航楼','8304534'),
('33','工业设计学院','19971004','设计大楼','8305204');
下面的格式有点乱,要是为了好看那你可以自己在idea上好好改一下!!!!电脑没电了,潦草一下吧。
INSERT INTO teacher(tno, name, sex, birthday, `rank`, salary,dno) VALUES
('2004124','刘东妮','女','1985-1-9','助教','7200','24'),
('2011120','万世平','男','1962-12-11','教授','9900','24'),
('2001009','周曼玲','女','1972-09-09','副教授','8600','24'),
('2000121','李平','男','1973-04-12','助教','7500','24'),
('1997030','刘小明','女','1979-03-30','教授','9700','24'),
('20041024','朱喜','男','1985-1-9','副教授','8500','24'),
('20111104','王林林','女','1962-12-11','助教','7900','24'),
('2007004','张中华','男','1972-09-09','教授','9850','27'),
('2009007','马力','女','1973-04-12','教授','8650','28'),
('1995008','陈浪','男','1979-03-30','教授','7880','29'),
('20011024','刘亚平','女','1972-09-09','教授','10800','31'),
('20001021','张国荣','男','1973-04-12','教授','8950','32'),
('19971004','赵子龙','男','1979-03-30','教授','9650','33'),
('20081003','马千里','男','1979-03-30','副教授','8850','24');
insert into cousetype(ctno, name) VALUES
('1','公共课'),
('2','专业基础课'),
('3','专业课'),
('4','选修课');
insert into speciality(spno, name, dno) VALUES
('01','软件工程','24'),
('02','人工智能','24'),
('03','物联网','24'),
('04','网络安全','24'),
('11','电算会计','29'),
('08','工商管理','28'),
('09','市场营销','28');
INSERT INTO student(sno, sname, sex, nation, birthday,classno,dno)VALUES
('20182401101','李毅','男','汉','1985-1-2','软件1班','24'),
('20182401102','厉超','男','苗','1984-3-8','软件1班','24'),
('20182401103','梁冰龙','男','壮','1984-7-9','软件1班','24'),
('20182401104','罗煦','女','维吾尔','1985-10-12','软件1班','24'),
('20182401105','马晓睿','男','汉','1983-11-1','软件1班','24'),
('20182401106','聂立业','男','壮','1984-6-3','软件1班','24'),
('20182401107','王实','男','汉','1984-2-11','软件1班','24'),
('20182401108','伍卉','女','汉','1985-1-3','软件1班','24'),
('20182401109','伍琼','男','汉','1985-12-11','软件1班','24'),
('20182401110','曾文超','男','汉','1984-7-5','软件1班','24'),
('20182401201','黄文杰','男','汉','1984-7-5','软件2班','24'),
('20182401202','龙淼','男','汉','1985-12-11','软件2班','24'),
('20182709101','何晓明','男','汉','1984-8-13','营销1班','27'),
('20182709102','刘寅','男','汉','1984-5-7','营销1班','27'),
('20182911101','邢晓静','女','汉','1984-8-13','电算1班','28'),
('20182911102','李明伟','女','汉','1985-3-9','电算1班','28'),
('20182911103','童熙','女','汉','1985-10-16','电算1班','28'),
('20182911104','朱俊华','男','汉','1985-12-3','电算1班','28');
INSERT INTO course(cno, name, spno, ctno, hours, experiment, credit, semester, `duty_tno`, dno) VALUES
('H24030006','数据结构',null,'2','64','24','4','4','2004124','24'),
('H24030007','计算机组成原理',null,'2','64',null,'4','5','2011120','24'),
('H24030008','数据库系统',null,'3','48','24','3','5','2001009','24'),
('H24030009','操作系统',null,'3','48','24','3','4','2000121','24'),
('H24030010','计算机网络',null,'3','48','16','3','6','1997030','24'),
('C31010001','毛泽东思想概论',null,'1','32',null,'2','6',null,'31'),
('C31010002','邓小平理论',null,'1','62',null,'2','1',null,'31'),
('C24020005','面向对象程序设计',null,'2','32','24','2','5','20111104','24'),
('X27020012','高等数学',null,'1','64',null,'4','2','2007004','27'),
('X24040019','数据挖掘','02','4','32',null,'2','7',null,'24'),
('X24040022','网络安全概论','02','4','32',null,'2','7',null,'24');
insert into teach( cno, year_semester,tno, classno) VALUES
#('H61030006','2019-1','2004124','A'),
#('H61030008','2020-1','2001009','A'),
('C31010001','2019-1','20011024','A'),
('C24020005','2019-2','2009007','A'),
('C31010001','2019-1','20011024','B');
insert into enroll(sno, cno, year_semester, tno, classno, score) VALUES
('20182401101','H24030006','2019-1','2004124','A','89'),
('20182401102','H24030006','2019-1','2004124','A','98'),
('20182401103','H24030006','2019-1','2004124','A','86'),
('20182401104','H24030006','2019-1','2004124','B','76'),
('20182401101','H24030008','2020-1','2001009','A','83'),
('20182401102','H24030008','2020-1','2001009','A','66'),
('20182401103','H24030008','2020-1','2001009','A','55'),
('20182401104','H24030008','2020-1','2001009','B','87'),
('20182401202','C31010001','2019-1','20011024','A','88'),
('20182709101','C31010001','2019-1','20011024','A','90'),
('20182709102','C24020005','2019-2','2009007','A','83'),
('20182911101','C24020005','2019-2','2009007','A','55'),
('20182911101','C31010001','2019-1','20011024','B','87'),
('20182401101','C31010001','2019-1','20011024','A','88');
别忘了执行一下alter语句
接下来就是做题了,这个还是要自己写一下,不然期中考很容易挂的。
3.对于如下业务需求,写出获取所须数据的SQL语句:
①输出信息科学与工程学院(学院编号“24”)2018级学生“李毅”的课程选修成绩单,输出项包括课程名称,学分,成绩三项;
SELECT c.name,c.credit,e.score
FROM
course AS c,
enroll AS e
WHERE
c.cno=e.cno
AND e.sno=(
SELECT sno
FROM student
WHERE dno='24'
AND sname='李毅'
AND sno LIKE '2018%'
)
;
结果应该是
找出学生李毅(学号为 ‘20182401101’)的电话;
SELECT tel
FROM
student
WHERE
sno='20182401101'
AND sname='李毅'
;
输出信息科学与工程学院(学院编号为’24’)的学生清单,输出项包括姓名,学号,班级三列,按照班级和学号排序;
SELECT
sname,sno,classno
FROM
student
WHERE
dno='24'
ORDER BY
classno,sno;
输出所有学院的清单,包括学院编号,名称,地址,电话,院长姓名;
SELECT
D.dno,D.name,D.addr,D.phone,T.name
FROM
department AS D,teacher AS T
WHERE
D.dean=T.tno;
在2020-1学期选修了周曼玲老师(工号为”2001009”)开设的“数据库系统”课程的学生名单,输出项为五项:教学班,姓名、学号、性别,行政班。要求先按照教学班,再按行政班排序;(注:一个老师在某个学期开的某门课,完全有可能有多个教学班)
SELECT
E.classno,S.sname,S.sno,S.sex,S.classno AS EXCLASSNO
FROM
student AS S,
enroll AS E,
course AS C
WHERE E.sno=S.sno
AND E.cno=C.cno
AND E.year_semester LIKE '2020-1%'
AND C.duty_tno='2001009'
AND C.name='数据库系统'
ORDER BY
E.classno,S.classno;
对于在2020-1学期选修了“数据库系统”课程的各行政班,求其平均分,并按照平均分从高到低排序
SELECT
S.classno,AVG(E.score)AS AVGSCORE
FROM
enroll AS E,
course AS C,
student AS S
WHERE
E.cno=C.cno
AND E.sno=S.sno
AND C.name='数据库系统'
AND E.year_semester LIKE '2020-1%'
GROUP BY
S.classno
ORDER BY
AVGSCORE DESC ;
对于信息科学与工程学院(学院编号“24”)在2020-1学期开设的每门课程,输出其选修人数,输出项包括课程名称,选修人数;
SELECT
C.name,COUNT(DISTINCT E.sno) AS COUNT
FROM course AS C,
enroll AS E
WHERE C.cno=E.cno
AND C.dno='24'
AND E.year_semester='2020-1'
GROUP BY
C.name;
在2020-1学期,对于开设的“数据库系统”这门课程,在信息科学与工程学院(学院编号“24”)的2018级学生中,有哪些学生没有选修该课程,输出姓名,学号,行政班三项信息;
SELECT
sname,sno,classno
FROM
student
WHERE dno='24'
AND sno LIKE '2018%'
AND sno NOT IN (
SELECT
E.sno
FROM enroll AS E,
course AS C
WHERE
E.cno=C.cno
AND E.year_semester='2020-1'
AND C.name='数据库系统'
)
;
- 对于在2020-1学期开设的每门课程,输出其最高分、最低分,平均分;
SELECT C.name AS COURSENAME,MAX(E.score) AS MAXSCORE,MIN(E.score) MINSCORE,AVG(E.score) AVGSCORE FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester='2020-1' GROUP BY E.cno;
对于在2020-1学期开设的“数据库系统”课程,求选修了该课,但是没有及格的学生名单,输出姓名,学号,行政班,课程成绩四项信息;
-
SELECT S.sname,S.sno,S.classno,E.score FROM student AS S, enroll AS E WHERE S.sno=E.sno AND E.year_semester='2020-1' AND E.score<'60' AND E.cno= (SELECT course.cno FROM course WHERE name='数据库系统') ;
对于信息科学与工程学院(学院编号“24”)的每个教师,统计出其在2019年度的教学工作量;(提示:课程表中,每门课有课时量hours字段)
-
WITH TB0(CNO,HOURS,CLASSNO,TNO) AS (SELECT DISTINCT E.cno,C.hours,E.classno,E.tno FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester LIKE '2019%' AND E.tno IN ( SELECT T.tno FROM teacher AS T WHERE T.dno='24' ) ) SELECT TE.tno,SUM(HOURS) AS WORKSTIME FROM TB0 AS TT, teacher AS TE WHERE TT.TNO=TE.tno GROUP BY TNO;
对于信息科学与工程学院(学院编号“24”)的每个教师,统计在2019年度教学工作量未达到要求(小于150学时)的教师,输出教师姓名,工号,教学工作量三项;
WITH TB0(CNO,HOURS,CLASSNO,TNO) AS (SELECT DISTINCT E.cno,C.hours,E.classno,E.tno FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester LIKE '2019%' AND E.tno IN ( SELECT T.tno FROM teacher AS T WHERE T.dno='24' ) ) SELECT TE.name,TE.tno,SUM(HOURS) AS WORKSTIME FROM TB0 AS TT, teacher AS TE WHERE TT.TNO=TE.tno GROUP BY TNO HAVING WORKSTIME<150;
统计各个学院在2019年度的教学工作量;
WITH TB0(CNO,HOURS,CLASSNO,TNO) AS (SELECT DISTINCT E.cno,C.hours,E.classno,E.tno FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester LIKE '2019%' AND E.tno IN ( SELECT T.tno FROM teacher AS T ) ) SELECT TE.tno,SUM(TB.HOURS) FROM TB0 , teacher AS TE WHERE TB.TNO=TE.tno GROUP BY TE.tno ;
14.对于2016级学生,即将毕业,求出输出所得总学分不足172的学生,输出项为所属学院名称,学号,姓名,已得学
SELECT D.name,S.sno,SUM(C.credit) FROM course AS C, department AS D, student AS S WHERE D.dno=S.dno AND C.cno=( SELECT cno FROM enroll WHERE sno=( SELECT sno FROM student WHERE sno LIKE '2016%' ) ) GROUP BY S.sno HAVING SUM(C.credit)<172;
分数这四项;
附加题emmm做的比较烂就不放这里