gabarito_aula_subconsultas

CREATE DATABASE subconsulta;

USE subconsulta;


#----------------------------------------------------------------------------
# CRIAÇÃO DE TABELAS

CREATE TABLE profissao (
    id_profissao INTEGER NOT NULL AUTO_INCREMENT,
    nome VARCHAR(45) NOT NULL,
    PRIMARY KEY(id_profissao)
);

CREATE TABLE cep (
    num_cep VARCHAR(10) NOT NULL,
    cidade VARCHAR(45) NOT NULL,
    estado VARCHAR(2) NOT NULL,
    PRIMARY KEY(num_cep)
);

CREATE TABLE estado_civil (
    id_estado_civil INTEGER NOT NULL AUTO_INCREMENT,
    nome VARCHAR(45) NOT NULL,
    PRIMARY KEY(id_estado_civil)
);

CREATE TABLE meus_contatos (
    id_contato INTEGER NOT NULL AUTO_INCREMENT,
    sobrenome VARCHAR(40) NOT NULL,
    prenome VARCHAR(30) NOT NULL,
    telefone VARCHAR(20) NOT NULL,
    email VARCHAR(40),
    sexo VARCHAR(1),
    aniversario DATE,
    id_profissao INTEGER,
    num_cep VARCHAR(10),
    id_estado_civil INTEGER,
    PRIMARY KEY(id_contato),
    FOREIGN KEY(id_profissao) REFERENCES profissao(id_profissao),
    FOREIGN KEY(num_cep) REFERENCES cep(num_cep),
    FOREIGN KEY(id_estado_civil) REFERENCES estado_civil(id_estado_civil)
);

CREATE TABLE emprego_desejado (
    id_emprego_desejado INTEGER NOT NULL AUTO_INCREMENT,
    id_contato INTEGER NOT NULL,
    cargo VARCHAR(40) NOT NULL,
    salario_minimo FLOAT NOT NULL,
    salario_maximo FLOAT,
    disponibilidade VARCHAR(40),
    anos_experiencia TINYINT, # 0 a 255 no maximo
    PRIMARY KEY(id_emprego_desejado),
    FOREIGN KEY(id_contato) REFERENCES meus_contatos(id_contato)
);

CREATE TABLE emprego_atual (
    id_contato INTEGER NOT NULL,
    cargo VARCHAR(40) NOT NULL,
    salario FLOAT NOT NULL,
    data_inicio DATE,
    PRIMARY KEY(id_contato),
    FOREIGN KEY(id_contato) REFERENCES meus_contatos(id_contato)
);

CREATE TABLE interesses (
    id_interesse INTEGER NOT NULL AUTO_INCREMENT,
    interesse VARCHAR(30),
    PRIMARY KEY(id_interesse)
);

CREATE TABLE contato_interesse (
    id_contato INTEGER NOT NULL,
    id_interesse INTEGER NOT NULL,
    PRIMARY KEY(id_contato, id_interesse),
    FOREIGN KEY(id_contato) REFERENCES meus_contatos(id_contato),
    FOREIGN KEY(id_interesse) REFERENCES interesses(id_interesse)
);

CREATE TABLE procura (
    id_procura INTEGER NOT NULL AUTO_INCREMENT,
    procura VARCHAR(30),
    PRIMARY KEY(id_procura)
);

CREATE TABLE contato_procura (
    id_contato INTEGER NOT NULL,
    id_procura INTEGER NOT NULL,
    PRIMARY KEY(id_contato, id_procura),
    FOREIGN KEY(id_contato) REFERENCES meus_contatos(id_contato),
    FOREIGN KEY(id_procura) REFERENCES procura(id_procura)
);

CREATE TABLE lista_empregos(
    id_emprego INTEGER NOT NULL AUTO_INCREMENT,
    cargo VARCHAR(40) NOT NULL,
    salario FLOAT NOT NULL,
    num_cep VARCHAR(10),
    descricao TEXT,
    PRIMARY KEY(id_emprego),
    FOREIGN KEY(num_cep) REFERENCES cep(num_cep)
);

#----------------------------------------------------------------------------
# INSERÇÃO DE VALORES

INSERT INTO profissao (nome) VALUES
    ("Pedreiro"),
    ("Médico"),
    ("Eletricista"),
    ("Carpinteiro");

INSERT INTO cep VALUES
    ("35570-000", "Formiga", "MG"),
    ("38459-170", "Sabará", "MG"),
    ("15679-562", "Cubatão", "SP"),
    ("45698-125", "Santos", "SP");

INSERT INTO estado_civil (nome) VALUES
    ("Solteiro"),
    ("Casado"),
    ("Viúvo"),
    ("Divorciado");

INSERT INTO interesses (interesse) VALUES
    ("Natação"),
    ("Cozinhar"),
    ("Ciclismo"),
    ("Computadores");

INSERT INTO meus_contatos
(sobrenome, prenome, telefone, email, sexo, aniversario, id_profissao, num_cep, id_estado_civil) VALUES
    ("da Silva", "Carlos", "3322-1565", "[email protected]", "M", "2000-10-5", 1, "35570-000", 4),
    ("Menezes", "José", "3321-8998", "[email protected]", "M", "1970-10-11", 2, "45698-125", 1),
    ("dos Santos", "Maria", "3321-8998", "[email protected]", "F", "1993-05-20", 3, "38459-170", 2),
    ("Assis", "Joana", "3321-7855", "[email protected]", "F", "1950-03-05", 2, "15679-562", 3),
    ("Seixas", "Raul", "2115-8998", "[email protected]", "M", "1960-03-15", 3, "35570-000", 3),
    ("Camargo", "Marcos", "1587-8989", "[email protected]", "M", "1975-05-20", 3, "38459-170", 3),
    ("das Cruzes", "Beatriz", "1123-6565", "[email protected]", "F", "1992-06-27", 1, "35570-000", 3),
    ("Pereira", "Tatiana", "4545-8978", "[email protected]", "F", "1984-07-19", 1, "15679-562", 3);

INSERT INTO contato_interesse (id_contato, id_interesse) VALUES
    (1, 2),
    (2, 3),
    (1, 4),
    (3, 1),
    (4, 4),
    (4, 2);

INSERT INTO emprego_atual VALUES
(1, "Assistente", 3500, "2010-10-05"),
(2, "Plantonista", 10750, "2005-09-30"),
(3, "Engenheiro", 7120, "2016-12-05"),
(4, "Cirurgião", 9123, "2000-03-15"),
(5, "Engenheiro", 6535, "2014-05-10"),
(6, "Cirurgião", 10750, "2005-08-20");

INSERT INTO emprego_desejado (id_contato, cargo, salario_minimo, salario_maximo, disponibilidade, anos_experiencia) VALUES
(1, "Assistente Senior", 5000, 8000, "Integral", 8),
(2, "Cirurgião", 10000, 15000, "De 13:00 às 20:00", 13),
(3, "Engenheiro Chefe", 8500, 12000, "40 horas semanais", 2),
(4, "Cirurgião Chefe", 15000, 20000, "Integral", 18);

INSERT INTO lista_empregos (cargo, salario, num_cep, descricao) VALUES
("Programador", 7000, "35570-000", "Programador com experiência em Python."),
("Programador", 5500, "35570-000", "Programador com experiência em Java."),
("Cirurgião", 11000, "15679-562", "Cirurgião com especialização."),
("Engenheiro Chefe", 9000, "45698-125", "Engenheiro de minas."),
("Administrador", 15000, "45698-125", "Administrador com experiência em BD.");


# -----------------------------------


# Problema 1

# Procura-se Cirurgião com experiência superior a cinco anos. Salário entre R$10.500,00 e R$13.000,00

SELECT
    *
FROM
    meus_contatos INNER JOIN emprego_desejado ON meus_contatos.id_contato = emprego_desejado.id_contato
WHERE
    emprego_desejado.cargo = "Cirurgião" AND emprego_desejado.salario_minimo <= 10500 AND emprego_desejado.anos_experiencia >= 5;


