I85PREP
⌘ K
RBSE SS Informatics Practices study materials
RBSE SS Informatics Practices study resources

RBSE SS Informatics Practices: Database Query using SQL

RBSE SS Informatics Practices · Work through your exam, one topic at a time.

All guide sections

Database Query using SQL

What this domain tests

This domain tests command classification, table creation, and the SELECT family: filtering rows, sorting, aggregating, grouping and joining tables.

Command families and core syntax

  • DDL changes structure: CREATE, ALTER, DROP, TRUNCATE. DML changes data: INSERT, UPDATE, DELETE; SELECT is the query command.
  • SELECT col1, col2 FROM table WHERE condition ORDER BY col DESC; Filtering operators include BETWEEN, IN, LIKE (% for many characters, _ for one) and IS NULL.
  • Aggregates: COUNT(*), COUNT(col) (ignores NULLs), SUM, AVG, MIN, MAX.
  • GROUP BY forms groups; HAVING filters those groups after aggregation; WHERE filters individual rows before grouping.
  • Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN with ON table1.key = table2.key. Two tables joined with no condition give a Cartesian product.
  • Functions: UPPER, LOWER, LENGTH, CONCAT, ROUND, ABS, YEAR, IFNULL.

Worked example

SELECT dept, AVG(salary) FROM emp WHERE salary > 10000 GROUP BY dept HAVING COUNT(*) > 2 ORDER BY AVG(salary) DESC; Reasoning, in execution order: WHERE first removes employees earning 10000 or less; remaining rows are grouped by department; HAVING keeps only departments with more than two such employees; finally the surviving groups are sorted by average salary, highest first.

Traps that cost marks

Using HAVING where WHERE belongs, or the reverse; assuming COUNT(col) counts NULLs; writing = NULL instead of IS NULL; selecting a non-aggregated column that is absent from GROUP BY; confusing DELETE (removes rows), TRUNCATE (empties the table) and DROP (removes the table); omitting the join condition and accidentally producing every row pair.