Introduction to Structured Query Language (SQL)

Class 11 · Informatics Practices

4 ideas in Class 11 Informatics Practices, explained in plain words, with games and quizzes to practise.

Tables and Types

Part of: SQL Start

SQL asks databases; CREATE TABLE defines; CHAR, INT, FLOAT, DATE type columns; keys constrain.

Why it matters. Tables built typed. Keys guard.

Key words

SQL: : A language for asking and managing databases.
CREATE TABLE: : The command that defines a new table and its columns.
Data type: : The kind of values a column may hold, like INT or DATE.
Constraint: : A rule like unique or primary key guarding a column.

Try it in two minutes

Name table?
Type columns?
Add keys?

Try at home. Design marks table.

Changing Data

Part of: SQL Start

INSERT adds rows; UPDATE edits with WHERE; DELETE removes with WHERE; missing WHERE hits all.

Why it matters. Rows added. Edits targeted.

Formulas

UPDATE or DELETE without WHERE changes every row

Key words

INSERT: : The command adding new rows to a table.
UPDATE: : The command changing values in matching rows.
DELETE: : The command removing matching rows from a table.
WHERE clause: : The clause picking which rows a command affects.

Try it in two minutes

Add rows?
Target edits?
Delete carefully?

Try at home. Edit a list safely.

Select Queries

Part of: Queries and Totals

SELECT picks columns; WHERE filters; ORDER BY sorts; DISTINCT drops repeats.

Why it matters. Data fetched. Rows filtered.

Formulas

SELECT picks columns; WHERE keeps only matching rows

Key words

SELECT: : The command fetching chosen columns from a table.
Filter: : Keeping only rows that satisfy a condition.
ORDER BY: : Arranging result rows in rising or falling order.
DISTINCT: : Dropping repeated values from the result.

Try it in two minutes

Pick columns?
Filter rows?
Sort results?

Try at home. Query a list.

Aggregate Totals

Part of: Queries and Totals

COUNT, SUM, AVG, MAX, MIN summarise; GROUP BY splits groups; HAVING filters groups.

Why it matters. Totals computed. Groups compared.

Formulas

GROUP BY splits rows into groups; HAVING filters the groups

Key words

Aggregate function: : A function like COUNT or AVG summarising many rows.
COUNT: : Counting how many rows fall in a group.
GROUP BY: : Collecting rows with the same value before summarising.
HAVING: : A condition on groups, applied after grouping.

Try it in two minutes

Count or sum?
Group by what?
Filter groups?

Try at home. Total your scores.

What your child will practise

Aggregate Totals: Jungle Puzzle (Clue Puzzle)
Aggregate Totals: Jungle Quick Fire (Quick Fire)
Changing Data: Candy Puzzle (Clue Puzzle)
Select Queries: Space Quest (Flip and Match)
Tables and Types: Space Quick Fire (Quick Fire)

Quiz: Aggregate Totals (8 questions)
Quiz: Changing Data (8 questions)
Quiz: Select Queries (8 questions)
Quiz: Tables and Types (8 questions)

Introduction to Structured Query Language (SQL)

Class 11 · Informatics Practices

4 ideas in Class 11 Informatics Practices, explained in plain words, with games and quizzes to practise.

Tables and Types

Part of: SQL Start

SQL asks databases; CREATE TABLE defines; CHAR, INT, FLOAT, DATE type columns; keys constrain.

Why it matters. Tables built typed. Keys guard.

Key words

SQL: : A language for asking and managing databases.
CREATE TABLE: : The command that defines a new table and its columns.
Data type: : The kind of values a column may hold, like INT or DATE.
Constraint: : A rule like unique or primary key guarding a column.

Try it in two minutes

Name table?
Type columns?
Add keys?

Try at home. Design marks table.

Changing Data

Part of: SQL Start

INSERT adds rows; UPDATE edits with WHERE; DELETE removes with WHERE; missing WHERE hits all.

Why it matters. Rows added. Edits targeted.

Formulas

UPDATE or DELETE without WHERE changes every row

Key words

INSERT: : The command adding new rows to a table.
UPDATE: : The command changing values in matching rows.
DELETE: : The command removing matching rows from a table.
WHERE clause: : The clause picking which rows a command affects.

Try it in two minutes

Add rows?
Target edits?
Delete carefully?

Try at home. Edit a list safely.

Select Queries

Part of: Queries and Totals

SELECT picks columns; WHERE filters; ORDER BY sorts; DISTINCT drops repeats.

Why it matters. Data fetched. Rows filtered.

Formulas

SELECT picks columns; WHERE keeps only matching rows

Key words

SELECT: : The command fetching chosen columns from a table.
Filter: : Keeping only rows that satisfy a condition.
ORDER BY: : Arranging result rows in rising or falling order.
DISTINCT: : Dropping repeated values from the result.

Try it in two minutes

Pick columns?
Filter rows?
Sort results?

Try at home. Query a list.

Aggregate Totals

Part of: Queries and Totals

COUNT, SUM, AVG, MAX, MIN summarise; GROUP BY splits groups; HAVING filters groups.

Why it matters. Totals computed. Groups compared.

Formulas

GROUP BY splits rows into groups; HAVING filters the groups

Key words

Aggregate function: : A function like COUNT or AVG summarising many rows.
COUNT: : Counting how many rows fall in a group.
GROUP BY: : Collecting rows with the same value before summarising.
HAVING: : A condition on groups, applied after grouping.

Try it in two minutes

Count or sum?
Group by what?
Filter groups?

Try at home. Total your scores.

What your child will practise

Aggregate Totals: Jungle Puzzle (Clue Puzzle)
Aggregate Totals: Jungle Quick Fire (Quick Fire)
Changing Data: Candy Puzzle (Clue Puzzle)
Select Queries: Space Quest (Flip and Match)
Tables and Types: Space Quick Fire (Quick Fire)

Quiz: Aggregate Totals (8 questions)
Quiz: Changing Data (8 questions)
Quiz: Select Queries (8 questions)
Quiz: Tables and Types (8 questions)