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'));

(Create order processed table and employee table then do the SQL query .)

1. question. SQL query

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