Class 12 SQL Notes / Query Sheet (Complete)
1️⃣ Database Basics
• Database: Collection of related data stored systematically.
• Types of Databases:
• Relational (RDBMS): MySQL, Oracle, SQL Server
• Non-Relational (NoSQL): MongoDB, Firebase
• SQL: Structured Query Language, used to manage RDBMS.
2️⃣ Database Commands
Create Database
CREATE DATABASE database_name;
Use Database
USE database_name;
Alter Database
ALTER DATABASE old_name RENAME TO new_name;
Drop Database
DROP DATABASE database_name;
3️⃣ Table Commands
Create Table
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
1
...
);
Datatypes: INT, FLOAT, CHAR(size), VARCHAR(size), DATE, TIME, DATETIME Constraints: PRIMARY KEY,
FOREIGN KEY, UNIQUE, NOT NULL, CHECK(condition), DEFAULT value
Example:
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK(Age>=18),
Class VARCHAR(10),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
Alter Table
• Add column:
ALTER TABLE table_name ADD column_name datatype;
• Drop column:
ALTER TABLE table_name DROP COLUMN column_name;
• Modify column:
ALTER TABLE table_name MODIFY column_name new_datatype;
• Add constraint:
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(column_name)
REFERENCES other_table(column);
Drop Table
DROP TABLE table_name;
2
4️⃣ Data Manipulation (DML)
Insert Data
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
INSERT INTO table_name VALUES (value1, value2, ...);
Update Data
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Delete Data
DELETE FROM table_name WHERE condition;
5️⃣ Data Retrieval (SELECT)
Basic SELECT
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;
Filtering Data
SELECT * FROM table_name WHERE condition;
SELECT * FROM Student WHERE Age BETWEEN 18 AND 22;
SELECT * FROM Student WHERE Name LIKE 'A%';
Sorting Data
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;
Aggregate Functions
• COUNT(column) , SUM(column) , AVG(column) , MIN(column) , MAX(column)
3
SELECT COUNT(*) FROM Student;
SELECT AVG(Age) FROM Student WHERE DeptID=1;
GROUP BY & HAVING
SELECT DeptID, COUNT(*) FROM Student GROUP BY DeptID;
SELECT DeptID, COUNT(*) FROM Student GROUP BY DeptID HAVING COUNT(*) > 5;
6️⃣ JOINs
Join Type Description Syntax Example
SELECT * FROM A INNER JOIN B ON
INNER JOIN Only matching rows
[Link]=[Link];
All rows from left + matching SELECT * FROM A LEFT JOIN B ON
LEFT JOIN
right [Link]=[Link];
All rows from right + SELECT * FROM A RIGHT JOIN B ON
RIGHT JOIN
matching left [Link]=[Link];
FULL OUTER SELECT * FROM A FULL OUTER JOIN B ON
All rows from both tables
JOIN [Link]=[Link];
CROSS JOIN Cartesian product SELECT * FROM A CROSS JOIN B;
7️⃣ Keys
• Primary Key: Unique, not NULL
• Foreign Key: References another table
• Unique Key: Unique, can be NULL
• Null Key: Column can accept NULL (default), NOT NULL disallows it
• Composite Key: Primary key on multiple columns
Examples:
-- Primary Key
CREATE TABLE Student (RollNo INT PRIMARY KEY, Name VARCHAR(50));
-- Foreign Key
CREATE TABLE Student (DeptID INT, FOREIGN KEY (DeptID) REFERENCES
Department(DeptID));
4
-- Unique Key
CREATE TABLE Employee (Email VARCHAR(50) UNIQUE);
ALTER TABLE Employee ADD CONSTRAINT uq_Email UNIQUE (Email);
-- Not Null / Null
CREATE TABLE Student (Name VARCHAR(50) NOT NULL);
-- Composite Key
CREATE TABLE Enrollment (StudentID INT, CourseID INT, PRIMARY KEY (StudentID,
CourseID));
8️⃣ Other Useful Clauses
• DISTINCT: Remove duplicates
SELECT DISTINCT DeptID FROM Student;
• LIMIT / TOP: Restrict rows
SELECT * FROM Student LIMIT 5; -- MySQL
SELECT TOP 5 * FROM Student; -- SQL Server
9️⃣ Complete Example
-- Create Dept table
CREATE TABLE Department (DeptID INT PRIMARY KEY, DeptName VARCHAR(50) NOT NULL);
-- Create Student table with foreign key
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT,
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
-- Insert records
INSERT INTO Department VALUES (1, 'CS'), (2, 'Math');
INSERT INTO Student VALUES (101, 'Alice', 20, 1), (102, 'Bob', 21, 2);
5
-- Select all students
SELECT * FROM Student;
-- Join Student with Department
SELECT [Link], [Link] FROM Student S INNER JOIN Department D ON [Link] =
[Link];
-- Aggregate: average age by department
SELECT DeptID, AVG(Age) AS AvgAge FROM Student GROUP BY DeptID;
✅ Covers: Creating/altering/dropping databases and tables, keys, constraints, DML, SELECT queries,
WHERE, LIKE, BETWEEN, ORDER BY, GROUP BY, HAVING, JOINs, DISTINCT, LIMIT/Top, aggregate functions,
and full examples.