--Salespeople

CREATE TABLE salespeople (
	Id 				SERIAL PRIMARY KEY,
	first_name			VARCHAR(50),
	last_name			VARCHAR(50),
	salary			INT,
	commission_rate		REAL,
	commission_2021		REAL,
	branch_id			REAL);

INSERT INTO	salespeople (id, first_name,last_name,salary,commission_rate,commission_2021,branch_id)
VALUES
	(11,'Katarina','Rostova',45000,0.15,47345.60,1),
	(12,'Alina','Park',43000,0.15,45678.90,2),
	(13,'Meera','Malik',50000,0.15,39045.63,2),
	(17,'Samar','Navabi',52000,0.14,23023.45,2),
	(18,'Donald','Ressler',40000,0.14,41345.75,2),
	(20,'Elisabeth','Keen',59000,0.14,45350.00,2),
	(21,'Tom','Keen',41000,0.12,41560.75,1),
	(22,'Dembe','Zuma',40000,0.12,31540.70,5),
	(23,'Aram','Mojtabai',50000,0.12,29050.65,2),
	(30,'Kate','Kaplan',54000,0.10,25760.45,5),
	(32,'Marvin','Gerard',55000,0.10,22500.00,5),
	(34,'Raymond','Reddington',60000,0.10,17570.80,5),
	(35,'Harold','Cooper',57000,0.10,15450.50,2),
	(37,'Ian', 'Garvey',43000,0.08,NULL,1),
	(38,'Ivan','Stepanov',41000,0.08,NULL,1);

SELECT * FROM salespeople;

--Where >=
SELECT *
	FROM salespeople
	WHERE salary >= 50000;

--Where >
SELECT *
	FROM salespeople
	WHERE commission_rate > 0.12;
SELECT *
	FROM salespeople
	WHERE commission_2021 > salary;

--Where BETWEEN
SELECT *
	FROM salespeople
	WHERE commission_rate BETWEEN 0.10 AND 0.14;

--Where <
SELECT *
	FROM salespeople
	WHERE last_name < ‘Keen’;
--LIKE
SELECT *
	FROM salespeople
	WHERE last_name LIKE 'K%';

--IS NULL
SELECT *
	FROM salespeople
	WHERE commission_2021 IS NULL;

-- IN
SELECT *
	FROM salespeople
	WHERE last_name IN ('Kaplan', 'Gerard', 'Zuma');

--AND
SELECT *
	FROM salespeople
	WHERE branch_id = 5 AND salary >= 50000;

--OR
SELECT *
	FROM salespeople
	WHERE branch_id = 5 OR last_name = 'Reddington';

--NOT
SELECT *
	FROM salespeople
	WHERE NOT branch_id = 2;

SELECT *
	FROM salespeople
	WHERE (last_name = ‘Keen’ OR last_name = ‘Park’)
	AND (commission_2021 > salary)
	AND (NOT branch_id = 2);

--DISTINCT
SELECT 
	DISTINCT salary
	FROM salespeople;

--ORDER BY
SELECT 
	DISTINCT salary
	FROM salespeople ORDER BY salary

--ORDER BY DSEC
SELECT 
	DISTINCT salary
	FROM salespeople ORDER BY salary DESC;

--ALIAS
SELECT 
	DISTINCT salary AS Salario_alto
	FROM salespeople 
	WHERE salary >= 50000;

--SUBQUERY SELECT (SELECT..)
SELECT 
	salespeople.first_name AS PRIMEIRO_NOME, salespeople.last_name AS ULTIMO_NOME, T.MAIOR_SALARIO
	FROM salespeople, (SELECT MAX(salary) AS MAIOR_SALARIO FROM salespeople) T
	WHERE salespeople.salary = T.MAIOR_SALARIO;
SELECT * 
	FROM salespeople
	WHERE salary > (SELECT AVG(salary) FROM salespeople);
SELECT
            P.id,
            P.nome as Produto,
            (SELECT
                COUNT(VP.id_produto)
                FROM
                    venda_produto VP
                WHERE
                    P.id = VP.id_produto
                GROUP BY
                    P.id
            ) as TOTAL_VENDIDO
        FROM
            produto P
        GROUP BY
            P.id