1. List the differences between file system and DBMS.

Basis File System DBMS

1. Definition A method used by operating systems to A software system that manages databases to
store and organize files on storage store, retrieve, and manipulate data efficiently.
devices.
2. Data Handling Data is stored in files and manually Data is stored in tables and automatically
managed. managed.
3. Data High chances of duplicate data. Minimizes redundancy using normalization
Redundancy and relationships.
4. Data Integrity Difficult to maintain data accuracy. Ensures data integrity through constraints
(e.g., primary key).
5. Data Security Limited security (basic file permissions). High-level security with authentication,
encryption, and access control.
6. Querying & No built-in query language. Supports SQL for powerful queries and
Reporting reports.
7. Backup & Manual backup and recovery. Automatic backup and crash recovery features.
Recovery
8. Concurrency Difficult to manage multiple users Manages concurrent access efficiently.
accessing data.

2. Define relational algebra and list its basic operations.

 Relational Algebra is called a procedural query language.

 Here we must describe how to get the result, step by step, using operations.
 Uses operations like: SELECT, PROJECT, JOIN, UNION, INTERSECTION

Operators:
 σ (Selection) → filter rows

 ∪ (Union) → combine results

 π (Projection) → pick columns

 − (Difference) → subtract sets

 ⨝ (Join) → combine related rows

 × (Cartesian product) → combine all pairs

Example:
Students
I Name Marks
D
1 Ram 85
2 Priya 78
3 Kiran 90
To get the names of students with marks > 80.
In Relational Algebra (procedural way):
π_Name (σ_Marks>80 (Students))

Table: Students_A Table: Students_B

ID Name
1 Ram
2 Priya
3 Kiran

1. Union ( ∪ )

Relational Algebra: Students_A ∪ Students_B

Combines all rows from both tables without duplicates.

I Name
D
1 Ram
2 Priya
 3 Kiran
4 Anu
5 John
2. Difference ( − )
Returns rows in the first table that are not in the second.
Relational Algebra: Students_A − Students_B
ID Name
1 Ram
2 Priya

3. Cartesian Product ( × )
Combines every row of one table with every row of another.
Let’s say:
Table: Courses
C_ID Course
C1 Math
C2 Science
Relational Algebra: Students_A × Courses
ID Name C_ID Course
1 Ram C1 Math
1 Ram C2 Science
2 Priya C1 Math
2 Priya C2 Science
3 Kiran C1 Math
3 Kiran C2 Science

4. Join (⨝)
Combines two tables based on common attribute(s).
Let’s say:
Table: Marks
ID Marks
1 85
2 78

Relational Algebra (Natural Join): Students_A ⨝ Marks

3 90

ID Name Marks
1 Ram 85
2 Priya 78
3 Kiran 90

3. What is data models and classify different types with examples.

A data model defines how data is stored, connected, accessed, and updated in a database system.

Types of Data Models in DBMS:

1. Hierarchical Data Model
 Data is organized in a tree-like structure.
 Each child has only one parent, but a parent can have multiple children.
Example:
University
├── Department
├── Faculty
└── Students
Like folders in a file system.

2. Network Data Model

 Similar to hierarchical, but here a child can have multiple parents (many-to-many relationships).
 Uses pointers to represent relationships.
 Example:
Course
├── Student1
└── Student2
Student1
 ├── Course1
└── Course2
Students enroll in many courses; courses have many students.
3. Relational Data Model (Most popular)
 Data is stored in tables (relations).
 Tables are linked using keys (primary key, foreign key).
 Uses SQL for querying.
Example:
Student Table
student_i name dept_id
d
101 Ravi 1
Department Table
dept_i dept_name
d
1 Computer Sci.
Here, dept_id is used to relate both tables.

4. Entity-Relationship (ER) Model

 Graphical model using:
o Entities (e.g., Student, Course)
o Attributes (e.g., name, ID)
o Relationships (e.g., Enrolls)
Example:

Usually the first step in database design.

5. Object-Oriented Data Model

 Data is stored as objects, similar to OOP (like Java or C++).
 Supports inheritance, encapsulation, abstraction and polymorphism.
Example:
class Student
{
int student_id;
String name;
}

4. Explain the various types of SQL constraints with suitable syntax and examples.

 SQL Constraints are rules applied to table columns to enforce data integrity, consistency, and accuracy in a relational
database.
 They ensure that only valid data is stored in the database.

