SQL语句你会写了吗？一题教你轻松对付数据库期末考试（写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语句在考试中还是占了比较大的比重，这分在考试中也是必拿分哦，伙伴们加油！！！