1. Database Fundamentals
What is a Database?
A database is an organized collection of data that allows
information to be stored, managed, searched, updated,
and retrieved efficiently.
In a typical application, data such as student details,
employee information, product records, orders, payments,
and examination results are stored inside a database.
Example:
A university may maintain a database containing
students, courses, departments, faculty members,
examinations, attendance, and results.
Why Do We Need Databases?
- To store large amounts of data.
- To retrieve information quickly.
- To update records efficiently.
- To maintain data consistency.
- To control access to data.
- To establish relationships between data.
- To support multiple users and applications.
DBMS vs RDBMS
A DBMS is software used to create,
store, manage, and retrieve data from databases.
An RDBMS is a type of DBMS that stores
data in related tables using the relational model.
| DBMS |
RDBMS |
| Database Management System |
Relational Database Management System |
| May not use relationships |
Uses relationships between tables |
| Suitable for simpler data models |
Suitable for structured relational data |
| Examples include file-based systems |
SQL Server, MySQL, PostgreSQL, Oracle |
Important:
SQL is a language. SQL Server, MySQL, Oracle and
PostgreSQL are database management systems that
implement SQL with their own dialect-specific features.
What is SQL?
SQL stands for
Structured Query Language.
It is used to communicate with relational databases.
SQL can be used to create database objects, insert
records, retrieve information, modify existing data,
delete records, control access, and perform complex
data analysis.
Common SQL Categories
| Category |
Purpose |
Examples |
| DDL |
Define database structures |
CREATE, ALTER, DROP |
| DML |
Modify data |
INSERT, UPDATE, DELETE |
| DQL |
Retrieve data |
SELECT |
| DCL |
Control permissions |
GRANT, REVOKE |
| TCL |
Manage transactions |
COMMIT, ROLLBACK |
Database Structure
A relational database organizes information into
tables. Each table contains rows and columns.
-
Database:
A collection of related tables and database objects.
-
Table:
Stores data about a particular entity.
-
Column:
Represents an attribute or property.
-
Row:
Represents one record.
Example:
A Students table may contain
StudentID, StudentName, Age and City.
Primary Key
A primary key uniquely identifies each record in a table.
A primary key cannot contain duplicate values and
normally cannot contain NULL values.
Example
CREATE TABLE Students
(
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Age INT,
City VARCHAR(50)
);
In this example, StudentID uniquely identifies every
student.
Foreign Key
A foreign key creates a relationship between two tables.
It references a primary key in another table.
Example
CREATE TABLE Enrollments
(
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID)
REFERENCES Students(StudentID)
);
Foreign keys help maintain
referential integrity
between related tables.
2. SQL Basics
CREATE DATABASE
The CREATE DATABASE statement is used to create a new
database.
SQL
CREATE DATABASE StudentDB;
The database StudentDB is now available for creating
tables and other database objects.
Selecting a Database
SQL Server
USE StudentDB;
CREATE TABLE
The CREATE TABLE statement defines the structure of a
table.
Students Table
CREATE TABLE Students
(
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Age INT,
City VARCHAR(50)
);
Common Data Types
| Data Type |
Purpose |
| INT |
Integer numbers |
| DECIMAL |
Exact decimal numbers |
| VARCHAR |
Variable-length text |
| DATE |
Date values |
| DATETIME |
Date and time values |
| BIT |
Boolean-style 0/1 values |
INSERT Statement
INSERT is used to add new records to a table.
SQL
INSERT INTO Students
(
StudentID,
StudentName,
Age,
City
)
VALUES
(
1,
'Rahul Kumar',
21,
'Delhi'
);
Multiple records can also be inserted in one statement.
Multiple Records
INSERT INTO Students
(
StudentID,
StudentName,
Age,
City
)
VALUES
(2, 'Priya Sharma', 22, 'Mumbai'),
(3, 'Amit Patel', 20, 'Ahmedabad'),
(4, 'Neha Singh', 21, 'Jaipur');
SELECT Statement
SELECT is used to retrieve data from one or more tables.
Retrieve all columns
SELECT *
FROM Students;
Retrieve specific columns
SELECT StudentName, City
FROM Students;
Sample Output
StudentName City
-------------------------
Rahul Kumar Delhi
Priya Sharma Mumbai
Amit Patel Ahmedabad
Neha Singh Jaipur
UPDATE Statement
UPDATE modifies existing records.
SQL
UPDATE Students
SET City = 'Pune'
WHERE StudentID = 2;
Important:
Always use an appropriate WHERE condition when updating
specific records. Without WHERE, all matching rows may
be modified.
DELETE Statement
DELETE removes records from a table.
SQL
DELETE FROM Students
WHERE StudentID = 4;
Without a WHERE condition, DELETE can remove all
records from the table.
3. Filtering & Sorting Data
WHERE Clause
WHERE filters records according to a specified condition.
Example
SELECT *
FROM Students
WHERE Age > 20;
Only students whose age is greater than 20 will be
returned.
AND, OR and NOT
AND
SELECT *
FROM Students
WHERE Age > 20
AND City = 'Mumbai';
OR
SELECT *
FROM Students
WHERE City = 'Delhi'
OR City = 'Mumbai';
NOT
SELECT *
FROM Students
WHERE NOT City = 'Delhi';
IN Operator
IN checks whether a value exists within a specified list.
SELECT *
FROM Students
WHERE City IN ('Delhi', 'Mumbai', 'Pune');
IN is often cleaner than writing multiple OR conditions.
BETWEEN Operator
BETWEEN filters values within a specified range.
SELECT *
FROM Students
WHERE Age BETWEEN 20 AND 22;
BETWEEN is generally inclusive of both boundary values.
LIKE Operator
LIKE is used for pattern matching.
Names beginning with A
SELECT *
FROM Students
WHERE StudentName LIKE 'A%';
Names ending with a
SELECT *
FROM Students
WHERE StudentName LIKE '%a';
% represents zero or more characters.
_ represents exactly one character.
ORDER BY
ORDER BY sorts query results.
Ascending
SELECT *
FROM Students
ORDER BY StudentName ASC;
Descending
SELECT *
FROM Students
ORDER BY Age DESC;
4. SQL Functions
Aggregate Functions
Aggregate functions perform calculations on multiple
rows and return a single result.
| Function |
Purpose |
| COUNT() |
Counts records |
| SUM() |
Calculates total |
| AVG() |
Calculates average |
| MIN() |
Returns minimum value |
| MAX() |
Returns maximum value |
COUNT()
COUNT returns the number of records.
SELECT COUNT(*) AS TotalStudents
FROM Students;
Output
TotalStudents
-------------
4
SUM() and AVG()
SUM calculates a total, while AVG calculates an average.
SELECT
SUM(Fee) AS TotalFees,
AVG(Fee) AS AverageFee
FROM Courses;
Aggregate functions generally ignore NULL values when
calculating their results.
MIN() and MAX()
SELECT
MIN(Age) AS YoungestStudent,
MAX(Age) AS OldestStudent
FROM Students;
GROUP BY
GROUP BY groups rows that have the same values and
allows aggregate calculations to be performed for
each group.
SELECT
City,
COUNT(*) AS StudentCount
FROM Students
GROUP BY City;
Example Output
City StudentCount
-------------------------
Ahmedabad 1
Delhi 1
Mumbai 1
Jaipur 1
HAVING
HAVING filters grouped results after GROUP BY.
SELECT
City,
COUNT(*) AS StudentCount
FROM Students
GROUP BY City
HAVING COUNT(*) > 1;
WHERE vs HAVING
WHERE filters individual rows before grouping.
HAVING filters groups after grouping.
5. Relationships & Joins
Database Relationships
Relationships describe how records in different tables
are associated with each other.
| Relationship |
Example |
| One-to-One |
Person and Passport |
| One-to-Many |
Department and Employees |
| Many-to-Many |
Students and Courses |
In a student management system, one student can enroll
in multiple courses and one course can have multiple
students. This is a many-to-many relationship and is
commonly implemented using an intermediate table such
as Enrollments.
INNER JOIN
INNER JOIN returns records that have matching values
in both tables.
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses
ON Enrollments.CourseID = Courses.CourseID;
Example Output
StudentName CourseName
--------------------------------
Rahul Kumar C# Programming
Priya Sharma Python Programming
Amit Patel Web Development
LEFT JOIN
LEFT JOIN returns all records from the left table and
matching records from the right table.
SELECT
Students.StudentName,
Enrollments.CourseID
FROM Students
LEFT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;
This is useful when you want to identify students who
may not have any enrollment record.
RIGHT JOIN
RIGHT JOIN returns all records from the right table and
matching records from the left table.
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
RIGHT JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID
RIGHT JOIN Courses
ON Enrollments.CourseID = Courses.CourseID;
In many projects, INNER JOIN and LEFT JOIN are used more
frequently. RIGHT JOIN can often be rewritten as a
LEFT JOIN by reversing the table order.
6. Advanced SQL
Subqueries
A subquery is a query written inside another query.
It can be used to calculate a value or identify records
required by the outer query.
Example
SELECT StudentName, Age
FROM Students
WHERE Age >
(
SELECT AVG(Age)
FROM Students
);
The inner query calculates the average age. The outer
query then returns students whose age is greater than
that average.
UNION
UNION combines the results of two compatible SELECT
statements and removes duplicate rows.
SELECT City
FROM Students
UNION
SELECT City
FROM Faculty;
The SELECT statements used with UNION must have compatible
numbers and types of columns.
CASE Expression
CASE allows conditional logic to be used inside SQL
queries.
SELECT
StudentName,
Age,
CASE
WHEN Age < 21 THEN 'Junior'
WHEN Age BETWEEN 21 AND 22 THEN 'Regular'
ELSE 'Senior'
END AS StudentCategory
FROM Students;
CASE is useful for creating calculated categories,
labels, and conditional output.
Views
A view is a virtual table based on a SELECT query.
Views can simplify frequently used queries and provide
a controlled representation of data.
Create View
CREATE VIEW StudentCourseView
AS
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses
ON Enrollments.CourseID = Courses.CourseID;
Use View
SELECT *
FROM StudentCourseView;
Indexes
An index is a database structure that can improve the
performance of searches and sorting operations on
appropriate columns.
CREATE INDEX IX_Students_City
ON Students(City);
Indexes are not free. They consume storage and can add
overhead to INSERT, UPDATE and DELETE operations.
Indexes should therefore be designed according to
actual query patterns.
Stored Procedures
A stored procedure is a named collection of SQL
statements stored in the database and executed when
required.
Create Procedure
CREATE PROCEDURE GetStudentById
@StudentID INT
AS
BEGIN
SELECT *
FROM Students
WHERE StudentID = @StudentID;
END;
Execute Procedure
EXEC GetStudentById
@StudentID = 1;
Stored procedures are commonly used in enterprise
applications to encapsulate database operations and
provide a reusable interface for application code.
7. Practical SQL
SQL Constraints
Constraints are rules applied to table columns to
maintain data integrity and consistency.
| Constraint |
Purpose |
| PRIMARY KEY |
Uniquely identifies each record |
| FOREIGN KEY |
Maintains relationships between tables |
| NOT NULL |
Prevents NULL values |
| UNIQUE |
Prevents duplicate values |
| CHECK |
Restricts values according to a condition |
| DEFAULT |
Provides a default value |
Example
CREATE TABLE Students
(
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE,
Age INT CHECK (Age >= 18),
City VARCHAR(50),
IsActive BIT DEFAULT 1
);
Complete StudentDB Project
We will now build a small relational database for a
student management system. This project will connect
the concepts learned throughout this course.
Database Design
StudentDB
Students → stores student information
Courses → stores available courses
Enrollments → connects students with courses
Step 1 — Create Database
CREATE DATABASE StudentDB;
USE StudentDB;
Step 2 — Create Students Table
CREATE TABLE Students
(
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Age INT CHECK (Age >= 18),
City VARCHAR(50),
Email VARCHAR(150) UNIQUE
);
Step 3 — Create Courses Table
CREATE TABLE Courses
(
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL,
Fee DECIMAL(10,2) NOT NULL
);
Step 4 — Create Enrollments Table
CREATE TABLE Enrollments
(
EnrollmentID INT PRIMARY KEY,
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrollmentDate DATE NOT NULL,
FOREIGN KEY (StudentID)
REFERENCES Students(StudentID),
FOREIGN KEY (CourseID)
REFERENCES Courses(CourseID)
);
Step 5 — Insert Students
INSERT INTO Students
(
StudentID,
StudentName,
Age,
City,
Email
)
VALUES
(1, 'Rahul Kumar', 21, 'Delhi', 'rahul@example.com'),
(2, 'Priya Sharma', 22, 'Mumbai', 'priya@example.com'),
(3, 'Amit Patel', 20, 'Ahmedabad', 'amit@example.com'),
(4, 'Neha Singh', 21, 'Jaipur', 'neha@example.com');
Step 6 — Insert Courses
INSERT INTO Courses
(
CourseID,
CourseName,
Fee
)
VALUES
(101, 'C# Programming', 5000),
(102, 'Python Programming', 4500),
(103, 'Web Development', 4000);
Step 7 — Insert Enrollments
INSERT INTO Enrollments
(
EnrollmentID,
StudentID,
CourseID,
EnrollmentDate
)
VALUES
(1, 1, 101, '2026-08-01'),
(2, 2, 102, '2026-08-02'),
(3, 3, 103, '2026-08-03'),
(4, 1, 102, '2026-08-05');
Database Completed!
You now have a three-table relational database with
primary keys, foreign keys, constraints, records,
and relationships.
Practical Queries
1. Display all students
SELECT *
FROM Students;
2. Display students from Ahmedabad
SELECT *
FROM Students
WHERE City = 'Ahmedabad';
3. Display students above 20 years
SELECT *
FROM Students
WHERE Age > 20;
4. Display all courses
SELECT *
FROM Courses;
5. Find the average course fee
SELECT AVG(Fee) AS AverageFee
FROM Courses;
6. Display students with their courses
SELECT
Students.StudentName,
Courses.CourseName,
Courses.Fee,
Enrollments.EnrollmentDate
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses
ON Enrollments.CourseID = Courses.CourseID;
7. Count students enrolled in each course
SELECT
Courses.CourseName,
COUNT(Enrollments.StudentID) AS TotalStudents
FROM Courses
LEFT JOIN Enrollments
ON Courses.CourseID = Enrollments.CourseID
GROUP BY Courses.CourseName;
8. Find the highest course fee
SELECT MAX(Fee) AS HighestFee
FROM Courses;
Practice Tasks
-
Display students whose names start with "R".
-
Display courses having a fee greater than 4000.
-
Count the total number of students.
-
Find the average student age.
-
Display students ordered by age descending.
-
Display each student with their enrolled course.
-
Find the number of students enrolled in
each course.
-
Create a view containing student and course
information.
Mini Project — Student Management Database
Build a complete relational database for a small
educational institute.
Required Tables
- Students
- Courses
- Instructors
- Enrollments
- Results
Minimum Requirements
- Create the database.
- Create all required tables.
- Define primary and foreign keys.
- Apply appropriate constraints.
- Insert at least 10 students.
- Insert at least 5 courses.
- Create student-course relationships.
- Write SELECT queries.
- Use WHERE, LIKE, IN and BETWEEN.
- Use GROUP BY and HAVING.
- Use INNER JOIN and LEFT JOIN.
- Create at least one view.
- Create at least one stored procedure.
- Create an index for an appropriate search column.
Learning Outcome:
After completing this project, you should be able to
design a basic relational database, create tables,
maintain relationships, write SQL queries, and work
with common database objects used in application
development.
Course Summary
In this course, you learned the fundamentals of
relational databases and SQL.
- Database and RDBMS fundamentals
- SQL commands and categories
- Database and table creation
- INSERT, SELECT, UPDATE and DELETE
- Filtering and sorting records
- Aggregate functions
- GROUP BY and HAVING
- Primary and foreign keys
- Database relationships
- INNER, LEFT and RIGHT JOIN
- Subqueries and CASE
- Views and indexes
- Stored procedures
- Constraints and data integrity
- Practical relational database design
Next Step
The best way to learn SQL is to practice every
concept directly in a database environment.
Create the StudentDB database, execute each query,
modify the examples, and then complete the mini
project without referring to the solution.