Write SQL queries for the following statements based on Employee and Department table whose schema is given…

Question

Write SQL queries for the following statements based on Employee and Department table whose schema is given below:

Employee:
(Employee_ID, Name, age, address, Phone_Number, Salary, Hire_Date, Dept_ID)

Department:
(Dept_ID, Dept_Name, Dept_Location)

Create the given tables with following constraints: Age, Hire_Date and salary in Employee table and Dept_Name and Dept_Location in Department table should not be left undefined. The values of Employee_ID in Employee table and Dept_ID in Department table should uniquely identify each row. The salary should be between 3000 to 40000 and age of every employee joining the department must be greater than 23. Finally, the Dept_ID in employee table must be only from the one’s that exist in department table.
Display the Employee_ID and name of lowest paid employees in the department, sorted in descending order based on their salaries.
Count the number of employees who belong to the same department and display Dept_ID, Dept_Name and its corresponding employees count.
Display Employee_ID of those employees who joined the department this month.
Display Dept_ID & salary in ascending order of Dept_ID and within each Dept_ID, salary should be in descending order.

Accepted Answer

Create Department table with required constraints

Dept_ID must uniquely identify each department and Dept_Name and Dept_Location cannot be NULL.

CREATE TABLE Department (
    Dept_ID INT PRIMARY KEY,
    Dept_Name VARCHAR(50) NOT NULL,
    Dept_Location VARCHAR(50) NOT NULL
);

Create Employee table with constraints

Conditions required:
• Employee_ID unique
• Age, Hire_Date, Salary cannot be NULL
• Age > 23
• Salary between 3000 and 40000
• Dept_ID must exist in Department table

CREATE TABLE Employee (
    Employee_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT NOT NULL CHECK (Age > 23),
    Address VARCHAR(100),
    Phone_Number VARCHAR(15),
    Salary INT NOT NULL CHECK (Salary BETWEEN 3000 AND 40000),
    Hire_Date DATE NOT NULL,
    Dept_ID INT,
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);

Display Employee_ID and Name of lowest paid employees in department

SELECT Employee_ID, Name
FROM Employee
WHERE Salary = (SELECT MIN(Salary) FROM Employee)
ORDER BY Salary DESC;

Count number of employees in each department

SELECT d.Dept_ID, d.Dept_Name, COUNT(e.Employee_ID) AS Employee_Count
FROM Department d
LEFT JOIN Employee e
ON d.Dept_ID = e.Dept_ID
GROUP BY d.Dept_ID, d.Dept_Name;

Display Employee_ID of employees who joined this month

SELECT Employee_ID
FROM Employee
WHERE MONTH(Hire_Date) = MONTH(CURRENT_DATE)
AND YEAR(Hire_Date) = YEAR(CURRENT_DATE);

Display Dept_ID and Salary

Sort by:
• Dept_ID in ascending order
• Salary in descending order within each department

SELECT Dept_ID, Salary
FROM Employee
ORDER BY Dept_ID ASC, Salary DESC;

Write SQL queries for the following statements based on Employee and…