1.

Introduction to Database

A database is an organized collection of data stored electronically, enabling easy retrieval,

insertion, update, and deletion.

Need for Database:

 Avoids data redundancy

 Maintains data integrity

 Enables data sharing

 Secure and easy to access

2. Relational Data Model – Key Terms

Term Meaning

Relation A table in a database

Attribute A column in a table

Tuple A row in a table

Domain The set of possible values an attribute can take

Degree Number of columns in a table

Cardinality Number of rows in a table

Candidate Key A column or set of columns that can uniquely identify a row

Primary Key The chosen candidate key for unique identification

Alternate Key Candidate keys not chosen as primary

Foreign Key A column in one table that refers to the primary key in another table

3. Sample Database

Let’s create two tables: Employee and Department.

CREATE DATABASE CompanyDB;

USE CompanyDB;

CREATE TABLE Department (

 DeptID INT PRIMARY KEY,

DeptName VARCHAR(20) NOT NULL UNIQUE

);

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(30) NOT NULL,

DeptID INT,

Salary FLOAT,

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

);

Insert Data

INSERT INTO Department VALUES

(1, 'HR'),

(2, 'IT'),

(3, 'Finance');

INSERT INTO Employee VALUES

(101, 'Alice', 1, 50000),

(102, 'Bob', 2, 60000),

(103, 'Charlie', 1, 55000),

(104, 'David', 3, 45000);

4. Viewing Tables

SELECT * FROM Employee;

Output:

EmpID EmpName DeptID Salary

101 Alice 1 50000

102 Bob 2 60000

EmpID EmpName DeptID Salary

103 Charlie 1 55000

104 David 3 45000

SELECT * FROM Department;

Output:

DeptID DeptName

1 HR

2 IT

3 Finance

5. Basic Commands

Show databases

SHOW DATABASES;

(Lists all databases in the system.)

Describe a table

DESCRIBE Employee;

Dec employee;

Output:

Field Type Null Key Default Extra

EmpID int NO PRI NULL
EmpName varchar(30 NO NULL
)
DeptID int YES MUL NULL
Salary float YES NULL

6. Alter Table
Add a column:

ALTER TABLE Employee ADD Email VARCHAR(50);

Remove a column:

ALTER TABLE Employee DROP COLUMN Email;

Add a primary key:

ALTER TABLE Employee ADD PRIMARY KEY (EmpID);

🔧 1. Add a New Column

ALTER TABLE students ADD COLUMN age INT;

You can also specify the position:

ALTER TABLE students ADD COLUMN city VARCHAR(100) AFTER name;

✏️2. Modify an Existing Column

Change data type, length, or constraints:

ALTER TABLE students MODIFY COLUMN name VARCHAR(200);

Or make it NOT NULL:

ALTER TABLE students MODIFY COLUMN age INT NOT NULL;

🔁 3. Change Column Name and Type (Using CHANGE )

ALTER TABLE students CHANGE COLUMN city location VARCHAR(150);

Note: CHANGE requires you to specify both the old column name and the new column name
and type.

✂️4. Drop a Column

ALTER TABLE students DROP COLUMN location;

🔑 5. Add a Primary Key

ALTER TABLE students ADD PRIMARY KEY (id);

Make sure the column is already defined as NOT NULL and unique.
🔑 6. Add a Foreign Key
ALTER TABLE enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id)
REFERENCES students(id);

🚫 7. Drop a Foreign Key

First, find the constraint name:

SHOW CREATE TABLE enrollments;

Then:

ALTER TABLE enrollments DROP FOREIGN KEY fk_student;

📛 8. Rename Table
ALTER TABLE students RENAME TO learners;

🔄 11. Set or Remove Default Values

Set default:

ALTER TABLE students ALTER COLUMN age SET DEFAULT 18;

Remove default:

ALTER TABLE students ALTER COLUMN age DROP DEFAULT;

📋 12. Multiple Alterations in One Statement

ALTER TABLE students
ADD COLUMN gender VARCHAR(10),
MODIFY COLUMN name VARCHAR(150),
DROP COLUMN city;

7. Delete Table

DROP TABLE Employee;

8. Insert, Delete, Update

INSERT INTO Employee VALUES (105, 'Eva', 2, 70000);

DELETE FROM Employee WHERE EmpID = 105;

UPDATE Employee SET Salary = 65000 WHERE EmpName = 'Bob';

✅ 1. Basic UPDATE
Update a single column for matching rows:

UPDATE students
SET age = 20
WHERE id = 1;

🧾 2. UPDATE Multiple Columns

UPDATE students
SET name = 'Alice', age = 22
WHERE id = 2;

🔁 3. UPDATE Without WHERE (⚠️updates all rows)

UPDATE students
SET city = 'Unknown';

⚠️This will update all rows! Always use WHERE unless you mean it.

🎯 4. UPDATE with Expressions

UPDATE students
SET age = age + 1
WHERE city = 'Delhi';

You can perform arithmetic on the column values.

🔎 5. UPDATE with LIKE Pattern Matching

UPDATE students
SET city = 'Mumbai'
WHERE name LIKE 'A%';

🔘 6. UPDATE with IN / NOT IN

UPDATE students
SET status = 'active'
WHERE city IN ('Delhi', 'Mumbai');
UPDATE students
SET status = 'inactive'
WHERE city NOT IN ('Delhi', 'Mumbai');

📌 7. UPDATE with ORDER BY and LIMIT

UPDATE students
SET age = 18
ORDER BY id ASC
LIMIT 3;

Updates only the first 3 rows ordered by ID.

🔗 8. UPDATE Using JOINs

Update one table using data from another.

UPDATE students s
JOIN enrollments e ON s.id = e.student_id
SET s.status = 'enrolled'
WHERE e.course_id = 1;

🧠 9. UPDATE with Subquery

Update based on the result of a subquery:

UPDATE students
SET age = (SELECT AVG(age) FROM students)
WHERE id = 5;

9. Select with Clauses

SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;

SELECT DISTINCT DeptID FROM Employee;

SELECT * FROM Employee WHERE Salary BETWEEN 45000 AND 60000;

SELECT * FROM Employee WHERE EmpName LIKE 'A%';

✅ 1. Basic SELECT
SELECT * FROM students;

Returns all columns and rows from the students table.

SELECT name, age FROM students;

Returns only name and age columns.

🎯 2. SELECT DISTINCT
SELECT DISTINCT city FROM students;

Returns unique (distinct) values in the city column.

🧮 3. SELECT with WHERE Condition

SELECT * FROM students WHERE age > 18;

Filters rows where age is greater than 18.

🧠 4. SELECT with Aliases (AS)

SELECT name AS student_name, age AS student_age FROM students;

Renames columns in the output.

📊 5. SELECT with Aggregate Functions

SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;
SELECT MAX(age) FROM students;
SELECT MIN(age) FROM students;
SELECT SUM(marks) FROM students;

📌 6. SELECT with GROUP BY

SELECT city, COUNT(*) FROM students GROUP BY city;

Groups rows by city and counts students in each city.

🎯 7. SELECT with HAVING (Used with GROUP BY)

SELECT city, COUNT(*) AS total FROM students GROUP BY city HAVING total >
2;

🧩 8. SELECT with ORDER BY

SELECT * FROM students ORDER BY age ASC;
SELECT * FROM students ORDER BY age DESC;

🔍 9. SELECT with LIKE (Pattern Matching)

SELECT * FROM students WHERE name LIKE 'A%'; -- Starts with A
SELECT * FROM students WHERE name LIKE '%n'; -- Ends with n
SELECT * FROM students WHERE name LIKE '%li%'; -- Contains "li"

🧾 10. SELECT with IN / NOT IN

SELECT * FROM students WHERE city IN ('Delhi', 'Mumbai');
SELECT * FROM students WHERE age NOT IN (18, 20);

🧮 11. SELECT with BETWEEN

SELECT * FROM students WHERE age BETWEEN 18 AND 25;

🔗 12. SELECT with JOINs

SELECT students.name, courses.title
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN courses ON courses.id = enrollments.course_id;

🧱 13. SELECT from Multiple Tables (with aliases)

SELECT s.name, c.title
FROM students s, courses c
WHERE s.id = 1 AND c.id = 2;

⏳ 14. SELECT with LIMIT

SELECT * FROM students LIMIT 5; -- First 5 rows
SELECT * FROM students LIMIT 5 OFFSET 10; -- Rows 11–15

📦 15. SELECT with Subqueries

In SELECT:
SELECT name, (SELECT COUNT(*) FROM enrollments WHERE enrollments.student_id
= students.id) AS total_courses
FROM students;

In WHERE:
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments);

10. Aggregate Functions

SELECT MAX(Salary) FROM Employee;

SELECT MIN(Salary) FROM Employee;

SELECT AVG(Salary) FROM Employee;

SELECT SUM(Salary) FROM Employee;

SELECT COUNT(*) FROM Employee;

11. Group By & Having

SELECT DeptID, AVG(Salary) AS AvgSalary

FROM Employee

GROUP BY DeptID

HAVING AVG(Salary) > 50000;

Output:

DeptID AvgSalary
1 52500
2 60000

12. Joins

Cartesian Product

SELECT * FROM Employee, Department;

Output → Every employee paired with every department.

EmpID EmpName DeptI Salary DeptID DeptName

D
101 Alice 1 50000 1 HR
101 Alice 1 50000 2 IT
101 Alice 1 50000 3 Finance
102 Bob 2 60000 1 HR
... ... ... ... ... ...

Equi-Join

SELECT Employee.EmpID, EmpName, Salary, DeptName

FROM Employee

JOIN Department
ON Employee.DeptID = Department.DeptID;

Output:

EmpI EmpName Salary DeptName

D
101 Alice 50000 HR
103 Charlie 55000 HR
102 Bob 60000 IT
104 David 45000 Finance

Natural Join

SELECT *

FROM Employee

NATURAL JOIN Department;

Output → Same as equi-join here, since both tables share DeptID.

The difference between CHAR and VARCHAR in mainly comes down to storage and
performance:

1. Definition

 CHAR(n) → Fixed-length string that always occupies exactly n characters.

 VARCHAR(n) → Variable-length string that stores only the characters you put, plus 1
or 2 bytes for length information.

2. Storage Behavior

Feature CHAR(n) VARCHAR(n)

Always n characters (pads with

Length Stores only actual length of data.
spaces if shorter).

Pads with spaces to make up the

Padding No padding — stores exactly what you enter.
full length.

Storage Actual length of string + 1 byte (if ≤ 255 chars)

Fixed — n bytes.
Used or 2 bytes (if > 255 chars).

To find the second maximum salary from the following table:

Example Table
Let’s say we have a table Employee:

EmpI Name Salary

D
101 Asha 50000
102 Ravi 65000
103 Meena 72000
104 Arjun 45000
105 Priya 60000
Using MAX() with a subquery

SELECT MAX(Salary) AS SecondMaxSalary

FROM Employee

WHERE Salary < (SELECT MAX(Salary) FROM Employee);

Output:

65000

calculate the total and average of 3 subject marks directly in the query.

Sample Table: Student

RollN Name Sub1 Sub2 Sub3

o
1 Asha 80 75 90
2 Ravi 70 85 95
3 Meena 88 92 85

Query to Calculate Total and Average

SELECT RollNo,

Name,

(Sub1 + Sub2 + Sub3) AS TotalMarks,

(Sub1 + Sub2 + Sub3) / 3.0 AS AverageMarks

FROM Student;

Output:

RollNo Name TotalMarks AverageMarks

1 Asha 245 81.67
 2 Ravi 250 83.33
3 Meena 265 88.33

🔐 Foreign Key in MySQL — Full Explanation with Examples

A foreign key is a constraint used to enforce a relationship between two tables. It ensures
referential integrity — meaning a value in one table must exist in another.

🧱 Basic Concept
Let’s say we have:

 A students table — each student has a class_id.

 A classes table — each class has an id.

We use a foreign key in students.class_id to reference classes.id.

🧪 Create Tables with Foreign Key

-- Parent table
CREATE TABLE classes (
id INT PRIMARY KEY,
class_name VARCHAR(100)
);

-- Child table
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100),
class_id INT,
FOREIGN KEY (class_id) REFERENCES classes(id)
);

✅ Now, students.class_id must match an id in classes.

🧲 Add Foreign Key to Existing Table

ALTER TABLE students
ADD CONSTRAINT fk_class
FOREIGN KEY (class_id) REFERENCES classes(id);

You can name the constraint (fk_class), or let MySQL assign a name automatically.
🔗 1. Equi Join (Equality Join)
✅ Definition:

An Equi Join is a type of INNER JOIN where the condition is based on equality (=)
between columns in the two tables.

🔍 Syntax:
SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

🧱 Example Tables:
students table:
id name class_id

1 Alice 101

2 Bob 102

3 Charlie 101

classes table:
id class_name

101 Math

102 Science

103 History

🧮 Equi Join Example:

SELECT students.name, classes.class_name
FROM students
JOIN classes
ON students.class_id = classes.id;

🔍 Output:
name class_name

Alice Math

Bob Science

Charlie Math
name class_name

✅ Joins rows where students.class_id = classes.id.

🌿 2. Natural Join
✅ Definition:

A Natural Join automatically joins tables based on all columns with the same name and
compatible data types. You don’t need to specify the join condition.

🔍 Syntax:
SELECT *
FROM table1
NATURAL JOIN table2;

⚠️Notes:

 The columns must have the same name and compatible types.
 You don’t specify ON condition — SQL automatically matches based on common
column(s).
 It can behave like an INNER JOIN with implicit condition.

⚙️Let's make sure both tables have a common column with the same name:
-- students table (with 'class_id')
CREATE TABLE students (
id INT,
name VARCHAR(100),
class_id INT
);

-- classes table (with 'class_id')

CREATE TABLE classes (
class_id INT,
class_name VARCHAR(100)
);

🧮 Natural Join Example:

SELECT name, class_name
FROM students
NATURAL JOIN classes;
🔍 Output (same as before):
name class_name

Alice Math

Bob Science

Charlie Math

✅ Automatically joins on class_id because it exists in both tables.

🔍 Key Differences Between Equi Join and Natural Join

Feature Equi Join Natural Join

Automatically joins on common column

Join Condition You must specify the ON condition
names

Control Full control over join columns Less control (automatic)

Column Keeps both columns (e.g.,

Removes duplicate columns from result
Names class_id)

Flexibility More flexible Less flexible, but simpler

Recommended in most Useful for quick queries when column

Usage
production cases names match

✅ Summary
Equi Join:
SELECT s.name, c.class_name
FROM students s
JOIN classes c
ON s.class_id = c.id;

Natural Join:
SELECT name, class_name
FROM students
NATURAL JOIN classes;

🧮 Cartesian Product (CROSS JOIN) in SQL (MySQL)

✅ Definition:

The Cartesian Product, also known as a CROSS JOIN, returns all possible combinations
of rows from two tables.

 If Table A has m rows and Table B has n rows, the result will have m × n rows.
 It does not require any join condition.

🔍 Syntax:
SELECT *
FROM table1
CROSS JOIN table2;

Or just:

SELECT *
FROM table1, table2;

Both forms produce a Cartesian product.

🧱 Example Tables
🎓 students Table:
id name

1 Alice

2 Bob

📚 courses Table:
id course_name

1 Math

2 Science

3 History

🧮 Cartesian Product Example:

SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;
🔍 Output:
name course_name

Alice Math

Alice Science

Alice History

Bob Math

Bob Science

Bob History

✅ Each student is paired with every course, producing 6 combinations (2 students × 3

courses).