当前位置: 代码迷 >> SQL >> sql 话语练习
  详细解决方案

sql 话语练习

热度:85   发布时间:2016-05-05 12:56:23.0
sql 语句练习

?

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)

?

  相关解决方案