### Hackerrank > SQL > Aggregation

Aggregation · Hacker Rank

# 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

## Given a table STATION that holds data for five fields namely ID, CITY, STATE,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);``````

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;

### 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

### Zoho Interview | Set 1 (Advanced Programming Round)

Third Round: (Advanced Programming Round) Here they asked us to create a “Railway reservation system” and gave us 4 modules. The modules were:
1. Booking
2. Availability checking
3. Cancellation
4. Prepare chart
We were asked to create the modules for representing each data first and to continue with the implementation phase.

My Solution :