SQL的語句練習題(1).doc

1、一、對于學生和課程之間的學習關系，有如下的屬性：學生（學號，姓名，性別，出生日期，民族，班級，家庭住址，聯(lián)系電話）課程（課程ID，課程名稱）學習（學號，課程ID，成績）1畫出這種關系的E-R圖。2指出各表的主鍵字段。3在此數據庫的基礎上，寫出SQL語句。（1）查出所有男同學的基本信息（2）查出學生的學號，姓名，班級，課程名稱，成績（3）查出成績表中成績大于平均成績的信息（4）查出民族為漢族或者回族的學生的所有基本信息（5）查出成績在7080分之間的學生姓名，課程名稱和成績二、.設職工社團數據庫有三個基本表:職工（職工號，姓名，年齡，性別）；社會團體（編號，名稱，負責人，活動地點）；參加（職工號

2、，編號，參加日期）。其中：1）職工表的主碼為職工號。2）社會團體表的主碼為編號；外碼為負責人，被參照表為職工表，對應屬性為職工號。3）參加表的職工號和編號為主碼；職工號為外碼，其被參照表為職工表，對應屬性為職工號；編號為外碼，其被參照表為社會團體表，對應屬性為編號。試用SQL語句表達下列操作：l）定義職工表、社會團體表和參加表，并說明其主碼和參照關系。2）建立下列兩個視圖。社團負責人（編號，名稱，負責人職工號，負責人姓名，負責人性別）；參加人情況（職工號，姓名，社團編號，社團名稱，參加日期）3）查找參加唱歌隊或籃球隊的職工號和姓名。4）查找沒有參加任何社會團體的職工情況。5）查找參加了全部社會

3、團體的職工情況。6）查找參加了職工號為“1001”的職工所參加的全部社會團體的職工號。7）求每個社會團體的參加人數。8）求參加人數最多的社會團體的名稱和參加人數。9）求參加人數超過100人的社會團體的名稱和負責人。10）把對社會團體和參加兩個表的數據查看、插入和刪除數據的權力賦給用戶李平，并允許他再將此權力授予其他用戶。建立一個數據庫表student,數據表computer,字段名name,number,sex,SQL2000,flash,net ,其中SQL2000,flash,net設置為浮點型 float.1、輸出所有男生的成績use studentselect yuyan as SQL

4、數據庫 ,flash as 網絡動畫,net as 計算機網絡 from computerwhere sex=男2、輸出所有SQL成績在90以上的女生的成績use studentselect SQL2000 as SQL數據庫 from computerwhere sex=女and SQL2000=903、輸出某一科目不合格所有的男生的成績use studentselect yuyan as SQL數據庫 ,flash as 網絡動畫,net as 計算機網絡 from computerwhere sex=男and SQL200060 or flash60 or net=9012、用SQL命令

5、向成績表添加一個新字段C語言use studentalter table computeradd c語言 float問題描述：已知關系模式：s (sno,sname）學生關系。sno為學號，sname為姓名c (cno,cname,cteacher) 課程關系。cno為課程號，cname為課程名，cteacher為任課教師sc(sno,cno,scgrade) 選課關系。scgrade為成績要求實現如下5個處理：1找出沒有選修過“李明”老師講授課程的所有學生姓名2列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績3列出既學過“1”號課程，又學過“2”號課程的所有學生姓名4列出“1”號課成

6、績比“2”號同學該門課成績高的所有學生的學號5列出“1”號課成績比“2”號課成績高的所有學生的學號及其“1”號課和“2”號課的成績1. 找出沒有選修過“李明”老師講授課程的所有學生姓名-實現代碼：select sname from swhere not exists(select * from sc,cwhere o=oand c.cteacher=李明and sc.sno=s.sno)2. 列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績-實現代碼：select s.sno,s.sname,avg_scgrade=avg(sc.scgrade)from s,sc,(select sn

7、ofrom scwhere scgrade=2)a where s.sno=a.sno and sc.sno=a.snogroup by s.sno,s.sname3. 列出既學過“1”號課程，又學過“2”號課程的所有學生姓名-實現代碼：select s.sno,s.snamefrom s,(select sc.snofrom sc,cwhere o=oand ame in(1,2)group by snohaving count(distinct cno)=2)sc where s.sno=sc.sno4. 列出“1”號課成績比“2”號同學該門課成績高的所有學生的學號-實現代碼：select

8、 s.sno,s.snamefrom s,sc sc1,sc sc2where o=1and sc2.sno=2and o=oand sc1.scgradesc2.scgrade5. 列出“1”號課成績比“2”號課成績高的所有學生的學號及其“1”號課和“2”號課的成績-實現代碼：select sc1.sno,1號課成績=sc1.scgrade,2號課成績=sc2.scgradefrom sc sc1,sc sc2where o=1and o=2and sc1.sno=sc2.snoand sc1.scgradesc2.scgrade創(chuàng)建表和輸入數據CREATE TABLE STUDENT(SN

9、O VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL,SSEX VARCHAR(2) NOT NULL, SBIRTHDAY DATETIME,CLASS VARCHAR(5)goCREATE TABLE COURSE(CNO VARCHAR(5) NOT NULL, CNAME VARCHAR(10) NOT NULL, TNO VARCHAR(10) NOT NULL)goCREATE TABLE SCORE (SNO VARCHAR(3) NOT NULL, CNO VARCHAR(5) NOT NULL, DEGREE NUMERIC(10,

10、 1) NOT NULL) goCREATE TABLE TEACHER (TNO VARCHAR(3) NOT NULL, TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL, TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6), DEPART VARCHAR(10) NOT NULL)INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,曾華 ,男 ,1977-09-01,95033);INSERT INTO STUDENT

