SQL project

CSC384-01

Project Group #3 - Dhafer Alamri, Morgan Kelly, Phil Stevens

Project Title: Morgan’s Auto Emporium

PHASE III - December 11, 2017 - 12/12/2017 @ 11:15AM – Appt. for presenting to Dr. Mani

Description of Project:

Morgan’s Auto Emporium is a full service, retailer of fine used cars. Since the business is thriving, we have now opened multiple locations to server our customers more conveniently. Because we care about our salespeople, and because they are the most important asset to the company, the decision has been made to award an employee from each branch who records the most total sales per month. Therefore it is necessary to track each employee’s sales at the branches.

Requirements:

1. Track sales of cars

2. Track employees sales for the month

3. Keep a record of car Inventory for the branches

4. Keep a record of company branch locations

5. At the end of each month determine awards for top salesperson at each branch

Given our requirements, we should now consider some functions to run on our data to see if our entity relationship model for our database is robust. Our design should be able to handle several different types of actions. Our proposed list is comprised of the following:

1. Insert new data to the tables – inventory, employees, customer, and sale table, etc.

2. Update data in existing tables – inventory, employees, customer, branch (new branch), etc.. – Deletes or changing of names, or corrections to incorrect data entry.

3. Find the top 3 salespersons for each branch

4. Find the top 3 salesperson for all branches combined (our top 3 breadwinners)

5. Find the best customers (customers who purchased the most vehicles)

6. Find the most expensive vehicles in the inventory (car, truck, SUV)

7. Find the most expensive cars (top 3) in the inventory

8. Find the most expensive SUV’s (top 3) in the inventory

9. Find the most expensive Trucks (top 3) in the inventory

10. Find the top salesperson for sales award

11. Find the top employee for a service award

12. Update a table with data from another table – correlated update

13. Cascade delete data from tables – those that have dependencies should delete the data.

14. Find branches that sell (x) model of car – use group by and order by

15. Find the min price of car and max price of car – order from least expensive to most expensive

16. Sum each Employee’s total sales $$$ for the month.

17. Identify branches with more than (x) number of certain cars. – Identifying excess inventory to discount

18. Give a list of all employees and their start dates in order from most seniority to least seniority.

Bonus Piece to see if I could get this to work…

19. Use a TRIGGER in oracle to automatically generate a sales report for each branch after every business day.

Updated ER Schema for Phase III –

Change Log

1. Added Date of Hire to the Employee Table – needed to answer question #18

2. Added type of vehicle to inventory table – T = Truck, S = SUV, C = Car

ORACLE SQL DDL Statements to create tables and populate data in the tables.

Drop Table MCEInventory CASCADE CONSTRAINTS;

Drop Table MCECompany CASCADE CONSTRAINTS;

Drop Table MCEBranch CASCADE CONSTRAINTS;

Drop Table MCEEMployee CASCADE CONSTRAINTS;

Drop Table MCECustomer CASCADE CONSTRAINTS;

Drop Table MCEAwards CASCADE CONSTRAINTS;

Drop Table MCESale CASCADE CONSTRAINTS;

CREATE TABLE MCECompany (

MCEName VARCHAR(60) PRIMARY KEY,

MCEPhone VARCHAR (20),

MCEAddress VARCHAR (60));

CREATE TABLE MCEBranch (

BName VARCHAR(60) PRIMARY KEY,

BAddress VARCHAR(60),

BPhone VARCHAR (20));

CREATE TABLE MCEInventory (

VIN VARCHAR(20) PRIMARY KEY,

MYear INT,

Model VARCHAR(20),

Make VARCHAR(40),

Type VARCHAR(20),

BName VARCHAR(60) REFERENCES MCEBranch);

CREATE TABLE MCEEmployee (

EmpID VARCHAR(20) PRIMARY KEY,

EName VARCHAR(40),

EStartDate Date,

EBranch VARCHAR(60) REFERENCES MCEBranch);

CREATE TABLE MCECustomer (

CustomerID INT PRIMARY KEY,

CPhone VARCHAR (20),

CAddress VARCHAR(60),

CName VARCHAR(60));

