Question

Write the following SQL queries and measure their execution time both with and without using indexes 1. Count how many parts in NYC have more than 70 parts on hand 2. Count how many total parts on hand, in both NYC and SFO, are Red 3. List all the suppliers that have more total on _hand parts in NYC than they do in SFO List all suppliers that supply parts in NYC that aren't supplied by anyone in SFO 5. Update all of the NYC on_hand values to on hand 10 6. Delete all parts from NYC which have less than 30 parts on hand. Some notes about the data model and the questions part number is the primary key for each part table. But it is ot unique across both tables If a part has the same number in NYC and SFO it is the same part regardless of color, etc . If I say, e.g. "Red parts", I mean color-name-"Red" not color = 0 Different suppliers may supply the same part in NYC and SFO

Please finish all 6 parts. Don't need to do the measure time things, but you can do it if you wanna do it. Thanks!

Answer 1

--1

select count(part_number) As PartCount From part_nyc where oh_hand>70;

count helps to get number of parts from nyc. Whete clause to filter data;

--2

select SUM(SumOnHand) from (

select (sum(p.on_hand)-sum(s.on_hand))+sum(s.on_hand) SumOnHand FROM part_nyc p LEFT JOIN part_sfo s ON p.part_number=s.part_number group by p.part_number having p.color='Red') As Subtable

In above query i have used calculations first i have removed if same part exist in both table and adding sfo. Using subquery table i have added all column data;

--3

select s.supplier_name from supplier s inner join part_nyc n on s.supplier_id=n.supplier inner join part_sfo s on s.supplier_id=s.supplier group by s.supplier_name having sum(n.on_hand)>sum(s.on_hand);

--4

select supplier_name from supplier where supplier_id in (select supplier from part_nyc) and supplier_id not in (select supplier from part_sfo);

--5

update part_nyc set on_hand=10;

--6

delete * from part_nyc where on_hand<30;