查询数据库

--第2_1题创建数据库
create database Student201038897041
on primary 
(name='student1',
filename='F:\coures\SQL Server数据库应用\student\data\student1.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%),
filegroup[group1]
(name=N'student2',
filename=N'F:\coures\SQL Server数据库应用\student\mydb\Student2.ndf',
size=20MB,
maxsize=100MB,
filegrowth=1MB
)
LOG ON 
(name=N'Studentlog1',
filename='F:\coures\SQL Server数据库应用\student\log\Studentlog1.ldf',
size=10MB,
maxsize=50MB,
filegrowth=1MB
)
alter database student201038897041
add log file(name=N'studentlog2',
filename='F:\coures\SQL Server数据库应用\student\log\Studentlog2.ldf',
size=10MB,
filegrowth=1MB,
maxsize=50MB
)

--第2_2题修改Student数据库，
--增加一个数据文件Student3，把它存放到file1
--文件组中，初始大小5MB，最大尺寸20MB，
--增长速度15%。

alter database student201038897041
add file 
(name='student3',
filename='F:\coures\SQL Server数据库应用\student\mydb\student3.ndf',
size=5MB,
maxsize=20MB,
filegrowth=15%
)to filegroup group1

--删除数据文件Student3.ndf.




--第2_3题在Student数据库中，创建“学生表”，
--包括如下字段：学号(char(6))、
--姓名(Char(8))、年龄(int not null和
--性别(Char(2)))．主键为学号，
--年龄在16到30岁之间，性别默认值为“女”．
create table studentTable 
(studentNum char(6)not null primary key,
name char(8) ,age int not null,check(age>=16 and age<=30),
sex char(2) check(sex='女'or sex='男') default('女'),
)

--第2_4题修改数据表“学生表”，  在学生表中增加宇
--段：家庭地址(varchar(30))和学生所在系(Char(20))．
alter table studentTable
add  studentAddress varchar(30),
studentCollege char(20)

----第2_5题修改数据表“学生表’，设置年龄的默认值为20
alter table studentTable
add constraint age_s default (20)for age

--第2_6题向“学生表”插入4条记录
insert into studentTable(studentNum,name,age,sex,studentAddress
,studentCollege)
values('021101','王英',20,'女','绍兴路','交通工程系')
insert into studentTable(studentNum,name,age,sex,
studentAddress,studentCollege)
values('022111','吴波',18,'男','延安路','汽车系')

insert into studentTable(studentNum,name,age,sex,
studentAddress,studentCollege)
values('034320','李霞',19,'女','南山路','管理信息系')
insert into studentTable(studentNum,name,age,sex,
studentAddress,studentCollege)
values('031202','张兵',20,'男','北山路','汽车系')

--第2_7题修改表中的数据。
--(1)在学生信息表，学生王英从交通工程系转到管理信息系，请修改此记录。

update studentTable set studentCollege='管理信息系' 
where studentNum='021101'

----(2)吴波同学的家搬到了解放路。
update studentTable set studentAddress='解放路' 
where studentNum='022111';

----(3)在学生信息表中，管理信息系的学生都毕业了，把他们的记录都删除。

delete from studentTable where
 studentCollege='管理信息系';

--第2_8．关于学生表的“姓名”字段建立唯一非聚集索引IX_XM，按姓名降序排列。
create unique  index  IX_XM on 
studentTable(name desc)

--第2_9为学生表创建一个基于年龄和
--学号的索引IX_年龄，其中年龄按降序排列，
--当年龄相同时，按学号升序排列．
create index Ix_age on studentTable
(age desc,studentNum asc)

--第2_10关于家庭地址建立非簇索引，要求填充因子为80，
--索引名为address．使用SQL命令查看索引address的空间使用情况．
create nonclustered index [address]on studentTable
(studentAddress)with fillfactor=80

--第2_11修改索引address，要求填充因子为90．
--删除索引address和“IX_年龄”。
drop index [address]on studentTable
go
drop index [IX_age]on studentTable
go
sp_helpindex studentTable

go
--第2_13将Student数据库中的“学生表”删除
drop table studentTable
go
drop database [student2010388979041]
go
--2_15设计规划创建数据库studentcourse．
--向studentcourse添加三张数据表学生基本信息表S、
--课程数据表C和学生选课数据表sc，
--数据结构如表3.6～表3.8所示：完整性约束如表3.9～表3.11所示：记录信息如表3.3～表3.5所示
create database studentcourse

go
create table s (
学号 char(6)not null primary key default('J0400'),
姓名 char(8)not null,
性别 char(2)not null,
出生日期 datetime not null default('1980-01-01'),
系 varchar(20)not null,
电话 char(8),
CONSTRAINT CK_Num CHECK (学号 Like'[A-Z][0-9][0-9][0-9][0-9]'),
CONSTRAINT CK_Sex CHECK (性别 ='女' OR 性别 ='男'),
CONSTRAINT CK_tel CHECK (电话 Like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
)

go

create table c
(课程号 char(3)not null primary key,
课程名 varchar(20) not null,
学分 smallint ,预选课程号 char(3),
教师 char(8),
CONSTRAINT CK_Cno CHECK (课程号 Like 'C[0-9][0-9]'),
)

 go
 CREATE TABLE     SC (
学号   Char (6) NOT NULL,
课程号   Char (3) NOT NULL,
成绩   Smallint  NULL,
FOREIGN KEY(课程号) REFERENCES  C (课程号),
FOREIGN KEY( 学号 ) REFERENCES  S (学号),
CHECK (成绩>=(0) AND 成绩<=(100) OR 成绩 IS NULL),
PRIMARY KEY CLUSTERED (学号  ASC,课程号 ASC)) 


 --查询李丽同学选修课程信息
 select s.姓名,s.学号,SC.课程号,c.课程名,c.学分,sc.成绩 from s,sc,c 
 where s.学号=SC.学号 and SC.课程号=C.课程号 and s.姓名='李丽'
 
 --查询陈弄清教师所讲授的课程信息
 select s.学号,s.姓名,c.课程号,c.课程名,c.教师,SC.成绩 from SC,s,c
 where s.学号=SC.学号 and SC.课程号=c.课程号 and c.教师='陈弄清'
 

题3-16取出没有选修“数据库”课程的学生姓名和年龄
select s.姓名,YEAR(GETDATE())-YEAR(s.出生日期)as 
年龄 from s where s.学号 not in (select sc.学号
from SC where sc.课程号 in(select c.课程号 from c where
c.课程名='数据库'))

题3-17没有选修李老师所讲课程的学生
select  *from s where s.学号 not in(select SC.学号 from SC where 
SC.课程号=(select c.课程号 from c where c.教师 like '李%'))

题3-18取出选修了全部课程的学生姓名和性别
select s.姓名,s.性别 from s where not exists (select * from
c where not exists (select * from SC where SC.学号=s.学号 and sc.课程号=c.课程号))

题3-19检索至少选修课程“数据结构”和“c语言”的学生学号。
select sc.学号,c.课程名 from c,SC where c.课程名='数据结构' and 
c.课程号=SC.课程号 and SC.学号 in (select SC.学号 from SC,c where
c.课程名='c语言' and c.课程号=SC.课程号)

题3-20检索学习课程号为C02的学生学号与姓名
select s.学号,s.姓名 from s where s.学号 in(select SC.学号 from SC where
SC.课程号='C02')

题3-21检索选修课程号为C01或C02的学生学号、姓名和所在系
select s.学号,s.姓名,s.系 from s where s.学号 in(select SC.学号
from SC where SC.课程号='C01' or SC.课程号='C02')

题3-22检索至少选修课程号为C01和C03的学生姓名．
select s.姓名 from s where s.学号 in(select x.学号 from SC as x,SC as y
where x.学号=y.学号 and x.课程号='c01' and y.课程号='c03')


题3-23检索每个学生的年龄
select s.学号,s.姓名,YEAR(GETDATE())-YEAR(s.出生日期)as 年龄 from s 



题3-24在学生基本信息表S中检索学生的姓名和出生年份
，输出的列名为STUDENT_ NAME和BIRTH_ YEAR
select s.姓名 as STUDENT_NAME,year(s.出生日期)as BIRTH_YEAR from s 

题3-25向学生选课数据表SC中插入一个元组(Q0404，C10，90)
insert into SC values('Q0404','C10',90)

题3-26把课程名为vb的成绩从学生选课数据表SC中删除
delete from SC where SC.课程号 in (select c.课程号 from c where
c.课程名='vb')

题3-27把女同学的成绩提高10%。
update SC set sc.成绩=SC.成绩*1.1 where SC.学号 in
(select s.学号 from s where s.性别='女')

题3-28列出选修课程超过3门的学生姓名及选修门数
select s.姓名,COUNT(*)选修门数 from s,SC where s.学号=SC.学号
group by s.学号,s.姓名 having COUNT(*)>3

题3-29求选修各课程的学生的人数．
select SC.课程号,COUNT(*)人数 from SC group by SC.课程号

题3-30在学生选课数据表SC中，求选修课程C01的学生的学号和得分，
并将结果按分数降序排序
select SC.学号,SC.成绩 from SC where SC.课程号='c01' 
order by SC.成绩 desc

题3-31查找每个同学的学号及选修课程的平均成绩情况
select SC.学号,AVG(SC.成绩)平均成绩 from SC group by SC.学号

题3-32列出学生所有可能的选课情况
select * from S,c

题3-33列出每个同学的学号、姓名及选修课程的平均
成绩情况，没有选修的同学也列出
select s.学号,s.姓名,AVG(SC.成绩)平均成绩 from s,SC where
s.学号=SC.学号 group by s.学号,s.姓名

题3-34列出每个同学的学号及选修课程号，没有选修的同学也列出
select SC.学号,SC.课程号 from SC

题3-35如果学号为J0403的学生的成绩少于90，则加上10分
update sc set sc.成绩=SC.成绩+10 where SC.学号='J0403' and SC.成绩<90

题3-36将成绩最低的学生的成绩加上10分．
update SC set sc.成绩=SC.成绩+10 where SC.成绩=(select MIN(SC.成绩)from SC)

题3-37将前3名成绩最高的学生的成绩减去10分
update SC set sc.成绩=SC.成绩-10 where SC.成绩 in(select top 3 
SC.成绩 from SC order by SC.成绩 desc)

题3-38将前10%成绩最低的学生的成绩减去5分
update sc set sc.成绩=SC.成绩-5 where SC.成绩 in(select 
top 10 percent SC.成绩 from SC order by SC.成绩 desc)

题3-39检索至少有两名男同学选修的课程名。
select SC.课程号 from SC where SC.课程号 in(select c.课程号 from c
,s where s.性别='男' group by c.课程号 having COUNT(c.课程号)>=2)


题3-40检索学生基本信息表S中不姓“王”的学生记录
select * from s where s.姓名 not like '王%'

题3-41检索和“李军”同性别并同系的学生的姓名．
select s.姓名 from s where s.性别=(select s.性别 from s where s.姓名='李丽')
and s.系=(select s.系 from s where s.姓名='李丽')

题3-42统计被学生选修的课程门数
select COUNT(distinct SC.课程号)as 课程门数 from SC


题3-43求选修C04课程的学生的平均年龄．
select AVG(YEAR(getdate())-year(s.出生日期))as 平均年龄 from s where
s.学号 in(select SC.学号 from SC where SC.课程号='C04')


题3-44求李老师所授课程的每门课程的学生平均成绩
select 课程号, avg(成绩) 平均成绩 from sc where 课程号 in (select 课程号 from 
c where 教师 like '李%') group by 课程号

题3-45统计每门课程的学生选修人数(超过3人的课程才统计)．要求输出课程号和选修人数，
查询结果按人数降序排列；若人数相同，按课程号升序排列。
select 课程号, count(*) 选修人数 from sc group by 课程号 having
 count(*)>=3 order by 课程号 asc ,count(*)desc