Question
Using the given table info, please create the following SQL query: /* Delete Table if it already exists before recreating table and populating FOR TESTING
Using the given table info, please create the following SQL query: /* Delete Table if it already exists before recreating table and populating FOR TESTING */ if OBJECT_ID('tblPurchaseHistory') IS NOT NULL DROP TABLE tblPurchaseHistory; if OBJECT_ID('tblReceiver') IS NOT NULL DROP TABLE tblReceiver; if OBJECT_ID('tblPurchaseOrderLine') IS NOT NULL DROP TABLE tblPurchaseOrderLine; if OBJECT_ID('tblProduct') IS NOT NULL DROP TABLE tblProduct; IF OBJECT_ID('tblProductType') IS NOT NULL DROP TABLE tblProductType; IF OBJECT_ID('tblPurchaseOrder') IS NOT NULL DROP TABLE tblPurchaseorder; IF OBJECT_ID('tblCondition') IS NOT NULL DROP TABLE tblCondition; IF OBJECT_ID('tblEmployee') IS NOT NULL DROP TABLE tblEmployee; IF OBJECT_ID('tblVendor') IS NOT NULL DROP TABLE tblVendor; /*Create tblVendor Table */ CREATE TABLE tblVendor (VendorID CHAR(5) PRIMARY KEY NOT NULL, Name VARCHAR(30) NOT NULL, Address1 VARCHAR(30) NOT NULL, Address2 VARCHAR(30), City VARCHAR(20) NOT NULL, State CHAR(2) NOT NULL, Zip VARCHAR(12) NOT NULL, Email VARCHAR(30), Contact VARCHAR(30), Phone CHAR(15) NOT NULL, FirstBuyDate DATETIME NOT NULL); INSERT INTO tblVendor VALUES ('00216', 'PolySort Manufacturing', '2550 23rd Avenue', NULL, 'Denver', 'CO', '80568', 'fxd@polysort.com', 'Francisco Delgado', '3035558123', '2016-07-28 00:00:00'), ('09567', 'Apex Mills', '3500 Industrial Parkway', 'Unit 7','SPARKS', 'Nv', '89431', 'tcc@yahoo.com', NULL, '7755552894', '2013-03-13 00:00:00'), ('13135', 'Adventure Materials', 'P.O. Box 2700', NULL, 'SALT LAKE CITY', 'UT', '84560-2700', 'info@advmat.com', NULL, '8015554500', '2016-08-04 00:00:00'), ('17453', 'Albemarle Corporation', '2355-B Vista Drive', 'Suite 765-B','Sparks', 'NV', '89431', 'sandp@msn.com', 'maryanne Jones', '7755553451', '1998-06-14 00:00:00'), ('18567', 'Alcan Plastic, Inc.', 'P.O. Box 4456', NULL, 'Eagle Falls', 'AK', '99565', 'alcanp@plastic.com', NULL, '9075555268', '2016-05-15 00:00:00'), ('20566', 'BestCo Food Equipment', '1515 Kendall Mill Road', NULL, 'Allentown', 'PA', '15003', 'buyer@best.com', NULL, '4845556789', '2017-09-14 00:00:00'), ('22890', 'Kitchen Chemicals Corp.', '7750 Rock Blvd.', NULL, 'sparks', 'nv', '89431-5602', 'info@kchem.corp', 'melinda', '7755552566', '2013-07-23 00:00:00'), ('36257', 'Injectomatic Mold Corp.', '14557 Hawthorne Blvd.', 'Unit 14','los angeles', 'CA', '90036-9960', 'hayman@inject.com', 'Sam hayes', '2135554963', '2017-06-15 00:00:00'), ('45899', 'Celanette Design, LLC', '9865 Sepulveda Blvd.', 'Suite B','Los Angeles', 'ca', '90045-3660', 'buyer@celanette.com', 'Linda Burch', '3105555545', '2017-09-23 00:00:00'), ('87654', 'Recycle Plastics Company', '10 Riverview Highway', NULL, 'detroit', 'mi', '48050', 'vendor@rpc.com', NULL, '3135554266', '2014-08-16 00:00:00'); /*Create tblEmployee Table*/ CREATE TABLE tblEmployee (EmpID CHAR(6) PRIMARY KEY, EmpLastName VARCHAR(30) NOT NULL, EmpFirstName VARCHAR(30) NOT NULL, EmpEmail VARCHAR(30), EmpPhone CHAR(15) NOT NULL, EmpMgrID CHAR(6), FOREIGN KEY (EmpID) REFERENCES tblEmployee (EmpID)); INSERT INTO tblEmployee VALUES ('E10003', 'Armstrong', 'Evelyn', 'EARMSTRONG@yahoo.com', '7755423212', NULL), ('E10009', 'Hernandez', 'Nathan', NULL,'7755313562','E10003'), ('E10015', 'Fetters', 'Sam', 'SFETTERS@yahoo.com', '7753453821','E10003'), ('E10018', 'Schnitkowski', 'Michael', 'MSCHNIT@yahoo.com', '7776553582','E10003'), ('E10026', 'Williams', 'Barbara Ray', 'BWILLIAMS@yahoo.com', '7755313834','E10015'), ('E10042', 'Van Meter', 'Juliette', 'JVANMETER@yahoo.com', '7758453551','E10026'), ('E10055', 'Smith', 'Jeannie Anne', 'JSMITH2@yahoo.com', '7759153891', NULL), ('E10056', 'Chen', 'John', NULL,'7759053821', 'E10015'), ('E10057', 'Pinot', 'Jean Claude', 'JPINOT@yahoo.com', '7756749002', NULL), ('E10077', 'MacAndrews-Abernethy', 'Elizabeth Victoria', 'EMACANDREWSABERNET@yahoo.com', '7755553894', 'E10026'), ('E10085', 'Wong', 'Thomas', 'TNG@yahoo.com', '7757783511','E10026'), ('E10087', 'Ursenbach', 'Wolter', 'WURSENBACH@yahoo.com', '7757821341','E10026'), ('E10088', 'OToole', 'Timothy', 'TOTOOL@yahoo.com', '7758423529', 'E10026'), ('E10101', 'Kelly', 'Kendall', 'KKELLY@yahoo.com', '7758413838', 'E10026'), ('E10192', 'Alberghetti', 'Antonio', NULL, '7756219005', NULL); /*Create tblCondition Table*/ CREATE TABLE tblCondition (ConditionID CHAR(2) PRIMARY KEY NOT NULL, Description VARCHAR(30) NOT NULL); INSERT INTO tblCondition VALUES ('FD', 'Freight damage'), ('IP', 'Improper packaging'), ('OK', 'Acceptable'), ('OT', 'Other'), ('UK', 'Unknown'), ('WD', 'Water damage'); /*Create tblPurchaseOrder Table */ CREATE TABLE tblPurchaseOrder (PoNumber CHAR(6) PRIMARY KEY NOT NULL, PODatePlaced DATETIME NOT NULL, PODateNeeded DATETIME, Terms VARCHAR(15), Conditions VARCHAR(15), BuyerEmpID CHAR(6), VendorID CHAR(5) NOT NULL, FOREIGN KEY (BuyerEmpID) REFERENCES tblEmployee (EmpID), FOREIGN KEY (VendorID) REFERENCES tblVendor (VendorID)); INSERT INTO tblPurchaseOrder VALUES ('025974', '08/15/2017 00:00:00', '08/18/2017 00:00:00', 'Net 15', 'FOB-AIR', 'E10015', '18567'), ('045687', '08/21/2017 00:00:00', '09/12/2017 00:00:00', 'COD', NULL, 'E10055', '00216'), ('056489', '08/04/2017 00:00:00', '08/15/2017 00:00:00', NULL, 'FOB', 'E10055', '36257'), ('112233', '09/21/2017 00:00:00', '10/25/2017 00:00:00', 'Net10', NULL, 'E10026', '17453'), ('234607', '09/04/2017 00:00:00', '09/28/2017 00:00:00', 'Net 30', 'FOB', 'E10055', '17453'), ('256887', '09/19/2017 00:00:00', '10/15/2017 00:00:00', 'Net 30', 'FOB', NULL, '87654'), ('329987', '10/10/2017 00:00:00', '01/12/2018 00:00:00', 'Net 30', 'FOB', 'E10101', '18567'), ('365870', '09/14/2017 00:00:00', '03/14/2018 00:00:00', 'Net 30', NULL, 'E10101', '17453'), ('543791', '09/15/2017 00:00:00', '01/15/2018 00:00:00', 'Net 30', NULL, 'E10055', '45899'), ('600124', '10/01/2017 00:00:00', '12/20/2017 00:00:00', 'COD', NULL, 'E10055', '00216'), ('661677', '09/30/2017 00:00:00', '11/15/2017 00:00:00', 'Net 15', 'FOB', 'E10101', '00216'), ('781900', '10/03/2017 00:00:00', '11/12/2017 00:00:00', NULL, 'FOB', 'E10055', '09567'), ('902347', '09/16/2017 00:00:00', '10/18/2017 00:00:00', NULL, 'Pickup', 'E10087', '09567'); /*Create tblProductType Table */ CREATE TABLE tblProductType (ProductTypeID CHAR(2) PRIMARY KEY NOT NULL, Description VARCHAR(30) NOT NULL); INSERT INTO tblProductType VALUES ('CC', 'Camping and Cooking'), ('CS', 'Camping and Resting'), ('HT', 'Hiking and Trails'), ('LA', 'Comfort Essentials'), ('MS', 'Miscellaneous'), ('PG', 'Travel Bags'), ('UT', 'Utility Materials'); /*Create tblProduct Table */ CREATE TABLE tblProduct (ProductID CHAR(5) PRIMARY KEY NOT NULL, Description VARCHAR(30) NOT NULL, UOM CHAR(10) CHECK (UOM IN ('each', 'feet', 'inches', 'meters', 'cm', 'sheet', 'case')), EOQ DECIMAL(6,2), QOH DECIMAL(8,2) NOT NULL, ProductTypeID CHAR(2) FOREIGN KEY REFERENCES tblProductType (ProductTypeID) NOT NULL); INSERT INTO tblProduct VALUES ('A7879', 'Canvas, Non-Woven', 'feet', 2000.00, 1200.00, 'CS'), ('C2399', 'Thermoplastic', 'sheet', 10.00, 5.00, 'CS'), ('C9100', 'Unbleached Muslin', 'meters', 8000.00, 5500.00, 'CS'), ('G0983', 'Alpine Small Pot', 'each', 100.00, 65.00, 'HT'), ('G1258', 'Alpine Pot/Kettle Handle', 'each', 50.00, 96.00, 'UT'), ('G1366', 'Alpine Pot/Kettle Insert', 'each', 48.00, 55.00, 'UT'), ('G5698', 'Alpine Skillet Handle Set', 'each', 48.00, 50.00, 'UT'), ('J8006', 'Microfilter tubing', 'feet', 450.00, 550.00, 'MS'), ('L8500', 'Hiking Lounge Seating - Blue', 'each', 15.00, 1.00, 'LA'), ('L8501', 'Hiking Lounge Seating - Gray', 'each', 10.00, .00, 'LA'), ('M2356', 'Cot Mesh - Ultralite', 'meters', 1000.00, 1200.00, 'MS'), ('M3577', 'Cot Mesh - Sturdy', 'feet', 300.00, 650.00, 'MS'), ('O1957', 'Poly pro tubing, 1/2"', 'feet', 300.00, 95.00, 'MS'), ('P5678', 'Stuff Sacks - Pillow Size', 'case', 48.00, 40.00, 'PG'), ('P7844', 'Down Baffle Liner', 'meters', 50.00, 45.00, 'MS'), ('R5660', 'Water Filtration Pump', 'each', 30.00, 25.00, 'CC'), ('T0460', 'Alpine Water Bottle', 'each', 100.00, 15.00, 'CC'); /*Create tblPurchaseProductOrderLine Table */ CREATE TABLE tblPurchaseOrderLine (PONumber CHAR(6) FOREIGN KEY REFERENCES tblPurchaseOrder (PoNumber) NOT NULL, ProductID CHAR(5) FOREIGN KEY REFERENCES tblProduct (ProductID) NOT NULL, QtyOrdered DECIMAL(6,2) CHECK (QtyOrdered>0), Price MONEY NOT NULL, DateNeeded DATETIME NOT NULL, PRIMARY KEY(PONumber, ProductID, DateNeeded)); INSERT INTO tblPurchaseOrderLine VALUES ('025974', 'M3577', 600.00, 4.63, '08/18/2017 00:00:00'), ('025974', 'O1957', 300.00, 0.46, '08/18/2017 00:00:00'), ('045687', 'C2399', 40.00, 2.99, '09/05/2017 00:00:00'), ('045687', 'L8500', 15.00, 29.64, '09/10/2017 00:00:00'), ('045687', 'O1957', 450.00, 0.29, '09/12/2017 00:00:00'), ('056489', 'M3577', 600.00, 5.29, '08/15/2017 00:00:00'), ('112233', 'M3577', 600.60, 5.89, '10/07/2017 00:00:00'), ('112233', 'P7844', 500.25, 1.5, '09/24/2017 00:00:00'), ('234607', 'C2399', 75.00, 1.75, '09/06/2017 00:00:00'), ('234607', 'G0983', 200.00, 1.5, '09/08/2017 00:00:00'), ('234607', 'G1366', 182.00, 4.89, '09/10/2017 00:00:00'), ('234607', 'G5698', 182.00, 1.22, '09/19/2017 00:00:00'), ('234607', 'R5660', 100.00, 1.99, '09/26/2017 00:00:00'), ('234607', 'T0460', 200.00, 2.75, '09/26/2017 00:00:00'), ('256887', 'C2399', 20.00, 2.5, '10/15/2017 00:00:00'), ('256887', 'P5678', 48.00, 22.5, '10/15/2017 00:00:00'), ('329987', 'O1957', 300.00, 0.41, '11/15/2017 00:00:00'), ('329987', 'T0460', 50.00, 3.1, '01/12/2018 00:00:00'), ('365870', 'G0983', 120.00, 1.85, '12/14/2017 00:00:00'), ('365870', 'R5660', 75.00, 1.59, '11/10/2017 00:00:00'), ('365870', 'T0460', 120.00, 2.39, '03/14/2018 00:00:00'), ('543791', 'G0983', 100.00, 1.89, '09/17/2017 00:00:00'), ('543791', 'G0983', 20.00, 2.19, '09/28/2017 00:00:00'), ('543791', 'G0983', 20.00, 2.25, '10/02/2017 00:00:00'), ('543791', 'G0983', 25.00, 2.25, '10/12/2017 00:00:00'), ('543791', 'G0983', 45.00, 2.15, '11/15/2017 00:00:00'), ('543791', 'G0983', 25.00, 2.15, '12/10/2017 00:00:00'), ('543791', 'G1366', 48.00, 1.89, '01/10/2018 00:00:00'), ('543791', 'G5698', 12.00, 1.95, '10/15/2017 00:00:00'), ('543791', 'G5698', 48.00, 2.38, '01/10/2018 00:00:00'), ('543791', 'T0460', 120.00, 1.89, '10/12/2017 00:00:00'), ('543791', 'T0460', 50.00, 2.49, '12/15/2017 00:00:00'), ('600124', 'G0983', 100.00, 1.96, '10/18/2017 00:00:00'), ('600124', 'G1366', 150.00, 4.85, '10/18/2017 00:00:00'), ('600124', 'G5698', 100.00, 1.87, '10/18/2017 00:00:00'), ('661677', 'L8500', 8.00, 26.45, '11/15/2017 00:00:00'), ('781900', 'C9100', 800.00, 3.45, '10/15/2017 00:00:00'), ('781900', 'M3577', 750.00, 5.55, '11/10/2017 00:00:00'), ('902347', 'G0983', 100.00, 2.25, '10/15/2017 00:00:00'), ('902347', 'T0460', 100.00, 2.25, '09/22/2017 00:00:00'), ('902347', 'T0460', 450.00, 1.39, '12/05/2017 00:00:00'); /*Create tblReceiver Table */ CREATE TABLE tblReceiver (ReceiverID INT PRIMARY KEY IDENTITY(1,1), DateReceived DATETIME NOT NULL, PONumber CHAR(6), ProductID CHAR(5), DateNeeded DATETIME, QtyReceived DECIMAL(6,2) CHECK (QtyReceived>0), ConditionID CHAR(2) NOT NULL FOREIGN KEY REFERENCES tblCondition (ConditionID), ReceiveEmpID CHAR(6) FOREIGN KEY REFERENCES tblEmployee (EmpID), FOREIGN KEY (PONumber, ProductID, DateNeeded) REFERENCES tblPurchaseOrderLine (PONumber, ProductID, DateNeeded)); INSERT INTO tblReceiver VALUES ('08/15/2017 00:00:00', '025974', 'M3577', '08/18/2017 00:00:00', 400.00, 'OK', NULL), ('08/16/2017 00:00:00', '025974', 'M3577', '08/18/2017 00:00:00', 100.00, 'OK', NULL), ('08/17/2017 00:00:00', '025974', 'M3577', '08/18/2017 00:00:00', 100.00, 'WD', NULL), ('08/15/2017 00:00:00', '025974', 'O1957', '08/18/2017 00:00:00', 100.00, 'OK', 'E10018'), ('08/17/2017 00:00:00', '025974', 'O1957', '08/18/2017 00:00:00', 210.00, 'OK', 'E10018'), ('09/05/2017 00:00:00', '045687', 'C2399', '09/05/2017 00:00:00', 40.00, 'OK', 'E10042'), ('09/14/2017 00:00:00', '045687', 'C2399', '09/05/2017 00:00:00', 11.00, 'FD', 'E10042'), ('09/05/2017 00:00:00', '045687', 'O1957', '09/12/2017 00:00:00', 400.00, 'OK', 'E10042'), ('09/15/2017 00:00:00', '045687', 'O1957', '09/12/2017 00:00:00', 40.00, 'OK', 'E10042'), ('09/07/2017 00:00:00', '234607', 'C2399', '09/06/2017 00:00:00', 13.00, 'FD', 'E10018'), ('09/08/2017 00:00:00', '234607', 'C2399', '09/06/2017 00:00:00', 22.00, 'IP', 'E10018'), ('09/12/2017 00:00:00', '234607', 'C2399', '09/06/2017 00:00:00', 40.00, 'WD', 'E10018'), ('09/07/2017 00:00:00', '234607', 'G0983', '09/08/2017 00:00:00', 200.00, 'WD', 'E10042'), ('09/07/2017 00:00:00', '234607', 'G1366', '09/10/2017 00:00:00', 150.00, 'OK', 'E10087'), ('09/10/2017 00:00:00', '234607', 'G1366', '09/10/2017 00:00:00', 25.00, 'OK', 'E10087'), ('09/14/2017 00:00:00', '234607', 'G1366', '09/10/2017 00:00:00', 7.00, 'OK', 'E10087'), ('09/22/2017 00:00:00', '234607', 'G5698', '09/19/2017 00:00:00', 182.00, 'OK', NULL), ('09/22/2017 00:00:00', '234607', 'R5660', '09/26/2017 00:00:00', 50.00, 'OK', NULL), ('09/26/2017 00:00:00', '234607', 'R5660', '09/26/2017 00:00:00', 50.00, 'OK', NULL), ('09/22/2017 00:00:00', '234607', 'T0460', '09/26/2017 00:00:00', 200.00, 'OK', 'E10087'), ('09/23/2017 00:00:00', '256887', 'P5678', '10/15/2017 00:00:00', 40.00, 'OK', NULL), ('09/25/2017 00:00:00', '256887', 'P5678', '10/15/2017 00:00:00', 9.00, 'OK', NULL), ('09/26/2017 00:00:00', '256887', 'C2399', '10/15/2017 00:00:00', 20.00, 'OK', 'E10042'), ('09/25/2017 00:00:00', '543791', 'G0983', '09/17/2017 00:00:00', 50.00, 'FD', 'E10042'), ('09/24/2017 00:00:00', '543791', 'G0983', '09/17/2017 00:00:00', 50.00, 'OK', 'E10042'), ('09/29/2017 00:00:00', '543791', 'G0983', '09/28/2017 00:00:00', 20.00, 'FD', 'E10042'), ('09/25/2017 00:00:00', '543791', 'G0983', '10/02/2017 00:00:00', 20.00, 'IP', 'E10042'), ('10/09/2017 00:00:00', '543791', 'G0983', '10/12/2017 00:00:00', 20.00, 'OK', 'E10042'), ('10/14/2017 00:00:00', '543791', 'G0983', '10/12/2017 00:00:00', 5.00, 'OK', 'E10042'), ('10/19/2017 00:00:00', '543791', 'G5698', '10/15/2017 00:00:00', 12.00, 'OK', 'E10087'), ('10/08/2017 00:00:00', '543791', 'T0460', '10/12/2017 00:00:00', 100.00, 'OK', 'E10087'), ('10/09/2017 00:00:00', '543791', 'T0460', '10/12/2017 00:00:00', 10.00, 'WD', 'E10087'), ('10/11/2017 00:00:00', '543791', 'T0460', '10/12/2017 00:00:00', 10.00, 'UK', NULL), ('10/15/2017 00:00:00', '600124', 'G0983', '10/18/2017 00:00:00', 10.00, 'WD', 'E10087'), ('10/16/2017 00:00:00', '600124', 'G0983', '10/18/2017 00:00:00', 100.00, 'OK', 'E10087'), ('10/28/2017 00:00:00', '600124', 'G1366', '10/18/2017 00:00:00', 150.00, 'UK', 'E10087'), ('10/14/2017 00:00:00', '600124', 'G5698', '10/18/2017 00:00:00', 10.00, 'UK', 'E10042'), ('10/16/2017 00:00:00', '600124', 'G5698', '10/18/2017 00:00:00', 60.00, 'OK', 'E10042'), ('10/17/2017 00:00:00', '600124', 'G5698', '10/18/2017 00:00:00', 30.00, 'UK', NULL), ('10/14/2017 00:00:00', '902347', 'G0983', '10/15/2017 00:00:00', 45.00, 'UK', 'E10087'), ('10/15/2017 00:00:00', '902347', 'G0983', '10/15/2017 00:00:00', 45.00, 'OK', 'E10101'), ('10/05/2017 00:00:00', '902347', 'G0983', '10/15/2017 00:00:00', 10.00, 'OK', 'E10101'), ('09/19/2017 00:00:00', '902347', 'T0460', '09/22/2017 00:00:00', 95.00, 'OK', 'E10101'), ('09/24/2017 00:00:00', '902347', 'T0460', '09/22/2017 00:00:00', 5.00, 'OK', 'E10101'), ('09/19/2017 00:00:00', '902347', 'T0460', '12/05/2017 00:00:00', 100.00, 'UK', NULL), ('09/23/2017 00:00:00', '112233', 'P7844', '09/24/2017 00:00:00', 475.25, 'OK', 'E10101'), ('09/28/2017 00:00:00', '112233', 'M3577', '10/07/2017 00:00:00', 300.45, 'OK', 'E10101'), ('10/25/2017 00:00:00', '112233', 'M3577', '10/07/2017 00:00:00', 340.50, 'WD', 'E10101'); /*Create tblPurchaseHistory Table */ CREATE TABLE tblPurchaseHistory (HistoryID INT PRIMARY KEY IDENTITY(1,1), ProductID CHAR(5) FOREIGN KEY REFERENCES tblProduct (ProductID) NOT NULL, DatePurchased DATETIME NOT NULL, Qty DECIMAL(8,2) NOT NULL, Price MONEY NOT NULL, VendorID CHAR(5) FOREIGN KEY REFERENCES tblVendor (VendorID)); INSERT INTO tblPurchaseHistory VALUES ('A7879', '07/21/2014 00:00:00', 10000.00, 0.07, '09567'), ('A7879', '03/15/2015 00:00:00', 10000.00, 0.08, '09567'), ('A7879', '10/16/2016 00:00:00', 8000.00, 0.1, '17453'), ('A7879', '03/15/2017 00:00:00', 7500.00, 0.12, '17453'), ('C2399', '04/14/2014 00:00:00', 3000.00, 1.45, '87654'), ('C2399', '01/25/2016 00:00:00', 150.00, 1.75, '36257'), ('C2399', '03/22/2016 00:00:00', 100.00, 1.95, '87654'), ('C2399', '08/15/2016 00:00:00', 150.00, 1.75, '36257'), ('C2399', '02/12/2017 00:00:00', 100.00, 1.99, '87654'), ('C2399', '05/16/2017 00:00:00', 350.00, 1.94, '18567'), ('C2399', '06/04/2017 00:00:00', 200.00, 1.55, '87654'), ('C2399', '08/12/2017 00:00:00', 350.00, 1.89, '18567'), ('G0983', '02/10/2015 00:00:00', 90.00, 2.15, '20566'), ('G0983', '05/10/2015 00:00:00', 250.00, 2.11, '18567'), ('G0983', '07/20/2015 00:00:00', 300.00, 2.1, '18567'), ('G0983', '09/22/2016 00:00:00', 200.00, 2.38, '17453'), ('G0983', '01/18/2017 00:00:00', 250.00, 1.99, '00216'), ('G0983', '03/12/2017 00:00:00', 75.00, 2.15, '17453'), ('G1258', '06/20/2017 00:00:00', 25.00, 4.29, '20566'), ('G1258', '07/21/2017 00:00:00', 25.00, 4.29, '20566'), ('G1366', '06/20/2017 00:00:00', 25.00, 4.81, NULL), ('G5698', '06/15/2016 00:00:00', 200.00, 2.16, NULL), ('G5698', '06/20/2017 00:00:00', 25.00, 2.21, NULL), ('L8500', '04/21/2015 00:00:00', 2.00, 29.45, '13135'), ('L8500', '02/02/2016 00:00:00', 5.00, 38.4, '13135'), ('L8500', '12/12/2016 00:00:00', 10.00, 26.22, '13135'), ('L8501', '12/12/2016 00:00:00', 15.00, 29.94, '13135'), ('M3577', '05/12/2014 00:00:00', 3200.00, 4.85, '18567'), ('M3577', '12/02/2014 00:00:00', 1200.00, 5.62, '18567'), ('M3577', '02/10/2015 00:00:00', 1200.00, 5.62, '18567'), ('M3577', '03/10/2015 00:00:00', 1509.00, 5.59, '18567'), ('M3577', '08/15/2015 00:00:00', 200.00, 5.95, '87654'), ('M3577', '11/06/2016 00:00:00', 300.00, 6.25, '87654'), ('M3577', '01/10/2017 00:00:00', 100.00, 5.91, '87654'), ('M3577', '07/12/2017 00:00:00', 150.00, 6.35, '18567'), ('O1957', '11/02/2015 00:00:00', 2500.00, 0.42, '18567'), ('O1957', '07/19/2016 00:00:00', 450.00, 0.41, '00216'), ('O1957', '09/18/2016 00:00:00', 2500.00, 0.42, '20566'), ('O1957', '10/03/2016 00:00:00', 100.00, 0.65, '18567'), ('O1957', '01/12/2017 00:00:00', 1400.00, 0.53, '00216'), ('O1957', '05/07/2017 00:00:00', 450.00, 0.44, '00216'), ('O1957', '07/23/2017 00:00:00', 200.00, 0.43, '20566'), ('P5678', '06/12/2017 00:00:00', 250.00, 23.51, NULL), ('P7844', '07/12/2017 00:00:00', 2570.00, 0.67, NULL), ('R5660', '02/27/2016 00:00:00', 80.00, 2.15, '17453'), ('R5660', '05/23/2017 00:00:00', 80.00, 2.12, '17453'), ('R5660', '06/18/2017 00:00:00', 4500.00, 2.1, NULL), ('T0460', '03/29/2015 00:00:00', 1200.00, 1.12, '45899'), ('T0460', '03/12/2016 00:00:00', 1200.00, 1.76, '45899'), ('T0460', '02/19/2017 00:00:00', 1500.00, 1.98, '45899');
MountainDesign wonders whether there might be fraud in the Receiving department. Which Employee received the most products that were damaged? List information about the employee, such as the EmployeeID, employee name, and employee email address. In addition, list the employeeID of the manager of that employee, the managers name and the managers email address. Use a max of the sum of the QtyReceived in the Receiver table to make the determination of the most received products. Assume that new ConditionIDs may be created in the future, so make sure that your query uses the description of the damage (in the condition table) and not the ConditionID for the actual search in the where clause. In other words, use the word damage in the WHERE clause to do the search because there might be new types of "damage" for received products in the future. Here is the result table:
EmployeelD EmployeeName empemail Manager EmplD Manager's Name Manager's Email Quantity of Damaged Items Received 1E10101 Kelly, K KKELLY@yahoo.com E10026 Williams, B BWILLIAMS@yahoo.com 340.50Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started