Question

3. Consider the following relations: salesperson(emp#, name, age, salary); key is emp# order(order#, cust#, emp#, amount); key is order# customer(cust#, name, city, industry-type); key is cust# Show the SQL expressions for the following queries: a. Get all the information of customers who have placed at least one order b. Get all the information of customers who have placed more than one order. c. Get all the information of customers who have placed an order with the salesperson, Scott Tiger. d. Get all the information of customers who have placed more than one order with the salesperson, Scott Tiger. e. Show the names and employee numbers for all salespeople who have at least an order with ABERNATHY CONSTRUCTION, in ascending order of employee numbers. f. Compute the number of orders for each salesperson.

Answer 1

If you have any doubts, please give me comment...

-- a

SELECT *

FROM customer

WHERE cust# IN(

SELECT cust#

FROM order

);

-- b

SELECT *

FROM customer

WHERE cust# IN(

SELECT cust#

FROM order

GROUP BY cust#

HAVING COUNT(*)>1

);

-- c

SELECT *

FROM customer

WHERE cust# IN(

SELECT cust#

FROM order O, salesperson S

WHERE O.emp# = S.emp# AND name = 'Scott Tiger'

);

-- d

SELECT *

FROM customer

WHERE cust# IN(

SELECT cust#

FROM order O, salesperson S

WHERE O.emp# = S.emp# AND name = 'Scott Tiger'

GROUP BY cust#

HAVING COUNT(*)>1

);

-- e

SELECT name, S.emp#

FROM salesperson S, order O, customer C

WHERE S.emp# = O.emp# AND O.cust# = C.cust# AND C.industry_type = 'ABERNATHY CONSTRUCTION'

ORDER BY S.emp# ASC;

-- f

SELECT emp#, COUNT(*) AS noOfOrders

FROM order

GROUP BY emp#;