Hello there! Learning about DDL and DML concepts is a fundamental foundation for becoming a Data Wrangling and Data Warehousing extraordinaire. SQL statements are divided into two major categories: Data Definition Language (DDL) and Data Manipulation Language (DML). In Short:
DDL describes a bunch of commands that CREATE Data Structures, like Tables, Views, and Procedures.
DML describes the commands that SELECT, INSERT, UPDATE or DELETE data to and from these Data Structures.
Concepts of DDL and DML
DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.
The CREATE TABLE statement does exactly that:
CREATE TABLE <table name> ( <attribute name 1> <data type 1>, …. <attribute name n> <data type n>);
The ALTER TABLE statement may be used (as you have seen) specifying the primary/foreign key constraints, then you can do other modifications to the table structure. Key constraints may also be specified in the CREATE TABLE statement.
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);
The foreign key constraint is a bit more complicated since we have to specify both the FK attributes in this (child) table, and the PK attributes that they link to the parent table.
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY (<attribute list>) REFERENCES <parent table name> (<attribute list>);
If you totally mess things up and want to start over, you can always get rid of any object you have created with a DROP statement. The syntax is different for tables and constraints.
DROP TABLE <table name>;
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>;
DML statements are used to work with the data in tables. You have already seen the SELECT statement, it is considered to be part of DML even though it just retrieves data rather than modifying it.
The INSERT statement is used to add new rows to a table:
INSERT INTO <table name>VALUES (<value 1>, … <value n>);
The UPDATE statement is used to change values that are already in a table.
UPDATE <table name> SET <attribute> = <expression> WHERE <condition>;
The DELETE statement does just that, for rows in a table.
DELETE FROM <table name> WHERE <condition>;
You will find more fundamentals in my Previous Posts! This post is an introduction to DDL and DML concepts. We’ll see more about these statements in the next episodes!