trabalho-satc-db1.sql

CREATE TABLE pessoa (
	pessoa_id INT NOT NULL IDENTITY,
	pessoa_nome VARCHAR(150) NOT NULL,
	pessoa_email VARCHAR(200) NOT NULL UNIQUE,
	pessoa_celular VARCHAR(30) NOT NULL UNIQUE,
	pessoa_dt_cadastro DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
	pessoa_status CHAR(1) NULL DEFAULT 'O',
	CONSTRAINT pk_pessoa_id PRIMARY KEY(pessoa_id),
	CONSTRAINT chk_pessoa_status CHECK(pessoa_status IN ('O', 'F', 'A'))
)

CREATE TABLE mensagem (
	mensagem_id INT NOT NULL IDENTITY,
	mensagem_dt_cadastro DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
	mensagem_dt_visualizado DATETIME NULL,
	mensagem_texto TEXT NOT NULL,
	mensagem_anexo VARCHAR(100) NULL,
	pessoa_id INT NOT NULL,
	CONSTRAINT pk_mensagem_id PRIMARY KEY(mensagem_id),
	CONSTRAINT fk_mensagem__pessoa_id FOREIGN KEY(pessoa_id) REFERENCES pessoa(pessoa_id)
)

CREATE TABLE conversa (
	pessoa_origem_id INT NOT NULL,
	pessoa_destino_id INT NOT NULL,
	mensagem_id INT NOT NULL,
	excluido CHAR(1) NOT NULL DEFAULT ' ',
	CONSTRAINT fk_conversa__pessoa_origem FOREIGN KEY (pessoa_origem_id) REFERENCES pessoa(pessoa_id),
	CONSTRAINT fk_conversa__pessoa_destino FOREIGN KEY (pessoa_destino_id) REFERENCES pessoa(pessoa_id),
	CONSTRAINT fk_conversa__mensagem_id FOREIGN KEY (mensagem_id) REFERENCES mensagem(mensagem_id)
)

CREATE TABLE grupo (
	grupo_id INT NOT NULL IDENTITY,
	grupo_dt_cadastro DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	grupo_nome VARCHAR(30) NOT NULL,
	grupo_descricao VARCHAR(150) NULL,
	CONSTRAINT pk_grupo_id PRIMARY KEY(grupo_id)
)

CREATE TABLE grupo_participantes (
	pessoa_id INT NOT NULL,
	grupo_id INT NOT NULL,
	grupo_admin CHAR(1) NOT NULL DEFAULT 'N',
	CONSTRAINT fk_participantes__pessoa_id FOREIGN KEY (pessoa_id) REFERENCES pessoa(pessoa_id),
	CONSTRAINT fk_participantes__grupo_id FOREIGN KEY (grupo_id) REFERENCES grupo(grupo_id)
)

CREATE TABLE grupo_mensagens (
	grupo_id INT NOT NULL,
	mensagem_id INT NOT NULL,
	excluido CHAR(1) NOT NULL DEFAULT ' ',
	CONSTRAINT fk_grupo_mensagens__grupo_id FOREIGN KEY (grupo_id) REFERENCES grupo(grupo_id),
	CONSTRAINT fk_grupo_mensagens__mensagem_id FOREIGN KEY (mensagem_id) REFERENCES mensagem(mensagem_id)
)




INSERT INTO pessoa (pessoa_nome, pessoa_email, pessoa_celular, pessoa_status)
VALUES('Rafael Rodigues','rafael.jai.fc@gmail.com','998099032','O'),
	('Suyane dos Santos','suyanemk@outlook.com','996983987','A'),
	('Guilherme Correa','guilhermecorrearabelo@gmail.com','999794207','O'),
	('Alexandre Hermes','alexandrehermes@gmail.com','996989357','O'),
	('Gabriel Kaufmann','gabrielkaufmann@gmail.com','999352127','A'),
	('Juliano Silva','julianosilva@gmail.com','982346281','O'),
	('Wallace Silva Mendes','wallacesilvamendes@gmail.com','996381145','F')
go

INSERT INTO grupo (grupo_nome) VALUES
	('FAMILIA'), ('FUTEBOL'), ('FESTA'), ('TRABALHO'),
	('ESCOLA'), ('ABIGOS'), ('TODIS')
go

UPDATE grupo SET grupo_descricao = 'GRUPO DE FUTEBOL' 
WHERE grupo_id=2
go

UPDATE grupo SET grupo_descricao = 'TRABALHO ZOADO' 
WHERE grupo_id=5
go

INSERT INTO grupo_participantes (pessoa_id, grupo_id, grupo_admin) VALUES (1, 1, 'S')
go

INSERT INTO grupo_participantes (pessoa_id, grupo_id) VALUES (5, 1), (7, 1)
go

INSERT INTO grupo_participantes (pessoa_id, grupo_id, grupo_admin) VALUES (2, 4, 'S')
go

INSERT INTO grupo_participantes (pessoa_id, grupo_id) VALUES 
	(1, 4), (3, 4), (5, 4), (6, 4), (7, 4), (4, 4)
go

INSERT INTO grupo_participantes (pessoa_id, grupo_id, grupo_admin) VALUES 
	(3, 2, 'S'), (4, 3, 'S'), (5, 7, 'S'), (6, 5, 'S'), (7, 6, 'S')
go

INSERT INTO mensagem (mensagem_texto, pessoa_id) VALUES
	('opa', 3), ('kkk', 1), ('e ai e o trab?', 3), ('deu ruim', 1), 
	('n n aacho q ta bom assim', 3), ('opa', 7), ('kkk', 3), ('asd', 7), 
	('312dcv ', 3), ('gfdbdf', 3), ('oi', 6), ('tdo bem', 6), 
	('ja termiinaram?', 6), ('falta muito ?', 6), ('Oi', 3), 
	('Oi',3),('kkk tudo maluco', 3), ('Oi cara de boi', 3), ('eae man', 7),
	('eae filhote', 7), ('bora jogar Dayz rapaziada',7), ('demorooo',7),
	('dá um 10 vou entra disc tbm',7), ('boa tarde', 2), ('oi', 2),
	('de boa', 2), ('sim', 2), ('dai',1), ('tudo certo',1),
	('grupo morto',1), ('buenas', 5), ('buenas notches', 5), 
	('bora tomar um chimas? ',5), ('bora, eu lego a erva mate',5), 
	('chegando ai te mando um zap',5), ('Oi', 3), ('Oi',4),
	(' bah tche', 3), ('fala', 4)
go

INSERT INTO conversa (pessoa_origem_id, pessoa_destino_id, mensagem_id, excluido) VALUES
	(3, 1, 1, ' '), (1, 3, 2, ' '), (3, 1, 3, ' '), (1, 3, 4, ' '), (3, 1, 5, ' '),
	(7, 3, 6, ' '), (3, 7, 7, ' '), (7, 3, 8, ' '), (3, 7, 9, ' '), (3, 7, 10, ' ')
go

INSERT INTO grupo_mensagens (grupo_id, mensagem_id, excluido) VALUES
	(4, 11, ' '), (4, 18, ' '), (4, 13, ' '), (4, 22, ' '), (4, 12, ' '),
	(4, 28, ' '), (4, 32, ' '), (4, 39, ' '), (4, 25, ' '), (4, 23, ' '),
	(4, 26, ' '), (4, 14, ' '), (4, 34, ' '), (4, 33, ' '), (4, 29, ' '),
	(4, 31, ' '), (4, 19, ' '), (4, 37, ' '), (4, 30, ' '), (4, 15, ' '),
	(4, 36, ' '), (4, 21, ' '), (4, 16, ' '), (4, 17, ' '), (4, 20, ' '),
	(4, 24, ' '), (4, 35, ' ')
go

UPDATE mensagem SET mensagem_dt_visualizado = CURRENT_TIMESTAMP 
WHERE mensagem_id IN (42, 15, 33, 25, 26, 27, 16, 17, 18)
go


1. QUAL PESSOA TEM MAIS MENSAGENS FORAM VISUALIZADAS ?
SELECT TOP 1 COUNT(*) qtd_mensagem, pessoa_nome
FROM mensagem m
INNER JOIN pessoa p ON (
	p.pessoa_id = m.pessoa_id
)
WHERE mensagem_dt_visualizado IS NOT NULL
GROUP BY pessoa_nome
ORDER BY COUNT(*) DESC
go

2. QUAL USUÁRIO COM A LETRA 'G' NO NOME TEM O MAIOR NÚMERO DE MENSAGENS
SELECT COUNT(*), pessoa_nome
FROM mensagem m
INNER JOIN pessoa p ON (
	p.pessoa_id = m.pessoa_id
)
WHERE pessoa_nome LIKE '%g%'
GROUP BY pessoa_nome
ORDER BY COUNT(*) DESC
go

3. QUAIS GRUPOS TÊM MAIS PARTICIPANTES :
SELECT count(*) participantes, grupo_nome
FROM grupo_participantes gp
INNER JOIN grupo g ON (
	g.grupo_id = gp.grupo_id
)
GROUP BY grupo_nome
ORDER BY count(*) DESC
go

4. QUAL A LISTA DE CONVERSAS COM O USUÁRIO ORIGEM E USUÁRIO DESTINO ?
SELECT po.pessoa_nome as origem,
	pd.pessoa_nome as destino,
	mensagem_texto
FROM conversa c
INNER JOIN pessoa po ON pessoa_origem_id = po.pessoa_id
INNER JOIN pessoa pd ON pessoa_destino_id = pd.pessoa_id
INNER JOIN mensagem m ON m.mensagem_id = c.mensagem_id
go

5. QUANTAS CONVERSAS E GRUPOS RAFAEL PARTICIPA ?
SELECT pessoa_nome,
	COUNT(c.mensagem_id) AS conversas,
	COUNT(gp.grupo_id) AS grupos
FROM pessoa p
LEFT JOIN conversa c ON (
	c.pessoa_destino_id = p.pessoa_id
	OR c.pessoa_origem_id = p.pessoa_id
)
LEFT JOIN grupo_participantes gp ON (
	gp.pessoa_id = p.pessoa_id
)
WHERE pessoa_nome LIKE '%Rafael%'
GROUP BY pessoa_nome
go

6. QUAIS PESSOAS ADMINISTRAM QUAL GRUPO ?
SELECT pessoa_nome, grupo_nome
FROM grupo_participantes gp
INNER JOIN pessoa p ON p.pessoa_id = gp.pessoa_id
INNER JOIN grupo g ON g.grupo_id = gp.grupo_id
WHERE grupo_admin='S'
ORDER BY pessoa_nome
go

7. QUANTAS CONVERSAS, GRUPOS, MENSAGEMS E PARTICIPANTES NOS GRUPOS, MENSAGENS E PESSOAS ESTÃO CADASTRADAS ?
SELECT
	(SELECT COUNT(*) FROM conversa) conversa,
	(SELECT COUNT(*) FROM grupo) grupo,
	(SELECT COUNT(*) FROM grupo_mensagens) grupo_mensagens,
	(SELECT COUNT(*) FROM grupo_participantes) grupo_participantes,
	(SELECT COUNT(*) FROM mensagem) mensagem,
	(SELECT COUNT(*) FROM pessoa) pessoa
go