EMPLOYEE (ENO, NAME, GENDER, DOB, DOJ, DESIGNATION, BASIC, DEPT_NO, PAN, SENO)
Implement a Check Constraint for GENDER
PAN - Permanent account Number
SENO - Supervisor Employee Number
DEPARTMENT (DEPT NO, NAME, MENO)
MENO - Manager Employee Number
PROJECT (PROJ NO, NAME, DEPT_NO)
WORKSFOR (ENO, PROJ NO, DATE WORKED, HOURS)
Each department has a manager managing it. There are also supervisors in each
department who supervise a set of employees. A department can control any number of
projects. But only one department can control a project. An employee can work on
any number of distinct projects on a given day. The Primary Key of each relation is
underlined.
a. Develop DDL to implement the above Schema specifying appropriate data types for
each attribute enforcing primary key, check constraints and foreign key
constraints.
b. Populate the database with a rich data set.
c. Develop an SQL query to list the departments and the details of manager in each
department.
d. Develop an SQL query to list details of all employees and the details of their
supervisors.
e. Develop an SQL query to list the department number, department name and the
number of employees in each department.
f. Develop a SQL query to list the details of employees who earn less than the
average basic pay of all employees.
g. Develop a SQL query to list the details of departments which has more than six
employees working in it.
h. Create a view that will keep track of the department number, department name,
number of employees in the department, and total basic pay expenditure for each
department.
i. Develop a database trigger that will not permit an employee to work on more than
three projects on a day.
j. Develop a procedure INCR that will accept employee number and increment amount
as input and update the basic pay of the employee in the employee table. Include
exception in the procedure that will display a message 'Employee has basic pay
null' if the basic pay of the employee is null and display a message 'No such
employee number' if the employee number does not exist in the employee table.
POSTGRES:
a. Develop DDL to implement the above Schema specifying appropriate data types for
each attribute enforcing primary key, check constraints and foreign key
constraints.
CREATE TABLE EMPLOYEE (
ENO INT PRIMARY KEY,
NAME VARCHAR(255),
GENDER CHAR(1) CHECK (GENDER IN ('M', 'F')),
DOB DATE,
DOJ DATE,
DESIGNATION VARCHAR(255),
BASIC DECIMAL(10, 2),
DEPT_NO INT,
PAN VARCHAR(20),
SENO INT,
FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO),
FOREIGN KEY (SENO) REFERENCES EMPLOYEE(ENO)
);
CREATE TABLE DEPARTMENT (
DEPT_NO INT PRIMARY KEY,
NAME VARCHAR(255),
MENO INT,
FOREIGN KEY (MENO) REFERENCES EMPLOYEE(ENO)
);
CREATE TABLE PROJECT (
PROJ_NO INT PRIMARY KEY,
NAME VARCHAR(255),
DEPT_NO INT,
FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
);
CREATE TABLE WORKSFOR (
ENO INT REFERENCES EMPLOYEE(ENO),
PROJ_NO INT REFERENCES PROJECT(PROJ_NO),
DATE_WORKED DATE,
HOURS INT,
PRIMARY KEY (ENO, PROJ_NO, DATE_WORKED)
);
b. Populate the database with a rich data set.
-- Insert data into DEPARTMENT table
INSERT INTO DEPARTMENT VALUES (1, 'HR', 101);
INSERT INTO DEPARTMENT VALUES (2, 'IT', 102);
-- Insert data into EMPLOYEE table
INSERT INTO EMPLOYEE VALUES (101, 'John Doe', 'M', '1990-01-15', '2015-05-01',
'Manager', 80000.00, 1, 'ABC123', NULL);
INSERT INTO EMPLOYEE VALUES (102, 'Jane Smith', 'F', '1985-08-22', '2018-03-10',
'Developer', 60000.00, 2, 'XYZ789', 101);
-- Insert data into PROJECT table
INSERT INTO PROJECT VALUES (201, 'ProjectA', 1);
INSERT INTO PROJECT VALUES (202, 'ProjectB', 2);
-- Insert data into WORKSFOR table
INSERT INTO WORKSFOR VALUES (101, 201, '2024-01-10', 8);
INSERT INTO WORKSFOR VALUES (102, 201, '2024-01-10', 7);
c. Develop an SQL query to list the departments and the details of manager in each
department.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, M.ENO AS MANAGER_ENO, M.NAME AS
MANAGER_NAME
FROM DEPARTMENT D
JOIN EMPLOYEE M ON D.MENO = M.ENO;
d. Develop an SQL query to list details of all employees and the details of their
supervisors.
SELECT E.ENO, E.NAME, E.DESIGNATION, E.SENO AS SUPERVISOR_ENO, S.NAME AS
SUPERVISOR_NAME
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.SENO = S.ENO;
e. Develop an SQL query to list the department number, department name and the
number of employees in each department.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME;
f. Develop a SQL query to list the details of employees who earn less than the
average basic pay of all employees.
SELECT *
FROM EMPLOYEE
WHERE BASIC < (SELECT AVG(BASIC) FROM EMPLOYEE);
g. Develop a SQL query to list the details of departments which has more than six
employees working in it.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME
HAVING COUNT(E.ENO) > 6;
h. Create a view that will keep track of the department number, department name,
number of employees in the department, and total basic pay expenditure for each
department.
CREATE VIEW DEPT_DETAILS AS
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES,
SUM(E.BASIC) AS TOTAL_BASIC_PAY
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME;
i. Develop a database trigger that will not permit an employee to work on more than
three projects on a day.
CREATE OR REPLACE FUNCTION check_project_limit()
RETURNS TRIGGER AS $$
BEGIN
IF (SELECT COUNT(*) FROM WORKSFOR WHERE ENO = NEW.ENO AND DATE_WORKED =
NEW.DATE_WORKED) >= 3 THEN
RAISE EXCEPTION 'An employee cannot work on more than three projects in a
day';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER project_limit_trigger
BEFORE INSERT ON WORKSFOR
FOR EACH ROW
EXECUTE FUNCTION check_project_limit();
j. Develop a procedure INCR that will accept employee number and increment amount
as input and update the basic pay of the employee in the employee table. Include
exception in the procedure that will display a message 'Employee has basic pay
null' if the basic pay of the employee is null and display a message 'No such
employee number' if the employee number does not exist in the employee table.
CREATE OR REPLACE PROCEDURE INCR(IN emp_number INT, IN increment_amount DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
DECLARE current_basic_pay DECIMAL;
-- Check if the employee number exists
SELECT BASIC INTO current_basic_pay
FROM EMPLOYEE
WHERE ENO = emp_number;
IF current_basic_pay IS NULL THEN
RAISE EXCEPTION 'Employee has basic pay null';
END IF;
-- Update the basic pay
UPDATE EMPLOYEE
SET BASIC = current_basic_pay + increment_amount
WHERE ENO = emp_number;
COMMIT;
END;
$$;
-----------------------------------------------------------------------------------
---------------------------------------------------------------------
MYSQL:
a. Develop DDL to implement the above Schema specifying appropriate data types for
each attribute enforcing primary key, check constraints and foreign key
constraints.
CREATE TABLE EMPLOYEE (
ENO INT PRIMARY KEY,
NAME VARCHAR(255),
GENDER CHAR(1) CHECK (GENDER IN ('M', 'F')),
DOB DATE,
DOJ DATE,
DESIGNATION VARCHAR(255),
BASIC DECIMAL(10, 2),
DEPT_NO INT,
PAN VARCHAR(15) UNIQUE,
SENO INT,
FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO),
FOREIGN KEY (SENO) REFERENCES EMPLOYEE(ENO)
);
CREATE TABLE DEPARTMENT (
DEPT_NO INT PRIMARY KEY,
NAME VARCHAR(255),
MENO INT,
FOREIGN KEY (MENO) REFERENCES EMPLOYEE(ENO)
);
CREATE TABLE PROJECT (
PROJ_NO INT PRIMARY KEY,
NAME VARCHAR(255),
DEPT_NO INT,
FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
);
CREATE TABLE WORKSFOR (
ENO INT,
PROJ_NO INT,
DATE_WORKED DATE,
HOURS INT,
PRIMARY KEY (ENO, PROJ_NO, DATE_WORKED),
FOREIGN KEY (ENO) REFERENCES EMPLOYEE(ENO),
FOREIGN KEY (PROJ_NO) REFERENCES PROJECT(PROJ_NO)
);
b. Populate the database with a rich data set.
-- Insert data into EMPLOYEE, DEPARTMENT, PROJECT, and WORKSFOR tables.
-- Use appropriate values for your dataset.
INSERT INTO EMPLOYEE VALUES (1, 'John Doe', 'M', '1990-01-01', '2020-01-01',
'Manager', 50000.00, 1, 'ABC123456', NULL);
INSERT INTO EMPLOYEE VALUES (2, 'Jane Smith', 'F', '1995-02-15', '2019-05-15',
'Developer', 45000.00, 2, 'XYZ789012', 1);
INSERT INTO DEPARTMENT VALUES (1, 'HR', 1);
INSERT INTO DEPARTMENT VALUES (2, 'IT', 2);
INSERT INTO PROJECT VALUES (1, 'Project A', 1);
INSERT INTO PROJECT VALUES (2, 'Project B', 2);
INSERT INTO WORKSFOR VALUES (1, 1, '2022-02-01', 8);
INSERT INTO WORKSFOR VALUES (2, 1, '2022-02-01', 7);
c. Develop an SQL query to list the departments and the details of manager in each
department.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, E.ENO AS MANAGER_ENO, E.NAME AS
MANAGER_NAME
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.MENO = E.ENO;
d. Develop an SQL query to list details of all employees and the details of their
supervisors.
SELECT E.ENO, E.NAME, E.DESIGNATION, E.SENO AS SUPERVISOR_ENO, S.NAME AS
SUPERVISOR_NAME
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.SENO = S.ENO;
e. Develop an SQL query to list the department number, department name and the
number of employees in each department.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME;
f. Develop a SQL query to list the details of employees who earn less than the
average basic pay of all employees.
SELECT *
FROM EMPLOYEE
WHERE BASIC < (SELECT AVG(BASIC) FROM EMPLOYEE);
g. Develop a SQL query to list the details of departments which has
more than six employees working in it.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME
HAVING COUNT(E.ENO) > 6;
h. Create a view that will keep track of the department number, department name,
number of employees in the department, and total basic pay expenditure for each
department.
CREATE VIEW DepartmentDetails AS
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES,
SUM(E.BASIC) AS TOTAL_BASIC_PAY
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME;
i. Develop a database trigger that will not permit an employee to work on more than
three projects on a day.
DELIMITER //
CREATE TRIGGER check_worksfor_limit
BEFORE INSERT ON WORKSFOR
FOR EACH ROW
BEGIN
DECLARE worksfor_count INT;
SELECT COUNT(*) INTO worksfor_count
FROM WORKSFOR
WHERE ENO = NEW.ENO AND DATE_WORKED = NEW.DATE_WORKED;
IF worksfor_count >= 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An employee cannot work on more than three projects in
a day.';
END IF;
END //
DELIMITER ;
j. Develop a procedure INCR that will accept employee number and increment amount
as input and update the basic pay of the employee in the employee table. Include
exception in the procedure that will display a message 'Employee has basic pay
null' if the basic pay of the employee is null and display a message 'No such
employee number' if the employee number does not exist in the employee table.
DELIMITER //
CREATE PROCEDURE INCR(IN p_eno INT, IN p_increment_amount DECIMAL(10, 2))
BEGIN
DECLARE v_basic_pay DECIMAL(10, 2);
-- Check if employee number exists
SELECT BASIC INTO v_basic_pay
FROM EMPLOYEE
WHERE ENO = p_eno;
IF v_basic_pay IS NOT NULL THEN
-- Increment the basic pay
UPDATE EMPLOYEE
SET BASIC = BASIC + p_increment_amount
WHERE ENO = p_eno;
SELECT 'Basic pay updated successfully.' AS Message;
ELSE
SELECT 'No such employee number.' AS Message;
END IF;
END //
DELIMITER ;
-----------------------------------------------------------------------------------
-------------------------------------------------------------
ORACLE:
a. Develop DDL to implement the above Schema specifying appropriate data types for
each attribute enforcing primary key, check constraints and foreign key
constraints.
CREATE TABLE EMPLOYEE (
ENO INT PRIMARY KEY,
NAME VARCHAR2(50),
GENDER CHAR(1) CHECK (GENDER IN ('M', 'F')),
DOB DATE,
DOJ DATE,
DESIGNATION VARCHAR2(50),
BASIC NUMBER,
DEPT_NO INT,
PAN VARCHAR2(15),
SENO INT,
CONSTRAINT fk_dept FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO),
CONSTRAINT fk_seno FOREIGN KEY (SENO) REFERENCES EMPLOYEE(ENO)
);
CREATE TABLE DEPARTMENT (
DEPT_NO INT PRIMARY KEY,
NAME VARCHAR2(50),
MENO INT,
CONSTRAINT fk_meno FOREIGN KEY (MENO) REFERENCES EMPLOYEE(ENO)
);
CREATE TABLE PROJECT (
PROJ_NO INT PRIMARY KEY,
NAME VARCHAR2(50),
DEPT_NO INT,
CONSTRAINT fk_proj_dept FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
);
CREATE TABLE WORKSFOR (
ENO INT,
PROJ_NO INT,
DATE_WORKED DATE,
HOURS NUMBER,
PRIMARY KEY (ENO, PROJ_NO, DATE_WORKED),
CONSTRAINT fk_works_emp FOREIGN KEY (ENO) REFERENCES EMPLOYEE(ENO),
CONSTRAINT fk_works_proj FOREIGN KEY (PROJ_NO) REFERENCES PROJECT(PROJ_NO)
);
b. Populate the database with a rich data set.
-- Insert data into DEPARTMENT
INSERT INTO DEPARTMENT VALUES (1, 'HR', 101);
INSERT INTO DEPARTMENT VALUES (2, 'IT', 102);
-- Add more department data as needed
-- Insert data into EMPLOYEE
INSERT INTO EMPLOYEE VALUES (101, 'John Doe', 'M', TO_DATE('1990-01-15', 'YYYY-MM-
DD'), TO_DATE('2020-01-01', 'YYYY-MM-DD'), 'Developer', 70000, 1, 'ABC123456',
NULL);
INSERT INTO EMPLOYEE VALUES (102, 'Jane Smith', 'F', TO_DATE('1985-05-20', 'YYYY-
MM-DD'), TO_DATE('2019-01-01', 'YYYY-MM-DD'), 'DBA', 75000, 2, 'XYZ789012', 101);
-- Add more employee data as needed
-- Insert data into PROJECT
INSERT INTO PROJECT VALUES (1, 'Project A', 1);
INSERT INTO PROJECT VALUES (2, 'Project B', 2);
-- Add more project data as needed
-- Insert data into WORKSFOR
INSERT INTO WORKSFOR VALUES (101, 1, TO_DATE('2024-01-10', 'YYYY-MM-DD'), 8);
INSERT INTO WORKSFOR VALUES (102, 1, TO_DATE('2024-01-10', 'YYYY-MM-DD'), 7);
-- Add more worksfor data as needed
c. Develop an SQL query to list the departments and the details of manager in each
department.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, M.ENO AS MANAGER_ENO, M.NAME AS
MANAGER_NAME
FROM DEPARTMENT D
JOIN EMPLOYEE M ON D.MENO = M.ENO;
d. Develop an SQL query to list details of all employees and the details of their
supervisors.
SELECT E.ENO, E.NAME, E.SENO AS SUPERVISOR_ENO, S.NAME AS SUPERVISOR_NAME
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.SENO = S.ENO;
e. Develop an SQL query to list the department number, department name and the
number of employees in each department.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME;
f. Develop a SQL query to list the details of employees who earn less than the
average basic pay of all employees.
SELECT *
FROM EMPLOYEE
WHERE BASIC < (SELECT AVG(BASIC) FROM EMPLOYEE);
g. Develop a SQL query to list the details of departments which has
more than six employees working in it.
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME
HAVING COUNT(E.ENO) > 6;
h. Create a view that will keep track of the department number, department name,
number of employees in the department, and total basic pay expenditure for each
department.
CREATE VIEW DEPT_SUMMARY AS
SELECT D.DEPT_NO, D.NAME AS DEPARTMENT_NAME, COUNT(E.ENO) AS NUM_EMPLOYEES,
SUM(E.BASIC) AS TOTAL_PAY
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_NO = E.DEPT_NO
GROUP BY D.DEPT_NO, D.NAME;
i. Develop a database trigger that will not permit an employee to work on more than
three projects on a day.
CREATE OR REPLACE TRIGGER TRG_CHECK_PROJECT_LIMIT
BEFORE INSERT OR UPDATE ON WORKSFOR
FOR EACH ROW
DECLARE
V_PROJECT_COUNT INT;
BEGIN
SELECT COUNT(*)
INTO V_PROJECT_COUNT
FROM WORKSFOR
WHERE ENO = :NEW.ENO AND DATE_WORKED = :NEW.DATE_WORKED;
IF V_PROJECT_COUNT >= 3 THEN
RAISE_APPLICATION_ERROR(-20001, 'Employee cannot work on more than three
projects in a day.');
END IF;
END;
/
j. Develop a procedure INCR that will accept employee number and increment amount
as input and update the basic pay of the employee in the employee table. Include
exception in the procedure that will display a message 'Employee has basic pay
null' if the basic pay of the employee is null and display a message 'No such
employee number' if the employee number does not exist in the employee table.
CREATE OR REPLACE PROCEDURE INCR (
P_ENO INT,
P_INCREMENT_AMOUNT NUMBER
) AS
V_BASIC_PAY NUMBER;
BEGIN
-- Check if employee number exists
SELECT BASIC
INTO V_BASIC_PAY
FROM EMPLOYEE
WHERE ENO = P_ENO;
IF V_BASIC_PAY IS NULL THEN
-- Display message if basic pay is null
DBMS_OUTPUT.PUT_LINE('Employee has basic pay null');
ELSE
-- Increment basic pay and update the table
UPDATE EMPLOYEE
SET BASIC = BASIC + P_INCREMENT_AMOUNT
WHERE ENO = P_ENO;
DBMS_OUTPUT.PUT_LINE('Basic pay updated successfully.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Display message if no such employee number
DBMS_OUTPUT.PUT_LINE('No such employee number');
WHEN OTHERS THEN
-- Handle other exceptions
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END INCR;
/
-----------------------------------------------------------------------------------
-------------------------------------------------------------------
MONGO DB:
a. Develop DDL to implement the above Schema specifying appropriate data types for
each attribute enforcing primary key, check constraints and foreign key
constraints.
// EMPLOYEE Collection
db.createCollection("employee", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["eno", "name", "gender", "dob", "doj", "designation", "basic",
"dept_no", "pan", "seno"],
properties: {
eno: { bsonType: "int" },
name: { bsonType: "string" },
gender: { bsonType: "string", enum: ["M", "F"] },
dob: { bsonType: "date" },
doj: { bsonType: "date" },
designation: { bsonType: "string" },
basic: { bsonType: "decimal" },
dept_no: { bsonType: "int" },
pan: { bsonType: "string" },
seno: { bsonType: "int" }
},
check: {
gender: { $in: ["M", "F"] },
seno: { $exists: true },
$or: [
{ $expr: { $eq: [{ $ifNull: ["$basic", null] }, null] } },
{ $expr: { $gte: [{ $ifNull: ["$basic", null] }, 0] } }
]
}
}
}
});
// DEPARTMENT Collection
db.createCollection("department", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["dept_no", "name", "meno"],
properties: {
dept_no: { bsonType: "int" },
name: { bsonType: "string" },
meno: { bsonType: "int" }
}
}
}
});
// PROJECT Collection
db.createCollection("project", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["proj_no", "name", "dept_no"],
properties: {
proj_no: { bsonType: "int" },
name: { bsonType: "string" },
dept_no: { bsonType: "int" }
}
}
}
});
// WORKSFOR Collection
db.createCollection("worksfor", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["eno", "proj_no", "date_worked", "hours"],
properties: {
eno: { bsonType: "int" },
proj_no: { bsonType: "int" },
date_worked: { bsonType: "date" },
hours: { bsonType: "int" }
}
}
}
});
b. Populate the database with a rich data set.
// Insert values into the "employee" collection
db.employee.insertMany([
{ eno: 1, name: "John Doe", gender: "M", dob: new Date("1990-01-01"), doj: new
Date("2010-01-01"), designation: "Developer", basic: 60000.00, dept_no: 101, pan:
"ABCDE1234F", seno: 101 },
{ eno: 2, name: "Jane Smith", gender: "F", dob: new Date("1992-05-15"), doj: new
Date("2012-03-15"), designation: "Manager", basic: 80000.00, dept_no: 102, pan:
"XYZAB5678H", seno: 102 },
// Add more employee records as needed
]);
// Insert values into the "department" collection
db.department.insertMany([
{ dept_no: 101, name: "Engineering", meno: 102 },
{ dept_no: 102, name: "Management", meno: 103 },
// Add more department records as needed
]);
// Insert values into the "project" collection
db.project.insertMany([
{ proj_no: 201, name: "Software Development", dept_no: 101 },
{ proj_no: 202, name: "Marketing Campaign", dept_no: 102 },
// Add more project records as needed
]);
// Insert values into the "worksfor" collection
db.worksfor.insertMany([
{ eno: 1, proj_no: 201, date_worked: new Date("2022-01-15"), hours: 8 },
{ eno: 2, proj_no: 202, date_worked: new Date("2022-01-15"), hours: 6 },
// Add more worksfor records as needed
]);
c. Develop an SQL query to list the departments and the details of manager in each
department.
db.department.aggregate([
{
$lookup: {
from: "employee",
localField: "meno",
foreignField: "eno",
as: "manager"
}
},
{
$project: {
_id: 0,
dept_no: 1,
name: 1,
manager: {
$arrayElemAt: ["$manager", 0]
}
}
}
]);
d. Develop an SQL query to list details of all employees and the details of their
supervisors.
db.employee.aggregate([
{
$lookup: {
from: "employee",
localField: "seno",
foreignField: "eno",
as: "supervisor"
}
},
{
$project: {
_id: 0,
eno: 1,
name: 1,
designation: 1,
supervisor: {
$arrayElemAt: ["$supervisor", 0]
}
}
}
]);
e. Develop an SQL query to list the department number, department name and the
number of employees in each department.
db.employee.aggregate([
{
$group: {
_id: "$dept_no",
num_employees: { $sum: 1 }
}
},
{
$lookup: {
from: "department",
localField: "_id",
foreignField: "dept_no",
as: "department"
}
},
{
$project: {
_id: 0,
dept_no: "$_id",
department_name: { $arrayElemAt: ["$department.name", 0] },
num_employees: 1
}
}
]);
f. Develop a SQL query to list the details of employees who earn less than the
average basic pay of all employees.
var averageBasicPay = db.employee.aggregate([
{
$group: {
_id: null,
avg_basic_pay: { $avg: "$basic" }
}
}
]).next().avg_basic_pay;
db.employee.find({ basic: { $lt: averageBasicPay } });
g. Develop a SQL query to list the details of departments which has
more than six employees working in it.
db.employee.aggregate([
{
$group: {
_id: "$dept_no",
num_employees: { $sum: 1 }
}
},
{
$match: {
num_employees: { $gt: 6 }
}
},
{
$lookup: {
from: "department",
localField: "_id",
foreignField: "dept_no",
as: "department"
}
},
{
$project: {
_id: 0,
dept_no: "$_id",
department_name: { $arrayElemAt: ["$department.name", 0] },
num_employees: 1
}
}
]);
h. Create a view that will keep track of the department number, department name,
number of employees in the department, and total basic pay expenditure for each
department.
db.createView("department_view", "employee", [
{
$group: {
_id: "$dept_no",
num_employees: { $sum: 1 },
total_basic_pay: { $sum: "$basic" }
}
},
{
$lookup: {
from: "department",
localField: "_id",
foreignField: "dept_no",
as: "department"
}
},
{
$project: {
_id: 0,
dept_no: "$_id",
department_name: { $arrayElemAt: ["$department.name", 0] },
num_employees: 1,
total_basic_pay: 1
}
}
]);
j. Develop a procedure INCR that will accept employee number and increment amount
as input and update the basic pay of the employee in the employee table. Include
exception in the procedure that will display a message 'Employee has basic pay
null' if the basic pay of the employee is null and display a message 'No such
employee number' if the employee number does not exist in the employee table.
function INCR(eno, incrementAmount) {
var employee = db.employee.findOne({ eno: eno });
if (!employee) {
print("No such employee number");
return;
}
if (employee.basic === null) {
print("Employee has basic pay null");
return;
}
var updatedBasic = employee.basic + incrementAmount;
db.employee.updateOne({ eno: eno }, { $set: { basic: updatedBasic } });
print("Basic pay updated successfully");
}
// Example usage:
INCR(1, 5000);
