SQL Table Joins & DDL / DML Review

In relational databases, data is split across multiple normalized tables to prevent data redundancy and anomalies. A Join is an operation that combines records from two or more tables based on a related common column.

---

1. Cartesian Product (Cross Join)

The Cartesian Product of two relations $A$ and $B$ (denoted $A\times B$) is the concatenation of every tuple in relation $A$ with every tuple in relation $B$.

SELECT * FROM Student, Course;
-- Or explicitly:
SELECT * FROM Student CROSS JOIN Course;

Mathematical Properties:

If relation $A$ has Degree $D_1$ and Cardinality $C_1$, and relation $B$ has Degree $D_2$ and Cardinality $C_2$:

$$\text{Degree}(A\times B)=D_1+D_2$$$$\text{Cardinality}(A\times B)=C_1\times C_2$$

NOTE

If Student has 4 columns and 10 rows, and Course has 3 columns and 5 rows, the Cartesian Product will have:

• $\text{Degree}=4+3=7\text{ columns}$
• $\text{Cardinality}=10\times 5=50\text{ rows}$

---

2. Equi-Join and Natural Join

2.1 Equi-Join

An Equi-Join is a join condition that specifies an equality comparison (=) on the common key attribute(s) between the two tables.

Syntax

SELECT table1.col1, table2.col2, ...
FROM table1, table2
WHERE table1.common_col = table2.common_col;

Using Table Aliases

Aliases provide concise, readable shorthand for table names:

SELECT S.RollNo, S.Name, C.CourseName, C.Fee
FROM Student S, Course C
WHERE S.CourseId = C.CourseId;

---

2.2 Natural Join

A Natural Join is a special type of Equi-Join where the join occurs automatically on all attributes that share the same name and domain across both tables, and the duplicate common column is eliminated from the result set.

SELECT * FROM Student NATURAL JOIN Course;

$$\text{Degree}(\text{Natural Join})=D_1+D_2-K(\text{where }K\text{ is number of common attributes})$$

---

3. Two-Table Query Walkthrough

Consider two relational tables:

Table DOCTOR ($D_1=4,C_1=4$)

DocID	DocName	Department	OPD_Fee
D101	Dr. Verma	Cardiology	800
D102	Dr. Sharma	Neurology	1000
D103	Dr. Patel	Pediatrics	600
D104	Dr. Sen	Cardiology	900

Table PATIENT ($D_2=4,C_2=5$)

PCode	PatientName	DocID	AdmitDate
P01	Rohan	D101	2026-02-10
P02	Tanya	D102	2026-02-12
P03	Kabir	D101	2026-02-14
P04	Aanya	D103	2026-02-15
P05	Meera	D101	2026-02-18

---

Sample Board Queries on DOCTOR and PATIENT:

Query 1: Display Patient Name, Doctor Name, and Department for all patients.

SELECT P.PatientName, D.DocName, D.Department
FROM Patient P, Doctor D
WHERE P.DocID = D.DocID;

Query 2: Display Patient Name and OPD Fee for patients admitted under the 'Cardiology' department.

SELECT P.PatientName, D.OPD_Fee
FROM Patient P, Doctor D
WHERE P.DocID = D.DocID AND D.Department = 'Cardiology';

Query 3: Display Doctor Name and total number of patients admitted under each doctor.

SELECT D.DocName, COUNT(P.PCode) AS TotalPatients
FROM Doctor D, Patient P
WHERE D.DocID = P.DocID
GROUP BY D.DocName;

---

4. DDL vs DML Review

SQL statements are divided into categorized sub-languages based on their functional purpose:

graph TD
    SQL["SQL Commands"] --> DDL["DDL (Data Definition Language)<br><i>Schema & Structure</i>"]
    SQL --> DML["DML (Data Manipulation Language)<br><i>Row Data & Content</i>"]
    SQL --> TCL["TCL (Transaction Control)<br><i>COMMIT, ROLLBACK</i>"]
    
    DDL --> C["CREATE TABLE"]
    DDL --> A["ALTER TABLE"]
    DDL --> D["DROP TABLE"]
    
    DML --> S["SELECT"]
    DML --> I["INSERT INTO"]
    DML --> U["UPDATE ... SET"]
    DML --> DEL["DELETE FROM"]

Essential DDL & DML Commands Cheat Sheet

Command Type	Command	Purpose	Example Syntax
DDL	CREATE TABLE	Defines new table structure	CREATE TABLE Item (ItemId INT PRIMARY KEY, Name VARCHAR(30), Price DECIMAL(8,2));
DDL	ALTER TABLE ... ADD	Adds a new column	ALTER TABLE Item ADD Category VARCHAR(20);
DDL	ALTER TABLE ... MODIFY	Modifies column data type	ALTER TABLE Item MODIFY Name VARCHAR(50);
DDL	ALTER TABLE ... DROP	Removes an existing column	ALTER TABLE Item DROP COLUMN Category;
DDL	DROP TABLE	Deletes entire table and structure	DROP TABLE Item;
DML	INSERT INTO	Adds new row(s)	INSERT INTO Item VALUES (101, 'Mouse', 450.00);
DML	UPDATE ... SET	Modifies existing data	UPDATE Item SET Price = Price * 1.10 WHERE ItemId = 101;
DML	DELETE FROM	Deletes row(s)	DELETE FROM Item WHERE ItemId = 101;

---

5. Board Exam Traps: DELETE vs DROP

CAUTION

• DELETE FROM TableName; $\to$ DML Command: Deletes all data rows from the table, but the table schema/structure remains intact.
• DROP TABLE TableName; $\to$ DDL Command: Permanently removes both the table schema definition AND all stored data from the database.
• ALTER TABLE TableName DROP COLUMN Col; $\to$ DDL Command: Removes an attribute column from the table definition.

---

6. Board Examination Join Drills

Question 1 (CBSE 2023 Paper)

Consider two tables STAFF with 5 columns and 8 rows, and SALARY_GRADE with 3 columns and 4 rows.

1. What will be the Degree and Cardinality of the Cartesian product of STAFF and SALARY_GRADE?
2. What will be the Degree of an Equi-join on Grade?

Answers:

1. $\text{Degree}=5+3=\mathbf{8}$, $\text{Cardinality}=8\times 4=\mathbf{32}$.
2. In an Equi-join where columns are preserved or referenced, $\text{Degree}=5+3=\mathbf{8}$. (If Natural Join, $\text{Degree}=8-1=\mathbf{7}$).