Interview SQL Query On Joins | Campus Preparation | Solve Any SQL Query | JOINS

[@sohailsmd5871]

You are teaching joins facts that you never explained why are we using left and right joins great teaching👍

[@rathan7942]

😂😂

[@arpit9895]

sahii bolee bhaii yaar

[@arpit9895]

jo pta he vahi bta rhee

[@pratig4284]

You should explain why you choose left join

[@alivia_ansari]

Watched this video for revision before my interview. Luckily the interviewer asked many questions from SQL and I had to write down the queries. This really helped 😍❤️

[@vijaykumar.m3509]

very thank you madam i cant understand while my madam teaching in class now i can understand

[@BobbeeSingh]

Why a left join for 1st 3 queries and not an inner join? Explaining why left join and inner joins were used would have been helpful.

[@snehanair5436]

@bobbesingh, inner joins are used to fetch common in both tables, if questions didnt ask connon in both table, insted it just ask partial data ,2 or more data id, if here all data, than inner

[@090-jellabharath2]

Actually you can use.

[@GowthamS-vy9ts]

@@snehanair5436 if we use left join it also give the bal dept name also right??

[@madhukareppalapelly7253]

in query 2...we have to write dept_id in select statement when we write group by class using dept_id

[@rahulbagada7]

no bro it is not compulsory

[@MrTejasreddy]

u r explanation is really simple and clear..but if u provied data for practice for beginner then its very helpfull to them ...anyway gud explanation..keep it up...😊

[@aryanshinde1822]

Mam 3rd query we can use natural join through joining two tables

[@sandipwaghmare4477]

#Thank you shoo much the way of explaining topic is great within a half hour i have learn lot ☺️

[@aryanshinde1822]

Mam why should be use left join we can also use inner join throught join the 2 or more table

[@appianbpmdesigner1673]

INSTEAD OF USING JOINS, WE CAN ACHEIVE THIS BY SUB QUARIES NAA. SELECT DEPT_NAME FROM DEPT_TB WHERE DEPT_ID IN (SELECT DEPT_ID FROM EMPLOYEE GROUP BY DEPT_ID HAVING COUNT(DEPT_ID)>2)

[@shivankitss8396]

Ayy lol . We have almost similar profiles.

[@akshaykumarrai5055]

How I will come to know which should be the left column here ? I mean for example you fetched two columns from employees why not from department ?

[@beingrishi6450]

Can you share the table schema in description box

[@Priyanka-q3k3d]

In query 3 can we change the order of manager and employee like. Can we write manager first and employee second? Please answer.

[@manojkumarrajaka5204]

I subscribed ur channel without watching this video. Bcz I assume that it will be helpful for me. Tq. And joined in ur telegram group too

[@prachikushwaha108]

Same me too🤗😂

[@aastha4708]

I got all the queries but why and how only left join we have used why not other joins??

[@vasugupta4272]

why we use left join not normal join..any specific reason

[@user-ej5nt6lk3q]

thanks a lot saloni. it was really helpful appreciate the hard work keep going!!!!

[@neeshakumarisinha7596]

For the first question the Having clause seems incorrect. It should be HAVING COUNT(e.emp_id) > 2 to fetch the number of employees greater than 2 from each department.

[@vishalchauhan9832]

I believe dept_id is totally fine. You are checking count of dept_id . If occurence of any dept_id is more than 2 then we need to print that department name. So it is totally fine

[@manishascorner5443]

Yes dear you are totally correct

[@nayanmonibaruah6049]

This is what I wanted 😃

[@amalpeters]

1st query got error like not a group by expressions

[@maradadharmaraju2358]

Why u chose left join for 1st question why not inner join

[@crictune8820]

The column you taken after group by clause, it should be there in select statement too🙏, I have wasted hour in finding it out as I am beginnet

[@CodeEra2020]

It will work in mysql but if you are using oracle Or any other dbms then you have to write it with select statement also..

[@jiteshshewale764]

I think this query won't work as we are referening d.dept_name in select statement and grouping by e.dept_id which contradict sql syntax and rule, correct me if i m wrong

[@jiteshshewale764]

select d.dept_name, count(*) from dept d left join employee e on d.dept_id = e.dept_id group by d.dept_name having count(d.dept_name) > 2; this might be the correct query using joins

[@jiteshshewale764]

refering 1st query question

[@snehalgajbhiye1454]

Thank you so much saloni😊

[@rahuljain-zw8vx]

how to know when to use left join or inner join

[@YardAnalytics]

Say you have two tables t1 and t2. Use left join if you need records that are common in both t1 and t2 as well as those records that are unique to t1( considering t1 to be the left side table). And use inner join only if you want just the common records for both the tables t1 and t2. Hope this helps :)

[@rneditz3250]

Are this tables are given in our interview rounds while asking query to us????

[@ponkarthickkumarl1655]

Can i use full outer join for 1 and 2 questions

[@sudhanshupathak7844]

In the second one in place of e.dept_id it will come e._dept_name with group by

[@athul03]

d.dept_name . This is correct. Why the query in the screen works ???

[@PandeyJii9632]

