数据库11.26实验

数据库实验

实验十一：T-SQL(9)
1  实验目的
    1．  熟悉数据库的交互式SQL工具。
    2．  熟悉通过SQL对数据库进行操作。
    3．  完成作业的上机练习。
2  实验平台
利用SQLServer及其交互式工具SSMS来熟悉T-SQL。
3  实验内容及要求
     基于student、course、sc表，用SQL语句实现如下要求，填写实验报告，记录所有的实验用例。
1.  列出每个系的男生人数、女生人数
2.  查询选修了课程名为“信息系统”的学生学号和姓名
3.  删除“王兰”所选的全部课程；
4.  将“计算机”系的学生成绩全部清零；
5.  查询没有选修1号课程的学生姓名。
6.  查询选修了课程1或者选修了课程2的学生姓名。
7.  查询既选修了课程1又选修了课程2    的学生姓名
8.  查询选修了课程1但没有又选修了课程2的学生姓名

0 建表代码

create database Students
on
primary(name=Students,
   filename='D:\test\test.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
  )
log on
(name=Studentslog,
filename='D:\test\testlog.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use Students;
create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno char(6),
Ccredit int null,
primary key(cno),
)
go 
create table SC
(
Sno char(8) not null foreign key references Student(sno),
Cno char(6) not null foreign key references course(cno),
primary key(Sno,Cno),
Grade int  null
)
insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')
insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)
insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)
alter table course add  foreign key(cpno) references course(cno)
select * from Course;
select * from SC;
select * from Student;

1. 列出每个系的男生人数、女生人数

select Sdept,ssex,count(ssex)as '人数' from Student group by Sdept,ssex

2. 查询选修了课程名为“信息系统”的学生学号和姓名

select b.cname,a.sname,a.sno
from Student a,course b,sc c
where a.sno=c.sno
and b.cno=c.cno
and b.cname='数据库'

3. 删除“王兰”所选的全部课程；

delete from sc
where sno in
(select sno from Student where sname='丁鹏')

4. 将“计算机”系的学生成绩全部清零；

update sc 
set grade = null
where sno in
(select sno from student where sdept ='software')

5. 查询没有选修1号课程的学生姓名。

select sname  from Student 
where sno not in
(select sno from sc where cno =1)

6. 查询选修了课程1或者选修了课程2的学生姓名。

select sname from Student
where sno in
(select sno from sc where cno ='1')
union
select sname from Student
where sno in
(select sno from sc where cno ='2')

7. 查询既选修了课程1又选修了课程2的学生姓名

select sname from Student
where sno in
(select sno from sc where cno ='1')
intersect
select sname from Student
where sno in
(select sno from sc where cno ='2')

8. 查询选修了课程1但没有又选修了课程2的学生姓名

select sname from Student
where sno in
(select sno from sc where cno ='1')
except
select sname from Student
where sno in
(select sno from sc where cno ='2')