Actividad de diseño

Una empresa de la salud está a punto de inaugurar policonsultorios de múltiples especialidades.
Los pacientes serán atendidos por secretarias que otorgarán los turnos en forma anticipada de acuerdo a la agenda del profesional a quien desean consultar.
Los pacientes pueden tener obras sociales y presentar sus ordenes para ser atendidos.
Algunos profesionales no atienden por todas las obras sociales.
Una vez atendido el paciente, el profesional debe poder registrar las prácticas realizadas, los medicamentos prescriptos, el diagnóstico, las prácticas solicitadas y una descripción general.
Las secretarias también se encargarán de mantener al día información general de prácticas, medicamentos, sus composiciones y acciones terapéuticas y patologías.
En la generalidad de los casos cada médico pordrá acceder solo a las historias clínicas que le corresponden de acuerdo a las consultas o visitas que el atendió, pero también podrá otorgar permisos de acceso a otros profesionales.

Diseñar el modelo utilizando DER y derivación aproximada de tablas, presentándolo por correo a la dirección de gmail de Guillermo Villanueva.

Resolución 2do parcial

Crear las tablas haciendo las validaciones y creando los índices que crea necesarias/os

Solo un ejemplo
CREATE TABLE MovCaja (
NumMov int PRIMARY KEY,
NumCenCosto int,
TipMovimiento int,
Importe numeric(10,4),
NumIns int,
NumCuota int,
Fecha datetime,
FOREIGN KEY (NumCenCosto) REFERENCES CentrosCostos,
FOREIGN KEY (NumIns,NumCuota) REFERENCES Cuotas)
/*Considero Ingreso = 1 Egreso = -1*/

Realizar las actualizaciones de datos necesarias (insert y/o update) para registrar que el alumno: Juan Perez, domiciliado en Alvarado 537, con DNI 26.687.698 se inscriba en el curso de "Cocina internacional con recursos locales", dictado por el profesor Andrés Fournier.

Se resolvía simplemente usando los INSERT correspondientes a las tablas Personas, Alumnos, Profesores, Tematicas, Cursos, Inscripciones



Listado alfabético de alumnos que asisten al curso de "Reparación de automotores diesel"
select p.Nombre
from personas p, alumnos a, inscripciones i, cursos c
where
p.NumPer = a.NumPer and
a.NumAlumno = i.NumAlumno and
i.NumCur = c.NumCur and
c.Descripcion = 'Reparacion de automotores diesel'
order by p.Nombre

Cuotas adeudas por el alumno Juan Perez por todos los cursos tomados
select c.* from cuotas c, inscripciones i
where
c.NumIns = i.NumIns and
i.NumAlumno IN
(select NumAlumno from Alumnos a, Personas p
where a.NumPer=p.NumPer and p.Nombre='Juan Perez') and
c.fecVencimiento <>

Listado alfabético de cursos dictados en el primer trimestre del año en curso con la cantidad de inscriptos en cada uno (sin tener en cuenta que se haya completado o no el cupo)
select c.descripcion, count(*)
from cursos c, inscripciones i
where
c.NumCur=i.NumCur and
MONTH(c.FecInicio) IN (1,2,3) and /*Agrego fecha de inicio a la tabla de cursos*/
YEAR(c.FecInicio) = YEAR(getdate())
GROUP BY c.descripcion
ORDER BY c.descripcion

Listado alfabético de los cursos pendientes de inicio por falta de cupo
select c.descripcion, c.cupo, count(*)
from cursos c, inscripciones i
where
c.NumCur=i.NumCur and
c.FecInicio <= getdate() GROUP BY c.descripcion,c.cupo HAVING count(*)<>

Listado de ingresos en el año en curso, clasificado por tipos de curso impartidos.
select t.Tema, sum(m.Importe)from tematicas t, cursos c, inscripciones i, movcaja mwhere
t.NumTem = c.NumTem and
c.NumCur = i.NumCur and
i.NumIns = m.NumIns and
m.TipMovimiento = 1 and /* Considero 1 para ingreso, -1 para egreso */
YEAR(cu.FecInscripcion) = YEAR(getdate()) /*Agrego fecha de inicio a la tabla de cursos*/
GROUP BY t.Tema

Importes totalizados de movimientos de caja por centros de costo en el mes de octubre’2006
select c.Descripcion, sum(m.tipMovimiento * m.Importe) /* Considero 1 para ingreso, -1 para egreso */
from CentrosCostos c, MovCaja m
where
c.NumCenCosto = m.NumCenCosto and
month(m.Fecha) = 10 and
year(m.Fecha) = 2006 /*Agrego Fecha a la tabla de movimientos de caja*/
GROUP BY c.Descripcion

IS NULL

Continuando con el manejo de valores nulos, existe la forma de consultar si una columna es nula y devolver verdadero si lo es o falso si no lo es, simplemente se debe escribir en la condición: IS NULL o sino IS NOT NULL

Veamos unos ejemplos:
Listar todos los productos que no tengan especificado el IVA:
SELECT * FROM Productos
WHERE pr_iva IS NULL

Recordemos el ejercicio de un práctico anterior de venta de propiedades inmobiliarias en donde se determinaba de antemano cual era el plan de pago. En la tabla de detalles del plan de pago debería existir una fecha de vencimiento de cuota y una fecha de pago efectivo. Entonces un ejemplo podría ser listar todas aquellas cuotas que están vencidas (sin pagar)
SELECT * FROM DetCuotas
WHERE
...dc_fecVto <= getdate() AND
...dc_fecPago IS NULL

Observe que se filtran las filas y solo se muestran aquellas en que la fecha de vencimiento es anterior a hoy y la fecha de pago está en NULL (es decir está vencida y sin pago)

ISNULL

ISNULL
Reemplaza el valor nulo por un valor especificado

Sintaxis:
ISNULL(expresión,valor)
Parámetros:
expresión: Es la expresión a chequear, puede ser de cualquier tipo
valor: Es el valor a devolver si expresión es NULL, debe ser del mismo tipo que expresión

Si expresión no es NULL entonces devuelve expresión

Ejemplo:
Listar todas las facturas del año con sus totales con IVA (si el valor de iva no está cargado entonces corresponde el 21%)
SELECT
...f.fv_tipo,
...f.fv_nro,
...fv_fecha,
...SUM( det_cantidad * det_precio * ISNULL(det_iva,0.21) )
FROM
...factVent f,
...Detalles d
WHERE
...f.fv_tipo = d.fv_tipo AND
...f.fv_nro = d.fv_nro AND
...YEAR(fv_fecha) = YEAR(getdate())
GROUP BY
...f.fv_tipo,
...f.fv_nro,
...fv_fecha,

Listar todos los productos ordenados por código de rubro y si este no se especificó indicar con la leyenda "Sin especificar"
SELECT
...pr_codigo,
...pr_nombre,
...pr_precio,
...ISNULL(rub_codigo,'SIN ESPECIFICAR')
FROM
...Productos
ORDER BY
...ISNULL(rub_codigo,'SIN ESPECIFICAR'),
...pr_nombre

SUBCONSULTAS

Las subconsultas son consultas dentro de otras consultas, pueden estar dentro de las columnas especificadas en el SELECT o como parte de las tablas de la cláusula FROM o como parte de las condiciones de la cláusula WHERE.
Cuando están dentro de la cláusula WHERE normalmente van acompañadas de algun operador como IN, NOT IN, EXISTS, NOT EXISTS o cualquier otro operador lógico como >, <, =.
Veamos algunos ejemplos

* Listado de todos los productos con la cantidad vendida (uso de subconsultas con referencia a consulta externa)
SELECT *,(SELECT SUM(det_cantidad) FROM Detalles D WHERE D.pr_id=P.pr_id)
FROM Productos P
Puede observarse que en este caso la subconsulta utiliza el valor de una columna de la fila actual de la consulta externa P.pr_id . Este es un caso en el que se puede resolver la consulta de varias maneras, entre ellas a través de subconsultas como en el ejemplo pero tambien se puede resolver a través de GROUP BY sin recurrir a subconsulta.

* Listado de todos aquellos clientes que no compraron nada en el año actual (Uso de NOT IN)
SELECT *
FROM Clientes
WHERE cl_id NOT IN
(SELECT cl_id FROM FactVent WHERE YEAR(fv_fecha)=YEAR(GETDATE())

GROUP BY Y HAVING

Cuando se agrupan filas a través de GROUP BY también se puede optar por restringir las filas de salida de acuerdo a los resultados de agrupamiento a través de HAVING.
Así como en la cláusula WHERE se restringen las filas de salida de acuerdo a condiciones lógicas antes del agrupamiento, HAVING permite restricción de filas posteriores al agrupamiento.

Veamos algunos ejemplos:
Obtener los productos que generaron ingresos superiores a 40000:
SELECT pr_id, sum(det_cantidad*det_precio)
FROM Detalles
GROUP BY pr_id
HAVING sum(det_cantidad*det_precio) >40000

Listar aquellos productos que se vendieron mas de 100 veces en el mes actual
SELECT pr_id, count(*)
FROM Detalles d, FactVent f
WHERE
d.fv_tipo = f.fv_tipo AND
d.fv_nro = f.fv_nro AND
month(fv_fecha) = month(getdate()) AND
year(fv_fecha) = year(getdate())
GROUP BY pr_id
HAVING count(*) > 100

Observe que en primer lugar se indican las condiciones de reunion y de filtros en el WHERE las cuales se llevarán a cabo antes del agrupamiento y luego de agrupar, recién se filtrarán aquellos grupos que tengan mas de 100 filas

EJEMPLOS: GROUP BY

Listado de todas las carreras con la cantidad de materias correspondientes
SELECT
ca_nombre,
cantidad=COUNT(*)
FROM
Carreras c,
MateriasXCarreras m
WHERE
c.ca_id = m.ca_id
GROUP BY
ca_nombre

Listar cantidad de materias por carrera distinguiendo por año de cursado y por cuatrimestre
SELECT
ca_nombre,
mc_año,
mc_cuat
cantidad=COUNT(*)
FROM
Carreras c,
MateriasXCarreras m
WHERE
c.ca_id = m.ca_id
GROUP BY
ca_nombre,
mc_año,
mc_cuat

El siguiente material es tomado textualmente de Books On Line de Microsoft SQL Server

Cuando se usan sin una cláusula GROUP BY, las funciones de agregado sólo devuelven un valor de agregado para la instrucción SELECT. La cláusula GROUP BY se usa para producir valores de agregado para cada fila del conjunto de resultados.

En este ejemplo se devuelve el número de unidades vendidas de cada producto de la categoría 2:

SELECT OrdD.ProductID AS ProdID,
SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD , Products as Prd
WHERE OrdD.ProductID = Prd.ProductID
AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID

Éste es el conjunto de resultados:

ProdID AmountSold
------ -----------
3........ 328
4........ 453
5........ 298
6........ 301
8........ 372
15....... 122
44....... 601
61....... 603
63....... 445
65....... 745
66....... 239
77....... 791

(12 row(s) affected)

A continuación de las palabras clave GROUP BY se coloca una lista de columnas, conocidas como las columnas de agrupamiento. La cláusula GROUP BY restringe las filas del conjunto de resultados y sólo hay una fila por cada valor distinto de las columnas o columna de agrupamiento. Cada fila del conjunto de resultados contiene los datos de resumen relacionados con el valor específico de sus columnas de agrupamiento.

Hay restricciones en los elementos que se pueden especificar en la lista de selección cuando una instrucción SELECT contiene una cláusula GROUP BY. Los únicos elementos permitidos en la lista de selección son:

• Las columnas de agrupamiento.
• Expresiones que devuelven un solo valor por cada valor de las columnas de agrupamiento, como, por ejemplo, las funciones de agregado que tienen un nombre de columna como uno de sus parámetros. Se conocen como agregados vectoriales.

Por ejemplo, TableX contiene:

ColumnA	ColumnB	ColumnC
-------	-------	-------
1	abc	5
1	def	4
1	ghi	9
2	jkl	8
2	mno	3

Si ColumnA se usa como la columna de agrupamiento, habrá dos filas en el conjunto de resultados, una que resuma la información del valor 1 y la otra que resuma la información del valor 2.

Cuando la ColumnA es la columna de agrupamiento, la única forma en la que se podría hacer referencia a la ColumnB o ColumnC es en el caso de que fueran parámetros de una función de agregado que pudiera devolver un valor único por cada valor de ColumnA. La lista de selección puede incluir expresiones como, por ejemplo, MAX(ColumnB), SUM(ColumnC) o AVG(ColumnC):

SELECT ColumnA,
MAX(ColumnB) AS MaxB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA

Esta selección devuelve dos filas, una por cada valor exclusivo de ColumnA:

ColumnA MaxB SumC
------- ---- -----
1....... ghi 18
2....... mno 11

(2 row(s) affected)

Sin embargo, no se permite tener sólo la expresión ColumnB en la lista de selección:

SELECT ColumnA,
ColumnB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA

Puesto que GROUP BY sólo puede devolver una fila con el valor 1 en ColumnA, no hay forma de devolver los tres valores de ColumnB (abc, def y ghi) asociados con el valor 1 de ColumnA.

EJEMPLOS: ALTER TABLE

Agregar la columna ma_id a la tabla MateriasXCarreras y luego (por separado) definirla como clave foránea que hace referencia a la tabla de Materias
ALTER TABLE MateriasXCarreras ADD ma_id varchar(10)

ALTER TABLE MateriasXCarreras ADD CONSTRAINT FK_Materias_MXC
FOREIGN KEY (ma_id) REFERENCES Materias

Trabajo Práctico SQL

BASES DE DATOS
Departamento Informática
Cs. Exactas - UNSa

Trabajo Práctico SQL
1) Dado el siguiente DER crear las tablas correspondientes (el DER ya fue provisto en clases)

2) Agregar una tabla de provincias con la siguiente estructura:

....pr_codigo: varchar(10)
....pr_nombre: varchar(25)

3) A la tabla de clientes agregarle los siguientes atributos:
....cl_telefono: varchar(15)
....pr_codigo: varchar(10) FK(provincias)

4) Agregar las tablas necesarias para reflejar las siguientes suposiciones:
Se trata de un comerciante mayorista, los clientes pueden pagar sus facturas en varios pagos y pueden pagar a la vez mas de una factura, cada vez que se recibe un pago se emite un recibo en el que se detalla que a que facturas aplica dicho pago.Los productos están categorizados por rubros los cuales tienen un código y un nombre

5) Ingresar datos en todas las tablas

6) Listado alfabético de todos los productos

7) Listar todas las ventas del mes especificando tipo, nro y fecha de factura, apellido y nombre del cliente

8) Listado alfabético de clientes que realizaron compras durante el mes actual.

9) Listado con Código, Descrip y Rubro de los artículos vendidos a King, Robert durante el año en curso.

10) Listado de todos los clientes que alguna vez compraron el producto 'Maxilaku'

11) Listado de Todas las provincias especificando por cada una, cantidad de clientes registrados

12) Idem a 7 pero con total por factura

13) Ranking de rubros de acuerdo a las ventas del mes en curso