DB TABLE OPERATIONS

This post contains all database table operations- like select, insert, update, modify , collect ,  inner-join, left-outer-join, for all entries ....

******DB TABLE OPERATION-   SELECT - ENDSELECT ******

TYPES : BEGIN OF TAB,
         ROLL_NO TYPE ZEMP-REGD_NO,
         NAME TYPE ZEMP-NAME,
         ADDRESS TYPE ZEMP-ADDRESS,
         STATE TYPE ZEMP-STATE,
         COUNTRY TYPE  ZEMP-COUNTRY,
         END OF TAB.

   DATA : WA_TAB TYPE  TAB.
     SELECT REGD_NO
            NAME
            ADDRESS
            STATE
            COUNTRY FROM ZEMP INTO  WA_TAB.
     WRITE :/ WA_TAB-ROLL_NO,
              WA_TAB-NAME,
              WA_TAB-ADDRESS,
              WA_TAB-STATE,
              WA_TAB-COUNTRY.
  ENDSELECT.

****DB TABLE OPERATION-   SELECT *  STATEMENT********

  TYPES : BEGIN OF TAB.
         INCLUDE STRUCTURE ZEMP.
  TYPES : END OF TAB.

   DATA : IT_TAB TYPE TABLE OF TAB.
   DATA : WA_TAB TYPE  TAB.

   SELECT * FROM ZEMP INTO TABLE IT_TAB.

   LOOP AT IT_TAB INTO WA_TAB.
     WRITE :/ WA_TAB-REGD_NO,
              WA_TAB-NAME,
              WA_TAB-ADDRESS,
              WA_TAB-STATE,
              WA_TAB-COUNTRY,
              WA_TAB-MOBILE_NO,
              WA_TAB-BRANCH,
              WA_TAB-MARK,
              WA_TAB-GRADE.
     ENDLOOP. 

********** single record selection without condition *********
data wa type spfli.
select single carrid connid cityfrom cityto into corresponding fields
                                                    of wa from spfli.

if sy-subrc = 0.
write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
endif. 

********** single record selection with condition *********
data wa type spfli.
select single carrid connid cityfrom cityto into corresponding fields
                                 of wa from spfli where carrid = 'LH'.

if sy-subrc = 0.
write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
endif. 

***** single record selection with condition 'AND'******
data wa type spfli.
select single carrid connid cityfrom cityto into corresponding fields
              of wa from spfli where carrid = 'LH' and connid = '0400'.

if sy-subrc = 0.
write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
endif.

********multiple line selection without condition ***********
data : itab type standard table of spfli,
       wa like line of itab.

  select carrid connid cityfrom cityto into corresponding fields of
                                             table itab from spfli .

  loop at itab into wa.

  write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  endloop.

********multiple line selection with condition *************
data : itab type standard table of spfli,
       wa like line of itab.

  select carrid connid cityfrom cityto into corresponding fields of
                          table itab from spfli where carrid = 'LH'.

  loop at itab into wa.

  write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  endloop.

*********multiple line selection with condition 'AND'*****
data : itab type standard table of spfli,
       wa like line of itab.

  select carrid connid cityfrom cityto into corresponding fields of
       table itab from spfli where carrid = 'LH' and connid = '5002'.

  loop at itab into wa.

  write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  endloop.

******multiple line selection with condition 'OR'*****
data : itab type standard table of spfli,
       wa like line of itab.

  select carrid connid cityfrom cityto into corresponding fields of
          table itab from spfli where carrid = 'LH' or carrid = 'AA'.

  loop at itab into wa.

  write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  endloop.

******multiple line selection with condition 'OR'and 'AND'*****
data : itab type standard table of spfli,
       wa like line of itab.

  select carrid connid cityfrom cityto into corresponding fields of
  table itab from spfli where carrid = 'LH' or carrid = 'AA' and
                                               connid = '0064'.

  loop at itab into wa.

  write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  endloop.


*****multiple line selection with condition 'OR'and 'AND'*****
data : itab type standard table of spfli,
       wa like line of itab.

