31 SQL Interview Questions and Answers for 2022

Interview Questions

Advertise your job effectively

Advertise your job requirements effectively to get the right candidates for interviews. Include the following:

Company descriptions

Introduce your company, however, be sure to make the introduction an exciting one. Describe the career development opportunities on offer. Talk about the organizational culture, work environment, and skill development programs in your organization. Describe your compensation and benefits policies.

Job descriptions for software developers with SQL skills

Describe your project and the scope of the work of SQL developers. This could include designing relational databases and building them using relevant tools. SQL developers might need work on the scalability, stability, and reliability of these databases. They might need to optimize in-application SQL statements. The job might involve the performance-tuning of SQL databases. Talk about how the contribution of the developer will help your organization succeed.

Roles and responsibilities of software developers with SQL skills

You expect an SQL developer to fulfill the following responsibilities:

  • Understanding the project requirements;
  • Analyzing the database requirements for the project;
  • Designing or helping in the design of the relational databases, database tables, columns, indices, views, schemas, etc.;
  • Providing inputs to DBAs (Database Administrators) and architects about the performance, reliability, security, scalability, and maintainability of the SQL database needed in the project;
  • Designing and developing SQL statements, and incorporating them in the programs;
  • Optimizing the performance of the SQL statements in the application programs;
  • Handling database management tasks like an upgrade, patching, backup, disaster recovery testing, etc.;
  • Helping the DBA and architect in performance tuning of the database;
  • Collaborating with the larger team;
  • Providing the status of the project work;
  • Communicating effectively with the relevant stakeholders.

Skills and competencies that you need in a software developer with SQL skills

You need an SQL developer with a bachelor’s degree in computer science, information technology, or related fields. Look for the following skills:

  • Excellent knowledge of SQL and how it varies among popular databases;
  • In-depth knowledge of popular RDBMSs like MySQL, PostgreSQL, Microsoft SQL Server, Oracle, etc.;
  • Experience in key programming languages like Java, JavaScript, Python, etc. per your project requirements;
  • Knowledge of the best practices in designing relational databases;
  • Experience in developing application programs with SQL statements;
  • Knowledge of designing scalable, performant, reliable, and maintainable SQL databases, database tables, etc.;
  • Experience in performance-tuning of in-application SQL statements;
  • Proficiency in SQL database-related troubleshooting;
  • Sufficient knowledge of popular software development, database maintenance, code versioning, testing, and DevOps tools.

Look for SQL developers with certifications in the SQL database that you need.

You need an SQL developer with the following competencies:

  • Passion for excellence:
  • Commitment to your project objectives;
  • Problem-solving skills;
  • Communication skills;
  • Teamwork;
  • The ability to see the larger picture.

SQL interview questions for junior developers

Use the following interview questions when interviewing junior application developers with SQL skills:

Question 1: Explain the differences between DBMS and RDBMS.

Answer: A DBMS (Database Management System) is a software product that helps you to store data systematically. You would want a DBMS so that you can perform various data manipulation operations like “Create”, “Read”, “Update”, and “Delete” (CRUD) with relative ease. A DBMS enables you to define, create, and manage a database for this purpose. It also allows you to provide controlled access to users to the data.

An RDBMS (Relational Database Management System) is a DBMS too. You can think of it as a subset of DBMS. E. F. Codd, a computer scientist working with IBM had developed the concept of RDBMS in 1070. An RDBMS helps you to store data in form of tables with rows and columns.

The differences between a DBMS and RDBMS are as follows:

DBMS RDBMS
A DBMS stores data as files. An RDBMS stores data as tables.
You can access data elements individually. An RDBMS allows you to access multiple data elements simultaneously.
Data elements in a database don’t have any relationships between them. RDBMS allows you to define a relationship between data elements.
A DBMS doesn’t allow normalization. An RDBMS allows normalization.
You store data in a navigational or hierarchical form in a database. An RDBMS has tables with multiple columns. Each row in a table has values for the corresponding columns.
A DBMS might have data redundancy. An RDBMS avoids data redundancy with the help of keys and indices.
Examples: XML, Microsoft Access Examples: MySQL, PostgreSQL, Microsoft SQL Server, Oracle
A database might offer very limited features, therefore, it might require hardware and software with limited sophistication. All RDBMSs must provide a basic set of key features, and many of them require significant computing. This requires software and hardware with a certain degree of sophistication.

