Anz Sql Server Interview Questions & Answers

Question 1. What Is A Foreign Key?

Answer :

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

Question 2. What Is Normalization?

Answer :

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

Question 3. What Is Self-join?

Answer :

Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.

Question 4. What Is Sql Profiler?

Answer :

• Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
• Use SQL Profiler to monitor only the events in which you are interested.
• If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

Question 5. How To Find Second Highest Salary Of An Employee?

Answer :

There are many ways to find second highest salary of Employees in SQ. You can either use SQL Join or Subquery to solve this problem.

Here is SQL query using Subquery :

Select MAX(Salary) from table1 WHERE Salary NOT IN ( select MAX(Salary) 

from table1.

Question 6. What Is The Difference Between Truncate And Drop Statements?

Answer :

TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.

Question 7. What Are Aggregate And Scalar Functions?

Answer :

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.

Example -.

Aggregate – max(), count – Calculated with respect to numeric.

Scalar – UCASE(), NOW() – Calculated with respect to strings.

Question 8. Which Operator Is Used In Query For Pattern Matching?

Answer :

LIKE operator is used for pattern matching, and it can be used as -.

% – Matches zero or more characters.

_(Underscore) – Matching exactly one character.

Question 9. What Are The Multiple Ways To Secure A Sql Server?

Answer :

There are multiple approaches with which a developer can ensure the safety and security of a SQL server. Some of them might be common usage while others can be more technically oriented.

• Administrator account on SQL server computer can be renamed.
• Isolate the SQL server from the web server, by setting up SSL and firewalls.
• Always control access to the data by applying roles for server, database, and application.
• NTFS permissions can be utilized to secure physical database files.
• Enable auditing with encryption, disabling the guest account.
• NOT authentications should be preferred.
• Utilize a strong System administrator password, restricting physical access to SQL server.

Question 10. What Is A Trigger And How Many Types Of Triggers Are There?

Answer :

Every time an event with a table occurs like, insert, update or delete, a batch of SQL code can be executed with an entity called Trigger. They are managed by DBMS. Triggers can also be utilized to execute a stored procedure.

Triggers available in SQL server are listed below:

• DML trigger: They are called as Data manipulation trigger. As the name suggests, they are being triggered every time, a DML command i.e. insert, update or delete occur on the table or the view.
• DDL trigger: Any changes occur in the definition of any database object, it can be captured by Data definition language trigger. Production and development-based environment can be controlled and managed by these triggers.
• Logon trigger: These triggers are handy in the case; a login event of SQL server happens. It is fired before a user session is being set up in a SQL server.

Question 11. Explain The Usage Of View In Sql Server?

Answer :

This is the basic SQL Server Interview Questions asked in an interview. Views are the most beneficial entity for an SQL server developer. There is a whole lot of complexity involved in the database schema. At the same time, customizing the data for a particular set of users can be a tedious task, which is equally complex as database schema design. These types of complexity can be abstract away with a View. They provide a mechanism to control access to specific rows and columns. Thus, the performance of a database can be improved significantly by aggregating the data.

Question 12. What Is The Replication And Why It Is Required On The Sql Server?

Answer :

It is a set of technologies for copy and distributes data and database objects from one database to another. Synchronize the data can also be achieved with replication to maintain consistency. Replication can be used to distribute data to various locations and to remote or mobile users over the certain medium of internet. Multiple servers having data can be synchronized with the replication process using a replica set. Thus, reading capacity can be enhanced and users can be provided with a choice to selecting among different server to perform read-write operations.

Question 13. What Is The Command To Create A Database In Sql Server?

Answer :

There is a command called ‘CREATEDATABASE’, it can be utilized to create any database on the SQL server.

Its syntax is:

CREATE DATABASE database_name

Example- “Test” database can be created to CREATE DATABASE Test

Question 14. What Are The Merits And Demerits Of Having An Index In Sql Server?

Answer :

There are various merits and demerits of index usage in SQL server. Some of them can cite as:

• Indexes can help speed up a SELECT query.
• One can also search against large string values, given index is set to full-text index.
• Indexes help to make a row unique or without any duplicate.

Some disadvantages can be:

• Indexes take additional space; thus, disk size is being occupied.
• An index can slow down the basic operation like insert, delete, and update but if where the condition has an index field, then update operation can be faster. Insert, delete, or update becomes slower since after each operation index must be updated.

Question 15. What Is Collation In Sql Server?

Answer :

There are the certain set of rules which would decide how data needs to be sorted and compared in a database. These rules can be referred to as Collation. Example – Character data is sorted using rules that defines the right character sequence, with an option for specifying case sensitivity, character width etc. Collation is predefined in SQL server, which would decide how data in SQL server are stored and retrieved.

There are various collations that exist in SQL server, but 2 are main:

• SQL_Latin1_General_CP1_CI_AS
• SQL_Latin1_General_CP1_CI_AS

Where CI is case insensitive and CS is case sensitive. So, by default collation is case insensitive, thus all database in it is also case-insensitive.

Question 16. What Is A Cursor And What Are Its Different Types?

Answer :

This is the frequently asked SQL Server Interview Questions in an interview. A cursor is a database object, which can utilize to retrieve the data, one row at a time from the result set. When data needs to be updated row-by-row, cursors can be very handy. The cursor life cycle consists of mainly 5 steps.

• Declaring cursor – declared by defining SQL statement.
• Opening cursor – Opened for storing data, retrieved from the result set.
• Fetching cursor – Once a cursor is opened, rows can be fetched one by one or in a block to perform data manipulation.
• Closing cursor – Once data manipulation is done, cursor needs to be closed explicitly.
• Deal location cursor – to delete cursor definition, cursors should be deallocated, released all system resources associated with a cursor.

Types of a cursor are:

• Static – These types of a cursor are responsible for making a temporary copy of data and store in TempDB. Any modification done on base table is not highlighted in data return by fetches made by a cursor.
• Dynamic – As the opposite of static, they highlight all changes in a base table.
• Forward-only – cursor can only fetch sequentially from first to last.
• Keyset-driven – Keyset is the set of keys which uniquely identifies a row is built in TempDB.

Question 17. What Is The Difference Between A Union And A Join In Sql Server?

Answer :

A UNION select rows whereas a JOIN selects columns from two or more tables. Both can be used to combine data from multiple tables. In a nutshell, JOIN combines data into new columns, whereas UNION combines data into new rows.