Task 1: Consider the following relational schema • Employee (EmpID, Name, Dob, Gender, Doj, Designation, Basic_pay, DNo); Gender must take value ‘M’ or ‘F’. • Dept (DNo, DName); • SkillSet (SkillNo, Description, Charge_Outrage); • EmpSkills (EmpID, SkillNo); • Project (PNo, PName, Start_Date, End_Date, Project_Manager_ID); Project Number must start with ‘P’. • Works (EmpID, PNo, Date_Worked_On, Intime, Outtime); Questions 1. Develop DDL to implement the above schema specifying appropriate data types for each attributes and enforcing primary key, check constraints and foreign key constraints. 2. Develop a SQL query to list the details of staff who earn the AVG basic pay of all staff. 3. Develop a SQL query to list the details of staff who have skills with a charge outrate greater than 60 per hour. 4. Develop a SQL query that will keep track of the department number, department name, the number of employees in the department and total basic pay expenditure for the department and store result in a temporary table. 5. Develop a SQL query to list the details of Depts which has more than 3 employees working in it. 6. Develop a SQL query to list the details of employees who have 2 or more skills but not more than 4

Sure, here are the SQL queries for each of the tasks:

1. DDL to implement the schema:

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100),
    Dob DATE,
    Gender CHAR(1) CHECK (Gender IN ('M', 'F')),
    Doj DATE,
    Designation VARCHAR(100),
    Basic_pay DECIMAL(10,2),
    DNo INT
);

CREATE TABLE Dept (
    DNo INT PRIMARY KEY,
    DName VARCHAR(100)
);

CREATE TABLE SkillSet (
    SkillNo INT PRIMARY KEY,
    Description VARCHAR(100),
    Charge_Outrage DECIMAL(10,2)
);

CREATE TABLE EmpSkills (
    EmpID INT,
    SkillNo INT,
    PRIMARY KEY (EmpID, SkillNo),
    FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
    FOREIGN KEY (SkillNo) REFERENCES SkillSet(SkillNo)
);

CREATE TABLE Project (
    PNo CHAR(10) PRIMARY KEY CHECK (PNo LIKE 'P%'),
    PName VARCHAR(100),
    Start_Date DATE,
    End_Date DATE,
    Project_Manager_ID INT
);

CREATE TABLE Works (
    EmpID INT,
    PNo CHAR(10),
    Date_Worked_On DATE,
    Intime TIME,
    Outtime TIME,
    PRIMARY KEY (EmpID, PNo, Date_Worked_On),
    FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
    FOREIGN KEY (PNo) REFERENCES Project(PNo)
);

2. SQL query to list the details of staff who earn the AVG basic pay of all staff:

SELECT * FROM Employee
WHERE Basic_pay = (SELECT AVG(Basic_pay) FROM Employee);

3. SQL query to list the details of staff who have skills with a charge outrate greater than 60 per hour:

SELECT E.* FROM Employee E
JOIN EmpSkills ES ON E.EmpID = ES.EmpID
JOIN SkillSet S ON ES.SkillNo = S.SkillNo
WHERE S.Charge_Outrage > 60;

4. SQL query that will keep track of the department number, department name, the number of employees in the department and total basic pay expenditure for the department and store result in a temporary table:

CREATE TEMPORARY TABLE DeptExpenditure AS
SELECT D.DNo, D.DName, COUNT(E.EmpID) AS NumEmployees, SUM(E.Basic_pay) AS TotalBasicPay
FROM Dept D
JOIN Employee E ON D.DNo = E.DNo
GROUP BY D.DNo, D.DName;

5. SQL query to list the details of Depts which has more than 3 employees working in it:

SELECT D.* FROM Dept D
JOIN Employee E ON D.DNo = E.DNo
GROUP BY D.DNo
HAVING COUNT(E.EmpID) > 3;

6. SQL query to list the details of employees who have 2 or more skills but not more than 4:

SELECT E.* FROM Employee E
JOIN EmpSkills ES ON E.EmpID = ES.EmpID
GROUP BY E.EmpID
HAVING COUNT(ES.SkillNo) BETWEEN 2 AND 4;