Using subqueries only
List all customers who purchased products during 2019. Includetheir first and last name (strung together and renamed as CustName)and the customer number. Leave normal spacing between the twonames.
Code:
Results:
USA Products Datahase Tables/Fields CUSTOMER dbo.CUSTOMER E Columns CustID (PK, int, not null) CustLastName (char(20), not null) CustFirstName (char(20), not null) CustAddress (char(30), not null) CustCity (char(20), not null) CustState (char(2), not null) CustZIP (char(10), not null) CustPhone (char(14), null) CustNotes (varchar(200), null) PRODUCT dbo.PRODUCT Columns ProdID (PK, int not null) ProdName (char(30), not null) ProdCategory (char(12), not null) ProdDescription (varchar(300), null) ProdSupplier (FK, char(12), not null) ProdlnvQty (int, null) ProdUnitPrice (numeric(8,2), null) ProdUnitCost (numeric(8.2), null) ProdDiscontinued (char(1), null) ITEM : Hbo.ITEM Columns ItemID (PK, int not null) ItemSalelD (FK, int, not null) Item ProdID (FK, int, not null) ItemQty (numeric(8.2), not null) ItemUnitPrice (numeric(8.2), not null) SALE VENDOR 3 dbo.SALE dbo.VENDOR o Columns 6 Columns VendorName (PK, char(12), not null) SaleID (PK, int, not null) VendorContactName (char(50), null) SaleCust# (FK, int, not null) VendorNotes (varchar(200), null) SaleDate (datetime, not null) VendorPhone (char(14), null) VendorWeb (char(100), null) IMPORTANT NOTES: When referring to “sales” always be sure to obtain data from the SALE and ITEM tables When referring to “inventory” or “value” always be sure to obtain data from the PRODUCT table. When referring to “profit” it will always be (Price – Cost) When referring to “value” it will always be (Quantity * Price) Show transcribed image text USA Products Datahase Tables/Fields CUSTOMER dbo.CUSTOMER E Columns CustID (PK, int, not null) CustLastName (char(20), not null) CustFirstName (char(20), not null) CustAddress (char(30), not null) CustCity (char(20), not null) CustState (char(2), not null) CustZIP (char(10), not null) CustPhone (char(14), null) CustNotes (varchar(200), null) PRODUCT dbo.PRODUCT Columns ProdID (PK, int not null) ProdName (char(30), not null) ProdCategory (char(12), not null) ProdDescription (varchar(300), null) ProdSupplier (FK, char(12), not null) ProdlnvQty (int, null) ProdUnitPrice (numeric(8,2), null) ProdUnitCost (numeric(8.2), null) ProdDiscontinued (char(1), null) ITEM : Hbo.ITEM Columns ItemID (PK, int not null) ItemSalelD (FK, int, not null) Item ProdID (FK, int, not null) ItemQty (numeric(8.2), not null) ItemUnitPrice (numeric(8.2), not null) SALE VENDOR 3 dbo.SALE dbo.VENDOR o Columns 6 Columns VendorName (PK, char(12), not null) SaleID (PK, int, not null) VendorContactName (char(50), null) SaleCust# (FK, int, not null) VendorNotes (varchar(200), null) SaleDate (datetime, not null) VendorPhone (char(14), null) VendorWeb (char(100), null) IMPORTANT NOTES: When referring to “sales” always be sure to obtain data from the SALE and ITEM tables When referring to “inventory” or “value” always be sure to obtain data from the PRODUCT table. When referring to “profit” it will always be (Price – Cost) When referring to “value” it will always be (Quantity * Price)
Expert Answer
Answer to Using subqueries only List all customers who purchased products during 2019. Include their first and last name (strung t…