MySQL Basic Select challenges

So today, I started solving SQL challenges on HackerRank using the MySQL database. HackerRank is a platform that provides a variety of coding challenges in different programming languages. SQL is one of the languages that HackerRank offers challenges in, and it is a valuable tool for anyone looking to work with databases.

In this post, we will explore a few basic SQL select statement challenges from HackerRank, and specifically, we will look at how to use the left() and right() functions, display sorted results, and use the where statement in MySQL.

Challenge 1: Using the Left() and Right() Functions
We will explore using the left() and right() functions. These functions are used to extract a specified number of characters from the left or right side of a string, respectively.

For example, the following query uses the left() function to extract the first three characters from the "name" field in a table called "employees":

SELECT LEFT(name, 3) FROM employees;

In this challenge, we are given a table called "employees" that contains columns for "employee_id", "first_name", and "last_name". The goal is to write a query that returns the first three characters of each employee's last name, as well as the employee's ID.

SELECT employee_id, LEFT(last_name, 3) FROM employees;

Challenge 2: Displaying Sorted Results
The next challenge we will look at involves displaying sorted results. Sorting is a powerful tool that allows you to arrange the data in your query in a specific order.

For example, the following query sorts the results of a table called "sales" by the "amount" column in descending order:

SELECT * FROM sales ORDER BY amount DESC;

One of the challenges needed sorting of data provided in a table called STUDENTS and asked to query the Name of any student in STUDENTS who scored higher than 75 Marks. The output was to be ordered by the last three characters of each name. If two or more students both had names ending in the same last three characters (i.e.: Bobby, Robby, etc.), they had to be secondary sorted by ascending ID.

Here is one solution that worked:

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

Challenge 3: Using the Where Statement
The final challenge we will explore involves using the where statement. The where statement is used to filter the results of a query based on a specific condition.
One challenge asks to query the list of CITY names from STATION that either do not start with vowels or do not end with vowels, making sure the result does not contain duplicates. Below is one way to go about it:

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
OR RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u');

These challenges demonstrate some of the basic SQL select statement concepts that are commonly used when working with databases. By using the left() and right() functions, displaying sorted results, and using the where statement, you can manipulate your data and extract the information you need. I hope this helps as you continue to leverage SQL's power to work with complex data structures and large datasets.