实验一 关系数据库标准语言SQL

【实验目的】

在给定的关系数据库管理系统Microsoft SQL Server环境下，通过实验学生能够：

1、MS SQL SERVER认识及其环境熟悉，了解T-SQL对标准SQL的扩充。

2、掌握T-SQL环境下数据定义包括数据库、基本表、视图、索引定义。

3、掌握T-SQL环境下数据操纵包括数据插入、删除、修改。

4、掌握T-SQL环境下数据查询及其各种变化。

5、理解T-SQL环境下数据控制。

【实验性质】

验证性实验

【实验内容】

create database qixin

use qixin

create table s
(
sno        char(2),
sname    varchar(6) not null,
status    int,
city    varchar(6),
primary key (sno)
)

create table p
(
pno        char(2),
pname    varchar(6) not null,
color    char(2),
weight    int,
primary key (pno)
)

create table j
(
jno        char(2),
jname    varchar(8) not null,
city    varchar(6),
primary key (jno)
)

create table spj
(
sno    char(2),
pno    char(2),
jno    char(2),
qty    int,
primary key (sno,pno,jno),
foreign key (sno) references s(sno),
foreign key (pno) references p(pno),
foreign key (jno) references j(jno)
)

/*创建索引*/
create unique index s_sname on s(sname)
drop index s.s_sname

insert into s values('S1','竟仪',20,'天津')
insert into s values('S2','盛锡',10,'北京')
insert into s values('S3','东方红',30,'北京')
insert into s values('S4','丰泰盛',20,'天津')
insert into s values('S5','为民',30,'上海')
go
insert into p values('P1','螺母','红',12)
insert into p values('P2','螺栓','绿',17)
insert into p values('P3','螺丝刀','蓝',14)
insert into p values('P4','螺丝刀','红',14)
insert into p values('P5','凸轮','蓝',40)
insert into p values('P6','齿轮','红',30)
go
insert into j values('J1','三建','北京')
insert into j values('J2','一汽','长春')
insert into j values('J3','弹簧厂','天津')
insert into j values('J4','造船厂','天津')
insert into j values('J5','机车厂','唐山')
insert into j values('J6','无线电厂','常州')
insert into j values('J7','半导体厂','南京')
go
insert into spj values('S1','P1','J1',200)
insert into spj values('S1','P1','J3',100)
insert into spj values('S1','P1','J4',700)
insert into spj values('S1','P2','J2',100)
insert into spj values('S2','P3','J1',400)
insert into spj values('S2','P3','J2',200)
insert into spj values('S2','P3','J4',500)
insert into spj values('S2','P3','J5',400)
insert into spj values('S2','P5','J1',400)
insert into spj values('S2','P5','J2',100)
insert into spj values('S3','P1','J1',200)
insert into spj values('S3','P3','J1',200)
insert into spj values('S4','P5','J1',100)
insert into spj values('S4','P6','J3',300)
insert into spj values('S4','P6','J4',200)
insert into spj values('S5','P2','J4',100)
insert into spj values('S5','P3','J1',200)
insert into spj values('S5','P6','J2',200)
insert into spj values('S5','P6','J4',500)

create table student
(
 sno char(9),
 sname varchar(10) not null,
 ssex char(2),
 sage int,
 sdept char(2),
 primary key(sno)
)

create table course
(
 cno char(1),
 cname varchar(20) not null,
 cpno char(1),
 ccredit smallint,
 primary key(cno)
)

create table sc
(
 sno char(9),
 cno char(1),
 grade int,
 primary key(sno,cno)
)

drop table student
drop table course
drop table sc

insert into student values('200215121','李勇','男',20,'CS')
insert into student values('200215122','刘晨','女',19,'CS')
insert into student values('200215123','王敏','女',18,'MA')
insert into student values('200215125','张立','男',19,'IS')

insert into course values('1','数据库','5',4)
insert into course values('2','数学','',2)
insert into course values('3','信息系统','1',4)
insert into course values('4','操作系统','6',3)
insert into course values('5','数据结构','7',4)
insert into course values('6','数据处理','',2)
insert into course values('7','PASCAL语言','6',4)

insert into sc values('200215121','1',92)
insert into sc values('200215121','2',85)
insert into sc values('200215121','3',88)
insert into sc values('200215122','2',90)
insert into sc values('200215122','3',80)

---查询选修了全部课程的学生姓名
select sname from student where not exists
(select * from course where not exists
(select * from sc where sno=student.sno and cno=course.cno))
---查询至少选修了学生200215122选修的全部课程的学生号码
select distinct sno from sc scx where not exists
(select * from sc scy where scy.sno='200215122' and not exists
(select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno))

---查询计算机科学系的学生及年龄不大于19岁的学生
select * from student where sdept='CS' intersect select * from student where sage<=19
---查询计算机科学系的学生与年龄不大于19岁的学生的交集
select * from student where sdept='CS' intersect select * from student where sage<=19
---查询计算机科学系的学生与年龄不大于19岁的学生的差集
select * from student where sdept='CS' except select * from student where sage<=19

---对每一个系，求学生的平均成绩，并把结果存入数据库
create table dept_age (sdept char(15),avg_age smallint)
insert into dept_age(sdept,avg_age) select sdept,avg(sage) from student group by sdept
select * from dept_age

---建立信息系选修了1号课程的学生的视图
create view IS_S1(sno,sname,grade)
as
select student.sno,sname,grade from student,sc
where sdept='IS' and student.sno=sc.sno and sc.cno='1'

create view IS_S2
as
select sno,sname,grade
from IS_S1
where grade>=90

--P127页第4题
---(1)求供应工程J1零件的供应商号码SNO：
select distinct sno from spj where jno='j1'
---(2)求供应工程J1零件P1的供应商号码SNO：
select distinct sno from spj where jno='j1' and pno='p1'
---(3)求供应工程J1零件为红色的供应商号码SNO：
select distinct sno from spj,p where spj.pno=p.pno and  jno='j1' and color='红'
----------------------------------------------------------------------------------------------------------------
select distinct sno from spj where jno='j1' and pno in (select pno from p where color='红')
----------------------------------------------------------------------------------------------------------------
select distinct sno from spj join p on spj.pno=p.pno where jno='j1' and color='红'
---(4)求没有使用天津供应商生产的红色零件的工程号JNO：
select distinct jno from spj where jno not in
(select jno from spj,p,s where spj.pno=p.pno and spj.sno=s.sno and city='天津' and color='红')
----------------------------------------------------------------------------------------------------------------
select distinct jno from spj where jno not in
(select spj.jno from s join spj on s.sno=spj.sno join p on p.pno=spj.pno where s.city='天津' and p.color='红')
----------------------------------------------------------------------------------------------------------------
select distinct jno from spj where jno not in
(select jno from spj where sno in (select sno from s where city='天津') and pno in (select pno from p where color='红'))
----------------------------------------------------------------------------------------------------------------
---下面语句错误，请自行分析：
select distinct jno from p join spj on spj.pno=p.pno join s on spj.sno=s.sno where color='红' and s.city!='天津'
----------------------------------------------------------------------------------------------------------------
---(5)求至少用了供应商S1所供应的全部零件的工程号JNO：
select distinct jno from spj spjx
  where not exists (
        select * from spj spjy where sno = 'S1' and not exists(
        select * from spj spjz where spjx.jno = spjz.jno and spjz.pno = spjy.pno))
----------------------------------------------------------------------------------------------------------------
select distinct jno from spj spjx
  where not exists (
        select * from spj where sno = 'S1' and pno not in(
        select pno from spj spjz where spjz.jno = spjx.jno ))
----------------------------------------------------------------------------------------------------------------

