重庆邮电大学 数据库技术及应用(A1040090) 实验三 代码

CREATE TABLE student
(
SNO VARCHAR(20),
Name VARCHAR(10),
Age Integer,
College VARCHAR(30)
);

CREATE TABLE Course
(
CourseID VARCHAR(15),
Coursename VARCHAR(30),
coursebeforeID VARCHAR(15)

);
CREATE table choose
(
sno VARCHAR(20),
courseid VARCHAR(30),
score DECIMAL(5,2)
);

INSERT INTO student VALUES('S00001','张三',20,'计算机学院');
INSERT INTO student VALUES('S00002','李四',19,'通信学院');
INSERT INTO student VALUES('S00003','王五',21,'计算机学院');


INSERT INTO course VALUES('C1','计算机引论',NULL);
INSERT INTO course VALUES('C2','C 语言','C1');
INSERT INTO course VALUES('C3','数据结构','C2');

INSERT INTO choose values('S00001','C1',95);
INSERT INTO choose values('S00001','C2',80);
INSERT INTO choose values('S00001','C3',84);
INSERT INTO choose values('S00002','C1',80);
INSERT INTO choose values('S00002','C2',85);
INSERT INTO choose values('S00003','C1',78);
INSERT INTO choose values('S00003','C3',70);

SELECT sno,Name FROM student WHERE college='计算机学院';
SELECT *FROM student WHERE age BETWEEN 20 and 23;
SELECT COUNT(*) FROM student;
SELECT max(score) FROM choose WHERE courseid='C1';
SELECT min(score) FROM choose WHERE courseid='C1';
SELECT sum(score) FROM choose WHERE courseid='C1';
SELECT avg(score) FROM choose WHERE courseid='C1';
SELECT courseid,coursename  FROM course where coursebeforeid is NULL;

SELECT student.sno,name,coursename,score
from student
join choose
on student.sno=choose.sno
join course
on choose.courseid=course.courseid;

select student.sno,name from student where (college='计算机学院') and exists(SELECT student.sno,name,coursename,score
from student
join choose
on student.sno=choose.sno
join course
on choose.courseid=course.courseid);


-- 16.用 SELECT 语句的嵌套查询，查询选修 C1 课程的成绩低于“张三”的学生的学号和成绩。
-- 这里新建了个表
CREATE TABLE select_15(
SNO VARCHAR(20),
Name VARCHAR(10),
Coursename VARCHAR(30),
score DECIMAL(5,2),
CourseID VARCHAR(15)
)
INSERT INTO SELECT_15
SELECT student.sno,name,coursename,score,choose.courseid
from student
join choose
on student.sno=choose.sno
join course
on choose.courseid=course.courseid;

SELECT sno ,score FROM select_15 WHERE sno IN( SELECT sno FROM choose where courseid='C1' and score<95)and courseid='C1';

-- 17.
SELECT sno FROM choose WHERE courseid='C1'
UNION
SELECT sno FROM choose WHERE courseid='C3';
-- 18.
SELECT DISTINCT sno FROM choose WHERE courseid='C1'
UNION
SELECT DISTINCT sno FROM choose WHERE courseid='C3';