A3.3.1 The languages inside SQL | IB Computer Science

Loading…

The four groups#

creates and changes the structure: CREATE a table, view or index; ALTER a table (add or drop a column); DROP an object; other systems also have TRUNCATE (empty a table) and RENAME.
works with the data itself: SELECT to retrieve, INSERT to add, UPDATE to change and DELETE to remove records.
controls access: GRANT gives a user a privilege (such as reading one table) and REVOKE takes it away. It is how the database stays secure.
manages transactions: BEGIN (or START TRANSACTION), COMMIT to make changes permanent, ROLLBACK to undo them and SAVEPOINT to mark a point to roll back to (A3.3.6).

A useful way to remember it: DDL changes what the shelves look like, DML changes what is on the shelves, DCL decides who may enter the shop, and TCL lets you undo a whole tidy-up.

Figure 1Try one of eachInteractive

SQL (you can edit it)

Tables

Author

Author
AuthorIDPK	Name	Country
1	Maya Okafor	Nigeria
2	Liam Chen	China
3	Sofia Rossi	Italy
4	Arun Patel	India
5	Noor Haddad	Egypt

5 rows. AuthorID INT, Name TEXT(40), Country TEXT(20).

The sandbox has the bookshop database. Run an example from each group. DDL and DML change the sandbox data (Reset data puts it back); the transaction example undoes itself.

Figure 2Which language?Interactive

ALTER TABLE Customer ADD Email VARCHAR(40);
SELECT Title FROM Book WHERE Genre = 'Fiction';
GRANT SELECT ON Book TO shop_assistant;
ROLLBACK;
INSERT INTO Customer VALUES (6, 'Fay', 'Seoul');
DROP TABLE Review;

0 of 6 matched

Check yourself

The languages of SQL

Quick questions, made fresh every time and marked here. Do a few until they feel easy.

Exam practice

Paper 1-style questions

4 questions · 13 marks

Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.

1.Distinguish between the data definition language (DDL) and the data manipulation language (DML) in SQL.
[4]
Distinguish: Make the differences between two things clear.
Your answer to question 1
2.Identify the type of SQL language (DDL, DML, DCL or TCL) that each of these belongs to: (a) CREATE TABLE, (b) UPDATE, (c) GRANT, (d) COMMIT.
[4]
Identify: Pick out the answer from the options or the context.
Your answer to question 2
3.Outline the purpose of the data control language.
[2]
Outline: A brief account: the main points, without much detail.
Your answer to question 3
4.Explain the difference between DELETE FROM Customer and DROP TABLE Customer.
[3]
Explain: Give reasons: say what happens AND why, or how one thing causes another.
Your answer to question 4

In a nutshell

SQL (structured query language) is the standard language for relational databases. It has parts for different jobs.
DDL (data definition): CREATE, ALTER, DROP shape the structure. DML (data manipulation): SELECT, INSERT, UPDATE, DELETE work with the data.
DCL (data control): GRANT and REVOKE control who may do what. TCL (transaction control): BEGIN, COMMIT, ROLLBACK, SAVEPOINT group changes into transactions.
DELETE removes rows but keeps the table; DROP removes the table itself.
Some books count only DDL and DML as the two main languages and treat DCL and TCL as extras; learn all four.