CREATE TABLE MCEAwards (

AType VARCHAR(20),

AAmount FLOAT,

ABranch VARCHAR(60),

AEmpID VARCHAR (20),

FOREIGN KEY (ABranch) REFERENCES MCEBranch (BName),

FOREIGN KEY (AEmpID) REFERENCES MCEEmployee (EmpID));

CREATE TAble MCESale (

SaleID INT PRIMARY KEY,

SPrice FLOAT,

SDate Date,

VIN VARCHAR(20) REFERENCES MCEInventory,

CustomerID INT REFERENCES MCECustomer,

EmpID VARCHAR (20) REFERENCES MCEEmployee);

INSERT INTO MCECompany VALUES ('Morgans Flint Emporium', '810-223-6766', '123 Flint St. Flint, MI 48504');

INSERT INTO MCEBranch VALUES ('Flint South Side', '2100 S. Dort Hwy. Flint, MI 48502', '810-220-5400');

INSERT INTO MCEBranch VALUES ('Flint North Side', '6204 Saginaw St. Flint, MI 48505', '810-220-5401');

INSERT INTO MCEBranch VALUES ('Flint West Side', '1910 W Pierson Rd. # 157H, Flint, MI 48504', '810-220-5402');

INSERT INTO MCEBranch VALUES ('Flint East Side', '3112 Center Rd. Flint, MI 48506', '810-220-5403');

INSERT INTO MCEBranch VALUES ('Flint Downtown', '111 Saginaw St. Flint, MI 48501', '810-220-5490');

INSERT INTO MCEBranch VALUES ('Flint Northwest', '4112 Clio Rd. Flint, MI 48507', '810-220-5000');

INSERT INTO MCEInventory VALUES ('1GK1001X676677889', 2010, 'Ford', 'Mustang GT', 'C', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1002X676677889', 2000, 'Chevrolet', 'Cavalier Conv.', 'C', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1003X676677889', 2014, 'GMC', 'Yukon Denali', 'S', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1004X676677889', 2016, 'Cadillac', 'Escalade', 'S', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1005X676677889', 2017, 'Buick', 'Cascada Conv', 'C', 'Flint North Side');

INSERT INTO MCEInventory VALUES ('1GK1006X676677889', 2015, 'Lincoln', 'Mark VIII', 'C', 'Flint North Side');

INSERT INTO MCEInventory VALUES ('1GK1007X676677889', 2010, 'Chevrolet', 'Corvette ZR1', 'C', 'Flint North Side');

INSERT INTO MCEInventory VALUES ('1GK1008X676677889', 1999, 'GMC', 'Sierra 2500HD', 'T', 'Flint North Side');

INSERT INTO MCEInventory VALUES ('1GK1009X676677889', 2001, 'Jaguar', 'XJS', 'C', 'Flint West Side');

INSERT INTO MCEInventory VALUES ('1GK1010X676677889', 2011, 'Ford', 'F150 Limited Crew Cab', 'T', 'Flint West Side');

INSERT INTO MCEInventory VALUES ('1GK1011X676677889', 2010, 'Ford', 'F150 Work Truck w/Plow', 'T', 'Flint West Side');

INSERT INTO MCEInventory VALUES ('1GK1012X676677889', 2017, 'Lexus', 'GX SUV', 'S', 'Flint West Side');

INSERT INTO MCEInventory VALUES ('1GK1013X676677889', 2016, 'GMC', 'Terrain AWD', 'S', 'Flint East Side');

INSERT INTO MCEInventory VALUES ('1GK1014X676677889', 2010, 'Chevrolet', 'Silverado Crew 4X4 1/2 Ton', 'T', 'Flint East Side');

INSERT INTO MCEInventory VALUES ('1GK1015X676677889', 2010, 'Chevrolet', 'Equinox SUV', 'S', 'Flint East Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677890', 2012, 'Chevrolet', 'Tahoe', 'S', 'Flint Downtown');

INSERT INTO MCEInventory VALUES ('1GK1016X676677900', 2002, 'Chevrolet', 'Suburban', 'S', 'Flint Downtown');

INSERT INTO MCEInventory VALUES ('1GK1016X676677910', 2002, 'Pontiac', 'Grand AM GT', 'C', 'Flint Downtown');

