30+ SQL Server Interview Questions 2023 | HTML KICK

Top 30 SQL Server Interview Questions

Are you getting ready for your interview with a sql developer?

Then you’ve arrived to the right location.

This tutorial will assist you in brushing up on your SQL skills, regaining confidence, and preparing for a career.

This guide includes a collection of real-world SQL Server interview Questions from organizations such as Google, Oracle, Amazon, and Microsoft, among others. Each question has a neatly prepared answer right next to it, saving you time while you prepare for your interview.

This guide also includes several SQL practice problems that you may complete right now, which is the quickest and most convenient approach to clear your basic SQL principles.

Basic SQL Server Interview Questions

What exactly is a database?

A database is a digitally stored and retrieved collection of data from a remote or local computer system. Databases can be large and sophisticated, and they are built using pre-determined design and modelling methodologies.

What is a database management system (DBMS)?

The term DBMS refers to a database management system. A database management system (DBMS) is a piece of software that allows you to create, retrieve, update, and manage databases. It serves as an interface between the database and its end users or application software’s, ensuring that our data is consistent, organized, and easily accessible.

What is a relational database management system (RDBMS)? What distinguishes it from a database management system (DBMS)?

SQL Server Interview Questions : Relational Database Management System (RDBMS) is an acronym for Relational Database Management System. The main distinction between RDBMS and DBMS is that RDBMS stores data in the form of a collection of tables, with relations specified between the tables’ common fields. RDBMS is the foundation of most modern database management systems, including MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift.

What exactly is SQL?

Structured Query Language (SQL) is an acronym for Structured Query Language. It’s the industry standard for database management systems that use relational databases. It’s notably handy for dealing with structured data made up of entities (variables) and relationships between them.

How do SQL and MySQL differ from one another?

SQL is a programming language that is used to retrieve and manipulate organised databases. MySQL, on the other hand, is a relational database management system used to manage SQL databases, similar to SQL Server, Oracle, or IBM DB2.

What are the differences between tables and fields?

A table is a collection of data that is structured into rows and columns. Vertical columns and horizontal rows are the two types of columns and rows, respectively. A table’s columns are known as fields, while the rows are known as records.

What are SQL Constraints?

Constraints are used to define the rules that govern the table’s data. It can be used on single or multiple fields in a SQL table when the table is created or after it has been created using the ALTER TABLE command. The following are the limitations:

NOT NULL – Prevents the insertion of a NULL value into a column.

CHECK – Checks if all values in a field meet a set of criteria.

DEFAULT – If no value has been specified for the field, it will be assigned a default value.

UNIQUE – Ensures that the field’s values are unique.

INDEX – Indexes a field to allow for faster record retrieval.

PRIMARY KEY – Identifies each record in a table in a unique way.

A FOREIGN KEY – ensures the referential integrity of a record in a different table.

What is the definition of a primary key?

Each row in a table is uniquely identified by the PRIMARY KEY constraint. It contains an implied NOT NULL constraint and must include UNIQUE data.

In SQL, a table can only have one and only one primary key, which can be made up of single or multiple fields (columns).

CREATE TABLE Students ( 	 /* Create table with a single field as primary key */
    ID INT NOT NULL
    Name VARCHAR(255)
    PRIMARY KEY (ID)
);

CREATE TABLE Students ( 	 /* Create table with multiple fields as primary key */
    ID INT NOT NULL
    LastName VARCHAR(255)
    FirstName VARCHAR(255) NOT NULL,
    CONSTRAINT PK_Student
    PRIMARY KEY (ID, FirstName)
);

ALTER TABLE Students 	 /* Set a column as primary key */
ADD PRIMARY KEY (ID);

ALTER TABLE Students 	 /* Set multiple columns as primary key */
ADD CONSTRAINT PK_Student 	 /*Naming a Primary Key*/
PRIMARY KEY (ID, FirstName);

What does it mean to have a UNIQUE constraint?

A UNIQUE constraint guarantees that each value in a column is unique. This ensures that the column(s) are unique and that each row can be identified separately. Unlike primary keys, each table can have several unique constraints. UNIQUE has a code syntax that is very similar to PRIMARY KEY and can be used interchangeably.

CREATE TABLE Students ( 	 /* Create table with a single field as unique */
    ID INT NOT NULL UNIQUE
    Name VARCHAR(255)
);

CREATE TABLE Students ( 	 /* Create table with multiple fields as unique */
    ID INT NOT NULL
    LastName VARCHAR(255)
    FirstName VARCHAR(255) NOT NULL
    CONSTRAINT PK_Student
    UNIQUE (ID, FirstName)
);

ALTER TABLE Students 	 /* Set a column as unique */
ADD UNIQUE (ID);

ALTER TABLE Students 	 /* Set multiple columns as unique */
ADD CONSTRAINT PK_Student 	 /* Naming a unique constraint */
UNIQUE (ID, FirstName);

What is a Foreign Key, exactly?

A FOREIGN KEY is a single or group of fields in a table that refers to another table’s PRIMARY KEY. The foreign key constraint assures that the relationship between two tables is referentially sound.

The table with the candidate key is referred to as the referenced or parent table, while the table with the foreign key restriction is referred to as the child table.

CREATE TABLE Students ( 	 /* Create table with foreign key - Way 1 */
    ID INT NOT NULL
    Name VARCHAR(255)
    LibraryID INT
    PRIMARY KEY (ID)
    FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);

CREATE TABLE Students ( 	 /* Create table with foreign key - Way 2 */
    ID INT NOT NULL PRIMARY KEY
    Name VARCHAR(255)
    LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);


ALTER TABLE Students 	 /* Add a new foreign key */
ADD FOREIGN KEY (LibraryID)
REFERENCES Library (LibraryID);

Intermediate Level SQL Server Interview Questions

What is the procedure for creating a database in SQL Server?

A database is a collection of data that has been arranged. It’s made up of schemas, tables, procedures, code functions, and other items. To access and change data, a variety of query languages are employed. A table is an object in SQL Server that stores data in a tabular (columns and rows) format.

The SQL command CREATE DATABASE can be used to create a new database.

Syntax: CREATE DATABASE DatabaseName

Example: CREATE DATABASE Student

You can also use SQL Server Management Studio to build a database. Right-click on Databases, then pick New Database from the drop-down menu. Follow the wizard’s instructions. 

What are the different types of relationships in a SQL Server database?

Relationships are formed by linking one table’s column to another table’s column. There are four types of relationships that can be established.

The following are the connections:

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

One-to-Many & Many-to-One Relationship

A single column value in one table has one or more dependent column values in another table in a One-to-Many connection.

• one to many

Many to Many Relationship

The third table serves as a connector for tables that seek to form a Many-to-Many relationship. The bridge table is used to contain data that is shared by many-to-many relationship tables.

In SQL Server, how do you delete duplicate rows?

SQL Server’s CTE and ROW NUMER features can be used to eliminate duplicate records.

GETDATE vs. SYSDATETIME: What’s the difference?

Both are identical, with the exception that GETDATE can provide time in milliseconds while SYSDATETIME can provide precision in nanoseconds. The SYSDATE TIME is more precise than the GETDATE TIME.

What exactly is a query?

A query is a request for data or information from a table or set of tables in a database. A select query or an action query are two types of database queries.

SELECT fname, lname 		 /* select query */
FROM myDb.students
WHERE student_id = 1;
UPDATE myDB.students 		 /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

What is the difference between a subquery and a query? What are the different types of it?

A subquery, a nested query or inner query, is a query within another query. It’s used to limit or enhance the data requested by the main query, limiting or boosting the main query’s result, accordingly. For instance, in this case, we get the contact information for students who have enrolled in the maths course:

SELECT name, email, mob, address
FROM myDb.contacts
WHERE roll_no IN (
   SELECT roll_no
   FROM myDb.students
   WHERE subject = 'Maths');

Correlated and Non-Correlated subqueries are the two forms of the subquery.

A linked subquery is not a standalone query, but it might refer to a column in a table that is listed in the FROM of the main query.

A non-correlated subquery can be thought of as a standalone query, with the output of the subquery being used in the main query.

In SQL, what is an Alias?

SQL Server Interview Questions, An alias is a SQL feature that most, if not all, RDBMSs support. It’s a fictitious name given to a table or table column for the purposes of a SQL query. Furthermore, aliasing can be used as an obfuscation technique to protect the true names of database columns. A correlation name is another term for a table alias.

Although the AS keyword is used to signify an alias, it can also be used without it in rare instances. Using the AS keyword, however, is always a smart idea.

SELECT A.emp_name AS "Employee" 	/* Alias using AS keyword */
B.emp_name AS "Supervisor"
FROM employee A, employee B 		/* Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;

What is a Trigger, exactly?

When insert, update, or delete commands are run on a table, triggers are used to run a batch of SQL code. When data is updated, triggers are automatically triggered or executed. It can be run automatically when inserting, deleting, or updating data.

What are the different types of Triggers?

There are four different sorts of triggers:

• Insert
• Delete
• Update
• Instead of

What exactly is the distinction between UNION and UNION ALL?

UNION: The UNION command is used to select related data from two tables. It works in a similar way to the JOIN command.

UNION All: The UNION ALL command is the same as the UNION command, but it selects all values. It will obtain all rows from all tables rather than removing duplicate entries.

20+ PostgreSQL Interview Questions 2021

WordPress Web Server

Laravel Interview Questions

Expert Level SQL Server Interview Questions

What are the commands UNION, MINUS, and INTERSECT?

The UNION operator joins two or more SELECT statements to return a single result set.

In SQL, the MINUS operator is used to remove duplicates from the second SELECT query’s result set from the first SELECT query’s result set, and then return the filtered results from the first.

The INTERSECT clause in SQL combines the result sets returned by two SELECT queries where records from one match records from the other, and then return the intersection of result sets.

Before executing either of the above SQL statements, certain requirements must be met –

Within the clause, each SELECT statement must have the same amount of columns.

The data types in the columns must also be similar.

In each SELECT statement, the columns must be in the same order.

SELECT name FROM Students 	 /* Fetch the union of queries */
UNION
SELECT name FROM Contacts;

