SQL 3

Question 1

Transactions

Answer 1

Units of work done on a database that may include many operations (Database Manipulation Language, DML)

Question 2

ACID

Answer 2

Atomicity
Consistency
Isolation
Durability

Question 3

Atomicity

Answer 3

Smallest logical unit that can be run.
All or nothing.
All operations execute successfully or rollback.
If we have bad atomicity, we can lose referential integrity.

Question 4

Consistency

Answer 4

Database is in a valid state according to existing structure and constraints after a commit.

Question 5

Isolation

Answer 5

Concurrent transactions do not affect each other.
One transaction must complete before another may execute, to sharing resources.
The system state during concurrent transactions is the same as if transactions were sequential.

Question 6

Durability

Answer 6

If you shoot it, you have a backup.
Even in catastrophic failure of a database, the transaction will complete.
All commits are final and cannot be rolled back even in system failure.
Oracle 10g

Question 7

Transaction Phenomenon

Answer 7

Issues that occur with concurrent transactions.

TYPES
Dirty Read
Non-Repeatable Read
Phantom Read

Question 8

Dirty Read

Answer 8

returns something from a column/table that has not been committed.
Example
Session 1 - Begin transaction and modifies data
Session 2 - Begins with Session 1’s uncommitted data
If Session 1 rolls back, Session 2’s data is now invalid.

Question 9

Non-Repeatable Read

Answer 9

Row is edited after being viewed
·         Session 1 begins TX, returns a row of data
·         Session 2 update same row and commit
·         Session 1 TX is still in progress, can’t same row
·         Example
Session 1
 Begin TX
SELECT Name for VG WHERE Genre=RPG
§  Zelda and Dark Souls
Session 2
 Begin TX
Update ID2 to Adventure
  Commit

Question 10

Phantom Read

Answer 10

Query that does not return the same result when read again in a transaction.
  Example
Session 1
Begins a TX
Executes Query
Session 2
 Inserts data matching that Query
Session 1
Re-runs that Query and finds something that wasn’t there before. Phantom Data has occurred.

Question 11

Isolation Levels

Answer 11

Think of these as locks on our read-write permissions.
These are set up per database.
Need concurrent access to the database. Always assume thousands of people will be accessing your database concurrently.

Read-uncommitted
Read-committed (Oracle Default)
Repeatable Read
Serializable

Question 12

Read Uncommitted

Answer 12

Terrible.
No locks.
ALLOWS FOR
DIRTY READ
Non-Repeatable Read
Phantom Read

Question 13

Read-committed (Oracle Default)

Answer 13

Oracle default.
It allows only committed data to be read, eliminating the dirty read problem.
Write Lock

ALLOWS FOR
Non-Repeatable Read
Phantom Read

DOES NOT ALLOW
Dirty read

Question 14

Repeatable Read

Answer 14

Write and Read Lock

ALLOWS FOR
Phantom Read

DOES NOT ALLOW
Dirty read
Non-Repeatable Read

Question 15

Serializable

Answer 15

Most secure, but very slow.
Banks often use these, since their information needs to remain highly secure.
Read, Write and Range Lock

DOES NOT ALLOW
Dirty read
Non-Repeatable Read
Phantom Read

Question 16

Sequences

Answer 16

Stores and tracks a number that can be incremented/decremented.
Used for auto-incrementing primary keys
Can grab the current or next value (nextVal)
Good rule of thumb is to start with value 1001, so admins can use the smaller values for future purposes.

Question 17

Sequences EXAMPLE

Answer 17

CREATE SEQUENCE[Seq name]
MINVALUE[value]
MAXVALUE[value]
START WITH [value]
INCREMENT BY [value]
CACHE [value];

Question 18

Cache

Answer 18

storing values in memory for faster access.

Question 19

Views

Answer 19

Essentially a saved SELECT statement
Virtual table returned by Query
Considered a database object, although stored in memory only
If view definition does not have column names, names will be used from Query’s Result Set.v
If a table that view uses is dropped, the view becomes in accessible

Question 20

View vs. Cursor

Answer 20

View restricts access/how much you can see. You can grant permission to a user to see parts of a table, for example. Cursor allows you to see everything.

Question 21

Indices

Answer 21

Apply to column of a table to speed up searches by enforcing a physical in-memory order of rows
Searches are faster, but Insert/Delete statements are slowed down.

Question 22

Triggers

Answer 22

Event listener for a DB
Insert/Update/Delete on a specific table
Get values of an operation and change them
Before
After

Question 23

Functions

Answer 23

Block of code we can execute
MUST return one and ONLY one value.
You can have 0 or more parameters
A function can call other functions
Cannot call stored procedures
No transactions -
No data manipulation (though you can use SELECT statements/DQL)

Question 24

Stored Procedures

Answer 24

Do not and cannot return a value.
Support multiple IN, OUT AND IN/OUT PARAMETERS.
Can call another Stored Procedure
Transactions are okay
DML is okay
DDL is NOT okay

Question 25

Cursors

Answer 25

View stored in a local variable
A pointer to a section of a table/result set.
It must be opened
It can be looped through
Must be closed
Every SQL statement generates a cursor, allowing PL/SQL to interact with SQL.
You cannot return a cursor from a function.
Stored Procedures can have as many OUT parameters as you want, but functions can only return one thing.
We use Cursors because they can be used as parameters. We can pass in a result set to a stored procedure. They’re a lot like iterators, but they aren’t actually iterators

TYPES
Implicit
Explicit

Question 26

Explicit

Answer 26

CUR S = SELECT * FROM POKEMON;
SELECT ID FROM S;
Good way to format output.

Question 27

Implicit

Answer 27

DQL Statements “SELECT”
SELECT * FROM POKEMON
System Reference cursors are already there for use, but we can make our own cursors, too.
There’s always a cursor implicitly created for result sets.

Question 28

JDBC PreparedStatement

Answer 28

What we want to use instead of a regular Statement.
Pre-compiled (more efficient)
Safer against SQL injections
Safer, very efficient
SQL in Statement objects are compiled by Database and and SQL in PreparedStatement objects are compiled by Java.

Question 29

JDBC CallableStatement

Answer 29

For executing Stored Procedures
String str = “{ call SP_FEED_BEAR(?, ?, ?)}”;
//Grab our CallableStatement from Connection

CallableStatement cs = conn.prepareCall(str);

cs. setInt(…); //Set your parameters
cs. setString(…);
cs. setInt(…);
cs. execute();