SQL實(shí)驗(yàn)及部分答案

1、SQL實(shí)驗(yàn)及部分答案實(shí)驗(yàn)一使用SQL語句創(chuàng)建和刪除數(shù)據(jù)庫(kù)一. 實(shí)驗(yàn)?zāi)康模?、了解SQL Server 2005數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)和物理結(jié)構(gòu)。2、掌握使用SQL 語句創(chuàng)建和刪除數(shù)據(jù)庫(kù)。二. 實(shí)驗(yàn)準(zhǔn)備1明確能夠創(chuàng)建數(shù)據(jù)庫(kù)的用戶必須是系統(tǒng)管理員，或是被授權(quán)使用CREATE DATABASE語句的用戶。2了解用SQL語句創(chuàng)建和刪除數(shù)據(jù)庫(kù)的基本語法。三. 實(shí)驗(yàn)要求3、熟練使用查詢分析器進(jìn)行數(shù)據(jù)庫(kù)的創(chuàng)建和刪除操作4、完成用sql語句建立和刪除數(shù)據(jù)庫(kù)的實(shí)驗(yàn)報(bào)告四. 實(shí)驗(yàn)內(nèi)容1以下是創(chuàng)建數(shù)據(jù)庫(kù)userdb1的SQL語句，以下是創(chuàng)建數(shù)據(jù)庫(kù)userdb1的SQL語句，CREATE DATABASE userdb1

2、-創(chuàng)建名為userdb1的數(shù)據(jù)庫(kù)on( NAME=userdb2,-數(shù)據(jù)文件的邏輯名稱為userdb2FILENAME= d:testuserdb1.mdf, -物理路徑為d:testSIZE=5mb,-數(shù)據(jù)初始長(zhǎng)度為5MMAXSIZE=10mb, -最大長(zhǎng)度為10MFILEGROWTH=1mb -數(shù)據(jù)文件每次增長(zhǎng)1M)運(yùn)行上訴語句建立數(shù)據(jù)庫(kù)userdb1.2.用SQL語句刪除步驟一建立的數(shù)據(jù)庫(kù)userdb1。drop database userdb1 -刪除數(shù)據(jù)庫(kù)userdb1實(shí)驗(yàn)二分別用向?qū)Ш蚐QL語句創(chuàng)建和刪除表一用SQL語句創(chuàng)建表在SQL Server 2005 的查詢分析器中，用sq

3、l語句創(chuàng)建student表。表2.1 student表（學(xué)生信息表）CREATE TABLE student(sno char(8)not null primary key,-學(xué)號(hào)(主鍵)sname char(8)not null,-姓名sex char(2)null,-性別native char(20)null,-籍貫birthday datetime null,-出生日期dno char(6)null,-所在院系spno char(8)null,-專業(yè)代碼classno char(4)null,-班級(jí)號(hào)entime datetime null,-入校時(shí)間home varchar(40)nu

4、ll,-家庭住址tel varchar(40)null,-聯(lián)系電話)表2.2 course表（課程信息表）CREATE TABLE course(cno char(10)not null primary key,spno char(8)null,cname char(20)not null,ctno tinyint null,experiment tinyint null,lecture tinyint null,semester tinyint null,credit tinyint null)實(shí)驗(yàn)三修改數(shù)據(jù)庫(kù)基本表的定義一實(shí)驗(yàn)?zāi)康?了解表的結(jié)構(gòu)特點(diǎn)。3學(xué)會(huì)使用SQL語句修改表的定義。二實(shí)驗(yàn)

5、準(zhǔn)備1.了解常用的修改表定義語句。三實(shí)驗(yàn)要求1. 完成在已經(jīng)創(chuàng)建成功的表上修改表定義的操作四實(shí)驗(yàn)內(nèi)容1修改列屬性（1）用SQL語句將Student表中的屬性sno char(8)改成varchar(20)類型。alter table studentalter column sno varchar(20)2添加列（1）用SQL語句在Course表中添加一列year ，類型為varchar（4），默認(rèn)置為空。alter table courseadd year varchar(20)null（2）用SQL語句在year字段添加約束，year 的屬性值在2004-2008之間。alter table

6、 courseadd constraint year_constraint check(year=2004 and year3刪除列（1）用SQL語句將Course表中的year字段刪除。alter table coursedrop constraint year_constraintalter table course drop column year實(shí)驗(yàn)四創(chuàng)建和刪除索引一實(shí)驗(yàn)?zāi)康?了解索引的類型和應(yīng)用。2學(xué)會(huì)用SQL語句對(duì)表創(chuàng)建和刪除索引二實(shí)驗(yàn)準(zhǔn)備2. 了解創(chuàng)建和刪除索引的方法。三實(shí)驗(yàn)要求1. 了解索引類型并比較各類索引的不同之處2.完成索引的創(chuàng)建和刪除，并提交實(shí)驗(yàn)報(bào)告。四實(shí)驗(yàn)內(nèi)容1. 分

7、別建立以下索引(如果不能成功建立,請(qǐng)分析原因)(1) 在student表的sname列上建立普通降序索引。CREATE INDEX sname ON Student(Sname deSC)(2) 在course表的cname列上建立唯一索引。CREATE unique INDEX cname ON course(cname)(3) 在student_course表的sno列上建立聚集索引。create CLUSTERED INDEX student_sno on student(sno)無法對(duì)表student 創(chuàng)建多個(gè)聚集索引。在創(chuàng)建新聚集索引前應(yīng)該刪除已有的聚集索引PK_student。2.

8、 刪除索引將student_course表的sno列上的聚集索引刪掉。alter table student drop constraint PK_student實(shí)驗(yàn)五 SELECT語句一實(shí)驗(yàn)?zāi)康?觀察查詢結(jié)果, 體會(huì)SELECT語句實(shí)際應(yīng)用；3. 熟練掌握簡(jiǎn)單表的數(shù)據(jù)查詢、數(shù)據(jù)排序和數(shù)據(jù)連接查詢的操作方法。二實(shí)驗(yàn)要求1.完成簡(jiǎn)單查詢和連接查詢操作，并驗(yàn)收實(shí)驗(yàn)結(jié)果提交實(shí)驗(yàn)報(bào)告三實(shí)驗(yàn)內(nèi)容所有的查詢?nèi)坑肨ransact-SQL語句實(shí)現(xiàn)1.簡(jiǎn)單查詢操作首先通過insert語句向student和course表插入5條數(shù)據(jù)，sql語句及代碼如下：insert into student values(2

9、0131701110,張福新,女,山東,19901211,計(jì)算機(jī),17,2,2013,山東德州,157*)insert into student values(20131702111,楊文婷,女,山西,19880309,計(jì)算機(jī),17,2,2013,山西太原,157*)insert into student values(20131703111,張琦,女,山西,19890316,計(jì)算機(jī),17,2,2013,山西太原,157*)insert into student values(20130802044,馮藝,女,山東,19900809,經(jīng)濟(jì),08,1,2013,山東德州,157*)insert

10、into student values(201308702040,王仲,男,山東,19930623,工商,05,1,2014,山東德州,157*)insert into course values(1,001,數(shù)據(jù)庫(kù),001,9,18,1,2) insert into course values(2,001,網(wǎng)絡(luò)工程,002,9,18,1,2) insert into course values(3,001,信息系統(tǒng),003,9,18,1,2) insert into course values(4,001,ERP應(yīng)用,004,9,18,1,2) insert into course valu

11、es(5,001,移動(dòng)商務(wù),005,9,18,1,2)對(duì)數(shù)據(jù)庫(kù)實(shí)現(xiàn)以下查詢：計(jì)算機(jī)系的學(xué)生學(xué)號(hào)和姓名；select sno,sname from student where dno=計(jì)算機(jī)選修了課程的學(xué)生學(xué)號(hào)；ALTER TABLE studentADD CONSTRAINT spno_consFOREIGN KEY (spno)REFERENCES course ON DELETE SET NULL選修某一課程的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢結(jié)果按成績(jī)的降序排列，如果成績(jī)相同則按學(xué)號(hào)的升序排列；求選修某一課程且成績(jī)?cè)?090 之間的學(xué)生學(xué)號(hào)和成績(jī)，并將成績(jī)乘以系數(shù)0.75 輸出；求某兩個(gè)系的姓

