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
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:
| Type | Purpose | Key Difference | Example |
|---|---|---|---|
| Primary Key | Uniquely 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 Key | Ensures 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 Key | Links 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. |
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 Join | Description | Syntax |
|---|---|---|
| Inner Join | It 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 Join | Captures 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 Join | Captures 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 Join | Extracts 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 Join | It 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; |
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 Form | Description | Example |
|---|---|---|
| 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. |
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:
| Category | Statement | Purpose | Example |
|---|---|---|---|
| DML | SELECT | Retrieve data. | SELECT * FROM Employees; |
| DML | INSERT | Add new data. | INSERT INTO Products (Name, Price) VALUES ('Widget', 10.99); |
| DML | UPDATE | Modify existing data. | UPDATE Customers SET Email = 'new@email.com' WHERE CustomerID = 123; |
| DML | DELETE | Remove data. | DELETE FROM Orders WHERE OrderID = 456; |
| DDL | CREATE | Create database objects. | CREATE TABLE Students (StudentID INT, Name VARCHAR(50)); |
| DDL | ALTER | Modify database objects. | ALTER TABLE Employees ADD COLUMN Department VARCHAR(20); |
| DDL | DROP | Delete database objects. | DROP TABLE Products; |
| DDL | TRUNCATE | Delete all rows, keep structure. | TRUNCATE TABLE Logs; |
| DCL | GRANT | Give privileges. | GRANT SELECT ON Employees TO User1; |
| DCL | REVOKE | Remove privileges. | REVOKE INSERT ON Products FROM User2; |
| TCL | COMMIT | Save changes. | COMMIT; |
| TCL | ROLLBACK | Discard changes. | ROLLBACK; |
| TCL | SAVEPOINT | Set rollback point. | SAVEPOINT sp1; |
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 Property | Description | Simple Example |
|---|---|---|
| Atomicity | Transactions 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. |
| Consistency | Data 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. |
| Isolation | Multiple 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. |
| Durability | Changes 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. |
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:
| Command | Description | Differences |
|---|---|---|
| Delete | Removes a set of rows from a table depending on the given condition. | Operates on rows individually. |
| Truncate | Clear all rows from a table very fast. | Cannot be rolled back, but faster. |
| Drop | Deletes an entire table and its structure. | Irreversible and removes the table. |
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 Type | Description | Example |
|---|---|---|
| NOT NULL | Ensures a column cannot have NULL values. | Name NOT NULL |
| UNIQUE | Ensures that values in a column are unique. | Email UNIQUE |
| PRIMARY KEY | Ensures uniqueness and is a unique identifier for a row. | UserID PRIMARY KEY |
| FOREIGN KEY | Enforces referential integrity, linking data in one table to another. | OrderID FOREIGN KEY REFERENCES Orders(OrderID) |
| CHECK | Applies a condition to values in a column. | Age CHECK (Age >= 18) |
| DEFAULT | Provides a default value for a column. | Status DEFAULT 'Active' |
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:
| Concept | Description | Differences |
|---|---|---|
| Union | Combines and returns distinct rows from result sets. | Eliminates duplicate rows. |
| Union ALL | Combines and returns all rows from result sets, including duplicates. | Retains duplicate rows. |
Q-15. What do you know about the stored procedure? What are its benefits and drawbacks?
Ans.
Stored Procedures:
| Aspect | Description |
|---|---|
| Definition | A stored procedure is a ready-made script on the database server. It’s like a toolkit with specific tasks you can use whenever needed. |
| Benefits | They simplify complex tasks, reduce data traffic, promote code reuse, and improve database performance. |
| Drawbacks | Writing them can be complex, and they’re tied to specific databases, limiting their flexibility. |
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:
| Concept | Description | Benefits | Drawbacks |
|---|---|---|---|
| Database View | A 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. |
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:
| Aspect | Description |
|---|---|
| Definition | Triggers are automatic actions triggered by specific events like data changes (INSERT, UPDATE, DELETE). |
| Benefits | They ensure data is correct, automate tasks, and respond to changes. Useful for auditing and complex rules. |
| Explicit Calls | While usually automatic, some systems allow you to trigger them manually. |
Q-19. What are the different data clauses available in SQL?
Ans.
SQL Data Clauses:
| Clause | What It Does | Unique Example |
|---|---|---|
| SELECT | Gets data from tables. | SELECT Name FROM Customers; |
| FROM | Tells where to get data. | SELECT * FROM Products; |
| WHERE | Filters data based on conditions. | SELECT * FROM Orders WHERE Status = 'Shipped'; |
| GROUP BY | Groups similar data together. | SELECT Country, COUNT(*) FROM Customers GROUP BY Country; |
| HAVING | Filters grouped data based on conditions. | SELECT Country, COUNT(*) FROM Customers GROUP BY Country HAVING COUNT(*) > 5; |
| ORDER BY | To arrange data in a specific order. | SELECT ProductName, Price FROM Products ORDER BY Price DESC; |
| LIMIT/OFFSET | Controls how many rows to return and from where. | SELECT * FROM Orders LIMIT 10 OFFSET 20; |
| JOIN | Collate data from different tables. | -- Match authors with their published books |
| UNION | Combines results from multiple queries. | (SELECT ProductName FROM Products) UNION (SELECT ProductName FROM DiscontinuedProducts); |
| DISTINCT | Removes duplicate values. | SELECT DISTINCT Category FROM Products; |
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:
| Level | A Brief Description | Examples |
|---|---|---|
| Read Uncommitted | Allows transactions to read uncommitted changes from other transactions. | Transaction A can read a value that Transaction B is in the middle of updating. |
| Read Committed | Permits transactions to read only committed data from other transactions. | Transaction A can’t read the value until Transaction B has committed it. |
| Repeatable Read | Ensures 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. |
| Serializable | Provides 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. |
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.
