What is Data Definition Language (DDL)?
Simply put, DDL is a subset of SQL commands that we use to create and modify the structure of objects in a database. We can change the database structure by adding new tables or objects with all of their attributes such as data type, table name, and so on.
To gain a better understanding, let's delve deeper into each DDL command shown in the diagram. To demonstrate these commands, let's pretend we're working on a database for a fictitious music store Sequel Records.
CREATE
The DDL command CREATE is used to create the database or any objects such as a table, index, function, view, and store procedures.
1. CREATE DATABASE
Firstly we must create the database for our store. The CREATE query below allows you to create a new database.
1CREATE DATABASE musicstore;2. CREATE TABLE
We firstly want to create a table containing all of the information on the albums we sell. Within the CREATE statement, we must first define the structure of our table, including column names, data types, and the size of the data to be added to each column.
1CREATE TABLE albums (
2 album_id INT,
3 title VARCHAR(150),
4 artist VARCHAR (80),
5 year_published SMALLINT,
6 price DECIMAL (6,2) NOT NULL,
7 genre VARCHAR(20) NOT NULL,
8 PRIMARY KEY (album_id));
From there we can use Data Manipulation Language (DML) specifically the command INSERT to populate the table with the data from our albums.
3. CREATE VIEW
Suppose we run a query and want to save the results-set as a table, in this situation we can create a view using the CREATE command. Because a view displays current data, the database engine recreates it each time a user queries it.
1CREATE VIEW DE_view AS
2SELECT album_id, title, artist, price
3FROM albums
4WHERE genre = ‘Dance/Electronic’;We can then run queries on our view as if it was a separate table.
1SELECT title, artist
2FROM DE_view;
4. CREATE INDEX
Using the CREATE command we can create indexes for our table. Indexes are used to retrieve data from a database more quickly than would otherwise be possible. The indexes are not visible to users; they are only used to speed up searches and queries.
As an example, our database becomes increasingly populated with albums over time, to the point where searching becomes extremely slow. Using the following command, we create an index on a single column title or more than one column title and artist.
1-- One column index
2CREATE INDEX index_album
3ON albums (title);
4
5-- Multiple column index
6CREATE INDEX index_album_m
7ON albums (title, artist);In addition, we can make indexes unique in order to maintain data integrity by ensuring that no two rows of data in a table have the same key value.
1CREATE UNIQUE INDEX index_album_u
2ON albums (title);5. CREATE PROCEDURE
The CREATE command can also be used to create a stored procedure which is basically a prepared SQL code that can be saved and reused over and over again.
1DELIMITER //
2CREATE PROCEDURE SelectAllAlbums()
3BEGIN
4 SELECT * FROM albums;
5END //
6DELIMITER ;To execute our stored procedure we simply run the command below. Handy right?
1CALL SelectAllAlbums();
Try this exercise. Click the correct answer from the options.
You want to create a table called artists. Fill in the blank
1______________ artists(
2 artist_id CHAR(3),
3 artist_name VARCHAR (80),
4 PRIMARY KEY (artist_id ));Click the option that best answers the question.
- CREATE TABLE
- CREATE VIEW
- CREATE INDEX
- CREATE PROCEDURE
ALTER
1. ALTER TABLE
The DDL command ALTER is used to change the structure of the databases we create. For example, you might want to be able to keep track of inventory with your new database. You decide to add a column called qty_on hand.
1ALTER TABLE albums
2ADD qty_on_hand SMALLINT NOT NULL;Or you notice that the data type you entered for album_id is an integer, when you actually wanted it to be a VARCHAR of length 3.
1ALTER TABLE albums
2MODIFY COLUMN album_id VARCHAR(3);Try this exercise. Click the correct answer from the options.
You want to add a column called nationality to the table artists. Fill in the blank
1ALTER TABLE artists
2______ nationality VARCHAR(30);Click the option that best answers the question.
- MODIFY COLUMN
- ADD
- ALTER COLUMN
- ADD A COLUMN
DROP
Using the DROP command we can delete the database or any objects associated with it.
1. DROP DATABASE
Using the following command we can delete our entire database. Before running this command, you should be aware that it will delete your entire database including all tables, data, indexes, and other elements, so make sure you're certain before proceeding.
1DROP DATABASE musicstore;2. DROP TABLE
Similar to dropping a database, when we drop a table everything will be deleted.
1DROP TABLE albums;3. DROP VIEW
And to drop a view.
1DROP VIEW DE_view;3. DROP COLUMN
Let's say we want to delete a specific column. In this case we use the ALTER TABLE command in conjunction with the DROP command. As the owner of the music store, you determine that the column year_published is no longer necessary in this table and decide to remove it.
1ALTER TABLE albums
2DROP COLUMN year_published;4. DROP CONSTRAINT
Another useful feature of this command is the ability to remove constraints. Below we will go through the syntax for removing all constraints with the command DROP.
1-- Drop the Primary Key
2ALTER TABLE albums
3DROP PRIMARY KEY;
4
5-- Drop the Foreign Key
6ALTER TABLE albums
7DROP FOREIGN KEY FK_AlbumOrder;
8
9-- Drop a Unique constraint
10ALTER TABLE albums
11DROP INDEX UC_Album;
12
13-- Drop the Check constraint
14ALTER TABLE albums
15DROP CONTRAINT CHK_AlbumPrice;
16
17-- Drop the Default constraint
18ALTER TABLE abums
19ALTER genre DROP DEFAULT;5. DROP INDEX
Imagine we no longer have as much albums in our table therefore there is no real need for the index we created in the first part of this tutorial. We can remove the index using the DROP command.
1ALTER TABLE album
2DROP INDEX index_album; Try this exercise. Click the correct answer from the options.
You want to delete the table artists you just made. Fill in the blank
1_______________ artists;Click the option that best answers the question.
- DROP VIEW
- ELIMINATE TABLE
- DROP TABLE
- DROP CONSTRAINT
TRUNCATE
The TRUNCATE TABLE command deletes the data within a table. Unlike DROP, it keeps the table's structure, indexes, and dependencies. If we wanted to remove all data entries from a table in a database while keeping the table albums structure but not the entries, we would use the SQL command below.
1TRUNCATE TABLE albums;
2
3-- Check table with a query
4SELECT * FROM albums;
Build your intuition. Is this statement true or false?
Truncating a table deletes the tables structure.
Press true if you believe the statement is correct, or false otherwise.
RENAME
The DDL command RENAME, as expected is used to change names in our database.
1. RENAME TABLE
Perhaps we should choose a more appropriate name for our table in our music store. We can do so with the following.
1ALTER TABLE albums
2RENAME TO our_albums; 2. RENAME COLUMN
Or maybe we want to rename a column, we can do the following.
1ALTER TABLE albums
2RENAME COLUMN title TO album_name; Let's test your knowledge. Click the correct answer from the options.
You want to rename the column year_published to release_year. Fill in the blank
1ALTER TABLE albums
2____________________ year_published TO release_year; Click the option that best answers the question.
- RENAME COLUMN
- NAME COLUMN
- CHANGE COLUMN
- ALTER COLUMN NAME
One Pager Cheat Sheet
- Data Definition Language (DDL) is a subset of SQL commands that we use to create, modify and manipulate the structure of database objects.
- The
CREATEcommand is used to create objects such as a database, table, index, view, and stored procedure in a database. - The
CREATE TABLEcommand is used to create a new table called artists in the database, defining its columns, data types, size, andPRIMARY KEY. - The DDL command
ALTERis used to modify existing databases by adding or modifying columns in atable. - We can use the
ALTERcommand with theADDkeyword to add a new column called nationality to the table artists. - Using the
DROPcommand we can delete a database, table, column, constraint, or index. - The
DROP TABLEcommand is used to deletetables, such as artists, followed by asemicolon. - The
TRUNCATE TABLEcommand is used to delete data entries from a table while keeping its structure, indexes, and dependencies. - The
TRUNCATE TABLEcommand deletes the data within a table but does not affect its structure, indexes or dependencies. - The DDL command
RENAMEcan be used to change the name of tables and columns in our database. - We
RENAMEthecolumnyear_published to release_year in the albums table using theRENAME COLUMNclause of theALTER TABLEDLLcommand.