--(1)找出所有供应商的姓名和所在城市。
select sname,city from s
--(2)找出所有零件的名称、颜色、重量。
select pname,color,weight from p
--(3)找出使用供应商S1所供应零件的工程号码。
select jno from spj where sno='s1'
--(4)找出工程项目J2使用的各种零件的名称及其数量。
--方法一：
select pname,sum(qty) as sumqty from p join spj on p.pno=spj.pno where jno='j2' group by pname
--方法二：
select pname,sum(qty) as sumqty from spj,p where spj.pno = p.pno and jno = 'J2' group by pname
--方法三：
select pname,sumqty from p,
(select pno,sum(qty) as sumqty  from spj where jno = 'J2' group by pno) j2sumqty
where p.pno = j2sumqty.pno
--(5)找出上海厂商供应的所有零件号码。
select distinct pno from spj where sno in (select sno from s where city='上海')
select distinct pno from s,spj where s.sno=spj.sno and city='上海'
select distinct pno from s join spj on s.sno=spj.sno where city='上海'
select distinct pno from spj where exists (select * from s where sno=spj.sno and city='上海')
--(6)找出使用上海产的零件的工程名称。
select jname from j where jno in (select jno from spj where sno in (select sno from s where city='上海'))
select distinct jname from s,j,spj where s.sno=spj.sno and j.jno=spj.jno and s.city='上海'
--(7)找出没有使用天津产的零件的工程号码。
select jno from spj where jno not in (select jno from spj where sno in (select sno from s where city='天津'))
-------------------------------------------------------------------------------------------------------------
select jno from spj spjx where not exists
(select * from s,spj where s.sno=spj.sno and s.city='天津' and spj.jno=spjx.jno  )
-------------------------------------------------------------------------------------------------------------
select jno from spj spjx where not exists
(select * from spj spjy where spjy.jno=spjx.jno and sno in (select sno from s where city='天津'))
-------------------------------------------------------------------------------------------------------------
--错误：select distinct jno from spj where sno not in (select sno from s where city = '天津')
--(8)把全部红色零件的颜色改成蓝色。
update p set color='蓝' where color='红'
--(9)由S5供给J4的零件P6改为由S3供应。
update spj set sno='S3' where sno='S5' and pno='P6' and jno='J4'
--(10)从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
delete from spj where sno='S2'
delete from s where sno='s2'
--(11)请将(S2，J6，P4，200)插入供应情况关系。
insert into spj values('S2','P4','J6',200)

--第11题。请为三建工程项目建立一个供应情况的视图，包括供应商代码SNO、零件代码PNO、供应数量QTY。
create view v_sj as
select sno,pno,sum(qty) as qty from spj where jno in (select jno from j where  jname = '三建') group by sno,pno
--(1)找出三建工程项目使用的各种零件代码及其数量
select pno,sum(qty) as qtynum from v_spj group by pno
--(2)找出供应商S1的供应情况
select * from v_spj where sno='S1'

----------------------------------------------------------------------------------------------------------------
/*实验作业
（1）查询每项工程使用量最大的零件号，名称和使用量。
（2）查询使用了两种以上不同零件的工程名称。
（3）查询使用零件总量最大的工程号。
（4）查询使用零件种类最多的工程号。
*/
--1 查询每项工程使用量最大的零件号，名称和使用量。
--每项工程使用的零件号和使用量
select jno , pno , sum(qty) as sumqty from spj group by jno , pno
--每项工程使用量最大的使用量
select jno , max(sumqty) as maxsumqty from (
     select jno , pno , sum(qty) as sumqty from spj group by jno , pno) spjsumqty2
     group by jno
--比较二者
select jno , p.pno , pname , maxsumqty from p,
  (select spjsumqty1.jno , pno , maxsumqty from (
      select jno , pno , sum(qty) as sumqty from spj group by jno , pno) spjsumqty1 ,
  (select jno , max(sumqty) as maxsumqty from (
     select jno , pno , sum(qty) as sumqty from spj group by jno , pno) spjsumqty2
     group by jno ) spjmaxsumqty
  where spjsumqty1.jno = spjmaxsumqty.jno and spjsumqty1.sumqty = spjmaxsumqty.maxsumqty) spjpnomaxsumqty
where p.pno = spjpnomaxsumqty.pno
order by jno , p.pno
--2 查询使用了两种以上不同零件的工程的名称。
select distinct jname from j where jno in (
       select jno from spj group by jno having count(distinct pno) >= 2 )
select distinct jname from j,
    ( select jno from spj group by jno having count(distinct pno) >= 2 )spjcount
    where j.jno = spjcount.jno
--3 查询使用零件总量最大的工程号
--每项工程使用零件的总量
select jno,sum(qty) as sumqty from spj group by jno
--使用零件总量最大的数量
select max(sumqty) from (select jno,sum(qty) as sumqty from spj group by jno)
--比较二者
select distinct jno from (
    select jno,sum(qty) as sumqty from spj group by jno ) spjqty1
        where sumqty = (
            select max(sumqty) from (
                select jno,sum(qty) as sumqty from spj group by jno ) spjqty2)
--4 查询使用零件种类最多的工程号
--每项工程使用零件的种类数
select jno , count(distinct pno) as countpno from spj group by jno
--使用零件的种类数最大的数
select max(countpno) from (select jno,count(distinct pno) as countpno from spj group by jno
--比较二者
select distinct jno from (
    select jno,count(distinct pno) as countpno from spj group by jno ) spjcount1
    where countpno = (
       select max(countpno) from (
          select jno,count(distinct pno) as countpno from spj group by jno ) spjcount2)