1、精選優(yōu)質(zhì)文檔-傾情為你奉上一 在數(shù)據(jù)庫 school 中建立student , sc, course 表。學(xué)生表、課程表、選課表屬于數(shù)據(jù)庫 School ，其各自的數(shù)據(jù)結(jié)構(gòu)如下：學(xué)生 Student (Sno,Sname,Ssex,Sage,Sdept)序號列名含義數(shù)據(jù)類型長度1Sno學(xué)號字符型(char)62Sname姓名字符型(varchar)83Ssex性別字符型(char)24Sage年齡整數(shù) (smallint)5sdept系科字符型(varchar)15課程表 course(Cno,Cname,Cpno,Ccredit)序號列名含義數(shù)據(jù)類型長度1Cno課程號字符型(char)42c

2、name課程名字符型(varchar)203Cpno先修課字符型(char)44Ccredit學(xué)分短整數(shù) (tinyint)學(xué)生選課 SC(Sno,Cno,Grade)序號列名含義數(shù)據(jù)類型長度1Sno學(xué)號字符型(char)62Cno課程號字符型(char)43Grade成績小數(shù)(decimal)12,2二 設(shè)定主碼1 Student表的主碼：sno 2 Course表的主碼：cno3 Sc表的主碼：sno，cno 1寫出使用 Create Table 語句創(chuàng)建表 student , sc, course 的SQL語句2在student表中插入信息學(xué)號姓名性別年齡系科4001趙茵男20SX400

3、2楊華女21JSJ3 刪除student表中的元組 4在數(shù)據(jù)庫school中刪除關(guān)系student5在student表添加屬性sbirthdate 類型 datetime練習(xí) Delete1 刪除所有 JSJ 系的男生 delete from Student where Sdept=JSJ and Ssex=男;2 刪除“數(shù)據(jù)庫原理”的課的選課紀(jì)錄 delete from SC where Cno in (select Cno fromCourse where Cname=數(shù)據(jù)庫原理); Update1 修改 0001 學(xué)生的系科為: JSJ2 把陳小明的年齡加1歲，性別改為女。2 修改李文慶

4、的1001課程的成績?yōu)?93 分3 把“數(shù)據(jù)庫原理”課的成績減去1分Select 查詢語句一 單表1查詢年齡在19至21歲之間的女生的學(xué)號,姓名,年齡,按年齡從大到小排列。2查詢姓名中第2個字為“明”字的學(xué)生學(xué)號、性別。3查詢 1001課程沒有成績的學(xué)生學(xué)號、課程號4查詢JSJ 、SX、WL 系的年齡大于25歲的學(xué)生學(xué)號,姓名，結(jié)果按系排列5按10分制查詢學(xué)生的sno,cno,10分制成績 （1-10分 為1 ，11-20分為2 ，30-39分為3，。90-100為10） 6查詢 student 表中的學(xué)生共分布在那幾個系中。（distinct） 7查詢0001號學(xué)生1001，1002課程的成

