SQL语句练习题

一、表结构

----------------------  创建数据库 school 脚本 ---------------------------  

--drop database school
create database school    --创建数据库
go

/*CREATE DATABASE school
ON 
( NAME = school_dat,
   FILENAME = 'd:\school1.mdf',
   SIZE = 2,
   MAXSIZE = 4,
   FILEGROWTH = 1 )
LOG ON
( NAME =school_log,
   FILENAME = 'd:\school1.ldf',
   SIZE = 2MB,
   MAXSIZE = 3MB,
   FILEGROWTH = 1MB ) */

use school   --进入 school 数据库
go

EXEC sp_addtype ud_sno,'char(6)'   
go

--创建表
create table Student ( 
   Sno ud_sno , 
   Sname char(10) not null unique ,
   Ssex char(2) check (ssex='男' or ssex='女') ,
   Sage smallint check(sage>16) ,
   Sdept char(10)  not null default 'JSJ' ,
   
   primary key (sno)
 ) 
create index ix_student_sname ON student(sname)

create table course( 
   Cno char(4) ,
   Cname char(16) ,
   Cpno  char(4) ,
   Ccredit int check (Ccredit >=0 and Ccredit<=5),

   check( cno<>cpno) , --约束
   primary key (cno)
 )
create index ix_course_cpno ON course(cpno)
 
create table SC( 
  Sno ud_sno ,
  Cno char(4) ,
  Grade int check(grade<=100) ,

  constraint pk_sc primary key (sno,cno),
  foreign key (sno) references student(sno) ,
  foreign key (cno) references course(cno) ,
 )
create index ix_sc_cno ON sc(cno)
 
--添加数据

insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0003','陈小明','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0004','杨秀红','女',21,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0001','周志林','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0009','钱明明','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0002','李文庆','男',23,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0081','刘亭','女',  22,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0091','贺秋雪','女',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0078','王振','男',  21,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0092','赵三','男',  22,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8001','张华','男',23,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8002','赵颖','女',21,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8003','钱凯','男',22,'JSJ')
insert into student (sno,sname,ssex,sage,sdept) values ('8004','王华','男',21,'SX')
insert into student (sno,sname,ssex,sage) values ('8005','张英','女',21)
insert into student (sno,sname,ssex,sage) values ('8006','赵章','女',22)
insert into student (sno,sname,ssex,sage) values ('8007','钱利','男',23)
insert into student (sno,sname,ssex,sage) values ('8008','王铁','男',21)
insert into student (sno,sname,ssex,sage) values ('8009','张明','男',22)



insert into course(Cno,Cname,Cpno,Ccredit) values ('1001','高等数学','',5)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1002','离散数学','1001',3)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1003','程序设计','',5)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1004','数据结构','1003',4)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1005','数据库原理','1004',4)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1006','操作系统','1004',5)

insert into SC(Sno,Cno,Grade) values ('0002','1002',90)
insert into SC(Sno,Cno,Grade) values ('0002','1001',91)
insert into SC(Sno,Cno,Grade) values ('0002','1003',67)
insert into SC(Sno,Cno,Grade) values ('0002','1004',98)
insert into SC(Sno,Cno,Grade) values ('0001','1002',92)
insert into SC(Sno,Cno,Grade) values ('0001','1001',92)
insert into SC(Sno,Cno,Grade) values ('0001','1003',76)
insert into SC(Sno,Cno,Grade) values ('0003','1001',77)
insert into SC(Sno,Cno,Grade) values ('0001','1004',     91)
insert into SC(Sno,Cno,Grade) values ('0004','1001',     97)
insert into SC(Sno,Cno,Grade) values ('0004','1002',     78)
insert into SC(Sno,Cno,Grade) values ('0004','1003',     65)
insert into SC(Sno,Cno,Grade) values ('0004','1004',     89)
insert into SC(Sno,Cno,Grade) values ('0091','1001',     93)
insert into SC(Sno,Cno,Grade) values ('0091','1002',     87)
insert into SC(Sno,Cno,Grade) values ('0091','1003',     99)
insert into SC(Sno,Cno,Grade) values ('0091','1004',     95)
insert into SC(Sno,Cno,Grade) values ('0009','1001',     93)
insert into SC(Sno,Cno,Grade) values ('0009','1002',     88)
insert into SC(Sno,Cno,Grade) values ('0009','1003',     60)
insert into SC(Sno,Cno,Grade) values ('0009','1004',     83)
insert into SC(Sno,Cno,Grade) values ('0092','1001',     98)
insert into SC(Sno,Cno,Grade) values ('0092','1002',     86)
insert into SC(Sno,Cno) values ('0092','1003')

insert into SC(Sno,Cno,Grade) values ('0081','1001',     90)
insert into SC(Sno,Cno) values ('0081','1002')
insert into SC(Sno,Cno) values ('0081','1003')

insert into SC(Sno,Cno,Grade) values ('8001','1002',90)
insert into SC(Sno,Cno,Grade) values ('8001','1001',91)
insert into SC(Sno,Cno,Grade) values ('8001','1003',67)
insert into SC(Sno,Cno,Grade) values ('8001','1005',98)
insert into SC(Sno,Cno,Grade) values ('8002','1002',87)
insert into SC(Sno,Cno,Grade) values ('8002','1001',96)
insert into SC(Sno,Cno,Grade) values ('8002','1003',68)
insert into SC(Sno,Cno,Grade) values ('8002','1005',92)

insert into SC(Sno,Cno,Grade) values ('8003','1001',91)
insert into SC(Sno,Cno,Grade) values ('8003','1003',92)
insert into SC(Sno,Cno,Grade) values ('8003','1004',63)
insert into SC(Sno,Cno,Grade) values ('8003','1005',94)

insert into SC(Sno,Cno,Grade) values ('8004','1001',92)
insert into SC(Sno,Cno,Grade) values ('8004','1003',93)
insert into SC(Sno,Cno,Grade) values ('8004','1004',64)
insert into SC(Sno,Cno) values ('8004','1005')

insert into SC(Sno,Cno) values ('8006','1001')
insert into SC(Sno,Cno,Grade) values ('8006','1002',92)
insert into SC(Sno,Cno,Grade) values ('8006','1003',20)
insert into SC(Sno,Cno) values ('8006','1004')

insert into SC(Sno,Cno,Grade) values ('8007','1001',99)
insert into SC(Sno,Cno,Grade) values ('8007','1003',92)
insert into SC(Sno,Cno,Grade) values ('8007','1004',86)
insert into SC(Sno,Cno,Grade) values ('8007','1005',98)
insert into SC(Sno,Cno,Grade) values ('8007','1002',95)

insert into SC(Sno,Cno,Grade) values ('8008','1001',91)
insert into SC(Sno,Cno,Grade) values ('8008','1003',92)
insert into SC(Sno,Cno,Grade) values ('8008','1004',83)
insert into SC(Sno,Cno,Grade) values ('8008','1005',94)
insert into SC(Sno,Cno,Grade) values ('8008','1002',99)

insert into SC(Sno,Cno,Grade) values ('8009','1001',65)
insert into SC(Sno,Cno,Grade) values ('8009','1002',71)
insert into SC(Sno,Cno,Grade) values ('8009','1003',32)
insert into SC(Sno,Cno,Grade) values ('8009','1004',25)

二、单表查询

--一 单表
--1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

Select Sno ,Sname ,Sage 
From Student
Where ssex='女' and Sage between 19 and 21
Order by Sage DesC
--2查询姓名中第2个字为“明”字的学生学号、性别。
select Sno ,sname,ssex
from Student
where Sname like '%明%'
--3查询 1001课程没有成绩的学生学号、课程号
select sno, cno   
from  sc    
where grade is null and cno='1001'

--4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系及学号排列           X
select Sno,Sname from student

where (Sdept='JSJ' OR Sdept='SX' OR Sdept='WL')and Sage>25

--5按10分制查询学生的sno,cno,10分制成绩 
select sno,cno,grade*0.1 as A
from SC 
  -- （1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） 
--6查询 student 表中的学生共分布在那几个系中。（distinct） 
select distinct Sdept from student
--7查询0001号学生1001，1002课程的成绩。
SELECT grade
from sc
where Sno='0001' and Cno in('1001','1002')

三、统计

---二 统计
--1查询姓名中有“明”字的学生人数。
SELECT count(*) 
from student
where Sname like '%明%'
--2计算‘JSJ’系的平均年龄及最大年龄。
SELECT avg(Sage) AS A,max(Sage) AS B
from student
where sdept='JSJ'
--3查询学生中姓名为张明、赵英的人数                 ⭐X
SELECT count(*) 
from student
where Sname in('张明','赵英' ) 
--4计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列                          
SELECT Cno, SUM(grade) as 'sum',avg(grade)as 'avg',max(grade)as 'max',min(grade)as 'min' 
from sc 

GROUP BY Cno  ORDER BY avg(grade) DESC

--5 计算 1001,1002 课程的平均分。               X
SELECT cno,avg(grade)
from sc 
where Cno in ('1001','1002') ORDER BY Cno 
--6 查询平均分大于80分的学生学号及平均分 
select Sno , avg(grade)
from sc GROUP BY Sno HAVING avg(grade) >'80'
--7 统计选修课程超过 2 门的学生学号
SELECT Sno 
from sc GROUP BY Sno HAVING count(*) >'2'
--8 统计有10位成绩大于85分以上的课程号。
SELECT Cno 
FROM sc 
WHERE grade>'85' GROUP BY Cno HAVING count(*)='10'
--9 统计平均分不及格的学生学号
SELECT Sno
from sc GROUP BY Sno HAVING avg(grade) <'60'
--10 统计有大于两门课不及格的学生学号                 X
SELECT Sno 
from sc
WHERE grade<'60' GROUP BY Sno HAVING count(*)>'2'

四、其他更新

查询年龄最低的学生姓名
select sname
from student
where sage <=all(select sage
                from student
                )
                
select sname from student where sage=(
select min(sage ) from student
)

查询平均分最高的学生学号
select sno    from    sc group by sno having 
avg(grade)>=all(select avg(select avg(grade) from sc
                            group by sno)

查询每个系小于自己系的平均年龄的学生姓名

select sname from student X where sage<
(
select avg(sage)
from student Y where Y.sno=X.sno
)

查询所有未修1001号课程的学生姓名
select sname from student
where not exists 
(
select *from SC
where Cno='1001'and SC,Sno=student.Sno
)

查询选修了全部课程的学生学号
改为：不存在一门课程这个学生没有选修
select sno  from student where NOT EXTSTS(
    select * from where not exists(
    select *from sc where cno=course.cno and sno=student.sno
    )
    )
    
查询选修了同时1号和2号课程的学生姓名
select sname from student
where not exists (
    select *from course where
    cno in ('1','2') and not exists
    (select*from sc
    where sno= student.sno and 
    cno =course.cno
    
    )
)

 1查询1001课程前3名的学生学号及成绩
 select top 3 sno,grade from sc
 where cno='1001' order by grade DESC
 2查询平均分最后一名的学生学号
 select top 1 sno, avg(grade) from sc
 group by sno
 order by avg(grade)
 
 
 select sno from sc group by sno
 having avg(grade)=
 (select top 1 avg(grade) from sc)
    group by sno order by avg(grade))
    
并
查询JSJ系的学生或者年龄不大于19岁的学生
select *from student where sdept='JSJ'
UNION
select *from student where Sage<=19

交
查询JSJ系的学生及年龄不大于19岁的学生
select *from student where sdept='JSJ'
interSect
select *from student where sage<=19

差
查询JSJ系的学生及年龄不大于19岁的学生的差集
select *from student where sdept='JSJ'
except
select *from student where sage <=19

查询全部学生都及格的课程号4种方法
1select cno from sc group by cno having min(grade)>=60
2select cno from course where cno not in 
(select cno from sc where grade <60)
3select cno from course where not exists 
(select *from sc where sc.cno=course.cno and grade<60
)
4select cno from course 
    except 
    select cno from sc where grade<60
    
查询有2门80分以上成绩的学生学号(from的子查询)
select sno from 
(select sno,COUNT(*) as cnt from sc where Grade>80 group by sno) as temp
where cnt>2

select sno cno grade
    case when grade>=90 then '优'
    case when grade>=80 then '良'
    case when grade>=70 then '中'
    case when grade>=60 then '及格'
    else'不及格'end
from sc

with 子句
with Fail AS
    (SELECT sno,cno,from sc where Score<60)
select s.sno,max(Sname)
From S join Fail ON S.sno=Fail.sno
group by S.Sno
HAving count(*)>2

生成临时表的方法
select sno,cno into #tmp1                 --没有#号是永久表
    from SC where Score <60

查询平均分第一名的学生学号
select sno,avg(grade) as avg1 into #tmpAvg from sc
group by sno

select sno from #tmpAvg where avg1=(
    select max(avg1) from #tmpAvg)
    
插入子查询
将每一个系的学生平均年龄存入DEPTAGE表
insert into DeptAge (sdept,avgAge)
select Sdept, avg(Sage)From student
Group by Sdept

把0001号学生的1002课程的成绩增加5分
update Sc set grade =grade+5
    where Sno='0001'And Cno='1002'
    
将李文庆的1001课的成绩减去10分
update SC set grade=grade-10
where Cno='1001'And
Sno=(select Sno from student Where Sname='李文庆')

把0001号学生的1002课程记录删除
DELETE From Sc where Sno='0001' and cno='1002'

delete from sc
where cno='1001' and sno=(select sno from student where sname='李文庆')

把编译原理课程的成绩都减去1分。
update SC set grade=grade-1
    where cno in 
(select cno from course where cname='编译原理')


3.9-2 SQL语言(单表) 设某电子商务公司的数据库中四张基本表的结构如下(表中各属性的含义及各表的主码见第2章练习题四): 

Customers(Cno, Cname, Csex, Cage, Caddress, Mphone, Email), 

Goods(Gno, Gname, Gtype, Price, Manufac), 

Sells(Sno, Sdate, Saddress, Cno, IsPay),    Detail(Sno, Gno, Quantity) 

其中各表中各属性的含义如下,且假定在客户付款前IsPay取值为'N',付款后IsPay取值为'Y'。 客户表中的各属性分别为:客户编号,客户姓名,客户性别,客户年龄,客户地址,手机号,电子邮箱;商品表中的各属性分别为:商品编号,商品名称,商品类别,价格,生产商;销售单表中的各属性分别为:销售单号,销售日期,送货地址,客户编号,是否已付款;销售明细表中的各属性分别为:销售单号,商品编号,数量。 

请用T-SQL语言实现下列要求:

4 找出“海尔”公司生产的所有商品的名称和价格,并按价格降序排列。 

5. 找出“华为”公司生产的商品名称中有“手机”两字的所有商品的名称和价格。 

6. 找出在售商品的品种总数。 

7. 找出各大类商品中各种商品的品种数和平均价格。

8. 找出各大类商品中各种商品的平均价格大于1000元的商品类别。

正确答案：
4、SELECT Gname, Price FROM Goods WHERE Manufac='海尔' ORDER BY Price DESC 
5、SELECT Gname, Price FROM Goods WHERE Manufac='华为' AND Gname LIKE '%手机%' 
6、SELECT COUNT(*) FROM Goods 
7、SELECT Gtype, COUNT(*), AVG(Price) FROM Goods GROUP BY Gtype 
8、SELECT Gtype FROM Goods GROUP BY Gtype HAVING AVG(Price)>1000


