DBMS - Assignment No 2.A

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:

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)
 );

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)

Creating table Projects

 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
 );

Adding Foreign Key Constraint

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

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)

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');

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:

Querying Data:

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

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:

lists the number of different Employee Positions.

Ans:

 select distinct count(emp_position)
 from employee;

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';

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

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');

Find the project having cost in between 30000 to 50000.

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

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;

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;
```

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');

DBMS - Assignment No 2.A

DML and DDL commands

Table of contents

Steps to setup tables:

Querying Data: