| Snum | Sname | City | Comm |
| 1001 | Peel | London | 0.12 |
| 1002 | Serres | San Jose | 0.13 |
| 1004 | Motika | London | 0.11 |
| 1007 | Rifkin | Barcelona | 0.15 |
| 1003 | Axelrod | Newyork | 1.10 |
Customers
| Cnum | Cname | City | Rating | Snum |
| 2001 | Hoffman | London | 100 | 1001 |
| 2002 | Gio-Vanne | Rome | 200 | 1003 |
| 2003 | Liu | San Jose | 300 | 1002 |
| 2004 | Grass | Berlin | 100 | 1002 |
| 2006 | Clemens | London | 300 | 1007 |
| 2007 | Parera | Rome | 100 | 1004 |
Orders
| Onum | Amt | O-Dt | Cnum | Snum |
| 3001 | 18.69 | 10_3_94 | 2008 | 1007 |
| 3003 | 767.19 | 10_3_94 | 2008 | 1001 |
| 3002 | 1900.1 | 10_3_94 | 2007 | 1004 |
| 3004 | 5160.45 | 10_3_94 | 2003 | 1002 |
| 3006 | 1098.16 | 10_3_94 | 2008 | 1007 |
| 3009 | 1713.23 | 10_4_94 | 2002 | 1003 |
| 3007 | 75.75 | 10_4_94 | 2004 | 1002 |
| 3008 | 4723.00 | 10_5_94 | 2006 | 1001 |
| 3010 | 1309.25 | 10_6_94 | 2004 | 1002 |
| 3011 | 9891.88 | 10_6_94 | 2006 | 1001 |
Queries
1. Display all cols of all Sales People
2. Display all snum without duplicate from all orders.
3. Display names and comm of all sales people from London.
4. Display all customers with the rating of 100
5. Produce Order No, amount, dt for all rows in the order table
6. All orders for more than Rs. 1000
7. Display name and city of all sales people in London with comm. above 0.1
8. All customers excluding those with rating less than or equal to 100 unless they are located in Rome.
9. Display sales people either in Barcelona or London
10. All Salespeople with comm. between 0.10 to 0.12
11. All customers with null values in city column
12. All orders taken on Mar 10 and Aril 10
13. All customers services by Peel or Motika
14. All customers whose names begin with ‘C’ from customers
15. All orders excluding those with zero or null values in amt field
16. Count the number of sales people currently listing orders in order table
17. Largest orders taken by each sales person date wise.
18. Largest order taken by each sales person with order value more than 3000 date wise.
19. Which day had the highest total amount ordered?
20. Count all orders for Oct 3
21. Count the number of different non-null city values in customer table.
22. Select each customer's Smallest Order WITH CUSTOMERS NAME.
23. First customers in Alphabetical order whose names begin with ’G’.
24. Get the output like:
“On Dd Mm Yy there are _____ orders.”
25. Assume that each sales person has 12% comm. Produce order no, salesperson no and amount of sales person comm. for that order.
26. Find highest rating in each city. Display the output in the following form:
“For the city <city name> the rating is <rating>.”
27. All combination of sales people and customers for each order after the order number.
28. Names of all customers matched with the sales people serving them.
29. List each order no followed by the name of the customer who made the order.
30. Names of sales person and customers for each order after the order no.
31. Produce all customers serviced by sales person with a comm. above 12%
32. Calculate the amt of the sales person’s comm. on each order with a rating above 100.
33. Find all pairs of customers having the same rating with each pair coming once only.
34. Policy is to assign three sales people to customer, one at each of the 3 ratings. Display all such combinations.
35. Display all customers located in cities where sales man serves has customer.
36. Find all pairs of customers served by a single salesperson.
37. Produce all pairs of salesperson who live in the same city. Exclude combination of salesperson with themselves as well as duplicate with order reverse.
38. Produce names and cities of customers with the same rating as Hoffman.
39. Extract all orders of Motika
40. Produce all orders credited to same salesperson that serviced Hoffman.
41. All orders those are greater than the average for 4 Oct.
42. Find avg comm. of salesperson of London
43. Find all orders attributed to sales person in London.
44. Extract comm. of all sales people serving customers in London.
45. Find all customers whose snum is 1000 above the snum of Seres.
46. Count the customer with rating above San Jose average.
47. Obtain all orders for the customer name Liu.
48. Produce the name and rating of all customers who have above average orders.
49. Find total amt in orders for each sales person for whom this total is greater than the amt of the largest order in the table.
50. Find all customers with orders on 3 Oct.
51. Find names and no of all salesperson that have more than one customer.
52. Check if the correct salesman was credited with each sale.
53. Find all orders with the above average amt for their customers.
54. Display all the salespersons name with smallest length name first and so on…
55. Display all the records with city field containing nothing.
56. Display all the record with city field indicating ‘****’ where city is null.
57. Display all the records with comm indicating 0 where comm is not specified.
No comments:
Post a Comment