- 在该数据库下创建三张数据表；课程表、教师表、授课表，其具体结构如下：
表1 课程表结构
列名 | 数据类型 | 约束 |
课程号 | 字符长度为8 | 主键 |
课程名 | 可变字符串，最长长度为30 | 非空，取值唯一 |
学分 | 微整型 | 取值1-8 |
开课学期 | 微整型 | 取值1-12 |
课程性质 | 字符串，长度为4 | 取值‘必修’，‘选修’ |
考试性质 | 字符串，长度为4 | 取值‘考试’，‘考查’ |
授课时数 | 微整型 | 取值小于等于68 |
实践时数 | 微整型 |
create table course(
c_id char(8) primary key,
c_name varchar(30)not null unique,
c_credit tinyint check(c_id between 1 and 8),
c_nature char(4) check(c_nature='必修' or c_nature='选修'),
c_time tinyint check(授课时数<=68))查看约束
execute sp_helpconstraint 课程表授课表
列名 | 数据类型 | 约束 |
课程号 | 字符串，长度为8 | 主键 |
教师号 | 字符串，长度为10 | 主键 |
授课学年 | 字符串，长度为4 | 主键 |
授课学期 | 微整型 | 主键 |
授课类别 | 字符串，长度为6 | 取值‘主讲’，‘辅导’，‘带实验’ |
授课时数 | 微整型 |
create table sc(
课程号 char(8),
教师号 char(10),
授课学年 char(4),
授课学期 tinyint,
授课类别 char(6) check(授课类别=‘主讲’ or 授课类别='辅导' or 授课类别='带实验'),
primary key(课程号,教师号,授课学年,授课学期)3. 修改教师表中“出生日期”的数据类型为“date”
alter table 教师表
alter column 出生日期 date4. 对授课表添加外键约束：课程号参照课程表中的课程号，教师号参照教师表中的教师号。
alter table 授课表 add foreign key(课程号) references 课程表(课程号)
alter table 课程表 add foreign key(教师号) references 教师表(教师号)统计总共有多少学生
select count(*) from student查询各门课的课程号和考试平均成绩及选课人数。
select cno,avg(grade),count(*) from sc
group by cno查询平均成绩大于80的学生的学号。
select sno from sc group by sno having avg(grade)>80查询选课后还没有登录成绩的同学学号。
select sno from sc where grade is null从Student表中检索出所有的院系名，并且要求相同系名不重复显示。
select distinct sdept from student查询’B008’课程的最高分学生学号，课程号和成绩。
select sno,cno,max(grade)
from sc
where cno='B008'查询哪些学生没有选课，要求列出学号、姓名和所在系。
select student.sno,sname 姓名,sdept
from student left join sc on student.sno=sc.sno
where sc.sno is null用子查询查询与‘李素素’在同一个院系的学生。
select *
from student
where sdept = (
select sdept
from student
where sname='李素素')
and sname <> '李素素'用子查询找出平均成绩为大于80分的学生的学号、姓名、院系。
select sno,sname,sdept
from student
where sno in(
select sno from sc
group by sno
having avg(grade)>80)用子查询查询选修了“数据库及应用”课程的学生的学号、姓名。
select sno,sname
from student
where sno in(select sno from sc where cno in(select
cno from course where cname='数据库及应用'))** 用子查询查询修了‘B008’课程且成绩高于此课程的平均成绩的学生的学号和成绩。**
select sno,grade
from sc
where grade>(
select avg(grade) from sc where cno='B008')
and cno='B008'查询(石油工程学院)考试(成绩最高)的学生的(学号)。
select sno
from student
where sdept='石油工程学院' and grade=(select max(grade)
from sc where sno in(select sno from student where sdept='石油工程学院')用子查询查询（没有选修‘B007’号课程）的学生（姓名和所在系）。
select sname,sdept
from student
where sno not in(select sno from sc where cno = 'B007')用子查询找出‘杨华’所选课程的学分之和。
select sum(ccredit)
from course
where cno in(select cno from sc where sno =(select sno from student where sname='杨华'))用子查询找出所有学生中年龄最大的学生的姓名、年龄
select sname,sage
from student
where sage=(select max(age) from student)用子查询找出所有没有成绩的学生的姓名及系别。
select sname,sdept
from student
where sno in(select sno from sc where grade is null)用子查询统计‘理学院’每位学生的平均成绩。
select sno,avg(grade)
from sc
group by sno
having sno in(select sno from student where sdept='理学院')查询‘理学院’的学生和年龄不大于20岁的学生。
select * from student where sdept='理学院'
union
select * from student where sage<=20查询‘地球科学与技术学院’且年龄不大于20岁的学生。
select * from student where sdept='地球科学与技术学院'
intersect
select * from student where sage<=20查询‘地球科学与技术学院’的学生与年龄不大于20岁的学生的差集。
select * from student where sdept='地球科学与技术学院'
except
select * from student where sage<=201、 将新生（学号：G2016006；姓名：TOM；性别：男；年龄：22；所在系：理学院；专业：应用数学；电话：17712346666；EMAIL：TOM@126.COM）的记录插入到学生表中，并增加相应的选课记录（G2016006，B008）。
insert into student
values('G2016006','TOM','男','应用数学','理学院',22,'17712346666','TOM@126.COM')
insert into sc
values('G2016006','B008',null)2、 假设过了一个学年，将所有学生的年龄增加1岁
update student
set sage=sage+13、 事后发现学生叶家裕在《会计学》考试中作弊，将其该科成绩改为0分。
update sc
set grade=0
where sno =(select sno from student where sname='叶家裕')
and cno=(select cno from course where cname='会计学')1) 创建一个名为LECTURE_view1的视图，包含教师表中所有男教师的资料。
create view LECTURE_view1
as
select *
from 教师表
where 性别='男'3) 创建一个名为LECTURE_view3的视图，包含每个教师的教师号、姓名及其授课总学时。
create view LECTURE_view3
as
select 教师号,姓名,sum(授课学时)
from 教师,授课
where 教师.教师号=授课.授课号
group by 教师.教师号,教师名1) 修改LECTURE_view1视图，将其改为所有女教师的信息，并查询输出其中数据。
alter view LECTURE_view1
as
select *
from 教师
where 性别='女'2) 用user1登录并新建一个数据库查询后，在可用数据库下拉列表框中能否看到并选中“授课管理_本人姓名”数据库？为什么？
用户具有登陆账号后，只能连接到SQLSERVER服务器上，还不具有访问用户数据库的能力，只有成为数据库合法用户后，才能访问数据库
1) 建立SQL Server身份验证模式的登录帐户user1和user2。
create login user1 with password='123456'2) 将user1和user2映射为授课管理数据库中的用户，用户名同登录名。
create user user1 for login user1**3) 授予user1，user2具有对教师表和课程表的查询、插入权限。 **
grant user1,user2 select,insert on 教师 to user1,user2
grant user1,user2 select,insert on 课程 to user1,user24) 收回usr1对教师表的查询权。
revoke select on 教师 from user1指定的架构名称dbo不存在，或者你没有使用该名称的权限
这是因为SQL_user1用户没有在dbo架构中创建对象的权限，而且没有为SQL_user1用户指定默认的架构，因此创建表失败
解决的方法是让系统管理员定义一个架构，并将该架构的所有权限赋给SQL_user1用户，并将该架构设为SQL_user1的默认架构create schema testSchema authorization sql_user1 go alter user sql_user1 with default_schema=testshema
6) 在授课管理数据库中建立用户角色role1,并将user1,user2添加到此角色中。（提示：先用”create role 角色名”创建一个角色，然后用“exec sp_addrolemember ‘角色名’，’添加用户名’”添加指定用户到指定角色中。）
create role role1
exec sp_addrolemember 'role1'7) 授予role1角色对课程表具有插入、删除和查询权限。
grant insert,delete,select
on 课程
to role1定义一个numeric(4,1)类型的变量，然后将选课表中的平均成绩赋给该变量，最后输出“所有成绩的平均值为：”（其中为实际平均成绩值。
declare @avg numeric(4,1)
set @avg=(select avg(grade) from sc)
print '所有成绩的平均值为'+cast(@avg as char(8))在SelectCourse_*（*为本人真实姓名）数据库中，查询全体学生的成绩（不包括未选课学生的记录），要求显示学号，姓名，课程号，成绩等级（[90,100]为‘优秀’，[80,89]为‘良好’，[70,79]为‘中等’，[60,69]为‘及格’，NULL为‘无成绩’，其余为‘不及格’，要求使用case语句）
select student.sno,sname,cno,case
when grade<60 then '不及格'
when grade<70 then '及格'
when grade<80 then '中等'
when grade<90 then '良好'
when grade<=100 then '优秀'
else '无成绩'
end as '等级表'
from student,sc
where student.sno=sc.sno在SelectCourse_*（*为本人真实姓名）数据库中，判断是否有课程学分小于3，如果有则将所有课程学分加1，直到所有课程学分都大于等于3或有课程学分将超过学分上限值5（包含5）为止。
while(select min(ccredit) from course) <3
begin
if(select max(ccredit) from course) >=5
break
else
update course set ccredit =ccredit +1
end编写一个名为“SelectSTUByNamePROC”的存储过程，存储过程接收一个名为“@name”的参数，存储过程的作用是根据“@name”进行模糊查询，从学生表中查询姓名中含有“@name”的学生信息。
create proc selectstubynameproc char(20)
as
begin
select * from student
where sname like '%'+rtrim(@name)+'%'
end
exec selectstubynameproc '家'编写一个名为“UpdateSTUAgePROC”的存储过程，存储过程接收一个名为“@SNO”的参数和一个名为“@Sage”的参数，存储过程的作用是将学生表中学号为“@SNO”的学生年龄修改成“@Sage”。
create proc UpdateSTUAgePROC @Sno char(10),@Sage tinyint
as
begin
update student
set sage=@sage
where sno=@sno
end**编写一个名为“InsertCoursePROC”的存储过程，该存储过程根据接收的参数向课程表插入一条记录。存储过程接收4个名为“@CNO”、“@CNAME”、“@CREDIT”,“@XKLB”的参数，分别代表课程号，课程名，学分，课程性质。**
create proc InsertCoursePROC @CNO char(10),@cname char(30),@credit tinyint,@xklb char(5)
as
begin
insert into course(cno,cname,ccredit,xklb) values(@cno,@cname,@credit,@xklb)
end编写一个名为“GetMaxMinGradePROC”的存储过程，存储过程接收一个名为“@CNO”的参数，一个名为“@MAXGRADE”的输出参数和一个名为“@MINGRADE”的输出参数。存储过程的作用是使用输出参数获取课程号为“@CNO”的课程的最高分与最低分。
create proc GetMaxMinGradePROC @CNO CHAR(10),@MAXGRADE NUMERIC(3,0) OUTPUT,@MINGRADE NUMERIC(3,0) OUTPUT
AS
begin
select @maxgrade=max(grade) from sc where cno=@cno
select @mingrade=min(grade) from sc where cno=@cno
end
declare @maxgrade int
declare @mingrade int
exec GetMaxMinGradePROC 'B008',@maxgrade output,@mingrade output
print @maxgrade
print @mingrade在COURSE表上创建一个名为“AfterInsertTRI”的INSERT后触发器，判断插入的课程学分是否大于5，如果大于5，将其学分修改为5。
create trigger AfterInsertTRI on course
after insert
as
begin
if(select ccredit from inserted)>5
update course set ccredit=5 from inserted i
where course.cno=i.cno
end删除上题创建的“AfterInsertTRI”触发器，然后在COURSE表上创建一个名为“InsteadInsertTRI”的INSERT替代触发器，触发器判断插入的课程学分是否大于5，如果大于5，将学分修改为5再插入；如果学分小于等于5，按原插入数据插入。
create trigger InsteadInsertTRI
on course
instead of insert
as
if(select ccredit from inserted)>5
begin
update course set ccredit =5 from inserted
where course.cno=inserted.cno
end使用UPDATE触发器实现：当修改SC表的成绩时（每次只更新一行数据），如果修改后的成绩小于等于修改以前的，则不允许修改，并提示“修改后成绩小于等于修改前成绩，修改失败！”，否则提示“修改成功！”。
create trigger update_Sc
on sc
after update
as
if(select grade from inserted)<(select grade from deleted)
begin print '修改后成绩小于等于修改前成绩，修改失败！'
rollback transaction
else
print '修改成功'
