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)

C Questions

C Questions
C Questions

Note : All the programs are tested under Turbo C/C++ compilers.
It is assumed that,
Programs run under DOS environment, The underlying machine is an x86 system, Program is compiled using Turbo C/C++ compiler.
The program output may depend on the information based on this assumptions (for example sizeof(int) == 2 may be assumed).
Predict the output or error(s) for the following:

void main()
{
int const * p=5; printf("%d",++(*p));
}
Answer:
Compiler error: Cannot modify a constant value.
Explanation:
p is a pointer to a "constant integer". But we tried to change the value of the "constant integer".
main()
{
char s[ ]="man"; int i;
for(i=0;s[ i ];i++)
printf("\n%c%c%c%c",s[ i ],*(s+i),*(i+s),i[s]);
}
Answer: mmmm
aaaa nnnn
Explanation

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…