Question

Consider the following relational schema:

Salerep(sales_rep_ID, name, address, commission, rate)

Customer(customer_number, name, address, balance, credit_limit, sales_rep_ID)

Part(part_number, part_description, on_hand, class, warehouse, price)

Orders(order_number, order_date, customer_number)

Orderlilne(order_number, part_number, number_order)

Write SQL statements for the following queries:

a) Produce a list showing part_number, part_description, on_hand, and price sorted by part_description.

b) List customer’s name followed by order_number, part_description, and number_order.

c) List names of customers who have ordered the most expensive item(Hint: Use a nested SQL query to determine thehighest price.)

d) List the names of the sale_reps who have sold the most number of part “123”.(Hint: Use a nested SQL query for the FROM clause)

Answer 1

a)SELECT part_number, part_description, on_hand,price FROM Part ORDER BY part_description ASC;

b)SELECT Customer.name , Orders.order_number, Part.part_description , Orderlilne.number_order FROM Customer, Part,Orders, Orderlilne WHERE Customer.customer_number=Orders.customer_number AND Orders.order_number=Orderlilne.order_number AND Orderlilne.part_number=Part.part_number;

c)SELECT Customer.name FROM Customer, Part, Orders ,Orderlilne WHERE Customer.customer_number=Orders.customer_number AND Orders.order_number=Orderlilne.order_number AND Orderlilne.part_number=Part.part_number AND Part.price=(SELECT MAX(price) FROM Part);

d)SELECT Salerep.name FROM Salerep , Customer , Orders , Orderlilne WHERE

Salerep.sales_rep_ID=Customer.sales_rep_ID

AND Customer.customer_number=Orders.customer_number

AND Orders.order_number=Orderlilne.order_number

AND Orderlilne.part_number=123

AND Orderlilne.number_order=(SELECT MAX(number_order) FROM Orderlilne);

Hope I typed the table and attribute names correctly and I hope the above codes makes sense and you like it. Don't forget to UPVOTE, as it means a lot..THANKS:)