INSERT INTO MCEInventory VALUES ('1GK1016X676677912', 2017, 'Buick', 'Lacrosse', 'C', 'Flint Downtown');

INSERT INTO MCEInventory VALUES ('1GK1016X676677915', 2014, 'GMC', 'Yukon', 'S', 'Flint Northwest');

INSERT INTO MCEInventory VALUES ('1GK1016X676677917', 2013, 'Land Rover', 'Discovery', 'S', 'Flint Northwest');

INSERT INTO MCEInventory VALUES ('1GK1016X676677919', 2011, 'Toyota', 'Four Runner', 'S', 'Flint Northwest');

INSERT INTO MCEInventory VALUES ('1GK1016X676677929', 2016, 'Nissan', 'Pathfinder', 'S', 'Flint Northwest');

INSERT INTO MCEInventory VALUES ('1GK1016X676677931', 2015, 'Dodge', 'Caravan', 'C', 'Flint Northwest');

INSERT INTO MCEInventory VALUES ('1GK1016X676677940', 2009, 'Cadillac', 'Escalade', 'S', 'Flint East Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677945', 2008, 'GMC', 'Sierra Crew Cab', 'T', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677950', 2000, 'GMC', 'Sierra Crew Cab', 'T', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677955', 2015, 'Chevrolet', 'Silverado Crew Cab', 'T', 'Flint South Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677960', 2013, 'GMC', 'Terrain', 'S', 'Flint North Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677965', 2011, 'Toyota', 'Camry', 'C', 'Flint East Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677970', 2010, 'Volkswagen', 'Jetta', 'C', 'Flint North Side');

INSERT INTO MCEInventory VALUES ('1GK1016X676677975', 2009, 'Honda', 'Accord', 'C', 'Flint South Side');

INSERT INTO MCEEmployee VALUES ('12300', 'Joe Smith', '01-JAN-00', 'Flint North Side');

INSERT INTO MCEEmployee VALUES ('12400', 'Mary Williams', '09-FEB-97', 'Flint North Side');

INSERT INTO MCEEmployee VALUES ('12500', 'Morgan Kelly', '15-APR-14', 'Flint North Side');

INSERT INTO MCEEmployee VALUES ('12600', 'Phil Stevens', '22-JUN-13', 'Flint North Side');

INSERT INTO MCEEmployee VALUES ('12700', 'Bob Jones', '19-OCT-12', 'Flint South Side');

INSERT INTO MCEEmployee VALUES ('12800', 'Rick Oilslick', '06-SEP-11', 'Flint South Side');

INSERT INTO MCEEmployee VALUES ('12900', 'Sam Brokenbrake', '14-JUL-15', 'Flint South Side');

INSERT INTO MCEEmployee VALUES ('12101', 'Krystal Ball', '28-NOV-10', 'Flint South Side');

INSERT INTO MCEEmployee VALUES ('12301', 'Barry Headlight', '08-JAN-09', 'Flint East Side');

INSERT INTO MCEEmployee VALUES ('12302', 'Jennifer Sparks', '31-MAR-08', 'Flint East Side');

INSERT INTO MCEEmployee VALUES ('12303', 'Meghan Motors', '25-MAY-07', 'Flint East Side');

INSERT INTO MCEEmployee VALUES ('12404', 'Ricardo Leatherseats', '19-OCT-02', 'Flint East Side');

INSERT INTO MCEEmployee VALUES ('12810', 'Kerri Sellsalot', '14-AUG-01', 'Flint West Side');

INSERT INTO MCEEmployee VALUES ('12830', 'Beth Hammers', '23-DEC-16', 'Flint West Side');

INSERT INTO MCEEmployee VALUES ('12909', 'Mandi Makinmoney', '16-FEB-01', 'Flint West Side');

INSERT INTO MCEEmployee VALUES ('13100', 'Moore Money', '05-MAR-99', 'Flint West Side');

INSERT INTO MCEEmployee VALUES ('13110', 'Lotsa Kash', '17-APR-16', 'Flint Northwest');

INSERT INTO MCEEmployee VALUES ('14051', 'Needie Jobs', '19-JUN-15', 'Flint Downtown');

INSERT INTO MCEEmployee VALUES ('15010', 'Makin Change', '22-AUG-04', 'Flint Northwest');

