Explore topic-wise InterviewSolutions in Current Affairs.

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.

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.

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.

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.

Types of a cursor are:

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.

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:

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.

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.

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

Some disadvantages can be:

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

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

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.

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.

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.

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.

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.

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:

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.

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.

LIKE OPERATOR is used for pattern MATCHING, and it can be used as -.

% – MATCHES zero or more CHARACTERS.

_(Underscore) – Matching exactly one CHARACTER.

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

% – Matches zero or more characters.

_(Underscore) – Matching exactly one character.

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.

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.

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.

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.

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.

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.

• 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.

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.

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.

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.

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.

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.

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.