MS-SQL Database

1. 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)  |

+-------------+----------+

 

1. Write a query that will fetch all columns for every row in the table.

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.

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".

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".

Solution:

SELECT name FROM city WHERE countrycode = 'JPN';

05-Weather Observation Station 1

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    |

+-------------+------------+

 

2. 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.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

3. Write a query to print the list of CITY in lexicographical order for even ID only. Do not print duplicates.

Solution:

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

07-Weather Observation Station 4

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    |

+-------------+------------+

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

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 ID, CITY, STATE, 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.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

4. 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.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

5. 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.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

6. Write a query to print the list of CITY that starts with vowels and ends at vowels in lexicographical order. Do not print duplicates.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

7. Write a query to print the list of CITY that does not start with vowels in lexicographical order. Do not print duplicates.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

8. Write a query to print the list of CITY that does not end with vowels in lexicographical order. Do not print duplicates.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

9. 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.

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 ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 

10. 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.

Solution:

/* Oracle /

SELECT DISTINCT city

FROM station

WHERE REGEXP_LIKE(LOWER(city), '^[^aeiou].[^aeiou]$')

ORDER BY CITY;

2. 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