Sql Hackerrank

Question 1

Write function RemoveExclamationMarks which removes all exclamation marks from a given string.

Answer 1

SELECT s, REPLACE(s, ‘!’, ‘’) AS res
FROM removeexclamationmarks

REPLACE(s, ‘!’, ‘’): This function replaces all occurrences of the exclamation mark ‘!’ in the string s with an empty string ‘’.
REPLACE is a built-in SQL function that takes three arguments:
The original string (s).
The substring you want to replace (‘!’).
The replacement substring (in this case, an empty string ‘’).

Question 2

In your application, there is a section for adults only. You need to get a list of names and ages of users from the users table, who are 18 years old or older.

users table schema

name
age

Answer 2

SELECT name, age
FROM users
WHERE age >= 18

Question 3

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.
ID num
Name varchar2 (17)
countrycode varchar2
district varchar2
pop num

Answer 3

select *
from City
where CountryCode=’USA’ and Population>100000

Question 4

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.
ID num
Name varchar2 (17)
countrycode varchar2
district varchar2
pop num

Answer 4

select NAME
from CITY
where countrycode=’USA’ and Population >120000

Question 5

Query all columns (attributes) for every row in the CITY table.
ID num
Name varchar2 (17)
countrycode varchar2
district varchar2
pop num

Answer 5

select *
from city

Question 6

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

Answer 6

select *
from city
where Id=1661

Question 7

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

Answer 7

select *
from city
where countrycode=”JPN”

Question 8

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

Answer 8

select NAME
from CITY
where COUNTRYCODE= ‘JPN’

Question 9

Query a list of CITY and STATE from the STATION table.Please append a semicolon “;” at the end of the query and enter your query in a single line to avoid error.

Answer 9

Select CITY, STATE from STATION ORDER BY CITY, STATE;

Question 10

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.

Answer 10

SELECT DISTINCT city
FROM STATION
WHERE MOD(id, 2) = 0;

or

SELECT DISTINCT city
FROM STATION
WHERE RIGHT(id, 1) IN (‘0’, ‘2’, ‘4’, ‘6’, ‘8’);

Question 11

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

Answer 11

Select count(CITY) - count(DISTINCT CITY)
from station

Question 12

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.

Answer 12

SELECT City, LENGTH(City)
FROM (SELECT City
FROM Station
ORDER BY LENGTH(City), City)
WHERE ROWNUM = 1;

SELECT City, LENGTH(City)
FROM (SELECT City
FROM Station
ORDER BY LENGTH(City) DESC, City)
WHERE ROWNUM = 1;

Question 13

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

Answer 13

SELECT DISTINCT City FROM Station WHERE UPPER(SUBSTR(City, 1, 1)) IN (‘A’, ‘E’, ‘I’, ‘O’, ‘U’);

Question 14

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

Answer 14

SELECT DISTINCT City
FROM Station
WHERE City LIKE ‘%a’ or City LIKE ‘%e’ or City LIKE ‘%i’ or City LIKE ‘%o’ or City LIKE ‘%u’;

Question 15

What are the most common aggregations?

Answer 15

MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column

Question 16

When to use like?

Answer 16

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;

Question 17

Because Nathan knows it is important to stay hydrated, he drinks 0.5 litres of water per hour of cycling.

You get given the time in hours, write a sql query that returns the number of litres Nathan will drink, rounded to the smallest value.

Answer 17

SELECT
id,
hours,
FLOOR(hours * 0.5) AS liters
FROM
cycling;

Question 18

what is FLOOR?

Answer 18

FLOOR(): This function rounds down the calculated value to the nearest whole number.

Question 19

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

Answer 19

SELECT DISTINCT City FROM Station WHERE UPPER(SUBSTR(City, 1, 1)) NOT IN (‘A’, ‘E’, ‘I’, ‘O’, ‘U’);