Make your own free website on Tripod.com

 

COMPUTER SCIENCE                               SQL                                        DATABASE

_________________________________________________________________________

 

Tables used in the Database:

 

1.         Salespeople  Table:

 

SNUM

SNAME

CITY

COMM

1001

PEEL

LONDON

.12

1002

SERRES

SANJOSE

.13

1004

MOTIKA

LONDON

.11

1007

RIFKIN

BARCELONA

.26

1003

ALENXROAD

NEWYORK

.10

1005

FRAN

LONDON

.26

 

2.         Customers Table:

 

CNUM

CNAME

CITY

RATING

SNUM

2001

HOFFMAN

LONDON

100

1001

2002

GIOVANNI

ROME

200

1003

2003

LIU

SANJOSE

200

1002

2004

GRASS

BERLIN

300

1002

2006

CLEMENS

LONDON

100

1001

2008

CISNEROS

SAN JOSE

300

1007

2007

PEREIRA

ROME

100

1004

 

 

3.         Orders Table:

 

ONUM

AMT

ODATE

CNJM

SNJM

3001

18.69

10-3-96

2008

1007

3003

767.19

10-3-96

2001

1001

3002

1900.10

10-3-96

2007

1004

3005

5160.45

10-3-96

2003

1002

3006

1098.16

10-3-96

2008

1007

3009

1713.23

10-4-96

2002

1003

3007

75.75

10-4-96

2002

1003

3008

4723.00

10-5-96

2006

1001

3010

1309.95

10-6-96

2004

1002

3011

9861.88

10-6-96

2006

1001

 

1.        
List all the columns of salespeople table.

2.         List all the customers with a rating of 100

3.         Find all the records in the customer table with null values in the city column.

4.         Find the larger order taken by each salesperson on each date.

5.         Arrange the orders table by descending customer number.

6.         Find which salespeople currently have orders in the orders tale.

7.         List names of all customers matched with salesperson serving them.

8.         Find the names and numbers of all salespeople who had more than one customer.

9.         Count the orders of each of the salespeople and output the result in descending order.

10.       Match salespeople to customers according to which city they lived in.

11.       Find the largest order taken by each salesperson

12.       Find the customers in SanJose who has rating above 200.

13.       List the names and commission of all salespeople in London.

14.       List all orders of salesperson Motika from the orders table.

15.       Find all customers with orders in 10th March.

16.       Give the sum of the amounts that from the orders table grouped by date, eliminating all those dates where the sum was not atleast 2000.00 above max amount.

17.       Select all orders that had amounts that were greater than atleast one of the orders from 10th June.

18.       Write query that uses the EXISTS operator to extract all salespeople who have customers rating of 300.

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

20.       Find all customers whose CNUM is 1000 above the SNUM of sarres.

21.       Give the salespeople commission as percentage instead of decimal numbers.

22.       Find the largest order taken by each salesperson on each date, eliminating those MAX orders which are less tha $ 3000.00.

23.       List the larger order for Match 10th for each sales person.

24.       Find all customers which located in cities where serres has customers.

25.       Select all customers with a rating above 200.00

26.       Count the num of salesperson currently listing orders in the orders table.

27.       Write a query that produces all customers serviced by the salespeople with a commission above 12% output the customers name & the salesperson’s rate of commission.

28.       Find salespeople with customers located in their city.

29.       Find salespeople who have multiple customers.

30.       Find all salespeople whose name start with ‘0’ & the forth character is ‘I’.

31.       Write a query that uses a sub query to obtain all orders from customer named Cisneros.  Assume you don’t know his customer name.

32.       Find the largest order fro serres and rifkin.

33.       Extract the salespeople table in the following order SNUM, SNAME and COMMISSION, CITY.

34.       Select all customers whose name fall between ‘A’ & ‘G’ alphabetical range.

35.       Select all the possible combinations of customers that you can have.

36.       Select all orders that are greater than the average order for 10th April.

37.       Write select command using a correlated subquery that selects the names and numbers of all customers with rating equal to the maximum for their city.

38.       Write a query that totals the orders for each day and places the result in descending order.

39.       Write the select command that produces the ratting followed by name of each customers in San Jose.

40.       Find all orders with amounts smaller than any amount for a customers in San Jose.

41.       Find all orders with above average amounts for their customers.

42.       Write a query that selects the highest rating in each day.

43.       Write a query that calculates the amount of the salesperson’s commission on each order by customer with rating above 1000.00

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

45.       Write a query that produces all pairs of salespeople with themselves as well as duplicate rows with order reversed.

