HR Database Exercises

1. Write a query to display the names (first_name, last_name) using

alias name “First Name", "Last Name"
Ans:
select first_name as 'First name',last_name as 'Last name'
from hr.employees;

2. Write a query to get unique department ID from employee

table Ans:select distinct department_id from hr.employees;
3. Write a query to get all employee details from the employee
table order by first name, descending
Ans:

select*from hr.employees
order by first_name desc;

4. Write a query to get the names (first_name, last_name), salary, PF of

all the employees (PF is calculated as 15% of salary)

Ans;
Select first_name,last_name,salary,(salary*15/100)as PF from
hr.employees;
5. Write a query to get the employee ID, names (first_name,
last_name), salary in ascending order of salary
Ans;
select employee_id,first_name,last_name,salary from hr.employees
order by salary;

6. Write a query to get the total salaries payable to

employees Ans:
 select sum(salary)as 'total salary' from hr.employees;

7. Write a query to get the maximum and minimum salary

from employees table
Ans:
select max(salary)as Maximum,min(salary)as Minimum from
hr.employees;
8. Write a query to get the average salary and number of employees
in the employees table
Ans:
select avg(distinct salary)as average,count(*) as 'number of
employsees' from hr.employees;
 9. Write a query to get the number of employees working with
the company
Ans:
select count(distinct employee_id)from hr.employees;

10. Write a query to get the number of jobs available in the

employees table

Ans:
select count(distinct job_id) from hr.employees;
11. Write a query get all first name from employees table in upper case

Ans:
select upper(first_name) from hr.employees;
 12. Write a query to get the first 3 characters of first name
from employees table

Ans:
select left(first_name,3) from hr.employees;
13. Write a query to get first name from employees table after
removing white spaces from both side

Ans:
select trim(first_name) from hr.employees;
14. Write a query to get the length of the employee names
(first_name, last_name) from employees table

Ans:
select length(last_name)+length(last_name) as 'name length' from
hr.employees;
15. Write a query to check if the first_name fields of the employees
table contains numbers

Ans
select *from hr.employees
where first_name regexp'[0-9]';
16. Write a query to display the name (first_name, last_name) and salary
for all employees whose salary is not in the range $10,000 through
$15,000

Ans:
select first_name,last_name,salary from hr.employees
where salary<10000 or salary >15000;
17. Write a query to display the name (first_name, last_name) and
department ID of all employees in departments 30 or 100 in
ascending order

Ans:
select first_name,last_name,department_id from hr.employees
where department_id in (30,100)
order by department_id ;
18. Write a query to display the name (first_name, last_name) and salary
for all employees whose salary is not in the range $10,000 through
$15,000 and are in department 30 or 100

Ans:
select first_name,last_name,salary from hr.employees
where department_id in (30,100) and (salary<10000 or salary>15000);
19. Write a query to display the name (first_name, last_name) and hire
date for all employees who were hired in 1987

Ans:
select first_name,last_name,hire_date from hr.employees
where hire_date like '1987%';
20. Write a query to display the first_name of all employees who have
both "b" and "c" in their first name

Ans:
select first_name from hr.employees
where first_name like '%b%'and first_name like '%c%' ;
21. Write a query to display the last name, job, and salary for all
employees whose job is that of a Programmer or a Shipping Clerk, and
whose salary is not equal to $4,500, $10,000, or $15,000

Ans:
select last_name,job_id,salary from hr.employees
where job_id in ('IT_PROG','SH_CLERK') and salary not in
(4500,10000,15000);
22. Write a query to display the last name of employees whose names
have exactly 6 characters

Ans:
select last_name from hr.employees
where length(last_name)=6;
23. Write a query to display the last name of employees having 'e' as
the third character

Ans:
select last_name from
hr.employees where last_name like
' e%';
24. Write a query to get the job_id and related employee's id
Partial Employee
output of s ID
the query
: job_id
AC_ACCO 206
UNT
AC_MGR 205
AD_ASST 200
AD_PRES 100
AD_VP 101 ,102
FI_ACCO 110 ,113
UNT ,111 ,109
,112

Ans:
select job_id,group_concat(employee_id) as 'Employees ID'
from hr.employees
group by job_id;

25 .Write a query to update the portion of the phone_number in the

employees table, within the phone number the substring '124' will
be replaced by '999'

Ans:
UPDATE hr.employees
 SET phone_number = REPLACE(phone_number, '124',
'999') WHERE phone_number LIKE '%124%';

26. Write a query to get the details of the employees where the length
of the first name greater than or equal to 8

Ans:
select first_name from hr.employees
where length(first_name)=8 or length(first_name)>8;
27. Write a query to append '@example.com' to email field

Ans:
UPDATE employees SET email = CONCAT(email, '@example.com')
select email from hr.employees;
28. Write a query to extract the last 4 character of phone
numbers Ans:
select right(phone_number,4)as 'Phone Number' from hr.employees;
29. Write a query to get the last word of the street address

Ans:
SELECT location_id, street_address,
SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(street_address,',','
'),')',' '),'(',' '),' ',-1)
AS 'Last word-of street address'
FROM hr.locations;

30. Write a query to get the locations that have minimum street

length Ans:
SELECT * FROM hr.locations
WHERE LENGTH(street_address) <= (SELECT
MIN(LENGTH(street_address))
FROM hr.locations);
31. Write a query to display the first word from those job titles
which contains more than one words

Ans:
SELECT job_title, SUBSTR(job_title,1, INSTR(job_title, ' ')-1)
FROM hr.jobs;
32. Write a query to display the length of first name for employees
where last name contain character 'c' after 2nd position

Ans:
SELECT first_name, last_name,length(first_name) FROM hr.employees
WHERE INSTR(last_name,'C')> 2;
33. Write a query that displays the first name and the length of the first
name for all employees whose name starts with the letters 'A', 'J' or 'M'.
Give each column an appropriate label. Sort the results by the
employees' first names

Ans:
SELECT first_name,length(first_name) FROM hr.employees
where (first_name like 'A%') or (first_name like'j%') or (first_name like
'm%')
order by first_name;
34. Write a query to display the first name and salary for all employees.
Format the salary to be 10 characters long, left-padded with the $
symbol. Label the column SALARY

Ans:
SELECT first_name,
LPAD(salary, 10, '$')
SALARY
FROM hr.employees;
35. Write a query to display the first eight characters of the employees'
first names and indicates the amounts of their salaries with '$' sign. Each
'$' sign signifies a thousand dollars. Sort the data in descending order of
salary.

Ans:
SELECT left(first_name, 8),
REPEAT('$', FLOOR(salary/1000))
'SALARY($)', salary
FROM hr.employees
ORDER BY salary
DESC;
36. Write a query to display the employees with their code, first name,
last name and hire date who hired either on seventh day of any month or
seventh month in any year

Ans:
SELECT employee_id,first_name,last_name,hire_date
FROM hr.employees
WHERE POSITION("07" IN DATE_FORMAT(hire_date, '%d %m %Y'))>0;
 Northwind Database Exercises

1. Write a query to get Product name and

quantity/unit Ans:
SELECT ProductName, QuantityPerUnit
FROM northwind.products;
2. Write a query to get current Product list (Product ID and
name) Ans:
SELECT ProductID,ProductName
FROM northwind.products;

3. Write a query to get discontinued Product list (Product ID and

name) Ans:
SELECT ProductID, ProductName
FROM northwind.products
 WHERE Discontinued = 1;

4. Write a query to get most expense and least expensive Product

list (name and unit price)
Ans:
SELECT ProductName,UnitPrice
FROM northwind.products
order by UnitPrice;
5. Write a query to get Product list (id, name, unit price) where
current products cost less than $20
Ans:
SELECT ProductID,ProductName,UnitPrice
FROM northwind.products
where UnitPrice<20 and Discontinued = 0;
6. Write a query to get Product list (id, name, unit price) where
products cost between $15 and $25
Ans:
SELECT ProductID,ProductName,UnitPrice
FROM northwind.products
where UnitPrice>15 and UnitPrice<25;
7. Write a query to get Product list (name, unit price) of above
average price
Ans:
SELECT distinct ProductName, UnitPrice
FROM northwind.products
WHERE UnitPrice > (SELECT avg(UnitPrice) FROM
northwind.products);

8. Write a query to get Product list (name, unit price) of ten most
expensive products
 Ans:
SELECT distinct ProductName, UnitPrice
FROM northwind.products
order by UnitPrice desc
limit 10;

9. Write a query to count current and discontinued

products Ans:
SELECT Discontinued,count(*) as 'count current and discontinued
products'
FROM northwind.products
group by Discontinued;
10. Write a query to get Product list (name, units on order , units in
stock) of stock is less than the quantity on order
Ans:
select productname,UnitsInStock,UnitsOnOrder from
northwind.products
where UnitsInStock< UnitsOnOrder;