Why to use left join for emp and manager to be from same city. We can simply use Join or Inner join. e.empid= m.magerid will be correct as we shud have emp ID in employee table

[@PandeyJii9632]

U have prepared tables to show no null value....huhh

[@talknow2859]

You're amazing 🎉!

[@gulistananwar2418]

Perfect and easy explanation, thankyou!

[@CodeEra2020]

Glad it was helpful!

[@swarnadipmukherjee2826]

No point of using left join we should use inner join to achieve actual result.

[@pranavshelar4690]

Also Make tricky video on subqueries❤

[@Priyanka-q3k3d]

I think there is no need of group by clause in first query

[@anjalisharma1351]

Didilnt explain left join.. 😢 . Why are we using left join and not right join?.. i got more confused after reading this

[@prasannas439]

How to know the here we use left join here we use right join to know that

[@amalpeters]

First query Answer is below select d.department_name,e.department_id from departments d left join employees e on d.department_id=e.department_id group by e.department_id,d.department_name having count(e.department_id)>2;

[@divyadubey6679]

You should y u chhosen left join And one more things ur question should b seen during query.

[@AmitKumar-tl2fj]

Tell us why you used left join

[@harshithapogala8845]

Here dept_id means number of employees present in the table is it friends?

[@manishascorner5443]

No dear

[@sravanthipatil763]

(Operand data type varchar is invalid for avg operator) mam can u help me out am getting this error while doing 2qsn query

[@sudhanshupathak7844]

It shows an error because it's incorrect when you are using a group with any column name you need to use the same with select statement . 2nd solution is wrong

[@pranishsonone7839]

Mam plese chake your Firstt query it is wrong

[@rajeshkumar2k567]

it would be more benificial , if the data in the tables were provided to practice!

[@radhasakhi4546]

Nice video Didi 😊😊

[@CodeEra2020]

Thank you

[@gashok5231]

​@@CodeEra2020 table having columns custid,orderid,menuitemid,transactid How to write a query most purchased item?

[@bhagirathmakwana9112]

thank u mam🙏

[@wikimarathi]

Thanks❤

[@sridharmurari3007]

does inner join give different result ?

[@ratneshwarsingh1872]

It gives records which are present in both the tables but left join unlike inner join gives all the records for left table(on condition) as well as records of th corresponding data which is present in the right table, id not present then it return Null corresponding to that particular data

[@MAHI_NEWS_]

I remember, Raye Raye Raye Raye Raye saloni. Ratirela sandu chusi jampu jilani 😊😊😊

[@hehehe4206]

seriously??

[@abhishekranjan3563]

instead of using left join in first query can we use INNER JOIN?

[@sanskargupta470]

yes you can

[@vedantkadam4221]

can i get a pdf for table creation steps pls

[@mrkaydee0]

Amazing explanation 😌

[@roshankamble3747]

you should group by d.dept_name not e.dept_id

[@gokulprasandh1001]

Not working it's shows invalid identifier

[@gguk3471]

managers also should be employee but in this example ithkink not, its too hard the i understand

[@NehaKumari-cx6un]

Nice

[@AdityaSairam11]

I am using postgresql, the query for 1st question is not running fine. Below is the query I used: select d.dept_name from public.department d left join public.emp e on d.dept_id = e.dept_id group by e.dept_id having count(cast(e.dept_id as bigint))>2; Error: SQL Error [42803]: ERROR: column "d.dept_name" must appear in the GROUP BY clause or be used in an aggregate function Position: 8 Error position: line: 79 pos: 7 Solution: select d.dept_name from public.department d left join public.emp e on d.dept_id = e.dept_id group by e.dept_id,d.dept_name having count(cast(e.dept_id as bigint))>2; I had to add d.dept_name in the group by clause. It is working fine now. I know that this video covers only sql join solutions, however alternate approach to solve question #1: select dept_name from department where dept_id in (select dept_id from emp group by dept_id having count(cast(dept_id as bigint))>2);

[@vijay_madane03]

Left join kyu use Kiya he samja nhi..

[@Entertainment_12331]

Plj share data in discription all video

[@nishchalbasyal]

I think your qn 3 answer is mistake either you have to use right join or inner join. SELECT d.dept_name FROM department d Left JOIN employee e ON d.dept_id = e.dept_id GROUP BY d.dept_name HAVING COUNT(e.emp_id) > 2; this will include all department with zero employes

[@cr_kumar1284]

Wildcard character explain plzz

[@sivaprasadreddy7879]

can you please do a videos for subqueries like this simple trick

[@Ashis_Kumar.]

Where is the script to make the table? Do you want me to manually create the tables? Kids these days 🙄

[@vamshitha449]

Write a query to get particular row from a table in SQL server

[@stallerina]

Select * from table name where id =4 l;

[@gollangianand6378]

Madam little bit confusion please do some queries or give any pdf for practice

[@vikaspatil5807]

Mam Datediff and DateFormat ke releted video banavo please mam

[@Explore_Sarkar_Lens]

