All The Useful SQL Server Interview Questions & Answers You Need

Part 1: SQL Basics

Q 1. How do I prepare for the SQL interview?

There are many sources online that can help you prepare for an SQL interview. You can go through brief tutorials and free online courses on SQL to revise your knowledge of SQL. You can also practice projects to help you with practical aspects of the language. 

Q 2. What are the 5 basic SQL commands?

The five basic SQL commands are:

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
Data Query Language (DQL)

Q 3. What are basic SQL skills?

SQL is a vast topic and there is a lot to learn. But the most basic skills that an SQL professional should know are:

How to structure a database
Managing a database
Authoring SQL statements and clauses
Knowledge of popular database systems such as MySQL
Working knowledge of PHP
SQL data analysis
Creating a database with WAMP and SQL

Q 4. How can I practice SQL?

There are some platforms available online that can help you practice SQL such as SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others. 

Q 5. Where can I practice SQL questions?

There are some platforms available online that can help you practice SQL such as SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others. 

You can also refer to articles and blogs online that list the most important SQL interview questions for preparation.

Q 6. What is the most common SQL command?

Some of the most common SQL commands are:

CREATE DATABASE 
ALTER DATABASE
CREATE TABLE
ALTER TABLE 
DROP TABLE
CREATE INDEX
DROP INDEX

Q 7. How are SQL commands classified?

SQL Commands are classified under four categories, i.e.,

Data Definition Language (DDL)
Data Query Language (DQL)
Data Manipulation Language (DML)
Data Control Language (DCL)

Q 8. What are basic SQL commands?

Basic SQL commands are:

CREATE DATABASE 
ALTER DATABASE
CREATE TABLE
ALTER TABLE 
DROP TABLE
CREATE INDEX
DROP INDEX

Q 9. Is SQL coding?

Yes, SQL is a coding language/ programming language that falls under the category of domain-specific programming language. It is used to access relational databases such as MySQL.

Q 10. What is a SQL example?

SQL helps you update, delete, and request information from databases. Some of the examples of SQL are in the form of the following statements:

SELECT 
INSERT 
UPDATE
DELETE
CREATE DATABASE
ALTER DATABASE 

Q 11. What is SQL code used for?

SQL code is used to access and communicate with a database. It helps in performing tasks such as updating and retrieving data from the databases.

Wondering where to learn the highly coveted in demand skills for free? Check out the courses on Great Learning Academy. Enroll to any course, learn the in demand skill and get your free certificate. Hurry!

Part 2: SQL Server Basics 

Q 1. What is SQL Server?
SQL server has stayed on top as one of the most popular database management products ever since its first release in 1989 by Microsoft Corporation. The product is used across industries to store and process large volumes of data. It was primarily built to store and process data that is built on a relational model of data. 

SQL Server is widely used for data analysis and also scaling up of data. SQL Server can be used in conjunction with Big Data tools such as Hadoop. 

SQL Server can be used to process data from various data sources such as Excel, Table, .Net Framework application, etc.

Q 2. How to install SQL Server?

1. Click on the below SQL Server official release link to access the latest version: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
2.Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system. 
3.Click on the Download Now button.
4.Save the .exe file on your system. Right-click on the .exe file and click on Open.
5.Click on 'Yes' to allow the changes to be made on your system and have SQL Server Installed.
6.Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

Q 3. How to create a stored procedure in SQL Server.
A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAME
AS
SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)
GO;
Stored procedures can be user-defined or built-in. Various parameters can be passed onto a Stored Procedure.

Q 4. How to install SQL Server 2008?
Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and type in - SQL Server 2008 download
Click on the result link to download and save SQL Server 2008.
Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
Click on the Download Now button.
Save the .exe file on your system. Right-click on the .exe file and click on Open.
Click on 'Yes' to allow the changes to be made on your system and have SQL Server installed.
Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application.

Q 5. How to install SQL Server 2017?
Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and type in - SQL Server 2017 download
Click on the result link to download and save SQL Server 2017.
Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
Click on the Download Now button.
Save the .exe file on your system. Right-click on the .exe file and click on Open.
Click on 'Yes' to allow the changes to be made on your system and have SQL Server installed.
Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

Q 6. How to restore the database in SQL Server.
Launch the SQL Server Management Studio application and from the Object Explorer window pane, right-click on Databases and click on Restore. This would automatically restore the database.

Q 7. How to install SQL Server 2014?
Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and type in - SQL Server 2014 download
Click on the result link to download and save SQL Server 2014.
Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
Click on the Download Now button.
Save the .exe file on your system. Right-click on the .exe file and click on Open.
Click on 'Yes' to allow the changes to be made on your system and have SQL Server Installed.
Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

Q 8. How to get the connection string from SQL Server?
Launch the SQL Server Management Studio. Go to the Database for which you require the Connection string. Right-click on the database and click on Properties. In the Properties window that is displayed, you can view the Connection String property.

Connection strings help connect databases to another staging database or any external source of data.

Q 9. How to install SQL Server 2012?
Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and type in - SQL Server 2012 download
Click on the result link to download and save SQL Server 2012.
Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
Click on the Download Now button.
Save the .exe file on your system. Right-click on the .exe file and click on Open.
Click on 'Yes' to allow the changes to be made on your system and have SQL Server Installed.
Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

Q 10. What is cte in SQL Server?
CTEs are Common Table Expressions that are used to create temporary result tables from which data can be retrieved/ used. The standard syntax for a CTE with a SELECT statement is:

WITH RESULT AS 

(SELECT COL1, COL2, COL3

FROM EMPLOYEE)

SELECT COL1, COL2 FROM RESULT

CTEs can be used with Insert, Update or Delete statements as well.

Few examples of CTEs are given below:

Query to find the 10 highest salaries.

with result as 

(select distinct salary, dense_rank() over (order by salary desc) as salary rank from employees)

select result. salary from result where the result.salaryrank = 10 

Query to find the 2nd highest salary

with the result as 

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result. salary from result where the result.salaryrank = 2

In this way, CTEs can be used to find the nth highest salary within an organisation.

Q 11. How to change SQL Server password.
Launch your SQL Server Management Studio. Click on the Database connection for which you want to change the login password. Click on Security from the options that get displayed. 

Click on Logins and open your database connection. Type in the new password for login and click on 'OK' to apply the changes. 

Q 12. How to delete duplicate records in SQL Server.
Select the duplicate records in a table HAVING COUNT(*)>1 

Add a delete statement to delete the duplicate records.

Sample Query to find the duplicate records in a table-

(SELECT COL1, COUNT(*) AS DUPLICATE
FROM EMPLOYEE
GROUP BY COL1
HAVING COUNT(*) > 1)

Q 13. How to uninstall SQL Server.
In Windows 10, go to the START menu and locate the SQL Server.

Right-click and select uninstall to uninstall the application.

Q 14. How to check SQL Server version.
You can run the below query to view the current version of SQL Server that you are using.

SELECT @@version;

Q 15. How to rename column name in SQL Server.
From the Object Explorer window pane, go to the table where the column is present and choose Design. Under the Column Name, select the name you want to rename and enter the new name. Go to the File menu and click Save. 

Q 16. What is the stored procedure in SQL Server?
A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAME
AS
SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)
GO;
You can execute the Stored Proc by using the command Exec Procedure_Name;

Q 17. How to create a database in SQL Server.
After installing the required version of SQL Server, it is easy to create new databases and maintain them. 

Launch the SQL Server Management Studio
In the Object Explorer window pane, right-click on Databases and select 'New Database'
Enter the Database Name and click on 'Ok'.
Voila! Your new database is ready for use.

Q 18. What is an index in SQL Server?
Indexes are database objects which help in retrieving records quickly and more efficiently. Column indexes can be created on both Tables and Views. By declaring a Column as an index within a table/ view, the user can access those records quickly by executing the index. Indexes with more than one column are called Clustered indexes.

Syntax:

CREATE INDEX INDEX_NAME 
ON TABLE_NAME(COL1, COL2);

The syntax to drop an Index is DROP INDEX INDEX_NAME;

Indexes are known to improve the efficiency of SQL Select queries. 

