2024/05/25
今日学习时长:3小时
代码行数:没统计
博客数量:1篇
今天主要开始了数据库实验二的操作。
4、在数据库 test1 中进行下列查询操作,将查询语句与结果写入实验报告。 (1)查询所有供应商情况,先按城市升序排列,城市相同按供应商名称降序排列。 (2)查询所有零件情况,先按零件名称升序排列,零件名称相同按重量降序排列。 (3)查询项目名中含有“厂”的项目情况。 (4)查询供应商名称中第二个字为“方”的供应商情况。 (5)查询所有零件中的最大、最小、平均重量。 (6)查询零件中名为“螺丝刀”的零件的种类数、平均重量。 (7)查询供应商 S1 所供应的各种零件的名称和数量。 (8)查询工程 J1 所使用的各种零件的名称和数量。 (9)查询没有使用红色螺丝刀的工程名称。 (10)查询没有供应红色螺丝刀的供应商名称。 (11)查询所用零件数量超过 500 的工程项目号。 (12)查询所用零件种类超过 3 种的工程项目名称。 (13)查询使用了全部零件的工程项目名称。 (14)查询至少供应了工程 J1 所使用的全部零件的供应商名称。 (15)查询供应情况,显示内容为供应商名称、零件名、工程名称、数量。 (16)查询“东方红”供应商供应情况,显示供应的零件名、工程名称、数量。 5、请为机车厂工程项目建立一个供应情况的视图,包括供应商代码,零件代码, 供应数量。针对该试图完成如下查询并将 SQL 语句写入实验报告。 (1)查询机车厂工程项目使用的各种零件代码及其数量。 (2)查询处供应商 S2 的供应情况
create database test1;--创建数据库 Create table s( --供应商 Sno char(2) primary key, sname varchar(10) not null, status int, city varchar(10)); create table p(--零件 pno char(2) primary key, pname varchar(10) not null, color varchar(6), weight int); create table j( --工程 jno char(2) primary key, jname varchar(20) not null, city varchar(10)); create table spj( sno char(2) foreign key references s(sno), pno char(2) foreign key references p(pno), jno char(2) foreign key references j(jno), qty int, primary key(sno,pno,jno)); 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,'上海'); 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); 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','半导体厂','南京'); 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); --查询所有供应商情况,先按城市升序排列,城市相同按供应商名称降序排列。 select * from s order by city,sname desc; --查询所有零件情况,先按零件名称升序排列,零件名称相同按重量降序排列 select * from p order by pname,weight desc; --查询项目名中含有“厂”的项目情况 select * from j where jname like '%厂%'; --查询供应商名称中第二个字为“方”的供应商情况 select * from s where sname like '_方%'; --查询所有零件中的最大、最小、平均重量 select max(weight) 最大重量,min(weight) 最小重量,AVG(weight) 平均重量 from p; --查询零件中名为“螺丝刀”的零件的种类数、平均重量 select count(pno) 零件种类数,avg(weight) 平均重量 from p where pname='螺丝刀'; --查询供应商 S1 所供应的各种零件的名称和数量 select p.pname 零件名称,p.color 颜色,sum(qty) 数量 from spj,p where spj.sno='s1' and spj.pno=p.pno group by p.color,p.pname; --查询工程 J1 所使用的各种零件的名称和数量 select p.pname 零件名称,p.color 颜色,sum(qty) 数量 from spj,p where spj.jno='j1' and spj.pno=p.pno group by p.color,p.pname; --查询没有使用红色螺丝刀的工程名称 select DISTINCT jname from j where jno not in(select jno from spj,p where p.pno=spj.pno and p.pname='螺丝刀' and p.color='红'); --查询没有供应红色螺丝刀的供应商名称 select DISTINCT sname from s where sno not in(select jno from spj,p where p.pno=spj.pno and p.pname='螺丝刀' and p.color='红'); --查询所用零件数量超过 500 的工程项目号 select jno,sum(spj.qty) 零件总数 from spj group by jno having sum(qty)>500; --查询所用零件种类超过 3 种的工程项目名称 select j.jname,count(DISTINCT pno) 种类数 from spj,j where spj.jno=j.jno group by spj.jno,j.jname having count(DISTINCT pno)>3; --查询使用了全部零件的工程项目名称 select jname from j where not exists(select pno from p where not exists (select * from spj where spj.jno=j.jno and spj.pno=p.pno)); --查询至少供应了工程 J1 所使用的全部零件的供应商名称 select sname from s where sno in(select sno,count(*)种数 from spj where jno='j1' group by sno having count(*)>(select count(DISTINCT pno) from spj where jno='j1')); --查询供应情况,显示内容为供应商名称、零件名、工程名称、数量 select sname 供应商,pname 供应零件,jname 项目,qty 数量 from s,p,j,spj where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno; --查询“东方红”供应商供应情况,显示供应的零件名、工程名称、数量 select sname 供应商,pname 供应零件,jname 项目,qty 数量 from s,p,j,spj where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno and sname='东方红'; --视图 --请为机车厂工程项目建立一个供应情况的视图,包括供应商代码,零件代码, 供应数量 go create view jview as select spj.sno ,spj.pno,spj.qty from spj,j where spj.jno=j.jno and j.jname='机车厂'; go --查询机车厂工程项目使用的各种零件代码及其数量 select pno ,qty from jview; --查询处供应商 S2 的供应情况 select * from jview where sno='s2';