實(shí)驗(yàn)七 數(shù)據(jù)庫的連接和嵌套查詢實(shí)驗(yàn)

實(shí)驗(yàn)十 T-SQL語句的綜合應(yīng)用

一 實(shí)驗(yàn)?zāi)康暮鸵螅?/font> 掌握變量的定義及賦值,、數(shù)據(jù)顯示及IF、WHILE、CASE邏輯控制語句,。 實(shí)驗(yàn)類型：設(shè)計 驗(yàn)證 實(shí)驗(yàn)內(nèi)容：創(chuàng)建學(xué)員成績數(shù)據(jù)庫stu,。

實(shí)驗(yàn)?zāi)康暮鸵螅?/span> 掌握簡單子查詢、IN子查詢及EXISTS子查詢的用法,，并能應(yīng)用T-SQL進(jìn)行綜合查詢,。 實(shí)驗(yàn)類型：設(shè)計 驗(yàn)證

CREATE TABLE STUINFO

(

stuName char(9),

stoNo char(9) primary key,

stuSex char(2) check(stuSex in(‘男’,’女’)),

stuage int,

stuSeat char(2),

stuAddress varchar(20),

)

CREATE TABLE STUMARKS

(

ExamNo char(9),

stuNO char(9),

writtenExam numeric(5,2),

LabExam numeric(5,2),

Primary key(ExamNo,stuNo),

Foreign key(stuno)references STUINFO(stoNo),

)

輸入數(shù)據(jù)：

insert into STUINFO values('張秋麗','s25301','男',18,'1','北京海淀')

insert into STUINFO values('李文才','s25302','男',28,'2','地址不詳')

insert into STUINFO values('李斯文','s25303','女',22,'3','河南洛陽')

insert into STUINFO values('歐陽俊雄','s25304','女',34,'4','地址不詳')

insert into STUINFO values('梅超風(fēng)','s25318','女',23,'5','地址不詳')

insert into STUMARKS values('s271811','s25303',80,58)

insert into STUMARKS values('s271813','s25302',50,90)

insert into STUMARKS values('s271816','s25301',77,82)

insert into STUMARKS values('s271818','s25328',45,65)

試編寫SQL語句查找李文才的左右同桌。

set @name='李文才'
select @ stuSeat =id from STUINFO
where  stuName =@name
select * from STUINFO
where (id =@ stuSeat -1) or (id =@ stuSeat +1)
go

二 統(tǒng)計并顯示本班筆試平均分,，如果平均分在70以上,，顯示“成績優(yōu)秀“，并顯示前三名學(xué)員的考試信息,；如果在70以下,，顯示“本班成績較差“，并顯示后三名學(xué)員的考試信息

DECLARE @myavg float

SELECT @myavg=AVG(writtenExam) FROM STUMarks

print ‘本班平均分’+convert(varchar(5),@myavg) 

if(@myavg>70)

BEGIN

print ‘本班筆試成績優(yōu)秀,，前三名的成績?yōu)椋?/span>’

SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam DESC

END

ELSE

BEGIN

print ‘本班筆試成績較差,，后三名的成績?yōu)椋?/span>’

SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam

END

三 本次考試成績較差，假定要提分,，確保每人筆試都通過,。提分規(guī)則很簡單，先每人都加2分,，看是否都通過,，如果沒有全部通過,，每人再加2分，再看是否都通過,，如此反復(fù)提分,，直到所有人都通過為止 。

DECLARE @n int

WHILE(1=1)

  BEGIN

    SELECT @n=COUNT(*) FROM stuMarks

            WHERE writtenExam<60     

    IF (@n>0)

       UPDATE STUMarks               

 SET writtenExam=writtenExam+2

   ELSE

       BREAK    

  END

print '加分后的成績?nèi)缦拢?/span>'

SELECT * FROM STUMarks

四 采用美國的ABCDE五級打分制來顯示筆試成績,。

 A級:   90分以上

 B級:   80－89分

 C級:   70－79分

 D級:   60－69分

 E級:   60分以下

print  'ABCDE五級顯示成績?nèi)缦?/span>:'

SELECT stuNo,

   成績= CASE  

                  WHEN writtenExam<60 THEN 'E'

                  WHEN writtenExam BETWEEN 60 AND 69 THEN 'D'

                  WHEN writtenExam BETWEEN 70 AND 79 THEN 'C'

                  WHEN writtenExam BETWEEN 80 AND 89 THEN 'B'

                  ElSE    'A'

              END

      FROM stuMarks

五 有學(xué)員成績數(shù)據(jù)庫stu,，其中學(xué)員信息表stuinfo和學(xué)生成績表stumarks如下：

CREATE TABLE STUINFO

