What is Data Definition Language (DDL) ?How To Use DDL In SQL SERVER | Database Engineering

Objectives: 

 To guide you through execution of basic Data Definition commands.

Description: 

SQL commands can be classified into three types. 

1. Data Definition language (DDL) 

2. Data Manipulation language (DML) 

3. Data Control language (DCL) DATA DEFINITION LANGUAGE (DDL) These commands are used to create, alter, and drop tables, views, and indexes. There may be other objects controlled by the DDL, depending on the DBMS. 

For example, many DBMSs support defining synonyms (abbreviations) for database objects or a field to hold a specified sequence of numbers (which can be helpful in assigning primary keys to rows in tables). In a production database, the ability to use DDL commands will generally be restricted to one or more database administrators in order to protect the database structure from unexpected and unapproved changes. In development or student databases, DDL privileges will be granted to more users. DDL Commands are used to define a database, including those for creating, altering, and dropping tables and establishing constraints.

 DEFINING A DATABASE IN SQ:

CREATING TABLES:

CONSTRAINTS: 

1. Primary Key 

2. Domain specifications may be stated that are more constrained than those established by data type using check clause 

3. Foreign key using The SQL REFERENCES clause 

4. DEFAULT can be used to define a value that is automatically inserted when no value is inserted during data entry.

CHANGING TABLE DEFINITIONS:

Base table definitions may be changed by using ALTER on the column specifications. The ALTER TABLE command can be used to add new columns to an existing table. Existing columns may also be altered. Table constraints may be added or dropped. The ALTER TABLE command may include keywords such as ADD, DROP, or ALTER and allow the column’s names, data type, length, and constraints to be changed.

Creating Data Integrity Controls:

Removing Tables:

 To remove a table from a database, the owner of the table may use the DROP TABLE command. Command: To drop a table from a database schema. 

This command will drop the table and save any pending changes to the database.

Here are some of the practical example we will try to solve them using SQL SERVER:

01: Perform the following: 

1. Create the “Employee_Details” table having the following attributes and datatypes: • Emp_ID (NUMBER, 7) • FIRST_NAME (VARCHAR, 25) • MIDDLE_NAME (VARCHAR, 25) • LAST_NAME (VARCHAR, 25) 

2. Create another table “Dept_Details” having the following attributes and datatypes: • Dept_ID (NUMBER, 8) • DEPT_NAME (VARCHAR, 20) 

3. Add a table level primary key constraint to the “Employee_Details” table on the ID column. The constraint should be named at creation. Name the constraint my_emp_id_pk. 

4. Create a PRIMARY KEY constraint to the “Dept_Details” table using the ID column. The constraint should be named at creation. Name the constraint my_deptid_pk. 

5. Add a column DEPT_ID to the “Employee_Detail” table. Add a foreign key reference on the “Employee_Detail” table. Name the constraint my_emp_dept_id_fk. 

6. Drop the MIDDLE_Name column from the Emp_Details table. 

7. Create another table named “Emp_Details” based on the structure of Employee_Details table. 

8. Drop the table named Employee_Details. 

9. Modify the “Emp_Details” table. Add a COMMISION column of NUMBER data type, precision 2, scale 2. Add a constraint to the commission column that ensures that a commission value is greater than zero

Part(A): 

create table Employee_Details(

 Emp_ID numeric (7),FIRST_NAME VARCHAR(25),MIDDLE_NAME VARCHAR(25),LAST_NAME VARCHAR(25) ) 

Part(B): 

create table Dept_Details( 

Dept_ID numeric (8),DEPT_NAME VARCHAR(20) ) 

OUTPUT:

 
PART(C):

create table Employee_Details( 

Emp_ID numeric (7),FIRST_NAME VARCHAR(25),MIDDLE_NAME VARCHAR(25),LAST_NAME VARCHAR(25) 

constraint my_emp_id_pk primary key (Emp_ID)

OUTPUT:

Part(D): 

create table Dept_Details( 

Dept_ID numeric (8),DEPT_NAME VARCHAR(20)

 constraint my_deptid_pk primary key (Dept_ID) )

 Part(E): 

alter table Employee_Details add Dept_ID int; 

alter table Employee_Details add constraint my_emp_dept_id_fk

 Foreign key (Dept_ID) References Dept_Details(Dept_ID) 

Part(F): 

alter table Employee_Details Drop column MIDDLE_NAME; 

Part(G): 

create table Emp_Details(

 Emp_ID int,FIRST_NAME VARCHAR(25),MIDDLE_NAME VARCHAR(25),LAST_NAME VARCHAR(25) 

constraint my_emp_id_pk primary key (Emp_ID) )

Output:

Part(H):

 drop table Employee_Details;

OUTPUT:

Part(I):

 alter table Emp_Details add Commission Numeric(2,2)

 Constraint my_emp_comm_ck CHECK (commission >= 0);

Output: