Usuario:ManuelRomero/SQL

De WikiEducator
< Usuario:ManuelRomero
Revisión a fecha de 02:31 9 may 2018; ManuelRomero (Discusión | contribuciones)

(dif) ← Revisión anterior | Revisión actual (dif) | Revisión siguiente → (dif)
Saltar a: navegación, buscar

SQL

SQL es un lenguaje de consultas estructuradas o Structured Query Language. Es un lenguaje de 4º generación donde el programador o usuario del lenguaje especifica lo qué quiere, pero no establece el procedimiento a seguir para conseguirlo, no dice cómo ha de hacer el sistema para conseguirlo


Icon casestudy.gif
Lenguaje no procedural Vs lenguaje procedural)
Con lenguaje no procedural (tipo SQL) 
Dime todos los empleados de más de 45 años
o 
SELECT *
FROM empleados
WHERE edad>45;

Lenguaje procedural (tipo php o java) 

Abre la tabla empelados
Lee desde el primer registro hasta el último (Bucle)
     Verifica si ese empleado tiene mas de 45 
     Si es así añádelo a la lista de resultado
Muestra la lista de resultado




Icon define.gif
SQL

Lenguaje para gestionar bases de datos relacionales cuyas instrucciones o sentencias especifican lo qué se quiere, no cómo se va a realizar


Lenguajes dentro de SQL

SQL no es, como su nombre indica, solamente un lenguaje de consultas, sino que proporciona todas las instrucciones necesarias para gestionar una base de datos.
SQL incorpora tres tipos de lenguajes: de Definición, de Control, de Manipulación.
 Las instrucciones de SQL se conocen como sentencias, y todas se caracterizan por que empiezan por una palabra reservada que identifica a qué lenguaje de los tres que tiene SQL pertenece esa instrucción.


Icon key points.gif

Lenguajes SQL

DDL
Lenguaje de definición de datos.
Las sentencias de este lenguajes permite crear nuevos elementos en la base de datos como usuarios, tablas, índices, etc.
DCL
Lenguaje de control de datos
Las sentencias de este lenguaje permite gestionar permisos sobre los elementos y acciones sobre la consistencia de los datos
DML
Lenguaje de manipulación de datos
Sin duda son las instrucciones o sentencias mas utilizadas en un Gestor
Con ellas podemos manipular lo que se conoce como la extensión o contenido de la base de datos. Es decir actuar sobre las tuplas (insertar, modificar y borrar), y consultar


T111.png

Sentencias DDL



Icon define.gif
Sentencias DDL
CREATE 
CREATE DATABASE usuarios;
CREATE TABLE usuario(
       password VARCHAR(200),
       nombre varchar2(40)
);
DROP 
DROP TABLE usuario;
ALTER 
ALTER TABLE usuarios ADD direccion VARCHAR(20);


Instrucción CREATE
Sintaxis de la instrucción

Ddl t1.png

  • Los nombres de las columnas son identificadores
Tipos de datos para las columnas

Ddl t2.png

Especificación de Restricciones

Ddl t3.png

Definición de Restricciones

Ddl t4.png

Definición de Restricciones

Ddl t5.png

Restricción unique

Ddl t6.png

Restrición Foreign Key

Ddl t7.png

Restricción Ckeck

Ddl t8.png

Creacción de tabla como resultado de una consulta

Ddl t9.png



Icon activity.jpg
Crear una base de datos

Crea la base de datos dado el siguiente enunciado 

