SQL (General)

Question 1

What is SQL?

Answer 1

tructured Query Language (SQL) is the language used in relational database management systems (RDBMS) to query, update, and delete data. SQL is a standard query language for RDBMS. SQL language’s queries are also known as SQL commands or SQL statements.

Question 2

What are the types of Joins in SQL?

Answer 2

There are four types of joins in SQL.

• INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
• FULL JOIN: Returns all rows when there is a match in ONE of the table.

Question 3

What is the default join in SQL?

Answer 3

The default join in SQL is INNER JOIN.

Question 4

Describe a LEFT JOIN in SQL?

Answer 4

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name

Question 5

Describe a RIGHT JOIN in SQL?

Answer 5

The right join returns all the rows in the right table (table2) with the matching ones on the left table (table1).

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Question 6

Describe a FULL OUTER JOIN in SQL?

Answer 6

The full join returns all rows from the left table (table1) and from the right table (table2).

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Question 7

What is Union?

Answer 7

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Note: The UNION operator selects only distinct values by default.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Question 8

What is Union All?

Answer 8

The UNION operator is used to combine the result-set of two or more SELECT statements.

UNION ALL is not distinct as the UNION is.

SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Question 9

Differentiate Clustered and Non clustered Index in SQL?

Answer 9

A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.

A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.

Question 10

What is a Composite Key?

Answer 10

A composite key, in the context of relational databases, is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.

Question 11

Explain the difference between Stored Procedure and User Defined Function?

Answer 11

Stored Procedure (mini program):

Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when every time it is called.

Function (computed values):

Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only a single value or a table. We can’t use functions to Insert, Update, Delete records in the database table(s). It is compiled every time it is invoked.

Question 12

What are Cursors and why do we use Cursors in SQL Server?

Answer 12

A SQL cursor is a database object which is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row.

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.

Question 13

What are triggers and when to use a trigger

Answer 13

A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs.

If you write a trigger for an insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in memory. Then it does the insert operation and then the statements inside the trigger executes. We can query the “INSERTED” table to manipulate or use the inserted row(s) from the trigger. Similarly, if you write a trigger for a delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row.

We use a trigger when we want some event to happen automatically on certain desirable scenarios.

Question 14

What are Views in SQL Server

Answer 14

Views are database objects which are like virtual tables that have no physical storage and contains data from one table or multiple tables. A View does not have any physical storage so they do not contain any data. When we update, insert or apply any operation over the View then these operations are applied to the table(s) on which the view was created.

Question 15

What is the difference between CHAR and VARCHAR datatype?

Answer 15

The CHAR data type:

It is a fixed length data type
Used to store non-Unicode characters
Occupiers 1 byte of space for each character

The VARCHAR data type:

It is a variable length data type
Used to store non-Unicode characters
Occupies 1 byte of space for each character

Question 16

What is Cast() and Convert() Functions in SQL Server?

Answer 16

Both CAST and CONVERT are functions used to convert one data type to another data type. CAST and CONVERT are often used interchangeably. CAST is an ANSI standard and is available on most SQL platforms whereas CONVERT is a MSSQL specific function.

Question 17

What is Commit and Rollback Commands in SQL Server?

Answer 17

Commit is used for permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can’t Rollback after the Commit.

Question 18

What is Scalar Value Function in SQL Server?

Answer 18

A Scalar-valued function in SQL Server is used to return a single value of any T-SQL data type. A CREATE FUNCTION statement is used to create a Scalar-valued function. The name of the function should not be more than 128 characters. It is not a rule but it is conventional that the name of the function should begin with the prefix fn.

Up to 1024 input parameters can be defined for Scalar-valued functions. A Scalar-valued function however cannot contain an output parameter. The value is returned by a Scalar-valued function using the RETURNS clause.

Question 19

What is Pivot And Unpivot In SQL Server?

Answer 19

PIVOT and UNPIVOT are two relational operators that are used to convert a table expression into another. PIVOT is used when we want to transfer data from row level to column level and UNPIVOT is used when we want to convert data from column level to row level. PIVOT and UNPIVOT relational operators are used to generate a multidimensional reporting. Today we will discuss both the operators. PIVOT and UNPIVOT relational operators are used to generate an interactive table that quickly combines and compares large amount of data.

Question 20

What is User Defined Table Types And Table Valued Parameters?

Answer 20

The concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs) were introduced in SQL Server 2008. Before SQL Server 2008, it was not possible to pass a table variable in stored procedure as a parameter, after SQL Server now we can pass Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Before passing the table variable, first we need to create a user-defined table variable. So now we create a user-defined table type.

Question 21

Explain TRY_PARSE, TRY_CONVERT And TRY_CAST

Answer 21

TRY_PARSE

It converts string data type to target data type(Date or Numeric). For example, source data is string type and we need to convert to date type. If conversion attempt fails it returns NULL value.

TRY_CONVERT

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.

TRY_CAST

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need it in double/integer format. Then this will help us in achieving it.

Question 22

What does FETCH_STATUS Function do in SQL Server?

Answer 22

To find the most recent FETCH statement in SQL Server 2012 we use the @FETCH_STATUS system function. We can use the FETCH_STATUS system function with a while loop in SQL Server 2012. The FETCH_STATUS system function returns three values in SQL Server 2012 which are explained below.

When FETCH_STATUS system function returns 0 the FETCH is successful and it is equal to zero.

When FETCH_STATUS system function returns -1 the FETCH was unsuccessful.

When FETCH_STATUS system function returns -2 the FETCH was unsuccessful because the row was deleted.

Question 23

Difference Between Row_Number() Rank() And Dense_Rank()

Answer 23

Row_Number function helps perform more complex ordering of row in the report format that allow the over clause in SQL standard.

Rank() Function function will assign a unique value to each distinct Row, but it leaves a group between the groups.

Dense_Rank() Function is similar to Rank with only difference, this will not leave gaps between groups.

Question 24

What is the use of IDENTITY and SCOPE_IDENTITY?

Answer 24

@@IDENTITY will return the last identity value entered into a table. IDENTITY is not limited to a specific scope. Suppose we create a table and the set identity value to true for a column in the table. After that when we insert data into table we get the last identity value using IDENTITY. If the statement did not affect any tables with identity columns, then IDENTITY returns NULL. You can also access the IDENTITY value in your application.

SCOPE_IDENTITY returns the last identity values that were generated in any table in the current session. You will always get the value that was last inserted by your insert statement in the identity column, regardless of whether the insertion happens with your insert statements in any table or you execute any procedure that is doing any insertion operation in any table.

Question 25

Differences between After Trigger vs Instead of Trigger

Answer 25

After Trigger fire after the execution of an action query that can be either DDL statements like Create, Alter and Drop or DML statements like Insert, Update and Delete.

Instead of Trigger triggers fire before the execution of an action query that can only be DML statements like Insert, Update and Delete but after the execution of that query. The table data will not be affected, in other words if you want to insert or update the data of the table then you need to write it in the trigger using “inserted” or “deleted” virtual tables.

Question 26

Difference Between IsNull() and Coalesce() Functions?

Answer 26

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

The Coalesce() function returns the first non-null value among its arguments. This function doesn’t limit the number of arguments, but they must all be of the same data type.

Question 27

What is Temporary Table in SQL Server

Answer 27

Temporary tables are tables that are available only to the session that created them.

These tables are automatically destroyed at the termination of the procedure or session that created them.

Use of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in the current session.

Temporary tables are created using the same syntax as a CREATE TABLE except the table name starts with a ‘#’ sign. When the table consists of a single ‘#’ sign, it is defined as a local temporary table and its scope is limited to the session it is created in.

Question 28

Explain Row_Number function?

Answer 28

Ranking functions provide a very good feature of assigning numbering to the records in the result set in SQL. Row_Number is one of these functions available in SQL Server that allows us to assign rankings or numbering to the rows of the result set data. Different values are assigned to different rows, based on the type of ranking function used.

