# Create Tables
#To create table we have to define each column first with its datatype and constraints

#Data type 
Integer- TinyInt,MEdium int,Int,big int,Decimal
float - float, double
string type- varchar(20), Text, TinyText
#Here 20 means maqx number of characters in string

#Datetime -->Time(00:00:00),
Date(00-00-0000)
 Datetime(00-00-0000 00:00:00),Timedelta(00-00-0000 00:00:00)

#Datetime use 5 bytes to store provide wider range of time data to store
#Timedelta uses 4 bytes to store time 

#Other types-> set,enum,Blob etc.

#Decimal is used to store exact same precision in memory in case of numbers.

#Syntax to create a table is :
create table student(col1_name,col1_dtype,col2_name,col2_dtype,......);

#Creating table with constraint
create table student(col1_name col1_dtype col1_constraints,col2_name col2_dtype col2_constraints,......);



#Insert data into table(rows)
insert into student values (1,'MAninder',23.5),(2,'Shibham',20.5),(3,'Rahul',12.0);



#Inserting into paricular columns
insert into student(id,name) values(6,'Mohit');

#Note: The columns not included in above query will be replaced with null value



#Viewing table data
#Syntax
#Select command is used to show data on screen
#select column_name from table_name
select uid,name from student;

#TO show all table
select * from student;



#Types of commands
DML ->Data manipulation language:They are used to update the data of table.Ex: update,insert delete,etc.


DDL-> Data Definition Language :They used to update the structure of table.Ex: Create,drop,alter etc.


DCL ->Data Control Language : USe to change or apply coditions on flow of data .Ex: revoke,Grant etc.


TCL->Transaction control language: Use to control the saving ,rollbacking or restoring the backup.eX: rollback,commit etc.


DQL: Data query language : Select command:USe to show data acc to query




Dropping the table

drop table student;
-- This command will erase table from memory





#Delete command
delete from student where id>1;
-- delete is used when we want to delete data acc to condition
-- In this case table will stay in memory
-- Syntax delete from table_name where condition;


#To delete all rows simply dont in clude the where clause




#Truncate command
#It is used to delete whole rows from table

#Syntax:-  truncate table student;

#Difference in delete and truncate
#delete is a dml command and truncate is a ddl command
#We cannot rollback after truncate because log file is not mantained






Update command

#Update command is used to change the values of table data


#Syntax:
update student set marks=0 where id=2;

#where contain condition and set contain the column to be updated




Where clause -Explained

#Where clause is used to make condition
#We can use where cluase wirh select,update,delete
#Ex:Want to see data of roll no 5
select * from student where uid=5;


#Operators in condition
=
<
>
<=
>=
!=

and 
or
not

between
#Syntax
#SELECT * FROM actor where id between 2 and 10;


#like operator

-- like operator is used to check the patterns of the string
-- Wilcards-->Special symbols used for searching tha pattern

-- _ --> single character
-- % --> zero or more characters

select * from actor where first_name like 'P%E';




#Alter Table

-- renaming column
alter table student rename column id to uid;

-- Adding column
alter table student add column marks float;
update student set marks=0;

alter table student drop column marks;

select * from student;

-- Modify -->Changing data type,constraint of column
alter table student modify column uid float;

describe student;
-- It tells the information of table 



-- Datetime dtype
use 11_30;
create table orders_ ( id int,tm Datetime default current_timestamp);
-- current_timestamp represent current time on pc .To set time automatically
-- set current_timestamp as default value while creating column
insert into orders_(id) values(1);
select * from orders_;





-- Limit and offset
-- Limit operator is used to select only particular no of rows
-- offset is used to skip n number of rows from start

select * from sakila.actor limit 5,13;

-- here we get rows index from 5 upto 10 total rows

select * from sakila.actor limit 10;
-- Above will return top 10 rows


-- Offset
select * from sakila.actor limit 10 offset 3;
-- Here top 3 rows are skipped and we get data from 4 to 13




#Constraints

#Unique constraint--> Every value in column should be unique(Duplicacy #not allowed


#Syntax to create unique column
create table employee (id int unique,name varchar(20));


#Adding constraint using alter
alter table employee add constraint uni_name unique(name_);

#We can add constraint to multiple columns at one time by adding in bracket

#Dropping unique constraint
alter table employee drop index uni_name;
#Here uni_name is name given to constraint



#Default 
#Default constraint is used to set a default value when we are not adding value to a column

#Syntax while creating
create table employee (id int unique ,name_ varchar(20),hours int default 10);


#Adding default value with alter

alter table employee alter name_ set  default 'None';


#Dropping default constraint
alter table employee alter name_ drop default;
#HEre name_ is column name


#Check constraint

#It is used to disallow the values according to condition


#Syntax
create table employee(id int,hours int check(hours<=12));



#Adding check cionstraint through alter


alter table employee add constraint check(id<=100);

#Adding check over multiple columns

alter table employee add constraint cons_check check(id<=100 and hours<=12);


#Dropping check constraint
alter table employee drop check cons_check;
#Here cons_check is name of constraint




#Index constraint

#Index column is that which is used to retrieve the data from table fastly

#TO act column as index
create index idx_id on employee(id);

#HEre employee is table and id is column
#We can add multiple columns as single index


#To add unique index
create unique index idx_id on employee(id);


#To drop index
alter table employee drop index idx_id;




#Primary key
#It is a column which represents whole record
#Primary key cannot have null values and duplicate values
#Example: ROll number


#Syntax
create table employee(id int primary key,.....);


#Alter primary key

alter table employee add constraint prm_ primary key(id);

#We can represent multiple columns as one primary key


#Dropping primary key
alter table employee drop primary key;


#Foreign key

#Foreign key is a column which points to the column of another table which is a primary key of that table

#Foriegn key is that which is derived from another table

#Foreign key can only consists values present in parent table 



select name from student where id in(select id from winners where position in(1,2,3) limit 3)



#Refrential actions
#What to do if we want to delete parent row in foreign key constraint

#1- No action
#2- Put default value in child
#3- Make the value in child as null
#4- Cascade(Same action parent and same action in child)


#Syntax:
create table emp(id int primary key,name varchar(20),department varchar(20),
Foreign key(department) references Department(department)
on update cascade on delete cascade);



#Note: Foreign key value can be null

#TO use another actions replace cascade with their name






-- Relationships in database
#Three types:
# One to One
#One to Many
#Many to Many






#One to one-->When First table have only one record in second table and also
#second table have one unique record belongs to first table
#Example: Employee table and Employee_Info table



create table employee(id int primary key,name varchar(20),salary float,department_id int);

create table emp_info(adhar varchar(20) primary key,id int unique ,
adress varchar(20),
foreign key(id) references employee(id) on update cascade on delete cascade);


#One to many
#In this relationship one table have single record in second table
#but second table may have multiple records in first table refer to single record

#Example: Emplyeee(D id) and Department(Employees)  


create table Department(d_id int primary key,name varchar(20));
create table employee(id int primary key,name varchar(20),department_id int,
FOREIGN KEY(department_id) references Department(d_id));



#Many to many
#In this relationship first table have multiple records belong to same reference
# and also second table have multiple records in first table belong to single reference


create table employee(id int primary key,name varchar(20));
create table project(id int primary key,name varchar(20));

create table project_details(e_id int,p_id int,primary key(e_id,p_id),
FOREIGN KEY(e_id) references employee(id) on update cascade on delete cascade,
FOREIGN KEY(p_id) references project(id) on update cascade on delete cascade
)

 






#Autoincrementing
#Autoincreasing the value of a column in each insertion
#Rules:
#Autoincremented column must be unique
#There can be only one autoincrement column in table





#Advance query


#any/all
#any clause is used to to check if condition inside set is satisfied by atleast one row


#all clause is used to check wether all bool values of condition are True




-- Find the customer name who have order more than 200 once in the lifetime

select FirstName from customers where CustomerID= 
all(select CustomerID from orders where TotalAmount>200);

-- Finding customer who always order greater than 200
select FirstName from customers where CustomerID= 
all(select CustomerID from orders where TotalAmount>200);




SELECT * FROM inventory.orders;
-- Find the customer name who have order more than 200 once in the lifetime

select FirstName from customers where CustomerID= 
all(select CustomerID from orders where TotalAmount>200);

-- Finding customer who always order greater than 200
select FirstName from customers where CustomerID= 
all(select CustomerID from orders where TotalAmount>200);

-- Exists
-- Exists operator is used to check if internal query returns something or not 

select FirstName from Customers where not Exists(select CustomerID from Orders where Customers.CustomerId=CustomerID);


-- Here in nested query we are acessing same name columns at one time 
-- So for that we will use tablename.column_name concept

-- In Exists internal query is executed everytime row is acessed from outside query



use inventory;
-- aggregate functions are used to check the details of columns
#example: sum,count,min,max,avg
-- we use agg functions maximum time with group by
-- these functions are used with select command

select avg(TotalAmount) as Average from orders; 

select avg(TotalAmount),min(TotalAmount) from orders; 

-- group by 
-- group by clause is used when we want get information from table
-- according to each category

select CategoryID,avg(price) from products group by CategoryID;
-- In this command first data is grouped down after it
-- select command run


-- having clause
-- having keyword is used to apply conditions on table after grouping 
-- Ex:
-- Tell the detail of Categories with avg price greater than 5o
select CategoryID,avg(price) as average from products group by CategoryID 
having average>2;

-- In having we can only acess columns which are used in select or any agg function

#ORder of execution  Group by --> Select -->having
-- Tell the average price of categories whose sum(price)>50
select CategoryID,avg(price) from products group by CategoryID 
having sum(price)>200;

-- order by
-- order by is used to sort a table according to column or columns
-- Example:
select * from products order by Price;
-- To sort data in dictionary order we use two column
select * from products order by Price,StockQuantity;


-- order in ascending and descending order

-- In descending Syntax:
select * from products order by Price desc;

-- In ascending Syntax:
select * from products order by Price asc;

-- ORder by along group by

select CategoryID,avg(price) from products group by CategoryID order by CategoryID desc;

-- Find category whose total stock is maximum from all
select CategoryID,sum(StockQuantity) from products group by CategoryID 
order by sum(StockQuantity) desc limit 1;




#Making table from existing table

#Inserting only rows

insert into temp2 select * from location;
select * from temp2;



-- To make exact copy(along with their triggers,indices,constraints)
create table temp like location;




-- View is attached with the rows of tables


-- create or replace view

create or replace view customer_100 as 
select * from customers where CustomerID>5;
select * from customer_100;

drop view customer_100;




SET theory functions

Union
Intersect
Minus(Except)



-- Union
select * from A union select * from B;
-- Note: We only gets common row one time


-- Intersect
select * from A intersect select * from B;
-- We will get common rows


-- Except
select * from A except select * from B;


-- We will get those rows who are only present in table at left side
-- We will perform set-differnce operation: A-B




-- union all
select * from A union all select * from B;
-- Will return total rows of both tables irrespective 
-- of common one count



-- intersect all
insert into A values(2,3);
insert into B values(2,3);
select * from A intersect all select * from B;
 -- intersect all will return common items
 -- equal to common count

--Distinct keyword

select distinct A From A;





'''
Joins
'''

Joins are the methods to concat two tables column wise

#5 types of joins
#inner join
left join
right join
cross join
self join


#inner join

#inner join is used to join the rows of two tables when a particular condition is meet


#Syntax:
select * from student inner join
 course on student.course_id=course.id;


-- Here we get student and his course detail
-- Steps:
1- First caartesian product is formed
2- Filter out those who pass condition



#Left join 

In left join the rows who pass through condition are formed as well as the rows left in left side table is also included

#Syntax:
select * from student left join
 course on student.course_id=course.id;			

#Left join or right join is done when we have to keep all rows of left or right table



#Right join



#Cross join
#In cross join every row is included irrespective of condition

#IF we Provide cosnition in cross join it 
#behaves like inner

#Syntax

 select * from student cross join
 course;






-- Cross Join 

 select * from student cross join
 course;
 
 -- Self join
 select uid ,cost from student,course;
 
 
 
 
 create table current_student(uid int,
 name varchar(20));
 
 create table all_student(uid int, 
 family int );
 
 
 insert into current_student values(1,'MAni'),
 (2,'Vamika');
 insert into all_student values(1,7),
 (2,13),(3,6);


select sum(a.family)  from current_student as c 
inner join all_student as a on c.uid=a.uid;


 
'''
Stored PRocedure
'''
Stored procedure is  way to store multiple commands under one block to solve the burden of executing them again and again

Data from commands may be passed dynamically


#Syntax:
create procedure procedure_name(variables....) 
begin
commands......
end


To change the delimiter for some time we use 

DELIMITER //    -->This will change ; to // as delimiter



#Types of variables in stored procedure:
    #Input       In var int
    #Output      out var int 
    #Input-output    inout var int

#HEre var is variable name

#Input variable--> Use to take inout while calling the stored procedure
#We have to define input variable in the brackets of stored procedure
 
#We can pass any number of variables to stored procedure

  DELIMITER  //
 create PROCEDURE second_prc(in var int)
 begin
 
 insert into attendance(id,attendance) values
 (var,'Y');
  
 end //
 DELIMITER  ;
 
 call second_prc(2);-- PAssing data to input variable
 





#Output variable
#This variable is used to return something from stored procedure like mean,sum etc.

#We also have to pass a variable while calling as well as we also have to define that variable in the parameters of procedure

#Syntax:
-- Task -->Find total attendance of a id
  DELIMITER  //
 create procedure third_prc(in var int,out cnt int)
 begin
 select count(attendance) into cnt from attendance where id=var 
 and attendance='Y';
 
 end //
DELIMITER  ;

 

#Note: To store a value into a variable we use:
select count(att) into variable from table_name;



#Calling procedure
call third_prc(2,@total);
 -- @ is used to define global variable
 
 -- here total variable is connected to cnt 
 -- variable inside procedure
 
 select @total;


inout variable
#it is used for both taking input as well as storing output



 
   DELIMITER  //
 create procedure fourth_prc(inout var int)
 begin
 
 select Count(attendance) from attendance where id=var;
 set var=3;
end //
delimiter ;

select @temp:=2;
call fourth_prc(@temp);
select @temp;

Triggers
-- Trigger is a piece of code(commands) 
-- that automatically run after the speecified
-- operation(Ex: update delete insert

-- Triggers are used to:
-- Ensure security of data
-- Restrict illegal data
-- To create backup


-- Triggers are of two types:
-- Row based --> Execute the code for each row insert,delete or update
-- Statement based--> Execute code for whole statement irrespective of count

-- Situations for triggering
-- after insert
-- after delete
-- after update
-- before delete
-- before insert
-- before update


#Syntax:
delimiter //
create trigger trigger_name
after|before update|delete|insert
on table_name
for each row
begin
commands.....
end //
delimiter ;





#if else
if condition
statement
else if condition 
statement
else
statement

end if;