Description

The solutions of all the SQL challenges for all easy, medium and hard challenges on HackerRank executed on MS-SQLand tested using tSQLt

Steps to download and execute this project

• Download and Install MS-SQL Server.
• Download the SQL project.
• Extract the zip file.
• Open and Login SQL Server.
• Go to File -> Open -> Project/Solutions.
• Browse and Choose SQL.ssmssln and click Open.
• Choose DB as master.
• Open setup.sql and select All.
• Go to Query -> SQLCMD Mode then click Execute.
• Open main.sql and select All.
• Go to Query -> SQLCMD Mode then click Execute. _{^{Note: this will only work if your project folder is under C:/User if not you need to go and run .sql file one by one based on the order from 1 to 10.}}

Output

+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                       |Dur(ms)|Result |
+--+-----------------------------------------------------+-------+-------+
|1 |[AdvancedJoinTestClass].[test_01sqlprojectplanning]  |     63|Success|
|2 |[AdvancedJoinTestClass].[test_02placements]          |     77|Success|
|3 |[AdvancedJoinTestClass].[test_03symmetricpairs]      |     70|Success|
|4 |[AdvancedJoinTestClass].[test_04interviews]          |    293|Success|
|5 |[AdvancedJoinTestClass].[test_05learningsql]         |    260|Success|
|6 |[AdvancedSelectTestClass].[test_01typeoftriangle]    |     70|Success|
|7 |[AdvancedSelectTestClass].[test_02thepads]           |     56|Success|
|8 |[AdvancedSelectTestClass].[test_03occupations]       |     77|Success|
|9 |[AdvancedSelectTestClass].[test_04binarytreenodes]   |     70|Success|
|10|[AdvancedSelectTestClass].[test_05newcompaines]      |    563|Success|
|11|[AggregationTestClass].[test_01thecountfunction]     |      6|Success|
|12|[AggregationTestClass].[test_02thesumfunction]       |      6|Success|
|13|[AggregationTestClass].[test_03averages]             |      3|Success|
|14|[AggregationTestClass].[test_04averagepopulation]    |      0|Success|
|15|[AggregationTestClass].[test_05japanpopulation]      |      3|Success|
|16|[AggregationTestClass].[test_06populationdensitydiff]|      6|Success|
|17|[AggregationTestClass].[test_07theblunder]           |      3|Success|
|18|[AggregationTestClass].[test_08topearners]           |     44|Success|
|19|[AggregationTestClass].[test_09wos02]                |     40|Success|
|20|[AggregationTestClass].[test_10wos13]                |      7|Success|
|21|[AggregationTestClass].[test_11wos14]                |      4|Success|
|22|[AggregationTestClass].[test_12wos15]                |      7|Success|
|23|[AggregationTestClass].[test_13wos16]                |      7|Success|
|24|[AggregationTestClass].[test_14wos17]                |      7|Success|
|25|[AggregationTestClass].[test_15wos18]                |     10|Success|
|26|[AggregationTestClass].[test_16wos19]                |     10|Success|
|27|[AggregationTestClass].[test_17wos20]                |      6|Success|
|28|[AlternativeQueriesTestClass].[test_01drawtriangle1] |     87|Success|
|29|[AlternativeQueriesTestClass].[test_02drawtriangle2] |     87|Success|
|30|[AlternativeQueriesTestClass].[test_03printprime]    |    950|Success|
|31|[BasicJoinTestClass].[test_01populationcensus]       |     16|Success|
|32|[BasicJoinTestClass].[test_02africancities]          |     40|Success|
|33|[BasicJoinTestClass].[test_03avgpopulation]          |     40|Success|
|34|[BasicJoinTestClass].[test_04thereport]              |     70|Success|
|35|[BasicJoinTestClass].[test_05topcompetitors]         |    160|Success|
|36|[BasicJoinTestClass].[test_06ollivadersinventory]    |    950|Success|
|37|[BasicJoinTestClass].[test_07challenges]             |    113|Success|
|38|[BasicJoinTestClass].[test_08contestleaderboard]     |    317|Success|
|39|[BasicSelectTestClass].[test_01query1]               |    120|Success|
|40|[BasicSelectTestClass].[test_02query2]               |     43|Success|
|41|[BasicSelectTestClass].[test_03selectall]            |    157|Success|
|42|[BasicSelectTestClass].[test_04selectbyid]           |     50|Success|
|43|[BasicSelectTestClass].[test_05jpncity]              |     53|Success|
|44|[BasicSelectTestClass].[test_06jpncityname]          |     33|Success|
|45|[BasicSelectTestClass].[test_07wos01]                |    580|Success|
|46|[BasicSelectTestClass].[test_08wos03]                |    310|Success|
|47|[BasicSelectTestClass].[test_09wos04]                |      7|Success|
|48|[BasicSelectTestClass].[test_10wos05]                |     47|Success|
|49|[BasicSelectTestClass].[test_11wos06]                |    110|Success|
|50|[BasicSelectTestClass].[test_12wos07]                |    226|Success|
|51|[BasicSelectTestClass].[test_13wos08]                |     84|Success|
|52|[BasicSelectTestClass].[test_14wos09]                |    496|Success|
|53|[BasicSelectTestClass].[test_15wos10]                |    436|Success|
|54|[BasicSelectTestClass].[test_16wos11]                |    650|Success|
|55|[BasicSelectTestClass].[test_17wos12]                |    387|Success|
|56|[BasicSelectTestClass].[test_18higherthan75]         |     50|Success|
|57|[BasicSelectTestClass].[test_19employeenames]        |    186|Success|
|58|[BasicSelectTestClass].[test_20employeesalaries]     |     84|Success|
-------------------------------------------------------------------------------
Test Case Summary: 58 test case(s) executed, 58 succeeded, 0 failed, 0 errored.
-------------------------------------------------------------------------------

Domains

Basic Select Challenges

Number	Challenges	Solutions
1	Revising the Select Query I	SQL
2	Revising the Select Query II	SQL
3	Select All	SQL
4	Select By ID	SQL
5	Japanese Cities' Attributes	SQL
6	Japanese Cities' Names	SQL
7	Weather Observation Station 1	SQL
8	Weather Observation Station 3	SQL
9	Weather Observation Station 4	SQL
10	Weather Observation Station 5	SQL
11	Weather Observation Station 6	SQL
12	Weather Observation Station 7	SQL
13	Weather Observation Station 8	SQL
14	Weather Observation Station 9	SQL
15	Weather Observation Station 10	SQL
16	Weather Observation Station 11	SQL
17	Weather Observation Station 12	SQL
18	Higher Than 75 Marks	SQL
19	Employee Names	SQL
20	Employee Salaries	SQL

Advanced Select Challenges

Number	Challenges	Solutions
1	Type of Triangle	SQL
2	The PADS	SQL
3	Occupations	SQL
4	Binary Tree Nodes	SQL
5	New Companies	SQL

Aggregation Challenges

Number	Challenges	Solutions
1	Revising Aggregations - The Count Function	SQL
2	Revising Aggregations - The Sum Function	SQL
3	Revising Aggregations - Averages	SQL
4	Average Population	SQL
5	Japan Population	SQL
6	Population Density Difference	SQL
7	The Blunder	SQL
8	Top Earners	SQL
9	Weather Observation Station 2	SQL
10	Weather Observation Station 13	SQL
11	Weather Observation Station 14	SQL
12	Weather Observation Station 15	SQL
13	Weather Observation Station 16	SQL
14	Weather Observation Station 17	SQL
15	Weather Observation Station 18	SQL
16	Weather Observation Station 19	SQL
17	Weather Observation Station 20	SQL

Basic Join Challenges

Number	Challenges	Solutions
1	Asian Population	SQL
2	African Cities	SQL
3	Average Population of Each Continent	SQL
4	The Report	SQL
5	Top Competitors	SQL
6	Ollivander's Inventory	SQL
7	Challenges	SQL
8	Contest Leaderboard	SQL

Advanced Join Challenges

Number	Challenges	Solutions
1	SQL Project Planning	SQL
2	Placements	SQL
3	Symmetric Pairs	SQL
4	Interviews	SQL
5	15 Days of Learning SQL	SQL

Alternative Queries Challenges

Number	Challenges	Solutions
1	Draw The Triangle 1	SQL
2	Draw The Triangle 2	SQL
3	Print Prime Numbers	SQL

References

• Install MS SQL Server.
• Install tSQLt
• Instant SQL Server
• Instant SQL Formatter