Question

Customer Product Manufacturer ManufacturerID ManufacturerName Addressi Address2 City State PostalCode Phone Fax Contact URL Sale Saleltem CustomerID FirstName LastName StreetAddress City State PostalCode Country Phone SalelD SaleDate CustomerID Tax Shipping SaleID ProductiD ItemSize Quantity SalePrice ProductID ProductName ManufacturerID Composition ListPrice Gender Category Color Description Employee EmployeelD o FirstName LastName Address City State ZIP Phone ManagerlD SSN EmailAddress HireDate SalaryEmployee EmployeelD o Salary WageEmployee EmployeelD o Wage MaxHours

Answer 1

Select CustomerID,Sale.SaleID,SUM(SalePrice) as ‘TotalAmt’
FROM Sale,SaleItem
WHERE Sale.SaleID = SaleItem.SaleID
GROUP BY Sale.SaleID
HAVING SUM(SalePrice) > 500

--for the saleId in sale table take the corresponding salid in saleitem table
--take the sum of saleprice and by groupby condition,they are grouped by saleID which means we can find the sum of saleprice --for each group.Having condition only includes those sum values which are having greater than 500

select ProductName,ListPrice
from SaleItem,Product
where SaleItem.ProductID = Product.ProductID and SaleItem.ProductID IN (select ProductID from SaleItem where Quantity =2)

-- In the subquery we've taken the ProductID of those saleItem which has quantity = 2
-- in the where clause of main query we are searching for the ProductID using IN statement