@codeera The First & second answer is not working MS Sql Server. I am attaching the whole query , please correct me If I am wrong and attching the correct query too: create table Manager( Manager_id varchar(30) primary key, Manager_name varchar(30), City varchar(10) ) create table Department( Dept_id int primary key, Dept_name Varchar(10) ) create table employee( emp_id int primary key, emp_name varchar(30), Salary int, city varchar(30), Dept_id int foreign key references Department(Dept_id), Manager_id varchar(30) foreign key references Manager(Manager_id) ) insert into Manager values('M101','Ajay','Pune'); insert into Manager values('M102','Amit','Mumbai'); insert into Manager values('M103','Sandeep','Bangalore'); insert into Manager values('M104','Prabhakar','Pune'); insert into Manager values('M105','Joydeep','Indore'); insert into Manager values('M106','Shubham','Bangalore') select * from Manager insert into Department values(1,'Admin'); insert into Department values(2,'HR'); insert into Department values(3,'Developer'); insert into Department values(4,'Tester'); insert into Department values(5,'Accounts') select * from Department insert into employee values(1,'Karan',300000,'Mumbai',2,'M102'); insert into employee values(2,'Rohit',75000,'Bangalore',1,'M106'); insert into employee values(3,'Ankush',35000,'Ahemdabad',5,'M105'); insert into employee values(4,'Priyanshi',500000,'Kolakata',3,'M102'); insert into employee values(5,'Sanket',100000,'Pune',3,'M101'); insert into employee values(6,'Shruti',80000,'Indore',4,'M105'); insert into employee values(7,'Jayraj',75000,'Bhopal',2,'M103'); insert into employee values(8,'Dilip',66000,'Mumbai',3,'M102'); insert into employee values(9,'Geet',17000,'Mumbai',2,'M101') select * from employee --1st Question select d.Dept_name from Department d left join employee e on d.Dept_id=e.Dept_id group by e.Dept_id having count(e.Dept_id)>2 -- Correct Answer : --1st question select d.Dept_name from Department d join employee e on d.Dept_id=e.Dept_id group by d.Dept_name having count(*)>2 --2nd Question select d.Dept_name, avg(e.Salary) from Department d join employee e on d.Dept_id =e.Dept_id group by d.Dept_name having AVG(e.salary)>75000 order by avg(e.Salary) desc

[@akashsrivastava6311]

Hi, there are a few syntax errors try the following create table Manager( Manager_id varchar(30) primary key, Manager_name varchar(30), City varchar(10) ); create table Department( Dept_id int primary key, Dept_name Varchar(10) ); create table employee( emp_id int primary key, emp_name varchar(30), Salary int, city varchar(30), Dept_id int, foreign key (Dept_id) references Department(Dept_id), Manager_id varchar(30), foreign key (Manager_id) references Manager(Manager_id) ); insert into Manager values('M101','Ajay','Pune'),('M102','Amit','Mumbai'), ('M103','Sandeep','Bangalore'), ('M104','Prabhakar','Pune'),('M105','Joydeep','Indore'), ('M106','Shubham','Bangalore'); select * from Manager; insert into Department values(1,'Admin'),(2,'HR'),(3,'Developer'),(4,'Tester'),(5,'Accounts'); select * from Department; insert into employee values(1,'Karan',300000,'Mumbai',2,'M102'), (2,'Rohit',75000,'Bangalore',1,'M106'), (3,'Ankush',35000,'Ahemdabad',5,'M105'), (4,'Priyanshi',500000,'Kolakata',3,'M102'), (5,'Sanket',100000,'Pune',3,'M101'), (6,'Shruti',80000,'Indore',4,'M105'), (7,'Jayraj',75000,'Bhopal',2,'M103'), (8,'Dilip',66000,'Mumbai',3,'M102'), (9,'Geet',17000,'Mumbai',2,'M101'); -- q1 select count(employee.emp_id) t,department.Dept_name from department join employee on department.Dept_id=employee.Dept_id group by department.Dept_name having count(employee.emp_id)>2; -- q2 select avg(employee.salary), department.Dept_name from department join employee on department.Dept_id=employee.Dept_id group by department.Dept_id having avg(employee.Salary)>75000 order by department.Dept_name desc; -- q3 select manager.city, manager.Manager_name, employee.emp_name from manager join employee on manager.Manager_id=employee.Manager_id where manager.city=employee.city; -- q4 select employee.emp_name, employee.Salary, department.Dept_name,manager.Manager_name from employee join department on employee.Dept_id=department.Dept_id join manager on employee.Manager_id=manager.Manager_id where employee.salary between 35000 and 90000;

[@manishascorner5443]

The answer of 1st question is not correct the correct answer is as following Select dept_name,count(emp_id) From department Inner join employee On department.dept_id=employee . Dept_id Group by dept_name Having count(emp_id)>2; You can alise if you want 😊

[@MamtaVerma-tc5ip]

What is the name of your telegram account?

[@CodeEra2020]

Name is CodeEra or You will find the joining link in video Description box

[@monke890]

QUERY 1 IS WRONG

[@CodeEra2020]

It is working fine..

[@Priyanka-q3k3d]

In query 3 can we change the order of manager and employee like. Can we write manager first and employee second? Please answer.