SQL EXISTS Operator
EXISTS Operator
The EXISTS checks the existence of a result of a Subquery. The EXISTS subquery tests whether a subquery fetches at least one row. When no data is returned then this operator returns 'FALSE'.
A valid EXISTS subquery must contain an outer reference and it must be a correlated Subquery.
The select list in the EXISTS subquery is not actually used in evaluating the EXISTS so it can contain any valid select list.
Syntax:
SELECT [column_name... | expression1 ] FROM [table_name] WHERE [NOT] EXISTS (subquery)
Parameters:
| Name | Description |
|---|---|
| column_name | Name of the column of the table. |
| expression1 | Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations. |
| table_name | Name of the table. |
| WHERE [NOT] EXISTS | Tests the subquery for the existence of one or more rows. If a single row satisfies the subquery clause, it returns Boolean TRUE. When the subquery returns no matching rows the optional NOT keyword returns a Boolean TRUE. |
Contents:
Visual Presentation: SQL EXISTS Operator
DBMS Support: EXISTS Operator
| DBMS | Command |
| MySQL | Supported |
| PostgreSQL | Supported |
| SQL Server | Supported |
| Oracle | Supported |
Example: SQL EXISTS Operator
To get 'agent_code','agent_name','working_area' and 'commission' from the 'agents', with following conditions-
1. 'grade' in 'customer' table must be 3,
2. 'agent_code' in 'customer' and 'agents' table must match,
3. 'commission' of 'agents' should arrange in ascending order,
4. the above condition (1) and (2) should match at least one row,
the following sql statement can be used :
SELECT agent_code,agent_name,working_area,commission
FROM agents
WHERE exists
(SELECT * FROM customer
WHERE grade=3
AND agents.agent_code=customer.agent_code)
ORDER BY commission;
Sample table: customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+Sample table : agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Output :
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION ---------- -------------------- -------------------- ---------- A009 Benjamin Hampshair .11 A002 Mukesh Mumbai .11 A008 Alford New York .12 A010 Santakumar Chennai .14
SELECT using EXISTS
Sample table : employeesemployee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | | 120 | Treasury | 0 | 1700 | | 130 | Corporate Tax | 0 | 1700 | | 140 | Control And Credit | 0 | 1700 | | 150 | Shareholder Services | 0 | 1700 | | 160 | Benefits | 0 | 1700 | | 170 | Manufacturing | 0 | 1700 | | 180 | Construction | 0 | 1700 | | 190 | Contracting | 0 | 1700 | | 200 | Operations | 0 | 1700 | | 210 | IT Support | 0 | 1700 | | 220 | NOC | 0 | 1700 | | 230 | IT Helpdesk | 0 | 1700 | | 240 | Government Sales | 0 | 1700 | | 250 | Retail Sales | 0 | 1700 | | 260 | Recruiting | 0 | 1700 | | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+Sample table: locations
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | 1000 | 1297 Via Cola di Rie | 989 | Roma | | IT | | 1100 | 93091 Calle della Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | | 2000 | 40-5-12 Laogianggen | 190518 | Beijing | | CN | | 2100 | 1298 Vileparle (E) | 490231 | Bombay | Maharashtra | IN | | 2200 | 12-98 Victoria Street | 2901 | Sydney | New South Wales | AU | | 2300 | 198 Clementi North | 540198 | Singapore | | SG | | 2400 | 8204 Arthur St | | London | | UK | | 2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB | Oxford | Oxford | UK | | 2600 | 9702 Chester Road | 9629850293 | Stretford | Manchester | UK | | 2700 | Schwanthalerstr. 7031 | 80925 | Munich | Bavaria | DE | | 2800 | Rua Frei Caneca 1360 | 01307-002 | Sao Paulo | Sao Paulo | BR | | 2900 | 20 Rue des Corps-Saints | 1730 | Geneva | Geneve | CH | | 3000 | Murtenstrasse 921 | 3095 | Bern | BE | CH | | 3100 | Pieter Breughelstraat 837 | 3029SK | Utrecht | Utrecht | NL | | 3200 | Mariano Escobedo 9991 | 11932 | Mexico City | Distrito Federal, | MX | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
To display the employee details who are working in the country UK, we can use the following statement:
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES E
WHERE EXISTS (SELECT 1
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID
AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.COUNTRY_ID = 'UK');
Output :
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
203 Susan 6500 40
179 Charles 6200 80
177 Jack 8400 80
176 Jonathon 8600 80
175 Alyssa 8800 80
174 Ellen 11000 80
173 Sundita 6100 80
172 Elizabeth 7300 80
171 William 7400 80
170 Tayler 9600 80
169 Harrison 10000 80
168 Lisa 11500 80
167 Amit 6200 80
166 Sundar 6400 80
. . .
SQL Exists with GROUP BY
Here we have discussed how SQL EXIST can work with GROUP BY in a select statement.
Example:
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get 'cust_code', 'cust_name', 'cust_city' and 'grade' from the 'customer' table, with following conditions -
1. 'grade' in 'customer' table must be 2,
2. more than 2 agents are present in grade 2,
3. 'grade' in customer table should make a group,
the following sql statement can be used :
SELECT cust_code,cust_name,cust_city,grade
FROM customer
WHERE grade=2 AND
EXISTS(
SELECT COUNT(*) FROM customer
WHERE grade=2
GROUP BY grade
HAVING COUNT(*)>2);
Output:
CUST_CODE CUST_NAME CUST_CITY GRADE ---------- ---------------------------------------- --------------- ---------- C00013 Holmes London 2 C00001 Micheal New York 2 C00025 Ravindran Bangalore 2 C00024 Cook London 2 C00018 Fleming Brisban 2 C00022 Avinash Mumbai 2 C00017 Srinivas Bangalore 2 C00003 Martin Torento 2 C00014 Rangarappa Bangalore 2 C00016 Venkatpati Bangalore 2
SQL Exists with IN
Here is an example of SQL EXISTS operator using IN operator.
HIn this page we are discussing the usage of SQL EXISTS with IN operator in a SELECT statement.
Example:
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
To get distinct 'agent_code' from the 'orders' table, with following conditions -
1. 'agent_code' must be within the resultant 'agent_code' from 'customer' table which satisfies the condition bellow :
2. 'payment_amt' of 'customer' table must be more than 3000,
3. number of rows having said 'payment_amount' is more than 10,
the following sql statement can be used :
SELECT DISTINCT(agent_code)
FROM orders
WHERE agent_code IN(
SELECT agent_code
FROM customer WHERE payment_amt>3000
AND EXISTS(SELECT COUNT(*)
FROM customer GROUP BY payment_amt
HAVING COUNT(*)>10));
Output:
AGENT_CODE ---------- A004 A002 A007 A009 A011 A012 A010 A001 A008 A006 A005 A003
INSERT using EXISTS
To add employee details to EMP_TEMP table who are working in the country UK, the following sql statement can be used :
INSERT INTO EMP_TEMP SELECT *
FROM EMPNEW E
WHERE EXISTS (SELECT 1
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID
AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.COUNTRY_ID = 'UK');
Sample table : employees
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110Sample table: locations
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY | STATE_PROVINCE | COUNTRY_ID | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+ | 1000 | 1297 Via Cola di Rie | 989 | Roma | | IT | | 1100 | 93091 Calle della Testa | 10934 | Venice | | IT | | 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP | | 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | | JP | | 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US | | 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US | | 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US | | 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US | | 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA | | 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA | | 2000 | 40-5-12 Laogianggen | 190518 | Beijing | | CN | | 2100 | 1298 Vileparle (E) | 490231 | Bombay | Maharashtra | IN | | 2200 | 12-98 Victoria Street | 2901 | Sydney | New South Wales | AU | | 2300 | 198 Clementi North | 540198 | Singapore | | SG | | 2400 | 8204 Arthur St | | London | | UK | | 2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB | Oxford | Oxford | UK | | 2600 | 9702 Chester Road | 9629850293 | Stretford | Manchester | UK | | 2700 | Schwanthalerstr. 7031 | 80925 | Munich | Bavaria | DE | | 2800 | Rua Frei Caneca 1360 | 01307-002 | Sao Paulo | Sao Paulo | BR | | 2900 | 20 Rue des Corps-Saints | 1730 | Geneva | Geneve | CH | | 3000 | Murtenstrasse 921 | 3095 | Bern | BE | CH | | 3100 | Pieter Breughelstraat 837 | 3029SK | Utrecht | Utrecht | NL | | 3200 | Mariano Escobedo 9991 | 11932 | Mexico City | Distrito Federal, | MX | +-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
Output:
Here is the rows inserted into the table EMP_TEMP are:
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY FROM emp_temp;
EMPLOYEE_ID FIRST_NAME HIRE_DATE SALARY
----------- -------------------- --------- ----------
145 John 01-OCT-04 14000
146 Karen 05-JAN-05 13500
147 Alberto 10-MAR-05 12000
148 Gerald 15-OCT-07 11000
149 Eleni 29-JAN-08 10500
150 Peter 30-JAN-05 10000
151 David 24-MAR-05 9500
152 Peter 20-AUG-05 9000
153 Christopher 30-MAR-06 8000
. . .
175 Alyssa 19-MAR-05 8800
176 Jonathon 24-MAR-06 8600
177 Jack 23-APR-06 8400
179 Charles 04-JAN-08 6200
203 Susan 07-JUN-02 6500
35 rows selected.
UPDATE using EXISTS
Sample table : employeesemployee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
To update the commission of the employees to zero who earn the salary 14000 and above, the following sql statement can be used :
UPDATE EMPNEW E
SET COMMISSION_PCT = 0
WHERE EXISTS (SELECT 1
FROM EMPNEW
WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
AND SALARY>=14000);
Output:
Here is the rows before update the commission :
EMPLOYEE_ID FIRST_NAME SALARY COMMISSION_PCT
----------- -------------------- ---------- --------------
100 Steven 24000
101 Neena 17000
102 Lex 17000
145 John 14000 .4
Here is the effected rows after update the commission :
EMPLOYEE_ID FIRST_NAME SALARY COMMISSION_PCT
----------- -------------------- ---------- --------------
100 Steven 24000 0
101 Neena 17000 0
102 Lex 17000 0
145 John 14000 0
DELETE using EXISTS
Sample table : employeesemployee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 109 Daniel Faviet DFAVIET 515.124.4169 6/26/1987 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 6/27/1987 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 6/28/1987 FI_ACCOUNT 7700 108 100 112 Jose Manue Urman JMURMAN 515.124.4469 6/29/1987 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 6/30/1987 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 7/1/1987 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 7/2/1987 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 7/3/1987 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 7/4/1987 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 7/5/1987 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 7/6/1987 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 7/7/1987 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 7/8/1987 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 7/9/1987 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 7/10/1987 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 7/11/1987 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 7/12/1987 ST_CLERK 3200 120 50 126 Irene Mikkilinen IMIKKILI 650.124.1224 7/13/1987 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 7/14/1987 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 7/15/1987 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 7/16/1987 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 7/17/1987 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 7/18/1987 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 7/19/1987 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 7/20/1987 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 7/21/1987 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 7/22/1987 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 7/23/1987 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 7/24/1987 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 7/25/1987 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 7/26/1987 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 7/27/1987 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 7/28/1987 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 7/29/1987 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 7/30/1987 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 7/31/1987 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344. 8/1/1987 SA_MAN 14000 0.4 100 80 146 Karen Partners KPARTNER 011.44.1344. 8/2/1987 SA_MAN 13500 0.3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344. 8/3/1987 SA_MAN 12000 0.3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344. 8/4/1987 SA_MAN 11000 0.3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344. 8/5/1987 SA_MAN 10500 0.2 100 80 150 Peter Tucker PTUCKER 011.44.1344. 8/6/1987 SA_REP 10000 0.3 145 80 151 David Bernstein DBERNSTE 011.44.1344. 8/7/1987 SA_REP 9500 0.25 145 80 152 Peter Hall PHALL 011.44.1344. 8/8/1987 SA_REP 9000 0.25 145 80 153 Christophe Olsen COLSEN 011.44.1344. 8/9/1987 SA_REP 8000 0.2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344. 8/10/1987 SA_REP 7500 0.2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344. 8/11/1987 SA_REP 7000 0.15 145 80 156 Janette King JKING 011.44.1345. 8/12/1987 SA_REP 10000 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345. 8/13/1987 SA_REP 9500 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345. 8/14/1987 SA_REP 9000 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345. 8/15/1987 SA_REP 8000 0.3 146 80 160 Louise Doran LDORAN 011.44.1345. 8/16/1987 SA_REP 7500 0.3 146 80 161 Sarath Sewall SSEWALL 011.44.1345. 8/17/1987 SA_REP 7000 0.25 146 80 162 Clara Vishney CVISHNEY 011.44.1346. 8/18/1987 SA_REP 10500 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346. 8/19/1987 SA_REP 9500 0.15 147 80 164 Mattea Marvins MMARVINS 011.44.1346. 8/20/1987 SA_REP 7200 0.1 147 80 165 David Lee DLEE 011.44.1346. 8/21/1987 SA_REP 6800 0.1 147 80 166 Sundar Ande SANDE 011.44.1346. 8/22/1987 SA_REP 6400 0.1 147 80 167 Amit Banda ABANDA 011.44.1346. 8/23/1987 SA_REP 6200 0.1 147 80 168 Lisa Ozer LOZER 011.44.1343. 8/24/1987 SA_REP 11500 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343. 8/25/1987 SA_REP 10000 0.2 148 80 170 Tayler Fox TFOX 011.44.1343. 8/26/1987 SA_REP 9600 0.2 148 80 171 William Smith WSMITH 011.44.1343. 8/27/1987 SA_REP 7400 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343. 8/28/1987 SA_REP 7300 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343. 8/29/1987 SA_REP 6100 0.1 148 80 174 Ellen Abel EABEL 011.44.1644. 8/30/1987 SA_REP 11000 0.3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644. 8/31/1987 SA_REP 8800 0.25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644. 9/1/1987 SA_REP 8600 0.2 149 80 177 Jack Livingston JLIVINGS 011.44.1644. 9/2/1987 SA_REP 8400 0.2 149 80 178 Kimberely Grant KGRANT 011.44.1644. 9/3/1987 SA_REP 7000 0.15 149 179 Charles Johnson CJOHNSON 011.44.1644. 9/4/1987 SA_REP 6200 0.1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 9/5/1987 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 9/6/1987 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 9/7/1987 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 9/8/1987 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 9/9/1987 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 9/10/1987 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 9/11/1987 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 9/12/1987 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 9/13/1987 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 9/14/1987 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 9/15/1987 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 9/16/1987 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 9/17/1987 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 9/18/1987 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 9/19/1987 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 9/20/1987 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 9/21/1987 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 9/22/1987 SH_CLERK 3000 124 50 198 Donald OConnell DOCONNEL 650.507.9833 9/23/1987 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 9/24/1987 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 9/25/1987 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 9/26/1987 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 9/27/1987 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 9/28/1987 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 9/29/1987 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 9/30/1987 AC_MGR 12000 101 110 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
To delete the employee details from who worked 14 years and above, the following sql statement can be used :
DELETE FROM EMPNEW E
WHERE EXISTS (SELECT 1
FROM EMPNEW
WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
AND (TO_CHAR(SYSDATE,'YYYY')- TO_CHAR(HIRE_DATE,'YYYY'))>=14);
Output:
Here is the rows before delete who worked for 14 years and more :
EMPLOYEE_ID FIRST_NAME HIRE_DATE
----------- -------------------- ---------
102 Lex 13-JAN-01
108 Nancy 17-AUG-02
109 Daniel 16-AUG-02
114 Den 07-DEC-02
203 Susan 07-JUN-02
204 Hermann 07-JUN-02
205 Shelley 07-JUN-02
206 William 07-JUN-02
Here is the output after execute the command :
SQL> DELETE FROM EMPNEW E 2 WHERE EXISTS (SELECT 1 3 FROM EMPNEW 4 WHERE EMPLOYEE_ID = E.EMPLOYEE_ID 5 AND (TO_CHAR(SYSDATE,'YYYY')- TO_CHAR(HIRE_DATE,'YYYY'))>=14); 8 rows deleted.
SQL NOT Exists
Here we have discussed how SQL NOT EXISTS works with a select statement.
Example:
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
To get 'agent_code', 'ord_num', 'ord_amount' and 'cust_code' from the 'orders' table, with following condition -
1. any agent of 'customer' table not having a 'payment_amt' is 1400,
the following sql statement can be used:
SELECT agent_code,ord_num,ord_amount,cust_code
FROM orders a
WHERE NOT EXISTS(
SELECT agent_code
FROM customer
WHERE payment_amt=1400 AND a.cust_code=cust_code);
Output:
AGENT_CODE ORD_NUM ORD_AMOUNT CUST_C ---------- ---------- ---------- ------ A005 200134 4200 C00004 A007 200112 2000 C00016 A004 200122 2500 C00003 A008 200111 1000 C00020 A008 200114 3500 C00002 A002 200133 1200 C00009 A002 200128 3500 C00009 A002 200120 500 C00009 A010 200109 3500 C00011 A004 200108 4000 C00008 A004 200121 1500 C00008 A002 200113 4000 C00022 A002 200126 500 C00022 A002 200123 500 C00022 A006 200129 2500 C00024 ........ ........
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics