Structured Query Language (SQL) is a domain-specific language used to communicate with the Relational Database Management System (RDBMS) and manipulate data stored in the database. With RDBMS, we store data in the database in the form of tables. Moreover, a table holds data entries in the form of rows and columns.
In 1986, American National Standards Institute (ANSI) and then, in 1987, International Organization for Standardization (ISO) announced SQL as a standard language for relational database management systems. All systems that utilize E. F. Codd’s relational model are relational database management systems. Some typical examples of databases that use the relational model are Oracle, Microsoft SQL Server, Sybase, and Ingres.
In this article, we shall discuss briefly what Microsoft SQL Server is and what career opportunities it has to offer. Further, we will discuss some popular SQL Server interview questions and answers:
Microsoft SQL Server: A Brief Introduction
Developed by Microsoft, the Microsoft SQL Server is a powerful and industry-recognized relational database management system. It is a robust RDBMS that enables applications running on the same computer or a remote computer to store and retrieve data. In addition, this software product is compatible to work with Microsoft Windows Server, Linux, and Microsoft Windows operating systems.
A wide range of Microsoft SQL Server editions is available that aims to meet the varying needs of different users. The Microsoft SQL Server editions are available across three categories – Mainstream, Specialized, and Discontinued.
The Mainstream category includes Standard, Enterprise, Business Intelligence, Web, Express, and Workgroup editions. On the other hand, Azure, Compact, Evaluation, Developer LocalDB, Fast track, Data Warehouse Application, and Analytics Platform System are the editions that belong to the Specialized category. Finally, the Discontinued category consists of Datacenter, MSDE, and Personal editions.
Microsoft SQL Server supports a standardized language called SQL. We can use Microsoft SQL Server to create and maintain databases, analyze data via SQL Server Analysis Services, perform ETL operations using SQL Server Integration Services, and develop reports through SQL Server Reporting Services.
Ranked among the most reliable and safest database technologies, both individuals and organizations trust Microsoft SQL Server to store, maintain and retrieve data. In addition, organizations and businesses hire SQL Server professionals to manage their data operations.
You can find a wide range of career opportunities in Microsoft SQL Server, as it is among the top three globally utilized database technologies. Some popular job roles associated with Microsoft SQL Server are Database Administrator, SQL Server Writer, BI Developer, Data Scientist, ETL Developer, Data Analyst, Big Data Architect, and many more.
Best SQL Server Interview Questions and Answers
Pursuing a career in SQL Server requires you to possess at least a Bachelor’s Degree in Computer Science. In addition, it would be a plus point to hold certification in SQL Server.
If you are a fresher, an interviewer may ask questions on fundamental topics of SQL Server. Also, the level of questions asked by interviewers depends on your level of experience. Below are some commonly asked SQL Server interview questions that you need to know to excel in your interview.
Question: What is SQL Server?
Answer: SQL Server is one of the leading relational database management systems developed by Microsoft. It enables other applications running on the same computer as the SQL Server or other remote computers to store and retrieve data from a relational database.
Question: List and explain types of commands used in SQL Server.
Answer: SQL Server supports four different types of commands, as given below:
- Data Definition Language (DDL):
We use DDL commands in SQL Server to deal with the structure of entities or objects. There are three DDL commands in SQL Server:
- Create: This command creates an object or entity.
- Alter: It makes changes in an object or entity.
- Drop: It deletes an object or entity.
Here, an object or an entity is a database, trigger, table, view, etc.
- Data Manipulation Language (DML):
DML commands in SQL Server are used to perform manipulations on the data stored in entities, like a table, view, etc. The three DML commands in SQL Server are:
- Insert: It adds new data into a table. Also, this command supports the insertion of bulk data into a table.
- Delete: This command deletes the specific data from the table.
- Update: It updates the values of data in a table.
- Data Control Language (DCL):
In SQL Server, we use DCL commands to ensure the security of the data stored in objects or entities. Two DCL commands in SQL Server are:
- Grant: It grants database access permission to a user.
- Revoke: This command takes back the database access permission from a user.
- Transactional Control Language (TCL):
We use TCL commands in SQL Server to manage transactions. Three TCL commands in SQL Server are:
- Commit: It saves a transaction in SQL Server permanently.
- Rollback: This TCL command undoes all changes made to the data in a database made before committing.
- Save Tran: It saves a transaction in SQL Server and rolls it back to the desired point.
Question: Explain the method of creating a database in SQL Server.
Answer: In computer programming, a database is a well-organized collection of data. Alternatively, a database is a set of various components used for storing data, like tables, schemas, procedures, etc. Furthermore, Structured Query Language (SQL) is used in Microsoft SQL Server to access the data and perform manipulations.
We use the CREATE DATABASE command to create a database in the SQL Server. The syntax for creating a database in the SQL Server is:
|CREATE DATABASE DatabaseName;|
Example: Consider we need to create a database having the name Students, so we need to run the following command:
|CREATE DATABASE Students;|
Alternatively, we can use the SQL Server Management Studio to create a database; go to the left side of the window, right-click on the Databases option and then click on the New Database option.
Question: What is SQL?
Answer: SQL stands for Structured Query Language. All relational database management systems use this domain-specific language to access and manipulate the stored data.
It enables us to perform various tasks, such as creating a database, retrieving data from a database, creating records, tables, views, and procedures in a database, etc. All these operations make use of SQL queries.
Question: Explain PL/SQL.
Answer: An extension to SQL is PL/SQL. It stands for Procedural Language extensions to Structured Query Language. PL/SQL combines the SQL’s data manipulation feature and the procedural language’s processing power. Moreover, it is one of the most powerful programming languages that improve a database’s security and sturdiness.
PL/SQL instructs the Oracle compiler about ‘what actions to perform’ using the SQL’s manipulation feature and ‘how to perform actions’ using the procedural language’s processing power. As PL/SQL is a procedural language, it involves looping and conditional statements.
Question: Differentiate between SQL and PL/SQL.
Answer: The below table highlights some of the key dissimilarities between SQL and PL/SQL:
|It stands for Structured Query Language.||It stands for Procedural Language extensions to Structured Query Language.|
|SQL executes a single query or line of code at a time.||PL/SQL executes a block of code or multiple lines in one go.|
|It uses DDL and DML commands to develop SQL queries and commands.||It uses functions, variables, procedures, triggers, and packages to develop blocks of code.|
|We use SQL queries to fetch data, modify it, or delete it from a database.||PLSQL is used to develop applications that display information retrieved using SQL queries.|
|SQL queries do not support the use of PL/SQL syntax.||The PL/SQL syntax allows the use of SQL queries.|
Question: Explain a Relational Database Management System (RDBMS).
Answer: A database management system based on E. F. Codd’s relational model is called a Relational Database Management System (RDBMS). Alternatively, RDBMS is a collection of programs and functionalities that enables organizations and individuals to interact or communicate with a relational database.
Data in RDBMS is stored in the form of tuples, i.e., rows and columns. It supports a Structured Query Language (SQL) to access data from a relational database. Some vital characteristics of RDBMS are:
- It allows multiple users to access data.
- RDBMS is a robust type of database model that can handle every data size, whether small, medium, or large-scale.
- It uses the ACID property to ensure data consistency in a database.
- RDBMS supports distributed databases and normalization.
Question: List out properties of RDBMS.
Answer: The below list explains the properties of RDBMS:
- All values in a table are atomic.
- Each tuple in a table is unique.
- The sequence of attributes and tuples does not matter in SQL Server.
- Each attribute in a table should have a unique name.
- All field values in a single column are of the same type.
Question: What do you understand about a database table?
Answer: A database table in SQL Server stores all data in the form of horizontal rows and vertical and columns. In addition, a cell in a table is a unit where a row and a column intersects. A database table has a specific number of columns or attributes, whereas it may have any number of rows or tuples.
We use DDL commands for a database table, as a table is an object. The CREATE command creates a database table, whereas the DROP command deletes it. Moreover, the ALTER command modifies the previously defined structure of a table, and the SELECT command displays data from a table.
Question: Tell the syntax for creating a table in SQL Server with an example.
Answer: The syntax for creating a table in SQL Server is:
|CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype, …., ColumnNameN datatype);|
Let us see one example of creating a Students table in SQL Server. Consider that the Students table has four columns or attributes, like Name, Student_ID, Mobile_No, and City.
|CREATE TABLE Students ( Name varchar(20), Student_ID int, Mobile_no int, City varchar (20));|
After you hit enter, you will get a message ‘Table created successfully.’
Question: Write down the syntax for deleting a table in SQL Server.
Answer: To delete a table from a database in SQL Server, we use the DROP command. The syntax of the DROP command is:
|DROP TABLE TableName;|
Let us consider that we have a table named Employees, and we need to delete it from a database.
|DROP TABLE Employees;|
Question: Tell the syntax for updating a table in the SQL Server.
Answer: In SQL Server, we use the ALTER command to update a table. While creating a table, we specify column names and their corresponding data types. Also, if we want to add, delete, or alter any column after creating a table, we can again use the ALTER command. Therefore, the ALTER command is used in three different ways, as follows:
- To add a column:
|ALTER TABLE table_name
ADD column_name datatype;
- To delete a column:
ALTER TABLE table_name
DROP COLUMN column_name;
- To modify a column:
|ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Question: Explain different kinds of relationships in SQL Server.
Answer: SQL Server supports three kinds of relationships in a database, as explained below:
The one-to-one relation in SQL Server has rare usage. In this type of relationship, a record or data in one table correlates with only one record or data present in another table. The primary advantage of using one-to-one relationships is better security.
For example, consider a Student database. Here, only one Student_ID is assigned to a single student. Conversely, each student has a unique ID assigned.
- One-to-many and Many-to-one:
When one record in a table correlates with multiple records in another table, it is a one-to-many relationship. Conversely, when multiple records in a table are associated with a single record in another table, it is a many-to-one relationship.
For example, consider a Customer database. Here, a single customer can place any number of orders. On the other hand, a specific order belongs to a single customer.
When multiple records in a table are correlated to multiple records in another table, it is a many-to-many relationship.
For example, multiple customers can purchase different products.
Question: Explain the primary key in SQL Server.
Answer: A primary key in SQL Server can be a single attribute or field or combination of attributes or fields that uniquely identify a record or a tuple in a table. There is only one primary key for a database table. In addition, we can set a primary key for a table while creating or updating it. However, fields of an attribute or a combination of attributes that are considered a primary key cannot be NULL.
Below is the syntax for creating a primary key for a table:
|CREATE TABLE table_name
columnName1 datatype [NULL | NOT NULL],
columnName2 datatype [NULL | NOT NULL],
CONSTRAINTS constraint_name PRIMARY KEY (pk_col1, pk_col2, …,pk_col_n));
Question: What is a foreign key?
Answer: We use a foreign key in SQL Server to connect data between two different tables; one table will be a child, and another will be a parent. Alternatively, if an attribute or column of one table points to the primary key of another table, then that attribute or column is said to be a foreign key.
Let us go through one example to understand clearly about the foreign key.
Consider two tables: Student_record and Student_Marks.
In the above tables, Student_ID is the foreign key. The Student_ID attribute is a primary key of the table named Student_record and is present as an attribute in Student_marks.
Question: Name the TCP/IP Port on which SQL Server runs.
Answer: The SQL Server runs on the 1433 TCP/IP port by default.
Question: Explain database normalization and list its forms.
Answer: The technique used to organize data present in a database to reduce data redundancy is called database normalization. In addition, database normalization eliminates Update, Insertion, and Deletion Anomalies from a relation.
The fundamental idea of using the database normalization technique is to divide a table into smaller ones and connect them using database relationships.
There are six forms of database normalization, as listed below:
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF or 4NF)
- Fifth Normal Form (5NF)
- Fifth Normal Form (6NF)
Question: Explain functions in SQL Server.
Answer: A function in SQL Server is analogous to a function in programming languages. It is a sequence of SQL statements written to accomplish a definite task. In addition, a function in SQL Server accepts input parameters and returns the desired output.
More importantly, SQL functions are not used to insert, update, or delete data from a database. Every function in SQL Server has a specific name and does not begin with special symbols, such as $, #, or @.
SQL Server has two distinct kinds of SQL Server: predefined and user-defined.
- Predefined Functions:
Functions defined by SQL Server are called pre-defined functions. Aggregate functions and scalar functions are two different kinds of predefined functions in SQL Server.
Aggregate functions are min(), sum(), count(), max(), and avg(), whereas scalar functions are round(), ucase(), lcase(), format(), mid(), len(), and now().
- User-defined Functions:
A block of SQL statements written by a programmer or developer to accomplish a particular task is called a user-defined function.
Question: Explain Aggregate functions in SQL Server.
Answer: Aggregate functions in SQL Server group values from multiple rows of a table under specific criteria and return a single value. There are five aggregate functions in SQL Server, as given below:
It takes all values from different rows and returns their sum value.
This function accepts values from multiple tuples, compares them, and returns the maximum value.
This function accepts values from multiple tuples, compares them, and returns the minimum value.
This function returns the total number of rows or records present in a table.
This function accepts values from multiple tuples and returns an average of all values.
Question: List out and explain Scalar functions in SQL Server.
Answer: Functions that accept a single value and also produce a single value output are called scalar functions. Below are some scalar functions used in SQL Server:
We use the mid() function to retrieve a substring from an input string.
This function returns the uppercase of the given input.
The lcase() function returns the lowercase of the given input.
This function returns the input value in a specific format.
The len() function returns the length of the given input.
This function rounds off the given numerical input up to three decimal places.
We use the now() function to fetch the system’s current time and date.
Question: What is a stored procedure in SQL Server?
Answer: A collection of T-SQL statements grouped together as a single logical unit is called a stored procedure in SQL Server. We can store stored procedures in a database and use them whenever required. If a certain program requires a code block, again and again, we can store that code block as a stored procedure.
The syntax for creating a stored procedure in SQL Server is:
|CREATE PROCEDURE procedure_name
To execute a created stored procedure, use the below syntax:
Question: What do you mean by the trigger?
Answer: A trigger in SQL Server is analogous to a stored procedure. Alternatively, we can call a trigger a database object. When an event takes place in a database, a trigger is fired. However, we cannot fire or activate a trigger in SQL Server. It gets invoked automatically.
In other words, we can use a trigger in SQL Server to make an event happen in a database automatically. DML triggers and DDL triggers are two kinds of triggers in SQL Server.
- DML Triggers:
When we use any DML command like INSERT, UPDATE, and DELETE on a table’s or a view’s data, DML triggers are fired. These triggers help preserve a database’s consistency and integrity.
- DDL Triggers:
DDL triggers are raised when several DDL events take place. These events are associated with Transact-SQL statements that begin with CREATE, DENY, REVOKE, ALTER, GRANT, or DROP.
Question: Explain a view in a database.
Answer: A view in SQL Server is a virtual table consisting of the data from multiple tables. In addition, a view also stores data in rows and columns. However, a view is not present physically in a database as a stored procedure. Therefore, it does not require storage in a database. Like a database table, a view should also have a unique and unrepeated name.
The syntax for creating a view in SQL Server is:
|CREATE VIEW view_name AS
SELECT Column1, Column2, …, ColumnN FROM tables
Question: What is an index in SQL Server?
Answer: In SQL Server, an index is used to fetch row data quickly from a database’s view or table. It is an on-disk structure linked to a view or a table in a database. In addition, there are keys in an index developed using one or more attributes or columns of a view or a table. A structure called B-Tree stores index keys, enabling SQL Server to retrieve corresponding rows rapidly and efficiently.
The two different kinds of indexes in SQL Server are:
- Clustered Index:
A clustered index in SQL Server represents the order in which the data in the database should be stored physically. However, data in a database table can be sorted in only one way. Therefore, there is only one clustered index for a single table. Moreover, a clustered index is created automatically when we declare a primary key constraint on a table’s attribute.
- Non-Clustered Index:
Indexes that do not sort physical data within a database are non-clustered indexes. More importantly, the table data and non-clustered index are not present in one place.
Question: Explain the difference between clustered and non-clustered indexes.
Answer: The below table shows dissimilarities between clustered and non-clustered indexes:
|Clustered Index||Non-Clustered Index|
|This type of index is quicker, and clustered index operations require less memory space.||It is relatively slower than the clustered one, and non-clustered index operations consume a lot of memory.|
|An index is original data.||An index is a copy of data.|
|A table in SQL Server has only one clustered index.||A table can have several non-clustered indexes.|
|The table data in the clustered index is stored in an index’s lead nodes.||A non-clustered index does not store the table data in lead nodes.|
Question: List out SQL Server’s two authentication modes.
Answer: Two authentication modes in SQL Server are Mixed Mode and Windows Mode.
Question: Define global and local temporary tables in SQL Server.
Answer: Global Temporary Table:
A table that is noticeable to all users when the connection is established and gets deleted when the connection is closed is called a global temporary table.
CREATE TABLE ##<tablename>
A table that is visible to only the creator of the connection and gets deleted when the connection is closed is called a local temporary table.Local Temporary Table:
|CREATE TABLE #<tablename>|
Question: Tell the query used to get triggers’ list in a database.
Answer: Query to receive the list of triggers is:
|SELECT * FROM sys.objects WHERE type = ‘tr’;|
Question: Which query will you use to get the SQL Server’s current version?
Answer: The SQL query to fetch SQL Server’s current version is:
Question: Write a query to fetch the total count of records present in a table.
Answer: We can retrieve the total number of records in a table by using the following SQL query:
|SELECT * FROM <tablename>
SELECT count(*) <tablename>
SELECT rows from sysindexes
WHERE id=OBJECT_ID(tablename) and intid<2
Though SQL Server is one of the oldest RDBMS, it is likely to thrive in the future as well. Therefore, pursuing a career in the SQL domain offers many career opportunities in various domains such as Finance, Healthcare, and IT.
If you wish to shape your career in SQL and aim to appear for an interview, this article will surely assist you. We have listed the most common SQL Server interview questions and answers. You can be more confident about what to answer if an interviewer asks any of the above questions related to SQL Server.
People are also reading: