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, 'HR', 'Delhi'), (2, 'Finance', 'Mumbai'), (3, 'IT', 'Delhi'), (4, 'Marketing', 'Bangalore'); -- Insert some sample data into Employee table INSERT INTO Employee (Eid, Ename, DeptCode, DateofAppointment) 
__________ _______ ____ ________ __________ ______ _____ _________ ______ ______.
___ __________ ______ ____ ________ __________ __________ _________ _____ ___.
________ __________ ______ __________ _____ ________ _________ ______ _____ ________ __________ _______.
__________ __________ _______ _____ __________ ________ ________ ____ _________ ______ ____.
_________ ___ _________ _________ _____.
______ _________ _________ _____ ___ _____ _______ _________ _______ __________ ____ _________.
_________ ___ ____ _______ _________ _______ ________ _______ ______ _____.
______ ___ ____ _________ _______.
______ __________ ____ ______ _______.
______ _________ _________ __________ ______ _______ _______.
__________ _________ ______ _________ ________ ________ ___.
__________ _____ _____ _______ ____ ____ _____.
______ ____ _________ _______ __________.
________ ____ ________ _________ ________ ________.
_________ _________ _________ ________ _____ ______ _______ ___ ____.
_______ __________ __________ ________ __________.
_________ _______ _____ ____ ____.
___ _____ ________ __________ _________ _________ ______ _____ ________ ________.
_______ ___ __________ _______ ___.
______ __________ _______ _____ ________ ____ __________ ______.
_________ ___ ___ ___ __________ ___ ____ __________ ______.
_______ ___ ____ ____ _____.
__________ ________ ____ __________ _________ _________ _______ ___ _____ _____ __________ _________.
_______ ____ ______.
IGNOU ACADEMY
Your IGNOU journey, made easier.
  • Solved Assignments
  • Study Notes
  • Guess Papers
  • Question Papers
Skip in 10
Top
📞
Call Support Instant phone assistance IGNOU BCSL-091 2025 - Consider the following relational schema: Employee (Eid, | IGNOU Academy
→
🟢
WhatsApp Chat Fast live messaging
→
Email Us Business enquiries & support
→