w3resource

SQL Challenges-1: Exercises, Practice, Solution

SQL Challenges-1 [60 Challenges with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

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. Go to the editor

Input:

table: salespersons

salesperson_id|first_name|last_name|
--------------|----------|---------|
             1|Green     |Wright   |
             2|Jones     |Collins  |
             3|Bryant    |Davis    |

table: address

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

Output:

first_name|last_name|city       |state     |
----------|---------|-----------|----------|
Jones     |Collins  |Los Angeles|California|
Bryant    |Davis    |Denver     |Colorado  |
Green     |Wright   |           |          |
Click me to see the sample solution

2. From the following table, write a SQL query to find the third highest sale. Return sale amount. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

Input:

Table: sales

TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
           501|         18|    5200.00|
           502|         50|    5566.00|
           503|         38|    8400.00|
...
           599|         24|   16745.00|
           600|         12|   14900.00|

Table: salesman

SALESMAN_ID|SALESMAN_NAME        |
-----------|---------------------|
         11|Jonathan Goodwin     |
         12|Adam Hughes          |
         13|Mark Davenport       |
....
         59|Cleveland Hart       |
         60|Marion Gregory       |

Output:

salesman_name        |total_sale|
---------------------|----------|
Dan McKee            |  70530.00|
Cleveland Klein      |  61020.00|
Elliot Clapham       |  60519.00|
Evan Blake           |  53108.00|
Ollie Wheatley       |  52640.00|
Frederick Kelsey     |  52270.00|
Sean Mann            |  52053.00|
Callum Bing          |  48645.00|
Kian Wordsworth      |  45250.00|
Bradley Wright       |  41961.00|
Click me to see the sample solution

10. An active customer is simply someone who has bought company's product once before and has returned to make another purchase within 10 days.
From the following table, write a SQL query to identify the active customers. Show the list of customer IDs of active customers. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

Input:

Table: orders

ORDER_ID|CUSTOMER_ID|ITEM_DESC|
--------|-----------|---------|
     101|       2109|juice    |
     102|       2139|chocolate|
     103|       2120|juice    |
     104|       2108|cookies  |
     105|       2130|juice    |
     106|       2103|cake     |
     107|       2122|cookies  |
     108|       2125|cake     |
     109|       2139|cake     |
     110|       2141|cookies  |
     111|       2116|cake     |
     112|       2128|cake     |
     113|       2146|chocolate|
     114|       2119|cookies  |
     115|       2142|cake     |

Output:

item_desc|Number of orders|
---------|----------------|
cake     |               6|
Click me to see the sample solution

19. From the following tables, write a SQL query to find the overall rate of execution of orders, which is the number of orders execution divided by the number of orders quote. Return rate_of_execution rounded to 2 decimals places. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

Input:

Table: salemast

salesman_id|salesman_name|yearly_sale|
-----------|-------------|-----------|
        101|Adam         |     250000|
        103|Mark         |     100000|
        104|Liam         |     200000|
        102|Evan         |     150000|
        105|Blake        |     275000|
        106|Noah         |      50000|

Table : commision

salesman_id|commision_amt|
-----------|-------------|
        101|        10000|
        103|         4000|
        104|         8000|
        102|         6000|
        105|        11000|

Output:

salesman_name|commision_amt|
-------------|-------------|
Mark         |         4000|
Liam         |         8000|
Evan         |         6000|
Click me to see the sample solution

27. From the following table write a SQL query to find those distributors who purchased all types of item from the company. Return distributors ids. Go to the editor

Input:

Table: items

item_code|item_name|
---------|---------|
    10091|juice    |
    10092|chocolate|
    10093|cookies  |
    10094|cake     |

Table : orders

order_id|distributor_id|item_ordered|item_quantity|
--------|--------------|------------|-------------|
       1|           501|       10091|          250|
       2|           502|       10093|          100|
       3|           503|       10091|          200|
       4|           502|       10091|          150|
       5|           502|       10092|          300|
       6|           504|       10094|          200|
       7|           503|       10093|          250|
       8|           503|       10092|          250|
       9|           501|       10094|          180|
      10|           503|       10094|          350|

Output:

distributor_id|
--------------|
           503|
Click me to see the sample solution

28. From the following tables write a SQL query to find those directors and actors who worked together at least three or more movies. Return the director and actor name. Go to the editor

Input:

Table: actor_test

act_id|act_name         |
------|-----------------|
   101|James Stewart    |
   102|Deborah Kerr     |
   103|Peter OToole     |
   104|Robert De Niro   |
   105|F. Murray Abraham|
   106|Harrison Ford    |
   107|Bill Paxton      |
   108|Stephen Baldwin  |
   109|Jack Nicholson   |
   110|Mark Wahlberg    |

Table : director_test

dir_id|dir_name         |
------|-----------------|
   201|Alfred Hitchcock |
   202|Jack Clayton     |
   203|James Cameron    |
   204|Michael Cimino   |
   205|Milos Forman     |
   206|Ridley Scott     |
   207|Stanley Kubrick  |
   208|Bryan Singer     |
   209|Roman Polanski   |

Table: movie_test

mov_id|movie_name         |
------|-------------------|
   901|Vertigo            |
   902|Aliens             |
   903|Lawrence of Arabia |
   904|The Deer Hunter    |
   905|True Lies          |
   906|Blade Runner       |
   907|Eyes Wide Shut     |
   908|Titanic            |
   909|Chinatown          |
   910|Ghosts of the Abyss|

Table : mov_direction_test

dir_id|mov_id|act_id|
------|------|------|
   201|   901|   101|
   203|   902|   107|
   204|   904|   104|
   203|   905|   107|
   206|   906|   106|
   203|   908|   107|
   209|   909|   109|
   203|   910|   107|

Output:

dir_name     |act_name   |
-------------|-----------|
James Cameron|Bill Paxton|
Click me to see the sample solution

29. From the following tables write a SQL query to find those students who achieved 100 percent in various subjects in every year. Return examination ID, subject name, examination year, number of students. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

Input:

Table: managing_body

manager_id|manager_name|running_years|
----------|------------|-------------|
        51|James       |            5|
        52|Cork        |            3|
        53|Paul        |            4|
        54|Adam        |            3|
        55|Hense       |            4|
        56|Peter       |            2|

Table : scheme

scheme_code|scheme_manager_id|
-----------|-----------------|
       1001|               51|
       1001|               53|
       1001|               54|
       1001|               56|
       1002|               51|
       1002|               55|
       1003|               51|
       1004|               52|

Output:

scheme_code|Average year of experience|
-----------|--------------------------|
       1001|                      3.50|
       1002|                      4.50|
       1003|                      5.00|
       1004|                      3.00|
Click me to see the sample solution

33. From the following tables write a SQL query to find those schemes which executed by minimum number of employees. Return scheme code. Go to the editor

Input:

Table: managing_body

manager_id|manager_name|running_years|
----------|------------|-------------|
        51|James       |            5|
        52|Cork        |            3|
        53|Paul        |            4|
        54|Adam        |            3|
        55|Hense       |            4|
        56|Peter       |            2|

Table : scheme

scheme_code|scheme_manager_id|
-----------|-----------------|
       1001|               51|
       1001|               53|
       1001|               54|
       1001|               56|
       1002|               51|
       1002|               55|
       1003|               51|
       1004|               52|

Output:

scheme_code|
-----------|
       1003|
       1004|
Click me to see the sample solution

34. From the following tables write a SQL query to find those experienced manager who execute the schemes. Return scheme code and scheme manager ID. Go to the editor

Input:

Table: managing_body

manager_id|manager_name|running_years|
----------|------------|-------------|
        51|James       |            5|
        52|Cork        |            3|
        53|Paul        |            4|
        54|Adam        |            3|
        55|Hense       |            4|
        56|Peter       |            2|

Table : scheme

scheme_code|scheme_manager_id|
-----------|-----------------|
       1001|               51|
       1001|               53|
       1001|               54|
       1001|               56|
       1002|               51|
       1002|               55|
       1003|               51|
       1004|               52|

Output:

scheme_code|scheme_manager_id|
-----------|-----------------|
       1001|               51|
       1002|               51|
       1003|               51|
       1004|               52|
Click me to see the sample solution

35. From the following tables write an SQL query to find the best seller by total sales price. Return distributor ID , If there is a tie, report them all. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

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. Go to the editor

Input:

Table: sales

Field         |Type        |Null|Key|Default|Extra|
--------------|------------|----|---|-------|-----|
TRANSACTION_ID|int(5)      |NO  |PRI|       |     |
SALESMAN_ID   |int(4)      |NO  |   |       |     |
SALE_AMOUNT   |decimal(8,2)|YES |   |       |     |

Data:

TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
           501|         18|    5200.00|
           502|         50|    5566.00|
           503|         38|    8400.00|
           504|         43|    8400.00|
           505|         11|    9000.00|
           506|         42|    5900.00|
           507|         13|    7000.00|
           508|         33|    6000.00|
           509|         41|    8200.00|
           510|         11|    4500.00|
           511|         51|   10000.00|
           512|         29|    9500.00|
           513|         59|    6500.00|
           514|         38|    7800.00|
           515|         58|    9800.00|
           516|         60|   12000.00|
           517|         58|   13900.00|
           518|         23|   12200.00|
           519|         34|    5480.00|
           520|         35|    8129.00|
           521|         49|    9323.00|
           522|         46|    8200.00|
           523|         47|    9990.00|
           524|         42|   14000.00|
           525|         44|    7890.00|
           526|         47|    5990.00|
           527|         21|    7770.00|
           528|         57|    6645.00|
           529|         56|    5125.00|
           530|         25|   10990.00|
           531|         21|   12600.00|
           532|         41|    5514.00|
           533|         17|   15600.00|
           534|         44|   15000.00|
           535|         12|   17550.00|
           536|         55|   13000.00|
           537|         58|   16800.00|
           538|         25|   19900.00|
           539|         57|    9990.00|
           540|         28|    8900.00|
           541|         44|   10200.00|
           542|         57|   18000.00|
           543|         34|   16200.00|
           544|         36|   19998.00|
           545|         30|   13500.00|
           546|         37|   15520.00|
           547|         36|   20000.00|
           548|         20|   19800.00|
           549|         22|   18530.00|
           550|         19|   12523.00|
           551|         46|    9885.00|
           552|         22|    7100.00|
           553|         54|   17500.00|
           554|         19|   19600.00|
           555|         24|   17500.00|
           556|         38|    7926.00|
           557|         49|    7548.00|
           558|         15|    9778.00|
           559|         56|   19330.00|
           560|         24|   14400.00|
           561|         18|   16700.00|
           562|         54|    6420.00|
           563|         31|   18720.00|
           564|         21|   17220.00|
           565|         48|   18880.00|
           566|         33|    8882.00|
           567|         44|   19550.00|
           568|         22|   14440.00|
           569|         53|   19500.00|
           570|         30|    5300.00|
           571|         30|   10823.00|
           572|         35|   13300.00|
           573|         35|   19100.00|
           574|         18|   17525.00|
           575|         60|    8995.00|
           576|         53|    9990.00|
           577|         21|    7660.00|
           578|         27|   18990.00|
           579|         11|   18200.00|
           580|         30|   12338.00|
           581|         37|   11000.00|
           582|         27|   11980.00|
           583|         18|   12628.00|
           584|         52|   11265.00|
           585|         53|   19990.00|
           586|         27|    8125.00|
           587|         25|    7128.00|
           588|         57|    6760.00|
           589|         19|    5985.00|
           590|         52|   17641.00|
           591|         53|   11225.00|
           592|         22|   12200.00|
           593|         59|   16520.00|
           594|         35|   19990.00|
           595|         42|   19741.00|
           596|         19|   15000.00|
           597|         57|   19625.00|
           598|         53|    9825.00|
           599|         24|   16745.00|
           600|         12|   14900.00|

Table: salesman

Field        |Type       |Null|Key|Default|Extra|
-------------|-----------|----|---|-------|-----|
SALESMAN_ID  |int(4)     |NO  |PRI|       |     |
SALESMAN_NAME|varchar(30)|YES |   |       |     |

Data:

SALESMAN_ID|SALESMAN_NAME        |
-----------|---------------------|
         11|Jonathan Goodwin     |
         12|Adam Hughes          |
         13|Mark Davenport       |
         14|Jamie Shelley        |
         15|Ethan Birkenhead     |
         16|Liam Alton           |
         17|Josh Day             |
         18|Sean Mann            |
         19|Evan Blake           |
         20|Rhys Emsworth        |
         21|Kian Wordsworth      |
         22|Frederick Kelsey     |
         23|Noah Turner          |
         24|Callum Bing          |
         25|Harri Wilberforce    |
         26|Gabriel Gibson       |
         27|Richard York         |
         28|Tobias Stratford     |
         29|Will Kirby           |
         30|Bradley Wright       |
         31|Eli Willoughby       |
         32|Patrick Riley        |
         33|Kieran Freeman       |
         34|Toby Scott           |
         35|Elliot Clapham       |
         36|Lewis Moss           |
         37|Joshua Atterton      |
         38|Jonathan Reynolds    |
         39|David Hill           |
         40|Aidan Yeardley       |
         41|Dan Astley           |
         42|Finlay Dalton        |
         43|Toby Rodney          |
         44|Ollie Wheatley       |
         45|Sean Spalding        |
         46|Jason Wilson         |
         47|Christopher Wentworth|
         48|Cameron Ansley       |
         49|Henry Porter         |
         50|Ezra Winterbourne    |
         51|Rufus Fleming        |
         52|Wallace Dempsey      |
         53|Dan McKee            |
         54|Marion Caldwell      |
         55|Morris Phillips      |
         56|Chester Chandler     |
         57|Cleveland Klein      |
         58|Hubert Bean          |
         59|Cleveland Hart       |
         60|Marion Gregory       |

Output:

salesman_id|name                 |order_count|total_sale_amount|
-----------+---------------------+-----------+-----------------+
         57|Cleveland Klein      |          5|         61020.00|
         53|Dan McKee            |          5|         70530.00|
Click me to see the sample solution

55. From the following tables write a SQL query to find the minimum order amount sold by each salesperson. Return the salesperson ID, name and the minimum order amount they sold. Go to the editor

Input:

Table: sales

Field         |Type        |Null|Key|Default|Extra|
--------------|------------|----|---|-------|-----|
TRANSACTION_ID|int(5)      |NO  |PRI|       |     |
SALESMAN_ID   |int(4)      |NO  |   |       |     |
SALE_AMOUNT   |decimal(8,2)|YES |   |       |     |

Data:

TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------|-----------|-----------|
           501|         18|    5200.00|
           502|         50|    5566.00|
           503|         38|    8400.00|
           504|         43|    8400.00|
           505|         11|    9000.00|
           506|         42|    5900.00|
           507|         13|    7000.00|
           508|         33|    6000.00|
           509|         41|    8200.00|
           510|         11|    4500.00|
           511|         51|   10000.00|
           512|         29|    9500.00|
           513|         59|    6500.00|
           514|         38|    7800.00|
           515|         58|    9800.00|
           516|         60|   12000.00|
           517|         58|   13900.00|
           518|         23|   12200.00|
           519|         34|    5480.00|
           520|         35|    8129.00|
           521|         49|    9323.00|
           522|         46|    8200.00|
           523|         47|    9990.00|
           524|         42|   14000.00|
           525|         44|    7890.00|
           526|         47|    5990.00|
           527|         21|    7770.00|
           528|         57|    6645.00|
           529|         56|    5125.00|
           530|         25|   10990.00|
           531|         21|   12600.00|
           532|         41|    5514.00|
           533|         17|   15600.00|
           534|         44|   15000.00|
           535|         12|   17550.00|
           536|         55|   13000.00|
           537|         58|   16800.00|
           538|         25|   19900.00|
           539|         57|    9990.00|
           540|         28|    8900.00|
           541|         44|   10200.00|
           542|         57|   18000.00|
           543|         34|   16200.00|
           544|         36|   19998.00|
           545|         30|   13500.00|
           546|         37|   15520.00|
           547|         36|   20000.00|
           548|         20|   19800.00|
           549|         22|   18530.00|
           550|         19|   12523.00|
           551|         46|    9885.00|
           552|         22|    7100.00|
           553|         54|   17500.00|
           554|         19|   19600.00|
           555|         24|   17500.00|
           556|         38|    7926.00|
           557|         49|    7548.00|
           558|         15|    9778.00|
           559|         56|   19330.00|
           560|         24|   14400.00|
           561|         18|   16700.00|
           562|         54|    6420.00|
           563|         31|   18720.00|
           564|         21|   17220.00|
           565|         48|   18880.00|
           566|         33|    8882.00|
           567|         44|   19550.00|
           568|         22|   14440.00|
           569|         53|   19500.00|
           570|         30|    5300.00|
           571|         30|   10823.00|
           572|         35|   13300.00|
           573|         35|   19100.00|
           574|         18|   17525.00|
           575|         60|    8995.00|
           576|         53|    9990.00|
           577|         21|    7660.00|
           578|         27|   18990.00|
           579|         11|   18200.00|
           580|         30|   12338.00|
           581|         37|   11000.00|
           582|         27|   11980.00|
           583|         18|   12628.00|
           584|         52|   11265.00|
           585|         53|   19990.00|
           586|         27|    8125.00|
           587|         25|    7128.00|
           588|         57|    6760.00|
           589|         19|    5985.00|
           590|         52|   17641.00|
           591|         53|   11225.00|
           592|         22|   12200.00|
           593|         59|   16520.00|
           594|         35|   19990.00|
           595|         42|   19741.00|
           596|         19|   15000.00|
           597|         57|   19625.00|
           598|         53|    9825.00|
           599|         24|   16745.00|
           600|         12|   14900.00|

Table: salesman

Field        |Type       |Null|Key|Default|Extra|
-------------|-----------|----|---|-------|-----|
SALESMAN_ID  |int(4)     |NO  |PRI|       |     |
SALESMAN_NAME|varchar(30)|YES |   |       |     |

Data:

SALESMAN_ID|SALESMAN_NAME        |
-----------|---------------------|
         11|Jonathan Goodwin     |
         12|Adam Hughes          |
         13|Mark Davenport       |
         14|Jamie Shelley        |
         15|Ethan Birkenhead     |
         16|Liam Alton           |
         17|Josh Day             |
         18|Sean Mann            |
         19|Evan Blake           |
         20|Rhys Emsworth        |
         21|Kian Wordsworth      |
         22|Frederick Kelsey     |
         23|Noah Turner          |
         24|Callum Bing          |
         25|Harri Wilberforce    |
         26|Gabriel Gibson       |
         27|Richard York         |
         28|Tobias Stratford     |
         29|Will Kirby           |
         30|Bradley Wright       |
         31|Eli Willoughby       |
         32|Patrick Riley        |
         33|Kieran Freeman       |
         34|Toby Scott           |
         35|Elliot Clapham       |
         36|Lewis Moss           |
         37|Joshua Atterton      |
         38|Jonathan Reynolds    |
         39|David Hill           |
         40|Aidan Yeardley       |
         41|Dan Astley           |
         42|Finlay Dalton        |
         43|Toby Rodney          |
         44|Ollie Wheatley       |
         45|Sean Spalding        |
         46|Jason Wilson         |
         47|Christopher Wentworth|
         48|Cameron Ansley       |
         49|Henry Porter         |
         50|Ezra Winterbourne    |
         51|Rufus Fleming        |
         52|Wallace Dempsey      |
         53|Dan McKee            |
         54|Marion Caldwell      |
         55|Morris Phillips      |
         56|Chester Chandler     |
         57|Cleveland Klein      |
         58|Hubert Bean          |
         59|Cleveland Hart       |
         60|Marion Gregory       |

Output:

salesman_id|salesman_name        |minimum_sale_amount|
-----------+---------------------+-------------------+
         11|Jonathan Goodwin     |            4500.00|
         12|Adam Hughes          |           14900.00|
         13|Mark Davenport       |            7000.00|
         15|Ethan Birkenhead     |            9778.00|
         17|Josh Day             |           15600.00|
         18|Sean Mann            |            5200.00|
         19|Evan Blake           |            5985.00|
         20|Rhys Emsworth        |           19800.00|
         21|Kian Wordsworth      |            7660.00|
         22|Frederick Kelsey     |            7100.00|
         23|Noah Turner          |           12200.00|
         24|Callum Bing          |           14400.00|
         25|Harri Wilberforce    |            7128.00|
         27|Richard York         |            8125.00|
         28|Tobias Stratford     |            8900.00|
         29|Will Kirby           |            9500.00|
         30|Bradley Wright       |            5300.00|
         31|Eli Willoughby       |           18720.00|
         33|Kieran Freeman       |            6000.00|
         34|Toby Scott           |            5480.00|
         35|Elliot Clapham       |            8129.00|
         36|Lewis Moss           |           19998.00|
         37|Joshua Atterton      |           11000.00|
         38|Jonathan Reynolds    |            7800.00|
         41|Dan Astley           |            5514.00|
         42|Finlay Dalton        |            5900.00|
         43|Toby Rodney          |            8400.00|
         44|Ollie Wheatley       |            7890.00|
         46|Jason Wilson         |            8200.00|
         47|Christopher Wentworth|            5990.00|
         48|Cameron Ansley       |           18880.00|
         49|Henry Porter         |            7548.00|
         50|Ezra Winterbourne    |            5566.00|
         51|Rufus Fleming        |           10000.00|
         52|Wallace Dempsey      |           11265.00|
         53|Dan McKee            |            9825.00|
         54|Marion Caldwell      |            6420.00|
         55|Morris Phillips      |           13000.00|
         56|Chester Chandler     |            5125.00|
         57|Cleveland Klein      |            6645.00|
         58|Hubert Bean          |            9800.00|
         59|Cleveland Hart       |            6500.00|
         60|Marion Gregory       |            8995.00|
Click me to see the sample solution

56. From the following table write a SQL query to find the number of male and female employees in each department and along with their total salaries. Return department ID, number of female employees, their total salaries, number of male employees and their total salaries. Go to the editor

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 Administration 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. Go to the editor

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. Go to the editor

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. Go to the editor

Input:

Table: employees

Field        |Type         |Null|Key|Default|Extra|
-------------+-------------+----+---+-------+-----+
employee_id  |int          |NO  |PRI|       |     |
emp_name     |varchar(25)  |YES |   |       |     |
hire_date    |date         |YES |   |       |     |
job_id       |varchar(25)  |YES |   |       |     |
salary       |decimal(10,2)|YES |   |       |     |
manager_id   |int          |YES |   |       |     |
department_id|int          |YES |   |       |     |

Data:

employee_id|emp_name   |hire_date |job_id    |salary  |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
        100|Steven     |1987-06-17|AD_PRES   |24000.00|         0|           90|
        101|Neena      |1987-06-18|AD_VP     |17000.00|       100|           90|
        102|Lex        |1987-06-19|AD_VP     |17000.00|       100|           90|
        103|Alexander  |1987-06-20|IT_PROG   | 9000.00|       102|           60|
        104|Bruce      |1987-06-21|IT_PROG   | 6000.00|       103|           60|
        105|David      |1987-06-22|IT_PROG   | 4800.00|       103|           60|
        106|Valli      |1987-06-23|IT_PROG   | 4800.00|       103|           60|
        107|Diana      |1987-06-24|IT_PROG   | 4200.00|       103|           60|
        108|Nancy      |1987-06-25|FI_MGR    |12000.00|       101|          100|
        109|Daniel     |1987-06-26|FI_ACCOUNT| 9000.00|       108|          100|
        110|John       |1987-06-27|FI_ACCOUNT| 8200.00|       108|          100|
        111|Ismael     |1987-06-28|FI_ACCOUNT| 7700.00|       108|          100|
        112|Jose Manuel|1987-06-29|FI_ACCOUNT| 7800.00|       108|          100|
        113|Luis       |1987-06-30|FI_ACCOUNT| 6900.00|       108|          100|
        114|Den        |1987-07-01|PU_MAN    |11000.00|       100|           30|
        115|Alexander  |1987-07-02|PU_CLERK  | 3100.00|       114|           30|
        116|Shelli     |1987-07-03|PU_CLERK  | 2900.00|       114|           30|
        117|Sigal      |1987-07-04|PU_CLERK  | 2800.00|       114|           30|
        133|Jason      |1987-07-20|ST_CLERK  | 3300.00|       122|           50|
        134|Michael    |1987-07-21|ST_CLERK  | 2900.00|       122|           50|
        135|Ki         |1987-07-22|ST_CLERK  | 2400.00|       122|           50|
        136|Hazel      |1987-07-23|ST_CLERK  | 2200.00|       122|           50|
        137|Renske     |1987-07-24|ST_CLERK  | 3600.00|       123|           50|
        138|Stephen    |1987-07-25|ST_CLERK  | 3200.00|       123|           50|
        139|John       |1987-07-26|ST_CLERK  | 2700.00|       123|           50|

Output:

employee_id|emp_name   |
-----------+-----------+
        108|Nancy      |
Click me to see the sample solution

60. From the following table find customers who orders more than three times. Return customer ID and number of orders the customer booked. Go to the editor

Input:

Table: orders

Field      |Type       |Null|Key|Default|Extra|
-----------|-----------|----|---|-------|-----|
ORDER_ID   |int(5)     |NO  |PRI|       |     |
CUSTOMER_ID|int(4)     |NO  |   |       |     |
ITEM_DESC  |varchar(30)|NO  |   |       |     |
ORDER_DATE |date       |NO  |   |       |     |

Data:

ORDER_ID|CUSTOMER_ID|ITEM_DESC|ORDER_DATE|
--------|-----------|---------|----------|
     101|       2109|juice    |2020-03-03|
     102|       2139|chocolate|2019-03-18|
     103|       2120|juice    |2019-03-18|
     104|       2108|cookies  |2019-03-18|
     105|       2130|juice    |2020-03-28|
     106|       2103|cake     |2019-03-29|
     107|       2122|cookies  |2021-03-07|
     108|       2125|cake     |2021-03-13|
     109|       2139|cake     |2019-03-30|
     110|       2141|cookies  |2019-03-17|
     111|       2116|cake     |2020-03-31|
     112|       2128|cake     |2021-03-04|
     113|       2146|chocolate|2021-03-04|
     114|       2119|cookies  |2020-03-28|
     115|       2142|cake     |2019-03-09|
     116|       2122|cake     |2019-03-06|
     117|       2128|chocolate|2019-03-24|
     118|       2112|cookies  |2019-03-24|
     119|       2149|cookies  |2020-03-29|
     120|       2100|cookies  |2021-03-18|
     121|       2130|juice    |2021-03-16|
     122|       2103|juice    |2019-03-31|
     123|       2112|cookies  |2019-03-23|
     124|       2102|cake     |2020-03-25|
     125|       2120|chocolate|2020-03-21|
     126|       2109|cake     |2019-03-22|
     127|       2101|juice    |2021-03-01|
     128|       2138|juice    |2019-03-19|
     129|       2100|juice    |2019-03-29|
     130|       2129|juice    |2021-03-02|
     131|       2123|juice    |2020-03-31|
     132|       2104|chocolate|2020-03-31|
     133|       2110|cake     |2021-03-13|
     134|       2143|cake     |2019-03-27|
     135|       2130|juice    |2019-03-12|
     136|       2128|juice    |2020-03-28|
     137|       2133|cookies  |2019-03-21|
     138|       2150|cookies  |2019-03-20|
     139|       2120|juice    |2020-03-27|
     140|       2109|cake     |2021-03-02|
     141|       2110|cake     |2021-03-13|
     142|       2140|juice    |2019-03-09|
     143|       2112|cookies  |2021-03-04|
     144|       2117|chocolate|2019-03-19|
     145|       2137|cookies  |2020-03-23|
     146|       2130|cake     |2021-03-09|
     147|       2133|cake     |2020-03-08|
     148|       2143|juice    |2019-03-11|
     149|       2111|chocolate|2020-03-23|
     150|       2150|cookies  |2021-03-04|
     151|       2131|cake     |2020-03-10|
     152|       2140|chocolate|2019-03-17|
     153|       2147|cookies  |2020-03-22|
     154|       2119|chocolate|2019-03-15|
     155|       2116|juice    |2021-03-12|
     156|       2141|juice    |2021-03-14|
     157|       2143|cake     |2019-03-16|
     158|       2105|cake     |2020-03-21|
     159|       2149|chocolate|2019-03-11|
     160|       2117|cookies  |2020-03-22|
     161|       2150|cookies  |2020-03-21|
     162|       2134|cake     |2019-03-08|
     163|       2133|cookies  |2019-03-26|
     164|       2127|juice    |2019-03-27|
     165|       2101|juice    |2019-03-26|
     166|       2137|chocolate|2021-03-12|
     167|       2113|chocolate|2019-03-21|
     168|       2141|cake     |2019-03-21|
     169|       2112|chocolate|2021-03-14|
     170|       2118|juice    |2020-03-30|
     171|       2111|juice    |2019-03-19|
     172|       2146|chocolate|2021-03-13|
     173|       2148|cookies  |2021-03-14|
     174|       2100|cookies  |2021-03-13|
     175|       2105|cookies  |2019-03-05|
     176|       2129|juice    |2021-03-02|
     177|       2121|juice    |2019-03-16|
     178|       2117|cake     |2020-03-11|
     179|       2133|juice    |2020-03-12|
     180|       2124|cake     |2019-03-31|
     181|       2145|cake     |2021-03-07|
     182|       2105|cookies  |2019-03-09|
     183|       2131|juice    |2019-03-09|
     184|       2114|chocolate|2020-03-31|
     185|       2120|juice    |2021-03-06|
     186|       2130|juice    |2021-03-06|
     187|       2141|chocolate|2019-03-11|
     188|       2147|cake     |2021-03-14|
     189|       2118|juice    |2019-03-15|
     190|       2136|chocolate|2020-03-22|
     191|       2132|cake     |2021-03-06|
     192|       2137|chocolate|2019-03-31|
     193|       2107|cake     |2021-03-01|
     194|       2111|chocolate|2019-03-18|
     195|       2100|cake     |2019-03-07|
     196|       2106|juice    |2020-03-21|
     197|       2114|cookies  |2019-03-25|
     198|       2110|cake     |2019-03-27|
     199|       2130|juice    |2019-03-16|
     200|       2117|cake     |2021-03-10|

Output:

customer_id|customer_appears|
-----------+----------------+
       2120|               4|
       2130|               6|
       2141|               4|
       2112|               4|
       2100|               4|
       2133|               4|
       2117|               4|
Click me to see the sample solution

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

Delete all Duplicate Rows except for One in MySQL?

SELECT * FROM names;

+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+

1. If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

2. If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

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