BCO2149- Database Systems

Questions

Students are to develop SQL queries to answer the following questions:

1. What are the names (Last and First Name) of all the sales representatives that work within the ‘Electrical’ department?

2. Display all the details of customers that have a balance between 600 and 2000 dollars inclusive. 

3. Which products have the letter 'o' in their description field? List the product number and description. Sort the output by description in descending order.

4. Display the names (Last Name and First Name) of all customers that have placed an order. Do not display any repeats.

5. Display all the Orders (Order Number, Order Date, and Order Status) that have an Order Status of ‘C’.

6. Which product(s) have not been placed on any order? Display the Product Number only.

7. How many orders has customer 1040 placed?

8. For each order display the full name of the customer and sales representative, the order number, order date and the product number and description of each product ordered.

9. Display the customer numbers of those customers who do not have a current order in the order table.

10. What is the total amount (QtyPurchased*PurchasePrice) of all products on each order? Display the order number and the total amount. Rename the calculated column to "Total Amount".

11. What is the description of the product that has the highest "QtyPurchased" on any specific order?

12. How many sales representatives are there in each department? Display the full department name and the number of sales representatives. Rename the calculated field as "Number of Employees".

13. Which customer (First and Last Name) has the highest balance?

14. Which customers have placed more than three orders? (Display the customer number and the number of orders).

15. Display the number of orders that have an order status of 'C' and an order status of 'N'. Display the status and count only. Order the output by status in ascending order.

16. Display the names of the sales representatives that have taken three (3) or more orders.(Display the sales representatives Surname and First Name and the count.

17. List all the orders that were not taken by sales representative 'Karen Salt'. Display the Order Number and Order Date for each order.

18. What product has the second highest unit price? Display the Product Number and the Description.

19. What is the name of the customer who placed the order 12640?

20. How many times has ProductNo ‘DP03’ been ordered?

21. Are there any products that have not been ordered? List the product number and product name.

22. What are the names (First and last) of the customers that have ordered a product described as a 'Walkman'?

23. Display all details of products that have a quantity on hand of greater than 15 and a unit price of greater than 120 dollars.

24. What is the name (last name and First Name) of the sales representative that is assigned to order 12800?

25. How many orders were taken by sales person ‘S5’ in the year 2002?