T-SQL Interview Questions and Answers

What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both of these data types are used for characters, but varchar2 is used for character strings of variable length, whereas char is used for character strings of fixed length. For example, if we specify the type as char(5) then we will not be allowed to store a string of any other length in this variable, but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length. We can store a string of length 3 or 4 or 2 in this variable.

What is a Default constraint?

The DEFAULT constraint is used to fill a column with default and fixed values. The value will be added to all new records when no other value is provided. For more details please refer to the SQL | Default Constraint article.

What is Denormalization?

Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. Note that denormalization does not mean not doing normalization. It is an optimization technique that is applied after normalization.

In a traditional normalized database, we store data in separate logical tables and attempt to minimize redundant data. We may strive to have only one copy of each piece of data in the database.

What is a query?

An SQL query is used to retrieve the required data from the database. However, there may be multiple SQL queries that yield the same results but with different levels of efficiency. An inefficient query can drain the database resources, reduce the database speed or result in a loss of service for other users. So it is very important to optimize the query to obtain the best database performance.

What is a subquery?

In SQL a Subquery can be simply defined as a query within another query. In other words, we can say that a Subquery is a query that is embedded in the WHERE clause of another SQL query.

What is ETL in SQL?

ETL is a process in Data Warehousing and it stands for Extract, Transform, and Load. It is a process in which an ETL tool extracts the data from various data source systems, transforms it in the staging area, and then finally, loads it into the Data Warehouse system. These are three database functions that are incorporated into one tool to pull data out from one database and put data into another database.

What is SQL injection?

SQL injection is a technique used to exploit user data through web page inputs by injecting SQL commands as statements. Basically, these statements can be used to manipulate the application’s web server by malicious users.

SQL injection is a code injection technique that might destroy your database.

SQL injection is one of the most common web hacking techniques.

SQL injection is the placement of malicious code in SQL statements, via web page input.

Difference between delete and truncate.

Delete	Truncate
It is a DML command.	It is a DDL command.
Delete command is used to remove some or all the records from a table.	Truncate is used to remove all the records from a

What are joins in SQL?

There are many types of Joins in SQL. Depending on the use case, you can use different type of SQL JOIN clause. Here are the frequently used SQL JOIN types:

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

NATURAL JOIN

What is primary key?

A primary key in SQL is a column or set of columns that uniquely identifies each row in a table. It is used to establish relationships between tables and ensure data integrity. A primary key must be unique, not null, and cannot be changed once it is created. It is typically defined using the PRIMARY KEY constraint in a table's CREATE TABLE statement.

What are constraints?

In SQL, constraints are rules that restrict the data that can be entered into a database table. They are used to ensure data integrity and maintain the consistency of the data stored in the table. There are several types of constraints including primary key constraints, foreign key constraints, check constraints, and unique constraints. These constraints are enforced by the database management system and prevent invalid data from being inserted into the table. Using constraints helps to maintain the accuracy and reliability of the data in the database.

Types of constraints?

7 Different Types of Constraints in SQL

NOT NULL Constraint in SQL. ...
UNIQUE Constraint in SQL. ...
PRIMARY KEY Constraint. ...
FOREIGN KEY Constraint. ...
CHECK Constraint. ...
DEFAULT Constraint. ...
CREATE INDEX Constraint.

What are subsets of SQL?

4 subsets of SQL: Structured Query Language.

DDL — Data Definition Language — It deals with database schemas and descriptions of how the data should reside in the database. ... create Alter, drop.
DML - Data Manipulation Language – Select, update, insert
DCL — Data Control Language — It allows us to access control to the database. Grant , Revoke
TCL - Transaction Control Language. Commit, rollback, savepoint

What is normalization in a database?

Normalization is the process of organizing data in a database. It includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Database normalization is the process of organizing a database structure into tables and defining relationships between them in such a way that data redundancy and dependency are minimized. It aims to reduce data duplication, improve data integrity, and ensure efficient data management.

In SQL Server, database normalization is typically achieved through the following normalization forms:

First Normal Form (1NF):

Ensures that each column contains atomic values (single values) and there are no repeating groups of data.
Example: Breaking down a column with multiple phone numbers into separate phone number columns.

Second Normal Form (2NF):

Meets 1NF requirements and ensures that non-key attributes are fully functionally dependent on the primary key.
Involves creating separate tables for subsets of data that apply to multiple rows and depend on the same key.
Example: Moving order details (like order line items) into a separate table with the order ID as the primary key.

Third Normal Form (3NF):

Meets 2NF requirements and ensures that non-key attributes are transitively dependent only on the primary key.
Involves further breaking down tables to eliminate dependencies on non-key attributes.
Example: Separating customer address details into a separate table with customer ID as the primary key.

