**Database management question*

Do the following SQL questions. The resulting columns must all have descriptive names. You must show the result of the query. For example, if the query is:

Show the office id, the city, and the region

Your query should be:

select office, city, region

from offices;

and then you need to show the following on the screen: (snapshot of your result)

1. Write a select statement to list all the columns from the Offices table. 'Select *' is not allowed. Return the list in ascending order of Offices column.

2. List the Product id (mfr_id and product_id), its description, and Dollar_Value (quantity on hand *price) for manufacturer REI. Return the list in descending order by Dollar_Valuev.

3. List the Order Number, Order Date, Customer Number and Sales Rep Number for orders for part 'XK47' or '773C'. (Use a compound search condition - OR.)

4. List the Name and Age for all Sales Reps in the following offices: 12; 21; 13. (Use the set membership test - IN.)

5. List the names of all Sales Reps who have the letter 'a' (this is lower case a) as the second character of their name.

6. Return the Sales Rep ID and Name of any Sales Rep not assigned to an office yet

7. Show all the sales rep names with last name Smith.

8. List different titles in the sales reps table. Only list each title once and unknown titles should be ignored.

9. List the description of the products which are at least 6 character and less than or equal to 10 character long. No duplicate row is allowed. You can use the build in function length() to do this. For example, length(name) return the number of characters for attribute called name.

10. List the order numbers with the name of the rep who placed the order and the name of the customer (Company Name) who made the order and the name of the rep for that customer as long as the rep and the cust_rep are not the same.

11. List the Mfr Id, the Product Id, Company and PRICE of all products brought by customers where customer number is one of (2111,2112,2105,2119) and where the amount of the order < $3000. Order the results by ascending Company.

12. List the id and the name of the Salesreps who have taken some order and they are at least 45 years old.

13. List all customers (Customer number, Company, and Credit Limit) and orders (Order Number, Amount) where the order is within $5000.00 of the Credit Limit (less than $5000) [Hint: Amount must be greater than zero and Credit_Limit Amount < 5000). Sort the result by Customer number in descending order.

14. List all salesreps names and their managers names where the salesrep is at least as old as the manager.

15. List all salesreps (Name) and the City they work in where the sales of the salesrep < Quota and the sales for the office is < Target.

16. List the name, id, and hire date of the salesreps where both of the following conditions hold:

- The salesrep sales should be greater than the quota

- The salesrep has taken an order from Customer number 2117, 2111, or 2101.

- Sort the result by the salesreps id.

17 . List all orders (Order Number) over $25500 showing the company placing the order, the Customer Rep assign to the company, the Office id and the city where the Customer Rep works in, such that the Customer Reps manager is not the person who took the order.

18. List order_num and name of the rep who took the order and the name of his/her manager and long as the managers office is in New York

19. List all products (Mfr_id, ProductId, and Description), customers (CustNum, Company) who have bought that product, and orders (Order Number, and Order Date) where the order < $700. Sort the rows by the OrderDate.

20. List the name of the salesreps and the name of their managers only if the manager has taken care of any order.