UF2176_R1 Codigos

GRUPOS DE COMANDOS SQL


DDL : crear y modificar la estructura de una bbdd

• CREATE, ALTER, DROP, TRUNCATE 

DML : seleccionar registros de una bbdd, consultar, insertar, actualizar

• SELECT, INSERT, UPDATE, DELETE 

DCL : proporciona seguridad

• GRANT, REVOKE 

TCL : gestion de cambios de datos

• COMMIT, ROLLBACK, SAVE POINT 

CLAUSULAS

• FROM, WHERE, GROUP BY, HAVING, ORDER BY

Herramienta para hacer pruebas y ejercicios de sentencias SQL en línea

Si no tienes un motor de base de datos a tu disposición y quieres correr algunas instrucciones de SQL, te recomiendo este sitio web:

http://sqlfiddle.com

Puedes seleccionar de distintos manejadores de bases de datos

Después pones las instrucciones para crear tu tabla e insertar algunos registros de prueba y oprimes Build Schema.

Una vez hecho lo anterior tendrás tu base de datos lista y con registros para probar, ahora del lado derecho teclea tu consulta y oprime Run SQL


EXPORTAR DE ACCESS A MYSQL
Desde la Tabla - Menu datos externos - Excel - Aceptar Abrir con Excel y guardar como OpenDocumente (ods)

Abrir phpmyadmin Crear una BBDD - seleccionarla - importar *.ods (ojo formato : OpenDocument)
habilitar la primera fila contiene nombres
en excel el formato de las fechas 'aaaa-mm-dd',
en mysql cambiar el formato del campo a Date

Sintaxis (En otros motores Auto_increment, Autonumerico)
IDENTITY [ (seed , increment) ]
Argumentos
seed
    Es el valor que se utiliza para la primera fila cargada en la tabla.
increment
    Se trata del valor incremental que se agrega al valor de identidad de la anterior fila cargada.

CREAR BASES DE DATOS
CREATE DATABASE pruebas;

MOSTRAR BASES DE DATOS
 SHOW DATABASES;

ENTRAR EN MYSQL DESDE CONSOLA
 mysql -u root -p la contraseña en blanco (si no se ha cambiado nunca)

USAR UNA BASES DE DATOS
 USE pruebas

CREAR TABLA
 CREATE TABLE servicios ( idSERVICIOS int NOT NULL , NombreSERVICIO char(10) NOT NULL, Descripcion char(30) NOT NULL, Precio money NOT NULL, IVA numeric (5,2) NOT NULL, Fecha date NOT NULL, PRIMARY KEY ( idSERVICIOS ), foreign key (nombreServicio) references tipo_servicio ( NombreSERVICIO ) ) ;
CREATE TABLE profesor( pk_idprofe int, nombre_profe varchar(50), direccion_profe varchar(50), telefono_profe varchar(9); constraint pk_idprofe primary key(pk_idprofe) ) CREATE TABLE alumnos( pk_idalumno int, nombre_alumno varchar(100), edad_alumno int, fk_idcarrera int, constraint pk_idalumno2 primary key(pk_idalumno), constraint fk_idcarrera2 foreign key(fk_idcarrera) references carrera(pk_idcarrera) )

INSERTAR UN REGISTRO
INSERT INTO pruebas ( nif, nombre, apellidos, edad) VALUES ( 11111, "pepe", "apellidos", 22); INSERT INTO pruebas VALUES ( 11111, "pepe", "apellidos", 22);

LISTA TODOS LOS DATOS DE UNA TABLA
SELECT * FROM tabla

SELECCIONA TODOS CAMPOS DE LOS REGISTROS CUYO DEPARTAMENTO SEA 1, 2 O 3
SELECT * FROM tabla WHERE departamento IN (1,2,3)

SELECCIONAR REGISTROS ENTRE DOS FECHAS
SELECT * FROM tabla WHERE fecha >='2000-12-31' AND fecha <='2002-01-01' SELECT * FORM tabla BETWEEN '2000-12-31' AND '2002-01-01'

MUESTRA REGISTROS CUYO CAMPO seccion SEA IGUAL A deportes, ORDENANDOLOS DE FORMA DESCENDENTE (en la segunda ORDENA POR seccion y precio)
SELECT * FROM tabla WHERE seccion='Deportes' ORDER BY seccion DESC SELECT * FROM tabla WHERE seccion='Deportes' ORDER BY seccion, precio

SUMA EL CAMPO precio AGUPANDOLO POR EL CAMPO seccion
SELECT seccion, SUM(precio) AS sumaPrecio FROM tabla GROUP BY seccion
SUM = suma AVG = media COUNT = cuenta MAX = maximo MIN = minimo

HAVING
MUESTRA LAS DIFERENTES secciones, SUMA EL CAMPO precio AGRUPO POR secciones Y ORDENA POR LA SUMA de los precios DE LA seccion DEPORTES
SELECT seccion, SUM(precio) AS sumaPrecio FROM tabla GROUP BY seccion ORDER BY sumaPrecio HAVING seccion='Deportes' *** HAVIN sustituye a WHERE en las consultas de agrupación ****

CUENTA LOS REGISTROS AGRUPADOS POR poblacion
SELECT poblalcion, COUNT(pk_codigo) FROM tabla GROUP BY poblacion

REDONDEA UNA CANTIDAD CON UN NUMERO DE DECIMALES
SELECT articulo, seccion, precio ROUND (precio*1.21,2) AS precioConIva FROM tabla ROUND redondea (valor, decimales)

CALCULA LOS DIAS ENTRE DOS FECHA
SELECT articulo, fecha, NOW() AS DiaDeHoy, DATEDIFF(NOW(), fecha) FROM tabla

DA FORMATO A LAS FECHAS
SELECT articulo, fecha, DATE_FORMAT(NOW(), '%D-%M') AS DiaDeHoy FROM tabla

UNION = UNE DOS TABLAS CON EL MISMO Nº DE CAMPOS Y ESTOS DE TIPO CAMPATIBLE (UNION los registros repetidos solo los muestra una vez) (UNION ALL muestra también los repetidos) (se puede hacer tantas uniones como haga falta en una consulta)
SELECT * FROM tabla1 WHERE seccion='Deportes' UNION SELECT * FROM tabla2 WHERE seccion='Informatica'

SUBCONSULTAS ESCALONADAS
SELECT articulos, seccion FROM tabla WHERE precio > ( SELECT AVG(precio) FROM productos)

SUBCONSULTAS DE LISTAS (ALL = todos ANY = cualquiera)
SELECT * FROM tabla WHERE precio > ALL (SELECT precio FROM productos WHERE seccion='ceramica')

SUBCONSULTAS con IN y con NOT IN
SELECT articulos, precio FROM productos WHERE codArticulo IN ( SELECT codArticulo FROM pedidos WHERE unidades >20) SELECT empresa FORM clientes WHERE codCliente NOT IN (SELECT codCliente FROM pedidos WHERE formaPago='tarjeta')

Para duplicar una tabla, 
podemos hacer varias cosas, copiar sólo la estructura: SELECT * Into DestinationTableName From SourceTableName Where 1 = 2
porque puede ser útil para casos X, o bien hacer un duplicado exacto de la misma:
 SELECT * INTO MyNewTable FROM MyTable
con la salvedad de que, no se copiarán las constraints o índices. Hay cre crear los indices:
 ALTER TABLE PRUEBA ADD PRIMARY KEY(CODIGO_C)

Insertar filas mediante subconsultas INSERT y SELECT
La subconsulta SELECT de la instrucción INSERT se puede utilizar para agregar valores a una tabla de una o más tablas o vistas. Además, utilizar una subconsulta SELECT permite insertar más de una fila al mismo tiempo.
En el siguiente ejemplo, la instrucción INSERT inserta en una tabla independiente algunos de los datos de todas las filas de una tabla
INSERT INTO MySalesReason SELECT SalesReasonID, Name, ModifiedDate FROM AdventureWorks2008R2.Sales.SalesReason WHERE ReasonType = N'Marketing';


La función CONCAT
La función CONCAT permite concatenar varias cadenas de texto (desde 2 hasta 254) de una manera muy sencilla, tal y como se muestra en la siguiente consulta SQL de ejemplo:
SELECT CONCAT(Nombre,' ',Ape1,' ',Ape2) AS NombreCompleto FROM ( SELECT 'Pedro' AS Nombre, 'Martín' AS Ape1, 'López' AS Ape2 UNION ALL SELECT 'Ramón' AS Nombre, 'Pérez' AS Ape1, NULL AS Ape2 ) AS Personas

Resultado con formato personalizado (ver tipos de cadenas con formato) 
DECLARE @d DATETIME = GETDATE(); SELECT FORMAT(@d, 'dd/MM/yyyy') 'Custom DateTime', FORMAT(@d, 'dd/MM/yyyy hh:mm:ss tt') 'Custom DateTime 2', FORMAT(@d, 'dd/MM/yyyy HH:mm:ss') 'Custom 3 (24 horas)' SELECT FORMAT(123456789,'(###)-##-####') AS 'Teléfono', FORMAT(10,'##.00%') AS 'Porcentaje', FORMAT(100,'00000.00') AS 'Numerico 1', FORMAT(1000000.00,'##,###.00') AS 'Numerico Decimal'


48. Hallar los departamentos que tienen más de tres (3) empleados. Entregar el número de empleados de esos departamentos.
SELECT N.codDepto, D.nombreDpto, N.nro FROM Departamento D, (SELECT codDepto, COUNT(nDIEmp) AS nro FROM Empleado GROUP BY codDepto HAVING COUNT(nDIEmp)>3) N WHERE N.codDepto = D.codDepto; select Departamento.nombreDpto, Empleado.codDepto, count(*) from Empleado, Departamento where Empleado.codDepto=Departamento.codDepto group by Empleado.codDepto, Departamento.nombreDpto having count(*)>3

Caracteres comodín de SQL

Un carácter comodín se utiliza para sustituir cualquier otro carácter (s) en una cadena.
Los caracteres comodín se utilizan con el operador SQL LIKE. El operador LIKE se utiliza en una cláusula WHERE para buscar un patrón especificado en una columna.
Hay dos comodines utilizados junto con el operador LIKE:

• % - El signo de porcentaje representa cero, uno o varios caracteres
• _ - El subrayado representa un solo carácter

Nota: MS Access utiliza un signo de interrogación (?) En lugar del subrayado (_).

En MS Access y SQL Server también puede utilizar:

• [Charlist] - Define conjuntos y rangos de caracteres para coincidir
• [^ Charlist] o [! Charlist] - Define conjuntos y rangos de caracteres NO coincidentes

Los comodines también se pueden utilizar en combinaciones!
Estos son algunos ejemplos que muestran diferentes operadores LIKE con comodines '%' y '_':

LIKE Operator	Description
WHERE CustomerName LIKE 'a%'	Encuentra cualquier valor que comience con "a"
WHERE CustomerName LIKE '%a'	Encuentra cualquier valor que termine con "a"
WHERE CustomerName LIKE '%o%'	Encuentra cualquier valor que tenga "o" en cualquier posición
WHERE CustomerName LIKE '_r%'	Encuentra cualquier valor que tenga "r" en la segunda posición
WHERE CustomerName LIKE 'a_%_%'	Encuentra cualquier valor que empiece por "a" y tenga al menos 3 caracteres de longitud
WHERE ContactName LIKE 'a%o'	Encuentra cualquier valor que comience con "a" y termine con "o"

Orden del concepto	Concepto	Detalle del concepto	Problema resuelto	Problema a resolver
1	Introducción.
2	show databases
3	Creación de una tabla y mostrar sus campos (create table - show tables - describe - drop table)
4	Carga de registros a una tabla y su recuperación (insert into - select)
5	Típos de datos básicos de un campo de una tabla.
6	Recuperación de algunos campos (select)
7	Recuperación de registros específicos (select - where)
8	Operadores Relacionales = <> < <= > >=
9	Borrado de registros de una tabla (delete)
10	Modificación de registros de una tabla (update)
11	Clave primaria.
12	Campo entero con autoincremento.
13	Comando truncate table.
14	Valores null.
15	Valores numéricos sin signo (unsigned)
16	Tipos de datos
17	Tipos de datos (texto)
18	Tipos de datos (numéricos)
19	Tipos de datos (fechas y horas)
20	Valores por defecto.
21	Valores inválidos.

Orden del concepto	Concepto	Detalle del concepto	Problema resuelto	Problema a resolver
22	Atributo default en una columna de una tabla.
23	Atributo zerofill en una columna de una tabla.
24	Columnas calculadas.
25	Funciones para el manejo de cadenas.
26	Funciones matemáticas.
27	Funciones para el uso de fecha y hora.
28	Cláusula order by del select.
29	Operadores Lógicos (and - or - not)
30	Otros operadores relacionales (between - in)
31	Búsqueda de patrones (like y not like)
32	Búsqueda de patrones (regexp)
33	Contar registros (count)
34	Funciones de agrupamiento (count - max - min - sum - avg)
35	Agrupar registros (group by)
36	Selección de un grupo de registros (having)
37	Registros duplicados (distinct)
38	Alias
39	Clave primaria compuesta.
40	Indice de una tabla.
41	Indice de tipo primary.
42	Indice común (index)

Orden del concepto	Concepto	Detalle del concepto	Problema resuelto	Problema a resolver
43	Indice único (unique)
44	Borrar indice (drop index)
45	Creación de índices a tablas existentes (create index)
46	Cláusula limit del comando select.
47	Recuperación de registros en forma aleatoria(rand)
48	Remplazar registros (replace)
49	Agregar campos a una tabla (alter table - add)
50	Eliminar campos de una tabla (alter table - drop)
51	Modificar campos de una tabla (alter table - modify)
52	Cambiar el nombre de un campo de una tabla (alter table - change)
53	Agregar y eliminar la clave primaria (alter table)
54	Agregar índices(alter table - add index)
55	Borrado de índices (alter table - drop index)
56	renombrar tablas (alter table - rename - rename table)
57	Tipo de dato enum.
58	Tipo de dato set.
59	Tipos de datos blob y text.
60	Funciones de control de flujo (if)
61	Funciones de control de flujo (case)
62	Varias tablas (join)
63	Clave foránea.

Orden del concepto	Concepto	Detalle del concepto	Problema resuelto	Problema a resolver
64	Varias tablas (left join)
65	Varias tablas (right join)
66	Varias tablas (cross join)
67	Varias tablas (natural join)
68	Varias tablas (inner join - straight join)
69	join, group by y funciones de agrupamiento.
70	join con más de dos tablas.
71	Función de control if con varias tablas.
72	Variables de usuario.
73	Crear tabla a partir de otra (create - insert)
74	Crear tabla a partir de otras (create - insert - join)
75	Insertar datos en una tabla buscando un valor en otra (insert - select)
76	Insertar registros con valores de otra tabla (insert - select)
77	Insertar registros con valores de otra tabla (insert - select - join)
78	Actualizar datos con valores de otra tabla (update)
79	Actualización en cascada (update - join)
80	Borrar registros consultando otras tablas (delete - join)
81	Borrar registros buscando coincidencias en otras tablas (delete - join)
82	Borrar registros en cascada (delete - join)
83	Chequear y reparar tablas (check - repair)
84	Encriptación de datos (encode - decode)

SQL ALTER TABLE Statement

La instrucción ALTER TABLE se utiliza para agregar, eliminar o modificar columnas de una tabla existente.

La instrucción ALTER TABLE también se utiliza para agregar y eliminar varias restricciones en una tabla existente.
ALTER TABLE - ADD Column

Para agregar una columna en una tabla, utilice la siguiente sintaxis:
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE - DROP COLUMN

Para eliminar una columna de una tabla, utilice la siguiente sintaxis (observe que algunos sistemas de base de datos no permiten eliminar una columna):
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE - ALTER/MODIFY COLUMN

Para cambiar el tipo de datos de una columna de una tabla, utilice la siguiente sintaxis:

SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Oracle 10G and later:
ALTER TABLE table_name
MODIFY column_name datatype;

El operador SQL EXISTS

El operador EXISTS se utiliza para probar la existencia de cualquier registro en una subconsulta.

El operador EXISTS devuelve true si la subconsulta devuelve uno o más registros.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SQL CHECK Constraint 

La restricción CHECK se utiliza para limitar el rango de valores que se puede colocar en una columna. Si define una restricción CHECK en una sola columna, sólo permite ciertos valores para esta columna. Si define una restricción CHECK en una tabla, puede limitar los valores en ciertas columnas basándose en valores de otras columnas de la fila.

SQL CHECK en CREATE TABLE El SQL siguiente crea una restricción CHECK en la columna "Age" cuando se crea la tabla "Personas". La restricción CHECK asegura que no puede tener ninguna persona menor de 18 años: MySQL:

 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );

SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );

Para permitir la denominación de una restricción CHECK y para definir una restricción CHECK en varias columnas, utilice la siguiente sintaxis SQL: MySQL / SQL Server / Oracle / MS Access: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );

SQL CHECK on ALTER TABLE Para crear una restricción CHECK en la columna "Edad" cuando la tabla ya está creada, utilice el siguiente

SQL: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CHECK (Age>=18);

Para permitir la denominación de una restricción CHECK y para definir una restricción CHECK en varias columnas, utilice la siguiente sintaxis SQL:
MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint Para eliminar una restricción CHECK, utilice el siguiente SQL: SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
MySQL:
 ALTER TABLE Persons DROP CHECK CHK_PersonAge;

Cuestiones a responder antes de hacer una consulta
1. ¿Que información queremos visualizar?
2. ¿En qué tablas se encuentra la información?
3. ¿Qué condición deben cumplir las filas resultantes?
4. ¿Queremos ordenar el resultado por alguna columna?

Obtener el número de las etapas donde el nombre de la ciudad de llegada tenga por segunda letra una “O” o donde el nombre de la ciudad de salida lleve dos o más ‘A’s.
1. n.etapas 2. etapa 3. ciudad llegada 2ºletra O ó salida lleve 2 o mas A
SELECT netapa FROM Etapa WHERE llegada LIKE ‘_O%’ OR salida LIKE ‘%A%A%’;

Cuando se va a trabajar con una tabla para hacer consulta entre diferentes tuplas de ella, entonces se utilizan las variables de recorrido

[tabla | variable_recorrido ].columna Es una instancia de la tabla.

Es virtual Por tanto, permiten dar un nombre alternativo a la misma tabla dentro de una consulta. La manera de declarar una variable de recorrido es:

FROM tabla [as] variable_recorrido

Ejemplo: Obtener el nombre de los ciclistas compañeros de equipo de ‘Miguel Induráin’ que sean más jóvenes que él. 

1. ¿En qué tablas se encuentra la información?
 FROM Ciclista

Pero, como se requiere comparar con tuplas de la misma tabla, entonces se necesita tener varias imágenes de ella

2. ¿Qué condición deben cumplir las filas resultantes?

WHERE C2.nombre=‘Miguel Induráin’ AND C1.nomeq = C2.nomeq AND C1.edad < C2.edad;

 3. ¿Qué información queremos visualizar?

SELECT DISTINCT C1.nombre

Por lo tanto la consulta será: SELECT DISTINCT C1.nombre FROM Ciclista C1, Ciclista C2 WHERE C2.nombre=‘Miguel Induráin’ AND C1.nomeq = C2.nomeq AND C1.edad < C2.edad;


51. Entregar un reporte con el numero de cargos en cada departamento y cual es el promedio de salario de cada uno. Indique el nombre del departamento en el resultado.
SELECT Departamento.codDepto, Departamento.nombreDpto, N.CARGO,N.N_CARG, N.SAL_MED FROM DEPARTAMENTO , ( SELECT Empleado.codDepto AS CD, Empleado.cargoE AS CARGO, COUNT(*) AS N_CARG, FORMAT(AVG(EMPLEADO.SALEMP), 'N') AS SAL_MED FROM Empleado GROUP BY Empleado.codDepto, Empleado.cargoE ) N WHERE Departamento.codDepto= N.CD ORDER BY Departamento.codDepto

Prácticas

Práctica 1

• PR-F-001.  Creación y uso de mi primera BD en SQL Server 2008. (pdf)

Práctica 2

• PR-F-002.  Ejercicios de diseño relacional. (pdf)

Práctica 3

Ejercicio resuelto

• PR-F-003.  Enunciado de Ejercicio resuelto de SQL. (pdf)
• PR-F-004.  BD con Datos para Ejercicio resuelto. (sql)
• PR-F-005.  Solución del Ejercicio resuelto. (sql)

Ejercicio propuesto

• PR-F-006.  Ejercicio propuesto de SQL. (pdf)
• PR-F-007.  BD con Datos para realizar el Ejercicio propuesto. (mdb).

Práctica 4

Ejercicio resuelto

• PR-F-008.  Ejercicio resuelto de SQL. (pdf)
• PR-F-009.  Solución. (sql)

Ejercicio propuesto

• PR-F-010.  Ejercicio propuesto de SQL. (pdf)

Práctica 5

Ejercicio resuelto

• PR-F-011.  Prototipo de aplicación de BD. (pdf)
• PR-F-012.  Archivos (zip).

Ejercicios de procedimientos almacenados en SQL Server

Para poder realizar los ejercicios de procedimientos almacenados debes tener la base de datos Hospital, la cual puedes descargar desde el índice o desde los siguientes enlaces: DESCARGAR BD HOSPITAL - DESCARGAR REGISTROS BD HOSPITAL.

Bien, sin más preámbulos iniciemos.

1) Sacar todos los empleados que se dieron de alta entre una determinada fecha inicial y fecha final y que pertenecen a un determinado departamento.

CREATE PROCEDURE FECHASDEPT

@FINICIAL DATETIME,

@FFINAL SMALLDATETIME ,

@DEPT_NO NVARCHAR(10)

AS

SELECT * FROM EMP WHERE FECHA_ALT BETWEEN @FINICIAL AND @FFINAL

AND DEPT_NO = @DEPT_NO

EXEC FECHASDEPT '01/01/1965','01/01/1985',20O