Interview :: SQL
- A trigger allows you to execute a batch of SQL code when an insert, update or delete command is run against a specific table as TRIGGER is said to be the set of actions that are performed whenever commands like insert, update or delete are given through queries.
- The trigger is said to be activated when these commands are given to the system.
- Triggers are the particular type of stored procedures that are defined to execute automatically in place or after data modifications.
- Triggers are generated using CREATE TRIGGER statement.
A self-join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.
SQL queries which contain set operations are called compound queries.
Union, Union All, Intersect or Minus operators are the set operators used in the SQL.
The BETWEEN operator is used to display rows based on a range of values. The values can be numbers, text, and dates as well. BETWEEN operator gives us the count of all the values occurs between a particular range.
The IN condition operator is used to check for values contained in a specific set of values. IN operator is used when we have more than one value to choose.
Constraints are the rules and regulations which are applied to the table column which enforces yours to store valid data and prevents users to store irrelevant data. There are two levels :
- column level constraint
- table level constraint
The main differences between SQL DELETE and TRUNCATE statements are given below:
| No. | DELETE | TRUNCATE |
|---|---|---|
| 1) | DELETE is a DML command. | TRUNCATE is a DDL command. |
| 2) | We can use WHERE clause in DELETE command. | We cannot use WHERE clause with TRUNCATE |
| 3) | DELETE statement is used to delete a row from a table | TRUNCATE statement is used to remove all the rows from a table. |
| 4) | DELETE is slower than TRUNCATE statement. | TRUNCATE statement is faster than DELETE statement. |
| 5) | You can rollback data after using DELETE statement. | It is not possible to rollback after using TRUNCATE statement. |
ACID property is used to ensure that the data transactions are processed reliably in a database system.
A single logical operation of a data is called transaction.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.
Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.
Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.
Ans: A NULL value is not the same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable.' On the other hand, zero is a number, and a blank space is treated as a character.
The NULL value can be treated as unknown and missing value as well, but zero and blank spaces are different from the NULL value.