BASIC SELECT SQL QUERIES

Let me explain it with some "Hackerank" qns


QN: 1

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


ANS: SELECT * from CITY where ID = 1661;

since, it is to select all columns from table city.. and the condition is for ID = 1661.


QN: 2

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


ANS: SELECT * from CITY where COUNTRYCODE= 'JPN';

since, it is to select all attributes from the table city.. and the condition is for a COUNTRYCODE =JPN


QN:3

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


ANS: SELECT NAME from CITY where POPULATION > 120000 and COUNTRYCODE = 'USA';

since it is to select all the names from the table city , condition is population is more than 120000 and CountryCode =USA


QN:4

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


ANS: 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%";

Since, it is to select the unique city names from the table STATION and the condition is to the names of the city should not start with vowels(a,e,i,o,u).

so, A%,E%,I%,O%,U%.. is that all other character after that are considered . so, this represents the first leter.


QN:5

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


ANS: SELECT CITY,STATE FROM STATION;

Since, we need both city and state columns from table STATION .. we select both table

QN:6

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



ANS: SELECT * FROM CITY WHERE POPULATION >100000 AND COUNTRYCODE = 'USA';
Since, it is to select all from table CITY . the condition is where population is more than 100000 and COUNTRYCODE IS USA

QN:7

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


ANS: SELECT COUNT(CITY)-COUNT(DISTINCT CITY) from STATION;

here it is asked for the difference between all city and unique city names to find the repetition we use COUNT keyword to city and distinct city .

QN:8

Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN


ANS: SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';

Here, it is to select the names of the city from table CITY and the condition is COUNTRYCODE to be JPN


Comments

Post a Comment

Popular posts from this blog

THE DELIVERY MAN

  The Delivery Man – Python List Operations  Introduction Lists in Python are used to store multiple items in a single variable. They are flexible, allowing us to add, remove, update, and manipulate elements easily. In this task, different list operations are performed to simulate a delivery man's workflow. 1. Creating a list and accessing an item items = ["Notebook", "Pencil", "Eraser", "Ruler", "Marker"] print(items[2]) Approach: Indexing Why: Lists use zero-based indexing, so the third item is accessed using index 2. 2. Adding a new item items.append("Glue Stick") print(items) Approach: append() method Why: It adds an item at the end of the list, which is ideal for adding new deliveries. 3. Inserting an item items.insert(2, "Highlighter") print(items) Approach: insert() method Why: Used when we need to add an item at a specific position instead of the end. 4. Removing an item items.remove("Ruler...
Read more

EC2 LAUNCHING

  EC2 Step 1: Launch EC2 Instance Go to: AWS Console --> EC2 --> Launch Instance Configure: Name : my-web-server AMI : Amazon Linux 2 (easy for beginners) Instance Type : t2.micro (free tier) Step 2: Key Pair Create or select a key pair. Step 3: Network Settings  Allow these: SSH (port 22) --> for login  HTTP (port 80) --> for web access Step 4: Launch Instance Click Launch Instance Step 5: Connect to Instance Use: EC2 --> Connect --> EC2 Instance Connect (easiest) OR via terminal: with this command. for this you have to download the pem files while selecting the key pair.  ssh -i your-key.pem ec2-user@<public-ip> Step 6: Install Web Server For Amazon Linux: sudo yum update -y sudo yum install httpd -y Step 7: Start Web Server sudo systemctl start httpd sudo systemctl enable httpd Step 8: Add a Web Page echo "Hello from EC2" | sudo tee /var/www/html/index.html Step 9: Get Public IP Go to EC2 d...
Read more

SORT A LINKED LIST USING MERGE SORT

  Sort a Linked List using Merge Sort Introduction Sorting a linked list efficiently is a common problem in data structures. Unlike arrays, linked lists do not allow direct access to elements, so algorithms like quicksort are not ideal. Merge Sort is the best choice for linked lists because: It does not require random access It works efficiently with node-based structures Problem Statement Given the head of a linked list: Sort the list in ascending order Return the sorted linked list Example Input: 10 -> 30 -> 20 -> 40 -> 60 -> 50 Output: 10 -> 20 -> 30 -> 40 -> 50 -> 60 Solution Code class Solution: def mergeSort(self, head): if not head or not head.next: return head mid = self.getMiddle(head) next_to_mid = mid.next mid.next = None left = self.mergeSort(head) right = self.mergeSort(next_to_mid) return self.merge(left, right) def getMiddle(self,...
Read more