SQL Interview Questions and Answers (5+ Exp)

Meenakshi Agarwal
By
Meenakshi Agarwal
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large...
23 Min Read

Database and SQL skills are highly sought-after by various IT companies, such as Accenture, Microsoft, Cognizant, Stack Overflow, and Dell. These skills are also essential for DBAs and QA engineers. In this article, we covered some of the core SQL interview questions with their answers. especially for engineers having 5+ years of experience.

A database, sometimes referred to as the backend, is the backbone of any Enterprise-grade web service. So it becomes inevitable to check it for speed and scale. And, in order to effectively use databases, it is important to learn SQL. This is the default database language to take commands from the users. All famous database solutions like MYSQL, Oracle, MongoDB, PostgreSQL, and SQL Server support it.

Hence, SQL is a valuable skill not only for Software engineers but also for interviewers to conduct evaluations. While most engineers already know the basics of SQL, it is important for them to focus on the areas where interviewers may grill them. This is where this tutorial will help and give a list of important SQL performance interview questions and answers. Just keep in mind that you keep track of other areas.

It’s imperative that you read all these SQL interview questions and answers. But you need to be equally strong in other areas too. If you have missed the following posts, then care to go through each of these.

Let’s now explore the SQL Q&A section.

20 SQL Interview Questions and Answers for Experienced.

In a software engineer interview with a focus on SQL, you can expect to be asked questions about the following topics:

  • SQL fundamentals, such as data types, operators, functions, and clauses
  • Database design, including database normalization, table relationships, and indexes
  • Complex queries, such as subqueries, joins, and window functions
  • Performance optimization, such as query planning, execution plans, and caching
  • Transactions, such as ACID properties, isolation levels, and concurrency control
  • SQL joins, such as different types of joins, how to use them, and when to use them
Top SQL Interview Questions One Should Know

Q-1. What is the primary difference between SQL and PL/SQL?

Ans.

PL/SQL is an advanced form of SQL developed by Oracle in the early 90s as a superset of SQL. It inculcates many additional programming features to enable application development at the database level. Please refer to the below list.

  • Modular structure.
  • Control-Flow statements and loops.
  • Types, constants, and variables.
  • User-defined data types.
  • Exceptional handling.

Q-2. How does a Primary Key differ from the Unique and Foreign Key?

Ans.

Primary Key vs. Unique Key vs. Foreign Key:

TypePurposeKey DifferenceExample
Primary KeyUniquely identifies each row in a table.Must be unique for every row. Only one per table.In a “Students” table, “StudentID” is the primary key, giving each student a unique ID.
Unique KeyEnsures values in a column are unique, but allows some to be empty.Requires unique values but allows some to be blank. Multiple unique keys are possible.In a “Products” table, “ProductCode” is a unique key, letting some products have no code.
Foreign KeyLinks data in one table to another.Relates to a primary key in another table, ensuring linked values exist.In an “Orders” table, “CustomerID” is a foreign key, linking to the “Customers” table’s primary key to associate orders with customers.
Check the Difference Between the DB Keys

The above points and examples should help illustrate the differences between Primary Keys, Unique Keys, and Foreign Keys in a DB context.

Q-3. What is the use of a SQL JOIN and what all types of JOIN exist?

Ans.

SQL JOIN is like a matchmaker. It works by unifying the rows of two or more than two tables on the basis of a specific field.

Type of JoinDescriptionSyntax
Inner JoinIt is the most basic Join which collects data from both tables if the given columns match.SELECT * FROM tableX INNER JOIN tableY ON tableX.column_name = tableY.column_name;
Left JoinCaptures all rows of the left-side table including the matching rows from the 2nd table. If matching fails, then the output includes NULL in place of the 2nd table.SELECT * FROM tableX LEFT JOIN tableY ON tableX.column_name = tableY.column_name;
Right JoinCaptures all rows of the right-side table including the matching rows from the first table. If nothing matches, then the output includes NULL in place of the first table.SELECT * FROM tableX RIGHT JOIN tableY ON tableX.column_name = tableY.column_name;
Full Outer JoinExtracts every single row from each of the tables, despite matching succeeds or fails.SELECT * FROM tableX FULL OUTER JOIN tableY ON tableX.column_name = tableY.column_name;
Self JoinIt virtually splits a table into two and joins the two copies together based on a filter.SELECT * FROM tableX INNER JOIN tableX AS tableY ON tableX.column_name = tableY.column_name WHERE tableX.id <> tableY.id;
SQL Join Types

