Skip to main content

Hackerrank > SQL > Aggregation

Aggregation · Hacker Rank

Hacker Rank My personal attemp

Aggregation

01-Average Population

Given a City table, whose fields are described as
+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+
you have to print the average population of all cities, rounded down to the nearest integer.
Solution
SELECT FLOOR (AVG(population))
FROM city;


                               Aggregation

01-Average Population

Given a City table, whose fields are described as
+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+
you have to print the average population of all cities, rounded down to the nearest integer.
Solution
SELECT FLOOR (AVG(population))
FROM city;

02-Japan Population

Given a City table, whose fields are described as
+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+
you have to print the sum of population of all the cities of Japan. The CountryCode for Japan is "JPN".
Solution
SELECT SUM(population)
FROM city
WHERE countrycode = 'JPN';

03-Population Density Difference

Given a City table, whose fields are described as
+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+
print the difference between the maximum and minimum city populations.
Solution
SELECT MAX(population) - MIN(population)
FROM city;

04-Weather Observation Station 2

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Write a query to print the sum of LATN and the sum of LONGW separated by space, up to 2 decimal places
Solution
SELECT ROUND(SUM(latn),2) || ' ' || ROUND(SUM(longw),2)
FROM station;

05-Weather Observation Station 13

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Write a query to find the sum of the Northern Latitudes having values greater than 38.7880 and less than 137.2345 up to 4 decimal places.
Solution
SELECT ROUND(SUM(latn),4)
FROM station
WHERE  latn > 38.7880 AND lat_n < 137.2345;

06-Weather Observation Station 14

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Write a query to find the greatest value of the Northern Latitudes less than 137.2345 up to 4 decimal places.
Solution
SELECT MAX(ROUND(latn,4))
FROM station
WHERE latn < 137.2345;

07-Weather Observation Station 15

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Write a query to find the corresponding Western Longitude value for the greatest value of the Northern Latitudes less than 137.2345 up to 4 decimal places.
Solution
SELECT ROUND(longw,4)
FROM station
WHERE latn = (SELECT MAX(latn) FROM station WHERE latn < 137.2345);

08-Weather Observation Station 16

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Write a query to find the smallest value of the Northern Latitudes greater than 38.7780 up to 4 decimal places.
Solution
SELECT ROUND(MIN(latn),4)
FROM station
WHERE latn > 38.7780;

09-Weather Observation Station 17

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Write a query to find the corresponding Western Longitude to the smallest value of the Northern Latitudes greater than 38.7780 up to 4 decimal places.
Solution
SELECT ROUND(longw,4)
FROM station
WHERE latn = (SELECT MIN(latn) FROM station WHERE latn > 38.7780);

10-Weather Observation Station 18

Given a table STATION that holds data for five fields namely IDCITYSTATE,NORTHERN LATITUDE and WESTERN LONGITUDE.
+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+
Consider P1(a, b) and P2(c, d) be two points on 2D plane, where (a, b) be minimum and maximum values of Northern Latitude and (c, d) be minimum and maximum values of Western Longitude. Write a query to print the Manhattan Distance between points P1 and P2 up to 4 decimal digits.
Solution
SELECT ROUND(longw,4)
FROM station
WHERE latn = (SELECT MIN(latn) FROM station WHERE latn > 38.7780);

Comments

  1. For Weather Observation Station 18 - in SQL Server
    SELECT CAST( (MAX(LONG_W)-MAX(LAT_N))+(MIN(LONG_W)-MIN(LAT_N)) AS DECIMAL(8,4)) FROM STATION;

    ReplyDelete
  2. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete

Post a Comment

Popular posts from this blog

ORACLE 9i practice solutions

Created by BCL easyConverter SDK 3 (HTML Version)

Zoho Puzzle Questions With Answers

Measuring Time Logic Puzzle You are given with two ropes with variable width. However if we start burning both the ropes, they will burn at exactly same time i.e. an hour. The ropes are non-homogeneous in nature. You are asked to measure 45 minutes by using these two ropes.

How can you do it?

Please note that you can’t break the rope in half as it is being clearly stated that the ropes are non-homogeneous in nature.
Answer & Explanation Solution: 45 minutes

Explanation :
All you have to do is burn the first rope from both the ends and the second rope from one end only simultaneously. The first rope will burn in 30 minutes (half of an hour since we burned from both sides) while the other rope would have burnt half. At this moment, light the second rope from the other end as well. Where, the second rope would have taken half an hour more to burn completely, it will take just 15 minutes as we have lit it from the other end too.

Thus you have successfully calculated 30+15 = 45 minutes …

Hackerrank > SQL > Basic Select

Select
01-Select All
Given a City table, whose fields are described as +-------------+----------+ | Field       | Type     | +-------------+----------+ | ID          | int(11)  | | Name        | char(35) | | CountryCode | char(3)  | | District    | char(20) | | Population  | int(11)  | +-------------+----------+
write a query that will fetch all columns for every row in the table.

My Solution
SELECT*FROM city;
---------------------------------------------------------------------------------
02-Select by ID
Given a City table, whose fields are described as