SQL Data Definition and Data Manipulation

Introduction

A database is useful only when its structure accurately represents a domain and its data can be stored, protected, changed, and retrieved reliably. Structured Query Language (SQL) provides a standardized language for these activities. Although database products such as PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, and SQLite implement SQL with some differences, the central ideas are widely shared.

This chapter examines two closely related parts of SQL. Data Definition Language (DDL) defines database objects such as tables, constraints, indexes, and views. Data Manipulation Language (DML) inserts, changes, removes, and retrieves rows. Together, these capabilities form the practical foundation for application databases.

SQL is declarative: a programmer states what result is required rather than describing every internal processing step. The database management system (DBMS) chooses an execution strategy, often using indexes and query-optimization techniques.

Learning Objectives

By the end of this chapter, you should be able to:

• design tables using suitable data types;
• create and modify tables with DDL;
• apply primary-key, foreign-key, unique, check, and not-null constraints;
• explain the purpose and trade-offs of indexes;
• create and use views;
• insert, update, delete, and retrieve rows safely;
• use filtering, sorting, grouping, joins, and subqueries;
• recognize SQL portability, integrity, security, and transaction issues.

1. The Relational Foundation

In a relational database, a table represents a relation, a row represents one occurrence of an entity or relationship, and a column represents an attribute. A table should have a clear purpose. For example, a Customer table stores customer facts, while an Order table stores order facts.

A column has a data type, which restricts the values that can be stored. Common types include:

DECIMAL(10,2) permits up to 10 digits in total, with 2 digits after the decimal point. Exact numeric types are generally preferable to floating-point types for monetary values because floating-point representation can introduce rounding differences.

SQL uses three-valued logic: a condition can be true, false, or unknown. The unknown result commonly occurs when a value is NULL. NULL does not mean zero, an empty string, or the word “unknown” in every practical sense; it indicates that a value is absent or not applicable. Comparisons with NULL require IS NULL or IS NOT NULL, not = NULL.

2. Creating Tables with DDL

The basic form of table creation is:

CREATE TABLE table_name (
    column_name data_type [constraint],
    ...
);

The following example creates a small sales schema:

CREATE TABLE Customer (
    customer_id INTEGER PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    registration_date DATE NOT NULL
);

CREATE TABLE Product (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(120) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT product_price_ck CHECK (unit_price >= 0),
    CONSTRAINT product_stock_ck CHECK (stock_quantity >= 0)
);

CREATE TABLE SalesOrder (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    CONSTRAINT order_customer_fk
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    CONSTRAINT order_status_ck
        CHECK (status IN ('Pending', 'Paid', 'Shipped', 'Cancelled'))
);

Constraint names improve error messages and make later maintenance easier. Some DBMS products generate identity values automatically, but the syntax differs. For example, PostgreSQL supports identity columns, while other systems use AUTO_INCREMENT or sequences. Such features should be checked against the target DBMS documentation.

2.1 Constraints and Integrity

Constraints express rules that the DBMS enforces rather than leaving every rule to application code.

• Primary key: uniquely identifies each row and cannot be null.
• Foreign key: requires a value to match a referenced key, unless nullability permits an absent relationship.
• UNIQUE: prevents duplicate non-null values according to DBMS rules.
• NOT NULL: requires a value.
• CHECK: requires a Boolean condition to be satisfied.
• DEFAULT: supplies a value when an insert omits the column; it does not generally replace an explicitly supplied null.

These constraints support three important forms of integrity. Entity integrity requires each row to be identifiable. Referential integrity keeps relationships between tables valid. Domain integrity restricts values to appropriate types and ranges.

A foreign key may specify actions when the referenced row is changed or deleted:

FOREIGN KEY (customer_id)
REFERENCES Customer(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE

The exact availability and behavior of actions varies by DBMS. CASCADE can be useful, but careless cascading deletion can remove more data than intended.

3. Altering and Removing Objects

Existing tables can be modified with ALTER TABLE:

ALTER TABLE Customer ADD COLUMN phone VARCHAR(30);
ALTER TABLE Product ADD CONSTRAINT product_name_uq UNIQUE (product_name);

Changing a column’s type or adding a NOT NULL constraint may fail if existing rows violate the new rule. A safe migration commonly involves adding a nullable column, populating and validating it, and then applying the stricter constraint.

DROP TABLE removes a table and, depending on dependencies and options, may remove associated objects or fail. TRUNCATE TABLE removes all rows while retaining the table definition; its transaction and trigger behavior is DBMS-specific. These commands must never be used casually in production.

4. Indexes

An index is an auxiliary data structure that helps the DBMS locate rows efficiently. An index may improve a query such as:

CREATE INDEX customer_name_ix
ON Customer(full_name);

A composite index can support conditions involving multiple columns:

CREATE INDEX order_customer_date_ix
ON SalesOrder(customer_id, order_date);

Column order matters. An index beginning with (customer_id, order_date) is generally useful for searches by customer_id, and often for searches by both columns. It is not normally equivalent to an index beginning with (order_date, customer_id) for every query.

Indexes have costs. They consume storage and must be maintained during insert, update, and delete operations. An index is most valuable when it supports frequent, selective searches, joins, ordering, or grouping. Indexing every column is poor practice. Primary keys and unique constraints often create indexes automatically, but this is implementation-dependent.

Query plans should be examined with the DBMS’s plan-analysis tool, such as EXPLAIN. An index is not guaranteed to be used: if a query returns most rows, a sequential scan may be cheaper.

5. Views

A view is a named query that presents data as a virtual table:

CREATE VIEW CustomerOrderSummary AS
SELECT c.customer_id,
       c.full_name,
       o.order_id,
       o.order_date,
       o.status
FROM Customer AS c
JOIN SalesOrder AS o
  ON o.customer_id = c.customer_id;

Users can query the view as follows:

SELECT full_name, order_id
FROM CustomerOrderSummary
WHERE status = 'Paid';

Views can simplify complex queries, provide a stable interface when base tables change, and restrict access to selected columns or rows. A view is not automatically a security boundary; permissions must be configured correctly. Many views are not freely updatable, especially those containing grouping, aggregation, DISTINCT, or several joins. A materialized view, where supported, stores query results and must be refreshed.

6. Data Manipulation Language

6.1 Inserting Rows

An explicit column list is recommended:

INSERT INTO Customer
    (customer_id, full_name, email, registration_date)
VALUES
    (101, 'Amina Yusuf', '[email protected]', DATE '2025-02-01');

Multiple rows may be inserted in one statement:

INSERT INTO Product
    (product_id, product_name, unit_price, stock_quantity)
VALUES
    (1, 'USB Keyboard', 24.50, 40),
    (2, 'Wireless Mouse', 18.75, 65);

A foreign-key constraint means a sales order for customer 101 can be inserted only after that customer exists. The standard date literal syntax shown above is portable in principle, although product support differs.

6.2 Retrieving Rows with SELECT

The essential form is:

SELECT column_list
FROM table_or_view
WHERE condition
ORDER BY column_list;

Example:

SELECT product_name, unit_price
FROM Product
WHERE unit_price BETWEEN 20 AND 50
ORDER BY unit_price DESC;

BETWEEN is inclusive at both ends. Use IN for a set of alternatives and LIKE for pattern matching:

SELECT full_name
FROM Customer
WHERE email LIKE '%@example.org';

The percent sign represents zero or more characters; underscore represents one character. Pattern behavior and case sensitivity can vary by DBMS and collation.

Avoid SELECT * in application code when a stable column list is sufficient. Explicit columns reduce unnecessary data transfer and protect applications from accidental schema changes.

6.3 Joins

A join combines related rows. An inner join returns only matching rows:

SELECT c.full_name, o.order_id, o.order_date
FROM Customer AS c
INNER JOIN SalesOrder AS o
  ON o.customer_id = c.customer_id;

A left join retains every row from the left table, including customers without orders:

SELECT c.full_name, o.order_id
FROM Customer AS c
LEFT JOIN SalesOrder AS o
  ON o.customer_id = c.customer_id;

A frequent error is placing a condition on the right table in the WHERE clause of a left join, which can effectively turn it into an inner join. Conditions intended to preserve unmatched left rows are often placed in the ON clause.

6.4 Aggregation and Grouping

Aggregate functions summarize rows:

SELECT customer_id,
       COUNT(*) AS order_count
FROM SalesOrder
GROUP BY customer_id
HAVING COUNT(*) >= 2;

WHERE filters rows before grouping; HAVING filters groups after aggregation. COUNT(*) counts rows, while COUNT(column) generally excludes null values in that column. Grouped queries must follow the DBMS’s rules for selected nonaggregated columns.

6.5 Updating Rows

An update should normally include a carefully tested WHERE clause:

UPDATE Product
SET unit_price = unit_price * 1.05
WHERE product_id = 1;

Without WHERE, every row is updated. A transaction and a preliminary SELECT can reduce risk:

BEGIN;
SELECT product_id, unit_price FROM Product WHERE product_id = 1;
UPDATE Product SET unit_price = unit_price * 1.05 WHERE product_id = 1;
COMMIT;

Transaction syntax varies. If validation fails, use ROLLBACK instead of COMMIT.

6.6 Deleting Rows

DELETE FROM SalesOrder
WHERE order_id = 5001;

A delete can fail because another table references the row, or it can trigger configured cascading actions. Soft deletion—marking a row as inactive rather than physically deleting it—is sometimes preferable for auditability, but it requires consistent filtering and governance.

7. A Worked Application: Training-Centre Database

This illustrative case concerns a training centre that enrolls learners in courses. The centre initially stores enrollment information in one spreadsheet, causing duplicate learner details, inconsistent course names, and difficulty identifying active enrollments.

Problem and analysis

A learner may enroll in many courses, and a course may contain many learners. This is a many-to-many relationship, so an associative table is required. The design separates Learner, Course, and Enrollment. The enrollment table stores relationship-specific facts such as enrollment date and completion status.

CREATE TABLE Learner (
    learner_id INTEGER PRIMARY KEY,
    learner_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE Course (
    course_id INTEGER PRIMARY KEY,
    course_title VARCHAR(120) NOT NULL UNIQUE,
    fee DECIMAL(10,2) NOT NULL CHECK (fee >= 0)
);

CREATE TABLE Enrollment (
    learner_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    result VARCHAR(20),
    PRIMARY KEY (learner_id, course_id),
    FOREIGN KEY (learner_id) REFERENCES Learner(learner_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id),
    CHECK (result IN ('Pass', 'Fail', 'Withdrawn') OR result IS NULL)
);

The composite primary key prevents the same learner from being enrolled in the same course twice. A report of current enrollments can be created as a view:

CREATE VIEW CurrentEnrollment AS
SELECT l.learner_name, c.course_title, e.enrolled_on
FROM Enrollment AS e
JOIN Learner AS l ON l.learner_id = e.learner_id
JOIN Course AS c ON c.course_id = e.course_id
WHERE e.result IS NULL;

An administrator can retrieve learners taking a particular course:

SELECT learner_name
FROM CurrentEnrollment
WHERE course_title = 'Database Management';

Outcome and learning points

The design removes repeated facts, enforces valid relationships, supports useful reports, and provides a controlled interface through the view. It also demonstrates that table design, constraints, indexes, and queries should be considered together. If reports frequently search by course_id, an index on Enrollment(course_id) may improve performance, especially when the table becomes large.

8. Transactions, Security, and Professional Practice

A transaction groups operations into a unit of work. The traditional ACID properties are:

• Atomicity: all operations succeed or none are applied.
• Consistency: committed data satisfies defined rules.
• Isolation: concurrent transactions are controlled according to the isolation level.
• Durability: committed changes survive ordinary failures.

Applications should use parameterized queries rather than constructing SQL by string concatenation. For example, a parameter placeholder such as WHERE email = ? allows the driver to bind a value safely and helps prevent SQL injection. Database accounts should receive least privilege: a reporting account should not automatically have permission to drop tables.

Sensitive personal data requires appropriate access control, encryption, retention, auditing, and lawful processing. Views can reduce exposure, but they do not replace comprehensive authorization. Backups should be tested through restoration exercises, not merely assumed to work.

9. Portability and Contemporary Practice

SQL standards define common syntax, but implementations differ in identity columns, date functions, pagination, regular expressions, error handling, stored procedures, and transaction behavior. Professional developers should identify the target DBMS, test migration scripts on a safe copy, and avoid relying on undocumented behavior.

Modern database work also includes migration tools, continuous integration tests, schema versioning, cloud-managed databases, and observability of query latency. AI-assisted tools can help draft queries or explain execution plans, but generated SQL must be reviewed for correctness, authorization, privacy, performance, and unintended data modification. AI should not be granted unsupervised production write access.

Challenges and Limitations

SQL does not automatically produce a good data model. Poorly chosen keys, inconsistent naming, unvalidated assumptions, and excessive nullability can create anomalies. Constraints improve reliability but may complicate bulk loading and legacy-data migration. Indexes improve some queries while slowing writes. Views simplify access but can hide expensive joins. Finally, SQL injection, excessive privileges, accidental unrestricted updates, and untested deletion behavior remain serious operational risks.

Best-Practice Checklist

1. Give every table a clear purpose and primary key.
2. Use appropriate, sufficiently precise data types.
3. Enforce important business rules with constraints where practical.
4. Write explicit column lists for INSERT and usually for SELECT.
5. Test UPDATE and DELETE predicates with a SELECT first.
6. Use transactions for related changes.
7. Add indexes based on measured query needs.
8. Use parameterized statements.
9. Apply least privilege and audit sensitive operations.
10. Test backups, migrations, and rollback procedures.

Chapter Summary

DDL creates and changes database structures, while DML works with the rows stored in those structures. Tables should use suitable data types and constraints to preserve domain, entity, and referential integrity. Primary keys identify rows; foreign keys represent valid relationships; unique, not-null, check, and default constraints enforce additional rules. Indexes can accelerate selective queries but consume storage and increase write cost. Views provide reusable, controlled presentations of data. SELECT, joins, grouping, INSERT, UPDATE, and DELETE support everyday database work, but safe use requires explicit predicates, transactions, parameterized queries, least privilege, and careful attention to DBMS-specific behavior.

Key Terms

DDL: SQL statements that define or modify database objects.

DML: SQL statements that insert, update, delete, or retrieve data.

Table: A relational structure consisting of rows and columns.

Row: One record or tuple in a table.

Column: A named attribute with a defined data type.

Primary key: Column or columns that uniquely identify each row.

Foreign key: Column or columns referencing a key in another table.

Constraint: A rule enforced by the DBMS.

NULL: A marker for an absent, inapplicable, or unknown value.

Index: An auxiliary structure used to accelerate data access.

Composite index: An index containing multiple columns.

View: A named query presented as a virtual table.

Join: An operation combining rows from related tables.

Aggregate function: A function such as COUNT or SUM that summarizes rows.

Transaction: A logical unit of database work.

Atomicity: The all-or-nothing property of a transaction.

Referential integrity: The requirement that foreign-key references remain valid.

Parameterized query: SQL using bound values rather than concatenated input.

Least privilege: Granting only the permissions required for a task.

Review Questions

Short Answer Questions

1. What is the difference between DDL and DML?
2. Why is a primary key important?
3. What is the purpose of a foreign-key constraint?
4. How does a view differ from a base table?
5. Why can indexes reduce write performance?

Long Answer Questions

1. Explain the main SQL constraints and give an example use for each.
2. Compare inner joins and left joins, including a practical use case for each.
3. Explain the logical distinction between WHERE and HAVING.
4. Discuss the benefits and risks of cascading foreign-key actions.
5. Explain how transactions and parameterized queries improve application reliability and security.

Analytical / Application Questions

1. Design tables for students, modules, and registrations in which a student can register for many modules. Identify keys, constraints, and the associative table.
2. A report query is slow after a table grows from 10,000 to 10 million rows. Propose a systematic approach involving query inspection, indexing, and measurement.
3. An administrator accidentally runs DELETE FROM Customer;. Analyze the technical and governance controls that could prevent or recover from this event.

Multiple Choice Questions with Answers

Question 1

Which SQL object uniquely identifies each row in a table?

A. Primary key

B. View

C. Index only

D. Default value

Correct Answer: A. Primary key

Question 2

Which clause filters groups after aggregation?

A. WHERE

B. HAVING

C. ORDER BY

D. FROM

Correct Answer: B. HAVING

Question 3

What does a foreign key primarily enforce?

A. Faster sorting

B. Unique column names

C. Referential integrity

D. Automatic encryption

Correct Answer: C. Referential integrity

Question 4

Which statement correctly tests whether a value is absent?

A. WHERE email = NULL

B. WHERE email IS NULL

C. WHERE email == NULL

D. WHERE email LIKE NULL

Correct Answer: B. WHERE email IS NULL

Question 5

What is a major cost of adding indexes?

A. They prevent all joins

B. They eliminate constraints

C. They increase maintenance work during data changes

D. They make tables unable to store nulls

Correct Answer: C. They increase maintenance work during data changes

Question 6

Which command adds a new column to an existing table?

A. ALTER TABLE

B. UPDATE TABLE

C. CHANGE DATABASE

D. MODIFY ROW

Correct Answer: A. ALTER TABLE

Question 7

What is the safest general practice for user-supplied values in SQL?

A. Concatenate them into SQL strings

B. Use parameterized queries

C. Disable all constraints

D. Grant administrator privileges

Correct Answer: B. Use parameterized queries

Question 8

Which join returns all rows from the left table, including unmatched rows?

A. INNER JOIN

B. CROSS JOIN

C. LEFT JOIN

D. SELF JOIN only

Correct Answer: C. LEFT JOIN

Question 9

What does a composite primary key in Enrollment commonly prevent?

A. All course registrations

B. Duplicate combinations of its key columns

C. Use of foreign keys

D. Queries involving joins

Correct Answer: B. Duplicate combinations of its key columns

Question 10

Which transaction property means that committed changes survive ordinary failures?

A. Isolation

B. Atomicity

C. Consistency

D. Durability

Correct Answer: D. Durability