1.




2. DATABASE
create database myDB;

use myDB;

drop database myDB;

alter database myDB read only = 1;
alter database myDB read only = 0;




3. TABLES

use myDB;

CREATE TABLE employees (
	employee_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	hourly_pay DECIMAL (5, 2),
	hire_date DATE
);

select * from employees;

rename table employees to workers;
rename table workers to employees;

drop table employees;

alter table employees add phone_number varchar(15);

alter table employees rename column phone_number to email;

alter table employees modify column email varchar(100);

alter table employees modify email varchar(100) after last_name;
select * from employees;

alter table employees modify email varchar(100) first;

alter table employees drop column email;




4. INSERT ROWS

select * from employees;

insert into employees values (1, "Eugene", "Krabs", 25.50, "2023-01-02");

insert into employees values 
    (2, "Squidward", "Tentacles", 15.00, "2023-01-03"),
    (3, "Spongebob", "Squarepants", 12.50, "2023-01-04"),
    (4, "Patrick", "Star", 12.50, "2023-01-05"),
    (5, "Sandy", "Cheeks", 17.25, "2023-01-06");

insert into employees (employee_id, first_name, last_name) values
(6, "Sheldon", "Plankton");




5. SELECT

select * from employees;

select first_name, last_name from employees;
select last_name, first_name from employees;

select * from employees where employee_id = 1;
select * from employees where employee_id = 3;
select * from employees where first_name = "Spongebob";
select * from employees where hourly_pay >= 15;
select * from employees where hire_date <= "2023-01-03";
select * from employees where employee_id != 1;
select * from employees where hire_date is null;
select * from employees where hire_date is not null;




6. UPDATE & DELETE

update employees set hourly_pay = 10.25 where employee_id = 6;

update employees set hourly_pay = 10.50, hire_date = "2023-01-07" where employee_id = 6;

update employees set hire_date = null where employee_id = 6;

delete from employees where employee_id = 6;




7. AUTOCOMMIT, COMMIT, ROLLBACK

SET AUTOCOMMIT = OFF;

commit; //savepoint

rollbback; //respawn :D




8. CURRENT DATE & CURRENT TIME

create table test(
    my_date date,
    my_time time,
    my_datetime datetime
);

select * from test;

insert into test values(current_date(), current_time(), now());

values(current_date() + 1, null, null);

values(current_date() - 1, null, null);

drop table test;




9. UNIQUE

create table products (
    product_id int,
    product_name varchar(25) unique,
    price decimal(4, 2)
);

alter table products add constraint unique(product_name);

insert into products values (100, "hamburger", 3.99),
			    (101, "fries", 1.89),
			    (102, "soda", 1.00),
			    (103, "ice cream", 1.49);

select * from products;




10. NOT NULL

create table products (
    product_id int,
    product_name varchar(25) not null,
    price decimal(4, 2)
);

alter table products modify price decimal(4, 2) not null;

insert into products values (104, "cookie", null);
Error Code: 1048. Column 'price' cannot be null




11. CHECK

CREATE TABLE employees (
     employee_id INT,
     first_name VARCHAR(50),
     last_name VARCHAR(50),
     hourly_pay DECIMAL(5, 2),
     hire_date DATE,
     constraint check_hourly_pay check (hourly_pay >= 10.00)
);

alter table employees add constraint check_hourly_pay check(hourly_pay >= 10.00);

insert into employees values (6, "Sheldon", "Plankton", 5.00, "2023-01-07"); //nefungje kvuli check

insert into employees values (6, "Sheldon", "Plankton", 10.00, "2023-01-07");

alter table employees drop check check_hourly_pay;




12. DEFAULT

insert into products values (104, "straw", 0.00), (105, "napkin", 0.00),
                            (106, "fork", 0.00), (107, "spoon", 0.00);

select * from products;

delete from products where product_id >= 104;

create table products (
    product_id int,
    product_name varchar(25),
    price decimal(4, 2) default 0v
);

alter table products alter price set default 0;

insert into products (product_id, product_name) values (104, "straw"), (105, "napkin"),
                                                       (106, "fork"), (107, "spoon");

CREATE TABLE transactions(
	transaction_id INT,
	amount DECIMAL(5, 2),
	transaction_date DATETIME DEFAULT NOW()
);




13. PRIMARY KEY - unique, not null

create table transactions(
	transaction_id int primary key,
    amount decimal(5, 2)
);

select * from transactions;

alter table transactions add constraint primary key(transaction_id);


insert into transactions values (1000, 4.99);
insert into transactions values (1001, 2.89);
insert into transactions values (1002, 3.38);




14. AUTO INCREMENT

create table transitions (
	transition_id int primary key auto_increment,
    amount decimal(5, 2)
);

select * from transitions;

insert into transitions (amount) values (4.99);




15. FOREIGN KEYS

CREATE TABLE customers (
	customer_id INT PRIMARY KEY AUTO_INCREMENT,
	first_name VARCHAR(50),
	last_name VARCHAR(50)
) ;

SELECT * FROM customers;

INSERT INTO customers (first_name, last_name)
VALUES ("Fred", "Fish"), ("Larry", "Lobster"), ("Bubble", "Bass");

CREATE TABLE transactions (
	transaction_id INT PRIMARY KEY AUTO_INCREMENT,
	amount DECIMAL(5, 2),
	customer_id INT,
	FOREIGN KEY(customer_id) REFERENCES customers (customer_id)
);

SELECT * FROM transactions;

alter table transactions drop foreign key transactions_ibfk_1;

alter table transactions add constraint fk_customer_id foreign key (customer_id) references customers(customer_id);

DELETE FROM customers WHERE customer_id = 3;




16. JOINS

INSERT INTO transactions (amount, customer_id) VALUES (1.00, NULL);

SELECT * FROM transactions;

SELECT * FROM transactions inner join customers on transactions.customer_id = customers.customer_id;

SELECT transaction_id, amount, first_name, last_name
FROM transactions inner join customers 
on transactions.customer_id = customers.customer_id;

SELECT *
FROM transactions left join customers 
on transactions.customer_id = customers.customer_id;

SELECT *
FROM transactions left join customers 
on transactions.customer_id = customers.customer_id;




17. FUNCTIONS

select count(amount) from transactions;

select count(amount) as count from transactions;

select max(amount) as maximum from transactions;

select min(amount) as minimum from transactions;

select avg(amount) as average from transactions;

select sum(amount) as sum from transactions;

select concat(first_name, last_name) as full_name from employees;

select concat(first_name, " ", last_name) as full_name from employees;




18. LOGICIAL OPERATORS - AND, OR, NOT

alter table employees add column job varchar(25) after hourly_pay;

select * from employees;

update employees set job = "manager" where employee_id = 1;
update employees set job = "cashier" where employee_id = 2;
update employees set job = "cook" where employee_id = 3;
update employees set job = "cook" where employee_id = 4;
update employees set job = "asst. manager" where employee_id = 5;
update employees set job = "janitor" where employee_id = 6;


select * from employees where hire_date < "2023-01-5" AND job = "cook";

select * from employees where job = "cashier" OR job = "cook";

select * from employees where NOT job = "manager";

select * from employees where NOT job = "manager" AND NOT job = "asst. manager";

select * from employees where hire_date BETWEEN "2023-01-04" AND "2023-01-07";

select * from employees where job IN ("cook", "cashier", "janitor");




19. WILD CARDS

-- wild card characters % _
-- used to substitute one or more characters in a string

SELECT * FROM employees;

SELECT * FROM employees where first_name like "s%";

SELECT * FROM employees where hire_date like "2023%";

SELECT * FROM employees where last_name like "%r";

SELECT * FROM employees where first_name like "sp%";

SELECT * FROM employees where job like "_ook";

SELECT * FROM employees where hire_date like "____-__-02";

SELECT * FROM employees where job like "_a%";




20. ORDER BY

SELECT * FROM employees order by last_name;
SELECT * FROM employees order by last_name desc;
SELECT * FROM employees order by last_name asc;

SELECT * FROM employees order by first_name;
SELECT * FROM employees order by first_name desc;

SELECT * FROM transactions order by amount, customer_id;
SELECT * FROM transactions order by amount ASC, customer_id DESC;
 



21. LIMIT

-- LIMIT clause is used to limit the number of records.
-- Useful if you're working with a lot of data.
-- Can be used to display a large data on pages (pagination).

SELECT * FROM customers limit 1;

SELECT * FROM customers order by last_name limit 4;

SELECT * FROM customers order by last_name desc limit 3;

SELECT * FROM customers limit 1, 1;

SELECT * FROM customers limit 2, 2;




22. UNIONS

-- UNION combines the results of two or more SELECT statements

select * from employees;
select * from customers;

select first_name, last_name from employees UNION select first_name, last_name from customers;

select first_name, last_name from employees UNION ALL select first_name, last_name from customers;




23. SELF JOIN

