Use a subquery to write an SQL statement that will list the FirstName,LastName a

Question

Use a subquery to write an SQL statement that will list the FirstName,LastName and Phone of customers who made a purchase (Sale Subtotal) of at least $500. Each customer should be listed only once
Write a SQL statement that will list the Employee FirstName, LastName, Phone, SaleDate, and Subtotal for Employees who have initiated sale subtotals of $3000 and above.
Write an SQL statement that will list the ItemId, ItemDescription, ItemPrice, CompanyName of vendor of item with ItemID 1.
Write an SQL statement that will list the Customer LastName, FirstName, Phone with two additional columns- the total number of sales as NumberSalesTranscations and total sum of sales as TotalSalesAmount
Write an SQL statement that will list the ItemID and ItemDescription of items that have not had a sale transaction. In other words items that do not appear in the Sale_Item table

CUSTOMER customerID LastName First Name Addre City State ZIP Phone Eman EMPLOYEE Employee ID LastName Name Phone Emai SALE SaleID CustomerID EmployeeID Sale Date SubTotal Tax Total SALE ITEM aleID Y SaleltemID ItemID Item Price ITEM ItemID ItemDescription Purchase Date ItemCost Item Price VendorID VENDOR VendorID Company Name Contact Last Name ContactFi Name Addres City State ZIP Phone Fax Ema

Explanation / Answer

1.

SELECT DISTINCT FirstName,LastName,Phone

FROM CUSTOM C

WHERE C.CustomorID IN (SELECT CustomerID

FROM SALE

WHERE SubTotal>=500);

2.

SELECT C.FirstName, C.LastName, C.Phone, S.SaleDate, S.SubTotal

FROM EMPLOYEE C,SALE S

WHERE C.EmployeeID=S.EmployeeID AND

S.SubTotal>=3000;

3.

SELECT I.ItemID, I.ItemDescription, I.ItemPrice, V.CompanyName

FROM ITEM I, VENDOR V

WHERE I.VendorID=V.VendorID AND I.ItemID=1;

4.

SELECT LastName, FirstName, Phone, COUNT(SaleID), SUM(Total)

FROM CUSTOMER INNER JOIN SALE

ON CUSTOMER.CustomerID=SALE.CustomerID;

5.

SELECT  ItemID, ItemDescription

FROM ITEM

WHERE ItemID NOT IN ( SELECT ItemID

FROM SALE_ITEM);

  

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.