Question 2: What is SQL?

Answer: SQL (Structured Query Language) is the language to communicate with an RDBMS. Donald D. Chamberlin and Raymond F. Boyce jointly developed SQL in the early 1970s. SQL was the first commercial language to use the RDBMS model.

You can use SQL to read and manipulate data in an RDBMS. The different types of SQL statements are as follows:

  • DDL (Data Definition Language): An Example is the “CREATE” statement to create databases, tables, views, indices, etc. The “ALTER” statement is another example, which modifies the structure of the database.
  • DQL (Data Query Language): You can use the statements in this category to execute SQL queries. The “SELECT” statement is an example.
  • DML (Data Manipulation Language): You can use the statements in this category to manipulate data in an RDBMS table. Examples are “INSERT”, “UPDATE”, and “DELETE” commands.
  • DCL (Data Control Language): You can use the statements in this category to control users’ access to a database. Examples are commands like “GRANT” and “REVOKE”.
  • TCL (Transaction Control Language): Statements of this category deal with the transactions within a database. “COMMIT” and “ROLLBACK” are a few examples.

Question 3: Are SQL, MySQL, and Microsoft SQL Server the same?

Answer: SQL, MySQL, and Microsoft SQL Server are different. SQL is the popular programming language to interact with, modify, and manage RDBMSs and the data they store. One can perform many operations against RDBMS using SQL statements, e.g., create databases, create tables, retrieve rows, insert rows, update rows, delete rows, etc.

MySQL is a popular open-source RDBMS. Oracle had developed it. MySQL uses SQL, and it can run on various operating systems like Linux, Unix, and Windows.

Microsoft SQL Server (MS SQL Server) is a popular RDBMS. Microsoft had developed it, and the company owns it. MS SQL Server uses SQL. Unlike MySQL, MS SQL Server isn’t free.

Question 4: What is a “Primary Key” in an SQL database?

Answer: A “Primary Key” in an SQL database is a key constraint in the RDBMS concepts that help to maintain data integrity. You can create a table and define a primary key in it, and you use the “PRIMARY KEY” keyword for this.

A primary key can be one column or a combination of columns. Such a column or the combination of columns must have unique values. Primary key values can’t be “null”.

An RDBMS table can’t have two primary keys. You can create an RDBMS table without a primary key, however, you can add one in the future by using the “ALTER TABLE” statement.

Question 5: What is a “Foreign Key” in a SQL database?

Answer: A “Foreign Key” in an SQL database is an important way to maintain relationships between data elements in an RDBMS. You can create a foreign key in a table by using the “FOREIGN KEY” keyword.

A foreign key in a table can be one column or a combination of columns. That column or the combination of columns must be a primary key in another table. We call the table with the primary key a “parent table”, and we call the table with the foreign key a “child table”.

The fact that a foreign key must be a primary key in another table ensures that you can’t just enter any value that you want in a foreign key. That value must be present in the primary key column of the parent table. A primary key doesn’t necessarily have to be a foreign key in another table.

Question 6: What is an index in an RDBMS?

Answer: An index in an RDBMS is a way to improve the performance of SQL queries. RDBMS indexes allow faster retrieval of rows from a table.

They work as the index in a book would do. An index page in a book helps you reduce your searches through the pages of the book. You look up the index page, and you find the page which describes the information that you need.

An index in an RDBMS has a unique value, therefore, you can’t duplicate it. The RDBMS analyzes your SQL commands, and it finds the relevant rows quickly with the help of the index.

Question 7: What are the different types of indexes in an RDBMS?

Answer: An RDBMS can have the following 3 types of indexes:

  • Unique index: If you define a unique index for a column, then an RDBMS prevents duplicates in that column. You can define a unique index for a combination of columns too. You automatically define a unique index when you create a primary key for a table.
  • Clustered index: A clustered index reorders the physical order of a table. It allows searches based on a key value. An RDBMS table can have one clustered index only.
  • Non-clustered index: A non-clustered index doesn’t modify the physical order of an RDBMS table. It maintains a logical order of data in that table. You can create up to 999 non-clustered indexes for one table.

Question 8: What is a stored procedure in the context of SQL?

Answer: A stored procedure is a set of SQL statements you write. You might need to execute these SQL statements many times. Instead of writing them every time, you can write a stored procedure once. You can then save it.

You can execute the stored procedure whenever you need it. For this, you can call it from your application. Stored procedures accept parameters.

Question 9: Explain how the WHERE clause in SQL works.

Answer: SQL offers a tool to filter records, and that’s the “WHERE” clause. You need to specify a condition in an SQL statement so that it only returns the rows that match the condition. The “WHERE” clause allows you to specify that condition. You use the “WHERE” keyword for this. The SQL “WHERE” clause is applicable to SELECT, UPDATE, and DELETE statements.

Question 10: What is “normalization” in an RDBMS?

Answer: The design of RDBMS emphasizes storing data efficiently that follows logic. Data model designers and DBAs working with RDBMS want to eliminate the repetition of data, and they don’t the redundancy of data. They also want to store data in a form that follows a clear pattern of relationship.

They follow the process called “normalization” for this. This process involves multiple steps, and it intends to reduce the repetition of data. Data model designers and DBAs also use normalization to eliminate anomalies concerning INSERT, UPDATE, and DELETE operations.

Question 11: What is a “NULL value” in an RDBMS table?

Answer: A “NULL” for a column in an RDBMS table indicates that the column can have its value missing. The term “NULL” doesn’t mean zero or spaces. It means that a field in that column has no value at all.

You need to explicitly specify whether a column is “NOT NULL” when using the SQL command named “CREATE TABLE”. If you don’t specify “NOT NULL” for a column, then you are allowing that column to have a “NULL”.

Note: You can’t have a “NULL” value in a column that’s the primary key of the table. You need to explicitly define a primary key as “NOT NULL”.

Question 12: Explain the concept of a subquery in SQL

Answer: Within the context of SQL statements, a subquery is a query within another SQL query. You need to embed a subquery within another query. We call the outer query the “main query”, and we call the inner query the “subquery”.

The following rules exist vis-à-vis SQL subqueries:

  • You need to place a subquery within an SQL clause. A few SQL clauses allow that, e.g., “WHERE”, “HAVING”, and “FROM”.
  • You can subqueries with SQL statements like “SELECT”, “UPDATE”, “INSERT”, and “DELETE”.
  • You need to use an expression operator with subquery, e.g., “=”, “>”, “<=”, etc.
  • The subquery executes first, and its output becomes a part of the query condition for the main query.
  • You can’t use the SQL “ORDER BY” statement within a subquery.
  • SQL allows the use of a “GROUP BY” statement within a subquery.
  • You need to use single-row operators for subqueries that involve one row. On other hand, you need to use multiple-row operators for subqueries that involve multiple rows.

Question 13: What is a unique key in SQL?

Answer: A unique key in an RDBMS can be one column, alternatively, it can be a combination of columns. You create a unique key to enforce uniqueness in rows. A unique key in an RDBMS table doesn’t allow duplicates. In that sense, it has a similarity with the primary key of an RDBMS table.

However, a unique key can contain a null value. You can have many unique keys in one table. That’s different in the case of the primary key since an RDBMS table can have only one primary key. You create a unique key by using the “UNIQUE” keyword while creating a table.

Question 14: What is an alias in SQL?

Answer: You can use an SQL alias to give a temporary name to a table or a column. This helps you to use a more readable name. An alias remains in effect only for the duration of a query, and you use the “AS” keyword to create it. The syntax is as follows:

Alias Column Syntax


SELECT column_name AS empname
FROM table_name;

Alias Table Syntax


SELECT column_name
FROM table AS alias_name;

Question 15: Which SQL statement should you use to create an RDBMS table?

Answer: SQL developers should use the “CREATE TABLE” statement to create a new table in an RDBMS. The syntax for this is as follows:


CREATE TABLE tablename (
  Column1 datatype,
  Column2 datatype,
  Column3 datatype
);

Question 16: How can you test for the NULL value in an RDBMS?

Answer: You need to use the SQL “IS NULL” operator to test whether a column contains a NULL value. The syntax is as follows:


SELECT columnnames
FROM tablename
WHERE columnname IS NULL;

Question 17: What are the aggregate functions of SQL?

Answer: SQL offers the following aggregate functions:

  • “COUNT”: This counts the number of relevant elements.
  • “SUM”: This calculates the sum of the attributes/expression that meet the prescribed conditions.
  • “AVG”: This calculates the average value of the attributes/expressions in a group.
  • “MIN”: This function finds the minimum value in a group.
  • “MAX”: It finds the maximum value in a group.

Question 18: What is a “VIEW” in an RDBMS?

Answer: A “VIEW” in an RDBMS is a virtual table. It’s the result set of an SQL statement, and you don’t define the data structure for this virtual table separately. A view contains rows and columns. The columns in a view are columns from one or more tables in an RDBMS. However, a view allows you to present data as if you are showing data from one table. You can create a view using the “CREATE VIEW” statement.

Interview questions and answers for hiring mid-level programmers with SQL skills

Use the following interview questions for hiring mid-level software developers with SQL skills:

Question 19: Explain the utility of “JOIN” in SQL and mention the different types of joins.

Answer: A “JOIN” is a form of SQL query. All SQL queries to retrieve data from tables return the data in a tabular form, and a “JOIN” isn’t an exception to this. It returns data from multiple tables. A “JOIN” query returns data based on the relationships between different columns in these tables.

You can have 4 types of “JOINs”, which are as follows:

“Inner Join”

This type of “JOIN” returns rows from two tables if at least one row in each of the tables has matching values in the column that’s supposed to match. Consider the following example:


Select first_name, last_name
from student s
inner join marksheets m
on s.student_id = m.student_id

This example shows two tables named “students” and “marksheets”. If the value in the column “student_ID” in the “student” table matches with the value of “student_ID” in the “marksheets” table for at least 1 row, then this “JOIN” will return that matching row.

“Right join”

A “right join” will return all rows from the right table. If the system finds any matching rows in the left table, then it will return that. Otherwise, it will only return the rows from the right table. You can replace the syntax “inner join” with “right join” in the above-mentioned example.

“Left join”

A “left join” is similar to a “right join”, except, it returns all rows from the left table. If it finds a matching row in the right table, then it returns that. Replace the syntax “inner join” with “left join” in the above-mentioned example.

“Full join”

A “full join” returns all rows from the left and right tables. Replace the syntax “inner join” with “full join” in the above-mentioned example. You can also use the “FULL OUTER JOIN” keyword for this since a “Full Outer Join” is the same as a “Full Join”.

Question 20: Explain the working and constraints of the SQL “UNION” operator.

Answer: The SQL “UNION” operator combines the result-set of multiple SELECT statements. You need to use the keyword “UNION” after coding every SELECT statement.

This operator has the following constraints:

  • Every one of the SELECT statements must return the same number of columns. As you know, every SQL statement for data retrieval returns a table. Therefore, the “UNION” operator can’t work with different numbers of columns in different SQL statements.
  • The corresponding columns in each of the SELECT statements must have the same data types.
  • The corresponding columns in each of the SELECT statements must be in the same order.

Question 21: What is PL/SQL?

Answer: Oracle created PL/SQL (Procedural Language extensions to SQL), which is an extension of SQL. PL/SQL offers a combination of the data manipulation capabilities of SQL and the processing capabilities of a procedural language. The objectives of using PL/SQL are as follows:

  • To enhance the capabilities of SQL statements;
  • To provide added security, portability, and robustness to the execution of SQL statements;
  • To provide more control to SQL developers.

PL/SQL tells the compiler about what needs to be done through the use of a SQL statement. It instructs the compiler about how to do that using procedural language capabilities. Programmers can use loops, conditions, and object-oriented concepts, thanks to PL/SQL.

Question 22: Provide examples of data integrity constraints in an RDBMS.

Answer: Data integrity constraints in an RDBMS helps you to enforce the business rules associated with a database. You can prevent invalid information from getting into RDBMS tables by using data integrity constraints. A few examples of such constraints are as follows:

  • “NOT NULL”: You can’t enter a null value in a column that enforces the “NOT NULL” constraint.
  • Unique key: Columns with this constraint prevent duplicates, however, they can accept null values.
  • Primary key: This combines the “unique key” and “NOT NULL” constraints. A column or a combination of columns designated as a primary key in a table must have unique values only. It can’t have a null value, and a table can have only one primary key.
  • Foreign key: A column or combination of columns can be a foreign key in a table. That column or the combination of columns must be a primary key in another table.
  • “Check”: Values in a column must conform to a specific condition.

