08 Structured Query Language (SQL)

CBSE Class 11 Informatics Practices

8.1 Introduction

In the previous chapter, we learned about databases, DBMS, and the relational data model, where data is stored in the form of tables. However, storing data in tables alone is not sufficient. We also need a systematic and efficient way to create tables, insert data, retrieve information, modify records, and delete unwanted data.

To perform all these operations on a relational database, a special language called Structured Query Language (SQL) is used.

According to the NCERT textbook, Structured Query Language (SQL) is a standard language used to communicate with a relational database.

Need for SQL

NCERT highlights that SQL is needed because:

  • Data must be created and organised in tables
  • Large volumes of data must be handled efficiently
  • Data must be retrieved quickly and accurately
  • Multiple users may access the database simultaneously
  • Data integrity and security must be maintained

Without SQL, interacting with databases would be complex and inefficient.

What SQL Does

SQL allows users to perform the following operations on a database:

  • Create databases and tables
  • Insert data into tables
  • Retrieve specific data from tables
  • Update existing records
  • Delete unwanted records
  • Control access to data

Thus, SQL acts as a bridge between the user and the database.

SQL and Relational Databases

SQL is specifically designed for relational databases, where:

  • Data is stored in tables (relations)
  • Rows represent records (tuples)
  • Columns represent attributes

SQL commands operate directly on these tables.

📌 NCERT Exam Point SQL is used to access and manipulate data stored in relational databases.

Characteristics of SQL

NCERT outlines the following important characteristics of SQL:

  • SQL is non-procedural (declarative)
  • Users specify what data is required, not how to get it
  • SQL is easy to learn and use
  • SQL is supported by most relational DBMS

This makes SQL user-friendly and powerful.

SQL as a Standard Language

SQL is a standardised language, meaning:

  • It is recognised by international standards organisations
  • Most DBMS follow SQL standards with minor variations

Examples of DBMS that support SQL include:

  • MySQL
  • Oracle
  • PostgreSQL
  • Microsoft SQL Server

In Class XI IP, NCERT uses MySQL as the reference DBMS.

Advantages of Using SQL

SQL provides several advantages:

  • Simple and readable commands
  • Efficient handling of large datasets
  • Supports multi-user environments
  • Ensures data security and integrity
  • Reduces complexity of database operations

These advantages make SQL the most widely used database language.

SQL and Data Integrity

SQL supports mechanisms to:

  • Prevent duplicate records
  • Restrict invalid data entry
  • Maintain relationships between tables

This ensures accuracy and consistency of data, which is essential in database systems.

Role of SQL in Informatics Practices

NCERT introduces SQL in Class XI to help students:

  • Understand how databases are used in real-world applications
  • Learn basic database operations
  • Prepare for advanced database concepts in higher classes
  • Develop data-handling skills

Thus, SQL forms a core part of database education.

Key Points to Remember (NCERT-Oriented)

  • SQL stands for Structured Query Language
  • SQL is used to interact with relational databases
  • SQL commands create, manipulate, and retrieve data
  • SQL is non-procedural in nature
  • MySQL is the DBMS used in NCERT syllabus

8.2 Structured Query Language (SQL)

After understanding the need for SQL, the next step is to understand how SQL is organised. SQL is not a single command or operation; rather, it is a collection of commands, each designed to perform a specific type of task on a database.

According to the NCERT textbook, SQL commands are classified into different categories based on the type of operation they perform on the database.

This classification helps users:

  • Understand the purpose of each command
  • Use commands correctly
  • Learn SQL in a structured manner

Classification of SQL Commands

NCERT broadly classifies SQL commands into the following categories:

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Data Query Language (DQL)
  4. Transaction Control Language (TCL)

Each category plays a specific role in database management.

1. Data Definition Language (DDL)

Data Definition Language (DDL) commands are used to define and modify the structure of database objects such as tables, databases, and schemas.

DDL commands deal with the structure of data, not the data itself.

Purpose of DDL

DDL is used to:

  • Create database objects
  • Modify the structure of tables
  • Remove database objects when they are no longer required

Common DDL Commands (NCERT Scope)

Command Purpose
CREATE Creates database objects
ALTER Modifies existing structures
DROP Deletes database objects
TRUNCATE Removes all records from a table

📌 NCERT Exam Point DDL commands affect the database structure.

Characteristics of DDL Commands

  • They permanently change database structure
  • Changes are automatically saved
  • Used mainly by database designers and administrators

2. Data Manipulation Language (DML)

Data Manipulation Language (DML) commands are used to insert, modify, and delete data stored in database tables.

DML commands deal with the actual data, not the structure.

Purpose of DML

DML is used to:

  • Add new records to tables
  • Modify existing records
  • Remove unwanted records

Common DML Commands (NCERT Scope)

Command Purpose
INSERT Adds new records
UPDATE Modifies existing records
DELETE Removes records

📌 NCERT Exam Point DML commands work on table data.

Characteristics of DML Commands

  • Changes can be rolled back (with transaction control)
  • Used frequently by end users and applications
  • Affect selected rows or entire tables

3. Data Query Language (DQL)

Data Query Language (DQL) commands are used to retrieve data from the database.

Although sometimes grouped with DML, NCERT treats DQL as a separate category.

Purpose of DQL

DQL is used to:

  • Fetch required data from tables
  • Display data in meaningful formats
  • Apply conditions and filters

Main DQL Command

Command Purpose
SELECT Retrieves data from tables

📌 NCERT Exam Point SELECT is the most commonly used SQL command.

Characteristics of DQL Commands

  • Do not modify data
  • Used for reporting and analysis
  • Support conditions and sorting

4. Transaction Control Language (TCL)

Transaction Control Language (TCL) commands are used to manage transactions in a database.

A transaction is a sequence of one or more SQL operations treated as a single logical unit of work.

Purpose of TCL

TCL is used to:

  • Save changes permanently
  • Undo changes when required
  • Maintain database consistency

Common TCL Commands (NCERT Scope)

Command Purpose
COMMIT Saves changes permanently
ROLLBACK Undoes changes
SAVEPOINT Sets a point for rollback

📌 NCERT Observation TCL commands help maintain data consistency and integrity.

Summary Table: SQL Command Categories

Category Deals With Example Commands
DDL Database structure CREATE, ALTER
DML Table data INSERT, UPDATE
DQL Data retrieval SELECT
TCL Transactions COMMIT, ROLLBACK

Importance of SQL Classification

NCERT expects students to understand this classification because:

  • It simplifies learning SQL
  • Helps choose correct commands
  • Improves exam performance
  • Clarifies the role of each command

Many exam questions are based on identifying the correct category of a given SQL command.

Key Points to Remember (NCERT-Oriented)

  • SQL commands are grouped by functionality
  • DDL defines database structure
  • DML manipulates data
  • DQL retrieves data
  • TCL controls transactions
  • SELECT is the most commonly used SQL command

8.3 Data Types and Constraints in MySQL

When creating tables in a database, it is essential to specify:

  1. What kind of data each column will store
  2. What rules should be applied to the data

In SQL (MySQL), this is done using data types and constraints.

According to NCERT, data types define the type of data that can be stored in a column, while constraints define rules that restrict the values allowed in a column.

8.3.1 Data Types in MySQL

A data type specifies:

  • The kind of values a column can store
  • The size of data
  • The operations that can be performed on the data

Choosing the correct data type is important for:

  • Efficient storage
  • Data accuracy
  • Better performance

Classification of Data Types (NCERT Scope)

NCERT classifies MySQL data types into:

  1. Numeric Data Types
  2. Character (String) Data Types
  3. Date and Time Data Types

1. Numeric Data Types

Numeric data types are used to store numbers.

Common Numeric Data Types

Data Type Description
INT Stores whole numbers
FLOAT Stores decimal numbers
DOUBLE Stores large decimal numbers

Example: Using INT and FLOAT

CREATE TABLE StudentMarks (
    RollNo INT,
    Marks FLOAT
);

Explanation:

  • RollNo stores whole numbers
  • Marks stores decimal values

📌 NCERT Exam Point INT is used for integer values; FLOAT is used for decimal values.

2. Character (String) Data Types

Character data types store textual data such as names and addresses.

Common Character Data Types

Data Type Description
CHAR(n) Fixed-length character string
VARCHAR(n) Variable-length character string

Difference Between CHAR and VARCHAR

CHAR VARCHAR
Fixed length Variable length
Wastes space Saves space
Faster access More efficient storage

Example: Using CHAR and VARCHAR

CREATE TABLE Student (
    Name VARCHAR(30),
    Gender CHAR(1)
);

Explanation:

  • Name can store names of varying length
  • Gender stores a single character (M/F)

📌 NCERT Exam Point VARCHAR is preferred when data length varies.

3. Date and Time Data Types

Date and time data types are used to store dates and time values.

Common Date/Time Data Types

Data Type Description
DATE Stores date (YYYY-MM-DD)
TIME Stores time (HH:MM:SS)
DATETIME Stores date and time

Example: Using DATE

CREATE TABLE Exam (
    ExamDate DATE
);

Explanation:

  • Stores only the date of the exam

8.3.2 Constraints in MySQL

Constraints are rules applied to table columns to ensure accuracy, validity, and integrity of data.

According to NCERT, constraints are used to:

  • Prevent invalid data entry
  • Enforce uniqueness
  • Maintain relationships between tables

Types of Constraints (NCERT Scope)

NCERT focuses on the following constraints:

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. DEFAULT

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have empty (NULL) values.

Example: NOT NULL

CREATE TABLE Student (
    RollNo INT NOT NULL,
    Name VARCHAR(30)
);

Explanation:

  • RollNo must always have a value
  • NULL values are not allowed

📌 NCERT Exam Point NOT NULL prevents missing values.

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Example: UNIQUE

CREATE TABLE Student (
    Email VARCHAR(50) UNIQUE
);

Explanation:

  • No two students can have the same email ID

3. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each record in a table.

Rules of Primary Key

  • Must be unique
  • Cannot be NULL
  • Only one primary key per table

Example: PRIMARY KEY

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30)
);

📌 NCERT Exam Point PRIMARY KEY = UNIQUE + NOT NULL

4. FOREIGN KEY Constraint

A FOREIGN KEY is used to link two tables.

Example: FOREIGN KEY

CREATE TABLE Marks (
    RollNo INT,
    Marks INT,
    FOREIGN KEY (RollNo) REFERENCES Student(RollNo)
);

Explanation:

  • RollNo in Marks table refers to RollNo in Student table
  • Ensures referential integrity

5. DEFAULT Constraint

The DEFAULT constraint assigns a default value if no value is provided.

Example: DEFAULT

CREATE TABLE Student (
    City VARCHAR(20) DEFAULT 'Delhi'
);

Explanation:

  • If city is not entered, it will be set to Delhi

Using Multiple Constraints Together

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Email VARCHAR(50) UNIQUE,
    City VARCHAR(20) DEFAULT 'Delhi'
);

📌 NCERT Exam Tip Questions often ask you to identify errors in table definitions.

Importance of Data Types and Constraints

They ensure:

  • Correct type of data is stored
  • Data consistency and accuracy
  • Prevention of invalid entries
  • Proper relationships between tables

Without data types and constraints, databases become unreliable.

Key Points to Remember (NCERT-Oriented)

  • Data types define the type of data
  • Constraints enforce rules on data
  • INT, VARCHAR, DATE are commonly used data types
  • PRIMARY KEY uniquely identifies records
  • FOREIGN KEY links tables
  • Constraints maintain data integrity

8.4 SQL for Data Definition (DDL)

In a relational database, before inserting or querying data, it is necessary to define the structure of the database objects such as tables. SQL provides a set of commands specifically meant for this purpose. These commands are known as Data Definition Language (DDL) commands.

According to NCERT, DDL commands are used to create, modify, and delete the structure of database objects.

DDL commands work on the schema (structure) of the database, not on the data stored inside it.

DDL Commands in SQL (NCERT Scope)

The main DDL commands covered in Class XI IP are:

  1. CREATE
  2. ALTER
  3. DROP
  4. TRUNCATE

Each command serves a specific purpose in database definition.

8.4.1 CREATE Command

The CREATE command is used to:

  • Create a database
  • Create a table inside a database

Creating a Database

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE SchoolDB;

Explanation:

  • A new database named SchoolDB is created
  • No tables exist yet inside the database

📌 NCERT Exam Point CREATE DATABASE creates an empty database.

Creating a Table

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Example: Creating a Table

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Class INT,
    City VARCHAR(20)
);

Explanation:

  • Student table is created
  • RollNo uniquely identifies each record
  • Name cannot be NULL
  • Data types and constraints define structure

📌 NCERT Exam Tip Always mention datatype + constraint in table definition questions.

8.4.2 ALTER Command

The ALTER command is used to modify the structure of an existing table.

It can be used to:

  • Add a new column
  • Modify an existing column
  • Delete a column

1. Adding a New Column

Syntax

ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE Student
ADD Email VARCHAR(40);

Explanation:

  • Adds a new column Email to Student table
  • Existing records get NULL values for this column

2. Modifying an Existing Column

Syntax

ALTER TABLE table_name
MODIFY column_name new_datatype;

Example

ALTER TABLE Student
MODIFY Name VARCHAR(50);

Explanation:

  • Changes the size of Name column
  • Existing data is preserved

3. Deleting a Column

Syntax

ALTER TABLE table_name
DROP column_name;

Example

ALTER TABLE Student
DROP City;

Explanation:

  • Removes City column permanently
  • Data in that column is lost

📌 NCERT Exam Warning ALTER DROP cannot be undone.

8.4.3 DROP Command

The DROP command is used to delete database objects permanently.

Dropping a Table

Syntax

DROP TABLE table_name;

Example

DROP TABLE Student;

Explanation:

  • Entire Student table is deleted
  • Structure and data are both removed

Dropping a Database

DROP DATABASE SchoolDB;

📌 NCERT Exam Point DROP removes structure + data permanently.

8.4.4 TRUNCATE Command

The TRUNCATE command is used to:

  • Remove all records from a table
  • Keep the table structure intact

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE Student;

Explanation:

  • All records are deleted
  • Table still exists
  • Faster than DELETE (covered later)

Difference Between DROP and TRUNCATE

DROP TRUNCATE
Deletes table structure Keeps table structure
Deletes data Deletes data
Table no longer exists Table still exists
Cannot be reused Can insert data again

📌 NCERT Exam Favourite Table

Important Notes on DDL Commands

  • DDL commands are auto-committed
  • Changes are permanent
  • Used mainly by database designers
  • Affect schema, not data values

Common Mistakes Students Make

  • Forgetting data types in CREATE TABLE
  • Confusing DROP and TRUNCATE
  • Using ALTER incorrectly
  • Missing semicolon (;)

Exams often test these mistakes.

Key Points to Remember (NCERT-Oriented)

  • DDL defines database structure
  • CREATE is used to create databases and tables
  • ALTER modifies table structure
  • DROP deletes tables/databases permanently
  • TRUNCATE deletes all records but keeps table
  • DDL changes are auto-saved

8.5 SQL for Data Manipulation (DML)

After defining the structure of a database using DDL commands, the next step is to work with the data stored inside the tables. SQL provides a set of commands specifically designed to insert, modify, and delete data. These commands are known as Data Manipulation Language (DML) commands.

According to NCERT, DML commands are used to manipulate data stored in database tables.

DML Commands in SQL (NCERT Scope)

The main DML commands covered in Class XI IP are:

  1. INSERT
  2. UPDATE
  3. DELETE

These commands directly affect the records (rows) in a table.

8.5.1 INSERT Command

The INSERT command is used to add new records into a table.

Inserting Data into a Table (All Columns)

Syntax

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example

INSERT INTO Student
VALUES (1, 'Amit', 11, 'Delhi');

Explanation:

  • A new record is inserted into Student
  • Values must match the order of columns in the table
  • Data types must match column definitions

📌 NCERT Exam Tip Order of values must match table structure.

Inserting Data into Selected Columns

Syntax

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Example

INSERT INTO Student (RollNo, Name)
VALUES (2, 'Neha');

Explanation:

  • Only specified columns receive values
  • Remaining columns get NULL or DEFAULT values

8.5.2 UPDATE Command

The UPDATE command is used to modify existing records in a table.

Updating All Records

Syntax

UPDATE table_name
SET column_name = value;

Example

UPDATE Student
SET City = 'Mumbai';

Explanation:

  • Updates City for all records
  • Use carefully

📌 NCERT Warning UPDATE without WHERE affects all rows.

Updating Selected Records (Using WHERE)

Syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Example

UPDATE Student
SET City = 'Chennai'
WHERE RollNo = 2;

Explanation:

  • Updates city only for student with RollNo 2

Updating Multiple Columns

UPDATE Student
SET Class = 12, City = 'Kolkata'
WHERE RollNo = 1;

8.5.3 DELETE Command

The DELETE command is used to remove records from a table.

Deleting All Records

Syntax

DELETE FROM table_name;

Example

DELETE FROM Student;

Explanation:

  • Removes all records
  • Table structure remains intact

Deleting Selected Records (Using WHERE)

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM Student
WHERE RollNo = 2;

Explanation:

  • Deletes only the record with RollNo 2

📌 NCERT Exam Tip DELETE without WHERE removes all rows.

Difference Between DELETE and TRUNCATE

DELETE TRUNCATE
DML command DDL command
Can use WHERE No WHERE allowed
Removes selected rows Removes all rows
Slower Faster

Using DEFAULT Values in INSERT

INSERT INTO Student (RollNo, Name)
VALUES (3, 'Riya');
  • Columns with DEFAULT values are automatically filled
  • Columns without default get NULL

Common Mistakes in DML Commands

  • Forgetting WHERE clause
  • Mismatch in number of values
  • Violating PRIMARY KEY or NOT NULL constraints
  • Wrong data type usage

Exams often test these errors.

Transaction Awareness (Conceptual)

Although transactions are covered later, NCERT expects students to know:

  • DML commands can be rolled back
  • Changes are not permanent until committed

Key Points to Remember (NCERT-Oriented)

  • DML commands manipulate table data
  • INSERT adds records
  • UPDATE modifies records
  • DELETE removes records
  • WHERE clause controls affected rows
  • DELETE ≠ TRUNCATE

8.6 SQL for Data Query (DQL)

After data has been stored and manipulated in database tables, the most common requirement is to retrieve specific information from the database. SQL provides a powerful command for this purpose called SELECT. The set of commands used to retrieve data is known as Data Query Language (DQL).

According to NCERT, DQL commands are used to retrieve data from one or more tables without modifying the data.

SELECT Command

The SELECT command is used to:

  • Display data from a table
  • Retrieve specific columns or rows
  • Apply conditions and sorting

It is the most frequently used SQL command.

Basic Syntax

SELECT column1, column2
FROM table_name;

8.6.1 Selecting All Columns

To display all columns of a table, the asterisk (*) symbol is used.

Example

SELECT * FROM Student;

Explanation:

  • Displays all records and all columns of Student table

📌 NCERT Exam Point * represents all columns.

8.6.2 Selecting Specific Columns

To display only selected columns:

Example

SELECT RollNo, Name
FROM Student;

Explanation:

  • Displays only RollNo and Name columns
  • Other columns are ignored

8.6.3 Using WHERE Clause

The WHERE clause is used to filter records based on a condition.

Syntax

SELECT column_name
FROM table_name
WHERE condition;

Example

SELECT * FROM Student
WHERE City = 'Delhi';

Explanation:

  • Displays only students from Delhi

📌 NCERT Exam Tip WHERE filters rows, not columns.

Using Relational Operators in WHERE

Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> or != Not equal

Example

SELECT Name, Class
FROM Student
WHERE Class > 10;

8.6.4 Using AND / OR Operators

Multiple conditions can be combined using logical operators.

AND Operator

SELECT * FROM Student
WHERE City = 'Delhi' AND Class = 11;
  • Both conditions must be true

OR Operator

SELECT * FROM Student
WHERE City = 'Delhi' OR City = 'Mumbai';
  • Any one condition must be true

8.6.5 DISTINCT Keyword

The DISTINCT keyword is used to remove duplicate values from query results.

Syntax

SELECT DISTINCT column_name
FROM table_name;

Example

SELECT DISTINCT City
FROM Student;

Explanation:

  • Displays each city only once

📌 NCERT Exam Point DISTINCT works on column values, not rows.

8.6.6 ORDER BY Clause

The ORDER BY clause is used to sort query results.

Syntax

SELECT column_name
FROM table_name
ORDER BY column_name ASC|DESC;

Ascending Order (Default)

SELECT Name
FROM Student
ORDER BY Name;

Descending Order

SELECT Name
FROM Student
ORDER BY Name DESC;

📌 NCERT Exam Tip ASC is default; DESC must be specified explicitly.

8.6.7 LIMIT Clause

The LIMIT clause is used to restrict the number of rows displayed.

Syntax

SELECT * FROM table_name
LIMIT number;

Example

SELECT * FROM Student
LIMIT 3;

Explanation:

  • Displays only first 3 records

8.6.8 Using Multiple Clauses Together

SQL clauses are written in a specific order:

SELECT column_name
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number;

Example

SELECT Name, City
FROM Student
WHERE Class = 11
ORDER BY Name
LIMIT 5;

Common Errors Students Make

  • Writing WHERE before FROM
  • Forgetting quotes for text values
  • Using = instead of <>
  • Using LIMIT before ORDER BY

📌 NCERT Exam Strategy Clause order matters.

Difference Between WHERE and DISTINCT

WHERE DISTINCT
Filters rows Removes duplicate values
Uses conditions No condition required
Works on rows Works on column values

Key Points to Remember (NCERT-Oriented)

  • SELECT is a DQL command
  • WHERE filters rows
  • DISTINCT removes duplicates
  • ORDER BY sorts results
  • LIMIT restricts number of rows
  • Clauses must follow correct order

8.7 Data Updation and Deletion

In a database, data is not static. Records may need to be modified when information changes or removed when they are no longer required. SQL provides specific commands to perform these tasks safely and efficiently.

According to NCERT, data updation and deletion are performed using the UPDATE and DELETE commands, which belong to the Data Manipulation Language (DML) category.

Why Data Updation and Deletion Are Needed

NCERT highlights that data updation and deletion are required because:

  • Information may change over time
  • Incorrect data needs correction
  • Old or irrelevant data must be removed
  • Databases must remain accurate and up-to-date

Without proper updation and deletion, databases become unreliable.

8.7.1 Data Updation Using UPDATE Command

The UPDATE command is used to change existing values in one or more records of a table.

Basic Syntax

UPDATE table_name
SET column_name = new_value
WHERE condition;

📌 NCERT Exam Point The WHERE clause is optional but very important.

Updating a Single Record

Example

UPDATE Student
SET City = 'Pune'
WHERE RollNo = 3;

Explanation:

  • Updates city only for the student with RollNo 3
  • Other records remain unchanged

Updating Multiple Columns

Example

UPDATE Student
SET Class = 12, City = 'Jaipur'
WHERE RollNo = 1;

Explanation:

  • Updates both Class and City in one command

Updating Multiple Records

Example

UPDATE Student
SET City = 'Delhi'
WHERE Class = 11;

Explanation:

  • Updates city for all students of Class 11

⚠️ Updating All Records (Without WHERE)

UPDATE Student
SET City = 'Mumbai';

📌 NCERT Warning (Very Important) If WHERE is omitted, ALL records are updated.

This is a frequently tested concept.

8.7.2 Data Deletion Using DELETE Command

The DELETE command is used to remove records from a table.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Deleting a Single Record

Example

DELETE FROM Student
WHERE RollNo = 5;

Explanation:

  • Deletes only the student with RollNo 5

Deleting Multiple Records

Example

DELETE FROM Student
WHERE Class = 10;

Explanation:

  • Deletes all students belonging to Class 10

⚠️ Deleting All Records

DELETE FROM Student;

📌 NCERT Exam Point DELETE without WHERE removes all rows, but table structure remains.

DELETE vs TRUNCATE (Revisited)

DELETE TRUNCATE
DML command DDL command
WHERE allowed WHERE not allowed
Deletes selected rows Deletes all rows
Slower Faster
Can be rolled back Cannot be rolled back

Using Conditions in UPDATE and DELETE

SQL allows conditions using:

  • Relational operators (=, >, <, !=)
  • Logical operators (AND, OR)

Example: Conditional UPDATE

UPDATE Student
SET City = 'Bhopal'
WHERE Class = 12 AND City = 'Indore';

Example: Conditional DELETE

DELETE FROM Student
WHERE City = 'Chennai' OR City = 'Kochi';

Safety Practices (NCERT Exam Awareness)

NCERT implicitly expects students to follow safe practices:

  1. Always use WHERE clause
  2. Verify records using SELECT before UPDATE/DELETE
  3. Be careful with mass updates
  4. Understand irreversible operations

Safe Practice Example

SELECT * FROM Student
WHERE Class = 11;

(Verify records first)

DELETE FROM Student
WHERE Class = 11;

Common Mistakes Students Make

  • Forgetting WHERE clause
  • Using incorrect conditions
  • Deleting required data accidentally
  • Confusing DELETE and TRUNCATE

These mistakes are often tested as error-spotting questions.

Transaction Awareness (Conceptual)

NCERT expects basic awareness that:

  • UPDATE and DELETE are part of transactions
  • Changes can be rolled back before COMMIT
  • Once committed, changes become permanent

(Details covered in higher classes)

Key Points to Remember (NCERT-Oriented)

  • UPDATE modifies existing records
  • DELETE removes records
  • WHERE clause controls affected rows
  • UPDATE/DELETE without WHERE affects all records
  • DELETE ≠ TRUNCATE
  • Always verify before modifying data
Page last updated: 19-Jan-2026
Last Change: "some changes for timezone and git checkin history pull by hugo" (#b34f1bc)