Trigger

 In this lesson, we will introduce you to MySQL triggers.

• By definition, a MySQL trigger is a type of stored program, associated with a table, that will be activated automatically once a specific event related to the table of association occurs. 
• This event must be related to one of the following three DML statements: INSERT, UPDATE, or DELETE.
• Therefore, triggers are a powerful and handy tool that professionals love to use where database consistency and integrity are concerned.
•  Moreover, to any of these DML statements, one of two types of triggers can be assigned – a "before", or an "after" trigger. In other words, a trigger is a MySQL object that can “trigger” a specific action or calculation ‘before’ or ‘after’ an INSERT, UPDATE or DELETE statement has been executed. For instance, a trigger can be activated before a new record is inserted into a table, or after a record has been updated.

Perfect! Let’s execute some code:
First, in case you are just starting Workbench, select “Employees” as your default database.

USE employees;
Then, execute a COMMIT statement, because the triggers we are about to create will make some changes to the state of the data in our database. At the end of the exercise, we will ROLLBACK up to the moment of this COMMIT.
COMMIT;

We said triggers are a type of stored program. Well, one could say the syntax resembles that of stored procedures, couldn’t they?

BEFORE INSERT
DELIMITER $$
CREATE TRIGGER before_salaries_insert
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END $$
DELIMITER ;
After stating we want to CREATE a TRIGGER and then indicating its name, we must indicate its type and the name of the table to which it will be applied. In this case, we devised a “before” trigger, which will be activated whenever new data is inserted in the “Salaries” table.

 Then, an interesting phrase follows – “for each row”. It designates that before the trigger is activated, MySQL will perform a check for a change of the state of the data on all rows. In our case, a change in the data of the “Salaries” table will be caused by the insertion of a new record.

Within the BEGIN-END block, you can see a piece of code that is easier to understand if you just read it without focusing on the syntax.

 The body of this block acts as the core of the “before_salaries_insert” trigger. Basically, it says that if the newly inserted salary is of negative value, it will be set as 0.
/*
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
*/

 From a programmer’s perspective, there are three things to note about these three lines of code.
 First, especially for those of you who are familiar with some programming, this is an example of a conditional. The IF statement starts the conditional block. Then, if the condition for negative salary is satisfied, one must use the keyword THEN before showing what action should follow. The operation is terminated by the END IF phrase and a semi-colon.

The second thing to be noted here is even more interesting. That’s the use of the keyword NEW. In general, it refers to a row that has just been inserted or updated. In our case, after we insert a new record, “NEW dot salary” will refer to the value that will be inserted in the “Salary” column of the “Salaries” table.
The third part of the syntax regards the SET keyword. As you already know, it is used whenever a value has to be assigned to a certain variable. Here, the variable is the newly inserted salary, and the value to be assigned is 0.

Let’s execute this query. BEFORE INSERT
DELIMITER $$
CREATE TRIGGER before_salaries_insert
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END $$
DELIMITER ;

 Let’s check the values of the “Salaries” table for employee 10001.
SELECT
    *
FROM
    salaries
WHERE
    emp_no = '10001';
 Now, let’s insert a new entry for employee 10001, whose salary will be a negative number.
INSERT INTO salaries VALUES ('10001', -92891, '2010-06-22', '9999-01-01');
 Let’s run the same SELECT query to see whether the newly created record has a salary of 0 dollars per year.
SELECT
    *
FROM
    salaries
WHERE
    emp_no = '10001';
You can see that the “before_salaries_insert” trigger was activated automatically. It corrected the value of minus 92,891 we tried to insert.
 Now, let’s look at a BEFORE UPDATE trigger. The code is similar to one of the triggers we created above, with two substantial differences.
BEFORE UPDATE
DELIMITER $$
CREATE TRIGGER trig_upd_salary
BEFORE UPDATE ON salaries
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = OLD.salary;
END IF;
END $$
DELIMITER ;

 First, we indicated that this will be a BEFORE UPDATE trigger.
/*
BEFORE UPDATE ON salaries
*/
 Second, in the IF conditional statement, instead of setting the new value to 0, we are basically telling MySQL to keep the old value.  Technically, this is achieved by setting the NEW value in the “Salary” column to be equal to the OLD one. This is a good example of when the OLD keyword needs to be used.
/*
IF NEW.salary < 0 THEN
SET NEW.salary = OLD.salary;
END IF;
*/
 Create the “before_salaries_update” trigger by executing the above statement.

Then, run the following UPDATE statement, with which we will modify the salary value of employee 10001 with another positive value.
UPDATE salaries
SET
    salary = 98765
WHERE
    emp_no = '10001'
        AND from_date = '2010-06-22';   
