w3resource

SQL Challenges-1: Exercises, Practice, Solution

SQL Challenges-1 [77 Challenges with solution]

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

1. From the following tables, write a SQL query to find the information on each salesperson of ABC Company. Return name, city, country and state of each salesperson.

Input:

table: salespersons

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

table: address

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

Output:

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

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

Input:

table: salemast

sale_id|employee_id|sale_date |sale_amt|
-------|-----------|----------|--------|
      1|       1000|2012-03-08|    4500|
      2|       1001|2012-03-09|    5500|
      3|       1003|2012-04-10|    3500|
      3|       1003|2012-04-10|    2500|

Output:

SecondHighestSale|
-----------------|
             4500|
Click me to see the sample solution

3. From the following table, write a SQL query to find the Nth highest sale. Return sale amount.

Input:

table: salemast

sale_id|employee_id|sale_date |sale_amt|
-------|-----------|----------|--------|
      1|       1000|2012-03-08|    4500|
      2|       1001|2012-03-09|    5500|
      3|       1003|2012-04-10|    3500|

Output:

getNthHighestSaleAmt(3)|
-----------------------|
                   3500|
Click me to see the sample solution

4. From the following table, write a SQL query to find the marks, which appear at least thrice one after another without interruption. Return the number.

Input:

table: logs

student_id|marks|
----------|-----|
       101|   83|
       102|   79|
       103|   83|
       104|   83|
       105|   83|
       106|   79|
       107|   79|
       108|   83|

Output:

ConsecutiveNums|
---------------|
             83|
Click me to see the sample solution

5. From the following table, write a SQL query to find all the duplicate emails (no upper case letters) of the employees. Return email id.

Input:

table: employees

employee_id|employee_name|email_id     |
-----------|-------------|-------------|
        101|Liam Alton   |[email protected]|
        102|Josh Day     |[email protected]|
        103|Sean Mann    |[email protected]|
        104|Evan Blake   |[email protected]|
        105|Toby Scott   |[email protected]|

Output:

email_id     |
-------------|
[email protected]|
Click me to see the sample solution

6. From the following tables, write a SQL query to find those customers who never ordered anything. Return customer name.

Input:

table: customers

customer_id|customer_name|
-----------|-------------|
        101|Liam         |
        102|Josh         |
        103|Sean         |
        104|Evan         |
        105|Toby         |

table: orders

order_id|customer_id|order_date|order_amount|
--------|-----------|----------|------------|
     401|        103|2012-03-08|        4500|
     402|        101|2012-09-15|        3650|
     403|        102|2012-06-27|        4800|

Output:

Customers|
---------|
Evan     |
Toby     |
Click me to see the sample solution

7. From the following table, write a SQL query to remove all the duplicate emails of employees keeping the unique email with the lowest employee id. Return employee id and unique emails.

Input:

table: employees

employee_id|employee_name|email_id     |
-----------|-------------|-------------|
        101|Liam Alton   |[email protected]|
        102|Josh Day     |[email protected]|
        103|Sean Mann    |[email protected]|
        104|Evan Blake   |[email protected]|
        105|Toby Scott   |[email protected]|

Output:

employee_id|employee_name|email_id     |
-----------|-------------|-------------|
        101|Liam Alton   |[email protected]|
        102|Josh Day     |[email protected]|
        103|Sean Mann    |[email protected]|
        104|Evan Blake   |[email protected]|
Click me to see the sample solution

8. From the following table, write a SQL query to find all dates' city ID with higher pollution compared to its previous dates (yesterday). Return city ID, date and pollution.

Input:

table: so2_pollution

city_id|date      |so2_amt|
-------|----------|-------|
    701|2015-10-15|      5|
    702|2015-10-16|      7|
    703|2015-10-17|      9|
    704|2018-10-18|     15|
    705|2015-10-19|     14|

Output:

City ID|
-------|
    702|
    703|
Click me to see the sample solution

9. A salesperson is a person whose job is to sell products or services.
From the following tables, write a SQL query to find the top 10 salesperson that have made highest sale. Return their names and total sale amount.

Input:

Table: sales

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

Table: salesman

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

Output:

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

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

Input:

Table: orders

ORDER_ID|CUSTOMER_ID|ITEM_DESC|ORDER_DATE|
--------|-----------|---------|----------|
     101|       2109|juice    |2020-03-03|
     102|       2139|chocolate|2019-03-18|
     103|       2120|juice    |2019-03-18|
...
     199|       2130|juice    |2019-03-16|
     200|       2117|cake     |2021-03-10|

Output:

customer_id|
-----------|
       2103|
       2110|
       2111|
       2112|
       2129|
       2130|
Click me to see the sample solution

11. From the following table, write a SQL query to convert negative numbers to positive and vice verse. Return the number.

Input:

Table name: tablefortest

srno|pos_neg_val|
----|-----------|
   1|         56|
   2|        -74|
   3|         15|
   4|        -51|
   5|         -9|
   6|         32|

Output:

srno|pos_neg_val|converted_signed_value|
----|-----------|----------------------|
   1|         56|                   -56|
   2|        -74|                    74|
   3|         15|                   -15|
   4|        -51|                    51|
   5|         -9|                     9|
   6|         32|                   -32|
Click me to see the sample solution

12. From the following table, write a SQL query to find the century of a given date. Return the century.

Input:

Table name: tablefortest

ID|date_of_birth|
--|-------------|
 1|   1907-08-15|
 2|   1883-06-27|
 3|   1900-01-01|
 4|   1901-01-01|
 5|   2005-09-01|
 6|   1775-11-23|
 7|   1800-01-01|

Output:

id|date_of_birth|Century|
--|-------------|-------|
 1|   1907-08-15|   20  |
 2|   1883-06-27|   19  |
 3|   1900-01-01|   19  |
 4|   1901-01-01|   20  |
 5|   2005-09-01|   21  |
 6|   1775-11-23|   18  |
 7|   1800-01-01|   18  |
Click me to see the sample solution

13. From the following table, write a SQL query to find the even or odd values. Return "Even" for even number and "Odd" for odd number.

Input:

Table name: tablefortest

srno|col_val|
----|-------|
   1|     56|
   2|     74|
   3|     15|
   4|     51|
   5|      9|
   6|     32|

Output:

srno|col_val|Even_Odd|
----|-------|--------|
   1|     56|Even    |
   2|     74|Even    |
   3|     15|Odd     |
   4|     51|Odd     |
   5|      9|Odd     |
   6|     32|Even    |
Click me to see the sample solution

14. From the following table, write a SQL query to find the unique marks. Return the unique marks.

Input:

Table name: student_test

student_id|marks_achieved|
----------|--------------|
         1|            56|
         2|            74|
         3|            15|
         4|            74|
         5|            89|
         6|            56|
         7|            93|

Output:

Unique Marks|
------------|
          56|
          74|
          15|
          89|
          93|
Click me to see the sample solution

15. From the following table, write a SQL query to find those students who have referred by the teacher whose id not equal to 602. Return the student names.

Input:

Table Name: students

student_id|student_name|teacher_id|
----------|------------|----------|
      1001|Alex        |       601|
      1002|Jhon        |          |
      1003|Peter       |          |
      1004|Minto       |       604|
      1005|Crage       |          |
      1006|Chang       |       601|
      1007|Philip      |       602|

Output:

student_name|
------------|
Alex        |
Jhon        |
Peter       |
Minto       |
Crage       |
Chang       |
Click me to see the sample solution

16. From the following table, write a SQL query to find the order_id(s) that was executed by the maximum number of salespersons.
If there are, more than one order_id(s) executed by the maximum number of salespersons find all the order_id(s). Return order_id.

Input:

Table Name: salemast

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

Output:

 order_id|
--------|
    1002|
    1004|	
Click me to see the sample solution

17. A city is big if it has an area bigger than 50K square km or a population of more than 15 million.
From the following table, write a SQL query to find big cities name, population and area.

Input:

Table : cities_test

city_name    |country      |city_population|city_area|
-------------|-------------|---------------|---------|
Tokyo        |Japan        |       13515271|     2191|
Delhi        |India        |       16753235|     1484|
Shanghai     |China        |       24870895|     6341|
Sao Paulo    |Brazil       |       12252023|     1521|
Mexico City  |Mexico       |        9209944|     1485|
Cairo        |Egypt        |        9500000|     3085|
Mumbai       |India        |       12478447|      603|
Beijing      |China        |       21893095|    16411|
Osaka        |Japan        |        2725006|      225|
New York     |United States|        8398748|      786|
Buenos Aires |Argentina    |        3054300|      203|
Chongqing    |China        |       32054159|    82403|
Istanbul     |Turkey       |       15519267|     5196|
Kolkata      |India        |        4496694|      205|
Manila       |Philippines  |        1780148|       43|

Output:

city_name   |country |city_population|city_area|
------------|--------|---------------|---------|
Delhi       |India   |       16753235|     1484|
Shanghai    |China   |       24870895|     6341|
Beijing     |China   |       21893095|    16411|
Chongqing   |China   |       32054159|    82403|
Istanbul    |Turkey  |       15519267|     5196|
Click me to see the sample solution

18. From the following table, write a SQL query to find those items, which have ordered 5 or more times. Return item name and number of orders.

Input:

Table: orders

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

Output:

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

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

Input:

Table: orders_issued

distributor_id|company_id|quotation_date|
--------------|----------|--------------|
           101|       202|    2019-11-15|
           101|       203|    2019-11-15|
           101|       204|    2019-11-15|
           102|       202|    2019-11-16|
           102|       201|    2019-11-15|
           103|       203|    2019-11-17|
           103|       202|    2019-11-17|
           104|       203|    2019-11-18|
           104|       204|    2019-11-18|

Table: orders_executed

orders_from|executed_from|executed_date|
-----------|-------------|-------------|
        101|          202|   2019-11-17|
        101|          203|   2019-11-17|
        102|          202|   2019-11-17|
        103|          203|   2019-11-18|
        103|          202|   2019-11-19|
        104|          203|   2019-11-20|

Output:

rate_of_execution|
-----------------|
             0.67|
Click me to see the sample solution

20. From the following table write an SQL query to display the records with four or more rows with consecutive match_no's, and the crowd attended more than or equal to 50000 for each match. Return match_no, match_date and audience. Order the result by visit_date, descending.

Input:

table : match_crowd

match_no|match_date|audience|
--------|----------|--------|
       1|2016-06-11|   75113|
       2|2016-06-12|   62343|
       3|2016-06-13|   43035|
       4|2016-06-14|   55408|
       5|2016-06-15|   38742|
       6|2016-06-16|   63670|
       7|2016-06-17|   73648|
       8|2016-06-18|   52409|
       9|2016-06-19|   67291|
      10|2016-06-20|   49752|
      11|2016-06-21|   28840|
      12|2016-06-22|   32836|
      13|2016-06-23|   44268|

Output:

match_no|match_date|audience|
--------|----------|--------|
       6|2016-06-16|   63670|
       7|2016-06-17|   73648|
       8|2016-06-18|   52409|
       9|2016-06-19|   67291|
Click me to see the sample solution

21. From the following table write a SQL query to know the availability of the doctor for consecutive 2 or more days. Return visiting days.

Input:

Table: dr_clinic

visiting_date|availability|
-------------|------------|
   2016-06-11|           1|
   2016-06-12|           1|
   2016-06-13|           0|
   2016-06-14|           1|
   2016-06-15|           0|
   2016-06-16|           0|
   2016-06-17|           1|
   2016-06-18|           1|
   2016-06-19|           1|
   2016-06-20|           1|
   2016-06-21|           1|

Output:

visiting_date|
-------------|
   2016-06-11|
   2016-06-12|
   2016-06-17|
   2016-06-18|
   2016-06-19|
   2016-06-20|
   2016-06-21|   
Click me to see the sample solution

22. From the following tables find those customers who did not make any order to the supplier 'DCX LTD'. Return customers name.

Input:

Table: customers

customer_id|customer_name|customer_city|avg_profit|
-----------|-------------|-------------|----------|
        101|Liam         |New York     |     25000|
        102|Josh         |Atlanta      |     22000|
        103|Sean         |New York     |     27000|
        104|Evan         |Toronto      |     15000|
        105|Toby         |Dallas       |     20000|

Table : supplier

supplier_id|supplier_name|supplier_city|
-----------|-------------|-------------|
        501|ABC INC      |Dallas       |
        502|DCX LTD      |Atlanta      |
        503|PUC ENT      |New York     |
        504|JCR INC      |Toronto      |

Table: orders

order_id|customer_id|supplier_id|order_date|order_amount|
--------|-----------|-----------|----------|------------|
     401|        103|        501|2012-03-08|        4500|
     402|        101|        503|2012-09-15|        3650|
     403|        102|        503|2012-06-27|        4800|
     404|        104|        502|2012-06-17|        5600|
     405|        104|        504|2012-06-22|        6000|
     406|        105|        502|2012-06-25|        5600|

Output:

customer_name|
-------------|
Liam         |
Josh         |
Sean         |
----------------------

Click me to see the sample solution

23. Table students contain marks of mathematics for several students in a class. It may same marks for more than one student.
From the following table write a SQL table to find the highest unique marks a student achieved. Return the marks.

Table: students

student_id|student_name|marks_achieved|
----------|------------|--------------|
         1|Alex        |            87|
         2|Jhon        |            92|
         3|Pain        |            83|
         4|Danny       |            87|
         5|Paul        |            92|
         6|Rex         |            89|
         7|Philip      |            87|
         8|Josh        |            83|
         9|Evan        |            92|
        10|Larry       |            87|

Output:

marks|
-----|
   89|
Click me to see the sample solution

24. In a hostel, each room contains two beds. After every 6 months a student have to change their bed with his or her room-mate.
From the following tables write a SQL query to find the new beds of the students in the hostel. Return original_bed_id, student_name, bed_id and student_new.

Table : bed_info

bed_id|student_name|
------|------------|
   101|Alex        |
   102|Jhon        |
   103|Pain        |
   104|Danny       |
   105|Paul        |
   106|Rex         |
   107|Philip      |
   108|Josh        |
   109|Evan        |
   110|Green       |

Output:

original_bed_id|student_name|bed_id|student_new|
---------------|------------|------|-----------|
            102|Jhon        |   101|Jhon       |
            101|Alex        |   102|Alex       |
            104|Danny       |   103|Danny      |
            103|Pain        |   104|Pain       |
            106|Rex         |   105|Rex        |
            105|Paul        |   106|Paul       |
            108|Josh        |   107|Josh       |
            107|Philip      |   108|Philip     |
            110|Green       |   109|Green      |
            109|Evan        |   110|Evan       |
Click me to see the sample solution

25. From the following table, write a SQL query to find the first login date for each customer. Return customer id, login date.

Input:

Table: bank_trans

trans_id|customer_id|login_date|
--------|-----------|----------|
     101|       3002|2019-09-01|
     101|       3002|2019-08-01|
     102|       3003|2018-09-13|
     102|       3002|2018-07-24|
     103|       3001|2019-09-25|
     102|       3004|2017-09-05|

Output:

customer_id|first_login|
-----------|-----------|
       3001| 2019-09-25|
       3002| 2018-07-24|
       3003| 2018-09-13|
       3004| 2017-09-05|
Click me to see the sample solution

26. From the following table, write a SQL query to find those salespersons whose commission is less than ten thousand. Return salesperson name, commission.

Input:

Table: salemast

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

Table : commision

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

Output:

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

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

Input:

Table: items

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

Table : orders

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

Output:

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

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

Input:

Table: actor_test

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

Table : director_test

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

Table: movie_test

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

Table : mov_direction_test

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

Output:

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

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

Input:

Table: exam_test

exam_id|subject_id|exam_year|no_of_student|
-------|----------|---------|-------------|
     71|       201|     2017|         5146|
     71|       201|     2018|         3545|
     71|       202|     2018|         5945|
     71|       202|     2019|         2500|
     71|       203|     2017|         2500|
     72|       201|     2018|         3500|
     72|       202|     2017|         3651|
     73|       201|     2018|         2647|
     73|       201|     2019|         2647|
     73|       202|     2018|         4501|

Table : subject_test

subject_id|subject_name|
----------|------------|
       201|Mathematics |
       202|Physics     |
       203|Chemistry   |

Output:

exam_id|subject_name|exam_year|no_of_student|
-------|------------|---------|-------------|
     71|Chemistry   |     2017|         2500|
     71|Mathematics |     2017|         5146|
     71|Mathematics |     2018|         3545|
     71|Physics     |     2018|         5945|
     71|Physics     |     2019|         2500|
     72|Mathematics |     2018|         3500|
     72|Physics     |     2017|         3651|
     73|Mathematics |     2018|         2647|
     73|Mathematics |     2019|         2647|
     73|Physics     |     2018|         4501|
Click me to see the sample solution

30. From the following tables write a SQL query to find those students who achieved 100 percent marks in every subject for all the year. Return subject ID, subject name, students for all year.

Input:

Table: exam_test

exam_id|subject_id|exam_year|no_of_student|
-------|----------|---------|-------------|
     71|       201|     2017|         5146|
     71|       201|     2018|         3545|
     71|       202|     2018|         5945|
     71|       202|     2019|         2500|
     71|       203|     2017|         2500|
     72|       201|     2018|         3500|
     72|       202|     2017|         3651|
     73|       201|     2018|         2647|
     73|       201|     2019|         2647|
     73|       202|     2018|         4501|

Table : subject_test

subject_id|subject_name|
----------|------------|
       201|Mathematics |
       202|Physics     |
       203|Chemistry   |

Output:

subject_id|subject_name|Students for all year|
----------|------------|---------------------|
       201|Mathematics |                17485|
       202|Physics     |                16597|
       203|Chemistry   |                 2500|
Click me to see the sample solution

31. From the following tables write a SQL query that will generate a report which shows the total number of students achieved 100 percent for the first year of each examination of every subject.

Input:

Table: exam_test

exam_id|subject_id|exam_year|no_of_student|
-------|----------|---------|-------------|
     71|       201|     2017|         5146|
     71|       201|     2018|         3545|
     71|       202|     2017|         2701|
     71|       202|     2018|         5945|
     71|       202|     2019|         2500|
     71|       203|     2017|         2500|
     72|       201|     2018|         3500|
     72|       202|     2017|         3651|
     73|       201|     2017|         1000|
     73|       201|     2018|         2647|
     73|       201|     2019|         2647|
     73|       202|     2018|         4501|

Table : subject_test

subject_id|subject_name|
----------|------------|
       201|Mathematics |
       202|Physics     |
       203|Chemistry   |

Output:

exam_id|subject_name|first_year|no_of_student|
-------|------------|----------|-------------|
     71|Mathematics |      2017|         5146|
     71|Physics     |      2017|         2701|
     71|Chemistry   |      2017|         2500|
     72|Physics     |      2017|         3651|
     73|Mathematics |      2017|         1000|
Click me to see the sample solution

32. From the following tables write a SQL query to display those managers who have average experience for each scheme.

Input:

Table: managing_body

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

Table : scheme

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

Output:

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

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

Input:

Table: managing_body

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

Table : scheme

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

Output:

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

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

Input:

Table: managing_body

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

Table : scheme

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

Output:

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

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

Input:

Table: item

item_code|item_desc   |cost|
---------|------------|----|
      101|mother board|2700|
      102|RAM         | 800|
      103|key board   | 300|
      104|mouse       | 300|

Table : sales_info

distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
          5001|      101|       1001|  2020-02-12|       3|      8100|
          5001|      103|       1002|  2020-03-15|      15|      4500|
          5002|      101|       1001|  2019-06-24|       2|      5400|
          5001|      104|       1003|  2019-09-11|       8|      2400|
          5003|      101|       1003|  2020-10-21|       5|     13500|
          5003|      104|       1002|  2020-12-27|      10|      3000|
          5002|      102|       1001|  2019-05-18|      12|      9600|
          5002|      103|       1004|  2020-06-17|       8|      2400|
          5003|      103|       1001|  2020-04-12|       3|       900|  

Output:

distributor_id|
--------------|
          5002|
          5003|
Click me to see the sample solution

36. From the following table write a SQL query to find those retailers who have bought 'key board' but not 'mouse'. Return retailer ID.

Input:

Table: item

item_code|item_desc   |cost|
---------|------------|----|
      101|mother board|2700|
      102|RAM         | 800|
      103|key board   | 300|
      104|mouse       | 300|

Table : sales_info

distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
          5001|      101|       1001|  2020-02-12|       3|      8100|
          5001|      103|       1002|  2020-03-15|      15|      4500|
          5002|      101|       1001|  2019-06-24|       2|      5400|
          5001|      104|       1003|  2019-09-11|       8|      2400|
          5003|      101|       1003|  2020-10-21|       5|     13500|
          5003|      104|       1002|  2020-12-27|      10|      3000|
          5002|      102|       1001|  2019-05-18|      12|      9600|
          5002|      103|       1004|  2020-06-17|       8|      2400|
          5003|      103|       1001|  2020-04-12|       3|       900|  

Output:

retailer_id|
-----------|
       1001|
       1004|
Click me to see the sample solution

37. From the following table write a SQL query to display those items that were only sold in the 2nd quarter of a year, i.e. April 1st to June end for the year 2020. Return item code and item description.

Input:

Table: item

item_code|item_desc   |cost|
---------|------------|----|
      101|mother board|2700|
      102|RAM         | 800|
      103|key board   | 300|
      104|mouse       | 300|

Table : sales_info

distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
          5001|      101|       1001|  2020-02-12|       3|      8100|
          5001|      103|       1002|  2020-03-15|      15|      4500|
          5002|      101|       1001|  2019-06-24|       2|      5400|
          5001|      104|       1003|  2019-09-11|       8|      2400|
          5003|      101|       1003|  2020-10-21|       5|     13500|
          5003|      104|       1002|  2020-12-27|      10|      3000|
          5002|      102|       1001|  2019-05-18|      12|      9600|
          5002|      103|       1004|  2020-06-17|       8|      2400|
          5003|      103|       1001|  2020-04-12|       3|       900|  

Output:

item_code|item_desc   |
---------|------------|
      101|mother board|
      102|RAM         |
      103|key board   |
Click me to see the sample solution

38. From the following table write a SQL query to find the highest purchase with its corresponding item for each customer. In case of a same quantity purchase find the item code which is smallest.
The output must be sorted by increasing of customer_id. Return customer ID,lowest item code and purchase quantity.

Input:

Table: purchase

Field      |Type   |Null|Key|Default|Extra|
-----------|-------|----|---|-------|-----|
customer_id|int(11)|NO  |   |       |     |
item_code  |int(11)|NO  |   |       |     |
purch_qty  |int(11)|NO  |   |       |     |

Data:

customer_id|item_code|purch_qty|
-----------|---------|---------|
        101|      504|       25|
        101|      503|       50|
        102|      502|       40|
        102|      503|       25|
        102|      501|       45|
        103|      505|       30|
        103|      503|       25|
        104|      505|       40|
        101|      502|       25|
        102|      504|       40|
        102|      505|       50|
        103|      502|       25|
        104|      504|       40|
        103|      501|       35|

Output:

customer_id|lowest item code|purch_qty|
-----------|----------------|---------|
        101|             503|       50|
        102|             505|       50|
        103|             501|       35|
        104|             504|       40|
Click me to see the sample solution

39. From the following table write a SQL query to find all the writers who rated at least one of their own topic. Sorted the result in ascending order by writer id. Return writer ID.

Input:

Table: topics

Field         |Type   |Null|Key|Default|Extra|
--------------|-------|----|---|-------|-----|
topic_id      |int(11)|YES |   |       |     |
writer_id     |int(11)|YES |   |       |     |
rated_by      |int(11)|YES |   |       |     |
date_of_rating|date   |YES |   |       |     |

Data:

topic_id|writer_id|rated_by|date_of_rating|
-------|---------|--------|--------------|
  10001|      504|     507|    2020-07-17|
  10003|      502|     503|    2020-09-22|
  10001|      503|     507|    2020-02-07|
  10002|      501|     507|    2020-05-13|
  10002|      502|     502|    2020-04-10|
  10002|      504|     502|    2020-11-16|
  10003|      501|     502|    2020-10-05|
  10001|      507|     507|    2020-12-23|
  10004|      503|     501|    2020-08-28|
  10003|      505|     504|    2020-12-21|

Output:

Author rated on own topic|
-------------------------|
                      502|
                      507|
Click me to see the sample solution

40. From the following table write a SQL query to find all the writers who rated more than one topics on the same date, sorted in ascending order by their id. Return writr ID.

Input:

Table: topics

Field         |Type   |Null|Key|Default|Extra|
--------------|-------|----|---|-------|-----|
topic_id      |int(11)|YES |   |       |     |
writer_id     |int(11)|YES |   |       |     |
rated_by      |int(11)|YES |   |       |     |
date_of_rating|date   |YES |   |       |     |

Data:

topic_id|writer_id|rated_by|date_of_rating|
-------|---------|--------|--------------|
  10001|      504|     507|    2020-07-17|
  10003|      502|     503|    2020-09-22|
  10001|      503|     507|    2020-02-07|
  10002|      501|     507|    2020-05-13|
  10002|      502|     502|    2020-04-10|
  10002|      504|     502|    2020-11-16|
  10003|      501|     502|    2020-10-05|
  10001|      507|     507|    2020-12-23|
  10004|      503|     501|    2020-08-28|
  10003|      505|     504|    2020-12-21|

Output:

Topic rated by the writer|
-------------------------|
                      502|
                      507|
Click me to see the sample solution

41. From the following table write a SQL query to make a report such that there is a product id column and a sale quantity column for each quarter. Return product ID and sale quantity of each quarter.

Input:

Table: sale

Field     |Type       |Null|Key|Default|Extra|
----------|-----------|----|---|-------|-----|
product_id|int(11)    |NO  |PRI|       |     |
sale_qty  |int(11)    |YES |   |       |     |
qtr_no    |varchar(25)|NO  |PRI|       |     |

Data:

product_id|sale_qty|qtr_no|
----------|--------|------|
         1|   15000|qtr1  |
         1|   10000|qtr2  |
         2|   20000|qtr1  |
         2|   12000|qtr2  |
         3|   20000|qtr1  |
         3|   15000|qtr2  |
         3|   23000|qtr3  |
         3|   22000|qtr4  |
         4|   25000|qtr2  |
         4|   18000|qtr4  |

Output:

product_id	qtr1_sale	qtr2_sale	qtr3_sale	qtr4_sale
1	15000	10000	NULL	NULL
2	20000	12000	NULL	NULL
3	20000	15000	23000	22000
4	NULL	25000	NULL	18000
Click me to see the sample solution

42. From the following table write a SQL query to find for each month and company, the number of orders issued and their total quantity, the number of orders booked and their total order quantity. Return month, name of the company, number of orders issued, number of booked orders, total order quantity and total booked orders quantity.

Input:

Table: order_stat

Field    |Type       |Null|Key|Default|Extra|
---------|-----------|----|---|-------|-----|
order_id |int(11)    |NO  |PRI|       |     |
com_name |varchar(25)|YES |   |       |     |
ord_qty  |int(11)    |YES |   |       |     |
ord_stat |varchar(25)|YES |   |       |     |
stat_date|date       |YES |   |       |     |

Data:

order_id|com_name   |ord_qty|ord_stat |stat_date |
--------|-----------|-------|---------|----------|
     151|MMS INC    |    500|Booked   |2020-08-15|
     152|BCT LTD    |    300|Cancelled|2020-08-15|
     153|MMS INC    |    400|Cancelled|2020-08-26|
     154|XYZ COR    |    500|Booked   |2020-08-15|
     155|MMS INC    |    500|Cancelled|2020-10-11|
     156|BWD PRO LTD|    250|Cancelled|2020-11-15|
     157|BCT LTD    |    600|Booked   |2020-10-07|
     158|MMS INC    |    300|Booked   |2020-12-11|
     159|XYZ COR    |    300|Booked   |2020-08-26|
     160|BCT LTD    |    400|Booked   |2020-11-15|

Output:

month year	com_name	no_of_orders	booked_orders	total_order_qty	no_of_booked_qty
2020-08		MMS INC		2		1		900				500
2020-08		BCT LTD		1		0		300				0
2020-08		XYZ COR		2		2		800				800
2020-10		MMS INC		1		0		500				0
2020-11		BWD PRO LTD	1		0		250				0
2020-10		BCT LTD		1		1		600				600
2020-12		MMS INC		1		1		300				300
2020-11		BCT LTD		1		1		400				400
Click me to see the sample solution

43. From the following table write a SQL query to find for each month and company, the number of orders issued and their total quantity, the number of orders cancelled and their total quantity. Return month, name of the company, number of orders booked, number of booked quantity, number of cancelled order, and number of cancelled quantity.

Input:

Table: order_stat

Field    |Type       |Null|Key|Default|Extra|
---------|-----------|----|---|-------|-----|
order_id |int(11)    |NO  |PRI|       |     |
com_name |varchar(25)|YES |   |       |     |
ord_qty  |int(11)    |YES |   |       |     |
ord_stat |varchar(25)|YES |   |       |     |
stat_date|date       |YES |   |       |     |

Data:

order_id|com_name   |ord_qty|ord_stat |stat_date |
--------|-----------|-------|---------|----------|
     151|MMS INC    |    500|Booked   |2020-08-15|
     152|BCT LTD    |    300|Cancelled|2020-08-15|
     153|MMS INC    |    400|Cancelled|2020-08-26|
     154|XYZ COR    |    500|Booked   |2020-08-15|
     155|MMS INC    |    500|Cancelled|2020-10-11|
     156|BWD PRO LTD|    250|Cancelled|2020-11-15|
     157|BCT LTD    |    600|Booked   |2020-10-07|
     158|MMS INC    |    300|Booked   |2020-12-11|
     159|XYZ COR    |    300|Booked   |2020-08-26|
     160|BCT LTD    |    400|Booked   |2020-11-15|

Table: order_return

Structure:

FieldTypeNullKeyDefaultExtra
order_idint(11)NOMUL
return_datedateYES

Data:

order_idreturn_date
1532020-10-12
1542020-11-07
1562020-12-05
1592020-09-17

Output:

month  |com_name   |booked_count|booked_qty|cancelled_count|cancelled_qty|
-------|-----------|------------|----------|---------------|-------------|
2020-08|BCT LTD    |           0|       0.0|              1|        300.0|
2020-08|MMS INC    |           1|     500.0|              1|        400.0|
2020-08|XYZ COR    |           2|     800.0|              0|          0.0|
2020-10|BCT LTD    |           1|     600.0|              0|          0.0|
2020-10|MMS INC    |           0|       0.0|              1|        500.0|
2020-11|BCT LTD    |           1|     400.0|              0|          0.0|
2020-11|BWD PRO LTD|           0|       0.0|              1|        250.0|
2020-12|MMS INC    |           1|     300.0|              0|          0.0|
Click me to see the sample solution

44. From the following tables write a SQL query to find the average selling price for each item. Return item code and average_selling_price. average_selling_price should be rounded to 2 decimal places.

Input:

Table: item_price

Field    |Type   |Null|Key|Default|Extra|
---------|-------|----|---|-------|-----|
item_code|int(11)|YES |   |       |     |
date_from|date   |YES |   |       |     |
date_to  |date   |YES |   |       |     |
item_cost|int(11)|YES |   |       |     |

Data:

item_code|date_from |date_to   |item_cost|
---------|----------|----------|---------|
      101|2018-04-07|2018-06-28|        8|
      102|2018-02-15|2018-04-17|       13|
      103|2018-03-12|2018-04-30|       10|
      101|2018-06-29|2018-10-31|       15|
      103|2018-05-01|2019-08-24|       14|
      102|2018-04-18|2018-07-10|       25|
      104|2018-06-11|2018-10-10|       25|
      101|2018-11-01|2019-01-15|       20|

Table: sale

Structure:

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

Data:

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

Output:

item_code|average_selling_price|
---------|---------------------|
      101|                11.18|
      102|                17.00|
      103|                11.54|
      104|                25.00|
Click me to see the sample solution

45. From the following table write a SQL query to find all employees that directly or indirectly report to the head of the company. Return employee_id, name, and manager_id.

Input:

Table: emp_test_table

Field      |Type       |Null|Key|Default|Extra|
-----------|-----------|----|---|-------|-----|
employee_id|int(11)    |NO  |PRI|       |     |
first_name |varchar(25)|YES |   |       |     |
manager_id |int(11)    |YES |   |       |     |

Data:

employee_id|first_name |manager_id|
-----------|-----------|----------|
        100|Steven     |       100|
        101|Neena      |       100|
        102|Lex        |       100|
        103|Alexander  |       102|
        104|Bruce      |       103|
        105|David      |       103|
        106|Valli      |       103|
        107|Diana      |       103|
        108|Nancy      |       101|
        109|Daniel     |       108|
        110|John       |       108|

Output:

employee_id|Name       |manager_id|
-----------|-----------|----------|
        101|Neena      |       100|
        102|Lex        |       100|
        103|Alexander  |       102|
        104|Bruce      |       103|
        105|David      |       103|
        106|Valli      |       103|
        107|Diana      |       103|
        108|Nancy      |       101|
        109|Daniel     |       108|
        110|John       |       108|
Click me to see the sample solution

46. From the following tables write a SQL query to find the number of times each patient call the specialist doctor since their treating period. Order the result table by patient_id and specialist_call.

Input:

Table: patient

Field       |Type       |Null|Key|Default|Extra|
------------|-----------|----|---|-------|-----|
patient_id  |int(11)    |NO  |PRI|       |     |
patient_name|varchar(25)|YES |   |       |     |

Data:

patient_id|patient_name   |
----------|---------------|
      1001|Gilbart Kane   |
      1002|Thomas Richi   |
      1003|Ricardo Grance |
      1004|Vanio Tishuma  |
      1005|Charls Brown   |

Table: speciality

Field     |Type       |Null|Key|Default|Extra|
----------|-----------|----|---|-------|-----|
specialist|varchar(25)|YES |   |       |     |

Data:

specialist |
-----------|
medicine   |
cardiology |
neurology  |
hematology |

Table: treatment

Field          |Type       |Null|Key|Default|Extra|
---------------|-----------|----|---|-------|-----|
patient_id     |int(11)    |YES |MUL|       |     |
specialist_call|varchar(25)|YES |   |       |     |

Data:

patient_id|specialist_call|
----------|---------------|
      1001|medicine       |
      1003|medicine       |
      1002|cardiology     |
      1001|hematology     |
      1004|medicine       |
      1003|cardiology     |
      1005|neurology      |
      1002|neurology      |
      1001|cardiology     |
      1005|cardiology     |
      1003|cardiology     |
      1005|hematology     |
      1004|hematology     |
      1005|neurology      |
      1002|neurology      |
      1001|hematology     |

Output:

patient_id|patient_name   |specialist |Specialist Attended|
----------|---------------|-----------|-------------------|
      1001|Gilbart Kane   |cardiology |                  1|
      1001|Gilbart Kane   |hematology |                  2|
      1001|Gilbart Kane   |medicine   |                  1|
      1001|Gilbart Kane   |neurology  |                  0|
      1002|Thomas Richi   |cardiology |                  1|
      1002|Thomas Richi   |hematology |                  0|
      1002|Thomas Richi   |medicine   |                  0|
      1002|Thomas Richi   |neurology  |                  2|
      1003|Ricardo Grance |cardiology |                  2|
      1003|Ricardo Grance |hematology |                  0|
      1003|Ricardo Grance |medicine   |                  1|
      1003|Ricardo Grance |neurology  |                  0|
      1004|Vanio Tishuma  |cardiology |                  0|
      1004|Vanio Tishuma  |hematology |                  1|
      1004|Vanio Tishuma  |medicine   |                  1|
      1004|Vanio Tishuma  |neurology  |                  0|
      1005|Charls Brown   |cardiology |                  1|
      1005|Charls Brown   |hematology |                  1|
      1005|Charls Brown   |medicine   |                  0|
      1005|Charls Brown   |neurology  |                  2|
Click me to see the sample solution

47. From the following table write a SQL query to find the number of employees are working in the department of each employees. Return employee Id and number of employees are working in their department.

Input:

Table: emp_test_table

Field      |Type       |Null|Key|Default|Extra|
-----------|-----------|----|---|-------|-----|
employee_id|int(11)    |NO  |PRI|       |     |
first_name |varchar(25)|YES |   |       |     |
manager_id |int(11)    |YES |   |       |     |

Data:

employee_id|first_name |department_id|
-----------|-----------|-------------|
        100|Steven     |           90|
        101|Neena      |           90|
        102|Lex        |           90|
        103|Alexander  |           60|
        104|Bruce      |           60|
        105|David      |           60|
        106|Valli      |           60|
        107|Diana      |           60|
        108|Nancy      |          100|
        109|Daniel     |          100|
        110|John       |          100|

Output:

employee_id|employees_in_department|
-----------|-----------------------|
        100|                      3|
        101|                      3|
        102|                      3|
        103|                      5|
        104|                      5|
        105|                      5|
        106|                      5|
        107|                      5|
        108|                      3|
        109|                      3|
        110|                      3|
Click me to see the sample solution

48. From the following table write a SQL query to find the total sale quantity of items of each unit type at each day. Return unit type, date and total sale quantity at each day. Order the result table by gender and day.

Input:

Table: sale

Field       |Type       |Null|Key|Default|Extra|
------------|-----------|----|---|-------|-----|
product_name|varchar(25)|YES |   |       |     |
unit_type   |varchar(5) |YES |   |       |     |
sale_date   |date       |YES |   |       |     |
sale_qty    |int(11)    |YES |   |       |     |

Data:

product_name   |unit_type|sale_date |sale_qty|
---------------|---------|----------|--------|
Munchos        |P        |2018-05-15|      20|
Boyer Chocolate|P        |2018-04-27|      30|
CocaCola       |L        |2018-04-10|      25|
Fruit Cakes    |P        |2018-07-12|      30|
CocaCola       |L        |2018-07-07|      50|
Fanta          |L        |2018-01-27|      70|
Chex Mix       |P        |2018-09-17|      40|
Jaffa Cakes    |P        |2018-06-25|      40|
Pom-Bear       |P        |2018-02-11|      30|
Twix Chocolate |P        |2018-12-24|      50|
Limca          |L        |2018-03-15|      50|
Mirinda        |L        |2018-02-05|      40|

Output:

unit_type|sale_date |running unit|
---------|----------|------------|
L        |2018-01-27|          70|
L        |2018-02-05|         110|
L        |2018-03-15|         160|
L        |2018-04-10|         185|
L        |2018-07-07|         235|
P        |2018-02-11|          30|
P        |2018-04-27|          60|
P        |2018-05-15|          80|
P        |2018-06-25|         120|
P        |2018-07-12|         150|
P        |2018-09-17|         190|
P        |2018-12-24|         240|
Click me to see the sample solution

49. From the following tables write a SQL query to get the description of items with 50 or more quantities sold out within January and February of 2020. Return item description and sale quantity.

Input:

Table: item

Field    |Type        |Null|Key|Default|Extra|
---------|------------|----|---|-------|-----|
item_code|int(11)     |NO  |PRI|       |     |
item_desc|varchar(255)|YES |   |       |     |
cost     |int(11)     |YES |   |       |     |

Data:

item_code|item_desc   |cost|
---------|------------|----|
      101|mother board|2700|
      102|RAM         | 800|
      103|key board   | 300|
      104|mouse       | 300|

Table: sales_info

Field         |Type   |Null|Key|Default|Extra|
--------------|-------|----|---|-------|-----|
distributor_id|int(11)|YES |   |       |     |
item_code     |int(11)|YES |   |       |     |
retailer_id   |int(11)|YES |   |       |     |
date_of_sell  |date   |YES |   |       |     |
quantity      |int(11)|YES |   |       |     |
total_cost    |int(11)|YES |   |       |     |

Data:

distributor_id|item_code|retailer_id|date_of_sell|quantity|total_cost|
--------------|---------|-----------|------------|--------|----------|
          5001|      101|       1001|  2020-01-12|      30|      8100|
          5001|      103|       1002|  2020-01-15|      25|      4500|
          5002|      101|       1001|  2019-01-30|      25|      5400|
          5001|      104|       1003|  2019-02-17|      75|      2400|
          5003|      101|       1003|  2020-03-07|      55|     13500|
          5003|      104|       1002|  2020-05-27|     100|      3000|
          5002|      102|       1001|  2020-05-18|      65|      9600|
          5002|      103|       1004|  2020-01-30|      45|      2400|
          5003|      103|       1001|  2020-03-12|      30|       900|

Output:

item_desc   |sale_quantity|
------------|-------------|
key board   |           70|
mother board|           55|
mouse       |           75|
Click me to see the sample solution

50. From the following table write a SQL query to find the order id and the item name for all companies who are not registered with the distributor. Return the result table in any order.

Input:

Table: company_info

Field       |Type       |Null|Key|Default|Extra|
------------|-----------|----|---|-------|-----|
company_id  |int(11)    |NO  |PRI|       |     |
company_name|varchar(25)|YES |   |       |     |

Data:

company_id|company_name|
----------|------------|
      5001|Intel       |
      5002|Kingston    |
      5003|Dell        |
      5004|Sony        |
      5005|Iball       |
      5006|Canon       |

Table: orders

Field     |Type       |Null|Key|Default|Extra|
----------|-----------|----|---|-------|-----|
order_id  |int(11)    |YES |   |       |     |
item_name |varchar(25)|YES |   |       |     |
company_id|int(11)    |YES |   |       |     |

Data:

order_id|item_name    |company_id|
--------|-------------|----------|
     101|mother board |      5001|
     102|RAM          |      5002|
     103|printer      |      5006|
     104|keyboard     |      5005|
     105|mouse        |      6051|
     106|speaker      |      6009|
     107|web cam      |      5005|
     108|hard disk    |      5002|
     109|monitor      |      5003|
     110|scanner      |      7023|

Output:

order_id|item_name|
--------|---------|
     105|mouse    |
     106|speaker  |
     110|scanner  |
Click me to see the sample solution

51. From the following table write a SQL query to find the ID for the salesman who makes largest number of transactions.

Input:

Table: sales

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

Data:

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

Output:

salesman_id|make_transaction|
-----------+----------------+
         57|               5|
         53|               5|
Click me to see the sample solution

52. From the following tables write a query in SQL to compare the purchasing status of the average purchase quantity of products of a category to the average pruchase quantity of the distributor. Return purchase month, category_id and purchase status.

Input:

Table: product

Field      |Type|Null|Key|Default|Extra|
-----------+----+----+---+-------+-----+
product_id |int |NO  |PRI|       |     |
category_id|int |NO  |   |       |     |

Data:

product_id|category_id|
----------+-----------+
      8001|        150|
      8002|        160|
      8003|        160|
      8004|        150|
      8005|        160|

Table: purchase

Field        |Type|Null|Key|Default|Extra|
-------------+----+----+---+-------+-----+
purchase_no  |int |NO  |PRI|       |     |
item_code    |int |NO  |MUL|       |     |
purchase_qty |int |YES |   |       |     |
purchase_date|date|YES |   |       |     |

Data:

purchase_no|item_code|purchase_qty|purchase_date|
-----------+---------+------------+-------------+
       1001|     8001|         240|   2019-12-17|
       1002|     8002|         150|   2019-12-17|
       1003|     8003|         175|   2020-11-15|
       1004|     8004|         150|   2019-12-17|
       1005|     8005|         145|   2019-12-05|
       1006|     8001|         150|   2020-01-05|
       1007|     8002|         200|   2020-01-15|
       1008|     8003|         150|   2020-12-17|
       1009|     8001|         200|   2020-01-28|
       1010|     8002|         180|   2020-02-07|
       1011|     8001|         300|   2020-02-25|
       1012|     8005|         100|   2020-01-27|

Output:

purchase_month|category_id|purchase_status|
--------------+-----------+---------------+
2019-12       |        150|increase       |
2020-01       |        150|increase       |
2020-02       |        150|increase       |
2019-12       |        160|decrease       |
2020-01       |        160|decrease       |
2020-02       |        160|decrease       |
2020-11       |        160|remain same    |
2020-12       |        160|remain same    |
Click me to see the sample solution

53. From the following table write a SQL query to identifies the highest difference in total sale of all quarters on a product of many companies. Return highest and lowest total sale and highest difference.

Input:

Table: sales

Field     |Type|Null|Key|Default|Extra|
----------+----+----+---+-------+-----+
company_id|int |NO  |PRI|       |     |
qtr1_sale |int |YES |   |       |     |
qtr2_sale |int |YES |   |       |     |
qtr3_sale |int |YES |   |       |     |
qtr4_sale |int |YES |   |       |     |

Data:

company_id|qtr1_sale|qtr2_sale|qtr3_sale|qtr4_sale|
----------+---------+---------+---------+---------+
      1001|      240|      310|      330|      400|
      1002|      310|      250|      320|      280|
      1003|      370|      420|      400|      450|
      1004|      400|      340|      320|      350|
      1005|      270|      350|      340|      360|
      1006|      160|      200|      220|      200|
      1007|      340|      350|      370|      400|
      1008|      250|      280|      300|      350|
      1009|      350|      300|      280|      350|
      1010|      230|      260|      280|      300|

Output:

max_sale|min_sale|sale_difference|
--------+--------+---------------+
    1640|     780|            860|
Click me to see the sample solution

54. A 'Smart' salesperson executed at least 5 transactions with a total sale amount is more than Rs.30000. From the following tables write a SQL query to find the smart salespersons. Return salesperson ID, name, number of transactions executed and the total amount of executed transactions.

Input:

Table: sales

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

Data:

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

Table: salesman

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

Data:

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

Output:

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

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

Input:

Table: sales

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

Data:

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

Table: salesman

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

Data:

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

Output:

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

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

Input:

Table: employees

Field         |Type       |Null|Key|Default|Extra|
--------------+-----------+----+---+-------+-----+
emp_id        |int        |NO  |PRI|       |     |
emp_name      |varchar(30)|YES |   |       |     |
emp_sex       |varchar(1) |YES |   |       |     |
emp_salary    |int        |YES |   |       |     |
emp_department|int        |YES |   |       |     |

Data:

emp_id|emp_name   |emp_sex|emp_salary|emp_department|
------+-----------+-------+----------+--------------+
   100|Steven     |M      |     24000|            90|
   101|Neena      |F      |     17000|            90|
   102|Lex        |M      |     17000|            80|
   103|Alexander  |M      |      9000|            60|
   104|Bruce      |M      |      6000|            60|
   105|David      |M      |      4800|            80|
   106|Valli      |F      |      4800|            60|
   107|Diana      |F      |      4200|            60|
   108|Nancy      |M      |     12000|           100|
   109|Daniel     |F      |      9000|           100|
   110|John       |M      |      8200|           100|
   111|Ismael     |M      |      7700|           100|
   112|Jose Manuel|M      |      7800|           100|
   113|Luis       |F      |      6900|           100|
   114|Den        |M      |     11000|            30|
   115|Alexander  |M      |      3100|            30|
   116|Shelli     |F      |      2900|            30|
   117|Sigal      |F      |      2800|            30|
   133|Jason      |M      |      3300|            50|
   134|Michael    |F      |      2900|            50|
   135|Ki         |F      |      2400|            50|

Output:

department|female_employees|female_total_salary|male_employees|male_total_salary|
----------+----------------+-------------------+--------------+-----------------+
        90|               1|              17000|             1|            24000|
        80|                |                   |             2|            21800|
        60|               2|               9000|             2|            15000|
       100|               2|              15900|             4|            35700|
        30|               2|               5700|             2|            14100|
        50|               2|               5300|             1|             3300|
Click me to see the sample solution

57. From the following table write a query in SQL find the employees in department Sales who solved the cases for all quarters are more than 1200. Return the name of the employees and total cases solved for all quarter. Result should be in ascending order or employee name.

Input:

Table: employees

Field         |Type       |Null|Key|Default|Extra|
--------------+-----------+----+---+-------+-----+
emp_id        |int        |NO  |PRI|       |     |
emp_name      |varchar(30)|YES |   |       |     |
emp_sex       |varchar(1) |YES |   |       |     |
depart_name   |varchar(25)|YES |   |       |     |
emp_salary    |int        |YES |   |       |     |
qtr1          |int        |YES |   |       |     |
qtr2          |int        |YES |   |       |     |
qtr3          |int        |YES |   |       |     |
qtr4          |int        |YES |   |       |     |
emp_department|int        |YES |   |       |     |

Data:

