DBMS — Part 4

Exercise 6: COMMIT · ROLLBACK · SAVEPOINT | Exercise 7: JOINS &

Sub-Queries
WHITE SIDE = tables & output results | RULED LINES SIDE = theory & queries
Page 1 — Exercise 6: COMMIT

✦ WHITE SIDE — No table on this page (write heading only)

(This page is mostly theory on the ruled side. White side stays blank or can show the customers table
structure.)

CUSTOMERS table structure (optional — draw on white side):

Column Datatype Size
id number 10
name varchar2 12
age number 3
address varchar2 30

✦ RULED LINES SIDE — Write Exercise 6 Theory + COMMIT Queries

Here

Write heading:
Exercise - 6
Queries on Controlling Data: Commit, Rollback and Savepoint.

Commit:
Commit keyword saves the data permanently in the database. If commit statement is not issued after
insert keyword, then the inserted rows or DML Statements remains in the database till the user logs-in
the database. Once the user logs out his session then the data inserted will be erased off from the
database.

Write all these queries line by line on ruled lines:

SQL> create table customers(id number(10), name
varchar2(12), age number(3), address varchar2(30));
Table created.
SQL> insert into customers values(23,'sanjana',34,'vizag');
1 row created.
SQL> insert into customers values(21,'angel',45,'patna');
1 row created.
SQL> insert into customers values(20,'gayatri',23,'gujarat');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> insert into customers values(25,'anusha',33,'banglore');
1 row created.
Now user logs out and then logs in.
SQL> select * from customers;
(Only first three rows are shown as they are saved by COMMIT)
Page 2 — ROLLBACK: Before & After Tables

✦ WHITE SIDE — Write These TWO Tables Here (Before & After
Rollback)

Table 1: BEFORE ROLLBACK (write label 'Before Rollback' above):

ID NAME AGE ADDRESS
23 sanjana 34 vizag
21 angel 45 patna
20 gayatri 23 gujarat
25 srujana 33 banglore
22 anjali 28 hyderabad

Table 2: AFTER ROLLBACK (write label 'After Rollback' above):

ID NAME AGE ADDRESS
23 sanjana 34 vizag
21 angel 45 patna
20 gayatri 23 gujarat
25 srujana 33 banglore

✦ RULED LINES SIDE — Write ROLLBACK Theory + Queries Here

Queries using ROLLBACK:

Rollback:
Rollback undoes the data (Undo the changes made by DML) that has been saved till the last commit
statement.
Rollback takes (or restores) the database state to its previous committed state.

Write these queries on ruled lines:

SQL> insert into customers values(25,'srujana',33,'banglore');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> insert into customers values(22,'anjali',28,'hyderabad');
1 row created.
 SQL> select * from customers;
(Shows 5 rows — see BEFORE ROLLBACK table on white side)

SQL> ROLLBACK;
Rollback complete.
SQL> select * from customers;
(Shows 4 rows — anjali removed. See AFTER ROLLBACK table on white side)
Page 3 — SAVEPOINT: Tables After Each Savepoint

✦ WHITE SIDE — Write THREE Tables Here (After S1, After S2, After
Rollback to S1)

Table 1: After SAVEPOINT S1 (anjali inserted + S1 created):

ID NAME AGE ADDRESS
23 sanjana 34 vizag
21 angel 45 patna
20 gayatri 23 gujarat
25 srujana 33 banglore
22 anjali 28 hyderabad
26 kavya 29 kolkata

Table 2: After SAVEPOINT S2 (kavya inserted + S2 created):

ID NAME AGE ADDRESS
23 sanjana 34 vizag
21 angel 45 patna
20 gayatri 23 gujarat
25 srujana 33 banglore
22 anjali 28 hyderabad
26 kavya 29 kolkata

Table 3: After ROLLBACK TO S1 (kavya removed):

ID NAME AGE ADDRESS
23 sanjana 34 vizag
21 angel 45 patna
20 gayatri 23 gujarat
25 srujana 33 banglore
22 anjali 28 hyderabad

✦ RULED LINES SIDE — Write SAVEPOINT Theory + Queries Here

Queries using SAVEPOINT:
Savepoint:
Savepoint rollbacks the database state to a specific point (savepoint) that has been already defined
with savepoint. We can define any number of save points in a database.

Write these queries on ruled lines:

SQL> insert into customers values(22,'anjali',28,'hyderabad');
1 row created.
SQL> SAVEPOINT S1;
Savepoint created.
SQL> select * from customers;
(See Table 1 on white side — 6 rows with anjali)

SQL> insert into customers values(26,'kavya',29,'kolkata');

1 row created.
SQL> SAVEPOINT S2;
Savepoint created.
SQL> select * from customers;
(See Table 2 on white side — 7 rows with kavya)

SQL> ROLLBACK TO S1;

Rollback complete.
SQL> select * from customers;
(See Table 3 on white side — kavya removed, back to 6 rows)
Page 4 — Exercise 7: SQL JOINS

✦ WHITE SIDE — Write BASE TABLES + INNER JOIN Result Tables

Here

STUDENTS table (write on white side):

STUDENT_ID NAME CITY
1 Rahul Delhi
2 Anita Hyderabad
3 kiran Chennai
4 Meena Mumbai
5 Arjun Banglore

MARKS table (write on white side):

STUDENT_ID SUBJECT MARKS
1 Maths 85
1 Science 90
2 English 78
3 Maths 88

Query 1 Result — INNER JOIN (student_id, name, subject, marks):

STUDENT_I NAME SUBJECT MARKS
D
1 Rahul Maths 85
1 Rahul Science 90
2 Anita English 78
3 kiran Maths 88

Query 2 Result — Maths marks only (name, marks):

NAME MARKS
Rahul 85

Query 3 Result — marks > 75 (name, subject, marks):

NAME SUBJECT MARKS
 Rahul Maths 85
Rahul Science 90
kiran Maths 88

✦ RULED LINES SIDE — Write Exercise 7 Theory + INNER JOIN Queries

Here

Write heading:
Exercise - 7
Queries on Joins and Correlated Sub-Queries

Joins:
SQL Join Statement combines data or rows from 2 or more tables based on a common field between
them.

INNER JOIN:
Inner join returns only the rows that match in both tables.
Consider two tables Student, Marks for performing all the JOIN operations with data inserted.

Query 1: To show student name, subject, marks.

select s.student_id, [Link], [Link], [Link] from
students s INNER JOIN marks m on s.student_id =
m.student_id;

Query 2: To show students who have Maths marks.

select [Link], [Link] from students s inner join
marks m on s.student_id = m.student_id where
[Link] = 'Maths';

Query 3: To show students scored more than 75.

select [Link], [Link], [Link] from students inner
join marks m on s.student_id = m.student_id where
[Link] > 75;
Page 5 — EQUI JOIN & CROSS JOIN

✦ WHITE SIDE — Write EQUI JOIN + CROSS JOIN Result Tables Here

EQUI JOIN Result (students & marks where student_id matches):

STUDEN NAME CITY STUDEN SUBJECT MARKS
T_ID T_ID
1 Rahul Delhi 1 Maths 85
1 Rahul Delhi 1 Science 90
2 Anita Hyderabad 2 English 78
3 kiran Chennai 3 Maths 88

CROSS JOIN Query 1 Result — sname, bname (sailors x boats — 10+ rows):
SNAME BNAME
brutus interlake
horatio interlake
zorba interlake
horatio interlake
dustin interlake
lubber interlake
andy interlake
rusty interlake
art interlake
bob interlake
Note: More than 10 rows are available (all sailors × all boats)

CROSS JOIN Query 2 Result — bname, sid, day (boats x reserves — 10+ rows):
BNAME SID DAY
interlake 22 10-JULY-98
interlake 22 10-OCT-98
interlake 22 10-OCT-98
interlake 31 11-JUN-98
interlake 64 11-DEC-98
interlake 31 11-OCT-98
 interlake 31 11-OCT-98
interlake 64 09-MAY-98
interlake 74 09-AUG-98
interlake 22 10-AUG-98
Note: More than 10 rows are available

✦ RULED LINES SIDE — Write EQUI JOIN + CROSS JOIN Theory &
Queries Here

EQUI JOIN:
A Join operation that combines rows from two tables when the values in the common column are equal
(=)
Query 1:
select * from students s, marks m where s.student_id
= m.student_id;

CROSS JOIN
In SQL, a CROSS JOIN returns all possible combinations of rows from two tables. It is also called a
Cartesian Product.
Eg: 1) Show every sailor with every boat.
select sname, bname from sailors s cross join
boats b;

2) Show every boat with every reservation record.

select [Link], [Link], [Link] from boats b cross join
reserves r;

OUTER JOIN:
In SQL, an Outer Join is used to retrieve matching rows from two tables and also the non-matching
rows. If there is no match the result shows NULL values.
Page 6 — LEFT OUTER JOIN & RIGHT OUTER JOIN

✦ WHITE SIDE — Write LEFT & RIGHT OUTER JOIN Result Tables Here

Left Outer JOIN Q1 Result — sailors with rating>=7 and their reservations:
SID SNAME RATING BID
31 lubber 8 104
31 lubber 8 102
31 lubber 8 103
74 horatio 9 -
32 andy 8 -
71 zorba 10 -
58 rusty 10 -

Left Outer JOIN Q2 Result — sailors & boats reserved where boat color is red:
SNAME BNAME COLOR
dustin marine red
dustin interlake red
lubber marine red
lubber interlake red
horatio interlake red

Right Outer JOIN Q1 Result — sname, bid, day where bid < 103:
SNAME BID DAY
horatio 102 09-MAY-98
horatio 101 11-DEC-98
dustin 102 10-OCT-98
dustin 101 10-OCT-98
lubber 102 11-OCT-98

Right Outer JOIN Q2 Result — sailors & boats for green boats only:
SNAME BNAME COLOR
horatio clipper green
 dustin clipper green
lubber clipper green

✦ RULED LINES SIDE — Write LEFT & RIGHT OUTER JOIN Theory &
Queries Here

Left Outer Join:

Left outer join returns all rows from the left table and the matching rows from the right table. If there is
no match, right table columns show NULL.
Query 1: Show sailors with rating>=7 and their reservations.
select [Link], [Link], [Link], [Link] from sailors s left
outer join reserves r on [Link] = [Link] where [Link]>=7;

Query 2: Show sailors and boats they reserved only if boat color is red.
select [Link], [Link], [Link] from sailors s left outer
join reserves r on [Link] = [Link] left outer join boats b on
[Link] = [Link] where [Link]='red';

Right Outer Join:

Right Outer join returns all rows from the right table and the matching rows from the left table. If there is
no match, left table columns show NULL.
Query 1: Show reservation records for boats with bid < 103.
select [Link], [Link], [Link] from sailors s right outer
join reserves r on [Link] = [Link] where [Link] < 103;

Query 2: Show boats and sailors for green boats only.

select [Link], [Link], [Link] from sailors s right
outer join reserves r on [Link] = [Link] right outer join
boats b on [Link] = [Link] where [Link]='green';
Page 7 — FULL OUTER JOIN + Nested vs Correlated Query

✦ WHITE SIDE — Write FULL OUTER JOIN Result Tables Here

Full Outer JOIN Q1 Result — sailors & reservations where bid<=102:

SID SNAME BID
64 horatio 102
64 horatio 101
22 dustin 102
22 dustin 101
31 lubber 102

Full Outer JOIN Q2 Result — sname, bname where rating>7 (sailors x boats):
SNAME BNAME
zorba -
horatio -
lubber -
andy -
rusty -

✦ RULED LINES SIDE — Write FULL OUTER JOIN Theory + Nested vs

Correlated Table Here

Full Outer Join:

Returns all rows from both tables, matching or not.
Query 1: Show sailors and reservation records for boats with bid <= 102.
select [Link], [Link], [Link] from sailors s full outer
join reserves r on [Link] = [Link] where [Link]<=102;

Query 2: Show sailors and boats where rating > 7.

select [Link], [Link] from sailors s full outer join
boats b on [Link] = [Link] where [Link]>7;
Nested Query vs Correlated Query:

Nested Query Correlated Query

A query written inside another query and result of A query nested inside another query and inner
inner query is used in execution of outer query. query uses values from outer query.
Uses Bottom-up-approach Uses Top to down approach
Inner query runs first and only once. The outer Outer query executes first and for every outer
query is executed with inner query result. query, inner query is executed.
Inner query execution is not dependent on Outer Inner query is dependent on outer query.
query.
Performs better than correlated query. Performs slower than nested query.
Page 8 — Correlated Sub-Query Results

✦ WHITE SIDE — Write These 5 Sub-Query Result Tables Here

Result 1 — EXISTS (students who have marks recorded):