Beyond 3NF, there are additional normalization forms such as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF), which address more complex dependencies and further reduce data redundancy.

Benefits of Database Normalization in SQL Server:

Reduces data duplication, which saves storage space and improves data consistency.
Minimizes update anomalies by ensuring data modifications only need to be made in one place.
Enhances data integrity by enforcing relationships and constraints between tables.
Improves database performance by reducing redundant data retrieval and storage operations.

While normalization is essential for data organization and integrity, it's also crucial to strike a balance between normalization and denormalization based on specific application requirements, performance considerations, and query optimization needs.

What are the set operations in SQL?

Set operations in SQL is a type of operations which allow the results of multiple queries to be combined into a single result set. Set operators in SQL include UNION , INTERSECT , and EXCEPT , which mathematically correspond to the concepts of union, intersection and set difference.

In SQL, set operations are used to combine the results of two or more queries or tables. The main set operations supported by SQL are:

UNION:

The UNION operator is used to combine the results of two SELECT statements into a single result set.
It removes duplicate rows by default unless UNION ALL is used.
Syntax:

SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;

UNION ALL:

Similar to UNION, UNION ALL also combines the results of two SELECT statements into a single result set, but it retains duplicate rows.
Syntax:

SELECT column1, column2, ... FROM table1 UNION ALL SELECT column1, column2, ... FROM table2;

INTERSECT:

The INTERSECT operator is used to retrieve rows that appear in both result sets of two SELECT statements.
It returns only distinct rows, removing duplicates.
Syntax:

SELECT column1, column2, ... FROM table1 INTERSECT SELECT column1, column2, ... FROM table2;

EXCEPT or MINUS:

The EXCEPT or MINUS operator is used to retrieve rows from the first SELECT statement that are not present in the result set of the second SELECT statement.
It returns only distinct rows, removing duplicates.
Syntax:

SELECT column1, column2, ... FROM table1 EXCEPT SELECT column1, column2, ... FROM table2;

SELECT column1, column2, ... FROM table1 MINUS SELECT column1, column2, ... FROM table2;

These set operations are powerful tools for combining and comparing data from multiple tables or queries in SQL, allowing for flexible and efficient data manipulation and analysis.

What is difference between in and between operator in SQL?

The key differences between the two operators are:

The IN operator is used to match values from a specified list, while the BETWEEN operator is used to match values within a specified range.
The IN operator can be used with any data type, including strings, numbers, and dates, to match specific values. On the other hand, the BETWEEN operator is typically used with numerical or date values to define a range.
The IN operator allows for specifying multiple discrete values, whereas the BETWEEN operator specifies a continuous range of values.
Using the IN operator can result in simpler and more concise queries when filtering based on a specific set of values, while the BETWEEN operator is useful for filtering based on a numerical or date range.

What is difference between where and Having operator in SQL?

The WHERE and HAVING clauses are both used in SQL queries to filter rows based on specified conditions, but they are used in different contexts and have distinct functionalities:

WHERE Clause:

The WHERE clause is used to filter rows before any groupings are made in a query.
It is typically used with SELECT, UPDATE, DELETE, and other SQL statements to specify conditions for selecting or modifying rows.
The WHERE clause filters rows based on individual row values, such as columns in a table.
Example:

SELECT column1, column2 FROM table_name WHERE condition;

HAVING Clause:

The HAVING clause is used to filter rows after the grouping of data has been performed using the GROUP BY clause.
It is specifically used with aggregate functions like SUM, COUNT, AVG, MAX, MIN, etc., to filter groups of rows based on aggregate values.
The HAVING clause filters groups of rows based on aggregated values, such as the result of applying aggregate functions to columns in a grouped result set.
Example:

SELECT column1, COUNT(column2) AS count_column2 FROM table_name GROUP BY column1 HAVING COUNT(column2) > 10;

What are the aggregate functions in SQL?

Aggregate functions in SQL are used to perform calculations on sets of values and return a single result. They operate on groups of rows and are commonly used with the GROUP BY clause to generate summary information from a table. Some of the common aggregate functions in SQL are:

COUNT():

Counts the number of rows that match a specified condition or returns the total number of rows in a table.
Syntax:

SELECT COUNT(column_name) FROM table_name WHERE condition;

SUM():

Calculates the sum of a numeric column's values in a table.
Syntax:

SELECT SUM(column_name) FROM table_name WHERE condition;

AVG():

Calculates the average of a numeric column's values in a table.
Syntax:

SELECT AVG(column_name) FROM table_name WHERE condition;

MIN():

Returns the minimum value in a column.
Syntax:

SELECT MIN(column_name) FROM table_name WHERE condition;

MAX():

Returns the maximum value in a column.
Syntax:

SELECT MAX(column_name) FROM table_name WHERE condition;

GROUP_CONCAT() or STRING_AGG():

Concatenates the values of a column into a single string, optionally separated by a delimiter.
Syntax (GROUP_CONCAT, MySQL):

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') FROM table_name WHERE condition GROUP BY group_column;

Syntax (STRING_AGG, SQL Server):

SELECT STRING_AGG(column_name, ', ') FROM table_name WHERE condition GROUP BY group_column;

COUNT(DISTINCT):

Counts the number of unique values in a column.
Syntax:

SELECT COUNT(DISTINCT column_name) FROM table_name WHERE condition;

What is DBMS?

DBMS stands for Database Management System. It is a software application or system that allows users to create, manage, manipulate, and access databases. A DBMS provides an interface for users and applications to interact with databases efficiently and securely.

In DBMS (Database Management System), there are different types of databases based on the data model they use to organize and structure data. The main types of databases in DBMS are:

Relational Databases:

Relational databases organize data into tables consisting of rows and columns, where each row represents a record and each column represents an attribute or field. They use structured query language (SQL) for data manipulation and querying.
Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server.

NoSQL Databases:

NoSQL (Not Only SQL) databases are designed to handle unstructured and semi-structured data. They use non-relational data models and are suitable for large-scale distributed systems and applications requiring high scalability and flexibility.
Types of NoSQL databases:

Document-oriented databases (e.g., MongoDB, Couchbase): Store data in JSON or BSON documents.
Key-value stores (e.g., Redis, Amazon DynamoDB): Store data as key-value pairs.
Column-family stores (e.g., Apache Cassandra, HBase): Organize data in column families and columns rather than rows.
Graph databases (e.g., Neo4j, Amazon Neptune): Model data using graph structures with nodes, edges, and properties.

Object-Oriented Databases:

Object-oriented databases model data as objects, similar to object-oriented programming concepts. They support complex data types, inheritance, encapsulation, and polymorphism.
Examples: ObjectDB, db4o, ObjectStore.

Hierarchical Databases:

Hierarchical databases organize data in a tree-like structure with parent-child relationships. Each record (node) can have multiple children but only one parent.
Example: IBM Information Management System (IMS).

Network Databases:

Network databases extend hierarchical databases by allowing multiple parent-child relationships between records. They use a network model to represent complex relationships between data entities.
Example: Integrated Data Store (IDS).

Temporal Databases:

Temporal databases store historical or time-varying data, allowing users to track changes over time and perform temporal queries.
Example: Oracle Temporal Database, SQL Server Temporal Tables.

Spatial Databases:

Spatial databases store and manage spatial data and support spatial data types, indexing, and spatial queries for geographic information systems (GIS) applications.
Example: PostGIS (for PostgreSQL), Oracle Spatial, Microsoft SQL Server Spatial.

Display the rank without any gap to show the sales wise rank?

Select productid, sales, dense_rank() over(order by sales desc) as sales_dese_rank from facttable

Write a query by creating a condition in which if the total expenses is less than 60 then it is a profit or else loss?

Select total_expenses, IIF(total_expenses<6,’profit’,’loss’) as status from facttable

What is the difference between views and tables?

Basis	View	Table
Definition	A view is a database object that allows generating a logical subset of data from one or more tables.	A table is a database object or an entity that stores the data of a database.
Dependency	The view depends on the table.	The table is an independent data object.
Database space	The view is utilized database space when a query runs.	The table utilized database space throughout its existence.
Manipulate data	We can not add, update, or delete any data from a view.	We can easily add, update, or delete any data from a table.
Recreate	We can easily use replace option to recreate the view.	We can only create or drop the table.
Aggregation of data	Aggregate data in views.	We can not aggregate data in tables.
table/view relationship	The view contains complex multiple tables joins.	In the table, we can maintain relationships using a primary and foreign key.

DBMS Vs. RDBMS

Parameters	DBMS	RDBMS
Access	Data elements need to be accessed separately	Multiple data elements can be accessed at the same time
Relationship Between Data	No relationship between data	Data in tables are related to each other
Normalization	It is not present	It is present
Distributed Database	It does not support distributed databases.	It supports distributed database
Data Storage Format	Data is stored in either a navigational or hierarchical form	Data is stored in a tabular structure with headers being the column names and the rows containing the corresponding values
Amount of Data	It deals with a small quantity of data	It deals with a larger amount of data
Data Redundancy	It is prevalent	Keys and indexes do not allow data redundancy
Number of Users	It supports a single user	It supports multiple users
Data Fetching	It is slower for large amounts of data	It is speedy due to the relational approach
Data Security	Low-security levels when it comes to data manipulation	Multiple levels of data security exist
Software and Hardware Requirements	Low	High
Examples	XML, Window Registry, etc.	MySQL, SQL Server, Oracle, Microsoft Access, PostgreSQL, etc.

State the differences between clustered and non-clustered indexes

Parameters	Clustered Index	Non-Clustered Index
Used For	Sorting and storing records physically in memory	Creating a logical order for data rows; pointers are used for physical data files
Methods for Storing	Stores data in the leaf nodes of the index	Never stores data in the leaf nodes of the index
Size	Quite large	Comparatively, small
Data Accessing	Fast	Slow
Additional Disk Space	Not required	Required to store indexes separately
Type of Key	By default, the primary key of a table is a clustered index	It can be used with the unique constraint on the table that acts as a composite key
Main Feature	Improves the performance of data retrieval	Should be created on columns used in Joins

What is a TRIGGER in SQL?

The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands, such as insert, update, and delete, are given.

The syntax used to generate the trigger function is as follows:

CREATE TRIGGER trigger_name

What do you know about magic tables in SQL Server?

A magic table can be defined as a provisional logical table that is developed by an SQL Server for tasks such as insert, delete, or update (DML) operations. The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.

What are the types of views in SQL?

In SQL, the views are classified into four types. They are the following:

Simple View: It is a view based on a single table and does not have a GROUP BY clause or other features.
Complex View: It is a view built from several tables and includes a GROUP BY clause as well as functions.
Inline View: It is a view built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
Materialized View: It is a view that saves both the definition and the details. It builds data replicas by physically preserving them.

How will you optimize a slow-moving SQL query? What are some of the optimization techniques?

We can optimize a slow-moving SQL query by using indexing in the DBMS to find the specific rows in a table very quickly.

There are several optimization techniques:

1. Indexing

2. Using Distinct

3. Having and Where clauses

4. Avoiding correlated subqueries

5. Limit

6. Column statistics

What do you know about the stuff() function?

The stuff() function deletes a part of the string and then inserts another part into the string, starting at a specified position.

Syntax:

1	STUFF(String1, Position, Length, String2)

Here, String1 is the one that will be overwritten. The position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1.

Example:

1	select stuff(‘SQL Tutorial’,1,3,’Python’)

This will change ‘SQL Tutorial’ to ‘Python Tutorial’

Output:

1	Python Tutorial

What is a database cursor? How to use a database cursor?

A database cursor is a control that allows you to navigate around a table’s rows or documents. It can be referred to as a pointer for a row in a set of rows. Cursors are extremely useful for database traversal operations such as extraction, insertion, and elimination.

After any variable declaration, DECLARE a cursor. A SELECT statement must always be aligned with the cursor declaration.
To initialize the result set, OPEN statements must be called before fetching the rows from the result table.
To grab and switch to the next row in the result set, use the FETCH statement.
To deactivate the cursor, use the CLOSE expression.
Finally, use the DEALLOCATE clause to uninstall the cursor description and clear all the resources associated with it.

Here is an example SQL cursor:

DECLARE @name VARCHAR(50)

DECLARE db_cursor CURSOR FOR

SELECT name

From myDB.company

WHERE employee_name IN (‘Jay’, ‘Shyam’)

OPEN db_cursor

FETCH next

FROM db_cursor

Into @name

Close db_cursor

DEALLOCATE db_cursor

What is a Deadlock or a live Deadlock, and how do you resolve it?

A deadlock is a situation where a set of processes are blocked because each process is holding the resource and waiting for the other resource. A live deadlock is just like a deadlock-like situation where the processes block each other with a repeated state change yet make no progress.

There are several ways to prevent a deadlock or live deadlock situation:

Acquired multiple locks for a thread.
Abort and restart the process.
Timeouts
Transaction Rollback

What is the difference between the DELETE and TRUNCATE commands?

DELETE: This query is used to delete or remove one or more existing tables.

TRUNCATE: This statement deletes all the data inside a table.

What are some common clauses used with SELECT queries in SQL?

There are many SELECT statement clauses in SQL. Some of the most commonly used clauses with SELECT queries are as follows:

FROM: The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given.
WHERE: The WHERE clause defines the parameters that are used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects.
GROUP BY: The GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions.
ORDER BY: The ORDER BY clause helps in choosing the columns on which the table’s result should be sorted.
HAVING: The HAVING clause filters the results of the GROUP BY clause by using an aggregate function.

Explain the difference between OLTP and OLAP.

OLTP: It stands for Online transaction processing, and we can consider it a category of software applications that are efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to maintain consistency. The OLTP system often follows decentralized planning to avoid single points of failure. This system is generally designed for a large audience of end users to perform short transactions. The queries involved in such databases are generally simple, require a fast response time, and, in comparison, return only a few records. So, the number of transactions per second acts as an effective measure for those systems.

OLAP: It stands for online analytical processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions. For OLAP systems, the efficiency of computing depends heavily on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multidimensional schemas.