1.
Introduction to SQL
1.1 What is SQL?
SQL (Structured Query Language) is a standard language for storing, ma-
nipulating, and retrieving data in relational databases. It is used to communi-
cate with a database.
1.2 Components of SQL
1. DDL (Data Definition Language) - Defines database structure
2. DML (Data Manipulation Language) - Manipulates data
3. DCL (Data Control Language) - Controls access to data
4. TCL (Transaction Control Language) - Manages transactions
2. Data Types in SQL
2.1 Character/String Types
• CHAR(n): Fixed-length character string (n characters)
– Example: CHAR(10) always uses 10 characters
– Padding with spaces if data is shorter
• VARCHAR(n): Variable-length character string (up to n characters)
– Example: VARCHAR(50) can store 0-50 characters
– More space efficient than CHAR
2.2 Numeric Types
• INT or INTEGER: Whole numbers
– Example: 25, -10, 0
– Range: -2147483648 to 2147483647
• FLOAT: Floating point numbers (decimal numbers)
– Example: 15.75, -3.14, 0.5
– Can specify precision: FLOAT(7,2) means 7 digits total, 2 after dec-
imal
2.3 Date Type
• DATE: Stores date values
– Format: YYYY-MM-DD
– Example: ‘2024-03-15’ for 15th March 2024
1
3. Database Level Commands
3.1 CREATE DATABASE
Creates a new database.
CREATE DATABASE school;
CREATE DATABASE company;
3.2 USE DATABASE
Selects a database to work with.
USE school;
USE company;
3.3 SHOW DATABASES
Displays all databases on the server.
SHOW DATABASES;
3.4 DROP DATABASE
Deletes a database and all its contents.
DROP DATABASE school;
-- Warning: This permanently deletes all data
4. Table Level Commands - DDL
4.1 CREATE TABLE
Creates a new table with specified columns and constraints.
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Class INT,
Marks FLOAT,
DOB DATE
);
4.2 SHOW TABLES
Displays all tables in the current database.
SHOW TABLES;
2
4.3 DESCRIBE TABLE
Shows the structure of a table.
DESCRIBE Student;
-- OR
DESC Student;
4.4 ALTER TABLE - ADD COLUMN
Adds a new column to an existing table.
ALTER TABLE Student
ADD Email VARCHAR(100);
4.5 ALTER TABLE - DROP COLUMN
Removes a column from a table.
ALTER TABLE Student
DROP COLUMN Email;
4.6 ALTER TABLE - ADD PRIMARY KEY
Adds a primary key constraint to an existing table.
-- If table created without primary key
ALTER TABLE Student
ADD PRIMARY KEY (RollNo);
4.7 ALTER TABLE - DROP PRIMARY KEY
Removes primary key constraint.
ALTER TABLE Student
DROP PRIMARY KEY;
4.8 DROP TABLE
Deletes a table and all its data.
DROP TABLE Student;
-- Warning: Permanent deletion
5. Constraints
5.1 NOT NULL
Ensures a column cannot have NULL values.
3
CREATE TABLE Employee (
EmpID INT,
Name VARCHAR(50) NOT NULL, -- Name must have a value
Department VARCHAR(30)
);
5.2 UNIQUE
Ensures all values in a column are different.
CREATE TABLE Student (
RollNo INT,
Email VARCHAR(100) UNIQUE, -- Each email must be unique
Phone VARCHAR(10) UNIQUE
);
5.3 PRIMARY KEY
Combination of NOT NULL and UNIQUE. Uniquely identifies each row.
CREATE TABLE Product (
ProductID INT PRIMARY KEY, -- Unique and not null
Name VARCHAR(50),
Price FLOAT
);
6. DML Commands - Data Manipulation
6.1 INSERT
Adds new rows to a table.
-- Insert specific columns
INSERT INTO Student (RollNo, Name, Class, Marks)
VALUES (101, 'Rahul', 12, 85.5);
-- Insert all columns (must match order)
INSERT INTO Student
VALUES (102, 'Priya', 12, 92.0, '2006-05-15');
-- Insert multiple rows
INSERT INTO Student VALUES
(103, 'Amit', 11, 78.0, '2007-02-20'),
(104, 'Sonia', 12, 88.5, '2006-08-10');
4
6.2 SELECT
Retrieves data from a table.
-- Select all columns
SELECT * FROM Student;
-- Select specific columns
SELECT RollNo, Name, Marks FROM Student;
-- Select with column aliases
SELECT RollNo AS "Roll Number",
Name AS "Student Name",
Marks FROM Student;
6.3 UPDATE
Modifies existing data.
-- Update all rows
UPDATE Student SET Class = 12;
-- Update specific rows
UPDATE Student SET Marks = Marks + 5
WHERE Class = 12;
-- Update multiple columns
UPDATE Student
SET Marks = 95, Class = 12
WHERE RollNo = 101;
6.4 DELETE
Removes rows from a table.
-- Delete specific rows
DELETE FROM Student WHERE RollNo = 103;
-- Delete all rows (table structure remains)
DELETE FROM Student;
-- Delete with condition
DELETE FROM Student WHERE Marks < 40;
5
7. SELECT with Clauses and Operators
7.1 DISTINCT Clause
Returns only distinct (different) values.
SELECT DISTINCT Class FROM Student;
SELECT DISTINCT City FROM Employee;
7.2 WHERE Clause
Filters records based on conditions.
SELECT * FROM Student WHERE Class = 12;
SELECT Name, Marks FROM Student WHERE Marks > 80;
7.3 Operators
Mathematical Operators
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus
SELECT Marks + 5 FROM Student;
SELECT Price * Quantity FROM OrderDetails;
Relational Operators
= Equal to
!= or <> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
SELECT * FROM Student WHERE Marks >= 75;
SELECT * FROM Employee WHERE Salary > 50000;
Logical Operators
AND - All conditions must be true
OR - At least one condition must be true
NOT - Negates a condition
SELECT * FROM Student
WHERE Class = 12 AND Marks > 80;
6
SELECT * FROM Employee
WHERE Department = 'Sales' OR Department = 'Marketing';
SELECT * FROM Student
WHERE NOT Class = 11;
7.4 BETWEEN
Selects values within a range (inclusive).
SELECT * FROM Student
WHERE Marks BETWEEN 70 AND 90;
SELECT * FROM Employee
WHERE Salary BETWEEN 30000 AND 50000;
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31';
7.5 IN
Matches any value in a list.
SELECT * FROM Student
WHERE Class IN (11, 12);
SELECT * FROM Employee
WHERE Department IN ('HR', 'IT', 'Finance');
SELECT * FROM Product
WHERE Category IN ('Electronics', 'Books');
7.6 LIKE
Searches for a specified pattern.
-- % represents zero or more characters
-- _ represents a single character
SELECT * FROM Student
WHERE Name LIKE 'A%'; -- Names starting with A
SELECT * FROM Student
WHERE Name LIKE '%a%'; -- Names containing 'a'
SELECT * FROM Student
WHERE Name LIKE '_a%'; -- Second letter is 'a'
7
SELECT * FROM Product
WHERE Name LIKE 'Laptop%';
7.7 ORDER BY
Sorts the result set.
-- Ascending order (default)
SELECT * FROM Student ORDER BY Marks;
-- Descending order
SELECT * FROM Student ORDER BY Marks DESC;
-- Multiple columns
SELECT * FROM Student
ORDER BY Class ASC, Marks DESC;
-- Using column position
SELECT Name, Class, Marks FROM Student
ORDER BY 2, 3 DESC;
8. NULL Values
8.1 Meaning of NULL
• NULL represents missing or unknown data
• NULL is not the same as zero or empty string
• NULL cannot be compared using = or != operators
8.2 IS NULL
Checks for NULL values.
SELECT * FROM Student
WHERE Marks IS NULL;
SELECT * FROM Employee
WHERE Department IS NULL;
8.3 IS NOT NULL
Checks for non-NULL values.
SELECT * FROM Student
WHERE Marks IS NOT NULL;
8
SELECT * FROM Employee
WHERE Email IS NOT NULL;
9. Aggregate Functions
9.1 COUNT
Returns the number of rows.
SELECT COUNT(*) FROM Student; -- All rows
SELECT COUNT(Marks) FROM Student; -- Non-null values only
SELECT COUNT(DISTINCT Class) FROM Student;
9.2 SUM
Returns the sum of values.
SELECT SUM(Marks) FROM Student;
SELECT SUM(Salary) FROM Employee
WHERE Department = 'Sales';
9.3 AVG
Returns the average value.
SELECT AVG(Marks) FROM Student;
SELECT AVG(Salary) FROM Employee;
9.4 MAX
Returns the maximum value.
SELECT MAX(Marks) FROM Student;
SELECT MAX(Price) FROM Product;
9.5 MIN
Returns the minimum value.
SELECT MIN(Marks) FROM Student;
SELECT MIN(Salary) FROM Employee;
10. GROUP BY Clause
Groups rows that have the same values into summary rows.
9
-- Group by single column
SELECT Class, AVG(Marks)
FROM Student
GROUP BY Class;
-- Group by multiple columns
SELECT Class, Gender, COUNT(*)
FROM Student
GROUP BY Class, Gender;
-- With aggregate functions
SELECT Department,
COUNT(*) AS TotalEmployees,
AVG(Salary) AS AverageSalary
FROM Employee
GROUP BY Department;
11. HAVING Clause
Filters groups (used with GROUP BY).
SELECT Class, AVG(Marks)
FROM Student
GROUP BY Class
HAVING AVG(Marks) > 75;
SELECT Department, COUNT(*)
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 5;
-- Difference between WHERE and HAVING:
-- WHERE filters rows before grouping
-- HAVING filters groups after grouping
SELECT Class, AVG(Marks)
FROM Student
WHERE Marks IS NOT NULL -- Filters individual rows
GROUP BY Class
HAVING AVG(Marks) > 70; -- Filters groups
10
12. Joins
12.1 Cartesian Product
Returns all possible combinations of rows from both tables.
-- Explicit Cartesian Product
SELECT *
FROM Table1, Table2;
-- Example
SELECT *
FROM Student, Marks;
-- If Student has 4 rows and Marks has 3 rows
-- Result will have 4 × 3 = 12 rows
12.2 Equi-Join
Join based on equality condition.
-- Using WHERE clause
SELECT [Link], [Link], [Link], [Link]
FROM Student s, Marks m
WHERE [Link] = [Link];
-- Using JOIN keyword
SELECT [Link], [Link], [Link], [Link]
FROM Student s JOIN Marks m
ON [Link] = [Link];
12.3 Natural Join
Automatically joins tables based on columns with same name and data type.
SELECT *
FROM Student NATURAL JOIN Marks;
-- Automatically joins on RollNo (if same in both tables)
-- Equivalent to:
SELECT *
FROM Student JOIN Marks
USING (RollNo);
11
13. Example Database Schema
13.1 Sample Tables Creation
-- Create database
CREATE DATABASE SchoolDB;
USE SchoolDB;
-- Create Student table
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Class INT,
Gender CHAR(1),
DOB DATE
);
-- Create Marks table
CREATE TABLE Marks (
ExamID INT PRIMARY KEY,
RollNo INT,
Subject VARCHAR(30),
Marks FLOAT,
FOREIGN KEY (RollNo) REFERENCES Student(RollNo)
);
-- Create Teacher table
CREATE TABLE Teacher (
TeacherID INT PRIMARY KEY,
Name VARCHAR(50),
Subject VARCHAR(30),
Salary FLOAT
);
13.2 Comprehensive Example Queries
-- 1. Insert data
INSERT INTO Student VALUES
(101, 'Rahul Sharma', 12, 'M', '2006-05-10'),
(102, 'Priya Patel', 12, 'F', '2006-08-15'),
(103, 'Amit Kumar', 11, 'M', '2007-02-20'),
(104, 'Sonia Gupta', 12, 'F', '2006-11-30');
-- 2. Basic queries
SELECT * FROM Student WHERE Class = 12;
SELECT Name, DOB FROM Student ORDER BY Name;
SELECT DISTINCT Class FROM Student;
12
-- 3. Aggregate functions
SELECT Class, COUNT(*) AS TotalStudents
FROM Student
GROUP BY Class;
SELECT AVG(Salary) FROM Teacher;
-- 4. Complex queries with HAVING
SELECT Class, Gender, COUNT(*)
FROM Student
GROUP BY Class, Gender
HAVING COUNT(*) > 1;
-- 5. Joins
SELECT [Link], [Link], [Link]
FROM Student s JOIN Marks m
ON [Link] = [Link]
WHERE [Link] > 80;
14. Important Notes
14.1 Common Mistakes to Avoid
1. Forgetting to use USE database command
2. Confusing CHAR and VARCHAR
3. Using = instead of IS NULL for NULL checks
4. Forgetting GROUP BY when using aggregate functions with non-
aggregated columns
5. Confusing WHERE and HAVING clauses
6. Not using table aliases in joins when column names are ambiguous
14.2 SQL Command Categories Summary
Category Commands Purpose
DDL CREATE, ALTER, Define/modify
DROP, TRUNCATE database structure
DML INSERT, UPDATE, Manipulate data
DELETE, SELECT
Query SELECT with clauses Retrieve data
Constraints NOT NULL, UNIQUE, Enforce data integrity
PRIMARY KEY,
FOREIGN KEY
13
14.3 Operator Precedence
1. Parentheses ()
2. Arithmetic operators (*, /, %, +, -)
3. Comparison operators (=, >, <, >=, <=, !=)
4. NOT
5. AND
6. OR
15. Practice Questions
Question 1: Create and Populate
Create a database “Company” with table “Employee” having columns: EmpID
(PK), Name, Department, Salary, JoinDate. Insert 5 records.
Question 2: Basic Queries
For the above table: 1. List all employees in ‘IT’ department 2. Find employees
with salary > 50000 3. Display names and salaries sorted by salary descending
Question 3: Aggregate Functions
1. Find average salary of each department
2. Count employees in each department
3. Find maximum and minimum salary
Question 4: Advanced Queries
1. Find departments with average salary > 45000
2. List employees joined in 2023
3. Find employees whose name starts with ‘A’
Question 5: Joins
Create another table “Department” with DeptID and DeptName. Establish
relationship with Employee table and write queries using joins.
16. CBSE Pattern Questions
Short Answer (2-3 marks)
1. Differentiate between CHAR and VARCHAR
2. Explain DISTINCT clause with example
3. What are aggregate functions? List any three
4. Differentiate between WHERE and HAVING
14
Long Answer (4-5 marks)
1. Explain various types of joins with examples
2. Create a student database schema with appropriate constraints
3. Write SQL commands for given scenarios
4. Explain GROUP BY and HAVING with examples
Application Based
1. Design database for library system
2. Write queries for given business requirements
3. Identify errors in given SQL code
4. Write optimized queries for specific requirements
17. Quick Reference Sheet
-- Database Operations
CREATE DATABASE dbname;
USE dbname;
SHOW DATABASES;
DROP DATABASE dbname;
-- Table Operations
CREATE TABLE tablename (...);
SHOW TABLES;
DESC tablename;
ALTER TABLE tablename ADD column;
ALTER TABLE tablename DROP column;
DROP TABLE tablename;
-- Data Operations
INSERT INTO tablename VALUES (...);
SELECT * FROM tablename;
UPDATE tablename SET column=value WHERE condition;
DELETE FROM tablename WHERE condition;
-- Clauses
WHERE, GROUP BY, HAVING, ORDER BY
DISTINCT, LIKE, BETWEEN, IN, IS NULL
-- Aggregate Functions
COUNT(), SUM(), AVG(), MAX(), MIN()
-- Joins
INNER JOIN, NATURAL JOIN, CARTESIAN PRODUCT
15
16