IB Computer Science Topic A3.3.1: Differences Between Data Language Types in SQL Notes

SQL Splits Into Sublanguages Grouped by the Job Each Command Does

1. Structured Query Language (SQL) is the language used to work with a relational database, and its commands are grouped into four sublanguages.
2. Data Definition Language (DDL) builds and changes the structure of the database: the tables, the columns and the constraints.
3. Data Manipulation Language (DML) works with the rows stored inside that structure.
4. Data Control Language (DCL) grants and revokes the permissions that decide which users may run which commands.
5. Transaction Control Language (TCL) groups several statements so that they all succeed together or none of them takes effect.

DDL Commands Change the Shape of the Database, Not Its Contents

1. CREATE makes a new database object such as a table, an index or a schema.
   1. CREATE TABLE Customer (CustomerID INT PRIMARY KEY, CustomerEmail VARCHAR(255));
2. ALTER changes the structure of an object that already exists, for example by adding a column.
   1. ALTER TABLE Customer ADD COLUMN CustomerAddress VARCHAR(255);
3. DROP removes the object itself, so the table and every row in it are gone.
   1. DROP TABLE Customer;
4. TRUNCATE empties a table of all its rows but leaves the table definition in place.
   1. TRUNCATE TABLE Customer;
5. RENAME changes the name of a database object without touching the data it holds.
   1. RENAME TABLE Customer TO Client;

TRUNCATE Versus DROP: Emptying a Table or Removing It Entirely

1. After TRUNCATE TABLE Customer the table still exists and you can insert new rows into it straight away.
2. After DROP TABLE Customer the table definition is gone, so any query naming it now fails.
3. Any foreign key in another table that pointed at the dropped table is broken as well, so DROP has consequences beyond the table you named.

DML Commands Move Rows in and out of an Existing Structure

1. SELECT reads rows out of one or more tables and returns them as a result set.
   1. Building a SELECT across two tables with joins, filters and ordering is covered in A3.3.2.
2. INSERT adds new rows, UPDATE changes values in rows that already exist, and DELETE removes rows.
   1. The syntax for these three, and what happens when you forget the WHERE clause, is covered in A3.3.3.
3. All four act on data only, so none of them can add a column or change a data type.

DDL Versus DML: The Comparison an Exam Answer Needs

Picking the Right Sublanguage for a Given Task

1. If the task changes what a table can hold, such as adding a phone number field, it is a DDL job.
2. If the task changes what is stored in a field that already exists, such as correcting one customer's email, it is a DML job.
3. Reading the task for the words structure or data is usually enough to place it correctly.