Introduction to Database Systems

and SQL Commands

Complete Study Guide — Concepts, Definitions, SQL Commands & Practice Problems

Prepared for exam-ready revision

Table of Contents
TOC \h \o "1-3"
Section 1: Concepts and Definitions
1.1 Data and Information
Data is raw, unprocessed facts and figures that have no meaning by themselves.
Example: 21, "Ramesh", "[Link]"
Information is data that has been processed, organized, or structured to become meaningful and useful for
decision-making.
Example: "Ramesh, age 21, is enrolled in [Link]."
Key idea to remember: Data → (Processing) → Information

Basis Data Information

Meaning Raw, unorganized facts Processed, organized, meaningful

Dependency Independent of information Depends on data

Usefulness Not directly useful for decisions Useful for decision-making

Example 90, 85, 78 (marks) “The class average is 84.3%”

Form Numbers, characters, symbols Reports, statements, summaries

Easy way to remember: Data is like ingredients; Information is the cooked dish.

1.2 Data Elements: Data Items, Records, Files, and Metadata

Think of this as a hierarchy, like a filing cabinet:
DATABASE
└── FILE (Table)
└── RECORD (Row)
└── FIELD / DATA ITEM (Column value)

1. Data Item (Field): The smallest unit of named data. It represents a single characteristic/attribute of an entity.
Example: Name, Roll No, Age are fields.
2. Record: A collection of related data items (fields) treated as a single unit, describing one entity/instance.
Example: (101, "Anita", 20, "BCA") — one student’s complete record.
3. File (Table): A collection of related records of the same type.
Example: The "Student File" contains records of all students.
4. Metadata: “Data about data.” It describes the structure, format, constraints, and meaning of the actual data
— not the data itself.
Example: Metadata for the "Student" table tells us that Roll No is a Number, Name is Text (max 30
characters), and Roll No is the Primary Key.
Simple analogy: In a school register —
● Field = one column like “Name”
 ● Record = one student’s entire row
● File = the whole register (all students)
● Metadata = the instructions printed on the register cover explaining what each column means

1.3 Data Dictionary

Definition: A Data Dictionary is a centralized repository (a “dictionary”) that stores metadata — information
about the data in a database, such as table names, field names, data types, sizes, constraints, and relationships.

Components of a Data Dictionary

● Table/File names – names of all tables in the database
● Field/Column names – names of attributes in each table
● Data types and sizes – e.g., VARCHAR(30), INT, DATE
● Constraints – Primary Key, Foreign Key, NOT NULL, UNIQUE
● Relationships – how tables are linked to each other
● Default values and validation rules
● Ownership/Access information – who can read/write the data

Significance (Why it is important)

1. Helps maintain consistency of data across the database.
2. Reduces data redundancy by clearly defining each data element once.
3. Serves as documentation for developers, DBAs, and users.
4. Improves data integrity by enforcing defined rules/constraints.
5. Makes database design and modification easier.
6. Helps in impact analysis — knowing what will be affected if a field is changed.

1.4 Database Concepts

Definition: A Database is an organized, structured collection of related data that is stored electronically and can
be easily accessed, managed, and updated.

Characteristics of a Database
1. Self-describing nature – contains metadata (data dictionary) about itself.
2. Data abstraction / Insulation – users see only relevant data, not physical storage details.
3. Data sharing – multiple users/applications can access the same data simultaneously.
4. Minimal redundancy – reduces duplicate data storage.
5. Data integrity – ensures accuracy and consistency through constraints.
6. Data security – controls who can access or modify data.
7. Multiple views – different users can have different views of the same data.
8. Persistent storage – data remains stored even after the program/session ends.

Importance of Databases
● Efficient storage and retrieval of large volumes of data
● Avoids data duplication and inconsistency
 ● Enables multi-user access with controlled concurrency
● Provides data security and backup/recovery mechanisms
● Supports quick decision-making through organized information
● Maintains data integrity via rules and constraints

1.5 Database Systems and Environment

A Database System (DBMS – Database Management System) is software that enables users to define, create,
maintain, and control access to a database.

Components of a Database Environment

