Are you going for a job where you need to know SQL, such as a Database Developer or Database Administrator? Brush up on your interview questions with this extensive list of SQL interview questions.
This collection of interview questions on SQL has been collated from my experience with SQL and from various websites.
It contains a list of questions of different types:
- Definitions: These questions ask for a definition of a term or concept.
- Differences: Questions that ask for the difference between two similar concepts.
- Query examples: A sample data set has been provided, and you either need to write a query or explain what a query does.
So, read through the list and try to answer the questions yourself before reading the answer.
Or, read the question then the answer, and try to understand the answer and remember it.
It won’t cover every possible SQL interview question and answer, but it should help.
Also, this list of questions focuses on the SQL standard and on Oracle SQL or Oracle PL/SQL. There are some differences if you’re looking for MySQL or SQL Server questions, but I’d say that 80% of these questions are applicable to all database management systems.
Let’s look at the questions.
If you’re interested in becoming a database developer, have a read of my article – How to Become a Database Developer: The Definitive Guide.
SQL Interview Questions and Answers
1. What is the difference between SQL, Oracle, MySQL, and SQL Server?
SQL is the name of the language used to query databases and follows a standard. Oracle, MySQL and SQL Server are different implementations or versions of a database management system, which implement the SQL standard and build on it in different ways.
Oracle database is targeted to large companies, SQL Server is owned by Microsoft, and MySQL is owned by Oracle but targeted towards smaller companies and systems.
2. What is the difference between SQL and PL/SQL?
SQL is the language used to query databases. You run a query, such as SELECT or INSERT, and get a result.
PL/SQL stands for Procedural Language/Structured Query Language. It’s Oracle’s procedural language and is built on top of SQL. It allows for more programming logic to be used along with SQL.
3. What are the different DDL commands in SQL? Give a description of their purpose.
- CREATE: creates objects in the database
- ALTER: makes changes to objects in the database
- DROP: removes objects from the database
- TRUNCATE: deletes all data from a table
- COMMENT: adds comments to the data dictionary
- RENAME: renames an object in the database
4. What are the different DML commands in SQL? Give a description of their purpose.
- SELECT: retrieve or view data from the database
- INSERT: add new records into a table
- UPDATE: change existing records in a table
- DELETE: removes data from a table
- MERGE: performs an UPSERT operation, also known as insert or update.
- CALL: runs a PL/SQL procedure or Java program
- EXPLAIN PLAN: explains the way the data is loaded
- LOCK TABLE: helps control concurrency
5. What is DCL? Provide an explanation of some of the commands.
DCL stands for Data Control Language. The commands that come under DCL are:
- GRANT: give access privileges to a user
- REVOKE: withdraw access privileges from a user
6. What is TCL? Provide an explanation of some of the commands.
TCL stands for Transaction Control Language and it contains statements to manage changes made by DML statements. It includes:
- COMMIT: saves the data to the database
- ROLLBACK: undo the modifications made since the last COMMIT
- SAVEPOINT: create a point in a transaction that you can ROLLBACK to
- SET TRANSACTION: change the transaction options, such as isolation level
- SET ROLE: sets the current active role
7. What is a “cross join”?
A cross join is a type of join where the results displayed contain the records from both tables in all possible combinations. There is no field used to perform the join.
For example, if table A has 10 records and table B has 8 records, then the cross join will result in 80 (or 10 x 8) records.
The result can also be called a “cartesian product”.
8. What is an aggregate function?
An aggregate function is an SQL function that reads data from multiple rows and displays a single value. Some examples of aggregate functions are COUNT, SUM, MIN, MAX, and AVG. They are often used with a GROUP BY clause but can be used by themselves.
9. What is a subquery?
A subquery is a query within another query. This subquery can be in many places, such as in the FROM clause, the SELECT clause, or a WHERE clause.
It’s often used if you need to use the result of one query as an input into another query.
10. What is the purpose of the BETWEEN keyword?
The BETWEEN keyword allows you to check that a value falls in between two other values in the WHERE clause.
It’s the same as checking if a value is greater than or equal to one value, and less than or equal to another value.
11. What is the purpose of the IN keyword?
The IN keyword allows you to check if a value matches one of a range of values. It’s often used with subqueries that return more than one row.
12. What does the MERGE statement do?
The MERGE statement allows you to check a set of data for a condition, and UPDATE a record if it exists or INSERT a record if it doesn’t exist.
13. What is a view? When would you use one?
A view is a database object that allows you to run a saved query to view a set of data. You create a view by specifying a SELECT query to be used as the view, and then the view can be queried just like a table.
There are several reasons to use a view, such as to improve to security, create a layer of abstraction between the underlying tables and applications, and to simplify queries.
14. What is an execution plan? How can you view the execution plan?
An execution plan is a graphic or text visualisation of how the database’s optimiser will run a query. They are useful for helping a developer understand and analyse the performance of their query.
To find the execution plan of a query, add the words “EXPLAIN PLAN FOR” before your query. The query won’t run, but the execution plan for the query will be displayed.
15. What is a primary key?
A primary key is a column or set of columns that uniquely identifies a row in a table. It’s created on a table and ensures that the values in that column or columns must be unique and not NULL.
This is often done using some kind of numeric ID field but doesn’t have to be.
16. What is a foreign key?
A foreign key is a field in a table that refers to a primary key in another table. It is used to link the record in the first table to the record in the second table.
17. What is a synonym?
A synonym is a database object that allows you to create a kind of “link” or “alias” to another database object. This is often done to hide the name of the actual object for security reasons, or to improve maintenance of the code in the future.
18. What is cardinality?
Cardinality refers to the uniqueness of values in a column. High cardinality means that there is a large percentage of unique values. Low cardinality means there is a low percentage of unique values.
19. What is a composite key?
A composite key is a primary key that is made up of two or more fields. Often, primary keys are single fields, but in some cases, a row is identified by multiple fields. This is what a composite key is.
20. What is a surrogate key?
A surrogate key is a field in a table that has been created solely for the purpose of being the primary key. It has no other purpose than an internal storage and reference number.
For example, a customer may have an account number that is unique to them, but a customer_id field might be created on the table and used as the primary key, in case business rules change and mean that the account number is no longer unique.
21. What is a self join and why would you use one?
A self join is a type of join where a table is joined to itself.
You would use a self join when a table has a field that refers to another record in the same table. It’s often used in hierarchical structures, such as employee tables having a manager_id column where the manager_id refers to another employee record.
22. What is a correlated subquery?
A correlated subquery is a subquery that refers to a field in the outer query.
Subqueries can be standalone queries (non-correlated), or they can use fields in the outer query. These fields are often used in join conditions or in WHERE clauses.
23. What are the different JOIN types and what do they do?
The different join types in Oracle SQL are:
- Inner join: Returns records that exist in both tables.
- Left join/left outer join: Returns records that exist in the first table and shows NULL for those values that don’t exist in the second table.
- Right join/right outer join: Returns records that exist in the second table and shows NULL for those values that don’t exist in the first table.
- Full join/full outer join: Returns records that exist in both the first and second table, and shows NULL for those values that don’t exist in the corresponding table.
- Cross join: Returns all combinations of all records in both tables.
- Natural join: an inner join with two tables on columns that have the same names.
- Self join: A join from one table to another record in the same table.
For more information and examples, check out this post on SQL Joins interview questions.
24. Can you sort a column using a column alias?
Yes, you can sort by column aliases in an ORDER BY clause.
25. Is NULL the same as a zero or blank space? If not, what is the difference?
No, they are different. NULL represents and unknown value. Zero represents the number zero, and a blank space represents a character string with no data.
NULL is compared differently to a zero and a blank space, and must use comparisons like IS NULL or IS NOT NULL.
26. If a table contains duplicate rows, will a query display duplicate values by default? How can you eliminate duplicate rows from a query result?
Yes, they will be displayed by default. To eliminate duplicate records, you use the DISTINCT keyword after the word SELECT.
27. What is the default sort order using ORDER BY? How can it be changed?
The default sort order is ascending. This can be changed by specifying the word DESC after any column name in the ORDER BY clause. The word ASC can be used instead to specify ascending order.
28. What are the case manipulation functions in Oracle SQL?
29. Which function or functions returns the remainder of a division operation?
This is one of the SQL interview questions which is Oracle specific, as the REMAINDER function does not exist in other database management systems.
30. What does the NVL function do, and how is it different from NVL2?
The NVL function checks if a value is NULL, and returns the value if it is not NULL. If the value is NULL, it returns a different value which you can specify.
NVL2 is slightly different in that you specify both the value to return if the checked value is NULL and if it is not NULL.
NVL takes two parameters and NVL2 takes three.
31. How can you perform conditional logic in an SQL statement?
The CASE statement is more flexible and arguably easier to read than the DECODE function.
32. How can you search for a value in a column when you don’t have the exact match to search for?
If you don’t know the exact match, you can use wildcards along with LIKE. The wildcards are the % symbol for any number of characters, and the _ symbol for a single character.
33. Can you nest aggregate functions?
Yes, you can have nested aggregate functions up to two levels deep. For example, you can use MAX(COUNT(*)).
34. Does COUNT return the number of columns in a table?
No, it returns the number of records in a table.
35. What’s the difference between ANY and ALL?
The ANY keyword checks that a value meets at least one of the conditions in the following set of values. The ALL keyword checks that a value meets all of the conditions in the following set of values.
36. Can you insert a NULL value into a column with the INSERT statement?
Yes, you can. You can do this by:
- Leaving the column out of the list of columns in the INSERT statement; or
- Specifying the value of NULL for the column in the VALUES clause
37. Can you INSERT data from one table into another table? If so, how?
Yes, you can do this using an INSERT INTO SELECT query. You start by writing an INSERT INTO statement, along with the columns you want, and then instead of the VALUES clause, you write a SELECT query.
This SELECT query can select data from the same table, or another table, or a combination of tables using JOINs, just like a regular SELECT query.
38. What happens if you don’t have a WHERE clause in an UPDATE statement?
All records in the table will be updated. You need to be sure that’s what you want to do.
39. What happens if you don’t have a WHERE clause in a DELETE statement?
All records will be deleted from the table. It will still run, there will be no error. You need to be sure that’s what you want to do.
40. What’s the difference between DROP and DELETE?
DROP is used to remove database objects from the database, such as tables or views. DELETE is used to remove data from a table.
Also, DROP is a DDL statement and DELETE is a DML statement, which means DELETE can be rolled back but DROP cannot.
41. What’s the difference between TRUNCATE and DELETE?
There are several differences.
- TRUNCATE deletes all records from a table and you cannot specify a WHERE clause, but DELETE allows you to specify a WHERE clause if you want.
- TRUNCATE does not allow for rollbacks, and DELETE does.
- TRUNCATE is often faster because it does not generate an undo log, but DELETE does.
42. What’s the difference between VARCHAR2 and CHAR?
VARCHAR2 does not pad spaces at the end of a character string, but CHAR does. CHAR values are always the maximum length, but VARCHAR2 values are variable length.
43. What does UNION do? What’s the difference between UNION and UNION ALL?
Union allows you to combine two sets of results into the one result.
It’s different to UNION ALL because UNION removes duplicate values and UNION ALL does not.
44. List the ACID properties and explain what they are.
ACID stands for Atomicity, Consistency, Isolation, and Durability. They are a set of properties that ensure that database transactions are processed reliably.
- Atomicity means that each transaction be atomic, which means “all or nothing”. Either the entire transaction gets saved, or none of it gets saved.
- Consistency means that any transaction will bring the database from one consistent state to another. Data must be valid according to all business rules.
- Isolation means that transactions that are executed at the same time will give the same results as transactions executed one after the other. The effects of one transaction may not be visible to another transaction.
- Durability means that once a transaction has been committed, it remains committed. This is even if there is a disaster, such as power loss or other errors.
This SQL interview question should be relevant to all database management systems. It’s not Oracle specific.
45. What is the difference between RANK and DENSE_RANK?
RANK will assign non-consecutive values, which means there will be gaps in numbers.
DENSE_RANK will assign consecutive values, which means there will be no gaps.
46. What is the difference between the WHERE and HAVING clauses?
The WHERE clause is run to remove data before grouping. The HAVING clause is run on data after it has been grouped.
This also means the WHERE clause cannot operate on aggregate functions calculated as part of the group.
More information: The Difference Between the WHERE and HAVING Clause
47. What is a unique constraint? How is it different from a primary key?
A unique constraint is a constraint on a table that says that a column or set of columns needs to have unique values.
It’s different to a primary key in that a table can only have one primary key, but a table can have zero, one, or many unique constraints.
Unique constraints can also allow NULL values, but primary keys cannot.
48. What’s the difference between a view and a materialized view?
A view is simply an SQL query that is stored on the database, without the results. Every time a view is queried, this definition of the view’s query is run. If the underlying tables have been updated, the view will load these results.
A materialized view is a query where the results have been stored in a permanent state, like a table. If the underlying tables are updated, then by default, the materialized views are not updated.
49. What’s the difference between UNION and JOIN?
A join allows us to lookup data from one table in another table based on common fields (for example employees and departments). It requires us to have a field that is common in both tables.
A union allows us to combine the results of two queries into a single result. No join between the results is needed. Only the number and type of columns needs to be the same.
50. What’s the difference between UNION, MINUS, and INTERSECT?
They are all set operators.
But, UNION will combine the results from query1 with query2 and remove duplicate records.
MINUS will display the results of query1 and remove those that match any records from query2.
INTERSECT will display the records that appear in both query1 and query2.
51. What’s the difference between ROWNUM and ROW_NUMBER?
ROWNUM is a pseudocolumn and has no parameters, where as ROW_NUMBER is an analytical function that takes parameters.
ROWNUM is calculated on all results but before ORDER BY. ROW_NUMBER is calculated as part of the column calculation
ROWNUM is unique. ROW_NUMBER can contain duplicates.
More information: What’s The Difference Between Oracle ROWNUM vs Oracle ROW_NUMBER?
52. How can you create an auto increment column in Oracle, in version 11 or earlier? What about in Oracle 12c?
In Oracle 11g, you can create an auto increment column by using a combination of a sequence and a BEFORE INSERT trigger.
The sequence is used to generate new values, and the BEFORE INSERT trigger will read these new values and put them into the required column whenever you INSERT a new record.
In Oracle 12c, you can define a column as an Identity column by putting the words GENERATED AS IDENTITY after the column in the CREATE TABLE statement. This means new values are generated automatically.
53. How can you create an empty table from an existing table?
You can use the CREATE TABLE AS SELECT command.
The SELECT statement will contain all of the columns that you want to have in your new table. To ensure it is empty, add a WHERE clause that evaluates to FALSE, such as WHERE 1=0.
54. What’s the difference between % and _ for pattern matching (e.g. in the LIKE operator)?
The difference is the % sign will match one or more characters, but the _ sign will match only one character.
55. What’s wrong with this query? SELECT department_id, count(*) FROM department;
There is no GROUP BY clause and it will display an error. Because we have used the COUNT function, which is an aggregate function, along with a database field, we need to add a GROUP BY clause. It should GROUP BY the department_id column.
56. What’s wrong with this query? SELECT department_id, count(*) FROM department WHERE count(*) > 5 GROUP BY department_id;
The WHERE clause cannot include any checks on the aggregate column – even if a GROUP BY has been performed.
This is because the WHERE happens before the grouping, so there is no way for the WHERE clause to know what the value of the COUNT function is.
To resolve this, use the HAVING clause to check for COUNT(*) > 5.
57. Given these two queries and result sets, what will the result of this query be? Explain your answer.
SELECT * FROM employee
|EMPLOYEE_ ID||FIRST_ NAME||LAST_ NAME||SALARY||DEPARTMENT_ ID||MANAGER_ ID||HIRE_ DATE|
SELECT * FROM department;
What will the result of this query be?
SELECT * FROM department WHERE department_id NOT IN ( SELECT department_id FROM employee );
This will return an empty result set. This is because of how the NOT IN command treats NULL values.
If the set of data inside the NOT IN subquery contains any values that have a NULL value, then the outer query returns no rows.
To avoid this issue, add a check for NULL to the inner query:
SELECT * FROM department WHERE department_id NOT IN ( SELECT department_id FROM employee WHERE department_id IS NOT NULL );
58. Given this ERD, write a query that shows the following information.
The customer ID, customer first and last name, the order ID of any orders the customer has placed (if any) and the date of the order. The data should be ordered by last name then first name, both in ascending order.
SELECT c.customer_id, c.first_name, c.last_name, co.order_id, co.order_date FROM customer c LEFT JOIN customer_order co ON c.customer_id = co.customer_id ORDER BY c.last_name, c.first_name;
This question checks your ability to translate a normal English statement into a SELECT query.
You should have picked up on the need for a LEFT JOIN, the need for table aliases for ambiguous columns, and the ORDER BY.
Table aliases are good to use in any case, so experienced developers will use them for every query.
You might have several different variations of this interview question for SQL interviews. Knowing your query structure and focusing on the requirement for the query are important here.
59. Write a query to display the 5th highest employee salary in the employee table
SELECT * FROM ( SELECT employee_id, first_name, last_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC NULLS LAST) rank_val FROM employee ) WHERE rank_val = 5;
This could also be done using the ROW_NUMBER function. It’s one of those interview questions in SQL that can have multiple answers, but as long as you provide an answer to it, you should be OK.
60. What is wrong with this query: SELECT SYSDATE;
Even if you don’t need a table to get your data, you need to add a table to your SELECT query for it to run.
In this case, you can use the DUAL table that Oracle has created.
SELECT SYSDATE FROM dual;
So, that concludes the list of SQL interview questions and answers. Do you have any questions about these, or any other interview questions you’ve been asked in your job interviews? Add them in the comments below.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!