create company database

 CREATE TABLE employee(

    emp_id int primary key,
    first_name varchar(40),
    last_name varchar(40),
    birth_day DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);

CREATE TABLE branch(
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(40),
    mgr_id INT,
    mgr_start_date DATE,
    FOREIGN KEY (mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

ALTER TABLE employee
ADD FOREIGN KEY (branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY (super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;

CREATE TABLE client(
    client_id INT PRIMARY KEY,
    client_name VARCHAR(40),
    branch_id INT,
    FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);

CREATE TABLE works_with(
    emp_id INT,
    client_id INT,
    total_sales INT,
    PRIMARY KEY (emp_id, client_id),
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE  CASCADE,
    FOREIGN KEY (client_id ) REFERENCES client(client_id) ON DELETE cascade
);

CREATE TABLE branch_supplier(
    branch_id INT,
    supplier_name VARCHAR(40),
    supply_type VARCHAR(40),
    PRIMARY KEY (branch_id, supplier_name),
    FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);

Some of the queries of different types

-- Find all employees
SELECT *
FROM employee
ORDER BY salary desc;

-- Find all employee with sex and name
SELECT *
FROM employee
ORDER BY sex, first_name, last_name;

-- Find the first five employees in the table
SELECT *
FROM employee
LIMIT 5;

-- Find the first and last name of all employees
SELECT first_name as forename, last_name as surname
from employee;

-- Find out all the different genders
SELECT DISTINCT branch_id
from employee;

                        -- AGGREGATION FUNCTION


-- SUM, COUNT, AVG etc are this function
-- It is mostly used with GROUP BY

-- Find the number of employees
SELECT COUNT(super_id)
FROM employee;

-- Find the number of female employees born after 1970
SELECT *
FROM employee
WHERE sex = 'F' AND birth_day > '1971-01-01';

-- Find the average of all employee's salaries
SELECT AVG(salary)
FROM employee
WHERE sex='F';

-- Find the sum of all employee's salaries
SELECT SUM(salary)
from employee;

-- Find out how many males and how many females
SELECT COUNT(sex), sex
from employee
group by sex;

-- Find the total sales of each salesman
SELECT SUM(works_with.total_sales), works_with.emp_id, employee.first_name
from works_with, employee
WHERE works_with.emp_id = employee.emp_id
GROUP BY works_with.emp_id;


                                -- WILD CARDS


-- % is for any no of characters
-- _ is for one character

-- Find any client's who are an LLC
SELECT *
FROM client
WHERE client_name LIKE '%LLc';

-- Find any employee born in octorber
SELECT *
FROM employee
WHERE birth_day LIKE '____-10-__';

-- Find any clients who are schools
SELECT *
FROM client
WHERE client_name LIKE '%school%';


                                -- UNION

-- used for uniting two queries
-- Find a list of employee and branch names
SELECT first_name as company_NAMES
FROM employee
UNION
SELECT branch_name
FROM branch
UNION
SELECT client_name
FROM client;

-- Find a list of all clients & branch supplier's name
SELECT  client_name, client.branch_id
FROM client
UNION
SELECT supplier_name, branch_supplier.branch_id
FROM branch_supplier;

-- Find a list of all money spent or earned by company
SELECT salary
FROM employee
UNION
SELECT total_sales
FROM works_with;

                            -- Joins


-- Joins are of LEFT JOIN, RIGHT JOIN, OUTER JOIN
-- LEFT JOIN takes all element of left table
-- RIGHT JOIN takes all element of right table
-- JOIN is like intersection in venn-diagram
-- Find all branches and the names of their managers
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

-- Nested Query
-- QUERY IN QUERY -> for multiple result from inner query
-- Query = Query -> for single result from inner query

-- Find names of all employees who have
-- sold over 30,000 to a single client
SELECT first_name, SUM(total_sales)
FROM employee, works_with
WHERE employee.emp_id = works_with.emp_id
AND total_sales > 30000
GROUP BY first_name;

SELECT employee.first_name
FROM employee
WHERE employee.emp_id IN(
    SELECT works_with.emp_id
    FROM works_with
    WHERE total_sales > 30000
);

-- Find all clients who are handled by the branch
-- that michael scott manages
-- Assume you know Michael's ID

SELECT client.client_name
FROM client
WHERE branch_id = (
    SELECT employee.branch_id
    FROM employee
    WHERE first_name='Michael'
    LIMIT 1
);


                            -- ON DELETE


-- ON DELETE NULL, it makes the id null when deleted.
-- ON DELETE CASCADE, it deleted the entire row instead of making null
DELETE FROM employee
WHERE emp_id=102;

DELETE FROM branch
WHERE branch_id = 2;

SELECT *FROM branch_supplier;


                            -- Trigger


CREATE TABLE trigger_test(
    message VARCHAR(100)
);

DELIMITER $$
    CREATE
        TRIGGER my_trigger1 BEFORE INSERT
        ON employee
        FOR EACH ROW BEGIN
            INSERT INTO trigger_test VALUES(NEW.first_name);
        END $$
DELIMITER ;

-- Delimiter is used, so that the trigger won't end in between
-- Since we have delimiter; in insert statement, We create own our
-- Delimiter $$ and place after end, Which works same as ;
-- Finally change the delimiter to ; after work is done
-- NEW refers to the element that is inserted, here it is employee
-- Trigger name should be change for every new trigger

DELIMITER $$
    CREATE
        TRIGGER triggerForMaleAndFemale BEFORE INSERT
        ON employee
        FOR EACH ROW BEGIN
            IF NEW.sex = 'm' THEN
                INSERT INTO trigger_test VALUES ('added male employee');
            ELSEIF NEW.sex = 'F' THEN
                INSERT INTO trigger_test VALUES ('ADDED FEMALE');
            ELSE
                INSERT INTO trigger_test VALUES ('ADDED 6KA');
            END IF;
        END$$
DELIMITER ;

SELECT * FROM trigger_test;

INSERT INTO employee
VALUES (112, 'OSCAR', 'PURI', '2020-02-20', 'C',65000, 106, 3);

DROP TRIGGER my_trigger1;