DDL Commands:

A problem statement along with a solution for practicing DDL (Data Definition Language) commands 

Objective: To practice the use of DDL commands to create and modify database structures.

Tasks:

1. Create a database named CompanyDB.

2. Create the following tables within the CompanyDB database:

   • Employee table with the following attributes:

     • • Emp_No (integer, primary key)

       • Emp_Name (varchar, not null)

       • Designation (varchar, not null)

       • Designation_NO (integer, nullable)

       • SAL (decimal, not null)

       • COMMISSION (decimal, nullable)

     • Department table with the following attributes:

       • Dept_ID (integer, primary key)

       • Dept_Name (varchar, not null)

3. Alter the Employee table to add a foreign key constraint on Designation_NO that references the Dept_ID in the Department table.

4. Drop the COMMISSION column from the Employee table.

5. Rename the SAL column in the Employee table to Salary.

6. Drop the Department table.

Solution:

-- Step 1: Create the database

CREATE DATABASE CompanyDB;

-- Use the created database

USE CompanyDB;

-- Step 2: Create the Employee table

CREATE TABLE Employee (

    Emp_No INT PRIMARY KEY,

    Emp_Name VARCHAR(255) NOT NULL,

    Designation VARCHAR(255) NOT NULL,

    Designation_NO INT,

    SAL DECIMAL(10, 2) NOT NULL,

    COMMISSION DECIMAL(10, 2)

);

-- Create the Department table

CREATE TABLE Department (

    Dept_ID INT PRIMARY KEY,

    Dept_Name VARCHAR(255) NOT NULL

);

-- Step 3: Alter the Employee table to add a foreign key constraint

ALTER TABLE Employee

ADD CONSTRAINT FK_Designation_NO FOREIGN KEY (Designation_NO) REFERENCES Department(Dept_ID);

-- Step 4: Drop the COMMISSION column from the Employee table

ALTER TABLE Employee

DROP COLUMN COMMISSION;

-- Step 5: Rename the SAL column to Salary

ALTER TABLE Employee

CHANGE COLUMN SAL Salary DECIMAL(10, 2) NOT NULL;

-- Step 6: Drop the Department table

DROP TABLE Department;

-- Step 1: Create the database

CREATE DATABASE CompanyDB;

-- Use the created database

USE CompanyDB;

-- Step 2: Create the Employee table

CREATE TABLE Employee (

    Emp_No INT PRIMARY KEY,

    Emp_Name VARCHAR(255) NOT NULL,

    Designation VARCHAR(255) NOT NULL,

    Designation_NO INT,

    SAL DECIMAL(10, 2) NOT NULL,

    COMMISSION DECIMAL(10, 2)

);

-- Create the Department table

CREATE TABLE Department (

    Dept_ID INT PRIMARY KEY,

    Dept_Name VARCHAR(255) NOT NULL

);

-- Step 3: Insert records into Department table

INSERT INTO Department (Dept_ID, Dept_Name)

VALUES

(1, 'HR'),

(2, 'IT'),

(3, 'Sales');

-- Step 4: Insert records into Employee table

INSERT INTO Employee (Emp_No, Emp_Name, Designation, Designation_NO, SAL, COMMISSION)

VALUES

(1, 'XYZ', 'Manager', NULL, 5000.00, 1000.00),

(2, 'PQR', 'Developer', 2, 4000.00, NULL),

(3, 'ABC', 'Salesperson', 3, 3000.00, 500.00);

-- Step 5: Alter the Employee table to add a foreign key constraint

ALTER TABLE Employee

ADD CONSTRAINT FK_Designation_NO FOREIGN KEY (Designation_NO) REFERENCES Department(Dept_ID);

-- Step 6: Drop the COMMISSION column from the Employee table

ALTER TABLE Employee

DROP COLUMN COMMISSION;

-- Step 7: Rename the SAL column to Salary

ALTER TABLE Employee

CHANGE COLUMN SAL Salary DECIMAL(10, 2) NOT NULL;

-- Step 8: Drop the Department table

DROP TABLE Department;