MySQL Quick Reference (Syntax Version)

ACID properties:

Atomicity: All or None, if the transaction fails in between, it rolls back entirely.

Consistency: If the transaction has occurred between two parties, then it should reflect on both sides.

Isolation: If multiple transactions are running concurrently, they should not be affected by each other.

Durability: Hardware and Software failure must not cause the data loss during transaction.

DML: insert, update, delete, merge

DDL: Create, drop, truncate: deletes all the rows (where cannot be used), alter, rename.

DQL: select

DCL: grant, revoke

TCL: commit, rollback, savepoint- sets savepoint within transaction.

Operator order of precedence: SQL will always give higher precedence to AND followed by OR.

Wild Card Characters: % for sequence of characters, _ for a single character and * to select everything.

Syntax:

use employees;
#IN OPERATOR
select * from employees where first_name in ('Mark','Cathie','Nathan');

#LIKE OPERATOR
select * from employees where first_name like 'J%';

#BETWEEN OPERATOR
select * from employees where hire_date between '1990-01-01' and '1999-01-01';

#IS NOT NULL
select * from employees where first_name is NOT NULL;

#NOT EQUAL OPERATOR <> OR !=
select * from employees where first_name <> 'Georgi';
select * from employees where first_name != 'Georgi';

#DISTINCT KEYWORD
select distinct gender from employees;

#AGGREGATE FUNCTIONS

#1. COUNT : COUNTS ALL THE NON NULL RECORDS
select count(1) from employees;
#2. SUM
select sum(emp_no) from employees;
#3. MIN
select min(emp_no) from employees;
#4. MAX
select max(emp_no) from employees;
#5. AVG
select avg(emp_no) from employees;

#ORDER BY ASCENDING AND DESCENDING
select * from employees order by first_name asc;
select * from employees order by first_name DESC;

#GROUP BY GROUPS SIMILAR VALUES
#GROUP BY MUST BE PLACED RIGHT AFTER WHERE AND JUST BEFORE THE ORDER BY CLAUSE
#ALWAYS INCLUDE THE GROUP BY FIELD IN THE SELECT STATEMENT
#GROUP BY IS USED WITH AGGREGATE FUNCTIONS

SELECT
first_name, COUNT(1)
FROM
employees
GROUP BY first_name
ORDER BY first_name;

#COLUMN ALIAS
select first_name as name from employees;

#HAVING
#HAVING IS USED ALONG WITH THE GROUP BY BLOCK
#HAVING CAN HAVE AGGREGATE FUNCTIONS IN CONDITIONS WHILE WHERE CLAUSE CANNOT
SELECT
first_name, COUNT(1)
FROM
employees
GROUP BY first_name
HAVING COUNT(first_name) > 230
ORDER BY first_name ASC;

SELECT
*, AVG(salary)
FROM
salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000;

#HINT 1: USE HAVING WHEN AGGREGATE FUNCTIONS ARE INVOLVED IN THE CONDITION
#HINT 2: NEVER HAVE AN AGGREGATE AND NON AGGREGATE CONDITION TOGETHER IN HAVING.
SELECT
first_name, COUNT(first_name) AS name_count
FROM
employees
WHERE
hire_date > '1999-01-01'
GROUP BY first_name
HAVING COUNT(first_name) < 200;

#Select the employee numbers of all individuals
#who have signed more than 1 contract after the 1st of January 2000.

SELECT
emp_no, COUNT(dept_no) as contracts_signed
FROM
dept_emp
WHERE
from_date < '2000-01-01'
GROUP BY emp_no
HAVING COUNT(dept_no) > 1;

#LIMIT KEYWORD

SELECT
*
FROM
salaries
ORDER BY salary DESC
LIMIT 10;

#ROUND FUNCTION

select round(avg(salary)) from salaries;

#JOINS
#INNER JOIN: COMMON ELEMENTS FROM BOTH TABLES. IGNORE OTHERS.
#INNER JOIN IS THE DEFAULT JOIN

SELECT
e.emp_no, e.first_name, s.salary
FROM
employees e
JOIN
salaries s ON e.emp_no = s.emp_no;

#GROUP BY CAN BE USED TO DEAL WITH DUPLICATE ROWS, FOR EXAMPLE GROUPING THE EMP NOS IN THIS CASE.

#LEFT JOIN
SELECT
dm.emp_no, dm.dept_no, e.first_name
FROM
dept_manager dm
LEFT JOIN
employees e ON dm.emp_no = e.emp_no;

#RIGHT JOIN AND LEFT JOIN CAN BE USED INTERCHANGABLY BY SWAPPING THE TABLES

#CROSS JOIN: THE CARTESIAN PRODUCT OF THE TWO TABLES
select * from departments CROSS JOIN dept_manager;

#CROSS JOIN CAN ALSO BE IMPLEMENTED BY USING INNER JOIN WITHOUT THE ON CLAUSE

select * from departments inner join dept_manager; #WILL GIVE THE SAME OUTPUT

#BUT USING CROSS JOIN IS THE BEST PRACTICE


#FULL JOIN CAN BE IMPLEMENTED USING UNION OF TWO TABLES

SELECT
e.emp_no,
e.first_name,
e.last_name,
NULL AS dept_no,
NULL AS from_date
FROM
employees e
WHERE
e.emp_no = 10001
UNION ALL SELECT
d.dept_no,
d.from_date,
NULL AS emp_no,
NULL AS first_name,
NULL AS last_name
FROM
dept_manager d;