5、績。二 統(tǒng)計1查詢姓名中有“明”字的學(xué)生人數(shù)。2計算JSJ系的平均年齡及最大年齡。3查詢學(xué)生中姓名為張明、趙英的人數(shù)4計算每一門課的總分、平均分，最高分、最低分，按平均分由高到低排列5 計算 1001,1002 課程的平均分。6 查詢平均分大于80分的學(xué)生學(xué)號及平均分 7 統(tǒng)計選修課程超過 2 門的學(xué)生學(xué)號8 統(tǒng)計有10位成績大于85分以上的課程號。9 統(tǒng)計平均分不及格的學(xué)生學(xué)號10 統(tǒng)計有大于兩門課不及格的學(xué)生學(xué)號三 連接 1查詢 JSJ 系的學(xué)生選修的課程號2查詢選修1002 課程的學(xué)生的學(xué)生姓名 (不用嵌套及嵌套2種方法）3查詢數(shù)據(jù)庫原理不及格的學(xué)生學(xué)號及成績4查詢選修“數(shù)據(jù)庫原理”課且

6、成績 80 以上的學(xué)生姓名(不用嵌套及嵌套2種方法）5查詢平均分不及格的學(xué)生的學(xué)號，姓名,平均分。6查詢女學(xué)生平均分高于75分的學(xué)生姓名。7查詢男學(xué)生學(xué)號、姓名、課程號、成績。(一門課程也沒有選修的男學(xué)生也要列出，不能遺漏)四 嵌套、相關(guān)及其他1 查詢平均分不及格的學(xué)生人數(shù)2 查詢沒有選修1002 課程的學(xué)生的學(xué)生姓名 3 查詢平均分最高的學(xué)生學(xué)號及平均分 （2種方法 TOP , any , all）*4 查詢沒有選修1001，1002課程的學(xué)生姓名。 5 查詢1002課程第一名的學(xué)生學(xué)號（2種方法）6 查詢平均分前三名的學(xué)生學(xué)號7 查詢 JSJ 系的學(xué)生與年齡不大于19歲的學(xué)生的差集8 查詢

7、1001號課程大于90分的學(xué)生學(xué)號、姓名及平均分大于85分的學(xué)生學(xué)號、姓名9 查詢每門課程成績都高于該門課程平均分的學(xué)生學(xué)號10 查詢大于本系科平均年齡的學(xué)生姓名答案參考答案1 create table student (sno char(6), sname varchar(8), ssex char(2), sage smallint, sdept varchar(15), primary key(sno);create table sc (sno char(6), cno char(4), grade decimal(12,2), primary key(sno,cno);insert i

8、nto student values( 4001,趙茵,男,20,SX)delete from studentdrop table studentalter table student add sbirthdate datetime 1 select sno, sname, sage from student where ssex=女 and sage between 19 and 21 order by sage desc;2 select sno, ssex from student where sname like _明% ;3 select sno, cno from sc where

9、 grade is null and cno=1001 ;4 select sno, sname from student where sdept in (JSJ,SX,WL) and sage>25 group by sdept;select sno, cno, grade/10.0+1 as level from sc ;select distinct sdept from student ;select gradefrom sc where sno=0001 and (cno=1001 or cno=1002) ;select count(*) from student where

10、 sname like %明% ;select avg(sage),max(sage) from student where sdept=JSJ ;select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno order by avg(grade) desc ;select cno, avg(grade) from sc where cno in(1001,1002) group by cno ;select sc.sno ,avg(grade) from sc group by sc.sno havin

11、g avg(grade)>80 ;select sno from sc group by sno having count(*)>2 ;select cno from sc where grade>85 group by cno having count(*)=10 ;select sno from sc group by sno having avg(grade)<60 ;select sno from sc where grade<60 group by sno having count(*)>2 ;select cno from student,sc

12、where student.sno=sc.sno and sdept=JSJ ;a:select sname from student,sc where student.sno=sc.sno and cno=1002b:select sname from student where sno in (select sno from sc where cno=1002)select sno,grade from sc,course where o=o and cname=數(shù)據(jù)庫原理 and grade <60a:select sname from student ,sc,coursewher

13、e student.sno=sc.sno and o=o and grade>80 and cname= 數(shù)據(jù)庫原理b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname= 數(shù)據(jù)庫原理)select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)&

14、lt;60a:select sname from student where ssex=女 and sno in(select sno from sc group by sno having avg(grade)>75) b:select sname from sc,student where student.sno=sc.sno and ssex=女 group by student.sno having avg(grade)>75select student.sno,sname,cno,grade from student left join sc on student.sno

15、=sc.sno and ssex=男select count(*) from student where sno in( select sno from sc group by sno having avg(grade)<60)select sname from student where sno not in（select sno from sc where cno=1002）student 0001 aa X 0002 bb 0003 cc X Sc 0001 1001 0001 1002 0002 1001 0003 1002 Select sname from student w

16、here not exists(select* from sc where cno=1002 and sc.sno=student.sno)a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) descb:select sno, avg(grade) from sc group by sno having avg(grade)=(select top 1 avg(grade) from sc group by sno order by avg(grade) desc)c:select sno, avg(gr

17、ade) from sc group by sno having avg(grade)>=all ( select avg(grade) from sc group by sno)select sname from student where not exists( select * from course where cno in(1001,1002) and not exists(select * from sc where sno =student.sno and cno=o) )a:select top 1 sno from sc cno=1002 order by grade

18、descb:select sno from sc where cno=1002 and grade >=all (select grade from sc where cno=1002)select top 3 sno from sc group by sno order by avg(grade)desca:select*from student where sdept=JSJ and sage>19b:select*from student where sdept=JSJ except select* from student where sage<19select student.sno,sname from student,sc where cno=1001 and grade>90 union select sno,sname from student where