INSERT INTO MCEEmployee VALUES ('16900', 'Bobbie Bankroll', '21-SEP-07', 'Flint Northwest');

INSERT INTO MCEEmployee VALUES ('17000', 'Artie Cashin', '12-JAN-17', 'Flint South Side');

INSERT INTO MCEEmployee VALUES ('18009', 'Bubbles Washinmore', '18-FEB-14', 'Flint East Side');

INSERT INTO MCEEmployee VALUES ('10019', 'Grindem Down', '20-MAY-14', 'Flint Downtown');

INSERT INTO MCECustomer VALUES ('12040', '220-1840', '1800 N. Center Rd. Flint, MI 48505', 'Jennifer Quackenbush');

INSERT INTO MCECustomer VALUES ('12050', '230-1999', '2560 N. Clio Rd. Flint, MI 48506', 'Crystal Crandall');

INSERT INTO MCECustomer VALUES ('12060', '255-2240', '3300 S. Center Rd. Burton, MI 48515', 'Syndney Prollybroke');

INSERT INTO MCECustomer VALUES ('12065', '330-7640', '1800 E. Pierson Rd. Flint, MI 48503', 'Billy Blastoff');

INSERT INTO MCECustomer VALUES ('12066', '410-5560', '2240 E. Richfield Rd. Flint, MI 48502', 'Mark Bullenoff');

INSERT INTO MCECustomer VALUES ('12070', '810-379-6790', '4900 Hilltop Rd. Flushing, MI 48605', 'Emory Farmall');

INSERT INTO MCECustomer VALUES ('14010', '810-222-6700', '123 Main St. Lapeer, MI 48217', 'Emily Cryforever');

INSERT INTO MCECustomer VALUES ('14070', '676-1256', '1200 Hill Rd. Flint, MI 48606', 'Bobbie Twotone');

INSERT INTO MCECustomer VALUES ('15670', '810-459-1290', '1800 Jennings Rd. Fenton, MI 48607', 'Timmy Tompkins');

INSERT INTO MCECustomer VALUES ('17070', '810-671-6390', '7500 Silver Lake Rd. Linden, MI 48805', 'Crystal Ball');

INSERT INTO MCECustomer VALUES ('17071', '810-987-9790', '8609 Clio Rd. Clio, MI 48665', 'Billy Beans');

INSERT INTO MCECustomer VALUES ('18070', '989-673-7011', '454 Cranston Dr. Frankenmuth, MI 48705', 'Debbie Dart');

INSERT INTO MCECustomer VALUES ('19070', '810-279-1790', '6700 Barnswallow Ct. Genesee, MI 48601', 'Connie Driver');

INSERT INTO MCECustomer VALUES ('13555', '313-879-6111', '479 Canfield Detroit, MI 48321', 'Johnny Rocket');

INSERT INTO MCECustomer VALUES ('11116', '810-655-7790', '5455 Irish Rd. Davison, MI 48405', 'Melanie Mostofall');

INSERT INTO MCECustomer VALUES ('17777', '810-312-4790', '6700 Gale, Rd. Goodrich, MI 48438', 'Philemup Flapper');

INSERT INTO MCECustomer VALUES ('18919', '616-579-8790', '3100 N.E. Maple South Grand Rapids, MI 48305', 'Lisa Markfield');

INSERT INTO MCECustomer VALUES ('22900', '818-230-6790', '333 First St. Davisburg, MI 48805', 'Liddie McDowell');

INSERT INTO MCECustomer VALUES ('32100', '248-380-8788', '123 Second Ave. Lakeville, MI 49058', 'Abbie Falls');

INSERT INTO MCECustomer VALUES ('11111', '810-179-1790', '4500 Betsy Hwy. Bellaire, MI 49918', 'Rebecca Tomson');

INSERT INTO MCEAwards VALUES ('Sales', 5000.00, 'Flint North Side', '12300');

INSERT INTO MCEAwards VALUES ('Service', 1500.00, 'Flint West Side', '12909');

INSERT INTO MCEAwards VALUES ('Sales', 2500.00, 'Flint South Side', '12101');