Execute the following SELECT statement to see that the record has been successfully updated.
SELECT
    *
FROM
    salaries
WHERE
    emp_no = '10001'
        AND from_date = '2010-06-22';
     
 Now, let’s run another UPDATE statement, with which we will try to modify the salary earned by 10001 with a negative value, minus 50,000.
UPDATE salaries
SET
    salary = - 50000
WHERE
    emp_no = '10001'
        AND from_date = '2010-06-22';   
 Let’s run the same SELECT statement to check if the salary value was adjusted.
SELECT
    *
FROM
    salaries
WHERE
    emp_no = '10001'
        AND from_date = '2010-06-22';   
 No, it wasn’t. Everything remained intact. So, we can conclude that only an update with a salary higher than zero dollars per year would be implemented.
 All right. For the moment, you know you have created only two triggers. But how could you prove that to someone who is seeing your script for the first time? Well, in the ‘info’ section of the “employees” database, you can find a tab related to triggers. When you click on its name,  MySQL will show you the name, the related event, table, timing, and other characteristics regarding each trigger currently in use.
Let’s introduce you to another interesting fact about MySQL. You already know there are pre-defined system variables, but system functions exist too! System functions can also be called built-in functions. Often applied in practice, they provide data related to the moment of the execution of a certain query.
 For instance, SYSDATE() delivers the date and time of the moment at which you have invoked this function.
SELECT SYSDATE();
 Another frequently employed function, “Date Format”, assigns a specific format to a given date. For instance, the following query could extract the current date, quoting the year, the month, and the day.
SELECT DATE_FORMAT(SYSDATE(), '%y-%m-%d') as today;

 Of course, there are many other ways in which you could format a date; what we showed here was just an example. So, using system functions seems cool, doesn’t it?
 You already know how to work with the syntax that allows you to create triggers.
As an exercise, try to understand the following query. Technically, it regards the creation of a more complex trigger. It is of the size that professionals often have to deal with.

DELIMITER $$
CREATE TRIGGER trig_ins_dept_mng
AFTER INSERT ON dept_manager
FOR EACH ROW
BEGIN
DECLARE v_curr_salary int;
 
    SELECT
MAX(salary)
INTO v_curr_salary FROM
salaries
WHERE
emp_no = NEW.emp_no;

IF v_curr_salary IS NOT NULL THEN
UPDATE salaries
SET
to_date = SYSDATE()
WHERE
emp_no = NEW.emp_no and to_date = NEW.to_date;

INSERT INTO salaries
VALUES (NEW.emp_no, v_curr_salary + 20000, NEW.from_date, NEW.to_date);
    END IF;
END $$
DELIMITER ;

After you are sure you have understood how this query works, please execute it and then run the following INSERT statement.
INSERT INTO dept_manager VALUES ('111534', 'd009', date_format(sysdate(), '%y-%m-%d'), '9999-01-01');

 SELECT the record of employee number 111534 in the ‘dept_manager’ table, and then in the ‘salaries’ table to see how the output was affected.
SELECT
    *
FROM
    dept_manager
WHERE
    emp_no = 111534;
 
SELECT
    *
FROM
    salaries
WHERE
    emp_no = 111534;

Conceptually, this was an ‘after’ trigger that automatically added $20,000 to the salary of the employee who was just promoted as a manager. Moreover, it set the start date of her new contract to be the day on which you executed the insert statement.
Finally, to restore the data in the database to the state from the beginning of this lecture, execute the following ROLLBACK statement.
ROLLBACK;

SQL Query

Please download the script from the GitHub.
Schema

Database-Schema

JOINS

• The SQL tool that allows us to construct a relationship between objects.
• A join shows a result set, containing fields derived from two or more tables.
• We must find a related column from the two tables that contain the same type of data.
• We will be free to add columns from these two tables to our output the columns you use to relate tables must represent the same object, such as id.
• The tables you are considering need not be logically adjacent

Types of joins
INNER JOIN = JOIN
LEFT JOIN = LEFT OUTER JOIN
RIGHT JOIN = RIGHT OUTER JOIN
CROSS JOIN

INNER JOIN
Extract only records in which the values in the related columns match. Null values, or values appearing in just one of the two tables and not appearing in the other, are not displayed.
Only non-null matching values are in play. And what if such matching values did not exist?
Simply, the result set will be empty. There will be no link between the two tables.
Syntax
SELECT
table_1.column_name(s), table_2.column_name(s)
FROM
table_1
JOIN

table_2 ON table_1.column_name = table_2.column_name;
LEFT JOIN
All matching values of the two tables and all values from the left table that match no values from the right table.
When working with left joins, the order in which you join tables matters.
Left joins can deliver a list with all records from the left table that do not match any rows from the right table.
Syntax
SELECT
table_1.column_name(s), table_2.column_name(s)
FROM
table_1
LEFT JOIN

table_2 ON table_1.column_name = table_2.column_name;
RIGHT JOIN
Their functionality is identical to LEFT JOINs, with the only difference being that the direction of the operation is inverted.
When applying a RIGHT JOIN, all the records from the right table will be included in the result set
values from the left table will be included only if their linking column contains a value coinciding, or matching, with a value from the linking column of the right table
Syntax
SELECT
table_1.column_name(s), table_2.column_name(s)
FROM
table_1
RIGHT  JOIN

table_2 ON table_1.column_name = table_2.column_name;

Note: LEFT and RIGHT joins are perfect examples of one to many relationships
UNION ALL
Used to combine a few SELECT statements in a single output you can think of it as a tool that allows you to unify tables. We have to select the same number of columns from each table.
These columns should have the same name, should be in the same order, and should contain related data types.
SQL SYNTAX
SELECT
N columns
FROM
table_1
UNION ALL SELECT
N columns
FROM
table_2;
Join more than two tables
SELECT
    e.first_name,
    e.last_name,
    e.hire_date,
    m.from_date,
    d.dept_name
FROM
    employees e
        JOIN
    dept_manager m ON e.emp_no = m.emp_no
        JOIN
    departments d ON m.dept_no = d.dept_no;
Here employees, dept_manager and departments.

UNION vs UNION ALL
When uniting two identically organized tables

• UNION displays only distinct values in the output whereas UNION ALL retrieves the duplicates as well.
• UNION uses more MySQL resources (computational power and storage space) whereas UNION ALL uses less MySQL resources.

Looking for better results: use UNION
Seeking to optimize performance: opt for UNION ALL
SUBQUERY

• Queries embedded in a query
• subqueries = inner queries = nested queries = inner select
• They are part of another query, called an outer query(outer select) a subquery should always be placed within parentheses.
• The SQL engine starts by running the inner query then it uses its returned output, which is intermediate, to execute the outer query.
• The subquery may return a single value(a scalar), single row, single column, or an entire table
• you can have a lot more than one subquery in your outer query it is possible to nest inner queries within other inner queries
• In that case, the SQL engine would execute the innermost query first, and then each subsequent query, until it runs the outermost query last

Select the second highest salary of the employee.

select e1.* from employee e1 where 1 = (select count(distinct salary) from employee e2 where e2.salary>e1.salary);
Next Topic: Stored Routines

Stored Routines

Routine in a context other than a computer, a usual, fixed action, or series of actions, repeated periodically.
Stored routine
An SQL statement, or a set of SQL statements, that can be stored on the database server
Whenever a user needs to run the query in question, they can call, reference, or invoke the routine.
Types of Store routines
1. Stored procedures
2. functions = user define functions not built-in functions like aggregate function ,date function
The MySQL Syntax for Stored Procedures
DELIMITER $$
CREATE or replace PROCEDURE procedure_name()
BEGIN
SQL QUERY
END$$
DELIMITER;  From this moment on, $$ will not act as a delimiter.
How can you call the Stored Procedure?
call employees.select_employees();
call employees.select_employees;
call select_employees();
call select_employees;
Here employees is the database name and select_employees is the stored procedure name.
Stored Procedures with an Input Parameter
A stored routine can perform a calculation that transforms an input value in output value.
Stored procedures can take an input value and then use it in the query, or queries, written in the body of the procedure, this value is represented by the IN parameter.
For example:
DELIMITER $$
CREATE PROCEDURE procedure_name
(in parameter)
BEGIN
SELECT * FROM employees
LIMIT 1000;
END$$
DELIMITER ;
After that calculation is ready, a result will be returned.
Stored Procedures with an Output Parameter
DELIMITER $$
CREATE PROCEDURE procedure_name
(in parameter, out parameter)
BEGIN
SELECT * FROM employees
LIMIT 1000;
END$$
DELIMITER ;
Note : 
Output Parameter: It will represent the variable containing the output value of the operation executed by the query of the stored procedure.
Every time you create a procedure containing both an IN and an OUT parameter, remember that you must use the SELECT INTO structure in the query of this object’s body.
For Example
DELIMITER $$
CREATE PROCEDURE emp_info(in p_first_name varchar(255), in p_last_name varchar(255), out p_emp_no integer)
BEGIN
SELECT
e.emp_no
INTO p_emp_no FROM
employees e
WHERE
e.first_name = p_first_name
AND e.last_name = p_last_name;
END$$
DELIMITER ;
Variables
when you are defining a program, such as a stored procedure, for instance, you can say you are using parameters
Parameters are a more abstract term
Once the structure has been solidified, then it will be applied to the database. The input value you insert is typically referred to as the argument, while the obtained output value is stored in a variable.
IN-OUT parameters
input = output
User-Defined Functions in MySQL
DELIMITER $$
CREATE FUNCTION function_name(parameter data_type) RETURNS data_type
DECLARE variable_name data_type
BEGIN
SELECT …
RETURN variable_name
END$$
DELIMITER ;
Note : 
Parameters: Here you have no OUT parameters to define between the parentheses after the object’s name, all parameters are IN, and since this is well known, you need not explicitly indicate it with the word, ‘IN’.
Although there are no OUT parameters, there is a return value
It is obtained after running the query contained in the body of the function.
How we can call the function in MySQL
SELECT function_name(input_value);

