Transcript presentation source

Business Solutions
Formatting
It is sometimes necessary to alter the
output of a query for the sake of
readability such as in report generation.
This can also be applied to a view
creation which users will share.
SUBSTRING
Returns a part of a character or binary string.
SUBSTRING (expression, start, length)
SUBSTRING (‘SQL Programming’, 1, 3) = SQL
CONVERT
Changes one datatype to another.
CONVERT(datatype[length], expression)
CONVERT(char(2), ‘SQL’) = SQ
CONVERT(int, ‘10’) = 10
Using them together...
Select substring(title_id, 1, 2) as alpha
convert(int, substring(title_id, 3, 4))
as num
from titles
Other data
Col_length - returns the length defined for
a specific column.
COL_LENGTH(‘table_name’, ‘col_name’)
Data_length - returns the length of the
data in an expression
DATA_LENGTH(expression)
REPLICATE
Replicates a given character or string the
number of times specified.
REPLICATE(char, int)
ROUND
Rounds a number according to the
specified accuracy.
ROUND (col, int)
CHARINDEX
Returns the index position of the specified
string.
CHARINDEX(search_str, str)
CHARINDEX(‘.’, ‘12.3’) = 3
Transactions
A transaction is a set of SQL statements
that represent a unit of work or a
procedural operation.
A transaction is not complete unless all off
its steps are followed through.
This can be critical to maintaining data
integrity such as when an account must
be credited while debiting another.
Why transactions?
Transactions are necessary for the purpose
of concurrency control and recovery
concurrency control
- allowing multiple users simultaneous
access
recovery
- allowing the database system to return
the database to a reliable state after a
failure.
Concurrency
• Lost-update problem
• Locking
– database system puts a lock on accessed
data so it cannot be altered until lock is
released.
Locking
Since many users may be trying to access
the same data simultaneously the DBMS
has a locking mechanism which locks
data which is in use.
This provides a solution to concurrency
problems which would arise if locking
were not available.
2 Types of Locks
Exclusive
- for UPDATE, INSERT, and DELETE (write
operations)
- no other transaction can acquire lock until
original is released
Shared
- applied during non-update or read
operations - usually SELECT
- prevents write operations from acquiring
lock
- allows other read operations to share lock
Recovery
• Allows a database to bounce back after a
system failure
• must decide
– what transactions are incomplete
– which transactions completed but were not
written and must be redone
User-defined Transactions
• Allows user to define any number of SQL
statements as a transaction and instruct
the database to process them as one
unit.
Defining a Transaction
• A transaction starts with the keyword
BEGIN
BEGIN
SQL statement
SQL statement
SQL statement
COMMIT
Finishing the Transaction
• If the transaction goes successfully then
the COMMIT command will commit the
changes to the database.
• However, if an error occurs the
ROLLBACK command can be used to
restore the database to its state prior to
the transaction.