MS SQL Interview Questions

There are talented aspirants who fail to grasp the finest job opportunities accessible in MS SQL field due to finding it difficult to answer MS SQL Interview Questions. Inspired from such diffident situations; a Aptron team of skilled MS SQL trainers, MS SQL professionals and MS SQL experts have drafted the below mentioned MS SQL Interview Questions to help candidates get the success during interview sessions. Able to provide the relevant answers to MS SQL interview questions build up the scope for various opportunities for a prosperous career. This article contains MS SQL interview questionnaire. The article has been build up to provide MS SQL interview questions and answers for freshers as well for experienced candidates. The questionnaire comprises of detailed answers casing basic and advanced MS SQL interview questions. The MS SQL Interview Questions is a sum-up of all verticals available in the MS SQL technology.

ms sql interview questions

About MS SQL

Microsoft SQL Server is a relational database management system. It is a software product developed by Microsoft. As a database server, it’s primary function is to store and retrieve data as requested by other software applications. The data server may run either on the same computer or on another computer across a network. Microsoft aims at different audiences and for workloads ranging from small single machine applications to large Internet-facing applications by marketing at least a dozen different editions of Microsoft SQL Server with many concurrent users.

MS SQL Interview Questions And Answers

1. What is SQL?

SQL stands for ‘Structured Query Language’ and is used for communicating with the databases. According to ANSI, SQL is the standard query language for Relational Database Management Systems (RDBMS) that is used for maintaining them and also for performing different operations of data manipulation on different types of data. Basically, it is a database language that is used for the creation and deletion of databases, and it can be used to fetch and modify the rows of a table and also for multiple other things

2. Explain the different types of SQL commands.

  • Data Definition Language: DDL is that part of SQL which defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are:
    • Create table
    • Alter table
    • Drop table
  • Data Manipulation Language: DML is used to manipulate the already existing data in the database. That is, it helps users retrieve and manipulate the data. It is used to perform operations like inserting data into the database through the insert command, updating the data with the update command, and deleting the data from the database through the delete command.
  • Data Control Language: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and also to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.
  • Transaction Control Language: It is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction into logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

3. What is a Primary Key?

A primary key is used to uniquely identify all table records. It cannot have NULL values, and it must contain unique values. A table can have only one primary key that consists of single or multiple fields.

Now, we will write a query for demonstrating the use of a primary key for the Employee table:

//
CREATE TABLE Employee (
ID int NOT NULL,
Employee_name varchar(255) NOT NULL,
Employee_designation varchar(255),
Employee_Age int,
PRIMARY KEY (ID)
);

4. State the differences between Clustered and Non-clustered indexes.

  • Clustered index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. You can open the book at ‘David’ (for ‘David, Thompson’) and find information for all Davids right next to each other. Since the data is located next to each other, it helps a lot in fetching data based on range-based queries. Also, the clustered index is actually related to how the data is stored. There is only one clustered index possible per table.
  • Non-clustered index: It stores data at one location and indexes at some other location. The index has pointers that point to the location of the data. As the index in the non-clustered index is stored in different places, there can be many non-clustered indexes for a table.
  • Now, we will see the major 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

5. What is COALESCE function?

COALESCE function takes a set of inputs and returns the first non-null value.

Syntax: COALESCE(val1,val2,val3,……,nth val)

Example: SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Output: 1

6. What do you understand by Normalization and Denormalization?

Normalization and denormalization are basically two methods used in databases.

Normalization is used in reducing data redundancy and dependency by organizing fields and tables of databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make it more flexible.

De-normalization is contrary to normalization. In this, we basically add redundant data to speed up complex queries involving multiple tables to join. Here, we attempt to optimize the read performance of a database by adding redundant data or by grouping the data.

7. What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are:

  • NOT NULL - Restricts NULL value from being inserted into a column.
  • CHECK - Verifies that all values in a field satisfy a condition.
  • DEFAULT - Automatically assigns a default value if no value has been specified for the field.
  • UNIQUE - Ensures unique values to be inserted into the field.
  • INDEX - Indexes a field providing faster retrieval of records.
  • PRIMARY KEY - Uniquely identifies each record in a table.
  • FOREIGN KEY - Ensures referential integrity for a record in another table.

8. What is a Primary Key?

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint. A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).

9. What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table.

10. What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

11. What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

12. What are Entities and Relationships?

Entity: An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity.

Relationships: Relations or links between entities that have something to do with each other. For example - The employees table in a company's database can be associated with the salary table in the same database.

13. List the different types of relationships in SQL.

  • One-to-One - This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.
  • One-to-Many & Many-to-One - This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.
  • Many-to-Many - This is used in cases when multiple instances on both sides are needed for defining a relationship.
  • Self Referencing Relationships - This is used when a table needs to define a relationship with itself.

14. What are the reporting services in SQL Server?

SQL Server Reporting Services is a comprehensive reporting platform that includes processing components. Processing components are the basis for the multilayered architecture of SQL Server Reporting Services. Processing components interact with each other to retrieve data and deliver a report.

SQL Server Reporting Services has the following two basic components.

  • Processors
  • Extensions

Architecture of SQL Server Reporting Services

The following diagram shows the architecture of SQL Server Reporting Services.

Architecture of SQL Server Reporting Services

Tools and Components of SQL Server Reporting Services architecture

This architecture consists mainly of the following types of components and tools.

  • Report Builder
  • Report Designer
  • Report Manager
  • Report Server
  • Report server database
  • Data sources

15. What are the integration services in SQL Server?

Integration Services is a platform for building high performance data integration and workflow solutions, including extraction, transformation and loading (ETL) operations for data warehousing.

This includes graphical tools and wizards for building and debugging packages.

Uses of Integration Services:

One use of Integration Services is to merge data from multiple data stores and update the data to data warehouses and/or data marts. Create the Data Transformation process logic and automate the data loading process.

Architecture of Integration Services

Architecture of Integration Services

Some important components to using Integration Services:

  • SSIS Designer
  • Runtime engine
  • Tasks and other executables
  • Data Flow engine and Data Flow components
  • API or object model
  • Integration Services Service
  • SQL Server Import and Export Wizard
  • Other tools, wizards and command prompt utilities

16. What do you mean by Collation?

Collation is defined as a set of rules that determine how data can be sorted as well as compared. Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc.

17. What are the different types of Collation Sensitivity?

Following are the different types of collation sensitivity:

  • Case Sensitivity: A and a and B and b.
  • Kana Sensitivity: Japanese Kana characters.
  • Width Sensitivity: Single byte character and double-byte character.
  • Accent Sensitivity.

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.

18. What are Local and Global variables?

Local variables:

These variables can be used or exist only inside the function. These variables are not used or referred by any other function.

Global variables:

These variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called.

19. What is Auto Increment in SQL?

Auto increment keyword allows the user to create a unique number to get generated whenever a new record is inserted into the table.

This keyword is usually required whenever PRIMARY KEY is used.

AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.

20. What is a Datawarehouse?

Datawarehouse refers to a central repository of data where the data is assembled from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing. Warehouse data also have a subset of data called Data Marts.

21. What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.

22. What are Views used for?

A view refers to a logical snapshot based on a table or another view. It is used for the following reasons:

  • Restricting access to data.
  • Making complex queries simple.
  • Ensuring data independence.
  • Providing different views of same data.

23. What is a Stored Procedure?

A Stored Procedure is a function which consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required which saves time and avoid writing code again and again.

24. What is CHECK constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

25. Write a Query to display employee details whose salary > 20000 and whose age >23?

  1. SELECT * FROM Employee
  2. WHERE Salary>20000 AND Age>23;

26. Write a Query to display employee details whose salary >20000 and who is working in ECE department?

  1. SELECT * FROM Employee
  2. WHERE Salary>20000 AND Dept_Name=’ECE’

27. Write a Query to display employee details whose age is BETWEEN 18 and 22?

  1. SELECT * FROM Employee Details
  2. WHERE Age BETWEEN 18 AND 22;

28. Write a Query to display employee details whose salary range BETWEEN 20000 and 23000?

  1. SELECT * FROM Employee
  2. WHERE Salary BETWEEN 20000 AND 23000;

29. Write a Query to display employee details whose age is NOT BETWEEN 18 & 22?

  1. SELECT * FROM Employee
  2. WHERE Age NOT BETWEEN 18 AND 22;

Using String Operators:: LIKE, NOT LIKE

30. Write a Query to display employee details whose name starts with a?

  1. SELECT * FROM Employee
  2. WHERE Emp_Name LIKE ‘a%’

a% ----> starts with a
%a ----> ends with a

31. Write a Query to display employee details and whose age>20 & whose name starts with a?

  1. SELECT * FROM Employee
  2. WHERE Salary>20000 AND Age>20 AND Emp_Name LIKE ‘a%’

32. Write a Query to display employee details whose name not starts with a?

  1. SELECT * FROM employee
  2. WHERE Emp_Name NOT LIKE ‘a%’

Career scopes and salary scale

MS SQL, Machine Learning, Software programming, etc are the areas requiring talented professionals to handle their upcoming projects. Entering to the arena now will empower your career with high salary and position. MS SQL employees having in-depth knowledge and skill-based training in MS SQL are able realize their smooth ways to accomplish their career goals. A candidate of MS SQL applicant gets a minimum salary of 40, 000 dollars per annum, which get to double with experience. However, the salaries are very dependent upon the location, business, and the company’s requirements.

Conclusion

The questionnaire on MS SQL interview questions has been drafted by team of specialists to train fresh candidates in understanding the tricks and techniques to answer interview questions. Often, interviewers ask a simple question making it more elaborative and confusing for the candidates. This article has tried providing relevant answers to entire advanced MS SQL interview questions. It also has answers for MS SQL interview questions for experienced professionals. If any students still require more explanation, then they may drop in a message to our experts regarding MS SQL interview questions for experienced professionals. Join MS SQL Training in Noida, MS SQL Training in Delhi, MS SQL Training in Gurgaon

Enquiry Now
Quick Enquiry Form

APTRON Noida

B-10, SECTOR-2
NEAR SECTOR - 15 METRO STATION
NOIDA - 201301, U.P. (INDIA)
Contact Number : +91-706-527-3000

APTRON Delhi

Bhikaji Cama Place New Delhi,
Delhi
110070
Contact Number : +91-706-527-1000

APTRON Gurgaon

OLD DLF COLONY
Gurgaon,
122001

Navigation