A] (Employee Schema) Create following tables in MYSQL O Employee( Emp_id, Dept_id, Emp_fname, Emp_lname, Emp_Position, Emp_salary,Emp_JoinDate) O Dept ( Dept_id, Dept_name,Dept_location , ) O Project( Proj_id,Dept_id ,Proj_Name,Proj_Location,Proj_cost,Proj_year) Create view (simple), index, sequence and synonym based on above tables.

Note: Use referential integrity constraints while creating tables with on delete cascade options.

Steps to setup tables:

1. Creating table employees:

    create table employee(
        emp_id int primary key auto_increment,
        dept_id int,
        emp_fname varchar(255),
        emp_lname varchar(255),
        emp_position varchar(255),
        emp_salary varchar(255),
        emp_join_date varchar(255)
    );
   

1. Creating Table Dept

    mysql> create table dept(
              dept_id int primary key auto_increment,
              dept_name varchar(255),
              dept_location varchar(255)
         );
    Query OK, 0 rows affected (0.02 sec)
   

1. Creating table Projects

2.  create table projects(
        proj_id int primary key auto_increment,
        dept_id int,
        proj_name varchar(255),
        proj_location varchar(255),
        proj_cost int,
        proj_year int,
        foreign key (dept_id) references dept(dept_id) on delete cascade
    );
   

1. Adding Foreign Key Constraint

    alter table employee
    add constraint fk_emp_dept 
    foreign key (dept_id) references dept(dept_id) on delete cascade;
   

2. Inserting Data

    --Insert into table dept
    insert into dept(dept_name, dept_location)
    values("Production", "Banglore");
   
    insert into dept(dept_name, dept_location)
    values("Administration", "Mumbai");
   
    insert into dept(dept_name, dept_location)
    values("Finance", "Mumbai");
   
    insert into dept(dept_name, dept_location)
    values("IT", "Pune");
   
    insert into dept(dept_name, dept_location)
    values("Marketing", "Mumbai");
   
    insert into dept(dept_name, dept_location)
    values("Bussiness", "Nashik");
   
    insert into dept(dept_name, dept_location)
    values("Sales", "Nagpur");
   

   Result:

    mysql> select * from dept;
    +---------+----------------+---------------+
    | dept_id | dept_name      | dept_location |
    +---------+----------------+---------------+
    |       1 | Production     | Banglore      |
    |       2 | Administration | Mumbai        |
    |       3 | Finance        | Mumbai        |
    |       4 | IT             | Pune          |
    |       5 | Marketing      | Mumbai        |
    |       6 | Bussiness      | Nashik        |
    |       7 | Sales          | Nagpur        |
    +---------+----------------+---------------+
    7 rows in set (0.00 sec)
   

1. Inserting Data into Employee Table

    insert into employee (
        dept_id,
        emp_fname,
        emp_lname,
        emp_position,
        emp_salary,
        emp_join_date
    )
    values 
    (1, 'Ajinkya', 'Bhosle', 'Manager', 800000, '2022-06-12'),
    (3, 'Bhushan', 'Joshi', 'Accountant', 500000, '2025-08-12'),
    (2, 'Amit', 'Kumar', 'HR Manager', 700000, '2023-02-15'),
    (4, 'Sneha', 'Verma', 'Software Engineer', 90000, '2024-11-20'),
    (5, 'Priya', 'Sharma', 'Marketing Coordinator', 600000, '2023-07-10'),
    (6, 'Rahul', 'Gupta', 'Business Analyst', 750000, '2024-04-30');
   

1. Inserting data into the projects table.

    insert into projects (
        dept_id,
        proj_name,
        proj_location,
        proj_cost,
        proj_year
    )
    values
    (1, 'Design of New Products', 'Bangalore', 75000, 2023),
    (3, 'Financial Analysis System', 'Mumbai', 40000, 2022),
    (2, 'HR Training Program', 'Mumbai', 20000, 2023),
    (4, 'IT System Upgrade', 'Pune', 60000, 2024),
    (5, 'Marketing Campaign', 'Mumbai', 35000, 2023),
    (6, 'Business Expansion Plan', 'Nashik', 80000, 2024),
    (7, 'Sales Strategy Revamp', 'Nagpur', 30000, 2023);
   

   Result:

2. 

Querying Data:

Insert at least 10 records in the Employee table and insert other tables accordingly.

1. Display all Employee details with Department 'Finance' and 'IT' and Employee first name starting with 'p' or 'h'.

    select *
    from employee
    where dept_id in (
            select dept_id
            from dept
            where dept_name = 'Finance' or dept_name = 'IT'
        )
        and emp_fname like 'p%'
        or emp_fname like 'a%';
   

   Output:

   

2. lists the number of different Employee Positions.

   Ans:

    select distinct count(emp_position)
    from employee;
   

   

3. Give 10% increase in Salary of the Employee whose Birth date before 1985.
   Note: For this query i had to modify the table by adding date of birth.

    alter table employee add column emp_dob date;
   

   

   Ans:

    update employee set emp_salary = emp_salary*0.1+emp_salary 
    where emp_dob>'2001-09-10';
   

4. Delete Department details which location is 'Banglore'.

     delete from dept where dept_location='Banglore';
   

   

   

   Note: Here employee having dept_location Banglore is also deleted as we have used on delete cascade

5. Find the names of Projects with location 'pune'

    select proj_name from projects 
    where dept_id in (select dept_id from dept where dept_location='Pune');
   

   

6. Find the project having cost in between 30000 to 50000.

    select * from projects where proj_cost>30000 and proj_cost<50000;
   

   

7. Find the project having maximum price and find average of Project cost

    select * from projects order by proj_id desc limit 1;
    -- OR WE can use  Nested Query
    select * from projects where proj_id=(select max(proj_id) from projects);
   

   Finding Average

    select avg(proj_cost) from projects;
   

   

8. Display all employees with Emp id and Emp name in decreasing order of Emp_lname

     select emp_id, emp_fname from employee order by emp_lname desc;
   

   

9. Display Proj_name, Proj_location , Proj_cost of all project started in 2024, 2023

    select proj_name, proj_location, proj_cost from projects 
    where proj_year in ('2023','2024');
   

   

   ---