check.sql

sqL基本查询内容,统计选择“数据仓库”学生的人数

create database student
on primary
(
/*--数据文件的具体描述--*/ 
name='student_data', --主数据库的物理名称 
filename='E:\STUDY\大三下学期\数据库\data\student.mdf',
size=15MB,           --主数据库的初始大小             
maxsize=100MB ,      --主数据库文件增长最大文件增长 
filegrowth=15%  
)
 
Log on 
( 
/*--日志文件具体描述同上--*/ 
name='student_log', 
Filename='E:\STUDY\大三下学期\数据库\data\student.ldf', 
Size=10MB, 
MaxSize=20MB, 
FIleGrowth=2MB
)
go
 
ALTER DATABASE  student
add file
(
name='student_data2', --主数据库的物理名称 
filename='E:\STUDY\大三下学期\数据库\data\student.ndf',
size=15MB,           --主数据库的初始大小             
maxsize=100MB ,      --主数据库文件增长最大文件增长 
filegrowth=15% 
)

ALTER DATABASE  student
MODIFY FILE
(
NAME=student_data,
SIZE=20MB
)

use student
create table stu
(
sno char(9) PRIMARY KEY,
sname char(20) Unique,
ssex char (2) check (ssex in ('男','女')),
sbirth varchar(20),
sclass char(10),
smonitor char(20),
Foreign key (smonitor) references stu(sname)
)

use student
create table professor
(
pno char(9) primary key,
pname char(20) , 
psex char (2),
page int check (page between 20 and 65),
ptitle char(10) check (ptitle in( '讲师','副教授','教授')),
)

use student
create table subject
(
subno char(9) primary key,
subname char(20) , 
subpro char (9),
subassignment  char(20),
begindate datetime,
enddate datetime,
foreign key (subpro) references professor(pno)
) 


use student
create table sc
(
sno char(9),
subno char(9),
grade smallint,
primary key (sno,subno),
foreign key(sno) references stu(sno),
foreign key (subno) references subject(subno)
)

use student
alter table sc
add check (grade between 0 and 100)

use student
alter table stu
add check(2009-sbirth between 0 and 45)

create trigger subjectlimit on sc
after insert
as 
declare @subnum int
select @subnum=count(*)
from sc
where sno=(select sno from inserted)
if @subnum>3
begin
raiserror('已经选择了个课题',16,1)
rollback transaction
end

--增加学生的一个字段：身份证号，类型为char
use student
alter table stu
add identification char(25)

--修改开始日期和结束日期的类型为smalldatetime
use student
alter table subject
alter column begindate smalldatetime

alter table subject
alter column enddate smalldatetime

use student
alter table subject
add check (begindate<enddate)

-- 删除身份证号字段
alter table stu
drop column identification 

--查看最后的表结构
sp_MShelpcolumns 'stu'

--插入导师、课题信息
INSERT
into professor(pno,pname,psex,page,ptitle)
values(1,' 张红','女',null,null)
insert
into professor(pno,pname,psex,page,ptitle)
values(2,'江进','男',null,null)
insert 
into professor(pno,pname,psex,page,ptitle)
values(3,' 黎明','男',null,null)
insert
into professor(pno,pname,psex,page,ptitle)
values(4,' 赵刚 ','男',null,null)
insert
into professor(pno,pname,psex,page,ptitle)
values(5,' 李建设','男',null,null)

insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate) 
values('1','VC开发','1',null,null,null)

insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('2','VB 课件管理','2',null,null,null)

insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('3','人事管理','3',null,null,null)
insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('4','学生管理','4',null,null,null)
insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('5','档案管理','5',null,null,null)
insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('6','图书管理','3',null,null,null)

insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('7','财务管理','4',null,null,null)

insert
into subject(subno,subname,subpro,subassignment,begindate ,enddate)
values('8','JAVA开发','4',null,null,null)

--3、输入若干学生的信息，信息内容不限
--修改开始和结束日期                    
update subject
set begindate='2005-8-9',enddate='2005-12-9'
where subno='1'

update subject
set begindate='2005-9-1',enddate='2006-12-1'
where subno='2'

update subject
set begindate='2005-8-10', enddate='2005-12-8'
 where subno='3'

update subject
set begindate='2005-8-16', enddate='2005-12-6'
 where subno='4'

update subject
set begindate='2005-8-10', enddate='2005-12-5'
 where subno='5'

update subject
set begindate='2005-8-21', enddate='2005-12-13'
 where subno='6'

