SQL Server Interview Questions

1. What is Database?
A database is well organized collection of information, which can be easily accessed and managed.
We can strcutured data into columns/fields, rows and tables, we can also add some indexes on the table which can be used to retrieve required information easily and fast.

The important goal to create a database is to store large set of data at one place, accessing it and managing information.

Static website does not need any database but nowadays there are many websites are availabile on the internet which used database. For example checking flight schedule on the internet and rate of the flights.

Adavanced databases are working on the DBMS(Database management system) or RDBMS(Relational database management system) concept. There are number of databases avalailable today to manage datas like SQL Server, Oracle, Sybase, MySQL, PostgreSQL etc. Earlier foxpro and MS Access also was there and it was more popular.

Recently NOSQL concept is there which stores data in JSON document.

2. What is DBMS?
DBMS stands for Database Management System, which is a software for storing and manipulating data. It also provides security mechanisam for authentication and authorization. DBMS accpets user request for accessing data from user interface or any other application and tell operating system to provide specific data. It can be a large or small application which uses this stored data into database.

It allows you to create any kind of database per your requirement. Mainly it provides user interface between application and database where your data is stored.

3. What is RDBMS? How is it different from DBMS?
RDBMS stands for Relational Database Management System, It refers to a collection of applications and it provides a facility to the user to create, retrieve and manipulates the database.It structure the data into logically separate tables. There are different fatures which distinguish a RDBMS and DBMS as below.

DBMS RDBMS
It stores the data in hierarchical form It stored the data in tabular form
At a time only ONE user can manage At a time MULTIPLE user can manage
It requires less software and hardware capacity It requires more software and hardware capacity
It is capable of managing limited amount of data It is capable of managing unlimited amount of data
It does not support distributed database It supports distributed database
It does not provides ACID(Atomicity, Consistency, Isolation, and Durability) model It supports stucture of data on ACID model

4. What is SQL?
SQL is Structured Query Language, which is used to store, retrieve and manipulates the data stored in relational database.

All the RDBMS (Relational Database Management System) SQL Server, MySQL, MS Access, Postgres use SQL as their standard database query language. For the relational database SQL is accepted as a standard query language.

5. What is the difference between SQL and MySQL?
SQLMySQL
SQL is a query language user for operating relational databasesMySQL is a first open-source relational database
SQL is a query languageMySQL is a relational database which uses sql as a query language
SQL used to access, update and manipulate stored data from the databaseMySQL is used to store the data into database in well organized manner
SQL used to writing queries to retrieve data from databasesMySQL used to store, modifying data in tabular format
SQL is just a query language so does not support any interfaceMySQL comes with tools which provides UI for building databases.
SQL is a standard format which follows simple syntax and commandsMySQL get updated frequently
SQL support single storage engineMySQL support more storage engines, so it is more flexible
SQL is not an open-source so if you get any issue you have to rely on Microsoft SQL Server supportMySQL is an open-source platform and it has wide and robust support from community
SQL is much more secureMySQL is less secure compared to SQL, Any one can easily modify the database files.

6. What are Tables and Fields?
Table
In relational database model, there are some terms to identify the data or collection of the data. a table is the collection or group of the data elements which are orgranised in the terms of the rows and columns. A table also can represent a relationship of data. It is the easiest way to store the data storage. In table there can be a duplicate data as well. A single table can store same kind of data for multiple entity. Lets say we want to store all the students information then we can create a student table and each row contains a student records. Single student table can save all the students data. Following is the example of student data table

IDNameBirthdateStandardResult
1Alex4-Jul-20107th79 %
2Adrian2-Sept-20007th80 %
3Adrian12-Aug-20147th91 %
4Rose21-Dec-20107th89 %

Field
A field is a piece of data which is stored in each row as a column. Let's say in above table there are total 5 fields called ID, Name, Birthdate, Standard and Result. So each of them called a field. A field can have a single piece of information for that perticular row in a tale.

7. What are Constraints in SQL?
SQL Constraints

SQL Constraints are used to define rules into the data table. It means we can define some rules for the table that what should be the checks must be performed while inserting the data into table.


This constraints cab be ensure the reliability and accuracy of the data into the table. Suppose we want dateofbirth for each student then we can set a constraints on DateOfBirth column as not null, so it will enforce user to enter the dateofbirth else it will not allow user to make the student entries into the student table. In this way user has to enter the dateofbirth for the student while entering data to the table.


