建库、建表练习

--创建数据库
if exists(select*from sysdatabases where name='school')
drop database school
create database school
on primary
(
 name='school_db',
 filename='D:\project\school_db.mdf',
 size=5mb,
 maxsize=100,
 filegrowth=15%
)
log on
(
 name='school_log',
 filename='D:\project\school_log.ndf',
 size=3mb,
 maxsize=50,
 filegrowth=5%
)
go
--学生信息表(stuInfo)
create table stuInfo
(
stuNo varchar(50) primary key not null,
stuName varchar(50) not null,
stuSex varchar(50) not null default('男'),
stuAge int not null,
stuSeat int identity(1,1),
stuAddress text default('不详')
)
alter table stuinfo
add constraint ck_stoNo check(stuNo like 's253[0-9][0-9]')
alter table stuinfo
add constraint ck_stuSex check(stusex between '男'and'女')
alter table stuinfo
add constraint ck_stuage check(stuage between 15 and 50)
alter table stuinfo
add constraint ck_stuNo check(stuNo between 1 and 30)
--学生成绩表
create table stuMarks
(
 examNo varchar(50)primary key not null,
 StuNo varchar(50)not null,
 writtenExam int default(0),
 LabExam int default(0)
)
alter table stumarks
add constraint ck check(examNo like 'E200507[0-9][0-9][0-9][0-9]')
alter table stumarks
add constraint FK_stuNo foreign key (stuNo)
references stuinfo(stuNO)

alter table stumarks
add constraint ck_wExam check(writtenExam between 0 and 100 )
alter table stumarks
add constraint ck_LExam check(LabExam between 0 and 100 )
--插入数据
insert into stuinfo(stuNo,StuName,stusex,stuage,stuaddress)
select 's25301','张梅','女',22,'浙江金华' union
select 's25312','李四','男',19,'浙江丽水' union
select 's25303','王五','男',25,'浙江文章'
select* from stuinfo
--插入数据
insert into stumarks(examNo,stuNo,writtenExam,LabExam)
select 'E2005071111','s25301',100,50 union
select 'E2005072222','s25303',77,60 union
select 'E2005073333','s25312',80,76 
select *from stumarks
--向学员信息表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)
--1.查询两表的数据
select * from stuInfo
select * from stuMarks
--2.查询男学员名单
select * from stuInfo where stuSex='男'
--3.查询笔试成绩优秀的学员情况（成绩在75～100之间)
select stuNo ,writtenExam from stuMarks where writtenExam between 75 and 100 
--4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩
select stuName,writtenExam,labExam from  stuInfo 
  inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
--5.统计笔试考试平均分和机试考试平均分
select avg(writtenExam) as '笔试考平均分',
  avg(labexam) as '机试平均分' from stuMarks
--6.统计参加本次考试的学员人数 
select count(stuNo) as 考试人数 from stuMarks
--7.查询没有通过考试的人数（笔试或机试小于60分）
select count(stuNo) as 未通过的人数 from stuMarks  where 
labExam<60 or writtenExam<60
--8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分
select stuNO as 学号,writtenExam as 笔试,labExam as 机试,
  (writtenExam+labExam)/2.0 as 平均分 from stuMarks
--9.排名次（按平均分从高到低排序），显示学号、平均分
select stuNO as 学号,(writtenExam+labExam)/2.0 as 平均分
  from stuMarks order by 平均分 desc
--10.排名次(按平均分从高到低排序),显示姓名,笔试成绩,机试成绩,平均分
select stuName as 姓名,writtenExam as 笔试,labExam as 机试,
(writtenExam+labExam)/2.0 as 平均分 from stuInfo 
inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo 
order by 平均分 desc
--11.根据平均分,显示前两名信息,包括姓名、笔试成绩、机试成绩、平均分
select top 2 stuName as 姓名,writtenExam as 笔试,
labExam as 机试,(writtenExam+labExam)/2.0 as 平均分 from stuInfo 
inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo 
order by 平均分 desc
--都提5分
update stuMarks set writtenExam=writtenExam+5
select * from stuMarks
--100分封顶（加分后超过100分的，按100分计算)
update stuMarks set writtenExam=100
where writtenExam>100

select * from stuMarks

--创建登录
exec sp_addLogin 'likao',123
exec sp_grantdbaccess 'likao','cccc'
grant select on stumarks to cccc
grant select,update,delete on stuinfo to cccc