12、某姓的學(xué)生的信息；求缺少了成績(jī)的學(xué)生的學(xué)號(hào)和課程號(hào)。將2000以后的成績(jī)大于90分的學(xué)生成績(jī)存入永久成績(jī)表；將2000年以前的成績(jī)存入臨時(shí)成績(jī)表中。2.連接查詢操作對(duì)數(shù)據(jù)庫(kù)實(shí)現(xiàn)以下查詢：查詢每個(gè)學(xué)生的情況以及他（她）所選修的課程；求學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)；查詢每一門課的間接先行課。實(shí)驗(yàn)六子查詢一實(shí)驗(yàn)?zāi)康?掌握子查詢的表示。2進(jìn)一步掌握SQL Server 查詢分析器的使用方法，加深對(duì)SQL 語言的嵌套查詢語句的理解二實(shí)驗(yàn)準(zhǔn)備1掌握SQL語句的基本語法，并熟悉查詢分析器的工作環(huán)境。2完成了實(shí)驗(yàn)四，創(chuàng)建了數(shù)據(jù)庫(kù)及各個(gè)基本表。3了解子查詢的表示方法，熟悉IN 比較符、ANY、EXIST

13、S操作符的用法。三實(shí)驗(yàn)內(nèi)容1.建立“工程-零件”數(shù)據(jù)庫(kù)及如下4個(gè)表，并輸入實(shí)驗(yàn)數(shù)據(jù)，用SQL語句實(shí)現(xiàn)如下三個(gè)查詢：1) 供應(yīng)項(xiàng)目j4紅色零件的供應(yīng)商號(hào)及名稱2) 沒有上海供應(yīng)商生成的零件的項(xiàng)目號(hào)3)至少使用了供應(yīng)商S5所供應(yīng)全部零件的項(xiàng)目號(hào)。表結(jié)構(gòu)如下：供應(yīng)商（S）：供應(yīng)商表：供應(yīng)商代碼為主碼工程表：工程代碼為主碼零件表：零件代碼為主碼供應(yīng)零件表：供應(yīng)商代碼、工程代碼、零件代碼為聯(lián)合主碼；供應(yīng)商代碼為外碼，參照表、列是供應(yīng)商表的供應(yīng)商代碼列；工程代碼為外碼，參照表、列是工程表的工程代碼列；零件代碼為外碼，參照表、列是零件表的零件代碼列實(shí)驗(yàn)數(shù)據(jù)如下：供應(yīng)商：create table供應(yīng)商(供應(yīng)商

14、代碼char(5),姓名char(50),所在城市varchar(20),聯(lián)系電話char(20),)create table工程(工程代碼char(5),工程名char(50),負(fù)責(zé)人varchar(10),預(yù)算char(8),)create table零件(零件代碼char(5),零件名char(50),規(guī)格varchar(10),產(chǎn)地char(20),顏色char(10),)create table供應(yīng)零件(供應(yīng)商代碼char(5),工程代碼char(5),零件代碼char(5),數(shù)量int,)alter table供應(yīng)零件add constraint Sforeign key(供應(yīng)商代

15、碼) references供應(yīng)商(供應(yīng)商代碼)alter table供應(yīng)零件add constraint Jforeign key(工程代碼) references工程(工程代碼)alter table供應(yīng)零件add constraint Pforeign key(零件代碼)references零件(零件代碼)1、use工程零件select供應(yīng)商代碼,姓名from供應(yīng)商where供應(yīng)商代碼in(select供應(yīng)商代碼from供應(yīng)零件where零件代碼IN(SELECT零件代碼FROM零件WHERE顏色=黑色) and工程代碼IN(SELECT工程代碼FROM工程WHERE工程代碼=J4) )2

