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)
分享到：
相关推荐
sql语句练习，能有助于理解数据库的各项数据操作
这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习这是带答案的sql语句练习
经典的SQL语句练习
是关于图书借书表的SQL语句练习题，会对SQL语句的各个内容有所好的帮助
Sql语句练习答案Sql语句练习答案Sql语句练习答案Sql语句练习答案Sql语句练习答案Sql语句练习答案Sql语句练习答案Sql语句练习答案Sql语句练习答案
SQL语句基本以及加强练习题，基本查询，连接查询，嵌套查询等，基本的修、插、删、改等语句都有，是面试要掌握的基本东西
MySQL SQL语句练习题及答案
DDL用来创建数据库中的各种对象，包括数据库模式、表、视图、索引、同义词、聚簇等，它的基本语句有：CREATE DATABASE、CREATE TABLE、CREATE VIEW、CREATE INDEX等。 （4）数据控制语言（DCL） DCL用来授予或回收...
sql数据库练习题
SQL语句练习、学生可以查看自学SQL相关语句的练习
sql 语句 练习+ 答案 sql 语句 练习+ 答案 sql 语句 练习+ 答案
SQL语句练习系统 SQL语句练习系统
实验5 sql语句练习——图书馆数据库.docx
sql语句练习 sql语句练习 sql语句练习 sql语句练习 sql语句练习 sql语句练习 sql语句练习
SQL语句练习
SQL语句练习册，帮助程序员们记忆SQL语句，管理自己的日常开支；该应用没有混淆，帮助初级者学习怎么使用SQL语句，怎么向apk中嵌入广告
sql语句练习题及答案.doc
Oracle的sql语句练习题含答案，学习oracle的sql语句
Oracle的sql语句练习题及参考答案 希望一些程序员能够顺利通过oracle数据库考试