SQL Interview Questions and Answers
Worried about your interview? We got you covered for your SQL Interview Questions. These are few of the most frequently asked questions in Sql Interviews.
No matter what job role you choose in the software and IT industry, SQL skills are a must. Even today, RDBMS is one of the most extensively used databases around the world, and hence, SQL is an indispensable part of the development domain. Big names like Uber, Netflix, Airbnb, and of course, Microsoft use SQL as their primary database management tool.
The reason why SQL is immensely popular today is that it supports a host of transaction processing, analytics applications, and business intelligence operations in corporate IT environments.
In this post, we’ll help you get started with the basics of SQL. This is why we’ve created a list of the most commonly asked SQL interview questions. Hope SQL interview questions help you in your interviews. These SQL interview questions will offer you a relatively good idea of the fundamental concepts of SQL and how you should proceed with it.
Top SQL Interview Questions & Answers
1. Define SQL.
SQL or Structured Query Language is a relational database management system created by Microsoft. It is explicitly designed for communicating with the databases. ANSI (American National Standards Institute) maintains that SQL is the standard query language for Relational Database Management Systems (RDBMS).
It is not only used for maintaining RDBMS but also for performing a wide range of other data manipulation operations on different types of data. For instance, SQL is used for creating databases, creating tables in a database, retrieving data from a database, updating tables in a database, executing queries, and so on.
Read More: SQL for Data Science: Why SQL
2. Define Database.
Database refers to a structured form of data that is stored in an organized form in a computer to facilitate its easy access, storing, retrieval, and management of data. A database is essentially a collection of schemas, tables, queries, views, etc.
3. How is RDBMS different from DBMS?
RDBMS or Relational Database Management System is different from DBMS in the sense that RDBMS stores data as a collection of tables wherein you can define the relations between the common fields of the tables whereas, in DBMS, you cannot do so.
Unlike RDBMS that stores data in table form, DBMS functions more like a File Manager that stores the data within a database instead of saving it in a file system.
RDBMS is the foundation for many modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift.
4. Define Constraints.
In SQL, constraints are used to specify the limit on the data type of a table or declare the rules concerning data in a table in the database. Constraints can be specified for both single and multiple fields in an SQL table, either during the creation of a table or after creating it using the ALTER TABLE command.
Some of the constraints in SQL are:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
5. Define – Primary Key, Unique Key, and Foreign Key.
A primary key is a combination of fields that helps to specify a row uniquely. A primary key must have unique values, and also it contains an implicit NOT NULL constraint, meaning that a primary key cannot have NULL values.
A unique key constraint is used for ensuring that all the values in a column are different. It helps to identify each record in a database uniquely. Unlike the primary key (there’s only one primary key defined per table), there can be multiple unique constraints defined per table.
A foreign key consists of a single or collection of fields in one table that can be used to refer to the primary key of another table. This key helps maintain referential integrity in the relation between two tables. While the table containing the foreign key constraint is known as the child table, the table containing the candidate key is labeled as the parent table.
6. Define Join. What are the different types of Join?
Join is an SQL clause designed for combining records or rows from two or more tables based on a related column between them. Join acts like a keyword used for querying data from more tables based on the relationship between the fields of the tables. They are primarily used for retrieving data. However, data retrieval largely depends on the relationship between tables.
There are four types of join:
- Inner Join – This is used for returning records or rows that contain at least one matching value between the tables.
- Right Join – This returns rows that are a common match between the tables and all the other rows that lie on the right-hand side table. To put it simply, the right join returns all the rows from the right-hand-side table irrespective of the fact that there is no available match in the left-hand-side table.
- Left Join – Like the right join, left join returns rows that are common between the tables and all the rows that lie in the left-hand side table, even if there’s no available match in the right-hand side table.
- Full Join – This join returns rows when any single table consists of matching rows. In other words, it creates a set containing the result of both the right join and left join. Hence, the result-set includes all rows from both the left-hand side and right-hand side tables.
7. What is an Index? Name the different types of Indexes.
In SQL, an index is a performance tuning method that allows for faster retrieval of records from tables. They speed up the searching process in the database – an index creates an entry for each value, thereby making it faster to retrieve data.
Indexes are used to find rows that match with some columns by skimming through only those subsets of the data to find the correct matches.
There are three types of indexes:
- Unique Index – This index ensures that no two rows of data in a table have identical key values, thereby maintaining data integrity. You can automatically apply a unique index when defining the primary key.
- Clustered Index – This index helps re-order or rearrange the physical order of a table and search based on the key values. In this index, the order of rows contained in the database corresponds to the order of the rows in the index. This is why a table can have only one clustered index.
- Nonclustered Index – This index maintains the logical order of data. It is used to create a separate entity within the table that refers to the original table. A table can have multiple nonclustered indexes.
8. What is AUTO_INCREMENT?
AUTO_INCREMENT is used to automatically generate a unique number whenever a new record is added or entered into a table. Since a table has only one primary key, this primary key is added as the AUTO_INCREMENT field that helps increment the field each time a new record is added.
By default, the AUTO-INCREMENT value starts from 1, and it gets incremented by 1 whenever a new record is inserted.
9. Define Query and Subquery.
A query is a code written for requesting or retrieving data from a database table or multiple tables. A query can be an action query or a select query.
A subquery, on the other hand, is a query within another query. It is also known as an inner query or nested query. A subquery is used to restrict or enhance the data that must be queried by the main query, thereby restricting or enhancing the outcome of the main query. Usually, a subquery is executed first, and the result is passed on to the main query.
There are two types of subqueries:
- Correlated subquery – This subquery isn’t an independent query. However, it can refer to the column in a table listed in the FROM of the main query.
- Non-correlated subquery – This is an independent query, and its output is substituted in the main query.
10. Define Trigger.
A trigger is a stored code or program that is automatically executed when events like INSERT, DELETE, UPDATE(DML) statement occurs. They can also be executed as a response to Data definition statements(DDL) and database operations like SERVER ERROR, LOGON, etc. triggers help maintain the database integrity.
11. Explain the role of ACID properties in a transaction.
ACID properties are followed for maintaining consistency in a database both before and after transactions.
- Atomicity – This entails that a transaction must be completed. It should not be left midway. If ever a transaction fails, the entire transaction will fail, and the database will remain unchanged.
- Consistency – This seeks to maintain integrity constraints by validating the data entered into a database.
- Isolation – This property aims to control concurrency.
- Durability – This property ensures that once a transaction is done, it remains committed despite any challenges that may come (for example, power loss, internal errors, etc.).
12. Differentiate between DELETE and TRUNCATE commands.
The core difference between DELETE and TRUNCATE commands are as follows:
- While the DELETE command is used to delete or remove one or more existing tables, the TRUNCATE command deletes all the data from inside a table.
- DELETE is a DML command, whereas TRUNCATE is a DDL command.
- DELETE allows you to accomplish a trigger, but TRUNCATE doesn’t let you execute and trigger.
- The TRUNCATE command doesn’t work when foreign key constraints reference a table. In such cases, you have to use the DELETE command.
13. Name the different subsets of SQL.
The subsets of SQL include:
- DDL (Data Definition Language) – This language enables you to perform a host of operations on the database, including SQL commands like CREATE, ALTER, and DELETE objects.
- DML ( Data Manipulation Language) – This language lets you access and manipulate the data in a database using commands like INSERT, UPDATE, and DELETE.
- DCL ( Data Control Language) – This language allows you to control access to the database by using commands like GRANT and REVOKE.
14. Explain Data Integrity.
Data integrity defines the accuracy, consistency, and reliability of data stored in the database. Moreover, it helps define integrity constraints to enforce business rules on the data when it is inserted into a database or application.
Data integrity is of four kinds:
- Row integrity
- Column integrity
- Referential integrity
- User-defined integrity
15. What are the User-defined Functions? Name the types of User-defined Functions.
User-defined functions are functions that are specially written to use specific logic as and when required. These functions eliminate the need to write the same logic multiple times; instead, you can call or execute a user-defined function whenever needed.
There are three types of user-defined functions:
- Scalar functions.
- Inline table-valued functions.
- Multi statement valued functions.
16. Define Collation. Name the different types of collation sensitivity.
Collation refers to a collection of rules that determine how character data is sorted and compared. Apart from defining the correct character sequence to sort the character data, it incorporates options to specify case-sensitivity, accent marks, kana character types, and also character width.
The different types of collation sensitivity include:
- Case sensitivity – Characters’ A’ and ‘a’ are treated differently.
- Accent sensitivity – Characters ‘a’ and ‘á’ are treated differently.
- Kana sensitivity – This treats Japanese Kana characters like Hiragana and Katakana differently.
- Width sensitivity – This treats single-byte (half-width) character and double-byte (full-width) character differently.
17. What do you mean by Stored Procedure?
A stored procedure is a subroutine (SQL code) used for applications that access RDBMS. It supports the modular programming concept, meaning you can create a stored procedure once and save it and call it multiple times as and when required.
These procedures are stored in the database data dictionary. The advantage of a stored procedure is that it allows for faster query execution. This not only reduces network traffic but also provides better security to the data.
Another advantage is that stored procedures come with added functionality as users who can’t access the data directly can use stored procedures to gain access.
However, it has a disadvantage as well – a stored procedure can be executed only in a database, usually occupying more memory in the database server.
18. Differentiate between Views and Tables.
Here are some points of difference between views and tables:
- Views refer to a virtual table extracted from a database, while a table refers to a structured entity containing a limited number of columns and an infinite number of rows.
- Views cannot hold data by themselves, whereas tables contain data and store it in databases.
- Views allow you to query specific information contained within a few distinct tables. However, a table holds fundamental client information along with cases of characterized objects.
19. Define Temporary Table. How can you create one?
A temporary table is a table that allows you to store and process intermediate results. These tables are can be automatically deleted when they are no longer used. Temporary tables come in handy for situations where you need to store temporary data.
The syntax for creating a temporary table is:
CREATE TABLE #table_name();
The below query will create a temporary table:
create table #book(b_id int, b_cost int)
Now, we will insert the records.
insert into #book values(1,100)
insert into #book values(2,232)
select * from #book
We hope this SQL interview questions and answers guide will help you strengthen and expand your SQL knowledge base.
If you are curious to learn about SQL, and more about full stack development, check out IIIT-B & upGrad’s PG Diploma in Full Stack Software Development which is created for working professionals and offers 10+ case studies & projects, practical hands-on workshops, mentorship with industry experts, 1-on-1 with industry mentors, 400+ hours of learning and job assistance with top firms.