SQL: 5 SQL Interview Questions with Implementation

Introduction

In today’s world, technology has increased tremendously, and many people are using the internet. This results in the generation of so much data daily. This generated data is stored in the database and will maintain it. SQL is a structured query language used to read and write these databases. In simple words, SQL is used to communicate with databases. SQL allows you to perform any database-related task. It is accessible and economical for the majority of organizations. To ace an interview, learning SQL is very much helpful. In this article, we will learn some interesting topics of SQL which will help you in interviews.

                                                                                         Source: DigitalOcean

Learning Objectives

In this article, we will learn:

1. About Common Table Expressions and how we can implement them.
2. Different ways to replace null values with default values in SQL.
3. About auto increment, normalization, and denormalization.
4. Different rank functions and how they are different from each other.

This article was published as a part of the Data Science Blogathon.

Table of Contents

Q1. What is Common Table Expression in SQL?

A Common Table Expression (CTE) is a query’s result set that lives temporarily within the execution scope of a statement like SELECT, INSERT, UPDATE, DELETE, or MERGE. The output of a CTE is not kept and only exists while the query is being executed. Making complex queries more readable and simple makes it easier for users to create and maintain them.

The following demonstrates the typical SQL Server syntax for a CTE:

WITH 
  expression_name[(columns [,...])]
AS
  (CTE_definition)
SQL_statement;

• Use WITH clause to define a common table expression.
• In the columns, specify the names of all the columns that need to be retrieved in the CTE
• Define CTE after AS that retrieves a table with specified columns.
• Finally, write an SQL query using a common table expression.

Source: CoderEarth

Let’s see an example to define a Common Table Expression as students_data with id, name, and roll_no columns. And then, a query to return the names of students that starts with the letter A among them.

WITH students_data[(id,name,roll_no)] 
AS (
  SELECT
    id, name,roll_no
  FROM
    students
    )
  
SELECT
  name
FROM
  students_data
WHERE
  name LIKE 'A%';
      

Q2. How to Replace Null Values with Default Values in MYSQL?

Sometimes, while using MySQL, you don’t want NULL values to be returned as NULL. But sometimes, you want NULL values to return a different default value. There are some ways in MYSQL to replace these null values with default values.

There are four ways to replace it-

1. Using IFNULL() function

2. Using

COALESCE() function

3. Combination of IF() function and IS NULL operator

4. Combination of CASE expression and IS NULL operator

Let’s see them one by one.

1. Using IFNULL() function: 

The IFNULL() function takes two expressions and returns the first arguments if the first expression is not null. The second parameter is returned if the first expression returns null.

Let’s see the syntax.

IFNULL(expression, alternate_value)

#Example

SELECT IFNULL(Name,'N/A') 
FROM
  students
 
  

The above example returns names from table students. If the entry is not null, the name will be returned, and if the entry is null, the default N/A will be returned.

2. Using  COALESCE() function: 

The COALESCE() method returns the first non-null arguments from the given list of expressions. The function gives a null result if the expression is empty. Moreover, a specified default value can be used to replace null entries in a table.

Simply, it returns the first non-null argument in the given list of expressions. If there are no non-null values, then NULL is returned.

Let’s see some examples to understand.

SELECT COALESCE('one', 'two', 'three') AS result
#result
#one
SELECT COALESCE(NULL, 'one', 'two', 'three') AS result
#result
#one
SELECT COALESCE(NULL, NULL, 'two', 'three') AS result
#result
#two
SELECT COALESCE('A', NULL, 'B', NULL) AS result
#result
#A
SELECT COALESCE(NULL, NULL, 'P', NULL, 'Q') AS result
#result
#P
SELECT COALESCE(NULL, NULL, NULL, NULL, NULL) AS result
#result
#NULL

3. Combination of IF() function and IS NULL operator:

We can also use IF() function and IS NULL operator to replace null values with default values. It works like if the value is null, then replace it with a default value; else, return the original expression. Let’s see how it works with some examples.

To replace null values with ‘N/A’ in the names column of a students_data table.

SELECT IF(names IS NULL, 'N/A', names )
AS 
  result
FROM
  students_data

4. Combination of CASE expression and IS NULL operator:

This is almost similar to the previous one. Here we use the CASE operator instead of the IF() function. So first, we will take cases where there is a null value, and then we will replace it with the given default value. Else the original expression will be returned. Let’s take an example to understand in detail.

SELECT 
  CASE
    WHEN names IS NULL THEN 'N/A'
    ELSE names
  END
FROM
  students_data
      

This code is for the same previous example. To replace ‘N/A’ in the names column when there are null entries.

Q3. What is the SQL Syntax for Auto Increment?

Q4. What are the Different Rank Functions in SQL?

Q5. Explain Normalization and Denormalization in SQL.

Conclusion

Social media app users frequently share photographs and posts, which involves databases that can update information and simultaneously display content to millions of users. There are many tables in the database, and each table contains rows of data. SQL helps to maintain these databases. We learned some important topics in SQL in this article.

• For both small and large organizations, SQL is ideal, and also SQL makes it simple to handle permissions.
• Currently, businesses are looking for someone with SQL knowledge. So it helps to pass interviews by learning SQL.
• Compared to other programming languages, SQL is simpler to learn.
• Many big companies, including Microsoft, Dell, Accenture, and stack Overflows, use SQL to maintain their databases.

Hope you found this article useful. Connect with me on LinkedIn.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.