Wednesday 28 October 2015

Difference function

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.

>>select (max(population)-min(population)) from city;

Thanks.

Average function

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 average population of all cities, rounded down to the nearest integer.

>>select floor(avg(population)) from city; 

Thanks.

Saturday 24 October 2015

'Shortest' and 'Longest' cities name

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 shortest and longest cities name. If there are more than one cities print lexicographical smallest name.

>>(select CITY, char_length(CITY) as len_city from STATION order by len_city limit 1) union all (select CITY, char_length(CITY) as len_city from STATION order by len_city desc limit 1) order by len_city;

Thanks.

Selecting 'Consonants '

Given a table STATION that holds data for five fields namely IDCITYSTATENORTHERN 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.

>>select distinct city from station where (city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u') or (city not like 'a%' and city not like 'e%' and city not like 'i%' and city not like 'o%' and city not like 'u%') order by city;

Thanks.

Selecting 'vowels'

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

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

>>select distinct city from station where city like'a%' or city like'e%' or city like'i%' or city like'o%' or city like'u%' order by city;

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

>>select distinct city from station where city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' order by city;

SQL 'COUNT()' Function

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 NUMunique be no. of unique cities, then write a query to print the value of NUM - NUMunique


>>select count(city)-count(distinct city) from station;





Thanks.





Basic 'Select' query

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

>>select distinct city from station where mod(id,2)=0 order by city;

Thanks.