SQL Queries - DDL Commands

DDL(Data Definition Language) Queries in MySQL

1. Program No. # 01
Object :- Write SQL Queries for DDL (Data Definition Language) Statement.
DDL Commands :-
 CREATE Query
 ALTER Query
 TRUNCATE Query
 RENAME Query
 DROP Query

1. CREATE Command :- CREATE is a DDL SQL Command used to create a table or a
database in relational database management system.

 Creating a Database:- To create a database in RDBMS, Create command is
used.Following is the syntax:-




 Example for creating Database:-



 Creating a Table:- CREATE command can also be used to create tables. Now
when we create a table. We have to specify the names and datatypes of
various columns in the Create command itself. Following is the syntax:-








CREATE DATABASE DATABASE_NAME;
CREATE DATABASE DBMS;
CREATE TABLE TABLE_NAME
( Column_name1 datatype(size),
Column_name2 datatype(size),
Column_name3 datatype(size),
Column_name4 datatype(size) );

2.  Example for creating Table:-






2. Alter Command :- ALTER command is used for altering the table structure, such as,
 To add a column to an existing table.
 To change datatype of any column or to modify its size.
 To drop a column from table.
 Adding new columns :- Using ALTER command we can add single or multiple
new columns to any existing table. Following is the syntax:-






 Here is an Example for this :-







CREATE TABLE Student (
Roll_No. INT(15),
Name CHAR(50),
Branch CHAR(25) );
ALTER TABLE table_name ADD (
Column_name1 datatype(size),
Column_name2 datatype(size),
Column_name3 datatype(size) );
ALTER TABLE Student ADD (
Father_Name CHAR(60),
Mother_Name CHAR(60),
Address CHAR(100)
);

3.  Modifying an existing column :- ALTER command can also be used to modify
datatype of any existing column. Following is the syntax:-




 Here is an Example for this:-




3. TRUNCATE Command :- TRUNCATE command removes all the records from a table,
But this command will not destroy the table’s structure. When we use TRUNCATE
command on a table its (auto-increment) primary key is also initialized. Following is
its syntax:-


 Here is an Exapmle :-


4. RENAME Query:- RENAME command is used to set a new name for any existing
table. Following is the Syntax:-


 Here is an Example :-


ALTER TABLE table_name MODIFY
Column_name datatype (Size);
ALTER TABLE STUDENT MODIFY
Address VARCHAR (200) ;
TRUNCATE TABLE table_name ;
TRUNCATE TABLE STUDENT ;
RENAME TABLE STUDENT to STUDENT_INFO ;
RENAME TABLE old_table_name to new_table_name ;

4. 5. DROP Command :- DROP command completely removes a table from the database.
This command will destroy the table structure and the data stored in it. Following is
its Syntax:-


 Here is an Example:-



DROP command can also be used to delete the complete database. Following is its
Syntax:-


 Here is an Example :-



DROP TABLE table_name ;
DROP TABLE STUDENT ;
DROP DATABASE database_name ;
DROP DATABASE DBMS;