Unidad 2 Lenguaje de Manipulación de Datos (DML)felipealanis.org/Cursos ITD/Taller de Bases de...
Transcript of Unidad 2 Lenguaje de Manipulación de Datos (DML)felipealanis.org/Cursos ITD/Taller de Bases de...
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 1
Unidad 2
Lenguaje de Manipulación de Datos (DML)
2.1 Consultas
2.2 Vistas
2.2.1 From
2.2.2 Join
2.3 Funciones de agrupación, ordenamiento
2.4 Subconsultas
2.5 Operadores Set
2.6 Inserción, eliminación y modificación de
tuplas en forma múltiple.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 2
Renombrar la base de datos TallerBD1 a TallerBD2.
1. SQL Server lo permite de manera simple.
2. MySQL no tiene opción de renombrar en algunas versiones por lo que hay que hacer lo siguiente:
create database TallerBd2rename table TallerBD1.alumnos
to TallerBd2.alumnosrename table TallerBD1.dominiogenero
to TallerBd2.dominiogenero
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 3
Crear una tabla llamada InasistAlum, establecer integridadreferencial con la tabla Alumnos. En las páginas siguientes se indica la forma de añadir las tuplas con DML.El atributo FechaHora debe ser datetime.
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 4
Para SQL Server ejecute las siguientes expresiones DML para añadir las tuplas.
insert into InasistAlum values (1,'2001-03-05 16:00','Evento Deportivo')insert into InasistAlum values (1,'2001-03-06 16:00','Enfermedad')insert into InasistAlum values (1,'2001-03-07 16:00','Injustificada')insert into InasistAlum values (1,'2001-03-08 16:00','Injustificada')
insert into InasistAlum values (3,'2001-03-01 09:00','Enfermedad')insert into InasistAlum values (3,'2001-03-01 10:00','Injustificada')insert into InasistAlum values (3,'2001-03-01 11:00','Injustificada')
insert into InasistAlum values (4,'2001-03-09 16:00','Enfermedad')
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 5
MySQL:
insert into InasistAlum values (0,1,'2001-03-05 16:00','Evento Deportivo');insert into InasistAlum values (0,1,'2001-03-06 16:00','Enfermedad');insert into InasistAlum values (0,1,'2001-03-07 16:00','Injustificada');insert into InasistAlum values (0,1,'2001-03-08 16:00','Injustificada');
insert into InasistAlum values (0,3,'2001-03-01 09:00','Enfermedad');insert into InasistAlum values (0,3,'2001-03-01 10:00','Injustificada');insert into InasistAlum values (0,3,'2001-03-01 11:00','Injustificada');
insert into InasistAlum values (0,4,'2001-03-09 16:00','Enfermedad');
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 6
Obtener las tuplas de los estudiantes que faltaron a las 4 pm (sin importar la fecha).
SQL Server:
select * from InasistAlumwhere convert(time,FechaHora)='16:00'
Ahora ejecute la consulta de la siguiente forma:
select convert(date,FechaHora),Motivo,idAlumnofrom InasistAlumwhere convert(time,FechaHora)='16:00'
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 7
MySQL:
select * from InasistAlumwhere time(FechaHora)='16:00:00'
Ahora ejecute la consulta de la siguiente forma:
select date(FechaHora),Motivo,idAlumnofrom InasistAlumwhere time(FechaHora)='16:00:00'
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 8
Obtener las tuplas correspondientes a estudiantes que faltaron por la mañana (inclusive a las 12pm):
SQL Server:
select * from InasistAlum Where convert(time,FechaHora)<='12:00'
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 9
Obtener los idAlumno de los estudiantes que faltaron por la mañana (inclusive a las 12pm):
SQL Server:
select idAlumno from InasistAlum Where convert(time,FechaHora)<='12:00'
Este resultado no es la respuesta exacta a lo que se solicita porque, si solo faltó el alumno 3 a esa hora, en la lista solo debiera aparecer una vez su id.
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- |10
SQL Server:
select distinct idAlumno from InasistAlum Where convert(time,FechaHora)<='12:00'
2.1 Consultas
11
Modificar la tabla ALUMNOS, crear la tabla PERSONAS con las tuplas que se indican y establecer la integridad referencial necesaria entre Personas, Alumnos e InasistAlum.
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 12
(SQL SERVER)
set dateformat DMY
insert into Personas values ('Parejita','López','Zarco',123,'Cd de México','México','07-02-1981','L1')
insert into Personas values ('Johaness','Gutenberg','Carlomagno',1,'Mainz','Alemania','12-01-1398','G2')
insert into Personas values ('Benito','Juárez García','Monte Albán',100,'Cd de México','México','21-03-1806','J4')
insert into Personas values ('Luis','Pasteur','Campos Elíseos',234,'París','Francia','20-03-1850','P1')
insert into Personas values ('Abraham',NULL,'Calle del camello',347,'Jerusalén','Israel','11-04-1890','A0')
insert into Personas values ('José','Revueltas','Negrete',1002,'Durango','México','24-03-1982','R7')
insert into Personas values ('Lorena','Ochoa','Fresno',1410,'Guadalajara','México','23-06-1981','O1')
insert into Personas values ('Aristóteles',NULL,'Templo Atenea',542,'Atenas','Grecia','23-07-1905','A1')
insert into Personas values ('Tchaikovski',NULL,'Plaza Roja',471,'Moscú','Rusia','13-08-1920','T4')
insert into Personas values ('Botticelli',NULL,'Filipo Lippi',2,'Florencia','Italia','07-09-1919','B9')
insert into Personas values ('José Luis','López','Francisco Zarco',123,'Cd de México','México','09-03-1961','JLL01')
insert into Personas values ('Friele','Gensfleisch','Carlomagno',1,'Mainz','Alemania','09-03-1370','GF7')
insert into Personas values ('Antonio','Maza','Guelaguetza',201,'Oaxaca','México','15-05-1780','AM01')
insert into Personas values ('Margarita','Maza','Monte Albán',100,'Cd de México','México','19-08-1820','MM01')
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 13
insert into Alumnos values ('98040151','Prepa PUMAS',1)
insert into Alumnos values ('97040587','Palacio Nacional',3)
insert into Alumnos values ('97040014','Colegio Vizcaya',6)
insert into Alumnos values ('96040121','LPGA',7)
insert into Alumnos values ('98040150','Colegio Alemán',2)
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 14
La siguiente consulta contiene el número de control, la fecha y hora así como el motivo de inasistencia de quienes faltaron a las 4 pm.
SQL Server:
select NumControl,FechaHora,Motivofrom Alumnos,InasistAlumwhere convert(time,FechaHora)='16:00' and
Alumnos.idAlumno=InasistAlum.idAlumno
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 15
La siguiente consulta contiene el número de control, la fecha y hora así como el motivo de inasistencia de quienes faltaron a las 4 pm.
SQL Server:
select NumControl,FechaHora,Motivo from Alumnos a,InasistAlum iwhere convert(time,FechaHora)='16:00' and
a.idAlumno=i.idAlumno
Observe que para simplificar la consulta se renombra la tabla (renombrado solo para efectos de la consulta).
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 16
La siguiente consulta contiene el número de control, la fecha y hora así como el motivo de inasistencia de quienes faltaron a las 4 pm.
MySQL:
select NumControl,FechaHora,Motivo from Alumnos,InasistAlumwhere time(FechaHora)='16:00' and
Alumnos.idAlumno=InasistAlum.idAlumno
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 17
La siguiente consulta contiene el número de control, la fecha y hora así como el motivo de inasistencia de quienes faltaron a las 4 pm.
MySQL:
select NumControl,FechaHora,Motivo from Alumnos a,InasistAlum iwhere time(FechaHora)='16:00' and
a.idAlumno=i.idAlumno
Observe que para simplificar la consulta se renombra la tabla (renombrado solo para efectos de la consulta).
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 18
Ejercicio:
Escribir una expresión de consulta para obtener el número de control, nombre del alumno, fecha y hora así como el motivo de todas las inasistencias.
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 19
Obtener una lista de los datos de los alumnos que incluyen la palabra “Colegio” en la escuela de procedencia.
SQL Server/MySQL:
select * from Alumnos where EscuelaProcede like '%colegio%'
Obtener una lista de los estudiantes que contienen dígitos diferentes a “04” en las posiciones 3ª y 4ª del número de control.
select * from Alumnos where NumControl not like '__04%'
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 20
Obtenga una lista de los datos de los alumnos que nacieron en la “década de los 40”.
SQL Server/MySQL:
select * from Alumnos where year(fechaNacimiento)
between 1940 and 1949
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 21
Añada a la Base de Datos TallerBD2,las tablas que se indican en las diapositivas siguientes, establezca las restricciones de integridad necesarias e inserte las tuplas indicadas (puede añadir más de las que se muestran).
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 22
insert into Maestros values ('PP11','Doctorado',15000,4)insert into Maestros values ('AA00','Licenciatura',12000,5)insert into Maestros values ('AA11','Licenciatura',12500,8)insert into Maestros values ('GG22','Maestría',13000,2)insert into Maestros values ('TT44','Maestría',25000,9)insert into Maestros values ('BB99','Maestría',20000,10)
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 23
insert into Materias values ('1000','ISC-2345','Algebra Lineal',5,0)
insert into Materias values ('1100','ISC-2360','Fundamentos de Bases de Datos',3,2)
insert into Materias values ('1500','ISC-4300','Taller de Bases de Datos',0,4)
2.1 Consultas
Columna Calculada (computed en SQL Server, generated en MySQL)
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 24
insert into Grupos values (2,'2016B','4Z','L Ma Mi J V 19-20')insert into Grupos values (3,'2016B','5W','L Ma Mi J 13-14')insert into Grupos values (3,'2016A','5W','L Ma Mi J 13-14')insert into Grupos values (1,'2016V','1A','L Ma Mi J 8-9')insert into Grupos values (1,'2016V','1B','L Ma Mi J 14-15')
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 25
insert into GruposMaestro values (1,3)insert into GruposMaestro values (3,2)insert into GruposMaestro values (4,1)insert into GruposMaestro values (5,1)
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 26
A partir de las tablas anteriores escriba una consulta para mostrar los grupos ofrecidos indicando, además de la información de cada grupo, la clave interna de la materia, nombre de la materia y nombre completo del profesor asignado (los grupos sin profesor asignado no deben mostrarse). Debe resultar una tabla como la siguiente:
2.1 Consultas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 27
Tarea para exentar examen
1. Considere una base de datos con las tablas Personas, Alumnos y Maestros y escriba una aplicación para dar de alta, modificar y eliminar alumnos de la BD.
2. Se solicitarán todos los datos, tanto los que corresponden como persona y como alumno. Los datos se guardarán en la tabla que corresponda.
3. En personas debe considerarse el atributo género.4. Deben establecerse las restricciones de llave única, de
dominio e integridad referencial y la aplicación debe atrapar los errores.
5. Puede escribir el programa en cualquier lenguaje pero el DBMS debe ser SQL Server o MySQL.
6. Fecha Límite de entrega: 1 día antes del examen.7. Vea los ejemplos de conectividad de la Unidad 6.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 28
2.2 Vistas
Son objetos de la B.D. equivalentes a unaexpresión de consulta que pueden sertratados como una tabla para efectos deconsulta.
Algunos DBMS permiten la edición dedatos a través de una vista como si fueranuna tabla.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 29
Al ser expresiones de consulta, cadavez que se abre una vista, se realizala consulta correspondiente y losdatos resultantes están actualizadosal momento de la ejecución de laconsulta.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 30
Algunos DBMS pueden incorporar elconcepto de vistas materializadas,que significa que se guarda en unatabla virtual el resultado de la últimaconsulta, actualizándola en formacontinua para evitar realizar laconsulta en forma completa.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 31
Objetivos:
Permiten que los usuarios del DBMSvean los datos de la forma másconveniente de acuerdo a su nivel deconocimientos y experiencia.
Incluso para los usuarios especializados,las vistas simplifican los esquemaspara facilitar otras consultas.
2.2 Vistas
32
A partir de este esquema, crear una consulta para obtener todos los datos de la tablas Personas y Alumnoscorrespondientes a los Alumnos
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 33
select Alumnos.idAlumno, NumControl, CURP,Nombre,Apellidos, Calle, NumExt, Poblacion,Pais, FechaNacimiento, EscuelaProcede,Alumnos.idPersona
from Alumnos,Personaswhere Alumnos.idPersona=Personas.idPersona
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 34
creación de la Vista vAlumnos
create view vAlumnos asselect Alumnos.idAlumno, NumControl, CURP,
Nombre,Apellidos, Calle, NumExt, Poblacion,Pais, FechaNacimiento, EscuelaProcede,Alumnos.idPersona
from Alumnos,Personaswhere Alumnos.idPersona=Personas.idPersona
(Permite realizar otras consultas de manera más simple. idPersona se incluye en la Vista para poder relacionarla con otras Vistas o Tablas.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 35
Se pueden hacer consultas sobre la Vista:
select * from vAlumnos
select NumControl,Nombre,Apellidosfrom vAlumnos where NumControl like '98%'
select * from vAlumnos order by Pais, Poblacion
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 36
2.2 Vistas
En seguida se resolverá la consultasolicitada al final del tema 2.1,usando Vistas para simplificar lasolución.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 37
2.2 Vistas
1er Paso:Crear una Vista llamada vGrupos para combinar las tablas Gruposy Materias (la Vista resultante deberá contener los atributos que se muestran en la siguiente diapositiva).
La sintaxis para la creación de la vista vGrupos es createview vGrupos as <expresión de consulta>
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 38
2.2 Vistas
La vista vGrupos debe verse de esta manera.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 39
2.2 Vistas
2º PasoCrear una Vista llamada vMaestros para combinar las tablas Maestros y Personas con los atributos de ambas.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 40
2.2 Vistas
La Vista vMaestros debe verse de esta manera.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 41
2.2 Vistas
3er PasoCrear una Vista llamada vGruposMaestro para combinar la tabla GruposMaestro con las vistas vGrupos y vMaestros (debe contener todos los atributos de ambas vistas excepto los id)
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 42
2.2 Vistas
Esta es la vista resultante vGruposMaestro.
Esta vista se usará para obtener la respuesta a la consulta que solicitaba Periodo, CLaveInterna, NombreMateria, Paquete, Horario y Nombre del Maestro.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 43
2.2 Vistas
¿Por qué conviene tener todos los atributos de las tablas involucradas en una vista?
Respuesta: No sabemos que consulta puede requerirse en el futuro. Por ejemplo: Domicilios de todos los profesores que imparten clases en el paquete 5Z en el periodo 2017B.
44
Ya contamos con estas tablas, hay que crear las de la página siguiente
2.2 Vistas
45
Crear una expresión de consulta para obtener los datos de contacto correspondientes a las personas Relacionadas con los Alumnos
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 46
insert into AlumnosRelaciones values (1,11,'Padre')
insert into AlumnosRelaciones values (1,11,'Tutor Legal')
insert into AlumnosRelaciones values (2,13,'Tutor Legal')
insert into AlumnosRelaciones values (5,12,'Padre')
insert into AlumnosRelaciones values (2,14,'Esposa')
insert into AlumnosRelaciones values (1,11,'Emergencia')
insert into FormasContacto values (1,'Teléfono Fijo','819-27-37')
insert into FormasContacto values (1,'Email','[email protected]')
insert into FormasContacto values (3,'Email','[email protected]')
insert into FormasContacto values (4,'Teléfono Fijo','818-04-11')
insert into FormasContacto values (5,'Teléfono Celular','618 8189875')
insert into FormasContacto values (6,'Teléfono Fijo','803-17-13')
insert into FormasContacto values (7,'Teléfono Fijo','800-06-06')
insert into FormasContacto values (8,'Teléfono Fijo','801-00-00')
insert into FormasContacto values (9,'Teléfono Fijo','874-65-02')
insert into FormasContacto values (10,'Teléfono Fijo','830-77-55')
insert into FormasContacto values (11,'Teléfono Fijo','829-17-12')
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 47
En primer lugar se crea una Vista llamada vPersonasFContacto (necesaria para resolver la consulta solicitada):
create view vPersonasFContacto asselect Personas.idPersona, CURP, Nombre,
Apellidos, Calle, NumExt, Poblacion, Pais,FechaNacimiento, TipoContacto, Valor Contacto
from Personas left join FormasContactoon FormasContacto.idPersona=Personas.idPersona
Esta Vista nos muestra los datos de contacto de todas las personas, Alumnos, Padres, Tutores, etc. Se usa LEFT JOIN para asegurarse en la consulta que aparezcan los datos de la tabla de la IZQUIERDA aunque no haya correspondencia en la tabla de la derecha.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 48
Consulta solicitada
select NumControl,concat(A.Nombre,' ',A.Apellidos)NombreAlumno, Relacion,concat(PFC.Nombre,' ',PFC.Apellidos)NombreRelacion,concat(PFC.Calle,' ',PFC.numext,' ',PFC.Poblacion,
' ',PFC.Pais) DomicilioRelacion,TipoContacto,Contacto
from vAlumnos A,AlumnosRelaciones AR, vPersonasFContacto PFC
where A.idAlumno=AR.idAlumno andAR.idPersona=PFC.idPersona
Si uno de los atributos a concatenar es nulo, el resultado completo es nulo si se concatena usando +, además concat evita la necesidad de usar cast
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 49
A partir de las vistas que ya tenemos creadas, escribir las diferentes expresiones de consulta que se solicitan para resolver los problemas siguientes:
1. Obtener una lista de los grupos que no tienen profesor asignado.• Para obtener mas fácilmente el resultado de esta solicitud,
conviene modificar la vista vGruposMaestro para usar un LEFT JOIN en vez de FROM únicamente, de tal manera que en la vista aparezcan todos los grupos independientemente de que tengan profesor asignado o no.
• El LEFT JOIN sería entre vGrupos y GruposMaestro.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 50
INNER JOIN … ON se comporta básicamente como FROM … WHERE.
Sin embargo, LEFT JOIN … ON se comporta de una interesante manera: se incluyen todas las tuplas de la tabla de la IZQUIERDA aunque no tengan correspondencia en la tabla de la derecha de acuerdo a la condición establecida en ON.
2.2 Vistas
La vista vGruposMaestro hecha con LEFT JOIN proporciona el siguiente resultado, lo que permite obtener fácilmente la consulta número 1.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 52
Con los ejemplos anteriores se demuestra uno delos objetivos de las vistas: Ocultar lacomplejidad de los esquemas, sin embargohay otros usos importantes que enseguida sedescriben.
Mecanismo de seguridad. Los usuarios de losdatos (Usuarios Sofisticados o programadoresinexpertos solo tienen acceso a los datos que elDBA decida).
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 53
Capa para soporte de código heredado. Paraevitar desechar código funcional al corregir elesquema, modificar o crear nuevas tablas, secrean vistas equivalentes a las tablas antiguas y seeliminan las tablas antiguas.
Las nuevas tablas deberán tener establecida laintegridad referencial con tablas que se conservandel esquema.
2.2 Vistas
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 54
Continuemos con otras consultas:
2. El nombre y domicilio de los profesores que impartieron clases al paquete 5Z en el semestre 2015B.
3. El numero total de grupos que se ofrecieron en cada semestre. La información debe ir ordenada de mayor a menor en base al total.
4. El numero total de horas que impartió cada profesor en cada uno de los semestres, ordenado por semestre y por profesor.
2.3 Ordenamiento, Agrupación
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 55
Continuación ….
5. Una lista de RFC, Nombre de los profesores y total de horas que impartieron en un semestre, pero solo de aquellos que tuvieron 10 o menos horas impartidas.
6. Una lista de RFC, Nombre, Máximo grado de estudios de los profesores que impartieron clases los sábados. También hay que indicar en que períodos impartió clases los sábados.
7. Una lista con el RFC, Nombre y número total de horas de los profesores que impartieron clases los sábados. Se requiere el total de horas impartidas, no solo las de los sábados, pero solo se deben incluir los profesores y los períodos con clases impartidas los sábados.
2.3 Ordenamiento, Agrupación
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 56
2.4 Subconsultas
El uso de subconsultas no es un estándar en SQL porque tiende a complicar el código, como se puede ver en una expresión como la siguiente, que obtiene el domicilio y datos de contacto de los estudiantes.
select NumControl,Nombre,Calle,NumExt,TipoContacto,Valorfrom (select
NumControl,Nombre,Calle,NumExt,Personas.idPersonafrom Alumnos,Personas whereAlumnos.idPersona=Personas.idPersona)
A2 left join FormasContactoon A2.idPersona=FormasContacto.idPersona
El código en rojo equivale a la vista vAlumnos con la que se resuelve esta consulta de manera mas simple.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 57
2.5 Operadores de ConjuntosOperador IN (Pertenencia)
Obtener los números de control de alumnos que no son maestros.
select Control from Alumnos
where idPersona not in
(select idPersona from Maestros)
Modifique la expresión para obtener el Número de Control y el Nombre.
Modifique la consulta para que resulten los números de control de los alumnos que son maestros.
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 58
2.5 Operadores de ConjuntosOperador IN (Pertenencia)
Añada un buen número de tuplas a la tabla Materias para ejecutar la siguiente consulta (Obtener una lista de las materias que tienen en mayor número de créditos):
select * from Materias where creditos in (select max(creditos) from Materias)
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 59
2.5 Operadores de Conjuntos
select * from Materias where creditos in (select min(creditos) from Materias)
select * from Materias where creditos >= all (select creditos from Materias)
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 60
Encontrar a partir de la vista vGrupos y de la tabla GruposMaestro los datos de los grupos que no tienen profesor asignado.
select * from vGruposwhere
vGrupos.idGrupo not in (select idGrupo from GruposMaestro)
2.5 Operadores de Conjuntos
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 61
Ejemplo:
Eliminar la única tupla de inasistencia de aquellos alumnos que solo
tengan una.
delete from InasistAlum where IdAlumno in
(select IdAlumno from InasistAlum
group by idAlumno having count(*)=1)
Modifque la consulta para que solo elimine la única falta injustificada
(dado el caso).
2.6 INSERT, UPDATE, DELETE con una
expresión de consulta
Disminuya un punto en todas las
calificaciones de aquellos alumnos con
mas de 2 inasistencias injustificadas.
update Calificaciones
set Result = Result - 1
where IdAlumno in
(select IdAlumno from InasistAlum
where Motivo=‘Injustificada’
group by IdAlumno having count(*)>2)
2.6 INSERT, UPDATE, DELETE con una expresión de consulta
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 63
insert puede combinarse con select para añadir varias tuplas a la vez.
Ejemplo: A partir de las tablas Alumnos e InasistAlum, añadir a la tabla MensajesCorreo las tuplas correspondientes a los mensajes de correo que se deben enviar al padre del estudiante para avisarle de las
inasistencias.
2.6 INSERT, UPDATE, DELETE con una expresión de consulta
Taller Bases de Datos ISC Ing. Felipe Alanís González -ITD- 64
insert into MensajesCorreoselect EmailPadre,'su hijo '+Nombre+
' tiene una inasistencia el '+cast(FechaHora as varchar)+' motivo '+Motivo
from Alumnos a,InasistAlum i where i.idAlumno=a.idAlumno
2.6 INSERT, UPDATE, DELETE con una expresión de consulta