Database, SQL Revision (Class 11 Recap)

Introduction

This chapter opens Class 12's database work with a quick recap of Class 11's core vocabulary, before this unit moves into new SQL functions and querying techniques.

Concept

Recapping the essentials:

• A database is an organised collection of related data; a DBMS is the software managing it.
• The Relational Model organises data into Tables (Rows/Tuples, Columns/Attributes).
• A Candidate Key could uniquely identify a row; the Primary Key is the one chosen to actually do so (never NULL, never repeated); every other Candidate Key becomes an Alternate Key.

Worked Example

The STUDENT table this unit continues to use:

RollNo is the Primary Key — unique for every student, never empty. If Name also happened to be guaranteed unique in this specific school's records, it would be a Candidate Key too, and — since it isn't the chosen Primary Key — an Alternate Key.

Note: A well-chosen Primary Key should be something that genuinely never changes and never repeats for a real person — a roll number or admission number, not a name, which two different students could plausibly share.

Explanation

This entire vocabulary set exists for one purpose: guaranteeing every row in a table can be told apart from every other row, reliably — the foundation every SQL command in the rest of this unit depends on being true.

Summary

A database is managed by a DBMS; the Relational Model organises data into Tables of Rows and Columns; a table's Primary Key (chosen from its Candidate Keys) uniquely identifies each row, with every other Candidate Key becoming an Alternate Key — the STUDENT table's RollNo is this unit's running example of one.

Introduction

This topic recaps Class 11's Data Definition commands — the ones that build a database's structure before any data goes in.

Concept

Recapping the three DDL commands:

• CREATE DATABASE name; / CREATE TABLE name (columns...); — builds a new database or table.
• ALTER TABLE name ADD/DROP COLUMN/MODIFY ...; — changes an existing table's structure.
• DROP TABLE name; — removes an entire table, permanently.

Worked Example

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

ALTER TABLE STUDENT ADD City VARCHAR(20);

CREATE TABLE STUDENT (...) builds this unit's running table, with RollNo as its Primary Key and Name required (NOT NULL). ALTER TABLE STUDENT ADD City VARCHAR(20); then adds a City column to the already-existing table — every row gains the new column, empty until filled in.

Warning: DROP TABLE STUDENT; would remove the entire table and every row of data in it, permanently — a fundamentally more destructive command than ALTER, which only reshapes structure.

Explanation

CREATE, ALTER, and DROP together cover a table's entire structural lifecycle: bringing it into existence, reshaping it as requirements change, and — if genuinely needed — removing it completely.

Summary

CREATE builds a new database or table; ALTER TABLE changes an existing one's structure (ADD/DROP COLUMN/MODIFY); DROP TABLE removes an entire table and its data permanently — the three DDL commands this unit's SQL work continues to rely on.

/* ==================================================
   1. CLEANUP (Standard Practice)
   If table exists, delete it so we start fresh.
   ================================================== */
DROP TABLE IF EXISTS STUDENT;

/* ==================================================
   2. CREATE TABLE
   Syntax: CREATE TABLE Name (Col Type Constraint, ...);
   ================================================== */
CREATE TABLE STUDENT (
    AdmNo INTEGER PRIMARY KEY,
    Name VARCHAR(20),
    DOB DATE
);

/* ==================================================
   3. VERIFICATION
   Check if the table was created (It will be empty).
   ================================================== */
SELECT * FROM STUDENT;

Introduction

This topic recaps Class 11's Data Manipulation commands — the ones that fill and change the rows inside a table CREATE already built.

Concept

Recapping the three DML commands:

• INSERT INTO table (columns) VALUES (values); — adds one new row.
• UPDATE table SET column = value WHERE condition; — changes matching rows.
• DELETE FROM table WHERE condition; — removes matching rows.

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;

The two INSERT statements build up the STUDENT table one row at a time. UPDATE STUDENT SET Marks = 95 WHERE RollNo = 1; then changes only Aisha's Marks, leaving Rahul's row completely untouched — the WHERE clause is what limits the change to exactly the intended row.

Warning: UPDATE/DELETE without a WHERE clause applies to every row in the table, not just one — always double-check WHERE is present before running either.

Explanation

WHERE is the one clause every DML command relies on to target specific rows rather than the whole table — the exact same clause the next topic uses again, for SELECT instead.

Summary

INSERT adds a row; UPDATE ... WHERE changes matching rows; DELETE FROM ... WHERE removes matching rows — omitting WHERE on UPDATE/DELETE affects the entire table at once.

/* ==================================================
   SYSTEM SETUP (Building the empty table)
   ================================================== */
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE (EmpID INT, Name VARCHAR(50), Salary INT);

/* ==================================================
   YOUR TASK: INSERT DATA
   Syntax: INSERT INTO TableName VALUES (val1, val2...);
   ================================================== */
INSERT INTO EMPLOYEE VALUES (101, 'Amit', 50000);
INSERT INTO EMPLOYEE VALUES (102, 'Sumit', 60000);

/* ==================================================
   VERIFICATION
   ================================================== */
SELECT * FROM EMPLOYEE;

/* ==================================================
   SYSTEM SETUP (Table with Data)
   ================================================== */
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE (EmpID INT, Name VARCHAR(50), Salary INT);
INSERT INTO EMPLOYEE VALUES (101, 'Amit', 50000);
INSERT INTO EMPLOYEE VALUES (102, 'Sumit', 60000);

/* ==================================================
   YOUR TASK: UPDATE AMIT'S SALARY
   ================================================== */
UPDATE EMPLOYEE 
SET Salary = 55000 
WHERE EmpID = 101;

/* ==================================================
   VERIFICATION (Did Amit's salary change?)
   ================================================== */
SELECT * FROM EMPLOYEE;

/* ==================================================
   SYSTEM SETUP (Fresh Data)
   ================================================== */
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE (EmpID INT, Name VARCHAR(50), Salary INT);
INSERT INTO EMPLOYEE VALUES (101, 'Amit', 55000);
INSERT INTO EMPLOYEE VALUES (102, 'Sumit', 60000);

/* ==================================================
   YOUR TASK: REMOVE SUMIT
   ================================================== */
DELETE FROM EMPLOYEE 
WHERE EmpID = 102;

/* ==================================================
   VERIFICATION (Is Sumit gone?)
   ================================================== */
SELECT * FROM EMPLOYEE;

Introduction

This closing recap topic covers Class 11's SELECT command — the most-used SQL command, and the one the rest of this unit builds directly on top of.

Concept

Recapping:

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

SELECT names which columns to retrieve (* for all); FROM names the table; WHERE filters which rows are included.

Worked Example

SELECT * FROM STUDENT;

SELECT Name, Marks FROM STUDENT WHERE Stream = 'Science';

SELECT * FROM STUDENT; retrieves every column, for every row, unfiltered. SELECT Name, Marks FROM STUDENT WHERE Stream = 'Science'; retrieves only two columns, and only for rows where Stream is 'Science' — Rahul (Commerce) would not appear in this result.

Note: BETWEEN, IN, LIKE, IS NULL, and AND/OR/NOT — the more advanced ways to filter with WHERE — were also covered in Class 11, and remain exactly as useful here; this recap keeps to the essentials.

Explanation

Every new SQL technique this unit covers from here — functions, sorting, grouping, joins — is really just extending what SELECT ... FROM ... WHERE already does: retrieve exactly the data you actually need, nothing more.

Summary

SELECT columns FROM table WHERE condition; retrieves data, filtered to matching rows — the foundation this unit's new SQL functions and multi-table querying techniques all build directly on top of.

/* ==================================================
   SYSTEM SETUP (Populating Data)
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (Name VARCHAR(20), City VARCHAR(20), Marks INT);
INSERT INTO STUDENT VALUES ('Amit', 'Delhi', 90);
INSERT INTO STUDENT VALUES ('Ravi', 'Mumbai', 85);
INSERT INTO STUDENT VALUES ('Pooja', 'Delhi', 92);
INSERT INTO STUDENT VALUES ('Sumit', 'Chennai', 88);

/* ==================================================
   TASK A: View Everything
   ================================================== */
SELECT * FROM STUDENT;

/* ==================================================
   TASK B: Unique Cities
   (Notice 'Delhi' appears only once in the result)
   ================================================== */
SELECT DISTINCT City FROM STUDENT;

/* ==================================================
   SYSTEM SETUP (Re-using previous data)
   Amit (Delhi, 90), Ravi (Mumbai, 85), 
   Pooja (Delhi, 92), Sumit (Chennai, 88)
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (Name VARCHAR(20), City VARCHAR(20), Marks INT);
INSERT INTO STUDENT VALUES ('Amit', 'Delhi', 90);
INSERT INTO STUDENT VALUES ('Ravi', 'Mumbai', 85);
INSERT INTO STUDENT VALUES ('Pooja', 'Delhi', 92);
INSERT INTO STUDENT VALUES ('Sumit', 'Chennai', 88);

/* ==================================================
   TASK A: High Scorers (> 89)
   ================================================== */
SELECT * FROM STUDENT 
WHERE Marks > 89;

/* ==================================================
   TASK B: Toppers from Delhi
   (Only Pooja matches both conditions)
   ================================================== */
SELECT * FROM STUDENT 
WHERE City = 'Delhi' AND Marks > 90;