Business Solutions

1
Business Solutions
2
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.

3
SUBSTRING

• Returns a part of a character or binary string.
• SUBSTRING (expression, start, length)
• SUBSTRING (SQL Programming, 1, 3) SQL

4
CONVERT

• Changes one datatype to another.
• CONVERT(datatypelength, expression)
• CONVERT(char(2), SQL) SQ
• CONVERT(int, 10) 10

5
Using them together...
Select substring(title_id, 1, 2) as
alpha convert(int, substring(title_id, 3, 4)) as
numfrom titles
6
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)

7
REPLICATE

• Replicates a given character or string the number
  of times specified.
• REPLICATE(char, int)

8
ROUND

• Rounds a number according to the specified
  accuracy.
• ROUND (col, int)

9
CHARINDEX

• Returns the index position of the specified
  string.
• CHARINDEX(search_str, str)
• CHARINDEX(., 12.3) 3

10
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.

11
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.

12
Concurrency

• Lost-update problem
• Locking
• database system puts a lock on accessed data so
  it cannot be altered until lock is released.

13
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.

14
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

15
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

16
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.

17
Defining a Transaction

• A transaction starts with the keyword BEGIN
• BEGINSQL statementSQL statementSQL statement
• COMMIT

18
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.