數(shù)據(jù)庫(kù)課后作業(yè)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫(kù)大作業(yè)11.針對(duì)習(xí)題10的四個(gè)表，用SQL語言完成以下各項(xiàng)操作：（1）給學(xué)生表增加一屬性Nation（民族），數(shù)據(jù)類型為Varchar（20）；ALTER TABLE dbo.Student ADD Nation VarChar(20);SElECT *FROM dbo.Student;（2）刪除學(xué)生表中新增的屬性Nation；ALTER TABLE dbo.Student DROP COLUMN Nation;SElECT *FROM dbo.Student; （3）向成績(jī)表中插入記錄（“”，“3”，80）；INSERT INTO dbo.Grade VALU

2、ES ('','3',80);SElECT *FROM dbo.Grade; （4）將學(xué)號(hào)為“”的學(xué)生的成績(jī)修改為70分；UPDATE dbo.Grade SET Gmark = 70 WHERE Sno = ''SElECT *FROM dbo.Grade;（5）刪除學(xué)號(hào)為“”的學(xué)生的成績(jī)記錄；DELETE FROM dbo.Grade WHERE Sno = ''SElECT *FROM dbo.Grade;（6）在學(xué)生表的Clno屬性上創(chuàng)建一個(gè)名為IX_Class的索引，以班級(jí)號(hào)的升序排序；CREATE INDEX IX_C

3、LASS ON dbo.Student(Clno);SELECT *FROM dbo.Student;（7）刪除IX_Class索引。 DROP INDEX Student.IX_CLASS; SELECT *FROM dbo.Student;12. 針對(duì)習(xí)題10的四個(gè)表，用SQL語言完成以下各項(xiàng)查詢：（1）找出所有被學(xué)生選修了的課程號(hào)； SELECT DISTINCT Cno FROM Grade,Student WHERE Grade.Sno = Student.Sno;（2）找出01311班女學(xué)生的個(gè)人信息；SELECT * FROM StudentWHERE Ssex = '女

4、' AND Clno = '01311'（3）找出0311班和01312班的學(xué)生姓名、性別、出生年份；SELECT Sname,Ssex,(2014-Sage) AS '出生年份' FROM StudentWHERE Clno IN('01311','01312');（4）找出所有姓李的學(xué)生的個(gè)人信息；SELECT * FROM StudentWHERE Sname LIKE '李%'（5）找出學(xué)生李勇所在班級(jí)的學(xué)生人數(shù)；SELECT COUNT(*) AS '李勇班級(jí)人數(shù)'FROM Stu

5、dentWHERE Clno IN( SELECT Clno FROM Student WHERE Sname = '李勇'); （6）找出課程名為操作系統(tǒng)的平均成績(jī)、最高分、最低分；SELECT MAX(Gmark) AS '最高分' ,MIN(Gmark) AS '最低分' ,AVG(Gmark) AS '平均成績(jī)'FROM Grade g,Course cWHERE g.Cno IN(SELECT c.Cno FROM Course WHERE c.Cname = '操作系統(tǒng)'); （7）找出選修了課程的學(xué)生

6、人數(shù)；SELECT COUNT(DISTINCT Sno) AS '選修了課程的學(xué)生人數(shù)'FROM Grade;（8）找出選修了課程操作系統(tǒng)的學(xué)生學(xué)號(hào)和姓名；SELECT S.Sno,S.SnameFROM Student S,Grade GWHERE S.Sno IN( SELECT G.Sno FROM Grade WHERE G.Cno = ( SELECT Cno FROM Course WHERE Cname = '操作系統(tǒng)'); （9）找出2000級(jí)計(jì)算機(jī)軟件班的成績(jī)?yōu)榭盏膶W(xué)生姓名。SELECT S.SnameFROM Student SWHERE

7、S.Sno NOT IN( SELECT Sno FROM Grade) AND S.Clno = (SELECT Clno FROM Class WHERE Inyear = '2000' AND Speciality = '計(jì)算機(jī)軟件'); 13. 針對(duì)習(xí)題10的四個(gè)表，用SELECT的嵌套查詢完成以下各項(xiàng)查詢：（1）找出與李勇在同一個(gè)班級(jí)的學(xué)生信息；SELECT *FROM StudentWHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇') AND Sname NOT IN(&

8、#39;李勇'); （2）找出所有與李勇有相同選修課程的學(xué)生信息；SELECT *FROM StudentWHERE Sno IN( SELECT Sno FROM Grade WHERE Grade.Cno IN( SELECT Cno FROM Grade WHERE Sno = ( SELECT Sno FROM Student WHERE Sname = '李勇') AND Sname NOT IN('李勇'); （3）找出年齡介于學(xué)生李勇與25歲之間的學(xué)生信息（已知李勇的年齡小于25歲）；SELECT *FROM StudentWHERE Sa

