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 TABLE Employee ( Eid INT PRIMARY KEY, Ename VARCHAR(50) NOT ________ __________ _______ __________ ________ ___.
_______ ___ ___ _________ _____ ______ _________.
_______ ______ _______ ________ _____ ______ _______.
____ _______ ___ ______ __________ ____ __________ _______ __________.
_______ ____ ___ ___ ______.
_______ _____ ___ ______ ______ ___ _______ __________.
________ __________ ____ ____ _____ _______ _________.
_____ _______ _____ ______ _______ _________ ____ _________ ____.
______ _________ _____ _______ _________.
____ ______ _________ _______ ________ ________ ______ ___ ________ ______ ________ _____.
_________ _________ _____ ____ ________ ____ _______.
_______ _______ ___ ________ ____ __________.
____ _____ _______ __________ __________ ____ _____ ___ _______ _________.
______ _________ _____ ______ __________ _____ _________.
_________ __________ _____ _________ _______ _____ ________ _____ ________ ___ ____.
________ ________ __________ __________ ________.
___ _____ ________ ___ _____.
______ _________ ___ _________ _______ ______ _________ ____ ________ ____ ______.
___ ________ ______ ____ _____ ________ ________ ________ ______ _______ _____.
___ ___ _______ ___ _____ ___ ______ ____ _____ _______.
______ __________ __________ __________ _____ ______ _____.
_________ ____ ______ ________ _____ ____ ___ ___ _____.
__________ ______ ___ ____ _________.
__________ ______ _____ ______ _________ __________ __________ ________ _________ ____.
_________ ___ ________ _________ _________ ________ _______ ___ _______ ________.
_____ _______ __________ ______ _________.
_______ _________ ________ _______ ____ ___ ____ ________ ____ ________.
_______ __________ __________ _______ _________ ________ _________ ________.
______ _____ _______ ____ __________ ___ _______.
_______ ________ _______ __________ ___.
Get Full Answer on WhatsApp