Clientes (DNI, nombre, dirección, edad (mayor de 18 años y menor de 65), sexo (H, M, N_A), por defecto N_A)
Facturas (Número , fecha (por defecto la fecha actual, importe total, cantidad_productos)





Icon activity.jpg
Realiza el sql de la siguiente estructura

Cliente factura sql.png 

DROP TABLE cliente;
DROP TABLE facturas;
 
CREATE TABLE cliente(
dni varchar2(10) PRIMARY KEY,
nombre varchar2(10) DEFAULT 'mi nombre'
);
CREATE TABLE factura(
num NUMBER PRIMARY KEY,
fecha DATE DEFAULT sysdate,
cantidad NUMBER DEFAULT 10,
cliente varchar2(10),
CONSTRAINT cliente_factura FOREIGN KEY (cliente) REFERENCES cliente(dni) ON DELETE SET NULL
);
 
INSERT INTO  cliente (dni) VALUES ( 1);
INSERT INTO  cliente (dni) VALUES ( 2);
INSERT INTO  cliente (dni) VALUES ( 3);
INSERT INTO  cliente (dni) VALUES ( 4);
 
INSERT INTO  factura(num, cliente) VALUES ( 1, 1);
INSERT INTO  factura(num, cliente) VALUES ( 2, 1);
INSERT INTO  factura(num, cliente) VALUES ( 3, 1);
INSERT INTO  factura(num, cliente) VALUES ( 4, 2);
INSERT INTO  factura(num, cliente) VALUES ( 5, 3);
INSERT INTO  factura(num, cliente) VALUES ( 6, 3);
INSERT INTO  factura(num, cliente) VALUES ( 7, 4);
INSERT INTO  factura(num, cliente) VALUES ( 8, 4);





Alter table

La sentencia ALTER TABLE se utiliza para añadir, eliminar o modificar las columnas en una tabla existente.
  • SQL ALTER TABLE

Ddl alter t1.png 

ALTER TABLE TABLE_NAME
ADD column_name datatype

Eliminar una columna de una tabla 

ALTER TABLE TABLE_NAME
DROP COLUMN column_name
</sql>
 
Modificar co  cambiar el tipo de datos de una columna de una tabla
<SOURCE lang=sql>
ALTER TABLE TABLE_NAME
MODIFY column_name datatype

(Comment.gif: Ojo no violar restricciones con la modificación de la tabla)




Icon activity.jpg
Modifica la tabla anterior
  • Añade un atributo a cada tabla con un valor por defecto
  • Modifica el tipo de dato de dni a 15 caracteres.
  • Elimina los atributos añadidos





Icon activity.jpg
Alter table
  • Supongamos que tenemos el siguiente esquema 
En un departamento hay empleados
cada empleado pertenece a un solo departamento
Cada departamento es dirigido por un empleado
Un empleado puede dirigir varios departamentos
  • En él vemos dos tablas con referencias cruzadas

Relaciones dobles.png

  • Excribe las sentencias sql que generen la estructura de base de datos 
CREATE TABLE EMPLEADO (
dni varchar2(20),
direccion varchar2(15),
nombre varchar2(20) NOT NULL,
telefono NUMBER,
departamento NUMBER,
CONSTRAINT valor_telefono CHECK(telefono > 600000000),
CONSTRAINT clave_ppal_empleados PRIMARY KEY (dni),
CONSTRAINT nom_tel_unico_emp UNIQUE(nombre, telefono)
);
 
CREATE TABLE DEPARTAMENTO(
numero NUMBER,
nombre varchar2(15),
ubicacion varchar2(20) NOT NULL,
director varchar2(20),
CONSTRAINT numero  PRIMARY KEY (numero),
CONSTRAINT director FOREIGN KEY (director) REFERENCES EMPLEADO(dni)
);
 
ALTER TABLE EMPLEADO
ADD CONSTRAINT pertenece FOREIGN KEY (departamento) REFERENCES DEPARTAMENTO(numero);
Ahora si queremos eliminar las tablas, directamente no lo podremos hacer



Icon key points.gif

Importante a la hora de borrar


No se puede eliminar una tabla si está siendo referenciada por otra
  • Primero habría que eliminar esa referencia
  • En general se deben eliminar las tablas en orden inverso en el que se crearon 


ALTER TABLE departamento
DROP CONSTRAINT director;
DROP TABLE empleado;
DROP TABLE departamento;



}}

Drop table

Ddl drop t1.png

  • Elimina una tabla de forma completa
  • Si solo quire elminiar su contenido usa la claúsula trunctate


Icon activity.jpg
Drop
  • Prueba a borrar una tabla




Sentencias DCL



Icon define.gif
Sentencias DCL
GRANT
para asignar privilegios 
 GRANT ALL ON DATABASE.* TO 'manuel'@'localhost'
 #Asigna todos los privilegios sobre las tablas 
 #de la base de datos 'database' 
 # al usuario 'manuel'
REVOKE
Elimina privilegios asignados
COMMIT
Confirma una transacción de manera permanente en la base de datos
ROLLBACK
Desace todos los cambios en la base de datos desde la ultima transacción confirmada


Icon activity.jpg
Actividad
  • Prueba a hacer una inserción
  • Después sal y vuelve a entrar a ver si está la tupla 
Esto es por que por defecto se hace un rollback a todo lo hecho
  • Haz lo mismo haciendo un commit 
Ahora los cambios se mantienen





Sentencias DML



Icon define.gif
Sentencias DML
INSERT
Inserta tuplas en una tabla
Esta acción pude devolver algún tipo de valor booleano indicando se se ha insertado o no la tupla 
INSERT INTO usuarios (nombre, password) 
VALUES("manuel", "password_no_segura");
DELETE
Borra tuplas (0 o más) de una o más tablas 
DELETE FROM usuarios WHERE nombre="manuel";
UPDATE
Actuliza tuplas (0 o más) de una o más tablas
Las dos últimas sentencias (UPDATE Y DELETE) suelen devolver un entero.
Este valor indica el número de tuplas actualizadas o borradas (0 o n)
Un error se detalla con el valor (-1 o false). 
UPDATE usuarios SET nombre="alicia" WHERE nombre="manolo";



SELECT

  • Esta es una claúsula especial a la que le vamos a dedicar bastante tiempo.
  • Consiste en hacer consultas de nuestro conjunto de datos y poder recuperarlos.
  • La cantidad de consultas que se pueden realizar es muy grande y variado, siempre que se pueda establecer la consulta y la lógica matemática de su resolución, va a haber una sentencia SQL que permita implementarla.

Esta sentencia que empieza por la claúsual SELECT va a terner hasta 5 posibles claúsulas, de las cuales solo son obligatorias las dos primeras 

 SELECT
 FROM

Las 5 posibles que estudaremos son 

  SELECT 
  FROM
  WHERE
  GROUP BY
   HAVING
 (ORDER BY)


Icon key points.gif

Importante (lo estudiaremos)

  • Having solo se usa como opción en el group by.
  • Order by es un criterio de ordenación y no se considera como cláusula propiamente dicho


  • Usando el álgebra relacional y permite recuperar un subconjunto de los datos de la base de datos.
  • Esta sentencia devuelve un conjunto de tuplas como resultado de ejecutar la consulta.
  • Las operaciones del algebra relacional son
Proyecciones
Selecciones
Producto cartesiano
Join

Claúsula Select

Dml select t1.png

Dml select t2.png

Dml select t3.png

Dml select t4.png

Dml select t5.png

Dml select t6.png

Dml select t7.png

Dml select t8.png

Dml select t9.png

Dml select t10.png

Uso de funciones
  • Podemos usar funciones sobre todas las filas y obtener el resultado.
  • Algunas funciones típicas son
Funciones de carácter

Dml func 01.png

Descripción de las funciones

Dml func 02.png
Dml func 03.png

Ejemplos

Dml func 04.png
Dml func 05.png

Funciones con valores numércios

Dml func 06.png
Dml func 07.png

Funciones con fechas

Dml func 08.png
Dml func 09.png
Dml func 10.png

Funciones de agregación o grupo

  • Son funciones que se aplican a un conjunto de valores de diferentes filas
  • Nos darán como resultado un sólo valor
  • Conviene renombrar la columna

Dml func 11.png
Dml func 12.png
Dml func 13.png


Icon activity.jpg
Realiza las siguientes consultas
  • El usuario que más cobra
  • El trabajo más remunerado
  • La remuneración media, en número de trabajos y el que más y menos se paga
  • El empleado con la fecha de contratación mayor y menor
  • El total de comisiones pagadas a los empleados




From

  • Esta es una operación de un producto cartesiano
  • El producto cartesiano consiste en juntar todas las tuplas de una relación con cada una de las tuplas de otra
  • Genera gran cantidad de información, por ese motivo los gestores de bases de datos intentan hacerlo lo último


Icon activity.jpg
probar from
  • Realiza un producto cartesiano entre empleados y departamentos



  • En el from además de una relación o tabla, puede ir cualquier cosa que me devuelva una relación, incluso una consulta


Icon activity.jpg
probar from

Realiza un producto cartesiano entre empleados y departamentos, solo nombres, y fuérzalo en el from.



Where

  • La claúsura where establece la operación de selección.
  • Es decir de todas las filas, indica cuales son las que queremos seleccionar.

Where1.png

  • En esta claúsura, utiliza operadores de comparación para establecer el criterio de las filas seleccionadas

Dml func 14.png
Dml func 15.png
Dml func 16.png
Dml func 17.png
Dml func 18.png
Dml func 19.png
Dml func 20.png
Archivo:Dml func 31.png
Dml func 22.png

Ejercicios



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 last_name, salary, decode (commission_pct, 
                                                NULL, 'No' ,
                                                'Yes') AS "comisión"
FROM employees

Ej6.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';

Ej7.png 

SELECT COUNT(*) 
FROM employees
WHERE last_name LIKE '%n'

Ej8.png 

SELECT COUNT(*)
FROM employees
WHERE last_name LIKE '%n';
--or
SELECT COUNT(*)
FROM employees
WHERE SUBSTR(last_name, -1) = '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;

Ej10.png 

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

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;

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;

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;

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);

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;

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')));

Ej21.png 

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





Funciones de conversión de tipos

Func conv1.png

Func conv1.png

Func conv2.png

Func conv3.png

Func conv4.png

Func conv5.png

Func conv6.png

Func conv7.png

Func conv8.png

Func conv9.png

Func conv10.png

Funciones de agrupación o grupo

  • Son funciones que nos va a mostrar un valor por cada conjunto de filas a las que se aplica la función

Sql group1.png

Sql group2.png

Sql group3.png

Sql group4.png

Sql group5.png

Sql group6.png

Sql group7.png

Sql group8.png

Sql group9.png

Sql group10.png

Sql group11.png

Sql group12.png

Sql group13.png

Sql group14.png

Sql group15.png

Sql group16.png

Sql group17.png

Sql group18.png

Actividades de funciones y agregados

Ej func 01.png 

SELECT sysdate "Date"
FROM  dual;

Ej func 01.png 

 

Ej func 02.png 

SELECT employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary"
FROM employees;

Ej func 03.png 

SELECT INITCAP(last_name) "Name", LENGTH(last_name) "Length"
FROM employees
WHERE last_name LIKE 'J%' OR last_name LIKE 'M%' OR  last_name LIKE 'A%'
 ORDER BY last_name ;

Ej func 04.png 

SELECT INITCAP(last_name) "Name", LENGTH(last_name) "Length"
FROM employees
WHERE last_name LIKE '&start_letter%'
ORDER BY last_name;

Ej func 05.png 

SELECT INITCAP(last_name) "Name", LENGTH(last_name) "Length"
FROM employees
WHERE last_name LIKE UPPER('&start_letter%' )
ORDER BY last_name;

Ej func 06.png 

SELECT last_name, ROUND(MONTHS_BETWEEN(
SYSDATE, hire_date)) MONTHS_WORKED
FROM
employees
ORDER BY months_worked;

Ej func 07.png 

SELECT last_name, LPAD(salary, 15, '$') SALARY
FROM employees;

Ej func 08.png 

SELECT rpad(last_name, 8)||' '|| rpad(' ', salary/1000+1, '*') EMPLOYEES_AND_THEIR_SALARIES
FROM employees
ORDER BY salary DESC;

Ej func 09.png 

SELECT last_name, trunc((SYSDATE-hire_date)/7) AS TENURE
FROM employees
WHERE department_id = 90
ORDER BY TENURE DESC

Ej func 10.png 

SELECT last_name, hire_date,
TO_CHAR(hire_date, 'DAY') DAY
FROM employees
ORDER BY TO_CHAR(hire_date - 1, 'd');

Ej func 11.png 

SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') COMM
FROM employees

Ej func 12.png 

SELECT job_id, decode (job_id,
'ST_CLERK', 'E'
'SA_REP', 'D'
'IT_PROG','C'
'ST_MAN','B'
'AD_PRES','A'
'0')GRADE
FROM employees;

Ej func 13.png 

SELECT last_name, hire_date,
TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),'MONDAY'),
'fmDay, "the" Ddspth "of" Month, YYYY') REVIEW
FROM
employees;

Archivo:Ej func 14.png 

 

Ej func 15.png 

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

Ej func 16.png 

SELECT 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;

Ej func 17.png 

SELECT job_id, COUNT(*)
FROM employees
WHERE job_id = '&job_title'
GROUP BY job_id;

Ej func 18.png 

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

Ej func 19.png 

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

Ej func 20.png 

SELECT 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;

Ej func 21.png 

columna adecuadas.
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;

Ej func 22.png 

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",
FROM employees
GROUP BY job_id;
Obtenido de «https://es.wikieducator.org/index.php?title=Usuario:ManuelRomero/SQL&oldid=23616»