-
Notifications
You must be signed in to change notification settings - Fork 0
/
MySql - Queries.txt
410 lines (282 loc) · 23.4 KB
/
MySql - Queries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
1. List all the columns of the Salespeople table.
select * from salespeople;
2. List all customers with a rating of 100.
select rating from customer where rating=100;
3. Find all records in the Customer table with NULL values in the city column.
select * from customer where city='NULL';
4. Find the largest order taken by each salesperson on each date.
select max(amt) from orders group by odate;
5. Arrange the Orders table by descending customer number.
select min(cnum) from orders group by cnum;
6. Find which salespeople currently have orders in the Orders table.
select onum from orders;
7. List names of all customers matched with the salespeople serving them.
select cname from customer ;
select cname from customer where snum='NULL';
8. Find the names and numbers of all salespeople who had more than one customer.
select sname,snum,max(cnum) from salepoeple;
9. Count the orders of each of the salespeople and output the results in descending order.
select count(onum),min(onum) from orders group by odate;
10. List the Customer table if and only if one or more of the customers in the Customer table are located in San Jose.
select city from customer where city='San Jose';
select cname,city from customer where city='San Jose';
11. Match salespeople to customers according to what city they lived in.
select Customer.cname,Customers.city,Salepeople.sname,Salepeople.city from Salepeople,Customer;
12. Find the largest order taken by each salesperson.
select max(amt),sname from salepeople,orders group by sname;
13. Find customers in San Jose who have a rating above 200.
select cname from customer where city='San Jose' and rating > 200;
14. List the names and commissions of all salespeople in London.
select sname,city,comm from salepeople group by where city='London';
15. List all the orders of salesperson Motika from the Orders table.
select sname,onum from salepeople,orders where sname='Motika';
16. Find all customers with orders on October 3.
select cnum,onum from orders where odate='1996-03-10';
17. Give the sums of the amounts from the Orders table, grouped by date, eliminating all those dates where the SUM was not at least 2000.00 above the MAX amount.
select sum(amt) from orders group by odate;
18. Select all orders that had amounts that were greater than at least one of the orders from October 6.
Select onum, amt from orders where odate != '06-10-1994' and amt > ( select min(amt) from orders where odate = '06-10-1994');
19. Write a query that uses the EXISTS operator to extract all salespeople who have customers with a rating of 300.
Select * from salespeople where exists (select rating from customer where customers.snum=salespeople.snum and rating=300;
20. Find all pairs of customers having the same rating.
select c1.cname,c1.rating,c2.name from customer c1 join customer c2 on c1.rating=c2.rating;-self join
21. Find all customers whose CNUM is 1000 above the SNUM of Serres.
Select * from customers where cnum > ( select snum+1000 from salespeople where sname = 'Serres');
22. Give the salespeople’s commissions as percentages instead of decimal numbers.
select snum,sname(100*comm)+'%' as 'percentage' from salespeople;
23. Find the largest order taken by each salesperson on each date, eliminating those MAX orders which are less than $3000.00 in value.
select b.snum,max(a.amt) from orders a ,customers b where a.cnum=b.cnum group by b.snum having max(a.amt)>=3000;
24. List the largest orders for October 3, for each salesperson.
select * from customers a, orders b where a.cnum=b.cnum group by (snum) having b.odate='1996-03-10';
25. Find all customers located in cities where Serres (SNUM 1002) has customers.
select* from customers where snum=(select snum from salespeople where sname='serres';
26. Select all customers with a rating above 200.00.
select * from customers where rating>200;
27. Count the number of salespeople currently listing orders in the Orders table.
select a.cnum,b.cnum,count(c.snum) from orders a, customer b,salespeople c where a.cnum=b.cnum and b.snum=c.snum group by b.snum;
28. Write a query that produces all customers serviced by salespeople with a commission above 12%. Output the customer’s name and the salesperson’s rate of commission.
Select cname, sname, comm from customers , salespeople where comm > 0.12 and cust.snum = salespeople.snum; or select cname ,comm from customer a,salespeople b where a.snum=b.snum and b.comm>0.12;
29. Find salespeople who have multiple customers.
select a.cname,count(b.snum),b.sname from customer a join salespeople b where a.snum=b.snum group by b.snum having (b.snum)>1;
30. Find salespeople with customers located in their city.
Select sname from salespeople where snum in ( select snum from customer where salespeople.city = cust.city and salespeople.snum = cust.snum);
31. Find all salespeople whose name starts with ‘P’ and the fourth character is ‘l’.
select sname from salepeople where sname like 'P--l';
32. Write a query that uses a subquery to obtain all orders for the customer named Cisneros. Assume you do not know his customer number.
Select onum, odate from orders where cnum = ( select cnum from cust where cname = ‘Cisnerous’);
33. Find the largest orders for Serres and Rifkin.
select max(orders) from orders where sname=serres,rifkin;
34. Extract the Salespeople table in the following order : SNUM, SNAME, COMMISSION, CITY.
select snum,sname,comm,city from salepeople;
35. Select all customers whose names fall in between ‘A’ and ‘G’ alphabetical range.
select * from customer where cname between 'A%' and 'G%';
36. Select all the possible combinations of customers that you can assign.
select cname from customers;
37. Select all orders that are greater than the average for October 4.
select * from orders where amt > (select avg(amt) from orders where odate ='04-10-1996');
38. Write a select command using a corelated subquery that selects the names and numbers of all customers with ratings equal to the maximum for their city.
Select a.cnum, a.cname from cust a where a.rating = ( select max(rating) from cust b where a.city = b.city);
39. Write a query that totals the orders for each day and places the results in descending order.
select count(a.onum),sum (b.amt),a.odate from orders group by a.odate;
40. Write a select command that produces the rating followed by the name of each customer in San Jose.
select rating,cname from customers where city='San Jose';
41. Find all orders with amounts smaller than any amount for a customer in San Jose.
Select onum, amt from orders where amt < any ( select amt from orders, cust where city = 'San Jose' and orders.cnum = cust.cnum);
42. Find all orders with above average amounts for their customers.
select onum, cnum, amt from orders a where amt > ( select avg(amt) from orders b where a.cnum = b.cnum group by cnum);
43. Write a query that selects the highest rating in each city.
select max(rating) from customers;
44. Write a query that calculates the amount of the salesperson’s commission on each order by a customer with a rating above 100.00.
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;
45. Count the customers with ratings above San Jose’s average.
select cnum, rating from customer where rating > ( select avg(rating) from customer where city = 'San Jose');
46. Write a query that produces all pairs of salespeople with themselves as well as duplicate rows with the order reversed.
Select a.sname, b.sname from salespeople a, salespeople b where a.snum > b.snum and a.city = b.city;
47. Find all salespeople that are located in either Barcelona or London.
Select sname, city from salespeople where city in (‘Barcelona’,’London’);
48. Find all salespeople with only one customer.
Select snum from cust group by snum having count(*) = 1;
49. Write a query that joins the Customer table to itself to find all pairs of customers served by a single salesperson.
Select cname from customer where snum in (select snum from customer group by snum having count(snum) > 1);
50. Write a query that will give you all orders for more than $1000.00
Select * from orders where amt > 1000;
51. Write a query that lists each order number followed by the name of the customer who made that order.
select a.onum,b.cname from orders a, customer b where a.snum=b.snum;
52. Write 2 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 corelated subquery. Which solution is more elegant?
Select distinct cname from cust a, salespeople b where a.city = b.city and a.snum != b.snum;
Select cname from cust where cname in ( select cname from cust a, salespeople b where a.city = b.city and a.snum != b.snum );
53. Write a query that selects all customers whose ratings are equal to or greater than ANY (in the SQL sense) of Serres’?
Select cname, sname from cust, salespeople where rating >= any ( select rating from cust where snum = (select snum from salespeople where sname = 'Serres')) and sname != 'Serres' and salespeople.snum(+) = cust.snum;
54. Write 2 queries that will produce all orders taken on October 3 or October 4.
Select * from orders where odate in (‘03OCT96’,’04OCT96’);
55. Write a query that produces all pairs of orders by a given customer. Name that customer and eliminate duplicates.
Select c.cname, a.onum, b.onum from orders a, orders b, cust c where a.cnum = b.cnum and a.onum > b.onum and c.cnum = a.cnum;
56. Find only those customers whose ratings are higher than every customer in Rome.
Select a.cname from cust a where city = 'Rome' and rating > ( select max(rating) from cust where city != 'Rome');
57. Write a query on the Customers table whose output will exclude all customers with a rating <= 100.00, unless they are located in Rome.
Select * from customers where rating>100 or city='rome';
58. Find all rows from the Customers table for which the salesperson number is 1001.
select * from customers where snum=1001;
59. Find the total amount in Orders for each salesperson for whom this total is greater than the amount of the largest order in the table.
Select snum,sum(amt) from orders group by snum having sum(amt) > ( select max(amt) from orders);
60. Write a query that selects all orders save those with zeroes or NULLs in the amount field.
select * from orders where amt = NULL;
61. Produce all combinations of salespeople and customer names such that the former precedes the latter alphabetically, and the latter has a rating of less than 200.
Select sname from salespeople where sname < any ( select cname from cust where salespeople.snum = cust.snum);
Select sname from salespeople where exists ( select cname from cust where salespeople.snum = cust.snum and salespeople.rating < 200);
62. List all Salespeople’s names and the Commission they have earned.
select sname,comm from salepeople;
63. Write a query that produces the names and cities of all customers with the same rating as Hoffman. Write the query using Hoffman’s CNUM rather than his rating, so that it would still be usable if his rating changed.
SELECT cname,city FROM customers WHERE rating = (SELECT rating FROM customers WHERE cname='hoffman');
64. Find all salespeople for whom there are customers that follow them in alphabetical order.
Select sname from salespeople where sname < any ( select cname from cust where salespeople.snum = cust.snum);
65. Write a query that produces the names and ratings of all customers of all who have above average orders.
Select max(b.cname), max(b.rating), a.cnum from orders a, cust b where a.cnum = b.cnum group by a.cnum having count(a.cnum) > ( select avg(count(cnum)) from orders group by cnum);
66. Find the SUM of all purchases from the Orders table.
select sum(onum) from orders;
67. Write a SELECT command that produces the order number, amount and date for all rows in the order table.
Select ordno, amt, odate from orders;
68. Count the number of non NULL rating fields in the Customers table (including repeats).
Select count(rating) from cust;
69. Write a query that gives the names of both the salesperson and the 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;
70. List the commissions of all salespeople servicing customers in London.
Select commfrom salespeople where snum in (select snum from cust where city = ‘London’);
71. Write a query using ANY or ALL that will find all salespeople who have no customers located in their city.
Select sname from salespeople where snum in ( select snum from cust where salespeople.city != cust.city and salespeople.snum = cust.snum);
72. Write a query using the EXISTS operator that selects all salespeople with customers located in their cities who are not assigned to them.
Select snum, sname from salespeople where exists ( select cnum from cust where salespeople.city = cust.city and salespeople.snum != cust.snum);
73. Write a query that selects all customers serviced by Peel or Motika. (Hint : The SNUM field relates the two tables to one another.)
Select cname from cust, orders where orders.cnum = cust.cnum and orders.snum in ( select snum from salespeople where sname in 'Peel','Motika'));
74. Count the number of salespeople registering orders for each day. (If a salesperson has more than one order on a given day, he or she should be counted only once.)
SELECT count(odate)||' SALES PERSON REGISTERED ON' "TOTAL", odate FROM orders GROUP BY odate;
75. Find all orders attributed to salespeople in London.
Select snum, cnum from orders where cnum in (select cnum from cust where city = 'London');
76. Find all orders by customers not located in the same cities as their salespeople.
Select sname from salespeople where snum in ( select snum from cust where salespeople.city != cust.city and salespeople.snum = cust.snum);
Select sname from salespeople where snum = any ( select snum from cust where salespeople.city != cust.city and salespeople.snum = cust.snum);
77. Find all salespeople who have customers with more than one current order.
Select snum, count(snum) from orders group by snum having count(snum) > 1;
78. Write a query that extracts from the Customers table every customer assigned to a salesperson who currently has at least one other customer (besides the customer being selected) with orders in the Orders table.
Select a.cnum, max(c.cname) from orders a, cust c where a.cnum = c.cnum group by a.cnum,a.snum having count(*) < ( select count(*) from orders b where a.snum = b.snum) order by a.cnum;
79. Write a query that selects all customers whose names begin with ‘C’.
Select cname from customer where cname like ‘C%’;
80. Write a query on the Customers table that will find the 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 customer group by city;
81. Write a query that will produce the SNUM values of all salespeople with orders currently in the Orders table (without any repeats).
Select onum, sname, cname from orders, cust, salespeople where orders.cnum = cust.cnum and orders.snum = salespeople.snum;
82. Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customer’s names and numbers.
select rating,cname,cnum from customer group by min(rating);
83. Find the average commission for salespeople in London.
Select avg(comm) from salespeople where city = ‘London’;
84. Find all orders credited to the same salesperson who services Hoffman (CNUM 2001).
Select onum, sname, cname, amt from orders a, salespeople b, customer c where a.snum = b.snum and a.cnum = c.cnum and a.snum = ( select snum from orders where cnum = ( select cnum from customer where cname = 'Hoffman'));
85. Find all salespeople whose commission is in between 0.10 and 0.12 (both inclusive).
select comm from salepeople where comm='0.10%' and '0.12%';
Select sname, comm from salespeople where comm > 0.10 and comm < 0.12;
86. Write a query that will give you the names and cities of all salespeople in London with a commission above 0.10.
select sname,city='London',comm > 0.10 from salepeople;
87. What will be the output from the following query?
SELECT * FROM ORDERS
where (amt < 1000 OR NOT (odate = 10/03/1996 AND cnum > 2003));
ONUM AMT ODATE CNUM
3001 18.69 03- OCT -1990 2008
3003 767.19 03- OCT -1990 2001
3002 1900.10 03- OCT -1990 2007
3005 5160.45 03- OCT -1990 2003
3006 1098.16 03- OCT -1990 2008
3009 1713.23 04- OCT -1990 2002
3007 75.75 04- OCT -1990 2004
3008 4723.00 05- OCT -1990 2006
3010 1309.95 06- OCT -1990 2004
3011 9891.88 06- OCT -1990 2006
88. Write a query that selects each customer’s smallest order.
Select cnum, min(amt) from orders group by cnum;
89. Write a query that selects the first customer in alphabetical order whose name begins with G.
Select min(cname) from customer where cname like ‘G%’;
90. Write a query that counts the number of different non NULL city values in the Customers table.
Select count(distinct city) from customer;
91. Find the average amount from the Orders table.
select avg(amt) from orders;
92. What would be the output from the following query?
SELECT * FROM ORDERS
WHERE NOT (odate = 10/03/96 OR snum > 1006) AND amt >= 1500);
ONUM AMT ODATE CNUM SNUM
3001 18.69 03-OCT-90 2008 1007
3003 767.19 03-OCT-90 2001 1001
3006 1098.16 03-OCT-90 2008 1007
3009 1713.23 04-OCT-90 2002 1003
3007 75.75 04-OCT-90 2004 1002
3008 4723 05-OCT-90 2006 1001
3010 1309.95 06-OCT-90 2004 1002
3011 9891.88 06-OCT-90 2006 1001
93. Find all customers who are not located in San Jose and whose rating is above 200.
Select cname from cust where city != ‘San Jose’ or rating > 200;
94. Give a simpler way to write this query :
SELECT snum, sname city, comm FROM salespeople
WHERE (comm > + 0.12 OR comm < 0.14);
CITY SNAME SNUM COMM
london peel 1001 .12
san jose serres 1002 .13
newyork axelrod 1003 .1
london Motika 1004 .11
barcelona rifkin 1007 .15
95. Evaluate the following query : SELECT * FROM orders
WHERE NOT ((odate = 10/03/96 AND snum > 1002) OR amt > 2000.00);
Error
96. Which salespersons attend to customers not in the city they have been assigned to?
Select snum, sname from salespeople where exists ( select cnum from cust where salespeople.city = cust.city and salespeople.snum != cust.snum);
97. Which salespeople get commission greater than 0.11 are serving customers rated less than 250?
select comm, rating from salepeople,customer where comm>0.11 and rating < 250;
98. Which salespeople have been assigned to the same city but get different commission percentages?
Select sname, cname from salespeople, cust where salespeople.city = cust.city;
99. Which salesperson has earned the most by way of commission?
100. Does the customer who has placed the maximum number of orders have the maximum rating?
Select a.cnum, a.cname from cust a where a.rating = ( select max(rating) from cust b where a.city = b.city);
101. Has the customer who has spent the largest amount of money been given the highest rating?
select cname, max(rating), max(amt) from customer;
102. List all customers in descending order of customer rating.
select cname,rating from customer group by min(rating);
103. On which days has Hoffman placed orders?
104. Do all salespeople have different commissions?
Yes
105. Which salespeople have no orders between 10/03/1996 and 10/05/1996?
select sname,odate from salepeople,orders where odate='10-03-1996' and '10-05-1996';
106. How many salespersons have succeeded in getting orders?
Select onum, sname, cname from orders, cust, salespeople where orders.cnum = cust.cnum and orders.snum = salespeople.snum;
107. How many customers have placed orders?
select count(cnum) from orders;
108. On which date has each salesperson booked an order of maximum value?
Select odate, sum(amt) from orders a group by odate having sum(amt) > ( select max(amt) from orders b where a.odate = b.odate group by odate);
109. Who is the most successful salesperson?
select sname from salepeople where comm>0.15;
110. Who is the worst customer with respect to the company?
111. Are all customers not having placed orders greater than 200 totally been serviced by salespersons Peel or Serres?
SELECT * FROM customers WHERE rating >=ANY ( SELECT rating FROM customers WHERE snum IN ( SELECT snum FROM salespeople WHERE sname='serres' ) );
112. Which customers have the same rating?
select cnum,cname,rating from customer group by same rating;
113. Find all orders greater than the average for October 4th.
Select * from orders where amt > ( select avg(amt) from orders where odate = '03OCT94');
114. Which customers have above average orders?
select cname from customers where avg(onum);
115. List all customers with ratings above San Jose’s average.
Select cnum, rating from cust where rating > ( select avg(rating) from cust where city = 'San Jose');
116. Select the total amount in orders for each salesperson for whom the total is greater than the amount of the largest order in the table.
Select snum,sum(amt) from orders group by snum having sum(amt) > ( select max(amt) from orders);
117. Give names and numbers of all salespersons who have more than one customer.
Select sname, snum from salespeople where snum in ( select snum from cust group by snum having count(snum) > 1 );
118. Select all salespersons by name and number who have customers in their city whom they don’t service.
Select distinct cname from cust a, salespeople b where a.city = b.city and a.snum != b.snum;
Select cname from cust where cname in ( select cname from cust a, salespeople b where a.city = b.city and a.snum != b.snum );
119. Which customers’ rating should be lowered?
select cname from customer where rating<200;
120. Is there a case for assigning a salesperson to Berlin?
Yes
121. Is there any evidence linking the performance of a salesperson to the commission that he or she is being paid?
122. Does the total amount in orders by customer in Rome and London exceed the commission paid to salespersons in London and New York by more than 5 times?
Select snum,sum(amt) from orders group by snum having sum(amt) > ( select max(amt) from orders);
123. Which is the date, order number, amt and city for each salesperson (by name) for the maximum order he has obtained?
Select odate, onum, max(amt),sname from orders group by odate, snum order by odate,onum;
124. Which salesperson(s) should be fired?
125. What is the total income for the company?
select sum(comm) from salepeople;