#UNION ALL RETURNS DUPLICATE ROWS WHILE UNION RETURNS DISTINCT ROWS.

#NESTED QUERIES/ INNER QUERIES/ SUB QUERIES
#USING THE IN KEYWORD
SELECT
emp_no, first_name, last_name
FROM
employees
WHERE
emp_no IN (SELECT
emp_no
FROM
dept_manager);

#USING EXISTS KEYWORD REUTRNS BOOLEAN VALUE IF THE RECORD EXISTS
SELECT
emp_no, first_name, last_name
FROM
employees
WHERE
EXISTS (SELECT
emp_no
FROM
dept_manager);

#EXIST CHECKS IF RECORDS ARE PRESENT WHILE IN KEYWORD SEARCHES FOR THE RECORDS
#EXISTS IS EFFICIENT FOR LARGE DATASETS WHILE IN IS EFFICIENT FOR SMALLER ONES

#JOINS ARE MORE EFFICIENT COMPARED TO NESTED QUERIES

#STORED ROUTINES CAN BE OF TWO TYPES: PROCEDURES AND FUNCTIONS
#USE KEYWORD IS NOT ALLOWED IN PROCEDURES
DELIMITER $$
CREATE PROCEDURE TEST_1()
BEGIN

SELECT
*
FROM
EMPLOYEES
LIMIT 10000;
END$$ ;

DELIMITER ; #RESETTING THE DELIMITER IS VERY IMPORTANT

#CALLING THE STORED PROCEDURE
CALL TEST_1();

#USING INPUT PARAMETERS IN PROCEDURES
# IN KEYWORD IS USED TO DENOTE THE PROCEDURE INPUT PARAMETERS
drop procedure emp_salary;

DELIMITER $$
CREATE PROCEDURE EMP_SALARY(IN emp_first_name char(200))
BEGIN
SELECT
e.emp_no, e.first_name, e.last_name, s.salary
FROM
employees e
INNER JOIN
salaries s ON e.emp_no = s.emp_no
WHERE
e.first_name IN (emp_first_name);
END $$
DELIMITER ;

call emp_salary('Nathan');

#PROCEDURES WITH OUTPUT PARAMETERS
#OUT KEYWORD IS USED TO STORE THE OUTPUT OF THE PROCEDURE IN A VARIABLE
#ALWAYS USE SELECT INTO 'OUT PARAMETER' WHEN USING THE OUT PARAMETER
drop procedure avg_salary;
delimiter $$
create procedure avg_salary(in employee_number integer, out average_salary decimal(7,2))
begin
select avg(salary) as average_salary into average_salary from salaries where emp_no = employee_number
limit 1000;
end$$ ;
delimiter ;

#WE WOULD CALL THE ABOVE PROCEDURE WITH IN AND OUT PARAMETER AS FOLLOWS
set @average_salary = 0;
call employees.avg_salary(10001, @average_salary);
#DISPLAY THE OUT PARAMETER OR ANY SQL VARIABLE
SELECT @average_salary;

#IN-OUT PARAMETERS CAN ALSO BE USED WHEN WE WANT THE OUTPUT OF THE PROCEDURE INSIDE THE SAME VARIABLE
#AVERAGE SALARY WILL BE STORED IN employee_number ITSELF
drop procedure avg_salary;
delimiter $$
create procedure avg_salary(INOUT employee_number decimal(7,2))
begin
select avg(salary) as average_salary into employee_number from salaries where emp_no = employee_number
limit 1000;
end$$ ;
delimiter ;

#USER DEFINED FUNCTION
delimiter $$
create function avg_salary(emp_no INTEGER) returns decimal(8,2)
begin
declare average_salary decimal(8,2);
SELECT
AVG(salary)
INTO average_salary FROM
salaries
WHERE
emp_no = emp_no;
return average_salary;
end$$
delimiter ;
#ERROR : Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
#SOLVED USING BELOW AND FUNCTION SUCCESSFULLY CREATED
SET GLOBAL log_bin_trust_function_creators = 1;
#ADDING DETERMINISTIC KEYWORD BEFORE BEGIN SOLVES THE ERROR TOO

#WE CALL THE FUNCTION USING SELECT KEYWORD
select employees.avg_salary(13540);

#DIFFERENCES BETWEEN PROCEDURES AND FUNCTIONS
# 1. FUNCTION RETURNS ONLY ONE value WHILE PROCEDURE CAN HAVE MULTIPLE OUT PARAMETERS. A PROCEDURE CAN ALSO RETURN A RESULT SET.
# 2. SELECT FUNCTION AND CALL PROCEDURE.
# 3. INSERT UPDATE AND DELETE IS TO BE USED INSIDE PROCEDURE AS THEY DO NOT RETURN VALUE. EVERY FUNCITON HAS TO RETURN A VALUE.
# 4. FUNCTIONS CAN BE USED IN SELECT STATEMENT WHILE A PROCEDURE CANNOT BE USED

#SQL CASE STATEMENT

SELECT
first_name,
last_name,
CASE
WHEN gender = 'M' THEN 'Male'
ELSE 'Female'
END AS gender
FROM
employees;

#SQL IF STATEMENT

SELECT
first_name,
last_name,
IF(gender = 'M', 'Male', 'Female') AS gender
FROM
employees;

Hi good to see y'all, I am an aspiring data analyst and will be posting stuff about Statistics, Python and R and also some interesting projects I do. B-)