# HackerRank: MySQL Solutions

GuidelineS

Use the *hints *to help solve the problem. If you need to check a solution, please make sure you understand the different parts of the query.

- Top Earners
- The Blunder
- Average Population of Each Continent
- Population Census
- Japan Population
- Average Population
- Revising Aggregations – Averages
- Revising Aggregations – The Sum Function
- Revising Aggregations – The Count Function
- African Cities
- Population Density Difference
- Higher Than 75 Marks
- Employee Salaries
- Employee Names
- Japanese Cities’ Names
- Japanese Cities’ Attributes
- Select By ID
- Select All
- Revising the Select Query II
- Revising the Select Query I
- Weather Observation Station 17
- Weather Observation Station 16
- Weather Observation Station 15
- Weather Observation Station 14
- Weather Observation Station 13
- Weather Observation Station 12
- Weather Observation Station 11
- Weather Observation Station 10
- Weather Observation Station 9
- Weather Observation Station 8
- Weather Observation Station 7
- Weather Observation Station 6
- Weather Observation Station 5
- Weather Observation Station 4
- Weather Observation Station 3
- Weather Observation Station 2
- Weather Observation Station 1

## Top Earners

HackerRank: PREPARE > SQL

We define an employee’s *total earnings* to be their monthly salary x months worked, and the *maximum total earnings* to be the maximum total earnings for any employee in the **Employee** table. Write a query to find the *maximum total earnings* for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

**Input Format**

The **Employee** table containing employee data for a company is described as follows:

where *employee_id* is an employee’s ID number, *name* is their name, *months* is the total number of months they’ve been working for the company, and *salary* is the their monthly salary.

**Sample Input**

**Sample Output**

`69952 1`

**Explanation**

The table and earnings data is depicted in the following diagram:

The maximum *earnings* value is 69952. The only employee with *earnings* = 69952 is *Kimberly*, so we print the maximum *earnings* value (69952) and a count of the number of employees who have earned $69952(which is 1) as two space-separated values.

Read up on Count Function, Group by Statement, Order by and Limit

**SELECT** months*salary, **COUNT**(*) FROM employee
**GROUP BY** months*salary
**ORDER BY** months*salary **DESC**
**LIMIT** 1;

## The Blunder

HackerRank: PREPARE > SQL

Samantha was tasked with calculating the average monthly salaries for all employees in the **EMPLOYEES** table, but did not realize her keyboard’s 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: actual – miscalculated average monthly salaries), and round it up to the next integer.

**Input Format**

The **EMPLOYEES** table is described as follows:

**Note:** *Salary* is per month.

**Constraints**

**Sample Input**

**Sample Output**

`2061`

**Explanation**

The table below shows the salaries *without zeros* as they were entered by Samantha:

Samantha computes an average salary of 98.00. The *actual* average salary is 2159.00.

The resulting error between the two calculations is 2159.00 – 98.00 = 2061.00. Since it is equal to the integer 2061, it does not get rounded up.

Read up on the Ceil Function, AVG Function and Replace Function

**SELECT CEIL(AVG**(salary) - **AVG(REPLACE**(salary, '0', ''))) **FROM **employees;

## Average Population of Each Continent

HackerRank: PREPARE > SQL

Given the **CITY** and **COUNTRY** tables, query the names of all the continents (*COUNTRY.Continent*) and their respective average city populations (*CITY.Population*) rounded *down* to the nearest integer.

**Note:** *CITY.CountryCode* and *COUNTRY.Code* are matching key columns.

**Input Format**

The **CITY** and **COUNTRY** tables are described as follows:

Read up on the Floor Function and AVG Function

**SELECT **country.continent,**FLOOR(AVG**(city.population)) **FROM **city,country **WHERE **city.countrycode = country.code **GROUP BY** country.continent;

## Population Census

HackerRank: PREPARE > SQL

Given the **CITY** and **COUNTRY** tables, query the sum of the populations of all cities where the *CONTINENT* is *‘Asia’*.

