PAGEPAGE2SQLServer課程設計指導書長春工業大學人文信息學院[課程設計目的]SQLServer數據庫課程設計是計算機類專業的集中實踐性環節之一，是課程教學中的一項重要內容，是完成教學計劃達到教學目標的重要環節，是幫助學生全面牢固地掌握課堂教學內容、培養學生分析、解決實際問題的能力進行的一次全面的綜合練習。通過結合設計題目進行綜合性應用，對所學知識達到融會貫通的程度，對提高學生的綜合素質具有重要的意義本次課程設計的教學目的：1、加深對數據庫基礎理論和基本知識的理解。2、掌握數據庫設計與使用的基本方法。3、提高運用數據庫解決實際問題的能力。[課程設計內容及要求]課程設計的時間為兩周時間。題目分為必做和選作兩部分，根據題目要求，用T-SQL語句實現各種數據庫操作，需要上交課程設計報告。報告內容包括：設計目的設計題目題目的答案（sql語句）設計過程中遇到的問題和心得。目錄必做題TOC\o"1-3"\h\z題目一產品銷售信息數據庫操作 4題目二學生成績(XSCJ)數據庫操作 6題目三職工_社團數據庫操作 14題目四圖書管理數據庫操作 16題目五工程_零件數據庫操作 18題目六企業員工信息數據庫操作 19選做題學生學籍管理信息系統數據庫設計 22題目一產品銷售信息數據庫操作寫出創建產品銷售數據庫CPXS和表的T-SQL語句：數據庫初始大小為10MB，最大大小為100MB，數據庫自動增長，按10%比例增長；日志文件初始為2MB，最大可增長到5MB（默認為不限制），按1MB增長；其余參數自定。所包含的表如下：產品表：產品編號，產品名稱，價格，庫存量銷售商表：客戶編號，客戶名稱，地區，負責人，電話產品銷售表：銷售日期，產品編號，客戶編號，數量，銷售額（表中數據類型自擬）將所創建的CPXS數據庫的增長方式改為按5MB增長將所創建的CPXS數據庫的產品表增加“產品簡介”列向產品表插入如下數據：0001空調30002000203冰箱25001000301彩電280050將產品數據庫的產品表中的每種商品的價格打8折將產品數據庫的產品表中價格打8折后小于50的商品刪除查找價格在2000~2900元之間的產品名稱計算所有產品總價格求指定日期2002年3月18日銷售總額求名為“張三”的客戶在2002年3月18日所購買的產品名稱在產品銷售數據庫上創建冰箱產品表的視圖BXCP在BXCP視圖上查詢存儲量在100臺以下的產品編號求各個地區的負責人數基于“產品表”創建AFTERINSERT觸發器tr_insert_price，實現新添記錄數據時商品的價格限制在10000以內，高于10000的事務回滾。題目二學生成績(XSCJ)數據庫操作創建學生成績（XSCJ）數據庫及其表結構表A.1學生情況表（表名XS）結構列名數據類型長度是否允許為空值默認值說明學號char6否無主鍵姓名varchar8否無專業名varchar10是1男1女0性別bit1否無出生時間datetime8否無總學分int4是無備注text16是無表A.2課程表（表名KC）結構列名數據類型長度是否允許為空值默認值說明課程號char3否無主鍵課程名char16否無開課學期tinyint1否1只能為1~8學時tinyint1否無學分tinyint1是無表A.3學生與課程表（表名XS_KC）結構列名數據類型長度是否允許為空值默認值說明學號char6否無外鍵課程號char3否無外鍵成績tinyint1否無學分tinyint1是無2、向學生成績XSCJ數據的表中插入樣本數據學生情況表（表名XS）數據樣本課程表（表名KC）數據樣本（3）學生與課程表（表名XS_KC）數據樣本利用sql語句完成下列數據操作：查詢XSCJ數據庫的XS表中各個同學的姓名、專業名和總學分查詢XS表中計算機專業同學的學號、姓名和總學分查詢XS表中計算機系同學的學號、姓名和總學分，結果中各列的標題分為指定為number,name和mark查詢XS表中計算機系各同學的學號、姓名和總學分，對其總學分按以下規則進行替換；若總學分為空值，則顯示“尚未選課”；若總學分小于50分則顯示為“不及格”；若總學分在50分與52分之間，顯示為“合格”；若總學分大于52分，顯示為“優秀”。該列標題更改為“等級”查詢XS表的專業名和總學分，消除結果集中的重復行查詢XS表姓名、專業名和總學分，只返回結果集的前6行查詢XS表中通信工程專業總學分大于等于42的同學的情況查詢XS表中計算機系的學生情況查詢XS表中姓“王”且單名的學生情況查詢XS表中不在1979年出生的學生情況查詢XS表中專業名為“計算機”、“通信工程”或“無線電”的學生的情況查詢總學分尚不定的學生的情況查找選修了課程號為101的課程的學生的情況查找未選修離散數學的學生的情況查找比所有計算機系的學生年齡都打的學生查找課程號206的成績不低于課程號101的最低成績的學生的學號查找選修206號課程的學生姓名查找選修了全部課程的同學的姓名查找選修了與學號為001102同學所選修的全部課程的同學的學號在XS表中查找1980年1月1日以前出生的學生的姓名和專業名，分別使用別名stu_name和speciality表示查找每個學生的情況以及選修的課程情況查找選修了206課程且成績在80分以上的學生姓名及成績查找選修了“計算機基礎”課程且成績在80分以上的學生學號、姓名、課程名稱和成績查找不同課程成績相同的學生的學號、課程號和成績查找所有學生情況及他們選修的課程號，若學生未選修任何課，也要包括其情況查找被選修了的課程的選修情況和所有開設的課程名稱求選修101課程的學生的平均成績求選修101課程的學號的最高分和最低分求學生的總人數求選修了課程的學生總人數統計離散數學課程成績在85分以上的人數查詢所有專業名稱求各專業的學生數求被選修的各門課程的平均成績和選修改課程的人數在XSCJ數據庫上產生一個結果集，包括每個專業的男生、女生人數查找XSCJ數據庫總平均成績在85分以上的學生的學號和平均成績查找選修課程超過兩門且成績都在80分以上的學生的學號將通信工程專業的學生按出生時間先后排序將計算機專業學生的“計算機基礎”課程成績按降序排列由XS表創建“計算機系學生”表，其中包括學號和姓名創建CS_KC視圖，包括計算機專業各學生的學號、其選修的課程號及成績。要保證對該視圖中數據的修改都符合專業名為計算機這個條件。創建計算機專業學生的平均成績視圖CS_KC_AVG，包括學號（在視圖中列名為num）和平均成績（在視圖中列名為score_avg）使用視圖CS_KC查找計算機專業的學生學號和選修的課程號在視圖CS_KC_AVG中查找平均成績在80分以上的學生的學號和平均成績在XSCJ數據庫中創建表XS1和XS2，他們的結構與XS表一樣，分別在XS1和XS2的學號列上建立check約束為between‘004001’and‘004050’和between‘003001’and‘003050’并創建視圖，語句如下：CreateviewXS12_viewAsSelect*FromXS1UnionallSelect*fromXS2向XS12_view視圖中插入一條記錄（'004010','藍源源'，'外語'，0，40）將XS12_view視圖中的每個學生的總學分增加10修改41題中的視圖，將其定義為：包括學號、姓名、選修的課程、課程名和成績。定義游標XS_CUR3，游標中存儲學號、姓名和總學分，然后打開該游標，輸出其行數。創建局部變量，分別賦值為“中國”，“是一個偉大的國家”，然后輸出變量的值。創建一個名為sex的局部變量，并在select語句中使用該局部變量查找表XS中所有女同學的學號、姓名。語句如下：useXSCJDeclare@sexbitSet@sex=0Select學號、姓名FromXSWhere性別=@sex運行下面程序，并描述其功能UseXSCJDeclare@CursorVarcursorSet@CursorVar=cursorscrollForSelect學號、姓名FromXSWhere姓名like'王%'Open@CursorVarFetchnextfrom@CursorVarwhile@@fetch_status=0BeginFetchnextfrom@CursorVarEndClose@CursorVarDeallocate@CursorVar查詢成績高于劉豐最高成績的學生姓名、課程名和成績查詢總學分不在在40~50分的學生學號和姓名查詢課程名以C或A開頭的課程情況查詢所有選課學生的姓名利用T-SQL編程語句實現如下要求，如果“數據庫原理”課程的平均成績高于75分，則輸出“平均成績高于75分”利用T-SQL編程語句實現如下要求，顯示字符串“China”中每逢字符的ASCII值和字符定義存儲過程，輸入任意學生學號，查詢該學生的平均成績，若>75，將返回狀態代碼1，否則，將返回狀態代碼2利用字符串函數返回課程名稱最左邊的8個字符利用case語句顯示學生的性別，但值為1時，顯示男生，否則顯示女生定義函數計算全體學生某門功課的平均成績(課程號為輸入參數)為KC表的課程名列創建唯一索引為XS表的總學分字段設置默認值為0（分別用默認值約束和默認值對象實現）為XS添加“身份證”列，并定義唯一約束題目三職工_社團數據庫操作設職工_社團數據庫有三個基本表：職工（職工號，姓名，年齡，性別）社會團體（編號，名稱，負責人，活動地點）參加（職工號，編號，參加日期）其中：職工表的主碼為職工號社會團體表的主碼為編號；外碼為負責人，被參照表為職工表，對應屬性為職工號參加表的職工號和編號為主碼；職工號為外碼，其被參照表為職工表，對應屬性為職工號；編號為外碼，其被參照表為社會團體表，對應屬性為編號試用SQL語句表達下列操作：定義職工表，社會團體表和參加表，并說明其主碼和參照關系建立下列兩個視圖社團負責人（編號，名稱，負責人職工號，負責人姓名，負責人性別）參加人情況（職工號，姓名，社團編號，社團名稱，參加日期）查找參加唱歌隊或籃球隊的職工號和姓名查找沒有參加任何社會團體的職工情況查找參加了全部社會團體的職工情況查找參加了職工號為“1001”的職工所參加的全部社會團體的職工號求每個社會團體的參加人數求參加人數最多的社會團體的名稱和參加人數求參加人數超過100人的社會團體的名稱和負責人把對社會團體和參加兩個表的數據查看、插入和刪除數據的權利賦給用戶李平，餅允許他再將此權利授予其他用戶編寫一個存儲過程，該存儲過程根據輸入的職工號，輸出該年齡的職工姓名創建一個名為tr_age的觸發器，要求在向職工表插入和更新時檢查AGE是否在20到60之間，如不在20到60，則彈出“年齡不合法”提示信息。

題目四圖書管理數據庫操作設圖書管理數據庫數據表如下：借書卡card（cno,name,calss），其中：cno為卡號，name為姓名，class為班級圖書books（bno，bname，author，price，quantity），其中：bno為書號，bname為圖書名，author為作者，price為單價，quantity為庫存冊數借書記錄borrow（cno，bno，rdate），其中：cno為借書卡號，bno為書號，rdate為還書日期約束：限定每人每種書只能借一本；庫存冊數隨借書、還書而變化。用sql語句實現下列操作：1、寫出建立borrow表的sql語句，要求定義主鍵完整性約束和引用完整性約束2、找出借書超過五本的讀者，輸出借書卡號及所借圖書冊數3、查找借閱了“水滸”一書的讀者，輸出姓名及班級4、查找過期未還圖書，輸出借閱者（卡號）、書號及還書日期5、查詢書名包括“網絡“關鍵詞的圖書，輸出書號、書名、作者6、查詢現有圖書中價格最高的圖書，輸出書名及作者7、查詢當前借了“計算方法“但沒有借”計算方法習題集“的讀者，輸出其借書卡號，并按卡號降序排序輸出8、將“C01“班同學所借圖書的還書日期延長一周9、從books表中刪除當前無人借閱的圖書記錄10、在borrow表上建立一個觸發器，完成如下功能：如果讀者借閱的書名是“數據庫技術及應用“，就將該讀者的借閱記錄保存在borrowsave表中（注：borrowsave表結構同borrow表）11、如果經常按書名查詢圖書信息，請建立合適的索引12、建立一個視圖，顯示“C01“班學生的借書信息（只要求顯示姓名和書名）13、查詢當前同時借有“計算方法“和”組合數學“兩本書的讀者，輸出其借書卡號，并按卡號升序排序輸出14、假定在建books表時沒定義主鍵，寫出為books表追加定義主鍵的語句15、對card表做如下修改：A）將name最大列寬增加到10個字符（假定原為6個字符）B）為該表增加一列dname（系名），可變長，最大20個字符題目五工程_零件數據庫操作設工程_零件數據庫中有四個基本表：供應商（供應商代碼，姓名，所在城市，聯系電話）工程（工程代碼，工程名，負責人，預算）零件（零件代碼，零件名，規格，產地，顏色）供應零件（供應商代碼，工程代碼，零件代碼，數量）試用sql語句完成下列操作：找出天津市供應商的姓名和電話查找預算在50000~100000元之間的工程的信息，并將結果按預算降序排列找出使用供應商S1所提供零件的工程號碼找出工程項目J2使用的各種零件名稱及其數量找出上海廠商供應的所有零件號碼找出使用上海產的零件的工程名稱找出沒有使用天津產零件的工程號碼把全部紅色零件的顏色改為藍色將由供應商S5供給工程代碼為J4的零件P6改為由S3供應，并做其他必要的修改從供應商關系中刪除S2的記錄，并從供應零件關系中刪除相應記錄題目六企業員工信息數據庫操作數據庫介紹：這是一個企業員工信息數據庫，主要存儲員工信息，薪水以及部門等信息。該數據庫包括了員工信息表（emp表）和部門信息表（dept表）。各個表的內容與格式如下：emp表字段名數據類型含義備注empno整型員工編號主鍵，標識列（初始值為1000，增量為1）ename字符串姓名非空job字符串職位非空mgr整型部門負責人編號hiredate日期聘用日期非空sal浮點薪水非空comm浮點獎金deptno整型部門編號外鍵dept表字段名數據類型含義備注deptno整型部門編號主鍵標識列（初始值為10，增量為10）dname字符串部門名稱非空Loc字符串地址利用T-sql語句創建數據庫orcl，建立一個主數據文件orcl.mdf，一個次數據文件orcl1.ndf，以及一個日志文件orcl_log.ldf，各文件的初始大小為20M，可以自增，每次增長5M。根據上述兩表的結構，用T-sql語句創建表。（根據實際情況確定數據類型和字段長度，按照備注中的要求定義約束條件）向emp表添加一個字段sex（性別），字符串類型為sex字段添加檢查約束，要求該字段只能取值為男或者是女添加一個用戶自定義的數據類型名字為“工資”，該字段數據類型與sal和comm列數據類型一致，并將sal和comm改為該類型為hiredate列添加默認值約束，默認值為當前時間向emp表插入以下數據：（張三，經理，null，1982-12-7，800，null，20）（王新，銷售員，1001，1981-2-20，1600，300，30）（李立，銷售員，1001，1981-2-22，1250，500，30）向dept表插入以下數據：（銷售部，北京）（會計部，北京）（生產部，上海）查詢部門編號為20的員工信息查詢徐偶有銷售員的姓名、編號和部門名稱找出獎金高于薪水60%的員工找出部門20中所有銷售員或者部門30中的經理找出有獎金的員工的職位，去掉重復行找出10年前受雇傭的員工信息查找名字為兩個漢字的員工信息查找不姓“張”的所有員工信息顯示所有員工的姓名、職位和薪水，按職位的降序排序，職位相同時按照薪水和獎金的和升序排序顯示所有雇員的姓名和加入公司的年份和月份，按照年份降序排序，同一年的按照月份升序排序找出在（任何年份的）2月受聘的員工信息計算出每個員工加入公司的天數查詢至少有一個員工的部門信息查詢比“張三”薪水高的所有員工信息列出所有雇員的姓名及其直接上級的姓名列出入職日期早于其直接上級的所有雇員列出部門名稱和這些部門的雇員，同時列出那些沒有雇員的部門列出各種職位的最低薪水，顯示最低薪水大于1500的記錄列出薪水高于公司平均水品的所有雇員列出與“張三”從事相同工作的所有雇員列出某些雇員的姓名和薪水，條件是他們的薪水等于部門30中任何一個雇員的薪水列出某些雇員的姓名和薪水，條件是他們的薪水高于部門30中所有雇員的薪水列出每個部門的信息以及該部門中雇員的數量，即使是分配有0個雇員列出從事同一種工作但屬于不同部門的雇員的不同組合列出各種類別工作的最低工資列出按年薪排序的所有雇員的年薪創建一個視圖，存放“北京”的員工信息。