Q-4. What is Normalization and how does it work?

Ans.

Database normalization is the process of arranging data in a database to cut down redundancy and improve data integrity. It implies splitting the data into logical forms and deriving relationships between them.

Q-5. What different types of data normalization exist?

Ans.

There are basically the following commonly used types :

Normal FormDescriptionExample
1st Normal Form (1NF)Eliminate data duplication and ensure atomic values.In a table that stores customer orders, ensure that each customer’s full name is split into separate “First Name” and “Last Name” columns.
2nd Normal Form (2NF)Eliminate partial dependencies by linking attributes to the entire primary key.In an order details table with a compound primary key consisting of “Order ID” and “Product ID,” ensure that “Product Name” is linked to both keys to avoid partial dependencies.
3rd Normal Form (3NF)Remove transitive dependencies to make attributes independent of other non-key attributes.In the “Customers” table, separate attributes like “Phone” and “Email” to make them independent of each other and any other non-key attributes.
Decoding Types of DB Normalization

Q-6. How does the superkey differ from a candidate key?

Ans.

Check the below points for clarity.

  • Superkey: It’s a set of attributes that can uniquely identify rows, but it may have more attributes than needed.
  • Candidate Key: It’s a superkey with the fewest attributes required for unique identification.

Q-7. What types of statements does SQL support?

Ans.

SQL Command Categories: SQL encompasses diverse command categories for effective database management. These commands are organized into four key groups: Data Definition Commands (DDC), Data Control Commands (DCC), Data Manipulation Commands (DMC), and Transaction Control Commands (TCC). Let’s delve into each of these categories:

CategoryStatementPurposeExample
DMLSELECTRetrieve data.SELECT * FROM Employees;
DMLINSERTAdd new data.INSERT INTO Products (Name, Price) VALUES ('Widget', 10.99);
DMLUPDATEModify existing data.UPDATE Customers SET Email = 'new@email.com' WHERE CustomerID = 123;
DMLDELETERemove data.DELETE FROM Orders WHERE OrderID = 456;
DDLCREATECreate database objects.CREATE TABLE Students (StudentID INT, Name VARCHAR(50));
DDLALTERModify database objects.ALTER TABLE Employees ADD COLUMN Department VARCHAR(20);
DDLDROPDelete database objects.DROP TABLE Products;
DDLTRUNCATEDelete all rows, keep structure.TRUNCATE TABLE Logs;
DCLGRANTGive privileges.GRANT SELECT ON Employees TO User1;
DCLREVOKERemove privileges.REVOKE INSERT ON Products FROM User2;
TCLCOMMITSave changes.COMMIT;
TCLROLLBACKDiscard changes.ROLLBACK;
TCLSAVEPOINTSet rollback point.SAVEPOINT sp1;
SQL commands

Q-8. What is the role of COMMIT in an SQL transaction?

Ans.

COMMIT finalizes the changes, introduced by all SQL statements included in the transaction as permanent in the database.

Thus the changes made by the SQL statements of a transaction become visible to other user session transactions that start only after the transaction gets committed.

Q-9. What does ACID signify in the case of a database?

Ans.

ACID in Databases: ACID represents Atomicity, Consistency, Isolation, and Durability. These principles make sure database transactions are trustworthy and stable. They ensure that database operations work consistently and reliably, even when things go wrong or many people access the data at once.

ACID PropertyDescriptionSimple Example
AtomicityTransactions are all-or-nothing. Either complete the entire transaction or none of it.In a money transfer, if the debit works but the credit fails, both actions are rolled back.
ConsistencyData must meet integrity rules. Every change keeps the database valid.An e-commerce site won’t allow selling more items than in stock, ensuring data accuracy.
IsolationMultiple transactions run separately, so they don’t interfere or create confusion.Two users booking the last seat are handled one at a time to avoid conflicts.
DurabilityChanges are permanent and survive crashes. Once data is saved, it stays saved.After changing your email, even if the server crashes, your new email remains saved.
ACID in Databases