**Note:** *CITY.CountryCode* and *COUNTRY.Code* are matching key columns.

**Input Format**

The **CITY** and **COUNTRY** tables are described as follows:

Read up on MySQL Sum Function and IN Operator

**SELECT SUM(**population) **FROM **city **WHERE **countrycode **IN (SELECT** code **FROM **country **WHERE **continent = 'Asia');

## Japan Population

HackerRank: PREPARE > SQL

Query the sum of the populations for all Japanese cities in **CITY**. The *COUNTRYCODE* for Japan is **JPN**.

**Input Format**

The **CITY** table is described as follows:

Read up on MySQL Sum Function

**SELECT** **SUM**(population) **FROM **city **WHERE **countrycode = 'JPN'**;**

## Average Population

HackerRank: PREPARE > SQL

Query the average population for all cities in **CITY**, rounded *down* to the nearest integer.

**Input Format**

The **CITY** table is described as follows:

Read up on MySQL AVG Function and Round Function

**SELECT** **ROUND**(**AVG**(population)) **FROM **city**;**

## Revising Aggregations – Averages

HackerRank: PREPARE > SQL

Query the average population of all cities in **CITY** where *District* is **California**.

**Input Format**

The **CITY** table is described as follows:

Read up on MySQL AVG Function

**SELECT AVG**(population) **FROM **city **WHERE **district = 'California';

## Revising Aggregations – The Sum Function

HackerRank: PREPARE > SQL

Query the total population of all cities in **CITY** where *District* is **California**.

**Input Format**

The **CITY** table is described as follows:

Read up on SUM Function

**SELECT SUM**(population) **FROM **city **WHERE **district = 'California';

## Revising Aggregations – The Count Function

HackerRank: PREPARE > SQL

Query a *count* of the number of cities in **CITY** having a *Population* larger than 100,000.

**Input Format**

The **CITY** table is described as follows:

Read up on MySQL Count Function

**SELECT** **COUNT**(name) **FROM **city **WHERE**population > 100000;

## African Cities

HackerRank: PREPARE > SQL

Given the **CITY** and **COUNTRY** tables, query the names of all cities where the *CONTINENT* is *‘Africa’*.

**Note:** *CITY.CountryCode* and *COUNTRY.Code* are matching key columns.

**Input Format**

The **CITY** and **COUNTRY** tables are described as follows:

Read up on MySQL IN Operator

**SELECT **name FROM city **WHERE **countrycode **IN (SELECT **code **FROM **country **WHERE **continent = 'Africa');

## Population Density Difference

HackerRank: PREPARE > SQL

Query the difference between the maximum and minimum populations in **CITY**.

**Input Format**

The **CITY** table is described as follows:

Read up on MIN() and MAX() Functions

**SELECT** (**MAX**(population)) - (**MIN**(population)) **FROM **city;

## Higher Than 75 Marks

HackerRank: PREPARE > SQL

Query the *Name* of any student in **STUDENTS** who scored higher than 75 *Marks*. Order your output by the *last three characters* of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending *ID*.

**Input Format**

The **STUDENTS** table is described as follows:

The *Name* column only contains uppercase (`A`

–`Z`

) and lowercase (`a`

–`z`

) letters.

**Sample Input**

**Sample Output**

```
Ashley
Julia
Belvet
```

**Explanation**

Only Ashley, Julia, and Belvet have *Marks* > 75. If you look at the last three characters of each of their names, there are no duplicates and ‘ley’ < ‘lia’ < ‘vet’.

Read up on the SELECT Statement, the WHERE Clause, the FROM Clause, the RIGHT Function and ORDER BY Clause

**SELECT **name **FROM **students **WHERE **marks > 75 **ORDER BY RIGHT**(name, 3) , id;

## Employee Salaries

HackerRank: PREPARE > SQL

Write a query that prints a list of employee names (i.e.: the *name* attribute) for employees in **Employee** having a salary greater than $2000 per month who have been employees for less than 10 months. Sort your result by ascending *employee_id*.

**Input Format**

The **Employee** table containing employee data for a company is described as follows:

where *employee_id* is an employee’s ID number, *name* is their name, *months* is the total number of months they’ve been working for the company, and *salary* is the their monthly salary.

**Sample Input**

**Sample Output**

```
Angela
Michael
Todd
Joe
```

**Explanation**

*Angela* has been an employee for 1 month and earns $3443 per month.

*Michael* has been an employee for 6 months and earns $2017 per month.

*Todd* has been an employee for 5 months and earns $3396 per month.

*Joe* has been an employee for 9 months and earns $3573 per month.

We order our output by ascending *employee_id*.

Read up on the SELECT Statement, the FROM Clause, the WHERE Clause with operators and ORDER BY Clause

**SELECT **name **FROM **employee **WHERE **salary **> **2000 **AND **months **< **10 **ORDER BY **employee_id **ASC**;

## Employee Names

HackerRank: PREPARE > SQL

Write a query that prints a list of employee names (i.e.: the *name* attribute) from the **Employee** table in alphabetical order.

**Input Format**

The **Employee** table containing employee data for a company is described as follows:

where *employee_id* is an employee’s ID number, *name* is their name, *months* is the total number of months they’ve been working for the company, and *salary* is their monthly salary.

**Sample Input**

**Sample Output**

```
Angela
Bonnie
Frank
Joe
Kimberly
Lisa
Michael
Patrick
Rose
Todd
```

Read up on the SELECT Statement, the FROM Clause and ORDER BY Clause

**SELECT **name **FROM **employee **ORDER BY **name **ASC**;

## Japanese Cities’ Names

HackerRank: PREPARE > SQL

Query the names of all the Japanese cities in the **CITY** table. The **COUNTRYCODE** for Japan is `JPN`

.

The **CITY** table is described as follows:

**SELECT** name **FROM **city **WHERE **countrycode **=** "JPN"**;**

## Japanese Cities’ Attributes

HackerRank: PREPARE > SQL

Query all attributes of every Japanese city in the **CITY** table. The **COUNTRYCODE** for Japan is `JPN`

.

The **CITY** table is described as follows:

Read up on using WHERE clause with equality operator

**SELECT** * **FROM **city **WHERE **countrycode **=** "JPN"**;**

## Select By ID

HackerRank: PREPARE > SQL

Query all columns for a city in **CITY** with the *ID* `1661`

.

The **CITY** table is described as follows:

Read up on using WHERE clause with equality operator

**SELECT** * **FROM **city **WHERE **id = 1661;

## Select All

HackerRank: PREPARE > SQL

Query all columns (attributes) for every row in the **CITY** table.

The **CITY** table is described as follows:

Read up on how to use SELECT statement to retrieve data from all columns

**SELECT** ***** **FROM **city;

## Revising the Select Query II

HackerRank: PREPARE > SQL

Query the **NAME** field for all American cities in the **CITY** table with populations larger than `120000`

. The *CountryCode* for America is `USA`

.

The **CITY** table is described as follows:

Read up on Selecting Particular Columns and MySQL Logical Operators

**SELECT** name **FROM **city **WHERE **countrycode = "USA" **AND **population > 120000;

## Revising the Select Query I

HackerRank: PREPARE > SQL

Query all columns for all American cities in the **CITY** table with populations larger than `100000`

. The **CountryCode** for America is `USA`

.

The **CITY** table is described as follows:

Read up on MySQL Logical Operators

**SELECT** * **FROM **city **WHERE **countrycode = "USA" **AND **population > 100000;

## Weather Observation Station 17

HackerRank: PREPARE > SQL

Query the *Western Longitude* (*LONG_W*)where the smallest *Northern Latitude* (*LAT_N*) in **STATION** is greater than 38.7780. Round your answer to 4 decimal places.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the ROUND Function, MIN Function and subquery with comparison operators

**SELECT ROUND**(LONG_W, 4) **FROM **station **WHERE **LAT_N **= **(**SELECT MIN**(LAT_N) **FROM **station **WHERE **LAT_N **> **38.7780);

## Weather Observation Station 16

HackerRank: PREPARE > SQL

Query the smallest *Northern Latitude* (*LAT_N*) from **STATION** that is greater than 38.7780. Round your answer to 4 decimal places.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the ROUND Function, MIN Function and subquery with comparison operators

**SELECT ROUND**(LAT_N, 4) **FROM **station **WHERE **LAT_N **= **(**SELECT MIN**(LAT_N) **FROM **station **WHERE **LAT_N **> **38.7780);

## Weather Observation Station 15

HackerRank: PREPARE > SQL

Query the *Western Longitude* (*LONG_W*) for the largest *Northern Latitude* (*LAT_N*) in **STATION** that is less than 137.2345. Round your answer to 4 decimal places.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the ROUND Function, MAX Function and subquery with comparison operators

**SELECT ROUND**(LONG_W, 4) **FROM **station **WHERE **LAT_N **= **(**SELECT MAX**(LAT_N) **FROM **station **WHERE **LAT_N **< **137.2345);

## Weather Observation Station 14

HackerRank: PREPARE > SQL

Query the greatest value of the *Northern Latitudes* (*LAT_N*) from **STATION** that is less than 137.2345. Truncate your answer to 4 decimal places.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the TRUNCATE Function, MAX Function and comparison operators

**SELECT TRUNCATE**(**MAX**(LAT_N), 4) **FROM **station **WHERE **LAT_N **< **137.2345;

## Weather Observation Station 13

HackerRank: PREPARE > SQL

Query the sum of *Northern Latitudes* (*LAT_N*) from **STATION** having values greater than 38.7880 and less than 137.2345. Truncate your answer to 4 decimal places.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude

Read up on the TRUNCATE Function, the SUM Function, the WHERE clause combined with operators

**SELECT TRUNCATE**(**SUM**(LAT_N), 4) **FROM **station **WHERE **LAT_N > 38.7880 **AND **LAT_N < 137.2345;

## Weather Observation Station 12

HackerRank: PREPARE > SQL

Query the list of *CITY* names from **STATION** that *do not start* with vowels and *do not end* with vowels. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause, Regular Expressions and operators

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **NOT REGEXP** "^[a,e,i,o,u]" **AND **city **NOT REGEXP** "[a,e,i,o,u]$";

## Weather Observation Station 11

HackerRank: PREPARE > SQL

Query the list of *CITY* names from **STATION** that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause and Regular Expressions

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **NOT REGEXP** "^[a,e,i,o,u].*[a,e,i,o,u]$";

## Weather Observation Station 10

HackerRank: PREPARE > SQL

Query the list of *CITY* names from **STATION** that *do not end* with vowels. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause and Regular Expressions

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **NOT REGEXP** "[a,e,i,o,u]$";

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

## Weather Observation Station 9

HackerRank: PREPARE > SQL

uery the list of *CITY* names from **STATION** that *do not start* with vowels. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause and Regular Expressions

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **NOT REGEXP** "^[a,e,i,o,u]";

## Weather Observation Station 8

HackerRank: PREPARE > SQL

Query the list of *CITY* names from **STATION** which have vowels (i.e., *a*, *e*, *i*, *o*, and *u*) as both their first *and* last characters. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause and Regular Expressions

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **REGEXP** "^[a,e,i,o,u].*[a,e,i,o,u]$";

## Weather Observation Station 7

HackerRank: PREPARE > SQL

Query the list of *CITY* names ending with vowels (a, e, i, o, u) from **STATION**. Your result *cannot* contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause and Regular Expressions

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **REGEXP** "[a,e,i,o,u]$";

## Weather Observation Station 6

HackerRank: PREPARE > SQL

Query the list of *CITY* names starting with vowels (i.e., `a`

, `e`

, `i`

, `o`

, or `u`

) from **STATION**. Your result *cannot* contain duplicates.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

Read up on the DISCTINCT clause and Regular Expressions

**SELECT DISTINCT**(city) **FROM **station **WHERE **city **REGEXP** "^[a,e,i,o,u]";

## Weather Observation Station 5

HackerRank: PREPARE > SQL

Query the two cities in **STATION** with the shortest and longest *CITY* names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

The **STATION** table is described as follows:

where **LAT_N** is the northern latitude and **LONG_W** is the western longitude.

**Sample Input**

For example, **CITY** has four entries: **DEF, ABC, PQRS** and **WXY**.

**Sample Output**

```
ABC 3
PQRS 4
```

**Explanation**

When ordered alphabetically, the **CITY** names are listed as **ABC, DEF, PQRS,** and **WXY**, with lengths 3, 3, 4, and 3. The longest name is **PQRS**, but there are 3 options for shortest named city. Choose **ABC**, because it comes first alphabetically.

**Note**

You can write two separate queries to get the desired output. It need not be a single query.

Read up on the LENGTH Function, the IN operator, the MIN and MAX Functions, the UNION Clause and ORDER BY and LIMIT

**SELECT **city,**LENGTH**(city)

**FROM **station

**WHERE LENGTH**(city) **IN **(

**SELECT MIN**(**LENGTH**(city))

**FROM **station

**UNION**

**SELECT MAX**(**LENGTH**(city))

**FROM **station

)

**ORDER BY LENGTH**(city) DESC,city ASC **LIMIT **2;

## Weather Observation Station 4

HackerRank: PREPARE > SQL

Find the difference between the total number of **CITY** entries in the table and the number of distinct **CITY** entries in the table.

The **STATION** table is described as follows:

where **LAT_N** is the northern latitude and **LONG_W** is the western longitude.

For example, if there are three records in the table with **CITY** values ‘New York’, ‘New York’, ‘Bengalaru’, there are 2 different city names: ‘New York’ and ‘Bengalaru’. The query returns 1, because total number of records – number of unique city names = 3 – 2 = 1.

Read up on the COUNT Function

**SELECT COUNT**(city) - **COUNT**(**DISTINCT **city) **FROM **station;

## Weather Observation Station 3

HackerRank: PREPARE > SQL

Query a list of **CITY** names from **STATION** for cities that have an even **ID** number. Print the results in any order, but exclude duplicates from the answer.

The **STATION** table is described as follows:

where **LAT_N** is the northern latitude and **LONG_W** is the western longitude.

Read up on using the DISTINCT clause together with the SELECT statement and how to use modulus operator to find even numbers

**SELECT DISTINCT**(city) **FROM **station **WHERE **id % 2 = 0;

## Weather Observation Station 2

HackerRank: PREPARE > SQL

uery the following two values from the **STATION** table:

- The sum of all values in
*LAT_N*rounded to a scale of 2 decimal places. - The sum of all values in
*LONG_W*rounded to a scale of 2 decimal places.

**Input Format**

The **STATION** table is described as follows:

where *LAT_N* is the northern latitude and *LONG_W* is the western longitude.

**Output Format**

Your results must be in the form:

```
lat lon
```

where lat is the sum of all values in *LAT_N* and lon is the sum of all values in *LONG_W*. Both results must be rounded to a scale of 2 decimal places.

Read up on the ROUND Function and the SUM Function

**SELECT ROUND**(**SUM**(LAT_N), 2), **ROUND**(**SUM**(LONG_W), 2) **FROM **STATION;

## Weather Observation Station 1

HackerRank: PREPARE > SQL

Query a list of **CITY** and **STATE** from the **STATION** table.

The **STATION** table is described as follows:

Read up on Selecting Particular Columns

**SELECT** city, state **FROM **station;