Case Study 1: Company Management System 

1. Insert Data into Department Table 

INSERT INTO Department (Dept_ID, Dept_Name)

VALUES (1, 'Sales'),

       (2, 'Marketing'),

       (3, 'Human Resources'),

       (4, 'Finance'),

       (5, 'IT');

2. Insert Data into Employee Table 

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

VALUES (101, 'John Doe', 'Manager', 1, 75000, 5000),

       (102, 'Jane Smith', 'Executive', 2, 60000, 3000),

       (103, 'Richard Roe', 'HR Officer', 3, 50000, 2000),

       (104, 'Mary Major', 'Accountant', 4, 55000, 2500),

       (105, 'James Junior', 'IT Specialist', 5, 70000, 4000);

3. Update Employee's Salary 

UPDATE Employee

SET SAL = SAL + 5000

WHERE Emp_No = 101;

4. Delete an Employee Record

DELETE FROM Employee

WHERE Emp_No = 105;

5. Select All Employees

SELECT * FROM Employee;

6. Select Employees from a Specific Department

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

FROM Employee E

JOIN Department D ON E.Designation_NO = D.Dept_ID

WHERE D.Dept_Name = 'Sales';

7. Select Employees with Commission Greater than a Certain Value

SELECT Emp_Name, SAL, COMMISSION

FROM Employee

WHERE COMMISSION > 3000;

8. Aggregate Function Example: Total Salary and Commission for Each Department 

SELECT D.Dept_Name, SUM(E.SAL) AS Total_Salary, SUM(E.COMMISSION) AS Total_Commission

FROM Employee E

JOIN Department D ON E.Designation_NO = D.Dept_ID

GROUP BY D.Dept_Name;

9. View All Designations and Their Corresponding Departments

SELECT E.Designation, D.Dept_Name

FROM Employee E

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