Top 30 SQL Interview Questions and Answers

Here I am discussing commonly asked SQL interview questions and answers for a fresher and more experienced level. 

1. What Is SQL?

SQL (Structured Query Language) is a database which was design to retrieve and manipulate data. It is a Standard of an American National Standard Institute(ANSI). This language is used to perform tasks on data such as Select, Update, Delete and Insert.

2. What Are Tables and Fields in a Database?

A table is a collection of data that are designed in a model with columns and rows. In a table, the number of columns is specified, which is known as fields, but the number of rows is not defined and are known as records.

3. What Is a Database?

A database is a set of information in an ordered form for accessing, storing, and retrieving data.

4. What Are the Types of DBMSs?

A DBMS is a program that controls the maintenance and use of data. It is considered a file manager that manages the data. There are four types of DBMSs:

• Relational DBMS
• Hierarchical DBMS
• Network DBMS
• Object-Oriented Relation DBMS

And the most useful DBMS is Relational DBMS. It provides a relational operator for data.

5. Write Some Different Types of SQL Commands

SQL commands are separated into the following types:

• DDL (Data Definition Language) — It is used to define the structure of the database.
• DCL (Data Control Language) — It is used to provide permissions to the user.
• DML (Data Manipulation Language) — It is used to manage the data.
• DQL (Data Query Language) — All commands are in SQL which is used to retrieve data is coming under in DQL.
• TCL (Transaction Control Language) — It is used to manage the changes made by DML.

6. What Is a View in SQL?

It contains rows and columns from one or more table and can be defined as a virtual table. It consumes less memory to store.

Syntax:

CREATE VIEW view_name AS
SELECT column_name1, column_name2
FROM table_name
WHERE CONDITION;

7. What Are Joins in SQL?

Join is used to retrieve data from related rows and columns. It works between two or more tables, and it returns at least one match from both tables.

Types of joins are:

• Right Join
• Left Join
• Inner Join
• Outer Join
• Self-Join
• Cross Join
• Full Join

8. What Is a Query in SQL?

A database query is a request from the database table for data. A query can be a select query or any other type of query.

9. What Is a Subquery and What Are the Types?

A subquery is a part of the query. The main query is known by outer query and subquery is recognized by inner query. A subquery is always executed first and passes the result to the main query.

10. What Is Autoincrement?

Autoincrement is a keyword that is used to generate a number whenever we insert a new record in the table.

11. What Are the Constraints in SQL?

It can be used to set the limit of data types in the table. While creating or updating the table statement, constraints can be used. Some constraints are:

• NOT NULL
• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• DEFAULT

12. How Many Keys Are in SQL and How Do They Work?

There are different types of keys in SQL:

• Super Key — Set of one or more keys is defined as Super Key, and it is used to identify a record in a table uniquely. Primary key, unique key and the Alternate key is a subset of Super Key.
• Primary Key — It is a set of one or more fields in a table. They will not accept null values and duplicate values. And only one primary key will be existing in a table.
• Foreign Key —A key which defines a primary key in one table and as a field in another table is identified as a Foreign key.
• Unique Key — Apart from the primary key, there are more keys in a table, and it also identifies a record uniquely, but the only difference is that they accept only one null value but not duplicate values.
• Candidate Key — In any case, if required that any candidate key can work as a primary key.
• Compound Key — This key is a combination of candidate key and primary key.
• Alternate Key — In any case, if required that any Alternate key can work as a primary key or candidate key.

13. What Is Normalization?

Normalization is a designing technique that arranges the tables in a way that decreases the dependency of data. It divides a table in small modules and links them by relationship.

14. What Is Denormalization?

Denormalization is an optimization method in which we increase excess data to the table, and it is applied after normalization.

15. What Is a Stored Procedure?

A stored procedure is a set of SQL statements which is used as a function to access the database. To decrease network traffic and improve the performance we use stored procedures.

Syntax:

CREATE Procedure Procedure_Name
(
//Parameters
)
AS
BEGIN
SQL statements in stored procedures to update/retrieve records
END

16. What Is an Index?

An index is used to speed up the performance of queries. It makes for faster retrieval of data from the table. The index can be created on a group of columns.

17. What's the Difference Between a Clustered and a Non-Clustered Index?

Clustered Index — It helps do to easy retrieval of the data, and only one clustered index assign with one table. It changes the way records are saved in the database.

Non-Clustered Index — As compared to the Clustered index, Non-clustered is slow. And in case of a non-cluster index, the table can have multiple indexes. It creates an object for a table which is a point to the table after searching.

18. What Is a Trigger?

A trigger is used to perform a specific action on the table such as INSERT, UPDATE or DELETE. It is a type of Stored procedure. Action and event are the main components of triggers. When Action is performed the event arises in response to that action.

19. What Are Properties in a Database?

Generally, the properties are referred to as ACID. They help in a database transaction.

Atomicity — In a transaction connecting two or more separate pieces of data, either all the pieces are committed, or none are.

Consistency — A transaction either generates a new and valid state of data or, if any disappointment happens, returns all data to its state before the transaction was started.

Isolation — A transaction in process and not yet committed must continue isolated from any other operation.

Durability — In this operation, the system saved the committed data, whenever event failure and system restart, all the data is available in its correct position.

20. How Many Statements Are in SQL?

SQL Statements are divided into several categories:

• Data Definition Language Statements
• Data Manipulation Language Statements
• Transaction Control Statements
• Session Control Statements
• System Control Statements
• Embedded SQL Statements

21. What Is a CLAUSE?

It is defined to set the limit of the result set by giving condition to the query. They filter some rows from the whole records.

Some SQL CLAUSES are WHERE and HAVING.

22. What Are Aggregate Functions?

It is a mathematical function that returns single values.

Aggregate functions in SQL are:

• AVG() — return average value.
• COUNT() — return number of rows
• MAX() — return the largest value
• MIN() — return the smallest value
• ROUND() — based on decimal specification this function rounds a numeric field.
• SUM() — return the sum.

23. What Are String Functions?

To manipulate the string, we use String Function. Some of these are:

• LEN() — returns the length of the value.
• LOWER() — converts character data to lower case.
• UPPER() — converts character data to upper case.
• SUBSTRING() — extracts characters.
• LTRIM() — removes all the whitespace of the string from the beginning.
• RTRIM() — removes all the whitespace of the string at the end.
• CONCAT() — concatenate functions.
• REPLACE() — update the content.

24. What Is Collation?

Collation is a collection of rules that determine how data is sorted through compare. Such as character data stored using a set of rules that define the sequence of correct character along with type, accent, and case-sensitive.

25. What Is a Cursor?

When a SQL statement is executed in system memory, a temporary work area is created, which is known as a Cursor. At the select statement, cursor stored the information. The cursor can engage more than one row but can process only one row at a time. The set of rows is known as an active set.

The cursor has two types:

• Implicit Cursor
• Explicit Cursor.

26. What Is SQL Server?

SQL server is a type of or an example of Microsoft relational database management system (RDBMS). It provides a wide range of transaction processing and business intelligence in the IT atmosphere.

27. What Operators Are Available in SQL?

An operator is a reserved word used mainly in a SQL statement’s WHERE clause to make operations.

• Arithmetic Operations
• Logical Operations
• Comparison Operators ()
• Compound Operators ()

28. How Do You Define NULL Value, Blank Space, and ZERO?

Null value is a field without value. It is different from Zero. Assume, there is a table, and in the table, there is a field, it is possible to insert a record into the field without adding value, then that field will be saved with a NULL value.

Blank space is the value that we provide.

Zero is just a number.

29. What Is a Data Warehouse?

A Data Warehouse is known as a Central hub of data from multiple sources of information. These data are available for online processing as well as mining.

30. What Is the difference Between UNIQUE KEY and PRIMARY KEY?

In the table, there should be only one PRIMARY KEY, but in another case, UNIQUE KEY can be any number of.

PRIMARY KEYS don’t allow NULL values, but UNIQUE KEY allows NULL value.