SQL Sublanguages: DDL, DML, and DCL

Marwan Zaarab
2 min readJul 15, 2022

SQL is a declarative and special purpose language used to manipulate the structure and values of datasets stored in a relational database.

DDL

  • DDL (Data Definition Language) is concerned with schema. Its purpose is to allow a user to create / modify the schema stored within a database.
  • DDL controls the relation, structure, and rules that govern the data that is held within a database (e.g. creating tables, altering table attributes, deleting structures and adding constraints)
  • constructs: CREATE, ALTER, DROP
  • constraints: UNIQUE, CHECK, NOT NULL, DEFAULT
DDL — SQL Statements

DML

  • DML (Data Manipulation Language) is concerned with data. Its purpose is to perform CRUD (Create, Read, Update, Delete) operations on the actual values stored within a database.
  • DML allows a user to retrieve or modify data stored within a database as well as control the values stored within relations (e.g. inserting, selecting , updating, or deleting data from a table).
  • constructs: SELECT, INSERT, UPDATE, DELETE, DISTINCT
DML-SQL Statements

DCL

  • DCL (Data Control Language) is tasked with controlling the rights and access roles of the users interacting with a database or table.
  • It is responsible for defining the rights and roles granted to individual users (e.g., granting read-only access, or ensuring certain users only have access toSELECT statements).
  • constructs: GRANT,REVOKE

CHECK constraints

Aggregating data with GROUP BY

To ensure that there is a single value for every column in the result set, all of the columns in the SELECT list must either:

  • be included in the GROUP BY clause
  • be the result of an aggregate function
  • or the GROUP BY clause must be based on the primary key

HAVING

  • The HAVING clause specifies a search condition for a group or an aggregate returned by the GROUP BY clause.
  • Statement order: FROMWHEREGROUP BYHAVING
SELECT customer_id, SUM(amount)
FROM payments
GROUP BY customer_id HAVING SUM(amount) > 200;

Functions

--

--