modelo_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", "carlos@gmail.com", "M", "2000-10-5", 1, "35570-000", 4),
    ("Menezes", "José", "3321-8998", "jose@hotmail.com", "M", "1970-10-11", 2, "45698-125", 1),
    ("dos Santos", "Maria", "3321-8998", "mariasantos@gmail.com", "F", "1993-05-20", 3, "38459-170", 2),
    ("Assis", "Joana", "3321-7855", "joanaassis@gmail.com", "F", "1950-03-05", 2, "15679-562", 3),
    ("Seixas", "Raul", "2115-8998", "seixas.raul@gmail.com", "M", "1960-03-15", 3, "35570-000", 3),
    ("Camargo", "Marcos", "1587-8989", "carmargom@gmail.com", "M", "1975-05-20", 3, "38459-170", 3),
    ("das Cruzes", "Beatriz", "1123-6565", "biacruz@gmail.com", "F", "1992-06-27", 1, "35570-000", 3),
    ("Pereira", "Tatiana", "4545-8978", "tatipereira@gmail.com", "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



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


# Problema 2

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

# Passo 01: selecionar todos os cargos disponíveis:



# Passo 02: efetuar uma consulta em emprego_desejado para verificar quais são os interessados nos cargos listados anteriormente:



# Como juntar as duas consultas em uma?



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


# Problema 3

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



# Poderia ser feito com JOIN também:



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


# 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:



# Passo 02: conectar as tabelas meus_contatos e emprego_atual e selecionar o maior salário manualmente.



# Passo 03: utilizar subconsulta para automatizar o processo.



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


# Subconsultas como colunas



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


# Problema 05

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



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


# Problema 06

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



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


# Problema 07:

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



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

# SUBCONSULTAS CORRELACIONADAS

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


# Problema 08

# Selecionar os contatos que possuem dois interesses:



# Problema 09

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



# Problema 10

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



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

# TRANSFORMANDO SUBCONSULTAS EM CONEXÕES

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

# Utilizando subconsulta:



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

# OPERAÇÕES DE CONJUNTOS

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

# UNION

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

# Solução parcial


# Mas, como obter isso em uma lista ordenada pelo cargo?


# UNION ALL

# Mesmo exemplo anterior (os cargos que aparecem repetidos não foram removidos)



# Podemos criar uma tabela a partir de qualquer comando select, por exemplo, vamos criar uma tabela com os cargos cadastrados:



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

# Intersecção



# Diferença