queries-3-2-ch30.sql

-- SELECT * FROM sakila.address;

-- Ver os endereços com nomes das cidades
-- SELECT a.address_id, a.address, c.city FROM sakila.address AS a
-- INNER JOIN sakila.city AS c
-- ON a.city_id = c.city_id
-- WHERE a.address_id = 8

-- Ver qual cidade tem mais endereços
-- SELECT COUNT(*), sakila.address.city_id, sakila.city.city FROM sakila.address
-- INNER JOIN sakila.city
-- ON sakila.address.city_id = sakila.city.city_id
-- GROUP BY city_id
-- ORDER BY COUNT(*) DESC
-- LIMIT 1

-- Ver os endereços dessa cidade com o nome da cidade
-- SELECT a.address_id, a.address, c.city, a.city_id FROM sakila.address AS a
-- INNER JOIN sakila.city AS c
-- ON a.city_id = c.city_id
-- WHERE c.city = 'Anpolis'

-- Ver a lista de atores de um filme
-- SELECT fa.actor_id, fa.film_id, CONCAT(a.first_name,' ',a.last_name) AS name, f.title FROM sakila.film_actor AS fa
-- INNER JOIN sakila.actor AS a
-- ON fa.actor_id = a.actor_id
-- INNER JOIN sakila.film AS f
-- ON fa.film_id = f.film_id
-- WHERE f.film_id = 1

-- Agrupar lista de atores em uma linha só
-- SELECT fa.film_id, f.title, JSON_ARRAYAGG(CONCAT(a.first_name,' ',a.last_name)) AS cast FROM sakila.film_actor AS fa
-- INNER JOIN sakila.actor AS a
-- ON fa.actor_id = a.actor_id
-- INNER JOIN sakila.film AS f
-- ON fa.film_id = f.film_id
-- GROUP BY f.film_id

-- Ver endereço, com nome da cidade e nome do país
-- SELECT address_id, address, a.city_id, c.city, c.country_id, co.country FROM sakila.address AS a
-- INNER JOIN sakila.city AS c
-- ON c.city_id = a.city_id
-- INNER JOIN sakila.country AS co
-- ON c.country_id = co.country_id

-- Todas as pessoas
-- SELECT * FROM pettrybe.person;

-- Todas as pessoas com as infos dos seus pets
-- SELECT * FROM pettrybe.person AS per
-- LEFT JOIN pettrybe.pet AS pet
-- ON per.id = pet.person_id

-- Todos os pets independente com infos das pessoas, independente de ter pessoas relacionadas a eles ou não
-- SELECT * FROM pettrybe.person AS per
-- RIGHT JOIN pettrybe.pet AS pet
-- ON per.id = pet.person_id

-- UPDATE pettrybe.pet 
-- SET person_id = 4
-- WHERE id = 7

DROP DATABASE IF EXISTS `pettrybe`;
CREATE DATABASE `pettrybe`;
USE `pettrybe`;

CREATE TABLE `person` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `pet` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `person_id` INT NULL,
  `type` VARCHAR(100),
  `name` VARCHAR(100),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
);

INSERT INTO `person` (`id`, `name`) VALUES 
  (1, 'John'),
  (2, 'Sarah'),
  (3, 'Rachel'),
  (4, 'Sam');

INSERT INTO `pet` (`id`, `person_id`, `type`, `name`) VALUES 
  (1, 1, 'goldfish', 'Fishy'),
  (2, 1, 'goldfish', 'Nemo'),
  (3, 1, 'dog', 'Fido'),
  (4, 2, 'cat', 'Kitty'),
  (5, 2, 'bird', 'Feathers'),
  (6, 3, 'chinchilla', 'Fuzzy'),
  (7, NULL, 'iguana', 'Scales');
  
  CREATE TABLE pettrybe.employee (
	id INT PRIMARY KEY auto_increment,
    name varchar(50) not null,
    manager INT,
    FOREIGN KEY (manager) REFERENCES employee(id)
);

INSERT INTO pettrybe.employee (name, manager) VALUES 
	('Jade', null),
    ('Isaac', 1),
    ('Ale', 1),
    ('Setinha', 1),
    ('Felipe Trindade', null),
    ('Felipe BN', 5)
    

SELECT e.name, m.name as managerName 
FROM pettrybe.employee AS e, pettrybe.employee AS m
WHERE e.manager = m.id