Question 23: What is the “First Normal Form” in RDBMS?

Answer: The process of “normalization” in an RDBMS involves different levels, and the “First Normal Form” (1NF) is one such level. You can say that a table is in 1NF if it meets the following conditions:

  • The table has only single-valued attributes.
  • The attribute domain doesn’t change.
  • Every attribute or column has a unique name.
  • The order of storing data is immaterial.

Question 24: What is the “Second Normal Form” in RDBMS?

Answer: The “Second Normal Form” is a level in the process of normalization in RDBMS. A table is in the “Second Normal Form” if it meets the following conditions:

  • It satisfies the conditions of the “First Normal Form”.
  • There are no partial dependencies.

Question 25: Explain the concept of the “Third Normal Form” in RDBMS.

Answer: The “Third Normal Form” is a level of normalization in RDBMS. You can say that a table is in the “Third Normal Form” if it meets the following conditions:

  • It satisfies the conditions of the “Second Normal Form”.
  • There are no transitive dependencies.

SQL interview questions to hire senior developers

Hiring senior software programmers with SQL skills? Use the following interview questions:

Question 26: How can the SELECT statement retrieve unique records from a table?

Answer: You can use the “SELECT” statement with the “DISTINCT” keyword to retrieve unique records from a table. The following is an example:


SELECT DISTINCT EmployeID, EmployeeName from EMPLOYEE.

This statement retrieves unique values from the table named “EMPLOYEE”.

Question 27: Mention the different case manipulation functions in

Answer: The different case manipulation functions in SQL are as follows:

  • “LOWER”: This converts all characters to lowercase.
  • “UPPER”: This changes all characters to uppercase.
  • “INITCAP”: This converts the first character of each word to uppercase.

Question 28: Explain the differences between the DELETE and TRUNCATE commands.

Answer: The differences between the DELETE and TRUNCATE commands are as follows:

DELETE TRUNCATE
This SQL statement deletes specific rows in a table. This state deletes the entire data in a table.
You can use the “WHERE” clause with the DELETE statement. The TRUNCATE statement doesn’t allow the “WHERE” clause.
The use of the DELETE statement locks the row that it’s supposed to delete. The TRUNCATE statement locks the entire table.
You can ROLLBACK the changes caused by a DELETE statement. The TRUNCATE statement doesn’t allow a ROLLBACK.
The DELETE statement might have a slow execution. The TRUNCATE command executes fast.

Question 29: What is “Cross Join” in SQL?

Answer: An SQL “Cross Join” query produces a result set where the number of rows is the result of multiplication if you don’t use a “WHERE” clause. In this case, the RDBMS multiplies the number of rows in the first table with the number of rows in the second table.

We also call this kind of result a “Cartesian Product”. You use the keyword “CROSS JOIN” for this. A “Cross Join” becomes an “Inner Join” if you use a “WHERE” clause.

Question 30: What is “Self Join” in SQL?

Answer: A “Self Join” is a regular join. However, you “JOIN” a table with itself. You can use different aliases for the table to mention the different names of the table in the SQL query.

Question 31: Explain the use of the SQL “DESC” keyword.

Answer: The “DESC” keyword in SQL sorts the data returned by an SQL query in descending order.

Summary

You can print this question-and-answer sheet to use in an interview. Please contact us at DevTeam.Space if you need further help.

Frequently Asked Questions

We recommend you form a team with a judicious mix of senior, intermediate, and junior developers. Junior developers can deliver simple projects by learning SQL from tutorials. However, complex projects require developers with enough experience.
You can have an SQL developer write SQL statements that deliver the functionality you need. However, the performance of your application might degrade if the SQL queries aren’t optimized. The skills to optimize SQL statements are important.
SQL developers need to know about the different popular RDBMSs. The syntax of SQL is largely the same among them, however, a few minor differences exist. More than that, different RDBMSs have their unique strengths and limitations. SQL developers need to know about them.

Tell us about your challenge & get a free strategy session

Hire Expert Developers
clients
banner-img

DevTeam.Space is a vetted community of expert dev teams supported by an AI-powered agile process.

Companies like Samsung, Airbus, NEC, and startups rely on us to build great online products. We can help you too, by enabling you to hire and effortlessly manage expert developers.