A Database Management System (DBMS) is a software system that manages databases.

It
enables users to create, store, retrieve, manipulate, and share data efficiently. The core idea is
to facilitate data management through a collection of programs that handle various database
operations.

Basic Definitions and Terminologies

 Data: Raw, unprocessed facts such as numbers, names, images, audio, video, etc.
Examples include a person's name or a number.
 Information: Processed data that provides meaningful context. For example, stating
"Suresh is 25 years old" transforms raw data into useful information.
 Database: A collection of related data. It must contain data that is interconnected and
relevant to a specific domain, such as banking or library management systems.
 Metadata: Data about data; it describes the structure, format, data types, and
constraints of the database. It essentially defines the database's schema and
organization.

Key Functionalities of a DBMS

1. Defining the Database: Specify data types, structures, and constraints (limits on
data). For example, setting an age constraint to be greater than 18.
2. Constructing the Database: Store data on storage media, effectively creating the
database environment.
3. Manipulating the Database: Query, update, and generate reports from the data.
Examples include retrieving student grades or updating records.
4. Sharing the Database: Allow multiple users and applications to access and modify
data concurrently, ensuring efficiency and data integrity.

Structure of a Database

- Data is stored in tables, which are similar to spreadsheets with rows and columns.
- Each table contains records (rows) and fields (columns).
- Fields (columns) represent specific data types like name, age, marks, etc.
- Records (rows) contain complete information about an object, e.g., a student's full details.
- Example: Student table with columns for roll number, name, marks, email, etc.

Types of Data and Data Storage

- Data types include:

 Numeric: Integer, float, double, smallint, mediumint, bigint.

 String/Text: CHAR, VARCHAR, TEXT, MEDIUMTEXT, LONGTEXT.
 Date and Time: DATE, TIME, DATETIME, TIMESTAMP.

- Constraints like NOT NULL, UNIQUE, and DEFAULT can be applied to ensure data
integrity.
- Data types determine how data is stored and the size it occupies.

Keys and Constraints

  Primary Key: Ensures each record is uniquely identifiable. Example: roll number,
email, phone number.
 Composite Primary Key: Consists of multiple columns combined to uniquely
identify a record, e.g., name + email.
 Candidate Keys: Multiple attributes that could serve as primary keys; one is chosen
as primary, others are alternate keys.
 Unique Key: Ensures no duplicate values in a column.
 Foreign Key: Links tables, maintaining referential integrity.

Relationships in Databases

- Relationships are established via primary and foreign keys.

- Example: Student table linked to Course table through course ID.
- The relational model organizes data in related tables, enabling complex queries and data
retrieval.
- Referential integrity rules prevent orphaned records and maintain data consistency.

SQL and Query Language

- SQL (Structured Query Language) is the standard language for managing relational
databases.
- Developed initially by IBM, later adopted widely.
- SQL commands are categorized into:

 DDL (Data Definition Language): CREATE, ALTER, DROP, RENAME.

 DML (Data Manipulation Language): INSERT, UPDATE, DELETE, SELECT.
 DCL (Data Control Language): GRANT, REVOKE.
 TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.

- Commands are used to define, manipulate, control access, and manage transactions.

Practical SQL Commands

 Create Database/Table: CREATE DATABASE db_name; CREATE TABLE

table_name (...);
 Alter Table: ALTER TABLE table_name ADD COLUMN, DROP COLUMN,
MODIFY COLUMN.
 Insert Data: INSERT INTO table_name (columns) VALUES (values);
 Update Data: UPDATE table_name SET column = value WHERE condition;
 Delete Data: DELETE FROM table_name WHERE condition;
 Drop Table/Database: DROP TABLE table_name; DROP DATABASE db_name;
 Rename: RENAME TABLE old_name TO new_name;

MySQL and Its Features

- MySQL is an open-source RDBMS, freely available, and widely used.

- Developed by MySQL AB, later acquired by Sun Microsystems, now owned by Oracle.
- Known for speed, reliability, scalability, and cost-effectiveness.
- Supports large databases (up to 50 million records, 60,000 tables).
- Compatible across platforms and operating systems.
- Supports multiple languages and localization.
- Provides security features like password encryption and user permissions.
- Can be installed on servers and accessed remotely via client-server architecture.

Client-Server Architecture

- MySQL operates on a client-server model.

- The server hosts the database; clients connect over a network.
- Clients send SQL queries to the server, which processes and returns results.
- Example: Accessing Facebook's database or school management system remotely.

Advantages of DBMS and MySQL

 Speed: Fast query processing.

 Ease of Use: User-friendly interfaces, command line, GUI tools.
 Cost: Open source and free.0
 Security: Password encryption, user permissions.
 Scalability: Handles large data volumes and many tables.
 Portability: Works across different operating systems.
 Data Integrity: Ensures data accuracy and consistency.
 Connectivity: Supports multiple protocols and languages.

Data Types and Constraints

- Data types define what kind of data can be stored:

 Integer (INT, SMALLINT, BIGINT)

 Float, Double for decimal numbers
 CHAR, VARCHAR, TEXT for strings
 DATE, TIME, DATETIME for date/time data

- Constraints like NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY
enforce data integrity.
- Example: Age as INTEGER, Name as VARCHAR(50), Date of Birth as DATE.

Data Storage and Optimization

- Choosing appropriate data types optimizes storage space.

- For example, using TINYINT for small numbers saves space.
- String types like CHAR and VARCHAR differ in fixed vs. variable length.
- Constraints prevent invalid data entry.
- Indexing improves query speed, especially on large datasets.
SQL Elements in Sentences (MySQL)

These are the basic parts used to write SQL commands. Let’s understand each one clearly.

✅ 1. Literals

Literals are fixed values like numbers, text, or dates that are directly written in SQL
commands.

📌 Example:

sql
CopyEdit
SELECT 'Class 12' AS Class;

🔹 Here, 'Class 12' is a string literal.

✅ 2. Identifiers

Identifiers are names given to database objects like tables, columns, or databases.

📌 Example:

sql
CopyEdit
SELECT Name FROM Students;

🔹 Students is a table identifier, and Name is a column identifier.

✅ 3. Operators

Operators are used to perform operations like comparisons, calculations, or logic checks.

📌 Example:

sql
CopyEdit
SELECT * FROM Students WHERE Age > 16 AND Name LIKE 'A%';

🔹 >, AND, and LIKE are operators.

✅ 4. Expressions
An expression is a combination of literals, column names, and operators that gives a
value.

📌 Example:

sql
CopyEdit
SELECT Marks + Bonus AS Total FROM Results;

🔹 Marks + Bonus is an expression that adds two columns.

✅ 5. Clauses

Clauses are parts of SQL statements, like SELECT, FROM, WHERE, etc. Each has a specific job.

📌 Example:

sql
CopyEdit
SELECT Name FROM Students WHERE Age > 17;

🔹 SELECT, FROM, and WHERE are clauses.

✅ 6. Statements

A statement is a complete SQL instruction that performs an action like selecting, inserting,
or updating data.

📌 Example:

sql
CopyEdit
INSERT INTO Students VALUES (101, 'Ravi', 17);

🔹 This is an INSERT statement that adds a new record.

✅ 7. Comments

Comments are notes written in SQL code to explain what it does. They are not executed by
MySQL.

📌 Example:

sql
CopyEdit
-- This query selects all students
SELECT * FROM Students;

Refer text book 456

📋 1. Students

Stores basic details of students.

Students Table
sql
CopyEdit
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Class INT,
Gender VARCHAR(10)
);

RollNo Name Age Class Gender

101 Ravi 17 12 Male

102 Meera 16 11 Female

103 Arjun 18 12 Male

✅ 1. SELECT Command (Basic)

Used to retrieve data from one or more columns of a table.

🔹 Syntax:
sql
CopyEdit
SELECT column1, column2 FROM table_name;

🔹 Example:
sql
CopyEdit
SELECT Name, Age FROM Students;

This will display the Name and Age of all students.

 RollNo Name Age Class Gender

✅ 2. SELECT All Columns

Use * to select all columns in a table.

🔹 Syntax:
sql
CopyEdit
SELECT * FROM table_name;

🔹 Example:
sql
CopyEdit
SELECT * FROM Students;

✅ 3. SELECT with WHERE Clause

Used to filter records using conditions.

🔹 Syntax:
sql
CopyEdit
SELECT column1 FROM table_name WHERE condition;

🔹 Example:
sql
CopyEdit
SELECT Name FROM Students WHERE Age > 16;

✅ 4. SELECT with DISTINCT

Eliminates duplicate values from the result.

🔹 Example:
sql
CopyEdit
SELECT DISTINCT Class FROM Students;

✅ 5. SELECT with ORDER BY

Used to sort the result in ascending (default) or descending order.

 RollNo Name Age Class Gender

🔹 Syntax:
sql
CopyEdit
SELECT * FROM table_name ORDER BY column ASC|DESC;

🔹 Example:
sql
CopyEdit
SELECT * FROM Students ORDER BY Age DESC;

✅ 6. SELECT with Calculations

You can perform arithmetic operations inside a SELECT statement.

🔹 Example:
sql
CopyEdit
SELECT Name, Marks + 5 AS BonusMarks FROM Marks;

✅ 7. SELECT with Aliases (AS keyword)

Use AS to give a column or expression a new name in the output.

🔹 Example:
sql
CopyEdit
SELECT Name AS StudentName FROM Students;

✅ 8. SELECT with BETWEEN, IN, LIKE

 BETWEEN — range
 IN — multiple values
 LIKE — pattern matching

🔹 Example:
sql
CopyEdit
SELECT * FROM Students WHERE Age BETWEEN 16 AND 18;
SELECT * FROM Students WHERE Class IN (11, 12);
SELECT * FROM Students WHERE Name LIKE 'A%';
 RollNo Name Age Class Gender

✅ 9. SELECT with IS NULL / IS NOT NULL

Used to check for NULL (missing) values.

🔹 Example:
sql
CopyEdit
SELECT * FROM Students WHERE Email IS NULL;

📌 Real Example Using Sample Data

RollNo Name Age Class

101 Ravi 17 12

102 Meera 16 11
sql
CopyEdit
SELECT Name FROM Students WHERE Age = 17;
-- Output: Ravi

✅ 3. Selecting All Rows from a Table

sql
CopyEdit
SELECT * FROM Students;

This selects all rows and all columns from the Students table.

✅ 4. ALL Keyword

The ALL keyword is used with relational operators like > ALL, < ALL
to compare a value against all values in a subquery.

🔹 Example:

sql
CopyEdit
SELECT Name FROM Students
WHERE Age > ALL (SELECT Age FROM Students WHERE Class =
11);

6. Scalar Expression with Selected Fields

 RollNo Name Age Class Gender

A scalar expression returns a single value — such as performing a

calculation with fields.

🔹 Example:

sql
CopyEdit
SELECT Name, Marks, Marks + 5 AS BonusMarks FROM Marks;

This adds 5 to each student's marks and shows it as BonusMarks.

✅ 7. Handling NULL Values

Check for missing values using IS NULL or IS NOT NULL.

🔹 Example:

sql
CopyEdit
SELECT Name FROM Students WHERE Email IS NULL;

Returns students with no email stored.

✅ 8. Putting Text into Query Outputs

You can include fixed text using string literals or aliases.

🔹 Example:

sql
CopyEdit
SELECT Name, 'Class 12 Student' AS Tag FROM Students;

Adds a new column Tag with text for all rows.

✅ 9. Relational Operators in SQL

Used for comparison.

Operator Meaning
= Equal to
 RollNo Name Age Class Gender

Operator Meaning

!= or <> Not equal to

> Greater than

< Less than

>= Greater or equal

<= Less or equal

🔹 Example:

sql
CopyEdit
SELECT * FROM Students WHERE Age >= 17;

✅ 10. Logical Operators in SQL

Used to combine multiple conditions.

Operator Meaning

AND Both conditions true

OR At least one true

NOT Reverses the condition

🔹 Example:

sql
CopyEdit
SELECT * FROM Students WHERE Age > 16 AND Class = 12;

✅ 11. Condition Based on a Range — BETWEEN

sql
CopyEdit
SELECT * FROM Students WHERE Age BETWEEN 16 AND 18;

Returns students with age in the range 16 to 18 (inclusive).

✅ 12. Condition Based on a List — IN

 RollNo Name Age Class Gender
sql
CopyEdit
SELECT * FROM Students WHERE Class IN (11, 12);

Returns students from class 11 or 12.

✅ 13. Operator Precedence (Order of Evaluation)

Precedence Level Operators

1 (Highest) *, /, %

2 +, -

3 Comparison operators =, >, <

4 NOT

5 AND

6 (Lowest) OR

🔹 Example:

sql
CopyEdit
SELECT * FROM Students WHERE Age > 16 AND Class = 12 OR
Gender = 'Female';

🧠 Tip: Use brackets for clarity:

sql
CopyEdit
SELECT * FROM Students WHERE (Age > 16 AND Class = 12)
OR Gender = 'Female';

✅ 14. Reordering Columns in Query Result

Change column display order by reordering them in SELECT.

🔹 Example:

sql
CopyEdit
SELECT Name, Class, RollNo FROM Students;

5. Viewing Structure of a Table

 RollNo Name Age Class Gender
sql
CopyEdit
DESC Students;
-- or
DESCRIBE Students;

This shows column names, data types, NULL permissions, keys, etc.

1. Creating a Database
sql
CopyEdit
CREATE DATABASE SchoolDB;

Creates a new database named SchoolDB.

✅ 2. Accessing/Using a Database
sql
CopyEdit
USE SchoolDB;

This command selects SchoolDB as the active database.

1. String Functions

These work on text data (VARCHAR, CHAR) and help in

manipulating strings.

Function Description Example

Returns
SELECT LENGTH('Rashmika');
LENGTH(str) length in → 8
bytes

Returns
SELECT
CHAR_LENGTH(str) length in CHAR_LENGTH('Hi😊'); → 3
characters
CONCAT(str1, Joins two or SELECT CONCAT('Hello', '
 RollNo Name Age Class Gender

Function Description Example

str2) more strings World'); → 'Hello World'

UPPER(str) or Converts to SELECT UPPER('rashmika');

UCASE(str) uppercase → 'RASHMIKA'

LOWER(str) or Converts to SELECT LOWER('MySQL'); →

LCASE(str) lowercase 'mysql'

SELECT
SUBSTRING(str, Extracts part
SUBSTRING('Computer', 1,
start, length) of a string 4); → 'Comp'

Removes
SELECT TRIM(' Hello '); →
TRIM(str) spaces from 'Hello'
both ends
SELECT REPLACE('Hello
REPLACE(str, Replaces part
Rax', 'Rax', 'Rashmika');
from, to) of a string → 'Hello Rashmika'

🔢 2. Numeric Functions

These are used to perform calculations and return numeric results.

Function Description Example

Returns absolute
ABS(x) SELECT ABS(-10); → 10
value

Returns x raised to
POWER(x, y) SELECT POWER(2, 3); → 8
power y

SQRT(x) Returns square root SELECT SQRT(16); → 4

Rounds value x to d SELECT ROUND(12.456,

ROUND(x, d)
decimals 2); → 12.46

CEIL(x) or
Smallest integer ≥ x SELECT CEIL(4.2); → 5
CEILING(x)

FLOOR(x) Largest integer ≤ x SELECT FLOOR(4.8); → 4

MOD(x, y) or x % Remainder after SELECT MOD(10, 3); → 1

y division

📆 3. Date and Time Functions

 RollNo Name Age Class Gender

These functions are used to work with dates and times.

Function Description Example

Returns current SELECT NOW(); → '2025-

NOW()
date and time 08-02 23:40:10'

Returns current SELECT CURDATE(); →

CURDATE()
date '2025-08-02'

Returns current SELECT CURTIME(); →

CURTIME()
time '23:40:10'

SELECT YEAR('2024-01-
YEAR(date) Extracts year 01'); → 2024

Extracts month SELECT MONTH('2025-08-

MONTH(date)
(1–12) 02'); → 8

DAY(date) or Extracts day SELECT DAY('2025-08-

DAYOFMONTH(date) from date 02'); → 2

Returns name SELECT DAYNAME('2025-

DAYNAME(date)
of the day 08-02'); → 'Saturday'

Returns name SELECT MONTHNAME('2025-

MONTHNAME(date)
of the month 08-02'); → 'August'
SELECT DATEDIFF('2025-
DATEDIFF(date1, Difference in
12-31', '2025-08-02');
date2) days → 151

What are Aggregate Functions?

Aggregate functions perform calculations on a group of values (usually a column) and

return a single value as a result.

They are commonly used with GROUP BY and HAVING clauses to summarize data.

🔹 Common Aggregate Functions in MySQL

Function Description Example

SUM() Adds up all values in a column SUM(Salary) gives total salary

AVG() Returns the average (mean) AVG(Marks) returns average marks

MIN() Returns the smallest value MIN(Age) returns youngest age

MAX() Returns the largest value MAX(Price) returns highest price

Function Description Example

COUNT() Counts total number of values/rows COUNT(*) or COUNT(Name)

🧪 Examples:

Let’s say we have a table called Students:

RollNo Name Class Marks

101 Aditi 12 87

102 Rohan 12 76

103 Meena 11 92

104 Akash 11 NULL

105 Tara 12 81

🔢 1. SUM()
sql
CopyEdit
SELECT SUM(Marks) AS TotalMarks FROM Students;

Result: Adds all non-null marks: 87 + 76 + 92 + 81 = 336

📐 2. AVG()
sql
CopyEdit
SELECT AVG(Marks) AS AverageMarks FROM Students;

Result: 336 ÷ 4 = 84 (Ignores NULL)

🧮 3. MIN() and MAX()

sql
CopyEdit
SELECT MIN(Marks) AS Lowest, MAX(Marks) AS Highest FROM Students;

Result: Lowest = 76, Highest = 92

🔢 4. COUNT()
sql
CopyEdit
SELECT COUNT(Marks) AS TotalWithMarks FROM Students;
SELECT COUNT(*) AS TotalStudents FROM Students;

 COUNT(Marks) → 4 (ignores NULL)

 COUNT(*) → 5 (counts all rows)

📊 5. Using with GROUP BY

sql
CopyEdit
SELECT Class, AVG(Marks) AS AvgMarks
FROM Students
GROUP BY Class;

This groups students by class and gives average marks per class.

🕵️6. Using HAVING clause

sql
CopyEdit
SELECT Class, COUNT(*) AS StudentCount
FROM Students
GROUP BY Class
HAVING COUNT(*) > 1;

Shows only classes with more than 1 student.