COMPLETE SQL RDBMS PRACTICAL NOTES
(BASIC TO ADVANCED)
80+ Pages | Practical Focused | Step-by-Step SQL Learning
This book is designed for beginners to advanced learners. No heavy theory. Only real SQL
commands with explanation.
Useful for exams, interviews, and real-world practice.
SQL & RDBMS Basics
SQL is used to interact with relational databases.
RDBMS stores data in tables with rows and columns.
CREATE DATABASE company_db;
USE company_db;
Table Creation
Tables store actual data.
Each column has a datatype.
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50),
department VARCHAR(40),
salary INT
);
Insert Data
INSERT command adds records into tables.
INSERT INTO employees VALUES (1,'Aman','IT',50000);
INSERT INTO employees VALUES
(2,'Riya','HR',40000),
(3,'Jay','Finance',45000),
(4,'Neha','IT',60000);
Select Queries
SELECT is used to retrieve data.
SELECT * FROM employees;
SELECT emp_name, salary FROM employees;
SELECT * FROM employees WHERE department='IT';
Operators
Operators filter data logically.
SELECT * FROM employees WHERE salary > 45000;
SELECT * FROM employees WHERE department='IT' AND salary > 55000;
Update & Delete
UPDATE modifies data.
DELETE removes data.
UPDATE employees SET salary=65000 WHERE emp_id=4;
DELETE FROM employees WHERE emp_id=2;
Alter Table
ALTER modifies table structure.
ALTER TABLE employees ADD email VARCHAR(50);
ALTER TABLE employees DROP email;
Constraints
Constraints ensure data accuracy.
CREATE TABLE students (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
marks INT CHECK (marks>=0)
);
Aggregate Functions
Used for calculations.
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
Group By & Having
GROUP BY groups rows.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Joins
Joins combine tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON [Link] = d.dept_name;
Subqueries
Query inside another query.
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Views
Views are virtual tables.
CREATE VIEW high_salary AS
SELECT emp_name, salary FROM employees WHERE salary>50000;
SELECT * FROM high_salary;
Transactions
Ensure data safety.
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
ROLLBACK;
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
COMMIT;
SQL & RDBMS Basics
SQL is used to interact with relational databases.
RDBMS stores data in tables with rows and columns.
CREATE DATABASE company_db;
USE company_db;
Table Creation
Tables store actual data.
Each column has a datatype.
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50),
department VARCHAR(40),
salary INT
);
Insert Data
INSERT command adds records into tables.
INSERT INTO employees VALUES (1,'Aman','IT',50000);
INSERT INTO employees VALUES
(2,'Riya','HR',40000),
(3,'Jay','Finance',45000),
(4,'Neha','IT',60000);
Select Queries
SELECT is used to retrieve data.
SELECT * FROM employees;
SELECT emp_name, salary FROM employees;
SELECT * FROM employees WHERE department='IT';
Operators
Operators filter data logically.
SELECT * FROM employees WHERE salary > 45000;
SELECT * FROM employees WHERE department='IT' AND salary > 55000;
Update & Delete
UPDATE modifies data.
DELETE removes data.
UPDATE employees SET salary=65000 WHERE emp_id=4;
DELETE FROM employees WHERE emp_id=2;
Alter Table
ALTER modifies table structure.
ALTER TABLE employees ADD email VARCHAR(50);
ALTER TABLE employees DROP email;
Constraints
Constraints ensure data accuracy.
CREATE TABLE students (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
marks INT CHECK (marks>=0)
);
Aggregate Functions
Used for calculations.
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
Group By & Having
GROUP BY groups rows.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Joins
Joins combine tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON [Link] = d.dept_name;
Subqueries
Query inside another query.
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Views
Views are virtual tables.
CREATE VIEW high_salary AS
SELECT emp_name, salary FROM employees WHERE salary>50000;
SELECT * FROM high_salary;
Transactions
Ensure data safety.
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
ROLLBACK;
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
COMMIT;
SQL & RDBMS Basics
SQL is used to interact with relational databases.
RDBMS stores data in tables with rows and columns.
CREATE DATABASE company_db;
USE company_db;
Table Creation
Tables store actual data.
Each column has a datatype.
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50),
department VARCHAR(40),
salary INT
);
Insert Data
INSERT command adds records into tables.
INSERT INTO employees VALUES (1,'Aman','IT',50000);
INSERT INTO employees VALUES
(2,'Riya','HR',40000),
(3,'Jay','Finance',45000),
(4,'Neha','IT',60000);
Select Queries
SELECT is used to retrieve data.
SELECT * FROM employees;
SELECT emp_name, salary FROM employees;
SELECT * FROM employees WHERE department='IT';
Operators
Operators filter data logically.
SELECT * FROM employees WHERE salary > 45000;
SELECT * FROM employees WHERE department='IT' AND salary > 55000;
Update & Delete
UPDATE modifies data.
DELETE removes data.
UPDATE employees SET salary=65000 WHERE emp_id=4;
DELETE FROM employees WHERE emp_id=2;
Alter Table
ALTER modifies table structure.
ALTER TABLE employees ADD email VARCHAR(50);
ALTER TABLE employees DROP email;
Constraints
Constraints ensure data accuracy.
CREATE TABLE students (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
marks INT CHECK (marks>=0)
);
Aggregate Functions
Used for calculations.
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
Group By & Having
GROUP BY groups rows.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Joins
Joins combine tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON [Link] = d.dept_name;
Subqueries
Query inside another query.
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Views
Views are virtual tables.
CREATE VIEW high_salary AS
SELECT emp_name, salary FROM employees WHERE salary>50000;
SELECT * FROM high_salary;
Transactions
Ensure data safety.
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
ROLLBACK;
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
COMMIT;
SQL & RDBMS Basics
SQL is used to interact with relational databases.
RDBMS stores data in tables with rows and columns.
CREATE DATABASE company_db;
USE company_db;
Table Creation
Tables store actual data.
Each column has a datatype.
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50),
department VARCHAR(40),
salary INT
);
Insert Data
INSERT command adds records into tables.
INSERT INTO employees VALUES (1,'Aman','IT',50000);
INSERT INTO employees VALUES
(2,'Riya','HR',40000),
(3,'Jay','Finance',45000),
(4,'Neha','IT',60000);
Select Queries
SELECT is used to retrieve data.
SELECT * FROM employees;
SELECT emp_name, salary FROM employees;
SELECT * FROM employees WHERE department='IT';
Operators
Operators filter data logically.
SELECT * FROM employees WHERE salary > 45000;
SELECT * FROM employees WHERE department='IT' AND salary > 55000;
Update & Delete
UPDATE modifies data.
DELETE removes data.
UPDATE employees SET salary=65000 WHERE emp_id=4;
DELETE FROM employees WHERE emp_id=2;
Alter Table
ALTER modifies table structure.
ALTER TABLE employees ADD email VARCHAR(50);
ALTER TABLE employees DROP email;
Constraints
Constraints ensure data accuracy.
CREATE TABLE students (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
marks INT CHECK (marks>=0)
);
Aggregate Functions
Used for calculations.
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
Group By & Having
GROUP BY groups rows.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Joins
Joins combine tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON [Link] = d.dept_name;
Subqueries
Query inside another query.
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Views
Views are virtual tables.
CREATE VIEW high_salary AS
SELECT emp_name, salary FROM employees WHERE salary>50000;
SELECT * FROM high_salary;
Transactions
Ensure data safety.
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
ROLLBACK;
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
COMMIT;
SQL & RDBMS Basics
SQL is used to interact with relational databases.
RDBMS stores data in tables with rows and columns.
CREATE DATABASE company_db;
USE company_db;
Table Creation
Tables store actual data.
Each column has a datatype.
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50),
department VARCHAR(40),
salary INT
);
Insert Data
INSERT command adds records into tables.
INSERT INTO employees VALUES (1,'Aman','IT',50000);
INSERT INTO employees VALUES
(2,'Riya','HR',40000),
(3,'Jay','Finance',45000),
(4,'Neha','IT',60000);
Select Queries
SELECT is used to retrieve data.
SELECT * FROM employees;
SELECT emp_name, salary FROM employees;
SELECT * FROM employees WHERE department='IT';
Operators
Operators filter data logically.
SELECT * FROM employees WHERE salary > 45000;
SELECT * FROM employees WHERE department='IT' AND salary > 55000;
Update & Delete
UPDATE modifies data.
DELETE removes data.
UPDATE employees SET salary=65000 WHERE emp_id=4;
DELETE FROM employees WHERE emp_id=2;
Alter Table
ALTER modifies table structure.
ALTER TABLE employees ADD email VARCHAR(50);
ALTER TABLE employees DROP email;
Constraints
Constraints ensure data accuracy.
CREATE TABLE students (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
marks INT CHECK (marks>=0)
);
Aggregate Functions
Used for calculations.
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
Group By & Having
GROUP BY groups rows.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Joins
Joins combine tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON [Link] = d.dept_name;
Subqueries
Query inside another query.
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Views
Views are virtual tables.
CREATE VIEW high_salary AS
SELECT emp_name, salary FROM employees WHERE salary>50000;
SELECT * FROM high_salary;
Transactions
Ensure data safety.
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
ROLLBACK;
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
COMMIT;
SQL & RDBMS Basics
SQL is used to interact with relational databases.
RDBMS stores data in tables with rows and columns.
CREATE DATABASE company_db;
USE company_db;
Table Creation
Tables store actual data.
Each column has a datatype.
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50),
department VARCHAR(40),
salary INT
);
Insert Data
INSERT command adds records into tables.
INSERT INTO employees VALUES (1,'Aman','IT',50000);
INSERT INTO employees VALUES
(2,'Riya','HR',40000),
(3,'Jay','Finance',45000),
(4,'Neha','IT',60000);
Select Queries
SELECT is used to retrieve data.
SELECT * FROM employees;
SELECT emp_name, salary FROM employees;
SELECT * FROM employees WHERE department='IT';
Operators
Operators filter data logically.
SELECT * FROM employees WHERE salary > 45000;
SELECT * FROM employees WHERE department='IT' AND salary > 55000;
Update & Delete
UPDATE modifies data.
DELETE removes data.
UPDATE employees SET salary=65000 WHERE emp_id=4;
DELETE FROM employees WHERE emp_id=2;
Alter Table
ALTER modifies table structure.
ALTER TABLE employees ADD email VARCHAR(50);
ALTER TABLE employees DROP email;
Constraints
Constraints ensure data accuracy.
CREATE TABLE students (
roll INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
marks INT CHECK (marks>=0)
);
Aggregate Functions
Used for calculations.
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
Group By & Having
GROUP BY groups rows.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Joins
Joins combine tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON [Link] = d.dept_name;
Subqueries
Query inside another query.
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Views
Views are virtual tables.
CREATE VIEW high_salary AS
SELECT emp_name, salary FROM employees WHERE salary>50000;
SELECT * FROM high_salary;
Transactions
Ensure data safety.
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
ROLLBACK;
START TRANSACTION;
UPDATE employees SET salary=70000 WHERE emp_id=1;
COMMIT;
FINAL PRACTICE FLOW
Create → Insert → Select → Filter → Aggregate → Join → Subquery → View → Transaction