Structured Query Language

Section A (compulsory), B1 and B2. Covers SQL and MySQL basics; DDL commands; DML and queries; Math and text functions; Date functions; Aggregate functions and grouping; Operations on relations.

Computer Science/Informatics Practices35 questions7 topics
Practise this topic Take topic test

About this unit

This unit teaches SQL (Structured Query Language), the standard language for working with relational databases, using MySQL as the example RDBMS. You will create and modify tables, insert and change data, and write queries that filter, sort, group and combine data. Built-in functions for numbers, text and dates, and aggregate functions such as SUM and COUNT, are a large part of the unit.

Key ideas

  • SQL commands fall into groups: DDL (structure), DML (data changes) and DQL (queries).
  • Data types such as INT, FLOAT, CHAR, VARCHAR and DATE, and constraints such as PRIMARY KEY, NOT NULL, UNIQUE and DEFAULT.
  • SELECT … FROM … WHERE with operators such as BETWEEN, IN, LIKE and IS NULL.
  • Single-row functions (math, text, date) return one value per row.
  • Aggregate functions work on groups of rows, with GROUP BY and HAVING.
  • Union, intersection, minus, Cartesian product and joins combine tables.

How to prepare

Practise writing queries on a small sample table and predicting their output by hand. Memorise the exact behaviour of functions such as ROUND, MOD, SUBSTRING, INSTR and COUNT(*) versus COUNT(column). Many questions show a query and ask for its output or the number of rows returned.

Topics in this chapter

SQL and MySQL basics

Advantages of SQL; DDL, DQL and DML; data types and constraints.

5 questions

DDL commands

CREATE TABLE, DROP TABLE and ALTER TABLE.

5 questions

DML and queries

INSERT, UPDATE, DELETE and SELECT … FROM … WHERE.

5 questions

Math and text functions

POWER, ROUND, MOD, UPPER, LOWER, SUBSTRING, LENGTH, LEFT, RIGHT, INSTR and TRIM.

5 questions

Date functions

NOW, DATE, MONTH, MONTHNAME, YEAR, DAY and DAYNAME.

5 questions

Aggregate functions and grouping

MAX, MIN, AVG, SUM and COUNT with GROUP BY, HAVING and ORDER BY.

5 questions

Operations on relations

Union, intersection, minus, Cartesian product and JOIN.

5 questions

Practice questions

Which of these is a DDL command?Easy
Which constraint supplies a value for a column automatically when none is given during INSERT?Easy
A column is declared CHAR(10) and the value 'RAVI' is stored. How many characters of storage does the value occupy?Medium
Why is SQL described as a non-procedural language?Medium
A column Email has a UNIQUE constraint but no NOT NULL constraint. Which statement is correct?Hard

Practise all 35 questions as a set

Practice this topic