SQL supports multiple types of constraints as below

  • NOT NULL - This constraint ensures that a given column cannot have a NULL value.
  • UNIQUE - This constraint ensures that no duplicate values allowed for given column, each value must be a different
  • PRIMARY KEY - This constraint will not allow duplicate or a null values. It's a combination of a NOT NULL and UNIQUE constraint.
  • FOREIGN KEY - We also can say a reference key, it will point value of another table row column
  • CHECK - This constraint ensures that all values in a column satisfies a specific condition
  • DEFAULT - If we are not inserting any value for given column then it will autometically sets default value to the columns
  • INDEX - This constraint used to retrieve data faster.

8. What is a PRIMARY KEY CONSTRAINT in SQL Server?
The Primary Key Constraint in sql server uniquely identifies each row or records in any table.
It must be a UNIQUE values and it cannot contain NULL values.
Any table can have only one primary key, it can be for single column or multiple columns or fields.

9. What is a UNIQUE constraint?
SQL UNIQUE Constraint

The unique constratints ensures that all the values in a column are different
PrimaryKey and UNIQUE key constratints both are same and work in a same way, but in a table we can put more than 1 unique key while we can keep only 1 PrimaryKey constratints, UNIQUE Key constratint can have 1 null value while primary key column can not have any null value.

Example of UNIQUE Constraint while CREATE TABLE

The below example will creates an unique constratint on the pk column in student table CREATE TABLE Student ( PK int NOT NULL UNIQUE, FirstName varchar(100) NOT NULL, LastName varchar(100), DateOfBirth DateTime );


10. What is a Foreign Key?

In SQL Server foreign key constraint is a key which is used to link two tables together. Foreign key is a field in one table that relates to primary key of another table. The table which contains PRIMARY key that is called parent table and the table which contains reference key is called child table.


11. What is a Join? List its different types.

A jon claused is used to combine data or raws from more than 1 table based on some common field value. Below are the different types of the JOINs in SQL:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN


12. What is a Self-Join?

The SQL SELF JOIN is a regular join but in this case it will join with itself rather than another table.


13. What is a Cross-Join?

SQL Cross join is also a join but it will produced a result set equals to number of rows from first table multiplied by number of rows from another table if no where condition is provided. This result set is also knows as Cartesian Product. If we add where condition then it will work similar to INNER JOIN.


14. What is an Index? Explain its different types.

Indexes are special data structure associated with views or tables which helps query to speed up. It means where we are running query on very large table then indexes helps us to return the result faster. There are two types of indexed in the SQL Server

  • Clustered Indexed
  • Non Clustered Indexes
When we define any PRIMARY KEY on the table then it will create a clustered index on the table. We can create only one clustered index on the table while we can create multiple non-clustered index on the table. It is good to create an index on the numberic filed.


15. What is Data Integrity?

In SQL data integrity is the overall accuracy, completeness and consistency of the data. It also refers to the safety of data in terms of compliance. Data integrity also ensures that your data is safe from any outside forces.


16. What is a Query?

In SQL query is one type of questions we are asking database table. Generally when we ask question, we expect some result in return, so same way in database when we query database it will return some data as per condition we have passed to query. We can query to single table or multiple tables and also filter records based of perticular conditions. It can be simple to very complex.


17. What is a Subquery?

A sub query is the query within main query. It is also called nested query which will provide data to the parent query. Generally it will be enclosed between parenthesis. It can return single value or multiple rows as well. We can join with subquery too.


18. What is the SELECT statement?

The SELECT statement is used to retrieve data from the data table which returns data in the tabular format, is is called result set.


19. What are some common clauses used with SELECT query in SQL?

The SELECT statement has some common clauses as below.

  • SELECT
  • FROM
  • WHERE
  • GROUP BY
  • ORDER BY
  • UNION
  • HAVING
  • OFFSET
  • FETCH FIRST
  • INTERSECT
  • EXCEPT
  • WITH


20. What are UNION, MINUS and INTERSECT commands?


UNION
The UNION operator is used to combine two result set of two different select query, it combines two result set into one. In other word we can see the result set obtained using union is collection of the both result set as far as columns are same, order of columns is same and data type of column should be same or compatible with each other.

MINUS
The MINUS operator is used to retrieved only distinct rows from the first resultset. It must to follow the conditions that we have seen in the union clause like columns must be same, order of the column must be same and datatype should be same or should be compatible with each other.

INTERSECT
The INTERSECT operator is used to filter common records from two result set of two different select query, it checks distincts records from both result set. It must to follow the conditions that we have seen in the union clause like columns must be same, order of the column must be same and datatype should be same or should be compatible with each other.


21. What is Cursor? How to use a Cursor?

SQL Server curosr process a result set one row at a time, it processed all the row one by one. SQL works on result set, select statement return bunch of rows from the database and sometimes you want to process one by one row from result set then cursor will come into the picture. Cursor can be used to process all the row one by one.


