SQL> --SPOOL komutu herhangi bir SQL oturumunu istenilen dosya formatinda (txt, doc, sql) SQL> --kayit altina almak için kullanilan bir SQL*Plus komutudur. SQL> --DESC veya DESCRIBE komutu: Tablolarin yapisini gosterir. (Tablo yapisi: sutunlar, veri tipler SQL> -- tipleri, kisitlamalar vs) SQL> describe employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> --Soru: Calisanlarin (employees) isim ve soyisimlerini gösteren bir sorgu yaziniz. SQL> select first_name,LAST_NAME 2 from employees 3 SQL> run 1 select first_name,LAST_NAME 2* from employees FIRST_NAME LAST_NAME -------------------- ------------------------- Ellen Abel Sundar Ande Mozhe Atkinson David Austin Hermann Baer Shelli Baida Amit Banda Elizabeth Bates Sarah Bell David Bernstein Laura Bissot FIRST_NAME LAST_NAME -------------------- ------------------------- Harrison Bloom Alexis Bull Anthony Cabrio Gerald Cambrault Nanette Cambrault John Chen Kelly Chung Karen Colmenares Curtis Davies Lex De Haan Julia Dellinger FIRST_NAME LAST_NAME -------------------- ------------------------- Jennifer Dilly Louise Doran Bruce Ernst Alberto Errazuriz Britney Everett Daniel Faviet Pat Fay Kevin Feeney Jean Fleaur Tayler Fox Adam Fripp FIRST_NAME LAST_NAME -------------------- ------------------------- Timothy Gates Ki Gee Girard Geoni William Gietz Douglas Grant Kimberely Grant Nancy Greenberg Danielle Greene Peter Hall Michael Hartstein Shelley Higgins FIRST_NAME LAST_NAME -------------------- ------------------------- Guy Himuro Alexander Hunold Alyssa Hutton Charles Johnson Vance Jones Payam Kaufling Alexander Khoo Janette King Steven King Neena Kochhar Sundita Kumar FIRST_NAME LAST_NAME -------------------- ------------------------- Renske Ladwig James Landry David Lee Jack Livingston Diana Lorentz Jason Mallin Steven Markle James Marlow Mattea Marvins Randall Matos Susan Mavris FIRST_NAME LAST_NAME -------------------- ------------------------- Samuel McCain Allan McEwen Irene Mikkilineni Kevin Mourgos Julia Nayer Donald OConnell Christopher Olsen TJ Olson Lisa Ozer Karen Partners Valli Pataballa FIRST_NAME LAST_NAME -------------------- ------------------------- Joshua Patel Randall Perkins Hazel Philtanker Luis Popp Trenna Rajs Den Raphaely Michael Rogers John Russell Nandita Sarchand Ismael Sciarra John Seo FIRST_NAME LAST_NAME -------------------- ------------------------- Sarath Sewall Lindsey Smith William Smith Stephen Stiles Martha Sullivan Patrick Sully Jonathon Taylor Winston Taylor Sigal Tobias Peter Tucker Oliver Tuvault FIRST_NAME LAST_NAME -------------------- ------------------------- Jose Manuel Urman Peter Vargas Clara Vishney Shanta Vollman Alana Walsh Matthew Weiss Jennifer Whalen Eleni Zlotkey 107 rows selected. SQL> list 1 select first_name,LAST_NAME 2* from employees SQL> --RUN veya r: SQL*plus komutu en son yazilmis olan SQL komutunu caliştirir. SQL> --List veya L: SQL*plus komutudur. en son yazilmis olan SQL komutunu ekrana getirir. SQL> l 1 select first_name,LAST_NAME 2* from employees SQL> r 1 select first_name,LAST_NAME 2* from employees FIRST_NAME LAST_NAME -------------------- ------------------------- Ellen Abel Sundar Ande Mozhe Atkinson David Austin Hermann Baer Shelli Baida Amit Banda Elizabeth Bates Sarah Bell David Bernstein Laura Bissot FIRST_NAME LAST_NAME -------------------- ------------------------- Harrison Bloom Alexis Bull Anthony Cabrio Gerald Cambrault Nanette Cambrault John Chen Kelly Chung Karen Colmenares Curtis Davies Lex De Haan Julia Dellinger FIRST_NAME LAST_NAME -------------------- ------------------------- Jennifer Dilly Louise Doran Bruce Ernst Alberto Errazuriz Britney Everett Daniel Faviet Pat Fay Kevin Feeney Jean Fleaur Tayler Fox Adam Fripp FIRST_NAME LAST_NAME -------------------- ------------------------- Timothy Gates Ki Gee Girard Geoni William Gietz Douglas Grant Kimberely Grant Nancy Greenberg Danielle Greene Peter Hall Michael Hartstein Shelley Higgins FIRST_NAME LAST_NAME -------------------- ------------------------- Guy Himuro Alexander Hunold Alyssa Hutton Charles Johnson Vance Jones Payam Kaufling Alexander Khoo Janette King Steven King Neena Kochhar Sundita Kumar FIRST_NAME LAST_NAME -------------------- ------------------------- Renske Ladwig James Landry David Lee Jack Livingston Diana Lorentz Jason Mallin Steven Markle James Marlow Mattea Marvins Randall Matos Susan Mavris FIRST_NAME LAST_NAME -------------------- ------------------------- Samuel McCain Allan McEwen Irene Mikkilineni Kevin Mourgos Julia Nayer Donald OConnell Christopher Olsen TJ Olson Lisa Ozer Karen Partners Valli Pataballa FIRST_NAME LAST_NAME -------------------- ------------------------- Joshua Patel Randall Perkins Hazel Philtanker Luis Popp Trenna Rajs Den Raphaely Michael Rogers John Russell Nandita Sarchand Ismael Sciarra John Seo FIRST_NAME LAST_NAME -------------------- ------------------------- Sarath Sewall Lindsey Smith William Smith Stephen Stiles Martha Sullivan Patrick Sully Jonathon Taylor Winston Taylor Sigal Tobias Peter Tucker Oliver Tuvault FIRST_NAME LAST_NAME -------------------- ------------------------- Jose Manuel Urman Peter Vargas Clara Vishney Shanta Vollman Alana Walsh Matthew Weiss Jennifer Whalen Eleni Zlotkey 107 rows selected. SQL> l 1 select first_name,LAST_NAME 2* from employees SQL> edit Wrote file afiedt.buf 1 select first_name,LAST_NAME,salary 2* from employees SQL> r 1 select first_name,LAST_NAME,salary 2* from employees FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 Alexander Hunold 9000 Bruce Ernst 6000 David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Nancy Greenberg 12008 Daniel Faviet 9000 John Chen 8200 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Ismael Sciarra 7700 Jose Manuel Urman 7800 Luis Popp 6900 Den Raphaely 11000 Alexander Khoo 3100 Shelli Baida 2900 Sigal Tobias 2800 Guy Himuro 2600 Karen Colmenares 2500 Matthew Weiss 8000 Adam Fripp 8200 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Payam Kaufling 7900 Shanta Vollman 6500 Kevin Mourgos 5800 Julia Nayer 3200 Irene Mikkilineni 2700 James Landry 2400 Steven Markle 2200 Laura Bissot 3300 Mozhe Atkinson 2800 James Marlow 2500 TJ Olson 2100 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Jason Mallin 3300 Michael Rogers 2900 Ki Gee 2400 Hazel Philtanker 2200 Renske Ladwig 3600 Stephen Stiles 3200 John Seo 2700 Joshua Patel 2500 Trenna Rajs 3500 Curtis Davies 3100 Randall Matos 2600 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Peter Vargas 2500 John Russell 14000 Karen Partners 13500 Alberto Errazuriz 12000 Gerald Cambrault 11000 Eleni Zlotkey 10500 Peter Tucker 10000 David Bernstein 9500 Peter Hall 9000 Christopher Olsen 8000 Nanette Cambrault 7500 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Oliver Tuvault 7000 Janette King 10000 Patrick Sully 9500 Allan McEwen 9000 Lindsey Smith 8000 Louise Doran 7500 Sarath Sewall 7000 Clara Vishney 10500 Danielle Greene 9500 Mattea Marvins 7200 David Lee 6800 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Sundar Ande 6400 Amit Banda 6200 Lisa Ozer 11500 Harrison Bloom 10000 Tayler Fox 9600 William Smith 7400 Elizabeth Bates 7300 Sundita Kumar 6100 Ellen Abel 11000 Alyssa Hutton 8800 Jonathon Taylor 8600 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Jack Livingston 8400 Kimberely Grant 7000 Charles Johnson 6200 Winston Taylor 3200 Jean Fleaur 3100 Martha Sullivan 2500 Girard Geoni 2800 Nandita Sarchand 4200 Alexis Bull 4100 Julia Dellinger 3400 Anthony Cabrio 3000 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Kelly Chung 3800 Jennifer Dilly 3600 Timothy Gates 2900 Randall Perkins 2500 Sarah Bell 4000 Britney Everett 3900 Samuel McCain 3200 Vance Jones 2800 Alana Walsh 3100 Kevin Feeney 3000 Donald OConnell 2600 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Douglas Grant 2600 Jennifer Whalen 4400 Michael Hartstein 13000 Pat Fay 6000 Susan Mavris 6500 Hermann Baer 10000 Shelley Higgins 12008 William Gietz 8300 107 rows selected. SQL> l 1 select first_name,LAST_NAME,salary 2* from employees SQL> edit Wrote file afiedt.buf 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2* from employees SQL> r 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2* from employees ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 Alexander Hunold 9000 Bruce Ernst 6000 David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Nancy Greenberg 12008 Daniel Faviet 9000 John Chen 8200 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Ismael Sciarra 7700 Jose Manuel Urman 7800 Luis Popp 6900 Den Raphaely 11000 Alexander Khoo 3100 Shelli Baida 2900 Sigal Tobias 2800 Guy Himuro 2600 Karen Colmenares 2500 Matthew Weiss 8000 Adam Fripp 8200 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Payam Kaufling 7900 Shanta Vollman 6500 Kevin Mourgos 5800 Julia Nayer 3200 Irene Mikkilineni 2700 James Landry 2400 Steven Markle 2200 Laura Bissot 3300 Mozhe Atkinson 2800 James Marlow 2500 TJ Olson 2100 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Jason Mallin 3300 Michael Rogers 2900 Ki Gee 2400 Hazel Philtanker 2200 Renske Ladwig 3600 Stephen Stiles 3200 John Seo 2700 Joshua Patel 2500 Trenna Rajs 3500 Curtis Davies 3100 Randall Matos 2600 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Peter Vargas 2500 John Russell 14000 Karen Partners 13500 Alberto Errazuriz 12000 Gerald Cambrault 11000 Eleni Zlotkey 10500 Peter Tucker 10000 David Bernstein 9500 Peter Hall 9000 Christopher Olsen 8000 Nanette Cambrault 7500 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Oliver Tuvault 7000 Janette King 10000 Patrick Sully 9500 Allan McEwen 9000 Lindsey Smith 8000 Louise Doran 7500 Sarath Sewall 7000 Clara Vishney 10500 Danielle Greene 9500 Mattea Marvins 7200 David Lee 6800 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Sundar Ande 6400 Amit Banda 6200 Lisa Ozer 11500 Harrison Bloom 10000 Tayler Fox 9600 William Smith 7400 Elizabeth Bates 7300 Sundita Kumar 6100 Ellen Abel 11000 Alyssa Hutton 8800 Jonathon Taylor 8600 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Jack Livingston 8400 Kimberely Grant 7000 Charles Johnson 6200 Winston Taylor 3200 Jean Fleaur 3100 Martha Sullivan 2500 Girard Geoni 2800 Nandita Sarchand 4200 Alexis Bull 4100 Julia Dellinger 3400 Anthony Cabrio 3000 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Kelly Chung 3800 Jennifer Dilly 3600 Timothy Gates 2900 Randall Perkins 2500 Sarah Bell 4000 Britney Everett 3900 Samuel McCain 3200 Vance Jones 2800 Alana Walsh 3100 Kevin Feeney 3000 Donald OConnell 2600 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Douglas Grant 2600 Jennifer Whalen 4400 Michael Hartstein 13000 Pat Fay 6000 Susan Mavris 6500 Hermann Baer 10000 Shelley Higgins 12008 William Gietz 8300 107 rows selected. SQL> edit Wrote file afiedt.buf 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2 from employees 3* where salary>=3000 and salary<=5000 SQL> r 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2 from employees 3* where salary>=3000 and salary<=5000 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Alexander Khoo 3100 Julia Nayer 3200 Laura Bissot 3300 Jason Mallin 3300 Renske Ladwig 3600 Stephen Stiles 3200 Trenna Rajs 3500 Curtis Davies 3100 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Winston Taylor 3200 Jean Fleaur 3100 Nandita Sarchand 4200 Alexis Bull 4100 Julia Dellinger 3400 Anthony Cabrio 3000 Kelly Chung 3800 Jennifer Dilly 3600 Sarah Bell 4000 Britney Everett 3900 Samuel McCain 3200 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Alana Walsh 3100 Kevin Feeney 3000 Jennifer Whalen 4400 25 rows selected. SQL> l 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2 from employees 3* where salary>=3000 and salary<=5000 SQL> ed Wrote file afiedt.buf 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2 from employees 3* where salary between 3000 and 5000 SQL> r 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2 from employees 3* where salary between 3000 and 5000 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Alexander Khoo 3100 Julia Nayer 3200 Laura Bissot 3300 Jason Mallin 3300 Renske Ladwig 3600 Stephen Stiles 3200 Trenna Rajs 3500 Curtis Davies 3100 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Winston Taylor 3200 Jean Fleaur 3100 Nandita Sarchand 4200 Alexis Bull 4100 Julia Dellinger 3400 Anthony Cabrio 3000 Kelly Chung 3800 Jennifer Dilly 3600 Sarah Bell 4000 Britney Everett 3900 Samuel McCain 3200 ISIM SOYISIM MAAS -------------------- ------------------------- ---------- Alana Walsh 3100 Kevin Feeney 3000 Jennifer Whalen 4400 25 rows selected. SQL> l 1 select first_name ISIM,LAST_NAME SOYISIM,salary MAAS 2 from employees 3* where salary between 3000 and 5000 SQL> SQL> SQL> --Soru: Bolumu 10,20 veya 30 olan calisanlarin isim,soyisim, maas ve bolumlerini gosteriniz. SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> select first_name,last_name,salary,department_id 2 from employees 3 where department_id between 10 and 30; FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID -------------------- ------------------------- ---------- ------------- Jennifer Whalen 4400 10 Michael Hartstein 13000 20 Pat Fay 6000 20 Den Raphaely 11000 30 Alexander Khoo 3100 30 Shelli Baida 2900 30 Sigal Tobias 2800 30 Guy Himuro 2600 30 Karen Colmenares 2500 30 9 rows selected. SQL> --10, 30 veya 40 no'lu bolumde calisanlari gosterelim SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id between 10 and 40 SQL> r 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id between 10 and 40 FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID -------------------- ------------------------- ---------- ------------- Jennifer Whalen 4400 10 Michael Hartstein 13000 20 Pat Fay 6000 20 Den Raphaely 11000 30 Alexander Khoo 3100 30 Shelli Baida 2900 30 Sigal Tobias 2800 30 Guy Himuro 2600 30 Karen Colmenares 2500 30 Susan Mavris 6500 40 10 rows selected. SQL> --Between operatoru bu tür sorularda baslangic ve bitis değerleri SQL> --arasinda istenmeyen değeri de dahil edebilecegi için pek uygun degildir. SQL> --Secenek 1 SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id=10 or department_id=20 or department_id=30 SQL> r 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id=10 or department_id=20 or department_id=30 FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID -------------------- ------------------------- ---------- ------------- Jennifer Whalen 4400 10 Michael Hartstein 13000 20 Pat Fay 6000 20 Den Raphaely 11000 30 Alexander Khoo 3100 30 Shelli Baida 2900 30 Sigal Tobias 2800 30 Guy Himuro 2600 30 Karen Colmenares 2500 30 9 rows selected. SQL> --Seçenek 2. = IN operatörü SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id in (10,20,30) SQL> r 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id in (10,20,30) FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID -------------------- ------------------------- ---------- ------------- Jennifer Whalen 4400 10 Michael Hartstein 13000 20 Pat Fay 6000 20 Den Raphaely 11000 30 Alexander Khoo 3100 30 Shelli Baida 2900 30 Sigal Tobias 2800 30 Guy Himuro 2600 30 Karen Colmenares 2500 30 9 rows selected. SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,salary,department_id 2 from employees 3* where department_id in (10,20,30) SQL> SQL> --Ismi A ile C arasinda olan calisanlarin isim, ve soyisimlerini goruntuleyiniz. SQL> select first_name,last_name 2 from employees 3 where first_name between 'A' and 'C'; FIRST_NAME LAST_NAME -------------------- ------------------------- Amit Banda Alexis Bull Anthony Cabrio Bruce Ernst Alberto Errazuriz Britney Everett Adam Fripp Alexander Hunold Alyssa Hutton Alexander Khoo Allan McEwen FIRST_NAME LAST_NAME -------------------- ------------------------- Alana Walsh 12 rows selected. SQL> select first_name 2 from employees 3 order by first_name asc; FIRST_NAME -------------------- Adam Alana Alberto Alexander Alexander Alexis Allan Alyssa Amit Anthony Britney FIRST_NAME -------------------- Bruce Charles Christopher Clara Curtis Daniel Danielle David David David Den FIRST_NAME -------------------- Diana Donald Douglas Eleni Elizabeth Ellen Gerald Girard Guy Harrison Hazel FIRST_NAME -------------------- Hermann Irene Ismael Jack James James Janette Jason Jean Jennifer Jennifer FIRST_NAME -------------------- John John John Jonathon Jose Manuel Joshua Julia Julia Karen Karen Kelly FIRST_NAME -------------------- Kevin Kevin Ki Kimberely Laura Lex Lindsey Lisa Louise Luis Martha FIRST_NAME -------------------- Mattea Matthew Michael Michael Mozhe Nancy Nandita Nanette Neena Oliver Pat FIRST_NAME -------------------- Patrick Payam Peter Peter Peter Randall Randall Renske Samuel Sarah Sarath FIRST_NAME -------------------- Shanta Shelley Shelli Sigal Stephen Steven Steven Sundar Sundita Susan TJ FIRST_NAME -------------------- Tayler Timothy Trenna Valli Vance William William Winston 107 rows selected. SQL> select first_name,last_name 2 from employees 3 where first_name between 'A' and 'Cz'; FIRST_NAME LAST_NAME -------------------- ------------------------- Amit Banda Alexis Bull Anthony Cabrio Curtis Davies Bruce Ernst Alberto Errazuriz Britney Everett Adam Fripp Alexander Hunold Alyssa Hutton Charles Johnson FIRST_NAME LAST_NAME -------------------- ------------------------- Alexander Khoo Allan McEwen Christopher Olsen Clara Vishney Alana Walsh 16 rows selected. SQL> edit Wrote file afiedt.buf 1 select first_name,last_name 2 from employees 3* where first_name between 'A' and 'Cm' SQL> r 1 select first_name,last_name 2 from employees 3* where first_name between 'A' and 'Cm' FIRST_NAME LAST_NAME -------------------- ------------------------- Amit Banda Alexis Bull Anthony Cabrio Bruce Ernst Alberto Errazuriz Britney Everett Adam Fripp Alexander Hunold Alyssa Hutton Charles Johnson Alexander Khoo FIRST_NAME LAST_NAME -------------------- ------------------------- Allan McEwen Christopher Olsen Clara Vishney Alana Walsh 15 rows selected. SQL> ed Wrote file afiedt.buf 1 select first_name,last_name 2 from employees 3* where first_name between 'A' and 'D' SQL> r 1 select first_name,last_name 2 from employees 3* where first_name between 'A' and 'D' FIRST_NAME LAST_NAME -------------------- ------------------------- Amit Banda Alexis Bull Anthony Cabrio Curtis Davies Bruce Ernst Alberto Errazuriz Britney Everett Adam Fripp Alexander Hunold Alyssa Hutton Charles Johnson FIRST_NAME LAST_NAME -------------------- ------------------------- Alexander Khoo Allan McEwen Christopher Olsen Clara Vishney Alana Walsh 16 rows selected. SQL> --Soru: Ismi 'A' karateri ile başlayan calisanlarin isim, soyisim ve ise alinma tarihlerini gös SQL> --gosteriniz. SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> select first_name,last_name,hire_date 2 from employees 3 where first_name='A'; no rows selected SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like 'A' SQL> r 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like 'A' no rows selected SQL> edit Wrote file afiedt.buf 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like 'A%' SQL> r 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like 'A%' FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Amit Banda 21-APR-08 Alexis Bull 20-FEB-05 Anthony Cabrio 07-FEB-07 Alberto Errazuriz 10-MAR-05 Adam Fripp 10-APR-05 Alexander Hunold 03-JAN-06 Alyssa Hutton 19-MAR-05 Alexander Khoo 18-MAY-03 Allan McEwen 01-AUG-04 Alana Walsh 24-APR-06 10 rows selected. SQL> --Isminin 2. karakteri 'A' olan çalisanlari görüntüleyelim SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like '%A%' SQL> r 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like '%A%' FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Alexander Hunold 03-JAN-06 Alexander Khoo 18-MAY-03 Adam Fripp 10-APR-05 Alberto Errazuriz 10-MAR-05 Allan McEwen 01-AUG-04 Amit Banda 21-APR-08 Alyssa Hutton 19-MAR-05 Alexis Bull 20-FEB-05 Anthony Cabrio 07-FEB-07 Alana Walsh 24-APR-06 10 rows selected. SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like '%a%' SQL> r 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like '%a%' FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Neena Kochhar 21-SEP-05 Alexander Hunold 03-JAN-06 David Austin 25-JUN-05 Valli Pataballa 05-FEB-06 Diana Lorentz 07-FEB-07 Nancy Greenberg 17-AUG-02 Daniel Faviet 16-AUG-02 Ismael Sciarra 30-SEP-05 Jose Manuel Urman 07-MAR-06 Alexander Khoo 18-MAY-03 Sigal Tobias 24-JUL-05 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Karen Colmenares 10-AUG-07 Matthew Weiss 18-JUL-04 Adam Fripp 10-APR-05 Payam Kaufling 01-MAY-03 Shanta Vollman 10-OCT-05 Julia Nayer 16-JUL-05 James Landry 14-JAN-07 Laura Bissot 20-AUG-05 James Marlow 16-FEB-05 Jason Mallin 14-JUN-04 Michael Rogers 26-AUG-06 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Hazel Philtanker 06-FEB-08 Joshua Patel 06-APR-06 Trenna Rajs 17-OCT-03 Randall Matos 15-MAR-06 Karen Partners 05-JAN-05 Gerald Cambrault 15-OCT-07 David Bernstein 24-MAR-05 Nanette Cambrault 09-DEC-06 Janette King 30-JAN-04 Patrick Sully 04-MAR-04 Allan McEwen 01-AUG-04 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Sarath Sewall 03-NOV-06 Clara Vishney 11-NOV-05 Danielle Greene 19-MAR-07 Mattea Marvins 24-JAN-08 David Lee 23-FEB-08 Sundar Ande 24-MAR-08 Lisa Ozer 11-MAR-05 Harrison Bloom 23-MAR-06 Tayler Fox 24-JAN-06 William Smith 23-FEB-07 Elizabeth Bates 24-MAR-07 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Sundita Kumar 21-APR-08 Alyssa Hutton 19-MAR-05 Jonathon Taylor 24-MAR-06 Jack Livingston 23-APR-06 Charles Johnson 04-JAN-08 Jean Fleaur 23-FEB-06 Martha Sullivan 21-JUN-07 Girard Geoni 03-FEB-08 Nandita Sarchand 27-JAN-04 Julia Dellinger 24-JUN-06 Randall Perkins 19-DEC-07 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Sarah Bell 04-FEB-04 Samuel McCain 01-JUL-06 Vance Jones 17-MAR-07 Alana Walsh 24-APR-06 Donald OConnell 21-JUN-07 Douglas Grant 13-JAN-08 Michael Hartstein 17-FEB-04 Pat Fay 17-AUG-05 Susan Mavris 07-JUN-02 Hermann Baer 07-JUN-02 William Gietz 07-JUN-02 66 rows selected. SQL> l 1 select first_name,last_name,hire_date 2 from employees 3* where first_name like '%a%' SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,hire_date 2 from employees 3* where lower(first_name) like '%a%' SQL> r 1 select first_name,last_name,hire_date 2 from employees 3* where lower(first_name) like '%a%' FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Neena Kochhar 21-SEP-05 Alexander Hunold 03-JAN-06 David Austin 25-JUN-05 Valli Pataballa 05-FEB-06 Diana Lorentz 07-FEB-07 Nancy Greenberg 17-AUG-02 Daniel Faviet 16-AUG-02 Ismael Sciarra 30-SEP-05 Jose Manuel Urman 07-MAR-06 Alexander Khoo 18-MAY-03 Sigal Tobias 24-JUL-05 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Karen Colmenares 10-AUG-07 Matthew Weiss 18-JUL-04 Adam Fripp 10-APR-05 Payam Kaufling 01-MAY-03 Shanta Vollman 10-OCT-05 Julia Nayer 16-JUL-05 James Landry 14-JAN-07 Laura Bissot 20-AUG-05 James Marlow 16-FEB-05 Jason Mallin 14-JUN-04 Michael Rogers 26-AUG-06 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Hazel Philtanker 06-FEB-08 Joshua Patel 06-APR-06 Trenna Rajs 17-OCT-03 Randall Matos 15-MAR-06 Karen Partners 05-JAN-05 Alberto Errazuriz 10-MAR-05 Gerald Cambrault 15-OCT-07 David Bernstein 24-MAR-05 Nanette Cambrault 09-DEC-06 Janette King 30-JAN-04 Patrick Sully 04-MAR-04 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Allan McEwen 01-AUG-04 Sarath Sewall 03-NOV-06 Clara Vishney 11-NOV-05 Danielle Greene 19-MAR-07 Mattea Marvins 24-JAN-08 David Lee 23-FEB-08 Sundar Ande 24-MAR-08 Amit Banda 21-APR-08 Lisa Ozer 11-MAR-05 Harrison Bloom 23-MAR-06 Tayler Fox 24-JAN-06 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- William Smith 23-FEB-07 Elizabeth Bates 24-MAR-07 Sundita Kumar 21-APR-08 Alyssa Hutton 19-MAR-05 Jonathon Taylor 24-MAR-06 Jack Livingston 23-APR-06 Charles Johnson 04-JAN-08 Jean Fleaur 23-FEB-06 Martha Sullivan 21-JUN-07 Girard Geoni 03-FEB-08 Nandita Sarchand 27-JAN-04 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Alexis Bull 20-FEB-05 Julia Dellinger 24-JUN-06 Anthony Cabrio 07-FEB-07 Randall Perkins 19-DEC-07 Sarah Bell 04-FEB-04 Samuel McCain 01-JUL-06 Vance Jones 17-MAR-07 Alana Walsh 24-APR-06 Donald OConnell 21-JUN-07 Douglas Grant 13-JAN-08 Michael Hartstein 17-FEB-04 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Pat Fay 17-AUG-05 Susan Mavris 07-JUN-02 Hermann Baer 07-JUN-02 William Gietz 07-JUN-02 70 rows selected. SQL> l 1 select first_name,last_name,hire_date 2 from employees 3* where lower(first_name) like '%a%' SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,hire_date 2 from employees 3* where lower(first_name) like '_a%' SQL> r 1 select first_name,last_name,hire_date 2 from employees 3* where lower(first_name) like '_a%' FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- David Austin 25-JUN-05 Valli Pataballa 05-FEB-06 Nancy Greenberg 17-AUG-02 Daniel Faviet 16-AUG-02 Karen Colmenares 10-AUG-07 Matthew Weiss 18-JUL-04 Payam Kaufling 01-MAY-03 James Landry 14-JAN-07 Laura Bissot 20-AUG-05 James Marlow 16-FEB-05 Jason Mallin 14-JUN-04 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Hazel Philtanker 06-FEB-08 Randall Matos 15-MAR-06 Karen Partners 05-JAN-05 David Bernstein 24-MAR-05 Nanette Cambrault 09-DEC-06 Janette King 30-JAN-04 Patrick Sully 04-MAR-04 Sarath Sewall 03-NOV-06 Danielle Greene 19-MAR-07 Mattea Marvins 24-JAN-08 David Lee 23-FEB-08 FIRST_NAME LAST_NAME HIRE_DATE -------------------- ------------------------- --------- Harrison Bloom 23-MAR-06 Tayler Fox 24-JAN-06 Jack Livingston 23-APR-06 Martha Sullivan 21-JUN-07 Nandita Sarchand 27-JAN-04 Randall Perkins 19-DEC-07 Sarah Bell 04-FEB-04 Samuel McCain 01-JUL-06 Vance Jones 17-MAR-07 Pat Fay 17-AUG-05 32 rows selected. SQL> l 1 select first_name,last_name,hire_date 2 from employees 3* where lower(first_name) like '_a%' SQL> SQL> --Isminin son karakteri N, soyisiminin ilk karakteri K olan çalisanlari görüntülyiniz. SQL> select first_name, last_name 2 from employees 3 where upper(first_name) like '%N' and upper(last_name) like 'K%'; FIRST_NAME LAST_NAME -------------------- ------------------------- Steven King SQL> SQL> --Distinct anahtar kelimesi ile birlikte kullanilan sutundaki degerleri sadece bir kez g SQL> --goruntuler SQL> select department_id from employees; DEPARTMENT_ID ------------- 90 90 90 60 60 60 60 60 100 100 100 DEPARTMENT_ID ------------- 100 100 100 30 30 30 30 30 30 50 50 DEPARTMENT_ID ------------- 50 50 50 50 50 50 50 50 50 50 50 DEPARTMENT_ID ------------- 50 50 50 50 50 50 50 50 50 50 50 DEPARTMENT_ID ------------- 50 80 80 80 80 80 80 80 80 80 80 DEPARTMENT_ID ------------- 80 80 80 80 80 80 80 80 80 80 80 DEPARTMENT_ID ------------- 80 80 80 80 80 80 80 80 80 80 80 DEPARTMENT_ID ------------- 80 80 50 50 50 50 50 50 50 50 DEPARTMENT_ID ------------- 50 50 50 50 50 50 50 50 50 50 50 DEPARTMENT_ID ------------- 50 10 20 20 40 70 110 110 107 rows selected. SQL> l 1* select department_id from employees SQL> SQL> --Soru: Calisanlarin bölümlerini görüntüleyiniz. Her bölüm numarasi sadece bir kere görüntülenmeli SQL> SQL> select distinct department_id 2 from employees; DEPARTMENT_ID ------------- 100 30 90 20 70 110 50 80 40 60 DEPARTMENT_ID ------------- 10 12 rows selected. SQL> ed Wrote file afiedt.buf 1 select distinct department_id 2 from employees 3* where department_id is not null SQL> r 1 select distinct department_id 2 from employees 3* where department_id is not null DEPARTMENT_ID ------------- 10 20 30 40 50 60 70 80 90 100 110 11 rows selected. SQL> --Herhangi bir bolume atanmayan calisan varsa sorgu sonucunda isim, soyisim ve maasini SQL> --görüntüleyiniz. SQL> select first_name,last_name,salary 2 from employees 3 where department_id is null; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Kimberely Grant 7000 SQL> --Maasi 5000 ile 10000 arasinda olmayan calisanlarin isim ve soyisimlerini gösteriniz. SQL> SQL> select first_name,last_name 2 from employees 3 where salary not between 5000 and 10000 4 SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,salary 2 from employees 3* where salary not between 5000 and 10000 SQL> r 1 select first_name,last_name,salary 2 from employees 3* where salary not between 5000 and 10000 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Nancy Greenberg 12008 Den Raphaely 11000 Alexander Khoo 3100 Shelli Baida 2900 Sigal Tobias 2800 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Guy Himuro 2600 Karen Colmenares 2500 Julia Nayer 3200 Irene Mikkilineni 2700 James Landry 2400 Steven Markle 2200 Laura Bissot 3300 Mozhe Atkinson 2800 James Marlow 2500 TJ Olson 2100 Jason Mallin 3300 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Michael Rogers 2900 Ki Gee 2400 Hazel Philtanker 2200 Renske Ladwig 3600 Stephen Stiles 3200 John Seo 2700 Joshua Patel 2500 Trenna Rajs 3500 Curtis Davies 3100 Randall Matos 2600 Peter Vargas 2500 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- John Russell 14000 Karen Partners 13500 Alberto Errazuriz 12000 Gerald Cambrault 11000 Eleni Zlotkey 10500 Clara Vishney 10500 Lisa Ozer 11500 Ellen Abel 11000 Winston Taylor 3200 Jean Fleaur 3100 Martha Sullivan 2500 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Girard Geoni 2800 Nandita Sarchand 4200 Alexis Bull 4100 Julia Dellinger 3400 Anthony Cabrio 3000 Kelly Chung 3800 Jennifer Dilly 3600 Timothy Gates 2900 Randall Perkins 2500 Sarah Bell 4000 Britney Everett 3900 FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Samuel McCain 3200 Vance Jones 2800 Alana Walsh 3100 Kevin Feeney 3000 Donald OConnell 2600 Douglas Grant 2600 Jennifer Whalen 4400 Michael Hartstein 13000 Shelley Higgins 12008 64 rows selected. SQL> l 1 select first_name,last_name,salary 2 from employees 3* where salary not between 5000 and 10000 SQL> SQL> --40 50 vey 60 NOLU BÖLÜM DISINDA CALISANLARIN ISIM SOYISIM VE BOLUMLERINI GORUNTULEYELIM SQL> select first_name,last_name,department_id 2 from employees 3 where department_id not in(40,50,60); FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Ellen Abel 80 Sundar Ande 80 Hermann Baer 70 Shelli Baida 30 Amit Banda 80 Elizabeth Bates 80 David Bernstein 80 Harrison Bloom 80 Gerald Cambrault 80 Nanette Cambrault 80 John Chen 100 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Karen Colmenares 30 Lex De Haan 90 Louise Doran 80 Alberto Errazuriz 80 Daniel Faviet 100 Pat Fay 20 Tayler Fox 80 William Gietz 110 Nancy Greenberg 100 Danielle Greene 80 Peter Hall 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Michael Hartstein 20 Shelley Higgins 110 Guy Himuro 30 Alyssa Hutton 80 Charles Johnson 80 Alexander Khoo 30 Janette King 80 Steven King 90 Neena Kochhar 90 Sundita Kumar 80 David Lee 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Jack Livingston 80 Mattea Marvins 80 Allan McEwen 80 Christopher Olsen 80 Lisa Ozer 80 Karen Partners 80 Luis Popp 100 Den Raphaely 30 John Russell 80 Ismael Sciarra 100 Sarath Sewall 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Lindsey Smith 80 William Smith 80 Patrick Sully 80 Jonathon Taylor 80 Sigal Tobias 30 Peter Tucker 80 Oliver Tuvault 80 Jose Manuel Urman 100 Clara Vishney 80 Jennifer Whalen 10 Eleni Zlotkey 80 55 rows selected. SQL> l 1 select first_name,last_name,department_id 2 from employees 3* where department_id not in(40,50,60) SQL> SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 or department_id<>50 or department_id<>60 SQL> r 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 or department_id<>50 or department_id<>60 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Ellen Abel 80 Sundar Ande 80 Mozhe Atkinson 50 David Austin 60 Hermann Baer 70 Shelli Baida 30 Amit Banda 80 Elizabeth Bates 80 Sarah Bell 50 David Bernstein 80 Laura Bissot 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Harrison Bloom 80 Alexis Bull 50 Anthony Cabrio 50 Gerald Cambrault 80 Nanette Cambrault 80 John Chen 100 Kelly Chung 50 Karen Colmenares 30 Curtis Davies 50 Lex De Haan 90 Julia Dellinger 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Jennifer Dilly 50 Louise Doran 80 Bruce Ernst 60 Alberto Errazuriz 80 Britney Everett 50 Daniel Faviet 100 Pat Fay 20 Kevin Feeney 50 Jean Fleaur 50 Tayler Fox 80 Adam Fripp 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Timothy Gates 50 Ki Gee 50 Girard Geoni 50 William Gietz 110 Douglas Grant 50 Nancy Greenberg 100 Danielle Greene 80 Peter Hall 80 Michael Hartstein 20 Shelley Higgins 110 Guy Himuro 30 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Alexander Hunold 60 Alyssa Hutton 80 Charles Johnson 80 Vance Jones 50 Payam Kaufling 50 Alexander Khoo 30 Janette King 80 Steven King 90 Neena Kochhar 90 Sundita Kumar 80 Renske Ladwig 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- James Landry 50 David Lee 80 Jack Livingston 80 Diana Lorentz 60 Jason Mallin 50 Steven Markle 50 James Marlow 50 Mattea Marvins 80 Randall Matos 50 Susan Mavris 40 Samuel McCain 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Allan McEwen 80 Irene Mikkilineni 50 Kevin Mourgos 50 Julia Nayer 50 Donald OConnell 50 Christopher Olsen 80 TJ Olson 50 Lisa Ozer 80 Karen Partners 80 Valli Pataballa 60 Joshua Patel 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Randall Perkins 50 Hazel Philtanker 50 Luis Popp 100 Trenna Rajs 50 Den Raphaely 30 Michael Rogers 50 John Russell 80 Nandita Sarchand 50 Ismael Sciarra 100 John Seo 50 Sarath Sewall 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Lindsey Smith 80 William Smith 80 Stephen Stiles 50 Martha Sullivan 50 Patrick Sully 80 Jonathon Taylor 80 Winston Taylor 50 Sigal Tobias 30 Peter Tucker 80 Oliver Tuvault 80 Jose Manuel Urman 100 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Peter Vargas 50 Clara Vishney 80 Shanta Vollman 50 Alana Walsh 50 Matthew Weiss 50 Jennifer Whalen 10 Eleni Zlotkey 80 106 rows selected. SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 or department_id!=50 or department_id!=60 SQL> r 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 or department_id!=50 or department_id!=60 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Ellen Abel 80 Sundar Ande 80 Mozhe Atkinson 50 David Austin 60 Hermann Baer 70 Shelli Baida 30 Amit Banda 80 Elizabeth Bates 80 Sarah Bell 50 David Bernstein 80 Laura Bissot 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Harrison Bloom 80 Alexis Bull 50 Anthony Cabrio 50 Gerald Cambrault 80 Nanette Cambrault 80 John Chen 100 Kelly Chung 50 Karen Colmenares 30 Curtis Davies 50 Lex De Haan 90 Julia Dellinger 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Jennifer Dilly 50 Louise Doran 80 Bruce Ernst 60 Alberto Errazuriz 80 Britney Everett 50 Daniel Faviet 100 Pat Fay 20 Kevin Feeney 50 Jean Fleaur 50 Tayler Fox 80 Adam Fripp 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Timothy Gates 50 Ki Gee 50 Girard Geoni 50 William Gietz 110 Douglas Grant 50 Nancy Greenberg 100 Danielle Greene 80 Peter Hall 80 Michael Hartstein 20 Shelley Higgins 110 Guy Himuro 30 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Alexander Hunold 60 Alyssa Hutton 80 Charles Johnson 80 Vance Jones 50 Payam Kaufling 50 Alexander Khoo 30 Janette King 80 Steven King 90 Neena Kochhar 90 Sundita Kumar 80 Renske Ladwig 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- James Landry 50 David Lee 80 Jack Livingston 80 Diana Lorentz 60 Jason Mallin 50 Steven Markle 50 James Marlow 50 Mattea Marvins 80 Randall Matos 50 Susan Mavris 40 Samuel McCain 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Allan McEwen 80 Irene Mikkilineni 50 Kevin Mourgos 50 Julia Nayer 50 Donald OConnell 50 Christopher Olsen 80 TJ Olson 50 Lisa Ozer 80 Karen Partners 80 Valli Pataballa 60 Joshua Patel 50 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Randall Perkins 50 Hazel Philtanker 50 Luis Popp 100 Trenna Rajs 50 Den Raphaely 30 Michael Rogers 50 John Russell 80 Nandita Sarchand 50 Ismael Sciarra 100 John Seo 50 Sarath Sewall 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Lindsey Smith 80 William Smith 80 Stephen Stiles 50 Martha Sullivan 50 Patrick Sully 80 Jonathon Taylor 80 Winston Taylor 50 Sigal Tobias 30 Peter Tucker 80 Oliver Tuvault 80 Jose Manuel Urman 100 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Peter Vargas 50 Clara Vishney 80 Shanta Vollman 50 Alana Walsh 50 Matthew Weiss 50 Jennifer Whalen 10 Eleni Zlotkey 80 106 rows selected. SQL> l 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 or department_id!=50 or department_id!=60 SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 or department_id!=50 or department_id!=60 SQL> ed Wrote file afiedt.buf 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 and department_id!=50 and department_id!=60 SQL> r 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 and department_id!=50 and department_id!=60 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Ellen Abel 80 Sundar Ande 80 Hermann Baer 70 Shelli Baida 30 Amit Banda 80 Elizabeth Bates 80 David Bernstein 80 Harrison Bloom 80 Gerald Cambrault 80 Nanette Cambrault 80 John Chen 100 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Karen Colmenares 30 Lex De Haan 90 Louise Doran 80 Alberto Errazuriz 80 Daniel Faviet 100 Pat Fay 20 Tayler Fox 80 William Gietz 110 Nancy Greenberg 100 Danielle Greene 80 Peter Hall 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Michael Hartstein 20 Shelley Higgins 110 Guy Himuro 30 Alyssa Hutton 80 Charles Johnson 80 Alexander Khoo 30 Janette King 80 Steven King 90 Neena Kochhar 90 Sundita Kumar 80 David Lee 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Jack Livingston 80 Mattea Marvins 80 Allan McEwen 80 Christopher Olsen 80 Lisa Ozer 80 Karen Partners 80 Luis Popp 100 Den Raphaely 30 John Russell 80 Ismael Sciarra 100 Sarath Sewall 80 FIRST_NAME LAST_NAME DEPARTMENT_ID -------------------- ------------------------- ------------- Lindsey Smith 80 William Smith 80 Patrick Sully 80 Jonathon Taylor 80 Sigal Tobias 30 Peter Tucker 80 Oliver Tuvault 80 Jose Manuel Urman 100 Clara Vishney 80 Jennifer Whalen 10 Eleni Zlotkey 80 55 rows selected. SQL> l 1 select first_name,last_name,department_id 2 from employees 3* where department_id!=40 and department_id!=50 and department_id!=60 SQL> spool off