Class 12 Structured Query Language (SQL)

SQL’s DDL and DML commands, data types and constraints, SELECT with WHERE, BETWEEN, IN, LIKE, DISTINCT and ORDER BY, and UPDATE and DELETE.

Key ideas

DDL commands define the structure (CREATE, ALTER, DROP); DML commands work on the data (INSERT, SELECT, UPDATE, DELETE).

Types: CHAR(n) fixed length, VARCHAR(n) varying, INT, FLOAT, DATE; constraints: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT.

WHERE picks the rows; ORDER BY sorts them, ascending unless DESC is given.

BETWEEN a AND b includes both ends; IN (…) matches any value in the list; NOT turns a condition round.

A comparison with NULL is never true, so test for it with IS NULL or IS NOT NULL.

DISTINCT removes repeats; UPDATE and DELETE act on every row if there is no WHERE.

Remember

The order of a query

SELECT columns FROM table WHERE condition ORDER BY column

BETWEEN

Salary BETWEEN 30000 AND 50000 means Salary >= 30000 AND Salary <= 50000

LIKE (% any run, _ one character)

'A%' begins with A; '%a' ends with a; '_a%' has a second letter a

NULL

WHERE Bonus IS NULL, never Bonus = NULL

ALTER TABLE … ADD

a new column, in which the existing rows get NULL

DROP or DELETE

DROP TABLE removes the table itself (DDL); DELETE FROM removes rows and keeps the table (DML)

Worked example

Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Priya | IT | 73000 | 2000
E2 | Arjun | IT | 31000 | 9500
E3 | Vikram | HR | 84000 | 2500
E4 | Meera | IT | 69000 | NULL
E5 | Rohan | Accounts | 67000 | 2000
E6 | Zoya | HR | 60000 | 10000
E7 | Dev | Accounts | 45000 | 10000

UPDATE EMPLOYEE SET Salary = Salary + 5000 WHERE Dept NOT IN ('Accounts', 'IT') AND Salary BETWEEN 31000 AND 73000;
(a) How many rows does this statement change? (b) What does SELECT SUM(Salary) FROM EMPLOYEE; return after it? (c) Is it a DDL or DML command? Why?

Working: rows where Dept NOT IN ('Accounts', 'IT') AND Salary BETWEEN 31000 AND 73000

Answer: (a) 1; (b) 434000; (c) DML: it changes the data in the table, not its structure.

Structured Query Language (SQL)

Key ideas

Remember

Watch out

Worked example