实验2

--(一)以student数据库中数据为基础，请使用T-SQL 语句实现以下操作：
说明:该大题中特别需要注意的是2、5、15、16、19、21题
--1.列出不姓刘的所有学生；
select *
from student
where Sname not like '刘%'
或
select *
from student
where Sname  like '[^刘]%'
--2.列出年龄超过平均值的所有学生名单，按年龄降序显示
select *
from student
where sage>=(select avg(sage) from student)
order by sage desc

--3.显示在1985年以后出生的学生的基本信息；
select *
from student
where( year(getdate())-Sage)>1985
--4.查询出课程名含有“数据”字串的所有课程基本信息；
select *
from course
where Cname like'%数据%'
--5.显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
select sno,sname,ssex,sage,sdept
from student
where SUBSTRING(sno,8,1) like '[12349]' or SUBSTRING(sno,9,1) like '[12349]'
或
select Sno,Sname,Ssex,Sage,Sdept
from student
where Sno like'_______[12349]%'or Sno like'________[12349]%'
--6.列出选修了‘1’课程的学生，按成绩的降序排列；
select Sno,Grade
from sc
where Cno='1'
order by Grade desc
--7.查询选修课程超过3门的学生的学号和选课门数，以及平均成绩
select Sno,COUNT(Cno)AS 选课门数,AVG(Grade)平均成绩
from sc
group by Sno
having COUNT(Cno)>3
--8.列出课程表中全部信息，按先修课的升序排列；
select *
from course
order by Cpno
--9.查询平均成绩超过80分的课程的课程号和平均成绩；
select Cno,AVG(Grade)平均成绩
from sc
group by Cno
having AVG(Grade)>80
--10.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
select Sno as 学号,Sname as 姓名,Ssex as 性别,year(getdate())-Sage as 出生年份,Sdept as 院系
from student
order by 出生年份
--11.按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
select Sno,Cno,Grade
from sc
where Grade between 70 and 80
order by Cno desc,Grade desc 
--12.显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
select count (*)学生总人数,avg(Sage)平均年龄
from student
--13.查询每个专业所有姓张的人数；
select Sdept,count(*)人数
from Student
where Sname like'张%'
group by Sdept
--14.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
select Cno, count(*)总人数,MAX(Grade)最高成绩,min(grade)最低成绩,Avg(Grade)平均成绩
from sc
group by Cno
order by Cno desc
--15.	显示选修课程数最多的学号；
select top 1 with ties Sno,count(*)选修课程数 
from sc
group by Sno
order by 选修课程数 desc
--16.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
select sno,avg(grade) 平均成绩
from sc
where sno  in (select sno from sc where grade<60 group by sno having count(*)>=2)
group by sno
或
select sno,avg(grade) 平均成绩
from sc
group by sno
having count(case when grade <60 then 1 end)>=2
--17. 查询选修‘1’号课程成绩排名前五的学生的学号和成绩
select top 5 with ties Sno,grade
from sc
where Cno='1'
order by grade desc
--18.查询每个学生的最低分和选课门数
select Sno,MIN(grade)最低分,count(cno)选课门数
from sc
group by Sno
--19. 查询各个专业男生、女生的人数
select sdept,Ssex,count(Ssex)as 人数
from student
group by Ssex,Sdept
或
select sdept ,count(case when ssex='男' then 1 end) 男生人数, count(case when ssex='女' then 1 end ) 女生人数
from student
group by sdept
--20. 查询各种姓氏的人数（假设没有复姓）；
select left(Sname,1)姓氏,count(*)人数
from student
group by left(Sname,1)
补充：
--21查询选修课程数最多的学号及选修课程数最少的学号
select sno, count (*) 选课门数
from sc
group by sno
having  count(*)>=all(select count(*) from sc group by sno) or count(*)<=all(select count(*) from sc group by sno)
--（二）以SPJ数据库中数据为基础，请使用T-SQL 语句实现以下操作： 
说明:该大题中特别需要注意的是2、5、6题
--1．	求供应工程J1零件的供应商号码SNO
select distinct SNO
from SPJ
where JNO='j1'
--2．求每个工程使用不同供应商的零件的个数
select JNO,SNO,sum(QTY)零件个数
from spj
group by JNO,sno

--3．求供应工程使用零件P3数量超过200的工程号JNO
select JNO
from SPJ
where PNO='P3'and QTY>=200
--4．	求颜色为红色和蓝色的零件的零件号和名称
select  PNO,PNAME 
from P
where COLOR='红'or COLOR='蓝'
--5．求使用零件数量在200-400之间的工程号
select JNO
from SPJ
group by JNO
having sum(QTY)between 200 and 400
--6．	查询每种零件的零件号，以及使用该零件的工程数。
select  p.PNO,count(distinct JNO) 工程数
from p left join spj on spj.PNO=p.PNO
group by p.PNO
或
select  PNO,count(distinct JNO) 工程数
from spj
group by PNO

实验3

--（一）以数据库系统实验1中student数据库为基础，请使用T-SQL 语句实现进行以下操作：
--1.	查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；
select *
from course
where Cname like 'DB\_%s__' escape '\'' ;

--2.	查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
select Sname 学生姓名,sc.Sno 学号,sc.Cno 课程号,Cname 课程名
from student S,sc,course C
where S.Sno=sc.Sno and sc.Cno=C.Cno and  Sname like '_阳%';
或
select student.Sno,Sname,sc.Cno,Cname
from student,sc,course
where student.Sno=sc.Sno and sc.Cno=course.Cno and substring(student.Sname,2,2)='阳';
--3.	列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；
select S.Sno,Sname,Sdept,Cno,Grade
from student S,sc
where S.Sno=sc.Sno and 
Cno in( select Cno 
        from course
        where Cname='数学' or Cname='大学英语');
或
select S.Sno,Sname,Sdept,sc.Cno,Grade
from student S,sc,course C
where S.Sno=sc.Sno and C.cno=sc.cno and (Cname='数学' or Cname='大学英语')/*此处括号不可少*/
    
--4.	查询缺少成绩的所有学生的详细情况；
select *
from student
where Sno in(
    select distinct Sno 
    from sc
    where Grade is null
    );
	或
select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno=sc.sno and Grade is null
--5.	查询与‘张毅’(假设姓名唯一)年龄不同的所有学生的信息；
select *
from student 
where Sage not in(
    select Sage
    from student 
    where Sname='张毅'
    );

--6.	查询所选课程的平均成绩大于张毅的平均成绩的学生学号、姓名及平均成绩；
select student.Sno,Sname,avg(Grade) 平均成绩
from student ,sc
where student.sno=sc.sno
group by student.Sno,Sname
having avg(Grade)>(
    select avg(Grade)
    from sc inner join student on sc.Sno=student.Sno
    where Sname='张毅'
    );

--7.	按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；
select student.Sno,Sname,Sdept,sum(credit) 已修学分
from student  inner join sc  on student.sno=sc.sno inner join course   on sc.cno=course.cno
where grade>=60
group by student.sno,sname,sdept;
或
select student.sno,sname,sdept,sum(credit)已修学分
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and sc.grade>60
group by student.Sno,Sname,Sdept;
--8.	列出只选修一门课程的学生的学号、姓名、院系及成绩；
select student.Sno,Sname,Sdept,Grade
from student  inner join sc  on student.Sno=sc.Sno 
where exists(
    select *
    from sc x
    where x.Sno=sc.Sno 
    group by Sno
    having count(*)=1);
	或
select student.Sno,Sname,Sdept,Grade
from student,sc
where student.Sno=sc.Sno and student.Sno in(select Sno from sc group by Sno having count(Cno)=1)
--9.	查找选修了至少一门和张毅选修课程一样的学生的学号、姓名及课程号；
select SC.Sno,Sname,Cno
from student as S inner join sc as SC on S.Sno=SC.Sno
where Cno in (
    select Cno
    from sc
    where Sno=(
        select Sno
        from student 
        where sname='张毅'
    )
);
或
select student.Sno,student.Sname,c1.Cno
from student,sc c1,sc c2
where student.Sno=c1.Sno and c1.Sno=c2.Sno and c2.Cno in (
select  sc.Cno
from sc,student 
where sc.Sno=student.Sno and student.Sname='张毅') and student.Sname != '张毅'
--10.	查询至少选修“数据库”和“数据结构”课程的学生的基本信息；
select *
from student
where not exists (select *
	              from course 
			      where cname in ('数据库','数据结构')
				   and not exists(select *
								   from sc
								   where student.sno = sc.sno and sc.cno =course.cno))

