100+ SQL Interview Questions with Answers [2024]

The world is data-driven! Almost every organization, whether an aspiring tech venture or a seasoned global corporation, leans on data for day-to-day operations. SQL is a programming language that governs how this data is arranged and dealt with. A profession in SQL has experienced a dramatic rise in 2023. So, are you someone looking to pursue a career in SQL? If yes, you’ve come to the right place. In this blog, we have compiled a list of SQL interview questions and answers for freshers, 3 years, and 5 years of experienced professionals to help you ace your interview. Let’s begin!

What is SQL?

Structured Query Language, abbreviated as SQL or just “sequel”, is primarily a database communication language. It retrieves, adds, eliminates, or changes information from a database.

Assume databases to be warehouses, data tables to be filing cupboards, and data itself to be unique files. Now, consider a warehouse operator who remembers the database like the back of his hand and thus can quickly pull up any record you require. Now, this operator uses a unique language to communicate and perform tasks. SQL, in the database management system, is this language.

Why is it Important to Learn SQL?

Here are a few of the many reasons to learn SQL:

• It is the foundation of almost every enterprise because data is among the most precious assets on the planet. The ability to alter, analyze, control, and comprehend that data is critical. SQL enables you to perform all of these tasks.
• The implementation and success of SQL are proportional to the size of the database. It means that the bigger the data-driven task, the more successful SQL is.
• SQL Server provides a variety of high-reliability features that guarantee minimum disruption.
• It is also a global language applied to other fields and technologies; for example, understanding SQL can aid you in comprehending programming languages like Java and Python.
• SQL is also convenient to collaborate with because it is an open-source platform with a supportive environment. In the long run, it is a highly valuable skill in data science.

Basic SQL Interview Questions For Freshers

The following are the most frequently asked SQL interview questions for freshers. You can learn more about SQL through this in-depth SQL course.

Q1. What essentially is a database?

A database is a structured set of information digitally saved and retrieved from a local or remote software system. It is created by following predefined layouts and modeling methodologies.

Q2. What precisely is DBMS?

DBMS, or Database Management System, is a software application that creates, retrieves, updates, and manages databases. It also guarantees the accuracy and accessibility of information.

Q3. What do you mean by RDBMS? What distinguishes it from DBMS?

This is one of the most commonly asked SQL dba interview questions. RDBMS is the software that stores, manages, queries, and retrieves data from a relational database. The following are the differences between RDBMS and DBMS.

• RDBMS saves information in a tabular manner, whereas DBMS stores information in a file format.
• While data elements in DBMS must be accessed individually, RDBMS allows many data elements to be retrieved simultaneously.

Q4. What do you mean by Fields and Tables in SQL?

A table is a structured arrangement of information contained in columns and rows. These columns are also known as fields. 

Q5. What do you understand about SQL Constraints?

The Constraints in SQL serve as tools to describe the rules that apply to the information stored in the table. It can be applied to any number of fields in a SQL table after or during construction using the ALTER TABLE command.

Also Read: Types of SQL Commands

Q6. What is a Self-join in SQL?

A self-join is employed to combine two tables. It is frequently used to aggregate and evaluate rows within an identical database table.

Q7. What is a Cross-join in SQL?

A cross-join is the cartesian product between the two joined tables. 

Q8. What do you mean by an Index? Describe the many categories.

A database index provides a data framework that allows quick information retrieval in a table’s column or columns. It speeds up processes that acquire data out of a database table.

The following are some of the types of indexes.

• Unique and Non-Unique Indexes

The unique indexes aid in preserving the integrity of data by assuring that no two or more rows of information within a table have the same key value. Non-unique indexes are employed primarily to increase query efficiency by keeping a sorted sequence of frequently used data values.

• Non-Clustered and Clustered Indexes 

A clustered index physically reorders table records so they match the table index. A non-clustered index is a form of an index in which the logical sequence is unrelated to the physical recorded order of the table rows. The clustered indexes are bigger in size as compared to the non-clustered ones.

