CA 21 - Basic Select SQL Queries

 1.Revising the Select Query I:

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

SELECT id,name,countrycode,district,population from city where population > 100000 AND countrycode='USA';

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

select name from city where population > 120000 and countrycode='USA';

3.Select All

Query all columns (attributes) for every row in the CITY table.
select * from city;

4.Select By ID
Query all columns for a city in CITY with the ID 1661.
SELECT * from city where ID=1661;

5.Japanese Cities' Attributes
Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.
SELECT * from city where countrycode='JPN';

6.Japanese Cities' Names
Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.
SELECT name from city where countrycode='JPN';

7.Weather Observation Station 1
Query a list of CITY and STATE from the STATION table.
select city ,state from station;

8.Weather Observation Station 9
Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain 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%';
9.Weather Observation Station 4
Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
select count(city)-count(DISTINCT(city)) from station;


Comments

Post a Comment

Popular posts from this blog

  CA 09 - Move All Negative Elements to end Move all negative elements to end Difficulty:  Easy Accuracy:  56.24% Submissions:  174K+ Points:  2 Given an unsorted array  arr[ ]  having both negative and positive integers. The task is to place all negative elements at the end of the array without changing the order of positive elements and negative elements. Note :  Don't return any array, just in-place on the array. Examples: Input : arr[] = [1, -1, 3, 2, -7, -5, 11, 6 ] Output : [1, 3, 2, 11, 6, -1, -7, -5] Explanation: By doing operations we separated the integers without changing the order. Input : arr[] = [-5, 7, -3, -4, 9, 10, -1, 11] Output : [7, 9, 10, 11, -5, -3, -4, -1] MY CODE: class Solution: def segregateElements(self, arr): a = [] b = [] for i in arr: if i >= 0: a.append(i) else: b.append(i) temp = a + b fo...
Read more
  CA 06 - Find the Maximum and Minimum Element in the Array Min and Max in Array Input: arr[] = [1, 4, 3, 5, 8, 6] Output: [1, 8] Explanation: minimum and maximum elements of array are 1 and 8. Input: arr[] = [12, 3, 15, 7, 9] Output: [3, 15] Explanation: minimum and maximum element of array are 3 and 15. MY CODE: The min and max are the functions which shows the maximum and minimum element class Solution: class Solution: def getMinMax(self, arr): a = arr[0] # min b = arr[0] # max for c in range(1, len(arr)): d = arr[c] if d < a: a = d if d > b: b = d return [a, b]
Read more

CA 22 - Reverse a Linked List

Image
  Reverse a linked list Difficulty:  Easy Accuracy:  73.11% Submissions:  384K+ Points:  2 Average Time:  30m You are given the  head  of a singly  linked list . You have to  reverse  the linked list and return the  head  of the reversed list. Examples: Input: Output: 4 -> 3 -> 2 -> 1 Explanation: After reversing the linkedList Input: Output: 8 -> 9 -> 10 -> 7 -> 2 Explanation: After reversing the linked list Input: Output: 8 Explanation: Constraints: 1 ≤ number of nodes ≤ 10 5 1 ≤ node->data ≤ 10 5 MyCODE: class Solution:     def reverseList(self, head):         prev=None         curr=head         while curr:             n_x =curr.next             curr.next=prev             prev=curr     ...
Read more