Diferencia entre revisiones de «Usuario:ManuelRomero/ProgramacionWeb/INAEM2017/BasesDatos/SQL/ejercicios»

De WikiEducator
Saltar a: navegación, buscar
(Ejercicios Sentencias Group by)
(Ejercicios Sentencias Group by)
Línea 425: Línea 425:
 
<source lang=sql>
 
<source lang=sql>
 
</source>
 
</source>
 +
<hr />
 +
====Ejercicios de agrupación  ====
 +
<br />
 +
{{MRM_Actividad|Title=Ejercicios de agrupación|
 +
[[Archivo:ej9.png]]
 +
<source lang=sql>
 +
SELECT d.department_id, d.department_name, d.location_id, COUNT(e.employee_id)
 +
FROM  employees e
 +
      RIGHT OUTER JOIN departments d
 +
ON
 +
    e.department_id = d.department_id
 +
GROUP BY d.department_id, d.department_name, d.location_id;
 +
</source>
 +
<hr />
 +
[[Archivo:ej11.png]]
 +
<source lang=sql>
 +
SELECT e.job_id, count(e.job_id) FREQUENCY
 +
FROM employees e
 +
    JOIN departments d
 +
    ON e.department_id = d.department_id
 +
WHERE  d.department_name IN ('Administration', 'Executive')
 +
GROUP BY e.job_id
 +
ORDER BY FREQUENCY DESC;
 +
</source>
 +
<hr />
  
 +
[[Archivo:ej15.png]]
 +
<source lang=sql>
 +
SELECT  d.department_id, d.department_name, count(e1.employee_id) employees, 
 +
        NVL(TO_CHAR(AVG(e1.salary), '99999.99'), 'No average') avg_sal, 
 +
        e2.last_name, e2.salary, e2.job_id
 +
FROM  departments d RIGHT OUTER JOIN employees e1
 +
      ON  d.department_id = e1.department_id
 +
      RIGHT OUTER JOIN employees e2
 +
      ON  d.department_id = e2.department_id
 +
      GROUP BY d.department_id, d.department_name, e2.last_name,
 +
          e2.salary,
 +
          e2.job_id
 +
      ORDER BY d.department_id, employees;
 +
</source>
 +
<hr />
 +
 +
[[Archivo:ej18.png]]
 +
;a) Contrate menos de tres empleados:
 +
<source lang=sql>
 +
SELECT d.department_id, d.department_name, COUNT(*)
 +
FROM
 +
departments d JOIN employees e
 +
ON
 +
d.department_id = e.department_id
 +
GROUP BY d.department_id, d.department_name
 +
HAVING COUNT(*) < 3;
 +
</source>
 +
;b) Tenga el mayor número de empleados:
 +
<source lang=sql>
 +
SELECT d.department_id, d.department_name, COUNT(*)
 +
FROM
 +
departments d JOIN employees e
 +
ON
 +
d.department_id = e.department_id
 +
GROUP BY d.department_id, d.department_name
 +
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
 +
FROM
 +
employees
 +
GROUP BY department_id);
 +
</source>
 +
 +
;c) Tenga el menor número de empleados:
 +
<source lang=sql>
 +
SELECT d.department_id, d.department_name, COUNT(*)
 +
FROM
 +
departments d JOIN employees e
 +
ON
 +
d.department_id = e.department_id
 +
GROUP BY d.department_id, d.department_name
 +
HAVING COUNT(*) = (SELECT MIN(COUNT(*))
 +
FROM
 +
employees
 +
GROUP BY department_id);
 +
</source>
 +
 +
<hr />
 +
[[Archivo:ej19.png]]
 +
<source lang=sql>
 +
SELECT e.employee_id, e.last_name, e.department_id, e.salary,
 +
AVG(s.salary)
 +
FROM
 +
employees e JOIN employees s
 +
ON
 +
e.department_id = s.department_id
 +
GROUP BY e.employee_id, e.last_name, e.department_id,
 +
e.salary;
 +
</source>
 
<hr />
 
<hr />
 +
}}
  
 
===Ejercicios Sentencias de subconsultas===
 
===Ejercicios Sentencias de subconsultas===

Revisión de 22:20 14 may 2018


Ejercicios de bases de datos relacionales



Icon activity.jpg
Crea conexiones
Crea una conexión con oracle usando los parámetros facilitados en clase
Crea una conexión en línea de comandos con mysql
Crea una conexión usando phpmyadmin con el gestor de bases de datos mysql



Ejecuta los siguientes ficheros tanto en mysql como en oracle


Fichero SQL de inserción

Ejercicios Sentencias DCL

Ejercicios Sentencias Select

Ejercicios select/from/where con funciones



Icon activity.jpg
Realiza las siguientes consultas

Ej1 query.png

  • En este caso JOB_ID nos da el tipo de trabajo
Secretari@ es el tipo ST_CLERK
mysql> SELECT *  
       FROM EMPLOYEES 
       WHERE JOB_ID ="ST_CLERK"       
             AND       
             HIRE_DATE>"1997-12-31";

Ej2 query.png

SELECT LAST_NAME, JOB_ID,SALARY, COMMISSION_PCT 
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL 
ORDER BY LAST_NAME DESC;

Ej3 query.png

SELECT 'El salario de '||Last_name||
       'va a ser con un incremento del 10% de '||
       round(Salary *1.10) ||
       ' dólares' AS "Nuevo Salario"
FROM EMPLOYEES
WHERE COMMISSION_pCT IS NULL

Ej4 query.png

SELECT last_name, 
       trunc(months_between(sysdate, hire_date)/12) AS "años",
       trunc(MOD(months_between(sysdate, hire_date),12))AS "meses"
FROM employees
ORDER BY hire_date;

Ej5.png

SELECT *
FROM empleados
WHERE substr(last_name,1,1) IN ('J', 'K', 'L', 'M')

Ej6.png

SELECT last_name, salary, 
         decode (commission_pct,NULL, 'No' ,
                               'Yes') AS "comisión"
FROM employees

Ej7.png

SELECT department_name, location_id,job_id, salary
FROM departments d, employees e
WHERE d.department_id = e.department_id
       AND
       location_id = '&localizacion';

Ej8.png

SELECT COUNT(*)
FROM employees
WHERE last_name LIKE '%n';
--or
SELECT COUNT(*)
FROM employees
WHERE SUBSTR(last_name, -1) = 'n';

Ej10.png

SELECT DISTINCT job_id
FROM employees
WHERE department_id IN (10, 20);

Ej12.png

SELECT last_name, hire_date
FROM  employees
WHERE TO_CHAR(hire_date, 'DD') < 16;

Ej13.png

SELECT last_name, salary, TRUNC(salary, -3)/1000
FROM  employees;

Ej14.png

SELECT e.last_name, m.last_name manager, m.salary,
j.grade_level
FROM  employees e JOIN employees m
      ON    e.manager_id = m.employee_id
     JOIN job_grades j
     ON   m.salary BETWEEN j.lowest_sal AND j.highest_sal
     AND  m.salary > 15000;

Ej21.png

SELECT last_name, TO_CHAR(hire_date, 'Month DD') BIRTHDAY
FROM employees
ORDER BY TO_CHAR(hire_date, 'DDD');




Ejercicios Sentencias Join

Ejercicios Sentencias Group by

Actividades de funciones y agregados

Ej func 01.png

 

Ej func 01.png

 

Ej func 02.png

 

Ej func 03.png

 

Ej func 04.png

 

Ej func 05.png

 

Ej func 06.png

 

Ej func 07.png

 

Ej func 08.png

 

Ej func 09.png

 

Ej func 10.png

 

Ej func 11.png

 

Ej func 12.png

 

Ej func 13.png

 

Archivo:Ej func 14.png

 

Ej func 15.png

 

Ej func 16.png

 

Ej func 17.png

 

Ej func 18.png

 

Ej func 19.png

 

Ej func 20.png

 

Ej func 21.png

 

Ej func 22.png

 

Ejercicios de agrupación



Icon activity.jpg
Ejercicios de agrupación

Ej9.png

SELECT d.department_id, d.department_name, d.location_id, COUNT(e.employee_id)
FROM  employees e 
      RIGHT OUTER JOIN departments d
 ON
     e.department_id = d.department_id
GROUP BY d.department_id, d.department_name, d.location_id;

Ej11.png

SELECT e.job_id, COUNT(e.job_id) FREQUENCY
FROM employees e 
     JOIN departments d
     ON e.department_id = d.department_id
WHERE   d.department_name IN ('Administration', 'Executive')
GROUP BY e.job_id
ORDER BY FREQUENCY DESC;

Ej15.png

SELECT   d.department_id, d.department_name, COUNT(e1.employee_id) employees,  
         NVL(TO_CHAR(AVG(e1.salary), '99999.99'), 'No average') avg_sal,  
         e2.last_name, e2.salary, e2.job_id
FROM  departments d RIGHT OUTER JOIN employees e1
      ON  d.department_id = e1.department_id
      RIGHT OUTER JOIN employees e2
      ON   d.department_id = e2.department_id
      GROUP BY d.department_id, d.department_name, e2.last_name,
           e2.salary,
           e2.job_id
      ORDER BY d.department_id, employees;

Ej18.png

a) Contrate menos de tres empleados
SELECT d.department_id, d.department_name, COUNT(*)
FROM
departments d JOIN employees e
ON
d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) < 3;
b) Tenga el mayor número de empleados
SELECT d.department_id, d.department_name, COUNT(*)
FROM
departments d JOIN employees e
ON
d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM
employees
GROUP BY department_id);
c) Tenga el menor número de empleados
SELECT d.department_id, d.department_name, COUNT(*)
FROM
departments d JOIN employees e
ON
d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) = (SELECT MIN(COUNT(*))
FROM
employees
GROUP BY department_id);

Ej19.png

SELECT e.employee_id, e.last_name, e.department_id, e.salary,
AVG(s.salary)
FROM
employees e JOIN employees s
ON
e.department_id = s.department_id
GROUP BY e.employee_id, e.last_name, e.department_id,
e.salary;




Ejercicios Sentencias de subconsultas