SQL Tutorial 9 SubQuery

SubQuery in SQL

Follow on Facebook:

Subscribe to our other channel:
Telusko Hindi :

[abhishekchandrashekhar]

First Query:: Select count(*) from emp where deptno in (select deptno from dept where dname = sales ) and sal > 10000

Second Query :: select eno, ename from emp where designation = 'clerk' and deptno in (select deptno from dept where loc = delhi ) and sal > 5000

[shine]

It can be done using join as delineated below :

5) SELECT COUNT(*) FROM emp E, dept D WHERE E.deptno=D.deptno AND dname='sales' AND sal>10000;

6) SELECT eno, ename FROM emp E, dept D WHERE E.deptno=D.deptno AND designation='clerk' AND loc='delhi' AND sal>5000;

And I think it's much more easier than writing sub queries!

[vipularyaa]

select count(*) from emp where sal>10000
and deptno in (Select deptno from dept where dname ='sales');

select eno, ename from emp
where designation='clerk' and sal>5000 and
deptno in (Select deptno from dept where loc='delhi')

[naziaparveen]

select ename, eno
from emp
where designation='clerk '
AND sal >5000
AND depno IN (select deptno
from dept
where loc='delhi');

[rameshbodukani]

--5. how many employees of sales department earn more than 10000?

select count(*) as "Count" from emp
where sal > 10000 and deptno in (
select deptno from dept
where dname = 'sales'
);

--Result Set:
Count
1

[girivishnucharan]

5.select count(eno) from emp where sal>10000 and deptno in (select deptno from dept where dname='sales');


6.select eno, ename from emp where designation = 'clerk' and sal>5000 and deptno in (select deptno from dept where loc='delhi');

[biplabbaishnab]

5) Select count(*) from emp where sal>10000 and deptno in (select deptno from dept where dname=’sales’ );

6) Select eno, ename from emp where designation='clerk' and sal>5000 and deptno in (select deptno from dept where dname=’delhi’);

[akasksingh]

5)
select count(*) from emp where deptno in (select deptno from dept where dm=name = 'sales') and sal > 10000;

6)
select eno, enmae from emp where deptno in (select deptno from dept where loc = 'delhi') and sal > 5000;

[clearcoding]

SELECT ENO, ENAME
FROM EMP
WHERE DESIGNATION = 'CLERK' AND SAL >= 5000 AND DEPTNO IN ( SELECT DEPTNO
FROM DEPT
WHERE LOC = 'DELHI' );

RESULT = DDD

SEEMS CORRECT QUERY.

[rameshbodukani]

--6.Display employee no. and name of clerks in delhi earning more than 5000?
select eno, ename from emp
where sal > 5000 and deptno in (
select deptno from dept
where loc = 'delhi'
)
;

-- Results Set
eno ename
5 eee

[yeshwanthinturu]

if we even know that inner query returns 1 record can we use IN ??

[stankafan]

5th one : select count (*) from emp where sal >10000 and deptno in (select deptno from dept where dname =' sales');

[staronkar]

1 .select * from emp where sal > 10000 and deptno in (select deptno from dept where dname = 'sales');


2. select eno, ename from emp where sal > 5000 and deptno in (select deptno from dept where loc='delhi');

[BrewingHappinessrocks]

can u please tell the answer for the last two ques u give in the end as i want to confirm about it

[PiyushTambe]

Answer of the First Question:

SELECT DNAME, COUNT(*)
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE SAL>10000 AND DEPTNO = 10
);

[vinusagar]

FIRST QUERY ANSWER

SELECT COUNT(DEPTNO)
FROM EMP
WHERE SAL>10000 AND DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME='SALES');
OUTPUT:

COUNT(DEPTNO):-1(ONE)

[kushalkumar]

table-1-employe...!

create table employe(eno int, ename varchar(10), designation varchar(15), sal int, mgr int, deptno int);

insert into employe values (1, 'aaa', 'salesman', 7000, 2, 10);
insert into employee values (2, 'bbb', 'mgr', 17000, 3, 10);
insert into employee values (3, 'ccc', 'president', 40000, null, 30);
insert into employee values (4, 'ddd', 'clerk', 5000, 5, 20);
insert into employee values (5, 'eee', 'mgr', 20000, 3, 20);

table -2-dept...!

create table dept(deptno int, dname varchar(15), location varchar(15));

insert into dept values(10, sales, mumbai);
insert into dept values(20, hr, delhi);
insert into dept values(30, accounts, chennai);
insert into dept values(40, production, banglore);

Enjoy

[VishalDeepVerma]

5)
select count(*) from emp where sal>10000 and deptno=(select deptno from dept where dname='sales');
6)
select eno, ename from emp where designation='clerk' and sal>5000 and deptno in (select deptno from dept where loc='delhi');