--11.	查询没有选修张毅所选修的全部课程的学生的姓名；
--（就是查找一个学生，存在张毅选修的一个课程，他没有选修）
select Sname
from student S1
where exists(
    select * 
    from sc SC1
    where Sno=(
        select Sno
        from student
        where Sname='张毅'
    ) and not exists (
        select *
        from sc SC2
        where S1.Sno=SC2.Sno and SC1.Cno=SC2.Cno
    )
);

--12.	查询每个专业年龄超过该专业平均年龄的学生的姓名和专业；
select Sname,Sdept
from student S1
where Sage>(
    select avg(Sage)
    from student S2
    where S1.Sdept=S2.Sdept
);

--13.	查询选修了张毅同学所选修的全部课程的学生的姓名；
--(就是查找一个学生，不存在张毅选修的一个课程，他没有选修)
select Sname
from student as S
where not exists(
    select *
    from sc as SC1
    where Sno=(
        select Sno
        from student 
        where Sname='张毅'
    ) and not exists(
        select *
        from sc as SC2
        where SC2.Cno=SC1.Cno and SC2.Sno=S.Sno
    )
);

--14.	检索选修了全部课程的学生姓名；
--(就是查找一个学生，不存在一个课程，他没有选修)
select Sname
from student S
where not exists(
    select *
    from course C
    where not exists(
        select *
        from sc SC
        where SC.Sno=S.Sno and SC.Cno=C.Cno
    )
);

--15.	列出同时选修“1”号课程和“2”号课程的所有学生的姓名；（使用三种方法实现）
--(1)exists
select Sname
from student S
where exists(
    select *
    from sc
    where S.Sno=sc.Sno and Cno='1'
) and exists(
    select *
    from sc
    where S.Sno=sc.Sno and Cno='2'
);
--(2)求交集
select Sname
from student
where Sno in
    ((select Sno
    from sc
    where Cno='1')
    intersect 
    (select Sno
    from sc
    where Cno='2'));
--(3)
select Sname
from student
where Sno in
    (select Sno
    from sc
    where Cno='1'and 
	sno in(select Sno
    from sc
    where Cno='2'))
--16.	使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；
select Sno,Sname
from student
where Sno in(
    select Sno
    from sc
    where Cno in(
        select Cno
        from course
        where Cname='数据结构'
    )
);

--17.	使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；
select Sname,Sage,Sdept
from student
where Sdept<>'CS' and Sage<any(
    select Sage
    from student
    where Sdept='CS'
);

--18.	查询选课人数最多的课程号和课程名（包含并列）；
--方法1:
select Cno,Cname
from course
where Cno in(
    select top 1 with ties Cno
    from sc
    group by Cno
    order by count(Sno) desc
);
--方法2:
select sc.Cno,course.Cname
from sc,course 
where sc.Cno=course.Cno
group by sc.Cno,course.Cname
having count(sc.sno)>=all(select count(c1.sno) 
from sc c1
group by c1.sno)
--方法3:
select cno,cname
from course
where cno in (select cno 
              from sc 
              group by cno having count(sno)>=all(select count(c1.sno) 
                                                  from sc c1
                                                  group by c1.sno)
			  )
--19.	使用集合查询列出CS系的学生以及性别为女的学生名单；
(select Sname
from student
where Sdept='CS')
union
(select Sname
from student
where Ssex='女');

--20.	使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；
--交集
(select *
from student
where Sdept='CS')
intersect
(select *
from student
where Sage<=19);
--差集
(select *
from student
where Sdept='CS')
except
(select *
from student
where Sage<=19);

--（二）以数据库原理实验1中SPJ数据库为基础，请使用T-SQL 语句实现教材P130第5题（1）—（7）
--(1)找出所有供应商的姓名和所在城市
select Sname,City
from S;

--(2)找出所有零件的名称，颜色，重量
select Pname,Color,weights
from P;

--(3)找出使用供应商S1所供应零件的工程号码
select Jno
from SPJ
where Sno='s1';

--(4)找出工程项目J2使用的各种零件的名称以及数量
select pname,qty
from p,spj
where p.pno=spj.pno and jno='j2'


--(5)找出上海厂商供应的所有零件号码
select distinct Pno
from SPJ
where Sno in(
    select Sno
    from S
    where City='上海'
);

--(6)找出使用上海产的零件的工程名称
--方法1:
select  distinct jname
from s,j,spj
where s.sno=spj.sno and j.jno=spj.jno and s.City='上海'
--方法2
select jname
from j
where jno in(select jno from spj where sno in (select sno from s where city='上海'))
--(7)找出没有使用天津产的零件的工程号码
--方法1
select jno 
from j
where not exists 
  (select * 
  from spj 
   where spj.jno=j.jno and sno in (select sno from s where city='天津')
   )
--方法2:
select jno 
from j
where not exists
    (select * 
	  from spj,s
	  where spj.sno=s.sno and spj.jno=j.jno and s.city='天津')
--本题不可以这样写:
select distinct Jno
from SPJ
where Pno not in(
    select Pno
    from SPJ
    where Sno in(
        select Sno
        from S
        where City='天津'
    )
);
--（三）对罗斯文（Northwind）数据库完成一下查询
--1.查询每个订单购买产品的数量和总金额，显示订单号，数量，总金额
select OrderID 订单号,sum(Quantity) 数量,sum(Quantity*UnitPrice*(1-Discount)) 总金额
from [Order Details]
group by OrderID
--2. 查询每个员工在7月份处理订单的数量
--方法1
select EmployeeID,count(*) 订单数
from Orders
where datepart(month,OrderDate)=7
group by EmployeeID;
--方法2(若员工没有处理订单亦可显示出来)
select Employees.EmployeeID,count(Orders.OrderID) 订单数量
from Orders right join Employees on Employees.EmployeeID=Orders.EmployeeID
where MONTH(Orders.OrderDate)='07'
group by Employees.EmployeeID
--3. 查询每个顾客的订单总数，显示顾客ID，订单总数
--方法1
select CustomerID,count(*) 订单总数
from Orders
group by CustomerID;
--方法2(顾客没有订单也可以显示出来)
select Customers.CustomerID,count(Orders.OrderID) 订单总数
from Orders right join Customers on Orders.CustomerID=Customers.CustomerID
group by Customers.CustomerID
--4. 查询每个顾客的订单总数和订单总金额
--方法1
select CustomerID,count(distinct OD.OrderID) 订单总数,sum(Quantity*UnitPrice*(1-Discount)) 订单总金额
from Orders as O inner join [Order Details] as OD on O.OrderID=OD.OrderID
group by CustomerID;
--方法2(顾客没有订单也可以显示出来)
select Customers.CustomerID,count(distinct Orders.OrderID) 订单总数,sum([Order Details].Quantity*[Order Details].UnitPrice*(1-[Order Details].Discount)) 订单总金额
from Orders  right join Customers on Orders.CustomerID=Customers.CustomerID 
  left join [Order Details]
on Orders.OrderID=[Order Details].OrderID
group by Customers.CustomerID
--5. 查询每种产品的卖出总数和总金额
--方法1
select P.ProductID,sum(Quantity)卖出总数,sum(Quantity*OD.UnitPrice*(1-Discount)) 订单总金额
from Products as P left outer join [Order Details] as OD on P.ProductID=OD.ProductID
group by P.ProductID;
--方法2
select sum([Order Details].Quantity) 卖出总数,sum([Order Details].Quantity*[Order Details].UnitPrice*(1-[Order Details].Discount)) 总金额
from Products left join [Order Details] on Products.ProductID=[Order Details].ProductID
group by Products.ProductID
--6. 查询购买过全部商品的顾客的ID和姓名
--(查询一个顾客，不存在某个商品他没有购买过)
--方法1
select c1.CustomerID,c1.ContactName
from Customers c1
where not exists(
select *
from Products p1
where not exists (
select *
from Orders,[Order Details]
where Orders.OrderID=[Order Details].OrderID and Orders.CustomerID=c1.CustomerID and [Order Details].ProductID=p1.ProductID))
--方法2
select c1.CustomerID,c1.ContactName
from Customers c1,Orders,[Order Details]
where c1.CustomerID=Orders.CustomerID and Orders.OrderID=[Order Details].OrderID
group by c1.CustomerID,c1.ContactName
having count([Order Details].OrderID)=(
select count(Products.ProductID)
from Products)
--（四）	对books数据库完成以下操作
--1.	查询各种类别的图书的类别和数量（包含目前没有图书的类别）
select TypeName,count(BookNo)数量
from BookType as B1 left outer join BookInfo as B2 on B1.TypeID=B2.TypeID
group by TypeName;  

