Top 40+ SQL Interview Questions for Experienced Professionals

You must master the SQL interview questions for experienced professionals to crack the most challenging interview rounds. Your in-depth knowledge of SQL is vital for dealing with large amounts of data, retrieving specific results, or drawing quick insights.

If you are applying for data scientists or data engineer positions at FAANG+ companies, you must go through the most anticipated SQL interview questions for experienced professionals. You must also practice SQL query interview questions for experienced professionals. Continue reading to assess your SQL tech interview prep and clear some of the crucial concepts.

If you are preparing for a tech interview, check out our technical interview checklist, interview questions page, and salary negotiation e-book to get interview-ready!

Having trained over 15,000 software engineers, we know what it takes to crack the toughest tech interviews. Our alums consistently land offers from FAANG+ companies. The highest ever offer received by an IK alum is a whopping $1.267 Million!

At IK, you get the unique opportunity to learn from expert instructors who are hiring managers and tech leads at Google, Facebook, Apple, and other top Silicon Valley tech companies.

Want to nail your next tech interview? Sign up for our FREE Webinar.

Here's what we'll cover:

• SQL Interview Questions and Answers for Experienced Professionals
• SQL Queries Interview Questions and Answers for Experienced Professionals
• Oracle PL/SQL Interview Questions and Answers for Experienced Professionals
• Oracle SQL Interview Questions and Answers for Experienced Professionals
• FAQs on SQL Interview Questions

SQL Common Interview Questions and Answers for Experienced Professionals

You must go through the following curated list of SQL interview questions for experienced professionals, which are likely to be asked during the SQL interview.

1. What is normalization? What are the different normalizations?

The process of organizing fields and tables of a database to minimize redundancy and dependency. It allows you to add, delete or modify fields that can be incorporated into a single table. The different normalizations are as follows:

• First Normal Form (1NF): This should remove a table's duplicate columns. It is for the identification of unique columns and the creation of tables for the related data.
• Second Normal Form (2NF): A table is in its second normal form if it meets all requirements of the first normal form and places the subsets of data in separate tables. The relationships between tables are created using primary keys.
• Third Normal Form (3NF): The table should be in the second normal form. There should be no dependency on primary key constraints.
• Fourth Normal Form (4NF): It should meet all the requirements of the third normal form and should not have multi-valued dependencies.

2. What is denormalization?

Using the denormalization technique, you can access the data from higher to lower normal forms of the database. As the name suggests, it is a reversal of normalization, i.e., it introduces redundancy into a table as it incorporates data from the related tables.

3. What is collation? What are the different types of collation sensitivity?

It is a set of rules determining how character data can be sorted and compared. You can use collation to compare A and other language characters. It depends on the width of the characters. You can use ASCII values to compare these character data.

The different types of collation sensitivity are as follows:

4. What do you understand about CASE statements?

You can use a CASE statement to check certain conditions and return a value based on whether the conditions are evaluated as true. CASE allows you to bring logic, conditions, and order in combination with clauses like WHERE and ORDER BY.

A CASE expression is different from a CASE statement. An expression evaluates a list of conditions. It returns one of the multiple possible result expressions. Its result is a single value, whereas a CASE statement result is the execution of a sequence of statements.

5. What are some common SQL commands?

Some common commands are as follows:

• UNION: It combines the results of two tables. It also removes duplicate rows from the tables.
• MINUS: It returns rows from the first query and not from the second query.
• INTERSECT: It returns rows returned by both the queries.
• DELETE: It removes one or more rows from a table.
• TRUNCATE: It deletes all the rows from the table. Thus, it frees the space containing the table.
• INSERT: It inserts data into the row of a table.
• UPDATE: It updates or modifies the value of a column in the table.
• ALIAS: It is a name that you can give to a table or column. You can refer to this name in a WHERE clause to identify the table or column.
• ALTER: It alters the structure of the database. It allows you to add a column or modify an existing one.  
• DROP: It removes tables and databases from RDBMS.
• CREATE: It defines the database structure schema.
• GRANT: It gives the user access privileges to a database.
• REVOKE: It takes back permissions from the user.
• COMMIT: It saves all the transactions to the database.
• ROLLBACK: It allows you to undo transactions that haven't been saved to the database.
• SAVEPOINT: It allows you to set a savepoint within a transaction.
• SELECT: It selects the attribute as described by the WHERE clause.

6. What is SQL and why is it used?

SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It is used to create, update, and query the data stored in these databases. SQL is the standard language for interacting with relational databases and is supported by most relational database management systems (RDBMS) such as MySQL, Oracle, SQL Server, and PostgreSQL.

