IB COMPUTER SCIENCE | A3.3.1 Data Language Types Within SQL

Lesson objective

Outline the differences between DDL and DML, distinguishing statements that define database structures from statements that access or manipulate records.

Learn

A3.3.1 | DATA LANGUAGE TYPES WITHIN SQL

01 | ONE LANGUAGE, DIFFERENT JOBS

The school wants a database for its clubs. Before it can store the Robotics club, someone must define what a club record contains. Later, an administrator adds Robotics, changes its capacity and asks which clubs have space. These are different jobs, even though they can all be expressed in SQL.

SQL means Structured Query Language. Despite the word “query”, SQL can define structures as well as retrieve and change data. A DBMS, or database management system, receives statements and carries out permitted operations.

The two language types required here are data definition language (DDL) and data manipulation language (DML). They are categories of SQL statements, not separate database products.

The command word outline asks for the main features. By the end, you should be able to state each category’s purpose, give suitable commands and explain the difference with an example.

02 | THE STRUCTURE AND ITS CONTENTS

LayerIn the Club exampleQuestion it answers
Structure / schemaClubID, ClubName and Capacity, with types and constraintsWhat can a Club record contain, and what rules apply?
Stored data1, Robotics, 20Which particular club facts are recorded now?

The schema describes the database’s structure and rules. The data consists of particular stored values. Changing the design to include a Room column is different from changing Robotics’ capacity from 20 to 24.

Imagine a blank registration form. Designing its named boxes is like defining the structure. Completing one copy with a particular club’s details is like adding a record. The analogy is useful, but a real DBMS also enforces rules and manages concurrent access.

An empty table can still have a full definition. Zero records does not mean that the table does not exist. This distinction will help you understand CREATE, DELETE and DROP.

03 | DDL DEFINES DATABASE STRUCTURES

Data definition language defines, changes or removes database structures, also called schema objects. For tables, its central commands include CREATE TABLE, ALTER TABLE and DROP TABLE.

DDL can specify column names, data types, primary keys, foreign keys and other constraints. It establishes what the DBMS should accept in that structure.

Do not memorise “DDL never affects data”. Removing a table can remove its data, and structural changes can affect existing records or fail because existing data violates a proposed rule. The category describes the operation’s primary purpose.

04 | CREATE TABLE BUILDS THE CONTAINER

CREATE TABLE Club (
    ClubID INTEGER PRIMARY KEY,
    ClubName VARCHAR(60) NOT NULL,
    Capacity INTEGER NOT NULL CHECK (Capacity > 0)
);

Read the statement as: create a table named Club with these three columns and these rules. ClubID is the primary key. ClubName is required text. Capacity is a required whole number with a positive-value check.

After successful execution, the table exists but contains no club records. The definition did not add Robotics or any other club. That requires a separate data operation.

INTEGER and VARCHAR(60) are data types inside a DDL statement. They are not themselves SQL language categories. VARCHAR indicates a variable-length character value up to the stated limit in DBMSs that enforce that declaration. Exact type and constraint behaviour varies between systems.

05 | DML WORKS WITH THE RECORDS

Data manipulation language accesses or manipulates data in existing database structures. Its core modification commands are INSERT, UPDATE and DELETE. These add records, change stored values and remove records.

Retrieval with SELECT is commonly included in the broad DML category. Some courses and references label queries separately as data query language (DQL). This lesson uses DML including retrieval, while making clear that an ordinary SELECT query reads data rather than changing the stored rows.

The important distinction is the operation: defining the table’s structure versus working with its records. If a reference separates DQL, that does not turn SELECT into DDL.

06 | INSERT ADDS A RECORD

INSERT INTO Club (ClubID, ClubName, Capacity)
VALUES (1, 'Robotics', 20);
ClubIDClubNameCapacity
1Robotics20

The column list names the destination fields. The VALUES list supplies this new record’s matching values in the same order. The single quotation marks identify Robotics as a text value; the numeric values are not quoted here.

This is DML because it adds a particular club to the existing structure. It does not create a new column or a new table.

The definition still matters: attempting to insert a duplicate primary key or a non-positive capacity should be rejected by a DBMS enforcing these constraints. DML does not bypass the rules established through the schema.

07 | SELECT READS WITHOUT REDEFINING

SELECT ClubName, Capacity
FROM Club
WHERE Capacity >= 15;

SELECT names the columns to return, FROM names the source table and WHERE supplies a condition. The result contains the names and capacities of clubs whose capacity is at least 15.

With only the sample Robotics row, the result is Robotics and 20. The query does not change the recorded capacity, remove other clubs or create a permanent replacement table.

A result set is the returned collection of rows. Its displayed columns can differ from the stored table without altering that table’s definition. Later lessons build on this with filtering, joins and ordering.

08 | UPDATE CHANGES A VALUE, NOT A COLUMN DEFINITION

UPDATE Club
SET Capacity = 24
WHERE ClubID = 1;
BeforeAfter
ClubID 1, Robotics, Capacity 20ClubID 1, Robotics, Capacity 24

UPDATE Club identifies the table. SET Capacity = 24 supplies the new field value. WHERE ClubID = 1 selects the record to change. The column remains named Capacity with the same type and constraints.

This is DML. Although the everyday word “update” can mean any change, the SQL UPDATE statement specifically changes data values in rows.

Without a WHERE condition, this UPDATE would attempt to set the capacity of every row to 24. That is not a syntax error: it could be a valid but unintended operation. A sensible workflow checks the intended target records first.

09 | ALTER TABLE CHANGES THE DEFINITION

ALTER TABLE Club
ADD Room VARCHAR(20);
Before columnsAfter columns
ClubID, ClubName, CapacityClubID, ClubName, Capacity, Room

ALTER TABLE changes an existing table’s structure. Here it adds a Room column so that a room value can be stored for each club. That is DDL, even when the table already contains data.

In this example the new column has no NOT NULL rule or explicit default. Existing rows initially have NULL for Room under the demonstrated semantics. NULL represents an absent or unknown value, not the literal text “NULL”.

Assigning a room to Robotics then requires DML, such as UPDATE Club SET Room = 'Lab 2' WHERE ClubID = 1;. The two statements solve separate problems: provide a place for the fact, then record the fact.

ALTER syntax and supported operations vary by DBMS. Focus first on the structural purpose rather than assuming every database accepts every form unchanged.

10 | DELETE AND DROP ARE DIFFERENT

DELETE FROM Club
WHERE ClubID = 1;

DELETE removes the matching record. After deleting ClubID 1 from this one-record example, Club still exists with its columns and constraints, but has zero rows. You could insert a new club into it without recreating the table.

DELETE FROM Club;

DELETE without a WHERE condition removes all rows, subject to applicable constraints and other DBMS behaviour. It still leaves the table definition in place. It is DML.

DROP TABLE Club;

DROP TABLE removes the table object and its data if the operation is allowed. It is DDL. A later query referring to Club will fail until an appropriate table is created again. Dependencies, privileges and DBMS options can prevent the drop.

Do not rely on a universal rule that one command can always be rolled back and another never can. Transaction handling and DDL commit behaviour vary by DBMS and context. The reliable distinction here is rows removed versus table object removed.

11 | FOLLOW STRUCTURE AND ROWS TOGETHER

Step through the school example and watch two separate displays: the schema and the records. Notice that a SELECT returns a result while leaving both unchanged.

This is a fixed in-page simulation of the shown statements, not a live SQL database. It does not execute arbitrary SQL or model permissions, concurrent users, transactions or dependencies.

TABLE DEFINITION

STORED RECORDS

12 | CLASSIFY THE PURPOSE, NOT THE ENGLISH WORD

RequirementSQL operationCategory and reason
Create a new Loans tableCREATE TABLEDDL: defines a new structure.
Add one loan recordINSERT INTODML: adds data to a structure.
Add a ReturnDate fieldALTER TABLEDDL: changes which columns exist.
Record that a loan was returnedUPDATEDML: changes values in a record.
List outstanding loansSELECTRetrieval, included in broad DML here.
Remove a mistaken loan recordDELETEDML: removes row data.
Remove an obsolete tableDROP TABLEDDL: removes the schema object.

“Add” is not enough to classify an operation. Add a column means a structural change; add a record means new data. Likewise, “change” can mean changing a column’s definition or changing a value inside it. State what is being changed.

13 | TRY THE LANGUAGE-TYPE CHECK

For each statement, select the category and reason. SELECT uses the broad DML convention explained earlier. Your first checked response counts; you can restart to practise again.

 

14 | OTHER LABELS YOU MAY MEET

The required comparison for A3.3.1 is DDL and DML. Broader SQL resources may also discuss DQL for retrieval, data control language (DCL) for permissions and transaction control language (TCL) for transaction boundaries.

For example, GRANT and REVOKE concern privileges; COMMIT and ROLLBACK concern completing or undoing transaction changes under the DBMS’s rules. These labels provide context, not extra categories you need to force every example into here.

References differ in their taxonomies: some group permission statements with DDL, and some group SELECT with DML rather than separate DQL. Explain the statement’s purpose and state the convention when relevant. The structure-versus-records distinction remains useful.

15 | DATA INTEGRITY CONNECTS BOTH CATEGORIES

DDL can declare the rule CHECK (Capacity > 0). DML supplies actual Capacity values. The DBMS checks applicable constraints during an insertion or update, so a proposed value of zero is rejected in a system enforcing this rule.

A schema change can also affect existing data. Adding a mandatory column to a populated table may require a suitable default or migration process. Adding a uniqueness rule can fail if duplicates already exist.

This is why “DDL is for developers and DML is for users” is not a definition. Administrators, developers and applications may issue either category if authorised. Classify the operation by what it does, not by the person or interface that sends it.

16 | WRITE A PRECISE OUTLINE

A strong short answer might say: “DDL defines and changes database structures, for example CREATE TABLE creates columns and constraints. DML accesses and manipulates data within those structures, for example INSERT adds a record and UPDATE changes stored field values. ALTER TABLE adds a column, whereas UPDATE changes values in an existing column.”

If discussing SELECT, add that it retrieves data and is included in broad DML, although some references use DQL. Avoid implying that a query must change stored data.

For “outline”, give the main distinction and relevant examples. You do not need a long evaluation of competing database products. In the next lesson, use SELECT to construct queries involving two tables.

Further reading: Microsoft SQL query and DML reference.

MATCH THE SQL TERM TO ITS PURPOSE

Terminology

Terminology

SQL

Structured Query Language: a language for defining, accessing and manipulating relational database structures and data.

DBMS

Database management system: software managing databases and their permitted operations.

DDL

Data definition language: statements defining, changing or removing database structures.

DML

Data manipulation language: statements accessing or manipulating data in existing structures; broad usage includes SELECT.

DQL

Data query language: a label some references use separately for retrieval statements such as SELECT.

Schema

The defined structure of a database, including objects, columns and rules.

Table

A database structure containing rows and columns.

Column / field

A named attribute with a defined type and applicable rules.

Row / record

A set of field values representing one stored instance.

Data type

A declaration of the kind of value a column can represent.

Constraint

A database rule restricting permitted data or relationships.

CREATE TABLE

A DDL statement that defines a new table.

ALTER TABLE

A DDL statement that changes a table’s definition.

DROP TABLE

A DDL statement that removes a table object if permitted.

INSERT INTO

A DML statement adding records to a table.

UPDATE

A DML statement changing field values in matching records.

DELETE

A DML statement removing matching records while retaining the table definition.

SELECT

A statement retrieving a result; commonly broad DML, sometimes classified as DQL.

FROM

A clause identifying data sources in a query.

WHERE

A clause restricting affected or returned rows using a condition.

SET

Part of UPDATE specifying new column values.

VALUES

A clause supplying values for an INSERT operation.

Result set

The rows returned by a query, distinct from altering the stored table.

NULL

An absent or unknown value, not the literal word NULL.

Primary key

The selected unique, non-null identifier for each row.

NOT NULL

A constraint forbidding NULL in a column.

CHECK

A declared condition constraining data under the DBMS’s rules.

DCL

A label often used for statements controlling access privileges.

TCL

A label used for transaction-control statements such as COMMIT and ROLLBACK.

SQL dialect

A DBMS-specific variation in SQL syntax, features or behaviour.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which primarily change structure?
2. Which modify stored records?
3. Which are true after CREATE TABLE alone?
4. Which explain SELECT accurately?
5. Which distinguish ALTER and UPDATE?
6. Which distinguish DELETE and DROP?
7. Which describe WHERE?
8. Which clarify category versus type?
9. Which describe integrity accurately?
10. Which make a strong outline?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Outline the difference between DDL and DML.

2. Describe the state after the Club CREATE TABLE statement runs.

3. Explain why adding a Room column and entering a room value use different categories.

4. Outline the purpose of INSERT, UPDATE and DELETE.

5. Distinguish DELETE FROM Club from DROP TABLE Club.

6. Explain what SELECT ClubName, Capacity FROM Club returns.

7. Explain the role of WHERE in an UPDATE.

8. Distinguish INTEGER from DDL.

9. Explain how DDL and DML interact to preserve data integrity.

10. Outline why DDL does not mean “never affects data”.

11. Outline the convention used for SELECT in this lesson.

12. Outline a suitable SQL workflow for adding clubs and recording their rooms.

OUTLINE IT | A SCHOOL CLUB DATABASE

A school creates a Club table, adds Robotics, adds a Room column, records Lab 2 as its room, lists club names, removes one mistaken record and later removes an obsolete table. Outline the category and a suitable SQL command for each operation. Make the structure-versus-record distinction explicit and state the SELECT convention.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.3.1 Data Language Types Within SQL is coming soon. Complete the matching task and written questions in your exercise book.