MULTIPLE TABLE OPERATIONS IN SQL

1
MULTIPLE TABLE OPERATIONS IN SQL

• CHAPTER 8

2
VIEWS

• Data from related tables
• are combined into
• 1 table or view

3
Join

• A relation operation
• that causes two tables
• with a common domain
• to be combined into
• a single table

4
Equi-Join

• A join in which the joining condition
• is based on
• equality between values
• in the common columns
• Common columns appear (redundantly)
• in the result table.

5
Natural Join

• The same as an equi-join
• except that one of the
• duplicated columns is
• eliminated in the result table.

6
Outer Join

• A join in which rows
• that do not have
• matching values in
• common columns are
• nevertheless included
• in the result table.

7
UNION

• The union command
• combines the result
• of two queries
• into one table
• as long as the two tables being combined
• have compatible corresponding columns
• same number of columns
• same data type

8
Batch Input

• Some versions of SQL
• have a special command for
• entering rows as a batch
• INPUT command
• INPUT BILLED
• enters a row called BILLED

9
Adding Data to a Table

• INSERT INTO BILLED
• Values (1234, , 220.00)

10
Changing Database Content

• Inform
• relation, column and rows
• UPDATE BILLED
• SET CHARGE 322.5
• WHERE PATIENT_NO 1234
• AND ITEM_CODE 307

11
MODIFYING DATA THROUGH VIEW

• 1. Simple row-and-column subsets
• 2. No mathematically derived column
• 3. No view defined from another view
• 4. No new row inserted into a view table
• if the base table would have missing
  value.

12
Transaction Integrity

• Relevant SQL transaction
• integrity commands
• autocommit
• commit work
• rollback work
• backout

13
AUTOCOMMIT (ON/OFF)

• Specifies whether changes to a table are made
  permanent after each data modification command
  (ON)
• or only when work is explicitly made permanent
  (OFF) by the
• COMMIT WORK command

14
COMMIT WORK

• All changes since the last
• COMMIT WORK command
• are to be made permanent

15
ROLLBACK WORK

• Undo all changes made to the database
• since the last COMMIT WORK command.

16
BACKOUT

• Nullify all changes made since the last
• SAVE command