readme.md

Banco usado para os estudos: https://github.com/datacharmer/test_db

estudos-mysql.sql

select * from customers;

-- O valor dado no registro é aplicado aos registros já existentes ao adicionar uma nova coluna à tabela
alter table customers
      add level int default 1;

insert into customers (first_name, last_name, gender)
values ('Primeire', 'Teste', 'O');

insert into customers (first_name, last_name, gender)
values ('Segundo', 'Teste', 'M');

insert into customers (first_name, last_name, gender)
values ('Terceira', 'Teste', 'F');

-- A linha não deverá ser inserida pois o valor dado ao campo gender não está sendo validado
insert into customers (first_name, last_name, gender)
values ('Blabblap', 'Teste', 'X');

drop table customers;

-- Valida o campo gender com a constraint 'check'
create table customers (
    primary key (custom_no),
    custom_no  int         auto_increment,
    first_name varchar(14) not null,
    last_name  varchar(16) not null,
    gender     char        not null check(gender in ('O', 'F', 'M')),
    created_at datetime    default now()
);

-- Valida o campo gender com tipo 'enum'
create table customers (
    primary key (custom_no),
    custom_no  int                 auto_increment,
    first_name varchar(14)         not null,
    last_name  varchar(16)         not null,
    gender     enum('O', 'F', 'M') not null,
    created_at datetime            default now()
);

-- --------------------------------------------------------------------------------------------------------

-- all (subquery) = and | any (subquery) = or

-- Equivalente do not in
select *
  from employees
 where emp_no <> all
       (select inner_emp.emp_no
          from employees as inner_emp
         where inner_emp.emp_no <= 10010);
select *
  from employees
 where emp_no not in
       (select inner_emp.emp_no
          from employees as inner_emp
         where inner_emp.emp_no <= 10010);

-- Equivalente do in
select *
  from employees
 where emp_no = any
       (select inner_emp.emp_no
          from employees as inner_emp
         where inner_emp.emp_no <= 10010);
select *
  from employees
 where emp_no in
       (select inner_emp.emp_no
          from employees as inner_emp
         where inner_emp.emp_no <= 10010);

select emp_no
  from employees
 where emp_no <= 10010;

select emp_no
  from employees
 limit 10;

-- --------------------------------------------------------------------------------------------------------

-- Número de funcionários por departamento
select departments.dept_name, count(employees.emp_no) as employees_number
  from employees
 inner join current_dept_emp
       on employees.emp_no = current_dept_emp.emp_no
 inner join departments
       on current_dept_emp.dept_no = departments.dept_no
 group by departments.dept_no;

-- Último departamento de cada funcionário
select employees.emp_no, employees.first_name, employees.last_name, departments.dept_name
  from employees
 inner join current_dept_emp
       on employees.emp_no = current_dept_emp.emp_no
 inner join departments
       on current_dept_emp.dept_no = departments.dept_no;

-- Histórico de departamentos de cada funcionário
select employees.emp_no, employees.first_name, employees.last_name, dept_emp.from_date, departments.dept_name
  from employees
 inner join dept_emp
       on employees.emp_no = dept_emp.emp_no
 inner join departments
       on dept_emp.dept_no = departments.dept_no
 order by employees.emp_no;

select employees.emp_no, employees.first_name, employees.last_name, dept_emp.from_date
  from employees
 inner join dept_emp
       on employees.emp_no = dept_emp.emp_no;

-- --------------------------------------------------------------------------------------------------------

CALL get_employee_salary(499980);

DROP PROCEDURE IF EXISTS `get_employee_salary`;

DELIMITER $$
CREATE PROCEDURE get_employee_salary(In employeeId INT)
BEGIN

select employees.first_name, employees.last_name, salaries.salary
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where employees.emp_no = employeeId and salaries.from_date =
       (select max(salaries.from_date)
          from salaries
         where salaries.emp_no = employees.emp_no
         group by salaries.emp_no);

END $$
DELIMITER ;

-- ------------------------

-- Funcionário com o maior salário
select employees.emp_no, employees.first_name, employees.last_name, salaries.salary
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where salaries.to_date = '9999-01-01' and salaries.salary =
       (select max(max_salaries.salary)
          from salaries as max_salaries);

-- Último salário de todos os funcionários atuais (leve)
select employees.emp_no, employees.first_name, employees.last_name, salaries.salary
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where salaries.to_date = '9999-01-01' and salaries.salary;

-- Seleciona o último salário de todos funcionários (custoso)
select employees.emp_no, employees.first_name, employees.last_name, salaries.salary, salaries.to_date
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where salaries.from_date =
       (select max(salaries.from_date)
          from salaries
         where salaries.emp_no = employees.emp_no
         group by salaries.emp_no);

-- Seleciona o último salário de um funcionário (custoso)
select employees.emp_no, employees.first_name, employees.last_name, salaries.salary
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where employees.emp_no = 10001 and salaries.from_date =
       (select max(salaries.from_date)
          from salaries
         where salaries.emp_no = employees.emp_no
         group by salaries.emp_no);

select max(salaries.from_date) as last_date
  from salaries
 where salaries.emp_no = 10001
 group by salaries.emp_no;

select employees.emp_no, employees.first_name, employees.last_name, sum(salaries.salary), max(salaries.from_date), max(salaries.to_date)
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where employees.emp_no = 10001
 group by employees.emp_no;

select employees.emp_no, employees.first_name, employees.last_name, salaries.salary, salaries.from_date, salaries.to_date
  from employees
 inner join salaries on employees.emp_no = salaries.emp_no
 where employees.emp_no = 10001;