--2.	查询借阅了‘数据库基础’的读者的卡编号和姓名

select B.CardNo,Reader
from BorrowInfo as B inner join CardInfo as C on B.CardNo=C.CardNo
where B.BookNo=(
    select BookNo
    from BookInfo
    where BookName='数据库基础'
);
--或
select CardNo,Reader
from CardInfo
where CardNo in (
select CardNo
from BorrowInfo,BookInfo
where BorrowInfo.BookNo=BookInfo.BookNo and BookName='数据库基础'
)
--3.	查询各个出版社的图书价格超过这个出版社图书的平均价格的图书的编号和名称。
select BookNo,BookName
from BookInfo as B1
where Price>(
    select avg(Price)
    from BookInfo as B2
    where B1.Publisher=B2.Publisher
);

--4.	查询借阅过了全部图书的读者的编号和姓名
--(就是查找一个读者，不存在一本书，这个读者没有借阅过)
select CardNo,Reader
from CardInfo
where not exists(
select *
from BookInfo
where not exists
(
select *
from BorrowInfo
where BorrowInfo.CardNo=CardInfo.CardNo and BorrowInfo.BookNo=BookInfo.BookNo))

--5.	查询借阅图书包含李明所借的全部图书的读者的编号和姓名
--(就是查找某读者，不存在李明借过的图书，这个读者没有借过)
select CardNo,Reader
from CardInfo as CI 
where not exists(
    select *
    from BorrowInfo as BI2
    where BI2.CardNo=(
        select CardNo
        from CardInfo
        where Reader='李明'
    ) and not exists(
        select *
        from BorrowInfo as BI3
        where BI3.BookNo=BI2.BookNo and BI3.CardNo=CI.CardNo
    )
)and CI.Reader!='李明';
--或
select c1.CardNo,c1.Reader
from CardInfo c1
where not exists (
select *
from CardInfo c2,BorrowInfo b1
where c2.CardNo=b1.CardNo and c2.Reader='李明'
and not exists(
select *
from BorrowInfo b2
where b2.CardNo=c1.CardNo and b2.BookNo=b1.BookNo
) 
)and c1.Reader!='李明'
--6.	查询借阅次数超过2次的读者的编号和姓名
select BI.CardNo,Reader
from BorrowInfo as BI inner join CardInfo as CI on BI.CardNo=CI.CardNo
group by BI.CardNo,Reader
having count(BorrowDate)>2;
或
select CardInfo.CardNo,CardInfo.Reader
from CardInfo,BorrowInfo
where CardInfo.CardNo=BorrowInfo.CardNo
group by CardInfo.CardNo,CardInfo.Reader
having count(*)>2
--7.	查询借阅卡的类型为老师和研究生的读者人数
select count(*) 人数
from CardInfo as CI inner join CardType as CT on CI.CTypeID=CT.CTypeID
where TypeName in('教师','研究生');

--8.	查询没有被借过的图书的编号和名称
--(查找一本书，不存在它被借过)
select BookNo,BookName
from BookInfo as BI
where not exists(
    select *
    from BorrowInfo as BI2
    where BI2.BookNo=BI.BookNo
);
--或
select BookInfo.BookNo,BookInfo.BookName
from BookInfo
where BookInfo.BookNo not in(
select BorrowInfo.BookNo
from BorrowInfo)
--9.	查询没有借阅过英语类型的图书的学生的编号和姓名
--(不存在有一本英语类型的图书，他借过)
select CardInfo.CardNo,CardInfo.Reader
from CardInfo
where not exists
(select *
from BookType,BorrowInfo,BookInfo
where BookType.TypeName='英语' and BookType.TypeID=BookInfo.TypeID
 and  BookInfo.BookNo=BorrowInfo.BookNo and BorrowInfo.CardNo=CardInfo.CardNo)
 and CTypeID in(
    select CTypeID
    from CardType
    where TypeName in('学生')
);
--或
select CI.CardNo,Reader
from CardInfo as CI left outer join BorrowInfo as BI on BI.CardNo=CI.CardNo
where not exists(
    select *
    from BorrowInfo BI2
    where BI2.BookNo in(
        select BookNo
        from BookInfo as BI3 inner join BookType as BT on BI3.TypeID=BT.TypeID
        where BT.TypeName='英语'
    ) and BI2.CardNo=BI.CardNo 
)and CI.CTypeID in(
    select CTypeID
    from CardType
    where TypeName in('学生')
);

--10.	查询借阅了‘计算机应用’类别的‘数据库基础’课程的读者编号以及该读者的借阅卡的类型。

select CardInfo.CardNo, CardType.TypeName
from CardInfo,CardType
where CardInfo.CTypeID = CardType.CTypeID
      and CardInfo.CardNo in (
	      select BorrowInfo.CardNo from BorrowInfo, BookInfo,BookType
		  where BorrowInfo.BookNo = BookInfo.BookNo and BookType.TypeID = BookInfo.TypeID and BookType.TypeName = '计算机应用'
		  and BookName = '数据库基础'
	  )

--（五）	对商场数据库完成以下操作
/*
Market (mno, mname, city)
Item (ino, iname, type, color)
Sales (mno, ino, price)
其中，market表示商场，它的属性依次为商场号、商场名和所在城市；
item表示商品，它的属性依次为商品号、商品名、商品类别和颜色；
sales表示销售，它的属性依次为商场号、商品号和售价。
用SQL语句实现下面的查询要求：
*/

--1.	列出北京各个商场都销售，且售价均超过10000 元的商品的商品号和商品名
--(不存在北京的一个商场不销售它) 
select ino,iname
from item
where not exists
(
select *
from sales,market
where sales.mno=market.mno and market.city='北京'
and not exists
(select *
from sales s1
where s1.ino=item.ino and s1.mno=sales.mno and s1.price>10000
))
--2.	列出在不同商场中最高售价和最低售价之差超过100 元的商品的商品号、最高售价和最低售价

select sales.ino,max(sales.price),min(sales.price)
from sales
group by sales.ino
having max(sales.price)-min(sales.price)>100

--3.	列出售价超过该商品的平均售价的各个商品的商品号和售价
select s1.ino,s1.price
from sales s1
where  s1.price>
(select avg(s2.price)
from sales s2
where s1.ino=s2.ino)
--4.	查询销售了全部商品的商场号，商场名和城市
--(不存在一个商品，这个商场没有销售过)
select m1.mno,m1.mname,m1.city
from market m1
where not exists(
select*
from item i1
where not exists(
select *
from sales
where sales.mno=m1.mno and sales.ino=i1.ino))
--5.	查询所有商场都销售了的商品的商品号和商品名。（用两种方法实现）
--(1)(不存在一个商场没有销售过这个商品)
select i1.ino,i1.iname
from item i1
where not exists 
(select *
from market m1
where not exists (select *
from sales
where sales.ino=i1.ino and sales.mno=m1.mno))

--(2)销售它的商场数等于商场总数 
select item.ino,item.iname
from item,sales
where item.ino=sales.ino
group by sales.ino,item.ino,item.iname
having count(sales.mno)=(
select count(market.mno)
from market)

--6.	查询每个商场里价格最高的商品的名称（用两种方法做）
--(1)等于最高价
select mno,iname
from sales as S1 inner join item as I on S1.ino=I.ino
where price=(
    select max(price)
    from sales as S2
    where S2.mno=S1.mno
);

--(2)>=all
select mno,iname
from sales as S inner join item as I on S.ino=I.ino
where price>=all(
    select price
    from sales as S2
    where S2.mno=S.mno
);

实验4

--一、对xsgl数据库完成下列操作要求：
--1.将被全部学生都选修了的课程的总学分改为4学分。
方法一：
update kc
set kc.学分=4
where kc.课程号 in 
(select kc.课程号 
from kc where not exists (
select *from xs where not exists 
(select * from cj c1 where c1.课程号=kc.课程号 and c1.学号=xs.学号))) 
或
update kc
set kc.学分=4
where kc.课程号 in 
(select distinct cj.课程号
from cj group by cj.课程号
having count(cj.课程号)=(select count(xs.学号)from xs))

--2.从学生表删除没有选课的学生。
delete 
from xs
where xs.学号 not in
    (select cj.学号 
	 from cj)

