Skip to main content

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

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

you have to print all the details of the city with ID is 1661.


My Solution

SELECT * FROM city WHERE id = '1661';

---------------------------------------------------------------------------------

03-Japanese Cities Detail

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 all the details of all the cities of Japan. The CountryCode for Japan is "JPN".

My Solution

SELECT * FROM city WHERE countrycode = 'JPN';

---------------------------------------------------------------------------------

04-Japanese Cities Name

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 name of all the cities of Japan. The CountryCode for Japan is "JPN".

My Solution

SELECT name FROM city WHERE countrycode = 'JPN';


---------------------------------------------------------------------------------

05-Weather Observation Station 1

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 list of CITY and STATE in lexicographical order of city and state, i.e., if there are two or more cities with same name arrange these by lexicographical order of state.

My Solution

SELECT city, state FROM station ORDER BY city ASC, state ASC;

---------------------------------------------------------------------------------

06-Weather Observation Station 3

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 list of CITY in lexicographical order for even ID only. Do not print duplicates.

My Solution

SELECT DISTINCT city FROM station WHERE MOD (id, 2) = 0 ORDER BY cit
y ASC;

--------------------------------------------------------------------------------

07-Weather Observation Station 4

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

Let NUM be no. of cities and NUM unique be no. of unique cities, then write a query to print the value of NUM - NUMunique


My Solution

SELECT COUNT(CITY)-COUNT(UNIQUE CITY) FROM STATION;

---------------------------------------------------------------------------------

08-Weather Observation Station 5

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

Let |city| be the length of the city, write a query to print two lines: 1. First line is city1 and |city1| separated by space, where |city1| is the possible minimum value. 2. Second line is city2 and |city2| separated by space, where |city2| is the possible maximum value. If there are more than one possible cities print the lexicographical smallest.

My Solution

SELECT city || ' ' || LENGTH FROM
(SELECT city , LENGTH(city) AS LENGTH FROM station WHERE LENGTH(city) =
(SELECT MAX(LENGTH(city)) FROM station) ORDER BY city ) WHERE ROWNUM <= 1
UNION
SELECT city || ' ' || LENGTH FROM
(SELECT city, LENGTH(city) AS LENGTH FROM station WHERE LENGTH(city) =
(SELECT MIN(LENGTH(city)) FROM station) ORDER BY city) WHERE ROWNUM <= 1;


---------------------------------------------------------------------------------

09-Weather Observation Station 6

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 list of CITY that start with vowels (a, e, i, o, u) in lexicographical order. Do not print duplicates.

My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '^[aeiou].');
ORDER BY CITY;


---------------------------------------------------------------------------------

10-Weather Observation Station 7

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 list of CITY that ends with vowels (a, e, i, o, u) in lexicographical order. Do not print duplicates.

My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '.[aeiou]$')
ORDER BY CITY;


---------------------------------------------------------------------------------

11-Weather Observation Station 8

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 list of CITY that starts with vowels and ends at vowels in lexicographical order. Do not print duplicates.

My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '^[aeiou].[aeiou]$')
ORDER BY CITY;


---------------------------------------------------------------------------------

12-Weather Observation Station 9

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 list of CITY that does not start with vowels in lexicographical order. Do not print duplicates.

My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '^[^aeiou].')
ORDER BY CITY;

---------------------------------------------------------------------------------

13-Weather Observation Station 10

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 list of CITY that does not end with vowels in lexicographical order. Do not print duplicates.

My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '.[^aeiou]$')
ORDER BY CITY;

---------------------------------------------------------------------------------


14-Weather Observation Station 11

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 list of CITY that does not start with vowels or does not end with vowels in lexicographical order. Do not print duplicates.


My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '(^[^aeiou].|.*[^aeiou]$)')
ORDER BY CITY;

---------------------------------------------------------------------------------

15-Weather Observation Station 12

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 list of CITY that does not start with vowels and does not end with vowels in lexicographical order. Do not print duplicates.


My Solution

/* Oracle /
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(LOWER(city), '^[^aeiou].[^aeiou]$')
ORDER BY CITY;


Comments

  1. IN 07-Weather Observation Station 4

    use:- DISTINCT instead of UNIQUE

    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 …