# -----------------------------------


# Problema 2

# Como encontrar a melhor combinação de contatos para uma lista de diferentes empregos?

# Passo 01: selecionar todos os cargos disponíveis:
SELECT
    cargo
FROM
    lista_empregos
GROUP BY
    cargo
ORDER BY
    cargo;


# Passo 02: efetuar uma consulta em emprego_desejado para verificar quais são os interessados nos cargos listados anteriormente:
SELECT
    meus_contatos.prenome, meus_contatos.sobrenome, meus_contatos.telefone, emprego_desejado.cargo
FROM
    meus_contatos INNER JOIN emprego_desejado ON meus_contatos.id_contato = emprego_desejado.id_contato
WHERE
    emprego_desejado.cargo IN ("Programador", "Cirurgião", "Engenheiro Chefe", "Administrador");


# Como juntar as duas consultas em uma?
SELECT
    meus_contatos.prenome, meus_contatos.sobrenome, meus_contatos.telefone, emprego_desejado.cargo
FROM
    meus_contatos INNER JOIN emprego_desejado ON meus_contatos.id_contato = emprego_desejado.id_contato
WHERE
    emprego_desejado.cargo IN (SELECT cargo FROM lista_empregos);


# -----------------------------------


# Problema 3

# Selecionar os nomes e sobrenomes dos meus contatos que moram em Formiga.

SELECT
    prenome, sobrenome
FROM
    meus_contatos
WHERE
    num_cep = (SELECT num_cep FROM cep WHERE cidade = "Formiga");


# Poderia ser feito com JOIN também:
SELECT
    prenome, sobrenome
FROM
    meus_contatos JOIN cep ON meus_contatos.num_cep = cep.num_cep
WHERE
    cidade = "Formiga";


# -----------------------------------


# Problema 4

# Obter o primeiro nome e o sobrenome do contato que possui o maior salário.

# Passo 01: selecionar quem ganha o maior salário:
SELECT
    MAX(salario)
FROM
    emprego_atual;


# Passo 02: conectar as tabelas meus_contatos e emprego_atual e selecionar o maior salário manualmente.
SELECT
    meus_contatos.prenome, meus_contatos.sobrenome, emprego_atual.salario
FROM
    meus_contatos INNER JOIN emprego_atual ON meus_contatos.id_contato = emprego_atual.id_contato
WHERE
    emprego_atual.salario = 10750;


# Passo 03: utilizar subconsulta para automatizar o processo.
SELECT
    meus_contatos.prenome, meus_contatos.sobrenome, emprego_atual.salario
FROM
    meus_contatos INNER JOIN emprego_atual ON meus_contatos.id_contato = emprego_atual.id_contato
WHERE
    emprego_atual.salario = (SELECT MAX(emprego_atual.salario) FROM emprego_atual);


# -----------------------------------


# Subconsultas como colunas

SELECT
    meus_contatos.prenome AS Nome,
    meus_contatos.sobrenome AS Sobrenome,
    (SELECT estado FROM cep WHERE meus_contatos.num_cep = num_cep) AS UF
FROM
    meus_contatos;


# -----------------------------------


# Problema 05

# Localizando pessoas com salario atual maior que o salário da pessoa com determinado e-mail.

SELECT
    meus_contatos.prenome, emprego_atual.salario
FROM
    meus_contatos NATURAL JOIN emprego_atual
WHERE
    emprego_atual.salario > (SELECT emprego_atual.salario FROM meus_contatos NATURAL JOIN emprego_atual WHERE email = "[email protected]");


# -----------------------------------


# Problema 06

# Localizando pessoas com cargo atual igual à lista de empregos disponíveis para emprego:

SELECT
    meus_contatos.prenome, emprego_atual.cargo
FROM
    emprego_atual NATURAL JOIN meus_contatos
WHERE
    emprego_atual.cargo IN (SELECT cargo FROM lista_empregos);


# -----------------------------------


# Problema 07:

# Localizando pessoas com cargo atual não compatível aos cargos da lista de empregos:

SELECT
    meus_contatos.prenome, emprego_atual.cargo
FROM
    emprego_atual NATURAL JOIN meus_contatos
WHERE
    emprego_atual.cargo NOT IN (SELECT cargo FROM lista_empregos);


# -----------------------------------

# SUBCONSULTAS CORRELACIONADAS

# -----------------------------------


# Problema 08

# Selecionar os contatos que possuem dois interesses:

SELECT
    mc.prenome
FROM
    meus_contatos AS mc
WHERE
    2 = (SELECT COUNT(*) FROM contato_interesse WHERE id_contato = mc.id_contato);


# Problema 09

# Precisamos encontrar o nome, sobrenome e email de todos os contatos que não estão na tabela emprego_atual:

SELECT
    mc.prenome, mc.sobrenome, mc.email
FROM
    meus_contatos AS mc
WHERE
    NOT EXISTS (SELECT * FROM emprego_atual WHERE mc.id_contato = emprego_atual.id_contato);


# Problema 10

# Precisamos encontrar o nome, sobrenome e email de todos os contatos que estão na tabela emprego_atual:

SELECT
    mc.prenome, mc.sobrenome, mc.email
FROM
    meus_contatos AS mc
WHERE
    EXISTS (SELECT * FROM emprego_atual WHERE mc.id_contato = emprego_atual.id_contato);


# ----------------------------------------

# TRANSFORMANDO SUBCONSULTAS EM CONEXÕES

# ----------------------------------------

# Utilizando subconsulta:

SELECT
    meus_contatos.prenome, meus_contatos.sobrenome, meus_contatos.telefone, emprego_atual.cargo
FROM
    emprego_atual INNER JOIN meus_contatos ON emprego_atual.id_contato = meus_contatos.id_contato
WHERE
    emprego_atual.cargo IN (SELECT cargo FROM lista_empregos);


SELECT
    meus_contatos.prenome, meus_contatos.sobrenome, meus_contatos.telefone, emprego_atual.cargo
FROM
    emprego_atual
    INNER JOIN meus_contatos ON emprego_atual.id_contato = meus_contatos.id_contato
    INNER JOIN lista_empregos ON emprego_atual.cargo = lista_empregos.cargo;


# ----------------------------------------

# OPERAÇÕES DE CONJUNTOS

# ----------------------------------------

# UNION

# Como obter uma lista de todos os cargos que se encontram no banco de dados?

# Solução parcial
SELECT cargo FROM emprego_atual;
SELECT cargo FROM emprego_desejado;
SELECT cargo FROM lista_empregos;

# Mas, como obter isso em uma lista ordenada pelo cargo?
SELECT cargo FROM emprego_atual
UNION
SELECT cargo FROM emprego_desejado
UNION
SELECT cargo FROM lista_empregos
ORDER BY cargo;


# UNION ALL

# Mesmo exemplo anterior (os cargos que aparecem repetidos não foram removidos)
SELECT cargo FROM emprego_atual
UNION ALL
SELECT cargo FROM emprego_desejado
UNION ALL
SELECT cargo FROM lista_empregos
ORDER BY cargo;


# Podemos criar uma tabela a partir de qualquer comando select, por exemplo, vamos criar uma tabela com os cargos cadastrados:
CREATE TABLE cargos_completo AS
    SELECT cargo FROM emprego_atual
    UNION
    SELECT cargo FROM emprego_desejado
    UNION
    SELECT cargo FROM lista_empregos
    ORDER BY cargo;

SELECT * FROM cargos_completo;


# Intersecção e Diferença (o MySQL não dá suporte para os operadores Intersect e Except)

# Interseção

SELECT cargo FROM emprego_atual
WHERE cargo IN
(
    SELECT cargo
    FROM emprego_desejado
)
GROUP BY cargo;


# Diferença

SELECT cargo FROM emprego_atual
WHERE cargo NOT IN
(
    SELECT cargo
    FROM emprego_desejado
)
GROUP BY cargo;