Class 12 • Chapter 9

SQL Mastery: The Data Language

SQL is the declarative language for managing relational data, divided into DDL for structure, DML for records, and DQL for powerful querying.

10 note blocks6 exam topics

🎯 Exam Focus Areas

Distinction between DDL (CREATE/ALTER) and DML (INSERT/UPDATE)Importance of the WHERE clause in UPDATE/DELETEUsing GROUP BY vs HAVING and ORDER BYDifference between CHAR (fixed) and VARCHAR (variable) data typesAggregate functions: SUM, AVG, COUNT, MAX, MINLIKE operator with '%' and '_' wildcards

Introduction to SQL

SQL (Structured Query Language) is the standard professional language used to communicate with relational databases. Unlike procedural languages (like Python or Java) that tell the computer *how* to do something, SQL is a 'Declarative' language where you specify *what* result you want, and the database engine handles the underlying logic. SQL is divided into several sub-languages: **DDL** (Data Definition Language) for defining tables and constraints, **DML** (Data Manipulation Language) for adding or changing data, and **DQL** (Data Query Language) for retrieving data. Mastering SQL allows you to interact with massive industrial-scale databases used by Google, Amazon, and financial institutions worldwide.

SQL Data Types

1INT: Whole numbers (e.g., Roll Number)
2DECIMAL(p,s): Precise decimals (e.g., Price, Salary)
3CHAR(n): Fixed-length string (e.g., Code, Grade)
4VARCHAR(n): Variable-length string (e.g., Name, Address)
5DATE: Date values in YYYY-MM-DD format
6DATE_TIME: Date and time combined

Data Definition (DDL)

Data Definition Language (DDL) is used to define, modify, or destroy the structure (schema) of the database. The **CREATE TABLE** statement defines the names of columns, their data types, and any constraints (like PRIMARY KEY or NOT NULL). The **ALTER TABLE** command allows you to change an existing table's structure—such as adding a new column or modifying a constraint—without deleting the entire table and its existing data. Finally, **DROP TABLE** is used when a table is no longer needed, permanently deleting both its structure and all its records. These commands are typically used by database administrators to architect the layout of information.

sqlDDL Commands Example

-- Create a new table
CREATE TABLE Student (
    RollID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

-- Modify the table structure
ALTER TABLE Student ADD Marks FLOAT;

Data Manipulation (DML)

DML is used to work with the actual records stored inside the table. **INSERT** adds new rows of data into the table. **UPDATE** modifies the values of existing records, often using a WHERE clause to target specific rows. For example, you can use UPDATE to change the price of all items in a specific category. **DELETE** removes specific rows from the table. It is crucial to use the WHERE clause with UPDATE and DELETE; without it, the command will affect every single row in the table, potentially leading to catastrophic data loss. These commands are the foundation of 'CRUD' (Create, Read, Update, Delete) operations in modern applications.

sqlDML Commands Example

-- Add a new record
INSERT INTO Student VALUES (101, 'Aditi', 'aditi@mail.com', 95.5);

-- Update existing data
UPDATE Student SET Marks = 98 WHERE RollID = 101;

-- Delete a specific record
DELETE FROM Student WHERE RollID = 101;

Querying Data (DQL)

The most powerful part of SQL is the **SELECT** statement. It allows you to retrieve specific data using complex logic. You can use **WHERE** to filter rows based on conditions, **DISTINCT** to remove duplicate results, and **ORDER BY** to sort the output. SQL also provides built-in 'Aggregate Functions' like SUM(), AVG(), MIN(), MAX(), and COUNT() to perform calculations on entire columns. When combined with the **GROUP BY** clause, you can generate reports—for example, finding the average marks 'grouped by' each subject. The **HAVING** clause allows you to filter these grouped results even further.

sqlDQL Query Example

-- Complex query with grouping and sorting
SELECT Subject, AVG(Marks) as AvgMarks
FROM Student
WHERE Marks > 35
GROUP BY Subject
HAVING AvgMarks > 80
ORDER BY AvgMarks DESC;

Pattern Matching & Ranges

SQL provides powerful symbols for flexible searching. The **LIKE** operator with '%' (any string) and '_' (any single character) allows for pattern matching—for example, finding all names that start with 'A'. The **BETWEEN** operator checks if a value falls within a specific range (like marks between 70 and 90), and the **IN** operator checks if a value matches any item in a specific list. These 'operators' make SQL a highly versatile tool for data analysis and precise information retrieval in large-scale systems.

sqlSQL Operators Example

-- Pattern matching and range check
SELECT * FROM Student 
WHERE Name LIKE 'A%'
AND Marks BETWEEN 80 AND 100
AND Subject IN ('CS', 'IP');

📝 Quick Revision Points

1DDL: Structure (schema)
2DML: Content (data)
3DQL: Retrieval (queries)
4PRIMARY KEY enforces uniqueness
5WHERE filters rows, HAVING filters groups
6SQL is CASE-INSENSITIVE for commands but CASE-SENSITIVE for data

← PreviousDatabase Concepts Next →Python-MySQL Connectivity

Loading notes...

-- Create a new table CREATE TABLE Student ( RollID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE ); -- Modify the table structure ALTER TABLE Student ADD Marks FLOAT;

-- Add a new record INSERT INTO Student VALUES (101, 'Aditi', 'aditi@mail.com', 95.5); -- Update existing data UPDATE Student SET Marks = 98 WHERE RollID = 101; -- Delete a specific record DELETE FROM Student WHERE RollID = 101;