Software Training Institute

brollyacademy

SQL Queries for Interview Questions

100 SQL Queries for Interview Questions and answers for Freshers & 1 -2 Experienced Candidates
SQL Queries for Interview Questions​

SQL Queries for Interview Questions

1. What do you mean by database?

Ans: A database is a structured form of data storage where data can be retrieved and managed efficiently. It is the collection of tables where the data is stored in an organized way.

Tables consist of rows and columns which rows are also known as records or tuples, and columns are known as attributes. Bank Management Database and University Management Database are a few examples of databases.

2. What is a Relational Database?

Java supports primitive data types – byte, boolean, char, short, int, float, long, and double and hence it is not a pure object oriented language.

3. What do you mean by RDBMS?

Ans: RDBMS is nothing but – Relational DataBase Management System. It is the software that allows storing, managing, querying, and retrieving data from a relational database.

And RDBMS interacts with users and the database; it can also carry out administrative tasks such as – managing data storage, accessing data, and assessing database performance.

4. Can you differentiate between SQL and MYSQL?

SQL

MySQL

It is the licensed product of Microsoft

It is an open-source platform managed by Oracle Corporation

Knowns as Structured Query Language – It is a query programming language

It is the Relational Database Management System

It uses SQL queries to perform tasks in RDBMS

It has no separate querying language. Uses SQL to perform tasks in RDBMS

Data is protected safely by not allowing third parties to intrude on the SQL servers.

Unlike SQL, it is an open-source platform, so data security wouldn’t be as expected.

SQL doesn’t support any connectors

MySQL supports connectors such as the workbench tool to build databases.

5. What is an Entity in a Database?

Ans: Entities are nothing but objects that are stored in the master data services models. They are real-world objects that can be distinguishable from other objects.

Rows in an entity represent members of the master data, and columns represent attributes of the master data. Generally, entities group the same kind of data. For example, a list of employees of a company.

Learn SQL SERVER From Our Expert Trainer

6. What do you mean by Attributes and mention their Types?

Ans: Attributes are the objects that are included in master data service entities. Also, attribute values are used to describe the members of the entity.

There are three types of attributes, as mentioned below:

  • Free-form attributes
  • Domain-based attributes
  • File attributes

7. What is a transaction in SQL Server and mention its modes?

Ans: A transaction represents a single task. Once a transaction is over, modifications made in the data will be committed and stored in a database permanently. If an error occurs in the transaction, then the data changes will be canceled immediately.

The following are the modes of transactions:

  • Autocommit transactions
  • Explicit transactions
  • Implicit transactions
  • Batch-scoped transactions

8. What is a Transaction Log, and why is it important?

Ans: Transaction log records all transactions and related database modifications of every transaction. To be precise, it records the beginning of a transaction, the changes during the transaction, and the last COMMIT or ROLLBACK of the transaction.

The transaction log is one of the vital components in database management since it is used to retrieve the database to a consistent state if there is a system failure.

9. What are ACID properties, and what do they mean?

Ans: The ACID properties are nothing but Atomicity, Consistency, Isolation, and Durability. Generally, all transactions must follow ACID properties.

Atomicity: It ensures that a complete transaction must take place in a single execution. Suppose there is only a subset of operations during a transaction, then there could be a compromise in the aim of the transaction. But, atomicity eliminates this possibility.

Consistency: It ensures that a transaction takes place with absolute data consistency before and after the transaction. Simply put, data consistency shouldn’t be compromised during transactions.

Isolation: It ensures that each transaction takes place in complete isolation from other transactions. It means that each transaction must run as if it is the only transaction that is happening in the system.

Durability: It makes sure that every transaction must be recoverable when required. Once a transaction is committed, it means that all the data updates have been made in the database then, and they can be retrieved at any time by users.

10. What is a Stored Procedure?

Ans: It is a function that consists of a group of statements, which can be stored and used repeatedly. Stored procedures can be called and executed as and when required.

Stored procedures are stored as ‘Named objects’ in the SQL server database. The result set of the stored procedure depends on the values of the parameters.

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

11.What are the advantages of using stored procedures in SQL Server?

A list of advantages of Stored Procedures:

  • Stored procedures help in reducing the network traffic and latency. It boosts up the application performance.
  • Stored procedures facilitate the reusability of the code.
  • Stored procedures provide better security for data.
  • You can encapsulate the logic using stored procedures and change stored procedure code without affecting clients.
  • It is possible to reuse stored procedure execution plans, which are cached in SQL Server’s memory. This reduces server overhead.
  • It provides modularity of application.

12. Define the one-to-one relationship while designing tables.

One-to-One relationship: It can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One to one relationship exists if an entity in one table has a link with only one entity on another table. Let’s take an example of the employee and their employee id so that a unique employee id will be there for a particular employee at another table.

13.How can you hide the SQL Server instances?

You have to make a change in the SQL Server Configuration Manager to hide the SQL Server instances.

Follow the below instructions to launch SQL Server Configuration Manager and do the following:

  • Select the instance of SQL Server.
  • Right-click and select Properties.
  • After selecting properties, you will just set Hide Instance to “Yes” and click OK or Apply.
  • After the change is made, you need to restart the instance of SQL Server, not to expose the name of the instance.

14. What is the CHECK constraint in SQL Server?

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

After using the check constraint on the single column, we can only give some specific values for that particular column. Check constraints and apply a condition for each column in the table.

EXAMPLE:

  1. CREATE TABLE Employee (  
  2.     EMP_ID int NOT NULL,  
  3.     LastName varchar(255) NOT NULL,  
  4.     FirstName varchar(255),  
  5.     Age int,  
  6.     City varchar(255),  
  7.     CONSTRAINT CHK_Employee CHECK (Age>20AND City= ‘Greenville’)  
  8. ); 

15. What is a Recursive Stored Procedure?

Ans: A stored procedure turns into a recursive stored procedure, including a CALL statement in the procedure body. Here, the CALL statements can be nested.

In fact, there is no limit for recursions, but stored procedures nesting is limited by 15 times. Moreover, there are two forms of recursive stored procedures – mutual recursion and chain recursion.

Learn SQL SERVER From Our Expert Trainer

16. What are Database Normalisation and Denormalisation?

Ans: Database normalization is the process of restructuring a relational database to reduce data redundancy and improve data integrity.

On the other hand, denormalization is a reverse engineering process that helps increase the read performance of a database. And it is achieved by either adding copies of data or grouping data. Hence, data can be read in a shorter time.

17. What is a JOIN, and mention its Types?

Ans: JOIN is a logical operation used to retrieve data from two or more two tables. It can only be accomplished when there is a logical relationship between two tables. Here, data from one table is used to select rows in another table.

There are five types of logical JOIN operations as mentioned below:

  • INNER JOIN
  • LEFT (OUTER) JOIN
  • RIGHT (OUTER) JOIN
  • FULL (OUTER) JOIN
  • CROSS JOIN

18. What is a unique key?

The key that can accept only a null value and cannot accept duplicate values is called a unique key. The role of a unique key is to make sure that all columns and rows are unique.

The syntax for a unique key will be the same as the primary key. So, the query using a unique key for the employee table will be:

//

CREATE TABLE Employee (

ID int NOT NULL,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255),

Employee_Age int,

UNIQUE(ID)

);

19. What is the difference between primary key and unique key?

Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.

20. What is a foreign key?

A foreign key is an attribute or a set of attributes that reference the primary key of some other table. Basically, a foreign key is used to link together two tables.

Let us create a foreign key for the following table:

 

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

)

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

21. What are the subsets of SQL?

The main subsets of SQL are:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)

22. Explain the different types of SQL commands.

  • DDL: DDL is that part of SQL that 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
  • DML: DML is used to manipulate already existing data in a database, i.e., it helps users to retrieve and manipulate data. It is used to perform operations such as inserting data into the database through the insert command, updating data with the update command, and deleting data from the database through the delete command.
  • DCL: 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 to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.
  • TCL: TCL is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction with logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

23. What are the usages of SQL?

The following operations can be performed by using SQL database:

  • Creating new databases
  • Inserting new data
  • Deleting existing data
  • Updating records
  • Retrieving the data
  • Creating and dropping tables
  • Creating functions and views
  • Converting data types

24. What is an index?

Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check every row to find matches, which may result in slow operations in large data.

Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches.

Syntax:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

25. What are the Types of Subquery in SQL Server?

Ans: The following are the subquery types:

  • Subqueries with table aliases
  • Subqueries with IN and NOTIN
  • Subqueries in UPDATE, DELETE and INSERT statements
  • Subqueries with comparison statements
  • Comparison operators modified by ANY, SOME or ALL
  • Subqueries with EXISTS and NOT EXISTS

Learn SQL SERVER From Our Expert Trainer

26. How can you differentiate between a Primary Key and a Unique Key?

Ans: The primary key identifies each record in a table. It should have unique values but shouldn’t have NULL values. At the same time, the unique key ensures that all the values in a column are different.

Simply put, the unique key avoids duplication of data in a column except for NULL Values. Moreover, a table will have only one primary key, but it may have many unique keys.

27. How can you relate a Foreign Key and a Primary Key?

Ans: A foreign key is a field (s) in a table that links the primary key with another table. A foreign key is mainly used to prevent the loss of a link between two tables.

The table that has a primary key is known as the parent table, and a table that has a foreign key is known as the child table. A Foreign key can link with a unique key of another table as it links with a primary key.

28. What are Defaults in the SQL Server?

Ans: Default is the value specified for a column in a database table. This value will be duplicated in all the new records of the column unless there is no other value specified.

In this regard, SQL server management studio is the tool used to specify a default value. Know that we can create defaults only for a current database, and the default value can be truncated when it exceeds the size of the column.

29. What are Cursors, and mention their types?

Ans: Cursors are known to be the extensions to result in sets that are the group of rows returned for a statement. They help retrieve one or more blocks of rows from the current position.

Similarly, they can support data modifications for the rows in the current position in the result set.

There are four cursor types, as you can find below:

  • Forward only
  • Static
  • Keyset
  • Dynamic

30. What are Triggers, and mention their types in the SQL server?

Ans: Triggers are the special stored procedures. When there is an event in the SQL server, triggers will run automatically. There are three types of triggers: LOGON, DDL, and DML.

  • LOGON Triggers: They are fired when a user establishes a LOGON event.
  • DDL Triggers: They are fired when there is a Data Definition Language (DDL) event.
  • DML Triggers: They are fired when there is a modification in data due to Data Manipulation Language (DML).

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

31. What are entities and relationships?

Entities: An entity can be a person, place, thing, or any identifiable object for which data can be stored in a database.

For example, in a company’s database, employees, projects, salaries, etc., can be referred to as entities.

Relationships: A relationship between entities can be referred to as a connection between two tables or entities.

For example, in a college database, the student entity and the department entities are associated with each other.

32. What are SQL operators?

SQL operators are the special keywords or characters that perform specific operations. They are also used in SQL queries. These operators can be used within the WHERE clause of SQL commands. Based on the specified condition, SQL operators filter the data. 

The SQL operators can be categorized into the following types:

  • Arithmetic Operators: For mathematical operations on numerical data
    • addition (+)
    • subtraction (-)
    • multiplication (*)
    • division (/)
    • remainder/modulus (%)
  • Logical Operators: For evaluating the expressions and return results in True or False
    • ALL
    • AND
    • ANY
    • ISNULL
    • EXISTS
    • BETWEEN
    • IN 
    • LIKE
    • NOT
    • OR 
    • UNIQUE
  • Comparison Operators: For comparisons of two values and checking whether they are the same or not
    • equal to (=)
    • not equal to (!= or <>)
    • less than (<), 
    • greater than (>)
    • less than or equal to (<=)
    • greater than or equal to (>=)
    • not less than (!<)
    • not greater than (!>)
  • Bitwise Operators: For bit manipulations between two expressions of integer type. It first performs conversion of integers into binary bits and then applied operators
    • AND (& symbol)
    • OR (|, ^)
    • NOT (~)
  • Compound Operators: For operations on a variable before setting the variable’s result to the operation’s result
    • Add equals (+=)
    • subtract equals (-=)
    • multiply equals (*=)
    • divide equals (/=)
    • modulo equals (%=)
  • String Operators: For concatenation and pattern matching of strings
    • + (String concatenation)
    • += (String concatenation assignment)
    • % (Wildcard)
    • [] (Character(s) matches)
    • [^] (Character(s) not to match)
    • _ (Wildcard match one character)

33. What do you mean by data integrity?

Data integrity is the assurance of accuracy and consistency of data over its whole life cycle. It is a critical aspect of the design, implementation, and usage of systems that store, process, or retrieve data.

Data integrity also defines integrity constraints for enforcing business rules on data when it is entered into a database or application.

34. What is the difference between abstract and interface?

Abstract class:

  • It provides a set of rules to implement in the next class. Rules are provided through abstract methods.
  • An Abstract method does not contain any definition.
  • When a class contains all functions without the body, it is called a Fully Abstract Class.
  • Another class can inherit only one abstract class.

Interface:

  • If a class contains all abstract methods, then that class is called Interface.
  • Interface support like multiple inheritances.
  • An Interface does not contain any implementation
  • We can only use public or abstract modifiers.

35. 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. We 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 the data based on range-based queries. A clustered index is actually related to how the data is stored; only one clustered index is possible per table.
  • Non-clustered Index: It stores data at one location and indexes at another location. The index has pointers that point to the location of the data. As the indexes in a non-clustered index are stored in a different place, there can be many non-clustered indexes for a table.

Learn SQL SERVER From Our Expert Trainer

36. What is the ACID property in a database?

The full form of ACID is atomicity, consistency, isolation, and durability. ACID properties are used to check the reliability of transactions.

  • Atomicity refers to completed or failed transactions, where a transaction refers to a single logical operation on data. This implies that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged.
  • Consistency means that the data meets all validity guidelines. The transaction never leaves the database without finishing its state.
  • Concurrency management is the primary objective of isolation.
  • Durability ensures that once a transaction is committed, it will occur regardless of what happens in between such as a power outage, fire, or some other kind of disturbance.

37. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG(), and VARIANCE() are some of the most widely used group functions.

38. What do you understand about a character manipulation function?

Character manipulation functions are used for the manipulation of character data types.

Some of the character manipulation functions are:

UPPER: It returns the string in uppercase.

Syntax:

UPPER(‘ string’)

Example:

SELECT UPPER(‘demo string’) from String;

Output:

DEMO STRING

LOWER: It returns the string in lowercase.

Syntax:

LOWER(‘STRING’)

Example:

SELECT LOWER (‘DEMO STRING’) from String

Output:

demo string

INITCAP: It converts the first letter of the string to uppercase and retains others in lowercase.

Syntax:

Initcap(‘sTRING’)

Example:

SELECT Initcap(‘dATASET’) from String

Output:

Dataset

CONCAT: It is used to concatenate two strings.

Syntax:

CONCAT(‘str1’,’str2’)

Example:

SELECT CONCAT(‘Data’,’Science’) from String

Output:

Data Science

LENGTH: It is used to get the length of a string.

Syntax:

LENGTH(‘String’)

Example:

SELECT LENGTH(‘Hello World’) from String

Output:

11

39. What is the difference between value type and reference type?

Value type and reference type may be similar regarding declaration syntax and usage, but their semantics are distinct. Value type and reference type differ with the memory area, where it is stored.

The Value type is stored on the stack while reference type is stored on the heap.

The Value type stores real data while reference type stores reference to the data.

Accessing is faster in the value type in comparison to reference type.

The value type can contain a null value while the reference type can’t hold a null value.

The value types are derived from System. Value Type while Reference type is derived from System Object. Means value type stores a particular value but a reference type stores a reference or address to the object

String, Object, array are the reference type, as they are stored in heap and dynamic in 

40. What is Referential Integrity?

Ans: Referential integrity aims at keeping SQL databases consistent. It is achieved using a group of rules that enforces relationships among data in tables.

Generally, referential integrity is enforced with the support of foreign key constraints. Besides, it can be enforced with the help of check constraints with user-defined functions and triggers.

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

41. What do you mean by ER Diagram?

Ans: The ER diagram is known as the Entity-Relationship diagram. This diagram shows the visual representation of the structure of tables in databases and their logical relationships.

ER Diagram displays table structures with column names and the associated data types, primary and foreign keys used, and relationships between the tables.

42. What do you mean by CTE?

Ans: CTE is represented as Common Table Expression. It specifies the temporary named result set. This result set is obtained by executing simple queries.

CTE can be referred to in SELECT, INSERT, UPDATE, DELETE and MERGE statements. Moreover, CTE can also be used in VIEW statements. There are two types of CTE – recursive and non-recursive.

43. What is a Sparse Column?

Ans: Sparse columns are nothing but ordinary table columns that provide optimized storage for NULL values. They reduce space requirements for NULL values by about 20 to 40 percent.

Sparse columns can be used with filtered indexes and column sets. Sparse columns are defined by CREATE TABLE and ALTER TABLE statements.

44. What do you mean by Shared, Exclusive, and Updated locks?

Ans:

  • Shared locks: It allows a page or rows only for reading. It restricts modifications of data by concurrent transactions.
  • Exclusive locks: It allows exclusive transactions to modify a page or row using DML statements such as INSERT, UPDATE, and DELETE.
  • Updated locks: It is used to avoid deadlocks. You can place a shared lock over a resource that already has an updated lock.

45. What is COALESCE in SQL server and mention its few properties?

Ans: It is an expression that evaluates arguments in a list and returns only the first value that is not NULL.

For example, consider the following statement

 SELECT COALESCE (NULL, 14, 15);

Now, the COALESCE expression returns the first value 14, which comes first after a NULL value.

Properties of COALESCE expression:

  • The data type must be the same
  • It can be a syntactic shortcut for the case expression

Learn SQL SERVER From Our Expert Trainer

46. What is BULK COPY in SQL server?

Ans: BULK COPY allows a large amount of data transfer in and out of SQL tables or views. In addition, it allows data transfer between an SQL server and an Operating System data file such as ASCII.

BULK COPY in SQL server can be done in the following four modes:

  • Native mode data file: Bulk copy from a table or view into a table or view in the same format.
  • Character mode data file: Bulk copy from a table or view into another table in a different format
  • Bulk copying from a data file into a table or view
  • Loading data into program variables initially and then bulk copying into a table or view.

47. What do you mean by Collation?

Ans: Collation refers to the pre-defined rules to sort and compare data, and it also defines bit patterns that represent characters in a database. The rules help determine how the data should be stored, accessed, and compared.

In addition, collation also provides case sensitivity and accent sensitivity for a dataset. SQL servers can store objects that have different collations in a single database.

48. What is the use of the UPDATE_STATISTICS command?

Ans: SQL Server updates query optimization statistics regularly in a database table or indexed view. In this regard, the SQL server’s query optimizer performs this function by default.

UPDATE_STATISTICS is the command that allows updating query statistics frequently in database tables. As a result, it improves query plans and query performance as well.

49. What is a Filtered Index?

Ans: A filtered index is nothing but a non-clustered index with an optimized disk-based restore. It uses a filter predicate to select a portion of rows in a table.

A filtered index is created when a column has fewer relevant values for queries. Hence, it helps to improve query performance, reduce storage costs, and index maintenance.

50. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary field is added as the AUTO_INCREMENT field so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a new record is inserted.

Syntax:

CREATE TABLE Employee(

Employee_id int NOT NULL AUTO-INCREMENT,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255)

Age int,

PRIMARY KEY (Employee_id)

)

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

51. What is the difference between DROP and TRUNCATE commands?

If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints. 

To create and use the table again in its original form, all the elements associated with the table need to be redefined. 

However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.

52. What is a “TRIGGER” in SQL?

The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands, such as insert, update, and delete, are given.

The syntax used to generate the trigger function is:

CREATE TRIGGER trigger_name

53. Where are usernames and passwords stored in SQL Server?

In SQL Server, usernames and passwords are stored in the main database in the sysxlogins table.

54. What are the third-party tools that are used in SQL Server?

The following is the list of third-party tools that are used in SQL Server:

  • SQL CHECK
  • SQL DOC 2
  • SQL Backup 5
  • SQL Prompt
  • Litespeed 5.0

55. How can you handle expectations in SQL Server?

TRY and CATCH blocks handle exceptions in SQL Server. Put the SQL statement in the TRY block and write the code in the CATCH block to handle expectations. If there is an error in the code in the TRY block, then the control will automatically move to that CATCH block.

Learn SQL SERVER From Our Expert Trainer

56. How many authentication modes are there in SQL Server? And what are they?

Two authentication modes are available in SQL Server. They are:

  • Windows Authentication Mode: It allows authentication for Windows but not for SQL Server.
  • Mixed Mode: It allows both types of authentication—Windows and SQL Server.

57. What is a function in SQL Server?

A function is an SQL Server database object. It is a set of SQL statements that allow input parameters, perform processing, and return results only.

A function can only return a single value or table; the ability to insert, update, and delete records in database tables is not available.

58. Mention different types of replication in SQL Server?

In SQL Server, three different types of replications are available:

  • Snapshot replication
  • Transactional replication
  • Merge replication

59. Which command is used to find out the SQL Server version?

The following command is used to identify the version of SQL Server:

Select SERVERPROPERTY(‘productversion’)

60. What is the COALESCE function?

The 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

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

61. Can we link SQL Server with others?

SQL Server allows the OLE DB provider, which provides the link, to connect to all databases.

Example: Oracle, I have an OLEDB provider that has a link to connect with an SQL Server group.

62. What is SQL Server Agent?

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server.

The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

63. What do you know about magic tables in SQL Server?

A magic table can be defined as a provisional logical table that is developed by an SQL Server for tasks such as insert, delete, or update (DML) operations.

The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.

64. A table variable or a temporary table: which one is faster?

Ans: A table variable functions faster than a temporary table because table variables are stored in memory, whereas temporary tables are stored in a disk. Moreover, if a table variable’s size exceeds the size of memory, then its speed decreases.

65. What do you mean by Scheduled Tasks in SQL server?

Ans: To update the backups and statistics, databases must be maintained regularly. It can be achieved using scheduled jobs. In this way, the SQL server agent supports scheduling tasks in the SQL server to perform maintenance at regular intervals.

In addition, the SQL server agent helps to view and modify the scheduled tasks.

Learn SQL SERVER From Our Expert Trainer

66. What is a TABLESAMPLE?

Ans: This is the SQL statement that allows extracting random samples from a table using FROM statements. When users don’t require the entire dataset of a table, this statement can be applied to extract only the necessary samples.

Note that the extracted rows won’t be in any order, and sampling can be performed based on the percentage of rows.

67. What is SQL injection?

Ans: It is a malicious attack that would be sent targeting an SQL server instance. It is usually inserted through strings and will be passed into the SQL server for execution.

TO OVERCOME THIS ATTACK, all SQL statements need to be verified for SQL injection vulnerabilities before their execution.

Otherwise, the SQL server will execute the statements as usual, which will, in turn, cause harm to resources.

68. What is Database Mirroring, and mention its benefits?

Ans: Database mirroring allows keeping two copies of a single database in two different locations. The database is copied into different server instances of the SQL server database engine. It is applicable for the databases which adapt the full recovery model.

The benefits of database mirroring are as follows:

  • It increases the availability of the database through high-safety mode with automatic failover
  • It increases data protection irrespective of the mode –whether high-performance mode or operating mode
  • It increases the availability of the production database during upgrades, which will reduce downtime significantly.

69. What is the difference between Stored Procedures and Functions?

Ans: In stored procedures, codes are usually compiled, and these compiled codes are executed when the program calls them. But in functions, on the contrary, codes are compiled and executed every time they are called.

Also, there must be a return while executing functions, whereas it is optional while executing stored functions. Furthermore, functions can be called from stored procedures, but stored procedures cannot be called from functions.

70. Can you mention the different types of Queries in SQL Servers?

Ans:

  • Select Query: This query creates the SQL SELECT statement. It retrieves data from tables or views.
  • Insert results: This query creates the SQL INSERT INTO….SELECT statement. It allows copying rows from one table to another and copying rows within a table.
  • Insert Values: This query creates the SQL INSERT INTO….VALUES statement. It creates new rows in tables and inserts values into them.
  • Update Query: This query creates SQL UPDATE…SET statement. It allows updating values in multiple rows.
  • Delete Query: This query creates the SQL delete statement. It helps to remove rows from a table.
  • Make table query: This query creates the SQL SELECT…INTO statement. It creates a new table and rows in it.

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

71. What is the use of Database Engine in the SQL Server?

Ans: The database engine can store, process, and secure data. It creates database objects such as stored procedures, views, and triggers; and executes the objects.

It processes queries, provides controlled access, and optimizes transactions in a database. A SQL server can support up to 50 database engine instances in a single computer.

72. How can you differentiate between UNION and UNION ALL?

Ans: UNION is the operator that combines two queries into a single result set using select statements. Note that it functions based on specified conditions.

Syntax: query1 UNION query2

UNION ALL is the operator that combines two queries into a single result set, extracting all the rows from the tables without any conditions.

Syntax: query1 UNION ALL query2

73. Brief the different types of JOINS?

Ans:

  • INNER JOIN: This command helps to return records that are common to both the tables
  • LEFT JOIN: This command helps to return values that are common to each other along with the complete records of the left table
  • RIGHT JOIN: This command helps to return values that are common to each other along with the complete records of the right table
  • FULL JOIN: This command helps return all the tables’ records when there is a match between the two.
  • CROSS JOIN: It is known as Cartesian Join. This command returns all combinations of each row from the tables. There is no specific condition for joining two tables.

74. What is the difference between JOIN and UNION in SQL server?

Ans: JOIN is the operator that combines data from many tables based on specific conditions, which creates new columns. In contrast, UNION combines data from many tables using SELECT statements, creating new rows.

The number of columns retrieved from the tables needn’t be the same in JOIN statements, whereas the number of columns retrieved from the tables must be the same in UNION statements.

75. What do you mean by Clustered Indexes?

Ans: The clustered index describes how data is stored in a table, and the table should have a key value. Know that there can be only one clustered index for a table.

When there is a clustered index in a table, then it is known as a clustered table. When there is no clustered index in a table, then data is stored in tables unstructured.

Learn SQL SERVER From Our Expert Trainer

76. How can you use the SCOPE_IDENTITY function in the SQL server?

Ans: This function returns the last identity value inserted into an identity column within the same scope.

Here, the scope is nothing but a module, which will have a stored procedure, trigger, batch, and function. If two statements exist in the same stored procedure or batch or function, then it means that they are in the same scope.

The syntax for this function is provided as SCOPE_IDENTITY ( )

77. What is the use of WITH TIES?

Ans: WITH TIES allows adding one or more rows along with the rows limited by the TOP or similar statements. It works only when you use it alongside the ORDER BY expression.

The syntax is given as shown below:

[

     TOP (expression) [PERCENT]

     [WITH TIES]

]

78. How can Deadlocks in the SQL server be resolved?

Ans: Deadlock occurs in a SQL server when two processes lock a single resource simultaneously and wait for the other process to unlock the resource.

Generally, the SQL engine notices this type of incident and ends one of the processes voluntarily, which helps to release the lock. Thus, the SQL engine allows one process to complete successfully while stopping another process simultaneously.

79. What are the types of views in SQL?

In SQL, the views are classified into four types. They are:

  • Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features.
  • Complex View: A view that is built from several tables and includes a GROUP BY clause as well as functions.
  • Inline View: A view that is built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
  • Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.

80. What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system.

We can consolidate several SQL statements into a stored procedure and execute them whenever and wherever required.

A stored procedure can be used as a means of modular programming, i.e., we can create a stored procedure once, store it, and call it multiple times as required. This also supports faster execution when compared to executing multiple queries.

Syntax:

CREATE PROCEDURE procedure_name

AS

Sql_statement

GO;

To execute we will use this:

EXEC procedure_name

Example:

We are going to create a stored procedure that will help us extract the age of the employees.

create procedure employee_age

as

select e_age from the employee

go

Now, we will execute it.

exec employee_age

Output:

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

81. Explain Inner Join with an example.

Inner Join basically gives us those records that have matching values in two tables.

Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.

Syntax:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column_x=table2.column_y;

Example:

select * from employee

select * from department

Output:

Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.

Syntax:

select employee.e_name, employee.e_dept, department.d_name, department.d_location

from employee inner join department

on

employee.e_dept=department.d_name

Output:

After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.

 

82. Explain the difference between OLTP and OLAP.

OLTP: It stands for online transaction processing, and we can consider it to be a category of software applications that are efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to keep up consistency.

The OLTP system often follows decentralized planning to keep away from single points of failure. This system is generally designed for a large audience of end users to perform short transactions.

The queries involved in such databases are generally simple, need fast response time, and, in comparison, return only a few records. So, the number of transactions per second acts as an effective measure for those systems.

OLAP: It stands for online analytical processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions.

For OLAP systems, the efficiency of computing depends highly on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multidimensional schemas.

83. What is Hybrid OLAP?

Hybrid OLAP (HOLAP) uses a combination of multidimensional data structures and relational database tables to store multidimensional data. The aggregations for a HOLAP partition are stored by analysis services in a multidimensional structure. The facts are stored in a relational database.

84. Which is the main third-party tool used in SQL Server?

A list of third-party tools used in SQL Server:

  • SQL CHECK – Idera: It is used to monitor server activities and memory levels.
  • SQL DOC 2 – RedGate: It is used to document the databases.
  • SQL Backup 5 – RedGate: It is used to automate the Backup Process.
  • SQL Prompt – RedGate: It provides IntelliSense for SQL SERVER 2005/2000.
  • LiteSpeed 5.0 – Quest Soft: It is used for Backup and Restore.

85. What are the advantages of using third-party tools?

A list of advantages of using third-party tools:

  • Third party tools provide faster backups and restore.
  • They provide flexible backup and recovery options.
  • They provide secure backups with encryption.
  • They provide the enterprise view of your backup and recovery environment.
  • Easily identify optimal backup settings.
  • Visibility into the transaction log and transaction log backups.
  • Timeline view of backup history and schedules.
  • Recover individual database objects.
  • Encapsulate a complete database restore into a single file to speed up restore time.
  • When we need to improve upon the functionality that SQL Server offers natively.
  • Save time, better information or notification.
  • Third party tools can put the backups in a single compressed file to reduce the space and time.

Learn SQL SERVER From Our Expert Trainer

86. What are SQL Server Integration Services and their functions?

Ans: It is the platform for enterprise-level data integration and data transformation services. It includes copying and downloading files, loading data warehouses, managing SQL Server objects, and cleansing and mining data to solve complex business problems quickly.

Integration services consist of a group of built-in tasks, graphical tools, and an SSIS catalog database.

87. What are the different levels of normalization, and explain them shortly?

Ans:

  • First Normal Form (1 NF): It avoids data duplication in a table. It creates a specific table for the related data and uses the primary key to identify the data.
  • Second Normal Form (2 NF): It creates separate tables for the group of data that belongs to multiple records. The tables are linked with foreign keys.
  • Third Normal Form (3 NF): It eliminates the fields that are not related to keys
  • Boyce Codd normal form /Fourth Normal form (BCNF or 4NF): It should be in the form of the 3 NF. Also, there shouldn’t be any multi-valued dependencies.

88. What is the difference between DELETE and TRUNCATE commands?

Ans: The DELETE command removes a row (s) from a table based on given conditions, whereas the TRUNCATE command removes the entire rows from a table.

Also, changes have to be manually COMMITTED after executing the DELETE command, whereas changes are COMMITTED automatically after the execution of the TRUNCATE command.

89. What is the use of the SET NOCOUNT function?

Ans: This function helps to stop the message that indicates how many rows are being affected while executing a T-SQL statement or stored procedure.