This function works by assigning continuous ranking to the records, without skipping any number in the result set, whether it is partitioned or not. At the end of the discussion, we will see what we mean by continuous ranking and not skipping any record.

Question 29

What is Surrogate Key in SQL Server?

Answer 29

A Surrogate Key in SQL Server is a unique identifier for each row in the table. It is just a key. Using this key we can identify a unique row. There is no business meaning for Surrogate Keys. This type of key is either database generated or generated via another application (not supplied by user).

A Surrogate Key is just a unique identifier for each row and it may use as a Primary Key. There is only requirement for a surrogate Primary Key, which is that each row must have a unique value for that column. A Surrogate Key is also known as an artificial key or identity key. It can be used in data warehouses.

Question 30

What is Lock in SQL Server?

Answer 30

A concurrency conflict occurs when one user displays an entity’s data in order to edit it, and then another user updates the same entity’s data before the first user’s change is written to the database. If you don’t enable the detection of such conflicts, whoever updates the database last overwrites the other user’s changes.

Pessimistic Concurrency (Locking)

This form of concurrency handling locks the record for everyone, including readers until the update operation is fully complete and committed.

Optimistic Concurrency

This form of concurrency does not lock the record for readers, rather is updates a @@rowversion column every time the record is updated. If another user attempts to update a record with a lower @@rowversion value then you know the record has been updated by somebody else and should not be able to be committed.

Question 31

What is ACID Property?

Answer 31

For any business, transactions that may be comprised of many individual operations and even other transactions, play a key role.

Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.

A transaction is characterized by four properties, often referred to as the ACID properties: atomicity, consistency, isolation, and durability.

Question 32

What is Normalization?

Answer 32

Database design to remove redundant data.

Question 33

What is denormalization

Answer 33

A design pattern to improve search performance by limiting normalization. We merge separate table to create master tables to optimize for search.

Question 34

Explain OLTP and OLAP

Answer 34

These pertain to splitting your data logic across 2 separate databases, one for processing change transactions (create, update, and delete) OLTP and one for speed read optimization (OLAP). The OLTP is generally going to be a normalized structure and the OLAP is going to be a de-normalized.

Whenever data is inserted into the OLTP and ETL process is triggered which sync the data with the OLAP

Question 35

What is the 1st Normal Form?

Answer 35

A table that has no repeating groups. For example there are no comma separated values. Each value should have its own column

Question 36

What is the 2nd normal form?

Answer 36

Tables should have columns that represent single values and rows should be dependent on a primary key.

Question 37

What is the 3rd normal form?

Answer 37

Tables should have columns that hold atomic values, each row should be dependent on the primary key, and there should not be any transient data that relies on non key data.

Question 38

What is the difference between a primary key and a unique key?

Answer 38

Primary key cannot be null whereas the unique key can be. There can only be 1 primary key whereas you can have any number of unique indexes/keys

Question 39

What is the difference between char and nchar?

Answer 39

char is a fixed length character string whereas nchar is a fixed length unicode string (non-english)

Question 40

What is the point of an index?

Answer 40

Improves search performance.

Question 41

What is a transaction?

Answer 41

A group of statements that are treated as a single operation. If any of the statements fail then the entire transaction fails.

Question 42

What is a cross join?

Answer 42

A join that return all matching records from both tables regardless of what fields match. You cannot use the ON clause with a cross join

Question 43

What is a SQL Group By clause for

Answer 43

The GROUP BY statement groups rows that have the same values into summary rows

Its used mostly for aggregate functions. For example to count the number of distinct values in a column

–Count how many loans are VA, FHA, conventional, re-fi, etc
SELECT loan_type, count(*)
FROM loans
GROUP BY loan_type

Question 44

What is a HAVING clause in SQL?

Answer 44

Lets us specify a condition on a GROUP BY statement

--Find loans with LTV greater than 80
SELECT loan_type, Count(*)
FROM loans
GROUP BY loan_type
HAVING SUM(ltv_value) >= 80;