Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

dataBase Joins with examples, Cheat Sheet of Database Programming

Lovely Professional UniversityDatabase Programming

It contains all the required files

Typology: Cheat Sheet

2022/2023

Uploaded on 11/28/2025

krishi-hub
krishi-hub 🇮🇳

1 document

1 / 5

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Database Joins, Views & Transactional Control:
Comprehensive Notes
1. Database Joins: Concept & Need
What is a Database Join?
A database join is an operation that combines rows from two or more tables based on a related column
(typically a key) between them. This allows retrieval of data scattered across multiple tables in a single, unified
result set.
Why are Joins Needed?
Data normalization spreads data across tables to remove redundancy; joins reconstruct full views for
business needs.
Helps analyze related data in one result (e.g., merging employee and department details).
Enables queries across entities connected by logical relationships.
Example Diagram
[Employees Table] [Departments Table]
+----+--------+ +-----+-----------+
| ID | Name | | D_ID| DeptName |
+----+--------+ +-----+-----------+
| 1 | Alice | | 10 | Accounts |
| 2 | Bob | | 20 | HR |
+----+--------+ +-----+-----------+
Join on Employees.D_ID = Departments.D_ID
2. Types of Database Joins
2.1 Inner Join
Definition: Returns rows when there is a match in both tables.
Syntax Example:
pf3
pf4
pf5

Partial preview of the text

Download dataBase Joins with examples and more Cheat Sheet Database Programming in PDF only on Docsity!

Database Joins, Views & Transactional Control:

Comprehensive Notes

1. Database Joins: Concept & Need

What is a Database Join?

A database join is an operation that combines rows from two or more tables based on a related column

(typically a key) between them. This allows retrieval of data scattered across multiple tables in a single, unified

result set.

Why are Joins Needed?

 Data normalization spreads data across tables to remove redundancy; joins reconstruct full views for

business needs.

 Helps analyze related data in one result (e.g., merging employee and department details).

 Enables queries across entities connected by logical relationships.

Example Diagram

[Employees Table] [Departments Table] +----+--------+ +-----+-----------+ | ID | Name | | D_ID| DeptName | +----+--------+ +-----+-----------+ | 1 | Alice | | 10 | Accounts | | 2 | Bob | | 20 | HR | +----+--------+ +-----+-----------+ Join on Employees.D_ID = Departments.D_ID

2. Types of Database Joins

2.1 Inner Join

Definition: Returns rows when there is a match in both tables.

Syntax Example:

SELECT E.Name, D.DeptName FROM Employees E INNER JOIN Departments D ON E.D_ID = D.D_ID;

Result:

Only employees with a matching department appear in the output

2.2 Left Join (Left Outer Join)

Definition: Returns all rows from the left table, and matching rows from the right table. If no match, right table

columns are NULL.

Syntax Example:

SELECT E.Name, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.D_ID = D.D_ID;

Result:

All employees listed; if an employee lacks a department, DeptName will be NULL.

2.3 Right Join (Right Outer Join)

Definition: Returns all rows from the right table, and matching rows from the left table. If no match, left table

columns will be NULL.

Syntax Example:

SELECT E.Name, D.DeptName FROM Employees E RIGHT JOIN Departments D ON E.D_ID = D.D_ID;

Result:

All departments are listed; if a department lacks an employee, employee columns will be NULL.

2.4 Self Join

Definition: A table is joined with itself to compare rows within the same table.

Syntax Example:

Suppose an Employees table with a Manager_ID field:

SELECT * FROM HR_Employees;

4. Transactional Control Transactions in Databases

A transaction is a single logical unit of work that may contain multiple operations. Transactions ensure the

integrity of the database even in the presence of errors or failures

ACID Properties:

 Atomicity: All operations succeed or none do.

 Consistency: Data moves from one valid state to another.

 Isolation: Transactions do not interfere with each other.

 Durability: Changes are permanent once transaction completes.

Transaction Control Statements Command Description BEGIN Start a transaction COMMIT Save all changes made in the transaction ROLLBACK Undo all changes since transaction began SAVEPOINT Set a point within a transaction to roll back to

Example Scenario:

Transfer ₹100 from Account A to B:

1. BEGIN TRANSACTION

2. Deduct from A

3. Add to B

4. If both succeed, COMMIT; else, ROLLBACK any partial update

Sample SQL:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; Summary Table: Joins and Views Concept Key Point Example SQL/Diagram Inner Join Only matching rows from both tables ...INNER JOIN...ON... Left Join All rows left table, match or NULL from right (^) ...LEFT JOIN...ON... Right Join All rows right table, match or NULL from left (^) ...RIGHT JOIN...ON... Self Join Join table with itself (^) FROM T1 INNER JOIN T1 T ... View Virtual table built from query (^) CREATE VIEW view_name AS SELECT ... Transaction Logical unit of work; ACID properties BEGIN; ... COMMIT;

Note: Mastery of joins, views, and transactions is essential for robust, efficient, and maintainable relational

database design.