Diseño y Modelado de bases de datos:

--CREAREMOS UNA NUEVA BASE DE DATOS LLAMADA AGENDA_MIS_CONTACTOSCREATE
CREATE DATABASE AGENDA_MIS_CONTACTOS_2023;
go
--USAREMOS ESA BASE DE DATO 

USE AGENDA_MIS_CONTACTOS_2023;
GO

--CREAMOS UNA TABLA DE CATEGORÍA:

create table CATEGORIA_CONTACTOS
(
 id_categoria int primary key identity (1,1),
 nombre_Categoria varchar(120),
);

--CREAR UNA TABLA LLAMADA GÉNERO:

CREATE TABLE GENERO(
 id_genero int Primary key,
 genero varchar(15)
);
go

--VAMOS A CREAR LA TABLA CONTACTOS LA CUAL CONTIENE TODOS LOS DATOS:

CREATE TABLE CONTACTOS(
id_contacto int primary key identity(1,1) not null,
nombre_contacto varchar(30)not null,
apellido_contacto varchar(30) not null,
fecha_nacimiento date not null,
id_genero int not null,
telefono varchar(15) not null, 
email varchar(30) not null,
direccion varchar(100) not null,
id_categoria int not null, 
FOREIGN KEY (id_categoria) REFERENCES CATEGORIA_CONTACTOS (id_categoria),
FOREIGN KEY (id_genero) REFERENCES genero (id_genero),
);
GO

--HACEMOS UN SELECT A LAS DOS TABLAS
SELECT * FROM CATEGORIA_CONTACTOS;
SELECT * FROM CONTACTOS;
SELECT * FROM GENERO


--HACEMOS INSERCIÓN DE LOS DATOS EN LA TABLA CATEGORIA_CONTACTOS:

insert into CATEGORIA_CONTACTOS values('Mis mejores Amigos');
insert into CATEGORIA_CONTACTOS values('MI Familia');
insert into CATEGORIA_CONTACTOS values('Compañeros de Estudio');
insert into CATEGORIA_CONTACTOS values('Compañeros de Trabajo');
insert into CATEGORIA_CONTACTOS values('Redes Sociales');

--HACEMOS UN SELECT PARA LOS GÉNEROS:	--

SELECT * FROM CATEGORIA_CONTACTOS

--insertaremos datos en la tabla genero

insert into genero (id_genero, genero) values (1,'Femenino');
insert into genero (id_genero, genero) values (2,'Masculino');
insert into genero (id_genero, genero) values (3,'No Definido');

--HACEMOS UN SELECT PARA LOS GÉNEROS:

SELECT * FROM GENERO

--HACEMOS INSERCIÓN DE LOS DATOS EN LA TABLA CONTACTOS:

INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('JUANCITO','PEÑA','1988-09-14','2','809-111-0001','JUANCITO@EMIAL.COM','C/SIEMPRE FELIZ, # 2021','1'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('DARIEL','VAZQUEZ','2021-09-11','2','809-000-00000','DARIEL@EMIAL.COM','C/VIVO BIEN # 2021', '1'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('DANIELA','HICHEZ','2016-08-24','1','849-222-0000','DANIELA@EMIAL.COM','C/PRINCESA # 2022', '1'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento,  id_genero,telefono,email,direccion, id_categoria)
VALUES ('MARIA','VIZCAINO','1956-10-05','1','809-0101-0101','MARIA@EMIAL.COM','C/MADRE Y SEÑORA # 2021', '1'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero,  telefono, email, direccion, id_categoria)
VALUES ('ELIESER','G. P','2005-02-04','2','809-551-1001','BRAYAN@EMIAL.COM','C/JUEGOS Y MAS JUEGOS # 2021', '3'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('VICENTE','MIGUEL','1985-05-20','2','849-558-8977','JUPITER@EMIAL.COM','C/UNIVERSO, Y GALAXIA # 2021', '4'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('MARLENYS','PANIAGUA','1998-09-04','1','829-789-7894','MARLENYS@EMIAL.COM','C/NETFLIX TEMPORADA # 2', '3'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('ALBERT','FIGUERES','1986-10-10','1','115-789-0852','ALBERT@EMIAL.COM','C/LINKEDIN-APRENDIENDO SQL # 2', '5'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('DIEGO','LOPEZ','1991-10-10','1','985-789-0852','DIEGO@EMIAL.COM','C/LINKEDIN-AYUDANDO POWER BI', '5'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('JAVIER','GOMEZ','1991-10-10','1','985-789-0852','JAVIER@EMIAL.COM','C/LINKEDIN-DATDATA POWER BI', '5'); 
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('MIGUEL','ROJAS','1991-10-10','1','985-789-0852','MIGUEL@EMIAL.COM','C/LINKEDIN-TEMIXA POWER BI', '5'); 
--INSERT PARA PROBAR LA ACTUALIZACION DE DATOS:
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('NOMBRE ACTUALIZAR','ACTUALIZAR','1995-05-13','2','809-895-5214','ACTUALIZAR@EMIAL.COM','AV. LA VIDA ES BELLA # 2021', '3'); 
--INSERT PARA PROBAR EL BORRADO DE DATOS:
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('NOMBRE A BORRAR','BORRAR','1999-01-14','1','829-789-7894','BORRAR@EMIAL.COM','C/BORRAR # 2', '4');
--INSERT PARA PROBAR EL TRIGGER DE INSERCION:
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('PROBANDO','TRIGGER','1999-01-14','1','829-789-7894','TRIGGER@EMIAL.COM','C/TRIGGER # 2', '4');


--HACEMOS USO DE LA SIGUIENTE SENTENCIA DEL CRUD, EN ESTE CASO DEL READ -> QUE ES UN SELECT EL CUAL PUEDE ESTAR CONDICIONADO O NO.

select * from CONTACTOS
select * from CATEGORIA_CONTACTOS
SELECT * FROM GENERO

--PODEMOS HACER SELECT CON FILTRO POR EJEMPLO EL QUE TRAIGA EL CONTACTO CÓDIGO # 4

select * from CONTACTOS where id_contacto='4'

--PODEMOS HACER SELECT CON FILTRO POR EJEMPLO EL QUE TRAIGA EL CONTACTO CUYO NOMBRE SEA IGUAL MARLENYS

select * from CONTACTOS where nombre_contacto='MARLENYS'

--MUESTRAME SOLO EL NOMBRE Y LOS APELLIDO DE LOS CONTACTOS:

select nombre_contacto, apellido_contacto from CONTACTOS

-- CUENTAME CUANTOS REGISTROS TENGO EN MI TABLA DE CONTACTOS;

SELECT COUNT(*) AS 'Total Contactos' FROM CONTACTOS

--CUANTOS CONTACTOS SON FEMENINOS - MUJER:

SELECT COUNT(*) AS 'Total Contactos Genero Femenino' FROM CONTACTOS WHERE id_genero='1'

--MOSTRAR LOS CONTACTOS QUE SON FEMENINOS - MUJER:

SELECT nombre_contacto AS 'Total Contactos Genero Femenino' FROM CONTACTOS WHERE id_genero='1'

--CUANTOS CONTACTOS SON MASCULINO - HOMBRE:

SELECT COUNT(*) AS 'Total Contactos Genero Masculino' FROM CONTACTOS WHERE id_genero='2'

--MOSTRAR LOS CONTACTOS QUE SON FEMENINOS - HOMBRE:

SELECT nombre_contacto AS 'Total Contactos Genero Masculino' FROM CONTACTOS WHERE id_genero='2'

--MOSTRAR LOS CONTACTOS CUYO NOMBRE CONTENGA LA LETRA N

select * from CONTACTOS where nombre_contacto like '%n%' 

--MOSTRAR LOS CONTACTOS CUYO NOMBRE CONTENGA LA LETRA MA

select * from CONTACTOS where nombre_contacto like 'ma%';

--#-MOSTRAR EL NOMBRE, APELLIDO, TELÉFONO. EMAIL Y CUANTO AÑOS DE EDAD TIENEN LOS CONTACTOS:

select nombre_contacto, apellido_contacto, telefono, email,
DATEDIFF(YEAR, fecha_nacimiento, GETDATE()) as [Años de Edad]
From CONTACTOS
order by [Años de Edad] desc


select * from CONTACTOS 


--VAMOS AHORA HACER USO DEL UPDATE, ES DECIR ACTUALIZAR UN CAMPO O REGISTRO DE UNA TABLA: COMO EJEMPLO EL ID 5

UPDATE  CONTACTOS SET fecha_nacimiento= '2001-09-10' where id_contacto ='2'

UPDATE  CONTACTOS SET fecha_nacimiento= '1993-02-04' where id_contacto ='5'

UPDATE  CONTACTOS SET nombre_contacto ='LORENA' where id_contacto ='8'

UPDATE  CONTACTOS SET apellido_contacto = 'HERNANDEZ' where id_contacto ='8'

--PODEMOS ACTUALIZAR TODOS SUS DATOS AGREGANDO ENTRE CADA DATO UNA COMA:

UPDATE  CONTACTOS SET nombre_contacto= 'LORENA', apellido_contacto= 'HERNANDEZ', id_genero='1', email = 'LORENA@EMIAL.COM' where id_contacto ='8'

--AHORA VAMOS A UTILIZAR POR ULTIMO EL ULTIMO COMANDO DELETE, EL CUAL BORRA EL REGISTRO INDICADO O TODOS LOS REGISTROS SINO ESPECIFICAMOS.

DELETE FROM CONTACTOS WHERE id_contacto='9'

-- HACEMOS UN SELECT A LA TABLA CONTACTOS:

select * from CONTACTOS 

--VAMOS A VACIAR TODOS LOS CONTACTOS EN LA TABLA (DDL - TRUNCATE VACÍA LOS DATOS DE LA TABLA Y LA DEJA INTACTA SU ESTRUCTURA) NO USA WHERE NI FROM
--LOS DATOS AL INSERTARLOS NUEVAMENTE TOMAN EL ORDEN INICIAL, ES DECIR 1, 2, 3...N EN SUS ID SI SON INCREMENTALES.

TRUNCATE TABLE CONTACTOS;
GO

--VAMOS A BORRAR LOS REGISTROS DE LOS CONTACTOS EN LA TABLA: (DML - DELETE BORRA LOS REGISTROS DE LA TABLA), USA FROM Y WHERE, PERO LOS DATOS EN SU ÍNDICE
--RETOMAN EL ULTIMO ID ALMACENADO, ES DECIR SI EL REGISTRO BORRADO CON DELETE ERA 10, AHORA LOS DEMÁS QUE SE INSERTAN SERÁN 11,12,....N, ETC.

DELETE FROM CONTACTOS;
GO

--VAMOS A VACIAR TODOS LOS CONTACTOS EN LA TABLA (DDL - DROP ELIMINA LA TABLA O LA BASE DE DATOS COMPLETA INCLUYENDO SU ESTRUCTURA, PRIVILEGIOS, 
--ÍNDICES, RESTRICCIONES, TRIGGER, ETC.), NO SE RECUPERA.

DROP TABLE CONTACTOS;
GO



--MOSTRAR EL CODIGO, NOMBRE, TELEFONO Y CORREO DE LOS CONTACTOS:

select id_contacto, nombre_contacto, telefono, email from CONTACTOS;

--Hacer UNA CONSULTA QUE ME TRAIGA EL CODIGO, NOMBRE, TELEFONO, CATEGORIA DE AMIGO CON EL NOMBRE, POR MEDIO DE UN JOIN.

select C.id_contacto, C.nombre_contacto, C.telefono, CT.nombre_Categoria
 from CONTACTOS C
 join CATEGORIA_CONTACTOS CT
 on C.id_categoria = CT.id_categoria;

--HACER UNA CONSULTA QUE ME TRAIGA EL CODIGO, NOMBRE, TELEFONO, CATEGORIA Y GENERO DE AMIGO CON EL NOMBRE, POR MEDIO DE UN JOIN.

select C.nombre_contacto, C.telefono, CT.nombre_Categoria, g.genero
 from CONTACTOS C
 join CATEGORIA_CONTACTOS CT
 on C.id_categoria = CT.id_categoria
 join genero g on g.id_genero=c.id_genero

--HACER UNA CONSULTA QUE ME TRAIGA EL CODIGO, NOMBRE, TELEFONO, CATEGORIA Y GENERO DE AMIGO Y QUE LA CATEGORIA SEA MEJORES AMIGOS, POR MEDIO DE UN JOIN.

select C.nombre_contacto,C.telefono,CT.nombre_Categoria 'Categoria'
 from CONTACTOS C
 join CATEGORIA_CONTACTOS CT
 on C.id_categoria = CT.id_categoria
 where CT.id_categoria=5;


 --MOSTRAR EL NOMBRE, APELLIDO, TELEGONO, EMAIL, Y DE LOS CONTACTOS QUE SEAN MENORES DE 30 AÑO.

select nombre_contacto, apellido_contacto, telefono, email,
DATEDIFF(YEAR, fecha_nacimiento, GETDATE()) as [Años de Edad]
from CONTACTOS C
join CATEGORIA_CONTACTOS CT
on C.id_categoria = CT.id_categoria
where DATEDIFF(YEAR, fecha_nacimiento, GETDATE()) <=30
order by &#91;Años de Edad&#93; desc

--MOSTRAR EL NOMBRE, APELLIDO, TELEGONO, EMAIL, Y DE LOS CONTACTOS QUE SEAN MAYORES DE 30 AÑO.

select nombre_contacto, apellido_contacto, telefono, email,
DATEDIFF(YEAR, fecha_nacimiento, GETDATE()) as &#91;Años de Edad&#93;
from CONTACTOS C
join CATEGORIA_CONTACTOS CT
on C.id_categoria = CT.id_categoria
where DATEDIFF(YEAR, fecha_nacimiento, GETDATE()) >=30
order by [Años de Edad] desc


--VAMOS A CREAR UNA VISTA CON LOS DATOS EXTRAÍDOS:

create or alter view  V_CONTACTOS
as
select C.id_contacto as 'Codigo', C.nombre_contacto as 'Nombre de Contacto',C.apellido_contacto as 'Apellido de Contacto',  
c.fecha_nacimiento, C.direccion as 'Direccion', C.telefono as [Telefono], CT.nombre_Categoria as 'Categoria de Contacto', g.genero as Genero,
DATEDIFF(YEAR, C.fecha_nacimiento, GETDATE()) as [Edad]
from CONTACTOS C
join CATEGORIA_CONTACTOS CT
on C.id_categoria = CT.id_categoria
join genero g on g.id_genero=c.id_genero


--VAMOS LA VISTA COMO HA QUEDADO:

SELECT * FROM V_CONTACTOS


--CREAMOS UN PROCEDIMIENTO ALMACENADO PARA CONSULTAR POR FECHAS LOS CONTACTOS:

CREATE OR ALTER PROCEDURE usp_ObtenerContactosPorFecha (@fechaInicial DATE, @fechaFinal DATE)
AS
BEGIN
select C.id_contacto as 'Codigo', C.nombre_contacto as 'Nombre de Contacto',
C.apellido_contacto as 'Apellido de Contacto',  
c.fecha_nacimiento, C.direccion as 'Direccion', C.telefono as [Telefono], 
CT.nombre_Categoria as 'Categoria de Contacto', g.genero as Genero,
DATEDIFF(YEAR, C.fecha_nacimiento, GETDATE()) as [Edad]
from CONTACTOS C
join CATEGORIA_CONTACTOS CT
on C.id_categoria = CT.id_categoria
join genero g on g.id_genero=c.id_genero
   WHERE c.fecha_nacimiento BETWEEN @fechaInicial AND @fechaFinal
END

--Para ejecutar este procedimiento almacenado, puedes utilizar el siguiente comando:

EXEC usp_ObtenerContactosPorFecha @fechaInicial = '1956-10-05', @fechaFinal = '2016-08-24'

--Puedes crear 3 triggers en SQL Server para ejecutar un mensaje cuando se realice una acción de inserción, 
--actualización o eliminación en la tabla CONTACTOS. Aquí están los 3 triggers:

-- Trigger para inserción

CREATE OR ALTER TRIGGER tr_Insertar_Contactos
ON CONTACTOS
AFTER INSERT
AS
BEGIN
DECLARE @usuario varchar(30);
SET @usuario = SUSER_SNAME();
PRINT 'Un Nuevo Contacto se Ha Insertado, Realizado por el usuario: ' + @usuario;
END;

-- Trigger para actualización
CREATE OR ALTER TRIGGER tr_Actualizar_Contactos
ON CONTACTOS
AFTER UPDATE
AS
BEGIN
DECLARE @usuario varchar(30);
SET @usuario = SUSER_SNAME();
PRINT 'Un Contacto se Ha Actualizado por el usuario: ' + @usuario;
END;

-- Trigger para eliminación
CREATE OR ALTER TRIGGER tr_Eliminar_Contactos
ON CONTACTOS
AFTER DELETE
AS
BEGIN
DECLARE @usuario varchar(30);
SET @usuario = SUSER_SNAME();
PRINT 'Un Contacto se Ha Eliminado por el usuario: ' + @usuario;
END;

--Para ejecutar estos triggers, puedes realizar cualquier acción de inserción, 
--actualización o eliminación en la tabla CONTACTOS y verás el mensaje correspondiente en la salida.

SELECT * FROM CONTACTOS


 INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('NOMBRE ACTUALIZAR','ACTUALIZAR','1995-05-13','2','809-895-5214','ACTUALIZAR@EMIAL.COM','AV. LA VIDA ES BELLA # 2021', '3'); 
--INSERT PARA PROBAR EL BORRADO DE DATOS:
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('NOMBRE A BORRAR','BORRAR','1999-01-14','1','829-789-7894','BORRAR@EMIAL.COM','C/BORRAR # 2', '4');
--INSERT PARA PROBAR EL TRIGGER DE INSERCION:
INSERT INTO CONTACTOS(nombre_contacto, apellido_contacto, fecha_nacimiento, id_genero, telefono, email,direccion, id_categoria)
VALUES ('PROBANDO','TRIGGER','1999-01-14','1','829-789-7894','TRIGGER@EMIAL.COM','C/TRIGGER # 2', '4');


--VAMOS AHORA HACER USO DEL UPDATE, ES DECIR ACTUALIZAR UN CAMPO O REGISTRO DE UNA TABLA: COMO EJEMPLO EL ID 5

UPDATE  CONTACTOS SET fecha_nacimiento= '2001-09-10' where id_contacto ='2'

--AHORA VAMOS A UTILIZAR POR ULTIMO EL ULTIMO COMANDO DELETE, EL CUAL BORRA EL REGISTRO INDICADO O TODOS LOS REGISTROS SINO ESPECIFICAMOS.

DELETE FROM CONTACTOS WHERE id_contacto='14'