emp_id|emp_name   |emp_sex|depart_name   |emp_salary|qtr1|qtr2|qtr3|qtr4|emp_department|
------+-----------+-------+--------------+----------+----+----+----+----+--------------+
   100|Steven     |M      |Production    |     24000| 240| 310| 275| 300|            90|
   101|Neena      |F      |Production    |     17000| 270| 300| 275| 290|            90|
   102|Lex        |M      |Audit         |     17000| 300| 290| 285| 310|            80|
   103|Alexander  |M      |Marketing     |      9000|  25| 270| 260| 280|            60|
   104|Bruce      |M      |Marketing     |      6000| 300| 280| 275| 290|            60|
   105|David      |M      |Audit         |      4800| 200| 220| 250| 270|            80|
   106|Valli      |F      |Marketing     |      4800| 300| 320| 330| 350|            60|
   107|Diana      |F      |Marketing     |      4200| 280| 270| 310| 320|            60|
   108|Nancy      |M      |Administration|     12000| 260| 280| 300| 310|           100|
   109|Daniel     |F      |Administration|      9000| 220| 210| 240| 260|           100|
   110|John       |M      |Administration|      8200| 300| 290| 280| 320|           100|
   111|Ismael     |M      |Administration|      7700| 280| 300| 270| 310|           100|
   112|Jose Manuel|M      |Administration|      7800| 250| 280| 260| 300|           100|
   113|Luis       |F      |Administration|      6900| 300| 280| 270| 310|           100|
   114|Den        |M      |Sales         |     11000| 280| 290| 300| 320|            30|
   115|Alexander  |M      |Sales         |      3100| 310| 300| 320| 340|            30|
   116|Shelli     |F      |Sales         |      2900| 245| 260| 280| 300|            30|
   117|Sigal      |F      |Sales         |      2800| 250| 370| 290| 320|            30|
   133|Jason      |M      |Export        |      3300| 280| 270| 300| 290|            50|
   134|Michael    |F      |Export        |      2900| 260| 280| 290| 300|            50|
   135|Ki         |F      |Export        |      2400| 240| 260| 270| 290|            50|

Output:

emp_name   |total_cases|
-----------+-----------+
Alexander  |       1270|
Sigal      |       1230|
Click me to see the sample solution

58. From the following table write a SQL query to find the cheapest and most expensive room in the hotels of a city. Return hotel ID, most expensive and cheapest room's ID.

Input:

Table: hotels

Field         |Type      |Null|Key|Default|Extra|
--------------+----------+----+---+-------+-----+
hotel_id      |int       |YES |   |       |     |
floor_id      |int       |YES |   |       |     |
room_no       |int       |YES |   |       |     |
room_type     |varchar(2)|YES |   |       |     |
price_weekdays|int       |YES |   |       |     |
price_holidays|int       |YES |   |       |     |

Data:

hotel_id|floor_id|room_no|room_type|price_weekdays|price_holidays|
--------+--------+-------+---------+--------------+--------------+
     201|       1|   2005|D        |          5500|          6000|
     201|       1|   2007|Q        |          7500|          9000|
     201|       2|   3008|D        |          5300|          5000|
     201|       5|   5005|D        |          6200|          6000|
     201|       5|   5010|Q        |         10000|         15000|
     203|       2|   4051|S        |          4200|          3800|
     203|       2|   4056|D        |          4800|          5000|
     203|       4|   5058|D        |          5700|          6300|
     203|       4|   5065|S        |          8000|          6500|
     204|       1|   3825|S        |          4000|          3800|
     204|       1|   3830|D        |          3900|          4500|
     204|       2|   4620|D        |          6500|          7000|
     204|       2|   4625|Q        |          8000|         10000|
     204|       2|   4630|S        |          9500|          8500|
     204|       2|   4635|Q        |         15000|         20000|
     205|       2|   5525|S        |          4800|          4300|
     205|       3|   5652|Q        |          7200|          9000|
     205|       3|   5658|D        |          5200|          5000|
     205|       5|   6010|D        |          6500|          6300|
     205|       3|   5670|S        |          5500|          5000|
     205|       5|   6015|Q        |         18500|         16300|

Output:

hotel|most_expensive_room_no|cheapest_room_no|
-----+----------------------+----------------+
  201|                  5010|            3008|
  203|                  5065|            4051|
  204|                  4635|            3830|
  205|                  6015|            5525|
Click me to see the sample solution

59. From the following table write a SQL query to find the managers who can ordered more than 4 reporting employees. Return employee ID and name of the employees.

Input:

Table: employees

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

Data:

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

Output:

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

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

Input:

Table: orders

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

Data:

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

Output:

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

61. From the following table write a SQL query to find the departments where 6 or more employees are working. Return employees ID,name,designation and department ID.

Input:

Table: employees

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

Data:

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

Output:

employee_id|emp_name   |Designation|department_id|
-----------+-----------+-----------+-------------+
        108|Nancy      |FI_MGR     |          100|
        109|Daniel     |FI_ACCOUNT |          100|
        110|John       |FI_ACCOUNT |          100|
        111|Ismael     |FI_ACCOUNT |          100|
        112|Jose Manuel|FI_ACCOUNT |          100|
        113|Luis       |FI_ACCOUNT |          100|
        133|Jason      |ST_CLERK   |           50|
        134|Michael    |ST_CLERK   |           50|
        135|Ki         |ST_CLERK   |           50|
        136|Hazel      |ST_CLERK   |           50|
        137|Renske     |ST_CLERK   |           50|
        138|Stephen    |ST_CLERK   |           50|
        139|John       |ST_CLERK   |           50|
Click me to see the sample solution

62. From the following table write a query in SQL to find the highest sale among salespersons that appears only once. Return salesperson ID and sale amount.

Input:

Table: sales

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

Data:

TRANSACTION_ID|SALESMAN_ID|SALE_AMOUNT|
--------------+-----------+-----------+
           501|         18|    5200.00|
           502|         50|    5566.00|
           503|         38|    8400.00|
           504|         43|    8400.00|
           505|         11|    9000.00|
           506|         42|   12200.00|
           507|         13|    7000.00|
           508|         33|    6000.00|
           509|         41|    8200.00|
           510|         11|    4500.00|
           511|         51|   10000.00|
           512|         29|    9500.00|
           513|         59|    6500.00|
           514|         38|    7800.00|
           515|         58|    9800.00|
           516|         60|   12000.00|
           517|         58|   14000.00|
           518|         23|   12200.00|
           519|         34|    5480.00|
           520|         35|    8129.00|
           521|         49|    9323.00|
           522|         46|    8200.00|
           523|         47|    9990.00|
           524|         42|   14000.00|
           525|         44|    7890.00|
           526|         47|    5990.00|
           527|         21|    7770.00|
           528|         57|    6645.00|
           529|         56|    5125.00|
           530|         25|   10990.00|

Output:

sale_amount|
-----------+
   12000.00|
Click me to see the sample solution

63. From the following table write a query in SQL to find the highest daily total order for an item between 2019-07-01 to 2019-12-31. Return item description, order date and the total order quantity.

Input:

Table: orders

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

Data:

ORDER_ID|CUSTOMER_ID|ITEM_DESC |ORDER_DATE|order_qty|
--------+-----------+----------+----------+---------+
     101|       2109|juice     |2019-07-21|       50|
     102|       2139|chocolate |2019-05-17|       40|
     103|       2120|juice     |2019-05-17|       40|
     104|       2108|cookies   |2019-05-17|       50|
     105|       2130|juice     |2019-10-18|       45|
     106|       2103|cake      |2019-07-21|       35|
     107|       2122|cookies   |2019-12-17|       40|
     108|       2125|cake      |2019-12-17|       38|
     109|       2139|cake      |2019-07-21|       40|
     110|       2141|cookies   |2019-05-17|       60|
     111|       2116|cake      |2019-10-18|       45|
     112|       2128|cake      |2019-10-18|       38|
     113|       2146|chocolate |2019-10-18|       55|
     114|       2119|cookies   |2019-10-18|       30|
     115|       2142|cake      |2019-03-05|       26|
     116|       2122|cake      |2019-03-05|       59|
     117|       2128|chocolate |2019-06-19|       45|
     118|       2112|cookies   |2019-06-19|       28|
     119|       2149|cookies   |2019-10-18|       49|
     120|       2100|cookies   |2020-03-14|       76|
     121|       2130|juice     |2020-03-14|       20|
     122|       2103|juice     |2019-07-21|       27|
     123|       2112|cookies   |2019-06-19|       52|
     124|       2102|cake      |2019-07-21|       14|
     125|       2120|chocolate |2019-07-21|       85|
     126|       2109|cake      |2019-06-19|       18|
     127|       2101|juice     |2019-10-18|       64|
     128|       2138|juice     |2019-06-19|       55|
     129|       2100|juice     |2019-07-21|       45|
     130|       2129|juice     |2019-10-18|       35|
     131|       2123|juice     |2019-10-18|       25|
     132|       2104|chocolate |2019-10-18|       63|
     133|       2110|cake      |2019-12-17|       38|
     134|       2143|cake      |2019-06-19|       27|
     135|       2130|juice     |2019-05-17|       30|
     136|       2128|juice     |2019-10-18|       40|
     137|       2133|cookies   |2019-06-19|       51|
     138|       2150|cookies   |2019-06-19|       43|
     139|       2120|juice     |2019-10-18|       55|
     140|       2109|cake      |2019-10-18|       44|
     141|       2110|cake      |2019-12-17|       50|
     142|       2140|juice     |2019-03-05|       65|
     143|       2112|cookies   |2019-10-18|       46|
     144|       2117|chocolate |2019-06-19|       25|
     145|       2137|cookies   |2019-07-21|       49|
     146|       2130|cake      |2019-12-17|       40|
     147|       2133|cake      |2019-07-21|       30|
     148|       2143|juice     |2019-03-05|       60|
     149|       2111|chocolate |2019-07-21|       74|
     150|       2150|cookies   |2019-10-18|       75|
     151|       2131|cake      |2019-07-21|       45|
     152|       2140|chocolate |2019-05-17|       40|
     153|       2147|cookies   |2019-07-21|       65|
     154|       2119|chocolate |2019-05-17|       60|
     155|       2116|juice     |2019-12-17|       40|
     156|       2141|juice     |2019-12-17|       50|
     157|       2143|cake      |2019-05-17|       55|
     158|       2105|cake      |2019-07-21|       70|
     159|       2149|chocolate |2019-03-05|       50|
     160|       2117|cookies   |2019-07-21|       61|
     161|       2150|cookies   |2019-07-21|       50|
     162|       2134|cake      |2019-03-05|       55|
     163|       2133|cookies   |2019-06-19|       54|
     164|       2127|juice     |2019-06-19|       55|
     165|       2101|juice     |2019-06-19|       45|
     166|       2137|chocolate |2019-12-17|       40|
     167|       2113|chocolate |2019-06-19|       30|
     168|       2141|cake      |2019-06-19|       60|
     169|       2112|chocolate |2019-12-17|       25|
     170|       2118|juice     |2019-10-18|       70|
     171|       2111|juice     |2019-06-19|       60|
     172|       2146|chocolate |2019-12-17|       20|
     173|       2148|cookies   |2020-03-14|       55|
     174|       2100|cookies   |2019-12-17|       40|
     175|       2105|cookies   |2019-03-05|       30|
     176|       2129|juice     |2019-10-18|       40|
     177|       2121|juice     |2019-05-17|       30|
     178|       2117|cake      |2019-07-21|       40|
     179|       2133|juice     |2019-07-21|       20|
     180|       2124|cake      |2019-07-21|       30|
     181|       2145|cake      |2019-12-17|       25|
     182|       2105|cookies   |2019-03-05|       20|
     183|       2131|juice     |2019-03-05|       50|
     184|       2114|chocolate |2019-10-18|       56|
     185|       2120|juice     |2019-12-17|       55|
     186|       2130|juice     |2019-12-17|       46|
     187|       2141|chocolate |2019-03-05|       54|
     188|       2147|cake      |2020-03-14|       40|
     189|       2118|juice     |2019-05-17|       54|
     190|       2136|chocolate |2019-07-21|       58|
     191|       2132|cake      |2019-12-17|       50|
     192|       2137|chocolate |2019-07-21|       53|
     193|       2107|cake      |2019-10-18|       35|
     194|       2111|chocolate |2019-05-17|       50|
     195|       2100|cake      |2019-03-05|       55|
     196|       2106|juice     |2019-07-21|       47|
     197|       2114|cookies   |2019-06-19|       55|
     198|       2110|cake      |2019-06-19|       75|
     199|       2130|juice     |2019-05-17|       55|
     200|       2117|cake      |2019-12-17|       48|

Output:

item_desc|order_date|daysOrder|
---------+----------+---------+
juice    |2019-10-18|      374|
Click me to see the sample solution