16、、select工程代碼from供應(yīng)零件where供應(yīng)商代碼in(select供應(yīng)商代碼from供應(yīng)商where姓名not like上海%)3、select工程代碼from供應(yīng)零件where供應(yīng)商代碼=S5實(shí)驗(yàn)七使用子句的SELECT語句一實(shí)驗(yàn)?zāi)康?. 熟練掌握數(shù)據(jù)查詢中的分組、統(tǒng)計(jì)、計(jì)算和組合的操作方法。2. 進(jìn)一步掌握SQL Server 查詢分析器的使用，加深對(duì)SQL 語言的嵌套查詢語句的理解。二實(shí)驗(yàn)準(zhǔn)備1.了解SELECT語句的GROUP BY和ORDER BY子句的作用。2.了解統(tǒng)計(jì)函數(shù)和分組統(tǒng)計(jì)函數(shù)的使用方法。3.熟悉查詢分析器的運(yùn)行環(huán)境。三實(shí)驗(yàn)內(nèi)容所有查詢都全部用SQL語句實(shí)現(xiàn)1.

17、 建立“圖書_讀者”數(shù)據(jù)庫(kù)及如下3個(gè)表，并輸入實(shí)驗(yàn)數(shù)據(jù)，用SQL語句實(shí)現(xiàn)如下五個(gè)查詢：1）查找這樣的圖書類別：要求類別中最高的圖書定價(jià)不低于全部按類別分組的圖書的平均定價(jià)的2 倍。2）求機(jī)械工業(yè)出版社出版的各類圖書的平均定價(jià)，分別用GROUP BY 和GROUP BY ALL 表示。3）列出計(jì)算機(jī)類圖書的書號(hào)、名稱及價(jià)格，最后求出冊(cè)數(shù)和總價(jià)格。4）列出計(jì)算機(jī)類圖書的書號(hào)、名稱及價(jià)格，并求出各出版社這類書的總價(jià)格，最后求出全部?jī)?cè)書和總價(jià)格。5）查詢計(jì)算機(jī)類和機(jī)械工業(yè)出版社出版的圖書。表結(jié)構(gòu)如下：圖書：圖書表：書號(hào)為主碼讀者表：編號(hào)為主碼；性別只能是“男”或“女”的CHECK 約束借閱表：串號(hào)為主

18、碼；書號(hào)為外碼，參照表、列是圖書表的書號(hào)列；讀者編號(hào)為外碼，參照表、列是讀者表的編號(hào)列；書號(hào)和讀者編號(hào)的聯(lián)合UNIQUE 約束實(shí)驗(yàn)數(shù)據(jù)：圖書：實(shí)驗(yàn)八創(chuàng)建視圖一實(shí)驗(yàn)?zāi)康?掌握創(chuàng)建視圖的SQL語句的用法。2掌握查看視圖的系統(tǒng)存儲(chǔ)過程的用法。二實(shí)驗(yàn)準(zhǔn)備1了解創(chuàng)建視圖方法。2了解修改視圖的SQL語句的語法格式。3了解視圖更名的系統(tǒng)存儲(chǔ)過程的用法。4了解刪除視圖的SQL語句的用法。三實(shí)驗(yàn)要求1.用不同的方法創(chuàng)建視圖。2.提交實(shí)驗(yàn)報(bào)告，并驗(yàn)收實(shí)驗(yàn)結(jié)果。四實(shí)驗(yàn)內(nèi)容1.創(chuàng)建視圖（1）使用企業(yè)管理器創(chuàng)建視圖以“student”表為基礎(chǔ)，建立一個(gè)名為“V_計(jì)算機(jī)系學(xué)生”的視圖。在使用該視圖時(shí)，將顯示“student”表中的所有字段.(2) 使用SQL語句創(chuàng)建視圖在查詢分析器中建立一個(gè)每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課名及