Difference between Stored Procedure and User-defined Function

Stored Procedure	User-defined function
It doesn’t return a value.	Returns a value.
CALL Procedure;	SELECT function;
It can have multiple OUT parameters.	It can return a single value only.
You can use SELECT, INSERT, UPDATE, DELETE with stored procedures.	But you can use only select statement with function.
You can’t include the procedure in a SELECT statement.	You can easily include function as a column in a SELECT statement.

Next Topic: VIEWS

MySQL Index

• The index of table functions as the index of a book
• Data is taken from a column of the table and is stored in a certain order in a distinct place called an index.
• Your datasets will typically contain 100,000+ or even 1,000,000+ records the larger a database is, the slower the process of finding the record or records you need.
• We can use an index that will increase the speed of searches related to a table
• Apply the index on large datasets don't apply the index on small datasets otherwise the costs of having an index might be higher than the benefits

Syntax to create an Index
CREATE INDEX index_name
ON table_name (column_1, column_2, …);

Note: The parentheses serve us to indicate the column names on which our search will be based.
These must be fields from your data table you will search frequently.
For example
CREATE INDEX i_hire_date ON employees(hire_date);

Composite Indexes
Applied to multiple columns, not just a single one carefully pick the columns that would optimize your search.
Primary and unique keys are MySQL indexes they represent columns on which a person would typically base their search.

Views

•  A virtual table whose contents are obtained from an existing table or tables called base tables.
• The view itself does not contain any real data; the data is physically stored in the base table.
• The view simply shows the data contained in the base table.
• Acts as a dynamic table because it instantly reflects data and structural changes in the base table.
• Don’t forget they are not real, physical data sets, meaning we cannot insert or update the information that has already been extracted. They should be seen as temporary virtual data tables retrieving information from base tables

Syntax to create a view in MySQL
CREATE OR REPLACE VIEW view_name AS
SELECT
column_1, column_2,… column_n
FROM
table_name;

For example: 
CREATE OR REPLACE VIEW v_dept_emp_latest_date AS
    SELECT
        emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
    FROM
        dept_emp
    GROUP BY emp_no;

A view acts as a shortcut for writing the same SELECT statement every time a new
the request has been made

How to call  the view in MySQL
SELECT * FROM employees.dept_emp_latest_date;
Note: In the above query employees is the database name and dept_emp_latest_date is the view name.

Benefits of Views
Saves a lot of coding time.
Occupies no extra memory

Next Topic: MySQL INDEX

SQL Data Type

String Data Type

String Data Type		Storage/ MAX_SIZE(Byte)	Example	Remark
Character	CHAR	Fixed/255	CHAR(5)	50% faster
Variable character	VARCHAR	Variable/65,535	VARCHAR(5)	a lot more responsive to the data value inserted
ENUM (enumeration)	ENUM		ENUM(‘M’,’F’)	MySQL will show an error if you attempt to insert any value  different from "M" or "F"

Numeric Data Type
Integer
Fixed-point
Floating-point

Integer
Integer data types are ‘signed’ by default.
If you want to use a range containing only positive, ‘unsigned’ values, you would have to specify this in your query.

Numeric Data Type	Size(byte)	MIN_VALUE      (signed/unsigned)	MIN_VALUE          (signed/unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32,768	32,767
		0	65,535
MEDIUMINT	3	-8,388,608	8,388,607
		0	16,777,215
INT	4	-2,147,483,648	2,147,483,647
		0	4,294,967,295
BIGINT	8	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
		0	18,446,744,073,709,551,615

Fixed-point
DECIMAL
NUMERIC
DECIMAL(P, S)
e.g. DECIMAL(5,3)
10.523, here precision is 5 and scale is 3.
367.875, here precision is 6 and scale is 3

Floating-point

Floating-Point Data Type	Size(byte)	Precision	Max number of digits
FLOAT	4	Single	23
DOUBLE	8	Double	53

Next Topic: JOINS