El BLOG de FLamelas

1. a. SELECT table_name

FROM SYSTABLE S

WHERE EXISTS (SELECT *

FROM SYSTABLEPERM T

WHERE S.table_id = T.stable_id

AND EXISTS (SELECT *

FROM SYSUSERPERM U

WHERE U.user_id = T.grantee AND U.user_name LIKE 'LUIS%')) (10%)

Muestra todas las tablas y/o vistas a las que tienen permiso usuarios cuyo nombre inicia con LUIS.

b. SELECT T.table_name, S.selectauth AS SELECCIONAR, S.deleteauth AS BORRAR, S.insertauth AS INSERTAR, S.updateauth AS ACTUALIZAR

FROM SYS.SYSTABLEPERM S INNER JOIN SYS.SYSTABLE T ON S.stable_id = T.table_id

WHERE grantee IN (SELECT user_id

FROM SYS.SYSUSERPERM

WHERE user_name = 'MARIA MARTA')

AND (deleteauth = 'Y' or insertauth = 'Y' or updateauth = 'Y') (10%)

Muestra el nombre de las tablas/vistas y el detalle de las autorizaciones en las que la usuaria MARIA MARTA tiene cualquiera de los siguientes permisos de modificación de datos: borrar, insertar o actualizar.

c. SELECT P.user_name AS Creador, COUNT(T.TABLE_NAME ) AS Cantidad, T.table_type AS Tipo_Objeto

FROM SYS.SYSTABLE T INNER JOIN SYS.SYSUSERPERM P ON T.creator = P.user_id

WHERE P.user_name NOT IN ('SYS') AND T.table_type in ('BASE','VIEW')

GROUP BY P.user_name,T.table_type

ORDER BY P.user_name (10%)

Muestra la cantidad de tablas y vistas creadas por cada usuario. No se consideran las tablas del sistema o las creadas por el usuario SYS. El resultado se ordena por Nombre de Usuario.

Área(nomArea, descripcion)

Contiene información de las áreas de la empresa.

Servicio(idServ, nomArea, tipo)

Contiene información de los servicios que ofrece cada área.

Pagina (URL, titulo, descripcion, URLAnterior)

Contiene información sobre las páginas de la empresa. La URLAnterior siempre hace referencia a la página anterior a la página actual.

PaginaServicio(URL, idServ, nomArea)

Contiene información sobre las paginas de servicios, indicando que servicio ofrecen.

Usuario(usuario, nombre, ciudad, pais, profesión, sexo, edad, nomArea)

Contiene información sobre los usuarios registrados. Se indica el área favorita de cada usuario.

Sesión(fecha, hora, usuario,duracion)

Contiene información sobre los logins al sitio de la empresa. La duración de la sesión es expresada en minutos.

Accesa(fecha, hora, usuario, URL)

Contiene información sobre las páginas de la empresa que son accesadas por el usuario durante una sesión.

Solicitud(fecha, hora, usuario, idServ, nomArea, prioridad)

Contiene información sobre los servicios solicitados por el usuario durante una sesión. Se indica la prioridad que tiene este pedido para el usuario: Alta, Media o Baja.

Dado el esquema relacional anterior correspondiente a información de una empresa proveedora de servicios informáticos a través de Internet responda las siguientes consultas planteadas en ANSI SQL 2: (30%)

a. Devolver los nombres de aquellos usuarios menores de 25 años, que han solicitado servicios de TODAS las áreas de la empresa. (20%)

SELECT U.Nombre

FROM USUARIO U

WHERE U.Edad <25

AND NOT EXISTS ( (SELECT A.NomArea

FROM AREA A)

EXCEPT

(SELECT DISTINCT S.NomArea

FROM SOLICITUD S

WHERE S.Usuario = U.Usuario) )

b. Devolver usuario y nombre de aquellos usuarios que cada vez que realizan solicitudes de servicio lo hacen con prioridad Alta. (10%)

SELECT U.Usuario, U.Nombre

FROM USUARIO U

WHERE EXISTS (SELECT *

FROM SOLICITUD S

WHERE S.Usuario = U.Usuario

AND Prioridad = 'ALTA')

O

SELECT U.Usuario, U.Nombre

FROM USUARIO U, SOLICITUD S

WHERE U.Usuario = S.Usuario AND Prioridad = 'ALTA'

O

SELECT U.Usuario, U.Nombre

FROM USUARIO U

WHERE NOT EXISTS((SELECT *

FROM SOLICITUD S

WHERE S.Usuario = U.Usuario)

EXCEPT

(SELECT *

FROM SOLICITUD S

WHERE S.Usuario = U.Usuario AND S.Prioridad = 'ALTA') )

3. Considere el siguiente esquema relacional que almacena información parcial de una empresa de seguros: (40%)

POLIZA(Id-Poliza,Monto, Id-Cliente, Cod-Agente)

AGENTE_DE_SEGUROS(Cod-Agente, NombreA, Sueldo-Base,Comision)

CLIENTE(Id-Cliente,NombreC, Direccion, Edad)

BENEFICIA(Id-Poliza,Id-Cliente)

Usando ANSI SQL 2 resuelva los siguientes enunciados (10% cda. enunciado):

a. Imprima el nombre de los agentes de seguros y la cantidad de pólizas de todos aquellos agentes de seguros que hayan vendido más de 1,000 pólizas.

SELECT NombreA, COUNT(Id-Poliza) AS Cantidad

FROM POLIZA P, AGENTE_DE_SEGUROS A

WHERE P.Cod-Agente = A.Cod-Agente

GROUP BY NombreA

HAVING COUNT (Id-Poliza) > 1000

b. Elimine todas las pólizas mayores a 5,000 Lempiras que posee el cliente: “Hugo Salazar” que fueron vendidas por la agente de seguros: “Marcela Morales”.

DELETE FROM POLIZA P

WHERE EXISTS (SELECT *

FROM CLIENTE C

WHERE C.Id-Cliente = P.Id-Cliente

AND C.NombreC = ‘Hugo Salazar’)

AND EXISTS (SELECT *

FROM AGENTE_DE_SEGUROS A

WHERE A.Cod-Agente = P.Cod-Agente

AND A.NombreA = ‘Marcela Morales’)

AND P.Monto > 5000

O

DELETE FROM POLIZA P

WHERE P.Id-Cliente IN (SELECT Id-Cliente

FROM CLIENTE C

WHERE C.NombreC = ‘Hugo Salazar’)

AND P.Cod-Agente IN (SELECT Cod-Agente

FROM AGENTE_DE_SEGUROS A

WHERE A.NombreA = ‘Marcela Morales’)

AND P.Monto > 5000

c. Imprima el nombre de todos aquellos clientes que poseen una póliza pero que NO son beneficiarios en otras pólizas (por naturaleza, un cliente que posee una póliza de seguros NO puede aparecer como beneficiario en su misma póliza).

SELECT C.NombreC

FROM CLIENTE C

WHERE EXISTS (SELECT *

FROM POLIZA P

WHERE P.Id-Cliente=C.Id-Cliente)

AND NOT EXISTS (SELECT *

FROM BENEFICIA B

WHERE B.Id-Cliente=C.Id-Cliente)

O

SELECT NombreC

FROM CLIENTE

WHERE Id-Cliente IN (SELECT Id-Cliente FROM POLIZA)

AND Id-Cliente NOT IN (SELECT Id-Cliente FROM BENEFICIA)

d. Incremente en un 10% el sueldo base de todos los agentes de seguros con sueldo base mayor al mayor sueldo base promedio de los agentes de seguros.

UPDATE AGENTE_DE_SEGUROS

SET Sueldo-Base = Sueldo-Base * 1.10

WHERE Sueldo-Base > (SELECT MAX (T.Promedio)

FROM (SELECT AVG(Sueldo-Base) AS Promedio

FROM AGENTE_DE_SEGUROS) AS T)

# posted by Franklin Lamelas @ 12:02 p. m. 0 comments