--3.将每个学生的平均分，总分和选课门数插入到数据库中（学号，姓名，平均分，总分，选课门数）
create table New_data(学号 char(10),
姓名 nchar(10),平均分 numeric(10,2),总分 numeric(10,2),选课门数 smallint) 
insert 
into New_data
select xs.学号,xs.姓名,avg(cj.成绩),sum(cj.成绩),count(cj.课程号)
from xs left join cj on xs.学号=cj.学号
group by xs.学号,xs.姓名

select xs.学号,xs.姓名,avg(cj.成绩)平均分,sum(cj.成绩) 总分,count(cj.课程号) 选课门数
into xs1
from xs left join cj on xs.学号=cj.学号
group by xs.学号,xs.姓名
--4.创建每门课程的平均分和选课人数的视图（课程号，课程名，平均分，人数）
create view C(课程号,课程名,平均分,人数)
as
select kc.课程号,kc.课程名,avg(cj.成绩) 平均分,count(cj.学号) 人数
from kc left join cj on kc.课程号=cj.课程号
group by kc.课程号,kc.课程名

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

--6.插入一条选课记录（具体内容自己选）。
insert 
into xs
values('201700800','李四',null,null,null,null,null,null,null)
insert 
into cj
values('201700800','A001',NULL)

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

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

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


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

--11.查询每个学生成绩高于自己的平均成绩的学号，姓名，课程号和成绩（使用派生表实现）
select xs.学号,xs.姓名,cj.课程号,cj.成绩
from xs,cj,(select c1.学号,avg(c1.成绩) 平均成绩 from cj c1 group by c1.学号) as avg_score 
where xs.学号=cj.学号 and cj.学号=avg_score.学号 and cj.成绩>avg_score.平均成绩
--12.自己验证with check option的作用。
create view IS_Student
as
select xs.学号,xs.姓名,xs.性别,xs.专业
from xs
where xs.专业='网络工程'
with check option

 insert into IS_Student(学号,姓名)
 values('2001001','张三') 
--13.创建一个网络工程系的学生基本信息的视图MA_STUDENT，在此视图的基础上，
--再定义一个该专业女生信息的视图，然后再删除MA_STUDENT，观察执行情况。
--打不开女生信息的视图
create view MA_STUDENT
as
select *
from xs
where xs.专业='网络工程'
with check option

create view Woman
as
select *
from MA_STUDENT
where MA_STUDENT.性别='女'

drop view MA_STUDENT

--14.查询和程明同龄的学生的学号和姓名以及年龄

select xs.学号, xs.姓名, DATEDIFF(yy, xs.出生时间, GETDATE())
from xs
where DATEDIFF(yy, xs.出生时间, GETDATE()) = (select DATEDIFF(yy, x1.出生时间, GETDATE()) from xs x1 where x1.姓名 = '程明')

--15.查询没有被网络工程全部的学生都选修的课程的课程号和课程名（可以被网络工程专业的部分学生选修）

select 课程号,kc.课程名 from kc 
where  exists(select * from xs where xs.专业 = '网络工程' 
         and not exists(select * from cj where cj.学号 = xs.学号 and cj.课程号 = kc.课程号))
--16.查询选课包含了选英语的全部学生的课程的课程号和课程名。
select 课程号,课程名
from kc
where not exists
(select *
from xs
where 学号 in (select 学号 from cj where 课程号=(select 课程号 from kc where 课程名='英语'))and not exists 
(select * from cj
where  学号=xs.学号 and 课程号=kc.课程号))
--17.查询没有选修数据结构，操作系统和英语三门课的学生的学号，姓名，课程号，课程名和成绩

select xs.学号, xs.姓名, kc.课程号, kc.课程名, cj.成绩
from xs inner join cj on cj.学号 = xs.学号 inner join kc on kc.课程号 = cj.课程号
where xs.学号 not in (select c1.学号 from cj c1 inner join kc k1 on k1.课程号 = c1.课程号 where k1.课程名 in ('数据结构', '操作系统', '英语'))

--18.将没有选课的学生的总学分设置为0

update xs
set xs.总学分 = 0
where xs.学号 not in(select cj.学号 from cj)
--二、使用Northwind数据库完成下列操作
--1. 将员工lastname是: Peacock处理的订单中购买数量超过50的商品折扣改为七折
update [Order Details]
set Discount=0.3
where [Order Details].OrderID in
(select o1.OrderID
from Employees,Orders,[Order Details] o1
where Employees.EmployeeID=Orders.EmployeeID and Orders.OrderID=o1.OrderID
  and Employees.LastName='Peacock' and o1.Quantity>50) and [Order Details].Quantity>50
--2. 删除lastname是: Peacock处理的所有订单
delete 
from [Order Details]
where [Order Details].OrderID in(select distinct o1.OrderID
from Employees,Orders,[Order Details] o1
where Employees.EmployeeID=Orders.EmployeeID and Orders.OrderID=o1.OrderID
  and Employees.LastName='Peacock')
或
delete
from Orders
where Orders.EmployeeID=(
select Employees.EmployeeID
from Employees
where Employees.LastName='Peacock')

--3. 将每个订单的订单编号，顾客编号，产品总数量，总金额插入到数据库中
create table Order_Information
(OrdersID int, CustomerID nchar(5),Number smallint ,Totalmoney money)
insert
into Order_Information
select Orders.OrderID,Orders.CustomerID,sum([Order Details].Quantity) 数量,
sum([Order Details].Quantity*[Order Details].UnitPrice*(1-[Order Details].Discount)) 金额
from Orders,[Order Details]
where Orders.OrderID=[Order Details].OrderID
group by Orders.OrderID,Orders.CustomerID 

--4. 插入一个新的订单，要求该订单购买了商品编号为5,7,9的商品。
--（5号商品买了10个，7号买了20个，9号买了15个，都没有折扣）
--报错：仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'Orders'中的标识列指定显式值
insert
into Orders(CustomerID,EmployeeID)
values('VINET',6)

declare @id int
set @id=(select max(Orders.OrderID) from Orders)

insert 
into [Order Details]
values(@id, 5, 100, 10, 0);
insert into [Order Details]
values(@id, 7, 130, 20, 0);
insert into [Order Details]
values(@id, 9, 160, 15, 0);
      
--5.将每年每个员工处理订单的数量和订单的总金额创建为视图
create view Employ
as
select Employees.EmployeeID,datename(yy,Orders.OrderDate) 年份,
	   count(distinct Orders.OrderID) 订单数量,sum([Order Details].Quantity) 数量,
       sum([Order Details].Quantity*[Order Details].UnitPrice*(1-[Order Details].Discount)) 订单总金额
from Employees left join Orders on Employees.EmployeeID=Orders.EmployeeID
			   left join [Order Details] on Orders.OrderID=[Order Details].OrderID
group by Employees.EmployeeID,datename(yy,Orders.OrderDate)

--6. 购买了CustomerID是‘VINET’用户所购买的全部商品的用户的CustomerID和CompanyName。

select c1.CustomerID, c1.CompanyName
from Customers c1
where not exists(select * from Customers c2 inner join Orders o1 on o1.CustomerID = c2.CustomerID 
  inner join [Order Details] od1 on od1.OrderID = o1.OrderID where c2.CustomerID = 'VINET' 
  and not exists(select * from Orders o2 inner join [Order Details] od2 on od2.OrderID = o2.OrderID
  where o2.CustomerID = c1.CustomerID and od2.ProductID = od1.ProductID))

--7. 将被全部顾客都购买过的商品的商品编号和商品名和单价创建为一个视图proa

create view proa
as select Products.ProductID, Products.ProductName
from Products 
where Products.ProductID in 
(select p1.ProductID from Products p1 
where not exists(select * from Customers c1 
         where not exists(select * from [Order Details] od1 inner join Orders o1 on o1.OrderID = od1.OrderID 
		             where o1.CustomerID = c1.CustomerID and od1.ProductID = p1.ProductID)))

--8. 将被全部顾客都购买过的商品的单价加上5元

update Products
set Products.UnitPrice = Products.UnitPrice + 5
where Products.ProductID in (select p1.ProductID from Products p1 where not exists(select * from Customers c1 where not exists(select * from Order_Details od1 inner join Orders o1 on o1.OrderID = od1.OrderID where o1.CustomerID = c1.CustomerID and od1.ProductID = p1.ProductID)))

--9. 删除订单总金额少于50元的订单明细

delete from [Order Details]
where [Order Details].OrderID in
   (select od1.OrderID from [Order Details] od1 
   group by od1.OrderID 
   having sum(od1.Quantity * (1 - od1.Discount) * od1.UnitPrice) < 50)

实验5

--1．创建一windows用户（名字自定），用sql语句建立windows验证模式的登录名。默认数据库为student
CREATE LOGIN [DESKTOP-8QRH2EV\liu] FROM WINDOWS--注意：DESKTOP-8QRH2EV是计算机名，liu是Windows用户名
WITH DEFAULT_DATABASE = Student;
--2．用sql语句和系统存储过程建立登录名sqluser,密码为1234
CREATE LOGIN sqluser WITH PASSWORD = '1234'
exec sp_addlogin sqluser, '1234'
--3．为student数据库新建用户u1，其登录名为sqluser。
USE student
GO
CREATE USER u1 FOR LOGIN sqluser
exec sp_adduser sqluser,u1
--4．新建登录usersf，并将其加入到sysadmin固定服务器角色中。
exec sp_addlogin usersf, '1234'
exec sp_addsrvrolemember 'usersf','sysadmin'--由于sysadmin是系统管理员角色，拥有所有权限。
--5．将student用户u1（登录名为sqluser）加入到db_owner角色中，使其全权负责该数据库,并验证其权限。
USE student
GO 
exec sp_addrolemember 'db_owner',u1
 -- 验证:以sqluser登录,在新建查询界面输入以下内容验证
    USE student
    GO
    SELECT * FROM SC-- 成功，db_owner拥有查询权限
    UPDATE SC
    SET Grade = 75
    WHERE Sno = '200515001' AND cno = '4' -- 成功，db_owner拥有修改表项的权限
	insert into course values('12','大数据','1',3)--成功，db_owner拥有插入的权限
    ALTER TABLE SC
        ADD temp tinyint -- 成功，db_owner拥有更改表结构的权限

--6．为SPJ数据库新建用户u2，u3，其登录名分别为u2，u3。
CREATE LOGIN u2 WITH PASSWORD = '1234', DEFAULT_DATABASE = SPJ
CREATE LOGIN u3 WITH PASSWORD = '1234', DEFAULT_DATABASE = SPJ
USE SPJ
GO
CREATE USER u2 FOR LOGIN u2
CREATE USER u3 FOR LOGIN u3

--（1）授予用户u2对S表有SELECT 权，对P表颜色（COLOR）具有更新权限；
    GRANT SELECT ON S TO u2 WITH GRANT OPTION;
    GRANT UPDATE(COLOR) ON P TO u2
    WITH GRANT OPTION;
--（2）u2将其拥有的权限授予u3；
    -- 本小题在u2登录下执行.验证u2获得权限，并给u3授权
USE SPJ
GO
 SELECT * FROM S; -- 成功
 SELECT * FROM P; -- 拒绝
 UPDATE P SET COLOR='红'--成功
 update P set color='绿' where pno='p1'--拒绝，因为u2没有对P的查询权限
 GRANT SELECT ON S TO u3;
 GRANT UPDATE(COLOR) ON P TO u3
--（3）用sql语句逐一验证u3所获得的权限。
    -- 以u3身份登录:
 SELECT * FROM S; -- 成功
 SELECT * FROM P; -- 拒绝
 UPDATE P SET COLOR='红'--成功
 update P set color='绿' where pno='p1'--拒绝，因为u2没有对P的查询权限
  --（4）撤销用户u3所获得的权限，并验证。
    -- 以u2身份登录并操作:
    REVOKE SELECT ON S FROM u3;
    REVOKE UPDATE(COLOR) ON P FROM u3;
    -- 以u3身份登录并操作:
    SELECT * FROM S -- 拒绝
    UPDATE P SET COLOR='红' --拒绝
--7.在student数据库中建立角色operate,该角色具有对student和course表的查询权限；具有对表sc的插入和修改权限。
USE student
GO
CREATE ROLE operate
GRANT SELECT ON student TO operate
GRANT SELECT ON course TO operate
GRANT update,INSERT ON SC TO operate
--8.拒绝用户u1对sc表的修改权限。
DENY update ON SC TO u1
--9.使用存储过程将角色operate赋给用户u1,并用sql语句验证其权限。（特别验证u1对sc表的修改权限）
exec sp_addrolemember operate,u1
-- 验证:这里用登录名sqluser登录
USE student
GO
SELECT * FROM Student-- 成功
SELECT * FROM course-- 成功
insert into sc values ('200515003','3',88,NULL)--成功
update sc set temp=0
--消息 229，级别 14，状态 5，第 6 行
--拒绝了对对象 'sc' (数据库 'student'，架构 'dbo')的 UPDATE 权限。
--这是因为用户u1通过operate角色获得的对SC的update权限，由于被DENY了，因此该语句的执行被拒绝了.
--10.创建数据库架构jg_test,其所有者为student用户u1。
use student
go
create schema jg_test
authorization u1
grant alter
on schema::dbo
to u1
--（1）授予用户u1建表权限；
grant create table
to u1
--（2）授予用户（自己建立wei）对架构jg_test进行查询、插入的权限
exec sp_addlogin wei
create user wei
for login wei
grant select,insert
on schema::jg_test
to wei
--（3）以u1身份登录sql srver,新建一个表，观察表的架构名称。验证用户wei对该表的查询和插入权限，并验证其是否可对该表进行删除。
create table jg_test.s
(s1 char(10),s2 int)
--命令成功完成
--以wei身份登录sql srver
use student
go
select * from jg_test.s--命令成功完成
insert into jg_test.s values('t1234',10)--命令成功完成
delete from jg_test.s
--消息 229，级别 14，状态 5，第 5 行
--拒绝了对对象 's' (数据库 'student'，架构 'jg_test')的 DELETE 权限。
补充：
--11. 在student数据库中创建架构（schema）teacher指定给用户teacher（也就是要先创建一个teacher用户）
use student
go
CREATE LOGIN tt WITH PASSWORD = '1234'
create user teacher for login tt
create schema teacher AUTHORIZATION teacher
--12.	在已创建的teacher架构中创建“tea”表，表结构为（tno(编号), tname(姓名), tsd（专业）,tphone, te_mail）(数据类型和长度自己定义)，通过teacher架构为teacher1用户设置查询权限，验证teacher1用户对表tea是否具有select权限和delete权限，为什么？
create table teacher.tea
(tno char(10),
 tname varchar(20),
 tsd varchar(10),
 tphone char(11),
 te_mail varchar(30)
 )
 insert into teacher.tea values('1234567890','王伟','计算机','13963112345','123.com')
 --验证过程
 /*在“对象资源管理器”中展开数据库“student”→“安全性”→“架构”，选择新创建的架构teacher，
 右键单击鼠标，在弹出的快捷菜单中选择“属性”菜单项，打开“架构属性”窗口，
 在该架构属性的“权限”选项页中，单击“搜索”按钮，选择用户u1（假设已经创建），为用户授予“选择”权限。单击“确定”*/
 --以sqluser登录
 select * from teacher.tea--成功
delete from teacher.tea--拒绝了对对象 'tea' (数据库 'student'，架构 'teacher')的 DELETE 权限

实验6

USE STU
go
CREATE TABLE COURSE(
	CNO CHAR(4),
	CNAME CHAR(40),
	CPNO CHAR(4),
	CCREDIT SMALLINT
);

CREATE TABLE SC(
	SNO CHAR(9),
	CNO CHAR(4),
	GRADE SMALLINT
);

CREATE TABLE STUDENT(
	SNO CHAR(9),
	SNAME CHAR(20),
	SSEX CHAR(4),
	SAGE SMALLINT,
	SDEPT CHAR(10)
)

--1.	将stu数据库中student表的sno定义为主键；
USE STU
go
ALTER TABLE STUDENT 
ALTER COLUMN SNO CHAR(9) NOT NULL/*将某属性设置为主键之前，必须先将该属性设置为NOT NULL*/
ALTER TABLE STUDENT
ADD CONSTRAINT P1 PRIMARY KEY(SNO)
--2.	将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;
ALTER TABLE COURSE
ALTER COLUMN CNO CHAR(4) NOT NULL
ALTER TABLE COURSE 
ADD CONSTRAINT CNO_PK PRIMARY KEY(CNO)
--3.	为表course中的字段cname添加唯一值约束；
ALTER TABLE COURSE
ADD CONSTRAINT U1 UNIQUE(CNAME)
--4.	将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;
ALTER TABLE SC
ALTER COLUMN SNO CHAR(9) NOT NULL
ALTER TABLE SC
ALTER COLUMN CNO CHAR(4) NOT NULL
ALTER TABLE SC
ADD CONSTRAINT SC_PK PRIMARY KEY(SNO,CNO)
--5.	对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：
--1)	删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；
--2)	修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；
ALTER TABLE SC
ADD CONSTRAINT F1 FOREIGN KEY(SNO) REFERENCES STUDENT(SNO)
ON DELETE CASCADE
ON UPDATE NO ACTION 
--3)	修改course表cno字段值时，该字段在sc表中的对应值也应修改；
ALTER TABLE SC
ADD CONSTRAINT F2 FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
ON UPDATE CASCADE

