Class 1: Introduction and Setup

• Overview of databases and MySQL

• Installation (Windows/Linux)

• MySQL client programs, connecting to MySQL

• Database concepts (DBMS, RDBMS, types of models)

Class 2: Database and Table Creation

• Creating databases and tables (CREATE DATABASE, CREATE TABLE)

• Data types in MySQL

• Primary keys, foreign keys, constraints, NULL values, NOT NULL

• Altering and dropping tables

Class 3: Basic Data Operations

• Inserting, updating, and deleting data (INSERT, UPDATE, DELETE)

• Using WHERE clause, Logic operators, Comparison operators

• Fetching data: SELECT statement

Class 4: Advanced Data Retrieval

• Sorting and filtering results (ORDER BY, DISTINCT)

• Aggregate functions (SUM, AVG, MAX, MIN, COUNT)

• Grouping data (GROUP BY, HAVING)

• Pattern matching (LIKE, BETWEEN, IN)

Class 5: Joins and Subqueries

• Understanding and implementing joins (INNER, LEFT, RIGHT, FULL, SELF)

• Unions, except, and intersect operations

• Subqueries: Inline views and using “IN”

Class 6: Functions and Data Manipulation

• Using string, date, and control flow functions

• Data import/export (CSV, custom formats)

 • Modifying existing records, working with auto-increment

Class 7: Indexes, Views, and Data Security

• Creating indexes, understanding performance

• Creating and modifying views (simple and with check option)

• User management: creating users, assigning privileges

• Backup, restore, and database migration basics

Class 8: Stored Procedures, Triggers, and Optimization

• Creating and using stored procedures and functions

• Triggers: Understanding, creating, and dropping

• Scheduled events in MySQL

• Database optimization techniques: queries, indexing, analytics

Class 9: Project Work and Real-World Applications

• Planning and building a mini project (student database, payroll, etc.)

• Presenting, reporting, and exporting results

• Best practices, troubleshooting common errors

Class 2: Database and Table Creation

Creating a Database

Basic Syntax and Example

• CREATE DATABASE statement is used to create a new database.

• Syntax:

CREATE DATABASE database_name;

• Example:

CREATE DATABASE testDB;

• To list databases and verify creation.

SHOW DATABASES;

Optional Parameters

• To conditionally create if not present:

CREATE DATABASE IF NOT EXISTS testDB;

• Character set and collation (custom languages/encoding):

CREATE DATABASE testDB

CHARACTER SET utf8mb4

COLLATE utf8mb4_unicode_ci;

• Always ensure admin/CREATE privileges before issuing these commands.

Selecting a Database for Use

• Before creating tables, select the database:

USE testDB;

• In tools like Workbench, right-click and choose “Set as Default Schema”.

Creating Tables

Basic Table Structure

• Tables store data in rows (records) and columns (fields).

• Syntax:
 CREATE TABLE table_name (

column1 datatype PRIMARY KEY,

column2 datatype,

...

);

• Example:

CREATE TABLE students (

student_id INT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

age INT,

email VARCHAR(100)

);

Data Types

• Common data types:

o INT: Integers

o VARCHAR(n): Variable-length strings

o DATE: Date values

o FLOAT/DECIMAL: Decimal values

o TEXT, BOOLEAN

Constraints

• PRIMARY KEY: Uniquely identifies each row.

• NOT NULL: Ensures column cannot have empty value.

• UNIQUE: Prevents duplicate values in column.

• FOREIGN KEY: Links to another table’s column, enforcing referential integrity.

Advanced Options

• Auto-increment for serial numbers:

 student_id INT PRIMARY KEY AUTO_INCREMENT

• Table-level constraints, default values, etc.

Modifying and Dropping Tables

• Change a table structure (add/remove columns):

ALTER TABLE students ADD COLUMN address VARCHAR(255);

ALTER TABLE students DROP COLUMN age;

• Remove a table:

DROP TABLE students;

Practice Activities

• Create a database and select it using the mysql client or Workbench.

• Create a table with at least three columns, use PRIMARY KEY and NOT NULL constraints.

• Try altering and dropping a table.

Data Types

MySQL supports several categories of data types to organize and store data efficiently. Knowing
the right data type is critical for database design and performance.

String Data Types

• CHAR(size): Stores a fixed-length string, padded with spaces if shorter than the defined
size. Ideal for codes, fixed values. E.g., CHAR(10).

• VARCHAR(size): Stores variable-length strings, saving space for short entries. E.g.,
VARCHAR(50) holds up to 50 characters.

• BINARY(size) and VARBINARY(size): Store binary byte strings (not text), used for binary
data like files/images.

• TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT: Store large strings; suitable for comments,

descriptions. Length varies: up to 255 (TINYTEXT), 65K (TEXT), 16M (MEDIUMTEXT), 4GB
(LONGTEXT).

• BLOB types: Store binary large objects (BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB) for
files, images, etc.
 • ENUM: Stores a single value chosen from a list. E.g., ENUM('male', 'female').

• SET: Stores zero or more values from a list; good for multi-select scenarios.

Numeric Data Types

• BIT(size): Bit-value, useful for flags and binary data.

• TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT: Integer data types; range depends on
type. E.g., INT stores from -2147483648 to 2147483647.

• FLOAT, DOUBLE: Store approximate numeric values with decimals, used for scientific or
financial calculations.

• DECIMAL(size, d): Stores exact fixed-point numbers for high-precision values, ideal for
money.

• BOOL/BOOLEAN: Boolean values (TRUE/FALSE); internally stored as TINYINT.

Date and Time Data Types

• DATE: Stores date in YYYY-MM-DD format. Range: 1000-01-01 to 9999-12-31.

• DATETIME: Stores date and time as YYYY-MM-DD hh:mm:ss.

• TIMESTAMP: Similar to DATETIME, but also used to track changes (auto-updated).

• TIME: Stores time values, hh:mm:ss.

• YEAR: Stores year values (four digits).

Other Data Types

• JSON: For structured non-relational data.

• Spatial Types: For locations, geometry, etc.

Sample Table Creation

sql

CREATE TABLE sample (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

gender ENUM('male','female'),

join_date DATE,
 salary DECIMAL(8,2),

details TEXT

);

This table shows use of INT, VARCHAR, ENUM, DATE, DECIMAL, and TEXT.

Constraints

MySQL constraints are essential rules applied to columns or tables to enforce the integrity,
validity, and reliability of data stored in a database.

Types of Constraints

NOT NULL

• Ensures a column cannot contain NULL values.

• Example:

name VARCHAR(50) NOT NULL

• Enforces that every row has a value for this column.

UNIQUE

• All values in a column must be distinct.

• Example:

email VARCHAR(100) UNIQUE

• Prevents duplicate entries.

PRIMARY KEY

• Combination of NOT NULL and UNIQUE.

• Used to uniquely identify each record in a table.

• Example:

id INT PRIMARY KEY AUTO_INCREMENT

• Creates a unique index for fast access.

FOREIGN KEY
 • Establishes a link between tables by referencing a column in another table.

• Maintains referential integrity.

• Example:

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES departments(id)

CHECK

• Ensures column values satisfy a specific condition.

• Example:

age INT CHECK (age >= 18)

• Restricts the value range or pattern.

DEFAULT

• Sets a default value for a column if no value is specified on insert.

• Example:

status VARCHAR(10) DEFAULT 'active';

• Simplifies record creation and ensures consistency.

Applying Constraints

• Constraints can be applied at column level (single column) or table level (multiple
columns).

• Syntax in CREATE TABLE:

CREATE TABLE employees (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE,

age INT CHECK (age >= 18),

dept_id INT,

status VARCHAR(10) DEFAULT 'active',

 FOREIGN KEY (dept_id) REFERENCES departments(id)

);

Importance and Usage

• Data Accuracy: Ensures only valid and expected data is stored.

• Reliability: Prevents common errors—no duplicate, missing, or out-of-range values.

• Integrity: Maintains relationships between tables and enforces business logic at the
database level.

ALTER TABLE

The ALTER TABLE statement is used to change the structure of an existing table without losing
the data stored in it. It allows:

• Adding new columns

• Modifying existing columns

• Dropping columns

• Renaming columns

• Renaming the entire table

• Adding or dropping constraints

Syntax Patterns

ALTER TABLE table_name action;

Where action can be:

• ADD column_name datatype

• DROP COLUMN column_name

• MODIFY COLUMN column_name datatype

• RENAME COLUMN old_name TO new_name

• RENAME TO new_table_name

Common Use Cases with Examples

1. Add Column
Adds a new column at the end of the table.

ALTER TABLE Students ADD Email VARCHAR(255);

• Adds a column called Email of type VARCHAR(255) to the Students table.

• Initially, this column will have NULL in existing rows.

2. Modify Column

Changes the data type or definition of an existing column.

ALTER TABLE Students MODIFY COLUMN Email VARCHAR(100);

• Changes the Email column to reduce its size to VARCHAR(100).

• You can also change other attributes like NULL/NOT NULL here.

3. Drop Column

Removes a column and its data from the table permanently.

ALTER TABLE Students DROP COLUMN Email;

• Deletes the Email column from the Students table.

4. Rename Column

Renames an existing column without changing its data type.

ALTER TABLE Students RENAME COLUMN Email TO ContactEmail;

• Changes the column name from Email to ContactEmail.

5. Rename Table

Changes the name of a table while keeping its structure and data intact.

ALTER TABLE Students RENAME TO Pupils;

• Renames Students table to Pupils.

Example: Step-by-step Table Alterations

Suppose this is the initial table:

CREATE TABLE Student (

ROLL_NO INT,

NAME VARCHAR(50)
 );

Initial data:

ROLL_NO NAME

1 Emma

2 Travis

• Add columns AGE and COURSE:

ALTER TABLE Student ADD (AGE INT, COURSE VARCHAR(40));

ROLL_NO NAME AGE COURSE

1 Emma NULL NULL
2 Travis NULL NULL
• Modify COURSE to be VARCHAR(20):

ALTER TABLE Student MODIFY COURSE VARCHAR(20);

• Drop COURSE column:

ALTER TABLE Student DROP COLUMN COURSE;

• Rename column NAME to FullName:

ALTER TABLE Student RENAME COLUMN NAME TO FullName;

• Rename table Student to Pupils:

ALTER TABLE Student RENAME TO Pupils;

Important Notes

• Always back up your database before performing structural modifications.

• Some operations may cause table locks during execution.

• DROP COLUMN will remove the column and all its data irreversibly.

• MODIFY changes column attributes such as data type; CHANGE can rename and modify
simultaneously.

Adding Constraints

Add constraints using ALTER TABLE ... ADD CONSTRAINT syntax:

 • Add Primary Key:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);

• Add Unique Constraint:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column);

• Add Foreign Key:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name FOREIGN KEY (column)

REFERENCES parent_table(parent_column)

ON DELETE CASCADE ON UPDATE CASCADE;

• Add Check Constraint:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK (column > 0);

Dropping Constraints

• To drop a named constraint:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

• For foreign keys, MySQL requires:

ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name;

• For primary keys:

ALTER TABLE table_name DROP PRIMARY KEY;

• Finding constraint names is done by querying the

INFORMATION_SCHEMA.TABLE_CONSTRAINTS view or via SHOW CREATE TABLE
table_name.

Modifying Constraints (Via Drop and Recreate)

 • Direct alteration is not supported, to modify a foreign key (for example, to change ON
DELETE behavior), follow these steps:

1. Drop the existing foreign key constraint:

ALTER TABLE child_table DROP FOREIGN KEY fk_name;

2. Add a new foreign key with new properties:

ALTER TABLE child_table

ADD CONSTRAINT fk_name FOREIGN KEY (column)

REFERENCES parent_table(parent_column)

ON DELETE CASCADE;

• For primary keys or unique constraints, follow similar drop and add steps within one
transaction to avoid periods without a constraint.

Renaming Constraints

• Renaming constraints is possible via:

ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;

• This allows better naming conventions or clarity without dropping and recreating
constraints (availability depends on MySQL version).

Important Notes and Best Practices

• Back up data before altering constraints as structural changes can cause errors or lead to
data loss.

• Ensure no data violates the new constraint when adding or enforcing constraints,
otherwise the operation will fail.

• Drop and recreate constraints in a transaction (if supported) to maintain table integrity
without downtime.

• Check enforcement status for CHECK constraints (can switch between enforced and non-
enforced).

• Use schema inspection commands to identify existing constraint names prior to altering.