SQL Server Interview Questions

1. What do you mean by DBMS? What are its different types?

Ans: A database management system (DBMS) is application software that interacts with the user, applications and the database to represent and analyze the data.

A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved, deleted and the data in the database can be of any type like strings, numbers, images etc.

There are two types of DBMS:

1. Relational Database Management System:  The management system contains the data which is stored in relations or tables. 
   Example: MySQL.
2. Non-Relational Database Management System: It is the management system where there is no concept of relations, tuples and attributes.  
   Example: Mongo

2. What are joins in SQL?

Ans: A join is a clause which combines rows from two or more tables based on a related column between them. The join merges two tables or will retrieve the data from it. There are four joins.

1. Inner Join
2. Right Join
3. Left Join
4. Full Join

3. What is the Primary key?

Ans: 

• A Primary key is a column or a set of columns that uniquely identifies each row in the table.
• The primary key uniquely identifies a single row in the table
• Primary key will not allow Null values.

4. What is a Foreign Key?

Ans: 

• When a one table’s primary key field is added to related tables to create the common field which relates the two tables, it called a foreign key in other tables.
• Foreign Key constraints enforce referential integrity.

5. What is the difference between DELETE and TRUNCATE statements?

Ans: 

Delete:

• The command deletes a row in a table.
• It allows rollback after using the delete statement.
• It is a DML command.
• It performs slowly than a truncate statement.

Truncate:

• The command deletes all the rows from a table.
• It doesn’t allow rollback.
• It is a DDL command.
• It performs faster.

6. What are the properties of the Relational tables?

Ans: There are six properties for a relational table.

• The values are atomic.
• Each row is unique.
• Each column must have a unique name.
• The column values are of the same kind.
• The sequence of rows is insignificant.
• The sequence of columns is insignificant.

7. What is the difference between a primary key and a unique key?

Ans: 

Primary key:

• The primary key is a column whose values uniquely identifies every row in a table. The values cannot be reused. 
• They create a clustered index on the column. 
  It cannot be null.

Unique key:

• A unique key is a column whose values also uniquely identifies every row in a table. 
• They cannot create a non-clustered index by default.
• It allows only one NULL value.

8. What is the difference between HAVING CLAUSE and a WHERE CLAUSE?

Ans: 

• Both clauses specify a search condition but the HAVING clause is used only with the SELECT statement and typically used with GROUP BY clause.
• If the GROUP BY clause is not used, then the HAVING clause behaves like a WHERE clause only.

9. List the advantages of using the Stored procedures?

Ans: 

• It boosts the performance of an application.
• The execution plans of a stored procedure will be reused as they cached in SQL Server's memory which reduces server overhead.
• Stored procedures can be reused.
• It can encapsulate logic. The stored procedure code can be changed without affecting the clients.
• It provides better security for the data.

SQL Server Certification Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

10. Explain different types of Normalization?

Ans: There are many levels of normalization which are called normal forms. Each consecutive normal form is dependant on the previous normal form. 

• First Normal Form (1NF): There will be no repeating groups within rows in this normal form.
• Second Normal Form (2NF): In this normal form every non-key column value is dependent on the whole primary key.
• Third Normal Form (3NF): This normal form is entirely dependent on the primary key and no other non-key column value.

11. What are the different subsets of SQL?

Ans:

• DDL (Data Definition Language): It allows to perform various operations on the database such as CREATE, ALTER and DELETE objects.
• DML ( Data Manipulation Language): It allows to access and manipulate data. It helps to insert, update, delete and retrieve data from the database.
• DCL ( Data Control Language): It allows you to control access to the database. Example: Grant, Revoke access permissions.

12. What is a trigger and types of a trigger?

Ans: The trigger allows us to execute a batch of SQL code whenever the event of table arises such as INSERT, UPDATE or DELETE command is executed. Triggers are stored and managed by DBMS which also executes a stored procedure.

There are three types of triggers available in the SQL Server as follows.

• DML Triggers: These triggers are invoked when DML commands like INSERT, DELETE or UPDATE operation is executed on the table or the view.
• DDL Triggers: These triggers are invoked when the changes occur in the definition of the database objects instead of actual data. It is helpful in controlling the production and development of database environments.
• Logon Triggers: These triggers are fired in case of the logon event of the SQL Server. This is fired before the setup of a user session in the SQL Server.

13. What is a subquery?

Ans: A subquery is a subset of an SQL query. It is a subset of SELECT statements whose return values are used in filtering conditions of the main query. The subqueries can be applied in SELECT clause, FROM clause and WHERE clause. It can be nested inside a SELECT, INSERT, UPDATE or DELETE statement or inside another subquery.
Types of Sub-query:

• Single-row sub-query: This subquery returns only one row.
• Multiple-row sub-query: This subquery returns multiple rows.
• Multiple column sub-query: This subquery returns multiple columns.

14. Define UNION, UNION ALL, MINUS, INTERSECT?

Ans: 

• UNION: The command returns all distinct rows selected by either query.
• UNION ALL: This command returns all rows selected by either query, including all the duplicates.
• MINUS: The command returns all distinct rows selected by the first query but not by the second query.
• INTERSECT: This command returns all distinct rows selected by both queries.

15. What is a view?

Ans: It is a virtual table that contains data from one or more tables. Views will select only the required values for restricting the data access of the table and the complex queries simple.

Rows updated or deleted in the view are updated or deleted in the table the view was created with. When the data in the original table changes, so as the data in the view also changes, as views are the way which looks at part of the original table. The results of using a view are not permanently stored in the database.

16. What is an ACID property in a database?

Ans: ACID properties stand for Atomicity, Consistency, Isolation and Durability. It ensures the data transactions are processed reliably in a database system.

• Atomicity: Atomicity is the transactions which are completely done or completely failed where the transaction refers to a single logical operation of a data. If one part of any transaction fails, then the entire transaction fails and the database state is left unchanged.
• Consistency: Consistency ensures that the data should meet all the validation rules. In simple terms, it is the transaction that never leaves the database without completing its state.
• Isolation: The main goal of isolation is concurrency control.
• Durability: It means that if a transaction has been committed, it will occur whatever may come in between such as loss of power, crash or any kind of error.

17. What are Entities and Relationships?

Ans: 

Entity: It is a real-world object which can be identified easily. 

Example: A college database contains entities such as students, professors, workers, departments and projects. Each entity has an associated property that provides an identity.

Relationships: The relationships are the links or association between the entities.

Example: The company database contains the employee’s table that is associated with the salary table in the same database. 

18. What is an Alias in SQL?

Ans: Alias is a temporary name assigned to the table or table column for the purpose of a particular SQL query. A table alias is also called a correlation name. The alias name can be referred in WHERE clause to identify a particular table or a column. An alias is represented by using the AS keyword.

Example:

Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empID

In the above example, emp refers to alias name for employee table and dept refers to alias name for department table.

19. What is a stored procedure?

Ans: A stored procedure is a set of SQL queries which take input and send back output. If the procedure is modified, all clients automatically get the new version. Stored procedures will improve performance and reduce network traffic. Stored procedures ensure database integrity.

Syntax:

DELIMITER $$

CREATE PROCEDURE FetchAllStudents()

BEGIN

SELECT *  FROM myDB.students;

END $$

DELIMITER ;

Subscribe to our YouTube channel to get new updates..!

Subscribe

20. What is a Cursor? How to use a Cursor?

Ans: A cursor in a database is a control structure that traverses the records in a database. Cursors also facilitate processing the retrieval, addition and deletion of database records after traversal. It can be viewed as a pointer to one row in a set of rows.

Working with SQL Cursor

• DECLARE a cursor after any variable declaration. The cursor declaration must always be associated with a SELECT Statement.
• Open cursor to initialize the result set. The OPEN statement must be called before fetching rows from the result set.
• Use the FETCH statement to retrieve and move to the next row in the result set.
• Call the CLOSE statement to deactivate the cursor.
• At last use the DEALLOCATE statement to delete the cursor definition and release the associated resources.

Syntax:

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 cursor and deallocate the resources*/

DEALLOCATE db_cursor

21. List the different types of relationships in SQL?

Ans: The relationships in SQL are.

1. One-to-one: It is the relationship between two tables where each record in one table is linked with the maximum of one record in the other table.
2. One-to-Many & Many-to-one: It is the relationship where a record in a table is associated with multiple records in another table.
3. Many-to-Many: It is used when multiple instances on both sides are needed for defining a relationship.
4. Self Referencing Relationships: This is used when a table needs to define a relationship with itself.

22. Name the operator which is used in the query for pattern matching?

Ans: In SQL, LIKE operator is used for matching the patterns. It can be used as.

1. %: It is used to match zero or more characters.
   Example: 

select * from candidates where candidatename like ‘a%’

       2._ (Underscore): It matches exactly one character.
Example: 

select * from candidates where candidatename like ‘abc_’

23. What are the four types of JOIN in SQL?

Ans:

1.JOIN or INNER JOIN: It retrieves the records of matching values in both table involved in a join.
Syntax:

SELECT * FROM Table_A JOIN Table_B;

SELECT * FROM Table_A INNER JOIN Table_B;

2.LEFT JOIN:  It retrieves all the records/rows from the left table and the matched records/rows from the right table.
Syntax:

SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col;

3.RIGHT JOIN: It retrieves all the records/rows from the right table and the matched records/rows from the left table.
Syntax:

SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col;

4.FULL JOIN: It retrieves all the records where there is a match in either the left or right table.
Syntax:

SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col;

24. Explain the creation and execution of a user-defined function in the SQL Server?

Ans: User-defined function is created as follows.

Example:

CREATE Function fun1(@num int)

returns table

as

return SELECT * from employee WHERE empid=@num;

The function can be executed as follows:

SELECT * from fun1(31);

The name of ‘fun1’ is created to fetch employee details of an employee having empid=31.

25. List the ways to get the count of records in a table?

Ans: The following commands are used in a table for counting the records.

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

26. Write a SQL query to get the third-highest salary of an employee from employee table?

Ans: 

SELECT TOP 1 salary FROM

(SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY salary ASC;

27. Which TCL Commands are available on the SQL Server?

Ans: There are three TCL commands available in SQL Server.

Commit: This command saves the transaction permanently in the database.

Rollback: This command is used to roll back the changes that are done which means restoring the database of the last committed state.

Save Tran: It is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.

28. What are some common clauses used with SELECT query in SQL?

SELECT * FROM myDB.students

WHERE graduation_year = 2019

ORDER BY studentID DESC;

SELECT COUNT(studentId), country FROM myDB.students

WHERE country != "INDIA"

GROUP BY country

HAVING COUNT(studentID) > 5;

29. What is the main difference between “BETWEEN” and “IN” condition operators?

Ans: 

BETWEEN: This operator is used for displaying rows based on range of values in a row

Example:

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;

IN: This operator is used to check the values contained in a specific set of values.

Example:

SELECT * FROM students where ROLL_NO IN (8,15,25);

30. What is Collation? What are the different types of Collation Sensitivity?

Ans: The collation is a set of rules which determines how the data can be sorted and compared. Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc. The different types of collation sensitivity are.

• Case sensitivity: A and a are treated differently.
• Accent sensitivity: a and á are treated differently.
• Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently.
• Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.

31. What is the purpose of advertising cookies?

Ans: The purpose of advertising cookies is to improve the effectiveness and relevance of online advertising campaigns. These cookies are used to track user behavior and preferences, allowing advertisers to deliver targeted ads to specific individuals or groups. By gathering information such as browsing history, demographics, and interests, advertising cookies help advertisers tailor their messages to resonate with the intended audience, increasing the chances of engagement and conversion. Additionally, advertising cookies aid in measuring the success of advertising campaigns and optimizing them for better performance. Through this personalized approach, advertising cookies strive to enhance the overall user experience and ensure that the ads displayed are more likely to be of interest to the individuals seeing them.

32. How can users opt out of the sale of personal information cookies?

Ans: To opt out of the sale of personal information cookies, users can exercise their rights under the California Consumer Privacy Act (CCPA). Under the CCPA, individuals have the right to opt out of the sale of their personal information to third parties. To do this, users can navigate to the privacy settings or preferences section of the website or platform they are using. Within these settings, they should look for the option to manage cookie preferences or toggle the sale of personal information cookies. By using this toggle switch, users can choose to opt out of the collection of their browsing activity for targeted advertising purposes. It's important to note that opting out of these cookies does not mean users won't see any ads, but the ads they encounter will not be personalized based on their browsing behavior.