《數據庫原理》SQLServer期末復習

題型：

一. 概況分析題（5小題，每小題2分，共10分）

二. 計算題（3小題，每小題5分，共15分）

三. 數據庫設計（2小題，每小題10分，共20分）

四. 規范化分析與設計題（2小題，每小題10，共20分）

五. 編程應用題（本大題共6小題，共35分）

考點：

① 關系模型，DBMS，數據庫系統發展階段 (人工管理、文件系統、數據庫系統）

② 主鍵，外健，域，check，關系的類型

③ 關系代數：π s??查詢結果

④ E-R圖轉換成關系模式、關系的函數依賴集

⑤ 范式

⑥ 查詢：Select ,in ,Year, GetDate, DateDiff, 聚集函數，Like

⑦ Create database 、alter database、Create table

⑧ Insert, Update,Delete

⑩ 視圖view和索引index

? Create proc ?as ?執行：exec proc1（存儲過程和觸發器）

數據庫設計綜合實驗

本篇完。

題型：

一. 概況分析題（5小題，每小題2分，共10分）

填空，例：該信息屬于哪種數據模型：關系模型。數據？

二. 計算題（3小題，每小題5分，共15分）

三. 數據庫設計（2小題，每小題10分，共20分）

四. 規范化分析與設計題（2小題，每小題10，共20分）

函數依賴集、部分函數依賴集、傳遞函數依賴集、候選碼

五. 編程應用題（本大題共6小題，共35分）

考點：

① 關系模型，DBMS，數據庫系統發展階段 (人工管理、文件系統、數據庫系統）

② 主鍵，外健，域，check，關系的類型

③ 關系代數：π s??查詢結果

④ E-R圖轉換成關系模式、關系的函數依賴集

⑤ 范式

⑥ 查詢：Select ,in ,Year, GetDate, DateDiff, 聚集函數，Like

????????任務：查詢學生表中全體學生的全部信息。

select *from student

????????任務：檢索全體學生的學號、姓名、年齡。

Tips:其中年齡要由DateDiff(year,birthday,GETDATE( ))來求

select  sno,sname,DateDiff(year,birthday,GETDATE( )) as 年齡 
from student

????????任務：查詢成績大于80分的學生的學號及課程號、成績?

select sno,cno,grade
from sc 
where grade >80

????????任務：從學生表中分別檢索出名字的第二個字是“甜”或“小”的所有同學的信息。

select *from student 
where sname like '_[小，甜]%'

????????任務：統計女學生人數。

select count（distinct sno） as 女生人數 from student
where sex='女'

????????任務：從表sc中查詢所有成績中的最高分和最低分。

select max(grade) as 最高分, min(grade) as 最低分
from sc

??????任務：統計學號為‘0601110101’的學生的總成績和平均成績。

select sum(grade) as 總成績,avg(grade) as 平均成績
from sc
where sno='0601110101'

??????任務：查詢各個課程號相應的選課人數。

select count(*) as 選課人數 from sc
group by cno

???????任務：查詢平均年齡大于18歲的系和平均年齡

?????Tips: 使用DATEDIFF(Year,birthday,GetDate(?))計算年齡

select sdept,avg(DATEDIFF(Year,birthday,GetDate( ))) as 平均年齡 
from student 
group by sdept
having avg(DATEDIFF(Year,birthday,GetDate( )))

???????任務：匯總總分大于150分的學生的學號及總成績。

select sno,sum(grade) as 匯總總成績 
from sc
group by sno
having sum(grade)>150

??????任務：查詢所有學生的學號、姓名、選修課程號和成績（用where和join on分別實現）。

select student.sno,sname,cno,grade 
from student,sc
where student.sno = sc.sno

select student.sno,sname,cno,grade 
from student join sc
on student.sno = sc.sno

??????任務：查詢所有年齡比張甜甜大的學生的姓名、性別和年齡。

????????????Tips:可以使用自查詢也可以使用子查詢

select sname,sex,DATEDIFF(Year,birthday,GetDate( )) as age
from student
where DATEDIFF(Year,birthday,GetDate( )) > (select DATEDIFF(Year,birthday,GetDate( ))from studentwhere sname = '張甜甜')

??????任務：查詢比‘軟件技術’系的任一學生年齡都大的非‘軟件技術’系的學生的姓名，年齡，所在系名。

select sname,DATEDIFF(Year,birthday,GetDate( )) as age,sdept
from student
where sdept != '軟件技術' and DATEDIFF(Year,birthday,GetDate( )) >= (select MAX(DATEDIFF(Year,birthday,GetDate( )))from studentwhere sdept = '軟件技術')

??????任務：查詢尚沒有學生選修的課程信息。

select sname,DATEDIFF(Year,birthday,GetDate( )) as age,sdept
from student
where sdept != '軟件技術' and DATEDIFF(Year,birthday,GetDate( )) >= (select MAX(DATEDIFF(Year,birthday,GetDate( )))from studentwhere sdept = '軟件技術')

??????任務：將選修了“數據庫應用技術”課程的學生成績增加5分。

UPDATE sc
SET grade = grade + 5
FROM sc
JOIN course ON sc.cno = course.cno
WHERE course.cname = '數據庫應用技術'

⑦ Create database 、alter database、Create table

????????任務：創建一個學生成績管理數據庫XSCJ，存儲在D:\software文件夾下，該數據庫的主數據文件的邏輯名稱為xscj_data，物理文件名為xscj.mdf，初始存儲空間大小為20MB，最大存儲空間為500MB，自動增長量為10%；日志文件的邏輯名稱為xscj_log，文件名為xscj.ldf，初始存儲空間大小為10MB，最大存儲空間為100MB，存儲空間自動增長量為1MB。

create database XSCJ
ON(NAME=xscj_data,
filename = 'C:\mysoftware\xscj.mdf',
size=20,
maxsize=500,
filegrowth=10%
)
log on(NAME=xscj_log,filename='C:\mysoftware\xscj.ldf',size=10,maxsize=100,filegrowth=1
)

???????任務：用SQL命令修改數據庫XSCJ，添加一個次要數據文件，邏輯名稱為XSCJ_Datanew，存放在D:\software下，文件名為XSCJ_Datanew.ndf。數據文件的初始大小為100MB，文件自動增長容量為10MB。

alter database XSCJ
add file(name=XSCJ_Datanew,
filename='C:\mysoftware\XSCJ_Datanew.ldf',
size=100,
filegrowth=10
)

????????任務：按下表的邏輯結構創建student表。

列名稱	類型	寬度	允許空值	缺省值	約束	主鍵	說明
sno	char	10	否			是	學號
sname	varchar	15	否				學生姓名
sex	char	2	否	男			性別
birthday	smalldatetime		是				出生年月
sdept	varchar	15	是				班級號

use XSCJ
create table student
(
sno char(10) not null primary key,
sname varchar(15) not null,
sex char(2) not null default '男',
birthday smalldatetime,
sdept varchar(15)
)

⑧ Insert, Update,Delete

? ? ? ? 任務：向student表添加以下數據。

sno	sname	sex	birthday	sdept
0601110101	張甜甜	女	1986-05-05	計算機應用技術
0601110102	陳強	男	1986-01-06	計算機應用技術

insert into student(sno,sname,sex,birthday,sdept) values('0601110101','張甜甜','女','1986-05-05','計算機應用技術')

insert into student(sno,sname,sex,birthday,sdept) values('0601110102','陳強','男','1986-01-06','計算機應用技術')

????????任務：在student表中，將張甜甜同學，轉到軟件技術系。

update?student
set?sdept='軟件技術'
where?sname='張甜甜'

????????任務：將course表中課程號為 16020011的課程名改為Java語言，學分改為4.0.

update course
set cname='Java語言',credit=4.0
where cno='16020011'

????????任務：在sc表中刪除學號為0604150101的所有選課記錄。

delete from sc where sno='0604150101'

⑨ Create login, ceate user, ?grant to

⑩ 視圖view和索引index

???????任務：?創建視圖view_grade,查詢張甜甜同學的學號，姓名及成績，并顯示視圖view_grade結果。

create view view_grade
as
select student.sno,sname,sc.grade
from student,sc
where student.sno=sc.sno and student.sname='張甜甜'
go
select *from view_grade

????????任務：?創建視圖view_sc_count,統計課程號為’16020010’的課程的選修人數，并對視圖文本加密。顯示視圖結果(Tips:使用 with encryption子句加密)

create view view_sc_count(課程號,人數)
with encryption
as
select cno,count(cno)
from sc
group by cno having cno='16020010'
go
select *from view_sc_count

????????任務：?修改視圖view_bysex, 查詢student表中所有的女同學信息

alter view view_bysex
as
select *from student
where sex='女'
go
select *from view_bysex

????????任務：?刪除視圖view_grade。

drop view view_grade

????????任務：?運行以下代碼仔細觀察運行結果，試解釋產生結果的原因。

?????第一行，是利用視圖sub_student向Student表插入數據，視圖本身沒有發生變化

?????第二行，是查詢視圖里有沒有這個數據，因為視圖并沒有插入有，所以沒有數據顯示

?????第三行，是查詢Student表，因為插入有，所以有數據顯示

????????任務：?為student表創建一個以sname為索引關鍵字的非聚集索引。索引名稱是index_sname。

use xscj
create nonclustered index index_sname on student(sname)

????任務：?刪除索引index_sname。

drop index student.index_sname

? Backup database Teach to disk=”???restore database Teach from disk=’’

? Create proc ?as ?執行：exec proc1（存儲過程和觸發器）

?????任務：?創建一個無參存儲過程pro1，用于返回大于18歲學生的信息

use xscj
go
create procedure pro1
as
select *from student where datediff(year,birthday,getdate())>18
go
exec pro1

??????任務：?創建一個帶參存儲過程pro2，當用戶輸入一個學生姓名時，若該學生存在，就顯示該學生的學號、姓名、性別，年齡、系部。若該學生不存在，則返回“查無此人”的提示信息。執行pro2，進行測試。

use xscj
go
create proc pro2 @name char(6)
as
if exists(select *from student where sname=@name)select sno 學號,sname 姓名,sex 性別,datediff(year,birthday,getdate()) 年齡,sdept 系部from studentwhere sname=@name
else
print'查無此人!'
go
exec pro2 '張甜甜'

??????任務：?創建一個存儲過程pro3，要求帶一個輸入參數和兩個輸出參數，當用戶輸入一個學生學號時，輸出該學生的姓名和所在系部。執行pro3，進行測試。

use xscj
go
if exists(select *from sys.procedures where name ='pro3')drop proc pro3
go 
create proc pro3
@no char(10),@name char(6) output,@sdept char(10) output
as
select @name=sname,@sdept=sdept
from student
where sno=@no
go
declare @name char(6),@sdept char(10)
exec pro3 '0601110101',@name output,@sdept output
select @name as 姓名,@sdept as 系部

??????任務：?創建在SC表中添加一條新選課記錄的存儲過程pro4，新記錄的值由參數提供，請使用pro4添加一條新的選課記錄，學號為S6，課程號為C3。

create  proc pro4(@sno char(10) ,@cno char(8),@grade float='0')
as
insert into sc values(@sno,@cno,@grade)
go
exec pro4 '0601110101','16020015'
select *from sc

? Create trigger

? Create function fun(@ t datetime) returns table ?????????Return

? Case ?when ?while

? Cast(@num as char(2))

數據庫設計綜合實驗

? ? ? ? 實驗內容：設某學校建立圖書管理系統，需要存儲學生、圖書及借閱記錄的基本信息。其中，學生信息包括學號、姓名、性別及系別，圖書信息包括圖書編號、圖書名稱、出版日期及庫存數量，借閱記錄包括學號、圖書編號、借出時間及還書時間。學生可以通過該系統查看圖書信息，借閱后生成借閱記錄，并提交給圖書館。根據該系統的要求完成下列操作。

????????任務1：根據上面的業務邏輯畫出全局E-R圖

????????任務2：根據任務1的E-R圖轉換成關系模式，并標出每個模式的主碼和外碼。

學生信息（學號，姓名，性別，系別）

圖書（圖書編號，圖書名稱，庫存數量，出版日期）

借閱（學號，圖書編號，還書時間，借書時間）

????????任務3：創建圖書管理數據庫LMS，指定數據文件和日志文件都放在D:\LMSDB文件下。

create database LMS
on 
(name=LMS_data,filename='D:\LMSDB\LMS.mdf',SIZE=20,MAXSIZE=500,FILEGROWTH=10%
)
LOG ON (NAME=LMS_log,filename='D:\LMSDB\LMS.ldf',SIZE=10,MAXSIZE=100,FILEGROWTH=1
);

實驗結果：

????????任務4：創建圖書表book。( tips:表的數據類型設計按應用自己設計，需要主鍵)?

CREATE TABLE BOOK(Bid char（10）not null PRIMARY KEY,Bname varchar(20) not null,Bate smalldatetime,Bnumber int not null
)

實驗結果：

????????任務5：創建一個SQL Server登錄帳戶U1，密碼為123，并指定這個登錄名的默認數據庫為LMS。

create login U1 with password='123',default_database=LMS

????????任務6：為登錄帳戶U1創建數據庫（LMS）數據庫用戶admin1，為數據庫用戶admin1授權查看book表.

create user admin1 for login[U1] 
grant select on book to admin1

本篇完。

emmmmmmmmm。