Data Definition Language Commands in DBMS

Sanjivani Rural Education Society’s
Sanjivani College of Engineering, Kopargaon-423 603
(An Autonomous Institute, Affiliated to Savitribai Phule Pune University, Pune)
NACC ‘A’ Grade Accredited, ISO 9001:2015 Certified
Department of Computer Engineering
(NBA Accredited)
Prof. Monika Agrawal
Assistant Professor
E-mail : agrawalmonikacomp@sanjivani.org.in
Contact No: 8770361037
Course:CO210
Database Management System
Lecture-02 DDL Commands

Content
s
DEPARTMENT OF COMPUTER ENGINEERING, Sanjivani COE, Kopargaon 2
• Create Table
• Drop and Truncate Table
• Alter
• Comments
• Rename

Create Table
• A Table is a combination of rows and columns.
• For creating a table we have to define the structure of a table by adding names
to columns and providing data type and size of data to be stored in columns.
CREATE table table_name
(
Column1 datatype (size),
column2 datatype (size),
.
columnN datatype(size)
);

Contd..
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age int(2),
Phone int(10)
);
INSERT INTO Customer VALUES
(1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
(5, 'Nishant. Salchichas S.A.', 'Jain’,
'Spain','22','xxxxxxxxxx');

Create a Table Using Another Table
CREATE TABLE SubTable AS SELECT CustomerID, CustomerName FROM customer;
CREATE TABLE customer_copy AS SELECT * FROM customer;

Contd..
You can also use LIMIT to insert specific number of records from old table.
CREATE TABLE customer_copy AS SELECT * FROM customer LIMIT 3;

Drop and Truncate Table
DEPARTMENT OF COMPUTER ENGINEERING, Sanjivani COE, Kopargaon 6
• The drop table command deletes all information about the dropped relation from
the database.
Case 1: To Drop a table
DROP TABLE table_name;
Case 2: To Drop a database
DROP DATABASE database_name;
• The major difference between TRUNCATE and DROP is that truncate is used to
delete the data inside the table not the whole table.
TRUNCATE TABLE table_name;

Difference between Drop and Truncate
DROP TRUNCATE
In the drop table data and its definition is
deleted with their full structure.
It preserves the structure of the table for
further use exist but deletes all the data.
Integrity constraints get removed in the
DROP command.
Integrity constraint doesn’t get removed
in the Truncate command.
Since the structure does not exist, the
View of the table does not exist in the
Drop command.
Since the structure exists, the View of the
table exists in the Truncate command.
Drop query frees the table space
complications from memory.
This query does not free the table space
from memory.
It is slow as there are so many
complications compared to the TRUNCATE
command.
It is fast as compared to the DROP
command as there are fewer
complications.

Alter
• The alter table command is used to add attributes to an existing relation:
alter table r add A D
where A is the name of the attribute to be added to relation r and D
is the domain of A.
• All tuples in the relation are assigned null as the value for the new
attribute.
Example- ALTER TABLE Students ADD Email varchar(255);
• The alter table command can also be used to drop attributes of a relation:
alter table r drop A
where A is the name of an attribute of relation r
Example ALTER TABLE Students DROP Email;

Example:
1. To ADD 2 columns AGE and COURSE to table Student.
ALTER TABLE Student ADD (AGE number(3),COURSE varchar(40));

Contd..
2. ALTER TABLE Student MODIFY COURSE varchar(20);
3. ALTER TABLE Student DROP COURSE;

Comments
Comments can be written in the following three
formats:
1.Single-line comments
2.Multi-line comments
3.In-line comments
Single Line Comments
Comments starting and ending in a single line are considered single-line comments. A line
starting with ‘–‘ is a comment and will not be executed.
SELECT * FROM customers;
-- This is a comment that explains the purpose of the query.

Multi Line Comments
Comments starting in one line and ending in different lines are considered as multi-
line comments.
/*
This is a multi-line comment that explains
the purpose of the query below.
The query selects all the orders from the orders
table that were placed in the year 2022.
*/
SELECT * FROM orders WHERE YEAR(order_date) = 2022;

In-Line Comments
In-line comments are an extension of multi-line comments, comments can be stated
in between the statements and are enclosed in between ‘/*’ and ‘*/’.
SELECT customer_name,
/* This column contains the name of
the customer / order_date /
This column contains the date the
order was placed */ FROM orders;

ALTER (RENAME) in SQL
1. We can use ALTER TABLE to rename the name of the table.
Syntax- ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE Student RENAME TO Student_Details;
2. Columns can also be given a new name with the use of ALTER TABLE.
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE Student RENAME name TO FIRST_NAME;

SQL Create Constraints
Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is
created with the ALTER TABLE statement.
Syntax
CREATE TABLE table_name (
column1 datatype constraint,
....
);

The following constraints are commonly used in SQL:
• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

SQL NOT NULL Constraint
• By default, a column can hold NULL values.
• The NOT NULL constraint enforces a column to NOT accept NULL values.
Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use
the following SQL:
SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;

SQL UNIQUE Constraint
• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a
column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
ID int NOT NULL UNIQUE,
FirstName varchar(255),
Age int
);

SQL PRIMARY KEY Constraint
• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple
columns (fields).

SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
MySQL:
ID int NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

SQL FOREIGN KEY Constraint
• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another
table.
• The table with the foreign key is called the child table, and the table with the primary key is called the
referenced or parent table.

• The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
• The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
• The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column,
because it has to be one of the values contained in the parent table.

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(Perso
nID)
);

Data Definition Language Commands in DBMS

More Related Content

Similar to Data Definition Language Commands in DBMS

More from agrawalmonikacomp

Recently uploaded

Data Definition Language Commands in DBMS