9、ge < 25 AND Sage > (SELECT Sage FROM Student WHERE Sname = '李勇'); （4）找出選修了課程操作系統(tǒng)的學(xué)生學(xué)號(hào)與姓名；SELECT Sno,SnameFROM StudentWHERE Sno IN(SELECT Sno FROM Grade WHERE Cno = (SELECT Cno FROM Course WHERE Cname = '操作系統(tǒng)'); （5）找出沒有選修1號(hào)課程的所有學(xué)生姓名；SELECT Sname FROM Student WHERE Sno NOT IN(SEL

10、ECT Sno FROM Grade WHERE Cno = '1'); （6）找出選修了全部課程的學(xué)生姓名。SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM Grade WHERE Sno= Student.Sno AND Cno= Course.Cno);14. 針對(duì)習(xí)題10的四個(gè)表，用SQL語言完成以下各項(xiàng)查詢：（1）查詢選修了3門以上的課程的學(xué)生學(xué)號(hào)及其成績(jī)，并按成績(jī)的降序排列；SELECT Sno,GmarkFROM GradeW

11、HERE Cno = 3ORDER BY Gmark DESC;（2）查詢?nèi)w學(xué)生信息，要求查詢結(jié)果按班級(jí)號(hào)升序排列同一班級(jí)按年齡降序排列；SELECT *FROM StudentORDER BY Clno,Sage DESC;（3）求每個(gè)課程號(hào)及相應(yīng)的選課人數(shù)；SELECT Cno,COUNT(Cno) AS '選修人數(shù)'FROM GradeGROUP BY Cno;（4）查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。SELECT Sno,COUNT(Sno) AS '選修課程數(shù)'FROM GradeGROUP BY Sno HAVING(COUNT(Sno)>3)

12、; 15. 針對(duì)習(xí)題10的四個(gè)表，用SQL語言完成以下各項(xiàng)操作：（1）將01311班的全體學(xué)生的成績(jī)置零； UPDATE GradeSET Gmark = 0WHERE Sno IN(SELECT Sno FROM Student WHERE Clno = '01311'); （2）刪除2001級(jí)計(jì)算機(jī)軟件的全體學(xué)生的選課記錄； DELETE FROM GradeWHERE Sno IN (SELECT Sno FROM Student WHERE Clno = (SELECT Clno FROM Class WHERE Inyear = '2001' AND

13、Speciality = '計(jì)算機(jī)軟件'); （3）學(xué)生李勇已退學(xué)，從數(shù)據(jù)庫(kù)中刪除有關(guān)他的記錄；UPDATE ClassSET Monitor = NULLWHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇')DELETE FROM GradeWHERE Sno = (SELECT Sno FROM Student WHERE Sname = '李勇') DELETE FROM StudentWHERE Sname = '李勇'（4）對(duì)每個(gè)班，求學(xué)生的平均年齡，并把結(jié)果

14、存入數(shù)據(jù)庫(kù)。CREATE VIEW V_SagAS SELECT Clno,AVG(Sage) AS 'Sag' FROM Student GROUP BY Clno; 16.視圖操作：（1）建立01311班選修了1號(hào)課程的學(xué)生視圖Stu_01311_1； CREATE VIEW Stu_01311_1 AS SELECT * FROM Student WHERE Sno IN(SELECT Sno FROM Grade WHERE Cno = 1) AND Clno = '01311'（2）建立01311班選修了1號(hào)課程并且不及格的學(xué)生視圖Stu_01311_

15、2；CREATE VIEW Stu_01311_2AS SELECT * FROM Student WHERE Sno IN(SELECT Sno FROM Grade WHERE Cno = 1 AND Gmark < 60) AND Clno = '01311'（3）建立視圖Stu_year，由學(xué)生學(xué)號(hào)、姓名、出生年份組成；CREATE VIEW Stu_yearAS SELECT Sno AS '學(xué)號(hào)',Sname AS '姓名',(2014-Sage) AS '出生年份' FROM Student;（4）查詢1990

16、年以后出生的學(xué)生姓名；SELECT SnameFROM dbo.Stu_yearWHERE '出生年份' > '1990' （5）查詢01311班選修了1號(hào)課程并且成績(jī)不及格的學(xué)生的學(xué)號(hào)、姓名、出生年份。SELECT *FROM Stu_yearWHERE Sno IN (SELECT Sno FROM Stu_01311_2); 第四章 觸發(fā)器11.對(duì)于成績(jī)管理數(shù)據(jù)庫(kù),為成績(jī)管理數(shù)據(jù)庫(kù)中的Student表創(chuàng)建一觸發(fā)器:當(dāng)向表中插入或刪除記錄時(shí),修改Class表中相應(yīng)的班級(jí)人數(shù) CREATE TRIGGER fri_stu ON Student AFTER INSERT,DELETE AS IF UPDATE(Sno) UPDATE Class SET Number = Number + 1 WHERE Clno = (