SQL Interview Questions and Answers- Part 5

Answer:

• Equi-JOIN: An equi-join is a type of join where the joining condition is based on the equality of values in the specified columns. It matches rows from two tables where the specified columns have the same values. The equality operator (=) is used in the join condition to compare the columns.
• Non-Equi-JOIN: A non-equi-join is a type of join where the joining condition is based on a range or inequality comparison rather than equality. It matches rows from two tables based on conditions other than simple equality. Non-equi-joins are useful when you need to compare values with operators such as less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), or between.

Answer:

Lock escalation is a concept in database systems that refers to the process of converting multiple finer-grained locks into fewer coarser-grained locks. It is typically performed by the database management system (DBMS) to optimize the performance and efficiency of locking mechanisms.

Answer:

Lock escalation offers several benefits, such as:

1. Reduced overhead: With fewer locks to manage, the DBMS requires fewer system resources, such as memory and processing power, to handle the locks.
2. Improved concurrency: Coarser-grained locks reduce contention among transactions by allowing multiple transactions to access different parts of a resource simultaneously.
3. Enhanced performance: Lock escalation can improve the performance of queries and transactions by reducing the time spent on acquiring and releasing locks.

Answer:

The DELETE CASCADE constraint is a feature in relational database management systems (RDBMS) that ensures referential integrity between related tables. It is a type of referential integrity constraint that specifies what action should be taken when a row in the parent table is deleted.

Answer:

Below are some different types of indexes in SQL:

• Unique indexes
• Implicit indexes
• Composite indexes
• Single-column indexes

Answer:

Pattern matching in SQL refers to the capability of querying and searching for data based on specific patterns or regular expressions within the data. It allows you to find records that match a certain pattern or contain specific characters or sequences of characters.

Answer:

To retrieve the connection string from SQL Server, you can follow these steps:

1. Open SQL Server Management Studio (SSMS) or any other tool you use to connect to SQL Server.
2. Connect to the SQL Server instance you want to retrieve the connection string for. Enter the necessary credentials and select the appropriate authentication method.
3. Once connected, right-click on the SQL Server instance in the Object Explorer and select “Properties” from the context menu.
4. In the Properties window, navigate to the “Connection” tab.
5. On the Connection tab, you will find a section called “Server name” which displays the server’s name or IP address of the SQL Server instance you connected to.
6. Below the “Server name” section, you will find a text box labeled “Connection string.” The connection string is displayed in this box. It typically includes information such as the server name, authentication method, database name, and additional parameters.
7. Copy the connection string from the text box and use it in your application or wherever you need to establish a connection to the SQL Server instance.

Answer:

The following factors affect the performance of queries:

• Concurrent operations
• Code compilation
• Node types
• Dataset size
• Query structure
• Data sort order
• Data distribution
• Number of processors, nodes, or slices

Answer:

You can avoid duplicate keys in SQL by using the methods, below:

• 
• WHERE NOT EXISTS
• IF NOT EXISTS
• NOT IN
• COUNT(*)=0

Answer:

We can fetch common records using INTERSECT commands in SQL.

Answer:

Collation sensitivity refers to how a database or text processing system handles the ordering and comparison of characters and strings based on their linguistic rules and conventions. In simple terms, it determines how the system treats different characters, such as letters, numbers, and symbols, when sorting or comparing data.

Answer:

There are majorly four types of collation sensitivity, which are as follows:

• Case sensitivity
• Accent sensitivity
• Width sensitivity
• Kana sensitivity

Answer:

Here are some commonly used Data Definition Language (DDL) commands in SQL:

1. CREATE: Used to create a new database, table, index, view, or other database objects.
2. ALTER: Used to modify the structure of an existing database object.
3. DROP: Used to delete an existing database object.
4. TRUNCATE: Used to remove all the data from a table, but keeps the structure intact.
5. RENAME: Used to rename an existing database object.
6. CONSTRAINT: Used to define rules and restrictions on columns in a table.
7. INDEX: Used to create an index on one or more columns of a table to improve query performance.
8. GRANT: Used to provide privileges or permissions to a user or role.
9. REVOKE: Used to revoke previously granted privileges or permissions from a user or role.
10. COMMENT: Used to add comments or descriptions to a database object.

Answer:

In MySQL, TCL (Transaction Control Language) commands are used to manage transactions within a database. Here are some commonly used TCL commands in MySQL:

1. START TRANSACTION: Begins a new transaction. This command is used to define the start of a transaction block. All subsequent statements will be included in the transaction until a COMMIT or ROLLBACK command is issued.
2. COMMIT: Ends the current transaction and saves the changes made. It commits all the changes made during the transaction to the database, making them permanent.
3. ROLLBACK: Cancels the current transaction and undoes any changes made. It rolls back all the changes made during the transaction, reverting the database to its previous state.
4. SAVEPOINT: Sets a named savepoint within the current transaction. Savepoints allow you to mark specific points within a transaction to which you can roll back later if needed.
5. RELEASE SAVEPOINT: Removes a previously defined savepoint. It releases the savepoint and frees any resources associated with it. This command does not affect the changes made within the transaction.
6. ROLLBACK TO SAVEPOINT: Rolls back the transaction to a specified savepoint. It cancels all the changes made after the specified savepoint, including the savepoint itself.
7. SET TRANSACTION: Modifies the transaction characteristics, such as isolation level or access mode. It allows you to specify transaction-specific properties, such as read-only mode or locking behavior.

Answer:

Denormalization is a technique used in database design to improve the performance of database queries by introducing redundancy into the database schema. In a normalized database, data is organized into separate tables to eliminate data redundancy and improve data integrity. However, normalization can sometimes result in complex joins and slower query performance, especially when dealing with large amounts of data.

Answer:

In SQL, the “Auto Increment” feature is used to automatically generate unique numeric values for a column in a table. It is commonly used to assign a unique identifier to each row in a table. The Auto Increment feature is often associated with the primary key column of a table, although it can also be used in other columns.

Answer:

A database relationship refers to the association or connection between two or more tables based on common fields or attributes. It defines how data is related and organized within a database management system (DBMS).

Answer:

There are different types of database relationships:

1. One-to-One Relationship: This type of relationship occurs when each record in one table is associated with only one record in another table.
2. One-to-Many Relationship: In a one-to-many relationship, a single record in one table is associated with multiple records in another table.
3. Many-to-Many Relationship: This relationship occurs when multiple records in one table are associated with multiple records in another table.

Answer:

A table variable is relatively faster than a temporary table as it is stored in memory, while a temporary table is stored on the disk. If the size of the table variable is more than the memory size, then both tables perform similarly.

Answer:

In the context of SQL (Structured Query Language), a clause refers to a specific component or a part of a SQL statement that helps define or modify the behavior of the query. Clauses are used to specify conditions, filters, sorting orders, grouping, and other aspects of the data retrieval or manipulation process.