Q-10. How does an SQL delete differ from the truncate and drop commands?

Ans.

In SQL, Delete is similar to both Truncate and Drop commands. They help us manage data and objects in a database. Each of them serves different purposes and has distinct characteristics. Here’s a concise comparison in table form:

CommandDescriptionDifferences
DeleteRemoves a set of rows from a table depending on the given condition.Operates on rows individually.
TruncateClear all rows from a table very fast.Cannot be rolled back, but faster.
DropDeletes an entire table and its structure.Irreversible and removes the table.
SQL Delete vs. Truncate vs. Drop

Q-11. What is a database index? Briefly tell about their different types.

Ans.

Database index: A data structure that speeds up data retrieval by mapping column values to row locations.

Different types of indexes:

  • Clustered index: Stores the data rows in the table in the same order as the index.
  • Non-clustered index: A separate data structure that stores the values of the indexed columns in a sorted order, but does not store the data rows themselves.
  • Unique index: Ensures that no two rows in the table have the same value for the indexed column.
  • Full-text index: This can be used to search for text within the data in a table.
  • Spatial index: This can be used to search for data that is associated with a specific location.

Which type of index to use depends on the specific workload.

Example: If you frequently query for all employees in a specific department, a clustered index on the department column would be beneficial. If you frequently query for employees with a specific salary, a non-clustered index on the salary column would be beneficial.

Q-12. What is the role of a Subquery?

Ans.

Role of a Subquery: A subquery is used to retrieve data for a main query, acting as a nested query within a second query to filter, calculate, or retrieve specific information. It helps in making complex queries more efficient and concise.

Unique Situation Example:

Imagine a database of products and their prices. You want to find products that cost more than the average price of all products. A subquery can calculate the average price, and the main query can then identify products with prices exceeding that average.

Q-13. What do you think of a DBConstraint? What all constraint types exist in SQL?

Ans.

Database Constraints are like rules for a database. They make sure data is accurate and behaves as expected. They enforce things like “no empty values,” “each value must be unique,” or “this column is the main ID.” Constraints help keep the data in order.

Constraint TypeDescriptionExample
NOT NULLEnsures a column cannot have NULL values.Name NOT NULL
UNIQUEEnsures that values in a column are unique.Email UNIQUE
PRIMARY KEYEnsures uniqueness and is a unique identifier for a row.UserID PRIMARY KEY
FOREIGN KEYEnforces referential integrity, linking data in one table to another.OrderID FOREIGN KEY REFERENCES Orders(OrderID)
CHECKApplies a condition to values in a column.Age CHECK (Age >= 18)
DEFAULTProvides a default value for a column.Status DEFAULT 'Active'
List of DB Constraints

Q-14. How is Union different from Union ALL?

Ans.

Union vs. Union ALL

In SQL, “Union” and “Union ALL” are operators used to combine rows from multiple result sets. However, they differ in how they handle duplicate rows. Here’s a concise comparison in table form:

ConceptDescriptionDifferences
UnionCombines and returns distinct rows from result sets.Eliminates duplicate rows.
Union ALLCombines and returns all rows from result sets, including duplicates.Retains duplicate rows.
Union vs. Union ALL

Q-15. What do you know about the stored procedure? What are its benefits and drawbacks?

Ans.

Stored Procedures:

AspectDescription
DefinitionA stored procedure is a ready-made script on the database server. It’s like a toolkit with specific tasks you can use whenever needed.
BenefitsThey simplify complex tasks, reduce data traffic, promote code reuse, and improve database performance.
DrawbacksWriting them can be complex, and they’re tied to specific databases, limiting their flexibility.
To help you easily grasp the concept of stored procedure

This table puts up a simple and easy-to-grasp overview of stored procedures, their pros, and the most likely cons.

Q-16. What is a database view and what benefits and drawbacks does it bring?

Ans.

Database View

A database view is an important concept for databases. It provides a clean way to access data, focusing on improving data integrity and reducing redundancy. However, views also come with some drawbacks, here’s a brief overview in table form:

ConceptDescriptionBenefitsDrawbacks
Database ViewA virtual table created by a query over one or more base tables.1. Data abstraction and security.1. Slower query performance.
2. Simplified data access.2. May not support all operations.
3. Enhanced data integrity.3. Managing and keeping the database view up to date can be challenging.
4. Reduced data redundancy.4. Limited in handling very complicated data changes like calculating sums, averages, or other aggregated values over large datasets.
Views in SQL