select carrid connid cityfrom cityto into corresponding fields of
     table itab from spfli where carrid = 'LH' and connid = '5002'
                              or carrid = 'AA' and connid = '0064'.

  loop at itab into wa.

  write :/ wa-carrid, wa-connid, wa-cityfrom, wa-cityto.
  endloop.

***reading all columns of a table  using workarea*********
data : wa type spfli.

select * from spfli into wa. " give condition if needed
write :/ sy-dbcnt, wa-carrid, wa-connid.
endselect.

***reading all columns of a table  using internal table*********
data : itab  type table of spfli,
       wa like line of itab.

select * from spfli into table itab. " give condition if needed

if sy-subrc = 0.
loop at itab into wa.
write :/ sy-dbcnt, wa-carrid, wa-connid.
endloop.
endif.

***********single field as target *************

data : avg type p decimals 2,
       total type p decimals 2.

select avg( luggweight ) sum( luggweight )
            into (avg, total) from sbook.

  if sy-subrc = 0.
  write :/ 'average luggweight = ', avg left-justified,
         / 'total luggweight =', total.

  endif.


*********single field as target with condition *********

data : avg type p decimals 2,
       total type p decimals 2.

select avg( luggweight ) sum( luggweight )
         into (avg, total) from sbook where carrid = 'LH'.

  if sy-subrc = 0.
  write :/ 'average luggweight = ', avg left-justified,
         / 'total luggweight =', total.
  endif.

******using aliases to calculate avg and sum ********

data : begin of result,
       average type p decimals 2,
       total   type p decimals 2,
       end of result.

 select avg( luggweight ) as average sum( luggweight ) as total
                                         into result from sbook.

 if sy-subrc = 0.
  write :/ 'average luggweight = ', result-average left-justified,
         / 'total luggweight =', result-total.
 endif.

**********distinct statement ****************

 data : wa type spfli,
        itab like table of wa.

select cityfrom cityto into corresponding fields of table itab from spfli.
select distinct cityfrom cityto into corresponding fields of table itab from spfli.
 

write :/ sy-dbcnt.
 

if sy-subrc = 0.
loop at itab into wa.
write :/  wa-cityfrom, wa-cityto.
endloop.
endif.

*********specifying columns dynamically**************

data : itab type table of  spfli,
        wa like line of itab.

data : line type char72,
       list like table of line.

line = 'CITYFROM '.
append line to list.
line = 'CITYTO '.
append line to list.

select distinct (list) into corresponding fields of table itab from
spfli.

if sy-subrc = 0.
  loop at itab into wa.
    write :/ wa-cityfrom, wa-cityto.
  endloop.
endif.

*******specifying columns(only one) dynamically  *******
PARAMETERS : P_CITYFR TYPE char72. " INPUT- CITYFROM CITYTO

DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.

SELECT DISTINCT (P_CITYFR) INTO CORRESPONDING FIELDS OF TABLE
ITAB FROM SPFLI.

IF SY-SUBRC = 0.
  LOOP AT ITAB INTO WA.
    WRITE :/ WA-CITYFROM , wa-cityto.
  ENDLOOP.
ENDIF.

********SPECIFYING SINGLE FIELD AS TARGET********

DATA : CARR TYPE SPFLI-CARRID,
       CONN TYPE SPFLI-CONNID.

 SELECT CARRID CONNID INTO (CARR,CONN) FROM SPFLI.

 WRITE :/ CARR, CONN.
 ENDSELECT.

****SPECIFYING TARGET SAME AS DBTABLE*****

DATA : SPFLI TYPE SPFLI.

* SELECT * FROM SPFLI INTO SPFLI.
SELECT * FROM SPFLI .
WRITE :/ SPFLI-CARRID, SPFLI-CONNID, SPFLI-CITYFROM, SPFLI-CITYTO.
ENDSELECT.

****SPECIFYING TARGET TABLE  SAME AS DBTABLE*****

