BASIC SQL*PLUS :: SELECT STATEMENT
এই টিউটোরিয়ালে কি ভাবে ডেটাবেইজ এর টেবিল থেকে কলাম বাচাই করে রির্পোট দেখা যায় তা শিখানো হবে
বিস্তারিত ব্যাখা
ডেটাবেইজ এর টেবিলে থাকা কলাম সনাক্ত করে করে ডিসপ্লেতে রির্পোট দেখান জন্য select statement ব্যবহার করা হয় । আমরা এখন একটি select statement কি ভাবে কাজ করে তা শিখব নিচের সিনটেক্স ভাল ভাবে লক্ষ করুন
সিনটেক্স(SYNTAX)::
এটি BASIC SELECT STATEMENT CLAUSE SYNTAX :
SELECT * OR COLUMN FROM TABLE_NAME;
এই সাধারন সিনটেক্স হচ্ছে ডেটাবেইজ এর মুল SQL শিখার হাতিয়ার এটিকে ভাল ভাবে বুঝলে আপনি যে কোন SQL বুঝতে পারবেন । লক্ষ করুন
- SELECT – SELECT CLAUSE কোন কোন কলাম ডিসপ্লেতে দেখাবে যা বুঝচ্ছে
- * or column – * স্টেরিক দ্বারা টেবিলের সকল কলাম বুঝাচ্ছে কলাম দ্বারা Column দ্বারা নিদির্ষ্ট কলাম বুঝাচ্ছে
- FROM TABLE_NAME – FROM TABLE হচ্ছে ডেটাবেইজ এর TABLE এর নাম র্নিদেশ করছে কোন টেবিল থেকে রেকড ডিসপ্লে করবে
সহজ কথায় SELECT COLUMN দ্বারা টেবিলের কোন কোন কলাম DISPLAY করবে তা বুঝাচ্ছে এবং FROM TABLE দ্বারা টেবিলের নাম বুঝাচ্ছে আমারা নিচের উদাহরন দ্বারা আরো সহজে বুঝতে পারব
উদাহরণ- SELECT STATEMENT
নিচের select statement এর উদাহরণ ভাল ভাবে লক্ষ করুন
SQL> SELECT * FROM EMPLOYEES; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- ------------- 198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600 124 50 199 Douglas Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK 2600 124 50 200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400 101 10 201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000 100 20 202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000 201 20 203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500 101 40 204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 10000 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110 206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110 100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008 101 100 109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000 108 100 110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100 111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700 108 100 112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800 108 100 113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100 114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000 100 30 115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100 114 30 116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900 114 30 117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800 114 30 118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600 114 30 119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500 114 30 120 Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000 100 50 121 Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200 100 50 122 Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900 100 50 123 Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500 100 50 124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800 100 50 125 Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200 120 50 126 Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700 120 50 127 James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400 120 50 128 Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200 120 50 129 Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300 121 50 130 Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800 121 50 131 James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500 121 50 132 TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100 121 50 133 Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300 122 50 134 Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900 122 50 135 Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400 122 50 136 Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200 122 50 137 Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600 123 50 138 Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200 123 50 139 John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700 123 50 140 Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500 123 50 141 Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500 124 50 142 Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100 124 50 143 Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600 124 50 144 Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500 124 50 145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80 146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80 147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80 148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80 149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80 150 Peter Tucker PTUCKER 011.44.1344.129268 30-JAN-05 SA_REP 10000 .3 145 80 151 David Bernstein DBERNSTE 011.44.1344.345268 24-MAR-05 SA_REP 9500 .25 145 80 152 Peter Hall PHALL 011.44.1344.478968 20-AUG-05 SA_REP 9000 .25 145 80 153 Christopher Olsen COLSEN 011.44.1344.498718 30-MAR-06 SA_REP 8000 .2 145 80 154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09-DEC-06 SA_REP 7500 .2 145 80 155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80 156 Janette King JKING 011.44.1345.429268 30-JAN-04 SA_REP 10000 .35 146 80 157 Patrick Sully PSULLY 011.44.1345.929268 04-MAR-04 SA_REP 9500 .35 146 80 158 Allan McEwen AMCEWEN 011.44.1345.829268 01-AUG-04 SA_REP 9000 .35 146 80 159 Lindsey Smith LSMITH 011.44.1345.729268 10-MAR-05 SA_REP 8000 .3 146 80 160 Louise Doran LDORAN 011.44.1345.629268 15-DEC-05 SA_REP 7500 .3 146 80 161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV-06 SA_REP 7000 .25 146 80 162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV-05 SA_REP 10500 .25 147 80 163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR-07 SA_REP 9500 .15 147 80 164 Mattea Marvins MMARVINS 011.44.1346.329268 24-JAN-08 SA_REP 7200 .1 147 80 165 David Lee DLEE 011.44.1346.529268 23-FEB-08 SA_REP 6800 .1 147 80 166 Sundar Ande SANDE 011.44.1346.629268 24-MAR-08 SA_REP 6400 .1 147 80 167 Amit Banda ABANDA 011.44.1346.729268 21-APR-08 SA_REP 6200 .1 147 80 168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-05 SA_REP 11500 .25 148 80 169 Harrison Bloom HBLOOM 011.44.1343.829268 23-MAR-06 SA_REP 10000 .2 148 80 170 Tayler Fox TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2 148 80 171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80 172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR-07 SA_REP 7300 .15 148 80 173 Sundita Kumar SKUMAR 011.44.1343.329268 21-APR-08 SA_REP 6100 .1 148 80 174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-04 SA_REP 11000 .3 149 80 175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-MAR-05 SA_REP 8800 .25 149 80 176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80 177 Jack Livingston JLIVINGS 011.44.1644.429264 23-APR-06 SA_REP 8400 .2 149 80 178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-07 SA_REP 7000 .15 149 179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN-08 SA_REP 6200 .1 149 80 180 Winston Taylor WTAYLOR 650.507.9876 24-JAN-06 SH_CLERK 3200 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 23-FEB-06 SH_CLERK 3100 120 50 182 Martha Sullivan MSULLIVA 650.507.9878 21-JUN-07 SH_CLERK 2500 120 50 183 Girard Geoni GGEONI 650.507.9879 03-FEB-08 SH_CLERK 2800 120 50 184 Nandita Sarchand NSARCHAN 650.509.1876 27-JAN-04 SH_CLERK 4200 121 50 185 Alexis Bull ABULL 650.509.2876 20-FEB-05 SH_CLERK 4100 121 50 186 Julia Dellinger JDELLING 650.509.3876 24-JUN-06 SH_CLERK 3400 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 07-FEB-07 SH_CLERK 3000 121 50 188 Kelly Chung KCHUNG 650.505.1876 14-JUN-05 SH_CLERK 3800 122 50 189 Jennifer Dilly JDILLY 650.505.2876 13-AUG-05 SH_CLERK 3600 122 50 190 Timothy Gates TGATES 650.505.3876 11-JUL-06 SH_CLERK 2900 122 50 191 Randall Perkins RPERKINS 650.505.4876 19-DEC-07 SH_CLERK 2500 122 50 192 Sarah Bell SBELL 650.501.1876 04-FEB-04 SH_CLERK 4000 123 50 193 Britney Everett BEVERETT 650.501.2876 03-MAR-05 SH_CLERK 3900 123 50 194 Samuel McCain SMCCAIN 650.501.3876 01-JUL-06 SH_CLERK 3200 123 50 195 Vance Jones VJONES 650.501.4876 17-MAR-07 SH_CLERK 2800 123 50 196 Alana Walsh AWALSH 650.507.9811 24-APR-06 SH_CLERK 3100 124 50 197 Kevin Feeney KFEENEY 650.507.9822 23-MAY-06 SH_CLERK 3000 124 50 107 rows selected. SQL>
EMPLOYEES টেবিলের সকল কলাম সহ DISPLAY তে দেখানোর জন্য * চিহ ব্যবহার করা হয়েছে । আপনা যখন অন্য টেবিল দিয়ে প্র্যাটিস করবেন তখন শুরু মাত্র টেবিলের নাম বসিয়ে দিবেন
উদাহরন- SELECT STATEMENT
এখানে কলাম নিয়ে কি ভাবে রির্পোট দেখানো যা তা দেখানো হবে
SQL> SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,SALARY 2 FROM EMPLOYEES; EMPLOYEE_ID FIRST_NAME LAST_NAME PHONE_NUMBER SALARY ----------- -------------------- ------------------------- -------------------- ---------- 198 Donald OConnell 650.507.9833 2600 199 Douglas Grant 650.507.9844 2600 200 Jennifer Whalen 515.123.4444 4400 201 Michael Hartstein 515.123.5555 13000 202 Pat Fay 603.123.6666 6000 203 Susan Mavris 515.123.7777 6500 204 Hermann Baer 515.123.8888 10000 205 Shelley Higgins 515.123.8080 12008 206 William Gietz 515.123.8181 8300 100 Steven King 515.123.4567 24000 101 Neena Kochhar 515.123.4568 17000 102 Lex De Haan 515.123.4569 17000 103 Alexander Hunold 590.423.4567 9000 104 Bruce Ernst 590.423.4568 6000 105 David Austin 590.423.4569 4800 106 Valli Pataballa 590.423.4560 4800 107 Diana Lorentz 590.423.5567 4200 108 Nancy Greenberg 515.124.4569 12008 109 Daniel Faviet 515.124.4169 9000 110 John Chen 515.124.4269 8200 111 Ismael Sciarra 515.124.4369 7700 112 Jose Manuel Urman 515.124.4469 7800 113 Luis Popp 515.124.4567 6900 114 Den Raphaely 515.127.4561 11000 115 Alexander Khoo 515.127.4562 3100 116 Shelli Baida 515.127.4563 2900 117 Sigal Tobias 515.127.4564 2800 118 Guy Himuro 515.127.4565 2600 119 Karen Colmenares 515.127.4566 2500 120 Matthew Weiss 650.123.1234 8000 121 Adam Fripp 650.123.2234 8200 122 Payam Kaufling 650.123.3234 7900 123 Shanta Vollman 650.123.4234 6500 124 Kevin Mourgos 650.123.5234 5800 125 Julia Nayer 650.124.1214 3200 126 Irene Mikkilineni 650.124.1224 2700 127 James Landry 650.124.1334 2400 128 Steven Markle 650.124.1434 2200 129 Laura Bissot 650.124.5234 3300 130 Mozhe Atkinson 650.124.6234 2800 131 James Marlow 650.124.7234 2500 132 TJ Olson 650.124.8234 2100 133 Jason Mallin 650.127.1934 3300 134 Michael Rogers 650.127.1834 2900 135 Ki Gee 650.127.1734 2400 136 Hazel Philtanker 650.127.1634 2200 137 Renske Ladwig 650.121.1234 3600 138 Stephen Stiles 650.121.2034 3200 139 John Seo 650.121.2019 2700 140 Joshua Patel 650.121.1834 2500 141 Trenna Rajs 650.121.8009 3500 142 Curtis Davies 650.121.2994 3100 143 Randall Matos 650.121.2874 2600 144 Peter Vargas 650.121.2004 2500 145 John Russell 011.44.1344.429268 14000 146 Karen Partners 011.44.1344.467268 13500 147 Alberto Errazuriz 011.44.1344.429278 12000 148 Gerald Cambrault 011.44.1344.619268 11000 149 Eleni Zlotkey 011.44.1344.429018 10500 150 Peter Tucker 011.44.1344.129268 10000 151 David Bernstein 011.44.1344.345268 9500 152 Peter Hall 011.44.1344.478968 9000 153 Christopher Olsen 011.44.1344.498718 8000 154 Nanette Cambrault 011.44.1344.987668 7500 155 Oliver Tuvault 011.44.1344.486508 7000 156 Janette King 011.44.1345.429268 10000 157 Patrick Sully 011.44.1345.929268 9500 158 Allan McEwen 011.44.1345.829268 9000 159 Lindsey Smith 011.44.1345.729268 8000 160 Louise Doran 011.44.1345.629268 7500 161 Sarath Sewall 011.44.1345.529268 7000 162 Clara Vishney 011.44.1346.129268 10500 163 Danielle Greene 011.44.1346.229268 9500 164 Mattea Marvins 011.44.1346.329268 7200 165 David Lee 011.44.1346.529268 6800 166 Sundar Ande 011.44.1346.629268 6400 167 Amit Banda 011.44.1346.729268 6200 168 Lisa Ozer 011.44.1343.929268 11500 169 Harrison Bloom 011.44.1343.829268 10000 170 Tayler Fox 011.44.1343.729268 9600 171 William Smith 011.44.1343.629268 7400 172 Elizabeth Bates 011.44.1343.529268 7300 173 Sundita Kumar 011.44.1343.329268 6100 174 Ellen Abel 011.44.1644.429267 11000 175 Alyssa Hutton 011.44.1644.429266 8800 176 Jonathon Taylor 011.44.1644.429265 8600 177 Jack Livingston 011.44.1644.429264 8400 178 Kimberely Grant 011.44.1644.429263 7000 179 Charles Johnson 011.44.1644.429262 6200 180 Winston Taylor 650.507.9876 3200 181 Jean Fleaur 650.507.9877 3100 182 Martha Sullivan 650.507.9878 2500 183 Girard Geoni 650.507.9879 2800 184 Nandita Sarchand 650.509.1876 4200 185 Alexis Bull 650.509.2876 4100 186 Julia Dellinger 650.509.3876 3400 187 Anthony Cabrio 650.509.4876 3000 188 Kelly Chung 650.505.1876 3800 189 Jennifer Dilly 650.505.2876 3600 190 Timothy Gates 650.505.3876 2900 191 Randall Perkins 650.505.4876 2500 192 Sarah Bell 650.501.1876 4000 193 Britney Everett 650.501.2876 3900 194 Samuel McCain 650.501.3876 3200 195 Vance Jones 650.501.4876 2800 196 Alana Walsh 650.507.9811 3100 197 Kevin Feeney 650.507.9822 3000 107 rows selected. SQL>
এখানে নির্দিষ্ট কলাম উল্লেখ করে রির্পোট দেখানো হয়েছে কলাম ১টি,২টি,৩টি বা নিজের চাহিদা মত রির্পোট দেখানো যায়