SQL Sublanguages: DDL, DML, and DCL

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: FROM →WHERE → GROUP BY → HAVING

SELECT customer_id, SUM(amount)
  FROM payments
  GROUP BY customer_id HAVING SUM(amount) > 200;

Functions