SQL-Basics 9(Joins-Unions-CTE)

                                                           Joins &Unions&CTE 

create  database operations;

use operations;

select database();

JOINS

---->The primary key and foreign key are used to build the relationship b/w the tables.

----->But in this, we are joining the two tables to fetch the specific results(records).

create table if not exists course(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50));

create table if not exists student(

student_id int,

student_name varchar(50),

students_mobile int,

student_course_enroll varchar(50),

student_course_id int);

insert into course values(102,'fsds','FULL STACK DATA SCIENCE','DS'),

(103,'aipos','airtificial intillgence opearions','AIOPS'),

(104,'bigdata','FULL STACK BIG DATA','BD'),

(105,'mern','web dev','MERN'),

(111,'blockchain','full stack blockchain','BC'),

(112,'java','full stack java','JAVA'),

(109,'testing','full testing','testing'),

(106,'cyber security','FULL STACK cyber security','cyber security'),

(110,'C','C lanaguage','C'),

(113,'c++','c++ language','C++'),

(115,'python','python language','PYTHON')

select * from course;

insert into student values(301,'galla',12345,'yes',101),

(302,'galla',12345,'yes',102),

(303,'galla',12345,'yes',105),

(301,'galla',12345,'yes',106),

(302,'galla',12345,'yes',101),

(303,'galla',12345,'yes',103),

(304,'galla',12345,'yes',105),

(305,'galla',12345,'yes',107),

(306,'galla',12345,'yes',108),

(307,'galla',12345,'yes',109)

select * from course;

select * from student;

Q)I want to know who has enrolled in some particular course? from the student table? for example for courses 105,106,104?

Q)How we will be able to find out the above kind of relation?

---->For the above query we can get it with the help of primary key mapping, but apart from that, we will be able do another kind of mapping as well and another kind of relationship.

---->We will be able to find out and that is something, We will be able to match each and every record parallelly with the help of join operation.

Inner Join:-(Common Rows)

----->It will try to find out common records which are available in b/w two of this table and based on that it is going to give us the final result (intersection of two data sets).

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c inner join student s on c.course_id=s.student_course_id;

Left Join:-(Many to One)

----->In the case of the left join, it will try to fetch everything from the left table.

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c left join student s on c.course_id=s.student_course_id;

Q)Can you please find out all the courses which were not enrolled by anyone?

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c left join student s on c.course_id=s.student_course_id where s.student_id is null;

 Right Join:-(One to Many)

---->It will try to pull out everything from the right table.

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c right join student s on c.course_id=s.student_course_id;

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c right join student s on c.course_id=s.student_course_id where c.course_id is null;

Cross Join:-

------>It will behave the same as the inner join whenever we provide a condition. (It will try to do the permutation and combination on both tables on the left side and right side what are the possibilities)

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c cross join student s on c.course_id=s.student_course_id;

----->For example I have removed the condition here it will give the result of each and everything on 

the right side table and left side table.

select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c cross join student s;

Note:-

Outer Join:-(Many to Many)

----->Full outer join is not there in MySQL.

------> But it is possible to do it by appending two queries(syntax will try to use one left to join one right join and union all).

---->Create two queries a query for left join, one query for right join then union all and  the final result will give the full outer join.     

Q)When do we have to do cross join operation?

----->Whenever we have to do complete data mapping from one table to another table then we have to perform a cross-join operation like the above. 

----->The above query result all are the possible combinations, maybe I can take the comparison with other tables where this was the possible combination may be some of the combinations doesn't even exist.

----->So I will try to filter out those conditions then I try to run a marketing campaign only for the specific courses to the specific students.

------>But at end of the day, this cross-join simply means it is trying to fetch the data from both tables. 

Note:-

----->If both tables contain the same column then we can use the 'USING' like below take the reference as the below link from StackOverflow.

https://stackoverflow.com/questions/13750152/using-keyword-vs-on-clause-mysql

Note:-

----->The main thing for joins is to try to combine the two tables (or) more than two table

----->With that combining output, we can combine another table also.

INDEXING:-

---->Indexing is used for big tables wherever we have a big amount of dataset, So maybe we can try to create the indexes, and then we can try to store those data inside a particular table.  

----->At any point in time, it is highly optimized the query in terms of search operations.

----->like insert operation, update, delete, or heavy operations. 

------>Whenever we are performing an insertion operation it will try to reach the trees then it will do insert.

------->In case of an update also it will try to reach the trees then it will do updation.

-------->In case of delete also it will try to reach the trees it will delete after delete it will try to shift

(means my data structure is changing).

------>Update, delete, and insert are not frequent operations.

show index from course;

create table if not exists course1(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50),

index (course_id))

show index from course1;

insert into course1 values(102,'fsds','FULL STACK DATA SCIENCE','DS'),

(103,'aipos','airtificial intillgence opearions','AIOPS'),

(104,'bigdata','FULL STACK BIG DATA','BD'),

(105,'mern','web dev','MERN'),

(111,'blockchain','full stack blockchain','BC'),

(112,'java','full stack java','JAVA'),

(109,'testing','full testing','testing'),

(106,'cyber security','FULL STACK cyber security','cyber security'),

(110,'C','C lanaguage','C'),

(113,'c++','c++ language','C++'),

(115,'python','python language','PYTHON')

BTree(Binary Tree):-

------->Basically, Internally the data structure which has been used to store all of this Course_id is a binary tree.

------->So, Internally what the system will do is that  it is trying to create the BTree . 

------>BTree(Binary tree) always follows the specification that all the data which is having lesser value comes on the left-hand side all the data which have higher values always try to form a tree (or) branch on the right side.

Q)When to use partition VS Indexing?

Partition:-

------->Partition is nothing but creating a big table and then creating a sub-table (or) subfile for the big table So, Internally, It tries to store the information in a small bucket, Again depending on the partition condition we are trying to give based on that it will try to perform those operations.

------>So, it is creating the buckets again there is a possibility that inside a bucket (or) some of the bucket I have a huge amount of data, In some of the bucket I have less amount of data.

------>Basically there is a possibility my buckets will be skewed(suddenly change direction or position)

----->Even my buckets are there, Till some point partition is going to give you some sort of advantages.

Indexing

------>But let's suppose  I am working on a pure search operation, I am trying to build a system where my priority is to perform a search as faster as possible in that case I will end up using an indexing operation.

Q)Can we use indexing and partition in one query itself?

----->Yes we can use partition is nothing but the creation and separation of the multiple files inside that file I can create the indexes there is no issue at all.

General Information:-

----->We have to find in the market the Latest tool we are using Elastic search or solar, So which can be used to perform our unstructured data search operation faster right, Inside if we see their architecture if will see their system design, so they have heavily used indexing operations on different levels.

---->Bcz Search is one of the heavy operations.

------------------How to Create an Index on multiple Columns--------------

create table if not exists course2(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50),

index (course_id,course_name));

show index from course2;

create table if not exists course3(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50),

index (course_desc,course_name))

show index from course3;

create table if not exists course4(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50),

index (course_desc,course_name,course_id))

show index from course4;

insert into course4 values(102,'fsds','FULL STACK DATA SCIENCE','DS'),

(103,'aipos','airtificial intillgence opearions','AIOPS'),

(104,'bigdata','FULL STACK BIG DATA','BD'),

(105,'mern','web dev','MERN'),

(111,'blockchain','full stack blockchain','BC'),

(112,'java','full stack java','JAVA'),

(109,'testing','full testing','testing'),

(106,'cyber security','FULL STACK cyber security','cyber security'),

(110,'C','C lanaguage','C'),

(113,'c++','c++ language','C++'),

(115,'python','python language','PYTHON')

show index from course4;

explain analyze select * from course4 where course_id=106 or course_name='fsds';

explain select * from course4 where course_id=106;

analyze table course4;

describe course4;

Unique Index

create table if not exists course5(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50),

unique index (course_desc))

show index from course5;

                            ------ Diff B/w Index and Unique Index -------

---->In both cases, it will try to create the binary tree, In the case of unique indexes, it will just try to take the distinct record (or) distinct combination from one of the columns, and based on that it will try to create Btree.

----->In the case of the non-unique index, it will try to consider duplicate as well as Union(It will try to remove the duplicates).

---->It will try to join the dataset vertically with the combination of two tables.

select * from course;

select * from student;

create table if not exists course(

course_id int,

course_name varchar(50),

course_desc varchar(50),

course_tag varchar(50));

create table if not exists student(

student_id int,

student_name varchar(50),

students_mobile int,

student_course_enroll varchar(50),

student_course_id int);

Union, Union all:-

------->Union will give only unique values.

Ex:-1

select course_id,course_name from course 

union

select student_id,student_name from student;

Ex:-2

select course_id,course_name,course_desc from course 

union

select student_id,student_name from student;

Error Code: 1222. The used SELECT statements have a different number of columns

Reason:-

------> Columns list one of the outcomes and the columns list of another outcome must be the same. 

Ex:-3

select course_desc,course_name from course

union

select student_id,student_name from student;

Q)Can join and union can be used together?

---->yes we can.

Ex:-4

(select course_desc,course_name from course

union

select student_id,student_name from student)

union

select course_id,course_name from course;

Union all(it will not remove the duplicates)

Ex:-1

select course_id,course_name from course 

union all

select student_id,student_name from student

Ex:-2

select 1 as col1,2 as col2

union all

select 3,4;

CTE(comman table expression):-

---->I have table1 and I have table2 so whatever data which am try to fetch from table1 and I will

try to give an alias for that and then based on that I will able to search for something else from the

same table.

Ex:-1

with sample_students as

(select * from course where course_id in (101,102,106))

select * from sample_students where course_tag='DS'

Note:-

---->Wherever we have very very complex queries we always try to write CTE.

----->So that we will get clarity.

Ex:-2

with outcome_cross as(select c.course_id,c.course_name,c.course_desc,s.student_id,s.student_name ,s.student_course_id from course c cross join student s)select course_id,course_name,student_id from outcome_cross where student_id=301;

Ex:-3

with ctetest as (select 1 as col1,2 as col2

union all

select 3,4)select col1 from ctetest

Concatenation Operation:-

select concat("fs","fsdf");

Q)Can we write the sql statements inside the function?

---->Yes, it is possible.

Linked Q)Then what is the main difference between a procedure and a main function?

----->Function never takes the parameter, Procedure takes the parameter this is one of the differences.

---->We will never be able to pass the parameters for the functions.

----->Function can be used as the constant like an entity whereas procedure can be used as

 a dynamic one.

------>To call the procedure we are using the call clause where we are using the function we 

are directly using.