SQL Questions IV

Question 1

Write down various types of relationships in SQL?

Answer 1

There are various relationships, namely:

One-to-One Relationship.
One to Many Relationships.
Many to One Relationship.
Self-Referencing Relationship.

Question 2

What is a trigger?

Answer 2

The trigger is a statement that a system executes automatically when there is any modification to the database.

In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL.

Question 3

What is a Live Lock?

Answer 3

Livelock occurs when two or more processes continually repeat the same interaction in response to changes in the other processes without doing any useful work.

Livelock to sytuacja w systemach wielowątkowych, rozproszonych lub baz danych, w której operacje systemowe są aktywnie wykonywane, ale nie prowadzą do postępu w realizacji celu, czyli nie rozwiązują problemu, dla którego zostały zaprojektowane.

These processes are not in the waiting state, and they are running concurrently. This is different from a deadlock because in a deadlock all processes are in the waiting state.

Question 4

What is Case WHEN in SQL?

Answer 4

Control statements form an important part of most languages since they control the execution of other sets of statements. These are found in SQL too and should be exploited for uses such as query filtering and query optimization through careful selection of tuples that match our requirements. In this post, we explore the Case-Switch statement in SQL. The CASE statement is SQL’s way of handling if/then logic.

CASE case_value
WHEN when_value
THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE

Question 5

Name different types of case manipulation functions available in SQL.

Answer 5

LOWER: The purpose of this function is to return the string in lowercase. It takes a string as an argument and returns the string by converting it into lower case.

UPPER: The purpose of this function is to return the string in uppercase. It takes a string as an argument and returns the string by converting it into uppercase.

NITCAP: The purpose of this function is to return the string with the first letter in uppercase and the rest of the letters in lowercase.

Question 6

What are local and global variables and their differences?

Answer 6

Global Variable: In contrast, global variables are variables that are defined outside of functions. These variables have global scope, so they can be used by any function without passing them to the function as parameters.

Local Variable: Local variables are variables that are defined within functions. They have local scope, which means that they can only be used within the functions that define them.

Question 7

Name the function which is used to remove spaces at the end of a string?

Answer 7

In SQL, the spaces at the end of the string are removed by a trim function.

Question 8

Define SQL Order by the statement?

Answer 8

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.

By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Question 9

Explain SQL Having statement?

Answer 9

HAVING is used to specify a condition for a group or an aggregate function used in the select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike the HAVING clause, the WHERE clause cannot contain aggregate functions.

Question 10

Define BETWEEN statements in SQL?

Answer 10

The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition will return the records where the expression is within the range of value1 and value2.

Question 11

COMMIT

Answer 11

OMMIT permanently saves the changes made by the current transaction.

The transaction can not undo changes after COMMIT execution.

When the transaction is successful, COMMIT is applied.

Question 12

ROLLBACK

Answer 12

ROLLBACK undo the changes made by the current transaction.

Transaction reaches its previous state after ROLLBACK.

When the transaction is aborted, ROLLBACK occurs.

Question 13

What are ACID properties?

Answer 13

A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions access data using read-and-write operations. In order to maintain consistency in a database, before and after the transaction, certain properties are followed.

These are called ACID properties. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. For more details please read ACID properties in the DBMS article.

Question 14

ACID

Answer 14

Atomicity, Consistency, Isolation, Durability

Question 15

Are NULL values the same as zero or a blank space?

Answer 15

In SQL, zero or blank space can be compared with another zero or blank space. whereas one null may not be equal to another null.

null means data might not be provided or there is no data.

Question 16

What is the need for group functions in SQL?

Answer 16

In database management, group functions, also known as aggregate functions, is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()

Question 17

What is the need for a MERGE statement?

Answer 17

The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE, and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handling large running databases. But unlike INSERT, UPDATE, and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called a target table.

Question 18

What are the advantages of PL/SQL functions?

Answer 18

The advantages of PL / SQL functions are as follows:

We can make a single call to the database to run a block of statements. Thus, it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.

We can divide the overall work into small modules which becomes quite manageable, also enhancing the readability of the code.

It promotes reusability.

It is secure since the code stays inside the database, thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence, security and data hiding is ensured.

Question 19

What is the SQL query to display the current date?

Answer 19

CURRENT_DATE returns to the current date. This function returns the same value if it is executed more than once in a single statement, which means that the value is fixed, even if there is a long delay between fetching rows in a cursor.

Question 20

What are Nested Triggers?

Answer 20

A trigger can also contain INSERT, UPDATE, and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

Question 21

How to find the available constraint information in the table?

Answer 21

In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition. One can use these data dictionaries to check the constraints on an already existing table and to change them(if possible)

Question 22

What is the difference between COALESCE() & ISNULL()?

Answer 22

COALESCE(): COALESCE function in SQL returns the first non-NULL expression among its arguments. If all the expressions evaluate to null, then the COALESCE function will return null.

ISNULL(): The ISNULL function has different uses in SQL Server and MySQL. In SQL Server, ISNULL() function is used to replace NULL values.