DATA : SPFLI TYPE TABLE OF SPFLI,
       WA LIKE LINE OF SPFLI.
SELECT * FROM SPFLI INTO TABLE SPFLI.

LOOP AT SPFLI INTO WA.
WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
ENDLOOP.

**SPECIFYING TABLE NAME STATICALLY AND DYNAMICALLY ******
PARAMETERS : NAME TYPE DD02L-TABNAME.
DATA : ITAB TYPE TABLE OF SPFLI.
DATA : WA LIKE LINE OF ITAB.

SELECT * FROM (NAME) INTO TABLE ITAB. " ONLY WORKS FOR SPFLI TABLE AS
                                       " ITAB IS OF TYPE SPFLI

LOOP AT ITAB INTO WA.
WRITE :/ WA-CARRID, WA-CONNID.
ENDLOOP.

********RESTRICTING NUMBER OF LINES *****************
DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.

 SELECT * FROM SPFLI INTO TABLE ITAB UP TO 20 ROWS.

 LOOP AT ITAB INTO WA.
 WRITE :/ WA-MANDT,
          WA-CARRID,
          WA-CONNID,
          WA-CITYFROM,
          WA-CITYTO.
 ENDLOOP.

*********BYPASSING BUFFER ************

DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.
 SELECT * FROM SPFLI BYPASSING BUFFER INTO TABLE ITAB.
  LOOP AT ITAB INTO WA .
  WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
  ENDLOOP.

***********VALUES IN INTERVALS***************

DATA : ITAB TYPE TABLE OF SFLIGHT,
       WA LIKE LINE OF ITAB.

 SELECT * FROM SFLIGHT INTO TABLE ITAB WHERE SEATSOCC BETWEEN 100 AND
                                                              200.
 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-SEATSMAX, WA-SEATSOCC.
 ENDLOOP.


*******VALUES NOT IN INTERVALS******

DATA : ITAB TYPE TABLE OF SFLIGHT,
       WA LIKE LINE OF ITAB.

 SELECT * FROM SFLIGHT INTO TABLE ITAB WHERE SEATSOCC NOT BETWEEN 100
                                                              AND 200.
 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-SEATSMAX, WA-SEATSOCC.
 ENDLOOP.

*****COMAPIRING STRINGS************

DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.

 SELECT * FROM SPFLI INTO TABLE ITAB WHERE CITYFROM LIKE 'S%'.
                        " CITYFROM STARTS WITH 'S', %-WILDCARD

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP.

  REFRESH ITAB.

 SELECT * FROM SPFLI INTO TABLE ITAB WHERE CITYFROM LIKE '%R%'.
                           " CITYFROM CONTAINS  'R', %-WILDCARD

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP.

  REFRESH ITAB. 

 SELECT * FROM SPFLI INTO TABLE ITAB WHERE CITYFROM LIKE '%O'.
                      " CITYFROM ENDWITH WITH 'O', %-WILDCARD

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP.

 SELECT * FROM SPFLI INTO TABLE ITAB WHERE CITYFROM NOT LIKE '%O'.
                         " CITYFROM NOT ENDS WITH 'O', %-WILDCARD

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP.

  REFRESH ITAB. 

 SELECT * FROM SPFLI INTO TABLE ITAB WHERE CITYFROM  LIKE '_O%'.
                     " CITYFROM SECOND CHARAC IS 'O', %-WILDCARD

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP. 

********CHECKING LIST OF VALUES ****************
DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.

SELECT * FROM SPFLI INTO TABLE ITAB  WHERE CITYTO IN ('TOKYO','ROME').
* SELECT * FROM SPFLI INTO TABLE ITAB  WHERE CITYTO NOT IN ('TOKYO', 'ROME').

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP.

******CHECKING NULL VALUES ************

DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.

SELECT * FROM SPFLI INTO TABLE ITAB  WHERE CITYTO IS  NULL.
* SELECT * FROM SPFLI INTO TABLE ITAB  WHERE CITYTO IS NOT  NULL.

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP. 

*********NEGATING CONDITION **********
DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB.

SELECT * FROM SPFLI INTO TABLE ITAB  WHERE NOT CITYTO = 'SAN FRANCISCO'.

 WRITE :/ 'TOTAL RECORDS PROCESSED =',SY-DBCNT.
 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.
 ENDLOOP. 

**DB TABLE OPERATION- INSERT FROM WORKAREA TO DB TABLE*** 
   DATA : WA_TAB TYPE ZEMP.

   WA_TAB-REGD_NO = '106'.
   WA_TAB-NAME = 'RAM'.
   WA_TAB-ADDRESS = 'J PUR'.
   WA_TAB-STATE = 'DELHI'.
   WA_TAB-COUNTRY = 'USA'.
   INSERT INTO ZEMP_022 VALUES WA_TAB.

   WA_TAB-REGD_NO = '107'.
   WA_TAB-NAME = 'HARI'.
   WA_TAB-ADDRESS = 'JA PUR'.
   WA_TAB-STATE = 'DELHI'.
   WA_TAB-COUNTRY = 'USA'.
   INSERT INTO ZEMP_022 VALUES WA_TAB.

   IF SY-SUBRC = 0.
     WRITE :/ 'RECORD SUCCESSFULLY INSERTED.'.
   ENDIF. 

*******************************************
TABLES : ZEMP. " THIS STATEMENT ALSO CREATES A WORK AREA WITH SAME NAME AS THE DB TABLE
ZEMP-REGD_NO = '111'.
ZEMP-NAME = 'SSSS'.
ZEMP-ADDRESS = 'BANGA'.
ZEMP-STATE = 'KARN'.
ZEMP-COUNTRY = 'IND'.

INSERT INTO ZEMP VALUES ZEMP.
IF SY-SUBRC = 0.
  WRITE : 'SUCCESSFULLY INSERTED'.
 ENDIF.

*DB TABLE OPERATION- INSERT FROM INTERNAL TABLE TO DB TABLE*

 DATA : IT_TAB TYPE TABLE OF ZEMP,
        WA_TAB TYPE ZEMP.

WA_TAB-REGD_NO = '130'.
WA_TAB-NAME  = 'AJAY'.
WA_TAB-ADDRESS = 'K PUR'.
WA_TAB-STATE = 'DELHI'.
WA_TAB-COUNTRY = 'IND'.
APPEND WA_TAB TO IT_TAB.

WA_TAB-REGD_NO = '131'.
WA_TAB-NAME  = 'BIJAY'.
WA_TAB-ADDRESS = 'KR PUR'.
WA_TAB-STATE = 'UP'.
WA_TAB-COUNTRY = 'IND'.
APPEND WA_TAB TO IT_TAB.

WA_TAB-REGD_NO = '132'.
WA_TAB-NAME  = 'RAK'.
WA_TAB-ADDRESS = 'JAJ'.
WA_TAB-STATE = 'AP'.
WA_TAB-COUNTRY = 'IND'.
APPEND WA_TAB TO IT_TAB.

WA_TAB-REGD_NO = '134'.
WA_TAB-NAME  = 'HARI'.
WA_TAB-ADDRESS = 'MM PUR'.
WA_TAB-STATE = 'BIHAR'.
WA_TAB-COUNTRY = 'IND'.
APPEND WA_TAB TO IT_TAB.

INSERT  ZEMP FROM  TABLE IT_TAB.
IF SY-SUBRC = 0.
  WRITE :/ 'INSERTED'.

ENDIF.

*DB TABLE OPERATION- CLIENT SPECIFIC INSERT INTO DB TABLE*

data: wa_tab type zemp.
wa_tab-mandt = '400'.
wa_tab-regd_no = '152'.
wa_tab-name = 'raj'.
wa_tab-address = 'yash'.
wa_tab-state = 'wb'.
wa_tab-country = 'ind'.

insert into zemp client specified   values wa_tab .
 if sy-subrc = 0.
  write :/ 'client specific record insert successful'

 elseif sy-subrc = 4.
    write :/ 'failed'.
 endif.

 clear wa_tab.
 select single * into wa_tab from zemp client specified where mandt = '400'.
      if sy-subrc = 0.
        write :/ 'selection of record successful'.
        write :/ wa_tab-mandt,
                 wa_tab-regd_no,
                 wa_tab-name,
                 wa_tab-address,
                 wa_tab-state,
                 wa_tab-country.
      elseif sy-subrc = 4.

     endif.  

*****DB TABLE OPERATION- MODIFY STATEMENT**** 

data : wa_tab type zemp.
wa_tab-regd_no = '120'.
wa_tab-name = 'RAM'.
wa_tab-address = 'sale'.
wa_tab-state = 'pensyl'.
wa_tab-country = 'usa'.
modify zemp from wa_tab.
if sy-subrc = 0.
  write :/ 'modification successful'.

endif.

 ****DB TABLE OPERATION- DELETE  STATEMENT****** 

 DATA : WA_TAB TYPE ZEMP.
 DATA : it_TAB TYPE TABLE OF ZEMP.

WA_TAB-REGD_NO = '130'.
WA_TAB-NAME = 'HHHH'.
WA_TAB-ADDRESS = 'CTCT'.
WA_TAB-STATE = 'DELHI'.
WA_TAB-COUNTRY = 'IND'.
append wa_tab to it_tab.

WA_TAB-REGD_NO = '131'.
WA_TAB-NAME = 'HARI'.
WA_TAB-ADDRESS = 'CTCT'.
WA_TAB-STATE = 'DELHI'.
WA_TAB-COUNTRY = 'IND'.
append wa_tab to it_tab.

DELETE  ZEMP from table it_tab.
IF sy-subrc = 0.
write :/ ' records deleted'.
ENDIF.

** INNER JOIN ON SPFLI AND SFLIGHT TABLES**********
PARAMETERS : P_CARRID TYPE SPFLI-CARRID DEFAULT 'AA'.
PARAMETERS : P_CONNID TYPE SPFLI-CONNID DEFAULT '0017'.
TYPES : BEGIN OF TAB,
        CARRID TYPE SPFLI-CARRID,
        CONNID TYPE SPFLI-CONNID,
        COUNTRYFR TYPE SPFLI-COUNTRYFR,
        CITYFROM TYPE SPFLI-CITYFROM,
        AIRPFROM TYPE SPFLI-AIRPFROM,
        COUNTRYTO TYPE SPFLI-COUNTRYTO,
        CITYTO TYPE SPFLI-CITYTO,
        AIRPTO TYPE SPFLI-AIRPTO,
        DISTANCE TYPE SPFLI-DISTANCE,
        FLDATE TYPE SFLIGHT-FLDATE,
        PRICE TYPE SFLIGHT-PRICE,
        SEATSMAX TYPE SFLIGHT-SEATSMAX,
        SEATSOCC TYPE SFLIGHT-SEATSOCC,
      END OF TAB.

DATA : IT_FLIGHT TYPE TABLE OF TAB,
       WA_FLIGHT TYPE TAB.

SELECT SPFLI~CARRID
       SPFLI~CONNID
       SPFLI~COUNTRYFR
       SPFLI~CITYFROM
       SPFLI~AIRPFROM
       SPFLI~COUNTRYTO
       SPFLI~CITYTO
       SPFLI~AIRPTO
       SPFLI~DISTANCE
       SFLIGHT~FLDATE
       SFLIGHT~PRICE
       SFLIGHT~SEATSMAX
       SFLIGHT~SEATSOCC
       INTO TABLE IT_FLIGHT
       FROM SPFLI INNER JOIN SFLIGHT
       ON SPFLI~CARRID = SFLIGHT~CARRID AND SPFLI~CONNID = SFLIGHT~CONNID
       WHERE SPFLI~CARRID = P_CARRID AND SPFLI~CONNID = P_CONNID.

  LOOP AT IT_FLIGHT INTO WA_FLIGHT.
    WRITE :/ SY-TABIX,
             WA_FLIGHT-CARRID,
             WA_FLIGHT-CONNID,
             WA_FLIGHT-COUNTRYFR,
             WA_FLIGHT-CITYFROM ,

             WA_FLIGHT-COUNTRYTO,
             WA_FLIGHT-CITYTO,

             WA_FLIGHT-DISTANCE,
             WA_FLIGHT-FLDATE,
             WA_FLIGHT-PRICE,
             WA_FLIGHT-SEATSMAX,
             WA_FLIGHT-SEATSOCC.
    ENDLOOP. 

