473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,327 developers and data experts.

Using Subqueries - 2

debasisdas
8,127 Expert 4TB
Using Co-related sub query
========================
While a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row.

Sub query can take value from outer query.

Ex#1
---------
Delete duplicate record from table
Expand|Select|Wrap|Line Numbers
  1. Delete from emp e  where rowid>(select min(rowid) from emp where e.empno=empno)
  2.  
OR
Expand|Select|Wrap|Line Numbers
  1. Delete from emp 
  2. where rowid not in(select max(rowid) from emp group  by empno)
  3.  
Ex#2
------------
Display first position of employee
Expand|Select|Wrap|Line Numbers
  1. select * from emp e where 0=(select count(distinct(sal)) from emp
  2. where sal>e.sal) order by sal desc
  3.  
Ex#3
------------
Display details of employee whose sal greater than avg sal of it own department
Expand|Select|Wrap|Line Numbers
  1. Select * from emp e
  2. Where sal>(select avg(sal) from emp where deptno=e.deptno)
  3.  
Multiple - Row Subqueries
=======================
Inner query returns more than one row
Use multiple - row comparison operators
IN ----Equal to any member in the list
ANY--Compare value to each value returned by the sub query.
ALL---Compare value to every value returned by the sub query.
<ANY means less than the maximum
>ANY means more than the minimum
=ANY is equivalent to IN
>ALL means more than the maximum
<ALL means less than minimum.

The NOT operator can be used with IN, ANY, and ALL operators

Using IN
==========
Example #1
-------------------
Find the employees who earn the same salary as the minimum salary for departments.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM emp
  2. WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno);
  3.  
  4. is  same as
  5.  
  6. SELECT * FROM emp WHERE sal IN (800, 950, 1300);
  7.  
Using ANY
=============
The ANY operator (and its synonym SOME operator) compares a value to each value returned by a sub query.

Example #2
--------------------------
Display employees whose salary is less than any clerk and who are not clerks.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM emp  WHERE sal < ANY ( SELECT sal FROM  emp WHERE job= 'CLERK')  AND job <> 'CLERK';
  2.  
Using ALL
============
The ALL operator compares a value to every value returned by a subquery.

Example #3
--------------------------
Display details of employee whose sal greater than avg sal of all department
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM emp WHERE sal > ALL(SELECT avg(sal)  FROM emp GROUP BY deptno);
  2.  
The example above displays employees whose salary is greater than the average salaries of all the department. The highest average salary of a department is 2916.66 , so the query returns those employees whose salary is greater than 2916.66

Note :--Please Try on Scott Schema

Using In line sub query(inline views)
==============================
Ex#1
----------
Display Odd number records.
Expand|Select|Wrap|Line Numbers
  1. SELECT  * FROM  ( SELECT rownum rn, empno, ename FROM emp)  temp
  2.  WHERE  MOD(temp.rn,2) = 1
  3.  
Ex#2
----------
Display manager and its salary
Expand|Select|Wrap|Line Numbers
  1. SELECT A.MGR, B.SAL FROM (SELECT DISTINCT  MGR FROM EMP
  2.  WHERE MGR IS NOT NULL) A   LEFT OUTER JOIN 
  3.  (SELECT EMPNO, SAL FROM EMP) B  ON  A.MGR=B.EMPNO
  4.  
Ex#3
----------
Top three employee who earn hightest salary.
Expand|Select|Wrap|Line Numbers
  1. select * from (select  * from emp order by sal where rownum<4
  2.  
Ex#4
----------
Query for listing Deptno, ename, sal, SUM(sal in that dept) :
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT deptno, ename, sal, (SELECT SUM(sal) FROM emp WHERE a.deptno =deptno) FROM emp a  ORDER BY deptno
  3.  
Ex#5
----------
Display manager no and name who has more employee

Expand|Select|Wrap|Line Numbers
  1. select empno,ename from emp where empno=(select mgr from 
  2. (select mgr,count(empno) from emp  where mgr is not null group by mgr
  3. order by count(empno) desc) where rownum<2)
  4.  
Sep 17 '07 #1
1 11405
Hi Debasis,

Can you please tell me how to find the employees with greater than and less than the average salaries for their concerned departments? Thanks for your time.
Feb 23 '14 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: pete | last post by:
Been banging my head against the wall with subqueries. Even simple stuff like this fails: SELECT CompanyName FROM tblcompanies WHERE CompanyName IN (SELECT HostName FROM tblhosts) Am I...
5
by: Nick | last post by:
Im moving a development app (MySQL 5.0) to a different server which runs MySQL 4.0.20-standard. I am getting errors on queries that have subqueries such as... SELECT id FROM table1 WHERE id IN...
2
by: Kevin | last post by:
While converting SQL statements for a database change, I discovered a big performance hit in MYSQL with subqueries vices Sybase. I'm hoping that someone might be able to help me understand why? ...
11
by: SQL_developer | last post by:
Hello, I am trying to update a column in a table with an 8 digit string. The 8 digit number is obtained from another column which is a comments field. e.g. Comments Field :
2
by: psuaudi | last post by:
I have a main query that I would like to call two different subqueries. In MS Access, I usually just save the two subqueries as separate queries which are then called by a third separate and main...
4
by: muzu1232004 | last post by:
Can anyone explain me when we use correlated subqueries rather than nested subqueries. Do all the correlated subqueries can be written in nested subqueries form as well ? What are the major...
0
debasisdas
by: debasisdas | last post by:
Using Subqueries ================== The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement. The sub query executes once before the main query. The...
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
1
by: lizandra | last post by:
Greetings, I am a newbie, I have been working to extract data from a basic sales db and trying to decide when I should use joins and when I should use subqueries. Much of what I read online says...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.