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.
Check out our free courses to get an edge over the competition.
Explore our Popular Software Engineering Courses
Top SQL Interview Questions & Answers
You must ensure that you are equipped to respond to all types of queries, from the simple to the complex, when preparing for interview SQL questions. Prior preparation can go a long way towards increasing your chances of getting hired for your dream job. Roles such as a developer or data analyst might require you to own proficiency in SQL server interview questions or even DBMS interview questions as well. Therefore, thorough preparation is necessary before stepping into the interview space.
Our list contains many detailed SQL interview questions and answers, that are often asked or expected to be asked in such interviews. These SQL interview questions and answers will be useful to you whether you are a DBA, developer, tester, or data analyst.
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. SQL syntax structures and behaviours are formalised by the SQL standard across all database solutions. It becomes even more crucial in open-source databases like MySQL and PostgreSQL, where communities rather than large businesses mostly maintain the RDBMS. 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.
SQL databases provide the framework for numerous applications and services across various industries. SQL database servers are used by businesses to store and retrieve data because they enable a wide range of operational capabilities, such as transaction processing, analytics, and business intelligence needed to manage mission-critical applications. Becoming a pro database administrator will help you ace SQL query interview questions.
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.
Check out upGrad’s Advanced Certification in DevOps
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.
If you want to land a high-paying position as a database developer, you must demonstrate your proficiency and understanding in the DBMS field during your next job interview and correctly respond to DBMS interview questions.
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.
Here are a few constraints in SQL that can be asked as SQL server interview questions.
|Constraints in SQL||Usage|
|NOT NULL||Whenever a column shouldn’t accept null values.|
|CHECK||Used to verify that each piece of information in the column meets a certain requirement.|
|DEFAULT||Used to establish the column’s default value when the user doesn’t enter one.|
|UNIQUE||Used when every value in the column has to be distinctive.|
|PRIMARY KEY||Used to create a distinct identity for each row in a table.|
|FOREIGN KEY||Used to locate rows or records kept in a different table.|
|INDEX||Used to quickly obtain and search data from a database.|
Check out upGrad’s Advanced Certification in Blockchain
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.
Explore Our Software Development Free Courses
|Blockchain Technology||React for Beginners||Core Java Basics|
upGrad’s Exclusive Software Development Webinar for you –
SAAS Business – What is So Different?
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.
One of the most common SQL questions is what are the different types of Join?
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.
Read: Top 9 Data Science Tools in 2020
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
In-Demand Software Development Skills
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
SQL query interview questions might demand you to explain more than one way to create a temporary table. So, here’s an alternative way to do so.
Use an INTO statement within a SELECT query.
Let’s make a temporary table with the names, ages, and genders of all the male student records from the student table.
SELECT name, age, gender
WHERE gender = ‘Male’
Look at the aforementioned question. The name, age, and gender of each record for a male student from the student table are stored in a temporary table called “#MaleStudents” that was created in this location. After the SELECT statement, we define a temporary table using the INTO statement, while ensuring that the table’s name must begin with a hash (#).
Learn Software Development Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.
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 Executive PG Program 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.
Read our Popular Articles related to Software Development
What is SQL?
SQL - Structured Query Language, is a programming language used to store data and retrieve data from a database. It is much like a human language. In fact, SQL is a part of ANSI, which was developed by the American National Standards Institute. SQL is the most widely used programming language designed for Relational Database Management Systems. In other words, SQL is used for storing and manipulating data in a database. SQL is a standard that is independent of the database product.
What are the differences between SQL and MongoDB?
SQL and MongoDB are the leading database systems at the moment. SQL has been the most used database system for decades with a reputation of being powerful and versatile. SQL supports relational database management system (RDBMS). It comprises of tables and records with unique primary key. RDBMS allows users to query data with SQL queries, update data and define relationships between tables. MongoDB is used to store data in a flexible, scalable manner. It makes storing and managing the data easier for developers and businesses.
What are SQL triggers?
The SQL Trigger is a special action which is invoked when some event is fired. These events can be firing and changing of a row, before and after insert, and before and after update. Triggers are very important in the relational database. With the help of triggers, one can automatically maintain the data integrity and referential integrities. Triggers are actually stored procedures which are executed automatically when a triggering event occurs. In the simplest sense, an SQL trigger is a SQL event. The primary use of an SQL trigger is to automatically make copies of a row in another table when a certain action is performed on the source table.