SQL BASICS – ALEX YT
Select
* - everything
From
Distinct
!= - not equal to
; - end
And – both conditions
Or – either condition
Like , % - anything , _ - a specific no of characters
Group by – group rows together to give an aggregate function
Avg (column name)– average
Max(column name ) – gives out the max value
Min(column name ) – min value
Count ( column name) – gives the count of number of rows / values
Order By ( column name ) ASC/DESC – based on a particular column , arranges everything
Where – filtering out the rows
Having – aggregate function mei se hatana kuch
Limit – how many rows you want in your output
limit 2 , 1; - start at position 2 and take one row after it
aliasing AS – change column names
joins – 2 tables ka common column
INNER JOIN (table ka name) ON(common column)
LEFT JOIN – from wali table se sab kuch
RIGHT JOIN – 2nd wali table se sab kuch
Joining multiple tables
Union – used bw 2 select statements to join multiple rows
Union all or union distinct
String Functions
Length (column name) – no. of charecters
Upper (word/column name) – give the text in uppercase
Lower
Trim – take white spaces at the end / beg and delete it
L TRIM – taking out the lefthand spaces
R TRIM – all spaces on the right
Substring(column name , no for starting position , no of characters u want in output)
\
Replace
Replace(column name , what u want to replace , to be replaced with)
CONCAT
Case Statements
CASE
When then
End
Window Functions – basically group by ke aggregate function , bas unhe individually list karta hai
Over(Partition bY ----)
Create temp tables
CREATE TEMPORARY TABLE AS temp1
Whatever conditions u want to write down
Select * from temp1 ;
Stored Procedures
Create Procedure name ()
Whatever u want it to be ;
Call name of procedure(parameter ka position if u want ) ;
OR click on stored procedure on the left and bas phir write down your conditions
Stored procedure mei multiple conditions – ka example below
delimiter $$
create procedure all_sal (add a parameter if u want and data type eg - int)
begin
select *
from employee_salary
where salary >= 50000;
select *
from employee_salary
where salary >= 10000;
end $$
delimiter ;