Examen de Oracle

Muestre los nombres y apellidos (en una sola columna), nombre de la ciudad y el país donde está cada trabajador, cuyo nombre del país inicie por la letra indicada en el input. El resultado se muestra en orden descendente por el nombre de la ciudad

• SELECT CONCAT(e.first_name, ' '||e.last_name) NOMBRE, l.city, c.country_name PAIS FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN locations l ON l.location_id = d.location_id INNER JOIN countries c ON c.country_id = l.country_id WHERE c.country_name LIKE UPPER(:Inicial_pais)||'%' ORDER BY l.city DESC;

• SELECT CONCAT(e.first_name, ' '||e.last_name) NOMBRE, l.city, c.country_name PAIS FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN locations l ON l.location_id = d.location_id INNER JOIN countries c ON c.country_id = l.country_id WHERE c.country_name LIKE UPPER(:Inicial_pais)||'%' ORDER BY city DESC;

• SELECT CONCAT(e.first_name, ' '||e.last_name) NOMBRE, l.city, c.country_name PAIS FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN locations l ON l.location_id = d.location_id INNER JOIN countries c ON c.country_id = l.country_id WHERE country_name LIKE UPPER(:Inicial_pais)||'%' ORDER BY l.city DESC;

• Ninguna

Elabore un reporte que contenga la dirección, el código postal, la provincia, la ciudad, la región en que se encuentran, de las locaciones que se encuentran en ESTADOS UNIDOS, ordenados por su código postal de manera descendente.

• SELECT l.street_address "Direccion",

  l.city "Ciudad",l.state_province "Provincia",

  co.region_id "Region",l.postal_code "CodPostal"

  FROM locations l

  INNER JOIN countries co ON l.country_id=co.country_id

  WHERE l.country_id='US'

  ORDER BY l.postal_code

• SELECT l.street_address "Direccion",

  l.city "Ciudad",l.state_province "Provincia",

  co.region_id "Region",l.postal_code "CodPostal"

  FROM locations l

  INNER JOIN countries co ON l.country_id=co.country_id

  WHERE l.country_id='US'

  ORDER BY l.postal_code DESC;

• SELECT l.street_address "Direccion",

  l.city "Ciudad",l.state_province "Provincia",

  co.region_id "Region",l.postal_code "CodPostal"

  INNER JOIN countries co ON l.country_id=co.country_id

  WHERE l.country_id='US'

  ORDER BY l.postal_code DESC;

• Ninguno

Obtener el salario promedio de los empleados que tengan el cargo de «Stock Manager».

• SELECT TO_CHAR(ROUND(AVG(e.salary), 2), '$99,999.00') as 'Salario Promedio'

  FROM jobs j, employees e

  LEFT JOIN employees e ON e.job_id = j.job_id

  HAVING UPPER(j.job_title) = 'STOCK MANAGER'

  GROUP BY j.job_title;

• SELECT TO_CHAR(ROUND(AVG(e.salary), 2), '$99,999.00') as 'Salario Promedio'

  FROM jobs , employees

  LEFT JOIN employees e ON e.job_id = j.job_id

  HAVING UPPER(j.job_title) = 'STOCK MANAGER'

  GROUP BY j.job_title;

• SELECT TO_CHAR(ROUND(AVG(e.salary), 2), ‘$99,999.00’) as “Salario Promedio” FROM 4obs j LEFT JOIN employees e ON e.job_id = j.job_id HAVING UPPER(j.job_title) = ‘STOCK MANAGER’ GROUP BY j.job_title;

• Ninguno

Cree un informe para mostrar los empleados, el apellido y el número de empleado junto con el apellido y el número de gerente de su gerente. Etiquete la columna “Empleado, Emp#, Gerente y Mgr#”, respectivamente

• SELECT e.last_name "employee", e.employee_id "Emp#",m.last_name "manager", m.employee_id "Mgr#"

  FROM employees e JOIN employees m

  ON (e.manager_id);

• SELECT e.last_name "employee", e.employee_id "Emp#",m.last_name "manager", m.employee_id "Mgr#"

  FROM employees e JOIN employees

  ON (e.manager_id = m.employee_id);

• SELECT e.last_name "employee", e.employee_id "Emp#",m.last_name "manager", m.employee_id "Mgr#"

  FROM employees e JOIN employees m

  ON (e.manager_id = m.employee_id);

Muestre el nombre del departamento, los nombres de los empleados y los salarios, de acuerdo a el nombre del departamento que el usuario quiera buscar

• SELECT d.department_name, e.first_name || ‘ ‘ || e.last_name AS EMPLEADO,

  TO_CHAR((e.salary), ‘$99,999.00’)

  FROM employees e

  departments d e.department_id == d.department_id

  WHERE d.department_name = :Nomdepar;

• SELECT d.department_name, e.first_name || ‘ ‘ || e.last_name AS EMPLEADO,

  TO_CHAR((e.salary), ‘$99,999.00’)

  FROM employees e

  INNER JOIN departments d ON e.department_id = d.department_id

  WHERE d.department_name = :Nombre_departamento;

• SELECT d.department_name, e.first_name || ‘ ‘ || e.last_name AS EMPLEADO,

  TO_CHAR((e.salary), "$99,999.00")

  FROM employees e

  departments e.department_id = d.department_id

  WHERE d.department_name = &Nomdepar;

• Ninguna

Escriba una consulta que muestre la cantidad de personas que hacen parte del mismo departamento y a cuánto asciende la suma total de los días que llevan empleados El resultado debe mostrarse en orden descendente por el departamento que tiene la mayor cantidad de días empleados.

• SELECT d.DEPARTMENT_NAME AS “Nombre de departamento”,

  COUNT(d.DEPARTMENT_ID) AS “Total Empleados por departamento”,

  SUM(TO_NUMBER(jh.END_DATE – jh.START_DATE)) AS total_dias_empleados

  FROM DEPARTMENTS d INNER JOIN JOB_HISTORY jh ON d.DEPARTMENT_ID=jh.DEPARTMENT_ID

  GROUP BY d.DEPARTMENT_NAME

  ORDER BY 3 DESC;

• SELECT d.DEPARTMENT_NAME AS “Nombre de departamento”,

  COUNT(d.DEPARTMENT_ID) AS “Total Empleados por departamento”,

  SUM(TO_NUMBER( jh.START_DATE - jh.END_DATE)) AS total_dias_empleados

  FROM DEPARTMENTS d INNER JOIN JOB_HISTORY jh ON d.DEPARTMENT_ID=jh.DEPARTMENT_ID

  GROUP BY d.DEPARTMENT_NAME

  ORDER BY 3 DESC;

• SELECT d.DEPARTMENT_NAME AS “Nombre de departamento”,

  COUNT(DEPARTMENT_ID) AS “Total Empleados por departamento”,

  SUM(TO_NUMBER(jh.END_DATE – jh.START_DATE)) AS total_dias_empleados

  FROM DEPARTMENTS d INNER JOIN JOB_HISTORY jh ON d.DEPARTMENT_ID=jh.DEPARTMENT_ID

  GROUP BY d.DEPARTMENT_NAME

  ORDER BY 3 DESC

• Ninguna

SELECT (MAX(SALARY)-MIN(SALARY)) AS DIFFERENCE FROM EMPLOYEES;

Verdadera o Falsa

• Verdadera

• Falsa

SELECT manager_id, MIN(SALARY)

FROM employees

WHERE MIN(SALARY) > 6000

GROUP BY manager_id

ORDER BY 2 DESC

• Verdadera

• Falsas