Class XII · Chapter 9Unit 3, Database Management (20 marks shared with Database Concepts)13 min read
Share:WhatsAppLinkedIn

Chapter 9: Structured Query Language (SQL)

CBSE Unit: Unit 3, Database Management (20 marks shared with Database Concepts) Marks Weightage: ~14-16 marks (2nd most important chapter after Python) Priority: CRITICAL, heavy weightage, practical + theory questions

Key Concepts

9.1 Introduction to SQL, SQL = Structured Query Language, used to create and manage relational databases, Used with RDBMS like MySQL, Oracle, PostgreSQL, SQL Server, SQL is case insensitive (SELECT = select = SeLeCt), Every SQL statement ends with a semicolon (;)

  • Multiline statements: no semicolon until the last line; prompt changes from mysql> to ->
  • Text and date values must be enclosed in single quotes (' ')

9.2 SQL Categories

Category Full Form Purpose Commands
DDL Data Definition Language Define/modify table structure CREATE, ALTER, DROP
DML Data Manipulation Language Insert/modify/delete data INSERT, UPDATE, DELETE, SELECT

9.3 Data Types in MySQL

Data Type Description Size/Range
CHAR(n) Fixed-length character string 0 to 255 characters; pads with spaces if shorter
VARCHAR(n) Variable-length character string 0 to 65535; occupies only actual length
INT Integer values 4 bytes; 0 to 4,294,967,295 (unsigned)
FLOAT Decimal numbers 4 bytes
DATE Date in 'YYYY-MM-DD' format '1000-01-01' to '9999-12-31'

Key difference: CHAR(10) always uses 10 bytes; VARCHAR(10) uses only bytes needed for actual data.

9.4 Constraints in MySQL

Constraint Description
NOT NULL Column cannot have NULL values
UNIQUE All values in column must be distinct
DEFAULT Sets a default value if no value is provided
PRIMARY KEY Uniquely identifies each row (implies NOT NULL + UNIQUE)
FOREIGN KEY References the primary key of another table

DDL Commands (Data Definition Language)

CREATE DATABASE

CREATE DATABASE databasename;
-- Example:
CREATE DATABASE StudentAttendance;

USE Database

USE StudentAttendance;

SHOW Databases/Tables

SHOW DATABASES;
SHOW TABLES;

CREATE TABLE

CREATE TABLE tablename(
    attribute1 datatype constraint,
    attribute2 datatype constraint,
    PRIMARY KEY (attribute1)
);

-- Example:
CREATE TABLE STUDENT(
    RollNumber INT,
    SName VARCHAR(20),
    SDateofBirth DATE,
    GUID CHAR(12),
    PRIMARY KEY (RollNumber)
);

DESCRIBE Table

DESCRIBE tablename;
-- or
DESC tablename;

ALTER TABLE

(A) Add Primary Key:

ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);

(B) Add Composite Primary Key:

ALTER TABLE ATTENDANCE ADD PRIMARY KEY(AttendanceDate, RollNumber);

(C) Add Foreign Key:

ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

Rules for foreign key:

  • Referenced table must already exist, Referenced attribute must be part of primary key in referenced table, Data types and size of both attributes must match

(D) Add UNIQUE Constraint:

ALTER TABLE GUARDIAN ADD UNIQUE(GPhone);

(E) Add New Attribute:

ALTER TABLE GUARDIAN ADD income INT;

(F) Modify Datatype:

ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);

(G) Modify Constraint (add NOT NULL):

ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;

(H) Add Default Value:

ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT '2000-05-15';

(I) Remove/Drop Attribute:

ALTER TABLE GUARDIAN DROP income;

(J) Remove Primary Key:

ALTER TABLE GUARDIAN DROP PRIMARY KEY;

DROP Statement

DROP TABLE tablename;
DROP DATABASE databasename;

Caution: DROP is permanent and cannot be undone. Dropping a database removes all its tables.

DML Commands (Data Manipulation Language)

INSERT INTO

-- Insert values for ALL columns (order must match table structure):
INSERT INTO GUARDIAN VALUES (444444444444, 'Amit Ahuja', 5711492685, 'G-35, Ashok Vihar, Delhi');

-- Insert values for SPECIFIC columns:
INSERT INTO GUARDIAN(GUID, GName, GAddress) VALUES (333333333333, 'Danny Dsouza', 'S-13, Ashok Village, Daman');
  • Text and date values must be in single quotes, When inserting into a table with foreign key, referenced table records must exist first
  • NULL can be explicitly written: VALUES(3, 'Taleem Shah', '2002-02-28', NULL);

UPDATE

UPDATE tablename SET column1 = value1, column2 = value2 WHERE condition;

-- Example:
UPDATE STUDENT SET GUID = 101010101010 WHERE RollNumber = 3;
UPDATE GUARDIAN SET GAddress = 'New Address', GPhone = 9010810547 WHERE GUID = 466444444666;

CAUTION: Without WHERE clause, ALL records get updated!

DELETE

DELETE FROM tablename WHERE condition;

-- Example:
DELETE FROM STUDENT WHERE RollNumber = 2;

CAUTION: Without WHERE clause, ALL records get deleted!

SELECT Query (Data Query Language)

Basic SELECT

SELECT attribute1, attribute2 FROM tablename WHERE condition;
SELECT * FROM tablename;  -- all columns

Column Alias (AS)

SELECT EName AS Name, Salary*12 AS 'Annual Income' FROM EMPLOYEE;
  • Alias with spaces must be in quotes, Alias does NOT change the actual table column name

DISTINCT

SELECT DISTINCT DeptId FROM EMPLOYEE;
-- Removes duplicate values from output

WHERE Clause with Operators

Relational Operators: =, <, <=, >, >=, != (or <>)

Logical Operators: AND, OR, NOT

SELECT * FROM EMPLOYEE WHERE Salary > 5000 AND DeptId = 'D04';
SELECT * FROM EMPLOYEE WHERE NOT Ename = 'Aaliya';

BETWEEN Operator

SELECT * FROM EMPLOYEE WHERE Salary BETWEEN 20000 AND 50000;
-- Equivalent to: WHERE Salary >= 20000 AND Salary <= 50000
-- BETWEEN is INCLUSIVE of both boundary values

IN Operator (Membership)

SELECT * FROM EMPLOYEE WHERE DeptId IN ('D01', 'D02', 'D04');
SELECT * FROM EMPLOYEE WHERE DeptId NOT IN ('D01', 'D02');

ORDER BY Clause

SELECT * FROM EMPLOYEE ORDER BY Salary;        -- ascending (default)
SELECT * FROM EMPLOYEE ORDER BY Salary DESC;    -- descending
SELECT * FROM EMPLOYEE ORDER BY Salary, Bonus DESC;  -- multi-column sort

NULL Handling

SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
SELECT * FROM EMPLOYEE WHERE Bonus IS NOT NULL AND DeptID = 'D01';
  • NULL is NOT equal to 0 (zero), Any arithmetic with NULL gives NULL: 5 + NULL = NULL
  • Use IS NULL / IS NOT NULL (NOT = or !=)

LIKE Operator (Pattern Matching)

Wildcard Meaning Example
% Zero, one, or multiple characters 'K%' = starts with K
_ Exactly one character '_ANYA' = 5-letter word ending with ANYA 
SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';       -- starts with K
SELECT * FROM EMPLOYEE WHERE Ename LIKE '%a';        -- ends with a
SELECT * FROM EMPLOYEE WHERE Ename LIKE '%se%';      -- contains 'se'
SELECT * FROM EMPLOYEE WHERE Ename LIKE '_a%';       -- 'a' as second character
SELECT * FROM EMPLOYEE WHERE Ename LIKE '_ANYA';     -- exactly 5 chars, ends with ANYA

Built-in Functions/Methods

Single Row (Scalar) Functions

Math Functions

Function Description Example Output
POWER(X,Y) or POW(X,Y) X raised to power Y SELECT POWER(2,3); 8
ROUND(N,D) Round N to D decimal places SELECT ROUND(2912.564, 1); 2912.6
ROUND(N) Round to nearest integer SELECT ROUND(283.2); 283
MOD(A,B) Remainder of A/B SELECT MOD(21, 2); 1

String Functions

Function Description Example Output
UCASE(str) / UPPER(str) Convert to uppercase SELECT UCASE('hello'); HELLO
LCASE(str) / LOWER(str) Convert to lowercase SELECT LOWER('HELLO'); hello
MID(str, pos, n) / SUBSTRING(str, pos, n) / SUBSTR(str, pos, n) Extract substring of n chars from position pos SELECT MID('Informatics', 3, 4); form
MID(str, pos) Extract from pos to end SELECT MID('Informatics', 7); atics
LENGTH(str) Number of characters SELECT LENGTH('Informatics'); 11
LEFT(str, N) First N characters SELECT LEFT('Computer', 4); Comp
RIGHT(str, N) Last N characters SELECT RIGHT('SCIENCE', 3); NCE
INSTR(str, substr) Position of first occurrence (0 if not found) SELECT INSTR('Informatics', 'ma'); 6
LTRIM(str) Remove leading spaces SELECT LTRIM(' DELHI'); DELHI
RTRIM(str) Remove trailing spaces SELECT RTRIM('PEN '); PEN
TRIM(str) Remove leading and trailing spaces SELECT TRIM(' MADAM '); MADAM

Date and Time Functions

Function Description Example Output
NOW() Current date and time SELECT NOW(); 2019-07-11 19:41:17
DATE(expression) Extract date part SELECT DATE(NOW()); 2019-07-11
MONTH(date) Month as number (1-12) SELECT MONTH(NOW()); 7
MONTHNAME(date) Month name SELECT MONTHNAME('2003-11-28'); November
YEAR(date) Year SELECT YEAR('2003-10-03'); 2003
DAY(date) Day of month SELECT DAY('2003-03-24'); 24
DAYNAME(date) Name of day SELECT DAYNAME('2019-07-11'); Thursday

Aggregate (Multiple Row) Functions

Function Description Notes
MAX(column) Largest value in column Numeric columns
MIN(column) Smallest value in column Numeric columns
AVG(column) Average of all values Numeric columns
SUM(column) Sum of all values Numeric columns
COUNT(*) Number of rows in table Counts ALL rows including NULL
COUNT(column) Number of non-NULL values Ignores NULL values

Key Differences: Single Row vs Aggregate Functions

Single Row Function Aggregate Function
Operates on one row at a time Operates on groups of rows
Returns one result per row Returns one result per group
Can be used in SELECT, WHERE, ORDER BY Can be used in SELECT clause only
Examples: Math, String, Date functions Examples: MAX, MIN, AVG, SUM, COUNT 
-- Aggregate examples:
SELECT MAX(Price) FROM INVENTORY;
SELECT COUNT(*) FROM INVENTORY WHERE Model='VXI';
SELECT COUNT(DISTINCT Model) FROM INVENTORY;
SELECT AVG(Price) FROM INVENTORY WHERE Model='LXI';

GROUP BY Clause

Groups rows with same values in specified column; used with aggregate functions.

SELECT CustID, COUNT(*) AS "Number of Cars" FROM SALE GROUP BY CustID;

HAVING Clause, Used to filter groups created by GROUP BY (like WHERE but for groups), WHERE filters individual rows BEFORE grouping; HAVING filters AFTER grouping

SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING COUNT(*) > 1;
SELECT PaymentMode, COUNT(PaymentMode) FROM SALE GROUP BY PaymentMode HAVING COUNT(*) > 1 ORDER BY PaymentMode;

Operations on Relations

Conditions: Both relations must have the same number of attributes and corresponding attributes must have the same domain.

UNION (U), Combines selected rows of two tables, Removes duplicate rows (shows common rows only once), Result cardinality <= sum of both cardinalities

INTERSECT (intersection symbol), Returns only the common tuples from both tables, Result cardinality <= smaller table's cardinality

MINUS (-), Returns tuples in first table but NOT in second table, A - B is NOT the same as B, A

Cartesian Product (X), Combines ALL tuples from both relations (every row paired with every other row)

  • Degree of result = Degree(R1) + Degree(R2)
  • Cardinality of result = Cardinality(R1) x Cardinality(R2), Example: Table with 4 rows X Table with 5 rows = 20 rows, combined columns

Using Two Relations (JOIN)

Cartesian Product in SQL

SELECT * FROM DANCE, MUSIC;
-- With condition:
SELECT * FROM DANCE D, MUSIC M WHERE D.Name = M.Name;

JOIN with ON Clause

SELECT * FROM UNIFORM U JOIN COST C ON U.Ucode = C.Ucode;

NATURAL JOIN, Same as JOIN but removes the duplicate common column

  • Automatically joins on common attribute(s)
SELECT * FROM UNIFORM NATURAL JOIN COST;

Important rules for JOIN:

  • N-1 joins needed to combine N tables, JOIN can use any relational operator, NATURAL JOIN only works on equality of common attributes, Table aliases (e.g., UNIFORM U) are valid only for current query

Common Board Exam Question Patterns

  1. Write SQL queries (2-4 marks each): Create table, insert records, select with conditions, most common question type
  2. Write output of given SQL query (2-3 marks): Given a table and query, predict the output
  3. DDL vs DML identification (1 mark): Classify given statements
  4. ALTER vs UPDATE difference (2 marks): ALTER changes structure, UPDATE changes data
  5. DELETE vs DROP difference (2 marks): DELETE removes rows, DROP removes entire table
  6. Aggregate function queries (2-3 marks): MAX, MIN, AVG, SUM, COUNT on given data
  7. GROUP BY with HAVING (3-4 marks): Write queries using GROUP BY and HAVING
  8. Pattern matching with LIKE (2 marks): Write queries using % and _ wildcards
  9. NULL handling (1-2 marks): IS NULL / IS NOT NULL queries
  10. ORDER BY queries (1-2 marks): Sort ascending/descending
  11. BETWEEN and IN usage (1-2 marks): Range and membership queries
  12. Function-based queries (2-3 marks): String/Math/Date functions in SELECT
  13. JOIN queries (3-4 marks): Combine two tables using equi-join or natural join
  14. Degree and Cardinality of Cartesian Product (1-2 marks): Calculate resulting degree and cardinality

Key Points Students Miss

  1. ALTER changes table structure; UPDATE changes data, most confused pair
  2. DELETE removes rows; DROP removes entire table/database, another confused pair
  3. BETWEEN is inclusive of both boundary values
  4. WHERE clause cannot use aggregate functions, use HAVING instead
  5. COUNT(*) counts ALL rows including NULL; COUNT(column) ignores NULL
  6. GROUP BY must include all non-aggregate columns from SELECT
  7. HAVING is used ONLY with GROUP BY (not independently)
  8. ORDER BY default is ascending (ASC), use DESC explicitly for descending
  9. Foreign key table records must be inserted AFTER referenced table records
  10. String and date values need single quotes in SQL; numeric values do not
  11. LIKE wildcards: % = any number of characters, _ = exactly one character
  12. NATURAL JOIN removes duplicate column; regular JOIN does not
  13. NULL arithmetic: Any operation with NULL gives NULL (5 + NULL = NULL)
  14. Alias names with spaces must be in quotes: AS 'Annual Income'
  15. Without WHERE in UPDATE/DELETE, the operation applies to ALL rows, extremely dangerous
  16. CHAR vs VARCHAR: CHAR is fixed-length (wastes space), VARCHAR is variable-length (space efficient)

Test Your Knowledge

Take a quick quiz on this chapter

Start Quiz →

Prefer watching over reading?

Subscribe for free.

Subscribe on YouTube