Exercise_1.md

Exercise-I

Consider an Employee with a social security number (SSN) working on multiple projects with definite hours for each. Each Employee belongs to a Department. Each project is associated with some domain areas such as Database, Cloud and so on. Each Employee will be assigned to some project. Assume the attributes for Employee and Project relations.

a) Mention the constrainst neatly.

CREATE TABLE employee (
  ssn number(10) primary key,
  fname varchar(30),
  salary number(7),
  DNo number(4)
);
  
CREATE TABLE project (
  pno number(4) primary key,
  pname varchar(30),
  domain varchar(30)
);

CREATE TABLE workson (
  ssn number(10) references employee(ssn) on delete cascade,
  pno number(4) references project(pno) on delete cascade,
  hours number(3),
  primary key(ssn,pno)
);

INSERT ALL
INTO employee VALUES (123, 'John', 30000, 3)
INTO employee VALUES (321, 'Franklin', 40000, 3)
INTO employee VALUES (124, 'Alicia', 35000, 2)
INTO employee VALUES (312, 'Mark', 60000, 2)
INTO employee VALUES (122, 'Bill', 70000, 1)
INTO employee VALUES (120, 'James', 10000, 1)
SELECT * FROM DUAL;

INSERT ALL 
INTO project VALUES (1111, 'Product X', 'IoT')
INTO project VALUES (2111, 'Product Y', 'Database')
INTO project VALUES (2311, 'Product Z', 'Database')
INTO project VALUES (4411, 'Product W', 'Cloud')
INTO project VALUES (2511, 'Product V', 'AR/VR')
SELECT * FROM DUAL;

INSERT ALL
INTO workson VALUES (123,1111,10)
INTO workson VALUES (321,1111,20)
INTO workson VALUES (123,2111,18)
INTO workson VALUES (124,2311,30)
INTO workson VALUES (120,4411,55)
INTO workson VALUES (122,1111,10)
INTO workson VALUES (124,1111,20)
INTO workson VALUES (321,2111,18)
INTO workson VALUES (312,2311,30)
INTO workson VALUES (321,4411,55)
INTO workson VALUES (312,2511,4)
SELECT * FROM DUAL;

b) Design the ER diagram for the problem statement.

c) State the schema diagram for the ER diagram.

d) Create the tables, insert suitable tuples (min 6 each) and perform the following operations in SQL

1. Obtain the details of employees assigned to “Database” project.

SELECT *
FROM employee
WHERE ssn IN (SELECT ssn
              FROM workson
              WHERE pno IN (SELECT pno
                            FROM project
                            WHERE domain = 'Database' ));

2. Find the number of employees working in each department with department details.

SELECT DNo, count(ssn) as 'No. of Employees'
FROM employee
GROUP BY DNo;

3. Update the Project details of Employee bearing SSN = #SSN to ProjectNo = #Project_No and display the same.

UPDATE workson
SET pno = 2311
WHERE ssn = 120;

SELECT *
FROM workson 
WHERE ssn = 120;

4. Retrieve the employee who has not been assigned more than two projects.

SELECT ssn, count(pno) as 'Projects Assigned'
FROM workson
GROUP BY ssn
HAVING count(pno)<=2;

e) Create the table, insert suitable tuples and perform the following operations using MongoDB

db.createCollection('employee')
db.employee.insertMany([
    {'SSN': 123, 'Name' :'John', 'DName' : 'Research', 'PNo':1111},
    {'SSN': 321, 'Name' :'Franklin', 'DName' : 'Research', 'PNo':1115},
    {'SSN': 124, 'Name' :'Alicia', 'DName' : 'Admin', 'PNo':5111},
    {'SSN': 312, 'Name' :'Mark', 'DName' : 'Admin', 'PNo':4511},
    {'SSN': 122, 'Name' :'Bill', 'DName' : 'Finance', 'PNo':3111},
    {'SSN': 120, 'Name' :'James', 'DName' : 'Finance', 'PNo':2111}
    ])

1. List all the employees of Department named #Dept_name.

db.employee.find({'DName':'Finance'}).pretty()

2. Name the employees working on Project Number :#Project_No

db.employee.find({'PNo':1111}).pretty()

f) Write a program that gives all employees in Department #number a 15% pay increase. Display a message displaying how many employees were awarded the increase.

1. Create the PL/SQL file - file1.sql using the command edit file1.sql

BEGIN
    update employee
    set salary = salary + (0.15*salary)
    where dno = 2;
    dbms_output.put_line('Number of employess awarded hike are :' || sql%rowcount);
END;
/

2. Commands to execute the PL/SQL file -

set serveroutput on;
@file1.sql
plsql execution completed successfully
Number of employess awarded hike are : 2

SELECT ssn, salary, DNo
FROM employee;