[go: up one dir, main page]
Scribd
Upload
17 views19 pages

DBMS_Lab_5

The document contains a series of SQL exercises designed to query employee and department data from a database. Each exercise includes a specific task along with the corresponding SQL query to achieve the desired result. The queries cover various aspects such as employee details, department information, job history, and salary comparisons.

Uploaded by

zainab.umair
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views19 pages

DBMS_Lab_5

The document contains a series of SQL exercises designed to query employee and department data from a database. Each exercise includes a specific task along with the corresponding SQL query to achieve the desired result. The queries cover various aspects such as employee details, department information, job history, and salary comparisons.

Uploaded by

zainab.umair
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 19

Fifth Lab DBMS

Name: Majid Ali Shah


CMS_ID: 023-22-0101
Mail_Address: majidalishah.bscsf22@iba-suk.edu.pk
Exercise

1. Write a query in SQL to display the first name, last name, department number, and department name
for each employee. (Sample tables: employees & departments)

select e.first_name,e.last_name,e.department_id, d.department_name


from employees e
inner join departments d using (department_id);
2. Write a query to find the name (first_name, last_name), job, department ID and name of the department
who works in London. (Sample tables: employees , locations & departments)

select concat(e.first_name,' ',e.last_name) as 'Name',e.job_id,e.department_id, d.department_name


from employees e
inner join departments d using (department_id)
inner join locations l using (location_id)
where l.city = 'london';
3. Write a query in SQL to display the first and last name, department, city, and state province for each
employee. (Sample tables: employees , locations & departments)

select e.first_name,e.last_name, d.department_name, l.city, l.state_province


from employees e
inner join departments d using (department_id)
inner join locations l using (location_id);
4. Write a query to find the employee id, name (last_name) along with their manager_id and name
(last_name). (Sample tables: employees)

select e1.employee_id,e1.last_name,e1.manager_id,e2.last_name
from employees e1, employees e2
where e2.employee_id = e1.manager_id;
5. Write a query to find the name (first_name, last_name) and hire date of the employees who was hired
after 'Jones'. (Sample tables: employees)

select concat(e1.first_name,' ',e1.last_name) as 'Name',e1.hire_date


from employees e1, employees e2
where e1.hire_date > e2.hire_date and (e2.first_name like 'jones' or e2.last_name like 'jones');
6. Write a query to get the department name and number of employees in the department. (Sample tables:
employees & departments)

select d.department_name, count(d.department_id) as 'No of employees'


from departments d
inner join employees e using (department_id)
group by (d.department_id);
7. Write a query to display the department ID and name and first name of manager. (Sample tables:
employees & departments)

select d.department_id,d.department_name,e.first_name
from departments d
inner join employees e
where d.manager_id = e.employee_id;
8. Write a query to display the department name, manager name, and city. (Sample tables: employees ,
locations & departments)

select d.department_name,e.first_name,l.city
from departments d
inner join employees e
inner join locations l
where d.manager_id = e.employee_id and d.location_id = l.location_id;
9. Write a query to display the job history that were done by any employee who is currently drawing
more than 10000 of salary. (Sample tables: employees & job_history)

select j.*
from job_history j
inner join employees e using (employee_id)
where e.salary>10000;
10. Write a query to display the first name, last name, hire date, salary of the manager for all managers
whose experience is more than 15 years. (Sample tables: employees & departments)

select first_name,last_name,hire_date,salary
from departments d
inner join employees e ON (d.manager_id = e.employee_id)
where (datediff(now(),e.hire_date))/365 > 15;
11. Write a query in SQL to display the name of the department, average salary and number of employees
working in that department who got commission. (Sample tables: employees & departments)

select d.department_name,avg(salary), count(e.commission_pct) as 'No of employees'


from departments d
inner join employees e using (department_id)
group by (d.department_id);
12. Write a query in SQL to display the name of the country, city, and the departments which are running
there. (Sample tables: countries , locations & departments)

select country_name, city, department_name


from countries c
inner join locations l using (country_id)
inner join departments d using (location_id);
13. Write a query in SQL to display department name and the full name (first and last name) of the
manager. (Sample tables: employees & departments)

select department_name,concat(e.first_name,' ',e.last_name) as 'Full name'


from departments d
inner join employees e
where d.manager_id = e.employee_id;
14. Write a query in SQL to display the details of jobs which was done by any of the employees who is
presently earning a salary on and above 12000. (Sample tables: employees & job_history)

select j.*
from job_history j
inner join employees e on j.employee_id = e.employee_id
where e.salary >= 12000;
15. Write a query in SQL to display the full name (first and last name), and salary of those employees who
working in any department located in London. (Sample tables: employees , locations & departments)

select concat(first_name,' ',last_name) as 'Full name', salary


from employees
inner join departments d using (department_id)
inner join locations l using (location_id)
where l.city like 'london';
16. Write a query to display job title, employee name, and the difference between salary of the employee
and minimum salary for the job. (Sample tables: employees & jobs)

select job_title,first_name as 'Name',(e.salary-min_salary) as 'difference'


from employees e
inner join jobs jo using (job_id);
17. Write a query to display the job title and average salary of employees. (Sample tables: employees &
jobs)

select job_title, avg(salary)


from employees e
inner join jobs jo using (job_id)
group by job_id;
18. Write a query to find the employee ID, job title, number of days between ending date and starting date
for all jobs in department 90 from job history. (Sample tables: jobs & job_history)

select employee_id, job_title, datediff(end_date,start_date)


from jobs jo
inner join job_history using (job_id)
where department_id = '90'

The End

You might also like