SQL Part 2

Question 1

What are the properties a transaction must follow?

Answer 1

atomicity, consistency, isolation, and durability.

Question 2

types of isolation levels in sql

Answer 2

Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot

Question 3

What is Read Uncommitted isolation level?

Answer 3

the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads

Question 4

What phenomena does Read Uncommitted isolation level prevent?

Answer 4

Transactions running at this level do not issue shared locks to prevent other transactions from modifying data read by the current transaction

Question 5

What is Read Committed isolation level?

Answer 5

This isolation level guarantees that any data read is committed at the moment it is read

Question 6

What phenomena does Read Committed isolation level prevent?

Answer 6

So read-committed isolation level prevents dirty read phenomenon

Question 7

What is Repeatable Read isolation level?

Answer 7

only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.

Question 8

What phenomena does Repeatable Read isolation level prevent?

Answer 8

prevents dirty reads and not-repeatable reads

Question 9

What is Serializable isolation level?

Answer 9

provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.

Question 10

What phenomena does Serializable isolation level prevent?

Answer 10

protects from all phenomena that a Repeatable Read does, plus prevents phantom inserts,

preventing other users from updating or inserting rows into the data set until the transaction is complete

Question 11

What is Snapshot isolation level?

Answer 11

specifies that data read within a transaction will never reflect changes made by other simultaneous transactions, avoids most locking and blocking by using row versioning

Question 12

What phenomena does Snapshot isolation level prevent?

Answer 12

Dirty reads are prevented because only committed data is visible, and the static nature of the snapshot prevents both non-repeatable reads and phantoms from being encountered

Question 13

What is the difference between joins and set operators?

Answer 13

join combine columns from separate tables; whereas, set operations combine rows from separate tables

Question 14

What are the types of joins?

Answer 14

inner join,
full outer join,
left outer join
right outer join

Question 15

What is inner join?

Answer 15

selects all rows from both participating tables as long as there is a match between the columns

Question 16

What is full outer join?

Answer 16

a method of combining tables so that the result includes unmatched rows of both tables.

Question 17

What is left outer join?

Answer 17

returns all the rows from the table on the left and columns of the table on the right is null padded.

Question 18

What is right outer join?

Answer 18

It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table.

Question 19

Difference between joins?

Answer 19

The major difference between inner and outer joins is that inner joins result in the intersection of two tables, whereas outer joins result in the union of two tables

Question 20

Explain the difference between UNION, UNION ALL, and INTERSECT

Answer 20

UNION removes any duplicate records.

UNION ALL combines two or more result sets into a single set, including all duplicate rows.

INTERSECT takes the rows from both the result sets which are common in both.

Question 21

What is a cascade delete?

Answer 21

option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table

Question 22

What is the purpose of a view? What about an index?

Answer 22

Views are generally used to focus, simplify, and customize the perception each user has of the database.

Indexes are used to retrieve data from the database more quickly than otherwise.

Question 23

What’s the difference between a clustered and non-clustered index?

Answer 23

A clustered index is an index which defines the physical order in which table records are stored in a database.

Non-Clustered index is an index structure separate from the data stored in a table that reorders one or more selected columns.

Question 24

What is a trigger? Give the syntax for creating a trigger.

Answer 24

Trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]

Question 25

How would you setup a primary key that automatically increments with every INSERT statement?

Answer 25

You would create a table with a primary key and use the key word AUTO_INCREMENT on the value and use this in all related tables.

Question 26

What is the difference between scalar and aggregate functions? Give examples of each

Answer 26

Scalar Functions take one or more input values as arguments directly and return a value. Examples of Scalar Functions are LCASE(), UCASE(), LEN(), MID(), ROUND(). Aggregate Functions take a complete set of data as input and return a value that is computed from all the values in the set. Examples of Aggregate Functions are SUM(), COUNT(), AVG(), MIN(), MAX(), FIRST().

Question 27

What’s the difference between implicit and explicit cursors?

Answer 27

Implicit Cursors are automatically created when select statements are executed. Explicit Cursors need to be defined explicitly by the user by providing a name.