--4)	删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；

ALTER TABLE SC
ADD CONSTRAINT F3 FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
ON DELETE CASCADE 

--5)	向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；
ALTER TABLE SC
ADD CONSTRAINT F4 FOREIGN KEY(SNO)  REFERENCES STUDENT(SNO)


--6.	定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
ALTER TABLE STUDENT 
ADD CONSTRAINT C1 CHECK(SNO LIKE '[1-9]00[0-9][0-9][0-9][0-9][0-9][0-9]')
--7.	定义stu数据库中student表中学生年龄值在16-25范围内；
ALTER TABLE STUDENT 
ADD CONSTRAINT C2 CHECK(SAGE BETWEEN 16 AND 25)
--8.	定义stu数据库中student表中学生姓名长度在2-8之间；
ALTER TABLE STUDENT 
ADD CONSTRAINT C3 CHECK(LEN(SNAME) BETWEEN 2 AND 8)
--9.	定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
ALTER TABLE STUDENT 
ADD CONSTRAINT C4 CHECK(SSEX IN ('男','女'))
--10.	定义stu数据库student表中学生年龄值默认值为20；
ALTER TABLE STUDENT 
ADD CONSTRAINT D_SAGE DEFAULT 20 FOR SAGE
--11.	修改student表学生的年龄值约束可以为15-30范围内；
ALTER TABLE STUDENT 
DROP CONSTRAINT C2;
ALTER TABLE STUDENT 
ADD CONSTRAINT C2 CHECK(SAGE BETWEEN 15 AND 30)
--12.	删除上述唯一值约束、外键约束及check约束；
ALTER TABLE COURSE
DROP CONSTRAINT U1 
ALTER TABLE STUDENT 
DROP CONSTRAINT C2
--13.	设计触发器实现如果一个学生转专业了，那么输出一条信息显示该学生各门课程的平均分。
USE STUDENT
GO
CREATE TRIGGER A1
ON STUDENT
FOR
UPDATE
AS 
IF UPDATE(SDEPT)
BEGIN
DECLARE @AVG NUMERIC(4,1),@SNO CHAR(9)
SELECT @SNO=(SELECT SNO FROM DELETED)
SELECT @AVG=(SELECT AVG(GRADE) FROM SC WHERE SNO=@SNO) 
PRINT @SNO+'各门课的平均成绩'+cast(@AVG as CHAR(5))
END
--测试
UPDATE STUDENT SET SDEPT='CS' WHERE SNO='200515008'/*只有修改sdept时，触发器才触发执行*/
UPDATE STUDENT SET SAGE=26 WHERE SNO='200515008'/*该语句只修改了学生的年龄，A1触发器并没有执行*/

--14.	设计触发器实现如果成绩被修改了20分以上，则输出提示信息“修改成绩超过20分，	请慎重”。
CREATE TRIGGER A2
ON SC
FOR UPDATE
AS
IF UPDATE(GRADE)
BEGIN
DECLARE @OLD INT
DECLARE @NEW INT
SELECT @OLD = GRADE FROM DELETED
SELECT @NEW = GRADE FROM INSERTED
IF abs(@NEW-@OLD)>=20
PRINT'修改成绩超过20分，请慎重'
END
--测试
update sc
set grade=grade+15
where sno='200515002' and cno='5'/*触发器没执行*/
update sc
set grade=grade+25
where sno='200515001' and cno='4'
15.在课程表里添加一列：选课人数，设置触发器每门课如果有人选修了那么课程表里的选课人数相应修改，考虑批量插入选课记录的情况。

use student
go
alter table course add cnt int--cnt为选课人数
update course set cnt = (select count(*) from sc where sc.cno = course.cno)
select * from course

create trigger cnt_add
on sc 
for insert
as
begin
if @@ROWCOUNT=1
update course set cnt=cnt+1
where course .cno in (select cno  from inserted)
else 
update course set cnt=cnt+(select count(sno) from inserted where course .cno =inserted.cno ) 
where course.cno in (select cno from inserted)
end

--测试
select * from sc
insert into SC(Sno,Cno) VALUES
    ('200515001',9),
    ('200515002',9),
    ('200515002',6)
select * from course

--16.设计一触发器，约束每门课程的课容量为120。
use student
go
create trigger ex16
on sc
for insert
as 
	declare @count int,@kecheng varchar(20)
	select @count=count(*) from sc where cno=(select cno from inserted)
	select @kecheng=cname from course where cno=(select cno from inserted)
	if(@count>120)
		begin
			print '课程'+@kecheng+'人数已经达到课容量'
			rollback
		end
	else
		begin
			print '选课'+@kecheng+'成功'
		end
--测试
insert into sc(sno,cno)
	values('200515009','1')
--方法二
create trigger ex162
on sc
for insert
as
declare @cno char(4)
begin
	select @cno = cno from inserted
	if (select count(*) from sc where sc.cno = @cno ) >= 60
		rollback
end
--17.如果一门课的学分发生了修改， 则所有选修该课程并且及格的学生的总学分要做相应的修改，并输出学号，姓名，原总学分，新总学分
update student--求出学生原来的总学分total
set total=(select sum(credit) from course,sc
           where course.cno=sc.cno and sc.sno=student.sno and grade >=60)
create trigger credit_change
on course
for update
as
if update(credit)
begin
declare @cno char(4)
declare @oldc numeric(2,1)
declare @newc numeric(2,1)
select @cno = cno, @oldc = credit from deleted
select @newc = credit from inserted
update student  
set total = @newc - @oldc +total 
		where sno in 
		(select sno from sc
		 where cno = @cno and grade >= 60)
		
select sno, sname, total 新总学分, total + @oldc - @newc 原总学分
from student 
where sno in 
(select sno 
from sc 
where cno = @cno and grade >= 60)
end
--测试
update course set credit=credit+1 where cno='1'
--18．设有两个表：商品库存表（商品编号，商品名称，库存数量，库存单价，库存金额）；商品销售表（商品编号，商品名称，购货商号，销售数量，销售单价，销售金额）；设计一触发器实现如下业务规则：
--（1）保证在商品库存表中插入的数据，库存金额 = 库存数量 * 库存单价。
CREATE TABLE 商品销售表 
( 
商品编号 char(6)PRIMARY KEY,
商品名称 VARCHAR(40)  NOT NULL,  
购货商 VARCHAR(40) NULL,  
销售数量 INT NULL,
销售单价 MONEY NULL,  
销售金额 MONEY NULL  )  
CREATE TABLE 商品库存表
  (  商品编号 char(6)PRIMARY KEY,
商品名称 VARCHAR(40)  NOT NULL, 
库存数量 INT NULL,  
库存单价 MONEY NULL,  
库存金额 MONEY NULL  ) 
go
CREATE TRIGGER INSERT_商品库存表  
ON 商品库存表  
FOR INSERT 
 AS  
 UPDATE 商品库存表  
SET 库存金额 = 库存数量 * 库存单价  
WHERE 商品编号 IN (SELECT 商品编号 from INSERTED)  
go
--测试
INSERT INTO 商品库存表(商品编号,商品名称,库存数量,库存单价,库存金额) 
values('100000', '红塔山',100,12,1200), 
 ('100001','将军',100,22,NULL ), 
('100002' ,'中华',100,60,500 ), 
('100003','玉溪',0,30,0 )
go

--（2）如果销售的商品不存在库存或者库存为零，则返回提示信息。否则自动减少商品库存表中对应商品的库存数量和库存金额。
CREATE TRIGGER INSERT_商品销售表 
ON 商品销售表 
FOR INSERT 
AS 
BEGIN TRANSACTION 
--检查数据的合法性：销售的商品是否有库存，或者库存是否大于零 
IF NOT EXISTS ( 
SELECT 库存数量 
FROM 商品库存表 
WHERE 商品编号 IN (SELECT 商品编号 FROM INSERTED) 
) 
BEGIN 
--返回错误提示 
RAISERROR('错误！该商品不存在库存，不能销售！',16,1) 
--回滚事务 
ROLLBACK 
RETURN 
END 

IF EXISTS ( 
SELECT 库存数量 
FROM 商品库存表 
WHERE 商品编号 IN (SELECT 商品编号 FROM INSERTED) AND 
库存数量 <= 0 
) 
BEGIN 
--返回错误提示 
RAISERROR('错误！该商品库存小于等于0，不能销售!',16,1) --返回用户定义的错误信息
--回滚事务 
ROLLBACK 
RETURN 
END 

--对合法的数据进行处理 

--强制执行下列语句，保证业务规则 
UPDATE 商品销售表 
SET 销售金额 = 销售数量 * 销售单价 
WHERE 商品编号 IN (SELECT 商品编号 FROM INSERTED) 

DECLARE @商品编号 CHAR(6) 
SET @商品编号 = (SELECT 商品编号 FROM INSERTED) 

DECLARE @销售数量 MONEY 
SET @销售数量 = (SELECT 销售数量 FROM INSERTED) 

UPDATE 商品库存表 
SET 库存数量 = 库存数量 - @销售数量, 
库存金额 = (库存数量 - @销售数量)*库存单价 
WHERE 商品编号 = @商品编号 
COMMIT TRANSACTION 
GO 
--测试
INSERT INTO 商品销售表(商品编号,商品名称,购货商,销售数量,销售单价,销售金额) 
values('100000' ,'红塔山','利群',10,12,120)
INSERT INTO 商品销售表(商品编号,商品名称,购货商,销售数量,销售单价,销售金额) 
values('100001','将军','家家悦',10,22,1200)
INSERT INTO 商品销售表(商品编号,商品名称,购货商,销售数量,销售单价,销售金额) 
values('100004','红河','家家悦',10,22,220)

INSERT INTO 商品销售表(商品编号,商品名称,购货商,销售数量,销售单价,销售金额) 
values('100003','玉溪','家家悦',10,22,220)
--以下18题是为下一个实验做准备的
--18.创建借书表：lendt(bno（索书号）,sno（学号）,ldate（借阅日期）,rdate（应还日期），relend（是否续借）)
CREATE TABLE LENDT(
	BNO CHAR(9) NOT NULL,
	SNO CHAR(9) NOT NULL,
	LDATE DATETIME ,
	RDATE DATETIME,
	RELEND CHAR (2))

--请为该表增加四个约束条件：
--（1）增加主码约束（bno,sno）
ALTER TABLE LENDT
ADD CONSTRAINT PK PRIMARY KEY(BNO,SNO)
--（2）为借阅日期增加默认值约束，默认值为当前日期
ALTER TABLE LENDT
ADD CONSTRAINT C_GETDAY DEFAULT GETDATE() FOR LDATE
--（3）为应还日期增加默认值约束，默认值为当前日期+30天
ALTER TABLE LENDT
ADD CONSTRAINT C_ADDDAY DEFAULT DATEADD(DAY,30,GETDATE())for RDATE
--（4）为是否续借增加默认值约束，默认值为否
ALTER TABLE LENDT
ADD CONSTRAINT C_RELEND DEFAULT '否' FOR RELEND 
--19．建立教师表（教工编号，姓名，专业，职称，工资）和工资变化表（教工编号，原工资，新工资），
--设计触发器实现教授的工资不得低于4000元，如果低于4000元则自动改为4000元。
CREATE TABLE TEACHER(
	TNO CHAR(9) PRIMARY KEY,
	TNAME VARCHAR(20),
	ZY VARCHAR(20),
	ZC VARCHAR(20),
	SALARY INT);

CREATE TABLE SALARY_CHANGE(
	TNO CHAR(9),
	OLD_SALARY INT ,
	NEW_SALARY INT)

GO 
CREATE TRIGGER T1
ON TEACHER 
FOR INSERT,UPDATE
AS
BEGIN
	DECLARE @SA INT ,@JOB CHAR(20),@TNO CHAR(9)
	SELECT @JOB=ZC,@SA=SALARY,@TNO=TNO FROM INSERTED
	IF(@JOB='教授'AND @SA<4000)
	UPDATE TEACHER 
	SET SALARY=4000
	WHERE TNO=@TNO
END
--测试
insert into teacher values('200199801','丁源','计算机','教授',8500)/*正常执行,触发器不执行*/
insert into teacher values('200199802','王伟源','计算机','教授',3500)
--20.	使用第19题的两个表设计触发器实现如果员工的工资发生变化则向工资变化表插入一条记录，包含教工编号，原工资，新工资。
create trigger tri20 on teacher
after update
as
begin
declare @a char(9), @b int, @c int
select @a = tno, @b = salary from deleted
select @c = salary from inserted
if (@b<>@c) 
		insert into salary_change
		values(@a, @b, @c)
end
--测试
update teacher
set salary=salary+200 where tno='200199802'
--21. 针对northwind数据库实现触发器：每个员工每天处理订单的数量不能超过100，如果超出100个则拒绝处理，并提示“处理订单数量超出限额”
use Northwind;
go
create trigger order_limit
on Orders
after insert
as
	declare @EID int
	
begin 
	select @EID = EmployeeID from inserted
	if (select count(*) from Orders where Orders.RequiredDate = getdate() and Orders.EmployeeID = @EID ) >= 100
	begin
		rollback
		print'处理订单数量超出限额'
	end
end
--22.针对northwind数据库实现触发器：给orders表添加zje（订单总金额）属性列，要求保留两位小数，设置触发器实现当产生了新的订单明细之后将总金额更新到到订单表里
alter table orders
add zje numeric(18, 2)

create trigger cal_sum
on [Order Details]
for insert
as 
declare @OID int
declare @sum numeric(18, 2)
begin 
	select @OID = OrderID from inserted
	set @sum = (select sum(UnitPrice * Quantity * (1 - Discount)) from Order_Details where Order_Details.OrderID = @OID)
	update orders set zje = @sum where OrderID = @OID
end

实验7

--使用数据库student，完成以下操作，并写出相应的代码
--1.	用函数实现：求某个专业选修了某门课程的学生人数，并调用函数求出计算机系“数据库”课程的选课人数。
create function tongji(@dept as varchar(8),@cname  as varchar(20))
returns int
begin
declare @sum int
select @sum=count(*)
from sc,course,student
where sc.cno=course.cno and sc.sno=student.sno and course.cname=@cname and student.sdept=@dept
return @sum
end
--测试
select dbo.tongji('CS','数据库')as 人数 
--2.	用内嵌表值函数实现：查询某个专业所有学生所选的每门课的平均成绩；调用该函数求出计算机系的所有课程的平均成绩。
create function cavg2(@dept as varchar(8))
returns table 
as return(
select course.cno,avg(grade) 平均成绩
from sc,course,student
where sc.cno=course.cno and sc.sno=student.sno and student.sdept=@dept
group by course.cno)
--测试
select * from  dbo.cavg2('CS')
--3.	创建多语句表值函数，通过学号作为实参调用该函数，可显示该学生的姓名以及各门课的成绩和学分，
--调用该函数求出“200515002”的各门课成绩和学分。
create function gstu (@no char(9)) returns @score table 
( xs_no char(9) ,
xs_name varchar(8) ,
kc_name  varchar(20) ,
cj int ,
xf numeric(2,1) ) 
as 
begin
insert @score  select s.sno,s.sname,c.cname,sc.grade,c.credit  
from student s,course  c, sc
where s.sno=sc.sno AND c.cno=sc.cno
and  s.sno=@no 
return 
end
--测试
SELECT * FROM gstu ('200515002')


--4.	编写一个存储过程，统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，
--其输入参数是课程号，输出的是各级别人数及及格率，及格率的形式是90.25%，执行存储过程，在消息区显示1号课程的统计信息。
create procedure p1
@cno char(4),
@great int output,
@good int output,
@medium int output,
@pass int output,
@passper varchar(10) output
as
	declare @sum float
	select @great=count(*)	from sc where cno=@cno and grade between 90 and 100
	select @good=count(*)	from sc where cno=@cno and grade between 80 and 89
	select @medium=count(*)	from sc where cno=@cno and grade between 70 and 79
	select @pass=count(*)	from sc where cno=@cno and grade between 60 and 69
	select @sum=count(*)	from sc where cno=@cno
	select @passper = str(cast((@great+@good+@medium+@pass)*100/@sum as numeric(5,2)))
	--select @passper=(str(cast((@great)*100.0/@sum*1.0 as ) ) +'%')
