Top 30 SQL Interview Questions for 2024

1. What is SQL?

Ans. SQL stands for Structured Query Language.

 It is a standard computer language for accessing, storing, and manipulating data stored in the relational database. SQL become an International Standard Organization (ISO) in 1987.

Raymond Boyce and Donald Chamberlin developed SQL at IBM in the early 1970s. It is supported by the popular relational database systems like SQL Server, Oracle, and MySQL.

2. What is the difference between SQL and PL/SQL?

Ans. SQL

• It is a structured query language that adds, modifies, deletes, or manipulates the data in the database.
• In SQL, we can execute a single command or a query at a time.
• SQL cannot support any control Statements.
• It is used to write queries using DDL and DML statements.

PL/SQL

• It is a Procedural Language/Structured Query Language in which we can execute multiple queries at the same time.
• In PL/SQL, we can use programming concepts like if-else, loops control statements.
• PL/SQL is used to write procedures, packages, views, functions, triggers. These are called PL/SQL objects.

3. What are the Triggers in SQL?

Ans. Triggers are the stored procedure or a program in a database, which are automatically executed or fired when some event occurs. We can enable or disable the trigger, but it cannot be invoked explicitly. Triggers can be stored in a database and will be called repeatedly when the specific condition will match.

They are also known as event-condition-action (ECA) rules.

Trigger consist of three parts:

• Event – It is composed of DML statements Insert, Update, and Delete. It is used to activate the triggers.
• Condition – It test or check whether the trigger should run.
• Action – If the condition is satisfied, queries, and the command associated with the trigger are executed.

A trigger is called either after or before the following events:

• INSERT –  When a new row or tuple is inserted in the table
• UPDATE – When an existing row or tuple is updated in the table.
• DELETE – When a row is deleted from the table.

We create triggers using the create trigger Statement.

4. What is a Stored Procedure?

Ans. It is a collection of SQL statements which are stored in a relational database management system and are responsible for performing a specific task. If we have a situation where we write the same query over and over again, we can save that query as a stored procedure in a database and call it just by a name.

They can be called by the triggers, and other stored procedure and applications such as PHP, Java, and python. Stored procedure used to improve the performance of the database and reduce the network traffic.

The Stored procedure takes the input parameters and processes them. They return a single value such as a text value or a number or a set of results.

5. What is a view?

Ans. It is a virtual or logical table which is used to fetch the records from one or more table. Just like a real table, it also consists of rows and columns.

Views are used to restrict data in a table. Thus, if the student table contains three fields student_id, student_name, student_mob but we want to show the only student_id and student_mob to end-user then for that purpose we will use views.

Syntax to create a view:

CREATE VIEW “ View_name” as “SQL Statement”;

6. What is Indexing in SQL?

Ans. It is a schema object and used by the server to speed up the accessing of the rows with the help of pointers. Indexes are the data structure that contains a pointer to the data of a table arranged in specific order to help the database to optimize queries.

The main advantage of creating an index on a table is to access rows quickly. Suppose, there is a table with no index, and there are thousands of records in a table, so accessing operation will take a long time. That’s why we create an index on columns so that the information can be accessed quickly.

There are two types of index:

1. Clustered Index
2. Non – Clustered Index.

7. What is Join in SQL?

Ans.  The Join is a SQL statement used to combine data or rows from two or more tables based on common column or field between them in a relational database.

In SQL queries, JOIN keyword is used for joining two or more tables. A table in a SQL also joins to itself, which is known as a Self Join.

8. What are the different types of Join in SQL?

Ans. There are mainly five types of Join in SQL are:

1. Inner Join
2. Outer Join
3. Natural Join
4. Cross Join
5. Self Join

9. What is Inner Join?

Ans. This type of join returns only those rows or records that exist in both the table.

Note: We can either use JOIN keyword or INNER JOIN keyword for performing this operation.

Syntax:

   SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
   FROM Table1 
    INNER JOIN Table2 
    ON Table1.MatchingColumnName = Table2.MatchingColumnName; 

10. What is Outer Join?

Ans. We have three kinds of Outer Join:

1. Left Outer join: This type of Join returns all the records from the left table and matching records from the right table. When no matches have been found in the right table, then NULL is returned.

      Syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
 FROM Table1 
 LEFT JOIN Table2 
 ON Table1.MatchingColumnName = Table2.MatchingColumnName; 

• Right Outer Join: This type of Join returns all the records from the right table and matching records from the left table. When no matches have been found in the left table, then NULL is returned.

      Syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1 
RIGHT JOIN Table2 
ON Table1.MatchingColumnName = Table2.MatchingColumnName; 

• Full Outer Join: This Join combines the left and right outer join. This type of Join returns all the rows from the left table and right table. When the join condition is not met, a NULL value will be displayed in the result set.

       Syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1 
FULL JOIN Table2 
ON Table1.MatchingColumnName = Table2.MatchingColumnName; 

11. What is Natural Join?

Ans. This type of join is used to combine two tables using all the attributes or columns having the same name and same type.

Syntax:

Select * from 
table1 
NATURAL JOIN table2

12. What is Cross Join?

Ans. The Cross Join or Cartesian Join in SQL returns the Cartesian product of rows from both the tables.

When every row from the first table is combined with every row from the second table, in general, if the first table (table1) contains n rows and the second table (table2) contains m rows, resultant table (Cartesian product) contains (n x m) rows.

13. What is self join?

Ans. This type of Join is used to join a table to itself, i.e., join of two copies of the same table. This Join is used when two sets of data are compared in the same table.

14. What is Normalization in SQL?

Ans. Normalization is the process of reducing or minimizing data redundancy from the relation or a set of relations. It is a method to remove the insert anomalies, update anomalies, and delete anomalies and bring the database to the consistent state.

Normalization divides the larger table into two or more smaller tables and defines the relationship between them.

Normalization was developed by the E.F Codd (IBM researcher) in the 1970s.

Most commonly used normal forms to reduce the data redundancy from the database tables are as follows:

1. First normal form (1NF)
2. Second normal form (2NF)
3. Third normal form (3NF)
4. Boyce Codd normal form (BCNF)

15. Explain 1st normal form with an example?

Ans. Any table or relation in a database is in 1NF if every attribute in a relation is single (atomic) valued attribute.

Consider an example of how we bring the unnormalized table into 1NF.

In the table shown below, the [subject] column contains two values for student_id 101 and 103, which shows that the table is not in first NF.

Student_id	Student_name	Subject
101	Ram	C, Java
102	Shyam	Java
103	Gagan	C, C++

                                          Table: STUDENT

For converting the above student table into 1NF, we break the values of [Subject] column into atomic values, and now we have the resulting table which satisfies the First normal form (1NF).

Student_id	Student_name	Subject
101	Ram	C
101	Ram	Java
102	Shyam	Java
103	Gagan	C++
103	Gagan	C

16. What is 2^nd Normal form?

Ans. A relation in a database is in second normal form if it satisfies the following condition:

1. A table or relation must be in 1^st Normal form.
2. And all non-key attributes in a relationship should be fully functionally dependent on a primary key.
3. There should be no partial dependency in a table or a relation.

Partial Dependency: A non-prime attribute is determining by the part of a candidate key.

17. What is the 3^rd Normal form?

Ans. According to the E.F Codd, a table or relation is in 3^rd Normal form, if the following conditions are satisfied:

1. A table or relation must be in 2^nd normal form.
2. There should be no transitive functional Dependency in a relation.

18. What is Denormalization?

Ans. It is a technique used to move from higher to lower normal forms of the database. It is a reverse process of normalization and applied after normalizing the database.

19. What are the keys in SQL? Name its types.

Ans. A key is an attribute or a set of an attribute which helps us to identify the tuple in a table or relation uniquely. Keys also allows to establishing the connection between two different tables or views.

SQL supports the following types of keys:

1. Primary key
2. Composite key
3. Unique key
4. Foreign key
5. Super key
6. Alternate Key
7. Candidate key

20. Explain the primary key?

Ans. A primary key is a set of one or more columns or fields in a table that can uniquely identify each record or tuple in that table in a database. If in the table, a field or columns declared as a primary key, we cannot give two records of the same name of that field. Any table in a database contains only one primary key. A field declared as a primary key cannot have NULL values.

21. What is a query in SQL?

Ans. A query is a question or a request for information or data from the database table, expressed in a formal way. A query in a database can be either an action or a select query.

OR

A query is an instruction to the DBMS to modify some data in a database table or to access the data from the tables.

22. What is Subquery in SQL?

Ans. The subquery or nested query is a query or instruction inside another query, or we can say that it is a query which is embedded in a WHERE or HAVING clause of another SQL query.
A subquery is also known as an inner or nested query, while the statement containing a subquery or outer query is also called the main query. Subqueries are placed on the right side of comparison operator in query and enclosed in the ‘()’ parenthesis.

The syntax for creating the subquery is as follows:

SELECT column-names
FROM table-name1 
WHERE value IN (SELECT column-name 
FROM table-name2 
WHERE condition) 

23. What is the difference between having and where clause?

Ans.  

• In SQL,the HAVING clause can be used only with a Select statement but the WHERE clause can be used with the Update, Select and Delete Statement.
• We use WHERE clause in SQL queries to filter elements based on some criteria on individual records of a database table while we use HAVING clause to filter the groups based on the values of aggregate functions such as count, sum.
• In Select Statement, WHERE clause is used before the GROUP BY clause and HAVING clause is used after the GRUP BY clause.
• Aggregate functions like avg, sum, min, max, and the count can never appear with Where clause in a query statement while all of these functions can be used along Having Clause.
• HAVING clause behaves like a post-filter while WHERE clause generally behaves as the pre-filter.

24. What is the difference between Delete and Truncate?

Ans.

Delete	Truncate
It is a Data Manipulation Command (DML) command.	It is a Data Definition Command (DDL) command.
This command is used to delete specific rows from the database table.	This command is used to delete or removes all the rows from the table.
We can use WHERE clause with this command to filter the rows in a table.	We cannot use WHERE clause in a query statement with this command.
Delete command can be used with the indexed views.	The truncate command cannot be used with the indexed views.
It is slower than the truncate command because it maintains the log.	It is faster than delete command.

25. What are the different types of SQL statements?

Ans. Different types of SQL statements are as follows:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control language (DCL)
• Transaction Control Language (TCL)

26. What is Data Definition Language?

Ans. Data Definition Language commands changes or updates the structure of the table in a database like creating a table, altering a table, and deleting a table.

All the command of DDL (Data Definition Language) are auto-committed, that means it permanently save all the changes in the database.

Following commands come under the Data Definition Language:

• Create
• Alter
• Drop
• Truncate

27. What is Data Manipulation Language?

Ans. Data Manipulation Language commands are used to modify the database. These commands are responsible for all form of changes in the database tables.

Following commands come under the Data Definition Language:

• Select
• Insert
• Update
• Delete

28. What is Data Control Language?

Ans. Data Control Language commands are used to implement security on database objects like table, stored procedure, view, etc. In this category, we have REVOKE and GRANT command. These commands deal with the permissions, rights, and other controls of the database system.

29. What is the difference between ‘Between’ and ‘In’ operator?

Ans.  Between: The BETWEEN operator in SQL selects a range of values between two given values. The values can be text, number, etc.

Syntax:

SELECT * FROM table_name
WHERE column_name BETWEEN 'start_value' AND 'end_value';

In: The IN operator in SQL checks a value within a given set of values separated by commas and display the rows from the table which are matching.

Syntax:

SELECT * FROM table_name
WHERE column_name IN (value1, value2, value3, ……);

30. Explain the different types of SQL constraints?

Ans. The following are the different type of SQL constraints: -

• NOT NULL - This SQL constraint ensures that a column in a database table cannot have a NULL value.
• UNIQUE - This SQL constraint ensures that all values in a field or column are different.
• PRIMARY KEY – This SQL constraint is a combination of a NOT NULL and UNIQUE constraint. Primary Key constraint uniquely identifies each record or row in a table.
• FOREIGN KEY - This SQL constraint uniquely identifies a row/record in another table.
• CHECK – Check constraint ensures that all values in a column of a table satisfy a specific condition given in a query statement.
• DEFAULT – This SQL constraint sets a default value for a column when no value is specified to that column.
• INDEX – This SQL constraint is used to create and access data from the database very quickly.