Questions

Advanced Queries:

Can you help me how to write these question in SQL

1. List the names of customers and how many distinct products they have ordered where that customer has ordered 40 or more distinct products.

(Hint: Make sure you get only distinct products.)

2. List the names of all customers who have placed an order and the total dollar amount ordered by that customer.

(Hint: To get the dollar amount for an order, you will need to multiply the order quantity by the price each in the order detail table.)

3. List the names of all customers who have placed an order, the total dollar amount ordered for all orders for that customer, the dollar amount paid for all orders for that customer, and the outstanding balance (amount that has not yet been paid) for each customer.

(Hint: If you try to mix the inner and outer joins for this query in the same FROM clause, you will probably get a meaningless result. Instead, use the queries from questions 9 and 10. Embed one of them as a subquery in the FROM clause of the other while adding the extra items being computed in the embedded query in the appropriate places in the primary query.)

SCHEMAS Filter objects classicmodels Tables customers dates employees offices orderdetails orders payments productlines products customers customerNumber INT (11) customerName VARCHAR(50) contact astName VARCHAR(50) contacFirstName VARCHAR(50) phone VARCHAR(50) addressLine1 VARCHAR(50) addressine2 VARCHAR(50) City VARCHAR(50) state VARCHAR(50) postal Code VARCHAR(15) country VARCHAR(50) salesRepemployeeNumber INT(11) creditLimit DOUBLE employees employeeNumber INT(11) lastName VARCHAR(50) firstName VARCHAR(50) extension VARCHAR(10) emal VARCHAR(100) officeCode VARCHAR(10) reportsTO INT(11) job Title VARCHAR(50) offices officeCode VARCHAR(10) City VARCHAR(50) phone VARCHAR(50) addressLine1 VARCHAR(50) addressLine2 VARCHAR(50) state VARCHAR(SO) country VARCHAR(50) postalCode VARCHAR(15) territory VARCHAR(10) orderdetails orderNumber INT(11) product Code VARCHAR(15) quantityOrdered INT(11) price ach DOUBLE orderlineNumber SMALLINT (6) dates FullDate DATE Mont Name CHAR(3) Year Number INT(11) QuarterNumber INT(11) Notes VARCHAR(150) payments customerNumber INT (11) check Number VARCHAR(50) paymentDate DATETIME amount DOUBLE orders order Number INT (11) order Date DATETIME requiredDat DATETIME shippedDate DATETIME status VARCHAR(15) comments TEXT customerNumber INT (11) products product Code VARCHAR(15) productName VARCHAR(70) productLine VARCHAR(SO) productScale VARCHAR(10) productVendor VARCHAR(50) product Description TEXT quantityInStock SMALLINT(6) buyPrice DOUBLE productlines productLine VARCHAR(50) -i textDescription VARCHAR(4000) htmlDescription MEDIUMTEXT image MEDIUMBL OB MSRP DOUBLE SCHEMAS Filter objects classicmodels Tables customers dates employees offices orderdetails orders payments productlines products customers customerNumber INT (11) customerName VARCHAR(50) contact astName VARCHAR(50) contacFirstName VARCHAR(50) phone VARCHAR(50) addressLine1 VARCHAR(50) addressine2 VARCHAR(50) City VARCHAR(50) state VARCHAR(50) postal Code VARCHAR(15) country VARCHAR(50) salesRepemployeeNumber INT(11) creditLimit DOUBLE employees employeeNumber INT(11) lastName VARCHAR(50) firstName VARCHAR(50) extension VARCHAR(10) emal VARCHAR(100) officeCode VARCHAR(10) reportsTO INT(11) job Title VARCHAR(50) offices officeCode VARCHAR(10) City VARCHAR(50) phone VARCHAR(50) addressLine1 VARCHAR(50) addressLine2 VARCHAR(50) state VARCHAR(SO) country VARCHAR(50) postalCode VARCHAR(15) territory VARCHAR(10) orderdetails orderNumber INT(11) product Code VARCHAR(15) quantityOrdered INT(11) price ach DOUBLE orderlineNumber SMALLINT (6) dates FullDate DATE Mont Name CHAR(3) Year Number INT(11) QuarterNumber INT(11) Notes VARCHAR(150) payments customerNumber INT (11) check Number VARCHAR(50) paymentDate DATETIME amount DOUBLE orders order Number INT (11) order Date DATETIME requiredDat DATETIME shippedDate DATETIME status VARCHAR(15) comments TEXT customerNumber INT (11) products product Code VARCHAR(15) productName VARCHAR(70) productLine VARCHAR(SO) productScale VARCHAR(10) productVendor VARCHAR(50) product Description TEXT quantityInStock SMALLINT(6) buyPrice DOUBLE productlines productLine VARCHAR(50) -i textDescription VARCHAR(4000) htmlDescription MEDIUMTEXT image MEDIUMBL OB MSRP DOUBLE