Consider the following relational schema: Employee (Eid,

Question

Consider the following relational schema:

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.

18 Apr 2025

Answer :

Word Count : 278

(a) Create Tables Using SQL:

 -- Create the Department table CREATE TABLE Department ( DeptCode INT PRIMARY KEY, DeptName VARCHAR(100), DeptLocation VARCHAR(100) ); -- Create the Employee table with Foreign Key constraint CREATE TABLE Employee ( Eid INT PRIMARY KEY, Ename VARCHAR(100), DeptCode INT, DateofAppointment  ________ ______ _________ _________ ____ ________ ______ ____ _____.
 _________ ___ ________ _______ ________ _______ ______ _______.
 ______ ____ ___ ___ _____ ____.
 ___ _____ _______ ________ _______ _______ __________ _________ __________ _______ _____.
 ___ ___ __________ ____ ___ _________ _______ _________ ________ ____ _____ ________.
 ______ _________ ___ __________ __________ ____ _________ _______ ________.
 ___ __________ ____ _____ ______ ___ ____ ________ _____ ___ _______.
 _____ _____ ______ _________ _________ ___ ___ ________ __________ ______.
 ___ __________ __________ ______ __________ ___ __________.
 ___ ___ ______ ___ __________ __________ __________ __________ ____ __________.
 ___ _____ ______ __________ ___ ____ __________ ________ ______ ____ ____ ___.
 _____ ___ ____ ____ ______.
 ________ __________ _______ ____ ___ _______ ___ ___ ___ _________ _________.
 __________ _________ __________ _____ _______ _______ _______.
 ___ ______ _______ ______ ______.
 _________ ____ _________ ___ _________ _________ _________ ________ ______ ________ ________.
 ______ __________ _____ __________ ________ ________ ________ ___ ____ _____ _____.
 _____ ___ ______ ____ ______ ____ __________.
 __________ _____ ______ _______ _____.
 ________ ____ ____ ___ _______ ________ _____ __________ ______ __________ ________ _____.
 ______ _____ ___ ______ _____ __________ ________ _____ ____ _________.
 _____ _______ __________ _______ ____ __________ _______ _______ _______ __________ _______.
 ______ ____ ___ _______ ___.
 __________ _______ _______ _______ ____ _________ _______ _______ __________.
 _______ _______ _______ ________ _______.
 ____ _________ _____ ____ _____.
 _________ ___ ______ _____ ______ ____.
 __________ _____.
Get Full Answer on WhatsApp

IGNOU Solved Assignments Assignments Ignou Projects Project Previous Year Solved Exam Papers Notes Notes Sample Question Papers Sample Question Papers

IGNOU NEWS

Assignment Submission Last Date Extended Till 30 June 2026 Click Here★★★IGNOU June 2026 TEE Date Sheet Released Click Here★★★