Write an SQL script to query or modify data (DML) which are stored in (at most two) database tables

Resources | Subject Notes | Computer Science

SQL DDL and DML

SQL Data Definition Language (DDL) and Data Manipulation Language (DML)

This section covers Data Definition Language (DDL) and Data Manipulation Language (DML) in SQL. DDL is used to define and manage the structure of the database, while DML is used to manipulate the data within the database.

Data Definition Language (DDL)

DDL commands are used to create, modify, and delete database objects such as tables, indexes, and views. Common DDL statements include:

  • CREATE: Creates a new database object (e.g., a table).
  • ALTER: Modifies the structure of an existing database object.
  • DROP: Deletes a database object.
  • TRUNCATE: Removes all rows from a table, but the table structure remains.

Example DDL Script:

Suggested diagram: A simple database schema with two tables: Customers and Orders.
SQL Statement Description 
CREATE TABLE Customers (
                        CustomerID INT PRIMARY KEY,
                        FirstName VARCHAR(255),
                        LastName VARCHAR(255),
                        Email VARCHAR(255)
                    );
 Creates a table named 'Customers' with columns for customer information. 
CREATE TABLE Orders (
                        OrderID INT PRIMARY KEY,
                        CustomerID INT,
                        OrderDate DATE,
                        TotalAmount DECIMAL(10, 2),
                        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
                    );
 Creates a table named 'Orders' with columns for order information, including a foreign key referencing the 'Customers' table.

Data Manipulation Language (DML)

DML commands are used to manage the data stored in the database. Common DML statements include:

  • SELECT: Retrieves data from one or more tables.
  • INSERT: Adds new data into a table.
  • UPDATE: Modifies existing data in a table.
  • DELETE: Removes data from a table.

Example DML Script:

Consider the Customers and Orders tables created above.

SELECT Statement

Retrieving all customers:

Suggested diagram: A simple query to select all columns and rows from the Customers table.
SQL Statement Description 
SELECT * FROM Customers;
 Retrieves all columns and all rows from the 'Customers' table. 
SELECT FirstName, LastName FROM Customers WHERE Email = 'john.doe@example.com';
 Retrieves the first name and last name of the customer with the email 'john.doe@example.com'.

INSERT Statement

Adding a new customer:

Suggested diagram: An insert statement adding a new customer record to the Customers table.
SQL Statement Description 
INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
                        VALUES (1, 'John', 'Doe', 'john.doe@example.com');
 Inserts a new row into the 'Customers' table with the specified values.

UPDATE Statement

Updating a customer's email:

Suggested diagram: An update statement modifying the email address of an existing customer in the Customers table.
SQL Statement Description 
UPDATE Customers
                        SET Email = 'john.new@example.com'
                        WHERE CustomerID = 1;
 Updates the email address of the customer with CustomerID 1.

DELETE Statement

Deleting a customer:

Suggested diagram: A delete statement removing a customer record from the Customers table.
SQL Statement Description 
DELETE FROM Customers
                    WHERE CustomerID = 1;
 Deletes the customer with CustomerID 1 from the 'Customers' table.

These examples demonstrate basic DDL and DML operations. SQL provides a powerful and flexible way to manage and manipulate data within databases.