64. From the following tables write a query in SQL to count the number of patients treated by each doctor. Return doctors name and number of patients they treated.

Input:

Table: doctors

Field      |Type       |Null|Key|Default|Extra|
-----------+-----------+----+---+-------+-----+
doctor_id  |int        |NO  |PRI|       |     |
doctor_name|varchar(25)|YES |   |       |     |
degree     |varchar(10)|YES |   |       |     |

Data:

doctor_id|doctor_name   |degree|
---------+--------------+------+
      210|Dr. John Linga|MD    |
      211|Dr. Peter Hall|MBBS  |
      212|Dr. Ke Gee    |MD    |
      213|Dr. Pat Fay   |MD    |

Table: visits

Field       |Type       |Null|Key|Default|Extra|
------------+-----------+----+---+-------+-----+
doctor_id   |int        |YES |MUL|       |     |
patient_name|varchar(25)|YES |   |       |     |
vdate       |date       |YES |   |       |     |

Data:

doctor_id|patient_name |vdate     |
---------+-------------+----------+
      210|Julia Nayer  |2013-10-15|
      213|TJ Olson     |2013-10-14|
      211|John Seo     |2013-10-15|
      212|James Marlow |2013-10-16|
      212|Jason Mallin |2013-10-12|
      213|Dean Hops    |2013-10-18|
      212|Peter Kent   |2013-10-19|
      212|Moody Hogs   |2013-10-25|

Output:

doctor_name   |Patients Treated|
--------------+----------------+
Dr. John Linga|               1|
Dr. Peter Hall|               1|
Dr. Ke Gee    |               4|
Dr. Pat Fay   |               2|
Click me to see the sample solution

65. From the following table write a SQL query to calculate the total order amount issued by each customer in September 2008. Return customer ID and total order amount.

Input:

Table: orders

Field          |Type       |Null|Key|Default|Extra|
---------------+-----------+----+---+-------+-----+
ord_num        |int        |NO  |PRI|       |     |
ord_amount     |int        |YES |   |       |     |
advance_amount |int        |YES |   |       |     |
ord_date       |date       |YES |   |       |     |
cust_code      |varchar(10)|YES |MUL|       |     |
ord_description|varchar(25)|YES |   |       |     |

Data:

ord_num|ord_amount|advance_amount|ord_date  |cust_code|ord_description|
-------+----------+--------------+----------+---------+---------------+
 200100|      1000|           600|2008-01-08|C00015   |               |
 200101|      3000|          1000|2008-07-15|C00001   |               |
 200102|      2000|           300|2008-05-25|C00012   |               |
 200103|      1500|           700|2008-05-15|C00021   |               |
 200104|      1500|           500|2008-03-13|C00006   |               |
 200105|      2500|           500|2008-07-18|C00025   |               |
 200106|      2500|           700|2008-04-20|C00005   |               |
 200107|      4500|           900|2008-08-30|C00007   |               |
 200108|      4000|           600|2008-02-15|C00008   |               |
 200109|      3500|           800|2008-07-30|C00011   |               |
 200110|      3000|           500|2008-04-15|C00019   |               |
 200111|      1000|           300|2008-07-10|C00020   |               |
 200112|      2000|           400|2008-05-30|C00016   |               |
 200113|      4000|           600|2008-06-10|C00022   |               |
 200114|      3500|          2000|2008-08-15|C00002   |               |
 200116|       500|           100|2008-07-13|C00010   |               |
 200117|       800|           200|2008-10-20|C00014   |               |
 200118|       500|           100|2008-07-20|C00023   |               |
 200119|      4000|           700|2008-09-16|C00007   |               |
 200120|       500|           100|2008-07-20|C00009   |               |
 200121|      1500|           600|2008-09-23|C00008   |               |
 200122|      2500|           400|2008-09-16|C00003   |               |
 200123|       500|           100|2008-09-16|C00022   |               |
 200124|       500|           100|2008-06-20|C00017   |               |
 200125|      2000|           600|2008-10-10|C00018   |               |
 200126|       500|           100|2008-06-24|C00022   |               |
 200127|      2500|           400|2008-07-20|C00015   |               |
 200128|      3500|          1500|2008-07-20|C00009   |               |
 200129|      2500|           500|2008-07-20|C00024   |               |
 200130|      2500|           400|2008-07-30|C00025   |               |
 200131|       900|           150|2008-08-26|C00012   |               |
 200133|      1200|           400|2008-06-29|C00009   |               |
 200134|      4200|          1800|2008-09-25|C00004   |               |
 200135|      2000|           800|2008-09-16|C00007   |               |

Table: customer

Field          |Type         |Null|Key|Default|Extra|
---------------+-------------+----+---+-------+-----+
cust_code      |varchar(10)  |NO  |PRI|       |     |
cust_name      |varchar(25)  |YES |   |       |     |
cust_city      |varchar(25)  |YES |   |       |     |
working_area   |varchar(25)  |YES |   |       |     |
cust_country   |varchar(25)  |YES |   |       |     |
grade          |int          |YES |   |       |     |
opening_amt    |decimal(10,2)|YES |   |       |     |
receive_amt    |decimal(10,2)|YES |   |       |     |
payment_amt    |decimal(10,2)|YES |   |       |     |
outstanding_amt|decimal(10,2)|YES |   |       |     |
phone_no       |varchar(15)  |YES |   |       |     |

Data:

cust_code|cust_name  |cust_city |working_area|cust_country|grade|opening_amt|receive_amt|payment_amt|outstanding_amt|phone_no    |
---------+-----------+----------+------------+------------+-----+-----------+-----------+-----------+---------------+------------+
C00001   |Micheal    |New York  |New York    |USA         |    2|    3000.00|    5000.00|    2000.00|        6000.00|CCCCCCC     |
C00002   |Bolt       |New York  |New York    |USA         |    3|    5000.00|    7000.00|    9000.00|        3000.00|DDNRDRH     |
C00003   |Martin     |Torento   |Torento     |Canada      |    2|    8000.00|    7000.00|    7000.00|        8000.00|MJYURFD     |
C00004   |Winston    |Brisban   |Brisban     |Australia   |    1|    5000.00|    8000.00|    7000.00|        6000.00|AAAAAAA     |
C00005   |Sasikant   |Mumbai    |Mumbai      |India       |    1|    7000.00|   11000.00|    7000.00|       11000.00|147-25896312|
C00006   |Shilton    |Torento   |Torento     |Canada      |    1|   10000.00|    7000.00|    6000.00|       11000.00|DDDDDDD     |
C00007   |Ramanathan |Chennai   |Chennai     |India       |    1|    7000.00|   11000.00|    9000.00|        9000.00|GHRDWSD     |
C00008   |Karolina   |Torento   |Torento     |Canada      |    1|    7000.00|    7000.00|    9000.00|        5000.00|HJKORED     |
C00009   |Ramesh     |Mumbai    |Mumbai      |India       |    3|    8000.00|    7000.00|    3000.00|       12000.00|Phone No    |
C00010   |Charles    |Hampshair |Hampshair   |UK          |    3|    6000.00|    4000.00|    5000.00|        5000.00|MMMMMMM     |
C00011   |Sundariya  |Chennai   |Chennai     |India       |    3|    7000.00|   11000.00|    7000.00|       11000.00|PPHGRTS     |
C00012   |Steven     |San Jose  |San Jose    |USA         |    1|    5000.00|    7000.00|    9000.00|        3000.00|KRFYGJK     |
C00013   |Holmes     |London    |London      |UK          |    2|    6000.00|    5000.00|    7000.00|        4000.00|BBBBBBB     |
C00014   |Rangarappa |Bangalore |Bangalore   |India       |    2|    8000.00|   11000.00|    7000.00|       12000.00|AAAATGF     |
C00015   |Stuart     |London    |London      |UK          |    1|    6000.00|    8000.00|    3000.00|       11000.00|GFSGERS     |
C00016   |Venkatpati |Bangalore |Bangalore   |India       |    2|    8000.00|   11000.00|    7000.00|       12000.00|JRTVFDD     |
C00017   |Srinivas   |Bangalore |Bangalore   |India       |    2|    8000.00|    4000.00|    3000.00|        9000.00|AAAAAAB     |
C00018   |Fleming    |Brisban   |Brisban     |Australia   |    2|    7000.00|    7000.00|    9000.00|        5000.00|NHBGVFC     |
C00019   |Yearannaidu|Chennai   |Chennai     |India       |    1|    8000.00|    7000.00|    7000.00|        8000.00|ZZZZBFV     |
C00020   |Albert     |New York  |New York    |USA         |    3|    5000.00|    7000.00|    6000.00|        6000.00|BBBBSBB     |
C00021   |Jacks      |Brisban   |Brisban     |Australia   |    1|    7000.00|    7000.00|    7000.00|        7000.00|WERTGDF     |
C00022   |Avinash    |Mumbai    |Mumbai      |India       |    2|    7000.00|   11000.00|    9000.00|        9000.00|113-12345678|
C00023   |Karl       |London    |London      |UK          |    0|    4000.00|    6000.00|    7000.00|        3000.00|AAAABAA     |
C00024   |Cook       |London    |London      |UK          |    2|    4000.00|    9000.00|    7000.00|        6000.00|FSDDSDF     |
C00025   |Ravindran  |Bangalore |Bangalore   |India       |    2|    5000.00|    7000.00|    4000.00|        8000.00|AVAVAVA     |

Output:

cust_code|Total Order|
---------+-----------+
C00007   |       6000|
C00004   |       4200|
C00003   |       2500|
C00008   |       1500|
C00022   |        500|
Click me to see the sample solution

66. From the following table write a SQL query to show the salary of each employee along with the average salary for their department. Return job ID,name,salary and average salary.

Input:

Table: employees

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

Data:

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

Output:

job_id    |emp_name   |salary  |Average_Salary|
----------+-----------+--------+--------------+
AD_PRES   |Steven     |24000.00|  24000.000000|
AD_VP     |Neena      |17000.00|  17000.000000|
AD_VP     |Lex        |17000.00|  17000.000000|
IT_PROG   |Alexander  | 9000.00|   5760.000000|
IT_PROG   |Bruce      | 6000.00|   5760.000000|
IT_PROG   |David      | 4800.00|   5760.000000|
IT_PROG   |Valli      | 4800.00|   5760.000000|
IT_PROG   |Diana      | 4200.00|   5760.000000|
FI_MGR    |Nancy      |12000.00|  12000.000000|
FI_ACCOUNT|Daniel     | 9000.00|   7920.000000|
FI_ACCOUNT|John       | 8200.00|   7920.000000|
FI_ACCOUNT|Ismael     | 7700.00|   7920.000000|
FI_ACCOUNT|Jose Manuel| 7800.00|   7920.000000|
FI_ACCOUNT|Luis       | 6900.00|   7920.000000|
PU_MAN    |Den        |11000.00|  11000.000000|
PU_CLERK  |Alexander  | 3100.00|   2933.333333|
PU_CLERK  |Shelli     | 2900.00|   2933.333333|
PU_CLERK  |Sigal      | 2800.00|   2933.333333|
ST_CLERK  |Jason      | 3300.00|   2900.000000|
ST_CLERK  |Michael    | 2900.00|   2900.000000|
ST_CLERK  |Ki         | 2400.00|   2900.000000|
ST_CLERK  |Hazel      | 2200.00|   2900.000000|
ST_CLERK  |Renske     | 3600.00|   2900.000000|
ST_CLERK  |Stephen    | 3200.00|   2900.000000|
ST_CLERK  |John       | 2700.00|   2900.000000|
Click me to see the sample solution

67. From the following table write a query in SQL to find the highest salaried employees for each designation. Return job_id, name and the salary earned.

Input:

Table: employees

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

Data:

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

Output:

job_id    |Name       |salary  |
----------+-----------+--------+
AD_PRES   |Steven     |24000.00|
AD_VP     |Neena      |17000.00|
AD_VP     |Lex        |17000.00|
IT_PROG   |Alexander  | 9000.00|
FI_MGR    |Nancy      |12000.00|
FI_ACCOUNT|Daniel     | 9000.00|
PU_MAN    |Den        |11000.00|
PU_CLERK  |Alexander  | 3100.00|
ST_CLERK  |Renske     | 3600.00|
Click me to see the sample solution

68. From the following table write a query in SQL to find the employees who are earning more salary than their managers. Return employee ID, name, and salary, manager ID, manager name and the salary of the manager.

Input:

Table: employees

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