*********** LEFT OUTER  JOIN ******************** 

 PARAMETERS : P_CARRID TYPE SPFLI-CARRID DEFAULT 'AA'.
PARAMETERS : P_CONNID TYPE SPFLI-CONNID DEFAULT '0017'.
TYPES : BEGIN OF TAB,
        CARRID TYPE SPFLI-CARRID,
        CONNID TYPE SPFLI-CONNID,
        COUNTRYFR TYPE SPFLI-COUNTRYFR,
        CITYFROM TYPE SPFLI-CITYFROM,
        AIRPFROM TYPE SPFLI-AIRPFROM,
        COUNTRYTO TYPE SPFLI-COUNTRYTO,
        CITYTO TYPE SPFLI-CITYTO,
        AIRPTO TYPE SPFLI-AIRPTO,
        DISTANCE TYPE SPFLI-DISTANCE,
        FLDATE TYPE SFLIGHT-FLDATE,
        PRICE TYPE SFLIGHT-PRICE,
        SEATSMAX TYPE SFLIGHT-SEATSMAX,
        SEATSOCC TYPE SFLIGHT-SEATSOCC,
      END OF TAB.

DATA : IT_FLIGHT TYPE TABLE OF TAB,
       WA_FLIGHT TYPE TAB.

SELECT SPFLI~CARRID
       SPFLI~CONNID
       SPFLI~COUNTRYFR
       SPFLI~CITYFROM
       SPFLI~AIRPFROM
       SPFLI~COUNTRYTO
       SPFLI~CITYTO
       SPFLI~AIRPTO
       SPFLI~DISTANCE
       SFLIGHT~FLDATE
       SFLIGHT~PRICE
       SFLIGHT~SEATSMAX
       SFLIGHT~SEATSOCC
       INTO TABLE IT_FLIGHT
       FROM SPFLI LEFT OUTER JOIN SFLIGHT
       ON SPFLI~CARRID = SFLIGHT~CARRID AND SPFLI~CONNID = SFLIGHT~CONNID
       WHERE SPFLI~CARRID = P_CARRID AND SPFLI~CONNID = P_CONNID.

  LOOP AT IT_FLIGHT INTO WA_FLIGHT.
    WRITE :/ SY-TABIX,
             WA_FLIGHT-CARRID,
             WA_FLIGHT-CONNID,
             WA_FLIGHT-COUNTRYFR,
             WA_FLIGHT-CITYFROM ,

             WA_FLIGHT-COUNTRYTO,
             WA_FLIGHT-CITYTO,

             WA_FLIGHT-DISTANCE,
             WA_FLIGHT-FLDATE,
             WA_FLIGHT-PRICE,
             WA_FLIGHT-SEATSMAX,
             WA_FLIGHT-SEATSOCC.
    ENDLOOP. 

*******INNER JOIN OF THREE TABLES ***********

DATA : BEGIN OF WA,
       CARRID TYPE SPFLI-CARRID,
       CONNID TYPE SPFLI-CONNID,
       FLDATE TYPE SFLIGHT-FLDATE,
       SEATSOCC TYPE SFLIGHT-SEATSOCC,
       BOOKID TYPE SBOOK-BOOKID,
       END OF WA,
      ITAB LIKE SORTED TABLE OF  WA WITH UNIQUE KEY CARRID CONNID FLDATE BOOKID .

