Exploring SQL Concepts: Top 20 Interview Questions and Answers

In this Article, you will learn about 20 most commonly Asked SQL interview questions & answers.

Q1. What is DBMS ,RDBMS ,Database?

• Data base management system (DBMS) are software systems used to store, retrieve, and run queries on data.Also, can be termed as file manager which manages the data in database rather than saving in file system. Example : MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, Oracle Database, and Microsoft Access.
• Relational database management system (RDBMS) which stores the data into collection of tables. Example: sql server
• Database is an organized form of data or can say structured form of data which can be accesed many ways .this type of data helps in easy access ,storing , retrieval and managing of data.

Q2. What is difference in Tables and Fields ?

Tables is a set of data that are organised in the form of rows and columns where rows can be categorised as horizontal and columns as vertical. whereas table has specified number of columns can be called as fields but can have any number of rows can be called as records.

Q3. What are the Subsets of SQL?

SQL queries divided into four main categories:

Data Definition Language (DDL) :

SQL commands

• CREATE: Creates databases, tables, schema, etc.
• DROP: Drops tables and other database objects
• DROP COLUMN: Drops a column from any table structure
• ALTER: Alters the definition of database objects
• TRUNCATE: Removes tables, views, procedures, and other database objects
• ADD COLUMN: Adds any column to the table schema

Data Manipulation Language (DML) :

used to manipulate data

• SELECT INTO: Selects data from one table and inserts it into another
• INSERT: Inserts data or records into a table
• UPDATE: Updates the value of any record in the database
• DELETE: Deletes records from a table

Data Control Language (DCL) :

manage the access rights & permission control.

• GRANT: Grants access rights to database objects
• REVOKE: Withdraws permission from database objects

Transaction Control Language (TCL):

Its a set of commands that manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.

• COMMIT: An irreversible transaction, i.e., the previous image of the database before the transaction cannot be retrieved.
• ROLLBACK: Reverts the steps in a transaction in case of an error
• SAVEPOINT: Sets a savepoint in the transaction to which rollback can be executed
• SET TRANSACTION: Sets the characteristics of the transaction

Q4. What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

• NOT NULL — Restricts NULL value from being inserted into a column.
• CHECK — Verifies that all values in a field satisfy a condition.
• DEFAULT — Automatically assigns a default value if no value has been specified for the field.
• UNIQUE — Ensures unique values to be inserted into the field.
• INDEX — Indexes a field providing faster retrieval of records.
• PRIMARY KEY — Uniquely identifies each record in a table.
• FOREIGN KEY — Ensures referential integrity for a record in another table.

Q5. What is Primary Key & Foreign Key ?