1. Hardware – physical devices (servers, storage disks, computers) where data is stored.
2. Software – the DBMS software itself (e.g., MySQL, Oracle, SQL Server) plus OS and application programs.
3. Data – the actual data and metadata stored in the database.
4. Procedures – instructions/rules for designing and using the database (e.g., login procedures, backup
procedures).
5. Database Access Language – e.g., SQL, used to insert, retrieve, update, and delete data.
6. Users:
● Database Administrator (DBA) – manages the entire database, security, and performance
● Application Programmers – write programs that use the database
● End Users – interact with the database via applications
● Casual/Naïve Users – occasional users who query the database

Basic Architecture (Three-Level / ANSI-SPARC Architecture)

External Level → (User Views – what each user sees)
↑
Conceptual Level → (Logical structure of entire database)
↑
Internal Level → (Physical storage of data on disk)

Functions of a DBMS
● Data storage, retrieval, and update
● Provides a data dictionary/catalog
● Supports concurrent access (multiple users at once)
● Provides security and authorization
● Ensures backup and recovery
● Maintains data integrity and consistency
● Supports transaction management (ACID properties)

1.6 Schemas and Instances

Schema: The overall design or structure of the database — it describes what data is stored and how it's
organized (like a blueprint of a house). The schema does not change frequently.
Example: Student(RollNo, Name, Age, Course) — this structure is the schema.
Sub-schema: A subset of the schema — it defines the specific portion of the database that a particular user or
application is allowed to view or use. Different users can have different sub-schemas of the same database.
Example: The Accounts Department may only need RollNo, Name, FeesPaid — a sub-schema of the full
Student schema.
Instance: The actual data stored in the database at a particular moment in time. Unlike the schema, an instance
changes frequently as records are added, updated, or deleted.
Example: On Monday, the Student table has 50 rows; after new admissions on Tuesday, it has 55 rows —
these are two different instances of the same schema.
Easy analogy:
● Schema = the empty form/template (blueprint of the house)
● Sub-schema = a specific view of that form given to a specific person (only the rooms they're allowed to
see)
● Instance = the form filled with actual data at a given time (furniture placed in the house today)
Section 2: SQL Data Types
SQL data types define what kind of value a column can hold.

2.1 Numeric Data Types

Data Type Description Example

INT / INTEGER Whole numbers 25, 1000

SMALLINT Smaller range whole numbers 120

DECIMAL(p,s) / NUMERIC(p,s) Fixed-point numbers with precision (p) and 1234.56

scale (s = digits after decimal)

FLOAT / REAL Floating-point (approximate) decimal 3.14159

numbers

2.2 Character/String Data Types

Data Type Description Example

CHAR(n) Fixed-length character string of size n (padded CHAR(5) → "Hi "

with spaces)

VARCHAR(n) Variable-length character string, max size n VARCHAR(30) → "Ramesh"

TEXT Very large text data Long descriptions, articles

2.3 Date/Time Data Types

Data Type Description Example

DATE Stores date only 2026-07-09

TIME Stores time only 14:30:00

DATETIME / TIMESTAMP Stores both date and time 2026-07-09 14:30:00

YEAR Stores year value 2026

Tip to remember: Numeric = numbers you calculate with; Character = text/names; Date/Time = when something
happened.
Section 3: SQL Commands
SQL commands are divided into 5 categories. A simple mnemonic:
DDL – Defines Structure | DML – Manages Data | DQL – Queries Data | DCL – Controls Access | TCL –
Controls Transactions

3.1 DDL (Data Definition Language)

Used to define, modify, or remove database structures (tables, databases). DDL commands are auto-committed
(changes are saved permanently and cannot be rolled back).

1. CREATE DATABASE – creates a new database

CREATE DATABASE CollegeDB;

2. CREATE TABLE – creates a new table with defined columns and data types
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Age INT,
Course VARCHAR(20),
DOB DATE
);

3. ALTER TABLE – modifies an existing table's structure

-- Add a new column
ALTER TABLE Student ADD Email VARCHAR(50);

-- Modify a column's data type

ALTER TABLE Student MODIFY Age SMALLINT;

-- Drop a column
ALTER TABLE Student DROP COLUMN Email;

4. DROP TABLE – permanently deletes a table structure AND its data

DROP TABLE Student;

5. TRUNCATE TABLE – removes ALL rows/data but keeps the table structure
TRUNCATE TABLE Student;

Quick Comparison – DROP vs TRUNCATE vs DELETE

Command Removes Data Removes Structure Rollback Possible Type

DROP Yes Yes No DDL

TRUNCATE Yes No No (generally) DDL

DELETE Yes (selective) No Yes DML

3.2 DML (Data Manipulation Language)
Used to insert, update, delete, and retrieve data stored in tables.

1. INSERT – adds new rows/records into a table

INSERT INTO Student (RollNo, Name, Age, Course, DOB)
VALUES (101, 'Anita Sharma', 20, 'BCA', '2006-05-14');

2. SELECT – retrieves data from a table (basic use)

SELECT Name, Age FROM Student;

3. UPDATE – modifies existing records

UPDATE Student
SET Age = 21, Course = 'BCA Final'
WHERE RollNo = 101;

4. DELETE – removes specific record(s) from a table

DELETE FROM Student
WHERE RollNo = 101;

⚠ Caution: Always use WHERE with UPDATE/DELETE, otherwise ALL rows will be affected!

3.3 DQL (Data Query Language)

Used specifically to query/fetch data from the database. (Note: Some textbooks club SELECT under DML; here
it's treated separately as per your syllabus.)

1. Basic SELECT
SELECT * FROM Student; -- fetch all columns
SELECT Name, Course FROM Student; -- fetch specific columns

2. SELECT with WHERE clause – filters rows based on a condition

SELECT Name, Age FROM Student
WHERE Course = 'BCA';

SELECT * FROM Student

WHERE Age > 20 AND Course = 'BCA';

Common WHERE operators:

Operator Meaning Example

= Equal to WHERE Age = 20

>, <, >=, <= Comparison WHERE Age >= 18

<> or != Not equal WHERE Course <> 'BCA'

BETWEEN Range WHERE Age BETWEEN 18 AND 22

IN Matches a list WHERE Course IN ('BCA','BCom')

LIKE Pattern match WHERE Name LIKE 'A%'

AND / OR / NOT Combine conditions WHERE Age>18 AND Course='BCA'

3. SELECT DISTINCT – removes duplicate values, showing only unique results
SELECT DISTINCT Course FROM Student;

Example: If 5 students study "BCA" and 3 study "BCom", this returns just: BCA, BCom (no repeats).

3.4 DCL (Data Control Language)

Used to control access/permissions to the database — granting or removing rights of users.

1. Creating Users & Roles

-- Create a new user
CREATE USER 'ramesh'@'localhost' IDENTIFIED BY 'password123';

-- Create a role (a named set of privileges)

CREATE ROLE 'data_reader';

2. GRANT – gives specific privileges to a user or role

GRANT SELECT, INSERT ON [Link] TO 'ramesh'@'localhost';

GRANT ALL PRIVILEGES ON CollegeDB.* TO 'admin_user';

3. REVOKE – withdraws/removes previously granted privileges

REVOKE INSERT ON [Link] FROM 'ramesh'@'localhost';

Common privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALL PRIVILEGES
Easy way to remember: GRANT = “give the key” 🔑 | REVOKE = “take back the key” 🚫

3.5 TCL (Transaction Control Language)

Used to manage transactions — a transaction is a group of one or more SQL operations that are executed as a
single logical unit of work.

Introduction to Transactions
A transaction either completes entirely (all operations succeed) or not at all (if any operation fails, everything is
undone). Example: transferring money from Account A to Account B involves two steps (debit A, credit B) —
both must succeed together, or neither should happen.

ACID Properties (the 4 pillars of a reliable transaction)

Property Meaning

A – Atomicity The transaction is treated as a single, indivisible unit — “all or nothing.”

C – Consistency The database moves from one valid state to another valid state;
rules/constraints are never violated.

I – Isolation Concurrent transactions do not interfere with each other; each executes as if it
were the only one running.

D – Durability Once committed, changes are permanent, even in case of a system failure.
Easy memory trick: “A Car Is Durable” → Atomicity, Consistency, Isolation, Durability

TCL Commands
1. COMMIT – permanently saves all changes made in the current transaction
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 500 WHERE AccNo = 1;
UPDATE Account SET Balance = Balance + 500 WHERE AccNo = 2;
COMMIT;

2. ROLLBACK – undoes all changes made in the current transaction (since the last COMMIT)
BEGIN TRANSACTION;
DELETE FROM Student WHERE RollNo = 101;
ROLLBACK; -- the delete is undone; data is restored

3. SAVEPOINT – sets a marker within a transaction, allowing partial rollback to that point
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 500 WHERE AccNo = 1;
SAVEPOINT sp1;
UPDATE Account SET Balance = Balance + 500 WHERE AccNo = 2;
ROLLBACK TO sp1; -- undoes only the second update, keeps the first
COMMIT;
Section 4: Problem Solving Practice
Try these to test your understanding. (Answers/hints given below each set.)

A. Conceptual Questions
1. Differentiate between data and information with one real-life example.
2. Arrange in correct hierarchy: Record, Field, File, Database.
3. Explain metadata with an example from a “Library” database.
4. What is the difference between a schema and an instance?
5. List any four components of a database environment.

B. SQL Command Practice

Given a table Employee(EmpID, EName, Dept, Salary, JoinDate):
1. Write a query to create this table with appropriate data types.
2. Insert a record: EmpID=1, EName='Priya', Dept='HR', Salary=45000, JoinDate='2023-01-15'.
3. Write a query to display all employees from the 'HR' department.
4. Write a query to display distinct department names.
5. Increase the salary of employee with EmpID=1 by 5000.
6. Delete the record of the employee whose EmpID is 5.
7. Write a query to add a new column Email to the Employee table.
8. Write a query to remove all records from the table without deleting its structure.
9. Grant SELECT and UPDATE permission on the Employee table to a user 'clerk1'.
10. Write a transaction that debits ₹1000 from EmpID 1's salary and credits it to EmpID 2's salary, using
COMMIT.

Sample Answers (try first, then check)

-- Q1
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EName VARCHAR(30),
Dept VARCHAR(20),
Salary DECIMAL(10,2),
JoinDate DATE
);

