Tema nr.

7
Observație!
Scrieți rezolvarea direct în acest document!

1. Create a cross-join that displays the last name and department name from the employees and
departments tables.

SELECT employees.last_name,departments.department_name
FROM employees cross join departments;

2. Create a query that uses a natural join to join the departments table and the locations table by
the location_id column. Restrict the output to only department IDs of 20 and 50. Display the
department id and name, location id, and city.

SELECT departments.department_id,location_id,locations.city
FROM departments natural join locations;

3. Write a statement joining the employees and jobs tables. Display the first and last names, hire
date, job id, job title and maximum salary. Limit the query to those employees who are in jobs
that can earn more than $12,000.

SELECT first_name,last_name,hire_date,job_id,job_title,max_salary;
FROM employees join jobs using (job_id);
WHERE salary>=12000;

4. Write a statement that displays the employee ID, first name, last name, manager ID,
manager first name, and manager last name for every employee in the employees
table. Hint: this is a self-join.

SELECT
e.employee_id,e.first_name,e.last_name,m.employee_id,m.first_name,m.last_name
FROM employees e,employees m where e.employee_id=m.employee_id;

5. Query and display manager ID, department ID, department name, first name, and last
name for all employees in departments 80, 90, 110, and 190.

SELECT d.manager_id,d.department_id,d.department_name,e.first_name,e.last_name
FROM departments d,employees e
WHERE d.department_id=80 OR d.department_id=90 OR d.department_id=110 OR
d.department_id=190;
6. Display employee ID, last name, department ID, department name, and hire date for
those employees whose hire date was June 7, 1994.

SELECT employee_id,last_name,department_id,department_name,hire_date
FROM employees join departments using(department_id)
WHERE hire_date='07-June-1994';

7. Return the first name, last name, and department name for all employees including
those employees not assigned to a department.

Select employees.first_name,employees.last_name, departments.department_name

From employees,departments
where employees.department_id=departments.department_id(+);

8. Return the first name, last name, and department name for all employees including
those departments that do not have an employee assigned to them.

Select employees.first_name,employees.last_name, departments.department_name

From employees,departments
where employees.department_id(+)=departments.department_id;

9. Return the first name, last name, and department name for all employees including
those departments that do not have an employee assigned to them and those
employees not assigned to a department.

Select employees.first_name,employees.last_name, departments.department_name

From employees
full outer join departments ON(employees.department_id=departments.department_id);

10. Display the employee’s last name and employee number along with the manager’s
last name and manager number. Label the columns Employee, Emp#, Manager, and
Mgr#, respectively.

SELECT e.last_name "Employee",e.employee_id "Emp #",m.manager_id "Mgr #",m.last_name

"Manager"
FROM employees e,employees m
WHERE e.employee_id=m.manager_id

11. Modify problem 4 to display all employees, including those who have no manager.
Order the results by the employee number.

SELECT emp.employee_id,emp.first_name, emp.last_name, emp.manager_id, mng.first_name,

mng.last_name
from employees emp,employees mng
Where emp.manager_id=mng.employee_id(+) ORDER BY emp.employee_id;
12. Find the average salary for Global Fast Foods staff members whose manager ID is 19.

SELECT avg(salary)
FROM f_staffs
WHERE manager_id=19;

13. Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12
and 9.

SELECT sum(salary)
FROM f_staffs
WHERE id in(12,19);

14. What was the hire date of the last Oracle employee hired?

SELECT max(hire_date)
FROM employees;

15. How many songs are listed in the DJs on Demand D_SONGS table?

SELECT count(id)
FROM d_songs;

16. The d_track_listings table in the DJs on Demand database has a song_id column and
a cd_number column. How many song IDs are in the table and how many different
CD numbers are in the table?

SELECT count(song_id),count(distinct cd_number)

FROM D_TRACK_LISTINGs;

17. Create a query that will convert any null values in the auth_expense_amt column on
the DJs on Demand D_PARTNERS table to 100000 and find the average of the
values in this column. Round the result to two decimal places.

SELECT round(avg(nvl(auth_expense_amt,100000)),2)
FROM d_partners;