SQL is a query language used for accessing and managing databases. If you are applying for a job that involves working with a relational database management system (RDBMS), you may need to have practical knowledge of running SQL queries. Depending on your experience level, interviewers may ask questions to test how well you can use SQL to retrieve and manipulate data held in a database management system like MySQL. In this article, we list some of the common SQL query interview questions for freshers and experienced professionals and also provide answering tips and sample answers for several of them.
Basic SQL query interview questions for freshers
Following are some of the beginner-level SQL query interview questions for fresh graduates:
- What is a primary key?
- What is a foreign key? Can we have more than one foreign key?
- What are tables and fields?
- What is an SQL query?
- What is a subquery? What is the difference between a correlated and non-correlated query?
- List the different types of operators used in SQL along with their functions.
- What is a stored procedure?
- What is a constraint?
- Tell us about local and global variables.
- What do you understand by data integrity?
- Explain the different types of collation sensitivity.
- What is an SQL clause?
- What do you understand by ACID properties in SQL?
- What are SQL triggers?
- How do SQL privileges work?
- What is SQL injection?
- What is SQL sandbox?
- How would you select all the records from a table?
- How do you add a column?
- How does a BETWEEN operator differ from an IN operator?
- Differentiate between CHAR and VARCHAR2 data types.
- What is a cursor? How do you use it?
- How do you insert SQL comments?
Advanced-level SQL query interview questions for experienced professionals
Here are some of the in-depth SQL query interview questions for experienced candidates:
- What is data normalisation? Describe the different forms of normalisation.
- What is a view in SQL?
- Describe different types of indexes in SQL.
- Can we sort a column using a column alias?
- How do you display the current date in SQL?
- Describe the different types of SQL JOINS.
- Differentiate between DELETE and TRUNCATE statements.
- What are case manipulation functions?
- What is an ALIAS command in SQL?
- Differentiate between aggregate and scalar functions.
- Can we make an existing table empty? If yes, how?
- How would you fetch the common records between two tables?
- How would you fetch unique records from a table?
- What is a self-join?
- What common clauses would you use with a SELECT query?
- What is a user-defined function? Describe the different types of user-defined functions.
- Differentiate between OLTP and OLAP.
- What do you understand by recursive stored procedure?
- Explain the concept of pattern matching.
- What is ROWID?
- What is a composite primary key?
Read more: 9 Common Mistakes to Avoid in an Interview
Common SQL query interview questions and answers
Following are some of the common SQL query interview questions, along with answering tips and sample answers:
1. What is SQL?
Interviewers ask this question to check your basic understanding of SQL. You can explain how SQL helps in accessing data and making it more meaningful.
Example: "SQL or Structured Query Language is a computer language used to interact with databases. It uses queries to retrieve data from a database or perform actions like creating, deleting or updating a table and inserting or deleting rows and columns. It also enables us to perform complex operations, such as combining two or more tables, removing duplicate values from a database and print certain data based on the defined criteria.”
2. Explain the different subsets of SQL.
Based on the type of SQL queries, we can organise them into different categories. We commonly refer to these categories as subsets. Explain all the four subsets of SQL, namely data definition language (DDL), data manipulation language (DML), data control language (DCL) and transaction control language (TCL).
Example: “There are four subsets SQL – DDL, DML, DCL and TCL. The DDL or the data definition language defines and modifies the structure of the data. It includes commands like CREATE, TRUNCATE, ALTER and DROP. The DML or the data manipulation language is the subset of commands used to manipulate the existing data, such as SELECT, DELETE, INSERT and UPDATE. The DCL or the data control language manages the access rights and permission for the database. This subset includes commands like GRANT and REVOKE.
The fourth subset TCL refers to the transaction control language. It helps perform tasks in a single execution. Examples of TCL commands include COMMIT, ROLLBACK, SAVEPOINT and SET TRANSACTION.”
3. What is the difference between a primary key and a unique key?
Both these keys carry a unique value, but there are some essential differences between them. Be sure to include the major differences like null value, multiplicity and cluster indexing in your answer.
Example: “A unique key refers to a column in which all the values are unique or different from each other. A table can have multiple columns with unique values. Thus, we can have multiple unique keys in a table. Moreover, a unique key can also have one null value in a column.
A primary key uniquely identifies a table by creating a cluster index. It also carries a unique value meaning that we cannot have a duplicate value in a primary key column. However, unlike a unique key, it does not accept a null value. A table can have only one primary key.”
4. Describe the different clauses we use in SQL.
We use SQL clauses to request information from a database. You can list some of the general SQL clauses like WHERE clause, GROUP BY clause, HAVING clause, ORDER BY clause and USING clause, and describe the function each of them performs.
Example: “Some of the general SQL clauses include WHERE clause, GROUP BY clause, HAVING clause, ORDER BY clause and USING clause. We use a WHERE clause to filter the records that meet the defined criteria. Similarly, we use a GROUP BY clause to group the rows with the same values. A HAVING clause is similar to a WHERE clause except in that we use it with a GROUP BY clause to group the results of a query.
An ORDER BY clause sorts the result of a query in an ascending or descending order as specified in the statement. A USING clause is helpful while joining the tables. It matches the columns based on the given criteria for joining two or more tables.”
5. Describe the major types of SQL constraints.
SQL constraints set the rules that all records must meet. We define these constraints while creating or altering a database. Violation of any of the constraints aborts the action. List all the five major constraints and describe the type of records they indicate.
Example: “There are five major types of SQL constraints – NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY AND CHECK. We use NOT NULL when we do not want a value in a column to be blank. The UNIQUE constraint indicates that each value in a column must be unique.
The PRIMARY KEY identifies a table on the basis of a unique identity. There can be only one PRIMARY KEY in a table and it cannot have a NULL value. The FOREIGN KEY compares two tables on the basis of the PRIMARY KEY. The CHECK constraint ensures that the records in the columns meet the defined criteria.”
6. What are the different AGGREGATE functions in SQL and what do they do?
An AGGREGATE function checks the values from multiple columns and returns a single value based on the requested operation. You can list and describe the seven major types of AGGREGATE functions like SUM(), COUNT() and AVG().
Example: “There are seven aggregate functions in SQL – SUM(), COUNT(), AVG(), MAX(), MIN(), FIRST() and LAST(). The SUM() function adds the values of the requested columns and returns the sum total as a result. The COUNT() function returns the number of rows that meet the defined criteria. The AVG() function computes the average value from specified columns. The MAX() function identifies the largest value in the selected column, while the MIN() function identifies the smallest value. Similarly, the FIRST() function returns the value of the first record in a given column, while the LAST() function returns the value of the last record.”
7. What is an SQL transaction? What are the different types of transaction controls?
A transaction is a sequence of tasks performed on a database. Describe the functions of each of the four types of SQL transactions – COMMIT, ROLLBACK, SET TRANSACTION and SAVEPOINT.
Example: “An SQL transaction refers to a group of queries that perform a sequence of tasks in a logical manner. Operations like updating, creating and deleting records are the result of transactions. There are four different types of transaction controls in SQL – COMMIT, ROLLBACK, SET TRANSACTION and SAVEPOINT.
A COMMIT transaction saves the changes in the database. A ROLLBACK transaction reverts back the database to the original values or to a savepoint defined in the transaction. A SET transaction assigns the name to a transaction. A SAVEPOINT transaction defines a point to which the database reverts back in the case of a rollback.”