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 DATE, FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode) ); -- Insert some sample data into Department table INSERT INTO Department (DeptCode, DeptName, DeptLocation) VALUES (1,  __________ __________ _______ ___ __________.
 ________ ________ _________ _________ _____ _____ ________.
 ______ _______ __________ ___ ________ __________ __________.
 _______ ________ _______ _______ _______ ______ ___ _______ ________.
 _______ ________ _________ _______ _____ ______ ______ ______ ___.
 _____ ______ ____ ___ __________ ________ ______ ____.
 _____ _____ ______ _________ ______ ____ _________ ______ ________ _____ ______ ______.
 ___ ______ ____ ________ ____.
 _____ _______ _________ ___ _____ ________ ___ _________ ____ ________.
 ______ _______ ________ _______ ______ _____ ________ ___ __________ ___ _______ ________.
 ____ __________ ________ ________ ________ ________ _______ _________ ____ _________ _________ ____.
 __________ __________ ___ ______ __________ ________ _____ ______ _____ _________.
 ___ _______ __________ ____ __________ _________ _______ _____ _________ _________ _________.
 ___ ______ ________ _____ _____ _______ _____.
 ____ ______ _______ ______ _______ ____ _____ _______ _____ ___.
 __________ _______ _____ ____ _____ ___ ______ ________ ______ _________ _________ ________.
 _________ _______ ______ ______ ________ _______ _____ _________ _______ __________ _______ ____.
 _____ ____ _________ _______ ___.
 ______ ________ __________ ___ _____ ________ _________ _______ ____ ________ ______ ______.
 _________ __________ ___ _____ ___ __________ ____.
 _______ __________ ___ __________ ___.
 ___ ___ ____ _________ __________ _______ _____.
 ______ ___ ________ ___ _______ ____.
 __________ ______ _______ ______ ____ _____ _____ ______ _____.
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★★★