SQL Tutorial 12 Advanced Subqueries returning multiple columns

SQL Tutorial on Advanced Subqueries returning multiple columns

Check out our courses:

Coupon: TELUSKO10 (10% Discount)

Coupon: TELUSKO10 (10% Discount)

Coupon: TELUSKO20 (20% Discount)

For More Queries about Course, WhatsApp or Call: [phone]

[tiwarivikram]

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...

[abhiny]

Question number 4 answer for mysql:
select designation, count(designation)
from emp
group by designation
having count(designation)=
(select max(cnt)
from (select count(designation) as cnt
from emp
group by designation
)as a );

[sannidhirao]

Thanks for such helpful content.. Great job.. Where can I find more questions like these?

[BeccaL]

I wish I saw this earlier! Had similar sql tests

[prashantmhatre]

Nice explanation. Just need to mentioned one thing here. If you have used the analytical function then all queries will be much easy

[badrinathansathiyamurthy]

4th Question in Teradata ::


SELECT DESIGNATION, COUNT(*) FROM XDW_STG.EMP_B
GROUP BY DESIGNATION
HAVING COUNT(*) = (
SELECT MAX(CNT) FROM (
SELECT COUNT(*) AS CNT FROM XDW_STG.EMP_B GROUP BY DESIGNATION) A
)


as nested aggregation won't work in TD

[soumyajitbanerjee]

For getting Maximum count :we can use:
select designation, max(count(*)) from emp group by designation

[Ramesh.bodukani]

--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
)
)
;

[gattu]

Hello,

I have this below query, but im getting this error (An expression of non-boolean type specified in a context where a condition is expected, near ', '.
)

select empno, ename, job, mgr, sal, hiredate, com, deptno
from emp a
where (deptno, sal) in
(select deptno, max(sal) sal from emp group by deptno)

any thing wrong with this query?

[girivishnucharan]

I tried the same query for Question 4 in MYSQL Workbench, but I am getting error. Is the syntax different for it?
Its working fine in Oracle

[abhikchakraborty]

For 1st question its simple one just use order by salary and limit to 1

[lokeshtrivedi]

😅Observed one funny thing, for question no. 4 at 11:40, the subquery itself gives us the answer.

[MegaMoses]

Is there online sql sandbox with advanced questions like these for sql interview practice?

[RonaldJon]

Could you have also done it this way? SELECT TOP 1 eno, ename FROM emp WHERE designation = 'manager' ORDER BY sal DESC.

[ga]

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

[Ganeshay-]

If destination ...name don't have know then how to direct take sir we don't have know about row column name soo what should find out direct highest salary sir

[thevineetmishra]

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)//

[nitnand]

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 ....

[sreekuttybabu]

how to show column value only one time if it is repeated and blank until different value comes in sql in php?

[sigmarulesog]

nice tutorial! what if i wanna get 10th max salary? cant make 10 loops right?