# 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    |
```

```address_id|salesperson_id|city       |state     |country|
----------|--------------|-----------|----------|-------|
1|             2|Los Angeles|California|USA    |
3|             4|Atlanta    |Georgia   |USA    |

```

Output:

```first_name|last_name|city       |state     |
----------|---------|-----------|----------|
Jones     |Collins  |Los Angeles|California|
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     |
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|
```
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 salesperson that makes maximum number of sales amount.
If there are, more than one saleperson with maximum number of sales amount find all the salespersons. Return salesperson id.

Input:

Table Name: salemast

```salesperson_id|order_id|
--------------|--------|
5001|    1001|
5002|    1002|
5003|    1002|
5004|    1002|
5005|    1003|
5006|    1004|
```

Output:

``` order_id|
--------|
1002|
```
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    |
105|       2130|juice    |
106|       2103|cake     |
108|       2125|cake     |
109|       2139|cake     |
111|       2116|cake     |
112|       2128|cake     |
113|       2146|chocolate|
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|
-----------|-------------|-----------|
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|
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          |
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|
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|
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|
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	18000
```
Click 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				400
```
Click 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:

FieldTypeNullKeyDefaultExtra
order_idint(11)NOMUL
return_datedateYES

Data:

order_idreturn_date
1532020-10-12
1542020-11-07
1562020-12-05
1592020-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:

FieldTypeNullKeyDefaultExtra
sale_datedateYES
item_codeint(11)YES
sale_qtyint(11)YES

Data:

sale_dateitem_codesale_qty
2018-05-15101120
2018-04-2710380
2018-04-10102200
2018-07-12101100
2018-07-0710350
2018-09-17104100
2018-06-25102100

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     |
13|Mark Davenport       |
14|Jamie Shelley        |
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           |
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     |
13|Mark Davenport       |
14|Jamie Shelley        |
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           |
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|
13|Mark Davenport       |            7000.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|
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|
-----------+-----------+----------+----------+--------+----------+-------------+
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|
105|       2130|juice    |2020-03-28|
106|       2103|cake     |2019-03-29|
108|       2125|cake     |2021-03-13|
109|       2139|cake     |2019-03-30|
111|       2116|cake     |2020-03-31|
112|       2128|cake     |2021-03-04|
113|       2146|chocolate|2021-03-04|
115|       2142|cake     |2019-03-09|
116|       2122|cake     |2019-03-06|
117|       2128|chocolate|2019-03-24|
121|       2130|juice    |2021-03-16|
122|       2103|juice    |2019-03-31|
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|
139|       2120|juice    |2020-03-27|
140|       2109|cake     |2021-03-02|
141|       2110|cake     |2021-03-13|
142|       2140|juice    |2019-03-09|
144|       2117|chocolate|2019-03-19|
146|       2130|cake     |2021-03-09|
147|       2133|cake     |2020-03-08|
148|       2143|juice    |2019-03-11|
149|       2111|chocolate|2020-03-23|
151|       2131|cake     |2020-03-10|
152|       2140|chocolate|2019-03-17|
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|
162|       2134|cake     |2019-03-08|
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|
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|
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|
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|
-----------+-----------+----------+----------+--------+----------+-------------+
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|
105|       2130|juice     |2019-10-18|       45|
106|       2103|cake      |2019-07-21|       35|
108|       2125|cake      |2019-12-17|       38|
109|       2139|cake      |2019-07-21|       40|
111|       2116|cake      |2019-10-18|       45|
112|       2128|cake      |2019-10-18|       38|
113|       2146|chocolate |2019-10-18|       55|
115|       2142|cake      |2019-03-05|       26|
116|       2122|cake      |2019-03-05|       59|
117|       2128|chocolate |2019-06-19|       45|
121|       2130|juice     |2020-03-14|       20|
122|       2103|juice     |2019-07-21|       27|
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|
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|
144|       2117|chocolate |2019-06-19|       25|
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|
151|       2131|cake      |2019-07-21|       45|
152|       2140|chocolate |2019-05-17|       40|
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|
162|       2134|cake      |2019-03-05|       55|
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|
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|
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|
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|
-----------+-----------+----------+----------+--------+----------+-------------+
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|
----------+-----------+--------+--------------+
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|
-----------+-----------+----------+----------+--------+----------+-------------+
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  |
----------+-----------+--------+
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|
-----------+-----------+----------+----------+--------+----------+-------------+
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|
-----------+-----------+----------+----------+--------+----------+-------------+
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     |
13|Mark Davenport       |
14|Jamie Shelley        |
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           |
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     |
```

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|
-----------+-----------+----------+----------+--------+----------+-------------+
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|
-----------+-----------+----------+----------+--------+----------+-------------+
```
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

ï»¿

## SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM      Orders
WHERE     OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE   RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
```

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook