当前位置 博文首页 > 缘分锝天空的博客:SQL语句你会写了吗?一题教你轻松对付数据库
现有下列几个表,按题目要求写出相关的SQL语句
?第一部分:
# 1. 查询所在城市为“北京”的所有工程号和工程名。
select JNO,JNAME
from j
where CITY='北京';
# 2. 查询所有零件的名称和颜色。
select PNAME,COLOR
from p;
# 3. 查询工程J1中供货量大于300的供货商号和所供应的零件号。
select SNO,PNO
from spj
where JNO='J1'and QTY>300;
# 4. 查询在工程JI或J2中使用的零件编号。
select PNO
from spj
where JNO='J1' or JNO='J2';
# 5. 查询颜色未知的零件号和零件名。
select PNO,PNAME
from p
where COLOR IS NULL;
# 6. 查询在工程J1中所使用的红色零件的零件号。
select p.PNO
from p inner join spj
where p.PNO=spj.PNO and JNO='J1' and COLOR='红';
# 7. 查询不供应P2零件的供应商号和供应商名。
select s.SNO,s.SNAME
from s inner join spj
where s.SNO=spj.SNO and PNO!='P2';
# 8. 查询与工程J1在同一个城市的供货商信息。
select SNO, SNAME, CITY
from s
where CITY in (select CITY from j where JNO = 'J1');
# 9. 查询只为工程J1供货的供货商号和供应商名。
select s.SNO, SNAME
from spj,
s
where spj.SNO = s.SNO
and spj.SNO = 'J1'
and spj.SNO in (select spj.SNO
from spj
group by s.SNO
having count(JNO) = 1);
# 10. 查询为所有工程供货的供应商名。
select SNAME
from s
where not exists(
select *
from j
where not exists(
select *
from spj
where s.SNO=spj.SNO
and j.JNO=spj.JNO
)
);
第二部分:
# 1.查询工程J1中由北京供应商供应的零件总数。
select sum(QTY)
from spj,
j,
s
where spj.JNO = j.JNO
and j.JNO = spj.JNO
and s.CITY = '北京';
# 2.查询每个工程所需的零件总数。
select JNO,sum(QTY)
from spj
group by JNO;
# 3.查询为每个工程供货的供应商个数。
select JNO, count(distinct SNO)
from spj
group by JNO;
# 4.查询供应商数超过3个的工程号和供应商个数。输出时按供应商个数升序排列,供应商个数相同时,按工程号降序排列。
select JNO, count(distinct SNO)
from spj
group by JNO
having count(SNO)>3
order by SNO desc ;
# 5.查询向工程J1供货的各供应商号和供货总量,并按供货量降序排列输出。
select SNO,sum(QTY)
from spj
where JNO='J1'
group by SNO
order by 2 desc ;
# 6.查询最轻的螺丝刀的零件号和颜色。
select PNO, COLOR
from p
where WEIGHT = (select min(WEIGHT) from p where PNAME = '螺丝刀');
# 7.查询各种零件的平均使用量和零件号。
select PNO, avg(QTY)
from spj
group by PNO;
# 8.查询在所有工程中用量最大的零件号。
select PNO
from spj
group by PNO
having sum(QTY) >= all (select sum(QTY)
from spj
group by PNO
);
# 9.查询供货总量超过5000的供应商号和供应商名。
select s.SNO, SNAME
from s,
spj
where s.SNO=spj.SNO
group by s.SNO
having sum(QTY)>5000;
# 10.查询红色零件的种类。
select PNAME
from p
where COLOR = '红';
?第三部分:
# 1. 向基本表P中插入一种新零件的信息:(‘P5’,’钢钉’,’白’,18)
insert into p
value
('P5','钢钉','白','18');
# 2. 将供应商S3向工程J1、J2供应零件P5的信息插入到基本表SPJ中,供货量分别为300和400。
insert into spj
values ('S3', 'P5', 'J1', '300'),
('S3', 'P5', 'J2', '400');
# 3. 分别从基本表SPJ和J中删除和天津机床厂工程有关的信息。(使用两条删除语句,注意删除顺序)
delete
from p
where PNO not in (select PNO from spj);
# 4. 在基本表SPJ中将红色零件的供应商修改为S2。
update spj
set SNO='S2'
where PNO in (select p.PNO
from p
where p.COLOR = '红');
# 5. 将P4零件的颜色修改为白色,重量修改为12。
update p
set COLOR='白',WEIGHT=12
where PNO='P4';
# 6. 从基本表P中删除未在任何工程中使用的零件信息。
delete
from p
where PNO not in (select PNO
from spj
);
# 7. 建立供应商S1的供货视图V_S1(PNO,JNO,QTY)
create view V_S1
as select p.PNO,j.JNO,QTY
from p,j,spj
where p.PNO=spj.PNO and j.JNO=spj.JNO;
# 8. 建立工程J1的零件供应视图V_J1(SNAME,PNAME,QTY)
create view V_J1
as
select s.SNAME, p.PNAME, spj.QTY
from s,p,
spj
where s.SNO = spj.SNO
and p.PNO = spj.PNO
and JNO = 'J1';
# 9. 建立红色零件视图V_RED(PNO,PNAME)
create view V_red
as
select PNO, PNAME
from p
where COLOR = '红';
上面的题目做会了,数据库期末考试就不怕写sql语句啦,SQL语句在考试中还是占了比较大的比重,这分在考试中也是必拿分哦,伙伴们加油!!!
cs