IGNOU BCSL 91 2025 - Employee (Eid, Ename, DeptCode, DateofAppointment) Eid is the employee

Question

Employee (Eid, Ename, DeptCode, DateofAppointment) Eid is the employeeID, which is a unique identifier assigned by the Organisation when the employee joins (Primary Key), Ename is the employee name, DeptCode identifies the department of the employee (Foreign Key), DateofAppointment is the date when the employee joined the organisation.

Department (DeptCode, DeptName, DeptLocation)- DeptCode is the code allotted to a department and is the Primary Key, DeptName is the department's name, and DeptLocation is the city where the department office is located.

(a) Create the tables using SQL from the schema above; you must include the primary key, foreign keys and constraints in your implementation. Enter a few sets of meaningful data in each table.

(b) Write and run SQL statements for the following queries:

(i) List the name of the employees appointed before 1 January 2024.

(ii) List the name of all the departments located in "Delhi".

(iii) Find the name of all the employees and the name of the department in which he/she works.

(iv) List the employee ID of all those employees who work in Delhi.

(v) Find the total number of employees in each department.

(vi) Find the total number of departments.

17 Nov 2025

Answer :

Word Count : 292

```sql -- (a) Creating the tables with constraints and inserting data CREATE TABLE Department ( DeptCode INT PRIMARY KEY, DeptName VARCHAR(50) NOT NULL, DeptLocation VARCHAR(50) NOT NULL ); INSERT INTO Department (DeptCode, DeptName, DeptLocation) VALUES (101, 'HR', 'Delhi'), (102, 'Finance', 'Mumbai'), (103, 'IT', 'Bangalore'), (104, 'Marketing', 'Delhi'); CREATE TABLE Employee ( Eid INT PRIMARY KEY, Ename VARCHAR(50) NOT NULL, DeptCode INT, DateofAppointment DATE NOT NULL, FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode) ); INSERT INTO Employee (Eid, Ename, DeptCode, DateofAppointment) VALUES (1, 'Rohit Sharma', 101, '2022-05-10'), (2, 'Anjali Verma', 102, '2024-02-15'), (3, 'Suresh Kumar', 103, '2023-11-20'), (4, 'Priya Singh', 104, '2021-08-30'); -- (b) Queries -- (i) List the name of the employees appointed

_______ ____ ___ ________ _______ _____ ______ _____ ________ ______.
__________ _____ _______ ________ _____ ___ __________.
_______ _______ _______ ___ ____.
_____ _______ _________ ________ ___ ___ _________ __________ ____ ______ __________ _______.
________ ____ _______ _____ __________ ________ ________ ____ __________ _____.
_____ _______ _____ _____ _________.
_________ _____ _________ _________ ______ _____ ____ ____.
_______ ____ _______ ______ ____ ____ ___ _________.
_______ ________ _________ ______ __________ ________ ______ ___ ____ ________ __________.
_________ ____ ______ __________ ______ _______ __________ __________ __________.
__________ ______ __________ _______ _________ ___ _____ ____.
___ _______ ______ ____ __________ _________ ______ _________ ___ ________ _________ ______.
_________ _________ _____ __________ __________ _______ ____ ____ ______ _________ __________.
___ _____ _____ ____ __________ _______ ___ ____ _____ ________ _____ _____.
______ ____ __________ _____ _______ ________.
_______ ____ ____ _________ _________ ______ ________ _________.
_________ ______ ___ __________ _____ _________ _________.
____ ____ _________ ____ _____ ___ _______ _________ ______.
____ _______ ____ ____ _____ __________ ________.
_________ _________ ________ _________ _____ ___ ______ ____ ____ ___.
_______ _______ _________ _________ _______.
_______.

IGNOU ACADEMY

Your IGNOU journey, made easier.

Solved Assignments
Study Notes
Guess Papers
Question Papers

Call Us WhatsApp

STUDY • PREPARE • SUCCEED

Skip in 10