HackerRank SQL Basic Select
Tags: sql
Published: 2023-03-12 05:40:00

---
title: "HackerRank SQL Basic Select"
published: true
description: "HackerRank SQL Basic Select"
tags: ["SQL"]
cover_image: "https://img.spiffyeight77.com/blog/2023/03/12/cover.png"
published_at: 2023-03-12 05:40 +0000
---

## Before Starting

I haven't handwritten SQL for a long time, but I have recently started practicing again to regain my skills. This post mainly records interesting problems and solutions in SQL Basic Select.

## SQL Basic Select

### 1. 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.  
The **STATION** table is described as follows:

```
| Field    | Type          | 
|----------|---------------| 
| ID       | NNUMBER       | 
| CITY     | VARCHAR2(21)  | 
| STATE    | VARCHAR2(2)   | 
| LAT_N    | NUMBER        | 
| LONG_W   | NUMBER        |
```

The objective of this problem is to determine how to utilize the functions ``COUNT`` and ``DISTINCT`` in order to compute the quantity of answers.

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

### 2. Weather Observation Station 5

Query the two cities in **STATION** with the shortest and longest _CITY_ names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.  
The **STATION** table is described as follows:

```
| Field    | Type          | 
|----------|---------------| 
| ID       | NNUMBER       | 
| CITY     | VARCHAR2(21)  | 
| STATE    | VARCHAR2(2)   | 
| LAT_N    | NUMBER        | 
| LONG_W   | NUMBER        |
```

The goal of this problem is to use the ``UNION`` 、``LIMIT``  keywords and ``LENGTH`` function effectively for querying answers.

```
(SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) ASC, CITY ASC
LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY ASC
LIMIT 1);
```


### 3. Weather Observation Station 8

Query the list of _CITY_ names from **STATION** which have vowels (i.e., _a_, _e_, _i_, _o_, and _u_) as both their first _and_ last characters. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

```
| Field    | Type          | 
|----------|---------------| 
| ID       | NNUMBER       | 
| CITY     | VARCHAR2(21)  | 
| STATE    | VARCHAR2(2)   | 
| LAT_N    | NUMBER        | 
| LONG_W   | NUMBER        |
```

The goal of this problem is to use the ``LIKE`` 、``LIMIT``  keyword effectively for querying answers.

```
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%')
AND (CITY LIKE '%a' 
    OR CITY LIKE '%e' 
    OR CITY LIKE '%i'
    OR CITY LIKE '%o'
    OR CITY LIKE '%u');
```

### 4.  Weather Observation Station 11

Query the list of _CITY_ names from **STATION** that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

**Input Format**

The **STATION** table is described as follows:

```
| Field    | Type          | 
|----------|---------------| 
| ID       | NNUMBER       | 
| CITY     | VARCHAR2(21)  | 
| STATE    | VARCHAR2(2)   | 
| LAT_N    | NUMBER        | 
| LONG_W   | NUMBER        |
```

The goal of this problem is to use the ``LIKE`` 、``NOT`` and ``OR`` keyword effectively for querying answers.

```
SELECT DISTINCT(CITY)
FROM STATION
WHERE NOT (CITY LIKE 'a%' 
    OR CITY LIKE 'e%' 
    OR CITY LIKE 'i%'
    OR CITY LIKE 'o%'
    OR CITY LIKE 'u%')
OR NOT (CITY LIKE '%a' 
    OR CITY LIKE '%e' 
    OR CITY LIKE '%i'
    OR CITY LIKE '%o'
    OR CITY LIKE '%u');
```

### 5. Higher Than 75 Marks

Query the _Name_ of any student in **STUDENTS** who scored higher than 75  _Marks_. Order your output by the _last three characters_ of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending _ID_.

**Input Format**

The **STUDENTS** table is described as follows:

```
| Colume    | Type         | 
|----------|---------------| 
| ID       | Integer       | 
| Name     | String        | 
| Marks    | Integer       |
```

The goal of this problem is to use the ``SUBSTR`` function for querying answers.

```
SELECT Name
FROM STUDENTS
WHERE Marks > 75
ORDER BY SUBSTR(Name, -3, 3) ASC, ID ASC;
```