CA 21 - Basic Select SQL Queries

 1. Query all columns for a city in CITY with the ID 1661.

The CITY table is described as follows:

Here we make use of the where conditon cllause to retrieve the details of the Id with 1661, we select all the columns to be displayed and "*" is avoided for best practice.

SOLUTION:

SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY where ID = 1661;

2.Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

The CITY table is described as follows:

CITY.jpg

Here we make use of the where condtional clause, to obatin the population to be over thhe given number we make use of the' >' symbol to achieve this. Also the AND operator is used to state two where conditons where we want only the details of cities in USA so we use the countrycode check with 'USA' single quotes are used to check with the value, usage of double quotes will make the db server to assume it to be column

SOLUTION:

SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA';

3. Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

The CITY table is described as follows:

In this problem too we will make use of the where conditional clause. As we need only the details of the cities in japan we check with the country code of all the records to pick only the cities from japan

SOLUTION:

SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY WHERE COUNTRYCODE = 'JPN';

4. Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

For example, if there are three records in the table with CITY values 'New York', 'New York', 'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'. The query returns , because .

    Here we make use of the COUNT() function to count the total number of cities and then use the COUNT() to count and DISTINCT() to get the unique cities, so we nest the DIsitinct inside count. The rest of the distinct cities is given to the Count function to count and the difference "-" is used to get the difference.

SOLUTION:

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

5.Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

The CITY table is described as follows:
CITY.jpg

    Here similar to already solved problem we make use of where conditonal clause with AND operator to check two conditions if the cities are in USA and the population is greater than 120000, also only NAME column is used to select from the table

SOLUTION:

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'USA' AND POPULATION > 120000;

6.Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.
The CITY table is described as follows:
    Here like the already solved problem we make use of the where condition clause to check for Japan cities only. 

SOLUTION:

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';


7. Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.


    Here to obtain the given result, we first make use of distict to get unique cities and to get the cities which start with only consonants, we make use of NOT LIKE and string matching '%' with starting vowel to obtain the consonants only. 

SOLUTION:

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%';

8. Query all columns (attributes) for every row in the CITY table.

The CITY table is described as follows:
CITY.jpg

    This is a simple select query, selecting all the columns and getting the records from the CITY table

SOLUTION:

SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY;

9. Query a list of CITY and STATE from the STATION table.

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

    This is too a simple select query with specific collumns to be retrieved , such as city and state from the station table.

SOLUTION:

SELECT CITY,STATE FROM STATION;



Comments

Post a Comment

Popular posts from this blog

CA 04 - Two Sum & Sorted Two Sum

Image
  MY CODE: In brute force method the intuitive way of solving this problem with two for loop. Using nested loop to traverse through the list and try different combination of two sum to check if it matches with the target.    Time complexity: O(n^2)       OPTIMIZED:          In the optimized method of approaching the Two Sum problem. The given list is already sorted in ascending order. This gives us the leverage of using Two pointer technique to solve this problem. Thus using single loop to approach this problem. If current sum is less than target move the left pointer, if it is greater move the right pointer else return the current index (equal to target).  Time complexity: O(n) MY CODE:
Read more

CA 05 - Reverse the array

  Array Reverse Reverse an array  arr[] . Reversing an array means  rearranging  the elements such that the  first  element becomes the  last , the  second  element becomes  second last  and so on. Input: arr[] = [1, 4, 3, 2, 6, 5] Output: [5, 6, 2, 3, 4, 1] Explanation: The first element 1 moves to last position, the second element 4 moves to second-last and so on. Input: arr[] = [4, 5, 1, 2] Output: [2, 1, 5, 4] Explanation: The first element 4 moves to last position, the second element 5 moves to second last and so on. CODE:     BRUTE FORCE METHOD:          Here, in this method we will create a temporary array to store the reversed given array and then assign it to the given array class Solution :     def reverseArray ( arr ):         n = len (arr)         temp = [ 0 ] * n         for i in range (n- 1 ,- 1 ,- 1 ): ...
Read more