?
CREATE TABLE student
(
? ? ? ?stu_id NUMBER,
? ? ? ?stu_name NVARCHAR2(20),
? ? ? ?stu_age NUMBER(3) DEFAULT 15,
? ? ? ?stu_sex NUMBER(1),
? ? ? ?CONSTRAINT PK_stu_id PRIMARY KEY (stu_id)
);
?
COMMENT ON TABLE student IS '学员';
COMMENT ON COLUMN student.stu_id IS '主键';
?
CREATE TABLE teacher
(
? ? ? ?tea_id ?NUMBER,
? ? ? ?tea_name NVARCHAR2(20),
? ? ? ?CONSTRAINT PK_teacher_tea_id ?PRIMARY KEY (tea_id),
? ? ? ?CONSTRAINT UQ_tea_name unique (tea_name)?
);
?
CREATE TABLE course
(
? ? ? ?cou_id NUMBER,
? ? ? ?cou_name NVARCHAR2(20),
? ? ? ?cou_tea_id NUMBER,
? ? ? ?CONSTRAINT PK_course PRIMARY KEY (cou_id)
);
?
CREATE TABLE score
(
? ? ? ?stu_id NUMBER,
? ? ? ?cou_id NUMBER,
? ? ? ?score VARCHAR2(50)
);
?
CREATE SEQUENCE seq_stu_id START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE SEQUENCE seq_tea_id START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE SEQUENCE seq_cou_id START WITH 1 INCREMENT BY 1 NOMAXVALUE;
?
ALTER TABLE course ADD CONSTRAINT FK_course_tea_id?
FOREIGN KEY (cou_tea_id) REFERENCES teacher(tea_id) ;
ALTER TABLE score ADD CONSTRAINT FK_score_stu_id?
FOREIGN KEY (stu_id) REFERENCES student(stu_id) ;
ALTER TABLE score ADD CONSTRAINT FK_score_cou_id?
FOREIGN KEY (cou_id) REFERENCES course(cou_id) ;
?
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'李红',17,0);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'王米',17,0);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'戴小凤',19,0);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'李小凤',17,0);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'陈红',18,0);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'张喜',17,1);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'李刚',16,1);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'张正峰',16,1);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'王君',16,1);
INSERT INTO student(stu_id,stu_name,stu_age,stu_sex)
VALUES (seq_stu_id.nextval,'吴越',17,1);
?
INSERT INTO teacher (tea_id,tea_name)
VALUES (seq_tea_id.nextval,'李果');
INSERT INTO teacher (tea_id,tea_name)
VALUES (seq_tea_id.nextval,'毛勇军');
INSERT INTO teacher (tea_id,tea_name)
VALUES (seq_tea_id.nextval,'范君蒲');
INSERT INTO teacher (tea_id,tea_name)
VALUES (seq_tea_id.nextval,'卢君红');
?
?
INSERT INTO course(cou_id,cou_name,cou_tea_id)
VALUES (seq_cou_id.nextval,'数学',2);
INSERT INTO course(cou_id,cou_name,cou_tea_id)
VALUES (seq_cou_id.nextval,'历史',3);
INSERT INTO course(cou_id,cou_name,cou_tea_id)
VALUES (seq_cou_id.nextval,'语文',1);
INSERT INTO course(cou_id,cou_name,cou_tea_id)
VALUES (seq_cou_id.nextval,'英语',4);
?
INSERT INTO score(stu_id,cou_id,score)
VALUES (1,3,77);
INSERT INTO score(stu_id,cou_id,score)
VALUES (2,1,78);
INSERT INTO score(stu_id,cou_id,score)
VALUES (2,2,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (2,3,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (2,4,55);
INSERT INTO score(stu_id,cou_id,score)
VALUES (3,1,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (3,2,80);
INSERT INTO score(stu_id,cou_id,score)
VALUES (3,4,66);
INSERT INTO score(stu_id,cou_id,score)
VALUES (4,1,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (4,2,55);
INSERT INTO score(stu_id,cou_id,score)
VALUES (4,3,78);
INSERT INTO score(stu_id,cou_id,score)
VALUES (4,4,89);
INSERT INTO score(stu_id,cou_id,score)
VALUES (5,1,62);
INSERT INTO score(stu_id,cou_id,score)
VALUES (5,2,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (5,4,57);
INSERT INTO score(stu_id,cou_id,score)
VALUES (6,1,76);
INSERT INTO score(stu_id,cou_id,score)
VALUES (6,2,45);
INSERT INTO score(stu_id,cou_id,score)
VALUES (6,3,87);
INSERT INTO score(stu_id,cou_id,score)
VALUES (6,4,56);
INSERT INTO score(stu_id,cou_id,score)
VALUES (7,1,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (7,2,73);
INSERT INTO score(stu_id,cou_id,score)
VALUES (7,3,55);
INSERT INTO score(stu_id,cou_id,score)
VALUES (8,1,67);
INSERT INTO score(stu_id,cou_id,score)
VALUES (8,2,49);
INSERT INTO score(stu_id,cou_id,score)
VALUES (8,3,56);
INSERT INTO score(stu_id,cou_id,score)
VALUES (8,4,75);
INSERT INTO score(stu_id,cou_id,score)
VALUES (9,1,58);
INSERT INTO score(stu_id,cou_id,score)
VALUES (9,2,66);
INSERT INTO score(stu_id,cou_id,score)
VALUES (9,3,78);
INSERT INTO score(stu_id,cou_id,score)
VALUES (9,4,59);
INSERT INTO score(stu_id,cou_id,score)
VALUES (10,1,48);
INSERT INTO score(stu_id,cou_id,score)
VALUES (10,2,77);
INSERT INTO score(stu_id,cou_id,score)
VALUES (10,3,78);
INSERT INTO score(stu_id,cou_id,score)
VALUES (10,4,64);
?
数据库有以下几表:
student 学员表
列名 | 数据类型 | 含义 |
stu_id | int | 主键,标识列 |
stu_name | nvarchar(20) | 性名 |
stu_age | int | 年龄，默认15 |
stu_sex | bit | 姓别，1男，0女 |
?
teacher 教师表
列名 | 数据类型 | 含义 |
tea_id | int | 主键,标识列 |
tea_name | nvarchar(20) | 教师名，唯一 |
?
course 课程项
列名 | 数据类型 | 含义 |
cou_id | int | 主键 |
cou_name | nvarchar(20) | 课程名 |
cou_tea_id | int | 外键，教师 |
score 成绩表
列名 | 数据类型 | 含义 |
stu_id | int | 外键，学员 |
cou_id | int | 外键，课程 |
score | varchar(50) | 分数 |
1. 完成以下查询功能，每个功能只写一条SQL命令
查询“1”课程比“2”课程成绩高的所有学生的学号；
select stu_id from(select s1.stu_id,s1.score c1,s2.score c2,s3.score c3 from score s1,score s2,score s3 where s1.stu_id=s2.stu_id and s2.stu_id=s3.stu_id and s1.cou_id=1 and s2.cou_id=2 and s3.cou_id=3)where c1>c2
select a.stu_id,a.score,b.score from (select * from score sc where sc.cou_id=1) a ,(select * from score sc where sc.cou_id=2) b where a.stu_id=b.stu_id and a.score>b.score
2. 查询平均成绩大于70分的同学的学号和平均成绩；
select sc.stu_id,avg(sc.score) from score sc group by sc.stu_id having avg(sc.score)>70
3. 查询所有同学的学号、姓名、参考课数、总成绩；
select s.stu_id,s.stu_name,count(sc.cou_id),sum(sc.score) from score sc ,student s,course c where sc.stu_id=s.stu_id and sc.cou_id=c.cou_id group by s.stu_id,s.stu_name
select s.stu_id,s.stu_name,count(sc.cou_id),sum(sc.score) from student s left join score sc on s.stu_id=sc.stu_id left join course c on c.cou_id=sc.cou_id group by s.stu_id,s.stu_name
4. 查询没学过“李果”老师课的同学的学号、姓名;
select s.stu_id from student s where s.stu_id not in(select stu_id from teacher t left join course c on t.tea_id=c.cou_tea_id left join score sc on sc.cou_id=c.cou_id where tea_name='李果')
5. 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select * from student s where s.stu_id in(select a.stu_id from score a,score b where a.stu_id=b.stu_id and a.cou_id=1 and b.cou_id=2)
select s.* from score a join score b on a.stu_id=b.stu_id join student s on s.stu_id=a.stu_id where a.cou_id=1 and b.cou_id=2
select * from student s where s.stu_id in(select sc.stu_id from score sc,course c where sc.cou_id=1 and c.cou_id=2)
6. 查询学过“李果”老师所教的所有课的同学的学号、姓名；
select s.stu_id,s.stu_name from student s where s.stu_id in(select sc.stu_id from teacher t,course c,score sc where t.tea_id=c.cou_tea_id and sc.cou_id=c.cou_id and t.tea_name='李果'
7. 查询所有课程成绩小于60分的同学的学号、姓名;
select * from student s where s.stu_id not in(select distinct sc.stu_id from score sc where sc.score>=60)
8. 查询没有学全所有课的同学的学号、姓名
select s.stu_id,s.stu_name from student s where s.stu_id in(select sc.stu_id from score sc group by sc.stu_id having count(sc.cou_id)<(select count(c.cou_id) from course c))
9. 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select s.stu_id,s.stu_name from student s where s.stu_id!=1 and s.stu_id in (select distinct s.stu_id from score s where s.cou_id in(select sc.cou_id from score sc where sc.stu_id=1))
select * from student s,(select a.stu_id from score a,(select * from score sc where sc.stu_id=1) b where a.cou_id=b.cou_id) h where s.stu_id=h.stu_id and s.stu_id!=1
select s.stu_id,s.stu_name from score sc left join student s on s.stu_id=sc.stu_id where sc.stu_id!=1 and sc.cou_id in(select a.cou_id from score a where a.stu_id=1)
?