Answer to Question #171897 in Databases | SQL | Oracle | MS Access for Maryam

Question

Task 1: Single Row Function

1.    Write an SQL statement that will display the Employee ID, Last name, First name and Job ID of all employees that belong to a department with Department ID 20 and whose Job ID has a substring “rep”.

2.   Write an SQL statement that will display the Employee ID, Last name, First name, Job ID and salary of “GrantDouglas”.

3.    Write an SQL statement that will display the Employee ID, Last name, First name and Job ID of all employees that belong to a department with Department ID 20 and whose remainder of their salary from 3000 is greater thn 500.

4.    Write an SQL statement that will display the Employee ID, Last name, First name and Job ID of all employees working in the company for more than 15 years.

Answer 1

1.
SELECT e.idemployee,e.Last_name,e.first_name, jd.idJob_detail
FROM employeemanagementsystem.employee as e,job_detail as jd, department as d
where d.idDepartment=20;

2.

SELECT e.idemployee,e.Last_name,e.first_name, jd.idJob_detail, s.salary
FROM employeemanagementsystem.employee as e,job_detail as jd, department as d,salary as s
where e.idemployee=s.idSalary and e.first_name like '%GrantDouglas%';

3.

SELECT e.idemployee,e.Last_name,e.first_name, jd.idJob_detail, s.salary
FROM employeemanagementsystem.employee as e,job_detail as jd, department as d,salary as s
where d.idDepartment=20;

4

SELECT e.idemployee,e.Last_name,e.first_name, jd.idJob_detail, s.salary, datediff(year,'10-03-20000',GETDATE()) as 'number of years'
FROM employeemanagementsystem.employee as e,job_detail as jd, department as d,salary as s
where d.idDepartment=20;