Types of SQL Constraints:

1. NOT NULL Constraint

 Ensures that a column cannot have NULL values.
 Used when a column must always contain a value.
Syntax:
CREATE TABLE Students (
student_id INT NOT NULL,
name VARCHAR(50) NOT NULL
);

Example:
INSERT INTO Students (student_id, name)
VALUES (101, 'Ravi'); -- ✅ valid
INSERT INTO Students (student_id, name)
VALUES (102, NULL); -- ❌ Error: name cannot be null

2. UNIQUE Constraint
 Ensures that all values in a column are unique (no duplicates).
 A table can have multiple UNIQUE constraints.
Syntax:
CREATE TABLE Users (
user_id INT,
email VARCHAR(100) UNIQUE
);
Example:
INSERT INTO Users (user_id, email)
VALUES (1, 'a@[Link]'); -- ✅

INSERT INTO Users (user_id, email)

VALUES (2, 'a@[Link]'); -- ❌ Error: duplicate email

3. PRIMARY KEY Constraint

 Combines NOT NULL and UNIQUE.
 Each table can have only one primary key.
 Used to uniquely identify a record in a table.
Syntax:
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50)
);
Example:
INSERT INTO Employees (emp_id, name)
VALUES (101, 'John'); -- ✅

INSERT INTO Employees (emp_id, name)

VALUES (101, 'Mike'); -- ❌ Error: Duplicate emp_id

4. FOREIGN KEY Constraint

 Establishes a relationship between two tables.
 The foreign key in one table points to the primary key in another table.
Syntax:
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Staff (

staff_id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
Example:
INSERT INTO Staff (staff_id, name, dept_id)
VALUES (1, 'Sara', 101); -- ❌ Error if dept_id 101 doesn’t exist in Departments

5. CHECK Constraint
 Ensures that values in a column satisfy a specific condition.
Syntax:
CREATE TABLE Products (
product_id INT PRIMARY KEY,
price DECIMAL(10, 2) CHECK (price > 0)
);
Example:
INSERT INTO Products (product_id, price)
VALUES (1, 250.00); -- ✅

INSERT INTO Products (product_id, price)

VALUES (2, -10.00); -- ❌ Error: price must be > 0

6. DEFAULT Constraint
 Provides a default value for a column if no value is specified during insertion.
Syntax:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'Pending'
);
Example:
INSERT INTO Orders (order_id)
VALUES (1001); -- status will be 'Pending' automatically

7. AUTO INCREMENT / IDENTITY

 Automatically generates a unique number for each new record.
Syntax:
CREATE TABLE Customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
Example:
CREATE TABLE Customers (
customer_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50)
);

5. Summarize the role of COMMIT and ROLLBACK in transaction control.

In SQL, COMMIT and ROLLBACK are commands under Transaction Control Language (TCL). They
are used to manage transactions.
 A transaction begins when a DML operation like INSERT, UPDATE, or DELETE is performed.
 These changes are not permanently saved until a COMMIT command is issued.
 If there's any failure or if the user wants to cancel the transaction, ROLLBACK can be used to revert
all the changes made during that transaction.

Role in Transaction Control:

The main role of COMMIT and ROLLBACK is to ensure data consistency and integrity during
transaction processing in a database.
 COMMIT:
o Once the operations in a transaction are successfully executed and verified, COMMIT is
issued.
o It makes all the changes permanent in the database.
o After a COMMIT, the transaction is considered complete and cannot be reversed.
o It is useful when the data modifications are valid and need to be stored.
 ROLLBACK:
o If an error occurs, or the transaction needs to be aborted, ROLLBACK is used.
o It undoes all changes made in the current transaction, reverting the database to its previous
consistent state.
o This ensures that partial or incorrect data is not saved, maintaining the integrity of the
database.
Example of COMMIT:

BEGIN;

INSERT INTO Employees (emp_id, name, department)

VALUES (201, 'Anjali', 'HR');

UPDATE Employees
SET department = 'Human Resources'
WHERE emp_id = 201;

COMMIT;

 The transaction begins with the INSERT and UPDATE statements.

 Once you verify that both operations are successful and correct, you use COMMIT.
 All changes (insertion and update) are now permanently saved in the database.

Example of ROLLBACK:

BEGIN;

UPDATE Salaries
SET salary = salary + 5000
WHERE emp_id = 301;

-- You realize you selected the wrong employee or table

ROLLBACK;

 The update increases the salary, but before committing, you realize there’s a mistake.
 By using ROLLBACK, the update is cancelled, and the database returns to its original state.
 No change is saved since the transaction was not committed.

6. Identify four basic SQL data types and explain their usage.

In SQL, data types define the kind of value a column can store. Choosing appropriate data types ensures
proper storage, validation, and processing of data. The four main types are Numeric, Character/String, Date
and Time, and Boolean.

1. Numeric Data Types

These are used to store numeric values, including whole numbers and decimals.

Data Type Description Example

INT / INTEGER Whole numbers INT → 123
SMALLINT Small-range integers SMALLINT → 1000
BIGINT Very large integers BIGINT → 9223372036854775807
DECIMAL(p, s) Fixed-point numbers with precision DECIMAL(5,2) → 123.45
FLOAT Approx. floating-point number FLOAT → 12.34
REAL Lower precision float REAL → 1.23
DOUBLE Higher precision float DOUBLE PRECISION → 123.456
 PRECISION

Usage: Useful in calculations, financial data, measurements, and any field involving numeric operations.
CREATE TABLE Products (
product_id INT,
price DECIMAL(6,2),
rating FLOAT
);

2. Character/String Data Types

These are used to store text or alphanumeric values.

Data Type Description Example

CHAR(n) Fixed-length string CHAR(5) → 'ABC '
VARCHAR(n) Variable-length string VARCHAR(50) → 'Hello'
TEXT Long-form text 'This is a long text...'

Usage: Ideal for storing user names, descriptions, addresses, messages, etc.
CREATE TABLE Customers (
customer_id INT,
name VARCHAR(100),
feedback TEXT
);

3. Date and Time Data Types

Used for storing dates, times, and timestamps.

Data Type Description Example

DATE Stores only the date '2025-07-28'
TIME Stores only the time '[Link]'
DATETIME Stores date and time '2025-07-28 [Link]'
TIMESTAMP Date and time with time zone '2025-07-28 [Link]+00:00'
YEAR Year only (MySQL specific) '2025'

Usage: Crucial for recording transactions, logging events, tracking user activity, and scheduling tasks.
CREATE TABLE Events (
event_id INT,
event_date DATE,
start_time TIME,
created_at TIMESTAMP
);

4. Boolean Data Type

Used to store true or false values.

Data Type Description Example

BOOLEAN Stores TRUE or TRUE, FALSE
FALSE

Usage: Often used in conditions, status flags, or any field requiring a binary choice (e.g., is_active,
is_deleted).
CREATE TABLE Tasks (
task_id INT,
description VARCHAR(255),
is_completed BOOLEAN
);

7. Explain the use of SQL aggregate functions like COUNT, AVG, and SUM with examples.

In SQL, aggregate functions are built-in functions used to perform calculations on multiple rows of a table and return a single
summary value.

These functions are commonly used in reporting, analytics, and data summarization, often alongside the GROUP BY clause.

Among the most commonly used aggregate functions are:

 COUNT() – to count records
 AVG() – to find average
 SUM() – to calculate totals

These functions ignore NULL values (except COUNT(*)), and are useful in extracting meaningful insights from raw data.

1. COUNT() – Counting rows or values

Definition:
The COUNT() function returns the number of non-null values in a specified column. If you use COUNT(*), it counts all rows,
including those with NULLs.

Usage Scenarios:
 Count total number of employees
 Count how many orders were placed

Syntax:
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;

Example:
SELECT COUNT(*) FROM Employees;

2. AVG() – Calculating average value

Definition:
The AVG() function returns the average (arithmetic mean) of a numeric column. It adds all values and divides by the count of
non-null values.

Usage Scenarios:
 Calculate the average salary of employees
 Find the average marks of students

Syntax:
SELECT AVG(column_name) FROM table_name;

Example:
SELECT AVG(salary) FROM Employees;

3. SUM() – Calculating total value

Definition:
The SUM() function adds up all values in a numeric column and returns the total sum.

Usage Scenarios:
 Calculate total income or expenses
  Get total salary paid

Syntax:
SELECT SUM(column_name) FROM table_name;

Example:
SELECT SUM(salary) FROM Employees;

4. Usage with GROUP BY (for category-wise summary)

DEFINITION:
Aggregate functions are frequently used with the GROUP BY clause to calculate results per group/category.

Example:
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department;

8. Differentiate between GRANT and REVOKE commands with examples

Aspect GRANT REVOKE

Definition Gives specific privileges to a user or role. Removes previously given privileges from a
user or role.
Purpose Used to assign rights like SELECT, Used to take back rights that were earlier
INSERT, UPDATE, etc. granted.
Effect Enables users to perform specific actions on Disables users from performing those specific
a database object. actions.
Syntax GRANT privilege ON object TO user; REVOKE privilege ON object FROM user;
Example GRANT SELECT ON Employees TO John; REVOKE SELECT ON Employees FROM
John;
Privilege Scope Can be granted on tables, views, procedures, Can revoke privileges from any object
or entire databases. previously granted.
With GRANT You can allow a user to pass privileges to Revokes privileges, and if GRANT OPTION
OPTION others. was used, removes it too.
Usage Scenario Used by DBAs/admins to allow users access Used when access must be restricted due to
to data for their tasks. policy or misuse.

1. Explain in detail the architecture of DBMS

 Database Management System (DBMS) Architecture refers to the design structure of a database system that outlines how
data is stored, accessed, and managed.
 It helps organize components in a systematic way to ensure data security, efficiency, and integrity.
 DBMS architecture can be broadly divided into 1-tier, 2-tier, and 3-tier architectures.

Levels of DBMS Architecture

A Database Management System (DBMS) is organised into three main levels: the Internal Level, the Conceptual Level, and the
External Level.
1. Internal Level
o Lowest level of abstraction.
o Describes how data is actually stored in memory, including file structures, indexes, and compression.
o Managed by the DBMS storage engine.
o Concerned with performance, storage optimization, and access methods.
o Example: Data stored as blocks on a hard disk, using B+ Trees or hash indexing.

2. Conceptual Level:
 o Middle level that describes what data is stored in the database and the relationships among them.
o Independent of physical storage.
o Contains all entities, attributes, constraints, and relationships.
o Managed by the database administrator (DBA).
o Example: A table Student with attributes: Roll_No, Name, Dept_ID.

3. External Level:

o Highest level of abstraction.

o Describes how users interact with data via different views.
o Each user may see only part of the data, depending on permissions.
o Example: A professor might only access students' names and grades, not their contact information.

1-Tier Architecture
 In 1-Tier Architecture, the user works directly with the database on the same system.
 This means the client, server and database are all in one application.
 The user can open the application, interact with the data and perform tasks without needing a separate server or network
connection.
 A common example is Microsoft Excel. Everything from the user interface to the logic and data storage happens on the
same device. The user enters data, performs calculations and saves files directly on their computer.
 This setup is simple and easy to use, making it ideal for personal or standalone applications. It does not require a network
or complex setup, which is why it's often used in small-scale or individual use cases.
 This architecture is simple and works well for personal, standalone applications where no external server or network
connection is needed.

2-Tier Architecture
 The 2-tier architecture is similar to a basic client-server model.
 The application at the client end directly communicates with the database on the server side.
 APIs like ODBC and JDBC are used for this interaction.
 The server side is responsible for providing query processing and transaction management functionalities.
 On the client side, the user interfaces and application programs are run.
 The application on the client side establishes a connection with the server side to communicate with the DBMS.
For Example: A Library Management System used in schools or small organizations is a classic example of two-tier
architecture.
 Client Layer (Tier 1): This is the user interface that library staff or users interact with. For example they might use a
desktop application to search for books, issue them, or check due dates.
 Database Layer (Tier 2): The database server stores all the library records such as book details, user information and
transaction logs.
The client layer sends a request (like searching for a book) to the database layer which processes it and sends back the result. This
separation allows the client to focus on the user interface, while the server handles data storage and retrieval.
3-Tier Architecture
 In 3-Tier Architecture, there is another layer between the client and the server.
 The client does not directly communicate with the server.
 Instead, it interacts with an application server which further communicates with the database system and then the query
processing and transaction management takes place.
 This intermediate layer acts as a medium for the exchange of partially processed data between the server and the client.
 This type of architecture is used in the case of large web applications.
Example: E-commerce Store
 User: You visit an online store, search for a product and add it to your cart.
 Processing: The system checks if the product is in stock, calculates the total price and applies any discounts.
 Database: The product details, your cart and order history are stored in the database for future reference.

2. Construct an ER diagram for a student-course registration system and interpret its components.
3. Draw an Entity-Relationship (ER) diagram for a Library Management System. Identify entities,
relationships, attributes, and keys.

1. Entities and Their Attributes: Entities are the main objects in the system about which data is stored. In this ER
diagram, we identify the following entities:
1. Books
 Represents the collection of books in the library.
 Attributes:
o Book_ID (Primary Key): Uniquely identifies each book.
o Title: Name of the book.
o Author: Author’s name.
o Price: Cost of the book.
o Available: Indicates if the book is available for borrowing.
 Note: This entity is central to most relationships in the system.

2. Publisher
 Represents the publisher who published the book.
 Attributes:
o Pub_ID (Primary Key): Unique ID for each publisher.
o Name: Publisher name.
o Address: Publisher address.

3. Member
 Represents library members who borrow books.
 Attributes:
o Member_ID (Primary Key): Unique ID for each member.
o Name: Member’s name.
o Address: Member’s residential address.
o Member_type: E.g., student, faculty.
o Member_date: Date of membership.
o Expiry_date: Membership expiry date.

2. Relationships and Their Attributes

Relationships describe how entities interact with each other. Here are the identified relationships:

1. Published by
 A many-to-one (M:N) relationship between Books and Publisher.
 A book is published by one publisher, but a publisher can publish many books.
 Participation:
o Book: Total participation (each book must be published).
o Publisher: Partial participation (not all publishers may have books in the system).

2. Borrowed by
 A many-to-many (M:N) relationship between Books and Members.
 A member can borrow multiple books, and a book can be borrowed by multiple members over time.
 Attributes of this relationship:
o Issue: Issue date of the book.
o Due date: Date when the book is due.
o Return date: When the book was returned.
This relationship likely represents a transaction log or history of borrowed books.

3. Keys (Primary and Foreign)

Primary Keys (Uniquely identify entities):

 Book_ID – in Books
 Pub_ID – in Publisher
 Member_ID – in Member

Foreign Keys (Used in relationships):
 Book_ID and Member_ID – used in Borrowed by to link transactions.
  Pub_ID – used in Published by to associate a book with its publisher.

4. Explain the categories of SQL commands—DDL, DML, TCL, and DCL—with syntax and
examples.

SQL (Structured Query Language) commands are the core instructions used to communicate with a
Database Management System (DBMS). These commands perform a wide range of operations on the
database structure and stored data.

1. DDL – Data Definition Language

DDL commands are used to define and manage the structure or schema of database objects like tables,
views, schemas, and indexes.

Key Characteristics:
 Affects the structure (not data) of the database.
 Changes are auto-committed (you cannot roll them back).
 Mostly used during database design or maintenance.

Common DDL Commands:

 Command Description Syntax
CREATE Creates a new object (table, view, CREATE TABLE table_name (col1 TYPE, col2
etc.) TYPE);
DROP Deletes objects from the database DROP TABLE table_name;
ALTER Modifies structure of a table ALTER TABLE table_name ADD column_name
TYPE;
TRUNCATE Removes all records from a table TRUNCATE TABLE table_name;
RENAME Renames an object RENAME TABLE old_name TO new_name;

Example:

-- Create a table named Student

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100)
);

-- Add a new column to Student table

ALTER TABLE Student ADD Age INT;

-- Remove all rows from the Student table (but keep structure)
TRUNCATE TABLE Student;

-- Delete the entire Student table

DROP TABLE Student;

2. DML – Data Manipulation Language

DML is used for managing the data inside database tables—adding, updating, deleting, and retrieving
records.

Key Characteristics:
 Works on data, not structure.
 Changes are not auto-committed (can be rolled back).
 Frequently used by developers and data analysts.
 Forms the core of day-to-day operations on the database.

Common DML Commands:

Command Description Syntax

INSERT Adds new data INSERT INTO table (col1, col2) VALUES (val1, val2);
UPDATE Modifies data UPDATE table SET col = val WHERE condition;
DELETE Removes data DELETE FROM table WHERE condition;

Example:
-- Insert a new student
INSERT INTO Student (StudentID, Name, Age, Email)
VALUES (1, 'Diya', 21, 'diya@[Link]');

-- Update a student's name

UPDATE Student
SET Name = 'Kavya'
WHERE StudentID = 1;

-- Delete a student
DELETE FROM Student
WHERE StudentID = 1;

-- Retrieve all student records

SELECT * FROM Student;

✅ 3. TCL – Transaction Control Language

🔍 What It Does:
TCL manages transactions, which are groups of operations executed as a single unit of work. This ensures
data integrity and consistency, especially in multi-user environments.
Key Characteristics:
 Works with DML commands.
 Controls how and when changes are saved.
 Helps maintain ACID properties (Atomicity, Consistency, Isolation, Durability).

Common TCL Commands:

Command Description Syntax

COMMIT Saves all changes permanently COMMIT;
ROLLBACK Undoes changes since the last commit ROLLBACK;
SAVEPOINT Sets a point to which a rollback can occur SAVEPOINT sp1;

Example:
-- Insert a record
INSERT INTO Student (StudentID, Name) VALUES (2, 'Ankit');

-- Set a savepoint
SAVEPOINT sp1;

-- Update record
UPDATE Student SET Name = 'Ankit Kumar' WHERE StudentID = 2;

-- Rollback to savepoint
ROLLBACK TO sp1;

-- Commit final changes

COMMIT;

4. DCL – Data Control Language

🔍 What It Does:
DCL controls user access to data and permissions on database objects. It plays a crucial role in data
security and authorization.

Key Characteristics:
 Used to grant or revoke privileges on database objects.
 Ensures that only authorized users can access or manipulate data.
  Commonly used by DBAs (Database Administrators).

Common DCL Commands:

Command Description Syntax

GRANT Assigns privileges GRANT SELECT, INSERT ON table TO user;
REVOKE Removes privileges REVOKE INSERT ON table FROM user;

Example:
-- Grant SELECT and INSERT on Student table to user1
GRANT SELECT, INSERT ON Student TO user1;

-- Revoke INSERT permission from user1

REVOKE INSERT ON Student FROM user1;

5. Describe different types of SQL joins with diagrams and examples using two tables.

Joins are used to combine rows from two or more tables based on a related column between [Link] help retrieve related
data stored across multiple tables due to normalization.

1. INNER JOIN
 They are also referred to as an EQUIJOIN.
 The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon
the join-predicate.
 The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.
 Combines rows only where matching values exist in both tables.
 Returns only the rows where there is a match in both tables.
  Unmatched rows are excluded.
 Most commonly used join in applications.

Syntax

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_filed = table2.common_field;

EXAMPLE

Student Table

StudentID Name CourseID

1 Diya 101
2 Kavy 102
a
3 Surya NULL
Course Table

CourseI CourseName
D
101 Math
102 Science
103 History

SELECT [Link], [Link]

FROM Student

INNER JOIN Course ON [Link] = [Link];

OUTPUT

Name CourseNam
e
Diya Math
Kavy Science
a

2. LEFT JOIN (LEFT OUTER JOIN)

 The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.
 This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but
with NULL in each column from right table.
 This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in
case of no matching join predicate.

Syntax:

SELECT table1.column1, table2.column2...

FROM table1

LEFT JOIN table2

ON table1.common_filed = table2.common_field;

Example:

SELECT [Link], [Link]

FROM Student

LEFT JOIN Course

ON [Link] = [Link];

Output:

Name CourseNam
e
Diya Math
Kavy Science
a
Surya NULL

3. RIGHT JOIN (RIGHT OUTER JOIN)

 The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.
 This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but
with NULL in each column from left table.
 This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in
case of no matching join predicate.

Syntax:

SELECT table1.column1, table2.column2...

FROM table1

RIGHT JOIN table2 ON table1.common_filed = table2.common_field;

Example:

SELECT [Link], [Link]

FROM Student

RIGHT JOIN Course

ON [Link] = [Link];

Output:

Name CourseNam
e
Diya Math
Kavya Science
NULL History

4. FULL JOIN (FULL OUTER JOIN)

 The SQL FULL JOIN combines the results of both left and right outer joins.
 The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
 Returns all rows from both tables.
 Where no match exists, NULLs are filled.

Syntax:

SELECT table1.column1, table2.column2...

FROM table1

FULL JOIN table2 ON table1.common_filed = table2.common_field;

Example:

SELECT [Link], [Link]

FROM Student

FULL JOIN Course

ON [Link] = [Link];

Output:

Name CourseNam
e
Diya Math
Kavya Science
Surya NULL
NULL History

5. SELF JOIN

 The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one
table in the SQL statement.
 Joins a table with itself using aliases.
 Useful for comparing rows within the same table (e.g., employee-manager).
 Needs condition to avoid self-pairing and duplicate results.

Syntax:

SELECT a.column_name, b.column_name...

FROM table1 a, table1 b

WHERE a.common_filed = b.common_field;

Example:

To demonstrate self join, let’s slightly modify the Student table to include a column like MentorID to indicate one student is
mentored by another student (i.e., a relationship within the same table).

Student

StudentID Name CourseID MentorID

1 Diya 101 NULL

2 Kavy 102 1
a

3 Surya NULL 1

SELECT [Link] AS Student, [Link] AS Mentor

FROM Student S1

JOIN Student S2

ON [Link] = [Link];

Output:

Studen Mentor
t

Kavya Diya

Surya Diya

6. CARTESIAN JOIN (CROSS JOIN)

 Returns the cross product of both tables: every row in Student is paired with every row in Course.
  The CARTESIAN JOIN or CROSS JOIN returns the cartesian product of the sets of records from the two or more joined
tables.
 Thus, it equates to an inner join where the join-condition always evaluates to True or where the joincondition is absent
from the statement.
 Produces all possible combinations of rows from two tables.

Syntax:

SELECT table [Link] 1, table [Link] 2

FROM table 1

CROSS JOIN table 2;

Example:

SELECT [Link], [Link]

FROM Student

CROSS JOIN Course;

Output:

3 Students × 3 Courses = 9 Rows

Name CourseNam
e
Diya Math
Diya Science
Diya History
Kavy Math
a
Kavy Science
a
Kavy History
a
Surya Math
Surya Science
Surya History

6. Explain the structure of a basic SQL query and illustrate how clauses like WHERE, GROUP BY,
and HAVING are used.

 SQL (Structured Query Language) allows users to interact with databases to retrieve, manipulate, and analyze data.
 A basic SQL query typically consists of several clauses arranged in a specific order.
 Each clause plays a unique role in processing and filtering data.

Basic Structure of an SQL Query:

SELECT column1, column2, ...

FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column];

1. SELECT

 This statement is used to fetch the data from a database table which returns data in the form of result table.
 These result tables are called result-sets.

Syntax:

SELECT column1, column2, columnN

FROM table_name;

If you want to fetch all the fields of CUSTOMERS table, then use the following query:

SELECT * FROM CUSTOMERS;

2. FROM

 The FROM clause is used to specify the table(s) from which the data should be retrieved. It is a mandatory part of most
SQL queries and appears immediately after the SELECT clause.
 It tells SQL which table (or tables) to fetch data from.

Example:

SELECT Name, Age

FROM Student;

3. WHERE

 WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables.
 If the given condition is satisfied, then only it returns specific value from the table.
 You would use WHERE clause to filter the records and fetching only necessary records.
 The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc.,

Syntax:

SELECT column1, column2, column

FROM table_name WHERE [condition]

Example:

SELECT * FROM Student

WHERE Age > 18;

4. GROUP BY

 GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
 The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax:

SELECT column1, column2

FROM table_name WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

Example:

SELECT CourseID, COUNT(*) AS StudentCount

FROM Student

GROUP BY CourseID;

Groups students by CourseID and shows how many students in each course.

5. HAVING

 The HAVING clause is used to filter records after they have been grouped using the GROUP BY clause.
 Unlike the WHERE clause (which filters rows before grouping), HAVING filters groups based on the results of
aggregate functions like SUM(), COUNT(), AVG(), etc.
 Filters groups created by GROUP BY.
 Works after aggregation, unlike WHERE.

Key Points:
  HAVING is used with GROUP BY.
 It filters groups, not individual rows.
 Allows use of aggregate functions.
 Comes after GROUP BY in query order.
 Helps in producing meaningful summary reports.

Example:

SELECT CourseID, COUNT(*) AS StudentCount

FROM Student

GROUP BY CourseID

HAVING COUNT(*) > 1;

Shows only those courses with more than 1 student.

Full Example Using All Clauses

Student

StudentID Name Age CourseID

1 Diya 20 101
2 Kavy 21 101
a
3 Surya 18 102
4 Arya 22 101
5 Meera 20 102

SQL Query:

SELECT CourseID, COUNT(*) AS StudentCount

FROM Student

WHERE Age > 18

GROUP BY CourseID

HAVING COUNT(*) > 1;

Output:

CourseI StudentCount
D
101 2
(Only Diya and Kavya are >18 in CourseID 101)