drop table UpdateInventory;

drop table Receipt;

drop table Sales;

drop table Customer;

drop table Employee;

drop table Stock;

create table Stock

(

productID varchar(10) not null,

productName varchar(30),

pricePerKg float,

producttype varchar(15),

quantityInKg int,

primary key(productID)

);

insert into Stock values( 'P001', 'Catfish' , 7.99 ,'Fish' , 5);

insert into Stock values( 'P002', 'Tilapia', 6.99 ,'Fish', 12);

insert into Stock values( 'P003', 'Carp', 13.00 ,'Fish', 10);

insert into Stock values( 'P004', 'Tomatoes', 3.99 ,'Vegetable', 20);

insert into Stock values( 'P005', 'Grapes', 10.99 ,'Fruit', 30);

insert into Stock values( 'P006', 'Cabbage', 7.50 ,'Vegetable', 10);

insert into Stock values( 'P007', 'Chillies', 19.99 ,'Vegetable', 15);

insert into Stock values( 'P008', 'Potatoes', 2.99 ,'Vegetable', 25);

insert into Stock values( 'P009', 'Rambutan', 9.05 ,'Fruit', 25);

insert into Stock values( 'P010', 'Mackerel', 8.99 ,'Fish', 10);

create table Customer

(

customerID varchar(5) not null,

fName varchar(10),

lName varchar(10),

address varchar(25),

zipCode varchar(10),

phoneNo char(15),

dob date,

primary key(customerID)

);

insert into Customer values ('C01', 'Ann', 'Garcia', '26 Queen Rd', '15521', '0133920086', to_date('17-Oct-1982', 'dd-Mon-YYYY'));

insert into Customer values ('C02', 'Edward', 'Jones', '22 Hayward St', '15522', '0178502688', to_date('22-Mar-1985', 'dd-Mon-YYYY'));

insert into Customer values ('C03', 'Tom', 'Wilson', '21 Olive Rd', '15523', '0165320772', to_date('3-Feb-1987', 'dd-Mon-YYYY'));

insert into Customer values ('C04', 'Taylor', 'Brown', '19 Bay St', '15524', '0139003242', to_date('27-Dec-1985', 'dd-Mon-YYYY'));

insert into Customer values ('C05', 'Davis', 'Smith', '11 Stanford Rd', '15525', '0134562282', to_date('10-Aug-1989', 'dd-Mon-YYYY'));

create table Employee

(

userID varchar(5) not null,

fName varchar(10),

lName varchar(10),

position varchar(10),

address varchar(25),

zipCode varchar(10),

phoneNo char(15),

dob date,

salary int,

primary key(userID)

);

insert into Employee values('SR01', 'John' , 'White' , 'Manager' , '24 Dale St' , '22181' , '0132403897', to_date('30-Oct-1997', 'dd-Mon-YYYY'), 30000);

insert into Employee values('SR02', 'Jane' , 'Doe' , 'Assistant' , '22 Harbor St' , '22182' , '0178820982', to_date('12-Nov-1990', 'dd-Mon-YYYY'), 18000);

insert into Employee values('SR03', 'Jack' , 'Ford' , 'Assistant' , '21 William St' , '22183' , '0165320772', to_date('15-Nov-1994', 'dd-Mon-YYYY'), 12000);

insert into Employee values('SR04', 'Susan' , 'Lee' , 'Supervisor' , '19 Vienna St' , '22184' , '0139003242', to_date('17-Dec-1989', 'dd-Mon-YYYY'), 16000);

insert into Employee values('R05', 'Mary' , 'Jane' , 'Manager' , '11 Oakton St' , '22185' , '0134562282', to_date('25-Aug-2019', 'dd-Mon-YYYY'), 24000);

create table Sales

(

saleID varchar(10) not null,

productID varchar(10),

quantityInKg float,

customerID varchar(10),

primary key(saleID),

foreign key(productID) references stock(productID),

foreign key(customerID) references customer(customerID)

);

insert into Sales values('SA0001', 'P001' , 10.00 , 'C01');

insert into Sales values('SA0002', 'P002' , 3.00 , 'C01');

insert into Sales values('SA0003', 'P001' , 12.00 , 'C02');

insert into Sales values('SA0004', 'P004' , 7.00 , 'C01');

insert into Sales values('SA0005', 'P005' , 8.00 , 'C03');

insert into Sales values('SA0006', 'P005' , 12.00 , 'C04');

insert into Sales values('SA0007', 'P006' , 8.00 , 'C01');

insert into Sales values('SA0008', 'P007' , 2.00 , 'C04');

insert into Sales values('SA0009', 'P008' , 9.00 , 'C05');

insert into Sales values('SA0010', 'P009' , 6.00 , 'C05');