Data:

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

Output:

employee_id|emp_name   |salary  |manager_id|Manager Name|Manager Salary|
-----------+-----------+--------+----------+------------+--------------+
        104|Bruce      |11000.00|       103|Alexander   |       9000.00|
Click me to see the sample solution

69. From the following tables write a query in SQL to find the cost percent for each order of total order amount of a customer. Return customer ID, customer name, order details, order amount and cost percent.

Input:

Table: orders

Field          |Type       |Null|Key|Default|Extra|
---------------+-----------+----+---+-------+-----+
ord_num        |int        |NO  |PRI|       |     |
ord_amount     |int        |YES |   |       |     |
advance_amount |int        |YES |   |       |     |
ord_date       |date       |YES |   |       |     |
cust_code      |varchar(10)|YES |MUL|       |     |
ord_description|varchar(25)|YES |   |       |     |

Data:

ord_num|ord_amount|advance_amount|ord_date  |cust_code|ord_description|
-------+----------+--------------+----------+---------+---------------+
 200100|      1000|           600|2008-01-08|C00015   |RAM            |
 200102|      2000|           300|2008-05-25|C00012   |mother board   |
 200105|      2500|           500|2008-07-18|C00025   |RAM            |
 200107|      4500|           900|2008-08-30|C00007   |hard disk      |
 200108|      4000|           600|2008-02-15|C00008   |mouse          |
 200109|      3500|           800|2008-07-30|C00015   |key board      |
 200113|      4000|           600|2008-06-10|C00022   |key board      |
 200116|       500|           100|2008-07-13|C00010   |mouse          |
 200118|       500|           100|2008-07-20|C00022   |mouse          |
 200119|      4000|           700|2008-09-16|C00007   |RAM            |
 200121|      1500|           600|2008-09-23|C00008   |key board      |
 200122|      2500|           400|2008-09-16|C00007   |mother board   |
 200123|       500|           100|2008-09-16|C00022   |mother board   |
 200125|      2000|           600|2008-10-10|C00008   |hard disk      |
 200126|       500|           100|2008-06-24|C00022   |hard disk      |
 200127|      2500|           400|2008-07-20|C00015   |mouse          |
 200128|      3500|          1500|2008-07-20|C00009   |mother board   |
 200129|      2500|           500|2008-07-20|C00022   |RAM            |
 200130|      2500|           400|2008-07-30|C00025   |mother board   |
 200131|       900|           150|2008-08-26|C00012   |hard disk      |
 200133|      1200|           400|2008-06-29|C00009   |mouse          |

Table: customer

Field          |Type         |Null|Key|Default|Extra|
---------------+-------------+----+---+-------+-----+
cust_code      |varchar(10)  |NO  |PRI|       |     |
cust_name      |varchar(25)  |YES |   |       |     |
cust_city      |varchar(25)  |YES |   |       |     |
working_area   |varchar(25)  |YES |   |       |     |
cust_country   |varchar(25)  |YES |   |       |     |
grade          |int          |YES |   |       |     |
opening_amt    |decimal(10,2)|YES |   |       |     |
receive_amt    |decimal(10,2)|YES |   |       |     |
payment_amt    |decimal(10,2)|YES |   |       |     |
outstanding_amt|decimal(10,2)|YES |   |       |     |
phone_no       |varchar(15)  |YES |   |       |     |

Data:

cust_code|cust_name  |cust_city |working_area|cust_country|grade|opening_amt|receive_amt|payment_amt|outstanding_amt|phone_no    |
---------+-----------+----------+------------+------------+-----+-----------+-----------+-----------+---------------+------------+
C00001   |Micheal    |New York  |New York    |USA         |    2|    3000.00|    5000.00|    2000.00|        6000.00|CCCCCCC     |
C00002   |Bolt       |New York  |New York    |USA         |    3|    5000.00|    7000.00|    9000.00|        3000.00|DDNRDRH     |
C00003   |Martin     |Torento   |Torento     |Canada      |    2|    8000.00|    7000.00|    7000.00|        8000.00|MJYURFD     |
C00004   |Winston    |Brisban   |Brisban     |Australia   |    1|    5000.00|    8000.00|    7000.00|        6000.00|AAAAAAA     |
C00005   |Sasikant   |Mumbai    |Mumbai      |India       |    1|    7000.00|   11000.00|    7000.00|       11000.00|147-25896312|
C00006   |Shilton    |Torento   |Torento     |Canada      |    1|   10000.00|    7000.00|    6000.00|       11000.00|DDDDDDD     |
C00007   |Ramanathan |Chennai   |Chennai     |India       |    1|    7000.00|   11000.00|    9000.00|        9000.00|GHRDWSD     |
C00008   |Karolina   |Torento   |Torento     |Canada      |    1|    7000.00|    7000.00|    9000.00|        5000.00|HJKORED     |
C00009   |Ramesh     |Mumbai    |Mumbai      |India       |    3|    8000.00|    7000.00|    3000.00|       12000.00|Phone No    |
C00010   |Charles    |Hampshair |Hampshair   |UK          |    3|    6000.00|    4000.00|    5000.00|        5000.00|MMMMMMM     |
C00011   |Sundariya  |Chennai   |Chennai     |India       |    3|    7000.00|   11000.00|    7000.00|       11000.00|PPHGRTS     |
C00012   |Steven     |San Jose  |San Jose    |USA         |    1|    5000.00|    7000.00|    9000.00|        3000.00|KRFYGJK     |
C00013   |Holmes     |London    |London      |UK          |    2|    6000.00|    5000.00|    7000.00|        4000.00|BBBBBBB     |
C00014   |Rangarappa |Bangalore |Bangalore   |India       |    2|    8000.00|   11000.00|    7000.00|       12000.00|AAAATGF     |
C00015   |Stuart     |London    |London      |UK          |    1|    6000.00|    8000.00|    3000.00|       11000.00|GFSGERS     |
C00016   |Venkatpati |Bangalore |Bangalore   |India       |    2|    8000.00|   11000.00|    7000.00|       12000.00|JRTVFDD     |
C00017   |Srinivas   |Bangalore |Bangalore   |India       |    2|    8000.00|    4000.00|    3000.00|        9000.00|AAAAAAB     |
C00018   |Fleming    |Brisban   |Brisban     |Australia   |    2|    7000.00|    7000.00|    9000.00|        5000.00|NHBGVFC     |
C00019   |Yearannaidu|Chennai   |Chennai     |India       |    1|    8000.00|    7000.00|    7000.00|        8000.00|ZZZZBFV     |
C00020   |Albert     |New York  |New York    |USA         |    3|    5000.00|    7000.00|    6000.00|        6000.00|BBBBSBB     |
C00021   |Jacks      |Brisban   |Brisban     |Australia   |    1|    7000.00|    7000.00|    7000.00|        7000.00|WERTGDF     |
C00022   |Avinash    |Mumbai    |Mumbai      |India       |    2|    7000.00|   11000.00|    9000.00|        9000.00|113-12345678|
C00023   |Karl       |London    |London      |UK          |    0|    4000.00|    6000.00|    7000.00|        3000.00|AAAABAA     |
C00024   |Cook       |London    |London      |UK          |    2|    4000.00|    9000.00|    7000.00|        6000.00|FSDDSDF     |
C00025   |Ravindran  |Bangalore |Bangalore   |India       |    2|    5000.00|    7000.00|    4000.00|        8000.00|AVAVAVA     |

Output:

cust_code|cust_name  |ord_description|ord_amount|Percentage of Cost|
---------+-----------+---------------+----------+------------------+
C00022   |Avinash    |key board      |      4000|            0.5000|
C00022   |Avinash    |mouse          |       500|            0.0625|
C00022   |Avinash    |mother board   |       500|            0.0625|
C00022   |Avinash    |hard disk      |       500|            0.0625|
C00022   |Avinash    |RAM            |      2500|            0.3125|
C00010   |Charles    |mouse          |       500|            1.0000|
C00008   |Karolina   |mouse          |      4000|            0.5333|
C00008   |Karolina   |key board      |      1500|            0.2000|
C00008   |Karolina   |hard disk      |      2000|            0.2667|
C00007   |Ramanathan |hard disk      |      4500|            0.4091|
C00007   |Ramanathan |RAM            |      4000|            0.3636|
C00007   |Ramanathan |mother board   |      2500|            0.2273|
C00009   |Ramesh     |mother board   |      3500|            0.7447|
C00009   |Ramesh     |mouse          |      1200|            0.2553|
C00025   |Ravindran  |RAM            |      2500|            0.5000|
C00025   |Ravindran  |mother board   |      2500|            0.5000|
C00012   |Steven     |mother board   |      2000|            0.6897|
C00012   |Steven     |hard disk      |       900|            0.3103|
C00015   |Stuart     |RAM            |      1000|            0.1429|
C00015   |Stuart     |key board      |      3500|            0.5000|
C00015   |Stuart     |mouse          |      2500|            0.3571|
Click me to see the sample solution

70. From the following table write a query in SQL to find those employees who resolve the highest complaint in all quarter and works under the supervision of that manager, holding id 114. Returns employee name and the number of complaint resolved.

Input:

Table: employees

Field         |Type       |Null|Key|Default|Extra|
--------------+-----------+----+---+-------+-----+
emp_id        |int        |NO  |PRI|       |     |
emp_name      |varchar(30)|YES |   |       |     |
emp_sex       |varchar(1) |YES |   |       |     |
depart_name   |varchar(25)|YES |   |       |     |
emp_salary    |int        |YES |   |       |     |
qtr1          |int        |YES |   |       |     |
qtr2          |int        |YES |   |       |     |
qtr3          |int        |YES |   |       |     |
qtr4          |int        |YES |   |       |     |
emp_department|int        |YES |   |       |     |
manager_id    |int        |YES |   |       |     |

Data:

emp_id|emp_name   |emp_sex|depart_name   |emp_salary|qtr1|qtr2|qtr3|qtr4|emp_department| manager_id|
------+-----------+-------+--------------+----------+----+----+----+----+--------------+ ----------+
   100|Steven     |M      |Production    |     24000| 240| 310| 275| 300|            90|          0|
   101|Neena      |F      |Production    |     17000| 270| 300| 275| 290|            90|        100|
   102|Lex        |M      |Audit         |     17000| 300| 290| 285| 310|            80|        100|
   103|Alexander  |M      |Marketing     |      9000|  25| 270| 260| 280|            60|        102|
   104|Bruce      |M      |Marketing     |      6000| 300| 280| 275| 290|            60|        103|
   105|David      |M      |Audit         |      4800| 200| 220| 250| 270|            80|        103|
   106|Valli      |F      |Marketing     |      4800| 300| 320| 330| 350|            60|        103|
   107|Diana      |F      |Marketing     |      4200| 280| 270| 310| 320|            60|        103|
   108|Nancy      |M      |Administration|     12000| 260| 280| 300| 310|           100|        101|
   109|Daniel     |F      |Administration|      9000| 220| 210| 240| 260|           100|        108|
   110|John       |M      |Administration|      8200| 300| 290| 280| 320|           100|        108|
   111|Ismael     |M      |Administration|      7700| 280| 300| 270| 310|           100|        108|
   112|Jose Manuel|M      |Administration|      7800| 250| 280| 260| 300|           100|        108|
   113|Luis       |F      |Administration|      6900| 300| 280| 270| 310|           100|        108|
   114|Den        |M      |Sales         |     11000| 280| 290| 300| 320|            30|        100|
   115|Alexander  |M      |Sales         |      3100| 310| 300| 320| 340|            30|        114|
   116|Shelli     |F      |Sales         |      2900| 245| 260| 280| 300|            30|        114|
   117|Sigal      |F      |Sales         |      2800| 250| 370| 290| 320|            30|        114|
   133|Jason      |M      |Export        |      3300| 280| 270| 300| 290|            50|        122|
   134|Michael    |F      |Export        |      2900| 260| 280| 290| 300|            50|        122|
   135|Ki         |F      |Export        |      2400| 240| 260| 270| 290|            50|        122|

Output:

emp_name   |Case Resolved|
-----------+-------------+
Ismael     |         1160|
Luis       |         1160|
Click me to see the sample solution

71. From the following tables write a query in SQL to find the number of customer issued orders, number of orders they have issued along with the total order amount for each cities. Those cities should come in result set where four or more orders have been issued. Returns city name, number of customers, number of orders and total order amount.

Input:

Table: customer

Field       |Type       |Null|Key|Default|Extra|
------------+-----------+----+---+-------+-----+
cust_code   |varchar(10)|NO  |PRI|       |     |
cust_name   |varchar(25)|YES |   |       |     |
cust_city   |varchar(25)|YES |   |       |     |
cust_country|varchar(25)|YES |   |       |     |
grade       |int        |YES |   |       |     |

Data:

cust_code|cust_name  |cust_city |cust_country|grade|
---------+-----------+----------+------------+-----+
C00001   |Micheal    |New York  |USA         |    2|
C00002   |Bolt       |New York  |USA         |    3|
C00003   |Martin     |Torento   |Canada      |    2|
C00004   |Winston    |Brisban   |Australia   |    1|
C00005   |Sasikant   |Mumbai    |India       |    1|
C00006   |Shilton    |Torento   |Canada      |    1|
C00007   |Ramanathan |Chennai   |India       |    1|
C00008   |Karolina   |Torento   |Canada      |    1|
C00009   |Ramesh     |Mumbai    |India       |    3|
C00010   |Charles    |Hampshair |UK          |    3|
C00011   |Sundariya  |Chennai   |India       |    3|
C00012   |Steven     |San Jose  |USA         |    1|
C00013   |Holmes     |London    |UK          |    2|
C00014   |Rangarappa |Bangalore |India       |    2|
C00015   |Stuart     |London    |UK          |    1|
C00016   |Venkatpati |Bangalore |India       |    2|
C00017   |Srinivas   |Bangalore |India       |    2|
C00018   |Fleming    |Brisban   |Australia   |    2|
C00019   |Yearannaidu|Chennai   |India       |    1|
C00020   |Albert     |New York  |USA         |    3|
C00021   |Jacks      |Brisban   |Australia   |    1|
C00022   |Avinash    |Mumbai    |India       |    2|
C00023   |Karl       |London    |UK          |    0|
C00024   |Cook       |London    |UK          |    2|
C00025   |Ravindran  |Bangalore |India       |    2|

Table: orders

Field     |Type       |Null|Key|Default|Extra|
----------+-----------+----+---+-------+-----+
ord_num   |int        |NO  |PRI|       |     |
ord_amount|int        |YES |   |       |     |
ord_date  |date       |YES |   |       |     |
cust_code |varchar(10)|YES |MUL|       |     |

Data:

ord_num|ord_amount|ord_date  |cust_code|
-------+----------+----------+---------+
 200100|      1000|2008-01-08|C00015   |
 200101|      3000|2008-07-15|C00001   |
 200102|      2000|2008-05-25|C00012   |
 200103|      1500|2008-05-15|C00021   |
 200104|      1500|2008-03-13|C00006   |
 200105|      2500|2008-07-18|C00025   |
 200106|      2500|2008-04-20|C00005   |
 200107|      4500|2008-08-30|C00007   |
 200108|      4000|2008-02-15|C00008   |
 200109|      3500|2008-07-30|C00011   |
 200110|      3000|2008-04-15|C00019   |
 200111|      1000|2008-07-10|C00020   |
 200112|      2000|2008-05-30|C00016   |
 200113|      4000|2008-06-10|C00022   |
 200114|      3500|2008-08-15|C00002   |
 200116|       500|2008-07-13|C00010   |
 200117|       800|2008-10-20|C00014   |
 200118|       500|2008-07-20|C00023   |
 200119|      4000|2008-09-16|C00007   |
 200120|       500|2008-07-20|C00009   |
 200121|      1500|2008-09-23|C00008   |
 200122|      2500|2008-09-16|C00003   |
 200123|       500|2008-09-16|C00022   |
 200124|       500|2008-06-20|C00017   |
 200125|      2000|2008-10-10|C00018   |
 200126|       500|2008-06-24|C00022   |
 200127|      2500|2008-07-20|C00015   |
 200128|      3500|2008-07-20|C00009   |
 200129|      2500|2008-07-20|C00024   |
 200130|      2500|2008-07-30|C00025   |
 200131|       900|2008-08-26|C00012   |
 200133|      1200|2008-06-29|C00009   |
 200134|      4200|2008-09-25|C00004   |
 200135|      2000|2008-09-16|C00007   |

Output:

cust_city |Number of orders|Number of Customer|Total order Amountt|
----------+----------------+------------------+-------------------+
Bangalore |               5|                 4|               8300|
Chennai   |               5|                 3|              17000|
London    |               4|                 4|               6500|
Mumbai    |               7|                 3|              12700|
Torento   |               4|                 3|               9500|
Click me to see the sample solution

72. From the following table write a query in SQL to find the highest three unique salaries for each department. Return department ID and three highest unique salaries. Arranged the result set in ascending order on department ID and descending order on salaries.

Input:

Table: employees

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

Data:

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

Output:

department_id|salary  |
-------------+--------+
           30|11000.00|
           30| 3100.00|
           30| 2900.00|
           50| 3600.00|
           50| 3300.00|
           50| 3200.00|
           60| 9000.00|
           60| 6000.00|
           60| 4800.00|
           90|24000.00|
           90|17000.00|
          100|12000.00|
          100| 9000.00|
          100| 8200.00|
Click me to see the sample solution

73. From the following tables write a query in SQL to find the salespersons who not yet made any sale transaction. Return salesperson ID and salesperson's name. Arranged the resultset in ascending order on salesman ID.

Input:

Table: sales

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

Data:

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

Table: salesman

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

Data:

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

Output:

SALESMAN_ID|SALESMAN_NAME        |
-----------+---------------------+
         14|Jamie Shelley        |
         16|Liam Alton           |
         26|Gabriel Gibson       |
         32|Patrick Riley        |
         39|David Hill           |
         40|Aidan Yeardley       |
         45|Sean Spalding        |
Click me to see the sample solution

74. From the following table write a SQL query find the employee who resolve the highest number of cases in all quarters. Return employee name and total number of cases resolved.

Input:

Table: employees

Field         |Type       |Null|Key|Default|Extra|
--------------+-----------+----+---+-------+-----+
emp_id        |int        |NO  |PRI|       |     |
emp_name      |varchar(30)|YES |   |       |     |
emp_sex       |varchar(1) |YES |   |       |     |
depart_name   |varchar(25)|YES |   |       |     |
emp_salary    |int        |YES |   |       |     |
qtr1          |int        |YES |   |       |     |
qtr2          |int        |YES |   |       |     |
qtr3          |int        |YES |   |       |     |
qtr4          |int        |YES |   |       |     |
emp_department|int        |YES |   |       |     |

Data:

emp_id|emp_name   |emp_sex|depart_name   |emp_salary|qtr1|qtr2|qtr3|qtr4|emp_department|
------+-----------+-------+--------------+----------+----+----+----+----+--------------+
   100|Steven     |M      |Production    |     24000| 240| 310| 275| 300|            90|
   101|Neena      |F      |Production    |     17000| 270| 300| 275| 290|            90|
   102|Lex        |M      |Audit         |     17000| 300| 290| 285| 310|            80|
   103|Alexander  |M      |Marketing     |      9000|  25| 270| 260| 280|            60|
   104|Bruce      |M      |Marketing     |      6000| 300| 280| 275| 290|            60|
   105|David      |M      |Audit         |      4800| 200| 220| 250| 270|            80|
   106|Valli      |F      |Marketing     |      4800| 300| 320| 330| 350|            60|
   107|Diana      |F      |Marketing     |      4200| 280| 270| 310| 320|            60|
   108|Nancy      |M      |Administration|     12000| 260| 280| 300| 310|           100|
   109|Daniel     |F      |Administration|      9000| 220| 210| 240| 260|           100|
   110|John       |M      |Administration|      8200| 300| 290| 280| 320|           100|
   111|Ismael     |M      |Administration|      7700| 280| 300| 270| 310|           100|
   112|Jose Manuel|M      |Administration|      7800| 250| 280| 260| 300|           100|
   113|Luis       |F      |Administration|      6900| 300| 280| 270| 310|           100|
   114|Den        |M      |Sales         |     11000| 280| 290| 300| 320|            30|
   115|Alexander  |M      |Sales         |      3100| 310| 300| 320| 340|            30|
   116|Shelli     |F      |Sales         |      2900| 245| 260| 280| 300|            30|
   117|Sigal      |F      |Sales         |      2800| 250| 370| 290| 320|            30|
   133|Jason      |M      |Export        |      3300| 280| 270| 300| 290|            50|
   134|Michael    |F      |Export        |      2900| 260| 280| 290| 300|            50|
   135|Ki         |F      |Export        |      2400| 240| 260| 270| 290|            50|

Output:

emp_name   |Target achieved in all Qtrs.|
-----------+----------------------------+
Valli      |                        1300|
Click me to see the sample solution

75. From the following tables write a query in SQL to find the department where the highest salaried employee(s) are working. Return department name and highest salary to this department.

Input:

Table: departments

Field          |Type       |Null|Key|Default|Extra|
---------------+-----------+----+---+-------+-----+
department_id  |int        |NO  |PRI|       |     |
department_name|varchar(30)|YES |   |       |     |

Data:

department_id|department_name|
-------------+---------------+
           30|Sales          |
           50|Export         |
           60|Marketing      |
           80|Audit          |
           90|Production     |
          100|Administration |

Table: employees

Field         |Type       |Null|Key|Default|Extra|
--------------+-----------+----+---+-------+-----+
emp_id        |int        |NO  |PRI|       |     |
emp_name      |varchar(30)|YES |   |       |     |
emp_sex       |varchar(1) |YES |   |       |     |
emp_salary    |int        |YES |   |       |     |
emp_department|int        |YES |MUL|       |     |

Data:

emp_id|emp_name   |emp_sex|emp_salary|emp_department|
------+-----------+-------+----------+--------------+
   100|Steven     |M      |     24000|            90|
   101|Neena      |F      |     17000|            90|
   102|Lex        |M      |     17000|            80|
   103|Alexander  |M      |      9000|            60|
   104|Bruce      |M      |      6000|            60|
   105|David      |M      |      4800|            80|
   106|Valli      |F      |      4800|            60|
   107|Diana      |F      |      4200|            60|
   108|Nancy      |M      |     12000|           100|
   109|Daniel     |F      |      9000|           100|
   110|John       |M      |      8200|           100|
   111|Ismael     |M      |      7700|           100|
   112|Jose Manuel|M      |      7800|           100|
   113|Luis       |F      |      6900|           100|
   114|Den        |M      |     11000|            30|
   115|Alexander  |M      |      3100|            30|
   116|Shelli     |F      |      2900|            30|
   117|Sigal      |F      |      2800|            30|
   133|Jason      |M      |      3300|            50|
   134|Michael    |F      |      2900|            50|
   135|Ki         |F      |      2400|            50|

Output:

department_name|emp_salary|
---------------+----------+
Production     |     24000|
Click me to see the sample solution

76. From the following table write a query in SQL to find the 2nd highest salary of employees.

Input:

Table: employees

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

Data:

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

Output:

employee_id|emp_name   |hire_date |job_id    |salary  |manager_id|department_id|
-----------+-----------+----------+----------+--------+----------+-------------+
        100|Steven     |1987-06-17|AD_PRES   |24000.00|         0|           90|
Click me to see the sample solution

77. From the following table write a query in SQL to return the shipped and delivered rate for each order. Return order_id, shipped percentage, and delivered percentage.

Input:

Table: orderdetails

Field       |Type       |Null|Key|Default|Extra|
------------+-----------+----+---+-------+-----+
order_id    |int        |NO  |   |       |     |
order_status|varchar(30)|YES |   |       |     |
order_date  |date       |YES |   |       |     |

Data:

order_id|order_status|order_date|
--------+------------+----------+
   10001|booked      |2008-08-15|
   10001|shipped     |2008-08-16|
   10002|booked      |2008-07-13|
   10002|delivered   |2008-07-19|
   10003|booked      |2008-08-15|
   10003|delivered   |2008-08-18|
   10004|booked      |2008-08-19|
   10004|shipped     |2008-08-19|

Output:

order_id|shipped_perc|delivered_perc|
--------+------------+--------------+
   10001|     1.00000|       0.00000|
   10002|     0.00000|       1.00000|
   10003|     0.00000|       1.00000|
   10004|     1.00000|       0.00000|
Click me to see the sample solution

SQL Code Editor:


More to Come !

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.

Test your skills with w3resource's quiz



Follow us on Facebook and Twitter for latest update.