Q 19. How to create the table in SQL Server.
Tables are the fundamental storage objects within a database. A table is usually made up of 

Rows and Columns. The below syntax can be used to create a new table with 3 columns.

CREATE TABLE TABLE_NAME(
COLUMN1 DATATYPE, 
COLUMN2 DATATYPE, 
COLUMN3 DATATYPE
);

Alternatively, you can right-click on Table in the Object Explorer window pane and select 'New -> Table'.

You can also define the type of Primary/ Foreign/ Check constraint when creating a table.

Q 20. How to connect to SQL Server.
Launch the SQL Server Management Studio from the START menu.
In the dialog box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system.
Select the appropriate Authentication type and click on the Connect button.
A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.

Q 21. How to delete duplicate rows in SQL Server.
Select the duplicate records in a table HAVING COUNT(*)>1 

Add a delete statement to delete the duplicate records.

Sample Query to find the duplicate records in a table-

(SELECT COL1, COUNT(*) AS DUPLICATE
FROM EMPLOYEE
GROUP BY COL1
HAVING COUNT(*) > 1);

Q 22. How to download SQL Server.
The Express and Developer versions (open-source versions) of the latest SQL Server release can be downloaded from the official Microsoft website. The link is given below for reference.
https://www.microsoft.com/en-in/sql-server/sql-server-downloads

Q 23. How to connect SQL Server management studio to the local database.
Launch the SQL Server Management Studio from the START menu.
In the dialog box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system and click on the Connect button.
Select the Authentication as 'Windows Authentication.
A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.

Q 24. How to change column name in SQL Server.
From the Object Explorer window pane, go to the table in which the column is present and choose Design. Under the Column Name, select the name you want to rename and enter the new name. Go to the File menu and click Save.

Q 25. How to download SQL Server 2014.
Both the Express and Developer versions (free editions) of SQL Server can be downloaded from the official Microsoft website. The link is given below for reference.
Click on the link below : https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and type in - SQL Server 2014 download
Click on the result link to download and save SQL Server 2014.

Q 26. How to uninstall SQL Server 2014.
From the START menu, type SQL Server. Right-click on the app and select uninstall to uninstall the application from your system. Restart the system, if required, for the changes to get affected. 

Q 27. How to find server name in SQL Server.
Run the query SELECT @@version; to find the version and name of the SQL Server you are using. 

Q 28. How to start SQL Server.
Launch the SQL Server Management Studio from the START menu. Login using Windows Authentication. In the Object Explorer window pane, you can view the list of databases and corresponding objects. 

Q 29. What is the case when in SQL Server?
Case When statements in SQL are used to run through many conditions and to return a value when one such condition is met. If none of the conditions is met in the When statements, then the value mentioned in the Else statement is returned. 

Syntax:

CASE
WHEN CONDITION1 THEN RESULT1

WHEN CONDITION2 THEN RESULT2

ELSE
RESULT
END;

Sample query:

HOW MANY HEAD OFFICES/ BRANCHES ARE THERE IN CANADA

select 
sum ( 
case 
when region_id >=  5 AND region_id <= 7 then  

1
else 
0
end ) as Canada
from company_regions;
Nested CASE statement:
SELECT
SUM (
CASE
WHEN rental_rate = 0.99 THEN

1
ELSE
0
END
) AS 'Mass',
SUM (
CASE
WHEN rental_rate = 2.99 THEN

1
ELSE
0
END
) AS 'Economic',
SUM (
CASE
WHEN rental_rate = 4.99 THEN

1
ELSE
0
END
) AS ' Luxury'
FROM
film;

Q 30. How to install SQL Server management studio.
Launch Google and in the Search toolbar, type in SQL Server Management Studio' download. 

Go to the routed website and click on the link to download. Once the download is complete, open the .exe file to install the content of the file. Once the installation is complete, refresh or restart the system, as required.

Alternatively, once SQL Server is installed and launched, it will prompt the user with an option to launch SQ Server Management Studio. 

Q 31. How to write a stored procedure in SQL Server.
A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAME
AS
SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)
GO;
You can execute the Stored Proc by using the command Exec Procedure_Name;

Q 32. What is a trigger in SQL Server?
Triggers in SQL Server are automatic functions that get executed AFTER or BEFORE an event occurs. 

For example: Trigger to update the employee's salary to $40000 AFTER getting a promotion to job level band D1. It is also referred to as a type of Stored Procedure. The type of trigger as AFTER or BEFORE is defined in the syntax of the trigger itself.

Q 33. How to open SQL Server.
Launch the SQL Server Management Studio from the START menu. Login using Windows Authentication. In the Object Explorer window pane, you can view the list of databases and corresponding objects. 

Q 34. How to connect SQL Server.
The most common way of connecting to a SQL Server is using Windows Authentication. 

Make sure to select the Server Name as the desktop or laptop system name. 

Q 35. What is replication in SQL Server?
Replication of a database node is the most common way to prevent the complete loss of any data. When a database is replicated/ taken a copy of, it can be used across databases for data reuse and synchronization. Apart from the primary motive of data backup, replicated data is also used for data analysis in Big Data projects. 

Q 36. How to open SQL Server configuration manager.
Click on the START menu and select All Programs. Select Microsoft SQL Server, select Configuration Tools, and then select SQL Server Configuration tools. In that, select the SQL Server Configuration Manager.

Q 37. What is a collation in SQL Server?
Collation refers to a set of pre-defined rules on SQL Server, which define the encoding rules of character data both at a database and server level. Collation rules can be used on Metadata as well.

Q 38. How to use SQL Server.
SQL Server is used to retrieve and process various data that is built on a relational model.

Some of the common actions that can be taken on the data are CREATE, DELETE, INSERT, UPDATE, SELECT, REVOKE, etc.

SQL Server can also be used to import and export data from different data sources. SQL Server can also be connected to various other databases/ .Net framework using Connection Strings.

SQL Server can also be used in conjunction with Big Data tools like Hadoop. 

Q 39. What is a function in SQL Server?
Functions are pre-written codes that return a value and which help the user achieve a particular task concerning viewing, manipulating, and processing data.

Examples of few functions are:

AGGREGATE FUNCTIONS:

MIN()- Returns the minimum value
MAX()- Retuns the maximum value
AVG()- Returns the average value
COUNT()

STRING FUNCTIONS:

COALESCE()
CAST()
CONCAT()
SUBSTRING()

DATE FUNCTIONS:

GETDATE()
DATEADD()
DATEDIFF()

There are many types of functions such as Aggregate Functions, Date Functions, String Functions, Mathematical functions, etc.

Q 40. How to find nth highest salary in SQL Server without using a subquery.

Query to find the 10 highest salary. For up-gradation of the b10 band.

with result as 

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result.salary from result where result.salaryrank = 10

Query to find the 2nd highest salary

with the result as 

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result.salary from result where result.salaryrank = 2

In this way, by replacing the salaryrank value, we can find the nth highest salary in any organisation.

Q 41. How to install SQL Server in Windows 10.
Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and type in - SQL Server 2012 download
Click on the result link to download and save SQL Server 2012.
Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
Click on the Download Now button.
Save the .exe file on your system. Right-click on the .exe file and click on Open.
Click on 'Yes' to allow the changes to be made on your system and have SQL Server Installed

Q 42. How to create a temp table in SQL Server.
Temporary tables can be used to retain the structure and a subset of data from the original table from which they were derived. 

Syntax:

SELECT COL1, COL2
INTO TEMPTABLE1
FROM ORIGTABLE;
Temporary tables do not occupy any physical memory and can be used to retrieve data faster.

Q 43. What is schema in SQL Server?
A schema is a logical visual representation of the database. It establishes and defines the relationship between the various entities of a database. It describes the type of Constraints that are applied to a database. It also explains the data types that are used. It can be used on Tables and Views as well. 

There are different types of schema. Some of the most popular ones are Star schema and Snowflake schema. Star schema is the one that has the entities represented in a star shape, and snowflake schema has its entities represented in a snowflake shape.

Schemas form the basis of any database design.