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.
```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 ____ ______ ____ _______ __________.
_______ _____ _______ __________ ____ ___ ________.
________ __________ __________ _______ _____ _________ _______ ___ __________ __________.
_________ _______ ______ _______ _________ ____ ___ _____ _______ __________ ___ _________.
_________ _____ __________ _____ _______ __________ ___ ________.
__________ ___ ________ ___ _____ ___ _______ _____ ______.
__________ ______ ___ ________ ___.
____ ______ _______ ___ ____ ______ ___.
__________ __________ __________ _______ _________ ____ ___ _______ ___.
__________ ________ _______ _____ ______.
__________ ___ ___ ____ _________.
__________ _________ _______ __________ _________ ________ _______ __________ ________ ____ ____ __________.
__________ ________ ___ _________ _____.
_______ _______ ____ __________ _____ ____ _____ ___ _____ _______ _______ __________.
________ ______ ___ _________ ____ _______ ______ ________.
__________ __________ __________ _________ ________ ________ ________ _____ _________ _______.
_______ ________ ________ _____ ___ _____.
__________ ______ __________ _____ _______ _________ _________ ________ ______ __________ _________ ________.
________ ____ ___ ______ _______ ________ __________ ___ ___ _____.
_________ __________ _______ ______ ________ ________ ______.
______ _________ _______ ___ _______ __________ ______ _________ _______ _________ ___.
____ _________ ____ _______ ____ __________.
____ ___ ________ ___ _________ ____ _______.
_____ ______ ________ ____ _____ _____ _______ ___ _____.
_____ ________ ___ _______ ___ _________ __________.
______ ___ __________ _____ _______ _________ ______ ________ _____ ______.
__________ ________ _____ ________ _________ ____ __________ __________.
____ _________ __________ _______ ____ _______ ___ _________ _______ ________ ______ ______.
__________ _______ ___ ________ ______ _______ ________ ___.
___ _________.
Get Full Answer on WhatsApp