SELECT P~CARRID
       P~CONNID
       Q~FLDATE
       Q~SEATSOCC
       R~BOOKID
  INTO TABLE ITAB
  FROM ( ( SPFLI AS P INNER JOIN SFLIGHT AS Q ON P~CARRID = 

                                             Q~CARRID
                                             AND P~CONNID = Q~CONNID )
                      INNER JOIN SBOOK AS R ON  R~CARRID = Q~CARRID
                                            AND R~CONNID = Q~CONNID
                                            AND R~FLDATE = Q~FLDATE )
   WHERE P~CITYFROM = 'NEW YORK' AND P~CITYTO =

                                                          'SAN FRANCISCO'
                                 AND Q~SEATSMAX > Q~SEATSOCC
                                 AND Q~SEATSOCC < 350.
WRITE :/ SY-DBCNT.
LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-FLDATE, WA-SEATSOCC,WA-BOOKID.
ENDLOOP. 

******GROUP BY- SPECIFYING COLUMN STATICALLY********

*group by clause summarizes several lines  into a single line
data : BEGIN OF WA,
       carrid type sflight-carrid,
       minimum type p decimals 2,
       maximum type p decimals 2,
       END OF WA,
       ITAB LIKE TABLE OF WA.

  select carrid
         min( price )
         max( price )
         into   TABLE ITAB "(carrid, minimum,maximum)
         from sflight group by carrid .

     LOOP AT ITAB INTO WA.
    write : /  WA-carrid , WA-minimum , WA-maximum.
    ENDLOOP.
    endselect.

******HAVING CLAUSE WITH GROUP BY********

data : wa type sflight.
select CARRID CONNID into CORRESPONDING FIELDS OF WA from sflight
      where carrid = 'LH' OR CARRID = 'AA' GROUP BY CARRID CONNID
                                    HAVING SUM( SEATSOCC ) > 300.
WRITE :/ WA-CARRID, WA-CONNID.
ENDSELECT.

******SORTING - ORDRE BY COLUMN********

DATA : ITAB TYPE TABLE OF SPFLI,
       WA LIKE LINE OF ITAB. 
* SELECT * FROM SPFLI INTO TABLE  ITAB . 
 SELECT * FROM SPFLI INTO TABLE  ITAB ORDER BY PRIMARY KEY.
* SELECT * FROM SPFLI INTO TABLE ITAB ORDER BY CITYFROM  CITYTO. "DESCENDING.

 LOOP AT ITAB INTO WA.
 WRITE :/ WA-CARRID, WA-CONNID, WA-CITYFROM ,WA-CITYTO.
 ENDLOOP.

**** use of group by and order by clause *********

data : wa type sflight.

select carrid connid planetype seatsmax max( seatsocc )
        into (wa-carrid, wa-connid,wa-planetype, wa-seatsmax, wa-seatsocc)
        from sflight where carrid = 'AF'
        group by carrid connid planetype seatsmax order by carrid connid.
write :/ wa-carrid,
          wa-connid,
          wa-planetype,
          wa-seatsmax,
          wa-seatsocc.
 endselect. 

************(1) Sub Queries  ******************

data : name_tab type table of scarr-carrname,
       name like line of name_tab.

select carrname from scarr into table name_tab
where exists ( select *  from spfli where carrid = scarr~carrid and
                                          cityfrom = 'NEW YORK' ) .

if sy-subrc = 0.
  loop at name_tab into name.
    write :/ name.
  endloop.
endif. 

************(2) Sub Queries************

data : carr_id type spfli-carrid value 'AZ',
       conn_id type spfli-connid value '0555'.

data : city type sgeocity-city,
       lati type p decimals 2,
       longi type p decimals 2.

 select single city latitude longitude into (city , lati, longi)
 from sgeocity where city
 in ( select cityfrom from spfli where carrid = carr_id 

                                                  and connid = conn_id ).

write :/ city, lati, longi.