-- Q2
INSERT INTO Employee VALUES (1, 'Priya', 'HR', 45000, '2023-01-15');

-- Q3
SELECT * FROM Employee WHERE Dept = 'HR';

-- Q4
SELECT DISTINCT Dept FROM Employee;

-- Q5
UPDATE Employee SET Salary = Salary + 5000 WHERE EmpID = 1;
 -- Q6
DELETE FROM Employee WHERE EmpID = 5;

-- Q7
ALTER TABLE Employee ADD Email VARCHAR(50);

-- Q8
TRUNCATE TABLE Employee;

-- Q9
GRANT SELECT, UPDATE ON Employee TO 'clerk1';

-- Q10
BEGIN TRANSACTION;
UPDATE Employee SET Salary = Salary - 1000 WHERE EmpID = 1;
UPDATE Employee SET Salary = Salary + 1000 WHERE EmpID = 2;
COMMIT;

C. True/False (Quick Revision)

1. TRUNCATE can be rolled back in all databases. (Generally False)
2. Metadata describes the structure of data, not the data itself. (True)
3. SELECT DISTINCT removes duplicate rows from the result. (True)
4. A schema changes frequently, while an instance is fixed. (False — it's the reverse)
5. GRANT and REVOKE belong to DCL. (True)
6. Atomicity means a transaction is either fully completed or fully undone. (True)
Quick Revision Cheat Sheet
Category Full Form Commands

DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE

DML Data Manipulation Language INSERT, UPDATE, DELETE

DQL Data Query Language SELECT, SELECT DISTINCT, WHERE

DCL Data Control Language GRANT, REVOKE, CREATE USER

TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT

ACID = Atomicity, Consistency, Isolation, Durability

Hierarchy = Database → File (Table) → Record (Row) → Field (Column value)
Schema = Structure (rarely changes) | Instance = Actual data (changes often)

End of Study Guide. Good luck with your preparation!