一、建数据库和插入操作
1、 用代码创建了一个text1数据库和运行结果如下

CREATE DATABASE text1
ON(NAME = text1,FILENAME ='D:\sql\text1_data.mdf',SIZE = 5MB,MAXSIZE = 20,FILEGROWTH = 20)LOG ON(   NAME = text1_log,FILENAME ='D:\sql\text1_log.ldf',//存储的位置SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 1MB//主数据文件名和主日志要不起相同名字，否则报错的);

刷新一下数据库，点开database，点开表这时可以看到新建的四个表、
2、altert able语句将student表的sex属性添加check约束：代码与运行结果如下

ALTER TABLE student
ADD CONSTRAINT CK_score_sex CHECK(sex IN('男','女'))

3、每一个表用insert into语句插入5条以上记录，要求满足约束条件，代码如下

insert into student(sno,sname)
values('182','李伟'),('184','木棉花花'),('185','李花'),('187','陆花'),('186','马华')

insert into score(sno,cno)
values('182','1'),('184','2'),('185','3'),('187','4'),('186','5')

在SQL Server Management Studio中生成4个表的关系图。选择数据库名->数据库关系鼠标右键->新建数据库关系图，分别添加四个表，然后把student作为主键其他为外键，右击一个表选择表示图-标准，可以清楚地看到表的属性列名等等。

二、数据更新、视图及用户
1、创建一张history_student表（结构同student），将学生表中数据全部插入到history_student表中

select*into history_studentfrom student

2、新插入一门课程，并默认所有学生都选修该门课程插入到成绩表中（成绩为NULL）

insert into course(cname,cno)
values('java','08181069')
insert into score
select sno,'08181069',NULL from student

3、将所有成绩为空的成绩全部修改为60。

update score
set grade='60'
where grade=null

4、将所有平均成绩低于60分的学生成绩记录删除

Update score
set grade=null
where sno in(select sno from score y
where y.sno=sno
group by sno
having avg(grade)<60)

5、创建一个视图VIEW1：查询d01系学生的信息，只投影学生学号，姓名，年龄，系。要求在进行更新操作时满足约束

create view v_student_3
as
select sno,sname,age,Depart
from student
where specialty='001’

6、由VIEW1导出一个视图VIEW2：查询年龄在18到20岁的学生

create view view2
as 
select*
from v_student_1
where age between 18 and 20

7、将查询语句select sno,sname from view2 where ssex=’女’等价转换为对基本表的查询语句并运行结果

Select sno,sname 
from studentwhere gender='女' and depart='001' 
and(age between 10 and 20)

8、将VIEW1,VIEW2删除。

Deop view v_student_3
Deop view v_student_4

9、创建登录用户：USER1,USER2,USER3,DBA给USER1授予select,update(sage)权限，并允许其传递授权；USER1授予select,update(sage)权限给USER2，其中select允许传递；USER2授予select权限给USER3,不允许传递；DBA给USER3授予update(sage)权限，不允许其传递授权
10、将第9题中的所有权限逐级回 –9

grant select,update(sage)
on student 
to user1
with grant option
grant select
on student
to user2
with grant option
grant select
on student
to user3
with grant option
--
Exec sp_addlogin 'user1','123','stu'
exec sp_adduser 'user1','user1_a'
Exec sp_addlogin 'user2','123','stu'
exec sp_adduser 'user2','user2_a'
Exec sp_addlogin 'user3','123','stu'

三、数据查询语句
1、查询选修了课程号为"01",“02”,"03"的学号、课程号和成绩记录

select sno,cno,grade 
from score 
where cno IN('08181060','08181170','08181192')

2、查询课程号"01",“02”,"03"除外的，成绩大于60分的学号、课程号和成绩记录

select sno,cno,grade
from score
where cno NOT
IN(’01’，’02’，’03’)
group  by sno,cno,grade
AND  grade>=60

3、查询选修了课程号为"01",“02”,“03”，成绩在70-80分的学号、课程号和成绩记录

select sno,cno,grade
from score
where cno IN(’01’，’02’，’03’）
and grade between 70 and 80

4、查询选修了课程号为01 的最好成绩、最坏成绩、平均成绩

select max(grade),min(grade),avg(grade)
from score
where cno=’01’

5、查询20140101班的男生人数（学号前8位作为班级号）

select count(sno)
from student
where sno like '20158741__'and gender=’男’

6、查询2015班张姓同学的人数

select count(sno)
from student
where sno like '20158741__' and sname like '张%'

7、查询’20140101’班张姓同学的学号、姓名

select sno,snme
from student
where sno like '20158741__'and sname like’张%’

8、查询1980后出生的副教授记录

select *
from teacher
where Age<year(getdata()-1980) and prof=’副教授’

9、查询编号为‘0001’教授的授课门数

select count(cno)
from coure
where tno='0128'

10、查询没有安排授课老师的课程信息

select *
from course
where Tno is NULL

11、在表score中插入数据，要求每个同学选修3门课以上，
–每门课至少3个同学选修

insert into score
select cno
from score
group by cno
having count(*)>=3 
#Values()
#Insert into score
#Values()

12、查询至少选修了3门课的同学的学号和选修课程门数

select sno,count(cno)
from score 
group by sno
having count(cno)>=3

13、查询学号为101，,102,103三位同学不及格课程门数，查询结果按照学号降序排列

select count(distinct cno),sno
from score
where sno IN('2015',2013')
and grade<60
group by All sno
order by sno desc

14、查询每个同学的学号、姓名、选修的课程名称、成绩、上课老师姓名，
–按照学号升序排列结果

select student.sno,sname,cname,grade,tname
from  student,course,score,teacher
where student.sno=score.sno AND teacher.tno=course.tno AND course.cno=score.cno

15、查询《数据库课程设计》的间接先修课，要求出输出课程编号，课程名称，间接先修课的课程编号和名称

select a.cno,a.cname,a.sno,a.cname
from course a,course b,course c
where c.pcno=b.cno and b.pcno=c.p.cno
cname='数据库课程设计'

16、查询所有学生的选课信息（包括没有选课的学生）

select Student.sno,cno
From student left outer join score on 
student.sno=score.sno

17、查询学习课程号为C06的学生与姓名

select score.sno,sname
from score,student
where score.sno=student.sno and cno='C06'

18、查询至少选修了课程号为C02,C06课程的学号与姓名

select  sno 
from score
where cno='C02'
intersect
select sno
from score
where cno='C06' 

19、查询学习全部课程的学号

select sno
from score
group by sno
having count(sno)=(select count(cno)
from course0

20、查询每个学生超过自己选修课程平均成绩的课程号

select x.cno,x.sno
from score x where grade>=(select AVG(grade)
from score
where sno=x.sno

21、查询平均成绩大于80的课程编号和平均成绩

select cno,AVG(grade)
from score
group by cno
HAVING AVG(grade)>=80

22、查询专业specialty中第3个字符为字母“f“”的学号、姓名、专业

select sno,sanme,specialty
from student
where specialty like’__f%’

23、查询每个同学的学号、最好成绩、最差成绩、平均成绩

select sno,max(grade),min(grade),avg(grade)
from score
group by sno

24、查询最低分大于70，最高分小于90的同学学号

select sno
from score
where min(grade)>70 and max(grade)<90

25、查询所有同学的学号、姓名、最好成绩、最差成绩、平均成绩

select score.sno,student.sname,max(grade),min(grade),avg(grade)
from student,score
where student.sno=score.sno
group by score.sno,sname

26、查询最低分大于70，最高分小于90的学生学号姓名

select student.sno,sname
from student
where sno in(select sno
from score
group by sno
having MIN(grade)70 and max(grade)<90)

27、查询选修‘0818’课程的成绩高于‘0158’号同学成绩是所有学生学号

select sno
from score
where grade>(select grade)
where cno='0818' and cno='0158'

28、查询选修‘203‘课程的成绩高于‘03‘号同学成绩的使用学生学号、姓名

select  sno, sname
from  student
where sno in(select sno
from score
where grade>(select grade from score 
where cno='203`’ and sno='02')
and  cno='203'

29、查询‘何金凤‘同学同岁的使用学生的信息
select *
from student
where age=(select age
from student
where sname='何金凤’)
’’
30、查询与张三同龄同班的学生学号

select sname
from student
where specialty=(
select specialty
from student
where sanme='张三
and age=(select age
from student
where sname='何金’

31、查询成绩比该课程平均成绩低的学号、成绩

select x.sno,x.grade
from score x
where x.grade<(select avg(grade) from score
where cno=x.cno)
```、
三、sql高级编程
1、创建存储过程，给定老师姓名，删除学习老师的选课记录。存储过程的参数为老师姓名，返回删除记录的个数。
```c
create proc pp
@xm char(20),@del_num int output
as
begin
declare @tno char(4)
select @tno=tno from techer where tname=@Xm
delete from score
where cno in(select cno from course where @cno=tno)
set @del_num=@@rowcount
END

2、创建存储过程，给定老师姓名，把score表中该老师授课创建低于60分的改为0分，返回修改记录的个数

create proc pp
@xm char(10),@del_num int output
as 
begin
declare @tno char(10)
select @tno=tno from teacher where tname=@xm
update score
set grade=0
where grade<60 and cno in(select cno from course
where tno=@tno)
set @del_num==@@rowcount
END

3、创建存储过程，给定学号、课程号和成绩，如果在score表中有该选课记录，更新该选课的成绩，如果没有，则在score表中插入一条新记录。数据更新成功，返回1，数据更新失败，返回-1

create proc pp2
@sno char(10),@cno char(8),@grade int
as
begin
if exists(select*from score where sno=@sno and cno=@cno)
update score
set grade=@grade
where sno=@sno and cno=@cno
else
insert into score
values(@sno,@cno,@grade)
if(@@rowcount=1)
return 1
return -1
end

4、创建一个点delete触发器，删除course表中的课程信息时，同时删除score表中相应的选课信息

create trigger trtt
on courseinstead of deleteas begindeclare @cno char(8)select @cno=cno from deleteddelete from scorewhere cno=@cnodelete from coursewhere cno=@cnoend

5、创建一个update触发器，当更新course表中的课程标号cno时，修改score表中相应课程标号cno的信息

create trigger trr
on courseinstead of updateas begindeclare @cno char(8),@cname char(20),@pcno char(8),@tno char(4)select @cno=cno from deletedselect @cname=cname,@pcno=pcno,@tno=tnofrom coursewhere cno=@cnodeclare @new cno char(8)select @new cno=cno from insertedINsert into coursevalues(@new cno,@cname,@pcno,@tno)update scoreset cno=@new cnowhere cno=@cnodelete from coursewhere cno=@cnoend

四、查询、删除

1、写出Deparment增加一条记录和更新一条记录的sql语句，增加值（‘12’,’研发部’,’张三’）；
更新dept_id=’12’的记录（‘12’,’研发部’,’张丽’）

Insert deparment(dept_id,dept_name,dept_leader)
Values（‘12’,’研发部’,’张三’）
//更新记录
Update deparment set dept_leader=’张丽’ 
Where dept_id=’’12’;

2、需要给表department增加一列字段notes。长度为10的字符串，默认值为0

Alter table deparment notes var char(10) default0;

3、查找工资大于2000的员工记录，并按id升序排列

Select name
From person 
Where laryinteger>2000
Order by id;

4、查找工资大于2000的员工所在部门、部门编号、部门经理、员工名称

Select dept_name,dept_id,dept_leader
From person
Where  laryinteger>2000

5、查找张三和李四所在部门，所有人员的姓名

Select name
From person
Where name=’’张三’ and name=李四’

6、查看每个部门的部门经理和部门人数，按部门人数排列

Select  dept_leader
(selectcount(*)
From person
Where dept_id=p.dept_id)
From department,personn
Group by dept_id 
Order by co

数据分析
6、删除表所有记录

Delete from department
删除表
Drop table department

7、没有选“小易”老师课程的所有学生姓名所需要的程序语句如下：

select SNAME from S 
where SNO not in (select SNO from SC where CNO in (select CNO from C where CTEACHER = "小易“));

8、有三门（包括三门）以上课程分数>90的学生姓名及其平均成绩程序语句如下:

SELECT S.SNAME, AVG(SC.SCORE) 
FROM S,C,SC 
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO
GROUP BY S.NAME HAVING COUNT(SC.SCORE>90)>=3;