BASIC SQL*PLUS :: SELECT STATEMENT

posted in: SQL*PLUS | 0

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>

এখানে নির্দিষ্ট কলাম উল্লেখ করে রির্পোট দেখানো হয়েছে কলাম ১টি,২টি,৩টি বা নিজের চাহিদা মত রির্পোট দেখানো যায়