7. The HR department needs a report on job grades and salaries. To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that displays the name, job, department name, salary, and grade for all employees.

SELECT E.LAST_NAME , E.JOB_ID , D.DEPARTMENT_NAME , SALARY , J.GRADE_LEVEL
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING(DEPARTMENT_ID)
JOIN JOB_GRADES J
(ON ( E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL

8. The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies
SELECT e.last_name, e.hire_date
FROM employees e JOIN employees davies
ON (davies.last_name = 'Davies')
WHERE davies.hire_date < e.hire_date;

9. The HR department needs to find the names and hire dates for all employees who were hired before their managers, along with their managers’ names and hire dates. Save the script to a file named lab5_09.sql.

SELECT e.last_name, e.hire_date, m.last_name, m.hire_date
FROM   employees e JOIN employees m
ON    (e.manager_id = m.employee_id)
WHERE    e.hire_date <  m.hire_date;

6 chapter

1. The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply (excluding that employee). For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding Zlotkey).

select last_name , hire_date, department_id from employees wheredepartment_id = (select
('department_id from employees where last_name = '&&name
and last_name <> '&name';

تم تعديل 1 فبراير 201313 سنة بواسطة helal_10g

2. Create a report that displays the employee number and last name of all employees who earn more than the average salary. Sort the results in order of ascending salary.

select employee_id , last_name , salary
from employees where salary > (select avg(salary )
(from employees
order by salary  ;

3. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a u. Place your SQL statement in a text file named lab_06_03.sql. Run your query.

select employee_id , last_name
from employees where department_id in (select department_id from employees where last_name like '%a%' or last_name  like '%u%')

تم تعديل 1 فبراير 201313 سنة بواسطة helal_10g

4. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is 1700.

select last_name , department_id , job_id
from employees where department_id in (select department_id from departments where location_id = 1700

Create a report for HR that displays the last name and salary of every employee who reports to King.

select last_name , salary from employees where manager_id = (select employee_id from employees where last_name = 'King')

6. Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department.

SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE department_name = 'Executive');

7. Modify the query in lab_06_03.sql to display the employee number, last name, and salary of all employees who earn more than the average salary and who work in a department with any employee whose last name contains a u. Resave lab_06_03.sql as lab_06_07.sql. Run the statement in lab_06_07.sql.

SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id IN (SELECT department_id
					 FROM   employees
					 WHERE  last_name like '%u%')
AND    salary > (SELECT AVG(salary)
			 FROM   employees);

7 chapter

1. The HR department needs a list of department IDs for departments that do not contain the job ID ST_CLERK. Use set operators to create this report.

select department_id from departments
minus
select department_id from employees where job_id = 'ST_CLERK'

The HR department needs a list of countries that have no departments located in them. Display the country ID and the name of the countries. Use set operators to create this report.

SELECT country_id,country_name
FROM   countries
MINUS
SELECT country_id,country_name
FROM countries
NATURAL JOIN locations
NATURAL JOIN departments;

3. Produce a list of jobs for departments 10, 50, and 20, in that order. Display job ID and department ID using set operators.

SELECT job_id, department_id
FROM employees
WHERE department_id = 10
UNION
SELECT job_id, department_id
FROM employees
WHERE department_id = 50
UNION
SELECT job_id, department_id
FROM employees
WHERE department_id = 20

4. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the company (that is, they changed jobs but have now gone back to doing their original job).

select employee_id , job_id
from employees
intersect
select employee_id , job_id
from job_history

5. The HR department needs a report with the following specifications:
-Last name and department ID of all the employees from the EMPLOYEES table, regardless of whether or not they belong to a department
-Department ID and department name of all the departments from the DEPARTMENTS table, regardless of whether or not they have employees working in them
Write a compound query to accomplish this

SELECT last_name,department_id,TO_CHAR(null)
FROM   employees
UNION
SELECT TO_CHAR(null),department_id,department_name
FROM  departments;

8. The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies
SELECT e.last_name, e.hire_date
FROM employees e JOIN employees davies
ON (davies.last_name = 'Davies')
WHERE davies.hire_date < e.hire_date;

ممكن ده يابشمهندس
select last_name,hire_date
from emp
where hire_date>
(select hire_date from emp where last_name like'davies')

بصراحة انا استفدت جدا من هذه ااسئلة مع انى مزاكر SQL
افادكم الله طول حياتكم
وشكرا للاخ هلال

مش ممكن كل هذه التمارين يتم تجميعها في ملف من الأفضل