Step 1: Create the Department Table

The Department table will have a primary key on the Dept_ID column.

CREATE TABLE Department (

    Dept_ID INT PRIMARY KEY,

    Dept_Name VARCHAR(50)

);

Step 2: Create the Employee Table

The Employee table will have a primary key on the Emp_No column and a foreign key referencing Dept_ID in the Department table.

CREATE TABLE Employee (

    Emp_No INT PRIMARY KEY,

    Emp_Name VARCHAR(50),

    Designation VARCHAR(50),

    Designation_NO INT,

    SAL DECIMAL(10, 2),

    COMMISSION DECIMAL(10, 2),

    FOREIGN KEY (Designation_NO) REFERENCES Department(Dept_ID)

);

Step 3: Insert Data into Department Table 

INSERT INTO Department (Dept_ID, Dept_Name)

VALUES (1, 'Sales'),

       (2, 'Marketing'),

       (3, 'Human Resources'),

       (4, 'Finance'),

       (5, 'IT');

Step 4: Insert Data into Employee Table 

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

VALUES (101, 'John Doe', 'Manager', 1, 75000.00, 5000.00),

       (102, 'Jane Smith', 'Executive', 2, 60000.00, 3000.00),

       (103, 'Richard Roe', 'HR Officer', 3, 50000.00, 2000.00),

       (104, 'Mary Major', 'Accountant', 4, 55000.00, 2500.00),

       (105, 'James Junior', 'IT Specialist', 5, 70000.00, 4000.00);

 INSERT INTO Employee values (106,'ABC','IT Specialist',5,55555,333);

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Employee values (107,'PQR','IT Specialist',5,755555,7333);

Query OK, 1 row affected (0.03 sec)

Step 5: Violation Example: Inserting Invalid Data 

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

VALUES (106, 'Peter Parker', 'Web Developer', 6, 80000.00, 3000.00);

Step 6: Without using variable

select Employee.emp_name, department.Dept_name from Employee, department

    -> where Employee.Designation_NO = Department.Dept_ID AND Employee.Emp_No = 103;

+-------------+-----------------+

| emp_name    | Dept_name       |

+-------------+-----------------+

| Richard Roe | Human Resources |

+-------------+-----------------+

1 row in set (0.00 sec)

Step 7: Using variables

select E.emp_name, d.Dept_name from Employee E, department D

    -> where E.Designation_NO = D.Dept_ID AND E.Emp_No = 103;

+-------------+-----------------+

| emp_name    | Dept_name       |

+-------------+-----------------+

| Richard Roe | Human Resources |

+-------------+-----------------+

1 row in set (0.00 sec)

To display Department having Employee Count

mysql> select Dept_name, (select count(*) from employee where Designation_NO = Dept_ID) as Employee_Count     from Department;

 +-----------------+----------------+

| Dept_name              | Employee_Count |

+-----------------+----------------+

| Sales                       |              1 |

| Marketing                |              1 |

| Human Resources |              1 |

| Finance                  |              1 |

| IT                            |              3 |

+-----------------+----------------+

5 rows in set (0.00 sec)

To display Department having Maximum Employee

select Dept_name, (select count(*) from employee where Designation_NO = Dept_ID) as Employee_Count     from Department order by Employee_Count desc limit 1;

+-----------+----------------+

| Dept_name | Employee_Count |

+-----------+----------------+

| IT                   |              3 |

+-----------+----------------+

1 row in set (0.00 sec)

Step 8: (Using Join Operation)

SELECT E.Emp_Name, E.Designation, D.Dept_Name

FROM Employee E

JOIN Department D ON E.Designation_NO = D.Dept_ID;

Expected Output: