by Ram Sir

sql SQL Mastery Course

Comprehensive SQL: Learn from basics to expert level—core concepts, advanced queries, optimization, security, and real-world case studies with hands-on examples and theory, by Ram Sir.

Start Learning SQL
DeviX

About Your Instructor

Ram Sir, expert database architect, brings 2+ years of SQL and DBMS experience, training thousands in industry and academia.

SQL Learning Path

dataset

Core SQL

  • Data types & schemas
  • Tables & constraints
  • CRUD operations
psychology

Advanced SQL

  • Joins & subqueries
  • Views, Indexes
  • Transactions
engineering

Expert/Pro Topics

  • Performance tuning
  • Stored procedures
  • Triggers, CTE, Window
security

Security & Admin

  • Users & roles
  • Privileges
  • Backup/restore
case_study

Case Studies

  • Real-world queries
  • Optimization
  • Analytics
Core
Advanced
Expert
Security
Case Studies

dataset Core SQL

Start with the foundations of SQL: tables, data types, constraints, and basic data manipulation.

code Table Creation & Types

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT,
    Department VARCHAR(50),
    HireDate DATE,
    Salary DECIMAL(9,2)
);

code CRUD Operations

-- Insert
INSERT INTO Employees (EmpID, Name, Age, Department, Salary) VALUES (1, 'John', 30, 'IT', 70000);

-- Select
SELECT Name, Salary FROM Employees WHERE Department = 'IT';

-- Update
UPDATE Employees SET Salary = Salary + 5000 WHERE EmpID = 1;

-- Delete
DELETE FROM Employees WHERE EmpID = 1;

code Constraints

-- Add UNIQUE and CHECK
ALTER TABLE Employees ADD CONSTRAINT unique_email UNIQUE (Email);
ALTER TABLE Employees ADD CONSTRAINT check_age CHECK (Age >= 18);

sticky_note_2 Key Notes

SQL is a declarative language: you specify what you want, not how. Tables are the core, but constraints (PK, FK, UNIQUE, CHECK, DEFAULT, NOT NULL) are critical for data integrity.

psychology Advanced SQL

Go deeper with joins, subqueries, views, indexes, and transactions.

code Joins

-- INNER JOIN
SELECT e.Name, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.Department = d.DeptID;

-- LEFT JOIN
SELECT e.Name, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.Department = d.DeptID;

-- CROSS JOIN
SELECT * FROM Products CROSS JOIN Suppliers;

code Subqueries

-- Subquery in WHERE
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Correlated Subquery
SELECT e.Name FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department);

code Views & Indexes

-- View
CREATE VIEW HighEarners AS
SELECT Name, Salary FROM Employees WHERE Salary > 80000;

-- Index
CREATE INDEX idx_dept_salary ON Employees(Department, Salary);

code Transactions

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 2;
COMMIT;

sticky_note_2 Good to Know

Views = reusable queries; Indexes = performance boost; Transactions = atomicity, consistency, isolation, durability (ACID).

engineering Expert/Pro SQL Topics

Unlock the power of SQL with advanced programming, analytics, optimization, and database design.

code Stored Procedures & Functions

-- Procedure (MySQL style)
DELIMITER //
CREATE PROCEDURE GiveRaise(IN emp INT, IN amount DECIMAL(9,2))
BEGIN
    UPDATE Employees SET Salary = Salary + amount WHERE EmpID = emp;
END //
DELIMITER ;

CALL GiveRaise(2, 10000);

code Triggers

CREATE TRIGGER audit_salary
AFTER UPDATE ON Employees
FOR EACH ROW
INSERT INTO SalaryAudit(EmpID, OldSalary, NewSalary, ChangeDate)
VALUES (OLD.EmpID, OLD.Salary, NEW.Salary, NOW());

code CTEs & Window Functions

-- CTE (WITH)
WITH DeptAvg AS (
  SELECT Department, AVG(Salary) AS DeptAvgSalary
  FROM Employees
  GROUP BY Department
)
SELECT e.Name, e.Department, e.Salary, d.DeptAvgSalary
FROM Employees e
JOIN DeptAvg d ON e.Department = d.Department;

-- Window Function (RANK)
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

code Query Optimization

EXPLAIN SELECT * FROM Employees WHERE Department = 'Finance';

-- Optimize with index:
CREATE INDEX idx_dept ON Employees(Department);

sticky_note_2 Expert Notes

Use CTEs for complex queries; window functions for analytics; triggers for automation; study execution plans for speed.

security Security & Administration

Keep your database safe with user management, privileges, backup/restore, and auditing.

code User & Role Management

CREATE USER analyst IDENTIFIED BY 'pass123';
GRANT SELECT, INSERT ON Employees TO analyst;
REVOKE INSERT ON Employees FROM analyst;
DROP USER analyst;

code Backups & Restore

-- MySQL dump
mysqldump -u root -p mydb > backup.sql

-- Restore
mysql -u root -p mydb < backup.sql

code Auditing Access

-- Enable audit log (MySQL example)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SHOW VARIABLES LIKE 'audit%';

sticky_note_2 Security Best Practices

Always use least privilege, strong passwords, regular backups, and audit logs for compliance.

case_study Real-World Case Studies

Practice advanced SQL with real-world scenarios, reporting, and analytics.

code Reporting: Top Earners by Department

SELECT Department, Name, Salary
FROM (
  SELECT e.*, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rnk
  FROM Employees e
) ranked
WHERE rnk = 1;

code Analytics: Year-over-Year Growth

SELECT Year, SUM(Sales) AS TotalSales,
  LAG(SUM(Sales)) OVER (ORDER BY Year) AS PrevYearSales,
  ((SUM(Sales) - LAG(SUM(Sales)) OVER (ORDER BY Year))/LAG(SUM(Sales)) OVER (ORDER BY Year))*100 AS YoYGrowth
FROM Sales
GROUP BY Year;

code Data Cleaning: Remove Duplicates

DELETE FROM Employees
WHERE EmpID NOT IN (
  SELECT MIN(EmpID) FROM Employees GROUP BY Email
);

code Cross-Database Query

SELECT a.Name, b.Sales
FROM HR.Employees a
JOIN Sales.Records b ON a.EmpID = b.EmpID;

sticky_note_2 Learn by Doing

Real SQL skills come from practice—try to solve these and tweak them for your data!

SQL Playground

Try SQL Queries

Results will appear here...

SQL Command Reference

Most-used SQL statements and features, with query examples.

add_box

DDL: Structure

  • CREATE TABLE, ALTER TABLE, DROP
  • CONSTRAINTS: PK, FK, UNIQUE, CHECK
CREATE TABLE Products (...);
ALTER TABLE Products ADD COLUMN Price DECIMAL(5,2);
edit

DML: Data Manipulation

  • INSERT, UPDATE, DELETE
  • MERGE
INSERT INTO t (a,b) VALUES (1,2);
UPDATE t SET a=2 WHERE b=2;
search

DQL: Query

  • SELECT, WHERE, GROUP BY
  • HAVING, ORDER BY, JOIN
SELECT col FROM t WHERE a=1 ORDER BY b;
function

Functions & Expression

  • COUNT, SUM, AVG
  • CASE, COALESCE, CAST
SELECT COUNT(*), SUM(amount), AVG(salary) FROM t;
group_work

Joins & Subqueries

  • INNER/LEFT/RIGHT/FULL JOIN
  • Subqueries, IN, EXISTS
SELECT * FROM a INNER JOIN b ON a.id = b.id;
psychology

Window & CTE

  • RANK(), DENSE_RANK(), ROW_NUMBER()
  • WITH (CTE)
SELECT name, RANK() OVER (ORDER BY salary) FROM t;
security

Privileges & Security

  • GRANT, REVOKE
  • CREATE USER, DROP USER
GRANT SELECT ON db.table TO user;
backup

Admin & Backup

  • mysqldump, pg_dump
  • SHOW TABLES, DESCRIBE
mysqldump -u root -p db > backup.sql;