22. List the different types of relationships in SQL.

When ever we are storing one entiti's records into two table then we can say those data has relationship between them. There are different types of relationships as below in SQL Server.

  • One-to-One Relationship
  • One-to-Many or Many-to-One Relationship
  • Many-to-Many Relationship

One-to-One Relationship
Whenever first table has one record and another table has also exact one record using PRIMARY KEY and FOREIGN KEY then it is called one to one relationship.

One-to-Many or Many-to-One Relationship
When first table has one records and another table has one or more related records then it is called one to many or many to one relationship.

Many-to-Many Relationship
This kind of relationship exists when each record of the first table can be related to one or more than one records of the second table and single record of the second table can be related to one or many records in first table.


23. What is an Alias in SQL?

In SQL server alias is very important feature, we can give alias for columns and tables as well, here we will see both example how to use alias for columns and table. In SQL Server when we write select query and select columns then in the resultset it will display column name as heading see below example,

		SELECT firstname, lastname FROM Employee;
	

It will return result set as below

firstnamelastname
RohitSharma
BradPeter
NilArmstrong

In the above result we can see the column headings are used as firstname and lastname respectivly. Now lets say if we want to display employee full name as one column so we need to concat both column like below

		SELECT firstname + ' ' + lastname FROM Employee;
	

It will return result set as below

(No column name)
Rohit Sharma
Brad Peter
Nil Armstrong

In the above result we can see column heading has not given any name to the column so here we can use column alias like below so it will give some meaningful name to the column. First we will see the syntax for column alias as below

		column_heading | expression  AS column_heading_alias
	

In the above syntax keyword AS is an optional. Following is an example to use an column alias.

		SELECT firstname + ' ' + lastname AS 'Employee Name' FROM Employee;
	

If alias name contains space then we need to enclose it into quatation mark as shown in the above example.

Table alias is also same like column alias, in joining cluase we are using table alias to get the column name from perticular table then we can give table alias like below

		SELECT
			e.firstname,
			e.lastname,
			d.name,
			e.dateofbirth
		FROM
			employee e
		INNER JOIN department d ON d.id = e.departmentid;
	

In the above example e and d both are table alias so we can make the table name shorter while accessing column name form perticular table.


24. What is a View?

Views in SQL are similar to virtual tables, it has rows and columns from 1 for more tables. It can have all the columns or selected columns from the table and rows are based on the certain condition.


25. What is Normalization?

Normalization is a process of structuring the data in database in a way which can avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.


26. What is Denormalization?

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


27. What are the various forms of Normalization?

In RDBMS below are the forms of Normalization

  • 1NF (First Normal Form)
  • 2NF (Second Normal Form)
  • 3NF (Third Normal Form)
  • BCNF (Boyce-Codd Normal Form)
  • 4NF (Fourth Normal Form)
  • 5NF (Fifth Normal Form)
  • 6NF (Sixth Normal Form)


28. What are the TRUNCATE, DELETE and DROP statements?

In SQL there are a multiple of ways to delete data, including the DELETE, TRUNCATE TABLE and DROP TABLE commands. Now we need to understand which one should we use in a given situation? So lets see in which situation which statement we need to use, use DELETE : if we want to remove some specific rows from a given table. use TRUNCATE : if we want to remove all the data from table and need the blank table structure, It is faster then DELETE. use DROP TABLE: if we want to remove entire table along with it's data and structure.


29. What are Aggregate and Scalar functions?

In SQL it's providing some functions which are being used to do some operations on the data, those are built-in functions, they are categorised in two different category and both category has 7 functions as below
Aggregate functions

These functions are used to do some operations on the column value of the table and it's return single value only

  • AVG() - It will return an average value of the column from return rows
  • COUNT() - It will count the number of rows return from the query
  • FIRST() - It will return the first column value from the return result set
  • LAST() - It will return the last column value from the return result set
  • MAX() - It will return the maximum column value from the return result set
  • MIN() - It will return the minimum column value from the return result set
  • SUM() - It will return the sum of the column value from the return result set

Scalar functions

These functions are based on user input, these too returns single value.

  • UCASE() - It converts the value of a field to uppercase
  • LCASE() - It converts the value of a field to lowercase
  • MID() - It extracts texts from the text field.
  • LEN() - It will return the length of the given string
  • ROUND() - It will round the decimal number to the nearest integer
  • NOW() - It returns the current system date and time
  • FORMAT() - It is used to format how a field is to be displayed


30. What is User-defined function? What are its various types?

In SQL user defined function (UDF) is a programming construct which accepts some parameters, does some work using accepted params and returns a type of result. There are two types of UDF in SQL table=-valued and scalar-valued.