RDBMS is one of the most frequently used databases at present. Therefore SQL skills are essential in most job roles. In this SQL Interview Questions article, I will introduce you to the most commonly asked questions on SQL (Structured Query Language). With this article, you will learn everything you need to know about Oracle, MS SQL Server, and MySQL database. Also, you can boost your interview preparation by reading our article on the Top 50 SQL Interview Questions.

SQL Interview Questions And Answers

First, we will start with questions related to beginners, and then we will move on to the more advanced questions. So, the following are top SQL interview questions and answers that interviewers commonly ask:

1) What are DBMS and RDBMS?

DBMS are software applications that help in building and maintaining databases. RDBMS, on the other hand, is a subset of DBMS based on its relational model.

2) What is SQL?

Structured Query Language or SQL is the basic language for relational database management systems. It is primarily used in handling organized data comprised of entities (variables) and relations between different entities of the data.

By Learning SQL, you will be able to quickly and efficiently mine data for information.

3) Explain SQL Constraints?

To specify the rules of data type in a table, constraints are used. You can specify them while creating or altering the table. The following are the constraints in SQL:

• NOT NULL - Confines NULL value from being inserted into a column.
• CHECK - Authenticates that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has been defined for the field.
• UNIQUE - Assures unique values to be inserted into the field.
• INDEX - Indexes a field giving faster retrieval of records.
• PRIMARY KEY - Uniquely classifies each record in a table.
• FOREIGN KEY - Secures referential integrity for a record in another table.

4) What is a trigger?

Triggers are stored programs, which get automatically executed when an event such as INSERT, DELETE, UPDATE(DML) statement occurs. Data definition statements (DDL) and database operations can also invoke them. For example, SERVER ERROR, LOGON.

create trigger dbtrigger  
on database  
for  
create_table,alter_table,drop_table  
as  
print'you can not create ,drop and alter table in this database'  
rollback; 
create trigger emptrigger  
on emp  
for  
insert,update,delete  
as  
print'you can not insert,update and delete this table i'  
rollback;

5) What are the subsets of SQL?

These are the subsets of SQL:

• DDL(Data Definition Language): Consists of SQL commands like CREATE, ALTER, and DELETE.
• DML(Data Manipulation Language): Accesses and handles data. It uses INSERT, UPDATE commands.
• DCL(Data Control Language): Controls access to the database. It uses commands like GRANT and REVOKE.

6) What is the difference between SQL and MySQL?

SQL is a standard language that retrieves and manipulates structured database data. Meanwhile, MySQL is a relational database management system, similar to SQL Server, Oracle, or IBM DB2, which is used to manage SQL databases.

7) What do you mean by Data Manipulation Language (DML)?

DML includes the most common SQL statements for storing, modifying, deleting, and retrieving data. There are four options: SELECT, UPDATE, INSERT and DELETE.

INSERT INTO table_name /*    Insert is a DML  statement */
VALUES (value, value, value  …)
INSERT INTO customers /*   data being inserted in the table customers    */
VALUES (‘George’, 'Washington', 'Connecticut')

8) What are the scalar functions in SQL? Give an example?

The scalar function in SQL returns a single value based on the input values. Scalar functions include:

• UCASE(): Changes the specified field in the upper case

SELECT UCASE("SQL is FUN!") AS UppercaseText;
UppercaseText
SQL IS FUN!

• LCASE(): Changes the specified field in lower case

9) What is a Cursor, and when do you use it?

A cursor is a database object used to manipulate data by traversing row by row in a result set. You use a cursor to retrieve data from a result set, one row at a time, and to update records one row at a time.

10) What is a Deadlock?

Deadlock is an unwanted situation where two or more transactions are waiting indefinitely for one another to release the locks.

11) What are ACID properties in a transaction?

Certain properties are followed to make sure that a database's before and after transactions are consistent.

• Atomicity: Transactions need to be completed completely and cannot be left half-done.
• Consistency: To preserve integrity constraints hence valid data enters the database
• Isolation: Handles concurrency
• Durability: Once a transaction is committed it stays committed

They are called ACID properties.

12) What is a System Privilege?

DBAs grant certain rights to users to perform particular actions on database schema objects, such as creating tablespaces. Users can be granted the following system privileges:

• Using CREATE TABLE, a grantee can create tables in their schema.
• CREATE USER lets the grantee create users in the database.
• By utilizing CREATE SESSION, grantees can create a session with an Oracle database.

13) What are Object Privileges?

Permissions granted to a database user account or role to act on a database object are object-level privileges. These object privileges contain SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables, etc. The following are examples of object privileges that can be granted to users:

• SELECT ON hr.employees TO myuser
• INSERT ON hr.employees TO myuser

14) What is the role of GRANT and REVOKE commands?

When GRANT runs on database objects, it grants them privileges, while REVOKE removes them. These are DCL commands:

GRANT CREATE ANY TABLE TO username
GRANT sysdba TO username
GRANT DROP ANY TABLE TO username
REVOKE CREATE TABLE FROM username

15) Can Stored Procedures be called within Stored Procedures?

It is possible to call a stored procedure from another stored procedure. For instance, Procedure2 is the procedure which is called Procedure1. Business logic can be implemented in both Procedure1 and Procedure2.

Create PROCEDURE Procedure1
AS BEGIN
Exec Procedure2
END

16) What is Data Integrity?

Data integrity specifies the accuracy, consistency, and reliability of data stored in the database. There are four types of data integrity:

• Row Integrity
• Column Integrity
• Referential Integrity
• User-Defined Integrity

17) What is a Data warehouse?

Data warehouses are central repositories where information from multiple sources is stored. They have subsets of data called Data marts. The stored data is transformed and used for online mining and processing.

18) What are user-defined functions and What are their types?

User-defined functions are those which use specific logic whenever necessary. It avoids redundancy by avoiding writing the same logic multiple times. User-defined functions fall into three categories:

• Scalar functions
• Inline Table-valued functions
• Multi statement valued functions

19) What are STUFF and REPLACE functions?

STUFF: overwrites the existing character or inserts a string into another string.

Syntax:

STUFF(string_expression,start, length, replacement_characters)

REPLACE: replaces the existing characters of all the occurrences.

Syntax:

REPLACE (string_expression, search_string, replacement_string)

20) What is Collation? Name different types of collation sensitivity.

Collation refers to the set of rules that determine how to store and compare data. The types of collation sensitivity are as follows:

• Case
• Kana
• Width
• Accent

21) What are the different types of Collation sensitivity?

Following is a list of collation sensitivity types:

• Accent Sensitivity.
• Case Sensitivity – A and a and B and b.
• Kana Sensitivity – Japanese Kana characters.
• Width Sensitivity – Single-byte character and double-byte character.

22) What is Online Transaction Processing (OLTP)?

Online Transaction Processing (OLTP) manages transaction-based applications, such as data entry, data retrieval, and data processing. It streamlines and simplifies data management. Contrary to OLAP systems, OLTP systems serve real-time transactions.

For Example – Daily Bank Transactions.

23) What is the Recursive stored procedure?

A recursive stored procedure calls itself until it reaches a boundary condition. This recursive method helps programmers reuse the same set of code over and over again.

24) What are the advantages and disadvantages of stored procedures?

Advantages:

• Better Performance – The procedure calls are quick and efficient because stored procedures are compiled once and stored in executable form. Therefore, the response is quick. Consequently, the executable code is automatically cached, reducing the memory requirements.
• Higher Productivity – Since the same piece of code is reused repeatedly, it leads to higher productivity.
• Ease of Use – To create a stored procedure, one can use any Java Integrated Development Environment (IDE). After that, they can be deployed on any tier of network architecture.
• Scalability – Stored procedures increase scalability by separating application processing on the server.
• Maintainability – Maintaining a procedure on a server is far easier than keeping copies of it on multiple client machines, simply because scripts are located in one place.
• Security – Oracle data can be restricted by allowing users to access the data only through stored procedures that execute with their definer's privileges.

Disadvantages:

• Testing – Testing logic that is encapsulated within stored procedures is very difficult. Data errors in handling stored procedures are not generated until runtime.
• Debugging – Depending on the database technology, debugging stored procedures will either be very difficult or impossible. There are some debugging options in relational databases such as SQL Server.
• Versioning – The stored procedure does not support version control.
• Cost: An extra developer is required (in form of DBA) for accessing SQL and writing a better-stored procedure. This will automatically incur added cost.
• Portability – Complex stored procedures might not always port to upgraded versions of the same database. This is especially true in the case of moving from one database type(Oracle) to another database type(MS SQL Server).

25) What are Union, minus, and Intersect commands?

UNION: This is used to combine the results of two tables, and it eliminates duplicate rows from the tables.

MINUS: This is used to return rows from the first query but not from the second query. Matching records from the first and second queries, as well as other rows from the first query, will be displayed as a result set.

INTERSECT: This is used to return rows returned by both the queries.

26) What is the difference between TRUNCATE and DROP statements?

TRUNCATE removes all the rows from the table, and it cannot be rolled back. Meanwhile, the DROP command removes a table from the database and the operation cannot be rolled back.

27) What is an ALIAS command?

ALIAS name can be given to a table or column in a table. This alias name can be referred to in the WHERE clause to identify the table or column. For Example:

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

28) What is CLAUSE?

By providing conditions to the query, SQL clauses are defined to limit the result set. Usually, this filters out some rows from the whole set of records.

Example - Query that has WHERE condition

Query that has HAVING condition.

29) Which operator is used in query for pattern matching?

LIKE operator is used for pattern matching. It can be used as -.

• % - Matches zero or more characters.
• _(Underscore) – Matching exactly one character.

Example:

Select * from Student where studentname like 'a%'

Select * from Student where studentname like 'ami_'

30) What do you mean by Denormalization?

Denormalization is the process of accessing data from higher to lower forms of a database. As it introduces redundancy into a table, it helps the database managers to increase the performance of the entire infrastructure. Using database queries, it combines data from multiple tables into a single table to add redundant data into a table.

31) What are Entities and Relationships?

Entities: Items in the real world about which data can be stored in a database. Tables store data that represents a single type of entity.

For example – A bank database has a customer table that stores customer information. This information is stored in the customer table as a set of attributes (columns within the table) for each customer.

Relationships: Relations between entities with common interests.

For example – Customer names are associated with customer account numbers and contact information, which could be in the same table. Relationships between separate tables can also exist (for example, customer to account).

32) What is an Index?

An index is a performance tuning method for retrieving records from a table more quickly. An index creates an entry for each value, thus making data retrieval faster.

33) Explain different types of indexes in SQL.

There are three types of index in SQL:

• Unique Index: If the column is unique indexed, this index prevents duplicate values from appearing in the field. A unique index can be applied automatically if a primary key is defined.

• Clustered Index: This index reorders the physical order of the table and searches based on key values. There can only be one clustered index per table.

• Non-Clustered Index: The non-clustered index does not affect the physical order of the table and keeps the logical order of the data. There can be many unclustered indexes in a table.

34) What is Normalization and what are its advantages?

SQL normalization refers to the process of organizing data to avoid duplication and redundancy. The advantages include:

• More Compact Database

• Reduction of redundant and duplicate data

• Ensure Consistent data after modification

• Better Database organization

• Efficient data access

• Allows easy modification

• Quickly find the information

• More Tables with smaller rows

• Easier to implement Security

• Greater Flexibility for Queries

35) Explain different types of Normalization.

There are several successive levels of normalization called normal forms. Each following normal form depends on the previous one. In most cases, the first three normal forms will suffice.

• First Normal Form (1NF) – No repeating groups in rows.

• Second Normal Form (2NF) – Every non-key (supporting) column value depends on the whole primary key.

• Third Normal Form (3NF) – Only depends on the primary key and no non-key columns (supporting).

36) Why are SQL functions used?

SQL functions are used for the following purposes:

• To perform some calculations on the data
• To change individual data items
• To handle the output
• To format numbers and dates
• To transform the data types

37) List the various types of relationships in SQL.

One-to-One - This is a relationship between two tables where each record in one table is associated with a maximum of one record in the other table.

One-to-Many & Many-to-One - This is the most common relationship where a record in one table is associated with multiple records in another table.

Many-to-Many - This scenario occurs when on both sides there are multiple instances needed to define the relationship.

Self-Referencing Relationships - Used when a table needs to define a relationship with itself.

38) What are the differences between OLTP and OLAP?

Difference:

• OLTP or Online Transaction Processing is a class of software applications capable of supporting transaction-oriented programs. One of the most important attributes of OLTP is its ability to maintain concurrency. Many OLTP systems follow a decentralized architecture to prevent single points of failure. Systems like these are generally designed for a wide audience of end-users who conduct short transactions. These databases usually contain simple queries, require quick responses, and return relatively few records. Such systems measure effectiveness by counting transactions per second.

• OLAP stands for Online Analytical Processing, a class of software programs that are characterized by a relatively low frequency of online transactions. The queries are often too complex and involve too many aggregates. OLAP systems depend highly on response time as an effectiveness measure. The vast majority of these systems are used for data mining or maintaining aggregated, historical data, usually in multidimensional format.

39) How to create empty tables with the same structure as another table?

Smartly, you can create empty tables with the same structure by fetching the records of one table into a new table using the INTO operator while setting the WHERE clause to false for all records. Therefore, SQL creates a new table that has a duplicate structure to accept the fetched records, but since no records are fetched due to the WHERE clause, nothing is inserted into the new table.

Example:

SELECT * INTO Students_copy

FROM Students WHERE 1 = 2;

40) What is a Datawarehouse?

In a data warehouse, multiple sources of information are gathered to form one central repository of information. This data is consolidated, transformed, and made available for online mining and processing. Warehouse data also contain a subset of data called Data Marts.

41) What is Auto Increment in SQL?

With the Autoincrement keyword, a unique number gets generated whenever a new record is inserted into a table. In SQL, this keyword is usually required whenever PRIMARY KEY is used.

42) What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL and Windows. To change authentication mode in SQL Server, follow these steps:

• To run SQL Enterprise Manager from the Microsoft SQL Server program group, click Start> Programs> Microsoft SQL Server and select SQL Enterprise Manager.
• Select the server from the Tools menu.
• Click on SQL Server Configuration Properties, and choose the Security page.

43) How can you fetch alternate records from a table?

You can fetch alternate records with both odd and even row numbers. For example -

• To display even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

• To display odd numbers:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

44) What is the difference between DELETE and TRUNCATE statements?

When TRUNCATE is used, all the rows from the table are deleted, and the tablespace is freed up.

When DELETE is used, it either deletes all the rows from the table or only those rows determined by the condition specified in the where clause. However, it does not free up the space occupied by the table.

45) What are the different set operators available in SQL?

The different set operators available in SQL are Union, Intersect, and Minus.

46) List some case manipulation functions in SQL?

There are three case manipulation functions in SQL, namely:

• LOWER: This function returns a string in lowercase. It takes a string as an argument and returns it in lower case.

Syntax:

LOWER(‘string’)

• UPPER: The string is returned in uppercase. It takes a string as an argument and converts it into uppercase before returning it.

Syntax:

UPPER(‘string’)

• INITCAP: Returns a string with the first letter capitalized and the rest lowercase.

Syntax:

INITCAP(‘string’)

47) What is a View?

A view is a virtual table that contains a subset of data contained in the main table. It takes less space to store due to the absence of views. Depending on the relationship, a view can combine data from one or more tables.

48) What are Views used for?

Views are logical snapshots of tables or view objects. It is used for the following reasons:

• Restricting access to data.
• Providing different views of the same data.
• Ensuring data independence.
• Making complex queries simple.

49) How can you fetch the first 5 characters of the string?

There are many ways to retrieve characters from a string. For example:

Select SUBSTRING(StudentName,1,5) as studentname from student

50) What is the main difference between SQL and PL/SQL?

SQL is a query language that lets you issue a single query or perform a single insert/update/delete. While, PL/SQL is Oracle's "Procedural Language" SQL, which allows you to write a full program (loops, variables, etc.) to perform multiple operations like selects/inserts/updates/deletes.

Due to the large and thriving community surrounding PostgreSQL, it is a great asset to learn this amazing technology.

---

If you have made it this far, then certainly you are willing to learn more about SQL and database. Here are some more resources related to SQL that we think will be useful to you.

• 10 Best MySQL Courses & Tutorials in 2021
• 10 Free SQL Tutorials for beginners
• Learn MongoDB Online