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

De WikiEducator
Saltar a: navegación, buscar
(→‎Actividades de funciones y agregados)
 
(16 revisiones intermedias por el mismo usuario no mostrado)
Línea 1: Línea 1:
 
{{Usuario:ManuelRomero/ProgramacionWeb/INAEM2017/BasesDatos/SQL/nav}}
 
{{Usuario:ManuelRomero/ProgramacionWeb/INAEM2017/BasesDatos/SQL/nav}}
 
===Ejercicios de bases de datos relacionales===
 
===Ejercicios de bases de datos relacionales===
 +
<br />
 +
{{MRM_Actividad|Title=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'''''
 +
{{Plegable|hide|Create.sql|
 +
<source lang=sql>
 +
CREATE TABLE REGIONS(
 +
    REGION_ID numeric,
 +
    REGION_NAME varchar(25),
 +
    PRIMARY KEY (REGION_ID));
 +
 +
 +
CREATE TABLE COUNTRIES(
 +
    COUNTRY_ID CHAR(2),
 +
    COUNTRY_NAME varchar(40),
 +
    REGION_ID numeric,
 +
    PRIMARY KEY (COUNTRY_ID),
 +
    FOREIGN KEY(REGION_ID) REFERENCES REGIONS(REGION_ID));
 +
 +
 +
CREATE TABLE LOCATIONS (
 +
    LOCATION_ID numeric(4),
 +
    STREET_ADDRESS varchar(40),
 +
    POSTAL_CODE varchar(12),
 +
    CITY varchar(30) NOT NULL,
 +
    STATE_PROVINCE varchar(25),
 +
    COUNTRY_ID CHAR(2),
 +
    PRIMARY KEY(LOCATION_ID),
 +
    FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRIES(COUNTRY_ID));
 +
 +
 +
CREATE TABLE DEPARTMENTS(
 +
  DEPARTMENT_ID numeric(4),
 +
  DEPARTMENT_NAME varchar(30) NOT NULL,
 +
  MANAGER_ID numeric(6),
 +
  LOCATION_ID numeric(4),
 +
  PRIMARY KEY(DEPARTMENT_ID),
 +
  FOREIGN KEY(LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID));
 +
 +
 +
CREATE TABLE JOBS(
 +
  JOB_ID varchar(10),
 +
  JOB_TITLE varchar(35) NOT NULL,
 +
  MIN_SALARY numeric(6),
 +
  MAX_SALARY numeric(6),
 +
  PRIMARY KEY(JOB_ID));
 +
 +
 +
CREATE TABLE EMPLOYEES(
 +
    EMPLOYEE_ID numeric(6),
 +
    FIRST_NAME varchar(20),
 +
    LAST_NAME varchar(25) NOT NULL,
 +
    EMAIL varchar(25) NOT NULL,
 +
    PHONE_numeric varchar(20),
 +
    HIRE_DATE DATE NOT NULL,
 +
    JOB_ID varchar(10) NOT NULL,
 +
    SALARY numeric(8,2),
 +
    COMMISSION_PCT numeric(2,2),
 +
    MANAGER_ID numeric(6),
 +
    DEPARTMENT_ID numeric(4),
 +
    PRIMARY KEY(EMPLOYEE_ID),
 +
    FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
 +
    FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID),
 +
    CONSTRAINT EMP_EMAIL_UK UNIQUE(EMAIL));
 +
 +
 +
CREATE TABLE JOB_HISTORY(
 +
  EMPLOYEE_ID numeric(6),
 +
  START_DATE DATE NOT NULL,
 +
  END_DATE DATE NOT NULL,
 +
  JOB_ID varchar(10) NOT NULL,
 +
  DEPARTMENT_ID numeric(4),
 +
  PRIMARY KEY(EMPLOYEE_ID, JOB_ID),
 +
  FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
 +
  FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
 +
  FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));
 +
 +
CREATE TABLE JOB_GRADES(
 +
    GRADE_LEVEL varchar(3),
 +
    LOWEST_SAL numeric,
 +
    HIGHEST_SAL numeric);
 +
</source>
 +
 +
}}
 +
 +
 +
;Fichero SQL de inserción
 +
{{Plegable|hide|Insert.sql|
 +
<source lang=sql>
 +
INSERT INTO REGIONS VALUES (1, 'Europe');
 +
INSERT INTO REGIONS VALUES (2, 'Americas');
 +
INSERT INTO REGIONS VALUES (3, 'Asia');
 +
INSERT INTO REGIONS VALUES (4, 'Middle East and Africa');
 +
 +
 +
 +
INSERT INTO COUNTRIES VALUES( 'CA', 'Canada', 2);
 +
INSERT INTO COUNTRIES VALUES( 'DE', 'Germany', 1);
 +
INSERT INTO COUNTRIES VALUES( 'UK', 'United Kingdom', 1);
 +
INSERT INTO COUNTRIES VALUES( 'US', 'United States of America', 2);
 +
 +
 +
 +
INSERT INTO LOCATIONS VALUES( 1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
 +
INSERT INTO LOCATIONS VALUES( 1500, '2001 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
 +
INSERT INTO LOCATIONS VALUES( 1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
 +
INSERT INTO LOCATIONS VALUES( 1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'Ontario', 'CA');
 +
INSERT INTO LOCATIONS VALUES( 2500, 'Magdalen Centre, The Ofxord Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
 +
 +
 +
 +
 +
INSERT INTO DEPARTMENTS VALUES (10, 'Administration', 200, 1700);
 +
INSERT INTO DEPARTMENTS VALUES (20, 'Marketing', 201, 1800);
 +
INSERT INTO DEPARTMENTS VALUES (50, 'Shipping', 124, 1500);
 +
INSERT INTO DEPARTMENTS VALUES (60, 'IT', 103, 1400);
 +
INSERT INTO DEPARTMENTS VALUES (80, 'Sales', 149, 2500);
 +
INSERT INTO DEPARTMENTS VALUES (90, 'Executive', 100, 1700);
 +
INSERT INTO DEPARTMENTS VALUES (110, 'Accounting', 205, 1700);
 +
INSERT INTO DEPARTMENTS VALUES (190, 'Contracting', NULL, 1700);
 +
 +
 +
 +
INSERT INTO JOBS VALUES('AD_PRES', 'President', 20000, 40000);
 +
INSERT INTO JOBS VALUES('AD_VP', 'Administration Vice President', 15000, 30000);
 +
INSERT INTO JOBS VALUES('AD_ASST', 'Administration Assistant', 3000, 6000);
 +
INSERT INTO JOBS VALUES('AC_MGR', 'Accounting Manager', 8200, 16000);
 +
INSERT INTO JOBS VALUES('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
 +
INSERT INTO JOBS VALUES('SA_MAN', 'Sales Manager', 10000, 20000);
 +
INSERT INTO JOBS VALUES('SA_REP', 'Sales Representative', 6000, 12000);
 +
INSERT INTO JOBS VALUES('ST_MAN', 'Stock Manager', 5500, 8500);
 +
INSERT INTO JOBS VALUES('ST_CLERK', 'Stock Clerk', 2000, 5000);
 +
INSERT INTO JOBS VALUES('IT_PROG', 'Programmer', 4000, 10000);
 +
INSERT INTO JOBS VALUES('MK_MAN', 'Marketing Manager', 9000, 15000);
 +
INSERT INTO JOBS VALUES('MK_REP', 'Marketing Representative', 4000, 9000);
 +
 +
 +
 +
INSERT INTO EMPLOYEES VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567',  DATE '1987-06-17','AD_PRES', 24000, NULL, NULL, 90);
 +
INSERT INTO EMPLOYEES VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',  DATE '1989-09-21','AD_VP', 17000, NULL, 100, 90);
 +
INSERT INTO EMPLOYEES VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569',  DATE '1993-01-13','AD_VP', 17000, NULL, 100, 90);
 +
INSERT INTO EMPLOYEES VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567',  DATE '1990-01-03','IT_PROG', 9000, NULL, 102, 60);
 +
INSERT INTO EMPLOYEES VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568',  DATE '1991-05-21','IT_PROG', 6000, NULL, 103, 60);
 +
INSERT INTO EMPLOYEES VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567',  DATE '1999-02-07','IT_PROG', 4200, NULL, 103, 60);
 +
INSERT INTO EMPLOYEES VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234',  DATE '1999-11-16','ST_MAN', 5800, NULL, 100, 50);
 +
INSERT INTO EMPLOYEES VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009',  DATE '1995-10-15','ST_CLERK', 3500, NULL, 124, 50);
 +
INSERT INTO EMPLOYEES VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994',  DATE '1997-01-29','ST_CLERK', 3100, NULL, 124, 50);
 +
INSERT INTO EMPLOYEES VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874',  DATE '1998-03-15','ST_CLERK', 2600, NULL, 124, 50);
 +
INSERT INTO EMPLOYEES VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004',  DATE '1998-07-09','ST_CLERK', 2500, NULL, 124, 50);
 +
INSERT INTO EMPLOYEES VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018',  DATE '2000-01-29','SA_MAN', 10500, .2, 100, 80);
 +
INSERT INTO EMPLOYEES VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267',  DATE '1996-05-11','SA_REP', 11000, .3, 149, 80);
 +
INSERT INTO EMPLOYEES VALUES (176, 'Jonathan', 'Taylor', 'JTAYLOR', '011.44.1644.429265',  DATE '1998-03-24','SA_REP', 8600, .2, 149, 80);
 +
INSERT INTO EMPLOYEES VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263',  DATE '1999-05-24','SA_REP', 7000, .15, 149, NULL);
 +
INSERT INTO EMPLOYEES VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444',  DATE '1987-09-17','AD_ASST', 4400, NULL, 101, 10);
 +
INSERT INTO EMPLOYEES VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555',  DATE '1996-02-17','MK_MAN', 13000, NULL, 100, 20);
 +
INSERT INTO EMPLOYEES VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666',  DATE '1997-08-17','MK_REP', 6000, NULL, 201, 20);
 +
INSERT INTO EMPLOYEES VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080',  DATE '1994-06-07','AC_MGR', 12000, NULL, 101, 110);
 +
INSERT INTO EMPLOYEES VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181',  DATE '1994-06-07','AC_ACCOUNT', 8300, NULL, 205, 110); 
 +
 +
 +
INSERT INTO JOB_HISTORY VALUES( 102,DATE '1993-01-13',DATE  '1998-07-24', 'IT_PROG', 60);
 +
INSERT INTO JOB_HISTORY VALUES( 101,DATE '1989-09-21',DATE  '1993-10-27', 'AC_ACCOUNT', 110);
 +
INSERT INTO JOB_HISTORY VALUES( 101,DATE '1993-10-28',DATE  '1997-03-15', 'AC_MGR', 110);
 +
INSERT INTO JOB_HISTORY VALUES( 201,DATE '1996-06-02', DATE '1999-12-19', 'MK_REP', 20);
 +
INSERT INTO JOB_HISTORY VALUES( 144,DATE '1998-03-24',DATE '1999-12-31', 'ST_CLERK', 50);
 +
INSERT INTO JOB_HISTORY VALUES( 142,DATE '1997-01-29',DATE  '1999-12-31', 'ST_CLERK', 50);
 +
INSERT INTO JOB_HISTORY VALUES( 200,DATE '1987-09-17',DATE '1993-06-17', 'AD_ASST', 90);
 +
INSERT INTO JOB_HISTORY VALUES( 176,DATE '1998-03-24',DATE '1998-12-31', 'SA_REP', 80);
 +
INSERT INTO JOB_HISTORY VALUES( 176,DATE '1999-01-01', DATE '1999-12-31', 'SA_MAN', 80);
 +
INSERT INTO JOB_HISTORY VALUES( 200,DATE '1994-07-01',DATE  '1998-12-31', 'AC_ACCOUNT', 90);
 +
 +
 +
 +
INSERT INTO JOB_GRADES VALUES('A', 1000, 2999);
 +
INSERT INTO JOB_GRADES VALUES('B', 3000, 5999);
 +
INSERT INTO JOB_GRADES VALUES('C', 6000, 9999);
 +
INSERT INTO JOB_GRADES VALUES('D', 10000, 14999);
 +
INSERT INTO JOB_GRADES VALUES('E', 15000, 24999);
 +
INSERT INTO JOB_GRADES VALUES('F', 25000, 40000);
 +
</source>
 +
 +
}}
 +
 +
{{Plegable|hide|drop.sql|
 +
 +
<source lang=sql>
 +
DROP TABLE JOB_GRADES;
 +
DROP TABLE JOB_HISTORY;
 +
DROP TABLE EMPLOYEES;
 +
DROP TABLE JOBS;
 +
DROP TABLE DEPARTMENTS;
 +
DROP TABLE LOCATIONS;
 +
DROP TABLE COUNTRIES;
 +
DROP TABLE REGIONS;
 +
</source>
 +
 +
}}
 +
 
===Ejercicios Sentencias DCL===
 
===Ejercicios Sentencias DCL===
 
===Ejercicios Sentencias Select===
 
===Ejercicios Sentencias Select===
 +
====Ejercicios select/from/where con funciones ====
 +
<br />
 +
{{MRM_Actividad|Title=Realiza las siguientes consultas|
 +
 +
[[Archivo:ej1_query.png]]
 +
*En este caso JOB_ID nos da el tipo de trabajo
 +
Secretari@ es el tipo '''''ST_CLERK'''''
 +
<source lang=sql>
 +
mysql> SELECT * 
 +
      FROM EMPLOYEES
 +
      WHERE JOB_ID ="ST_CLERK"     
 +
            AND     
 +
            HIRE_DATE>"1997-12-31";
 +
</source>
 +
<hr />
 +
[[Archivo:ej2_query.png]]
 +
 +
<source lang=sql>
 +
SELECT LAST_NAME, JOB_ID,SALARY, COMMISSION_PCT
 +
FROM EMPLOYEES
 +
WHERE COMMISSION_PCT IS NOT NULL
 +
ORDER BY LAST_NAME DESC;
 +
</source>
 +
 +
<hr />
 +
[[Archivo:ej3_query.png]]
 +
<source lang=sql>
 +
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
 +
 +
</source>
 +
<hr />
 +
[[Archivo:ej4_query.png]]
 +
<source lang=sql>
 +
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;
 +
</source>
 +
<hr />
 +
[[Archivo:ej5.png]]
 +
<source lang=sql>
 +
select *
 +
from empleados
 +
where substr(last_name,1,1) in ('J', 'K', 'L', 'M')
 +
 +
</source>
 +
<hr />
 +
[[Archivo:ej6.png]]
 +
<source lang=sql>
 +
 +
select last_name, salary,
 +
        decode (commission_pct,NULL, 'No' ,
 +
                              'Yes') as "comisión"
 +
from employees
 +
 +
</source>
 +
<hr />
 +
[[Archivo:ej7.png]]
 +
<source lang=sql>
 +
 +
 +
select department_name, location_id,job_id, salary
 +
from departments d, employees e
 +
where d.department_id = e.department_id
 +
      and
 +
      location_id = '&localizacion';
 +
</source>
 +
<hr />
 +
[[Archivo:ej8.png]]
 +
<source lang=sql>
 +
SELECT COUNT(*)
 +
FROM employees
 +
WHERE last_name LIKE '%n';
 +
--or
 +
SELECT COUNT(*)
 +
FROM employees
 +
WHERE SUBSTR(last_name, -1) = 'n';
 +
</source>
 +
<hr />
 +
[[Archivo:ej10.png]]
 +
<source lang=sql>
 +
SELECT DISTINCT job_id
 +
FROM employees
 +
WHERE department_id IN (10, 20);
 +
</source>
 +
<hr />
 +
[[Archivo:ej12.png]]
 +
<source lang=sql>
 +
SELECT last_name, hire_date
 +
FROM  employees
 +
WHERE TO_CHAR(hire_date, 'DD') < 16;
 +
</source>
 +
<hr />
 +
[[Archivo:ej13.png]]
 +
<source lang=sql>
 +
SELECT last_name, salary, TRUNC(salary, -3)/1000
 +
FROM  employees;
 +
</source>
 +
<hr />
 +
 +
[[Archivo:ej21.png]]
 +
<source lang=sql>
 +
SELECT last_name, TO_CHAR(hire_date, 'Month DD') BIRTHDAY
 +
FROM employees
 +
ORDER BY TO_CHAR(hire_date, 'DDD');
 +
</source>
 +
<hr />
 +
}}
 +
 
===Ejercicios Sentencias Join===
 
===Ejercicios Sentencias Join===
 +
[[Archivo:ej14.png]]
 +
<source lang=sql>
 +
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;
 +
</source>
 +
<hr />
 +
 
===Ejercicios Sentencias Group by===
 
===Ejercicios Sentencias Group by===
 +
====Actividades de funciones y agregados====
 +
<hr />
 +
[[Archivo:ej_func_01.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_groupby1.png]]
 +
}}
 +
 +
<hr />
 +
[[Archivo:ej_func_02.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by2.png]]
 +
}}
 +
<hr />
 +
[[Archivo:ej_func_03.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by5.png]]
 +
}}
 +
<hr />
 +
[[Archivo:ej_func_04.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by5b.png]]
 +
[[Archivo:sol_group_by5c.png]]
 +
}}
 +
<hr />
 +
[[Archivo:ej_func_05.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by6.png]]
 +
}}
 +
<hr />
 +
[[Archivo:ej_func_06.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by7.png]]
 +
}}
 +
<hr />
 +
[[Archivo:ej_func_07.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by8.png]]
 +
}}
 +
 +
<hr />
 +
[[Archivo:ej_func_08.png]]
 +
{{Plegable|hide|Solucion|
 +
[[Archivo:sol_group_by9.png]]
 +
}}
 +
<hr />
 +
[[Archivo:ej_func_09.png]]
 +
<hr />
 +
[[Archivo:ej_func_10.png]]
 +
<hr />
 +
[[Archivo:ej_func_11.png]]
 +
 +
<hr />
 +
[[Archivo:ej_func_12.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_13.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_14.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_15.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_16.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_17.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_18.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_19.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_20.png]]
 +
<source lang=sql>
 +
</source>
 +
<hr />
 +
[[Archivo:ej_func_21.png]]
 +
<source lang=sql>
 +
 +
</source>
 +
[[Archivo:ej_func_22.png]]
 +
<source lang=sql>
 +
</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 />
 +
}}
 
===Ejercicios Sentencias de subconsultas===
 
===Ejercicios Sentencias de subconsultas===
 +
====Ejercicios con subconsultas ====
 +
<br />
 +
[[Archivo:prac_subquery_1.png|600px]]
 +
<br />
 +
[[Archivo:prac_subquery_1s.png|600px]]
 +
<hr />
 +
[[Archivo:prac_subquery_2.png|600px]]
 +
<br />
 +
[[Archivo:prac_subquery_2s.png|600px]]
 +
<hr />
 +
[[Archivo:prac_subquery_3.png|600px]]
 +
<br />
 +
[[Archivo:prac_subquery_3s.png|600px]]
 +
<hr />
 +
[[Archivo:prac_subquery_4.png|600px]]
 +
<br />
 +
[[Archivo:prac_subquery_4s.png|600px]]
 +
<hr />
 +
[[Archivo:prac_subquery_5.png|600px]]
 +
<br />
 +
<!--[[Archivo:prac_subquery_5s.png|600px]]-->
 +
<hr />
 +
[[Archivo:prac_subquery_6.png|600px]]
 +
<br />
 +
<!--[[Archivo:prac_subquery_6s.png|600px]]-->
 +
<hr />
 +
[[Archivo:prac_subquery_7.png|600px]]
 +
<br />
 +
<!--[[Archivo:prac_subquery_7s.png|600px]]-->
 +
<hr />
 +
{{MRM_Actividad|Title=Ejercicios de subconsultas
 +
[[Archivo:ej16.png]]
 +
<source lang=sql>
 +
SELECT department_id, MIN(salary)
 +
FROM  employees
 +
GROUP BY department_id
 +
HAVING AVG(salary) = (SELECT MAX(AVG(salary))
 +
                      FROM  employees
 +
                      GROUP BY department_id);
 +
</source>
 +
<hr />
 +
[[Archivo:ej17.png]]
 +
<source lang=sql>
 +
SELECT *
 +
FROM
 +
departments
 +
WHERE  department_id NOT IN(SELECT department_id
 +
                            FROM employees
 +
                            WHERE job_id = 'SA_REP'
 +
                              AND department_id IS NOT NULL);
 +
 +
</source>
 +
<hr />
 +
[[Archivo:ej20.png]]
 +
<source lang=sql>
 +
SELECT last_name, TO_CHAR(hire_date, 'DAY') day
 +
FROM  employees
 +
WHERE TO_CHAR(hire_date, 'Day') = (SELECT TO_CHAR(hire_date, 'Day')
 +
                                    FROM employees
 +
                                    GROUP BY TO_CHAR(hire_date, 'Day')
 +
                                    HAVING COUNT(*) = (SELECT MAX(COUNT(*))
 +
                                                      FROM    employees
 +
                                                      GROUP BY TO_CHAR(hire_date,'Day')));
 +
 +
</source>
 +
<hr />
 +
}}

Última revisión de 23:45 21 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;

Ej21.png 

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




Ejercicios Sentencias Join

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;

Ejercicios Sentencias Group by

Actividades de funciones y agregados

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

Ejercicios con subconsultas


Prac subquery 1.png
Prac subquery 1s.png

Prac subquery 2.png
Prac subquery 2s.png

Prac subquery 3.png
Prac subquery 3s.png

Prac subquery 4.png
Prac subquery 4s.png

Prac subquery 5.png

Prac subquery 6.png

Prac subquery 7.png


Icon activity.jpg

Ejercicios de subconsultas Ej16.png 

SELECT department_id, MIN(salary)
FROM  employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary))
                      FROM  employees
                      GROUP BY department_id);

Ej17.png 

SELECT *
FROM
departments
WHERE   department_id NOT IN(SELECT department_id
                             FROM employees
                             WHERE job_id = 'SA_REP' 
                               AND department_id IS NOT NULL);

Ej20.png 

SELECT last_name, TO_CHAR(hire_date, 'DAY') DAY
FROM  employees
WHERE TO_CHAR(hire_date, 'Day') = (SELECT TO_CHAR(hire_date, 'Day')
                                    FROM employees
                                    GROUP BY TO_CHAR(hire_date, 'Day')
                                    HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                                                       FROM    employees
                                                       GROUP BY TO_CHAR(hire_date,'Day')));
{{{1}}}


Obtenido de «https://es.wikieducator.org/index.php?title=Usuario:ManuelRomero/ProgramacionWeb/INAEM2017/BasesDatos/SQL/ejercicios&oldid=23875»