The syntax for the function is given as:

SET NOCOUNT { ON | OFF }

If you set this function ON, then no count is returned in the result set; on the other hand, if you set this function OFF, then count is returned.

90. What do you mean by Magic Tables in SQL server?

Ans: Magic tables are virtual tables that exist in two types – INSERTED AND DELETED. They hold the information of the newly INSERTED and DELETED rows.

The INSERTED table will have the newly inserted rows on top of it. The DELETED tables will have the recently deleted rows on top of it on similar tracks. Magic tables are stored in tempDB.

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

91. How can you prevent SQL injection vulnerabilities?

Ans: We can prevent SQL injection vulnerabilities in the following ways:

  • Using Type-Safe SQL parameters
  • Using parameterized input with stored procedures
  • Filtering inputs
  • Reviewing codes
  • Wrapping parameters

92. What do you mean by the recovery model in SQL Server and its types?

Ans: The recovery model is the property that controls the transaction log maintenance in a database. It tracks the logging of transactions and decides the necessary backup for the transactions and the availability of the restoration options.

There are three types of recovery models: Simple, Full, and Bulk-logged.

  • Simple Recovery Model: There is no log backup, and it eliminates the need to manage the transaction log space.
  • Full Recovery Model: This model requires log backups. It doesn’t allow any work to be lost for any reason.
  • Bulk-logged Model: This model requires log backups. It allows high-performance bulk-copy operations.

93. What are the different types of backups used in SQL servers?

Ans:

  • Copy-only backup: It is a special-use backup that is independent of the regular backups
  • Data backup: It is a backup of data either entirely or partially
  • Database backup: It is a backup of a complete database
  • Differential backup: It is a record of only the changes made in a database after a database backup event
  • Full backup: It is a backup of full data with the necessary transaction log for restoration
  • Log backup: It is a backup of all transaction logs
  • File backup: It is a backup of files in a database
  • Partial backup: It is a backup of files from a specific filegroup such as primary, every read/write, and optionally specified read-only files.

94. Write a query to display employee details working in the EEE department?

Ans: SELECT employee.employee_name, employee.address, employee.salary, employee.age,

FROM Department D

INNER JOIN Employees E

ON department.D_no=employee.D_no WHERE department.D_name=’EEE’

95. Write a query to display employee details with their department names?

Ans: SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary, employee.age, department.department_name

FROM department D

INNER JOIN employee E

ON department.D_no=employee.D_no

Learn SQL SERVER From Our Expert Trainer

96. Write a query to display employee details along with department_name and their age between 21 to 25?

Ans: SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

FROM Department D

INNER JOIN employee E

ON dept.D_no=emp.D_no

WHERE E.age between 21 and 25

97. Write a query to display the employee details whose salary is above 23000 and age is above 22 and working in the CSE department?

Ans: SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

FROM Department D

INNER JOIN employee E

ON dept.D_no=emp.D_no

WHERE E.age>22 and E.salary>23000 and dept.D_name=’CSE’

98. How does Intelligent Query Processing improve the performance of SQL Server workloads?

Ans: The intelligent query processing (IQP) family has many features that improve the performance of workloads with minimum implementation effort.

Here, the various IQP features are adaptive joins, batch mode on rowstore, approximate QP, etc. You can automatically apply IQP to all server workloads by enabling compatibility levels for databases.

99. What is the usage of the SIGN function?

SIGN function is used to define whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0. SIGN function returns the value with its sign.

SYNTAX:

  1. SIGN (number)  
  2.       
  3. If the number>0, then it will return +1  
  4. If the number=0, then it will return 0  
  5. If the number<0, then it will return -1  

100. Can you identify the employee who has the third-highest salary from the given employee table (with salary-related data)?

Consider the following employee table. In the table, Sabid has the third-highest salary (60,000).

Name

Salary

Madhu

70,000

Anil

60,000

Tarun

30,000

Sai

80,000

Below is a simple query to find out the employee who has the third-highest salary. The functions RANK, DENSE RANK, and ROW NUMBER are used to obtain the increasing integer value by imposing the ORDER BY clause in the SELECT statement, based on the ordering of the rows.

The ORDER BY clause is necessary when RANK, DENSE RANK, or ROW NUMBER functions are used. On the other hand, the PARTITION BY clause is optional.

WITH CTE AS

(

    SELECT Name, Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM EMPLOYEE

)

SELECT Name, Salary FROM CTE WHERE RN =3

Want to download these SQL SERVER Interview Questions and Answers in the form of a readable and printable PDF for your interview preparation? 

Click the download button below for the PDF version

Learn SQL SERVER From Our Expert Trainer

Other Related Topics Interview Questions