SQL Complete Course Notes & Code Reference

Comprehensive MySQL Guide: Database Architecture, DDL, DML, DQL, Joins, Subqueries & Practice Exercises

MySQL 8.0+ Full One-Shot Course Code & Syntax Documentation

1. Introduction to Database & DBMS

A Database is an organized collection of data stored electronically for seamless access, insertion, and manipulation. A
Database Management System (DBMS) acts as the software interface between the end-user and the underlying physical
data storage.

Feature Relational Database (RDBMS) Non-Relational Database (NoSQL)

Data Structure Tables (Rows and Columns) Documents, Key-Value, Graphs, Column-family

Query Language Structured Query Language (SQL) Unstructured / Custom API (JSON/BSON)

Schema Strict / Pre-defined Schema Dynamic / Schema-less

Examples MySQL, PostgreSQL, Oracle, MS SQL Server MongoDB, Cassandra, Redis, DynamoDB

CRUD Operations in SQL

All relational database interactions revolve around four primary operations: Create (Inserting data), Read (Querying data),
Update (Modifying records), and Delete (Removing records).

2. SQL Command Classification

SQL statements are grouped into five major categories based on their operational scope:

Category Full Name Primary Purpose Key Commands

DDL Data Definition Language Defines and alters database schema/ CREATE, ALTER, DROP, TRUNCATE,
structure RENAME

DQL Data Query Language Retrieves data from database tables SELECT

DML Data Manipulation Inserts, updates, or deletes table rows INSERT, UPDATE, DELETE
Language

DCL Data Control Language Manages user permissions and GRANT, REVOKE
privileges

TCL Transaction Control Manages transactional consistency COMMIT, ROLLBACK, SAVEPOINT

Language

3. Database Management & Setup Commands

Commands used to initialize, select, inspect, and remove databases on the MySQL server:

SQL Complete Course Reference | MySQL Page 1 of 7

 -- Create a database if it doesn't already exist
CREATE DATABASE IF NOT EXISTS college;

-- Set the current active database context

USE college;

-- View all databases on the current server instance

SHOW DATABASES;

-- Display all tables within the active database

SHOW TABLES;

-- Drop a database safely

DROP DATABASE IF EXISTS temp_db;

4. Data Types & Column Constraints

Core MySQL Data Types

• CHAR(n) : Fixed-length string (0 to 255 characters). Padded with spaces if text is shorter than n.
• VARCHAR(n) : Variable-length string (0 to 65,535 characters). Dynamic memory allocation; preferred for names/text.
• INT / INTEGER : Standard whole numbers (-2,147,483,648 to 2,147,483,647).
• TINYINT : Small integers (-128 to 127). Unsigned version range is 0 to 255 (used for Boolean flags).
• FLOAT / DOUBLE : Floating-point decimal values (Double provides higher precision).
• DATE / DATETIME : Stores calendar dates (YYYY-MM-DD) or timestamps.

Key Constraints
• NOT NULL: Ensures column cannot hold NULL values.
• UNIQUE: Enforces distinct values across all rows in a column.
• PRIMARY KEY: Uniquely identifies each record. Automatically combines NOT NULL and UNIQUE. (Max 1 per table).
• FOREIGN KEY: Points to the Primary Key of another table, establishing relational integrity.
• DEFAULT: Provides a fallback value when no value is specified during insertion.
• CHECK: Enforces specific boolean logical conditions on data values.

5. Table Creation with Primary & Foreign Keys

-- Parent Table: Department

CREATE TABLE IF NOT EXISTS department (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);

-- Child Table: Teacher (Linked via Foreign Key with Cascading)

CREATE TABLE IF NOT EXISTS teacher (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES department(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

SQL Complete Course Reference | MySQL Page 2 of 7

 Understanding Cascading Actions
ON UPDATE CASCADE: If a department's id changes in the parent table, all corresponding dept_id values in the child table
update automatically.
ON DELETE CASCADE: If a department is deleted, all teachers belonging to that department are automatically removed.

6. Inserting Data (`INSERT INTO`) & Basic Queries

-- Create Student Table

CREATE TABLE student (
rollno INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
marks INT NOT NULL,
grade VARCHAR(2),
city VARCHAR(50)
);

-- Insert Multiple Student Records

INSERT INTO student (rollno, name, marks, grade, city) VALUES
(101, "Anil", 78, "C", "Pune"),
(102, "Bhumika", 93, "A", "Mumbai"),
(103, "Chetan", 85, "B", "Mumbai"),
(104, "Dhruv", 96, "A", "Delhi"),
(105, "Emanuel", 12, "F", "Delhi"),
(106, "Farah", 82, "B", "Delhi");

7. Data Selection & Filtering Clauses (`WHERE`, `ORDER BY`, `LIMIT`)

-- Retrieve all records

SELECT * FROM student;

-- Select specific columns and eliminate duplicates

SELECT DISTINCT city FROM student;

-- Filter using relational and logical operators

SELECT * FROM student WHERE marks > 80;
SELECT * FROM student WHERE city = "Mumbai" AND marks >= 90;
SELECT * FROM student WHERE marks BETWEEN 80 AND 90;
SELECT * FROM student WHERE city IN ("Delhi", "Mumbai");
SELECT * FROM student WHERE city NOT IN ("Delhi");

-- Sort data and restrict result count

SELECT * FROM student ORDER BY marks DESC;
SELECT * FROM student ORDER BY marks DESC LIMIT 3;

SQL Complete Course Reference | MySQL Page 3 of 7

 8. Aggregation & Grouping (`GROUP BY` & `HAVING`)

-- Aggregate Functions
SELECT MAX(marks), MIN(marks), AVG(marks), SUM(marks), COUNT(rollno)
FROM student;

-- Grouping records by City

SELECT city, COUNT(rollno)
FROM student
GROUP BY city;

-- Grouping with HAVING filter (Filters summary groups after aggregation)

SELECT city, COUNT(rollno)
FROM student
GROUP BY city
HAVING MAX(marks) > 90;

Difference Between WHERE and HAVING Clauses

WHERE filters individual table rows before aggregation and grouping occur.
HAVING filters calculated summary groups after GROUP BY has executed.

9. Data Modification & Schema Alteration

-- Temporarily disable Safe Update Mode if updating without PK in WHERE

SET SQL_SAFE_UPDATES = 0;

-- UPDATE Records
UPDATE student SET grade = "O" WHERE grade = "A";
UPDATE student SET marks = marks + 1;

-- DELETE Specific Rows

DELETE FROM student WHERE marks < 33;

-- ALTER TABLE: Add, Modify, Rename, and Drop Columns

ALTER TABLE student ADD COLUMN age INT NOT NULL DEFAULT 19;
ALTER TABLE student MODIFY COLUMN age VARCHAR(2);
ALTER TABLE student CHANGE COLUMN age student_age INT;
ALTER TABLE student DROP COLUMN student_age;
ALTER TABLE student RENAME TO student_info;

-- TRUNCATE vs DROP
TRUNCATE TABLE temp_table; -- Empties all rows, keeps schema
DROP TABLE temp_table; -- Deletes table structure and data entirely

10. SQL Joins

Joins combine rows from multiple tables based on related logical columns.

-- Sample Tables setup for Join Examples

CREATE TABLE student_join (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE course_join (id INT PRIMARY KEY, course VARCHAR(50));

INSERT INTO student_join VALUES (101, "Adam"), (102, "Bob"), (103, "Casey");
INSERT INTO course_join VALUES (102, "English"), (105, "Math"), (103, "Science");

SQL Complete Course Reference | MySQL Page 4 of 7

1. INNER JOIN
Returns records that have matching keys in both tables.

SELECT * FROM student_join AS a

INNER JOIN course_join AS b
ON [Link] = [Link];

2. LEFT OUTER JOIN

Returns all records from the left table and matching records from the right table.

SELECT * FROM student_join AS a

LEFT JOIN course_join AS b
ON [Link] = [Link];

3. RIGHT OUTER JOIN

Returns all records from the right table and matching records from the left table.

SELECT * FROM student_join AS a

RIGHT JOIN course_join AS b
ON [Link] = [Link];

4. FULL OUTER JOIN (MySQL Syntax via UNION)

Combines Left Join and Right Join using UNION to simulate Full Outer Join.

SELECT * FROM student_join AS a LEFT JOIN course_join AS b ON [Link] = [Link]

UNION
SELECT * FROM student_join AS a RIGHT JOIN course_join AS b ON [Link] = [Link];

5. LEFT EXCLUSIVE JOIN

Returns records that exist exclusively in the left table.

SELECT * FROM student_join AS a

LEFT JOIN course_join AS b ON [Link] = [Link]
WHERE [Link] IS NULL;

6. SELF JOIN
Joins a table with itself to evaluate internal relationships (e.g., employee to manager).

SELECT [Link] AS employee, [Link] AS manager

FROM employee AS a
JOIN employee AS b
ON a.manager_id = [Link];

11. Set Operations (`UNION` & `UNION ALL`)

Combines the output of two distinct SELECT queries.

SQL Complete Course Reference | MySQL Page 5 of 7

 -- UNION: Combines result sets and removes duplicate rows
SELECT name FROM student
UNION
SELECT name FROM teacher;

-- UNION ALL: Combines result sets preserving all duplicate rows

SELECT name FROM student
UNION ALL
SELECT name FROM teacher;

12. Subqueries (Nested Queries)

Subqueries allow executing nested queries where the output of an inner query drives the execution of an outer query.

-- Subquery in WHERE clause: Students with marks higher than class average
SELECT name, marks
FROM student
WHERE marks > (SELECT AVG(marks) FROM student);

-- Subquery using IN clause: Find students with even roll numbers

SELECT rollno, name
FROM student
WHERE rollno IN (
SELECT rollno FROM student WHERE rollno % 2 = 0
);

-- Subquery in FROM clause: Calculate maximum score from a derived table

SELECT MAX(marks)
FROM (SELECT * FROM student WHERE city = "Delhi") AS delhi_students;

13. SQL Views

A View is a saved virtual table representing the result of a pre-compiled SQL query. It does not occupy physical storage
space for data, making it ideal for access control and abstracting complex joins.

-- Create a Virtual View

CREATE VIEW teacher_view AS
SELECT rollno, name, marks FROM student;

-- Querying the View

SELECT * FROM teacher_view WHERE marks > 80;

-- Drop View
DROP VIEW IF EXISTS teacher_view;

SQL Complete Course Reference | MySQL Page 6 of 7

 14. SQL Quick Reference Cheat Sheet

Task SQL Syntax Pattern

Create Database CREATE DATABASE db_name;

Create Table CREATE TABLE tbl (col1 INT PRIMARY KEY, col2 VARCHAR(50));

Insert Data INSERT INTO tbl (col1, col2) VALUES (val1, val2);

Select Filtered SELECT * FROM tbl WHERE col1 > 10 ORDER BY col2 DESC LIMIT 5;

Update Data UPDATE tbl SET col2 = 'val' WHERE col1 = 1;

Delete Rows DELETE FROM tbl WHERE col1 = 1;

Inner Join SELECT * FROM t1 INNER JOIN t2 ON [Link] = [Link];

Group & Filter SELECT col1, COUNT(*) FROM tbl GROUP BY col1 HAVING COUNT(*) > 2;

SQL Complete Course Reference | MySQL Page 7 of 7