Structured Query Language (SQL)

Introduction

Now that you know what a relational database and its keys are, this topic introduces the language used to actually create, fill, and query one: SQL.

Concept

SQL (Structured Query Language) is the standard language for working with a relational database. Its commands fall into three groups CBSE names explicitly:

• DDL (Data Definition Language) — defines the database structure itself: CREATE, ALTER, DROP.
• DML (Data Manipulation Language) — works with the data inside tables: INSERT, UPDATE, DELETE.
• DQL (Data Query Language) — retrieves data: SELECT.

Explanation

This three-way split mirrors a natural order of work: DDL builds the empty structure (a table with no rows yet), DML fills and changes what's inside it, and DQL reads it back out — you cannot manipulate or query data in a table that DDL hasn't created yet, which is exactly why this chapter covers them in that order.

Note: CBSE papers and reference material write SQL keywords in UPPERCASE (SELECT, WHERE) — this convention is followed throughout this chapter, and identifiers/values stay lowercase or as originally written.

Summary

SQL commands split into DDL (defines structure: CREATE/ALTER/DROP), DML (changes data: INSERT/UPDATE/DELETE), and DQL (retrieves data: SELECT) — structure must exist before data can be manipulated or queried, which is the order this chapter follows.

Procedural vs. Declarative: The Key Difference

This is a favorite exam question. Understanding the difference is crucial:

• Procedural (e.g., Python): You must tell the computer step-by-step how to do a task (e.g., "Start loop, check item, if match then print, else continue").
• Declarative (e.g., SQL): You only describe what you want (e.g., "SELECT * FROM Students"). The database engine figures out the most efficient way to get it.

Classification of SQL Commands

To master SQL, you must categorize commands by their function. We use the "House Building" analogy to remember them easily.

Category	Analogy (The House)	Technical Definition
DDL	Construction Phase Building walls, adding rooms, or demolishing the house.	Data Definition Language Deals with the database Structure (Schema). Commands: CREATE, ALTER, DROP
DML	Living Phase Moving furniture in, rearranging items, or throwing trash out.	Data Manipulation Language Deals with the Data inside the tables. Commands: INSERT, UPDATE, DELETE
DQL	Viewing Phase Looking for your keys or checking what's in the fridge.	Data Query Language Used strictly to Retrieve information. Commands: SELECT

/* ==================================================
   SYSTEM SETUP
   ================================================== */
-- In this lab, you don't need to create anything yet.
-- We are just checking if the MySQL server is alive 
-- and which workspace (Database) we are standing in.

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: 
   1. List all databases currently on this server.
   2. Check which database is currently "Active" (Selected).
   ================================================== */

-- 1. Command to view all available databases:
SHOW DATABASES;

-- 2. Command to see the name of the current active database:
SELECT DATABASE();

Introduction

This topic covers the first DDL command you'll actually run — building a database and a table inside it, from nothing.

Concept

CREATE DATABASE database_name;

Creates a new, empty database.

CREATE TABLE table_name (
    column_name data_type constraint,
    ...
);

Creates a table with named columns, each given a data type and optional constraints:

• PRIMARY KEY — marks a column as the table's Primary Key (unique, never NULL).
• NOT NULL — the column can never be left empty.

Worked Example

CREATE DATABASE School;

CREATE TABLE STUDENT (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Stream VARCHAR(20),
    Marks INT
);

CREATE DATABASE School; makes a new, empty database named School. CREATE TABLE STUDENT (...) then creates a STUDENT table with four columns: RollNo (a whole number, and the table's Primary Key), Name (text up to 30 characters, and required — NOT NULL), Stream (text up to 20 characters, optional), and Marks (a whole number). The table exists now, structurally — but has zero rows, since CREATE only defines structure; it never inserts data.

Note: VARCHAR(30) means "variable-length text, up to 30 characters" — the number in parentheses is a maximum length, not a fixed one.

Explanation

Declaring RollNo as PRIMARY KEY here is exactly the Primary Key concept from the last topic, enforced directly by the database itself: any attempt to insert a duplicate or NULL RollNo afterward will be rejected automatically, rather than relying on a person to notice the mistake.

Summary

CREATE DATABASE name; creates an empty database. CREATE TABLE name (...) creates a table with named, typed columns — PRIMARY KEY and NOT NULL are constraints the database then enforces automatically on every future row.

/* ==================================================
   SYSTEM SETUP
   ================================================== */
-- We clear any existing table named 'LIBRARY' 
-- so you can build it fresh.
DROP TABLE IF EXISTS LIBRARY;

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: Create a table named 'LIBRARY' with this Schema:
   1. BookID (Integer, must be the PRIMARY KEY)
   2. BookName (Variable text, max 50 chars)
   3. Cost (Integer)
   ================================================== */

-- Write your CREATE TABLE query below:
-- CREATE TABLE LIBRARY ( ... );


-- Verify your structure:
DESC LIBRARY;

Introduction

A table's structure isn't fixed forever once created — this topic covers changing it afterward, and removing it entirely.

Concept

ALTER TABLE table_name ADD column_name data_type;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY column_name new_data_type;

ALTER TABLE changes an existing table's structure: adding a column, removing one, or changing a column's data type.

DROP TABLE table_name;

Removes an entire table — structure and all its data — permanently.

Worked Example

ALTER TABLE STUDENT ADD City VARCHAR(20);

ALTER TABLE STUDENT DROP COLUMN Stream;

ALTER TABLE STUDENT ADD City VARCHAR(20); adds a new City column to the already-existing STUDENT table — every existing row gets this new column too, with no value in it yet. ALTER TABLE STUDENT DROP COLUMN Stream; removes the Stream column entirely, along with whatever data it held in every row.

Warning: DROP TABLE STUDENT; deletes the entire table permanently, including every row of data — there is no undo. This is a fundamentally different, far more destructive operation than removing a single column.

Explanation

ALTER and DROP sit at opposite ends of the same idea: ALTER reshapes a table that should keep existing, while DROP removes it (or, via DROP COLUMN, removes one specific part of it) permanently — knowing which one a question calls for is exactly the distinction between "change" and "destroy."

Summary

ALTER TABLE ... ADD/DROP COLUMN/MODIFY changes an existing table's structure. DROP TABLE removes an entire table, structure and data, permanently — a genuinely destructive operation, unlike ALTER.

Common Alterations:

Goal	Renovation Analogy	Syntax
ADD	Building a new room.	ALTER TABLE table_name ADD col_name datatype;
MODIFY	Changing the carpet to tiles (changing Data Type).	ALTER TABLE table_name MODIFY col_name new_type;
DROP	Demolishing a single room.	ALTER TABLE table_name DROP COLUMN col_name;

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE (EmpID INT, Name VARCHAR(20));
INSERT INTO EMPLOYEE VALUES (101, 'Ravi');
INSERT INTO EMPLOYEE VALUES (102, 'Sita');

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: The EMPLOYEE table is missing salary info.
   Add a new column named 'Salary' with type INT.
   ================================================== */

-- Write your ALTER query below:
-- ALTER TABLE EMPLOYEE ...


-- Verify the new structure (You should see Salary):
DESC EMPLOYEE;

/* ==================================================
   SYSTEM SETUP
   ================================================== */
CREATE TABLE OLD_DATA (ID INT, Note VARCHAR(50));
INSERT INTO OLD_DATA VALUES (1, 'Useless Record');

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: Completely destroy the 'OLD_DATA' table
   from the database.
   ================================================== */

-- Write your DROP query below:
-- DROP TABLE ... 


-- Attempting to select from it now should give an error:
-- SELECT * FROM OLD_DATA;

Introduction

The STUDENT table exists, but it's still empty. This topic covers the three DML commands that actually put data in, change it, and take it back out.

Concept

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

Adds one new row.

UPDATE table_name SET column = new_value WHERE condition;

Changes existing rows matching condition.

DELETE FROM table_name WHERE condition;

Removes rows matching condition.

Worked Example

INSERT INTO STUDENT (RollNo, Name, Stream, Marks) VALUES (1, 'Aisha', 'Science', 91);
INSERT INTO STUDENT (RollNo, Name, Stream, Marks) VALUES (2, 'Rahul', 'Commerce', 78);

UPDATE STUDENT SET Marks = 95 WHERE RollNo = 1;

DELETE FROM STUDENT WHERE RollNo = 2;

The two INSERT statements add Aisha and Rahul as rows. UPDATE STUDENT SET Marks = 95 WHERE RollNo = 1; changes only Aisha's Marks to 95, leaving every other row untouched — the WHERE clause is what limits the change to exactly one row. DELETE FROM STUDENT WHERE RollNo = 2; removes Rahul's row entirely; only Aisha's row remains.

Warning: UPDATE STUDENT SET Marks = 0; or DELETE FROM STUDENT; — either one without a WHERE clause — applies to every single row in the table, not just one. Always double-check a WHERE clause is present before running UPDATE or DELETE.

Explanation

WHERE is what turns UPDATE/DELETE from "affect the whole table" into "affect exactly the row(s) meant" — the same clause, covered fully for SELECT in the next two topics, that filters which rows a command actually touches.

Summary

INSERT INTO table (columns) VALUES (values); adds one row. UPDATE table SET column = value WHERE condition; changes matching rows. DELETE FROM table WHERE condition; removes matching rows — omitting WHERE on either UPDATE or DELETE affects the entire table at once.

Two Ways to Insert Data:

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS GADGETS;
CREATE TABLE GADGETS (ID INT, Name VARCHAR(20), Price INT);

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: Add two products to the GADGETS table.
   1. ID: 101, Name: 'Watch', Price: 2500
   2. ID: 102, Name: 'Tablet', Price: 15000
   ================================================== */

-- Write your INSERT queries below:
-- INSERT INTO GADGETS VALUES (...);
-- INSERT INTO GADGETS VALUES (...);


-- Check if the data is inside:
SELECT * FROM GADGETS;

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS STAFF;
CREATE TABLE STAFF (ID INT, Name VARCHAR(20), Salary INT);
INSERT INTO STAFF VALUES (1, 'Arjun', 50000);
INSERT INTO STAFF VALUES (2, 'Kavita', 50000);

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: Arjun has been promoted! 
   Change Arjun's salary (ID=1) to 60000.
   ================================================== */

-- Write your UPDATE query below:
-- UPDATE STAFF SET ... WHERE ...;


-- Verify the change:
SELECT * FROM STAFF;

Introduction

Everything so far has built and filled the STUDENT table. This topic covers actually reading data back out of it — the single most-used SQL command.

Concept

SELECT column1, column2, ... FROM table_name WHERE condition;

• SELECT — names which columns to retrieve. SELECT * retrieves every column.
• FROM — names the table to read from.
• WHERE — filters which rows are included, using a condition.

Worked Example

Given a STUDENT table with several rows:

SELECT * FROM STUDENT;

SELECT Name, Marks FROM STUDENT WHERE Marks >= 90;

SELECT * FROM STUDENT; returns every column, for every row, unfiltered. SELECT Name, Marks FROM STUDENT WHERE Marks >= 90; returns only two columns (Name, Marks), and only for rows where Marks is at least 90 — a student scoring 78 would not appear in this result at all.

Note: WHERE in a SELECT filters exactly the same way it does in UPDATE/DELETE from the last topic — the same clause, the same job: limiting which rows a command applies to.

Explanation

SELECT * is convenient while exploring a table, but naming exact columns (SELECT Name, Marks) is the better habit for a real query — it returns only what's actually needed, and stays correct even if the table later gains new columns you don't care about.

Summary

SELECT columns FROM table WHERE condition; retrieves data — SELECT * returns every column, WHERE filters which rows are included, using the same condition logic as UPDATE/DELETE.

The Anatomy of a Query:

• SELECT *: The asterisk (*) is a wildcard that means "ALL Columns".
• SELECT Col1, Col2: Used to show only specific columns (Projection).

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS MENU;
CREATE TABLE MENU (ItemID INT, Dish VARCHAR(20), Price INT, Type VARCHAR(10));
INSERT INTO MENU VALUES (1, 'Burger', 150, 'Veg');
INSERT INTO MENU VALUES (2, 'Chicken Wings', 250, 'Non-Veg');
INSERT INTO MENU VALUES (3, 'Fries', 100, 'Veg');
INSERT INTO MENU VALUES (4, 'Soda', 50, 'Drink');

/* ==================================================
   YOUR TASK STARTS HERE
   Goal 1: View the entire menu (All columns).
   Goal 2: View ONLY the 'Dish' and 'Price' columns.
   ================================================== */

-- 1. Select Everything:
SELECT * FROM MENU;

-- 2. Select Specific Columns:
-- SELECT ... FROM ...;

Relational Operators:

You use these symbols to define your condition:

Symbol	Meaning	Example Usage
=	Equal to	WHERE Price = 100
>	Greater than	WHERE Marks > 90
<	Less than	WHERE Age < 18
!= or <>	Not Equal to	WHERE City != 'Delhi'

/* ==================================================
   SYSTEM SETUP
   ================================================== */
-- Using the same MENU table from before:
-- 1. Burger (150)
-- 2. Chicken Wings (250)
-- 3. Fries (100)
-- 4. Soda (50)

/* ==================================================
   YOUR TASK STARTS HERE
   Goal 1: Find items that cost MORE than 120.
   Goal 2: Find items that are NOT 'Veg'.
   ================================================== */

-- 1. Expensive items:
-- SELECT * FROM MENU WHERE Price > 120;

-- 2. Non-Veg items:
-- SELECT * FROM MENU WHERE Type != 'Veg';

Introduction

WHERE from the last topic compared a column against one fixed value. This topic covers four operators that filter against a range, a list, a text pattern, and emptiness itself.

Concept

• BETWEEN a AND b — matches a value within a range, inclusive of both ends.
• IN (v1, v2, ...) — matches any value found in a given list.
• LIKE 'pattern' — matches a text pattern, using wildcards: % (any number of characters, including zero) and _ (exactly one character).
• IS NULL / IS NOT NULL — checks whether a column is empty — NULL can never be tested with =.

Worked Example

SELECT * FROM STUDENT WHERE Marks BETWEEN 80 AND 90;
SELECT * FROM STUDENT WHERE Stream IN ('Science', 'Commerce');
SELECT * FROM STUDENT WHERE Name LIKE 'A%';
SELECT * FROM STUDENT WHERE Stream IS NULL;

The first query returns every student with Marks from 80 to 90, both included. The second returns every student whose Stream is either 'Science' or 'Commerce' — the same result as chaining OR conditions, written more compactly. Name LIKE 'A%' matches any name starting with A, regardless of what follows. Stream IS NULL returns rows where Stream was never given a value.

Warning: WHERE Stream = NULL never matches anything, even a genuinely empty Stream — NULL represents "unknown/absent," and = can't meaningfully compare against it. IS NULL is the only correct way to test for it.

Explanation

IN (...) is really shorthand for a chain of OR conditions on the same column — Stream IN ('Science', 'Commerce') means exactly Stream = 'Science' OR Stream = 'Commerce', just shorter to write and easier to read once the list grows.

Summary

BETWEEN a AND b matches an inclusive range; IN (...) matches any value in a list; LIKE 'pattern' matches text using % (any characters) and _ (one character); IS NULL/IS NOT NULL is the only correct way to test for emptiness, since = can never match NULL.

Operator	Usage	Example
BETWEEN	Selects values within a specific range. Note: It is inclusive (start and end values are included).	WHERE Price BETWEEN 100 AND 200
IN	Checks if a value matches any value in a list. It replaces multiple OR conditions.	WHERE City IN ('Delhi', 'Mumbai', 'Goa')

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS SALES;
CREATE TABLE SALES (OrderID INT, Product VARCHAR(20), Amount INT);
INSERT INTO SALES VALUES (1, 'Laptop', 50000);
INSERT INTO SALES VALUES (2, 'Mouse', 500);
INSERT INTO SALES VALUES (3, 'Keyboard', 1500);
INSERT INTO SALES VALUES (4, 'Monitor', 12000);

/* ==================================================
   YOUR TASK STARTS HERE
   Goal 1: Find products with an Amount between 1000 and 15000.
   Goal 2: Find products that cost exactly 500 OR 50000.
   ================================================== */

-- 1. Using BETWEEN:
-- SELECT * FROM SALES WHERE Amount BETWEEN ... AND ...;

-- 2. Using IN:
-- SELECT * FROM SALES WHERE Amount IN (...);

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS CONTACTS;
CREATE TABLE CONTACTS (ID INT, Name VARCHAR(20));
INSERT INTO CONTACTS VALUES (1, 'Ravi');
INSERT INTO CONTACTS VALUES (2, 'Raj');
INSERT INTO CONTACTS VALUES (3, 'Ramesh');
INSERT INTO CONTACTS VALUES (4, 'Amit');

/* ==================================================
   YOUR TASK STARTS HERE
   Goal 1: Find names starting with 'R'.
   Goal 2: Find names that have exactly 3 characters.
   ================================================== */

-- 1. Using % (Starts with R):
-- SELECT * FROM CONTACTS WHERE Name LIKE 'R%';

-- 2. Using _ (Exactly 3 chars):
-- SELECT * FROM CONTACTS WHERE Name LIKE '___';

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS VISITORS;
CREATE TABLE VISITORS (Name VARCHAR(20), Email VARCHAR(30));
INSERT INTO VISITORS VALUES ('Arun', 'arun@gmail.com');
INSERT INTO VISITORS VALUES ('Bina', NULL); -- Email is unknown

/* ==================================================
   YOUR TASK STARTS HERE
   Goal: Find visitors who have NOT provided an email address.
   ================================================== */

-- Write your query below:
-- SELECT * FROM VISITORS WHERE Email ...;

Introduction

This closing topic of the chapter covers combining more than one condition in a single WHERE clause — the SQL equivalent of the logical operators you already used in Python.

Concept

• AND — a row matches only if every condition is true.
• OR — a row matches if at least one condition is true.
• NOT — inverts a condition.

Worked Example

SELECT * FROM STUDENT WHERE Stream = 'Science' AND Marks >= 90;
SELECT * FROM STUDENT WHERE Stream = 'Science' OR Stream = 'Commerce';
SELECT * FROM STUDENT WHERE NOT Stream = 'Commerce';

The first query returns only students who are both in Science and scored at least 90 — missing either condition excludes the row. The second returns any student in Science or Commerce (or both, though a student normally has one Stream). The third returns every student whose Stream is not 'Commerce' — Science students, and any other stream, but not Commerce.

Note: AND is always stricter than OR — adding an AND condition can only narrow a result down further; adding an OR condition can only widen it. Confusing the two changes which rows a query actually returns.

Explanation

These three operators combine SQL conditions exactly the way and/or/not combined Python conditions earlier in this course — the same underlying logic, just applied to filtering database rows instead of choosing a code path.

Summary

AND requires every condition to be true; OR requires at least one; NOT inverts a condition — combining conditions in WHERE this way is the same logical combination Python's and/or/not already taught, now applied to filtering rows in a query.

Operator	Logic Rule	Real World Example
AND	Both conditions must be TRUE.	City = 'Delhi' AND Age > 18 (Must be a Delhi adult)
OR	At least one condition must be TRUE.	City = 'Delhi' OR City = 'Mumbai' (Can be from either city)
NOT	Reverses the result. True becomes False.	NOT (City = 'Delhi') (Anyone except Delhi residents)

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS PHONES;
CREATE TABLE PHONES (Model VARCHAR(20), Brand VARCHAR(10), Price INT, 5G_Ready VARCHAR(3));
INSERT INTO PHONES VALUES ('iPhone 13', 'Apple', 60000, 'Yes');
INSERT INTO PHONES VALUES ('iPhone SE', 'Apple', 30000, 'Yes');
INSERT INTO PHONES VALUES ('Galaxy S21', 'Samsung', 55000, 'Yes');
INSERT INTO PHONES VALUES ('Galaxy A12', 'Samsung', 12000, 'No');

/* ==================================================
   YOUR TASK STARTS HERE
   Goal 1: Find phones that are 'Apple' AND cost less than 40000.
   Goal 2: Find phones that are either 'Samsung' OR have 'No' 5G.
   ================================================== */

-- 1. Cheap Apple Phones (AND):
-- SELECT * FROM PHONES WHERE Brand = 'Apple' AND Price < 40000;

-- 2. Samsung or Non-5G (OR):
-- SELECT * FROM PHONES WHERE Brand = 'Samsung' OR 5G_Ready = 'No';