Question

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

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 1 drop table part_nyc 2 create table part_nyc (part_number integer, supplier integer, color integer, on_hand integer, descr text); 4 7 COPY part_nyc 8 FROM 'part_nyc.dat 9 WITH DELIMITER,' 10 11 drop table part_sfo; 12 create table part_sfo (part_number integer, 13 supplier integer, color integer, on_hand integer, 15 descr text); 17 COPY part_sfo 18 FROM 'part_sfo.dat' 19 WITH DELIMITER, 20 21 drop table supplier 22 create table supplier (supplier_id integer, supplier_name varchar (20)); 23 24 insert into supplier values (0, 'Bob') 25 insert into supplier values (1, 'Ted) 26 insert into supplier values (2, 'Carol); 27 insert into supplier values (3, 'Alice') 28 insert into supplier values (4, 'Jerry) 29 insert into supplier values (5, 'Mickey) 30 insert into supplier values (6, 'Phil); 1 insert into supplier values (7, 'Vince') 32 insert into supplier values (8, 'Bill'); 33 insert into supplier values (9, 'Donna) 35 36 drop table color 7 create table color (color_id integer, color name varchar(20)); 38 9 insert into color values (0, 'Red) 40 insert into color values (1, 'Green)

Answer 1

1.

select count(*) from part_nyc where on_hand > 70;

2.

select (select count(*) from part_nyc where color .= 'Red') + (select count(*) from part_nyc where color .= 'Red') from dual;

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 2 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.