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

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

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

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

4、數(shù)據(jù)庫 ,flash as 網(wǎng)絡(luò)動畫,net as 計算機(jī)網(wǎng)絡(luò) from computerwhere sex=男2、輸出所有SQL成績在90以上的女生的成績use studentselect SQL2000 as SQL數(shù)據(jù)庫 from computerwhere sex=女a(chǎn)nd SQL2000=903、輸出某一科目不合格所有的男生的成績use studentselect yuyan as SQL數(shù)據(jù)庫 ,flash as 網(wǎng)絡(luò)動畫,net as 計算機(jī)網(wǎng)絡(luò) from computerwhere sex=男and SQL200060 or flash60 or net=9012、用SQL命令

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

6、績比“2”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號5列出“1”號課成績比“2”號課成績高的所有學(xué)生的學(xué)號及其“1”號課和“2”號課的成績1. 找出沒有選修過“李明”老師講授課程的所有學(xué)生姓名-實(shí)現(xiàn)代碼：select sname from swhere not exists(select * from sc,cwhere o=oand c.cteacher=李明and sc.sno=s.sno)2. 列出有二門以上（含兩門）不及格課程的學(xué)生姓名及其平均成績-實(shí)現(xiàn)代碼：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. 列出既學(xué)過“1”號課程，又學(xué)過“2”號課程的所有學(xué)生姓名-實(shí)現(xiàn)代碼：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”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號-實(shí)現(xiàn)代碼：select

8、 s.sno,s.snamefrom s,sc sc1,sc sc2where o=1and sc2.sno=2and o=oand sc1.scgradesc2.scgrade5. 列出“1”號課成績比“2”號課成績高的所有學(xué)生的學(xué)號及其“1”號課和“2”號課的成績-實(shí)現(xiàn)代碼：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)建表和輸入數(shù)據(jù)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 ,計算機(jī)導(dǎo)論,825)INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (3-245 ,操作系統(tǒng) ,804);INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (6-16

13、6 ,數(shù)據(jù)電路 ,856);INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (9-888 ,高等數(shù)學(xué) ,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,副教授,計算機(jī)系);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,助教,計算機(jī)系);INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,劉冰,女,1977-08-14,助教,電子工程系);練習(xí)題目1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。2、 查詢教師所有的單位即不重復(fù)的Depart列。3、 查詢Student表的

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

18、egree列。15、查詢所有學(xué)生的Sno、Cname和Degree列。16、查詢所有學(xué)生的Sname、Cname和Degree列。17、查詢“95033”班所選課程的平均分。18、假設(shè)使用如下命令建立了一個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;現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列。19、查詢選修“3-105”課程的成績高于“109”號同學(xué)成績的所有同學(xué)的記錄。20、查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄。21、查詢成績高于學(xué)號為“109”、課程號為“3-105”的成績的所有記錄。22、查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。23、查詢“張旭“教師任課的學(xué)生成績。24、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名。25、查詢95033班和95031班全體學(xué)生的記錄

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

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

22、導(dǎo)論”課程的“男”同學(xué)的成績表SQL語句練習(xí)題參考答案 1、 select Sname,Ssex,Class from Student;2、 select distinct depart from teacher;3、 select Sno as 學(xué)號,Sname as 姓名,Ssex as 性別,Sbirthday as出生日期,Class as班號from student;或select Sno as 學(xué)號,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=計算機(jī)系);28、select tname,prof from teacher where depart=計算機(jī)系 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=計算機(jī)導(dǎo)論);有四個表(關(guān)系）：product(maker,model,type)maker:產(chǎn)品制造商，model:型號，type:產(chǎn)品分類(pc,printer,laptop)pc(model,speed,ram,hd,cd,price)laptop(model,speed,ram,hd,screen,price)printer(model,color,price)四個表中主鍵都是model,求一SQL命令，能查出所有產(chǎn)品中價格(price)最高的產(chǎn)品。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.設(shè)有如下4個關(guān)系模式： 書店（書店編號，書店名，地址） 圖書（書號，書名，定價） 圖書館（館號，館名，城市，電話） 圖書發(fā)行（館號，書號，書店號，數(shù)量） 設(shè)各關(guān)系模式中的數(shù)據(jù)滿足下列問題，請回答： （1）用SQL語句檢索已發(fā)行的圖書中最貴的書名和定價。 （2）寫出下列SQL語句所表達(dá)的中文意思。 select 館名 from 圖書館 where 館號 in （select 館號 from 圖書發(fā)行 where 書號 in （select 書號 from 圖書 where 書名=數(shù)據(jù)庫系統(tǒng)基礎(chǔ)）； 2.關(guān)于教學(xué)數(shù)據(jù)庫的關(guān)系式如下： S（S#，SNAME，AGE，SEX） SC（S#，C#，CRADE） C（C#，CNAME，TEACHER） 其中，S表示學(xué)生，它的各屬性依次為學(xué)號、姓名、年齡、性別；