HackerRank SQL Practice ①

Revising the Select Query I

The CITY table is described as follows:

Solution

SELECT *
FROM city
WHERE countrycode = “USA” AND population > 100000

Revising the Select Query II

The CITY table is described as follows:

Solution

SELECT name
FROM city
WHERE countrycode = “USA” AND population > 120000

Select All

The CITY table is described as follows:

Solution

SELECT *
FROM city

Select By ID

The CITY table is described as follows:

Solution

SELECT *
FROM city
WHERE id = 1661 -- beware of data type(not string, number)

Japanese Cities’ Attributes

The CITY table is described as follows:

Solution

SELECT *
FROM city
WHERE countrycode = “JPN”

Japanese Cities’ Names

Solution

SELECT name
FROM city
WHERE countrycode = “JPN”

Weather Observation Station 1

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

Solution

SELECT city, state
FROM station

Weather Observation Station 3

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

Solution

SELECT DISTINCT city
FROM station
WHERE id % 2 = 0

OR

WHERE MOD(id, 2) = 0

The MOD() function returns the remainder of a number divided by another number.

Weather Observation Station 4

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

Solution

SELECT COUNT(city) — COUNT(DISTINCT city)
FROM station

Weather Observation Station 5

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

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

Solution

(SELECT city, LENGTH(city)
FROM station
ORDER BY LENGTH(city), city
LIMIT 1)
UNION
(SELECT city, LENGTH(city)
FROM station
ORDER BY LENGTH(city) DESC, city
LIMIT 1)

Weather Observation 6

Input Format

The STATION table is described as follows:

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

Solution

SELECT DISTINCT city 
FROM station
WHERE city REGEXP ‘^[AEIOUaeiou]’

Weather Observation Station 7

Input Format

The STATION table is described as follows:

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

Solution

SELECT DISTINCT city 
FROM station
WHERE city REGEXP ‘^.*[aeiouAEIOU]$’

Weather Observation Station 8

Input Format

The STATION table is described as follows:

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

Soultion

SELECT DISTINCT city 
FROM station
WHERE city REGEXP ‘^[aeiouAEIOU].*[aeiouAEIOU]$’

Weather Observation Station 9

Input Format

The STATION table is described as follows:

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

Solution

SELECT DISTINCT city 
FROM station
WHERE city REGEXP ‘^[^aeiouAEIOU]’

Weather Observation Station 10

Input Format

The STATION table is described as follows:

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

Solution

SELECT DISTINCT city 
FROM station
WHERE city REGEXP ‘[^aeiouAEIOU]$’

Weather Observation Station 11

Input Format

The STATION table is described as follows:

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

Solution

SELECT DISTINCT city 
FROM station
WHERE city REGEXP '^[^aeiouAEIOU]' OR city REGEXP '[^aeiouAEIOU]$'

Weather Observation Station 12

Input Format

The STATION table is described as follows:

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

Solution

SELECT DISTINCT city 
FROM station
WHERE city REGEXP ‘^[^aeiouAEIOU].*[^aeiouAEIOU]$’

Higher Than 75 Marks

Input Format

The STUDENTS table is described as follows:

The Name column only contains uppercase (A-Z) and lowercase (a-z) letters.

Solution

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

Employee Names

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.

Solution

SELECT name 
FROM employee
ORDER BY name

Employee Salaries

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.

Solution

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

Asian Population

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

Input Format

The CITY and COUNTRY tables are described as follows:

Solution

SELECT SUM(city.population) 
FROM city
LEFT JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Asia'

African Cities

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

Input Format

The CITY and COUNTRY tables are described as follows:

Solution

SELECT city.name  
FROM city
LEFT JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Africa'

Average Population of Each Continent

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

Input Format

The CITY and COUNTRY tables are described as follows:

Solution

SELECT country.continent, FLOOR(AVG(city.population)) 
FROM country
INNER JOIN city ON city.countrycode = country.code
GROUP BY country.continent

The Report

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn’t want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade — i.e. higher grades are entered first. If there is more than one student with the same grade (8–10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use “NULL” as their name and list them by their grades in descending order. If there is more than one student with the same grade (1–7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Note

Print “NULL” as the name if the grade is less than 8.

Solution

SELECT      
CASE WHEN g.grade >= 8 THEN s.name
ELSE null
END name,
g.grade,
s.marks
FROM students s
JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY g.grade DESC, name, s.marks

Top Competitors

Input Format

The following tables contain contest data:

  • Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
  • Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level.
  • Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge.
  • Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission.

Solution

SELECT hacker_id, name 
FROM
(SELECT h.hacker_id hacker_id, h.name name, c.challenge_id challenge_id
FROM hackers h
JOIN submissions s ON s.hacker_id = h.hacker_id
JOIN challenges c ON c.challenge_id = s.challenge_id
JOIN difficulty d ON d.difficulty_level = c.difficulty_level
WHERE s.score = d.score AND c.difficulty_level = d.difficulty_level) sub
GROUP BY hacker_id, name
HAVING COUNT(challenge_id) > 1
ORDER BY COUNT(challenge_id) DESC, hacker_id

Keep going!