HomeОбразованиеRelated VideosMore From: Telusko

SQL Tutorial 12 Advanced Subqueries returning multiple columns

245 ratings | 47141 views
SQL Tutorial on Advanced Subqueries returning multiple columns Check out our website: http://www.telusko.com Follow Telusko on Twitter: https://twitter.com/navinreddy20 Follow on Facebook: Telusko : https://www.facebook.com/teluskolearnings Navin Reddy : https://www.facebook.com/navintelusko Follow Navin Reddy on Instagram: https://www.instagram.com/navinreddy20 Subscribe to our other channel: Navin Reddy : https://www.youtube.com/channel/UCxmkk8bMSOF-UBF43z-pdGQ?sub_confirmation=1 Telusko Hindi : https://www.youtube.com/channel/UCitzw4ROeTVGRRLnCPws-cw?sub_confirmation=1
Html code for embedding videos on your blog
Text Comments (25)
Vikram Tiwari (2 days ago)
Thank you for the logical tutorial. I am learning SQL right now and this kind of logical scenarios is something I am struggling at.. I have a quick question. In your last question (Who gets the second highest salary) What will happen if you were to right it like this way - Select Name, Max(Sal) from Emp where Sal < (Select max(Sal) from Emp) group by Name; My rationale for this is - This way you would not have to have the outermost query... Is this assumption correct? If not, could you please explain why not? Thank you for sharing the valuable knowledge...
Gregory M (24 days ago)
Precisely I am asking about this: (select max(count(*)) from emp group by designation) How can this work in Microsoft SQL Management Studio...
Gregory M (24 days ago)
just to say, 4) is not working in Microsoft SQL...so if someone know how to make it work can help much because I give up :)
prashant mhatre (2 months ago)
Nice explanation. Just need to mentioned one thing here. If you have used the analytical function then all queries will be much easy
nit nand (3 months ago)
For getting Maximum count select designation ,count(*) from emp group by designation order by count(*) desc limit 1; I think this query execute faster that having sub query please give me the suggestion ....
Đurđina Milović (17 days ago)
Great!
Bobby D (2 months ago)
Awesome
Shahir Ansari (3 months ago)
nice tutorial! what if i wanna get 10th max salary? cant make 10 loops right?
subash panday (4 months ago)
tq
Antony Coulson (4 months ago)
Very helpful video. Thank you.
spicytuna08 (5 months ago)
1st Q, would this work? select eno, ename, max(salary) from emp group by designation having designation = 'manager'
Leo Torres (5 months ago)
all the car honking in the background haha india
Ramesh Bodukani (5 months ago)
--SQL Tutorial 11 Subqueries with aggregate functions database northwind; Create table emp ( eno integer, ename varchar(15), designation varchar(15), sal integer, mgr integer, deptno integer ); Create table dept ( deptno integer, dname varchar(15), loc varchar(15) ); Insert into emp values(1,'aaa','salesman',7000,2,10); Insert into emp values(2,'bbb','manager',17000,3,10); Insert into emp values(3,'ccc','president',40000,,30); Insert into emp values(4,'ddd','clerk',5000,5,20); Insert into emp values(5,'eee','manager',20000,3,20); Insert into dept values(10,"sales","mumbai"); Insert into dept values(20,"hr","pune"); Insert into dept values(30,"accounts","chennai"); Insert into dept values(40,"production","bangalore"); --1.Amongst the managers who gets the highest salary select ename from emp where designation = 'manager' and sal = ( select max(sal) from emp where designation = 'manager' ); --Alternate query for Query 1 i.e. Amongst the managers who gets the highest salary select ename,sal from emp where (designation,sal) in ( select designation,max(sal) from emp where designation ='manager' group by designation ); --2. Get the details of clerks earning 5000 select * from emp where designation = 'clerk' and sal = 5000; --Alternate query select * from emp where (designation,sal) in (select designation,sal from emp where designation = 'clerk' and sal = 5000); --3. which designation has atleast 2 employees select designation, count(*) from emp group by designation having count(*) >= 2 ; --4. which designation has most number of employees select designation, count(*) from emp group by designation having count(*) = ( select max(count(*)) from emp group by designation ) ; --5. what is the second highest salary select max(sal) from emp where sal < ( select max(sal) from emp ) ; --6. who gets second highest salary select eno,ename from emp where sal = ( select max(sal) from emp where sal < ( select max(sal) from emp ) ) ;
R J (6 months ago)
Could you have also done it this way? SELECT TOP 1 eno, ename FROM emp WHERE designation = 'manager' ORDER BY sal DESC.
vineet mishra (8 months ago)
if double aggregate function is not working then we could use this query //select designation, max (des) from (select designation, count(designation) as des from emp group by designation)//
Rupom Mahmud (8 months ago)
can barely see the fonts...
Gamal Abdall (10 months ago)
Sir, Can I ask you what database you are using ? because no database will accept a where clause like the one you are using Select ... Where (tablename.columnName, tableName.columnName) SQL Server produces an error when the where clause looks like that
Kartik Chauhan (9 months ago)
He's using Oracle. You can see that in top-left corner.
Becca L (11 months ago)
I wish I saw this earlier! Had similar sql tests
abhi ganta (1 year ago)
max(count(*)) not working in MySQL
Shubham Sahu (7 months ago)
Go for this select count(*) from emp group by designation order by count(*) desc limit 1; it will working
Tim Dahyun (1 year ago)
why not go: select name, number from whatever where designation is manager order by salary desc limit 1 for the first one? much easier. although doesn't teach subqueries!
harsh verma (1 year ago)
Hi I used the same query without group by and it fetches me the correct result and it does not throw an error : select emp_id, first_name,last_name from teachers where (department,salary) in (select department , max(salary) from teachers where department ='physics'); can you please tell why so?
ashish kumar (2 years ago)
teaches a lot .... thanx
Saurabh Saneja (2 years ago)
Nice video !!!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.