Q-17. List the built-in functions, available in SQL.

Ans.

Following are the important built-in functions available in SQL.

  • AVG(): Returns the average value.
  • COUNT(): Returns the number of rows.
  • FIRST(): Returns the first value.
  • LAST(): Returns the last value.
  • MAX(): It gives the largest value as output.
  • MIN(): It gives the smallest value as output.
  • SUM(): Outputs the Sum.
  • UCASE(): Converts a value to upper case.
  • LCASE(): Converts a value to lowercase.
  • MID(): Extract the middle character from a string or number.
  • LEN(): Returns the length of a text field.
  • ROUND(): Round Off a numeric field to the number of decimals specified.
  • NOW(): Returns the current system date and time.
  • FORMAT(): defines how a field is to be displayed.

Q-18. What is a trigger, its benefits and can we call it explicitly?

Triggers in SQL:

AspectDescription
DefinitionTriggers are automatic actions triggered by specific events like data changes (INSERT, UPDATE, DELETE).
BenefitsThey ensure data is correct, automate tasks, and respond to changes. Useful for auditing and complex rules.
Explicit CallsWhile usually automatic, some systems allow you to trigger them manually.
Database Triggers

Q-19. What are the different data clauses available in SQL?

Ans.

SQL Data Clauses:

ClauseWhat It DoesUnique Example
SELECTGets data from tables.SELECT Name FROM Customers;
FROMTells where to get data.SELECT * FROM Products;
WHEREFilters data based on conditions.SELECT * FROM Orders WHERE Status = 'Shipped';
GROUP BYGroups similar data together.SELECT Country, COUNT(*) FROM Customers GROUP BY Country;
HAVINGFilters grouped data based on conditions.SELECT Country, COUNT(*) FROM Customers GROUP BY Country HAVING COUNT(*) > 5;
ORDER BYTo arrange data in a specific order.SELECT ProductName, Price FROM Products ORDER BY Price DESC;
LIMIT/OFFSETControls how many rows to return and from where.SELECT * FROM Orders LIMIT 10 OFFSET 20;
JOINCollate data from different tables.-- Match authors with their published books
SELECT Authors.AuthorName, Books.BookTitle
FROM Authors
INNER JOIN Books
ON Authors.AuthorID = Books.AuthorID;
UNIONCombines results from multiple queries.(SELECT ProductName FROM Products) UNION (SELECT ProductName FROM DiscontinuedProducts);
DISTINCTRemoves duplicate values.SELECT DISTINCT Category FROM Products;
Different SQL Clauses

This table offers a simple and clear understanding of SQL data clauses, along with uniquely derived examples for each clause.

Q-20. How many isolation levels exist in SQL?

Ans.

SQL supports multiple isolation levels, which determine how transactions interact with each other in a multi-user database environment. Here’s a concise summary in table form:

LevelA Brief DescriptionExamples
Read UncommittedAllows transactions to read uncommitted changes from other transactions.Transaction A can read a value that Transaction B is in the middle of updating.
Read CommittedPermits transactions to read only committed data from other transactions.Transaction A can’t read the value until Transaction B has committed it.
Repeatable ReadEnsures that a transaction sees the data it reads as consistent and unchanged during the transaction.Transaction A reads a specific row, and it remains unchanged throughout the transaction.
SerializableProvides the highest level of isolation, where transactions appear to run sequentially without interference.Transactions A and B appear to run one after the other without overlapping changes.
SQL Isolation Levels

These levels offer varying levels of data consistency and protection against concurrent transactions.

Summary – Top SQL Interview Questions for 5+ Yrs. of Exp.

This post is meant for QA Engineers and focuses on the top twenty SQL interview questions. SQL skills are essential for comprehensive validation. We appreciate your feedback and welcome your suggestions for future topics. If you found this post useful, please share it with your colleagues and on social media. Your support is valuable, enabling us to create content that benefits you. We’re here to provide you with the knowledge and resources you need for success in your role as a QA Engineer.

We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.

Keep Learning,
TechBeamers.

Share This Article
Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *