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.
(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) ___ __________ ______ ____ ________ __________ __________ _________ _____ ___.
________ __________ ______ __________ _____ ________ _________ ______ _____ ________ __________ _______.
__________ __________ _______ _____ __________ ________ ________ ____ _________ ______ ____.
_________ ___ _________ _________ _____.
______ _________ _________ _____ ___ _____ _______ _________ _______ __________ ____ _________.
_________ ___ ____ _______ _________ _______ ________ _______ ______ _____.
______ ___ ____ _________ _______.
______ __________ ____ ______ _______.
______ _________ _________ __________ ______ _______ _______.
__________ _________ ______ _________ ________ ________ ___.
__________ _____ _____ _______ ____ ____ _____.
______ ____ _________ _______ __________.
________ ____ ________ _________ ________ ________.
_________ _________ _________ ________ _____ ______ _______ ___ ____.
_______ __________ __________ ________ __________.
_________ _______ _____ ____ ____.
___ _____ ________ __________ _________ _________ ______ _____ ________ ________.
_______ ___ __________ _______ ___.
______ __________ _______ _____ ________ ____ __________ ______.
_________ ___ ___ ___ __________ ___ ____ __________ ______.
_______ ___ ____ ____ _____.
__________ ________ ____ __________ _________ _________ _______ ___ _____ _____ __________ _________.
_______ ____ ______.
- Solved Assignments
- Study Notes
- Guess Papers
- Question Papers
✓ Unlock up to 60% of the answer
✓ No payment required