DATABASE TRANSACTION

1
DATABASE TRANSACTION

2
Transaction

• It is a logical unit of work that must succeed or
  fail in its entirety. A transaction is an atomic
  operation which may not be divided into smaller
  operations.

3
Transaction Properties

1. ATOMICITY(ALL OR NONE CONCEPT) This property
   ensures that either all operations of the
   transaction are reflected properly in the
   database or none are.
2. Consistency This property implies that if the
   database was in consistent state before the start
   of transaction-execution, then upon termination
   of transaction, the database will also be in
   consistent state.

4
Transaction Properties

1. ISOLATION This property implies that each
   transaction is unaware of other transactions
   executing concurrently in the System.
2. DURABLITY This property of transaction ensures
   that after the successful completion of
   transaction, the changes made by it to the
   database persist, even if there are System
   failures.

5
Transaction Control Commands

1. BEGIN / START TRANSACTION It marks the
   beginning of a transaction.
2. COMMIT It ends the current transaction by saving
   database changes and start new transaction.
3. ROLLBACK It ends the current transaction by
   discarding database changes and start new
   transaction.

6
Transaction Control Commands

1. SAVEPOINT It defines breakpoints for the
   transaction to allow partial rollbacks.
2. SET AUTOCOMMIT It enables or disables the
   default commit mode.

7

• SQL FUNCTIONS
• AND
• TABLE JOINS

8
Function

• Function is a special type of predefined commands
  that performs some operation and return a single
  value.
• SQL Supports many and many Functions

9
Function sqrtX
Y e.gx16
then y4
sqrt
10
Types of SQL Function

• (1)Single Row Function works with data of single
  row.
• (2) Multiple Row or Group function works with
  data of multiple rows.
• Single Row function works with single data like
  sqrt function whereas multi row function work on
  more than one value like average function to find
  average of many numbers.

11
Multiple Row or Group function

12
TABLE EMP
Empid empname empphone empsal deptno
101 Ajay 234567 20000 25
102 Vijay 654378 15000 30
103 Ramesh 345678 10000 25
104 Ram 346279 15000 10
13
AVG() function

• This function computes the average of given data.
• Suppose You want to find out the average of
  salary on EMP table then you can use AVG()
  function to do that
• e.g.
• SQLgt SELECT AVG(empsal) Average Salary FROM EMP

14
AVG() function

• Output-
• Average Salary
• ------------------
• 15000

15
COUNT() function

• This function computes the number of rows in a
  given column.
• Suppose You want to find out the number of
  employees in EMP table then you can use count()
  function to do that
• e.g.
• SQLgt SELECT count(empid) Total FROM EMP

16
COUNT() function

• Output-
• Total
• ------------------
• 5

17
MAX() function

• This function returns the maximum value for a
  given column.
• Suppose You want to find out the highest amount
  of salary paid from EMP table then you can use
  MAX() function to do that

18
MAX() function

• Output-
• Maximum Salary
• ------------------
• 20000

19
MIN() function

• This function returns the minimum value for a
  given column.
• Suppose You want to find out the lowest amount of
  salary paid from EMP table then you can use MIN()
  function to do that
• e.g.
• SQLgt SELECT MIN(empsal) Maximum salary FROM
  EMP

20
MIN() function

• Output-
• Minimum Salary
• ------------------
• 10000

21
SUM() function

• This function computes the sum of values of a
  given column.
• Suppose You want to find out the total salary
  paid to employees from EMP table then you can use
  SUM() function to do that
• e.g.
• SQLgt SELECT SUM(empsal) Total Salary FROM EMP

22
SUM() function

• Output-
• Total Salary
• ------------------
• 60000

23
GROUPING RESULTS

24
GROUP BY clause

• The GROUP BY clause combines all those records
  that have identical values in a particular
  column. E.g. To calculate the number of employees
  in each department. You can use the command.
• SELECT dept, count() FROM emp GROUP BY dept

25
GROUP BY clause

• Output-

deptno count()
25 2
30 1
10 1
26
HAVING Clause

• The having clause places conditions on groups.
• e.g. To calculate the number of employees in
  department no. 25. You can use the command.
• SELECT dept, count() FROM emp GROUP BY dept
  HAVING dept 25

27
HAVING Clause

• Output-

deptno count()
25 2
28
Joins

• A join is a query that combines two or more
  tables.
• For example
• select from emp, dept
• will give all the possible combination formed of
  all the rows of both the tables.

29
Equi Join

• The join in which columns are compared for
  equality is called equi-join.

30
Non-equi Join

• It is a query that specifies some relationship
  other than equality between columns of tables.

31
Natural Join

• The Join in which only one of the identical
  column exist is called natural join.

32
CROSS JOIN

• It is a very basic type of join that simply
  matches each row from one table to every row from
  another table.

33
LEFT JOIN

• It will return all rows from the first table
  whether there are matches in the second row or
  not. For unmatched rows of first table NULL is
  shown in second table.

34
RIGHT JOIN

• It will return all rows from the second table
  whether there are matches in the first row or
  not. For unmatched rows of second table NULL is
  shown in first table.

35
Tables and Integrity Constraints
36
Integrity Constraint

• These are the rules that a database must comply
  at all times. Integrity constraint determine what
  all changes are permissible to database.

37

• Valid database means consistent and correct data.
  Data is consistent if individual data items do
  not contradict one another. Data is correct if it
  satisfies all relevant constraints. MySQL
  maintains data integrity through constraints that
  are defined.

38
Alter Table with constraints

• Alter table command can be used for many things
  such as
• adding columns to table
• Modifying column definition of a table
• Adding constraints to table
• Deleting columns of table
• Enabling/disabling constraints

39
Viewing Constraints and their columns

• To view all the information about how the table
  was created including its constraints, you need
  to write the following statement
• Show create table lttablenamegt

40
Enabling/Disabling Constraints

• In MySQL, You cannot disable a Primary Key
  constraint, but you can disable or enable the
  foreign key.
• To disable
• set foreign_key_checks 0
• To enable
• set foreign_key_checks 1