SQL — 105 (Basics) Aggregation

Introduction

This series is the revision of SQL concepts. We are doing the basic questions, then intermediate, and so on.

All questions use the aggregation concept. You can learn more here.

Previous Posts

1. SQL 101
2. SQL 102 (Basics)
3. SQL — 103 (Basics)
4. SQL — 104 (Basics) Aggregation

STATION Table description

┌────────┬─────────────┐
│ Field  │    Type     │
├────────┼─────────────┤
│ ID     │ NUMBER      │
│ CITY   │ VARCHAR(21) │
│ STATE  │ VARCHAR(2)  │
│ LAT_N  │ NUMBER      │
│ LONG_W │ NUMBER      │
└────────┴─────────────┘

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

Question 1

[Ref: Hackerrank]

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

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.

Solution:

SELECT ROUND(SUM(LAT_N ),2), ROUND(SUM(LONG_W ),2) FROM STATION;

Question 2

[Ref: Hackerrank]

Get Ravi raj’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

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.

Solution:

SELECT ROUND(SUM(LAT_N),4) FROM STATION WHERE LAT_N > 38.7880 and LAT_N < 137.2345;

Question 3

[Ref: Hackerrank]

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.

Solution:

SELECT ROUND(MAX(LAT_N),4) FROM STATION WHERE LAT_N < 137.2345;

Question 4

[Ref: Hackerrank]

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.

Solution:

SELECT ROUND(LONG_W,4) FROM STATION WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);

Explanation:

1. SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345 will give the largest Northern Latitude in STATION that is less than 137.2345

Question 5

[Ref: Hackerrank]

Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780 . Round your answer to 4 decimal places.

Solution:

SELECT ROUND(MIN(LAT_N),4) FROM STATION WHERE LAT_N > 38.7780

Question 6

[Ref: Hackerrank]

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.

Solution:

SELECT ROUND(LONG_W,4) FROM STATION WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);