NAME : Dughrekar Shrikrupa

PROGRAM: BA (Hons)Economics

COURSE : SQL

ROLL NO. : 08

Q1. Explain the concept of Database Management System (DBMS) and SQL in
detail. Discuss the structure of a database table and explain the importance of
keys (Primary Key and Foreign Key) with suitable examples.

1. DBMS (Database Management System)

A DBMS is software used to store, manage, and organize data efficiently.
Examples of DBMS:
● MySQL
● Oracle
● SQL Server
Functions of DBMS:
● Stores large amounts of data
● Retrieves data quickly
● Updates and deletes data
● Ensures data security and integrity
● Avoids data duplication
Example:
● A college storing student records (name, marks, course) in a database

2. Concept of SQL (Structured Query Language)

SQL is a language used to communicate with the DBMS.
Uses of SQL:
● Create tables
● Insert data
● Update records
● Retrieve data
Example:
SELECT * FROM Students;
SQL acts as a bridge between user and database

3. Structure of a Database Table

A table is the basic unit of a database where data is stored.
Components of a Table:
A. Rows (Records)
● Each row represents a single entry
● Example: One student’s details
B. Columns (Fields)
● Each column represents a type of data
● Example: Name, Age, Marks

Example Table:
Students
ID | Name | Age | Course

1 | Rahul | 20 | BCA

2 | Anita | 21 | BBA

● Rows = Individual students

● Columns = Attributes of students

4. Keys in DBMS
Keys are used to identify records and maintain relationships between tables

A. Primary Key
A Primary Key is a column that uniquely identifies each record
Characteristics:
● No duplicate values
● Cannot be NULL
● Each table has only one primary key
Example:
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
Here:
● ID is the Primary Key
● Each student has a unique ID

B. Foreign Key
A Foreign Key is a column that creates a link between two tables
Purpose:
● Maintains relationship between tables
● Ensures data consistency

Example:
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50)
);

