學(xué)生選課數(shù)據(jù)庫SQL語句練習(xí)題(詳細(xì)分解答案

一、設(shè)有一數(shù)據(jù)庫，包括四個表：學(xué)生表（Student）、課程表（Course）、成績表（Score）以及教師信息表（Teacher）。四個表的結(jié)構(gòu)分別如表1-1的表（一）表（四）所示，數(shù)據(jù)如表1-2的表（一）表（四）所示。用SQL語句創(chuàng)建四個表并完成相關(guān)題目。表1-1數(shù)據(jù)庫的表結(jié)構(gòu) 表（一）Student 屬性名數(shù)據(jù)類型可否為空含 義SnoChar(3)否學(xué)號（主鍵）SnameChar(8)否學(xué)生姓名SsexChar(2)否學(xué)生性別Sbirthdaydatetime可學(xué)生出生年月ClassChar(5)可學(xué)生所在班級表（二）Course屬性名數(shù)據(jù)類型可否為空含 義CnoChar(5)否課程號（主鍵）CnameVarchar(10)否課程名稱TnoChar(3)否教師編號（外鍵）表（三）Score屬性名數(shù)據(jù)類型可否為空含 義SnoChar(3)否學(xué)號（外鍵）CnoChar(5)否課程號（外鍵）DegreeDecimal(4,1)可成績主碼：Sno+ Cno表（四）Teacher屬性名數(shù)據(jù)類型可否為空含 義TnoChar(3)否教師編號（主鍵）TnameChar(4)否教師姓名TsexChar(2)否教師性別Tbirthdaydatetime可教師出生年月ProfChar(6)可職稱DepartVarchar(10)否教師所在部門表1-2數(shù)據(jù)庫中的數(shù)據(jù)表（一）StudentSnoSnameSsexSbirthdayclass108曾華男1977-09-0195033105匡明男1975-10-0295031107王麗女1976-01-2395033101李軍男1976-02-2095033109王芳女1975-02-1095031103陸君男1974-06-0395031 表（二）CourseCnoCnameTno3-105計算機導(dǎo)論8253-245操作系統(tǒng)8046-166數(shù)字電路8569-888高等數(shù)學(xué)831表（三）ScoreSnoCnoDegree1033-245861053-245751093-245681033-105921053-105881093-105761013-105641073-105911083-105781016-166851076-166791086-16681表（四）TeacherTnoTnameTsexTbirthdayProfDepart804李誠男1958-12-02副教授計算機系856張旭男1969-03-12講師電子工程系825王萍女1972-05-05助教計算機系831劉冰女1977-08-14助教電子工程系- 1、查詢Student表中的所有記錄的Sname、Ssex和Class列。 select sname,ssex,class from student;- 2、查詢教師所有的單位即不重復(fù)的Depart列。 select distinct depart from Teacher;- 3、查詢Student表的所有記錄。 select * from student;- 4、查詢Score表中成績在60到80之間的所有記錄。 select * from score where degree between 60 and 80;- 5、查詢Score表中成績?yōu)?5，86或88的記錄。 select * from score where degree in(85,86,88);- 6、查詢Student表中“95031”班或性別為“女”的同學(xué)記錄。 select * from student where class = 95031 or ssex=女;- 7、以Class降序查詢Student表的所有記錄。 select * from student order by class desc;- 8、以Cno升序、Degree降序查詢Score表的所有記錄。 select * from score order by cno,degree desc;- 9、查詢“95031”班的學(xué)生人數(shù)。 select class,count(*) as 學(xué)生人數(shù) from student group by class having class=95031;- 10、查詢Score表中的最高分的學(xué)生學(xué)號和課程號。（子查詢或者排序） select sno,cno,degree, (select max(degree) from score) as maxscore-計算最高分 from score where degree= (select max(degree) from score);- 11、查詢3-105號課程的平均分。 select avg(degree) as avgdegree from score group by cno having cno=3-105;- 12、查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。 select avg(degree) as avgdegree from score group by cno -按照課程分組取平均值 having cno= (select cno from score group by cno having count(*)=5)-至少有5名學(xué)生選修的課程 and cno like 3%;-以3開頭的課程- 13、查詢最低分大于70，最高分小于90的Sno列。 select sno,max(degree)as maxdegree,min(degree) as mindegree from Score group by sno having max(degree)70- 14、查詢所有學(xué)生的Sname、Cno和Degree列。 select sname,cno,degree from student join score on student.sno=score.sno;- 15、查詢所有學(xué)生的Sno、Cname和Degree列。 select sno,cname,degree from Score join course on So=o;- 16、查詢所有學(xué)生的Sname、Cname和Degree列。 select sname,cname,degree from student join score on student.sno=score.sno join course on So=o;- 17、查詢“95033”班所選課程的平均分。 select avg(degree) as avgdegree from score where sno in(select sno from student where class=95033)18、假設(shè)使用如下命令建立了一個grade表：create table grade(low int(3),upp int(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(60,69,D)insert into grade values(0,59,E)-現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列。 select sno,cno, (case when degree between 90 and 100 then A when degree between 80 and 89 then B when degree between 70 and 79 then C when degree between 60 and 69 then D when degree between 0 and 59 then EEND) as rank from score;- 19、 查詢選修“3-105”課程的成績高于“109”號同學(xué)成績的所有同學(xué)的記錄。 select * from score where cno=3-105and degree(select degree from score where sno=109 and cno=3-105);- 20、查詢score中選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄。 select * from score where sno in-選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非最高分成績的同學(xué)的全記錄 (select sno from score group by sno having count(cno)1-選學(xué)多門課程的同學(xué) intersect-取交集為選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非最高分成績的同學(xué)。 select distinct sno from score where sno not in( -分?jǐn)?shù)為非最高分成績的同學(xué) select sno from score where degree=(select max(degree) from score)-分?jǐn)?shù)最高成績的同學(xué) - 21、查詢score中選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非同課程最高分成績的記錄。 方法1：select * from score where sno in-選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非同課程最高分成績的同學(xué)的全記錄 (select sno from score group by sno having count(cno)1-選學(xué)多門課程的同學(xué) intersect-取交集為選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非同課程最高分成績的同學(xué)。 select distinct sno from score where sno not in(-非同課程分?jǐn)?shù)最高成績的同學(xué) select distinct sno from score where degree in (-同課程分?jǐn)?shù)最高成績的同學(xué) select max(degree)from score group by cno)-同課程分?jǐn)?shù)最高成績 方法2：select * from score where sno in-選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非同課程最高分成績的同學(xué)的全記錄 (select sno from score group by sno having count(cno)1- 選學(xué)多門課程的同學(xué) intersect - 取交集為選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為同課程非最高分成績的同學(xué) select distinct sno from score where sno not in - 選出非同課程最高分成績的同學(xué)(select distinct sno from score as s1 where degree=(select max(degree) from score as s2 where o=o group by cno);- 使用關(guān)聯(lián)子查詢選出同課程最高分成績的同學(xué)- 22、查詢1975年之后出生的學(xué)生的所學(xué)課程以及成績。 select sname,Cname,degree from student join score on student.sno=score.sno join course on o=o where sbirthday=1975-01-01;- 23、查詢和學(xué)號為107的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。 select sno,sname,sbirthday from student where datepart(year,sbirthday)= (select datepart(year,sbirthday) from student where sno=107)-學(xué)號為107的同學(xué)的出生年份 and sno not in(107);-排除學(xué)號為107的同學(xué)- 24、查詢“張旭”教師任課的學(xué)生成績。 select degree from score where cno= (select cno from course join teacher on teacher.tno=course.tno where tname=張旭);-張旭老師所任課程- 25、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名。 select tname from teacher join course on teacher.tno=course.tno where cno in (select cno from score group by cno having count(*)5);- 多于5名同學(xué)選修的課程- 26、查詢95033班和95031班全體學(xué)生的記錄。 select * from student where class in(95033,95031);- 27、查詢存在有85分以上成績的課程Cno. select distinct cno from score where degree85;- 28、查詢出“計算機系”教師所教課程的成績表。 select o,degree from score join course on o=o where tno in (select tno from teacher where depart=計算機系);-計算機系教師的教師編號- 29、查詢“計算機系”與“電子工程系”不同職稱的教師的Tname和Prof。 select tname,prof from teacher where depart in(計算機系,電子工程系)-“計算機系”與“電子工程系”所有教師Tname和Profand prof not in -“計算機系”與“電子工程系”不同職稱的教師Prof(select prof from teacher where depart =計算機系intersectselect prof from teacher where depart =電子工程系)-“計算機系”與“電子工程系”相同職稱的教師Prof- 30、查詢選修編號為“3-105“課程且成績至少高于一個選修編號為“3-245”的同學(xué)的Cno、Sno和Degree,并按Degree從高到低次序排序。select cno,sno,degree from score wherecno=3-105-選修編號為“3-105”課程的同學(xué)and degreeany - 大于任意一個選修編號為“3-245”的同學(xué)的成績(select degree from score where cno=3-245)-選修編號為“3-245”的同學(xué)的成績order by degree desc- 31、查詢選修編號為“3-105“課程且成績高于所有選修編號為“3-245”的同學(xué)的Cno、Sno和Degree,并按Degree從高到低次序排序。select cno,sno,degree from score wherecno=3-105-選修編號為“3-105”課程的同學(xué)and degreeall - 大于所有選修編號為“3-245”的同學(xué)的成績(select degree from score where cno=3-245)-選修編號為“3-245”的同學(xué)的成績order by degree desc- 32、查詢所有教師和同學(xué)的name、sex和birthday.select sname as name,ssex as sex,sbirthday as birthday from studentunionselect tname as name,tsex as sex,tbirthday as birthday from teacher- 33、查詢所有“女”教師和“女”同學(xué)的name、sex和birthday.select sname as name,ssex as sex,sbirthday as birthday from student where ssex=女unionselect tname as name,tsex as sex,tbirthday as birthday from teacher where tsex=女- 34、查詢成績比該課程平均成績低的同學(xué)的成績表。select * from score as s1 where degree1；select Class,COUNT(*) from Student where Ssex=男g(shù)roup by Class having COUNT(*)=2；- 38、查詢Student表中不姓“王”的同學(xué)記錄。select * from student where sname not like 王%- 39、查詢Student表中每個學(xué)生的姓名和年齡。select sname,datediff(year,Sbirthday,current_timestamp)as 年齡 from student；select sname,datediff(year,Sbirthday,getdate() as 年齡 from student；select sname,datepart(year,getdate()-datepart(year,Sbirthday) as 年齡 from student；- 40、查詢Student表中最大和最小的Sbirthday日期值。select datepart(year,max(sbirthday)as max,datepart(year,min(sbirthday) as min from student;select max(year(sbirthday)as max,min(year(sbirthday) as min from student;- 41、以班號和年齡從大到小的順序查詢Student表中的全部記錄。select * from student order by class desc,Sbirthday - 42、查詢“男”教師及其所上的課程。select tname,tsex,cname from teacher left join course on course.tno=teacher.tno where tsex=男- 43、查詢最高分同學(xué)的Sno、Cno和Degree列。select student.sno,cno,degree from student join Score on Score.sno=student.snowhere degree=(select max(degree) from score);- 44、查詢和“李軍”同性別的所有同學(xué)的Sname.select sname from student where ssex= -與李