INSERT INTO MCEAwards VALUES ('Service', 1500.00, 'Flint East Side', '12303');

INSERT INTO MCEAwards VALUES ('Sales', 3500.00, 'Flint East Side', '12302');

INSERT INTO MCESale VALUES (100, 12500.00, '15-NOV-16', '1GK1001X676677889', '12040', '12101');

INSERT INTO MCESale VALUES (110, 22500.99, '11-OCT-16', '1GK1003X676677889', '12066', '12800');

INSERT INTO MCESale VALUES (111, 32500.00, '15-NOV-16', '1GK1005X676677889', '12060', '12600');

INSERT INTO MCESale VALUES (3290, 27650.00, '27-NOV-16', '1GK1006X676677889', '12050', '12500');

INSERT INTO MCESale VALUES (2231, 10900.00, '14-APR-16', '1GK1010X676677889', '12070', '12810');

INSERT INTO MCESale VALUES (355, 12500.00, '22-JUN-16', '1GK1012X676677889', '12060', '12810');

INSERT INTO MCESale VALUES (90012, 12500.00, '19-JUL-16', '1GK1015X676677889', '12066', '12303');

INSERT INTO MCESale VALUES (7745, 12500.00, '01-FEB-16', '1GK1014X676677889', '12070', '12303');

INSERT INTO MCESale VALUES (8100, 17500.00, '11-FEB-16', '1GK1002X676677889', '12070', '17000');

INSERT INTO MCESale VALUES (8200, 22500.00, '24-FEB-16', '1GK1004X676677889', '12070', '12900');

INSERT INTO MCESale VALUES (7900, 18500.00, '01-MAR-16', '1GK1007X676677889', '32100', '12600');

INSERT INTO MCESale VALUES (7901, 21500.00, '10-APR-16', '1GK1008X676677889', '17777', '12500');

INSERT INTO MCESale VALUES (6667, 19500.00, '15-MAY-16', '1GK1009X676677889', '15670', '12909');

INSERT INTO MCESale VALUES (6800, 32500.00, '28-MAY-16', '1GK1011X676677889', '11111', '12909');

INSERT INTO MCESale VALUES (2220, 19999.00, '05-JUN-16', '1GK1013X676677889', '17070', '12303');

INSERT INTO MCESale VALUES (8111, 16700.00, '22-JUN-16', '1GK1016X676677890', '22900', '14051');

INSERT INTO MCESale VALUES (6700, 21777.00, '03-JAN-16', '1GK1016X676677900', '13555', '14051');

INSERT INTO MCESale VALUES (6999, 9500.00, '21-JAN-16', '1GK1016X676677910', '18919', '10019');

INSERT INTO MCESale VALUES (2555, 14447.00, '14-MAR-16', '1GK1016X676677912', '14010', '14051');

INSERT INTO MCESale VALUES (9999, 26660.00, '18-APR-16', '1GK1016X676677915', '19070', '15010');

INSERT INTO MCESale VALUES (6777, 22888.00, '01-JUL-16', '1GK1016X676677917', '22900', '13110');

INSERT INTO MCESale VALUES (1200, 22500.00, '01-AUG-16', '1GK1016X676677919', '13555', '16900');

INSERT INTO MCESale VALUES (7788, 24500.00, '22-AUG-16', '1GK1016X676677929', '11116', '15010');

INSERT INTO MCESale VALUES (3369, 18580.00, '19-SEP-16', '1GK1016X676677931', '17070', '13110');

INSERT INTO MCESale VALUES (4411, 33500.00, '22-SEP-16', '1GK1016X676677940', '17777', '16900');

INSERT INTO MCESale VALUES (5651, 20500.00, '30-OCT-16', '1GK1016X676677945', '18070', '12800');

INSERT INTO MCESale VALUES (6101, 17770.00, '14-OCT-16', '1GK1016X676677950', '11116', '17000');

INSERT INTO MCESale VALUES (9811, 28500.00, '15-DEC-16', '1GK1016X676677955', '32100', '12101');

commit;

The SQL Operations for Phase III of the project were divided up among the three group members.

1. Morgan Kelly was assigned questions 3, 6, 9, 11, 13, and 17.

Results –

Question #3 - Find the top 3 salespersons for each branch