CREATE TABLE Students (

ID INT PRIMARY KEY,
Name VARCHAR(50),
CourseID INT,
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Here:
● CourseID in Students is a Foreign Key
● It refers to CourseID in Courses table

Sample Data:
Courses Table
CourseID | CourseName
101 | BCA
102 | BBA
Students Table
ID | Name | CourseID
1 | Rahul | 101
2 | Anita | 102
This connects students with their courses

5. Importance of Keys
Primary Key Importance:
● Uniquely identifies records
● Prevents duplication
● Helps in fast searching
Foreign Key Importance:
● Maintains relationships between tables
● Ensures data integrity
● Prevents invalid data entry
6. Practical Importance of DBMS & SQL
● Used in banking, education, business, e-commerce
● Handles large data efficiently
● Ensures accuracy and security
● Supports decision-making

Q.2 Consider an educational institution maintaining student records in a database.

Explain how SQL queries are used to retrieve, filter, and arrange data from tables.
Illustrate your answer by writing suitable SQL queries for displaying records,
applying conditions, sorting data, and counting entries using hypothetical
examples.

1. Introduction
● Educational institutions store data like:
o Student names
o Marks
o Courses
o Attendance
 ● SQL queries help to:
o Retrieve data
o Filter specific records
o Arrange/sort data
o Perform calculations (like counting)
2. Example Table (Students)
Assume a table:
Students(
ID INT,
Name VARCHAR(50),
Marks INT,
Course VARCHAR(50)
)
3. Retrieving Data (Using SELECT)
Used to display records from a table
Example:
SELECT * FROM Students;
● Displays all student records
SELECT Name, Marks FROM Students;
● Displays only Name and Marks columns
4. Filtering Data (Using WHERE Clause)
Used to apply conditions and get specific data
Examples:
SELECT * FROM Students WHERE Marks > 80;
● Shows students scoring more than 80
SELECT * FROM Students WHERE Course = 'BCA';
● Shows students from BCA course
SELECT * FROM Students WHERE Marks BETWEEN 60 AND 80;
● Students scoring between 60 and 80
5. Sorting Data (Using ORDER BY)
Used to arrange data in ascending or descending order
Examples:
SELECT * FROM Students ORDER BY Marks ASC;
 ● Sorts marks in ascending order
SELECT * FROM Students ORDER BY Marks DESC;
● Sorts marks in descending order
SELECT * FROM Students ORDER BY Name;
● Sorts students alphabetically by name

6. Counting Entries (Using COUNT Function)

Used to find number of records
Examples:
SELECT COUNT(*) FROM Students;
● Counts total number of students
SELECT COUNT(*) FROM Students WHERE Course = 'BCA';
● Counts students in BCA course

7. Combined Example (Multiple Operations)

Using filtering + sorting together:
SELECT Name, Marks
FROM Students
WHERE Marks > 70
ORDER BY Marks DESC;
● Displays students scoring above 70
● Sorted in descending order of marks

8. Practical Importance in Education Systems

● Quickly find top-performing students
● Generate class reports
● Filter students by course or marks
● Count total students for administration
● Arrange data for result sheets

[Link] various categories of SQL commands such as DDL, DML, DQL, and
DCL in detail with appropriate examples. Explain the practical applications and
importance of SQL in banking, business management, education, and e-commerce
systems.

1. Categories of SQL Commands

A. DDL (Data Definition Language)
Used to define or change the structure of the database (tables, schemas, etc.)
Common Commands:
● CREATE – Creates a new table/database
● ALTER – Modifies an existing table
● DROP – Deletes a table/database
● TRUNCATE – Removes all records but keeps the structure
Example:
CREATE TABLE Students (
ID INT,
Name VARCHAR(50),
Age INT
);

ALTER TABLE Students ADD Email VARCHAR(100);

DROP TABLE Students;

Key Point:
● Works on structure, not data

B. DML (Data Manipulation Language)

Used to manage and modify data inside tables
Common Commands:
● INSERT – Add new data
● UPDATE – Modify existing data
● DELETE – Remove data
Example:
INSERT INTO Students VALUES (1, 'Rahul', 20);
UPDATE Students SET Age = 21 WHERE ID = 1;

DELETE FROM Students WHERE ID = 1;

Key Point:
● Works on actual data (rows)

C. DQL (Data Query Language)

Used to retrieve data from the database
Main Command:
● SELECT
Example:
SELECT * FROM Students;

SELECT Name FROM Students WHERE Age > 18;

Key Point:
● Used for fetching/querying data

D. DCL (Data Control Language)

Used to control access and permissions
Common Commands:
● GRANT – Gives permission
● REVOKE – Removes permission
Example:
GRANT SELECT ON Students TO user1;

REVOKE SELECT ON Students FROM user1;

Key Point:
● Ensures security and access control

2. Practical Applications of SQL

A. Banking Systems
● Stores customer details, account info, transactions
● Helps in:
 o Tracking deposits & withdrawals
o Fraud detection
o Generating account statements
Example:
SELECT * FROM Transactions WHERE Account_ID = 101;
Importance:
● Ensures data accuracy, security, and fast processing

B. Business Management
● Used for inventory, employees, sales, and reports
Applications:
● Track stock levels
● Manage employee records
● Analyze sales performance
Importance:
● Helps in decision-making and planning
● Improves efficiency and organization

C. Education Systems
● Stores student records, marks, attendance
Applications:
● Manage student databases
● Generate report cards
● Track performance
Example:
SELECT Name, Marks FROM Students WHERE Marks > 80;
Importance:
● Makes data management easy and organized
● Saves time for teachers and admins

D. E-commerce Systems
 ● Used in platforms like online shopping websites
Applications:
● Store product details
● Manage orders and customers
● Track payments and deliveries
Example:
SELECT * FROM Orders WHERE Customer_ID = 500;
Importance:
● Enables real-time transactions
● Supports large-scale data handling

Q4. Students must also submit a comprehensive report on the Budget Exhibition
and explain how SQL software can assist in data management and analysis
related to such exhibitions
1. Introduction to Budget Exhibition
A Budget Exhibition is an academic or institutional activity where government
budgets (especially the Union Budget) are presented, analyzed, and displayed
using charts, graphs, reports, and models.
Objectives:
1. To understand government revenue and expenditure
2. To analyze economic policies and fiscal decisions
3. To improve analytical and presentation skills
4. To connect theoretical economics with real-world data

2. Key Components of a Budget Exhibition

A. Revenue Section
● Tax Revenue (Income tax, GST)
● Non-Tax Revenue (fees, dividends)
B. Expenditure Section
● Development Expenditure (education, health)
● Non-Development Expenditure (defence, subsidies)
C. Fiscal Indicators
● Fiscal Deficit
● Revenue Deficit
● Primary Deficit
D. Data Presentation Tools
● Charts and graphs
● Tables and reports
● Comparative analysis (current vs previous budgets)

3. Need for Data Management in Budget Exhibition

Handling budget data involves:
1. Large datasets (multiple years, sectors)
2. Complex classification (revenue, expenditure, sectors)
3. Continuous updates
4. Comparative analysis
Manual handling becomes:
● Time-consuming
● Error-prone
● Difficult to analyze

4. Role of SQL Software in Budget Exhibition

SQL helps in efficient storage, retrieval, and analysis of budget data.
A. Data Storage and Organization
SQL allows storing budget data in structured tables.
Example Table: Budget_Data
Yea Sector Type Amoun
r t

202 Educati Expenditu 50000

4 on re

202 Defence Expenditu 80000

4 re
202 GST Revenue 12000
4 0

Data is organized systematically and is easy to access.

B. Data Retrieval (SELECT)
Retrieve required information quickly.
SELECT * FROM Budget_Data;
Displays all budget records.
C. Filtering Data (WHERE)
Extract specific data for analysis.
SELECT * FROM Budget_Data
WHERE Sector = 'Education';
Shows only education-related data.
D. Aggregation and Analysis
SQL helps in performing calculations.
(i) Total expenditure
SELECT SUM(Amount)
FROM Budget_Data
WHERE Type = 'Expenditure';
(ii) Sector-wise expenditure
SELECT Sector, SUM(Amount)
FROM Budget_Data
GROUP BY Sector;
Helps in understanding which sector gets more allocation.
E. Sorting Data (ORDER BY)
SELECT Sector, Amount
FROM Budget_Data
ORDER BY Amount DESC;
Identifies highest spending sectors.
F. Comparative Analysis
SELECT Year, SUM(Amount)
FROM Budget_Data
GROUP BY Year;
Compares budgets across years.
G. Data Accuracy and Integrity
● Avoids duplication
● Maintains consistency
● Reduces human errors
H. Report Generation
SQL queries can generate:
● Summary tables
● Sector-wise reports
● Year-wise comparisons
Useful for presentation in exhibitions.

5. Practical Applications in Budget Exhibition

1. Trend Analysis
o Study changes in spending over years
2. Policy Evaluation
o Analyze impact of government decisions
3. Visualization Support
o Data extracted via SQL can be used in graphs
4. Efficient Presentation
o Quick generation of accurate reports

6. Importance of SQL in Budget Analysis

1. Saves Time – Fast data processing
2. Improves Accuracy – Reduces manual errors
3. Handles Large Data – Suitable for multi-year budgets
4. Supports Decision-Making – Helps in drawing conclusions
5. Enhances Research Skills – Useful for economics students