7. What are the main differences between SQL and other programming languages?

The main differences between SQL and other programming languages are as follows:

8. What are the main data types in SQL?

In SQL, the main data types are as follows:

• INT (integer)
• FLOAT (floating point number)
• CHAR (fixed-length character string)
• VARCHAR (variable-length character string)
• DATE (date)
• DATETIME (date and time)
• BOOLEAN (true or false)

9. How do you select, insert, update and delete data in SQL?

10. What is the difference between a primary key and a foreign key?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. It is used to enforce the integrity of the data in the table and to create relationships with other tables. A foreign key is a column or set of columns in a table that references the primary key of another table. It is used to enforce referential integrity and to create relationships between tables.

11. How do you create a table and its constraints in SQL?

To create a table and its constraints in SQL, you can use the CREATE TABLE statement. Here is an example of how to create a table called "orders" with a primary key and a foreign key:

CREATE TABLE orders (

  order_id INT PRIMARY KEY,

  customer_id INT,

  order_date DATE,

  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

12. How do you use JOINs in SQL?

JOINs in SQL are used to combine data from two or more tables based on a related column between them. There are several types of JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

An example of using an INNER JOIN in SQL would be as follows:

SELECT *

FROM orders

JOIN customers

ON orders.customer_id = customers.customer_id;

13. How do you use aggregate functions in SQL?

Aggregate functions in SQL are used to perform calculations on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

An example of using the SUM aggregate function in SQL would be:

SELECT SUM(price)

FROM products;

14. How do you use subqueries and temporary tables in SQL?

A subquery is a query that is nested inside another query, and it is used to return a set of results that will be used by the outer query. Subqueries can be used in various parts of a SQL statement, such as in the SELECT, FROM, WHERE, and HAVING clauses. Here is an example of how to use a subquery in the WHERE clause of a SELECT statement:

SELECT * FROM orders

WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

A temporary table is a table that exists only for the duration of a session or a transaction and is typically used to store intermediate results for a complex query. To create a temporary table in SQL, you can use the CREATE TEMPORARY TABLE statement, like this:

CREATE TEMPORARY TABLE temp_table AS

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

15. How do you optimize and troubleshoot SQL queries?

To optimize and troubleshoot SQL queries, you can use various techniques such as:

• Indexing columns that are frequently searched or sorted on
• Profiling the query execution plan to identify slow or inefficient parts of the query
• Using EXPLAIN or EXPLAIN ANALYZE to understand the query execution plan
• Using tools such as pgAdmin, MySQL Workbench, or SQL Server Management Studio to monitor performance metrics such as CPU and memory usage
• Running the query with different parameters and data to identify any issues with specific values
• Simplifying the query by breaking it down into smaller queries

16. How do you use indexes and views in SQL?

Indexes in SQL are used to improve the performance of queries by allowing the database management system (DBMS) to quickly locate and retrieve the requested data. An index is a separate data structure that is associated with a table and contains a copy of the data from one or more columns of the table, along with a pointer to the location of the corresponding rows in the table.

For example, to create an index on the "customer_id" column of the "orders" table, you would use the following SQL statement:

CREATE INDEX idx_customer_id ON orders (customer_id);

Views in SQL are virtual tables that are based on the result of a SELECT statement. They do not store data themselves, but rather provide a way to access data from one or more tables in a specific way, such as by filtering, joining, or aggregating the data.

For example, to create a view that shows all orders with a total price greater than $100, you would use the following SQL statement:

CREATE VIEW high_value_orders AS SELECT * FROM orders WHERE total_price > 100;

In SQL, NULL values represent missing or unknown data. When working with NULL values in SQL, it's important to understand the difference between NULL and an empty string or a zero value.

17. How do you handle NULL values in SQL?

There are several ways to handle NULL values in SQL:

1. Using the IS NULL or IS NOT NULL operators in a WHERE clause to filter for or exclude NULL values.

SELECT * FROM customers WHERE last_name IS NOT NULL;

2. Using the COALESCE() or NULLIF() functions to replace NULL values with a specific value or another expression.

SELECT COALESCE(last_name, 'N/A') AS last_name FROM customers;

3. Using the NULL value in a comparison operator to include NULL values in the result set.

SELECT * FROM orders WHERE total_price > 100 OR total_price IS NULL;

It's important to note that when using any comparison operator other than IS NULL or IS NOT NULL, with a NULL value, it will return false, so you need to use the IS NULL or IS NOT NULL operator to handle NULL values in the comparison.

18. How do you implement security in SQL?

Implementing security in SQL involves a combination of several different techniques, including:

1. Access control: This involves limiting access to the database based on user credentials and permissions. Users are assigned different roles and are only granted access to the data they need to perform their job.
2. Encryption: This involves encrypting sensitive data, such as credit card numbers, to protect it from unauthorized access.
3. Auditing: This involves keeping track of all actions performed on the database, such as who accessed it, when, and what data was accessed. This allows for the detection of any unauthorized access attempts.
4. Input validation: This involves validating user input to prevent SQL injection attacks, which are a common type of security vulnerability.
5. Firewall: This involves setting up a firewall to block unauthorized access to the database from the network

SQL Queries Interview Questions and Answers for Experienced Professionals

The interviewers might present a SQL query and ask questions on the same. They can also ask you to write SQL queries. Here are some SQL query interview questions for experienced professionals.

1. Write an SQL query to display the current date.

To display the current date in SQL, you can use the GETDATE() function:

SELECT GETDATE();

2. Write an SQL query to verify if the data passed to the query is of the given format: "DD/MM/YY."

To verify if the data passed to a query is in the format "DD/MM/YY," you can use the ISDATE() function along with the appropriate format string:

SELECT ISDATE('DD/MM/YY')

This will return 1 if the passed data is in the format "DD/MM/YY" and 0 otherwise.

Please keep in mind that the above query only check the passed string format and not the actual data, to check if the data passed to the query is in the format "DD/MM/YY" and is a valid date you can use the following query

SELECT CASE

           WHEN ISDATE(your_date_column) = 1 AND your_date_column like '__/__/__' THEN 'valid date'

           ELSE 'invalid date'

       END as 'date_status'

3. Write an SQL query to print the candidate's name, whose birth date is 08/09/1970 to 30/11/1975.

SELECT name FROM candidates

WHERE birth_date BETWEEN '1970-09-08' AND '1975-11-30';

4. Write a query to print an employee's name whose name starts with 'S.'

SELECT name FROM employees

WHERE name LIKE 'S%';

5. Write a query to find an employee whose salary is less than or equal to 10000.

SELECT * FROM employees WHERE salary <= 10000;

6. Write a query to find the month from a given date.

SELECT MONTH(date_column) FROM table_name;

7. Write a query to join three tables containing two NULL values.

To join three tables containing two NULL values, you can use the LEFT JOIN clause. The LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL values will be returned for the right table's columns:

SELECT *

FROM table1

LEFT JOIN table2 ON table1.column_name = table2.column_name

LEFT JOIN table3 ON table2.column_name = table3.column_name

8. Write a query to fetch the first three characters of the employee name.

To fetch the first three characters of the employee name from a table called "employee" and a column called "name", you can use the SUBSTRING() function:

SELECT SUBSTRING(name, 1, 3) as 'name_first_3' FROM employee;

This query will return a new column called "name_first_3" containing the first three characters of the "name" column for each employee.

9. Consider the following data table for answering the question given below:

Answer the following SQL query interview questions with reference to the above table:

• Write a SQL query to fetch the EmpFname in the upper case. Use the ALIAS name as EmpName.

SELECT UPPER(EmpFname) AS EmpName FROM Employee;

• Write a SQL query to retrieve the number of employees who are a part of the HR department.

SELECT COUNT(*) FROM Employee WHERE Department='HR';

• Write a query to fetch the first four characters of EmpLname whose name starts with 'W.'

SELECT SUBSTR(EmpLname,1,4) FROM Employee WHERE EmpLname LIKE 'W%';

• Write a SQL query to retrieve the place name, i.e., the string before brackets, from the Address column.

SELECT SUBSTRING_INDEX(Address,'(',1) as Place FROM Employee;

• Write a SQL query to fetch the names of employees that begin with 'R.'

SELECT EmpFname FROM Employee WHERE EmpFname LIKE 'R%';

Recommended Reading: SQL query interview questions based on a sample data table

Oracle PL/SQL Interview Questions for Experienced Professionals

Oracle created PL/SQL to overcome the disadvantages of SQL. It allows easier building and handling of critical applications. The following Oracle PL/ SQL interview questions for experienced professionals will help you brush up on the concepts:

1. What makes PL/SQL a better option than SQL?

PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL that is used for developing stored procedures, functions, and triggers in an Oracle database. It provides several features that make it a better option than plain SQL for certain types of tasks:

• Block structure: PL/SQL allows you to group multiple SQL statements together into logical blocks, making it easier to organize and manage your code.
• Control structures: PL/SQL provides various control structures such as IF-ELSE, LOOP and WHILE etc, which allows you to perform conditional logic and iteration in your code.
• Error handling: PL/SQL allows you to handle and raise exceptions, which makes it easier to handle errors and unexpected scenarios.
• Stored procedures and functions: PL/SQL allows you to create and use stored procedures and functions, which can be called from other applications or PL/SQL blocks. This can improve performance by reducing the amount of data that needs to be transferred between the application and the database.

2. How would you debug your code in PL/SQL?

To debug your code in PL/SQL, you can use the following methods:

• DBMS_OUTPUT.PUT_LINE : You can use this package to print your variable values, messages, etc.
• RAISE_APPLICATION_ERROR: This function allows you to raise an error with a custom error message.
• Using Oracle SQL Developer : It is an Integrated development environment (IDE) for working with SQL and PL/SQL. It provides debugging facilities like breakpoints, step-by-step execution and watch variables.

It's important to note that debugging PL/SQL can be more involved than debugging other programming languages, and it may require knowledge of the specific database and tools you are using.

3. How is the mutating table different from the constraining table?

A mutating table is a table that is currently being modified by a DML (Data Manipulation Language) statement, such as an UPDATE or DELETE statement. This can cause issues when trying to access the same table within a trigger, as the changes made by the DML statement may not be visible yet or may not be consistent. To avoid this, a mutating table trigger is not allowed to read or modify the table that it is associated with.

A constraining table is a table that is being referenced by a foreign key constraint. This means that the table has a column or set of columns that reference the primary key of another table. This constraint ensures that the data in the constraining table is consistent with the data in the referenced table.

4. Where are the outcomes of the execution of the DML statement saved?

The outcome of the execution of a DML statement is saved in the database. DML statements are used to modify the data in the database, so any changes made will be persisted in the relevant tables. These changes can be committed or rolled back, depending on the transaction management in use, with committed changes being permanent and visible to other sessions, and rolled-back changes being discarded and not visible to other sessions.

5. What virtual tables are available during the execution of the database trigger?

• Virtual tables available during the execution of a database trigger include:
• INSERTED: Contains the new data for any rows affected by an INSERT or UPDATE statement.
• DELETED: Contains the old data for any rows affected by an UPDATE or DELETE statement.

5. What is the significance of the SYS.ALL_DEPENDENCIES?

SYS.ALL_DEPENDENCIES is a view in the Oracle database that shows all dependencies between objects in the database. This view can be used to determine the dependencies between objects such as tables, views, procedures, and triggers. The significance of this view is that it can be used to track dependencies between objects in a database and ensure that changes to one object do not break any other objects that depend on it. It can also be used to help identify and resolve issues related to object invalidation and to help plan and manage upgrades and migrations of the database.

7. What is the difference between %TYPE and %ROWTYPE data types in PL/SQL?

In PL/SQL, %TYPE and %ROWTYPE are used to declare variables.

8. Write a PL/SQL program to calculate the sum of the digits of a three-digit number?

DECLARE

   num NUMBER := 123; -- Three-digit number

   sum NUMBER := 0; -- Variable to store the sum

BEGIN

   sum := sum + num MOD 10; -- Add the units digit

   num := num DIV 10; -- Remove the units digit

   sum := sum + num MOD 10; -- Add the tens digit

   num := num DIV 10; -- Remove the tens digit

   sum := sum + num MOD 10; -- Add the hundreds digit

   DBMS_OUTPUT.PUT_LINE('Sum of digits: ' || sum); -- Print the sum

END;

9. What are the different ways of commenting in a PL/SQL code?

• There are two ways to comment in PL/SQL code:
• Single-line comments, which begin with two dashes (--) and continue until the end of the line.
• Multi-line comments, which are enclosed between /* and */

9. What is exception handling?

Exception handling is a mechanism that allows a program to handle errors and unexpected conditions in a controlled and predictable manner. It is a process of dealing with runtime errors that occur during the execution of a program. PL/SQL provides a rich set of predefined exceptions and also allows you to define your own exceptions. Exceptions can be handled using the EXCEPTION block, where you can catch and handle the exceptions that occur in the EXECUTABLE block. Exception handling allows you to write robust and fault-tolerant code, making it easier to identify and correct errors and improve the overall stability of your PL/SQL programs.

11. What do you understand about INSTEAD OF triggers?

INSTEAD OF triggers are a type of database trigger that are executed in place of the triggering DML statement, rather than in addition to it. They are typically used to perform actions that cannot be easily accomplished using standard DML statements, or to override the default behavior of a view that cannot be modified directly. They are u

‍