|
NEXT TOPIC >
CONSTRUCT QUERIES |
DATABASE FUNDAMENTALS | DATABASE PROGRAMMING
DESIGNED FOR IB EXAMINATIONS
OBJECTIVES
A3.3.1 Outline the differences between data language types within SQL.
• Data language types must include data definition language (DDL) and data manipulation language (DML)
• SQL statements to define data structures or to manipulate data
A3.3.1 Outline the differences between data language types within SQL.
• Data language types must include data definition language (DDL) and data manipulation language (DML)
• SQL statements to define data structures or to manipulate data
Structured Query Language (SQL) is used to interact with relational databases. SQL commands are grouped into different data language types based on their purpose. Two of the most important categories are Data Definition Language (DDL) and Data Manipulation Language (DML). Understanding the distinction between these two is essential for effective database design and use.
SECTION 1 | DATA DEFINITION LANGUAGE
Data Definition Language (DDL) is concerned with defining, modifying, and removing the structure of a database. DDL statements describe what the database looks like, rather than the data stored within it.
Key characteristics of DDL:
Common DDL statements include:
Example use case:
Defining a new table to store student records or adding a new column to an existing table.
Key characteristics of DDL:
- Operates on database objects such as tables, fields (columns), indexes, and schemas.
- Changes made by DDL affect the database structure.
- DDL commands are usually auto-committed, meaning the changes take effect immediately.
Common DDL statements include:
- CREATE – used to create new database objects (e.g. tables, databases).
- ALTER – used to modify existing database structures.
- DROP – used to delete database objects entirely.
- TRUNCATE – used to remove all records from a table while keeping its structure.
Example use case:
Defining a new table to store student records or adding a new column to an existing table.
SECTION 2 | DATA MANIPULATION LANGUAGE
Data Manipulation Language (DML) is used to work with the data stored inside database tables. DML statements allow users to insert, update, retrieve, and delete records.
Key characteristics of DML:
Common DML statements include:
Adding a new student record, updating a student’s grade, or retrieving a list of students from a table.
Summary of the Difference
This distinction is fundamental in SQL, as database designers primarily use DDL, while database users and applications frequently rely on DML.
Key characteristics of DML:
- Operates on records (rows) within tables.
- Does not change the database structure, only the data.
- DML statements can usually be rolled back if a transaction system is in use.
Common DML statements include:
- INSERT – adds new records to a table.
- SELECT – retrieves data from one or more tables.
- UPDATE – modifies existing records.
- DELETE – removes records from a table.
Adding a new student record, updating a student’s grade, or retrieving a list of students from a table.
Summary of the Difference
- DDL defines the structure of the database (tables, fields, relationships).
- DML manipulates the data within that structure.
- DDL answers the question: How is the database organised?
- DML answers the question: What data is stored, changed, or retrieved?
This distinction is fundamental in SQL, as database designers primarily use DDL, while database users and applications frequently rely on DML.
Which statement correctly describes the difference between DDL and DML in SQL?
DDL is used to retrieve data, while DML is used to create tables.
DDL defines and modifies database structures, while DML manipulates the data stored in tables.
DDL is only used for deleting data, while DML is used for inserting data.
DDL and DML both only affect the structure of a database.
SECTION 1 | DATA MANIPULATION LANGUAGE
Having identified Data Definition Language (DDL) and Data Manipulation Language (DML) as the two required SQL data language types, this section focuses on how these categories are reflected in the actual SQL statements used in practice. The emphasis here is not on re-defining DDL and DML, but on clarifying the role of SQL statements within each category.
Defining Data Structures with SQLSQL statements that define data structures are used during the design and setup phase of a database. These statements establish the framework that determines:
At this stage, the concern is not what data is stored, but how data will be stored. For example, when a table is created, decisions are made about column names, data types, and keys. These decisions directly affect how efficiently and accurately data can later be manipulated.
This type of structural control is provided by DDL statements, which permanently change the database schema.
Manipulating Data with SQLOnce the data structures exist, SQL statements are used to interact with the data itself. These statements support the day-to-day operation of a database by allowing users and applications to:
At this stage, the database structure remains fixed, and only the contents of the tables change. These operations are typically frequent and may be reversible when transactions are used.
This form of interaction is provided by DML statements, which operate on the data held within the predefined structure.
Why This Distinction MattersUnderstanding whether an SQL statement defines structure or manipulates data helps to:
SQL statements fall into DDL or DML not just by name, but by whether they shape the database itself or work within that shape. This practical distinction underpins safe and effective database use.
Defining Data Structures with SQLSQL statements that define data structures are used during the design and setup phase of a database. These statements establish the framework that determines:
- What tables exist
- What fields they contain
- How data is constrained and related
At this stage, the concern is not what data is stored, but how data will be stored. For example, when a table is created, decisions are made about column names, data types, and keys. These decisions directly affect how efficiently and accurately data can later be manipulated.
This type of structural control is provided by DDL statements, which permanently change the database schema.
Manipulating Data with SQLOnce the data structures exist, SQL statements are used to interact with the data itself. These statements support the day-to-day operation of a database by allowing users and applications to:
- Add new records
- Find specific information
- Modify existing values
- Remove unwanted data
At this stage, the database structure remains fixed, and only the contents of the tables change. These operations are typically frequent and may be reversible when transactions are used.
This form of interaction is provided by DML statements, which operate on the data held within the predefined structure.
Why This Distinction MattersUnderstanding whether an SQL statement defines structure or manipulates data helps to:
- Prevent accidental changes to database design
- Choose appropriate permissions for different users
- Separate the roles of database designers and database users
SQL statements fall into DDL or DML not just by name, but by whether they shape the database itself or work within that shape. This practical distinction underpins safe and effective database use.
Structured Query Language (SQL) | A standard programming language used to create, manage, and interact with relational databases.
Data Language Type | A category of SQL statements grouped by their purpose, such as defining database structures or manipulating stored data.
Data Definition Language (DDL) | A subset of SQL used to define, modify, and remove the structure of a database.
Data Manipulation Language (DML) | A subset of SQL used to insert, retrieve, update, and delete data stored within database tables.
Database Structure | The design of a database, including its tables, columns, data types, and constraints.
Schema | The logical blueprint of a database that defines how data is organised and structured.
Table | A database object that stores data in rows and columns.
Record | A single row of data within a database table.
Field | A single column in a database table that stores a specific type of data.
Constraint | A rule applied to a database table to ensure the accuracy and integrity of data.
Database Schema Modification | The process of changing the structure of a database using DDL statements.
Data Language Type | A category of SQL statements grouped by their purpose, such as defining database structures or manipulating stored data.
Data Definition Language (DDL) | A subset of SQL used to define, modify, and remove the structure of a database.
Data Manipulation Language (DML) | A subset of SQL used to insert, retrieve, update, and delete data stored within database tables.
Database Structure | The design of a database, including its tables, columns, data types, and constraints.
Schema | The logical blueprint of a database that defines how data is organised and structured.
Table | A database object that stores data in rows and columns.
Record | A single row of data within a database table.
Field | A single column in a database table that stores a specific type of data.
Constraint | A rule applied to a database table to ensure the accuracy and integrity of data.
Database Schema Modification | The process of changing the structure of a database using DDL statements.
Check your understanding of SQL DDL and DML
1. Which SQL statement is used to define a new table?
2. Which type of SQL statement changes the contents of a table without altering its structure?
3. Which SQL statement would be used to retrieve data from a database?
4. Which SQL statement permanently removes a table structure?
5. Which pair correctly matches the SQL language type to its purpose?
1. Explain why SQL statements that define data structures are usually executed before SQL statements that manipulate data.
SQL statements that define data structures must be executed first because tables and fields need to exist before data can be stored or accessed. Without defining the structure using DDL, DML statements such as INSERT or SELECT would have no tables to operate on.
2. Describe one potential problem that could occur if a user confuses DDL and DML statements.
If a user confuses DDL and DML statements, they may accidentally change or delete the database structure instead of just modifying data. For example, using DROP TABLE instead of DELETE could permanently remove a table and all its data.
COMING SOON
A3 DATABASE FUNDAMENTALS
☐ 3.1.1 DDL AND DML
☐ 3.1.1 DDL AND DML
