A Practical Guide to SQL and Database Fundamentals/

...

/

Data Definition Language (DDL)

Data Definition Language (DDL)

Learn the commands to create, modify, and remove the database and its components like schema, table, view, etc.

We'll cover the following...

Data Definition Language (DDL) is the SQL syntax used to create, alter or remove objects within the instance or database. Here are some examples to get started. There are three types of commands in DDL.

The CREATE command

The CREATE command creates objects within the database or instance, such as other databases, tables, views, etc.

 CREATE DATABASE DatabaseName

The command above creates the database.

 CREATE SCHEMA SchemaName

The command above creates a container for the tables in the database.

MySQL
CREATE TABLE SchemaName.TableName
(
     Column1 datatype PRIMARY KEY,
     Column2 datatype(n),
     Column3 datatype
)

The query above creates a table in the specified schema.

MySQL
CREATE VIEW ViewName
AS
SELECT
  Column1,
  Column2
FROM TableName

The statement above takes two columns from the table and creates a respective view.

We can also add the constraints while creating the table, as follows:

MySQL
CREATE TABLE TableName (
      Column1 int NOT NULL
    )
GO

We can add the DEFAULT constraint in line 3 when creating a table.

MySQL
CREATE TABLE TableName
(
Column1 FLOAT(6,2) DEFAULT 0.00
);

We use the CREATE INDEX command as shown below:

MySQL
CREATE INDEX idx_new
ON TableName(Column1)
GO

The ALTER command

The ALTER command allows us to alter existing objects, like adding a column to a table or changing the name of the database.

MySQL
ALTER DATABASE DatabaseName MODIFY NAME = NewDatabaseName

The command above alters the name of the database.

MySQL
ALTER TABLE Tablename
ADD ColumnName datatype(n)

The ALTER command above alters the table by adding a column.

The ALTER command is also used when we add a constraint to a table.

MySQL
ALTER TABLE TableName
ADD CONSTRAINT FK_Col
FOREIGN KEY () REFERENCES TableName(Column1)
GO

NOT NULL with ALTER TABLE

It adds the constraint to the column by altering the already created table.

MySQL
ALTER TABLE TableName
MODIFY COLUMN Column1 int NOT NULL;
GO

The DROP command

The DROP command allows us to drop objects within the database or the database itself. We can drop tables, triggers, views, stored procedures, etc.

Note: These items will no longer exist within the database—or the database will cease to exist if we drop it.

This command below drops a database and uses the Master database first in line 1. Then it drops the desired database in line 3 as shown below:

MySQL
USE Master
GO
DROP DATABASE DatabaseName
GO

The DROP command below drops the table.

DROP TABLE TableName

The DROP command below drops the view from the current database.

DROP VIEW ViewName

We can drop a constraint as shown below:

MySQL
ALTER TABLE TableName
DROP CONSTRAINT FK_col
GO

Moving on, we can also drop an index by using the following command:

MySQL
DROP INDEX IF EXISTS TableName.idx_new
GO

Let's execute the commands above by clicking the “Run” button below:

-- We have used additional SELECT statements to show the effect of the queries

-- Creating database Educative--
CREATE DATABASE Educative 
GO

USE Educative
GO

-- Creating schema Ed-- 
CREATE SCHEMA Ed        
GO

-- Creating Table Department-- 
CREATE TABLE Ed.Department
(
     DepID INT PRIMARY KEY,
     DepName VARCHAR(30),
     DepNo INT UNIQUE
     
)
GO



-- Create table Team-- 
CREATE TABLE Ed.Team 
(
     TeamID INT PRIMARY KEY,
     TeamName VARCHAR(30), 
     Cost_perTeam DECIMAL(6,2) DEFAULT 0.00 -- apply constraint default --
     
)
GO
SELECT * FROM Ed.Department -- Select statement to see that the table has been created
GO
SELECT * FROM Ed.Team -- Select statement to see that the Team table has been created
GO

-- Change the name of the database to Educative_IO--
SELECT"--ALTER DATABASE--"
ALTER DATABASE Educative MODIFY NAME = EducativeIO 
GO

-- Alter the department table and add a column Dep_Floor--
SELECT"--ALTER TABLE--"
ALTER TABLE Ed.Department 
ADD DepFloor INT NOT NULL
GO

SELECT * FROM Ed.Department -- Select statement to see that the table has been altered
GO




-- Create index on the column Team_Name of the Team table--
SELECT"--CREATE INDEX--"
CREATE INDEX idx_new
ON Ed.Team(TeamName)
GO

-- Drop index idx_new if it exists--
SELECT"--DROP INDEX--"
DROP INDEX IF EXISTS Ed.Team.idx_new
GO
Practice the syntax