MySQL Database - 12 Lecture Complete Notes & Queries
Lecture 1: Introduction to Databases & MySQL
Topics Covered:
What is DBMS and RDBMS
Installing MySQL
Basic MySQL Commands
Example Queries:
SHOW DATABASES;
CREATE DATABASE college_db;
USE college_db;
SHOW TABLES;
Lecture 2: Creating Tables
Topics Covered:
Data Types
Primary Keys
NOT NULL and AUTO_INCREMENT
Example Queries:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT,
city VARCHAR(50)
);
Lecture 3: Insert, Update & Delete Data
Topics Covered:
INSERT Query
UPDATE Query
DELETE Query
Example Queries:
INSERT INTO students(name, age, city)
VALUES ('Ali', 20, 'Karachi');
UPDATE students
SET city='Hyderabad'
WHERE id=1;
DELETE FROM students WHERE id=1;
Lecture 4: SELECT Queries
Topics Covered:
Selecting Records
Filtering Data
Sorting Results
Example Queries:
SELECT * FROM students;
SELECT name, city FROM students WHERE age > 18;
SELECT * FROM students ORDER BY age DESC;
Lecture 5: Operators & Conditions
Topics Covered:
AND, OR, NOT
LIKE Operator
BETWEEN Operator
Example Queries:
SELECT * FROM students WHERE age > 18 AND city='Karachi';
SELECT * FROM students WHERE name LIKE 'A%';
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
Lecture 6: Aggregate Functions
Topics Covered:
COUNT()
SUM()
AVG(), MAX(), MIN()
Example Queries:
SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;
SELECT MAX(age), MIN(age) FROM students;
Lecture 7: GROUP BY & HAVING
Topics Covered:
Grouping Data
Filtering Groups
Example Queries:
SELECT city, COUNT(*) FROM students GROUP BY city;
SELECT city, COUNT(*) FROM students GROUP BY city HAVING COUNT(*) > 1;
Lecture 8: Joins
Topics Covered:
INNER JOIN
LEFT JOIN
RIGHT JOIN
Example Queries:
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
SELECT [Link], courses.course_name
FROM students
INNER JOIN courses
ON [Link] = courses.course_id;
Lecture 9: Constraints
Topics Covered:
PRIMARY KEY
FOREIGN KEY
UNIQUE
Example Queries:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY(student_id) REFERENCES students(id)
);
Lecture 10: Subqueries
Topics Covered:
Nested Queries
IN and EXISTS
Example Queries:
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);
SELECT * FROM students WHERE id IN (SELECT student_id FROM orders);
Lecture 11: Views & Indexes
Topics Covered:
Creating Views
Indexes for Performance
Example Queries:
CREATE VIEW student_view AS SELECT name, city FROM students;
CREATE INDEX idx_name ON students(name);
Lecture 12: Backup, Users & Permissions
Topics Covered:
Creating Users
Granting Permissions
Database Backup
Example Queries:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON college_db.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;
mysqldump -u root -p college_db > [Link]