select ename as EMP_NAME, ebranch as BRANCH_WORKS, sum(mcesale.sprice) as SALE_PRICE

from mceemployee

inner join

mcesale on mcesale.empid = mceemployee.empid group by ename, ebranch

order by ebranch, sale_price desc;

Question #6 - Find the most expensive vehicles in the inventory

SELECT

sprice as SALES_PRICE, mceinventory.model as MODEL, mceinventory.make as MAKE, mceinventory.type as Type, mceinventory.VIN as VIN

FROM mcesale

Inner JOIN

mceinventory ON mceinventory.vin = mcesale.Vin

where rownum <= 3

order by sales_price desc;

Question #9 - Find the most expensive Trucks (top 3) in the inventory

SELECT

sprice as SALES_PRICE, mceinventory.model as MODEL, mceinventory.make as MAKE, mceinventory.type as TRUCK, mceinventory.VIN as VIN

FROM mcesale

Inner JOIN

mceinventory ON mceinventory.vin = mcesale.Vin

where mceinventory.type = 'T' and rownum <= 3

order by sales_price desc;

Question #11 - Find the top employee for a service award

with EmpService AS

(select empID, count (*) as numSales

from MCESale

group by empID)

select *

from EmpService

WHERE numSales = (select max(numSales) from EmpService);

Question #13 – Cascade delete data from tables – those that have dependencies should delete the correct data.

(in create table statements) I added on delete cascade to all foreign keys

Question #17 - List the inventory of each branch (result = branch_name and vehicle info)

Select *

from MCEInventory

order by bName;

2. Phil Stevens was assigned questions 1, 4, 5, 8, 10, 18, and 19.

Results –

Question #1 - Insert new data to the MCEEmployees and MCEInventory tables

a. Added the Employee start date to the insert statements

b. Added the Inventory vehicle type to the MCEInventory table

i. T = Truck

ii. S = SUV

iii. C = Car

Question #4 - Find the top 3 salesperson for all branches combined (our Top 3 breadwinners).

WITH sums AS

(

select ename as EMP_NAME, ebranch as BRANCH_WORKS, sum(mcesale.sprice) as SALE_PRICE, MAX(SUM(mcesale.SPRICE)) over () max_sum

from mceemployee

inner join

mcesale on mcesale.empid = mceemployee.empid

group by ename, ebranch

)

SELECT *

from (select emp_name, branch_works, sale_price from sums order by sale_price desc) salesresult

where rownum <= 3

order by rownum;

Question #5 - Find the best 3 customers (customers who purchase the most vehicles by count in descending order).

With Customers AS

(

select customerID as Customer, count(saleid) as Final_Count

from mcesale

inner join mceinventory on mceinventory.vin = mcesale.vin

group by customerID

)

Select customer, cname, final_count

from (select customer, Final_Count from customers order by Final_Count desc) customer_sales

join mcecustomer on mcecustomer.customerID = customer_sales.customer

where rownum <= 3

order by final_count desc;

Question #8 - Find the most expensive SUV’s sold (top 3 in inventory).

With SUVSales As

(

select *

from mcesale

inner join mceinventory on mceinventory.vin = mcesale.vin

where mceinventory.type = 'S'

order by mcesale.sprice desc

)

SELECT *

from (select sprice as sales_price, myear as Model_Year, Make, Type as SUV from SUVSales order by SUVSales.sprice desc) SUV_salesresult

where rownum <= 3

order by rownum;

Question #10 - Find the top salesperson overall from the whole company

WITH sums AS

(

select ename as EMP_NAME, ebranch as BRANCH_WORKS, sum(mcesale.sprice) as SALE_PRICE, MAX(SUM(mcesale.SPRICE)) over () max_sum

from mceemployee

inner join

mcesale on mcesale.empid = mceemployee.empid

group by ename, ebranch

)

SELECT emp_name, branch_works, sale_price

FROM sums

WHERE sale_price = max_sum;

Question #18 - Give a list of all employees from their start dates in order from most seniority to least seniority.

select *

from mceemployee

order by estartdate asc;

3. Dhafer Alamri was assigned questions 2, 7, 12, 14, 15, and 16.

I did not receive the work completed.

13 | Page