查询每门课都及格的课程号
标准答案：select cno from sc group by sno having min(grade)>60
我的答案：
select DISTINCT cno
from sc
except
select DISTINCT cno 
from SC
where grade<60 


3.18设有关系R(A, B)和S(B, C),则下列SQL查询语句中必定含有语法错误的是( )。
A、SELECT A,B FROM R GROUP BY A       --group 后面没有B，所以select不能有B
B、SELECT A,B FROM R,S WHERE R.A=S.C   -- select 后面应是S.B或R.B
C、I SELECT COUNT(B) FROM R                
D、SELECT A FROM R WHERE B>=MAX(B)        --where 语句里不能有MAX


19使用带有IN谓词的子查询时,子查询的SELECT子句中最多可以指定( )个列。
A、1
B、2
C、3
D、任意多

选A

3.20使用嵌套查询时,当子查询的SELECT子句中出现多个列时,可以使用( )运算符。
A、=
B、>=
C、EXISTS
D、IN

选C A\B\D均为一列


3.9-3 SQL语言(连接)设某电子商务公司的数据库中四张基本表的结构如下(表中各属性的含义及各表的主码见第2章练习题四): 
 Customers(Cno, Cname, Csex, Cage, Caddress, Mphone, Email), 
Goods(Gno, Gname, Gtype, Price, Manufac), 
Sells(Sno, Sdate, Saddress, Cno, IsPay), Detail(Sno, Gno, Quantity) 
其中各表中各属性的含义如下,且假定在客户付款前IsPay取值为'N',付款后IsPay取值为'Y'。 
客户表中的各属性分别为:客户编号,客户姓名,客户性别,客户年龄,客户地址,手机号,电子邮箱;商品表中的各属性分别为:商品编号,商品名称,商品类别,价格,生产商;销售单表中的各属性分别为:销售单号,销售日期,送货地址,客户编号,是否已付款;销售明细表中的各属性分别为:销售单号,商品编号,数量。 
请用T-SQL语言实现下列要求: 
9 找出2015年5月份的所有未付款的销售单的编号、客户编号、客户姓名和手机号。 
select Sno,S,Cno Cname,Mphone
from sells S join cusotmers c on s.cno=c.cno
where Ispay='N'and sdate between'2015-05-01' and '2015-05-31'
10.找出每一个客户的编号、姓名、手机号以及他每次购物的日期和是否已付款信息,即使该客户没有购买过商品,也要输出他的编号、姓名和手机号。 
select C.cno,cname,Mphone,Sdate,IsPay from Customers C left join sells S on C.cno=S.cno 
11.找出每一张销售单的销售单号、销售日期、客户姓名和销售单总金额。 
select S.sno,max(sdate),max(cname),sum(quanity*price)
from sells S,cusotmers C,Detail D,Goods G
where s.cno=c.cno and s.sno=d.sno and d.gno=g.gno
group by s.sno

12.找出“TP-LINK”公司生产的商品名为“WR700N无线路由器”的销售总数量。
select sum(quantity) from detail D join Goods G on d.Gno=G.Gno
where Manfac='TP-LINk'and Gname='WR700N无线路由器'
另一种做法：
select sum(quanity) from detail where Gno In(select Gno from goods where manufac='TP-LINK'and Gname='WR700N无线路由器')

12找出“TP-LINK”公司生产的各种商品的销售总数量。
select detail.gno,sum(quantity)
from detail join goods on detail.gno=goods.gno
where Manufac='TP-LINK'
group by detail.gno

12找出每一个公司的各种商品的销售总数量。
select manufac,detail.gno,sum(quantity)
from detail join goods on detail.gno=goods.gno
group manufac,detail.gno

.9-4 SQL语言(嵌套、并交差) 设某电子商务公司的数据库中四张基本表的结构如下(表中各属性的含义及各表的主码见第2章练习题四): 

 Customers(Cno, Cname, Csex, Cage, Caddress, Mphone, Email), 

Goods(Gno, Gname, Gtype, Price, Manufac), 

Sells(Sno, Sdate, Saddress, Cno, IsPay), Detail(Sno, Gno, Quantity) 

其中各表中各属性的含义如下,且假定在客户付款前IsPay取值为'N',付款后IsPay取值为'Y'。 

客户表中的各属性分别为:客户编号,客户姓名,客户性别,客户年龄,客户地址,手机号,电子邮箱;商品表中的各属性分别为:商品编号,商品名称,商品类别,价格,生产商;销售单表中的各属性分别为:销售单号,销售日期,送货地址,客户编号,是否已付款;销售明细表中的各属性分别为:销售单号,商品编号,数量。 

请用T-SQL语言实现下列要求: 

13.找出仅仅注册但至今还没有购买过商品的客户编号。 
select cno from customers where cno=not in (select cno from sells)
或select cno from customers
except select cno from sells
 
14.找出2015年1月1日以后没有购买过商品的客户编号、客户姓名和手机号。 
select cno,cname,mphone from cusotmers
where cno not in (select cno from sells where sdate>'2015-01-01') 
15.找出同类商品中价格最低的商品编号、商品名称和生产商。 
select gno ,gname,manufac from goods A where price=(
select min(price)from goods B where B.Gtype=A.Gtype)
16.找出销售数量最多的商品的名称、价格和生产商。 
select gname ,price,manufac,from goods where gno in (
        select gno  from detail group by gno having sum(quantity)>=all(
        select sum(quanity)from detail group by gno)
)
或用top语句
select gno from detail group by having sum(quantity)=(
    select TOP 1 sum(quantity) from detail group by gno order by sum(quantity)desc)

17.找出同一张销售单中既有140010123号商品又有150020234号商品的所有销售单号。 
select sno from detail where gno='140010123'
interSect
select sno from detail where gno='150020234'

select sno from detail d where gno='140010123' and exists(
    select *from detail where sno=d.sno and gno='150020234'
)

select sno from detail where gno='140010123' and sno in (
    select sno from detail where gno ='150020234')

select sno from sells where not exists(       
    select *from goods where gno in ('140010123','150020234') and not exists(     
        select* from detail where detail.gno =goods.gno and detail.sno=sells.sno)    
)



18.找出购买过“奶粉”类商品中所有品种奶粉的客户编号。 
select sno from sells where not exists(
    select *from goods where gtype='奶粉'and not exists(
        select* from detail where detail.gno =goods.gno and detail.sno=sells.sno)
)

19.在FROM子句中使用子查询,重做第15题中的查询要求。


3.9-5 SQL语言(insert,update) 设某电子商务公司的数据库中四张基本表的结构如下(表中各属性的含义及各表的主码见第2章练习题四): 

 Customers(Cno, Cname, Csex, Cage, Caddress, Mphone, Email), 

Goods(Gno, Gname, Gtype, Price, Manufac), 

Sells(Sno, Sdate, Saddress, Cno, IsPay), Detail(Sno, Gno, Quantity) 

其中各表中各属性的含义如下,且假定在客户付款前IsPay取值为'N',付款后IsPay取值为'Y'。 

客户表中的各属性分别为:客户编号,客户姓名,客户性别,客户年龄,客户地址,手机号,电子邮箱;商品表中的各属性分别为:商品编号,商品名称,商品类别,价格,生产商;销售单表中的各属性分别为:销售单号,销售日期,送货地址,客户编号,是否已付款;销售明细表中的各属性分别为:销售单号,商品编号,数量。

 请用T-SQL语言实现下列要求: 

20.将商品表中“手机”类商品中所有品种手机的价格降价5%。
UPDATE Goods SET Price=0.95*Price WHERE Gtype='手机' 
21.删除商品表中商品编号为110050111的商品。 
DELETE FROM Goods WHERE Gno='110050111' 
22.在商品表中添加一种新商品('150050111', 'P8手机', '手机', 2499, '华为')。
insert into goods (Gno,Gname,Gtype,Price,Manufac)
            values('150050111', 'P8手机', '手机', 2499, '华为')


7.5.1 上机实验题：第二部分 实验七：数据查询 临时表，case。表为student,sc,course 表。
1、查询学生的学号、课程号、成绩等地，成绩等地按如下规定: 95以上 A+, 90-95 A,
85-89 B+, 80-84 B, 75-79 C+, 70-74 C,65-69 D+,60-64 D,60分以下 F。
select sno,cno,grade,
case when grade>=95 then 'A+'
    when grade>=90 then 'A'
    when grade>=85 then 'B+'
    when grade>=80 then 'B'
    when grade>=75 then 'C+'
    when grade>=70 then 'C'
    when grade>=65 then 'D+'
    when grade>=60 then 'D'
    else '不及格' end
from sc


2、查询每个系科男女生的比率（如男20人，女 40，则是20/40=0.5使用临时表）。
select sdept count(*) as cnt1 into #tmp1 from student where ssex='男' group by sdept
select sdept count(*) as cnt2 into #tmp2 from student where ssex='女' group by sdept

select #temp1.sdept,1.0*cnt1/cnt2 from #tmp1 join #tmp2 on #tmp1.sdept=#tmp2.sdept

或者：
select sdept sum(case when Ssex='男' then 1 else 0 end)*1.0/sum (case when Ssex='女' then 1 else 0 end )
from student    
group by sdept



6.1 上机实验题：第二部分 实验六： 数据更新 insert。表为student,sc,course 表。
一 insert
1 写出把下述学生的信息添加到student表中的命令。
学号 姓名 性别 年龄 系科
4001 赵茵 男 20 SX
4002 杨华 女 21
我的答案：

insert into student (Sno,Sname,Ssex,Sage,Sdept)

 values('4001', '赵茵', '男', 20, 'SX', null)

 values('4002', '杨华', '女', 21,  null,null)


6.2 上机实验题：第二部分 实验六： 数据更新 insert。表为student,sc,course 表。
一 insert
2 批量插入数据
1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade ，数据类型参照student,sc 表。
2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到新表 sc_name 中。
3） 察看 sc_name 表的数据
我的答案：

1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade ，数据类型参照student,sc 表。

create table sc_name ( 

   Sno ud_sno , 

   Sname char(10) not null unique ,

   Ssex char(2) check (ssex='男' or ssex='女') ,

   Cno char(4) ,

   Grade int check(grade<=100) ,

   

   primary key (sno)

 ) 



2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到新表 sc_name 中。

insert into sc_name

 select sno,sname,ssex from student

insert into sc_name

 select cno from course

insert into sc_name

 select grade from sc

3） 察看 sc_name 表的数据

select *

from sc_name


6.3 上机实验题：第二部分 实验六： 数据更新 update。 表为student,sc,course 表。
二 Update
1 修改 0001 学生的系科为: JSJ
2 把陈小明的年龄加1岁，性别改为女。
2 修改李文庆的1001课程的成绩为 93 分
3 把“数据库原理”课的成绩减去1分
我的答案：

1 修改 0001 学生的系科为: JSJ

update student set Sdept='JSJ'

 where Sno='0001'

2 把陈小明的年龄加1岁，性别改为女。

update student set sage=sage+1

 where Sname='陈小明'

update student set Ssex='女'

 where Sname='陈小明'

2 修改李文庆的1001课程的成绩为 93 分

update sc set grade='93'

 where Sno=(select sno from student where sname='李文庆')

3 把“数据库原理”课的成绩减去1分

update sc set grade=grade-1

 where Cno in (select cno from course where cname='数据库原理')
 
 6.4 上机实验题：第二部分 实验六： 数据更新 delete。 表为student,sc,course 表。
三 Delete
1 删除所有 JSJ 系的男生
2 删除“数据库原理”的课的选课纪录
我的答案：

1 删除所有 JSJ 系的男生

delete from Student

 where  sdept='JSJ' and ssex='男'

2 删除“数据库原理”的课的选课纪录

delete from SC

 where Cno='数据库原理'

上机实验题:第二部分 实验一、实验二、实验三。做完实验,把所有命令写到脚本文件保存,文件名自定。

3.12-2 上机实验题:第二部分 实验七: 一、单表,把所有命令写到脚本文件保存,文件名自定。 表为student,sc,course 表。
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
2查询姓名中第2个字为“明”字的学生学号、性别。
3查询 1001课程没有成绩的学生学号、课程号
4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名,结果按系及学号排列
5按10分制查询学生的sno,cno,10分制成绩
(1-10分 为1 ,11-20分为2 ,30-39分为3,。。。90-100为10)
6查询 student 表中的学生共分布在那几个系中。(distinct)
7查询0001号学生1001,1002课程的成绩。

3.12-3 上机实验题:第二部分 实验七:二、统计,把所有命令写到脚本文件保存,文件名自定。 表为student,sc,course 表。
1查询姓名中有“明”字的学生人数。
2计算‘JSJ’系的平均年龄及最大年龄。
3查询学生中姓名为张明、赵英的人数
select sname,count(*) from student 
    where sname in('张明','赵英') group by sname
4计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列
5 计算 1001,1002 课程的平均分。
6 查询平均分大于80分的学生学号及平均分
7 统计选修课程超过 2 门的学生学号
8 统计有10位成绩大于85分以上的课程号。
9 统计平均分不及格的学生学号
select sc.sno from student join sc on student.sno=sc.sno
group by sc.sno having avg(grade)<60

10 统计有大于两门课不及格的学生学号


3.12-4 上机实验题:第二部分 实验七:三、连接,把所有命令写到脚本文件保存,文件名自定。 表为student,sc,course 表。
1查询 JSJ 系的学生选修的课程号
2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法)
3查询数据库原理不及格的学生学号及成绩
4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法)
5查询平均分不及格的学生的学号,姓名,平均分。
select sc.sno,min(sname),avg(grade) from student join sc on student.sno=sc.sno
group by sc.sno having avg(grade)<60
6查询女学生平均分高于75分的学生姓名。
7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出,不能遗漏)


3.12-5 上机实验题:第二部分 实验七:四、嵌套、相关及其他,把所有命令写到脚本文件保存,文件名自定。 表为student,sc,course 表。
1 查询平均分不及格的学生人数
select count(*) from student where sno in (
    select sno from sc group by sno having avg(grade)<60
    )
2 查询没有选修1002 课程的学生的学生姓名
3 查询平均分最高的学生学号及平均分 (2种方法 TOP , any , all)
4查询同时选修了1001,1002课程的学生姓名。
*5 查询没有选修1001,1002课程的学生姓名。
6 查询大于1001课程平均分的学生学号
7 查询1002课程第一名的学生学号(2种方法)
8 查询平均分前三名的学生学号
9 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
10 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名
11 查询每门课程成绩都高于该门课程平均分的学生学号
    所有学号    (查询有低于课程平均分的学生学号)
    select sno from student
    except
    select  sno  from sc A  where grade<(
            select avg(grade) from sc B where B.cno=A.cno)

    select sno from student where sno not in (
    select  sno  from sc A  where grade<(
            select avg(grade) from sc B where B.cno=A.cno)
    )


12 查询大于本系科平均年龄的学生姓名

13 查询1001课程第三名的学生学号
前三名-前两名
select top 3 sno from sc where cno='1001' order by grade desc
except
select top 2 sno from sc where cno='1001' order by grade desc
*14 查询每个学生的学号、姓名、系科，系科用中文显示。JSJ 显示 计算机，SX 显示 数学，WL 显示 物理, 否则显示 其他

