Question

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

(Employee_ID,First_Name,Last_Name,Email,Phone_Number,Hire_Date,Job_ID,Salary, Manager_Id, Department_Id)

a. Create the given table along with the following constraints: Phone_Number should not be left undefined, salary should be between 10000 and 20000, employee_id should uniquely identify rows and should not be left undefined. User-defined names should be given to constraints.

b.  Display each employee’s last name, hire date and salary review date (which is the date after six months of service). Label the column as “REVIEW”.

c.  Display the manager number and salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000 or less. Sort the output in descending order of salary.

d.  Get First_Name and Last_Name as single column from employees table separated by '_'

Answer 1

The SQL queries for all the 4 sub questions (a,b,c,d) is given below.

--Create the table with the given requirements
--Question a
create table Employees
(
        --Employee_ID is defined as NOT NULL.  It will not accept NULL values or left blank.
        Employee_ID int NOT NULL,
        First_Name varchar(50),
        Last_Name varchar(50),
        Email varchar(50),
        
        --Phone_Number is defined as NOT NULL.  It will not accept NULL values or left blank.
        Phone_Number varchar(20) NOT NULL,
        Hire_Date datetime,
        Job_ID int,
        Salary float, 
        Manager_Id int, 
        Department_Id int,

        --This is the Primary Key Constraint on the Employee_ID field.  Uniquely identifies rows.
        Constraint PK_Employee_ID PRIMARY KEY (Employee_ID),
        --This is the constraint for checking salary between the provided range.
        Constraint CHK_Salary check (Salary between 10000 and 20000)
)


--The SQL Queries follows
--Question b
select
        Last_Name,
        Hire_Date,
        dateadd(mm,6,Hire_Date) REVIEW
from
        Employees

--Question c
select
        Manager_Id, min(Salary) lowest_salary
from
        Employees
where
        Manager_Id is not null
group by
        Manager_Id
having
        min(Salary) > 6000
order by
        lowest_salary desc

--Question d
select
        First_Name + '_' + Last_Name as Full_Name
from
        Employees

The screenshots of the code and output are provided below.