数据库实验四

--一、对xsgl数据库完成下列操作要求：

--1.将被全部学生都选修了的课程的总学分改为4学分。
update kc
set kc.学分=4
where kc.课程号 in (
	select cj.课程号
	from cj
	group by cj.课程号
	having count(cj.课程号)=(
		select count(xs.学号)
		from xs
	)
)

update kc
set kc.学分=4
where not exists
	(select *
	from xs
	where not exists(
		select *
		from cj
		where cj.课程号=kc.课程号 and cj.学号=xs.学号
	)
)

 
--2.从学生表删除没有选课的学生。
delete
from xs
where xs.学号 in (
	select xs1.学号
	from xs xs1
	left join cj
	on cj.学号=xs1.学号
	group by xs1.学号
	having count(cj.课程号)=0
)


--3.将每个学生的平均分，总分和选课门数插入到数据库中（学号，姓名，平均分，总分，选课门数）
create table score
	(学号 char(10),姓名 nchar(10),平均分 numeric(18,0),总分 numeric(18,0),选课门数 int)

insert
into score
select xs.学号,xs.姓名,avg(cj.成绩),sum(cj.成绩),count(cj.课程号)
from xs,cj
where xs.学号=cj.学号
group by xs.学号,xs.姓名

--4.创建每门课程的平均分和选课人数的视图（课程号，课程名，平均分，人数）
create view agv_course_score(课程号,课程名,平均分,人数)
as
select cj.课程号,课程名,avg(cj.成绩) 平均分,count(cj.学号) 人数
from cj,kc
where cj.课程号=kc.课程号
group by cj.课程号,课程名


--5.将李强同学从学生表删除（提示应该先删除李强同学的选课记录）
delete
from cj
where cj.学号 in (
	select cj.学号
	from cj,xs
	where cj.学号=xs.学号 and xs.姓名='李强'
)

delete
from xs
where xs.姓名='李强'



--6.插入一条选课记录（具体内容自己选）
insert
into cj
values('2006030307','A003',99)


--7.创建网络工程专业的学生的选课信息的视图，要求视图包含，学号，姓名，专业，课程号，课程名，成绩
create view network_cj (学号,姓名,专业,课程号,课程名,成绩)
as
select xs.学号,xs.姓名,xs.专业,kc.课程号,kc.课程名,cj.成绩
from xs,cj,kc
where xs.学号=cj.学号 and kc.课程号=cj.课程号 and xs.专业='网络工程'

--8.查询网络工程专业的各科的平均成绩，要求使用第7题创建的视图进行查询
select network_cj.课程号,avg(network_cj.成绩) 平均成绩
from network_cj
group by network_cj.课程号

--9.查询被信息管理专业的学生都选修了的课程的课程号，课程名
select kc.课程号,kc.课程名
from kc
where not exists(
	select *
	from xs
	where xs.专业='信息管理' and not exists(
	select *
	from cj
	where cj.学号=xs.学号 and kc.课程号=cj.课程号
	)
) 


--10.显示选修课程数最多的学号及选修课程数最少的学号，姓名（使用派生表实现）
select xs.姓名,xs.学号,count(*) 门数
from xs,cj,(select top 1 count(*)
			from cj
			group by cj.学号
			order by count(*) desc
			) ma(门数),(select top 1 count(*)
			from cj
			group by cj.学号
			order by count(*) asc 
			) mi(门数)
where xs.学号=cj.学号
group by  xs.姓名,xs.学号,ma.门数,mi.门数
having count(cj.课程号) in (ma.门数,mi.门数) 

--11.查询每个学生成绩高于自己的平均成绩的学号，姓名，课程号和成绩（使用派生表实现）
select xs.学号,xs.姓名,cj.课程号,cj.成绩
from xs,cj,(select 学号,avg(cj.成绩)
			from cj
			group by cj.学号
			) avg_score(学号,成绩)
where xs.学号=avg_score.学号 and xs.学号=cj.学号 and cj.成绩>avg_score.成绩

--12.自己验证with check option的作用。
create view network (学号,姓名,专业)
as
select xs.学号,xs.姓名,xs.专业
from xs
where xs.专业='网络工程'
with check option

insert into network_cj
values('2000','张三','计算机')


--13.创建一个网络工程系的学生基本信息的视图MA_STUDENT，在此视图的基础上，再定义一个该专业女生信息的视图，然后再删除MA_STUDENT，观察执行情况。
create view MA_STUDENT(学号,姓名,性别,专业)
as
select xs.学号,xs.姓名,xs.性别,xs.专业
from xs
where xs.专业='网络工程'

create view MA_female_STUDENT(学号,姓名,性别,专业)
as
select 学号,姓名,性别,专业
from  MA_STUDENT
where 性别='女'
 
drop view MA_STUDENT

--二、使用Northwind数据库完成下列操作

--1. 将员工lastname是: Peacock处理的订单中购买数量超过50的商品折扣改为七折
update [Order Details]
set Discount=0.3
where OrderID in (
	select [Order Details].OrderID
	from Employees,[Order Details],Orders
	where Orders.EmployeeID=Employees.EmployeeID and Employees.LastName='Peacock' and [Order Details].OrderID=Orders.OrderID
	group by [Order Details].OrderID
	having sum([Order Details].Quantity)>50
)

--2. 删除lastname是: Peacock处理的所有订单
delete 
from [Order Details]
where [Order Details].OrderID in (
	select OrderID
	from Employees,Orders
	where Employees.LastName='Peacock' and Employees.EmployeeID=Orders.EmployeeID
)

delete
from Orders
where Orders.EmployeeID in(
	select EmployeeID
	from Employees
	where Employees.LastName='Peacock'
)

--3. 将每个订单的订单编号，顾客编号，产品总数量，总金额插入到数据库中
create table newtable(订单编号 char(10),顾客编号 char(10),产品总数量 int,总金额 int)

insert
into newtable
select Orders.OrderID,Orders.CustomerID,sum(Quantity),sum(UnitPrice*Quantity*(1-discount))
from [Order Details],Orders
where [Order Details].OrderID=Orders.OrderID
group by Orders.OrderID,Orders.CustomerID


--4. 插入一个新的订单，要求该订单购买了商品编号为5,7,9的商品。（5号商品买了10个，7号买了20个，9号买了15个，都没有折扣）
insert
into Orders(CustomerID)
values('HANAR')

declare @orderid int
select @orderid=Orders.OrderID
from Orders
where Orders.CustomerID='HANAR'

declare @price5 int
select @price5=Products.UnitPrice
from Products
where ProductID=5

declare @price7 int
select @price7=Products.UnitPrice
from Products
where ProductID=7

declare @price9 int
select @price9=Products.UnitPrice
from Products
where ProductID=9

insert
into [Order Details]
values(@orderid,5,@price5,10,0)


insert
into [Order Details]
values(@orderid,7,@price7,20,0)

insert
into [Order Details]
values(@orderid,9,@price9,15,0)

--5. 将每年每个员工处理订单的数量和订单的总金额创建为视图
create view v1(EmployeeID,OrdersNum,Money)
as
select Orders.EmployeeID,sum(Orders.OrderID),sum(UnitPrice*Quantity*(1-discount))
from Orders,[Order Details]
where Orders.OrderID=[Order Details].OrderID
group by Orders.EmployeeID