–1
CREATE DATABASE _2018522018;
use _2018522018;
year(getdate())-year(Sbirth)
–2
create table Student
(
Sno char(30) primary key,
Sname char(30) unique,
Ssex char(2) ,
Classno char(30),
Sbirth datetime,
Status char(4),
Hometown char(30),
Enrollment datetime,
Telephone char(30),
Resume char(80)
);
create table SClass
(
ClassNo char(30) primary key,
ClassName char(15),
Major char(10),
Grade char(8),
Number int,
Advisor char(8)
);
create table Course
(
CourseNo char(30) primary key,
CourseName char(30),
Category char(30),
Major char(30),
Grade char(30),
StartTM char(30),
Credits int ,
WeekHours int ,
);
create table Schedule
(
CourseNo char(30) ,
StartYear char(30),
ClassNo char(30),
Teacher char(30),
);
create table SC
(
CourseNo char(30),
Sno char(30),
Score int
);
–3
insert into Student values(‘201815126’,‘张三’,‘男’,‘2018123’,‘1997-05-05’,‘团员’,‘北京’,‘2018-09-01’,‘13812345678’,’’);
–4
insert into Course values(‘1’,‘数据库技术’,‘指选’,‘通信工程’,’’,‘第2学期’,2,2);
–5
insert into SC values(‘1’,‘201815126’,0);
–6
update Course set StartTM=‘每年第二学期’ where CourseNo=‘1’;
–7
update SC set Score=90 where CourseNo=‘1’ and Sno=‘201815126’;
–8
delete from SC where CourseNo=‘1’ and Sno=‘201815126’;
–9
truncate table SC;
–10
select Sno,Sname,Classno into stu from Student;
–11
ALTER TABLE SC
add check(Score >=0 and Score <=100);
ALTER TABLE Student
add check(Ssex in (‘男’,‘女’));
ALTER TABLE Course
add check(Category in (‘必修’,‘指选’,‘任选’));
insert into Student values(‘2018127155’,‘小白’,‘男’,‘201812’,‘1999-08-24’,‘团员’,‘广东’,‘2018-09-01’,‘15363380342’,’’);
insert into Course values(‘1’,‘C语言’,‘必修’,‘通信工程’,’’,‘第2学期’,4,3);
insert into Course values(‘2’,‘java’,‘必修’,‘通信工程’,’’,‘第2学期’,4,3);
insert into SC values(‘2’,‘2018127155’,0);
–12
select Sname,Ssex,Hometown,Sbirth from Student;
–13
select Sname,Ssex,year(getdate())-year(Sbirth) age from Student;
–14
select Sname,year(getdate())-year(Sbirth)
17
select year(getdate())-year(Sbirth) 年龄,sname from student
where ssex=‘男’ and year(getdate())-year(Sbirth)>(
select avg(year(getdate())-year(Sbirth)) from student
where ssex=‘女’)
18
select count(courseno) from sc,student where sc.sno=student.sno
group by sc.sno;（错的）
19
select sname,count(courseno) from sc,student where sc.sno=student.sno group by sname having avg(score)>60
20
select sno,ssex from student where hometown=(select hometown from
student where sname=‘王小明’);
22
select status,classname from student,sclass where sclass.classno=student.classno
and sname=‘王小明’
24
select * into stuShandong from student where sname=‘王小明’
25
update sc set sc.score=sc.score+1 where
sc.sno in (select sno from student where
classno=‘2017211113’)
26
delete from student where sname=‘王小明’
27
alter table student add constraint S_SName_Edition unique(sname)
28
EXEC sp_rename ‘student.S_SName_Edition’, ‘S_Sname_new’, ‘index’
29
create CLUSTERED index sc_sno_cno on sc (sno,courseno)
30
drop index sc_sno_cno on sc
31
create view tongxinstudent
as
select s.*
from course c,student s,sc
where c.courseno=sc.courseno
and s.sno=sc.sno and c.Major=‘通信工程’
32
create view xuehaoxingmnng
as
select s.sno,s.sname,c.CourseName,score
from course c,student s,sc
where c.courseno=sc.courseno
and s.sno=sc.sno
–33
create view kechenghao_name_class as
select course.CourseNo,course.CourseName,classname,total from Course ,SClass ,
(select course.CourseNo,Student.classNo,count(*) total from Course ,Student,SC
where course.CourseNo=sc.CourseNo and Student.sno=sc.sno group by course.courseNo,student.Classno)t
where course.CourseNo=t.CourseNo and student.ClassNo=t.ClassNo
–34
alter view tongxinstudent
as
select student.*
from course ,student ,sc
where course.courseno=sc.courseno
and student.sno=sc.sno and course.Major=‘通信工程’
with check option;
–35
alter view xuehaoxingmnng with encryption
as
select student.sno,student.sname,course.CourseName,score
from course,student,sc
where course.courseno=sc.courseno
and student.sno=sc.sno
–36
select sname,ssex from tongxinstudent where Year(Sbirth)>2000
–37
insert into xuehaoxingmnng values(‘111’,‘222’,‘333’,50)
–38
drop view kechenghao_name_class
–1
CREATE DATABASE _2018522018;
use _2018522018;
–2
create table Student
(
Sno char(20) primary key,
Sname char(10) unique,
Ssex char(2) ,
Classno char(20),
Sbirth datetime,
Status char(4),
Hometown char(20),
Enrollment datetime,
Telephone char(11),
Resume char(80)
);
create table SClass
(
ClassNo char(20) primary key,
ClassName char(15),
Major char(10),
Grade char(8),
Number int,
Advisor char(8)
);
create table Course
(
CourseNo char(20) primary key,
CourseName char(15),
Category char(10),
Major char(10),
Grade char(8),
StartTM char(30),
Credits int ,
WeekHours int ,
);
create table Schedule
(
CourseNo char(20) ,
StartYear char(10),
ClassNo char(20),
Teacher char(20),
);
create table SC
(
CourseNo char(20),
Sno char(20),
Score int
);
–3
insert into Student values(‘201815126’,‘张三’,‘男’,‘2018123’,‘1997-05-05’,‘团员’,‘北京’,‘2018-09-01’,‘13812345678’,’’);
–4
insert into Course values(‘1’,‘数据库技术’,‘指选’,‘通信工程’,’’,‘第2学期’,2,2);
–5
insert into SC values(‘1’,‘201815126’,0);
–6
update Course set StartTM=‘每年第二学期’ where CourseNo=‘1’;
–7
update SC set Score=90 where CourseNo=‘1’ and Sno=‘201815126’;
–8
delete from SC where CourseNo=‘1’ and Sno=‘201815126’;
–9
truncate table SC;
–10
select Sno,Sname,Classno into stu from Student;
–11
ALTER TABLE SC
add check(Score >=0 and Score <=100);
ALTER TABLE Student
add check(Ssex in (‘男’,‘女’));
ALTER TABLE Course
add check(Category in (‘必修’,‘指选’,‘任选’));
insert into Student values(‘201815130’,‘王一’,‘男’,‘2018123’,‘1997-05-05’,‘团员’,‘北京’,‘2018-09-01’,‘13812345678’,’’);
insert into Course values(‘22’,‘C1’,‘指选’,‘通信工程’,’’,‘第2学期’,2,2);
insert into Course values(‘33’,‘C2’,‘指选’,‘通信工程’,’’,‘第2学期’,2,2);
insert into SC values(‘22’,‘201815128’,NULL);
–12
select Sname,Ssex,Hometown,Sbirth from Student;
–13
select Sname,Ssex,year(getdate())-year(Sbirth) age from Student;
–14
select Sname,year(getdate())-year(Sbirth) age from Student where Sname like ‘王%’;
–15
select s.Sname,c.CourseName,SC.Score where s.cOU
–17
select year(getdate())-year(Sbirth) 年龄,sname from student
where ssex=‘男’ and year(getdate())-year(Sbirth)>(
select avg(year(getdate())-year(Sbirth)) from student
where ssex=‘女’)
–19
select sname,count(courseno) from sc,student where sc.sno=student.sno group by sname having avg(score)>60
–20
select sno,ssex from student where hometown=(select hometown from
student where sname=‘王小明’);
–22
select status,classname from student,sclass where sclass.classno=student.classno
and sname=‘王小明’
–24
select * into stuShandong from student where sname=‘王小明’
–25
update sc set sc.score=sc.score+1 where
sc.sno in (select sno from student where
classno=‘2017211113’)
–26
delete from student where sname=‘王小明’
–27
alter table student add constraint S_SName_aa unique(sname)
–28
EXEC sp_rename ‘student.S_SName_aa’, ‘S_Sname_bb’, ‘index’
–29
create CLUSTERED index sc_no on sc (sno,courseno)
–30
drop index sc_no on sc
–31
create view ccc
as
select s.*
from course c,student s,sc
where c.courseno=sc.courseno
and s.sno=sc.sno and c.Major=‘通信工程’
–32
create view dddd
as
select s.sno,s.sname,c.CourseName,score
from course c,student s,sc
where c.courseno=sc.courseno
and s.sno=sc.sno
–33
create view eeee as
select c.CourseNo,c.CourseName,classname,total from Course c,SClass s,
(select c.CourseNo,Student.classNo,count(*) total from Course c,Student,SC
where c.CourseNo=sc.CourseNo and Student.sno=sc.sno group by c.courseNo,student.Classno)t
where c.CourseNo=t.CourseNo and s.ClassNo=t.ClassNo
–34
alter view ccc
as
select s.*
from course c,student s,sc
where c.courseno=sc.courseno
and s.sno=sc.sno and c.Major=‘通信工程’
with check option;
–35
alter view dddd with encryption
as
select s.sno,s.sname,c.CourseName,score
from course c,student s,sc
where c.courseno=sc.courseno
and s.sno=sc.sno
–36
select sname,ssex from ccc where Year(Sbirth)>2000
–37
insert into dddd values(‘111’,‘222’,‘333’,50)
–38
drop view eeee