SELECT name FROM Students 	 /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;
SELECT name FROM Students 	 /* Fetch names from students */
MINUS 				/* that aren't present in contacts */
SELECT name FROM Contacts;
SELECT name FROM Students 	 /* Fetch names from students */
INTERSECT 			/* that are present in contacts as well */
SELECT name FROM Contacts;

What exactly is an index? Describe the various types of it.

A database index is a data structure that allows for quick access to data in a table’s column or columns. It improves the performance of operations that access data from a database table at the cost of more writes and memory to keep the index data structure up to date.

CREATE INDEX index_name 	 /* Create Index */
ON table_name (column_1, column_2);

DROP INDEX index_name; 	 /* Drop Index */

There are a variety of indexes that can be developed for various purposes:

Unique and Non-Unique Index:

Unique indexes ensure that no two rows of data in a table have the same key values, which helps to maintain data integrity. When a table’s unique index is defined, it is enforced anytime keys are added or updated within the index.

CREATE UNIQUE INDEX myIndex
ON students (enroll_no);

Non-unique indexes, on the other hand, aren’t utilized to impose limitations on the tables they’re connected with. Non-unique indexes are instead utilized primarily to increase query efficiency by keeping a sorted order of frequently used data items.

Clustered and Non-Clustered Index:

SQL Server Interview Questions, Clustered indexes are indexes in which the database rows are ordered in the same order as the index rows. This is why a table can only have one clustered index, although a table can have several non-clustered indexes.

The sole difference between clustered and non-clustered indexes is that with clustered indexes, the database manager tries to retain the data in the database in the same order as the clustered index’s corresponding keys.

Because they provide a linear access channel to data stored in the database, clustering indexes can increase the performance of most query operations.

What exactly is a cursor? What is the best way to use a cursor?

A database cursor is a control structure that enables database records to be traversed. Cursors also make it easier to manipulate data after traversal, such as retrieving, adding, and deleting records. They can be thought of as a pointer to a specific row inside a set of rows.

Using the SQL Cursor

After any variable declaration, DECLARE a cursor. A SELECT Statement must always be coupled with the cursor definition.

To start the result set, move the cursor over it. Before obtaining rows from the result set, the OPEN statement must be executed.

To retrieve and go to the next row in the result set, use the FETCH command.

To deactivate the cursor, use the CLOSE command.

Finally, use the DEALLOCATE statement to remove the cursor definition and free up the resources connected with it.

DECLARE @name VARCHAR(50) 	 /* Declare All Required Variables */

DECLARE db_cursor CURSOR FOR 	 /* Declare Cursor Name*/
SELECT name
FROM myDB.students
WHERE parent_name IN ('Sara', 'Ansh')

OPEN db_cursor 	 /* Open cursor and Fetch data into @name */ 
FETCH next
FROM db_cursor
INTO @name

CLOSE db_cursor 	 /* Close the cursor and deallocate the resources */
DEALLOCATE db_cursor

What is the definition of normalisation?

Normalization is the process of efficiently structuring structured data in a database. It entails the building of tables, the establishment of relationships between them, and the definition of rules to govern those interactions. These rules can keep inconsistency and redundancy in control, allowing the database to be more flexible.

What is Denormalization and How Does It Work?

Denormalization is the inverse of normalisation, in which a normalised schema is transformed into one that contains redundant data. The use of redundancy and keeping redundant data constant improves performance. The overheads caused by an over-normalized structure in the query processor are the rationale for denormalization.

What are the different types of normalisation?

Normal Forms are used in database tables to eliminate or decrease redundancy. The following are the various forms:

First Normal Form

If every attribute in a relation is a single-valued attribute, it is said to be in the first normal form. The first normal form is broken when a relation has composite or multi-valued attributes. Take a look at the table of pupils below. Each student at the table has a name, an address, and a list of books borrowed from the public library –

Students Table

As can be seen, the Books Issued field has several values per record, which must be resolved into distinct individual entries for each book issued in order to convert it to 1NF. Check out the table below in 1NF format –

Students Table (1st Normal Form)

Second Normal Form

SQL Server Interview Questions, If a relation satisfies the rules for the first normal form and does not contain any partial dependencies, it is said to be in the second normal form. In 2NF, a relation has no partial dependency, which means it has no non-prime attribute that is dependent on any suitable subset of any table candidate key. Often, the problem can be solved by setting a single column Primary Key. a few examples

Take the preceding as first example. As can be seen, the Students Table in 1NF form contains a candidate key in the form of [Student, Address], which may be used to uniquely identify all of the table’s records. The non-prime attribute Books Issued is partially dependent on the Student

Terry White

Terry White is a professional technical writer, WordPress developer, Web Designer, Software Engineer, and Blogger. He strives for pixel-perfect design, clean robust code, and a user-friendly interface. If you have a project in mind and like his work, feel free to contact him