Question

2. Under the One-District-One-Factory Initiative, you a database engineer have been awarded a 200,000 USD contract to implement and administer a database needed to manage the data of the factories that have been setup in all the 216 districts of the country. The "FactoriesDatatable below shows sample data stored in the "One DistrictOneFactory" database. Commission Date Raw Material ProductionInTonnes 2016-03-04 Sugarcane 1436.36 Rice 1225.93 ID Name Region 100 Ayeduase Sugar Factory Ashanti 101 Oforikrom Rice Factory Ashanti 102 Agona Coconut Oil Factory Central 103 Nyankpala Rice Factory Northern 104 Abetifi Rubber Factory Eastern 105 Ahanta Rubber Factory Western 2017-02-05 2017-05-01 Coconut 1741.32 2018-01-01 Rice 1432.18 2017-03-02 Rubber 1526.19 2016-02-09 Rubber 1113.18 a) Using appropriate SQL commands create the above specified database and table. Your created database should overwrite any existing database bearing the same name. Note that the ID column in the above table uniquely identifies each row and it increases automatically and sequentially, starting from the value 100. Do not bother to populate the table created with the sample data above. (4) b) A new Factory, "Yamoransa Potato Chips Factory", was commissioned on the 18th of December, 2017 in the Central Region. This factory's main raw material is Sweet Potato. Since its inception, this factory has produced 754.38 metric tonnes of Potato Chips. Using appropriate SQL commands include this factory's data in the table you created. (2) c) You have been requested to show the detailed data of all Sugar Producing Factories in the Ashanti Region. Use appropriate SQL commands to achieve this request. d) You have been requested to show the detailed data of all Rice Producing Factories in the Northern Region, commissioned after May, 2017. The listed factories should have produced not less than 500 tonnes of products. Use appropriate SQL commands to achieve this request. (1.5) e) Write a query to show the factory names and tonnes of products produced of all factories which use either Rice or Rubber as their main raw material. The list should be ordered in decreasing magnitude of production in tonnes. (1.5)

Answer 1

a)create database OneDistrictOneFactory;

-->CREATE TABLE FactoriesData(ID int IDENTITY(100,1) PRIMARY KEY,Name varchar2(30),Region varchar2(20),CommissionDate date,RawMaterial varchar2(30),ProductionInTonnes decimal(10,2));

b)-->insert into FactoriesData(Name,Region,CommisionDate,ProductionInTonnes) values("Yamoransa Potato Chip Factory","Central","18-DEC-2017","Sweet Potato",754.38);

c)-->SELECT * FROM FactoriesData where Name LIKE '%Sugar%' and Region='Ashanti';

d)-->SELECT * FROM(SELECT * FROM FactoriesData where Name LIKE '%Rice%' and Region='Northern  and CommissionDate> '2017-05-01') where ProductionInTonnes>500 ;

e)-->SELECT Name,ProuctionInTonnes FROM FactoriesData where RawMaterial='Rice' OR RawMaterial='Rubber' order by ProductionInTonnes DESC;