-- SELF JOIN
-- join another copy of a table to itself
-- used to compare rows of the same table
-- helps to display a heirarchy of data

SELECT * FROM customers;

alter table customers add referral_id int;

update customers set referral_id = 1 where customer_id = 2;
update customers set referral_id = 2 where customer_id = 3;
update customers set referral_id = 2 where customer_id = 4;

SELECT * FROM customers as a inner join customers as b on a.referral_id = b.customer_id;

select a.customer_id, a.first_name, a.last_name, b.first_name, b.last_name
from customers as a inner join customers as b on a.referral_id = b.customer_id;

select a.customer_id, a.first_name, a.last_name, concat(b.first_name, " ", b.last_name) as "refered_by"
from customers as a inner join customers as b on a.referral_id = b.customer_id;


alter table employees add supervisior_id int;

select * from employees;

update employees set supervisior_id = 1 where employee_id = 5;

select a.first_name, a.last_name, concat(b.first_name, " ", b.last_name) as "reports to" from 
employees as a inner join employees as b on a.supervisior_id = b.employee_id;

select a.first_name, a.last_name, concat(b.first_name, " ", b.last_name) as "reports to" from 
employees as a left join employees as b on a.supervisior_id = b.employee_id;




24. VIEWS

-- Viwes
-- a virtual table based on the resuld-set of an sql statement
-- the fileds in a view are fields from one or more real tables in database
-- They are not real tables but can be interacted with as if they are

create view employee_attendance as select first_name, last_name from employees;

select * from employee_attendance;

select * from employee_attendance order by last_name asc;

drop view employee_attendance;


select * from customers;

alter table customers add column email varchar(50);

update customers set email = "FFish@gmail.com" where customer_id = 1;
update customers set email = "LLobster@gmail.com" where customer_id = 2;
update customers set email = "BBass@gmail.com" where customer_id = 3;
update customers set email = "PPuff@gmail.com" where customer_id = 4;

select * from customers;

create view customer_emails as select email from customers;

select * from customer_emails;

insert into customers values (5, "Pearl", "Krabs", null, "PKrabs@gmail.com");

select * from customers; 

select * from customer_emails; 





25. INDEXES

-- INDEX (BTree data structure)
-- Indexes are used to find values within a specific column more quickly
-- MySQL normally seraches sequentially through a column
-- The longer the column, the more expensive the operation is
-- UPDATE takes more time, SELECT takes less time

show indexes from customers;

create index last_name_idx on customers(last_name);
show indexes from customers;

select * from customers where last_name = "Puff";

select * from customers where first_name = "Poppy";

create index last_name_first_name_idx on customers(last_name, first_name);
show indexes from customers;

alter table customers drop index last_name_idx;
show indexes from customers;

select * from customers where last_name = "Puff" and first_name = "Poppy";





26. SUBQUERIES

-- subquery
-- a query within another query
-- query(subqueryyy);

select avg(hourly_pay) from employees;

select first_name, last_name, hourly_pay, 
(select avg(hourly_pay) from employees) as avg_pay 
from employees;

select first_name, last_name, hourly_pay from employees where hourly_pay > 
(select avg(hourly_pay) from employees);


select customer_id from transactions where customer_id is not null;

select first_name, last_name from customers where customer_id in 
(select customer_id from transactions where customer_id is not null);




27. GROUP BY

-- GROUP BY = aggregate all rows by a specific column
--            often used with aggregate functions
--            ex. SUM(), MAX(), MIN(), AVG(), COUNT()

alter table transactions add column order_date varchar(50);

update transactions set order_date = "2023-01-01" where transaction_id = 1000;
update transactions set order_date = "2023-01-01" where transaction_id = 1001;
update transactions set order_date = "2023-01-02" where transaction_id = 1002;
update transactions set order_date = "2023-01-02" where transaction_id = 1003;
update transactions set order_date = "2023-01-03" where transaction_id = 1004;

insert into transactions values (1005, 2.49, 4, "2023-01-03"), (1006, 5.48, null, "2023-01-03");

select sum(amount), order_date from transactions group by order_date;
select max(amount), order_date from transactions group by order_date;
select min(amount), order_date from transactions group by order_date;
select avg(amount), order_date from transactions group by order_date;
select count(amount), order_date from transactions group by order_date;

select sum(amount), customer_id from transactions group by customer_id;

select count(amount), customer_id from transactions 
group by customer_id having count(amount) > 1;

select count(amount), customer_id from transactions 
group by customer_id having count(amount) > 1 
and customer_id is not null;




