Name – Sammed Vijay Biraje Database Technology
HRMS Assignment Batch – B
Q.1 Create a query to display the last name and salary of employees earning
more than $12,000.
Query and Output –
select last_name, salary
from employees
where salary > 12000;
Q.2 Create a query to display the employee last name and department
number for employee number 176.
Query and Output –
select last_name, department_id
from employees
where employee_id=176;
Q.3 Display the last name and salary for all employees whose salary is not in
the range of $5,000 and $12,000.
Query and Output –
select last_name, salary
from employees
where salary not between 5000 and 12000;
Q.4 Display the employee last name, job ID, and start date of employees hired
between February 20, 1998, and May 1, 2002. Order the query in ascending order
by start date.
Query and Output –
Select last_name, job_id, hire_datefrom
employees
where hire_date between '1998-02-20' and '2002-05-01'order by
hire_date;
Q.5 Display the last name and department number of all employees in
departments20 and 50 in alphabetical order by name.
Query and Output –
select last_name, department_idfrom
employees
where department_id in (20,50)order
by first_name;
None of department has id 20,50
Q.6 list the last name and salary of employees who earn between $5,000 and
$12,000, and are in department 20 or 50. Label the columns Employee and
Monthly Salary, respectively.
Query and Output –
select last_name 'Employee', salary 'Monthly Salary'from
employees
where salary between 5000 and 12000and
department_id in (20,50);
None of department has id 20,50
Q.7 Display the last name and hire date of every employee who was hired in 1994.
Query and Output –
select last_name, hire_datefrom employees
where extract(year from hire_date)=1994;
Q.8 Display the last name and job title of all employees who do not have a
manager.
Query and Output –
select employees.last_name, jobs.job_titlefrom
employees, jobs
where jobs.job_title not like '%manager%';
Q.10 Display the last names of all employees where the third letter of the name is
an a.
Query and Output –
select last_name
from employees
where last_name like ' a%';
Q.11 Display the last name of all employees who have an a and an e in their last
name.
Query and Output –
select last_name
from employees
where last_name like '%a%e%';
Q.12 Display the last name, job, and salary for all employees whose job is
salesrepresentative or stock clerk and whose salary is not equal to $2,500,
$3,500, or
$7,000.
Query and Output –
select last_name, job_id, salaryfrom
employees
where salary not in (2500,3500,7000) and job_id in (select job_id
from jobs
where job_title in ('sales representative', 'stock clerk')
);
Q.13 display the last name, salary, and commission for all employees whose
commission amount is 20%.
a) Display the country names in region id 2.
Query and Output –
select country_name
from countries where
region_id=2;
Display the region name of region id 2.
select * from regions
where region_id = 2;
b) Display all the office addresses that are in the US.
select * from locationswhere
country_id='us';
c) Display the department names which are in location 1700.
select department_name
from departments
where location_id=1700;