create table Receipt

(

saleID varchar(10) not null,

productID varchar(10) not null,

dop date,

amount float,

primary key(saleID, productID),

foreign key(saleID) references sales(saleID),

foreign key(productID) references stock(productID)

);

insert into Receipt values( 'SA0001', 'P001', to_date('10-Oct-2020', 'dd-Mon-YYYY'), 79.90);

insert into Receipt values( 'SA0002', 'P002', to_date('12-Mar-2020', 'dd-Mon-YYYY'), 20.97);

insert into Receipt values( 'SA0003', 'P001', to_date('14-Feb-2020', 'dd-Mon-YYYY'), 95.88);

insert into Receipt values( 'SA0004', 'P004', to_date('26-Dec-2020', 'dd-Mon-YYYY'), 27.93);

insert into Receipt values( 'SA0005', 'P005', to_date('28-Aug-2020', 'dd-Mon-YYYY'), 87.92);

insert into Receipt values( 'SA0006', 'P005', to_date('13-Oct-2020', 'dd-Mon-YYYY'), 131.88);

insert into Receipt values( 'SA0007', 'P006', to_date('17-Mar-2020', 'dd-Mon-YYYY'), 60.00);

insert into Receipt values( 'SA0008', 'P007', to_date('8-Feb-2020', 'dd-Mon-YYYY'), 39.98);

insert into Receipt values( 'SA0009', 'P008', to_date('15-Dec-2020', 'dd-Mon-YYYY'), 26.91);

insert into Receipt values( 'SA0010', 'P009', to_date('10-Aug-2020', 'dd-Mon-YYYY'), 54.30);

create table UpdateInventory

(

productID varchar(10) not null,

productName varchar(30),

pricePerKg float,

producttype varchar(15),

quantityInKg int,

restockDate date,

primary key(productID)

);

insert into UpdateInventory values( 'P001', 'Catfish' , 7.99 ,'Fish' , 10 , to_date('11-Sep-2020', 'dd-Mon-YYYY'));

insert into UpdateInventory values( 'P002', 'Tilapia', 6.99 ,'Fish', 10 , to_date('18-Sep-2020', 'dd-Mon-YYYY'));

insert into UpdateInventory values( 'P003', 'Carp', 13.00 ,'Fish', 8 , to_date('15-Oct-2020', 'dd-Mon-YYYY'));

insert into UpdateInventory values( 'P004', 'Tomatoes', 3.99 ,'Vegetable', 15 , to_date('20-Nov-2020', 'dd-Mon-YYYY'));

insert into UpdateInventory values( 'P005', 'Grapes', 10.99 ,'Fruit', 12 , to_date('24-Dec-2020', 'dd-Mon-YYYY'));

1. question. SQL query

Show the orders processed by each employee. SQL statement . (Need screenshot of the output)

RECEIPT +v Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT SALEID PRODUCTID DOP AMOUNT SA0001 PO01 10/10/2020 79.9 SA0002 POO2 03/12/2020 20.97 SA0003 PO01 02/14/2020 95.88 SA0004 PO04 12/26/2020 27.93 SA0005 PO05 08/28/2020 87.92 SA0006 PO05 10/13/2020 131.88 SA0007 PO06 03/17/2020 60 SA0008 P007 02/08/2020 39.98 SA0009 PO08 12/15/2020 26.91 SA0010 P009 08/10/2020 54.3 Download EMPLOYEE + Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SOL REST Sample Queries Query Count Rows Insert Row Load Data EDIT USERID FNAME LNAME POSITION ADDRESS ZIPCODE PHONENO DOB SALARY SR01 John White Manager 24 Dale St 22181 0132403897 10/30/1997 30000 SRO2 Jane Doe Assistant 22 Harbor St 22182 0178820982 11/12/1990 18000 SRO3 Jack Ford Assistant 21 William St 22183 0165320772 11/15/1994 12000 SR04 Susan Lee Supervisor 19 Vienna St 22184 0139003242 12/17/1989 16000 R05 Mary Jane Manager 11 Oakton St 22185 0134562282 08/25/2019 24000 Download SALES + Table Data Indexes Model Constraints Grants Statistics UI Defaults Triggers Dependencies SQL REST Sample Queries Query Count Rows Insert Row Load Data EDIT SALEID PRODUCTID QUANTITYINKG CUSTOMERID SA0001 PO01 10 C01 SA0002 POO2 3 C01 SA0003 PO01 12 CO2 SA0004 PO04 7 C01 SA0005 P005 00 CO3 SA0006 PO05 12 C04 SA0007 P006 8 C01 SA0008 P007 2 C04 SA0009 PO08 9 C05 re SA0010 P009 6 C05 Download