### 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) |
+-------------+----------+
```

**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;
```

*City*table, whose fields are described as

```
+-------------+----------+
| Field | Type |
+-------------+----------+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |
| District | char(20) |
| Population | int(11) |
+-------------+----------+
```

**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';
```

*City*table, whose fields are described as

```
+-------------+----------+
| Field | Type |
+-------------+----------+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |
| District | char(20) |
| Population | int(11) |
+-------------+----------+
```

**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;
```

*City*table, whose fields are described as

```
+-------------+----------+
| Field | Type |
+-------------+----------+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |
| District | char(20) |
| Population | int(11) |
+-------------+----------+
```

**Solution**

```
SELECT MAX(population) - MIN(population)
FROM city;
```

## 04-Weather Observation Station 2

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

Write a query to print the sum of LAT*N and the sum of LONG*W separated by space, up to 2 decimal places
**Solution**
`SELECT ROUND(SUM(lat`*n**),2) || ' ' || ROUND(SUM(long*w),2)
FROM station;

*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 |
+-------------+------------+
```

*N and the sum of LONG*W separated by space, up to 2 decimal places

**Solution**

`SELECT ROUND(SUM(lat`*n**),2) || ' ' || ROUND(SUM(long*w),2)
FROM station;

## 05-Weather Observation Station 13

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

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(lat`*n**),4)
FROM station
WHERE lat*n > 38.7880 AND lat_n < 137.2345;

*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 |
+-------------+------------+
```

**Solution**

`SELECT ROUND(SUM(lat`*n**),4)
FROM station
WHERE lat*n > 38.7880 AND lat_n < 137.2345;

## 06-Weather Observation Station 14

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

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(lat`*n**,4))
FROM station
WHERE lat*n < 137.2345;

*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 |
+-------------+------------+
```

**Solution**

`SELECT MAX(ROUND(lat`*n**,4))
FROM station
WHERE lat*n < 137.2345;

## 07-Weather Observation Station 15

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

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(long`*w**,4)
FROM station
WHERE lat*n = (SELECT MAX(lat*n**) FROM station WHERE lat*n < 137.2345);

*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 |
+-------------+------------+
```

**Solution**

`SELECT ROUND(long`*w**,4)
FROM station
WHERE lat*n = (SELECT MAX(lat*n**) FROM station WHERE lat*n < 137.2345);

## 08-Weather Observation Station 16

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

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(lat`*n**),4)
FROM station
WHERE lat*n > 38.7780;

*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 |
+-------------+------------+
```

**Solution**

`SELECT ROUND(MIN(lat`*n**),4)
FROM station
WHERE lat*n > 38.7780;

## 09-Weather Observation Station 17

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

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(long`*w**,4)
FROM station
WHERE lat*n = (SELECT MIN(lat*n**) FROM station WHERE lat*n > 38.7780);

*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 |
+-------------+------------+
```

**Solution**

`SELECT ROUND(long`*w**,4)
FROM station
WHERE lat*n = (SELECT MIN(lat*n**) FROM station WHERE lat*n > 38.7780);

## 10-Weather Observation Station 18

##
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(long`*w**,4)
FROM station
WHERE lat*n = (SELECT MIN(lat*n**) FROM station WHERE lat*n > 38.7780);

*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 |
+-------------+------------+
```

**Solution**

`SELECT ROUND(long`*w**,4)
FROM station
WHERE lat*n = (SELECT MIN(lat*n**) FROM station WHERE lat*n > 38.7780);

For Weather Observation Station 18 - in SQL Server

ReplyDeleteSELECT CAST( (MAX(LONG_W)-MAX(LAT_N))+(MIN(LONG_W)-MIN(LAT_N)) AS DECIMAL(8,4)) FROM STATION;

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

ReplyDelete