# 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

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.

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

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

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

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

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

``SELECT COUNT(name) FROM city WHEREpopulation > 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:

``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’.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

``SELECT city,LENGTH(city)FROM stationWHERE LENGTH(city) IN (SELECT MIN(LENGTH(city))FROM stationUNIONSELECT 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.

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

1. The sum of all values in LAT_N rounded to a scale of 2 decimal places.
2. 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:

``SELECT city, state FROM station;``