Programs

Top SQL Server Interview Questions & Answers [For Freshers 2021]

Let’s look at some of the frequently asked questions in an SQL Server-based interview. We will also include examples of SQL queries along with the SQL interview questions and answers wherever necessary. 

SQL Server Interview Questions & Answers

Question 1: What is SQL?

SQL or Structured Query Language is the standard computer programming language used to manage and organize Relational Database Management Systems (RDBMS). It is used for storing, manipulating and gaining access to stored data, in other words, communicating with relational databases. Most popular RDBMSs make use of SQL and this includes SQL Server, MySQL  and Oracle. 

Any unit of execution or statement in SQL is known as a query. An SQL query can be used to create, select or modify data.

In Relational Database Management Systems, an organised file of data is stored in the form of tables. Each table consists of columns and rows.

Question 2: What is the SQL query to create a database in SQL Server?

 A database in SQL is an organised collection of data which may consist of tables, code functions, schemas, etc. A programmer can create this database or manipulate it using various query languages. 

To create a new database in the SQL server, the following SQL command is used:

CREATE DATABASE DatabaseName

Read: Full Stack Interview Questions

Question 3: How is a table created in SQL?

A table is an object in SQL that allows users to store and retrieve data. This data is stored in a tabular and each table consists of columns and rows. 

To create a new table in the SQL server, the following SQL command is used:

Create table TableName (columnName1 datatype, columnName2 datatype )

Question 4: What Is an SQL Profiler?

SQL Profiler is a means for a system administrator to keep track of the events in the SQL server. It’s mainly used for the analysis of the captured and saved data files of an event. 

Question 5: Explain with an example what a recursive stored procedure is.

Recursive stored procedures in SQL Server needn’t be called. It calls by itself until a boundary condition is reached. A maximum nesting level of 32 is possible with stored procedures. This is known as recursion. 

Using the recursive stored procedure, a programmer can use the same batch of code for n times. 

To cite an example, if you need to expand a tree relationship or compute the factorial algorithm, you can use reversion in stored procedures. Here is an example on how to calculate the factorial of a number. 

CREATE PROCEDURE [dbo].[Factorial_ap]

(

    @Number Integer,

    @RetVal Integer OUTPUT

)

AS

    DECLARE @In Integer

    DECLARE @Out Integer

    IF @Number != 1

        BEGIN

        SELECT @In = @Number – 1

        EXEC Factorial_ap @In, @Out OUTPUT

        SELECT @RetVal = @Number * @Out

    END

        ELSE

            BEGIN

                SELECT @RetVal = 1

            END

RETURN

GO

Question 6: What List the differences between local and global temporary tables. 

The visibility of local temporary tables lasts as long as there is a connection. Once the connection is closed, the server automatically deletes these tables.

Local temporary tables are denoted by # before the table name.

The syntax to create a local temporary table is:

CREATE TABLE #<tablename>

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  …

);

Global temporary tables are accessible and visible to all users. SQL Server deletes them once the SQL Server session ends. This happens when every user referencing table has disconnected from the session.

Local temporary tables are denoted using ## before the table name

The syntax to create a global temporary table is

CREATE TABLE ##<tablename>

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  …

);

Question 7: Explain pattern matching in SQL?

Pattern Matching in SQL allows programmers to use the underscore sign (_) for the purpose of matching a single character. It also enables you to use the percentage sign (%) for matching an arbitrary number of characters. This also includes any zero characters. It should be noted that SQL patterns are case-insensitive in MySQL. 

Question 8: What is a Trigger? How many types of triggers are there? 

Triggers are a special kind of stored procedure that are used to check a batch of SQL code. They are executed or “triggered” automatically when a table’s data is modified.

There are two types of Triggers. They are: 

  1. Data Manipulation Language (DML) and
  2. Data Definition Language (DDL)

When either of these queries are used to modify data, a set of commands are triggered. With DML command events, Insert, Delete, Update and Instead of are fired up. With DDL, the triggers Create, Alter and Drop are triggered. 

Also Read: PHP Interview Questions & Answers

Question 9: Define COALESCE?

The first non-null expression within a function’s arguments is returned using COALESCE. It can read one or more columns in its arguments to check for a non-null expression.

The syntax is –

Select COALESCE (emp_num, emp_name, salary) from employee;

Question 10. What is CDC?

A feature of SQL Server 2008, the CDC or Change Data Capture can be used to capture the data that has been recently modified.

Question 11: What are the queries used to get the count of the number of records in a SQL table?

We can get the count of records in a table using the following queries:

Select * from <tablename> 

Select count(*) from <tablename> 

Select rows from sysindexes where id=OB

JECT_ID(tablename) and indid<2

Question 12: State the difference between SUBSTR and CHARINDEX functions in the SQL Server?

The SUBSTR function returns the specified portion of a string as instructed by the programmer. The CHARINDEX function, on the other hand, is used to return the position of a specified  character in a given string.

For example:

SUBSTRING(‘Apple’,1,4)

– The output will be Appl

CHARINDEX(‘l’, ‘Apple’,1)

– The output will be 4 since the character l is in the 4th position in the specified string

Question 13: What is SQL injection?

SQL Injection is one of the vulnerabilities of the database where an SQL server is attacked by users using a malicious code that is inserted within the strings of a code. The intent is to send the malicious code parsing and execution. Even parameters are at risk of attack thus, all statements need to be reviewed for vulnerabilities. 

Question 14: What are the methods that can be used to  avoid an SQL injection attack?

The following four methods can be employed to protect an SQL server from an SQL injection attack: 

– Since Parameters are at a lower risk of an SQL injection attack, it is recommended to use them for Stored Procedures. 

– It is a good measure to filter input parameters. 

– Parameter collection with Dynamic SQL can be used. 

– Make use of escape characters in Like clauses. 

Question 15: State the two authentication modes in SQL Server. How can they be changed? 

The two authentication modes in SQL server are:

– Windows Mode

– Mixed Mode

In SQL Server’s configuration settings, under the Security Page, there is a tools menu to change the modes. 

Question 16: What are the different types of commands in an SQL database?

Answer: There are four types of commands in the SQL Server. They are categorized as: 

  1. Data Definition Language (DDL)
  2. Data Control Language Transaction Control Language (TCL)Transaction Control Language (TCL)
  3. Data Manipulation Language (DML)
  4. Transaction Control Language (TCL)

Conclusion 

We hope our SQL Server Questions and Answers guide is helpful. We will be updating the guide regularly to keep you updated.

If you’re interested to learn more about SQL, full stack development, check out upGrad & IIIT-B’s PG Diploma in Full-stack Software Development which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.

Become a Full Stack Developer

UPGRAD AND IIIT-BANGALORE'S PG DIPLOMA IN FULL STACK SOFTWARE DEVELOPMENT
Learn More

Leave a comment

Your email address will not be published.

Accelerate Your Career with upGrad

Our Popular Software Engineering Courses

×