• PRIMARY KEY is the column or field which specifies the unique row . this is a special kind of unique key . primary key cannot be null i.e., it implicit non null constraints Example: Each country in the world has a unique name, and code (ISO, ICAO, IOC, E. 164 or any other.
• FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

Q6. What is Normalization?

Normalization is about breaking down your data into smaller, related pieces and storing them in separate tables. This helps to reduce redundancy or say duplicates in a table (repeating the same information) and ensures that your data is consistent and easier to manage.

Q7. What are all different forms of Normalization?

1.First normal form (1NF): Remove all the duplicates columns from the table or in different words we can say a relation is in 1NF if it contains an atomic value. i.e each cell in your table should have just one value.’

Example: As we can observe, the Books Issued field has more than one value per record, and to convert it into 1NF, this has to be resolved into separate individual records for each book issued. Check the following table in 1NF form -

2. Second normal form (2NF): It fullfills all the requirements of 1NF .Placing the subsets of data in seperate table and creation of relation between tables using primary key.

Example: Consider the above example. the Students Table is currently in 1NF with a candidate key [Student, Address] to uniquely identify records. However, the non-prime attribute Books Issued depends partially on Student. To achieve 2NF, the table will be split into two. The Students table will have a new Primary Key, and the second table will have a Foreign Key linked to the Students table for referential integrity.

3. Third normal form (3NF): This meets all the requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

Example: In the 2NF Students Table, Student_ID functions as the primary key. However, Salutation depends on Student rather than this key, violating 3NF. To reach 3NF, we split tables: Students with Student_ID as primary key and Salutations with a Foreign Key linked to Students for unique identification.

4. Fourth normal form (4NF) : when data is not in 4NF Split your data into separate tables when one piece of information depends on another, so that the connection is direct and avoid unnecessary connections. Also, Eliminate redundant data that can be inferred from other information in your database.

Example: Imagine your table has columns for Friend Name, Dish Name, and Dish Type. In this scenario, the Dish Type depends only on the Dish Name, not on the Friend Name. In 4NF, you’d separate this into two tables: one for Friend Name and Dish Name, and another for Dish Name and Dish Type. This way, you prevent the Dish Type from being linked to the Friend Name indirectly.

5. Fifth normal form (5NF): A table is in 4NF only then. it is in 5NF and cannot be decomposed into any number of smaller table without any loss of data.

Q8. What is Denormalization?

Denormalization is the inverse process of normalization, where the normalized schema is converted into a schema that has redundant information. The performance is improved by using redundancy and keeping the redundant data consistent. The reason for performing denormalization is the overheads produced in the query processor by an over-normalized structure.

Q9. What is the difference between SQL and MySQL?

SQL(Structured Query Language) is the language you use to communicate with databases, and MySQL is one of the many tools that listen to that language and manage the data for you. Just like how you speak English to communicate, you use SQL to talk to databases, and MySQL ( is an RDMS (Relational Database Management System) is one of the systems that understands that language.

Q10. What are the D/B OLTP & OLAP?

OLTP stands for online transaction processing,it allows huge groups of people to execute massive amounts of database transactions in real time, usually via the internet. A database transaction occurs when data in a database is changed, inserted, deleted, or queried. whereas OLAP stands for online analytical processing. , whereas OLAP is an online database query response system.

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

Some common SQL clauses used in conjuction with a SELECT query are as follows:

• WHERE clause is used to filter records that are necessary, based on specific conditions.
• ORDER BY clause is used to sort the records based on some fields in ascending (ASC) or descending order (DESC).

SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;

• GROUP BY clause is like putting similar items together and using some aggregation functions to produce summarized results from the database.
• HAVING clause is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.

SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != "INDIA"
GROUP BY country
HAVING COUNT(studentID) > 5;

Q12. What is the difference between the RANK() and DENSE_RANK() functions?

• Imagine you have a list of people’s scores in a game, and you want to know who’s in the lead. The RANK() and DENSE_RANK() functions help you figure that out.
• With RANK(), if two people have the same score, they get the same rank, and then the next rank skips over those duplicates. So, if two people are tied for 4th place, the next rank is 7th.
• DENSE_RANK(), on the other hand, gives each person a unique rank even if they have the same score. It keeps counting in order without any gaps. So, if two people are tied for 4th place, the next rank is simply 5th.
• These functions help you see who’s winning without missing out on any ranks or repeating them.

Q13. List the different types of relationships in SQL.

There are different types of relations in the database:
1. One-to-One Relationship: Imagine you have two friends, Alice and Bob. Each of them has a single passport. That Each person is connected to only one passport & each passport is connected to only one person.

2. One-to-Many and Many-to-One Relationships: Think about a library. Each book in the library is written by one author but each author can have multiple books.As, one author can have many books, but each book belongs to only one author.

3. Many-to-Many Relationship: Picture a scenario where students can enroll in multiple courses and each course can have many students. This is a many-to-many relationship. Many students can be in many courses, creating a connection between different instances on both sides.

4. Self-Referencing Relationships: Imagine a company’s employee database. Each employee might have a manager who is also an employee in the same company. This is a self-referencing relationship. An employee (record) is related to another employee (record) within the same table.

Q14. What are Joins in SQL?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 types of joins, as you can refer to below:

• Inner Join: Retrieves only the rows where there is a match between the specified columns in both tables. Rows that do not have a match in both tables are excluded from the result.

SELECT * FROM Table_A JOIN Table_B; SELECT * FROM Table_A INNER JOIN Table_B;

• Left (Outer) Join: Retrieves all rows from the left (first) table and the matching rows (common values) from the right (second) table. If there is no match in the right table, the result will contain NULL values for the columns from the right table.

SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col;

• Right (Outer) Join: Similar to the left join, but it retrieves all rows from the right table and the matching rows (common values)from the left table. If there is no match in the left table, NULL values will be present for the columns from the left table.

SELECT *FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col;

• Full Join: returns all the records when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col;

• Self Join: Combines rows from a single table, treating it as two separate instances, often used to find relationships within the same table.

SELECT e1.employee_name, e2.supervisor_name
FROM employees AS e1
JOIN employees AS e2 ON e1.supervisor_id = e2.employee_id;

• Cross (Cartesian) Join: Creates a combination of all rows from the first table with all rows from the second table, resulting in a Cartesian product of the two tables.

SELECT stu.name, sub.subject FROM students AS stu CROSS JOIN subjects AS sub;

Q15. What is the difference between DELETE , TRUNCATE and DROP statements?

The DELETE command is utilized to remove individual rows from a table, whereas the TRUNCATE command is to eliminate all rows within a table. After the DELETE statement, data can be rolled back, whereas this is not possible with TRUNCATE. DELETE is categorized (DML) command, whereas TRUNCATE is a (DDL) command. DELETE generally takes more time compared to the faster TRUNCATE statement. DROP command removes a table and it cannot be rolled back from the database

Q16. What are Entities and Relationships?

• Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables store data that represents one type of entity. For example — A bank database has a customer table to store customer information. The customer table stores this information as a set of attributes (columns within the table) for each customer.
• Relationships: Relation or links between entities that have something to do with each other. For example — The customer name is related to the customer account number and contact information, which might be in the same table. There can also be relationships between separate tables (for example, customer to accounts).

Q17. How many Aggregate functions are available in SQL?

SQL provides 7 aggregate functions, which are given below:

• AVG(): returns the average value from specified columns.
• COUNT(): returns the number of table rows, including rows with null values.
• MAX(): returns the largest value among the group.
• MIN(): returns the smallest value among the group.
• SUM(): returns the total summed values(non-null) of the specified column.
• FIRST(): returns the first value of an expression.
• LAST(): returns the last value of an expression.

Q18. What is the ACID property in a database?

• Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
• Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
• Isolation: The main goal of isolation is concurrency control.
• Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

Q19. Are NULL values same as that of zero or a blank space?

A NULL value is not at all same as that of zero or a blank space. NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character.

Feedback/Suggestions:

For any feedback or suggestions, please comment below or drop a mail at anshika2797@gmail.com or contact me via LinkedIn at https://www.linkedin.com/in/anshika-aggarwal-033535210/