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)