Database Question

Need help with part (e) and (f)

Will leave thumbs up done properly.

Question 2 An automated factory manufactures items for the intergalactic starship maintenance busi- ness. The tables can contain data as in the example below Composition Stores Weight product quantity part part stock compressitor 14 connector hub 4 connector hub24 compressitor 28 spread flange 4 nu wheel nut bolt axle wheel 1024 nut 2048 bolt 2199 screw 4298 axle 534 part weight axle 120 wheel 230 screw 54 bolt 75 nut 47 The Composition table records how many of each type of part are used to make one of the products that the factory produces. The tuples in the example show that a compressitor has at least 42 components of which 28 are bolts and 14 are nuts. The Stores table is used to record how many of each of the parts is currently in stock, and the Weight table records the weight of each type of part. (a) What SQL statement would you use to create the Composition table, including speci- (b) What SQL statement would you use to find out how many different products the factory (c) What SQL statement would you use to find how many component nuts are used to (d) Write an SQL statement, using the inner join construct, that will calculate the weight fying the primary key? produces? make each 'spread flange'? of a 'connector hub'? You should assume that the weight is calculated by adding up the weights of all the individual components it contains (e) Write a request for information in SELECT product, SUM(quantity) AS total simple English about the factory's FROM composition products to which the following GROUP BY product SQL query provides the answer HAVING total 50; (f) Suppose all the axles and nuts in the store were used to make compressitors' and that there is no shortage of any of the other types of part needed for this product. Write an SQL statement, using the natural join construct, that will calculate the number of compressitors' that would be produced