11、 (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,匡明 ,男 ,1975-10-02,95031);INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,王麗 ,女 ,1976-01-23,95033);INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,李軍 ,男 ,1976-02-20,95033);INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,

12、CLASS) VALUES (109 ,王芳 ,女 ,1975-02-10,95031);INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,陸君 ,男 ,1974-06-03,95031);GOINSERT INTO COURSE(CNO,CNAME,TNO)VALUES (3-105 ,計算機導論,825)INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (3-245 ,操作系統(tǒng) ,804);INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (6-16

13、6 ,數據電路 ,856);INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (9-888 ,高等數學 ,100);GOINSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,3-245,86);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,3-245,75);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,3-245,68);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,3-105,92);INSER

14、T INTO SCORE(SNO,CNO,DEGREE)VALUES (105,3-105,88);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,3-105,76);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,3-105,64);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,3-105,91);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,3-105,78);INSERT INTO SCORE(SNO,CNO,DEGR

15、EE)VALUES (101,6-166,85);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,6-106,79);INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,6-166,81);GOINSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,李誠,男,1958-12-02,副教授,計算機系);INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (8

16、56,張旭,男,1969-03-12,講師,電子工程系);INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES (825,王萍,女,1972-05-05,助教,計算機系);INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,劉冰,女,1977-08-14,助教,電子工程系);練習題目1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。2、 查詢教師所有的單位即不重復的Depart列。3、 查詢Student表的

17、所有記錄。4、 查詢Score表中成績在60到80之間的所有記錄。5、 查詢Score表中成績?yōu)?5，86或88的記錄。6、 查詢Student表中“95031”班或性別為“女”的同學記錄。7、 以Class降序查詢Student表的所有記錄。8、 以Cno升序、Degree降序查詢Score表的所有記錄。9、 查詢“95031”班的學生人數。10、查詢Score表中的最高分的學生學號和課程號。11、查詢3-105號課程的平均分。12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。13、查詢最低分大于70，最高分小于90的Sno列。14、查詢所有學生的Sname、Cno和D