46.       Find all salespeople that are located in either Barcelona or London.

47.       Find all salespeople with only one customer.

48.       Write a query that joins the customer table to itself to find all pairs of customers served by a single person.

49.       Write a query that will give you all orders from more than $ 1000.00.

50.       Write a query that lists each order number followed by name of the customer who made that order.

51.       Write two queries that select all salespeople (by name and number) who have customers in their cities who they do not service, one using a join and one a correlated subquery, which solution is more elegent.

52.       Write a query that selects all customers who ratings are equal to or greater than ANY in the SQL sense of serres.

53.       Write two queries that will produce all orders taken on 03-10-96 and 04-10-96

54.       Write a query that produce a pair of orders by a given customer name that customer and eliminate duplicates.

55.       Find only those customers whose ratings are higher than every customer is rome is.

56.       Write a query on customers table whose output will exceed all customers with a rating <=100.00 unless they are located in Rome.

57.       Find all rows from customers table for which the salesperson number is 1001.

58.       Find the total amount in orders for each salesperson for which this total is greater than the amount of the largest order in the table.

59.       Write a query that selects all orders save those with zeroes of NULLs in the amount fields.

60.       Produce all combinations of salespeople and customer names such that the former precedes latter alphabetically and the letter has a rating of or less than rating.

61.       List all salespeople’s names and the commission they have earned.

62.       Write a query that produces the names and cities of all customers with the same rating as Hoffman.

63.       Find all salespeople for whom there are customers that follow that in alphabetical order.

64.       Write a query that produces the names and ratings of all the customers of all who have average orders.

65.       Find the sum of all purchases from the orders table.

66.       Write a select command that produces the order number, amount and date for all rows in order table.

67.       Count the number of non NULL rating fields in the customers table (including repeats).

68.       Write a query that gives the names of the salesperson and the customer for each code after the order number.

69.       List the commissions of all the salespeople servicing customers in London.

70.       Write a query using ANY or ALL that will find all salespeople who have no customers their city.

71.       Write a query using that EXISTS operator that selects all salespeople with customers located in their cities who are not assigned to them.

72.       Write a query that selects all customers serviced by PEEL or MOTIKA.

73.       Count the number of salespeople registering orders for each day.

74.       Find all orders attributed to salespeople in London.

75.       Find all salespeople who have customers with more than one current order.

76.       Find all orders by customers not located in the same cities as their salespeople.

77.       Write a query that extracts from the customers table every customers assigned to a salesperson that currently has atleast one other customer with orders in the order table.

78.       Write a query that select all customers whose names begin with ‘C’.

79.       Write a query on the customers table that find the highest rating in  each city.  Put the output in this form: for city, the highest rating is * parting.

80.       Write a query that will produce the SNUM values of all salespeople with orders currently in the order table (without any repeats).

81.       Write a query that lists customers in descending order of rating field followed by the customers name and members.

82.       Find the average commission  for salespeople in London.

83.       Find all order credited to the same salesperson that services Hoffamm (Snum = 2001).

84.       Find all salespeople whose commission is in between 0.10 and 0.12 (both inclusive).

85.       Write a query that will give you the names and cities of all salespeople in London with a commission above 0.10.

86.       What will be the output from the following query.

SELECT * FROM ORDERS

Where (amt < 1000 or Or not (odate = ‘3-oct-96 and ocum > 2003);

87.       Write a query that selects each customers smallest order.

88.       Write a query that selects the first customer in alphabetical order whose name begins with S.

89.       Write a query that counts the number of different non NULL values in the customer table.

90.       Find the average amount from the orders table.

91.       What would be the output from the following query?

92.       Find all the customers who are not located in San Jose and whose rating is above 200.

93.       Give a similar way to write this query.

94.       Evaluate the following query:

SELECT * FROM orders

Where not ((ODATE = 10/3/96 AND SNUM > 102) or AMT > 2000.00);

95.       Which salesperson attends the customers not in the city they have been assigned to?

96.       Which salespeople have been assigned to the same city but get the different commission percentage.

97.       Which salesperson has earned the most by the way of commission.

98.       Does the customer who has placed the maximum number of orders have the maximum rating?

99.       Has the customer who has spend the larger amount of money been given the highest rating?

100.    List all the customers in descending order of customer rating.

101.    On which days has Hoffman placed orders?

102.    Which salespeople have no orders between 10/3/96 and 10/5/96?

103.    How many salespersons have succeeded in getting orders?

104.    How many customers have placed orders?

105.    On which date has each salesperson booked an order of maximum value.