SQL Part 2 Flashcards

1
Q

What are the properties a transaction must follow?

A

atomicity, consistency, isolation, and durability.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

types of isolation levels in sql

A

Read Uncommitted
Read Committed
Repeatable Read
Serializable
Snapshot

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is Read Uncommitted isolation level?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What phenomena does Read Uncommitted isolation level prevent?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is Read Committed isolation level?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What phenomena does Read Committed isolation level prevent?

A

So read-committed isolation level prevents dirty read phenomenon

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is Repeatable Read isolation level?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What phenomena does Repeatable Read isolation level prevent?

A

prevents dirty reads and not-repeatable reads

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is Serializable isolation level?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What phenomena does Serializable isolation level prevent?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is Snapshot isolation level?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What phenomena does Snapshot isolation level prevent?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the difference between joins and set operators?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the types of joins?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is inner join?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is full outer join?

A

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

17
Q

What is left outer join?

A

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

18
Q

What is right outer join?

A

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

19
Q

Difference between joins?

A

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

20
Q

Explain the difference between UNION, UNION ALL, and INTERSECT

A

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.

21
Q

What is a cascade delete?

A

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

22
Q

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

A

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.

23
Q

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

A

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.

24
Q

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

A

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]

25
Q

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

A

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.

26
Q

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

A

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().

27
Q

What’s the difference between implicit and explicit cursors?

A

Implicit Cursors are automatically created when select statements are executed.

Explicit Cursors need to be defined explicitly by the user by providing a name.