學生學籍管理信息系統數據庫設計學生學籍管理工作包括學生檔案、學籍、成績、升學等內容的管理；面對大量的學生數據、報表，手工處理方式已經很難跟上現代化的步伐。隨著計算機技術及網絡通訊技術的飛速發展，許多學校已經有了較好的計算機應用甚至網絡硬件建設基礎。因此為提高學校管理工作的現代化、科學化水平，保證信息處理的即時化、準確化，開發一套對學生學籍進行管理的軟件是極其重要的。應完成的主要功能：（1）學生檔案的管理，即錄入、修改、查詢、輸出學生檔案信息，這些信息包括學生基本情況、學生簡歷情況、學生獎勵情況、學生處分情況、學生家庭信息、學生體檢情況。（2）學生學籍管理，錄入、修改、查詢、輸出學生學籍信息，這些信息包括學生獎貸學金情況、學生注冊、學生異動情況、學生軍訓情況、學生畢業情況。（3）學生成績管理，錄入修改、查詢、輸出學生入校成績，各學期、各門課程的成績信息，并支持按年級、班級等條件的統計、查詢、報表輸出。《學生學籍管理信息系統》功能需求參考按系匯總各專業各年級的學生總數及其中的男、女生數，并統計全校總人數。要求：當任意輸入系部名稱、專業名稱和年級均可返回相應信息。按系匯總各年齡段的學生人數及其中的男、女生數。要求：當任意輸入系部名稱、年齡時，均可返回相應信息。按系統計各地區的學生人數。要求：當任意輸入系部名稱、地區名稱時，均可返回相應學生人數。按任一學期（用戶輸入）統計學生變更數據（如：復學、休學、退學、開除、轉入、轉出、畢業、肄業等）。按系統計學生中的黨員、團員人數。要求：當任意輸入系部名稱時，均可返回該系的學生黨員和團員人數。按系統計各民族的學生人數。要求：當任意輸入系部名稱、民族時，均可返回相應信息。按學年根據學生變更數據自動修改班級總人數（提示：用觸發器實現）、系部總人數、學校總人數。統計各門課程的成績分布情況：其中：(90-100、80-90、70-80、60-70、0-59)的人數及各自占實際考試人數的百分比數。統計缺考人數。統計及格、不及格人數。統計每個學生的綜合測評總成績：總成績=平均分*0.7+德體分*0.3綜合測評的排名。根據總成績評定獎學金等級（特等、一等、二等、三等）。按學年根據