update subject
set begindate='2005-8-18', enddate='2005-12-8'
where subno='7'

update subject
set begindate='2005-7-1', enddate='2005-12-10'
where subno='8'

  

--输入若干学生的信息，信息内容不限
insert
into stu(Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('001','stu_1','女',null,'微机',null)

insert
into stu (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('002','stu_2','男',null,'微机',null)

insert
into stu  (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('003','stu_3','男',null,'信管',null)

insert
into stu (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('004','stu_4','女',null,'电商',null)

insert
into stu (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('005','stu_5','女',null,'微机',null)

insert
into stu  (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('006','stu_6','男',null,'电商',null)

insert
into stu (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('007','stu_7','女',null,'软件',null)

insert
into stu  (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('008','stu_8','女',null,'微机',null)

insert
into stu  (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('009','stu_9','男',null,'软件',null)

insert
into stu (Sno,Sname,Ssex,Sbirth,Sclass,Smonitor)
values('010','stu_10','女',null,'信管',null)
 



--输入若干学生的成绩
 
insert
into sc (sno,subno,grade)
values('001','1',68)
insert
into sc (Sno,subno,grade)
values('002','1',85)
insert
into sc (Sno,subno,grade)
values('003','6',61)
insert
into sc (Sno,subno,grade)
values('004','8',72)
insert
into sc (Sno,subno,grade)
values('005','6',99)
insert
into sc (Sno,subno,grade)
values('006','4',92)
insert
into sc (Sno,subno,grade)
values('007','7',82)
insert
into sc (Sno,subno,grade)
values('008','1',88)
insert
into sc (Sno,subno,grade)
values('008','2',74)
insert
into sc (sno,subno,grade)
values('005','7',94)
insert
into sc (Sno,subno,grade)
values('003','4',90)
insert
into sc (Sno,subno,grade)
values('004','5',81)
insert
into sc (Sno,subno,grade)
values('003','2',82)
insert
into sc (Sno,subno,grade)
values('002','5',77)

--统计查询每个课题的选课人数，输出课题名称、人数

select subname,count(sno) 选课人数
from sc,subject
where sc.subno=subject.subno
group by subname 

--统计查询每个导师的选课人数，输出教师名，人数
select pname,count(sno) 选课人数
from professor,sc,subject
where professor.pno=subject.subpro and subject.subno=sc.subno
group by pname

--统计查询不同班级的上述选课人数情况
select pname,sclass,count(sc.sno) 选课人数
from professor,sc,subject,stu
where professor.pno=subject.subpro and subject.subno=sc.subno and stu.sno=sc.sno
group by pname,sclass


update professor
set ptitle='副教授', page=45
where pno='1'



update professor
set ptitle='教授',page=34
where pno='2'


update professor
set ptitle='教授',page=49
where pno='3'


update professor
set ptitle='副教授',page=32
where pno='4'


update professor
set ptitle='副教授',page=40
where pno='5'


--  (1)查询全部学生的信息
select *
from stu

-- (2)查询微机班男性同学的信息
select *
from stu
where ssex='男' and sclass='微机'

-- (3)查询女性副教授教师信息，以年龄排序
select *
from professor
where psex='女' and ptitle='副教授'
order by page desc


--(4)查询年龄在35岁以上的教师信息

select *
from professor
where page>35

--(5)查询李建设所开设课题的信息
select pno,pname,subno,subname,begindate,enddate
from professor,subject
where professor.pno=subject.subpro and professor.pname=' 李建设'

--(6)查询学生选课的门数以及平均分数
select sname ,count(sc.subno) 选课门数,avg(grade)平均分数
from sc,stu
where sc.sno=stu.sno
group by sname


--(7')查询每个学生的均分，最高分，最低分，总分，要求显示学生的姓名以及对应的分数信息
select sname,avg(grade)平均分数,max(grade) 最高分,min(grade)最低分,sum(grade)总分
from sc,stu
where sc.sno=stu.sno
group by sname

--(8)分班级统计至少选了3门课程的学生
select sname,sclass
from sc,stu
where sc.sno=stu.sno
group by sclass,sname
having count(subno)>=3

--(9)统计选了教师'黎明'课题的学生人数
select professor.pname, count(sc.sno) 学生人数
from subject,professor,sc
where subject.subpro=professor.pno and subject.subno=sc.subno and pname=' 黎明'
group by professor.pname


--(10)查询课题设计时间大于2个月的课题信息
select *
from subject
where datediff(month,begindate,enddate)>2