Ing. Sistemas

UNIVERSIDAD TÉCNICA PARTICULAR DE LOJA
EJERCICIOS DE CAPITULO 5 Y 6
RAMIRO ISRAEL VIVANCO GUALAN
Capítulo 5
EJERCICIO 1 true

EJERCICIO 2 false

EJERCICIO 3 true

EJERCICIO 4

selectROUND(MAX(salary),0) "Maximum",
ROUND(MIN(salary),0) "Minimum",
ROUND(SUM(salary),0) "Sum",
ROUND(AVG(salary),0) "Average"
FROM employees;

EJERCICIO 5

select job_id "JOB",
ROUND(MAX(salary),0)"Maximum",
ROUND(MIN(salary),0) "Minimum",
ROUND(SUM(salary),0) "Sum",
ROUND(AVG(salary),0) "Average"
FROM employees
GROUP BY job_id;

EJERCICIO 6
SELECT job_id, COUNT(*)
FROM employeesGROUP BY job_id;

literal b

SELECT COUNT (DISTINCT manager_id) "Number of Managers"
FROM employees;

EJERCICIO 7
SELECT MAX(salary)- MIN(salary) DIFFERENCE
FROM employees;

EJERCICIO 8SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>6000
ORDER BY MIN(salary)DESC;

EJERCICIO 9

select COUNT(*) total,SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0)) "1995",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0)) "1996",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0)) "1997",
SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1998,1,0)) "1998"
FROM employees;

EJERCICIO 10
SELECT job_id "Job",
SUM(DECODE(department_id , 20, salary)) "Dept 20",
SUM(DECODE(department_id , 50, salary)) "Dept 50",SUM(DECODE(department_id , 80, salary)) "Dept 80",
SUM(DECODE(department_id , 90, salary)) "Dept 90",
SUM(salary) "Total"
FROM employees
GROUP BY job_id;

EJERCICIO 11
SELECT job_id, COUNT(*)
FROM employeesWHERE job_id= '&job_title'
GROUP BY job_id;

Capítulo 6

EJERCICIO 1
select location_id, street_address, city, state_province, country_name
from countries
natural join locations;

EJERCICIO2
select last_name, department_id, department_name
from employees join departments
using (department_id);

EJERCICIO 3
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM...