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(datatype[length], 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.