Question

1. For each vendor display the vendor name, sum of all orders as Total Value and the number of orders placed as # Orders. Hint you must calculate the total amount for each order based upon unit price and number of units ordered. Write SQL Query

2.    Display the name of vendors who have supplied at least one order to every event.   Hint this query will require nested correlated subqueries using the not exists operator. Write SQL query

Answer 1

[A]
SELECT T1.vendorname, T1.TOTAL AS "Total Value", T2.TOTAL AS "Orders"
FROM
(
SELECT vendorname, sum(unitprice * numberofunits) AS TOTAL
FROM <table name>
group by vendor name
) T1
JOIN
(
SELECT vendorname, sum(number of orders placed) AS TOTAL
FROM <table name>
group by vendor name
) T2
ON (T1.vendorname = T2.vendorname);  

[B]
SELECT vendorname
FROM <table name>
WHERE vendorID not exists ( SELECT vendorID
FROM <table name>
GROUP BY vendorID
Having count<orderID> < 1
)