1.) Please write a SELECT STATEMENT FOR each of the following queries. a.) Get t

Question

1.) Please write a SELECT STATEMENT FOR each of the following queries.

a.) Get the total number of projects supplied by supplier S1.

b.) Get the total quantity of part1 supplied by supplier 1, and name the column as TotalQty.

c.) Get supplier-numbers from suppliers with a status lower than that of supplier 1

d.) Get shipment details in the increasing order of supplier numbers, and decreasing order of project numbers if they have the same supplier numbers.

e.) Get part number of parts supplied by a supplier whose name starts with letter 'A'

f.) Ger supplier(s) with maximum status.

g.) Get supplier number, and part number such that the supplier supplies the part to 2 or more projects.

h.) Get part numbers of parts supplied to some project in an average quantity of more than 320.

i.) For each project, display the number of suppliers who have shipped some parts to it

j.) Get supplier numbers from suppliers whose city is NULL.

schema and a snapshot of SUPPLIER-PART PROJECT database is given below. Table S, P and J represent Supplier, Part, and Project, respectively. Table SPJ contains the shipment information such that a supplier shipped a quantity of a part to a project. S, P# and J# in table SPJ are foreign keys. 5. The Schema S | S# | Snamc | Status | City P | P# | Pname | Color | Weight | City Ja Citv Values: ,SPJ S# P# J# Qty. sl | PI | .11 | 200 700 3 100 2 200 3 J3200 S2 P3 500 3 600 52 P3 JG400 s# sname Status City SSmit S2 Joncs 20 aris Si 30 aris SAdams30 5 S2 Athens Pname Color Weight City P1 ut 12 0n atis reen Serew Serew CaBlue S2 P5 2 100 200 S3 P 200 tle ondon 53 12 l'aris S4 S4 0g 7300 J# Jname City orter 2Display Rom 14 0 S P00 S5 P57100 aris thens 2 200 P100 3200 S P 800 London JG 7 Tape EDS | Oslo ondon

Explanation / Answer

Let me know if you have any doubt.

a.) Get the total number of projects supplied by supplier S1.

select count(distinct J#)
from SPJ
where S#='S1';

b.) Get the total quantity of part1 supplied by supplier 1, and name the column as TotalQty.

select sum(Qty) as TotalQty
from SPJ
where S#='S1' and P#='P1';

c.) Get supplier-numbers from suppliers with a status lower than that of supplier 1.

select s# from S
where Status < (select Status from S
where s#=1)

d.) Get shipment details in the increasing order of supplier numbers, and decreasing order of project numbers if they have the same supplier numbers.

select * from SPJ
order by S#,P# desc

e.) Get part number of parts supplied by a supplier whose name starts with letter 'A'.

select P# from SPJ
where S# in ( select S# from S where Sname like 'A%'

f.) Ger supplier(s) with maximum status.

SELECT * FROM S
WHERE Status = (SELECT MAX(Status) from S);

g.) Get supplier number, and part number such that the supplier supplies the part to 2 or more projects.

Select s#,p#
from SPJ
where s# in (Select s# from SPJ group by s# having count(unique(j#)) >1)

h.) Get part numbers of parts supplied to some project in an average quantity of more than 320.

select distinct P#
from SPJ
group by J#, p#
where avg(Qty)>320;

i.) For each project, display the number of suppliers who have shipped some parts to it.

Select t2.j# , count(unique(t1.s#))
from projects t2 left join SPJ t1 on t2.j# = t1.j#
group by t2.j# ;

j.) Get supplier numbers from suppliers whose city is NULL.

select *
from SPJ
where Qty IS NOT NULL;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.