(

stuName char(9),

stoNo char(9) primary key,

stuSex char(2) check(stuSex in(‘男’,’女’)),

stuage int,

stuSeat char(2),

stuAddress varchar(20),

)

CREATE TABLE STUMARKS

(

ExamNo char(9),

stuNO char(9),

writtenExam numeric(5,2),

LabExam numeric(5,2),

Primary key(ExamNo,stuNo),

Foreign key(stuno)references STUINFO(stoNo),

)

insert into STUINFO values('張秋麗','s25301','男',18,'1','北京海淀')

insert into STUINFO values('李文才','s25302','男',31,'3','地址不詳')

insert into STUINFO values('李斯文','s25303','女',22,'2','河南洛陽')

insert into STUINFO values('歐陽俊雄','s25304','男',28,'4','新疆威武哈')

insert into STUINFO values('梅超風(fēng)','s25318','女',23,'5','地址不詳')

insert into STUMARKS values('s271811','s25303',93,59)

insert into STUMARKS values('s271813','s25302',63,91)

insert into STUMARKS values('s271816','s25301',90,83)

insert into STUMARKS values('s271818','s25328',63,53)

六、統(tǒng)計本次考試的缺考情況

七,、比較筆試平均分和機(jī)試平均分,，較低者進(jìn)行循環(huán)提分，但提分后最高分不能超過97分 ,。加分后重新統(tǒng)計通過情況 

八,、統(tǒng)計通過率

--SELECT * FROM stuInfo

--SELECT * FROM stuMarks

/*--------------統(tǒng)計考試缺考情況----------------------*/

SELECT 應(yīng)到人數(shù)=(SELECT count(*)  FROM STUINOFO) ,  

  實(shí)到人數(shù)=(SELECT count(*) FROM STUMARKS)  ,

   缺考人數(shù)=((SELECT count(*) FROM STUINOFO)-(SELECT count(*) FROM STUMARKS))

/*----統(tǒng)計考試通過情況，并將結(jié)果存放在新表newTable中---*/

IF EXISTS(SELECT * FROM sysobjects

                                  WHERE name='newTable')

    DROP TABLE newTable

SELECT  stuName,stuInfo.stuNo,writtenExam ,labExam ,

   isPass=CASE

           WHEN writtenExam>=60 and labExam>=60 THEN 1

           ELSE 0

       END

    INTO newTable FROM STUINFO 

       LEFT JOIN  STUMARKS    

           ON STUINFO.stuNo= STUMARKS.stuNo

--SELECT * FROM newTable --查看統(tǒng)計結(jié)果,，可用于調(diào)試 

/*-酌情加分：比較筆試和機(jī)試平均分,，決定加哪門---*/

DECLARE @avgWritten numeric(4,1)

DECLARE @avgLab numeric(4,1)

SELECT @avgWritten=AVG(writtenExam) FROM newTable

      WHERE  writtenExam IS NOT NULL

SELECT @avgLab=AVG(labExam)FROM newTable

     WHERE  labExam IS NOT NULL

IF @avgWritten<@avgLab

  WHILE (1=1) --循環(huán)給筆試加分，最高分不能超過97分

    BEGIN  

      UPDATE newTable SET writtenExam=writtenExam+1

      IF (SELECT MAX(writtenExam) FROM newTable )>=97

         BREAK

    END

ELSE

IF @avgWritten>@avgLab

  WHILE (1=1) --循環(huán)給筆試加分,，最高分不能超過97分

    BEGIN  

      UPDATE newTable SET LabExam=LabExam+1

      IF (SELECT MAX(LabExam) FROM newTable )>=97

         BREAK

    END

  --因?yàn)樘岱?，所以需要更?/span>isPass（是否通過）列的數(shù)據(jù)

UPDATE newTable

  SET isPass=CASE

        WHEN writtenExam>=60 and labExam>=60 THEN 1

        ELSE  0

     END

--SELECT * FROM newTable  --可用于調(diào)試

/*--------------顯示考試最終通過情況----------------*/

SELECT 姓名=stuName,學(xué)號=stuNo

 ,筆試成績=CASE

            WHEN writtenExam IS NULL THEN '缺考'

             ELSE  convert(varchar(5),writtenExam)

          END

 ,機(jī)試成績=CASE

             WHEN labExam IS NULL THEN '缺考'

             ELSE  convert(varchar(5),labExam)

          END

 ,是否通過=CASE

             WHEN isPass=1 THEN '是'

             ELSE  '否'

           END

 FROM newTable

/*--顯示通過率及通過人數(shù)--*/

SELECT 總?cè)藬?shù)=count(*) ,通過人數(shù)=SUM(isPass),

   通過率=(convert(varchar(5),AVG(isPass*100))+'%')  FROM newTable