建立JSJ系的学生的视图
create view student_jsj  AS
    select Sno,Sname,Sage From student
    where Sdept ='JSJ'

建立一个反映学生出生年份的视图
create view student_birth AS
    select Sno,Sname,2000-Sage as birth FROM
    
建立学生各系科平均年龄的视图
create view V_1 (Sdept,average) AS
    select Sdept,AVG(sage) from student
    group by sdept
    
    
    
update student_jsj Set Sage=21
    where Sname ='李文庆'
若李文庆不是计算机学生
create View student_jsj AS
    select Sno,Sname,Sage,From student
    where Sdept='JSJ'
    with check option
    (with check option)拥有这句 若李文庆不是计算机学生不能更改
    
    
查询每门课的及格率@
create view v1(cno,cntAll)as
    select cno,count(*)from sc
    group by cno
    
create view v2(cno,cnt60)as
    select cno,count(*)from sc
    where grade >=60
    group by cno
select v1.cno,1.0*cnt60/cntAll
from v1 join v2 on v1.cno=v2.cno

临时表方法
Cast 方法

例一：把查询student的权限授予用户U1
grant select
    on (table) student TO U1
例二：把对student、course表的select，update，insert权限授予用户U2,U3
Grant select,insert,update
    on (table) student,course TO U2,U3
    
例三：把查询student表和修改学生学号的权限授予用户U4
grant select ,update(Sno)
    on (table) student TO U4


4.8 SQL语言，下列各题中所涉及到的基本表的结构见第3章练习题四。
Customers(Cno, Cname, Csex, Cage, Caddress, Mphone, Email),
Goods(Gno, Gname, Gtype, Price, Manufac),
Sells(Sno, Sdate, Saddress, Cno, IsPay), Detail(Sno, Gno, Quantity)
1. 对Customers表的客户名按升序创建一个非聚集索引。
create INDEX index_Customers_Cname ON Customers(Cname)
2. 对Goods表按商品类别升序和价格升序创建一个复合非聚集索引。
CREATE INDEX index Goods GtypePrice ON Goods(Gtype,Price)
3. 对Sells表按客户编号升序和销售日期降序创建一个复合非聚集索引。
CREATE INDEX index_Sells_CnoSdate ON Sells (Cno,Sdate,DESC)
4. 定义一个名为GoodsPhone的视图，该视图只包含“手机”大类商品的商品信息。
CREATE VIEW GoodsPhone
 AS SELECT * From Goods Where Gtype='手机'
5. 在视图GoodsPhone的基础上定义一个名为GoodsPhoneHuaWei的视图，该视图只包含“华为”公司生产的手机类商品的商品信息。
Create view goods where 
6. 定义一个名为SellsQuantity的视图，该视图有4列，分别是商品的名称、价格、生产商和该商品的销售总数量（销售总数量用Squantity表示）。
create view SellsQuantity (Gname,Price,Manufac,Squantity) AS
    Select Gname,min(Price),min(Manufac),sum(quantity)
    From Goods,detail,where Goods,Gno=Detail.Gno
    Group by gname,price,manufac

7. 利用第6题中定义的视图，找出销售数量最多的商品的名称、价格和生产商。
select Gname ,Price,Manufac from sellsQuantity
where Squantity>=all(select Squantity FROM sellsQuantity)

select Gname,Price,Manufac from sellsQuantity
where    Squantity =(select max(squantity) from sellsQuantity)

select gname ,price,manufac from sellsQuantity
where squantity=(select top 1 squantity )from sellsQuantity order by squantity desc

8. 定义一个名为SellsMoney的视图，该视图有6列，分别是销售单号、销售日期、客户姓名、客户性别、客户年龄和该销售单总金额（销售单总金额用Smoney表示）。
create view sellsMoney(sno,sdate,cname,csex,cage,sMoney)
select sells.sno,sdate,cname,csex,cage,sum(quantity*price    )
from sells,detail,goods,customers
where sells.sno=detail.sno and customers.cno=sells.cno and goods.gno=detail.gno
group by sells.sno,sdate,cname,csex,cage
9. 利用第8题中定义的视图，找出2014年全年的购买总金额超过10000元的客户的姓名、性别和年龄。
select cname,min(csex),min (cage)
from sellsMoney where sdate between '2014-1-1'and'2014-12-31'
group by cname having sum(sMoney)>10000

10.在视图GoodsPhone中添加一种新商品('150050111', 'P8手机', '手机', 2499, '华为')。
insert into goodsphone(Gno,Gname,Gtype,price,Manufac)
    values('150050111','P8手机','手机'，'2499','华为')





一.简答题（共3题,100.0分）
1
13.1 在School 数据库上完成实验，理解 rollback。  
1输入下列语句并执行 ,记录该学生的年龄。
Select * from student where sno=’0001’
 答：
2  执行下列语句序列A:
BEGIN TRANsaction  
    Update student set sage=sage+1 where sno=’0001’
    Select * from student where sno=’0002’
 此事务结束了吗？
 答：
3   执行：
 Select * from student where sno=’0001’
记录该学生的年龄。
思考：student 中的0001的年龄确实被更改了吗? 为什么?
答：
4 执行下列语句。
   ROLLBACK  TRANsaction
   然后再执行：
Select * from student where sno=’0001’
,   观察0001的年龄, 解释发生这种现象的原因。
答：

正确答案：


一、1  答：21   （每个人可能不同）
2   答：事务没有执行 commit 或 rollback ,所以没有结束。
3  答：22 ,0001的年龄增加了1岁。
4  答：21 ，回滚后，数据恢复。

213.2 在School 数据库上完成实验，理解 commit 。
1在查询分析器输入下列语句并执行 ,记录该学生的年龄。
Select * from student where sno=’0001’
答：
2 执行下列 语句序列A:
BEGIN TRANsaction
Update student set sage=sage+1 where sno=’0001’
Select * from student where sno=’0002’
3执行:
commit transaction
Select * from student where sno=’0001’
记录结果, 此时更改后的数据被永久保存了吗?
答：
正确答案：


1 答：21
3 答：22
314.1 在School 数据库上完成实验，理解锁的概念及锁的作用
一 利用帮助系统了解Sql-server 的下列语句的含义
1 设置锁的隔离级别
SET TRANSACTION ISOLATION LEVEL Serializable
执行这个语句的效果？
答：
二 观察封锁
1 执行语句序列A
BEGIN TRANsaction
Update student set sage=sage+1 where sno=’0001’
Select * from student where sno=’0002’
2 在SSMS中打开第二个连接(连接 school)[文件-连接], 输入下列语句:
1) select * from student where sno=’0002’
记录执行结果,说明原因。
答：
2）select * from student where sno=’0001’
记录执行结果,说明原因。（如上一步没有停止，则强行终止）
答：
执行 select * from student with(nolock) where sno=’0001’
记录执行结果,说明原因。（如上一步没有停止，则强行终止）
答：
3) update student set sname=’aaa’ where sno=’0002’
记录执行结果,说明原因。（如上一步没有停止，则强行终止）
答：
4) 强行终止上一步的命令，然后执行语句：
DBCC opentran
记录结果 ,思考：如何知道此事务是那一台计算机发出的？
答：
5）执行：
select * from student where sno=’0001’
记录执行结果,说明原因
答：
然后回到第一个连接中，执行语句:
commit Tran
观察并记录第二个连接窗口中的现象，说明原因
答：
正确答案：


0001加了X锁，0002 加了S锁。
一、1 本语句，封锁协议达到三级封锁协议，并且解决幻影读。
二/2
1) 答：能查到0002 学生信息，S锁上可以加S锁。
2）答：等待解锁，无法查询到数据。因为X锁上不能加 s 锁。
可以执行，能查到数据。
3) 答：无法完成更新操作，因为 S锁上不能加X锁。
4) 答：发现有一个事务活动得。
5）答： 等待中 0001解锁，无法完成查询
答：马上看到0001 的信息。








2.1 按步骤执行：
一 完全备份的建立与恢复
1建立完全备份
USE school
GO
BACKUP DATABASE school TO DISK='E:\SQL Server Management Studio\schooldata.bak'
查看备份是否生成？
2查看备份文件中的信息
RESTORE HEADERONLY FROM DISK='E:\SQL Server Management Studio\schooldata.bak'
看到了什么？
3恢复完全备份
1) 先删除数据库 School
USE Master
GO
DROP DATABASE school
2) 然后恢复.
RESTORE DATABASE school from DISK='E:\SQL Server Management Studio\schooldata.bak'
3): 查看 school 的student 中的数据
二 建立差异备份
1 建立备份
1） 制作数据文件备份 schoolDiff.bak
2） 把学号 7001， 姓名：王海，性别：男，年龄为23 的学生加入student
3） 制作school 的差异备份 ，存入schoolDiff.bak
BACKUP DATABASE school TO DISK='schoolDiff.bak' WITH DIFFERENTIAL
4) 把学号 7002， 姓名：赵燕，性别：女,年龄为22 的学生加入student
5) 制作school 的差异备份 ，存入schoolDiff.bak
BACKUP DATABASE school TO DISK='schoolDiff.bak' WITH DIFFERENTIAL
2查看备份文件 schoolDiff.bak 中的信息
3 删除 school 数据库
4 恢复数据库 school 到第2步状态
RESTORE DATABASE school from DISK='E:\SQL Server Management Studio\schoolDiff.bak'WITH file=1 NORECOVERY
RESTORE DATABASE school from DISK='E:\SQL Server Management Studio\schoolDiff.bak'WITH file=2
Select * from student
观察student 数据
5 恢复数据库 school 到最新状态
RESTORE DATABASE school from DISK='E:\SQL Server Management Studio\schoolDiff.bak'WITH file=1 NORECOVERY
RESTORE DATABASE school from DISK='E:\SQL Server Management Studio\schoolDiff.bak'WITH file=3
Select * from student
观察student 数据，新增的两人是否存在。
答：
思考: 如果仅执行下述恢复语句,能查看 student 的数据吗?

RESTORE DATABASE school from DISK='E:\SQL Server Management Studio\schoolDiff.bak'WITH file=1 NORECOVERY
Select * from student

不能 norecovery 还需要恢复差异备份或日志备份，数据库还需要恢复数据
recovery 表示恢复到正常状态