18、egree列。15、查詢所有學生的Sno、Cname和Degree列。16、查詢所有學生的Sname、Cname和Degree列。17、查詢“95033”班所選課程的平均分。18、假設使用如下命令建立了一個grade表：create table grade(low number(3,0),upp number(3),rank char(1);insert into grade values(90,100,A);insert into grade values(80,89,B);insert into grade values(70,79,C);insert into grade values(

19、60,69,D);insert into grade values(0,59,E);commit;現查詢所有同學的Sno、Cno和rank列。19、查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。20、查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。21、查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。23、查詢“張旭“教師任課的學生成績。24、查詢選修某課程的同學人數多于5人的教師姓名。25、查詢95033班和95031班全體學生的記錄

20、。26、查詢存在有85分以上成績的課程Cno.27、查詢出“計算機系“教師所教課程的成績表。28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof。29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和Degree,并按 Degree從高到低次序排序。30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和Degree.31、查詢所有教師和同學的name、sex和birthday.32、查詢所有“女”教師和“女”同學的name、sex和birthday.33、查詢成績比該課程平均成績低的同

21、學的成績表。34、查詢所有任課教師的Tname和Depart.35、 查詢所有未講課的教師的Tname和Depart. 36、查詢至少有2名男生的班號。37、查詢Student表中不姓“王”的同學記錄。38、查詢Student表中每個學生的姓名和年齡。39、查詢Student表中最大和最小的Sbirthday日期值。40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。41、查詢“男”教師及其所上的課程。42、查詢最高分同學的Sno、Cno和Degree列。43、查詢和“李軍”同性別的所有同學的Sname.44、查詢和“李軍”同性別并同班的同學Sname.45、查詢所有選修“計算機

22、導論”課程的“男”同學的成績表SQL語句練習題參考答案 1、 select Sname,Ssex,Class from Student;2、 select distinct depart from teacher;3、 select Sno as 學號,Sname as 姓名,Ssex as 性別,Sbirthday as出生日期,Class as班號from student;或select Sno as 學號,Sname as 姓名,Ssex as 性別,Sbirthday as 出生日期,Class as 班號 from student;4、 select * from score whe

23、re degree between 60 and 80;或select * from score where degree=60 and degree5;13、select Sno from score group by Sno having min(degree)70 and max(degree)y.degree and y.sno=109and o=3-105;20、1,查詢成績非本科最高 select * from score b where degree y.degree and y.sno=109and o=3-105;select cno,sno,degree from scor

24、e where degree (select degree from score where sno=109 and cno=3-105)22、select sno,sname,sbirthday from student where to_char(sbirthday,yyyy)=(select to_char(sbirthday,yyyy) from student where sno=108);23、select cno,sno,degree from score where cno=(select o from course x,teacher y where x.tno=y.tno

25、and y.tname=張旭);24、select tname from teacher where tno in(select x.tno from course x,score y where o=o group by x.tno having count(x.tno)5);25、select * from student where class in(95033,95031);26、select distinct cno from score where degree in (select degree from score where degree85);27、select * fro

26、m score where cno in(select o from course x,teacher y where y.tno=x.tno and y.depart=計算機系);28、select tname,prof from teacher where depart=計算機系 and prof not in (select prof from teacher where depart=電子工程系);29、select * from score where cno=3-105 and degreeany (select degree from score where cno=3-245)

27、order by degree desc;30、select * from score where cno=3-105 and degreeall(select degree from score where cno=3-245);31、select tname,tsex,tbirthday from teacherunion select sname,ssex,sbirthday from student;32、select tname,tsex,tbirthday from teacher where tsex=女union select sname,ssex,sbirthday from

28、 student where ssex=女;33、select * from score a where degree=2;37、select * from student where sname not like王_;38、select sname as 姓名,(to_char(sysdate,yyyy)-to_char(sbirthday,yyyy) as 年齡 from student39、select sname,sbirthday as 最大 from student where sbirthday =(select min (sbirthday) from student)unio

29、n select sname,sbirthday as 最小 from student where sbirthday =(select max(sbirthday) from student) 40、select class,sname,sbirthday from student order by class desc,sbirthday;41、select x.tname,ame from teacher x,course y where x.tno=y.tno and x.tsex=男;42、select * from score where degree=(select max(de

30、gree)from score);43、select sname from student where ssex=(select ssex from student where sname=李軍);44、select sname from student where ssex=(select ssex from student where sname=李軍) and class=(select class from student where sname=李軍);45、select * from score where sno in(select sno from student where

31、ssex=男) and cno=(select cno from coursewhere cname=計算機導論);有四個表(關系）：product(maker,model,type)maker:產品制造商，model:型號，type:產品分類(pc,printer,laptop)pc(model,speed,ram,hd,cd,price)laptop(model,speed,ram,hd,screen,price)printer(model,color,price)四個表中主鍵都是model,求一SQL命令，能查出所有產品中價格(price)最高的產品。select modelfrom p

32、roductwhere model in (select modelfrom (select pc.model model,pc.price pricefrom produce,pcwhere pc.model=product.modelunionselect laptop.model model,laptop.price pricefrom produce,laptopwhere laptop.model=product.modelunionselect printer.model model,printer.price pricefrom produce,printerwhere prin

33、ter.model=product.model)where price in (select max(price)from (select pc.model model,pc.price pricefrom produce,pcwhere pc.model=product.modelunionselect laptop.model model,laptop.price pricefrom produce,laptopwhere laptop.model=product.modelunionselect printer.model model,printer.price pricefrom pr

34、oduce,printerwhere printer.model=product.model)1.設有如下4個關系模式： 書店（書店編號，書店名，地址） 圖書（書號，書名，定價） 圖書館（館號，館名，城市，電話） 圖書發(fā)行（館號，書號，書店號，數量） 設各關系模式中的數據滿足下列問題，請回答： （1）用SQL語句檢索已發(fā)行的圖書中最貴的書名和定價。 （2）寫出下列SQL語句所表達的中文意思。 select 館名 from 圖書館 where 館號 in （select 館號 from 圖書發(fā)行 where 書號 in （select 書號 from 圖書 where 書名=數據庫系統(tǒng)基礎）； 2.關于教學數據庫的關系式如下： S（S#，SNAME，AGE，SEX） SC（S#，C#，CRADE） C（C#，CNAME，TEACHER） 其中，S表示學生，它的各屬性依次為學號、姓名、年齡、性別；