declare	@gr int,@go int,@me int,@pa int,@pap varchar(10)
exec p1 '1' ,
@gr output,
@go  output,
@me  output,
@pa output,
@pap output
print '优秀人数'+str(@gr) 
print '良好人数'+str(@go)  
print '中等人数'+str(@me) 
print '及格人数'+str(@pa) 
print '及格率'+@pap+'%'


--5.	创建一个带有输入参数的存储过程，该存储过程根据传入的学生名字，查询其选修的课程名和成绩，
--执行存储过程，在消息区显示赵箐箐的相关信息。
create procedure p5 @sname nchar(6)
as
declare @cname char(20) , @grade int 
declare c scroll cursor for
select	course.cname,sc.grade	
from sc,student,course	
where sc.cno=course.cno and sc.sno=student.sno and student.sname=@sname
open c
print '课程名'+'                    '+'成绩'
fetch next from c into @cname,@grade
while @@FETCH_STATUS=0
begin
print @cname+'      '+cast(@grade as char(4))
fetch next from c into @cname,@grade
end
close c
deallocate c
--测试
exec p5 '赵菁菁'

--6.	以基本表 course为基础，完成如下操作
--生成显示如下报表形式的游标：报表首先列出学生的学号和姓名，然后在此学生下，
--列出其所选的全部课程的课程号、课程名和学分；依此类推，直到列出全部学生。
declare @sno char(9), @sname nchar(6)
declare @cno char(4),@cname char(20),@credit int
declare c1 scroll cursor	for
select student.sno,student.sname
from student
open c1
fetch next from c1 into @sno,@sname
while @@FETCH_STATUS=0
begin
print @sno+'    '+@sname
print'_______________________________________'
print '课程号'+'    '+'课程名'+'            '+'学分'
declare c2 scroll cursor for
select course.cno,course.cname,course.credit
from course,sc
where course.cno=sc.cno and sc.sno=@sno
open c2
fetch next from c2 into @cno,@cname,@credit
while @@FETCH_STATUS=0
begin 
print @cno+'      '+@cname+cast(@credit as char(4))
fetch next from c2 into @cno,@cname,@credit
end
close c2
deallocate c2
print'======================================='
fetch next from c1 into @sno,@sname
end
close c1
deallocate c1

--7. 请设计一个存储过程实现下列功能：
--判断某个专业某门课程成绩排名为n的学生的成绩是否低于该门课程的平均分，如果低于平均分
--，则将其成绩改为平均分，否则输出学号、姓名、课程号、课程名、成绩。（提示：可以在存储过程内部使用游标）。
create procedure ex7 @dept varchar(8),@cno char(4),@n int
as
declare @avggrade int
select @avggrade=(select avg(grade)	from sc	where cno=@cno)
declare c scroll cursor for
select student.sno,sname,sc.cno,cname,grade from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and course.cno=@cno and student.sdept=@dept 
order by grade desc
open c
declare @sno char(9),@sname nchar(6),@cno1  char(4),@cname varchar(20),@grade int
fetch absolute @n from c into @sno,@sname,@cno1,@cname,@grade
if @grade>@avggrade
print @sno+'   '+@sname+'    '+@cno1+'    '+@cname+'    '+cast(@grade as char(4))
else
update sc
set grade=@avggrade
where current of c
close c
deallocate c
--测试
exec  ex7 'CS', '1',3
--8.	对student数据库设计存储过程，实现将某门课程成绩低于课程平均成绩的学生成绩都加上3分。（提示可以使用存储过程内部使用游标）
--方法一：
create proc addgrade  @cno char(4)
as
	declare @avergrade decimal(4,1)
	select @avergrade = AVG(grade)
	from sc
	where cno = @cno
	update sc
	set grade = grade+3
	where cno = @cno and grade <= @avergrade
go

--测试：
select * from sc where cno='2'
exec addgrade '2'
--方法二：
create procedure addgrade1 @cno char(4)
as
declare @avg decimal(4,1),@sno char(9),@grade int
set @avg=(select avg(grade) from sc where cno=@cno)
declare c scroll cursor for 
select sno,grade from sc where  cno=@cno
open c
fetch next from c into @sno,@grade
while(@@FETCH_STATUS=0)
begin
	if(@grade<@avg)
	update sc set grade=@grade+3 where current of c
	fetch next from c into @sno,@grade
end
close c
deallocate c
--测试
select * from sc where cno='2'
exec addgrade1 '2'
--8.	对student数据库设计存储过程，实现将某门课程成绩低于课程平均成绩的学生成绩都加上3分。（提示可以使用存储过程内部使用游标）
--方法一：
create proc addgrade  @cno char(4)
as
	declare @avergrade decimal(4,1)
	select @avergrade = AVG(grade)
	from sc
	where cno = @cno
	update sc
	set grade = grade+3
	where cno = @cno and grade <= @avergrade
go

--测试：
select * from sc where cno='2'
exec addgrade '2'
--方法二：
create procedure addgrade1 @cno char(4)
as
declare @avg decimal(4,1),@sno char(9),@grade int
set @avg=(select avg(grade) from sc where cno=@cno)
declare c scroll cursor for 
select sno,grade from sc where  cno=@cno
open c
fetch next from c into @sno,@grade
while(@@FETCH_STATUS=0)
begin
	if(@grade<@avg)
	update sc set grade=@grade+3 where current of c
	fetch next from c into @sno,@grade
end
close c
deallocate c
--测试
select * from sc where cno='2'
exec addgrade1 '2'
--9.	设计存储过程实现学生转专业功能：某个学生（学号）在转专业时，如果想转入的专业是计算机专业那么要求该学生的平均成绩必须超过95分，否则不允许转专业，并将转专业的信息插入到一个转专业的表里，changesd(学号，原专业，新专业，平均成绩)
create table changesd
(学号 char(9),原专业 varchar(8),新专业 varchar(8),平均成绩 numeric(4,1))

create procedure change @sno char(9),@n_sdept varchar(8)
as
declare @avg numeric(4,1),@o_sdept varchar(8)
select @avg=avg(Grade)
from sc
where  Sno=@sno
select @o_sdept=Sdept
from student
where Sno=@sno
if(@n_sdept='CS' and @avg<=95)
print '成绩没超过95分，无法转入计算机专业'
else
begin
update student
set Sdept=@n_sdept
where Sno=@sno
insert into changesd
values(@sno,@o_sdept,@n_sdept,@avg)
end
--测试
exec change '200515004','CS'
--提示：成绩没超过95分，无法转入计算机专业
exec change '200515004','MA'
--成功
--10.	现有图书管理数据库， 其中包含如下几个表：
--读者表：reader(学号，姓名，性别，余额)
--借书表：lend（学号，书号，借书日期，应还日期，是否续借）
--欠款表：debt(学号，日期，欠款金额)
--还书表：return(学号，书号，还书日期) 
--请设计一个存储过程实现续借或还书操作，具体要求如下：
--只有没有超期的书才可以续借（借书和续借时间都为30天），并修改应还日期，否则只能还书；还书时删除借书表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，并且对超期图书，按照超期的天数计算出罚款金额（每天每本书罚款0.1元），并将罚款信息插入到欠款表中，同时将罚款从读者表的余额里扣除。
use 图书管理--已建数据库
create table reader
(学号 char(9),姓名 varchar(10),性别 char(2),余额 money)
create table lend
(学号 char(9),书号 char(20),借书日期 date,应还日期 date,是否续借 varchar(6))

create table debt
(学号 char(9),日期 date,欠款金额 money)

create table [return]
(学号 char(9),书号 char(20),还书日期 date)
create procedure return_book @sno char(9),@bno char(20)
as
declare @rdate date,@xujie varchar(6),@day int
select @rdate=应还日期,@xujie=是否续借
from lend
where 学号=@sno and 书号=@bno
 if (@xujie='续借' and @rdate>=getdate() ) 
 update lend set 应还日期=dateadd(day,30,应还日期)
  else begin
       delete from lend
	   where 学号=@sno and 书号=@bno
	   insert into [return] values (@sno,@bno,getdate())
	   end
if(@rdate<getdate())
begin
set @day=datediff(day,@rdate,getdate())
insert into debt values(@sno,getdate(),@day*0.1)
update reader set 余额=余额-@day*0.1
where 学号=@sno 
end
--测试
exec return_book '201900801','12345678'
exec return_book '201900801','112345678'
exec return_book '201900802','12345678'