北郵數(shù)據(jù)庫_sql實驗二報告.docx

數(shù)據(jù)庫實驗二SQL server實驗二SQL Server數(shù)據(jù)庫設計高級內(nèi)容一、使用查詢分析器實現(xiàn)以下查詢(1) 統(tǒng)計有學生選修的課程門數(shù)。 SELECT COUNT(DISTINCT CourseNo) CourseTotal FROM sc(2) 求選課在四門以上的學生所選課程的平均成績(不統(tǒng)計不及格的課程)。最后按降序列出平均成績名次名單來。 SELECT sno,AVG(score) avg_grade FROM scWHERE score = 60GROUP BY sno HAVING count(courseno)=4ORDER BY avg_grade DESC(3) 統(tǒng)計每門指選課程的學生選修人數(shù)(超過10人的課程才統(tǒng)計),要求輸出課程號,課程名和選修人數(shù),查詢結果按人數(shù)降序排列,若人數(shù)相同,按課程號升序排列。SELECT courseno,COUNT(sno) snumFROM sc GROUP BY courseno HAVING COUNT(sno)=10 ORDER BY COUNT(sno) DESC, courseno ASC(4) 檢索所學課程包含了s3所選所有課程的學生姓名。SELECT DISTINCT sno FROM sc sc_1 WHERENOT EXISTS( SELECT * FROM sc sc_2 WHERE sno=200815128 ANDNOT EXISTS(SELECT * FROM sc sc_3 WHERE sc_3.sno = sc_1.sno AND sc_3.courseno = sc_2.courseno)注：上述查詢中所用的課程號和學號的值，可以根據(jù)自己表中的數(shù)據(jù)作修改；為了驗證查詢的正確，可能還需要輸入或修改表中的示例數(shù)據(jù)；二、實現(xiàn)數(shù)據(jù)完整性（1）定義check約束check約束用來限制用戶輸入的某一列數(shù)據(jù)；例如：成績輸入的值應該限制為0-100之間的數(shù)值 alter table sc add constraint ck_sc CHECK( score = 0 AND score = 100)刪除約束alter table sc drop constraint ck_sc（2）定義規(guī)則（rule）rule也可以用來限制用戶輸入的數(shù)據(jù)，但它只定義一次，可以綁定到一列或多列；例如：創(chuàng)建一個規(guī)則，保證只允許輸入指定的課程類別：“必修”，“任選”，“指選”，然后把此規(guī)則綁定到“課程類別”；創(chuàng)建規(guī)則create rule coursetypeas coursetype in (必修,任選,指選)綁定規(guī)則sp_bindrule coursetype,Course.Category解除綁定sp_unbindrule Course.Category刪除規(guī)則drop rule coursetype（3）創(chuàng)建以上約束后，練習修改約束的操作（包括增加，修改和刪除以上約束）；三、在企業(yè)管理器中利用數(shù)據(jù)導入，導出向導練習數(shù)據(jù)的導入導出；把每個表中的數(shù)據(jù)導出到指定的文本文件中；導出的文件四、在企業(yè)管理器中，練習數(shù)據(jù)庫的完整性備份和恢復；備份文件；五、存儲過程和觸發(fā)器的實現(xiàn)1存儲過程（1）使用CREATE PROCEDURE命令創(chuàng)建存儲過程例1：定義存儲過程，實現(xiàn)學生學號，姓名，課程名和成績的查詢；CREATE PROC student_queryASSELECT student.sno,sname,coursename,score FROM student,course,scWHERE student.sno = sc.sno and course.courseno = sc.coursenoEXEC student_query例2：定義存儲過程，實現(xiàn)按某人指定課程的成績；CREATE PROC student_query1sname varchar(20),cname varchar(30)ASSELECT student.sno,sname,coursename,score FROM student,course,scWHERE student.sno = sc.sno AND course.courseno = sc.coursenoAND sname = sname AND coursename = cnameexec student_query1 李剛,數(shù)據(jù)庫技術例3：定義存儲過程，在查詢某人所選修的課程和成績，指定姓名時，可以只給出姓；CREATE PROC student_query2sname varchar(20) = %ASSELECT student.sno,sname,coursename,score FROM student,course,scWHERE student.sno = sc.sno AND course.courseno = sc.coursenoand sname LIKE snameEXEC student_query2 張%例4：定義存儲過程，計算并查看指定學生的總學分alter table course alter column Credits floatCREATE PROC student_query3sname varchar(20), total integer OUTPUTASSELECT total = SUM(Credits) FROM student,course,scWHERE student.sno = sc.sno AND course.courseno = sc.coursenoand sname = snamedeclare total floatexec student_query3 李剛,total OUTPUTselect total TotalCredits（2）使用EXEC命令執(zhí)行上述存儲過程2觸發(fā)器（1）使用CREATE TRIGGER命令對學生選課信息表創(chuàng)建插入觸發(fā)器，實現(xiàn)的功能是：當向學生選課信息表中插入一記錄時，檢查該記錄的學號在學生表中是否存在，檢查該記錄的課程編號是否在課程表中存在，若有一項為否，則提示“違背數(shù)據(jù)的一致性”錯誤信息，并且不允許插入。CREATE TRIGGER check_ins ON dbo.SC FOR INSERT ASif not exists(select * from student,course,insertedwhere student.sno=inserted.sno and course.courseno=inserted.courseno) RAISERROR (違背數(shù)據(jù)的一致性,16,1)elsebegin PRINT 數(shù)據(jù)已插入endinsert into sc values(200815128,C4,80)（2）使用CREATE TRIGGER 命令對學生信息表創(chuàng)建刪除觸發(fā)器，實現(xiàn)的功能是：當在學生信息表中刪除一條記錄時，同時刪除學生選課信息表中相應的記錄。CREATE TRIGGER del_trigger ON dbo.Student FOR DELETE ASdelete from sc where sc.sno in (select sno from deleted