Q9. What is PL/SQL?

PL/SQL is a procedural language construct for Structured Query Language that was introduced by Oracle to handle the limitations of plain SQL. However, it is important to note that PL/SQL works only with the Oracle database.

Q10. How to create a table in SQL?

A table in SQL can be created with the following command- 

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

);

Q11. What is the difference between DROP and TRUNCATE commands?

DROP TABLE is used to delete the entire table. TRUNCATE TABLE is used for deleting the data in the table and not the table itself. 

Q12. Which command is used to create a database in SQL?

CREATE DATABASE command is used for creating a database in SQL. It is followed by the name of the database. 

Q13. How to view tables in SQL?

We can view tables in SQL using the following command- Show tables;

Q14. How can you change the table name in SQL?

We can change the table name in SQL using the following command-

ALTER TABLE table_name

RENAME TO new_table_name;

Q15. How do we delete a row from a table in SQL?

We can delete a row from a table by using the following command-

DELETE FROM table_name

WHERE [condition];

Q16. Define Nested Triggers.

The triggers that contain DML or Data Manipulation Language and find other triggers to modify data are known as Nested Triggers. Examples of such triggers are INSERT, UPDATE, and DELETE.

Q17. How can you implement multiple conditions in SQL?

We can use the WHERE clause with AND, and OR operators to implement multiple conditions in SQL.

Q18. How can we add a date in SQL?

We can use the following functions to insert dates in our database-

SELECT GETDATE();

Or

SELECT CURRENT_TIMESTAMP();

Or

SELECT SYSDATETIME();

Or

SELECT CURDATE();

Intermediate-Level SQL Interview Questions for 3-5 Years Experienced

The following are the important questions that SQL developers and professionals with 3-5 years of expertise might face in a SQL interview.

Q19. In what order are SQL statements processed?

The select clauses undergo processing in the order listed below.

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause
• TOP clause

Q20. Can we create a distributed query to retrieve data from another server and Oracle Database?

SQL Server can be linked to any server that supports an OLE-DB source from Microsoft. Oracle, for example, has an OLE-DB provider that Microsoft supplies to add it as a connected server to the SQL Server group.

Q21. Does dropping a table also remove related objects such as indexes, constraints,  views, defaults, columns, and stored procedures?

Yes, the SQL server deletes all relevant objects within a table. These include constraints, indexes, columns, defaults, etc. However, eliminating a table does not remove views or stored procedures outside of the table.

Q22. Is it possible to include an identity column in the decimal datatype?

SQL Server supports the identity column in the decimal datatype. They can be INTEGER, SMALLINT,  DECIMAL, BIGINT, or NUMERIC.

Q23. Is WHERE faster than SQL join?

There is no distinction. Inside MySQL, these statements are algebraically similar and will follow an identical implementation plan.

Q24. What are the various methods for executing a dynamic query?

Both EXEC sp_executesql statement and EXECUTE() function can be used for executing a dynamic query in SQL server.

Q25. What is the distinction between COALESCE() and ISNULL()?

ISNULL only accepts two parameters. The first parameter is tested for a NULL value; if it is NULL, that parameter is returned; otherwise, the first parameter is returned.

COALESCE accepts at least two parameters. You can use as many parameters as you want, but it will only return the first non-NULL parameter.

Q26. How do you get SQL file output?

To get SQL file output using command-line interface, write the following code-

sqlcmd -S servername -U username -P password -d databasename -Q “SELECT * FROM tablename” > C:\path\to\outputfile.txt

The command will execute the SQL query and redirect the output to the specified file.

Also Read: SQL Injection Attack

Q27. How do you stop SQL Server from sending you informational signals during and after executing a SQL statement?

SET NOCOUNT ON;

Q28. Explain the UPDATE command in SQL.

It is used to update the existing data in the table. The following is the syntax for the same-

UPDATE table_name

SET condition

WHERE condition;

Q29. How do you copy a table in SQL?

We can copy a table in SQL using the following code-

SELECT*

INTO newtable

FROM oldtable

WHERE condition;

Q30. Are null values the same as zero or blank space?

No, they are not the same. A null value signifies a value that is unavailable or unspecified. A zero is considered a numeric value and the blank space is a character. 

Q31. How do we disable a trigger in SQL?

With DISABLE TRIGGER trigger_name ON<> command, we can disable a trigger in SQL.

Q32. What is COMMIT in SQL used for?

COMMIT is used to finalize a transaction and making the changes permanent in a database. When we modify data, the changes made are held temporarily in the memory. COMMIT statement, upon being issued, saves these changes permanently in the database. 

Q33. What is Equi join?

A category where two or more tables have been joined using the equal to “=” operator is called Equi join. 

Q34. What is Normalization and Denormalization?

Normalization is a design technique for databases to organize tables. It helps in reducing data redundancy and dependency. Denormalization, on the other hand, adds redundant data to tables to increase the performance of a database. It is a kind of optimization technique.

Q35. How can we know the version of MySQL we are working on?

By using the command SELECT version(), we can know the version of MySQL that we are currently working on.

Q36. What is the difference between SQL and MySQL?

SQL is a query language whereas MySQL is a relational database that queries a database using SQL. 

Q37. What is Auto-increment?

Auto-increment is used when the user wants a unique number to be generated automatically when a new record is created in a table. PRIMARY KEY is used for this along with the AUTO_INCREMENT keyword.

Q38. Which operator does Pattern Matching?

LIKE operator does pattern matching.

Q39. Is it feasible to change the Views? If so, how? If not, why not?

Yes, we can edit views. However, a DML statement on a join view can only modify one of the view’s base tables (thus, even if the view is produced as a result of a join of several tables, only one table, the key preserved table, can be modified via the view).

Q40. Could you provide an overview of the various types of Joins accessible through SQL Server?

There are two major types of joins in SQL, i.e., INNER JOIN and OUTER JOIN. The OUTER joins are further classified into RIGHT, LEFT, FULL, and CROSS JOIN.

Q41. How important are cursors and while loops in a transactional database?

SQL cursors and while loops are common ways to repeatedly execute an SQL statement within conditional or specified limits in a transactional database.

Q42. What is the definition of a connected subquery?

Whenever a subquery is linked to a larger query, it is called a connected subquery. This approach is typically applied to self-joins.

Q43. Is a subquery with a correlation faster than an inner join?

Yes, a subquery with a correlation is faster than an inner join.

Q44. You’re supposed to be working on SQL optimization, and given an option between an associated subquery and a exists, which runs faster?

Exists runs faster in SQL.

Q45. Can we call DLL from the SQL server?

Yes, we can call DLL from the SQL server.

Q46. What is the disadvantage of including a scalar function in a where clause?

Scalar-valued functions cannot be employed within a where clause since indexes cannot be implemented. Scalar functions in such locations should be skipped if feasible because they significantly slow down the query.

Q47. What exactly are user-defined data types, and when should they be used?

A user-defined data type, also called UDT, is a data type created by modifying a current data type. They can be used to customize the inherent data types and construct unique data types.

Q48. What exactly is SQL Server Agent?

SQL Server Agent is a critical component of SQL Server. The server agent’s goal is to simplify implementing tasks by utilizing a scheduling engine. It also stores scheduled management task information in SQL Server.

Q49. Can you tell me anything about SQL Server’s magic tables?

A magic table is a logical table created by a SQL Server for activities such as inserting, removing, or updating. The most recent operations performed on the rows are automatically saved in magic tables. 

Q50. What are your thoughts on the stuff() SQL function?

The stuff() SQL function deletes a part of the text before inserting another portion of the string beginning at a specified place.

Q51. What do you understand about Views?

Views in SQL are a kind of virtual tables used to limit the number of tables that are displayed. They are basically the output of a statement with a name. Because views don’t have to be physically present, they require less space for storage.

Q52. What are the different types of SQL views?

There are four categories of views in SQL. They are as follows:

• A Simple View: It relies on one table and lacks a GROUP BY clause and other capabilities.
• A Complex View: It is constructed in SQL from numerous tables and incorporates a clause, GROUP BY.
• An Inline View: It is constructed using an SQL subquery in the FROM clause that offers a temporary table and simplifies a complex query.
• A Materialized View: It retains both the data and the definition it contains. It produces data clones by physically maintaining them.

Q53. Give an example of where Equi join is used.

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

The rows matching the condition after ON, i.e., where the department ID is the same, will be included in the result. First name, last name, and department name will be printed.

Q54. What are AGGREGATE and SCALAR functions?

We use AGGREGATE to evaluate mathematical calculations and return single values. It is calculated based on the columns in the table. SCALAR returns a single value based on the input. 

Q55. Give an example of AGGREGATE and SCALAR functions.

The following is an example of an AGGREGATE function. It will count the number of orders in the table ‘Orders’.

SELECT COUNT(*) AS TotalOrders

FROM Orders

The following is an example of the SCALAR function. It will convert the ‘CustomerName’ values to uppercase and print them in a new column. 

SELECT UPPER(CustomerName) AS UpperCaseName

FROM Order;

Q56. What is the difference between UNION and UNION ALL?

When two tables are merged into one, we use the UNION or UNION ALL function. The difference between the two is that UNION deletes the duplicate records whereas UNION ALL will include the duplicate records.

Q57. What is the difference between IN and EXISTS?

IN function works on list result set and EXISTS works on tables. IN function compares every value in the result table but EXISTS function ends the comparison upon finding a match.

Q58. How can you change the order of data in SQL?

We can use the ORDER BY clause to change the order of data in SQL. Here, we can specify ASC or DESC modifiers to decide if the data will be in ascending or descending order. 

Q57. How do you copy some specific columns in the new table?

We can copy specific columns in a new table using the following code-

SELECT column1, column2, column3, ….

INTO newtable

FROM oldtable

WHERE condition;

Q58. What is the Alias command in SQL?

Alias is a name that can be given to any table or column. You can use the AS function to set the alias name for your table or column. Here is an example:

SELECT e.FirstName AS EmployeeFirstName, e.LastName AS EmployeeLastName,

       d.DepartmentName AS DeptName

FROM Employees AS e

JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;

Here, the data from the two tables Employees (aliased as “e”) and Departments (aliased as “d”) will be selected and joined with the ‘DepartmentID’ as join condition.

Q59. What are the types of user-defined functions in SQL?

There are three types of user-defined functions in SQL: Scalar functions, Inline Table-valued functions, and Multi-statement valued functions.

Q60. What is Collation?

Collation is a set of rules that determine how characters can be stored and compared in SQL. It is used with character data types.

Also Read: Schema in SQL

Advanced-Level SQL Interview Questions for Experienced Professionals

Here are some advanced SQL interview questions and answers. 

Q61. How can you make empty tables that have identical structures as others?

This can be accomplished by using the INTO operator to copy the records from one table to a new table while setting a WHERE clause to false for all records.

Q62. Write the query to get the maximum and lowest wage of an employee.

Select max (wage) from Employees Union Select min (wage) from Employees;

Q63. How can we calculate the total number of weekends in the present month?

SELECT count (*) AS Weekends FROM (SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dt FROM Dual CONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1 ) Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’);

Q64. What is the query to display the employee’s name, job title, and manager? Display employees that do not have a manager.

select e.ename, e.job, m.ename Manager from emp e,emp m wheree.mgr=m.empno union select ename,job,’no manager’ from emp wheremgr is null;

Q65. In Oracle SQL, what is the query to drop all tables?

Begin For Begin For I In (Select * from Tabs) – // Tabs is a system table in which users get the different user defined table names. // Loop Execute immediate (‘Drop Table  ‘||i.table_name||’cascade constraints’); End loop; End;

Q66. What are the various database environments that are used in any project?

• Development Environment
• Test Environment
• UAT Environment
• Performance Testing Environment
• Production Environment

Q67. What do you mean by a bit-map index? 

If the table has separate values with fewer than 20 distinct values, a bit map index is used.

Q68. Create an SQL query to identify an employee with a salary of 10,000 or more.

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

Q69. Identify all Employee records that contain the term “Michael,” whether it was saved as MICHAEL, Michael, or michael.

SELECT * from Employees  WHERE  UPPER(EmpName) like ‘%MICHAEL%’;

Q70. Write an SQL query to find all duplicate rows in a table. 

SELECT     column, FROM     table_name GROUP BY column HAVING COUNT(column) > 1;

Q71. How to get alternate records from a table?

SELECT EmployeeID, FirstName, LastName, Department

FROM (

    SELECT EmployeeID, FirstName, LastName, Department, 

           ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum

    FROM Employees

) AS T

WHERE RowNum % 2 = 1;

The condition above will filter only the row number that is odd.

Q72. How will you retrieve the first four characters of the string?

SELECT SUBSTRING(EmployeeName,1,4) AS FirstThreeCharacters

FROM EmplyeeRecord;

Q73. What are ACID properties?

Atomicity, Consistency, Isolation, and Durability are a set of properties that ensure the reliability of database transactions. Atomicity ensures that no change happens in the database due to an incorrect part of the transaction. Consistency makes sure that databases are brought from one valid state to another through any transactions. Isolation deals with concurrent executions of transactions. Durability ensures that the transaction, once committed, remains so even in case of power loss or crashes. 

Q74. What is multi-version concurrency control?

It helps in avoiding unnecessary locks when more than one request is trying to access or modify the database at the same time. It helps in avoiding time lag during login. 

Q75. What is T-SQL?

Transact Structured Query Language is an extension to SQL for interaction with relational databases. It is a product of Microsoft. The types of functions that can be used in T-SQL are Aggregate, Ranking, Rowset, and Scalar. 

Q76. How do you create a temporary table?

We can create the temporary table by writing the following code-

SELECT col1,col2

INTO TempTable

FROM OrigTable;

Q77. What are tokens in PostgreSQL?

Tokens in PostgreSQL are the building blocks of a source code. They can be a constant, keyword, or identifier and are separated by whitespaces.

Q78. What are the different types of SQL Sandbox?

• Safe Access Sandbox: A user can perform SQL operations here but cannot access the memory nor can they create files.
• Unsafe Access Sandbox: It contains untrusted codes and the user can access the memory. 
• External Access Sandbox: Here, the user is allowed to access the files but they do not have permission to manipulate the memory allocation.

Q79. Name the different types of SQL queries.

There are four types of queries in SQL. 

• Data Definition Language (DDL) that caters to the creation of objects.
• Data Manipulation Language (DML) is used for manipulating data.
• Data Control Language (DCL) deals with assigning and removing permissions.
• Transaction Control Language (TCL) deals with saving and restoring changes to a database.

Q80. What is lock escalation?

Initially, the smallest level lock is looked at by the query. But if many rows are locked, it escalates to a page lock. Then again, if too many pages are locked, it may escalate to a table lock. This is called a lock escalation.

SQL Interview Questions for Developers

The frequently asked SQL interview questions and answers listed below will assist you in preparing for the SQL developer interview. Let’s take a closer look at them.

Q81. What do you mean by a stored procedure?

A stored procedure refers to an SQL code piece that has been prepared once and saved. In other words, it is a function that consists of multiple SQL commands that help connect the database. 

Q82. Describe the Inner Join.

Inner Join returns records from two tables that have matching values.

Q83. What do you think of a temporary table? 

It stores and processes intermediate results. It is established and can be erased once it is not needed. Such tables are crucial for scenarios where transitory data has to be stored.

Q84. What precisely is Hybrid OLAP?

It stores multidimensional data and uses an assortment of multifunctional or multidimensional data structures and relational database tables. 

Q85. What do you mean by “Self Join”? 

In SQL, a self-join is used to join a table to itself. Every row of the table is connected to itself and to other rows depending on specific conditions.

Q86. What is the definition of a cursor in SQL? 

A database cursor is a control that enables you to traverse across the documents or rows of a table. It works as a pointer within an assortment of rows. They are important for database operations, including insertion, extraction, and deletion.

Q87. What is the use of an SQL INTERSECT operator?

The INTERSECT operator in SQL is used to join two select commands and returns just the shared rows or records. 

Q89. What is the distinction between SQL’s IN and BETWEEN operators?

The BETWEEN operation is utilized to classify rows according to a set of values. The entire amount of values that fall within the two specified regions is returned by the BETWEEN operator. The IN condition function searches for values among a specified range of values. We employ the IN operator when there are multiple values to choose from.

Q91. How do HAVING and WHERE clauses differ?

In SQL, the HAVING clause can be used with aggregates, whereas the WHERE clause cannot be. The WHERE clause only operates with data in a single row.

Q92. What is the difference between RANK and DENSE_RANK functions?

RANK will assign non-consecutive “ranks” to the values in a set whereas DENSE_RANK will assign consecutive “ranks” to the values in a set. In RANK, there will be a gap in integer ranking value in case of a tie, but there will be no gap in DENSE_RANK in a similar situation.

Q93. What is SQL injection? 

It is a cyberattack that allows hackers to insert malicious code into your SQL code. Through this, they can view, modify and delete data in the database. 

Q94. What are the different types of Collation sensitivity?

The different types of collation sensitivity are:

• Case Sensitivity: It differentiates between uppercase and lowercase letters.
• Kana Sensitivity: It differentiates between two Hiragana and Katakana Japanese characters. 
• Width Sensitivity: It differentiates between full and half-width characters.
• Accent Sensitivity: It differentiates between accented and unaccented characters.

Q95. What is a Data Warehouse?

Data Warehouse is a central repository that contains data from different sources. The data stored here is further consolidated and transformed to be made available for mining and online processing. 

Q96. How do you change the authentication mode in SQL Server?

We have to first run SQL Enterprise Manager from the Microsoft SQL Server program group. For this, begin by clicking on Start, then Programs, and then Microsoft SQL Server. Click on SQL Enterprise Manager. Then, from the Tools menu, select the server. Next, select SQL Server Configuration Properties, and select the Security page. 

Q97. What are parallel queries in PostgreSQL?

It is a feature used for creating query plans capable of exploiting multiple CPU processes in order to execute the queries faster. 

Q98. What is Write Ahead Logging?

With Write Ahead Logging, the changes are logged before they are made in the database. The feature helps in increasing data reliability. After the database crashes, this comes into use. We can know up until where the work was complete and begin from the next point. 

Q99. What is the INITCAP function?

INITCAP is a type of case manipulation function in SQL that returns the string with the first letter in uppercase and the rest of the letters in lowercase.

Q100. What is the ON DELETE CASCADE constraint?

It is used in MySQL to delete the rows from the child table when the rows of the parent table are deleted. 

Q101. What are BLOB and TEXT in MySQL?

BLOB refers to large binary objects and can hold a variable amount of data. TEXT is a case-sensitive BLOB that has non-binary strings or character strings as values.

Conclusion

SQL’s prominence has increased, where practically every developer depends on it to create their software’s storage functionality. It makes SQL a fascinating language to learn. This blog offers SQL interview questions and answers for freshers and seasoned professionals. So, if you’re looking for questions to ace your SQL interview, you are at the right place.