Class 12 Relational Databases and SQL: Complete Concepts and Practice

Big picture

A database stores related data in an organised way so it can be searched, updated and shared easily. A relational database stores data in tables (relations) that can be linked through common columns. SQL (Structured Query Language) is the language used to create these tables and query them.

The board paper always includes SQL queries on one or two given tables: write the query, or predict its output. Precision matters: a missing quote, the wrong use of WHERE instead of HAVING, or forgetting that aggregate functions ignore NULL can cost marks. Every query in this lesson was run to check its output.

Learning goals

• Explain the need for databases and the advantages of a DBMS.

• Define relational model terms and identify the degree and cardinality of a table.

• Identify candidate, primary, alternate and foreign keys.

• Use SQL DDL commands (CREATE, ALTER, DROP, DESCRIBE) and DML commands (INSERT, UPDATE, DELETE, SELECT).

• Use constraints, operators, DISTINCT, aliases, WHERE, IN, BETWEEN, LIKE, IS NULL and ORDER BY.

• Use aggregate functions, GROUP BY and HAVING.

• Join two tables and predict the results.

Core ideas

1. Databases and DBMS

A file system stores data in separate files, which causes redundancy (the same data repeated), inconsistency (copies don't match), and difficulty in access and sharing. A database management system (DBMS), such as MySQL, manages a database and provides:

• reduced redundancy and improved consistency;

• data sharing among many users;

• data security through access control;

• data integrity through constraints;

• easy querying and backup.

2. The relational model

Sample tables used in this lesson:

EMP

DEPT

EMP has degree 5 and cardinality 6.

3. Keys

4. SQL data types and constraints

Constraints: NOT NULL (value must be given), UNIQUE (no duplicates), PRIMARY KEY (NOT NULL + UNIQUE), DEFAULT (a value used when none is given), FOREIGN KEY (references another table).

5. DDL commands (Data Definition Language)

CREATE DATABASE company;
USE company;
SHOW TABLES;

CREATE TABLE EMP (
    EmpID   INT PRIMARY KEY,
    Name    VARCHAR(20) NOT NULL,
    Dept    VARCHAR(10),
    Salary  INT,
    DeptNo  INT
);

DESCRIBE EMP;                                   -- shows the table structure
ALTER TABLE EMP ADD Phone CHAR(10);             -- add a column
ALTER TABLE EMP DROP Phone;                     -- remove a column
ALTER TABLE EMP ADD PRIMARY KEY (EmpID);        -- add a primary key
ALTER TABLE EMP DROP PRIMARY KEY;               -- remove it
DROP TABLE EMP;                                 -- delete the table and its data

6. DML commands (Data Manipulation Language)

INSERT INTO EMP VALUES (107, 'Isha', 'HR', 41000, 3);
INSERT INTO EMP (EmpID, Name) VALUES (108, 'Dev');   -- other columns become NULL
UPDATE EMP SET Salary = Salary + 2000 WHERE Dept = 'HR';
DELETE FROM EMP WHERE EmpID = 108;

UPDATE or DELETE without a WHERE clause affects every row. DELETE removes rows but keeps the table; DROP TABLE removes the table itself.

7. SELECT queries

NULL means "no value". It can't be compared with =; use IS NULL or IS NOT NULL.

Operators: arithmetic (+ − * / %), relational (= < > <= >= <> or !=), logical (AND, OR, NOT).

8. Aggregate functions

Aggregate (multiple-row) functions return one value for a group of rows. They ignore NULL values, except COUNT(*).

9. GROUP BY and HAVING

GROUP BY collects rows with the same value into groups, so aggregates are calculated per group. HAVING filters groups (using aggregate conditions), while WHERE filters rows before grouping.

SELECT Dept, AVG(Salary) FROM EMP
WHERE Dept IS NOT NULL
GROUP BY Dept;
-- HR 38000, IT 60000, Sales 48000

SELECT Dept, COUNT(*) FROM EMP
GROUP BY Dept
HAVING COUNT(*) > 1;
-- IT 2, Sales 2

Order of clauses: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….

10. Joining two tables

Cartesian product: every row of one table is combined with every row of the other. SELECT * FROM EMP, DEPT; gives 6 × 3 = 18 rows; its degree is 5 + 3 = 8 columns.

Equi-join: a Cartesian product filtered by equality of the common column:

SELECT Name, City FROM EMP E, DEPT D
WHERE E.DeptNo = D.DeptNo AND City = 'Pune';
-- Ravi Pune, Neha Pune

(Table aliases E and D shorten the query. The common column appears twice in SELECT * of an equi-join.)

Natural join: like an equi-join, but the common column appears only once: SELECT * FROM EMP NATURAL JOIN DEPT;.

A reliable routine for SQL questions

1. Identify the table(s) and columns needed.

2. Write WHERE for row conditions, using quotes for text and IS NULL for nulls.

3. For "per department" or "each city", use GROUP BY; filter groups with HAVING.

4. For data from two tables, join on the common column.

5. For output questions, apply WHERE, then GROUP BY, then HAVING, then ORDER BY, and remember NULL rules.

Connections that matter

• Python–SQL connectivity runs these queries from Python programs.

• Informatics Practices: advanced SQL covers the same SQL with more functions.

• Computer networks: database servers are accessed over a network.

Common mistakes

• Using `= NULL` instead of `IS NULL`.

• Using `WHERE` with an aggregate condition (use HAVING).

• Forgetting that `BETWEEN` includes both end values.

• Mixing up `%` (any length) and `_` (exactly one character) in LIKE.

• Mixing up degree and cardinality. Degree counts columns; cardinality counts rows.

• Writing `DELETE` or `UPDATE` without WHERE when only some rows should change.

Revision checklist

• I can explain DBMS advantages and the relational model terms.

• I can identify all four types of keys.

• I can write DDL and DML commands with constraints.

• I can write SELECT queries with WHERE, IN, BETWEEN, LIKE, IS NULL, DISTINCT and ORDER BY.

• I can use aggregate functions, GROUP BY and HAVING.

• I can join two tables and predict the number of rows and columns.