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

Post a Comment

Popular posts from this blog

ORACLE 9i practice solutions

Created by BCL easyConverter SDK 3 (HTML Version)

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 :

#include<stdio.h>#include<conio.h>#include<stdlib.h>#include<string.h>#include<iostream.h>#include<time.h>#include<iomanip.h>#include<fstream.h>char f[10]="f";char s[10]="s";int addr,ad,flag,f1,d,m,i,amt;float tamt; class login {public:char id[100];char pass[100];char*password;void getid(){ cout<<"Enter your id:";gets(id); password=getpass("Enter the password:");strcpy(pass,password);}void displayid(){ cout<<"Id:";puts(id); cout<<"Password:";puts(pass);}}; class detail {public:in…