28. ROLL UP

-- rollup, extensionof the group by clause
-- produces another row and shows the grand total (super-aggregate value)

select sum(amount), order_date from transactions group by order_date;

select sum(amount), order_date from transactions group by order_date with rollup;

select count(transaction_id), order_date from transactions group by order_date with rollup;

select count(transaction_id) as "number of orders", customer_id 
from transactions group by customer_id with rollup; 


select * from employees;

select sum(hourly_pay), employee_id from employees group by employee_id with rollup;




29. ON DELETE

-- on delete set null = when a fk is deleted, replace fk with null
-- on delete cascade = when a fk is deleted, delete row

set foreign_key_checks = 0;
delete from customers where customer_id = 4;
select * from customers;

set foreign_key_checks = 1;

insert into customers values (4, "Poppy", "Puff", 2, "PPuff@gmail.com");
select * from customers;


CREATE TABLE transactions (
	transaction_id INT PRIMARY KEY AUTO_INCREMENT,
	amount DECIMAL(5, 2),
	customer_id INT,
	FOREIGN KEY(customer_id) REFERENCES customers (customer_id)
    on delete set null
);

alter table transactions drop foreign key fk_customer_id;

alter table transactions add constraint fk_customer_id foreign key(customer_id) 
references customers(customer_id) on delete set null;

delete from customers where customer_id = 4;
select * from transactions;


insert into customers values (4, "Poppy", "Puff", 2, "PPuff@gmail.com");
select * from customers;

alter table transactions drop foreign key fk_customer_id;

alter table transactions add constraint fk_transactions_id foreign key (customer_id) 
references customers (customer_id) on delete cascade;

update transactions set customer_id = 4 where transaction_id = 1005;

delete from customers where customer_id = 4;

select * from transactions;




30. STORED PROCEDURES

-- Stored procedure = is prepered sql code that you can save great if theres a query that you write often
-- reduces network traffic, increase performace, secure, admin can grant permission to use, increase memory usage of connection

select distinct first_name, last_name from transactions 
inner join customer on transactions.customer_id = customers.customer_id;



delimiter $$
create procedure get_customers() 
begin
	select * from customers;
end $$
delimiter ;

call get_customers();

drop procedure get_customers;



delimiter $$
create procedure find_customer(in id int)
begin
	SELECT * FROM customers where customer_id = id;
end $$
delimiter ;

call find_customer(1);
call find_customer(2);
call find_customer(3);
call find_customer(4);

drop procedure find_customer;



delimiter $$
create procedure find_customer(in f_name varchar(50), in l_name varchar(50))
begin
	select * from customers where first_name = f_name and last_name = l_name;
end $$
delimiter ;

call find_customer("Larry", "Lobster");




31. TRIGGERS

-- Trigger = when an event happens, do something ex.(insert, update, delete)
--           check data, handles errors, auditing tables

select * from employees;

alter table employees add column salary decimal(10, 2) after hourly_pay;

update employees set salary = hourly_pay * 2080;



create trigger before_hourly_pay_update before update on employees 
for each row set new.salary = (new.hourly_pay * 2080);

show triggers;

update employees set hourly_pay = 50 where employee_id = 1;

update employees set hourly_pay = hourly_pay + 1;


delete from employees where employee_id = 6;

create trigger before_hourly_pay_insert before insert on employees 
for each row set new.salary = (new.hourly_pay * 2080);

insert into employees values (6, "Sheldon", "Plankton", 10.00, null, "janitor", "2023-01-07", 5);


create table expenses(
	expense_id int primary key,
    expense_name varchar(50),
    expense_total decimal(10, 2)
);

select * from expenses;

insert into expenses values (1, "salaries", 0), (2, "supplies", 0), (3, "taxes", 0);

update expenses set expense_total = (select sum(salary) from employees) where expense_name = "salaries";

create trigger after_salary_delete after delete on employees 
for each row update expenses set expense_total = expense_total - old.salary
where expense_name = "salaries";

delete from employees where employee_id = 6;

select * from expenses;

create trigger after_salary_insert after insert on employees 
for each row update expenses set expense_total = expense_total + new.salary
where expense_name = "salaries";

insert into employees values (6, "Sheldon", "Plankton", 10.00, null, "janitor", "2023-01-07", 5);


create trigger after_salary_update after update on employees
for each row update expenses set expense_total = expense_total + (new.salary - old.salary)
where expense_name = "salaries";

update employees set hourly_pay = 100 where employee_id = 1;