代碼
use master
go
-----------創建數據庫------------

if exists (select * from sysdatabases where name='stuDB')
drop database stuDB
create database stuDB
on size=3mb,
maxsize=10mb,
filegrowth=1mb
)
log on
(
name='stuDB_log',
filename='D:/stuDB_data.ldf',
size=1mb,
filegrowth=1mb
)

-----------創建數據庫表------------
use stuDB
go
if exists (select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(
    stuId int identity(1,1) primary key not null,
    stuName varchar(20) not null,
    stuNo varchar(20) not null,
    stuSex char(6) not null,
    stuAge int not null,
    stuAddress text null
)
go

if exists (select * from sysobjects where name='stuMarks')
drop table stuMarks
create table stuMarks
(
    marksId int identity(1,1) primary key not null,
    ExamNo varchar(50) not null, --考號
    stuNo char(6) not null,--學號
    writtenExam int  null,--筆試成績
    LabExam int null--機試成績
)
go
--向學員信息表stuInfo插入數據--
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES('張秋麗','s25301','男',18,'北京海淀')
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛陽')
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('歐陽俊雄','s25304','男',28,'威武哈')

--向學員成績表stuMarks插入數據--
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('E2005070001','s25301',80,58)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam) VALUES('E2005070002','s25302',50)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('E2005070003','s25303',97,82)

--查看數據--
select * from stuInfo
select * from stuMarks

/*=======查詢數據練習=========*/
--1.查詢兩表的數據--
select * from stuInfo
select * from stuMarks
 --2.查詢男學員名單--
select * from stuInfo where stuSex='男'
 --3.查詢筆試成績優秀的學員情況（成績在75~100之間)--
select * from stumarks where writtenexam between 75 and 100
 --4.查詢參加本次考試的學員成績，包括學員姓名，筆試成績，機試成績--
select i.stuName,m.writtenExam,m.LabExam from stuInfo as i inner join stuMarks as m on m.stuNo = i.stuNo
 --5.統計筆試考試平均分和機試考試平均分--
select avg(writtenExam) as 筆試平均成績,avg(LabExam) as 機試平均成績 from stuMarks
select avg(writtenExam) 筆試平均成績,avg(LabExam) 機試平均成績 from stuMarks
 --6.統計參加本次考試的學員人數
select count(stuno) from stumarks
 --7.查詢沒有通過考試的人數（筆試或機試小于60分）--
select count(stuno) from stumarks where writtenExam <= 60 or labexam<=60
select * from stumarks where writtenExam is null or labexam is null --查詢為全部參加考試的信息
 --8.查詢學員成績，顯示學號，筆試成績，機試成績，平均分--
select stuno as 學號,writtenExam 筆試,labexam 機試,(writtenExam+labexam)/2 平均成績 from stumarks
 --9.排名次（按平均分從高到低排序），顯示學號、平均分-- 
select stuno as 學號,(writtenExam+labexam)/2 平均成績 from stumarks order by (writtenExam+labexam)/2 desc

select stuno as 學號,(writtenExam+labexam)/2 平均成績 from stumarks order by 平均成績 desc
 --10.排名次（按平均分從高到低排序），顯示姓名，筆試成績，機試成績，平均分--
select i.stuno as 學號,writtenExam 筆試,labexam 機試,(writtenExam+labexam)/2 平均成績 
from stumarks as m inner join stuinfo as i on m.stuno = i.stuno order by 平均成績 desc
--根據以上SQL語句總結：凡是兩個表中有同名的列名就需要用別名卻分開來，如果沒用別名可以直接查詢列明

 --11.根據平均分，顯示前兩名信息，包括姓名、筆試成績、機試成績、平均分--
select top 2 i.stuno as 學號,writtenExam 筆試,labexam 機試,(writtenExam+labexam)/2 平均成績 
from stumarks as m inner join stuinfo as i on m.stuno = i.stuno order by 平均成績 desc
/*=======修改數據練習=========*/
--都提5分--
--100分封頂（加分后超過100分的，按100分計算）--

update stumarks set writtenExam = writtenExam + 5
update stumarks set writtenExam = 100 where writtenExam>100