Worksheets › Class 11 › Introduction to Structured Query Language (SQL) › Cheat sheet

Class 11 Introduction to Structured Query Language (SQL): cheat sheet

CBSE Informatics Practices. SQL on one table: command and data types, SELECT with WHERE, BETWEEN, IN, LIKE, NULL, DISTINCT and ORDER BY.

Open the printable cheat sheet Practice worksheet

Key ideas

DDL: CREATE, ALTER, DROP (structure); DML: INSERT, UPDATE, DELETE (data); DQL: SELECT (queries).
Types: INT, FLOAT (decimals), CHAR(n) fixed-length text, VARCHAR(n) varying text, DATE ('2009-04-12').
WHERE keeps rows whose condition is TRUE; BETWEEN includes both ends; IN matches any value in its list.
In LIKE, % is any run of characters (even none) and _ exactly one; text comparisons and LIKE ignore case.
NULL means unknown: a comparison with it, even = NULL, is never TRUE, so test with IS NULL or IS NOT NULL.
DISTINCT drops repeats; ORDER BY sorts ascending (NULL first), or with DESC descending (NULL last).
UPDATE and DELETE affect only rows matching WHERE (all rows without it); DROP TABLE removes the table.

Remember

SELECT, in order	SELECT [DISTINCT] c FROM t WHERE … ORDER BY c [DESC];
BETWEEN	Marks BETWEEN 40 AND 60 is Marks >= 40 AND Marks <= 60
LIKE	'K%' starts with K; '%a' ends with a; '_a%' has a 2nd; '____' 4 letters
NULL with AND, OR	FALSE AND unknown is FALSE; TRUE OR unknown is TRUE; otherwise unknown, so the row is left out
A result’s size	degree = columns shown; cardinality = rows kept

Watch out

Writing = NULL instead of IS NULL: it never matches anything.
Running UPDATE or DELETE without WHERE, which changes every row.
Forgetting where NULL goes in ORDER BY: first when ascending, last with DESC.

Worked example

Table: ITEM ItemCode | IName | Category | Price | Qty I207 | Water Bottle | Daily Use | 110 | 30 I209 | Umbrella | Daily Use | 230 | 45 I210 | Pencil | Stationery | 10 | 50 I216 | Marker | Stationery | 25 | 25 I221 | Eraser | Stationery | 10 | 35 I232 | Lunch Box | Daily Use | 155 | 40 I239 | Gel Pen | Stationery | 30 | 15 I240 | Ruler | Stationery | 20 | 50 I255 | Glue Stick | Office | 45 | 35 SELECT IName FROM ITEM WHERE Category IN ('Daily Use') ORDER BY IName; Write the output, names separated by commas, in order.

Working: IN keeps a row whose value is any one in the list; ORDER BY Name sorts alphabetically

Answer: Lunch Box, Umbrella, Water Bottle

Practise Introduction to Structured Query Language (SQL)

Revise with this page, then practise: every Introduction to Structured Query Language (SQL) sheet in the app is new, with an answer key and working.

Coming soon for Android and iPhone.