Bright IT Career

1. Display snum, sname, city, and comm of all salespeople.

              Select snum, sname, city, comm from salespeople;

2. Display all snum without duplicates from all orders.

              Select distinct snum from orders;

3. Display names and commissions of all salespeople in London.

              Select sname, comm from salespeople where city = 'London';

4. All customers with a rating of 100.

              Select cname from cust where rating = 100;

5. Produce orderno, amount and date from all rows in the order table.

              Select ordno, amt, odate from orders;

6. All customers in San Jose, who have a rating more than 200.

              Select * from cust where city = 'San Jose' and rating > 200;

7. First customer in alphabetical order whose name begins with G.

              Select min(cname) from cust where cname like 'G%';

8. Get the output like "For dd/mm/yy there are_orders."

              Select 'For ' || to_char(odate, 'dd/mm/yy') || ' there are ' || count(*) || ' Orders' from orders group by odate;

9. Assume that each salesperson has a 12% commission. Produce order no., salesperson no., and amount of salesperson’s commission for that order.

              Select onum, snum, amt, amt * 0.12 from orders order by snum;

10. Find highest rating in each city. Put the output in this form: For the city (city), the highest rating is : (rating).

              Select 'For the city (' || city || '), the highest rating is : (' || max(rating) || ')' from cust group by city;

11. Display the totals of orders for each day and place the results in descending order.

              Select odate, count(onum) from orders group by odate order by count(onum) desc;

12. All combinations of salespeople and customers who shared a city.

              Select sname, cname from salespeople, cust where salespeople.city = cust.city;

13. Name of all customers matched with the salespeople serving them.

              Select cname, sname from cust, salespeople where cust.snum = salespeople.snum;

14. List each order number followed by the name of the customer who made the order.

              Select onum, cname from orders, cust where orders.cnum = cust.cnum;

15. Names of salesperson and customer for each order after the order number.

              Select onum, sname, cname from orders, cust, salespeople where orders.cnum = cust.cnum and orders.snum = salespeople.snum;

16. Produce all customers serviced by salespeople with a commission above 12%.

              Select cname, sname, comm from cust, salespeople where comm > 0.12 and cust.snum = salespeople.snum;

17. Calculate the amount of the salesperson’s commission on each order with a rating above 100.

              Select sname, amt * comm from orders, cust, salespeople where rating > 100 and salespeople.snum = cust.snum and salespeople.snum = orders.snum and cust.cnum = orders.cnum;

18. Find all pairs of customers having the same rating.

              Select a.cname, b.cname from cust a, cust b where a.rating = b.rating and a.cnum != b.cnum;

19. Policy is to assign three salespersons to each customer. Display all such combinations.

              Select a.cname, b.cname from cust a, cust b where a.rating = b.rating and a.cnum != b.cnum and a.cnum < b.cnum;

20. Display all customers located in cities where salesperson Serres has customers.

              Select cname, sname from salespeople, cust where sname in (select sname from salespeople where rownum <= 3) order by cname;

21. Find all pairs of customers served by a single salesperson.

              Select cname from cust where city = (select city from cust, salespeople where cust.snum = salespeople.snum and sname = 'Serres');
              Select cname from cust where city in (select city from cust, orders where cust.cnum = orders.cnum and orders.snum in (select snum from salespeople where sname = 'Serres'));

22. All orders that are greater than the average for Oct 4.

              Select * from orders where amt > (select avg(amt) from orders where odate = '03-OCT-94');

23. Find average commission of salespeople in London.

              Select avg(comm) from salespeople where city = 'London';

24. Find all orders attributed to salespeople servicing customers in London.

              Select snum, cnum from orders where cnum in (select cnum from cust where city = 'London');

25. Extract commissions of all salespeople servicing customers in London.

              Select comm from salespeople where snum in (select snum from cust where city = 'London');

26. Find all customers whose cnum is 1000 above the snum of Serres.

              Select cnum, cname from cust where cnum > (select snum + 1000 from salespeople where sname = 'Serres');

27. Count the customers with rating above San Jose’s average.

              Select cnum, rating from cust where rating > (select avg(rating) from cust where city = 'San Jose');

Basic Queries