Top SQL Interview Questions

SQL is a required skill since the Relational Database Management System (RDBMS) is the most often used company database. This blog on SQL Interview Questions and Answers is intended to help you through a variety of SQL questions based on topics such as MS SQL Server, MySQL, and others. This is a one-stop resource for maximizing your benefit and preparing for job interviews. Consider the following top SQL Query interview questions that employers are now asking:

1. Define SQL and Describe its Components.

SQL is a widely used standard language for accessing and modifying databases. The term "SQL" refers to Structured Query Language. It may be used to build a web application using a server-side scripting language such as ASP, PHP, or another similar language.

There are 3 components to it, and they are as follows:-

1. Data Definition Language.
2. Data Manipulation Language.
3. Data Control Language.

2. What Are the Different Kinds of Joins in SQL?

Joins are used in SQL to combine records from two or more tables in a database based on a shared field.

The four kinds of joins in SQL are as follows-

• INNER JOIN: It returns records that contain values that are identical in both tables.
• LEFT JOIN: This operation returns all records from the left table and all records that match those in the right table.
• RIGHT JOIN: A right join retrieves all records from the right table, as well as the records that meet the criteria in the left table.
• FULL JOIN: It returns all records when either the left or right table has a match.

You can learn more about SQL Joins from here.

3. What is the Definition of a Primary Key? Explain

Primary key constraint uniquely identifies each record in a database. A Primary Key must contain a unique value and it must not contain a null value. Usually, Primary Key is used to index the data inside the table.

PRIMARY KEY constraint at Table Level

CREATE table Student 
(	s_id int PRIMARY KEY, 
	Name varchar(60) NOT NULL, 
	Age int);

Copy

The above command will create a PRIMARY KEY on the s_id.

PRIMARY KEY constraint at Column Level

ALTER table Student 
ADD PRIMARY KEY (s_id);

Copy

The above command will create a PRIMARY KEY on the s_id.

4. What are SQL Constraints?

It is a rule that restricts the type of data that may be inserted into a database in order to preserve the table's integrity and correctness.

It is classified into two kinds-

• Column level constraints

• Table-level constraints

These are the most often used restrictions on a table.

• NOT NULL
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY etc

5. In SQL, what is the difference between HAVING and a WHERE clause?

While the WHERE clause is used to filter individual rows, the HAVING clause is used to filter groups.

While the WHERE clause may be used without the GROUP BY, the HAVING clause cannot.

Example of Having Clause

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition

Example of Where Clause

DELETE FROM table_name WHERE [condition];

6. Distinguish between SQL and MySQL.

• Mysql is accessible for free as an open-source project, while SQL is not.
• MySQL supports updatable views, while SQL has more powerful indexed views.
• Mysql does not support XML, while SQL does.
• MySQL does not allow auto-tuning, while SQL does.
• Mysql lacked support for user-defined functions, while SQL did.
• Mysql support is very restricted, while SQL support is robust and comprehensive.
• Mysql does not provide stored procedures or complete joins, but SQL does.
• Mysql does not have the Cursor functionality, although SQL does.

7. What is a SQL Trigger? Explain

A trigger is a database item connected with a table in SQL. It will be triggered when a specified action on the table is performed. It is done when one of the following SQL statements occurs in a table: INSERT, UPDATE, or DELETE. It may be activated BEFORE or AFTER.

8. What is Normalization and What are its Benefits?

Database Normalization is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy(repetition) and undesirable characteristics like Insertion, Update, and Deletion Anomalies. It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables.

Normalization is used for mainly two purposes,

• Eliminating redundant(useless) data.
• Ensuring data dependencies make sense i.e data is logically stored.

Advantages

• It contributes to the elimination of unnecessary data.
• It utilizes a much more adaptable database architecture.
• It is preferable to take care of database security.
• It is concerned with database consistency.

9. How To Get An Employee's Third-highest Pay From The Employee Database in SQL?

SELECT salary FROM employee e1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM employee e2 WHERE e2.salary is more than e1.salary)

10. What does ALIAS mean in SQL?

It is used inside a SQL query to rename a table. Renaming is a procedure that changes the name of a table temporarily without altering the table's real name in the database. It is applicable to both tables and fields.

The basic syntax of a table alias

SELECT field1, field2 FROM TABLE_NAME AS TABLE WHERE "WRITE_YOUT_CONDITION";

The basic syntax of a column alias

SELECT field1 AS column1 FROM TABLE_NAME WHERE "WRITE_YOUT_CONDITION";

11. How Would you Describe Scalar Functions in SQL?

It is a function that accepts one or more values but only returns one. It is a user-input method that returns a single value. Some examples of scalar functions are mentioned below:

• UCASE()

Example

Convert the text to upper-case:

SELECT UCASE("SQL Tutorials are FUN!");

• LCASE()

Example

Convert the text to lower-case:

SELECT  LCASE("SQL Tutorials are FUN!");

• MID()

Example

Extract a substring from a string (start at position 5, extract 3 characters):

SELECT MID("SQL Tutorial", 5, 3) AS ExtractString;

• LEN()

Example

Return the length of a string:

SELECT LEN('studytonight.com');

• ROUND()

Example

Round the number to 2 decimal places:

SELECT ROUND(235.415, 2) AS RoundValue;

• NOW()

Example

Return current date and time:

SELECT NOW();

• FORMAT()

Example

Format a number:

SELECT FORMAT(123456789, '##-##-#####');

12. Describe The Aggregate Functions Available in SQL.

The aggregate function performs arithmetic operations on a collection of values and outputs a single value. Except for the COUNT function, it ignores NULL values while doing calculations.

SQL has a number of aggregate functions, which are detailed below.

• AVG()

AVG() Syntax Example

SELECT AVG(column_name)
FROM table_name
WHERE condition;

• COUNT()

COUNT() Syntax Example

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• SUM()

SUM() Syntax Example

SELECT SUM(column_name)
FROM table_name
WHERE condition;