SQL Challenges-1: Exercises, Practice, Solution
SQL Challenges-1 [77 Challenges with solution]
[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]
1. From the following tables, write a SQL query to find the information on each salesperson of ABC Company. Return name, city, country and state of each salesperson.
Input:
table: salespersons
salesperson_id|first_name|last_name|
--------------|----------|---------|
1|Green |Wright |
2|Jones |Collins |
3|Bryant |Davis |
table: address
address_id|salesperson_id|city |state |country|
----------|--------------|-----------|----------|-------|
1| 2|Los Angeles|California|USA |
2| 3|Denver |Colorado |USA |
3| 4|Atlanta |Georgia |USA |
Output:
first_name|last_name|city |state | ----------|---------|-----------|----------| Jones |Collins |Los Angeles|California| Bryant |Davis |Denver |Colorado | Green |Wright | | |Click me to see the sample solution
2. From the following table, write a SQL query to find the third highest sale. Return sale amount.
Input:
table: salemast
sale_id|employee_id|sale_date |sale_amt|
-------|-----------|----------|--------|
1| 1000|2012-03-08| 4500|
2| 1001|2012-03-09| 5500|
3| 1003|2012-04-10| 3500|
3| 1003|2012-04-10| 2500|
Output:
SecondHighestSale|
-----------------|
4500|
Click me to see the sample solution
3. From the following table, write a SQL query to find the Nth highest sale. Return sale amount.
Input:
table: salemast
sale_id|employee_id|sale_date |sale_amt|
-------|-----------|----------|--------|
1| 1000|2012-03-08| 4500|
2| 1001|2012-03-09| 5500|
3| 1003|2012-04-10| 3500|
Output:
getNthHighestSaleAmt(3)|
-----------------------|
3500|
Click me to see the sample solution
4. From the following table, write a SQL query to find the marks, which appear at least thrice one after another without interruption. Return the number.
Input:
table: logs
student_id|marks|
----------|-----|
101| 83|
102| 79|
103| 83|
104| 83|
105| 83|
106| 79|
107| 79|
108| 83|
Output:
ConsecutiveNums|
---------------|
83|
Click me to see the sample solution
5. From the following table, write a SQL query to find all the duplicate emails (no upper case letters) of the employees. Return email id.
Input:
table: employees
employee_id|employee_name|email_id |
-----------|-------------|-------------|
101|Liam Alton |[email protected]|
102|Josh Day |[email protected]|
103|Sean Mann |[email protected]|
104|Evan Blake |[email protected]|
105|Toby Scott |[email protected]|
Output:
email_id | -------------| [email protected]|Click me to see the sample solution
6. From the following tables, write a SQL query to find those customers who never ordered anything. Return customer name.
Input:
table: customers
customer_id|customer_name|
-----------|-------------|
101|Liam |
102|Josh |
103|Sean |
104|Evan |
105|Toby |
table: orders
order_id|customer_id|order_date|order_amount|
--------|-----------|----------|------------|
401| 103|2012-03-08| 4500|
402| 101|2012-09-15| 3650|
403| 102|2012-06-27| 4800|
Output:
Customers| ---------| Evan | Toby |Click me to see the sample solution
7. From the following table, write a SQL query to remove all the duplicate emails of employees keeping the unique email with the lowest employee id. Return employee id and unique emails.
Input:
table: employees
employee_id|employee_name|email_id |
-----------|-------------|-------------|
101|Liam Alton |[email protected]|
102|Josh Day |[email protected]|
103|Sean Mann |[email protected]|
104|Evan Blake |[email protected]|
105|Toby Scott |[email protected]|
Output:
employee_id|employee_name|email_id |
-----------|-------------|-------------|
101|Liam Alton |[email protected]|
102|Josh Day |[email protected]|
103|Sean Mann |[email protected]|
104|Evan Blake |[email protected]|
Click me to see the sample solution
8. From the following table, write a SQL query to find all dates' city ID with higher pollution compared to its previous dates (yesterday). Return city ID, date and pollution.
Input:
table: so2_pollution
city_id|date |so2_amt|
-------|----------|-------|
701|2015-10-15| 5|
702|2015-10-16| 7|
703|2015-10-17| 9|
704|2018-10-18| 15|
705|2015-10-19| 14|
Output:
City ID|
-------|
702|
703|
Click me to see the sample solution
9. A salesperson is a person whose job is to sell products or services.
From the following tables, write a SQL query to find the top 10 salesperson that have made highest sale. Return their names and total sale amount.
Input:
Table: sales
TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
501| 18| 5200.00|
502| 50| 5566.00|
503| 38| 8400.00|
...
599| 24| 16745.00|
600| 12| 14900.00|
Table: salesman
SALESMAN_ID|SALESMAN_NAME |
-----------|---------------------|
11|Jonathan Goodwin |
12|Adam Hughes |
13|Mark Davenport |
....
59|Cleveland Hart |
60|Marion Gregory |
Output:
salesman_name |total_sale| ---------------------|----------| Dan McKee | 70530.00| Cleveland Klein | 61020.00| Elliot Clapham | 60519.00| Evan Blake | 53108.00| Ollie Wheatley | 52640.00| Frederick Kelsey | 52270.00| Sean Mann | 52053.00| Callum Bing | 48645.00| Kian Wordsworth | 45250.00| Bradley Wright | 41961.00|Click me to see the sample solution
10. An active customer is simply someone who has bought company's product once before and has returned to make another purchase within 10 days.
From the following table, write a SQL query to identify the active customers. Show the list of customer IDs of active customers.
Input:
Table: orders
ORDER_ID|CUSTOMER_ID|ITEM_DESC|ORDER_DATE|
--------|-----------|---------|----------|
101| 2109|juice |2020-03-03|
102| 2139|chocolate|2019-03-18|
103| 2120|juice |2019-03-18|
...
199| 2130|juice |2019-03-16|
200| 2117|cake |2021-03-10|
Output:
customer_id|
-----------|
2103|
2110|
2111|
2112|
2129|
2130|
Click me to see the sample solution
11. From the following table, write a SQL query to convert negative numbers to positive and vice verse. Return the number.
Input:
Table name: tablefortest
srno|pos_neg_val| ----|-----------| 1| 56| 2| -74| 3| 15| 4| -51| 5| -9| 6| 32|
Output:
srno|pos_neg_val|converted_signed_value| ----|-----------|----------------------| 1| 56| -56| 2| -74| 74| 3| 15| -15| 4| -51| 51| 5| -9| 9| 6| 32| -32|Click me to see the sample solution
12. From the following table, write a SQL query to find the century of a given date. Return the century.
Input:
Table name: tablefortest
ID|date_of_birth| --|-------------| 1| 1907-08-15| 2| 1883-06-27| 3| 1900-01-01| 4| 1901-01-01| 5| 2005-09-01| 6| 1775-11-23| 7| 1800-01-01|
Output:
id|date_of_birth|Century| --|-------------|-------| 1| 1907-08-15| 20 | 2| 1883-06-27| 19 | 3| 1900-01-01| 19 | 4| 1901-01-01| 20 | 5| 2005-09-01| 21 | 6| 1775-11-23| 18 | 7| 1800-01-01| 18 |Click me to see the sample solution
13. From the following table, write a SQL query to find the even or odd values. Return "Even" for even number and "Odd" for odd number.
Input:
Table name: tablefortest
srno|col_val| ----|-------| 1| 56| 2| 74| 3| 15| 4| 51| 5| 9| 6| 32|
Output:
srno|col_val|Even_Odd| ----|-------|--------| 1| 56|Even | 2| 74|Even | 3| 15|Odd | 4| 51|Odd | 5| 9|Odd | 6| 32|Even |Click me to see the sample solution
14. From the following table, write a SQL query to find the unique marks. Return the unique marks.
Input:
Table name: student_test
student_id|marks_achieved|
----------|--------------|
1| 56|
2| 74|
3| 15|
4| 74|
5| 89|
6| 56|
7| 93|
Output:
Unique Marks|
------------|
56|
74|
15|
89|
93|
Click me to see the sample solution
15. From the following table, write a SQL query to find those students who have referred by the teacher whose id not equal to 602. Return the student names.
Input:
Table Name: students
student_id|student_name|teacher_id|
----------|------------|----------|
1001|Alex | 601|
1002|Jhon | |
1003|Peter | |
1004|Minto | 604|
1005|Crage | |
1006|Chang | 601|
1007|Philip | 602|
Output:
student_name| ------------| Alex | Jhon | Peter | Minto | Crage | Chang |Click me to see the sample solution
16. From the following table, write a SQL query to find the order_id(s) that was executed by the maximum number of salespersons.
If there are, more than one order_id(s) executed by the maximum number of salespersons find all the order_id(s). Return order_id.
Input:
Table Name: salemast
salesperson_id|order_id|
--------------|--------|
5001| 1001|
5002| 1002|
5003| 1002|
5004| 1002|
5005| 1003|
5006| 1004|
5007| 1004|
5008| 1004|
Output:
order_id|
--------|
1002|
1004|
Click me to see the sample solution
17. A city is big if it has an area bigger than 50K square km or a population of more than 15 million.
From the following table, write a SQL query to find big cities name, population and area.
Input:
Table : cities_test
city_name |country |city_population|city_area| -------------|-------------|---------------|---------| Tokyo |Japan | 13515271| 2191| Delhi |India | 16753235| 1484| Shanghai |China | 24870895| 6341| Sao Paulo |Brazil | 12252023| 1521| Mexico City |Mexico | 9209944| 1485| Cairo |Egypt | 9500000| 3085| Mumbai |India | 12478447| 603| Beijing |China | 21893095| 16411| Osaka |Japan | 2725006| 225| New York |United States| 8398748| 786| Buenos Aires |Argentina | 3054300| 203| Chongqing |China | 32054159| 82403| Istanbul |Turkey | 15519267| 5196| Kolkata |India | 4496694| 205| Manila |Philippines | 1780148| 43|
Output:
city_name |country |city_population|city_area| ------------|--------|---------------|---------| Delhi |India | 16753235| 1484| Shanghai |China | 24870895| 6341| Beijing |China | 21893095| 16411| Chongqing |China | 32054159| 82403| Istanbul |Turkey | 15519267| 5196|Click me to see the sample solution
18. From the following table, write a SQL query to find those items, which have ordered 5 or more times. Return item name and number of orders.
Input:
Table: orders
ORDER_ID|CUSTOMER_ID|ITEM_DESC|
--------|-----------|---------|
101| 2109|juice |
102| 2139|chocolate|
103| 2120|juice |
104| 2108|cookies |
105| 2130|juice |
106| 2103|cake |
107| 2122|cookies |
108| 2125|cake |
109| 2139|cake |
110| 2141|cookies |
111| 2116|cake |
112| 2128|cake |
113| 2146|chocolate|
114| 2119|cookies |
115| 2142|cake |
Output:
item_desc|Number of orders| ---------|----------------| cake | 6|Click me to see the sample solution
19. From the following tables, write a SQL query to find the overall rate of execution of orders, which is the number of orders execution divided by the number of orders quote. Return rate_of_execution rounded to 2 decimals places.
Input:
Table: orders_issued
distributor_id|company_id|quotation_date|
--------------|----------|--------------|
101| 202| 2019-11-15|
101| 203| 2019-11-15|
101| 204| 2019-11-15|
102| 202| 2019-11-16|
102| 201| 2019-11-15|
103| 203| 2019-11-17|
103| 202| 2019-11-17|
104| 203| 2019-11-18|
104| 204| 2019-11-18|
Table: orders_executed
orders_from|executed_from|executed_date|
-----------|-------------|-------------|
101| 202| 2019-11-17|
101| 203| 2019-11-17|
102| 202| 2019-11-17|
103| 203| 2019-11-18|
103| 202| 2019-11-19|
104| 203| 2019-11-20|
Output:
rate_of_execution|
-----------------|
0.67|
Click me to see the sample solution
20. From the following table write an SQL query to display the records with four or more rows with consecutive match_no's, and the crowd attended more than or equal to 50000 for each match. Return match_no, match_date and audience. Order the result by visit_date, descending.
Input:
table : match_crowd
match_no|match_date|audience|
--------|----------|--------|
1|2016-06-11| 75113|
2|2016-06-12| 62343|
3|2016-06-13| 43035|
4|2016-06-14| 55408|
5|2016-06-15| 38742|
6|2016-06-16| 63670|
7|2016-06-17| 73648|
8|2016-06-18| 52409|
9|2016-06-19| 67291|
10|2016-06-20| 49752|
11|2016-06-21| 28840|
12|2016-06-22| 32836|
13|2016-06-23| 44268|
Output:
match_no|match_date|audience|
--------|----------|--------|
6|2016-06-16| 63670|
7|2016-06-17| 73648|
8|2016-06-18| 52409|
9|2016-06-19| 67291|
Click me to see the sample solution
21. From the following table write a SQL query to know the availability of the doctor for consecutive 2 or more days. Return visiting days.
Input:
Table: dr_clinic
visiting_date|availability| -------------|------------| 2016-06-11| 1| 2016-06-12| 1| 2016-06-13| 0| 2016-06-14| 1| 2016-06-15| 0| 2016-06-16| 0| 2016-06-17| 1| 2016-06-18| 1| 2016-06-19| 1| 2016-06-20| 1| 2016-06-21| 1|
Output:
visiting_date| -------------| 2016-06-11| 2016-06-12| 2016-06-17| 2016-06-18| 2016-06-19| 2016-06-20| 2016-06-21|Click me to see the sample solution
22. From the following tables find those customers who did not make any order to the supplier 'DCX LTD'. Return customers name.
Input:
Table: customers
customer_id|customer_name|customer_city|avg_profit|
-----------|-------------|-------------|----------|
101|Liam |New York | 25000|
102|Josh |Atlanta | 22000|
103|Sean |New York | 27000|
104|Evan |Toronto | 15000|
105|Toby |Dallas | 20000|
Table : supplier
supplier_id|supplier_name|supplier_city|
-----------|-------------|-------------|
501|ABC INC |Dallas |
502|DCX LTD |Atlanta |
503|PUC ENT |New York |
504|JCR INC |Toronto |
Table: orders
order_id|customer_id|supplier_id|order_date|order_amount|
--------|-----------|-----------|----------|------------|
401| 103| 501|2012-03-08| 4500|
402| 101| 503|2012-09-15| 3650|
403| 102| 503|2012-06-27| 4800|
404| 104| 502|2012-06-17| 5600|
405| 104| 504|2012-06-22| 6000|
406| 105| 502|2012-06-25| 5600|
Output:
customer_name| -------------| Liam | Josh | Sean | ----------------------Click me to see the sample solution
23. Table students contain marks of mathematics for several students in a class. It may same marks for more than one student.
From the following table write a SQL table to find the highest unique marks a student achieved. Return the marks.
Table: students
student_id|student_name|marks_achieved|
----------|------------|--------------|
1|Alex | 87|
2|Jhon | 92|
3|Pain | 83|
4|Danny | 87|
5|Paul | 92|
6|Rex | 89|
7|Philip | 87|
8|Josh | 83|
9|Evan | 92|
10|Larry | 87|
Output:
marks| -----| 89|Click me to see the sample solution
24. In a hostel, each room contains two beds. After every 6 months a student have to change their bed with his or her room-mate.
From the following tables write a SQL query to find the new beds of the students in the hostel. Return original_bed_id, student_name, bed_id and student_new.
Table : bed_info
bed_id|student_name| ------|------------| 101|Alex | 102|Jhon | 103|Pain | 104|Danny | 105|Paul | 106|Rex | 107|Philip | 108|Josh | 109|Evan | 110|Green |
Output:
original_bed_id|student_name|bed_id|student_new|
---------------|------------|------|-----------|
102|Jhon | 101|Jhon |
101|Alex | 102|Alex |
104|Danny | 103|Danny |
103|Pain | 104|Pain |
106|Rex | 105|Rex |
105|Paul | 106|Paul |
108|Josh | 107|Josh |
107|Philip | 108|Philip |
110|Green | 109|Green |
109|Evan | 110|Evan |
Click me to see the sample solution
25. From the following table, write a SQL query to find the first login date for each customer. Return customer id, login date.
Input:
Table: bank_trans
trans_id|customer_id|login_date|
--------|-----------|----------|
101| 3002|2019-09-01|
101| 3002|2019-08-01|
102| 3003|2018-09-13|
102| 3002|2018-07-24|
103| 3001|2019-09-25|
102| 3004|2017-09-05|
Output:
customer_id|first_login|
-----------|-----------|
3001| 2019-09-25|
3002| 2018-07-24|
3003| 2018-09-13|
3004| 2017-09-05|
Click me to see the sample solution
26. From the following table, write a SQL query to find those salespersons whose commission is less than ten thousand. Return salesperson name, commission.
Input:
Table: salemast
salesman_id|salesman_name|yearly_sale|
-----------|-------------|-----------|
101|Adam | 250000|
103|Mark | 100000|
104|Liam | 200000|
102|Evan | 150000|
105|Blake | 275000|
106|Noah | 50000|
Table : commision
salesman_id|commision_amt|
-----------|-------------|
101| 10000|
103| 4000|
104| 8000|
102| 6000|
105| 11000|
Output:
salesman_name|commision_amt| -------------|-------------| Mark | 4000| Liam | 8000| Evan | 6000|Click me to see the sample solution
27. From the following table write a SQL query to find those distributors who purchased all types of item from the company. Return distributors ids.
Input:
Table: items
item_code|item_name|
---------|---------|
10091|juice |
10092|chocolate|
10093|cookies |
10094|cake |
Table : orders
order_id|distributor_id|item_ordered|item_quantity|
--------|--------------|------------|-------------|
1| 501| 10091| 250|
2| 502| 10093| 100|
3| 503| 10091| 200|
4| 502| 10091| 150|
5| 502| 10092| 300|
6| 504| 10094| 200|
7| 503| 10093| 250|
8| 503| 10092| 250|
9| 501| 10094| 180|
10| 503| 10094| 350|
Output:
distributor_id|
--------------|
503|
Click me to see the sample solution
28. From the following tables write a SQL query to find those directors and actors who worked together at least three or more movies. Return the director and actor name.
Input:
Table: actor_test
act_id|act_name | ------|-----------------| 101|James Stewart | 102|Deborah Kerr | 103|Peter OToole | 104|Robert De Niro | 105|F. Murray Abraham| 106|Harrison Ford | 107|Bill Paxton | 108|Stephen Baldwin | 109|Jack Nicholson | 110|Mark Wahlberg |
Table : director_test
dir_id|dir_name | ------|-----------------| 201|Alfred Hitchcock | 202|Jack Clayton | 203|James Cameron | 204|Michael Cimino | 205|Milos Forman | 206|Ridley Scott | 207|Stanley Kubrick | 208|Bryan Singer | 209|Roman Polanski |
Table: movie_test
mov_id|movie_name | ------|-------------------| 901|Vertigo | 902|Aliens | 903|Lawrence of Arabia | 904|The Deer Hunter | 905|True Lies | 906|Blade Runner | 907|Eyes Wide Shut | 908|Titanic | 909|Chinatown | 910|Ghosts of the Abyss|
Table : mov_direction_test
dir_id|mov_id|act_id| ------|------|------| 201| 901| 101| 203| 902| 107| 204| 904| 104| 203| 905| 107| 206| 906| 106| 203| 908| 107| 209| 909| 109| 203| 910| 107|
Output:
dir_name |act_name | -------------|-----------| James Cameron|Bill Paxton|Click me to see the sample solution
29. From the following tables write a SQL query to find those students who achieved 100 percent in various subjects in every year. Return examination ID, subject name, examination year, number of students.
Input:
Table: exam_test
exam_id|subject_id|exam_year|no_of_student|
-------|----------|---------|-------------|
71| 201| 2017| 5146|
71| 201| 2018| 3545|
71| 202| 2018| 5945|
71| 202| 2019| 2500|
71| 203| 2017| 2500|
72| 201| 2018| 3500|
72| 202| 2017| 3651|
73| 201| 2018| 2647|
73| 201| 2019| 2647|
73| 202| 2018| 4501|
Table : subject_test
subject_id|subject_name|
----------|------------|
201|Mathematics |
202|Physics |
203|Chemistry |
Output:
exam_id|subject_name|exam_year|no_of_student|
-------|------------|---------|-------------|
71|Chemistry | 2017| 2500|
71|Mathematics | 2017| 5146|
71|Mathematics | 2018| 3545|
71|Physics | 2018| 5945|
71|Physics | 2019| 2500|
72|Mathematics | 2018| 3500|
72|Physics | 2017| 3651|
73|Mathematics | 2018| 2647|
73|Mathematics | 2019| 2647|
73|Physics | 2018| 4501|
Click me to see the sample solution
30. From the following tables write a SQL query to find those students who achieved 100 percent marks in every subject for all the year. Return subject ID, subject name, students for all year.
Input:
Table: exam_test
exam_id|subject_id|exam_year|no_of_student|
-------|----------|---------|-------------|
71| 201| 2017| 5146|
71| 201| 2018| 3545|
71| 202| 2018| 5945|
71| 202| 2019| 2500|
71| 203| 2017| 2500|
72| 201| 2018| 3500|
72| 202| 2017| 3651|
73| 201| 2018| 2647|
73| 201| 2019| 2647|
73| 202| 2018| 4501|
Table : subject_test
subject_id|subject_name|
----------|------------|
201|Mathematics |
202|Physics |
203|Chemistry |
Output:
subject_id|subject_name|Students for all year|
----------|------------|---------------------|
201|Mathematics | 17485|
202|Physics | 16597|
203|Chemistry | 2500|
Click me to see the sample solution
31. From the following tables write a SQL query that will generate a report which shows the total number of students achieved 100 percent for the first year of each examination of every subject.
Input:
Table: exam_test
exam_id|subject_id|exam_year|no_of_student|
-------|----------|---------|-------------|
71| 201| 2017| 5146|
71| 201| 2018| 3545|
71| 202| 2017| 2701|
71| 202| 2018| 5945|
71| 202| 2019| 2500|
71| 203| 2017| 2500|
72| 201| 2018| 3500|
72| 202| 2017| 3651|
73| 201| 2017| 1000|
73| 201| 2018| 2647|
73| 201| 2019| 2647|
73| 202| 2018| 4501|
Table : subject_test
subject_id|subject_name|
----------|------------|
201|Mathematics |
202|Physics |
203|Chemistry |
Output:
exam_id|subject_name|first_year|no_of_student|
-------|------------|----------|-------------|
71|Mathematics | 2017| 5146|
71|Physics | 2017| 2701|
71|Chemistry | 2017| 2500|
72|Physics | 2017| 3651|
73|Mathematics | 2017| 1000|
Click me to see the sample solution
32. From the following tables write a SQL query to display those managers who have average experience for each scheme.
Input:
Table: managing_body
manager_id|manager_name|running_years|
----------|------------|-------------|
51|James | 5|
52|Cork | 3|
53|Paul | 4|
54|Adam | 3|
55|Hense | 4|
56|Peter | 2|
Table : scheme
scheme_code|scheme_manager_id|
-----------|-----------------|
1001| 51|
1001| 53|
1001| 54|
1001| 56|
1002| 51|
1002| 55|
1003| 51|
1004| 52|
Output:
scheme_code|Average year of experience|
-----------|--------------------------|
1001| 3.50|
1002| 4.50|
1003| 5.00|
1004| 3.00|
Click me to see the sample solution
33. From the following tables write a SQL query to find those schemes which executed by minimum number of employees. Return scheme code.
Input:
Table: managing_body
manager_id|manager_name|running_years|
----------|------------|-------------|
51|James | 5|
52|Cork | 3|
53|Paul | 4|
54|Adam | 3|
55|Hense | 4|
56|Peter | 2|
Table : scheme
scheme_code|scheme_manager_id|
-----------|-----------------|
1001| 51|
1001| 53|
1001| 54|
1001| 56|
1002| 51|
1002| 55|
1003| 51|
1004| 52|
Output:
scheme_code|
-----------|
1003|
1004|
Click me to see the sample solution
34. From the following tables write a SQL query to find those experienced manager who execute the schemes. Return scheme code and scheme manager ID.
Input:
Table: managing_body
manager_id|manager_name|running_years|
----------|------------|-------------|
51|James | 5|
52|Cork | 3|
53|Paul | 4|
54|Adam | 3|
55|Hense | 4|
56|Peter | 2|
Table : scheme
scheme_code|scheme_manager_id|
-----------|-----------------|
1001| 51|
1001| 53|
1001| 54|
1001| 56|
1002| 51|
1002| 55|
1003| 51|
1004| 52|
Output:
scheme_code|scheme_manager_id|
-----------|-----------------|
1001| 51|
1002| 51|
1003| 51|
1004| 52|
Click me to see the sample solution
35. From the following tables write an SQL query to find the best seller by total sales price. Return distributor ID , If there is a tie, report them all.
Input:
Table: item
item_code|item_desc |cost|
---------|------------|----|
101|mother board|2700|
102|RAM | 800|
103|key board | 300|
104|mouse | 300|
Table : sales_info
distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
5001| 101| 1001| 2020-02-12| 3| 8100|
5001| 103| 1002| 2020-03-15| 15| 4500|
5002| 101| 1001| 2019-06-24| 2| 5400|
5001| 104| 1003| 2019-09-11| 8| 2400|
5003| 101| 1003| 2020-10-21| 5| 13500|
5003| 104| 1002| 2020-12-27| 10| 3000|
5002| 102| 1001| 2019-05-18| 12| 9600|
5002| 103| 1004| 2020-06-17| 8| 2400|
5003| 103| 1001| 2020-04-12| 3| 900|
Output:
distributor_id|
--------------|
5002|
5003|
Click me to see the sample solution
36. From the following table write a SQL query to find those retailers who have bought 'key board' but not 'mouse'. Return retailer ID.
Input:
Table: item
item_code|item_desc |cost|
---------|------------|----|
101|mother board|2700|
102|RAM | 800|
103|key board | 300|
104|mouse | 300|
Table : sales_info
distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
5001| 101| 1001| 2020-02-12| 3| 8100|
5001| 103| 1002| 2020-03-15| 15| 4500|
5002| 101| 1001| 2019-06-24| 2| 5400|
5001| 104| 1003| 2019-09-11| 8| 2400|
5003| 101| 1003| 2020-10-21| 5| 13500|
5003| 104| 1002| 2020-12-27| 10| 3000|
5002| 102| 1001| 2019-05-18| 12| 9600|
5002| 103| 1004| 2020-06-17| 8| 2400|
5003| 103| 1001| 2020-04-12| 3| 900|
Output:
retailer_id|
-----------|
1001|
1004|
Click me to see the sample solution
37. From the following table write a SQL query to display those items that were only sold in the 2nd quarter of a year, i.e. April 1st to June end for the year 2020. Return item code and item description.
Input:
Table: item
item_code|item_desc |cost|
---------|------------|----|
101|mother board|2700|
102|RAM | 800|
103|key board | 300|
104|mouse | 300|
Table : sales_info
distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
5001| 101| 1001| 2020-02-12| 3| 8100|
5001| 103| 1002| 2020-03-15| 15| 4500|
5002| 101| 1001| 2019-06-24| 2| 5400|
5001| 104| 1003| 2019-09-11| 8| 2400|
5003| 101| 1003| 2020-10-21| 5| 13500|
5003| 104| 1002| 2020-12-27| 10| 3000|
5002| 102| 1001| 2019-05-18| 12| 9600|
5002| 103| 1004| 2020-06-17| 8| 2400|
5003| 103| 1001| 2020-04-12| 3| 900|
Output:
item_code|item_desc |
---------|------------|
101|mother board|
102|RAM |
103|key board |
Click me to see the sample solution
38. From the following table write a SQL query to find the highest purchase with its corresponding item for each customer. In case of a same quantity purchase find the item code which is smallest.
The output must be sorted by increasing of customer_id. Return customer ID,lowest item code and purchase quantity.
Input:
Table: purchase
Field |Type |Null|Key|Default|Extra| -----------|-------|----|---|-------|-----| customer_id|int(11)|NO | | | | item_code |int(11)|NO | | | | purch_qty |int(11)|NO | | | |
Data:
customer_id|item_code|purch_qty|
-----------|---------|---------|
101| 504| 25|
101| 503| 50|
102| 502| 40|
102| 503| 25|
102| 501| 45|
103| 505| 30|
103| 503| 25|
104| 505| 40|
101| 502| 25|
102| 504| 40|
102| 505| 50|
103| 502| 25|
104| 504| 40|
103| 501| 35|
Output:
customer_id|lowest item code|purch_qty|
-----------|----------------|---------|
101| 503| 50|
102| 505| 50|
103| 501| 35|
104| 504| 40|
Click me to see the sample solution
39. From the following table write a SQL query to find all the writers who rated at least one of their own topic. Sorted the result in ascending order by writer id. Return writer ID.
Input:
Table: topics
Field |Type |Null|Key|Default|Extra| --------------|-------|----|---|-------|-----| topic_id |int(11)|YES | | | | writer_id |int(11)|YES | | | | rated_by |int(11)|YES | | | | date_of_rating|date |YES | | | |
Data:
topic_id|writer_id|rated_by|date_of_rating| -------|---------|--------|--------------| 10001| 504| 507| 2020-07-17| 10003| 502| 503| 2020-09-22| 10001| 503| 507| 2020-02-07| 10002| 501| 507| 2020-05-13| 10002| 502| 502| 2020-04-10| 10002| 504| 502| 2020-11-16| 10003| 501| 502| 2020-10-05| 10001| 507| 507| 2020-12-23| 10004| 503| 501| 2020-08-28| 10003| 505| 504| 2020-12-21|
Output:
Author rated on own topic|
-------------------------|
502|
507|
Click me to see the sample solution
40. From the following table write a SQL query to find all the writers who rated more than one topics on the same date, sorted in ascending order by their id. Return writr ID.
Input:
Table: topics
Field |Type |Null|Key|Default|Extra| --------------|-------|----|---|-------|-----| topic_id |int(11)|YES | | | | writer_id |int(11)|YES | | | | rated_by |int(11)|YES | | | | date_of_rating|date |YES | | | |
Data:
topic_id|writer_id|rated_by|date_of_rating| -------|---------|--------|--------------| 10001| 504| 507| 2020-07-17| 10003| 502| 503| 2020-09-22| 10001| 503| 507| 2020-02-07| 10002| 501| 507| 2020-05-13| 10002| 502| 502| 2020-04-10| 10002| 504| 502| 2020-11-16| 10003| 501| 502| 2020-10-05| 10001| 507| 507| 2020-12-23| 10004| 503| 501| 2020-08-28| 10003| 505| 504| 2020-12-21|
Output:
Topic rated by the writer|
-------------------------|
502|
507|
Click me to see the sample solution
41. From the following table write a SQL query to make a report such that there is a product id column and a sale quantity column for each quarter.
Return product ID and sale quantity of each quarter.
Input:
Table: sale
Field |Type |Null|Key|Default|Extra| ----------|-----------|----|---|-------|-----| product_id|int(11) |NO |PRI| | | sale_qty |int(11) |YES | | | | qtr_no |varchar(25)|NO |PRI| | |
Data:
product_id|sale_qty|qtr_no|
----------|--------|------|
1| 15000|qtr1 |
1| 10000|qtr2 |
2| 20000|qtr1 |
2| 12000|qtr2 |
3| 20000|qtr1 |
3| 15000|qtr2 |
3| 23000|qtr3 |
3| 22000|qtr4 |
4| 25000|qtr2 |
4| 18000|qtr4 |
Output:
product_id qtr1_sale qtr2_sale qtr3_sale qtr4_sale 1 15000 10000 NULL NULL 2 20000 12000 NULL NULL 3 20000 15000 23000 22000 4 NULL 25000 NULL 18000Click me to see the sample solution
42. From the following table write a SQL query to find for each month and company, the number of orders issued and their total quantity, the number of orders booked and their total order quantity. Return month, name of the company, number of orders issued, number of booked orders, total order quantity and total booked orders quantity.
Input:
Table: order_stat
Field |Type |Null|Key|Default|Extra| ---------|-----------|----|---|-------|-----| order_id |int(11) |NO |PRI| | | com_name |varchar(25)|YES | | | | ord_qty |int(11) |YES | | | | ord_stat |varchar(25)|YES | | | | stat_date|date |YES | | | |
Data:
order_id|com_name |ord_qty|ord_stat |stat_date |
--------|-----------|-------|---------|----------|
151|MMS INC | 500|Booked |2020-08-15|
152|BCT LTD | 300|Cancelled|2020-08-15|
153|MMS INC | 400|Cancelled|2020-08-26|
154|XYZ COR | 500|Booked |2020-08-15|
155|MMS INC | 500|Cancelled|2020-10-11|
156|BWD PRO LTD| 250|Cancelled|2020-11-15|
157|BCT LTD | 600|Booked |2020-10-07|
158|MMS INC | 300|Booked |2020-12-11|
159|XYZ COR | 300|Booked |2020-08-26|
160|BCT LTD | 400|Booked |2020-11-15|
Output:
month year com_name no_of_orders booked_orders total_order_qty no_of_booked_qty 2020-08 MMS INC 2 1 900 500 2020-08 BCT LTD 1 0 300 0 2020-08 XYZ COR 2 2 800 800 2020-10 MMS INC 1 0 500 0 2020-11 BWD PRO LTD 1 0 250 0 2020-10 BCT LTD 1 1 600 600 2020-12 MMS INC 1 1 300 300 2020-11 BCT LTD 1 1 400 400Click me to see the sample solution
43. From the following table write a SQL query to find for each month and company, the number of orders issued and their total quantity, the number of orders cancelled
and their total quantity. Return month, name of the company, number of orders booked, number of booked quantity, number of cancelled order, and number of cancelled quantity.
Input:
Table: order_stat
Field |Type |Null|Key|Default|Extra| ---------|-----------|----|---|-------|-----| order_id |int(11) |NO |PRI| | | com_name |varchar(25)|YES | | | | ord_qty |int(11) |YES | | | | ord_stat |varchar(25)|YES | | | | stat_date|date |YES | | | |
Data:
order_id|com_name |ord_qty|ord_stat |stat_date |
--------|-----------|-------|---------|----------|
151|MMS INC | 500|Booked |2020-08-15|
152|BCT LTD | 300|Cancelled|2020-08-15|
153|MMS INC | 400|Cancelled|2020-08-26|
154|XYZ COR | 500|Booked |2020-08-15|
155|MMS INC | 500|Cancelled|2020-10-11|
156|BWD PRO LTD| 250|Cancelled|2020-11-15|
157|BCT LTD | 600|Booked |2020-10-07|
158|MMS INC | 300|Booked |2020-12-11|
159|XYZ COR | 300|Booked |2020-08-26|
160|BCT LTD | 400|Booked |2020-11-15|
Table: order_return
Structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| order_id | int(11) | NO | MUL | ||
| return_date | date | YES |
Data:
| order_id | return_date |
|---|---|
| 153 | 2020-10-12 |
| 154 | 2020-11-07 |
| 156 | 2020-12-05 |
| 159 | 2020-09-17 |
Output:
month |com_name |booked_count|booked_qty|cancelled_count|cancelled_qty| -------|-----------|------------|----------|---------------|-------------| 2020-08|BCT LTD | 0| 0.0| 1| 300.0| 2020-08|MMS INC | 1| 500.0| 1| 400.0| 2020-08|XYZ COR | 2| 800.0| 0| 0.0| 2020-10|BCT LTD | 1| 600.0| 0| 0.0| 2020-10|MMS INC | 0| 0.0| 1| 500.0| 2020-11|BCT LTD | 1| 400.0| 0| 0.0| 2020-11|BWD PRO LTD| 0| 0.0| 1| 250.0| 2020-12|MMS INC | 1| 300.0| 0| 0.0|Click me to see the sample solution
44. From the following tables write a SQL query to find the average selling price for each item. Return item code and average_selling_price.
average_selling_price should be rounded to 2 decimal places.
Input:
Table: item_price
Field |Type |Null|Key|Default|Extra| ---------|-------|----|---|-------|-----| item_code|int(11)|YES | | | | date_from|date |YES | | | | date_to |date |YES | | | | item_cost|int(11)|YES | | | |
Data:
item_code|date_from |date_to |item_cost|
---------|----------|----------|---------|
101|2018-04-07|2018-06-28| 8|
102|2018-02-15|2018-04-17| 13|
103|2018-03-12|2018-04-30| 10|
101|2018-06-29|2018-10-31| 15|
103|2018-05-01|2019-08-24| 14|
102|2018-04-18|2018-07-10| 25|
104|2018-06-11|2018-10-10| 25|
101|2018-11-01|2019-01-15| 20|
Table: sale
Structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| sale_date | date | YES | |||
| item_code | int(11) | YES | |||
| sale_qty | int(11) | YES |
Data:
| sale_date | item_code | sale_qty |
|---|---|---|
| 2018-05-15 | 101 | 120 |
| 2018-04-27 | 103 | 80 |
| 2018-04-10 | 102 | 200 |
| 2018-07-12 | 101 | 100 |
| 2018-07-07 | 103 | 50 |
| 2018-09-17 | 104 | 100 |
| 2018-06-25 | 102 | 100 |
Output:
item_code|average_selling_price|
---------|---------------------|
101| 11.18|
102| 17.00|
103| 11.54|
104| 25.00|
Click me to see the sample solution
45. From the following table write a SQL query to find all employees that directly or indirectly report to the head of the company. Return employee_id, name, and manager_id.
Input:
Table: emp_test_table
Field |Type |Null|Key|Default|Extra| -----------|-----------|----|---|-------|-----| employee_id|int(11) |NO |PRI| | | first_name |varchar(25)|YES | | | | manager_id |int(11) |YES | | | |
Data:
employee_id|first_name |manager_id|
-----------|-----------|----------|
100|Steven | 100|
101|Neena | 100|
102|Lex | 100|
103|Alexander | 102|
104|Bruce | 103|
105|David | 103|
106|Valli | 103|
107|Diana | 103|
108|Nancy | 101|
109|Daniel | 108|
110|John | 108|
Output:
employee_id|Name |manager_id|
-----------|-----------|----------|
101|Neena | 100|
102|Lex | 100|
103|Alexander | 102|
104|Bruce | 103|
105|David | 103|
106|Valli | 103|
107|Diana | 103|
108|Nancy | 101|
109|Daniel | 108|
110|John | 108|
Click me to see the sample solution
46. From the following tables write a SQL query to find the number of times each patient call the specialist doctor since their treating period. Order the result table by patient_id and specialist_call.
Input:
Table: patient
Field |Type |Null|Key|Default|Extra| ------------|-----------|----|---|-------|-----| patient_id |int(11) |NO |PRI| | | patient_name|varchar(25)|YES | | | |
Data:
patient_id|patient_name |
----------|---------------|
1001|Gilbart Kane |
1002|Thomas Richi |
1003|Ricardo Grance |
1004|Vanio Tishuma |
1005|Charls Brown |
Table: speciality
Field |Type |Null|Key|Default|Extra| ----------|-----------|----|---|-------|-----| specialist|varchar(25)|YES | | | |
Data:
specialist | -----------| medicine | cardiology | neurology | hematology |
Table: treatment
Field |Type |Null|Key|Default|Extra| ---------------|-----------|----|---|-------|-----| patient_id |int(11) |YES |MUL| | | specialist_call|varchar(25)|YES | | | |
Data:
patient_id|specialist_call|
----------|---------------|
1001|medicine |
1003|medicine |
1002|cardiology |
1001|hematology |
1004|medicine |
1003|cardiology |
1005|neurology |
1002|neurology |
1001|cardiology |
1005|cardiology |
1003|cardiology |
1005|hematology |
1004|hematology |
1005|neurology |
1002|neurology |
1001|hematology |
Output:
patient_id|patient_name |specialist |Specialist Attended|
----------|---------------|-----------|-------------------|
1001|Gilbart Kane |cardiology | 1|
1001|Gilbart Kane |hematology | 2|
1001|Gilbart Kane |medicine | 1|
1001|Gilbart Kane |neurology | 0|
1002|Thomas Richi |cardiology | 1|
1002|Thomas Richi |hematology | 0|
1002|Thomas Richi |medicine | 0|
1002|Thomas Richi |neurology | 2|
1003|Ricardo Grance |cardiology | 2|
1003|Ricardo Grance |hematology | 0|
1003|Ricardo Grance |medicine | 1|
1003|Ricardo Grance |neurology | 0|
1004|Vanio Tishuma |cardiology | 0|
1004|Vanio Tishuma |hematology | 1|
1004|Vanio Tishuma |medicine | 1|
1004|Vanio Tishuma |neurology | 0|
1005|Charls Brown |cardiology | 1|
1005|Charls Brown |hematology | 1|
1005|Charls Brown |medicine | 0|
1005|Charls Brown |neurology | 2|
Click me to see the sample solution
47. From the following table write a SQL query to find the number of employees are working in the department of each employees. Return employee Id and number of employees are working in their department.
Input:
Table: emp_test_table
Field |Type |Null|Key|Default|Extra| -----------|-----------|----|---|-------|-----| employee_id|int(11) |NO |PRI| | | first_name |varchar(25)|YES | | | | manager_id |int(11) |YES | | | |
Data:
employee_id|first_name |department_id|
-----------|-----------|-------------|
100|Steven | 90|
101|Neena | 90|
102|Lex | 90|
103|Alexander | 60|
104|Bruce | 60|
105|David | 60|
106|Valli | 60|
107|Diana | 60|
108|Nancy | 100|
109|Daniel | 100|
110|John | 100|
Output:
employee_id|employees_in_department|
-----------|-----------------------|
100| 3|
101| 3|
102| 3|
103| 5|
104| 5|
105| 5|
106| 5|
107| 5|
108| 3|
109| 3|
110| 3|
Click me to see the sample solution
48. From the following table write a SQL query to find the total sale quantity of items of each unit type at each day. Return unit type, date and total sale quantity at each day. Order the result table by gender and day.
Input:
Table: sale
Field |Type |Null|Key|Default|Extra| ------------|-----------|----|---|-------|-----| product_name|varchar(25)|YES | | | | unit_type |varchar(5) |YES | | | | sale_date |date |YES | | | | sale_qty |int(11) |YES | | | |
Data:
product_name |unit_type|sale_date |sale_qty| ---------------|---------|----------|--------| Munchos |P |2018-05-15| 20| Boyer Chocolate|P |2018-04-27| 30| CocaCola |L |2018-04-10| 25| Fruit Cakes |P |2018-07-12| 30| CocaCola |L |2018-07-07| 50| Fanta |L |2018-01-27| 70| Chex Mix |P |2018-09-17| 40| Jaffa Cakes |P |2018-06-25| 40| Pom-Bear |P |2018-02-11| 30| Twix Chocolate |P |2018-12-24| 50| Limca |L |2018-03-15| 50| Mirinda |L |2018-02-05| 40|
Output:
unit_type|sale_date |running unit| ---------|----------|------------| L |2018-01-27| 70| L |2018-02-05| 110| L |2018-03-15| 160| L |2018-04-10| 185| L |2018-07-07| 235| P |2018-02-11| 30| P |2018-04-27| 60| P |2018-05-15| 80| P |2018-06-25| 120| P |2018-07-12| 150| P |2018-09-17| 190| P |2018-12-24| 240|Click me to see the sample solution
49. From the following tables write a SQL query to get the description of items with 50 or more quantities sold out within January and February of 2020.
Return item description and sale quantity.
Input:
Table: item
Field |Type |Null|Key|Default|Extra| ---------|------------|----|---|-------|-----| item_code|int(11) |NO |PRI| | | item_desc|varchar(255)|YES | | | | cost |int(11) |YES | | | |
Data:
item_code|item_desc |cost|
---------|------------|----|
101|mother board|2700|
102|RAM | 800|
103|key board | 300|
104|mouse | 300|
Table: sales_info
Field |Type |Null|Key|Default|Extra| --------------|-------|----|---|-------|-----| distributor_id|int(11)|YES | | | | item_code |int(11)|YES | | | | retailer_id |int(11)|YES | | | | date_of_sell |date |YES | | | | quantity |int(11)|YES | | | | total_cost |int(11)|YES | | | |
Data:
distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
5001| 101| 1001| 2020-01-12| 30| 8100|
5001| 103| 1002| 2020-01-15| 25| 4500|
5002| 101| 1001| 2019-01-30| 25| 5400|
5001| 104| 1003| 2019-02-17| 75| 2400|
5003| 101| 1003| 2020-03-07| 55| 13500|
5003| 104| 1002| 2020-05-27| 100| 3000|
5002| 102| 1001| 2020-05-18| 65| 9600|
5002| 103| 1004| 2020-01-30| 45| 2400|
5003| 103| 1001| 2020-03-12| 30| 900|
Output:
item_desc |sale_quantity| ------------|-------------| key board | 70| mother board| 55| mouse | 75|Click me to see the sample solution
50. From the following table write a SQL query to find the order id and the item name for all companies who are not registered with the distributor. Return the result table in any order.
Input:
Table: company_info
Field |Type |Null|Key|Default|Extra| ------------|-----------|----|---|-------|-----| company_id |int(11) |NO |PRI| | | company_name|varchar(25)|YES | | | |
Data:
company_id|company_name|
----------|------------|
5001|Intel |
5002|Kingston |
5003|Dell |
5004|Sony |
5005|Iball |
5006|Canon |
Table: orders
Field |Type |Null|Key|Default|Extra| ----------|-----------|----|---|-------|-----| order_id |int(11) |YES | | | | item_name |varchar(25)|YES | | | | company_id|int(11) |YES | | | |
Data:
order_id|item_name |company_id|
--------|-------------|----------|
101|mother board | 5001|
102|RAM | 5002|
103|printer | 5006|
104|keyboard | 5005|
105|mouse | 6051|
106|speaker | 6009|
107|web cam | 5005|
108|hard disk | 5002|
109|monitor | 5003|
110|scanner | 7023|
Output:
order_id|item_name|
--------|---------|
105|mouse |
106|speaker |
110|scanner |
Click me to see the sample solution
51. From the following table write a SQL query to find the ID for the salesman who makes largest number of transactions.
Input:
Table: sales
Field |Type |Null|Key|Default|Extra| --------------|------------|----|---|-------|-----| TRANSACTION_ID|int(5) |NO |PRI| | | SALESMAN_ID |int(4) |NO | | | | SALE_AMOUNT |decimal(8,2)|YES | | | |
Data:
TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
501| 18| 5200.00|
502| 50| 5566.00|
503| 38| 8400.00|
504| 43| 8400.00|
505| 11| 9000.00|
506| 42| 5900.00|
507| 13| 7000.00|
508| 33| 6000.00|
509| 41| 8200.00|
510| 11| 4500.00|
511| 51| 10000.00|
512| 29| 9500.00|
513| 59| 6500.00|
514| 38| 7800.00|
515| 58| 9800.00|
516| 60| 12000.00|
517| 58| 13900.00|
518| 23| 12200.00|
519| 34| 5480.00|
520| 35| 8129.00|
521| 49| 9323.00|
522| 46| 8200.00|
523| 47| 9990.00|
524| 42| 14000.00|
525| 44| 7890.00|
526| 47| 5990.00|
527| 21| 7770.00|
528| 57| 6645.00|
529| 56| 5125.00|
530| 25| 10990.00|
531| 21| 12600.00|
532| 41| 5514.00|
533| 17| 15600.00|
534| 44| 15000.00|
535| 12| 17550.00|
536| 55| 13000.00|
537| 58| 16800.00|
538| 25| 19900.00|
539| 57| 9990.00|
540| 28| 8900.00|
541| 44| 10200.00|
542| 57| 18000.00|
543| 34| 16200.00|
544| 36| 19998.00|
545| 30| 13500.00|
546| 37| 15520.00|
547| 36| 20000.00|
548| 20| 19800.00|
549| 22| 18530.00|
550| 19| 12523.00|
551| 46| 9885.00|
552| 22| 7100.00|
553| 54| 17500.00|
554| 19| 19600.00|
555| 24| 17500.00|
556| 38| 7926.00|
557| 49| 7548.00|
558| 15| 9778.00|
559| 56| 19330.00|
560| 24| 14400.00|
561| 18| 16700.00|
562| 54| 6420.00|
563| 31| 18720.00|
564| 21| 17220.00|
565| 48| 18880.00|
566| 33| 8882.00|
567| 44| 19550.00|
568| 22| 14440.00|
569| 53| 19500.00|
570| 30| 5300.00|
571| 30| 10823.00|
572| 35| 13300.00|
573| 35| 19100.00|
574| 18| 17525.00|
575| 60| 8995.00|
576| 53| 9990.00|
577| 21| 7660.00|
578| 27| 18990.00|
579| 11| 18200.00|
580| 30| 12338.00|
581| 37| 11000.00|
582| 27| 11980.00|
583| 18| 12628.00|
584| 52| 11265.00|
585| 53| 19990.00|
586| 27| 8125.00|
587| 25| 7128.00|
588| 57| 6760.00|
589| 19| 5985.00|
590| 52| 17641.00|
591| 53| 11225.00|
592| 22| 12200.00|
593| 59| 16520.00|
594| 35| 19990.00|
595| 42| 19741.00|
596| 19| 15000.00|
597| 57| 19625.00|
598| 53| 9825.00|
599| 24| 16745.00|
600| 12| 14900.00|
Output:
salesman_id|make_transaction|
-----------+----------------+
57| 5|
53| 5|
Click me to see the sample solution
52. From the following tables write a query in SQL to compare the purchasing status of the average purchase quantity of products of a category to the average pruchase quantity of the distributor. Return purchase month, category_id and purchase status.
Input:
Table: product
Field |Type|Null|Key|Default|Extra| -----------+----+----+---+-------+-----+ product_id |int |NO |PRI| | | category_id|int |NO | | | |
Data:
product_id|category_id|
----------+-----------+
8001| 150|
8002| 160|
8003| 160|
8004| 150|
8005| 160|
Table: purchase
Field |Type|Null|Key|Default|Extra| -------------+----+----+---+-------+-----+ purchase_no |int |NO |PRI| | | item_code |int |NO |MUL| | | purchase_qty |int |YES | | | | purchase_date|date|YES | | | |
Data:
purchase_no|item_code|purchase_qty|purchase_date|
-----------+---------+------------+-------------+
1001| 8001| 240| 2019-12-17|
1002| 8002| 150| 2019-12-17|
1003| 8003| 175| 2020-11-15|
1004| 8004| 150| 2019-12-17|
1005| 8005| 145| 2019-12-05|
1006| 8001| 150| 2020-01-05|
1007| 8002| 200| 2020-01-15|
1008| 8003| 150| 2020-12-17|
1009| 8001| 200| 2020-01-28|
1010| 8002| 180| 2020-02-07|
1011| 8001| 300| 2020-02-25|
1012| 8005| 100| 2020-01-27|
Output:
purchase_month|category_id|purchase_status| --------------+-----------+---------------+ 2019-12 | 150|increase | 2020-01 | 150|increase | 2020-02 | 150|increase | 2019-12 | 160|decrease | 2020-01 | 160|decrease | 2020-02 | 160|decrease | 2020-11 | 160|remain same | 2020-12 | 160|remain same |Click me to see the sample solution
53. From the following table write a SQL query to identifies the highest difference in total sale of all quarters on a product of many companies. Return highest and lowest total sale and highest difference.
Input:
Table: sales
Field |Type|Null|Key|Default|Extra| ----------+----+----+---+-------+-----+ company_id|int |NO |PRI| | | qtr1_sale |int |YES | | | | qtr2_sale |int |YES | | | | qtr3_sale |int |YES | | | | qtr4_sale |int |YES | | | |
Data:
company_id|qtr1_sale|qtr2_sale|qtr3_sale|qtr4_sale|
----------+---------+---------+---------+---------+
1001| 240| 310| 330| 400|
1002| 310| 250| 320| 280|
1003| 370| 420| 400| 450|
1004| 400| 340| 320| 350|
1005| 270| 350| 340| 360|
1006| 160| 200| 220| 200|
1007| 340| 350| 370| 400|
1008| 250| 280| 300| 350|
1009| 350| 300| 280| 350|
1010| 230| 260| 280| 300|
Output:
max_sale|min_sale|sale_difference|
--------+--------+---------------+
1640| 780| 860|
Click me to see the sample solution
54. A 'Smart' salesperson executed at least 5 transactions with a total sale amount is more than Rs.30000. From the following tables write a SQL query to find the smart salespersons. Return salesperson ID, name, number of transactions executed and the total amount of executed transactions.
Input:
Table: sales
Field |Type |Null|Key|Default|Extra| --------------|------------|----|---|-------|-----| TRANSACTION_ID|int(5) |NO |PRI| | | SALESMAN_ID |int(4) |NO | | | | SALE_AMOUNT |decimal(8,2)|YES | | | |
Data:
TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
501| 18| 5200.00|
502| 50| 5566.00|
503| 38| 8400.00|
504| 43| 8400.00|
505| 11| 9000.00|
506| 42| 5900.00|
507| 13| 7000.00|
508| 33| 6000.00|
509| 41| 8200.00|
510| 11| 4500.00|
511| 51| 10000.00|
512| 29| 9500.00|
513| 59| 6500.00|
514| 38| 7800.00|
515| 58| 9800.00|
516| 60| 12000.00|
517| 58| 13900.00|
518| 23| 12200.00|
519| 34| 5480.00|
520| 35| 8129.00|
521| 49| 9323.00|
522| 46| 8200.00|
523| 47| 9990.00|
524| 42| 14000.00|
525| 44| 7890.00|
526| 47| 5990.00|
527| 21| 7770.00|
528| 57| 6645.00|
529| 56| 5125.00|
530| 25| 10990.00|
531| 21| 12600.00|
532| 41| 5514.00|
533| 17| 15600.00|
534| 44| 15000.00|
535| 12| 17550.00|
536| 55| 13000.00|
537| 58| 16800.00|
538| 25| 19900.00|
539| 57| 9990.00|
540| 28| 8900.00|
541| 44| 10200.00|
542| 57| 18000.00|
543| 34| 16200.00|
544| 36| 19998.00|
545| 30| 13500.00|
546| 37| 15520.00|
547| 36| 20000.00|
548| 20| 19800.00|
549| 22| 18530.00|
550| 19| 12523.00|
551| 46| 9885.00|
552| 22| 7100.00|
553| 54| 17500.00|
554| 19| 19600.00|
555| 24| 17500.00|
556| 38| 7926.00|
557| 49| 7548.00|
558| 15| 9778.00|
559| 56| 19330.00|
560| 24| 14400.00|
561| 18| 16700.00|
562| 54| 6420.00|
563| 31| 18720.00|
564| 21| 17220.00|
565| 48| 18880.00|
566| 33| 8882.00|
567| 44| 19550.00|
568| 22| 14440.00|
569| 53| 19500.00|
570| 30| 5300.00|
571| 30| 10823.00|
572| 35| 13300.00|
573| 35| 19100.00|
574| 18| 17525.00|
575| 60| 8995.00|
576| 53| 9990.00|
577| 21| 7660.00|
578| 27| 18990.00|
579| 11| 18200.00|
580| 30| 12338.00|
581| 37| 11000.00|
582| 27| 11980.00|
583| 18| 12628.00|
584| 52| 11265.00|
585| 53| 19990.00|
586| 27| 8125.00|
587| 25| 7128.00|
588| 57| 6760.00|
589| 19| 5985.00|
590| 52| 17641.00|
591| 53| 11225.00|
592| 22| 12200.00|
593| 59| 16520.00|
594| 35| 19990.00|
595| 42| 19741.00|
596| 19| 15000.00|
597| 57| 19625.00|
598| 53| 9825.00|
599| 24| 16745.00|
600| 12| 14900.00|
Table: salesman
Field |Type |Null|Key|Default|Extra| -------------|-----------|----|---|-------|-----| SALESMAN_ID |int(4) |NO |PRI| | | SALESMAN_NAME|varchar(30)|YES | | | |
Data:
SALESMAN_ID|SALESMAN_NAME |
-----------|---------------------|
11|Jonathan Goodwin |
12|Adam Hughes |
13|Mark Davenport |
14|Jamie Shelley |
15|Ethan Birkenhead |
16|Liam Alton |
17|Josh Day |
18|Sean Mann |
19|Evan Blake |
20|Rhys Emsworth |
21|Kian Wordsworth |
22|Frederick Kelsey |
23|Noah Turner |
24|Callum Bing |
25|Harri Wilberforce |
26|Gabriel Gibson |
27|Richard York |
28|Tobias Stratford |
29|Will Kirby |
30|Bradley Wright |
31|Eli Willoughby |
32|Patrick Riley |
33|Kieran Freeman |
34|Toby Scott |
35|Elliot Clapham |
36|Lewis Moss |
37|Joshua Atterton |
38|Jonathan Reynolds |
39|David Hill |
40|Aidan Yeardley |
41|Dan Astley |
42|Finlay Dalton |
43|Toby Rodney |
44|Ollie Wheatley |
45|Sean Spalding |
46|Jason Wilson |
47|Christopher Wentworth|
48|Cameron Ansley |
49|Henry Porter |
50|Ezra Winterbourne |
51|Rufus Fleming |
52|Wallace Dempsey |
53|Dan McKee |
54|Marion Caldwell |
55|Morris Phillips |
56|Chester Chandler |
57|Cleveland Klein |
58|Hubert Bean |
59|Cleveland Hart |
60|Marion Gregory |
Output:
salesman_id|name |order_count|total_sale_amount|
-----------+---------------------+-----------+-----------------+
57|Cleveland Klein | 5| 61020.00|
53|Dan McKee | 5| 70530.00|
Click me to see the sample solution
55. From the following tables write a SQL query to find the minimum order amount sold by each salesperson. Return the salesperson ID, name and the minimum order amount they sold.
Input:
Table: sales
Field |Type |Null|Key|Default|Extra| --------------|------------|----|---|-------|-----| TRANSACTION_ID|int(5) |NO |PRI| | | SALESMAN_ID |int(4) |NO | | | | SALE_AMOUNT |decimal(8,2)|YES | | | |
Data:
TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
501| 18| 5200.00|
502| 50| 5566.00|
503| 38| 8400.00|
504| 43| 8400.00|
505| 11| 9000.00|
506| 42| 5900.00|
507| 13| 7000.00|
508| 33| 6000.00|
509| 41| 8200.00|
510| 11| 4500.00|
511| 51| 10000.00|
512| 29| 9500.00|
513| 59| 6500.00|
514| 38| 7800.00|
515| 58| 9800.00|
516| 60| 12000.00|
517| 58| 13900.00|
518| 23| 12200.00|
519| 34| 5480.00|
520| 35| 8129.00|
521| 49| 9323.00|
522| 46| 8200.00|
523| 47| 9990.00|
524| 42| 14000.00|
525| 44| 7890.00|
526| 47| 5990.00|
527| 21| 7770.00|
528| 57| 6645.00|
529| 56| 5125.00|
530| 25| 10990.00|
531| 21| 12600.00|
532| 41| 5514.00|
533| 17| 15600.00|
534| 44| 15000.00|
535| 12| 17550.00|
536| 55| 13000.00|
537| 58| 16800.00|
538| 25| 19900.00|
539| 57| 9990.00|
540| 28| 8900.00|
541| 44| 10200.00|
542| 57| 18000.00|
543| 34| 16200.00|
544| 36| 19998.00|
545| 30| 13500.00|
546| 37| 15520.00|
547| 36| 20000.00|
548| 20| 19800.00|
549| 22| 18530.00|
550| 19| 12523.00|
551| 46| 9885.00|
552| 22| 7100.00|
553| 54| 17500.00|
554| 19| 19600.00|
555| 24| 17500.00|
556| 38| 7926.00|
557| 49| 7548.00|
558| 15| 9778.00|
559| 56| 19330.00|
560| 24| 14400.00|
561| 18| 16700.00|
562| 54| 6420.00|
563| 31| 18720.00|
564| 21| 17220.00|
565| 48| 18880.00|
566| 33| 8882.00|
567| 44| 19550.00|
568| 22| 14440.00|
569| 53| 19500.00|
570| 30| 5300.00|
571| 30| 10823.00|
572| 35| 13300.00|
573| 35| 19100.00|
574| 18| 17525.00|
575| 60| 8995.00|
576| 53| 9990.00|
577| 21| 7660.00|
578| 27| 18990.00|
579| 11| 18200.00|
580| 30| 12338.00|
581| 37| 11000.00|
582| 27| 11980.00|
583| 18| 12628.00|
584| 52| 11265.00|
585| 53| 19990.00|
586| 27| 8125.00|
587| 25| 7128.00|
588| 57| 6760.00|
589| 19| 5985.00|
590| 52| 17641.00|
591| 53| 11225.00|
592| 22| 12200.00|
593| 59| 16520.00|
594| 35| 19990.00|
595| 42| 19741.00|
596| 19| 15000.00|
597| 57| 19625.00|
598| 53| 9825.00|
599| 24| 16745.00|
600| 12| 14900.00|
Table: salesman
Field |Type |Null|Key|Default|Extra| -------------|-----------|----|---|-------|-----| SALESMAN_ID |int(4) |NO |PRI| | | SALESMAN_NAME|varchar(30)|YES | | | |
Data:
SALESMAN_ID|SALESMAN_NAME |
-----------|---------------------|
11|Jonathan Goodwin |
12|Adam Hughes |
13|Mark Davenport |
14|Jamie Shelley |
15|Ethan Birkenhead |
16|Liam Alton |
17|Josh Day |
18|Sean Mann |
19|Evan Blake |
20|Rhys Emsworth |
21|Kian Wordsworth |
22|Frederick Kelsey |
23|Noah Turner |
24|Callum Bing |
25|Harri Wilberforce |
26|Gabriel Gibson |
27|Richard York |
28|Tobias Stratford |
29|Will Kirby |
30|Bradley Wright |
31|Eli Willoughby |
32|Patrick Riley |
33|Kieran Freeman |
34|Toby Scott |
35|Elliot Clapham |
36|Lewis Moss |
37|Joshua Atterton |
38|Jonathan Reynolds |
39|David Hill |
40|Aidan Yeardley |
41|Dan Astley |
42|Finlay Dalton |
43|Toby Rodney |
44|Ollie Wheatley |
45|Sean Spalding |
46|Jason Wilson |
47|Christopher Wentworth|
48|Cameron Ansley |
49|Henry Porter |
50|Ezra Winterbourne |
51|Rufus Fleming |
52|Wallace Dempsey |
53|Dan McKee |
54|Marion Caldwell |
55|Morris Phillips |
56|Chester Chandler |
57|Cleveland Klein |
58|Hubert Bean |
59|Cleveland Hart |
60|Marion Gregory |
Output:
salesman_id|salesman_name |minimum_sale_amount|
-----------+---------------------+-------------------+
11|Jonathan Goodwin | 4500.00|
12|Adam Hughes | 14900.00|
13|Mark Davenport | 7000.00|
15|Ethan Birkenhead | 9778.00|
17|Josh Day | 15600.00|
18|Sean Mann | 5200.00|
19|Evan Blake | 5985.00|
20|Rhys Emsworth | 19800.00|
21|Kian Wordsworth | 7660.00|
22|Frederick Kelsey | 7100.00|
23|Noah Turner | 12200.00|
24|Callum Bing | 14400.00|
25|Harri Wilberforce | 7128.00|
27|Richard York | 8125.00|
28|Tobias Stratford | 8900.00|
29|Will Kirby | 9500.00|
30|Bradley Wright | 5300.00|
31|Eli Willoughby | 18720.00|
33|Kieran Freeman | 6000.00|
34|Toby Scott | 5480.00|
35|Elliot Clapham | 8129.00|
36|Lewis Moss | 19998.00|
37|Joshua Atterton | 11000.00|
38|Jonathan Reynolds | 7800.00|
41|Dan Astley | 5514.00|
42|Finlay Dalton | 5900.00|
43|Toby Rodney | 8400.00|
44|Ollie Wheatley | 7890.00|
46|Jason Wilson | 8200.00|
47|Christopher Wentworth| 5990.00|
48|Cameron Ansley | 18880.00|
49|Henry Porter | 7548.00|
50|Ezra Winterbourne | 5566.00|
51|Rufus Fleming | 10000.00|
52|Wallace Dempsey | 11265.00|
53|Dan McKee | 9825.00|
54|Marion Caldwell | 6420.00|
55|Morris Phillips | 13000.00|
56|Chester Chandler | 5125.00|
57|Cleveland Klein | 6645.00|
58|Hubert Bean | 9800.00|
59|Cleveland Hart | 6500.00|
60|Marion Gregory | 8995.00|
Click me to see the sample solution
56. From the following table write a SQL query to find the number of male and female employees in each department and along with their total salaries. Return department ID, number of female employees, their total salaries, number of male employees and their total salaries.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| --------------+-----------+----+---+-------+-----+ emp_id |int |NO |PRI| | | emp_name |varchar(30)|YES | | | | emp_sex |varchar(1) |YES | | | | emp_salary |int |YES | | | | emp_department|int |YES | | | |
Data:
emp_id|emp_name |emp_sex|emp_salary|emp_department| ------+-----------+-------+----------+--------------+ 100|Steven |M | 24000| 90| 101|Neena |F | 17000| 90| 102|Lex |M | 17000| 80| 103|Alexander |M | 9000| 60| 104|Bruce |M | 6000| 60| 105|David |M | 4800| 80| 106|Valli |F | 4800| 60| 107|Diana |F | 4200| 60| 108|Nancy |M | 12000| 100| 109|Daniel |F | 9000| 100| 110|John |M | 8200| 100| 111|Ismael |M | 7700| 100| 112|Jose Manuel|M | 7800| 100| 113|Luis |F | 6900| 100| 114|Den |M | 11000| 30| 115|Alexander |M | 3100| 30| 116|Shelli |F | 2900| 30| 117|Sigal |F | 2800| 30| 133|Jason |M | 3300| 50| 134|Michael |F | 2900| 50| 135|Ki |F | 2400| 50|
Output:
department|female_employees|female_total_salary|male_employees|male_total_salary|
----------+----------------+-------------------+--------------+-----------------+
90| 1| 17000| 1| 24000|
80| | | 2| 21800|
60| 2| 9000| 2| 15000|
100| 2| 15900| 4| 35700|
30| 2| 5700| 2| 14100|
50| 2| 5300| 1| 3300|
Click me to see the sample solution
57. From the following table write a query in SQL find the employees in department Sales who solved the cases for all quarters are more than 1200. Return the name of the employees and total cases solved for all quarter. Result should be in ascending order or employee name.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| --------------+-----------+----+---+-------+-----+ emp_id |int |NO |PRI| | | emp_name |varchar(30)|YES | | | | emp_sex |varchar(1) |YES | | | | depart_name |varchar(25)|YES | | | | emp_salary |int |YES | | | | qtr1 |int |YES | | | | qtr2 |int |YES | | | | qtr3 |int |YES | | | | qtr4 |int |YES | | | | emp_department|int |YES | | | |
Data:
emp_id|emp_name |emp_sex|depart_name |emp_salary|qtr1|qtr2|qtr3|qtr4|emp_department| ------+-----------+-------+--------------+----------+----+----+----+----+--------------+ 100|Steven |M |Production | 24000| 240| 310| 275| 300| 90| 101|Neena |F |Production | 17000| 270| 300| 275| 290| 90| 102|Lex |M |Audit | 17000| 300| 290| 285| 310| 80| 103|Alexander |M |Marketing | 9000| 25| 270| 260| 280| 60| 104|Bruce |M |Marketing | 6000| 300| 280| 275| 290| 60| 105|David |M |Audit | 4800| 200| 220| 250| 270| 80| 106|Valli |F |Marketing | 4800| 300| 320| 330| 350| 60| 107|Diana |F |Marketing | 4200| 280| 270| 310| 320| 60| 108|Nancy |M |Administration| 12000| 260| 280| 300| 310| 100| 109|Daniel |F |Administration| 9000| 220| 210| 240| 260| 100| 110|John |M |Administration| 8200| 300| 290| 280| 320| 100| 111|Ismael |M |Administration| 7700| 280| 300| 270| 310| 100| 112|Jose Manuel|M |Administration| 7800| 250| 280| 260| 300| 100| 113|Luis |F |Administration| 6900| 300| 280| 270| 310| 100| 114|Den |M |Sales | 11000| 280| 290| 300| 320| 30| 115|Alexander |M |Sales | 3100| 310| 300| 320| 340| 30| 116|Shelli |F |Sales | 2900| 245| 260| 280| 300| 30| 117|Sigal |F |Sales | 2800| 250| 370| 290| 320| 30| 133|Jason |M |Export | 3300| 280| 270| 300| 290| 50| 134|Michael |F |Export | 2900| 260| 280| 290| 300| 50| 135|Ki |F |Export | 2400| 240| 260| 270| 290| 50|
Output:
emp_name |total_cases| -----------+-----------+ Alexander | 1270| Sigal | 1230|Click me to see the sample solution
58. From the following table write a SQL query to find the cheapest and most expensive room in the hotels of a city. Return hotel ID, most expensive and cheapest room's ID.
Input:
Table: hotels
Field |Type |Null|Key|Default|Extra| --------------+----------+----+---+-------+-----+ hotel_id |int |YES | | | | floor_id |int |YES | | | | room_no |int |YES | | | | room_type |varchar(2)|YES | | | | price_weekdays|int |YES | | | | price_holidays|int |YES | | | |
Data:
hotel_id|floor_id|room_no|room_type|price_weekdays|price_holidays|
--------+--------+-------+---------+--------------+--------------+
201| 1| 2005|D | 5500| 6000|
201| 1| 2007|Q | 7500| 9000|
201| 2| 3008|D | 5300| 5000|
201| 5| 5005|D | 6200| 6000|
201| 5| 5010|Q | 10000| 15000|
203| 2| 4051|S | 4200| 3800|
203| 2| 4056|D | 4800| 5000|
203| 4| 5058|D | 5700| 6300|
203| 4| 5065|S | 8000| 6500|
204| 1| 3825|S | 4000| 3800|
204| 1| 3830|D | 3900| 4500|
204| 2| 4620|D | 6500| 7000|
204| 2| 4625|Q | 8000| 10000|
204| 2| 4630|S | 9500| 8500|
204| 2| 4635|Q | 15000| 20000|
205| 2| 5525|S | 4800| 4300|
205| 3| 5652|Q | 7200| 9000|
205| 3| 5658|D | 5200| 5000|
205| 5| 6010|D | 6500| 6300|
205| 3| 5670|S | 5500| 5000|
205| 5| 6015|Q | 18500| 16300|
Output:
hotel|most_expensive_room_no|cheapest_room_no| -----+----------------------+----------------+ 201| 5010| 3008| 203| 5065| 4051| 204| 4635| 3830| 205| 6015| 5525|Click me to see the sample solution
59. From the following table write a SQL query to find the managers who can ordered more than 4 reporting employees. Return employee ID and name of the employees.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
employee_id|emp_name |
-----------+-----------+
108|Nancy |
Click me to see the sample solution
60. From the following table find customers who orders more than three times. Return customer ID and number of orders the customer booked.
Input:
Table: orders
Field |Type |Null|Key|Default|Extra| -----------|-----------|----|---|-------|-----| ORDER_ID |int(5) |NO |PRI| | | CUSTOMER_ID|int(4) |NO | | | | ITEM_DESC |varchar(30)|NO | | | | ORDER_DATE |date |NO | | | |
Data:
ORDER_ID|CUSTOMER_ID|ITEM_DESC|ORDER_DATE|
--------|-----------|---------|----------|
101| 2109|juice |2020-03-03|
102| 2139|chocolate|2019-03-18|
103| 2120|juice |2019-03-18|
104| 2108|cookies |2019-03-18|
105| 2130|juice |2020-03-28|
106| 2103|cake |2019-03-29|
107| 2122|cookies |2021-03-07|
108| 2125|cake |2021-03-13|
109| 2139|cake |2019-03-30|
110| 2141|cookies |2019-03-17|
111| 2116|cake |2020-03-31|
112| 2128|cake |2021-03-04|
113| 2146|chocolate|2021-03-04|
114| 2119|cookies |2020-03-28|
115| 2142|cake |2019-03-09|
116| 2122|cake |2019-03-06|
117| 2128|chocolate|2019-03-24|
118| 2112|cookies |2019-03-24|
119| 2149|cookies |2020-03-29|
120| 2100|cookies |2021-03-18|
121| 2130|juice |2021-03-16|
122| 2103|juice |2019-03-31|
123| 2112|cookies |2019-03-23|
124| 2102|cake |2020-03-25|
125| 2120|chocolate|2020-03-21|
126| 2109|cake |2019-03-22|
127| 2101|juice |2021-03-01|
128| 2138|juice |2019-03-19|
129| 2100|juice |2019-03-29|
130| 2129|juice |2021-03-02|
131| 2123|juice |2020-03-31|
132| 2104|chocolate|2020-03-31|
133| 2110|cake |2021-03-13|
134| 2143|cake |2019-03-27|
135| 2130|juice |2019-03-12|
136| 2128|juice |2020-03-28|
137| 2133|cookies |2019-03-21|
138| 2150|cookies |2019-03-20|
139| 2120|juice |2020-03-27|
140| 2109|cake |2021-03-02|
141| 2110|cake |2021-03-13|
142| 2140|juice |2019-03-09|
143| 2112|cookies |2021-03-04|
144| 2117|chocolate|2019-03-19|
145| 2137|cookies |2020-03-23|
146| 2130|cake |2021-03-09|
147| 2133|cake |2020-03-08|
148| 2143|juice |2019-03-11|
149| 2111|chocolate|2020-03-23|
150| 2150|cookies |2021-03-04|
151| 2131|cake |2020-03-10|
152| 2140|chocolate|2019-03-17|
153| 2147|cookies |2020-03-22|
154| 2119|chocolate|2019-03-15|
155| 2116|juice |2021-03-12|
156| 2141|juice |2021-03-14|
157| 2143|cake |2019-03-16|
158| 2105|cake |2020-03-21|
159| 2149|chocolate|2019-03-11|
160| 2117|cookies |2020-03-22|
161| 2150|cookies |2020-03-21|
162| 2134|cake |2019-03-08|
163| 2133|cookies |2019-03-26|
164| 2127|juice |2019-03-27|
165| 2101|juice |2019-03-26|
166| 2137|chocolate|2021-03-12|
167| 2113|chocolate|2019-03-21|
168| 2141|cake |2019-03-21|
169| 2112|chocolate|2021-03-14|
170| 2118|juice |2020-03-30|
171| 2111|juice |2019-03-19|
172| 2146|chocolate|2021-03-13|
173| 2148|cookies |2021-03-14|
174| 2100|cookies |2021-03-13|
175| 2105|cookies |2019-03-05|
176| 2129|juice |2021-03-02|
177| 2121|juice |2019-03-16|
178| 2117|cake |2020-03-11|
179| 2133|juice |2020-03-12|
180| 2124|cake |2019-03-31|
181| 2145|cake |2021-03-07|
182| 2105|cookies |2019-03-09|
183| 2131|juice |2019-03-09|
184| 2114|chocolate|2020-03-31|
185| 2120|juice |2021-03-06|
186| 2130|juice |2021-03-06|
187| 2141|chocolate|2019-03-11|
188| 2147|cake |2021-03-14|
189| 2118|juice |2019-03-15|
190| 2136|chocolate|2020-03-22|
191| 2132|cake |2021-03-06|
192| 2137|chocolate|2019-03-31|
193| 2107|cake |2021-03-01|
194| 2111|chocolate|2019-03-18|
195| 2100|cake |2019-03-07|
196| 2106|juice |2020-03-21|
197| 2114|cookies |2019-03-25|
198| 2110|cake |2019-03-27|
199| 2130|juice |2019-03-16|
200| 2117|cake |2021-03-10|
Output:
customer_id|customer_appears|
-----------+----------------+
2120| 4|
2130| 6|
2141| 4|
2112| 4|
2100| 4|
2133| 4|
2117| 4|
Click me to see the sample solution
61. From the following table write a SQL query to find the departments where 6 or more employees are working. Return employees ID,name,designation and department ID.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
employee_id|emp_name |Designation|department_id|
-----------+-----------+-----------+-------------+
108|Nancy |FI_MGR | 100|
109|Daniel |FI_ACCOUNT | 100|
110|John |FI_ACCOUNT | 100|
111|Ismael |FI_ACCOUNT | 100|
112|Jose Manuel|FI_ACCOUNT | 100|
113|Luis |FI_ACCOUNT | 100|
133|Jason |ST_CLERK | 50|
134|Michael |ST_CLERK | 50|
135|Ki |ST_CLERK | 50|
136|Hazel |ST_CLERK | 50|
137|Renske |ST_CLERK | 50|
138|Stephen |ST_CLERK | 50|
139|John |ST_CLERK | 50|
Click me to see the sample solution
62. From the following table write a query in SQL to find the highest sale among salespersons that appears only once. Return salesperson ID and sale amount.
Input:
Table: sales
Field |Type |Null|Key|Default|Extra| --------------|------------|----|---|-------|-----| TRANSACTION_ID|int(5) |NO |PRI| | | SALESMAN_ID |int(4) |NO | | | | SALE_AMOUNT |decimal(8,2)|YES | | | |
Data:
TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------+-----------+-----------+
501| 18| 5200.00|
502| 50| 5566.00|
503| 38| 8400.00|
504| 43| 8400.00|
505| 11| 9000.00|
506| 42| 12200.00|
507| 13| 7000.00|
508| 33| 6000.00|
509| 41| 8200.00|
510| 11| 4500.00|
511| 51| 10000.00|
512| 29| 9500.00|
513| 59| 6500.00|
514| 38| 7800.00|
515| 58| 9800.00|
516| 60| 12000.00|
517| 58| 14000.00|
518| 23| 12200.00|
519| 34| 5480.00|
520| 35| 8129.00|
521| 49| 9323.00|
522| 46| 8200.00|
523| 47| 9990.00|
524| 42| 14000.00|
525| 44| 7890.00|
526| 47| 5990.00|
527| 21| 7770.00|
528| 57| 6645.00|
529| 56| 5125.00|
530| 25| 10990.00|
Output:
sale_amount| -----------+ 12000.00|Click me to see the sample solution
63. From the following table write a query in SQL to find the highest daily total order for an item between 2019-07-01 to 2019-12-31. Return item description, order date and the total order quantity.
Input:
Table: orders
Field |Type |Null|Key|Default|Extra| -----------+-----------+----+---+-------+-----+ ORDER_ID |int |NO |PRI| | | CUSTOMER_ID|int |NO | | | | ITEM_DESC |varchar(30)|NO | | | | ORDER_DATE |date |NO | | | | order_qty |int |NO | | | |
Data:
ORDER_ID|CUSTOMER_ID|ITEM_DESC |ORDER_DATE|order_qty|
--------+-----------+----------+----------+---------+
101| 2109|juice |2019-07-21| 50|
102| 2139|chocolate |2019-05-17| 40|
103| 2120|juice |2019-05-17| 40|
104| 2108|cookies |2019-05-17| 50|
105| 2130|juice |2019-10-18| 45|
106| 2103|cake |2019-07-21| 35|
107| 2122|cookies |2019-12-17| 40|
108| 2125|cake |2019-12-17| 38|
109| 2139|cake |2019-07-21| 40|
110| 2141|cookies |2019-05-17| 60|
111| 2116|cake |2019-10-18| 45|
112| 2128|cake |2019-10-18| 38|
113| 2146|chocolate |2019-10-18| 55|
114| 2119|cookies |2019-10-18| 30|
115| 2142|cake |2019-03-05| 26|
116| 2122|cake |2019-03-05| 59|
117| 2128|chocolate |2019-06-19| 45|
118| 2112|cookies |2019-06-19| 28|
119| 2149|cookies |2019-10-18| 49|
120| 2100|cookies |2020-03-14| 76|
121| 2130|juice |2020-03-14| 20|
122| 2103|juice |2019-07-21| 27|
123| 2112|cookies |2019-06-19| 52|
124| 2102|cake |2019-07-21| 14|
125| 2120|chocolate |2019-07-21| 85|
126| 2109|cake |2019-06-19| 18|
127| 2101|juice |2019-10-18| 64|
128| 2138|juice |2019-06-19| 55|
129| 2100|juice |2019-07-21| 45|
130| 2129|juice |2019-10-18| 35|
131| 2123|juice |2019-10-18| 25|
132| 2104|chocolate |2019-10-18| 63|
133| 2110|cake |2019-12-17| 38|
134| 2143|cake |2019-06-19| 27|
135| 2130|juice |2019-05-17| 30|
136| 2128|juice |2019-10-18| 40|
137| 2133|cookies |2019-06-19| 51|
138| 2150|cookies |2019-06-19| 43|
139| 2120|juice |2019-10-18| 55|
140| 2109|cake |2019-10-18| 44|
141| 2110|cake |2019-12-17| 50|
142| 2140|juice |2019-03-05| 65|
143| 2112|cookies |2019-10-18| 46|
144| 2117|chocolate |2019-06-19| 25|
145| 2137|cookies |2019-07-21| 49|
146| 2130|cake |2019-12-17| 40|
147| 2133|cake |2019-07-21| 30|
148| 2143|juice |2019-03-05| 60|
149| 2111|chocolate |2019-07-21| 74|
150| 2150|cookies |2019-10-18| 75|
151| 2131|cake |2019-07-21| 45|
152| 2140|chocolate |2019-05-17| 40|
153| 2147|cookies |2019-07-21| 65|
154| 2119|chocolate |2019-05-17| 60|
155| 2116|juice |2019-12-17| 40|
156| 2141|juice |2019-12-17| 50|
157| 2143|cake |2019-05-17| 55|
158| 2105|cake |2019-07-21| 70|
159| 2149|chocolate |2019-03-05| 50|
160| 2117|cookies |2019-07-21| 61|
161| 2150|cookies |2019-07-21| 50|
162| 2134|cake |2019-03-05| 55|
163| 2133|cookies |2019-06-19| 54|
164| 2127|juice |2019-06-19| 55|
165| 2101|juice |2019-06-19| 45|
166| 2137|chocolate |2019-12-17| 40|
167| 2113|chocolate |2019-06-19| 30|
168| 2141|cake |2019-06-19| 60|
169| 2112|chocolate |2019-12-17| 25|
170| 2118|juice |2019-10-18| 70|
171| 2111|juice |2019-06-19| 60|
172| 2146|chocolate |2019-12-17| 20|
173| 2148|cookies |2020-03-14| 55|
174| 2100|cookies |2019-12-17| 40|
175| 2105|cookies |2019-03-05| 30|
176| 2129|juice |2019-10-18| 40|
177| 2121|juice |2019-05-17| 30|
178| 2117|cake |2019-07-21| 40|
179| 2133|juice |2019-07-21| 20|
180| 2124|cake |2019-07-21| 30|
181| 2145|cake |2019-12-17| 25|
182| 2105|cookies |2019-03-05| 20|
183| 2131|juice |2019-03-05| 50|
184| 2114|chocolate |2019-10-18| 56|
185| 2120|juice |2019-12-17| 55|
186| 2130|juice |2019-12-17| 46|
187| 2141|chocolate |2019-03-05| 54|
188| 2147|cake |2020-03-14| 40|
189| 2118|juice |2019-05-17| 54|
190| 2136|chocolate |2019-07-21| 58|
191| 2132|cake |2019-12-17| 50|
192| 2137|chocolate |2019-07-21| 53|
193| 2107|cake |2019-10-18| 35|
194| 2111|chocolate |2019-05-17| 50|
195| 2100|cake |2019-03-05| 55|
196| 2106|juice |2019-07-21| 47|
197| 2114|cookies |2019-06-19| 55|
198| 2110|cake |2019-06-19| 75|
199| 2130|juice |2019-05-17| 55|
200| 2117|cake |2019-12-17| 48|
Output:
item_desc|order_date|daysOrder| ---------+----------+---------+ juice |2019-10-18| 374|Click me to see the sample solution
64. From the following tables write a query in SQL to count the number of patients treated by each doctor. Return doctors name and number of patients they treated.
Input:
Table: doctors
Field |Type |Null|Key|Default|Extra| -----------+-----------+----+---+-------+-----+ doctor_id |int |NO |PRI| | | doctor_name|varchar(25)|YES | | | | degree |varchar(10)|YES | | | |
Data:
doctor_id|doctor_name |degree|
---------+--------------+------+
210|Dr. John Linga|MD |
211|Dr. Peter Hall|MBBS |
212|Dr. Ke Gee |MD |
213|Dr. Pat Fay |MD |
Table: visits
Field |Type |Null|Key|Default|Extra| ------------+-----------+----+---+-------+-----+ doctor_id |int |YES |MUL| | | patient_name|varchar(25)|YES | | | | vdate |date |YES | | | |
Data:
doctor_id|patient_name |vdate |
---------+-------------+----------+
210|Julia Nayer |2013-10-15|
213|TJ Olson |2013-10-14|
211|John Seo |2013-10-15|
212|James Marlow |2013-10-16|
212|Jason Mallin |2013-10-12|
213|Dean Hops |2013-10-18|
212|Peter Kent |2013-10-19|
212|Moody Hogs |2013-10-25|
Output:
doctor_name |Patients Treated| --------------+----------------+ Dr. John Linga| 1| Dr. Peter Hall| 1| Dr. Ke Gee | 4| Dr. Pat Fay | 2|Click me to see the sample solution
65. From the following table write a SQL query to calculate the total order amount issued by each customer in September 2008. Return customer ID and total order amount.
Input:
Table: orders
Field |Type |Null|Key|Default|Extra| ---------------+-----------+----+---+-------+-----+ ord_num |int |NO |PRI| | | ord_amount |int |YES | | | | advance_amount |int |YES | | | | ord_date |date |YES | | | | cust_code |varchar(10)|YES |MUL| | | ord_description|varchar(25)|YES | | | |
Data:
ord_num|ord_amount|advance_amount|ord_date |cust_code|ord_description| -------+----------+--------------+----------+---------+---------------+ 200100| 1000| 600|2008-01-08|C00015 | | 200101| 3000| 1000|2008-07-15|C00001 | | 200102| 2000| 300|2008-05-25|C00012 | | 200103| 1500| 700|2008-05-15|C00021 | | 200104| 1500| 500|2008-03-13|C00006 | | 200105| 2500| 500|2008-07-18|C00025 | | 200106| 2500| 700|2008-04-20|C00005 | | 200107| 4500| 900|2008-08-30|C00007 | | 200108| 4000| 600|2008-02-15|C00008 | | 200109| 3500| 800|2008-07-30|C00011 | | 200110| 3000| 500|2008-04-15|C00019 | | 200111| 1000| 300|2008-07-10|C00020 | | 200112| 2000| 400|2008-05-30|C00016 | | 200113| 4000| 600|2008-06-10|C00022 | | 200114| 3500| 2000|2008-08-15|C00002 | | 200116| 500| 100|2008-07-13|C00010 | | 200117| 800| 200|2008-10-20|C00014 | | 200118| 500| 100|2008-07-20|C00023 | | 200119| 4000| 700|2008-09-16|C00007 | | 200120| 500| 100|2008-07-20|C00009 | | 200121| 1500| 600|2008-09-23|C00008 | | 200122| 2500| 400|2008-09-16|C00003 | | 200123| 500| 100|2008-09-16|C00022 | | 200124| 500| 100|2008-06-20|C00017 | | 200125| 2000| 600|2008-10-10|C00018 | | 200126| 500| 100|2008-06-24|C00022 | | 200127| 2500| 400|2008-07-20|C00015 | | 200128| 3500| 1500|2008-07-20|C00009 | | 200129| 2500| 500|2008-07-20|C00024 | | 200130| 2500| 400|2008-07-30|C00025 | | 200131| 900| 150|2008-08-26|C00012 | | 200133| 1200| 400|2008-06-29|C00009 | | 200134| 4200| 1800|2008-09-25|C00004 | | 200135| 2000| 800|2008-09-16|C00007 | |
Table: customer
Field |Type |Null|Key|Default|Extra| ---------------+-------------+----+---+-------+-----+ cust_code |varchar(10) |NO |PRI| | | cust_name |varchar(25) |YES | | | | cust_city |varchar(25) |YES | | | | working_area |varchar(25) |YES | | | | cust_country |varchar(25) |YES | | | | grade |int |YES | | | | opening_amt |decimal(10,2)|YES | | | | receive_amt |decimal(10,2)|YES | | | | payment_amt |decimal(10,2)|YES | | | | outstanding_amt|decimal(10,2)|YES | | | | phone_no |varchar(15) |YES | | | |
Data:
cust_code|cust_name |cust_city |working_area|cust_country|grade|opening_amt|receive_amt|payment_amt|outstanding_amt|phone_no | ---------+-----------+----------+------------+------------+-----+-----------+-----------+-----------+---------------+------------+ C00001 |Micheal |New York |New York |USA | 2| 3000.00| 5000.00| 2000.00| 6000.00|CCCCCCC | C00002 |Bolt |New York |New York |USA | 3| 5000.00| 7000.00| 9000.00| 3000.00|DDNRDRH | C00003 |Martin |Torento |Torento |Canada | 2| 8000.00| 7000.00| 7000.00| 8000.00|MJYURFD | C00004 |Winston |Brisban |Brisban |Australia | 1| 5000.00| 8000.00| 7000.00| 6000.00|AAAAAAA | C00005 |Sasikant |Mumbai |Mumbai |India | 1| 7000.00| 11000.00| 7000.00| 11000.00|147-25896312| C00006 |Shilton |Torento |Torento |Canada | 1| 10000.00| 7000.00| 6000.00| 11000.00|DDDDDDD | C00007 |Ramanathan |Chennai |Chennai |India | 1| 7000.00| 11000.00| 9000.00| 9000.00|GHRDWSD | C00008 |Karolina |Torento |Torento |Canada | 1| 7000.00| 7000.00| 9000.00| 5000.00|HJKORED | C00009 |Ramesh |Mumbai |Mumbai |India | 3| 8000.00| 7000.00| 3000.00| 12000.00|Phone No | C00010 |Charles |Hampshair |Hampshair |UK | 3| 6000.00| 4000.00| 5000.00| 5000.00|MMMMMMM | C00011 |Sundariya |Chennai |Chennai |India | 3| 7000.00| 11000.00| 7000.00| 11000.00|PPHGRTS | C00012 |Steven |San Jose |San Jose |USA | 1| 5000.00| 7000.00| 9000.00| 3000.00|KRFYGJK | C00013 |Holmes |London |London |UK | 2| 6000.00| 5000.00| 7000.00| 4000.00|BBBBBBB | C00014 |Rangarappa |Bangalore |Bangalore |India | 2| 8000.00| 11000.00| 7000.00| 12000.00|AAAATGF | C00015 |Stuart |London |London |UK | 1| 6000.00| 8000.00| 3000.00| 11000.00|GFSGERS | C00016 |Venkatpati |Bangalore |Bangalore |India | 2| 8000.00| 11000.00| 7000.00| 12000.00|JRTVFDD | C00017 |Srinivas |Bangalore |Bangalore |India | 2| 8000.00| 4000.00| 3000.00| 9000.00|AAAAAAB | C00018 |Fleming |Brisban |Brisban |Australia | 2| 7000.00| 7000.00| 9000.00| 5000.00|NHBGVFC | C00019 |Yearannaidu|Chennai |Chennai |India | 1| 8000.00| 7000.00| 7000.00| 8000.00|ZZZZBFV | C00020 |Albert |New York |New York |USA | 3| 5000.00| 7000.00| 6000.00| 6000.00|BBBBSBB | C00021 |Jacks |Brisban |Brisban |Australia | 1| 7000.00| 7000.00| 7000.00| 7000.00|WERTGDF | C00022 |Avinash |Mumbai |Mumbai |India | 2| 7000.00| 11000.00| 9000.00| 9000.00|113-12345678| C00023 |Karl |London |London |UK | 0| 4000.00| 6000.00| 7000.00| 3000.00|AAAABAA | C00024 |Cook |London |London |UK | 2| 4000.00| 9000.00| 7000.00| 6000.00|FSDDSDF | C00025 |Ravindran |Bangalore |Bangalore |India | 2| 5000.00| 7000.00| 4000.00| 8000.00|AVAVAVA |
Output:
cust_code|Total Order| ---------+-----------+ C00007 | 6000| C00004 | 4200| C00003 | 2500| C00008 | 1500| C00022 | 500|Click me to see the sample solution
66. From the following table write a SQL query to show the salary of each employee along with the average salary for their department. Return job ID,name,salary and average salary.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
job_id |emp_name |salary |Average_Salary| ----------+-----------+--------+--------------+ AD_PRES |Steven |24000.00| 24000.000000| AD_VP |Neena |17000.00| 17000.000000| AD_VP |Lex |17000.00| 17000.000000| IT_PROG |Alexander | 9000.00| 5760.000000| IT_PROG |Bruce | 6000.00| 5760.000000| IT_PROG |David | 4800.00| 5760.000000| IT_PROG |Valli | 4800.00| 5760.000000| IT_PROG |Diana | 4200.00| 5760.000000| FI_MGR |Nancy |12000.00| 12000.000000| FI_ACCOUNT|Daniel | 9000.00| 7920.000000| FI_ACCOUNT|John | 8200.00| 7920.000000| FI_ACCOUNT|Ismael | 7700.00| 7920.000000| FI_ACCOUNT|Jose Manuel| 7800.00| 7920.000000| FI_ACCOUNT|Luis | 6900.00| 7920.000000| PU_MAN |Den |11000.00| 11000.000000| PU_CLERK |Alexander | 3100.00| 2933.333333| PU_CLERK |Shelli | 2900.00| 2933.333333| PU_CLERK |Sigal | 2800.00| 2933.333333| ST_CLERK |Jason | 3300.00| 2900.000000| ST_CLERK |Michael | 2900.00| 2900.000000| ST_CLERK |Ki | 2400.00| 2900.000000| ST_CLERK |Hazel | 2200.00| 2900.000000| ST_CLERK |Renske | 3600.00| 2900.000000| ST_CLERK |Stephen | 3200.00| 2900.000000| ST_CLERK |John | 2700.00| 2900.000000|Click me to see the sample solution
67. From the following table write a query in SQL to find the highest salaried employees for each designation. Return job_id, name and the salary earned.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
job_id |Name |salary | ----------+-----------+--------+ AD_PRES |Steven |24000.00| AD_VP |Neena |17000.00| AD_VP |Lex |17000.00| IT_PROG |Alexander | 9000.00| FI_MGR |Nancy |12000.00| FI_ACCOUNT|Daniel | 9000.00| PU_MAN |Den |11000.00| PU_CLERK |Alexander | 3100.00| ST_CLERK |Renske | 3600.00|Click me to see the sample solution
68. From the following table write a query in SQL to find the employees who are earning more salary than their managers. Return employee ID, name, and salary, manager ID, manager name and the salary of the manager.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG |11000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
employee_id|emp_name |salary |manager_id|Manager Name|Manager Salary|
-----------+-----------+--------+----------+------------+--------------+
104|Bruce |11000.00| 103|Alexander | 9000.00|
Click me to see the sample solution
69. From the following tables write a query in SQL to find the cost percent for each order of total order amount of a customer. Return customer ID, customer name, order details, order amount and cost percent.
Input:
Table: orders
Field |Type |Null|Key|Default|Extra| ---------------+-----------+----+---+-------+-----+ ord_num |int |NO |PRI| | | ord_amount |int |YES | | | | advance_amount |int |YES | | | | ord_date |date |YES | | | | cust_code |varchar(10)|YES |MUL| | | ord_description|varchar(25)|YES | | | |
Data:
ord_num|ord_amount|advance_amount|ord_date |cust_code|ord_description| -------+----------+--------------+----------+---------+---------------+ 200100| 1000| 600|2008-01-08|C00015 |RAM | 200102| 2000| 300|2008-05-25|C00012 |mother board | 200105| 2500| 500|2008-07-18|C00025 |RAM | 200107| 4500| 900|2008-08-30|C00007 |hard disk | 200108| 4000| 600|2008-02-15|C00008 |mouse | 200109| 3500| 800|2008-07-30|C00015 |key board | 200113| 4000| 600|2008-06-10|C00022 |key board | 200116| 500| 100|2008-07-13|C00010 |mouse | 200118| 500| 100|2008-07-20|C00022 |mouse | 200119| 4000| 700|2008-09-16|C00007 |RAM | 200121| 1500| 600|2008-09-23|C00008 |key board | 200122| 2500| 400|2008-09-16|C00007 |mother board | 200123| 500| 100|2008-09-16|C00022 |mother board | 200125| 2000| 600|2008-10-10|C00008 |hard disk | 200126| 500| 100|2008-06-24|C00022 |hard disk | 200127| 2500| 400|2008-07-20|C00015 |mouse | 200128| 3500| 1500|2008-07-20|C00009 |mother board | 200129| 2500| 500|2008-07-20|C00022 |RAM | 200130| 2500| 400|2008-07-30|C00025 |mother board | 200131| 900| 150|2008-08-26|C00012 |hard disk | 200133| 1200| 400|2008-06-29|C00009 |mouse |
Table: customer
Field |Type |Null|Key|Default|Extra| ---------------+-------------+----+---+-------+-----+ cust_code |varchar(10) |NO |PRI| | | cust_name |varchar(25) |YES | | | | cust_city |varchar(25) |YES | | | | working_area |varchar(25) |YES | | | | cust_country |varchar(25) |YES | | | | grade |int |YES | | | | opening_amt |decimal(10,2)|YES | | | | receive_amt |decimal(10,2)|YES | | | | payment_amt |decimal(10,2)|YES | | | | outstanding_amt|decimal(10,2)|YES | | | | phone_no |varchar(15) |YES | | | |
Data:
cust_code|cust_name |cust_city |working_area|cust_country|grade|opening_amt|receive_amt|payment_amt|outstanding_amt|phone_no | ---------+-----------+----------+------------+------------+-----+-----------+-----------+-----------+---------------+------------+ C00001 |Micheal |New York |New York |USA | 2| 3000.00| 5000.00| 2000.00| 6000.00|CCCCCCC | C00002 |Bolt |New York |New York |USA | 3| 5000.00| 7000.00| 9000.00| 3000.00|DDNRDRH | C00003 |Martin |Torento |Torento |Canada | 2| 8000.00| 7000.00| 7000.00| 8000.00|MJYURFD | C00004 |Winston |Brisban |Brisban |Australia | 1| 5000.00| 8000.00| 7000.00| 6000.00|AAAAAAA | C00005 |Sasikant |Mumbai |Mumbai |India | 1| 7000.00| 11000.00| 7000.00| 11000.00|147-25896312| C00006 |Shilton |Torento |Torento |Canada | 1| 10000.00| 7000.00| 6000.00| 11000.00|DDDDDDD | C00007 |Ramanathan |Chennai |Chennai |India | 1| 7000.00| 11000.00| 9000.00| 9000.00|GHRDWSD | C00008 |Karolina |Torento |Torento |Canada | 1| 7000.00| 7000.00| 9000.00| 5000.00|HJKORED | C00009 |Ramesh |Mumbai |Mumbai |India | 3| 8000.00| 7000.00| 3000.00| 12000.00|Phone No | C00010 |Charles |Hampshair |Hampshair |UK | 3| 6000.00| 4000.00| 5000.00| 5000.00|MMMMMMM | C00011 |Sundariya |Chennai |Chennai |India | 3| 7000.00| 11000.00| 7000.00| 11000.00|PPHGRTS | C00012 |Steven |San Jose |San Jose |USA | 1| 5000.00| 7000.00| 9000.00| 3000.00|KRFYGJK | C00013 |Holmes |London |London |UK | 2| 6000.00| 5000.00| 7000.00| 4000.00|BBBBBBB | C00014 |Rangarappa |Bangalore |Bangalore |India | 2| 8000.00| 11000.00| 7000.00| 12000.00|AAAATGF | C00015 |Stuart |London |London |UK | 1| 6000.00| 8000.00| 3000.00| 11000.00|GFSGERS | C00016 |Venkatpati |Bangalore |Bangalore |India | 2| 8000.00| 11000.00| 7000.00| 12000.00|JRTVFDD | C00017 |Srinivas |Bangalore |Bangalore |India | 2| 8000.00| 4000.00| 3000.00| 9000.00|AAAAAAB | C00018 |Fleming |Brisban |Brisban |Australia | 2| 7000.00| 7000.00| 9000.00| 5000.00|NHBGVFC | C00019 |Yearannaidu|Chennai |Chennai |India | 1| 8000.00| 7000.00| 7000.00| 8000.00|ZZZZBFV | C00020 |Albert |New York |New York |USA | 3| 5000.00| 7000.00| 6000.00| 6000.00|BBBBSBB | C00021 |Jacks |Brisban |Brisban |Australia | 1| 7000.00| 7000.00| 7000.00| 7000.00|WERTGDF | C00022 |Avinash |Mumbai |Mumbai |India | 2| 7000.00| 11000.00| 9000.00| 9000.00|113-12345678| C00023 |Karl |London |London |UK | 0| 4000.00| 6000.00| 7000.00| 3000.00|AAAABAA | C00024 |Cook |London |London |UK | 2| 4000.00| 9000.00| 7000.00| 6000.00|FSDDSDF | C00025 |Ravindran |Bangalore |Bangalore |India | 2| 5000.00| 7000.00| 4000.00| 8000.00|AVAVAVA |
Output:
cust_code|cust_name |ord_description|ord_amount|Percentage of Cost| ---------+-----------+---------------+----------+------------------+ C00022 |Avinash |key board | 4000| 0.5000| C00022 |Avinash |mouse | 500| 0.0625| C00022 |Avinash |mother board | 500| 0.0625| C00022 |Avinash |hard disk | 500| 0.0625| C00022 |Avinash |RAM | 2500| 0.3125| C00010 |Charles |mouse | 500| 1.0000| C00008 |Karolina |mouse | 4000| 0.5333| C00008 |Karolina |key board | 1500| 0.2000| C00008 |Karolina |hard disk | 2000| 0.2667| C00007 |Ramanathan |hard disk | 4500| 0.4091| C00007 |Ramanathan |RAM | 4000| 0.3636| C00007 |Ramanathan |mother board | 2500| 0.2273| C00009 |Ramesh |mother board | 3500| 0.7447| C00009 |Ramesh |mouse | 1200| 0.2553| C00025 |Ravindran |RAM | 2500| 0.5000| C00025 |Ravindran |mother board | 2500| 0.5000| C00012 |Steven |mother board | 2000| 0.6897| C00012 |Steven |hard disk | 900| 0.3103| C00015 |Stuart |RAM | 1000| 0.1429| C00015 |Stuart |key board | 3500| 0.5000| C00015 |Stuart |mouse | 2500| 0.3571|Click me to see the sample solution
70. From the following table write a query in SQL to find those employees who resolve the highest complaint in all quarter and works under the supervision of that manager, holding id 114. Returns employee name and the number of complaint resolved.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| --------------+-----------+----+---+-------+-----+ emp_id |int |NO |PRI| | | emp_name |varchar(30)|YES | | | | emp_sex |varchar(1) |YES | | | | depart_name |varchar(25)|YES | | | | emp_salary |int |YES | | | | qtr1 |int |YES | | | | qtr2 |int |YES | | | | qtr3 |int |YES | | | | qtr4 |int |YES | | | | emp_department|int |YES | | | | manager_id |int |YES | | | |
Data:
emp_id|emp_name |emp_sex|depart_name |emp_salary|qtr1|qtr2|qtr3|qtr4|emp_department| manager_id| ------+-----------+-------+--------------+----------+----+----+----+----+--------------+ ----------+ 100|Steven |M |Production | 24000| 240| 310| 275| 300| 90| 0| 101|Neena |F |Production | 17000| 270| 300| 275| 290| 90| 100| 102|Lex |M |Audit | 17000| 300| 290| 285| 310| 80| 100| 103|Alexander |M |Marketing | 9000| 25| 270| 260| 280| 60| 102| 104|Bruce |M |Marketing | 6000| 300| 280| 275| 290| 60| 103| 105|David |M |Audit | 4800| 200| 220| 250| 270| 80| 103| 106|Valli |F |Marketing | 4800| 300| 320| 330| 350| 60| 103| 107|Diana |F |Marketing | 4200| 280| 270| 310| 320| 60| 103| 108|Nancy |M |Administration| 12000| 260| 280| 300| 310| 100| 101| 109|Daniel |F |Administration| 9000| 220| 210| 240| 260| 100| 108| 110|John |M |Administration| 8200| 300| 290| 280| 320| 100| 108| 111|Ismael |M |Administration| 7700| 280| 300| 270| 310| 100| 108| 112|Jose Manuel|M |Administration| 7800| 250| 280| 260| 300| 100| 108| 113|Luis |F |Administration| 6900| 300| 280| 270| 310| 100| 108| 114|Den |M |Sales | 11000| 280| 290| 300| 320| 30| 100| 115|Alexander |M |Sales | 3100| 310| 300| 320| 340| 30| 114| 116|Shelli |F |Sales | 2900| 245| 260| 280| 300| 30| 114| 117|Sigal |F |Sales | 2800| 250| 370| 290| 320| 30| 114| 133|Jason |M |Export | 3300| 280| 270| 300| 290| 50| 122| 134|Michael |F |Export | 2900| 260| 280| 290| 300| 50| 122| 135|Ki |F |Export | 2400| 240| 260| 270| 290| 50| 122|
Output:
emp_name |Case Resolved| -----------+-------------+ Ismael | 1160| Luis | 1160|Click me to see the sample solution
71. From the following tables write a query in SQL to find the number of customer issued orders, number of orders they have issued along with the total order amount for each cities. Those cities should come in result set where four or more orders have been issued. Returns city name, number of customers, number of orders and total order amount.
Input:
Table: customer
Field |Type |Null|Key|Default|Extra| ------------+-----------+----+---+-------+-----+ cust_code |varchar(10)|NO |PRI| | | cust_name |varchar(25)|YES | | | | cust_city |varchar(25)|YES | | | | cust_country|varchar(25)|YES | | | | grade |int |YES | | | |
Data:
cust_code|cust_name |cust_city |cust_country|grade| ---------+-----------+----------+------------+-----+ C00001 |Micheal |New York |USA | 2| C00002 |Bolt |New York |USA | 3| C00003 |Martin |Torento |Canada | 2| C00004 |Winston |Brisban |Australia | 1| C00005 |Sasikant |Mumbai |India | 1| C00006 |Shilton |Torento |Canada | 1| C00007 |Ramanathan |Chennai |India | 1| C00008 |Karolina |Torento |Canada | 1| C00009 |Ramesh |Mumbai |India | 3| C00010 |Charles |Hampshair |UK | 3| C00011 |Sundariya |Chennai |India | 3| C00012 |Steven |San Jose |USA | 1| C00013 |Holmes |London |UK | 2| C00014 |Rangarappa |Bangalore |India | 2| C00015 |Stuart |London |UK | 1| C00016 |Venkatpati |Bangalore |India | 2| C00017 |Srinivas |Bangalore |India | 2| C00018 |Fleming |Brisban |Australia | 2| C00019 |Yearannaidu|Chennai |India | 1| C00020 |Albert |New York |USA | 3| C00021 |Jacks |Brisban |Australia | 1| C00022 |Avinash |Mumbai |India | 2| C00023 |Karl |London |UK | 0| C00024 |Cook |London |UK | 2| C00025 |Ravindran |Bangalore |India | 2|
Table: orders
Field |Type |Null|Key|Default|Extra| ----------+-----------+----+---+-------+-----+ ord_num |int |NO |PRI| | | ord_amount|int |YES | | | | ord_date |date |YES | | | | cust_code |varchar(10)|YES |MUL| | |
Data:
ord_num|ord_amount|ord_date |cust_code| -------+----------+----------+---------+ 200100| 1000|2008-01-08|C00015 | 200101| 3000|2008-07-15|C00001 | 200102| 2000|2008-05-25|C00012 | 200103| 1500|2008-05-15|C00021 | 200104| 1500|2008-03-13|C00006 | 200105| 2500|2008-07-18|C00025 | 200106| 2500|2008-04-20|C00005 | 200107| 4500|2008-08-30|C00007 | 200108| 4000|2008-02-15|C00008 | 200109| 3500|2008-07-30|C00011 | 200110| 3000|2008-04-15|C00019 | 200111| 1000|2008-07-10|C00020 | 200112| 2000|2008-05-30|C00016 | 200113| 4000|2008-06-10|C00022 | 200114| 3500|2008-08-15|C00002 | 200116| 500|2008-07-13|C00010 | 200117| 800|2008-10-20|C00014 | 200118| 500|2008-07-20|C00023 | 200119| 4000|2008-09-16|C00007 | 200120| 500|2008-07-20|C00009 | 200121| 1500|2008-09-23|C00008 | 200122| 2500|2008-09-16|C00003 | 200123| 500|2008-09-16|C00022 | 200124| 500|2008-06-20|C00017 | 200125| 2000|2008-10-10|C00018 | 200126| 500|2008-06-24|C00022 | 200127| 2500|2008-07-20|C00015 | 200128| 3500|2008-07-20|C00009 | 200129| 2500|2008-07-20|C00024 | 200130| 2500|2008-07-30|C00025 | 200131| 900|2008-08-26|C00012 | 200133| 1200|2008-06-29|C00009 | 200134| 4200|2008-09-25|C00004 | 200135| 2000|2008-09-16|C00007 |
Output:
cust_city |Number of orders|Number of Customer|Total order Amountt| ----------+----------------+------------------+-------------------+ Bangalore | 5| 4| 8300| Chennai | 5| 3| 17000| London | 4| 4| 6500| Mumbai | 7| 3| 12700| Torento | 4| 3| 9500|Click me to see the sample solution
72. From the following table write a query in SQL to find the highest three unique salaries for each department. Return department ID and three highest unique salaries. Arranged the result set in ascending order on department ID and descending order on salaries.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
department_id|salary |
-------------+--------+
30|11000.00|
30| 3100.00|
30| 2900.00|
50| 3600.00|
50| 3300.00|
50| 3200.00|
60| 9000.00|
60| 6000.00|
60| 4800.00|
90|24000.00|
90|17000.00|
100|12000.00|
100| 9000.00|
100| 8200.00|
Click me to see the sample solution
73. From the following tables write a query in SQL to find the salespersons who not yet made any sale transaction. Return salesperson ID and salesperson's name. Arranged the resultset in ascending order on salesman ID.
Input:
Table: sales
Field |Type |Null|Key|Default|Extra| --------------|------------|----|---|-------|-----| TRANSACTION_ID|int(5) |NO |PRI| | | SALESMAN_ID |int(4) |NO | | | | SALE_AMOUNT |decimal(8,2)|YES | | | |
Data:
TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
501| 18| 5200.00|
502| 50| 5566.00|
503| 38| 8400.00|
504| 43| 8400.00|
505| 11| 9000.00|
506| 42| 5900.00|
507| 13| 7000.00|
508| 33| 6000.00|
509| 41| 8200.00|
510| 11| 4500.00|
511| 51| 10000.00|
512| 29| 9500.00|
513| 59| 6500.00|
514| 38| 7800.00|
515| 58| 9800.00|
516| 60| 12000.00|
517| 58| 13900.00|
518| 23| 12200.00|
519| 34| 5480.00|
520| 35| 8129.00|
521| 49| 9323.00|
522| 46| 8200.00|
523| 47| 9990.00|
524| 42| 14000.00|
525| 44| 7890.00|
526| 47| 5990.00|
527| 21| 7770.00|
528| 57| 6645.00|
529| 56| 5125.00|
530| 25| 10990.00|
531| 21| 12600.00|
532| 41| 5514.00|
533| 17| 15600.00|
534| 44| 15000.00|
535| 12| 17550.00|
536| 55| 13000.00|
537| 58| 16800.00|
538| 25| 19900.00|
539| 57| 9990.00|
540| 28| 8900.00|
541| 44| 10200.00|
542| 57| 18000.00|
543| 34| 16200.00|
544| 36| 19998.00|
545| 30| 13500.00|
546| 37| 15520.00|
547| 36| 20000.00|
548| 20| 19800.00|
549| 22| 18530.00|
550| 19| 12523.00|
551| 46| 9885.00|
552| 22| 7100.00|
553| 54| 17500.00|
554| 19| 19600.00|
555| 24| 17500.00|
556| 38| 7926.00|
557| 49| 7548.00|
558| 15| 9778.00|
559| 56| 19330.00|
560| 24| 14400.00|
561| 18| 16700.00|
562| 54| 6420.00|
563| 31| 18720.00|
564| 21| 17220.00|
565| 48| 18880.00|
566| 33| 8882.00|
567| 44| 19550.00|
568| 22| 14440.00|
569| 53| 19500.00|
570| 30| 5300.00|
571| 30| 10823.00|
572| 35| 13300.00|
573| 35| 19100.00|
574| 18| 17525.00|
575| 60| 8995.00|
576| 53| 9990.00|
577| 21| 7660.00|
578| 27| 18990.00|
579| 11| 18200.00|
580| 30| 12338.00|
581| 37| 11000.00|
582| 27| 11980.00|
583| 18| 12628.00|
584| 52| 11265.00|
585| 53| 19990.00|
586| 27| 8125.00|
587| 25| 7128.00|
588| 57| 6760.00|
589| 19| 5985.00|
590| 52| 17641.00|
591| 53| 11225.00|
592| 22| 12200.00|
593| 59| 16520.00|
594| 35| 19990.00|
595| 42| 19741.00|
596| 19| 15000.00|
597| 57| 19625.00|
598| 53| 9825.00|
599| 24| 16745.00|
600| 12| 14900.00|
Table: salesman
Field |Type |Null|Key|Default|Extra| -------------|-----------|----|---|-------|-----| SALESMAN_ID |int(4) |NO |PRI| | | SALESMAN_NAME|varchar(30)|YES | | | |
Data:
SALESMAN_ID|SALESMAN_NAME |
-----------|---------------------|
11|Jonathan Goodwin |
12|Adam Hughes |
13|Mark Davenport |
14|Jamie Shelley |
15|Ethan Birkenhead |
16|Liam Alton |
17|Josh Day |
18|Sean Mann |
19|Evan Blake |
20|Rhys Emsworth |
21|Kian Wordsworth |
22|Frederick Kelsey |
23|Noah Turner |
24|Callum Bing |
25|Harri Wilberforce |
26|Gabriel Gibson |
27|Richard York |
28|Tobias Stratford |
29|Will Kirby |
30|Bradley Wright |
31|Eli Willoughby |
32|Patrick Riley |
33|Kieran Freeman |
34|Toby Scott |
35|Elliot Clapham |
36|Lewis Moss |
37|Joshua Atterton |
38|Jonathan Reynolds |
39|David Hill |
40|Aidan Yeardley |
41|Dan Astley |
42|Finlay Dalton |
43|Toby Rodney |
44|Ollie Wheatley |
45|Sean Spalding |
46|Jason Wilson |
47|Christopher Wentworth|
48|Cameron Ansley |
49|Henry Porter |
50|Ezra Winterbourne |
51|Rufus Fleming |
52|Wallace Dempsey |
53|Dan McKee |
54|Marion Caldwell |
55|Morris Phillips |
56|Chester Chandler |
57|Cleveland Klein |
58|Hubert Bean |
59|Cleveland Hart |
60|Marion Gregory |
Output:
SALESMAN_ID|SALESMAN_NAME |
-----------+---------------------+
14|Jamie Shelley |
16|Liam Alton |
26|Gabriel Gibson |
32|Patrick Riley |
39|David Hill |
40|Aidan Yeardley |
45|Sean Spalding |
Click me to see the sample solution
74. From the following table write a SQL query find the employee who resolve the highest number of cases in all quarters. Return employee name and total number of cases resolved.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| --------------+-----------+----+---+-------+-----+ emp_id |int |NO |PRI| | | emp_name |varchar(30)|YES | | | | emp_sex |varchar(1) |YES | | | | depart_name |varchar(25)|YES | | | | emp_salary |int |YES | | | | qtr1 |int |YES | | | | qtr2 |int |YES | | | | qtr3 |int |YES | | | | qtr4 |int |YES | | | | emp_department|int |YES | | | |
Data:
emp_id|emp_name |emp_sex|depart_name |emp_salary|qtr1|qtr2|qtr3|qtr4|emp_department| ------+-----------+-------+--------------+----------+----+----+----+----+--------------+ 100|Steven |M |Production | 24000| 240| 310| 275| 300| 90| 101|Neena |F |Production | 17000| 270| 300| 275| 290| 90| 102|Lex |M |Audit | 17000| 300| 290| 285| 310| 80| 103|Alexander |M |Marketing | 9000| 25| 270| 260| 280| 60| 104|Bruce |M |Marketing | 6000| 300| 280| 275| 290| 60| 105|David |M |Audit | 4800| 200| 220| 250| 270| 80| 106|Valli |F |Marketing | 4800| 300| 320| 330| 350| 60| 107|Diana |F |Marketing | 4200| 280| 270| 310| 320| 60| 108|Nancy |M |Administration| 12000| 260| 280| 300| 310| 100| 109|Daniel |F |Administration| 9000| 220| 210| 240| 260| 100| 110|John |M |Administration| 8200| 300| 290| 280| 320| 100| 111|Ismael |M |Administration| 7700| 280| 300| 270| 310| 100| 112|Jose Manuel|M |Administration| 7800| 250| 280| 260| 300| 100| 113|Luis |F |Administration| 6900| 300| 280| 270| 310| 100| 114|Den |M |Sales | 11000| 280| 290| 300| 320| 30| 115|Alexander |M |Sales | 3100| 310| 300| 320| 340| 30| 116|Shelli |F |Sales | 2900| 245| 260| 280| 300| 30| 117|Sigal |F |Sales | 2800| 250| 370| 290| 320| 30| 133|Jason |M |Export | 3300| 280| 270| 300| 290| 50| 134|Michael |F |Export | 2900| 260| 280| 290| 300| 50| 135|Ki |F |Export | 2400| 240| 260| 270| 290| 50|
Output:
emp_name |Target achieved in all Qtrs.| -----------+----------------------------+ Valli | 1300|Click me to see the sample solution
75. From the following tables write a query in SQL to find the department where the highest salaried employee(s) are working. Return department name and highest salary to this department.
Input:
Table: departments
Field |Type |Null|Key|Default|Extra| ---------------+-----------+----+---+-------+-----+ department_id |int |NO |PRI| | | department_name|varchar(30)|YES | | | |
Data:
department_id|department_name|
-------------+---------------+
30|Sales |
50|Export |
60|Marketing |
80|Audit |
90|Production |
100|Administration |
Table: employees
Field |Type |Null|Key|Default|Extra| --------------+-----------+----+---+-------+-----+ emp_id |int |NO |PRI| | | emp_name |varchar(30)|YES | | | | emp_sex |varchar(1) |YES | | | | emp_salary |int |YES | | | | emp_department|int |YES |MUL| | |
Data:
emp_id|emp_name |emp_sex|emp_salary|emp_department| ------+-----------+-------+----------+--------------+ 100|Steven |M | 24000| 90| 101|Neena |F | 17000| 90| 102|Lex |M | 17000| 80| 103|Alexander |M | 9000| 60| 104|Bruce |M | 6000| 60| 105|David |M | 4800| 80| 106|Valli |F | 4800| 60| 107|Diana |F | 4200| 60| 108|Nancy |M | 12000| 100| 109|Daniel |F | 9000| 100| 110|John |M | 8200| 100| 111|Ismael |M | 7700| 100| 112|Jose Manuel|M | 7800| 100| 113|Luis |F | 6900| 100| 114|Den |M | 11000| 30| 115|Alexander |M | 3100| 30| 116|Shelli |F | 2900| 30| 117|Sigal |F | 2800| 30| 133|Jason |M | 3300| 50| 134|Michael |F | 2900| 50| 135|Ki |F | 2400| 50|
Output:
department_name|emp_salary| ---------------+----------+ Production | 24000|Click me to see the sample solution
76. From the following table write a query in SQL to find the 2nd highest salary of employees.
Input:
Table: employees
Field |Type |Null|Key|Default|Extra| -------------+-------------+----+---+-------+-----+ employee_id |int |NO |PRI| | | emp_name |varchar(25) |YES | | | | hire_date |date |YES | | | | job_id |varchar(25) |YES | | | | salary |decimal(10,2)|YES | | | | manager_id |int |YES | | | | department_id|int |YES | | | |
Data:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
101|Neena |1987-06-18|AD_VP |17000.00| 100| 90|
102|Lex |1987-06-19|AD_VP |17000.00| 100| 90|
103|Alexander |1987-06-20|IT_PROG | 9000.00| 102| 60|
104|Bruce |1987-06-21|IT_PROG | 6000.00| 103| 60|
105|David |1987-06-22|IT_PROG | 4800.00| 103| 60|
106|Valli |1987-06-23|IT_PROG | 4800.00| 103| 60|
107|Diana |1987-06-24|IT_PROG | 4200.00| 103| 60|
108|Nancy |1987-06-25|FI_MGR |12000.00| 101| 100|
109|Daniel |1987-06-26|FI_ACCOUNT| 9000.00| 108| 100|
110|John |1987-06-27|FI_ACCOUNT| 8200.00| 108| 100|
111|Ismael |1987-06-28|FI_ACCOUNT| 7700.00| 108| 100|
112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00| 108| 100|
113|Luis |1987-06-30|FI_ACCOUNT| 6900.00| 108| 100|
114|Den |1987-07-01|PU_MAN |11000.00| 100| 30|
115|Alexander |1987-07-02|PU_CLERK | 3100.00| 114| 30|
116|Shelli |1987-07-03|PU_CLERK | 2900.00| 114| 30|
117|Sigal |1987-07-04|PU_CLERK | 2800.00| 114| 30|
133|Jason |1987-07-20|ST_CLERK | 3300.00| 122| 50|
134|Michael |1987-07-21|ST_CLERK | 2900.00| 122| 50|
135|Ki |1987-07-22|ST_CLERK | 2400.00| 122| 50|
136|Hazel |1987-07-23|ST_CLERK | 2200.00| 122| 50|
137|Renske |1987-07-24|ST_CLERK | 3600.00| 123| 50|
138|Stephen |1987-07-25|ST_CLERK | 3200.00| 123| 50|
139|John |1987-07-26|ST_CLERK | 2700.00| 123| 50|
Output:
employee_id|emp_name |hire_date |job_id |salary |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
100|Steven |1987-06-17|AD_PRES |24000.00| 0| 90|
Click me to see the sample solution
77. From the following table write a query in SQL to return the shipped and delivered rate for each order. Return order_id, shipped percentage, and delivered percentage.
Input:
Table: orderdetails
Field |Type |Null|Key|Default|Extra| ------------+-----------+----+---+-------+-----+ order_id |int |NO | | | | order_status|varchar(30)|YES | | | | order_date |date |YES | | | |
Data:
order_id|order_status|order_date| --------+------------+----------+ 10001|booked |2008-08-15| 10001|shipped |2008-08-16| 10002|booked |2008-07-13| 10002|delivered |2008-07-19| 10003|booked |2008-08-15| 10003|delivered |2008-08-18| 10004|booked |2008-08-19| 10004|shipped |2008-08-19|
Output:
order_id|shipped_perc|delivered_perc| --------+------------+--------------+ 10001| 1.00000| 0.00000| 10002| 0.00000| 1.00000| 10003| 0.00000| 1.00000| 10004| 1.00000| 0.00000|Click me to see the sample solution
SQL Code Editor:
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
Test your skills with w3resource's quiz
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/challenges-1/index.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics