Top 10 SQL Interview Questions With Implementation

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. If you plan to give an SQL interview then this article is a must read for you! Checkout the top SQL interview questions

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

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.

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-

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?

When a new entry is entered into a database, auto-increment enables the automatic generation of a unique number. We use the AUTO_INCREMENT keyword for auto increment in SQL. By default, the increment value is one.

Syntax:

CREATE TABLE table_name (
column_name datatype AUTO_INCREMENT, 
);

For example,

CREATE TABLE students_data (
id INT AUTO_INCREMENT,
name varchar,
phone_number INT
);

Q4. What are the Different Rank Functions in SQL?

There are four rank functions in SQL

• RANK()
• DENSE_RANK()
• ROW_NUMBER()
• NTILE()

Let’s see them one by one in detail.

1. RANK()

This function will return a number that will be applied to each row in the output partition. Each row receives a rank equal to one plus the rank of the row before it. The RANK function assigns the same rank number to two values that it discovers to be identical within the same partition. The following ranking number will also include duplicate numbers in addition to the preceding rank. As a result, this method does not always assign the ranking of rows in numerical order.

Let’s see the syntax

SELECT 
  column_name     
RANK() OVER (    
    PARTITION BY expression    
    ORDER BY expression)    
AS 
  result 
FROM 
  table_name; 
  

2. DENSE_RANK()

This is almost similar to that of the rank function. Here also, each row receives rank, adding one to the previous rank. If two rows are identical, then they receive the same rank, and the next row directly receives plus one to the current rank. For example, if the 1st and 2nd rows are identical, then both receive rank 1, and the next third row receives rank 2 instead of rank 3, as in the case of using the RANK() function. That’s the difference.

Let’s see the syntax

SELECT 
  column_name     
DENSE_RANK() OVER (    
    PARTITION BY expression    
    ORDER BY expression)    
AS 
  result 
FROM 
  table_name; 
  

3.ROW_NUMBER()

The row number function differs from the rank and dense rank functions. Starting from 1, this gives ranks adding 1 to the previous row. No matter if any two rows are identical.

let’s see the syntax

SELECT 
  column_name     
ROW_NUMBER() OVER (    
    PARTITION BY expression    
    ORDER BY expression)    
AS 
  result 
FROM 
  table_name; 
  

4. NTILE()

The NTILE() function is the one you want to use when you want to distribute groups of rows over a partition evenly. You must tell this ranking function how many groups you want the rows to be equally divided into. According to the specified requirement, each row group receives its rank.

let’s see the syntax

SELECT 
  column_name     
NTILE(N) OVER (    
    PARTITION BY expression    
    ORDER BY expression)    
AS 
  result 
FROM 
  table_name; 

Q5. Explain Normalization and Denormalization in SQL.

Normalization removes redundancy from the database, which means it is split across multiple tables instead of just one table. and non-redundant, consistent data is added. An improperly constructed database table is inconsistent and could cause problems when executing operations. Hence database normalization is an important step. An unnormalized table is transformed into a normalized table through this process.

Denormalization is used to aggregate data from several tables into one to be easily queried. Redundancy is added using it. In contradiction to normalization, denormalization reduces the number of tables. Denormalization is used when joins are expensive, and table queries are run frequently. Wastage of memory is the main drawback of denormalization.

Q6. What is the Difference Between SQL and MySQL?

Q6. What are the usages of SQL?

1. Creating and managing databases and their structures.
2. Inserting, updating, and deleting data within tables.
3. Querying and retrieving specific information from databases.
4. Filtering and sorting data based on specific criteria.
5. Aggregating and summarizing data using functions like SUM, COUNT, and AVG.
6. Joining multiple tables to combine data from different sources.
7. Creating views to present customized or filtered perspectives of data.
8. Implementing constraints to ensure data integrity and enforce rules.
9. Indexing columns to improve query performance and data retrieval.
10. Granting and managing user permissions to control access rights and data security.

Q7. What are the different subsets of SQL?

SQL encompasses several subsets or variations that are specific to different database management systems (DBMS) or have specialized purposes. Here are some notable subsets of SQL:

1. MySQL: SQL variant specific to the MySQL database management system.
2. PostgreSQL: SQL variant specific to the PostgreSQL database management system.
3. Oracle SQL: SQL variant specific to the Oracle Database system.
4. Microsoft T-SQL: SQL variant specific to Microsoft SQL Server, known as Transact-SQL.
5. SQLite: SQL variant specific to the lightweight, embedded database engine SQLite.
6. ANSI SQL: The American National Standards Institute (ANSI) SQL is a standardized version of SQL that sets the foundation for most SQL implementations. Different DBMSs may adhere to various versions of ANSI SQL, such as ANSI SQL-92, ANSI SQL:1999, ANSI SQL:2003, etc.
7. PL/SQL: A procedural extension to SQL used in Oracle Database for creating stored procedures, functions, and triggers.
8. NoSQL: Although not SQL in the traditional sense, NoSQL databases (e.g., MongoDB, Cassandra, Couchbase) represent a different approach to database management, often focusing on high scalability, schema flexibility, and distributed architectures, and they utilize their query languages that are different from traditional SQL.

Q8. What are some common clauses used with SELECT query in SQL?

When using the SELECT query in SQL, there are several common clauses that can be used to refine and customize the query results. Here are some frequently used clauses:

1. SELECT: Specifies the columns to be retrieved from the database table(s).
2. FROM: Identifies the table(s) from which to retrieve the data.
3. WHERE: Filters the rows based on specified conditions, allowing for data retrieval based on specific criteria.
4. DISTINCT: Removes duplicate values from the result set, returning only unique values.
5. ORDER BY: Sorts the result set in ascending (default) or descending order based on one or more columns.
6. GROUP BY: Groups the result set by one or more columns, often used in conjunction with aggregate functions.
7. HAVING: Filters the grouped rows based on specified conditions, similar to the WHERE clause but applied after the GROUP BY clause.
8. LIMIT: Specifies the maximum number of rows to be retrieved from the result set.
9. OFFSET: Specifies the number of rows to skip from the beginning of the result set before starting to return rows.
10. JOIN: Combines data from multiple tables based on related columns, allowing for retrieval of data from multiple sources.
11. UNION: Combines the result sets of two or more SELECT statements into a single result set.
12. IN: Tests whether a value matches any value in a specified list.
13. NOT IN: Tests whether a value does not match any value in a specified list.
14. LIKE: Performs pattern matching to retrieve rows based on specified patterns using wildcard characters.
15. BETWEEN: Retrieves rows with values within a specified range.

Q9. What is a view in SQL?

In SQL, a view is a virtual table derived from a query’s result. It allows you to encapsulate complex queries into a named, reusable object. A view can be used just like a regular table, enabling you to query its data or perform other operations on it.

Here’s the syntax for creating a view in SQL:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Let’s break down the syntax:

• CREATE VIEW view_name: This statement is used to create a view with a specific name, referred to as view_name. You can choose any suitable name for your view.
• AS: This keyword indicates that the view definition is starting.
• SELECT column1, column2, ...: Here, you specify the columns you want to include in the view. You can select specific columns or use * to select all columns.
• FROM table_name: Specifies the table from which you want to retrieve data for the view. You can include joins or subqueries to define more complex queries.
• WHERE condition: This part is optional and allows you to include a condition to filter the rows in the view based on specific criteria.

Q10. What is an Index in SQL?

In SQL, an index is a database object that improves the speed of data retrieval operations on database tables. It works like a table of contents, organizing and storing a sorted copy of selected columns from a table. By creating an index on one or more columns, the database engine can locate and retrieve data more efficiently, reducing the need for full-table scans. Indexes enable faster searching, sorting, and joining of data, resulting in improved query performance. However, indexes incur overhead during data modifications (insert, update, and delete operations) as they need to be updated to reflect the changes. Therefore, choose indexes carefully and balance them to optimize database performance.

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.

We hope you find these SQL interview questions helpful. Have we missed any SQL interview questions? Let us know in the comment section below. We will be happy to answer them for you!

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

Amrutha

This is Amrutha, I am pursuing B.Tech in the Computer science Department. I am interested in developing ML Models with python and Data Analysis. And also I have an interest in Web Development. I hope my articles in Analytics Vidhya help you to learn better. Thank you!!