Question

Using CIS495 sample database, write the SQL query: a. In the orderdetails table, list the unique order which has more than 3 productCode in each order. b. List the customer name and address who purchase more than 5 items (ProductCode) in the order.  

Answer 1

SQL QUERY:

a.

SELECT orderNumber, COUNT(DISTINCT productCode) AS "No of Products"

FROM orderDetails

GROUP BY orderNumber

HAVING "No of Products" > 3;

EXPLANATION:

Select keywords extracts columns 'orderNumber' and count of productCode.

FROM keyword specifies the tableName from which columns has to be retrived.

GROUP BY orderNumber: groups the total count of productCode for each orderNumber.

HAVING "No of Products"> 3: Due to this only those orderNumber will be displayed that has more than 3 productCode.

------------------------------------------

b.

SELECT c.customerName, CONCAT(c.addressLine1, ' ', c.addressLine2) AS "Address", COUNT(p.productCode) AS "Count Of Products"

FROM customers c, orderDetails o, products p

WHERE c.customerNumber= o.customerNumber

AND o.productCode= p.productCode

GROUP BY c.customerNumber

HAVING "Count of products" > 5;