Solution for All SQL practice questions on Hackerrank.
select * from city
where population > 100000 and countrycode = 'USA' ;select name from city
where population > 120000 and countrycode = 'USA' ;select * from city ;select * from city where id = 1661 ;select * from city
where countrycode = 'JPN' ;select name from city
where countrycode = 'JPN' ;seelct city ,state from station ;select distinct city from station where ID%2 = 0 ;select count(city) - count(distinct city) from station ;Select city ,length(city) from station group by city order by length(city) ,city limit 1;
select city, length(city) from station group by city order by length(city) desc ,city limit 1;Select distinct city from station where city regexp '^[aeiou]' ;Select distinct city from station where city regexp '[aeiou]$';Select distinct city from station
where city regexp '^[aeiou].*[aeiou]$' ;Select Distinct city from station
where city regexp '^[^aeiou]' ;Select distinct city from station
where city regexp "[^aeiou]$" ;Select distinct city from Station
where city regexp '^[^aeiou]|[^aeiou]$' ;Select distinct city from station
where city regexp '^[^aeiou].*[^aeiou]$' ;Select Name from students
where marks > 75
order by right(name,3) ,id ;Select name from employee
order by name asc ;Select name from emplyee
where salary > 2000 and months < 10
order by employee_id asc ;Select CASE
When 2*greatest(A,B,C) >= (A+B+C) Then "Not A Triangle"
When A = B and A = c Then "Equilateral"
When A = B or B = C or A = C Then "Isosceles"
Else "Scalene"
End
from Triangles ;Select concat(name ,'(',left(occupation),')') from occupations
order by name ;
Select concat('There are a total of ',count(occupation),' ',lower(occupation),'s.') from occupations
group by occupation
order by count(occupation) ;Select count(name) from city where population > 100000;Select sum(population) from city
where city = 'California' ;Select avg(population) from city
where district = 'California' ;Select round( avg(population)) from city ;Select sum(population) from city
where countrycode = 'JPN' ;Select (max(population) - min(population)) from city ;Select ceil(avg(salary) - (select avg(replace(salary,0,"")) from employees) ) from employees ;Select salary*months as earnings ,count(*) from city
group by earnings
order by earnings desc
limit 1 ;Select round(sum(lat_n) ,2), round(sum(long_w), 2) from station ;Select round(sum(lat_n), 4) from station
where lat_n > 38.7880 and lat_n < 137.2345 ;Select round(max(lat_n) ,4) from station
where lat_n < 137.2345 ;Select round(long_w,4) from station
where lat_n < 137.2345
order by 137.2345
limit 1 ;Select round(lat_n, 4) from station
where lat_n > 38.7780
order by lat_n asc
limit 1 ;Select round(lat_n, 4) from station
where lat_n > 38.7780
order by lat_n asc
limit 1 ;Select round(((max(lat_n) - (min(lat_n))) + (max(long_w)-min(long_w))),4) from station ;Select round(sqrt((pow(max(lat_n)-min(lat_n)),2) + pow((max(long_w)-min(long_w)),2)),4) from station ;Select sum(city.population)
from city inner join country
on city.countrycode = country.code
where country.continent = 'Asia' ;Select city.name
from city inner join country
on city.countrycode = country.code and country.continent = 'Africa' ;Select country.continent ,floor(city.population)
from city inner join country
on city.countrycode = country.code
group by country.continent ;set @number = 21;
select repeat('* ', @number := @number - 1) from information_schema.tables ;set @number = 0 ;
select repeat('* ', @number := @number + 1) from information_schema.tables
where @number < 20;Think of the information_schema.tables as a dummy table with no real use in the statement except to satisfy the MySQL condition that every SELECT must have a FROM ; Also, it contains as many rows as you could possibly need so it is a neat trick for incrementing variables. Just select the incrementing variable and specify the where condition on your incrementing variable.
select round(x.lat_n,4) from station x, station y
group by x.lat_n
having sum(sign(1-sign(x.lat_n - y.lat_n))) = (count(*)+1)/2 ;select wands.id , wands_property.age, wands.coins_needed ,wands.power
from wands join wands_property
on wands.code = wands_property.code
where (wands_property.age, wands.power, wands.coins_needed) in (
select wands_property.age , wands.power, min(wands.coins_needed)
from wands join wands_property
on wands.code = wands_property.code
where is_evil = 0
group by wands_property.age ,wands.power)
order by wands.power desc, wands_property.age desc ;Select a.x, a.y
from functions a, functions b
where a.x = b.y and a.y = b.x
group by a.x, a.y
having count(a.x) > 1 or a.x<a.y
order by a.x asc ;The criteria in the having clause allows us to prevent duplication in our output while still achieving our goal of finding mirrored pairs. We have to treat our pairs where a.x = a.y and a.x <> a.y differently to capture both. The first criteria handles pairs where a.x = a.y and the 2nd criteria handles pairs where a.x <> a.y, which is why the or operator is used.
The first part captures records where a.x = a.y. The 'count(a.x) > 1' requires there to be at least two records of a mirrored pair to be pulled through. Without this a pair would simply match with itself (since it's already it's own mirrored pair) and be pulled through incorrectly when you join the table on itself.
The 2nd part matches the remaining mirrored pairs. It's important to note that for this challenge, the mirrored match of (a.x,b.y) is considered a duplicate and excluded from the final output. You can see this in the sample output where (20, 21) is outputted, but not (21,20). The 'or a.x < a.y' criteria allows us to pull all those pairs where a.x does not equal a.y, but where a.x is also less than a.y so we don't end up with the mirrored paired duplicate.
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.