NAME
Rahul
Anita
kiran

Result 2 — NOT EXISTS (students who do NOT have marks):

NAME
Meena
Arjun

Result 3 — IN (students whose id is in marks table):

NAME
Rahul
Anita
kiran

Result 4 — NOT IN (students whose id is NOT in marks table):

NAME
Meena
Arjun

Result 5 — marks > 80 (students with marks > 80):

NAME
Rahul
Anita

✦ RULED LINES SIDE — Write Correlated Sub-Query Theory + All 5

Queries Here
Correlated Subquery:
It uses a value from the outer query executes once for each row in the outer query.
Types of Correlated Subquery:

1. using EXISTS: Find the students who have marks recorded.

select name from students s where exists(select *
from marks m where s.student_id = m.student_id);

2. using NOT EXISTS: Find the students who do not have marks.
select name from students s where not exists(select *
from marks m where s.student_id = m.student_id);

3. using IN: Find the students whose id is present in marks table.

select name from students where student_id in(
select student_id from marks);

4. using NOT IN: Find the students whose id is not present in marks table.
select name from students where student_id NOT IN(
select student_id from marks);

5. Find students whose marks greater than 80.

select name from students s where student_id = (select
select name from students s where student_id = (select
m.student_id from marks m where s.student_id =
m.student_id AND marks > 80);
Page 9 — Nested Queries Results

✦ WHITE SIDE — Write These Nested Query Result Tables Here

Result 1 — sailors who reserved boat = 103 (sname):

SNAME
dustin
lubber
horatio

Result 2 — sailors who reserved a RED boat (sname):

SNAME
dustin
lubber
horatio

Result 3 — sailors who have NOT reserved a red boat (sname):

SNAME
brutus
zorba
horatio
andy
rusty
art
bob

✦ RULED LINES SIDE — Write Nested Queries Theory + 3 Queries Here

Nested Queries:

1. Find the details of sailors who reserved boat = 103.

select sname from sailors s where [Link] in
(select [Link] from reserves r where [Link]=103);
2. Find the names of sailors who reserved a red boat.
select sname from sailors s where [Link] in
(select [Link] from reserves r where [Link] in(select
[Link] from boat b where [Link]='red'));

3. Find the names of sailors who have NOT reserved a red boat.
select sname from sailors s where [Link] not in
(select [Link] from reserves r where [Link] in(select
[Link] from boats b where [Link]='red'));
 QUICK REFERENCE CHEAT SHEET — PART 4

TCL Commands (Exercise 6)

Command Syntax Purpose
COMMIT SQL> COMMIT; Permanently saves all DML changes
ROLLBACK SQL> ROLLBACK; Undoes all changes since last COMMIT
SAVEPOINT SQL> SAVEPOINT s1; Creates a named restore point
ROLLBACK TO SQL> ROLLBACK TO s1; Rolls back to a specific savepoint

SQL Joins (Exercise 7)

Join Type Syntax Returns
INNER JOIN SELECT ... FROM t1 INNER JOIN t2 ON Only matching rows from both
[Link] = [Link] tables
EQUI JOIN SELECT ... FROM t1, t2 WHERE [Link] = Rows where common column
[Link] values are equal
CROSS JOIN SELECT ... FROM t1 CROSS JOIN t2 All combinations (Cartesian
Product)
LEFT OUTER JOIN SELECT ... FROM t1 LEFT OUTER JOIN All rows from left + matching from
t2 ON ... right (NULL if no match)
RIGHT OUTER SELECT ... FROM t1 RIGHT OUTER All rows from right + matching from
JOIN JOIN t2 ON ... left (NULL if no match)
FULL OUTER JOIN SELECT ... FROM t1 FULL OUTER JOIN All rows from both tables (NULL
t2 ON ... where no match)

Sub-Queries
Type Keyword Example Purpose
Correlated EXISTS Students who have marks: WHERE exists(SELECT * FROM
marks ...)
Correlated NOT EXISTS Students with NO marks: WHERE not exists(SELECT * FROM
marks ...)
Nested IN Students in marks table: WHERE student_id in(SELECT
student_id FROM marks)
Nested NOT IN Students NOT in marks: WHERE student_id NOT IN(SELECT ...)
Nested = (equals) Students with marks > 80: WHERE student_id = (SELECT ...)

— End of DBMS SQL Notes Part 4 —