Data Definition Language (DDL) and Data Manipulation Language (DML)

Computer Science AS · Databases. The idea in plain words, the rules to remember, the mistake to avoid and a worked example, written and checked by Mega Lecture.

Test this topic free All Computer Science AS topics

The idea

SQL is the industry standard language for relational databases. It has two parts. The Data Definition Language (DDL) creates and changes the structure: databases, tables, fields and keys. The Data Manipulation Language (DML) works with the data inside the tables: queries, and adding, changing and deleting records.

A query is built in a fixed order: SELECT the fields, FROM the table, WHERE a condition is true, GROUP BY a field, ORDER BY a field. Text and dates go in quotes; numbers do not.

The functions SUM, COUNT and AVG give one value for a set of records. With GROUP BY they give one value for each group.

Rules to remember

DDL: CREATE DATABASE, CREATE TABLE, ALTER TABLE, PRIMARY KEY, FOREIGN KEY ... REFERENCES.
DML: SELECT, INSERT INTO, DELETE FROM, UPDATE.
Data types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME.
Keys: PRIMARY KEY (MemberID) and FOREIGN KEY (MemberID) REFERENCES MEMBER(MemberID). New field: ALTER TABLE MEMBER ADD Phone VARCHAR(15);
Join: SELECT ... FROM A INNER JOIN B ON A.Key = B.Key returns only records that match in both tables.
ORDER BY sorts ascending unless DESC is written.
INSERT INTO T (F1, F2) VALUES (v1, v2); UPDATE T SET F1 = v1 WHERE condition; DELETE FROM T WHERE condition;

Common mistake

Leaving out WHERE in UPDATE or DELETE changes or removes every record in the table. Also, SELECT is DML, not DDL, although it does not change any data.

Worked example

STOCK has five records with (Category, Qty): (Pen, 12), (Paper, 40), (Pen, 8), (Ink, 5), (Paper, 10). What does this query return?
SELECT Category, SUM(Qty) FROM STOCK GROUP BY Category;

GROUP BY Category puts the records into three groups: Pen, Paper and Ink.
SUM(Qty) is worked out for each group. Pen: 12 + 8 = 20. Paper: 40 + 10 = 50. Ink: 5.
The query returns one row for each group, not one row for each record.

Answer: Three rows: Pen 20, Paper 50, Ink 5.

Ask about this

Type what you are stuck on. The helper finds the explanation, worked examples and the notes to read, free and instant.

Keep going

← Database Management Systems (DBMS)Computational thinking skills →Take a Computer Science AS test