.  SQL.  . ORACLE
  


      SQL         .        ,      .      .





 

.  SQL.  . ORACLE








,  !

,    ,   SQL     . ,   ,    .  ,      ,        ,        . , ,     -         ,  SQL      .

  ,            SQL   .       .   .       ,     ,                 ,    !         ,     ,   , . ,    ,         ?      , ,      !  ,                  ,         !    SQL!  ,                   ,    ,          SQL       ,                .   ,   ,  , ,   !        !   !          ,    .      .

     ! 

()

    SQL:   ,     .    : ,  ,  CRM  ERP     ,   -,      1     .       .   :        .  SQL    (!)     !  ,        ,       ?     , , ,   ,   SQL  .

     : SQL   ,            .        ,      , , .     !

    :     ,   SQL. ,            ().     ORACLE    ( ),              .       .    15    .            ,    .  ,    ,         .     .    .        - ,  , , ! , ,      .    !       - ,       .             ,     ,       .   ,      !       .          ,   ,   .  ,   ,   !




 









       ,     ,   ,     .      .

   .         , .          .       .

    SQL.  . ORACLE,    15 .

   ,      ,  ,   .      2005 .        ,    ,            . ,   .  2006  2008          .  2008   2017     -: Integrator IT  DiaSoft,            .  2018  2022       Status Pro GmbH, Orga-Soft GmbH  MPS-Solutions  .

 ,   ,            , Youtube  ..

Telegram,    SQL https://t.me/sql_oracle_databases (https://t.me/sql_oracle_databases)

Youtube https://youtube.com/c/PrimeSoft (https://youtube.com/c/PrimeSoft)

.https://zen.yandex.ru/iliahohlov (https://zen.yandex.ru/iliahohlov)




1.  


,        (  ).    ,  , ,   ,   (  )    . ,  ,  ,       .








    ?      !   .        , , ,    (, , ),  ()    .    !

,     ,         ,      ().   ()    ()   .   ( )  .








          (      ),  ,    .        ().

         .          ,       ,            .

       :








       .         :








    , ?       ID, NAME  BIRTHDATE.

  ID     .      .      .

  NAME     () .  ,         ,   .      ,    ,       ( )   ID.

  BIRTHDATE     .

       .         .

    .     ,   ,     SELECT,    . , , ,       ,  :     ?    :       (     ).            .         SQL,       .

   ,          ()  ,    (Primary Key,   PK).   ,    ( )  ID.








    (  )   (        )   ,             (   ). ,     ,          . ,               ,     ,      ,     .

  ,          (,       ,       ),    .

,            .       () .         ( )!  .              .           .         ,   .

 ,   ,         :      ,          .








  ID_FILIAL   ,   .  ,    PERIOD    ,    .    AVG_PERS_COUNT, SUM_IN  SUM_OUT    ,    , .

  ,   2018 ,      .         2018.








      ID_FILIAL,   ,   . ,        .    ,       PERIOD   !   ID_FILIAL = 1  PERIOD =  2018    .      ID_FILIAL = 3  PERIOD =  2018.

    ,   ,             ,     ID_FILIAL  PERIOD  .                ,   .  ,     ,  ,    :  ORACLE (  ,      ORACLE)  ,            ID_FILIAL  PERIOD.  ,             :         ID_FILIAL = 1    2019 (),           ,    .      ,       .   ,            ,    ,     !

    ,        (Foreign Key,   FK).

 ,     ,   ,     .

     4 :  ID      ,  ID_PERS   ,  ,  NOMER        MARKA   .








     ID     .    ID_PERS    ,       ID.  (  ),        ,    ( foreign key,  FK).    , Foreign key      ,    ,      ,  .  ?

   ,  Audi A4     1,    .   BMW X3  Ford Mondeo     . Fiat Panda     ..         ,           (  ),     ID_PERS   4.

       ID_PERS    ,     ,          ,       ID.     ,       ID_PERS     .       ,    ,  .       .

      ID_PERS     .          :   ,     ,     ..       ,       ,     . ,   ,  ,     ,    ,    .                 : ,   ..

 ,         ,       .  !     .      ,   ,            ,     .      .      .

,                 ,    ..      ( . relations  ).

       .    ,   .




2.   SQL


     DML  ?     ,  :      !

   SQL   4 :

DML (Data Manipulation Language)    .     ,     ,   .     :    (),        (,      ,      ),          .       ,      MERGE.       , ,            ,  MERGE  ;

DDL (Data Definition Language)    .         ,        .       DDL:       ,    ;

TCL (Transaction Control Language)    ;

DCL (Data Control Language)      .

   DML  : SELECT  /   /, INSERT      , UPDATE  ,    ,  DELETE     ,   MERGE  /   .

   DDL  : CREATE       ; ALTER     , ,  ,      ,    ; DROP      , ,  .    ,    .








   TCL     : COMMIT  ROLLBACK.    ,   .             .

      DCL  : GRANT            ,    ; REVOKE            ,   . ,    ,            ,   . ,   ,    , ,      ,  ,  .        ,            ,           .




  1


      ,       SQL      ,            .        :

1.   DML  ?

2.             .   SQL   ? (  , ,  UPDATE  DELETE,  ).

3.            ,   .     !      .   SQL  ?

4.                 .          .   SQL  ?

5.     ,       .    SQL?

6.           ,         .   SQL?

7.             .   SQL          ?

8.       .   SQL  ?

9.         .   SQL?

10.      .          .   SQL  ,       SELECT    ?

      .




    1


1.  DML  ?

:  DML: SELECT, INSERT, UPDATE, DELETE  MERGE.



2.            .   SQL   ? (  , ,  UPDATE  DELETE,  ).

:  INSERT.



3.           ,   .     !      .   SQL  ?

:  UPDATE.



4.                .          .   SQL  ?

:  ALTER.



5.    ,         .    SQL?

:  DELETE.



6.          ,         .   SQL?

: UPDATE,        ,          ,   .



7.            .   SQL          ?

:  CREATE.



8.      .   SQL  ?

:  SELECT.



9.        .   SQL?

:  SELECT.



10.     .            .   SQL  ,        SELECT    ?

:  GRANT.




3.  SELECT


SELECT ( . )               .               (  ,        ).    , , .

SELECT        SQL.         (,       ,       ,       ),         (,         /   ,           ).           SELECT.

  SELECT  .        :








,      ,    SELECT,      ( ),   FROM    .

        ,      .      , ,      ,      .         SQL.

      :








       Persons.     ID  Name.   :








      :








   SQL       ( ),       ORDER BY ( .  ):








     ,      (!)      ,  ,         ,      .      WHERE.    WHERE     () ,    .     SELECT:








 ORDER BY      SQL!

              (       ),   ,     FROM    ,         JOIN:








 ,  ,  ,       ?!          !

     .      SELECT   .

         .

            ,   SELECT   GROUP BY ( .  )     ,    .               .

,     , ,       ,           .   ,         500.000 .

   , ,          .   ,   ,   WHERE. ,      ,     ,   ,         500.000 .               500.000 . ,        (         ,      ),   ,   ,   ,    .

           ,                      .

,         ,    ,       ,     .     .             .    , ,         100.000 . ,      ,   1.200.000  (100.000 ? 12 ).

       ,     .      ,   ,     .

  GROUP BY   SELECT:








        ,   ,    ,         , ,   ,         .    .             .          ,    HAVING  GROUP BY.

 ,                  (     ,        ), ,        ( )    ,    HAVING.

      :    WHERE  HAVING?     : WHERE       () (  ,         ),  HAVING      .

   ,      ,       ,        .

,        ,    , , .      .     ,       500.000 .     WHERE,         ,     ,     GROUP BY             HAVING,     (,   )      .

HAVING    GROUP BY:








       SELECT.    ,      .         SELECT  FROM.      WHERE     ORBER BY. ,    ,      .

,      ,  ,        ,   . ,   .

 :  MS SQL Server  MySQL  FROM     ,         .        .




4.    





4.1.       


 ,         ORACLE       .  ,     SQL Developer,          ORACLE.   ,         ,  ,  .   ,       () .       !

       () !

        !      .        ,    .  ,       .        .      ,     SQL,    .   ,        SQL.          ,        , ,   !          , ,   ,   ,        .

          .      .      ,       . ,    .    ,     ,   .

         SELECT.    ,    ,           ,       ! ,   ,    SQL.

 ,     Persons,       ,     ,    .       NAME,      BIRTHDATE       FilialID:








    :








 ,      .  ,  ,     .      SQL.    ,   ORACLE        ,           (    SQL Developer)     .       .

  21  (21 ).      ,        .




4.2.     .   WHERE


  ,     ,    FilialID  2:








    ,       4 !








 ORACLE,   SELECT,         Persons,    FilialID   !

 ,   ,    :








  ,   SQL        ,      <>,   !=.

   ,     :








  ( 17 ,      10):











4.3.  .  ORDER BY


           .     ORDER BY.








  ORDER BY ( .  )   NAME,            .       :








 ,   ()    .  ,              , , ,    ,    ORDER BY   BIRTHDATE.

 ,   ,     ,       ,    .              DESC.  !

   ,        :








:








      ,    .   !      DESC.   DESC  ASC.








ASC  DESC      ORACLE,   ,      .  ASC    ,    ,       ,  DESC !     ASC ,   ,     .      . ,      ,     ,          ,   ,     ,      ASC,     ,   !  SQL,    ,    .  ,   ,  .      .

    SQL,      ,     .         ,       ,   .




4.4.     .  AND  OR.  


 ,        WHERE.  ,    2  3,   .  ,       Persons,     FilialID   2  3.      FilialID = 2    !  3   !








:








   ,     SQL,    :








  ,     ?   ,   ORACLE (  )    ,   . ORACLE,   , :        FilialID     .    ,   ?!        ?      :

      .  :

, ?

!

 .      :

, ?!

!

 ?

  ?

      (,   OR) !  :








 SQL ,   ,   OR.           ,   ,   .  ,    . ORACLE,     (Persons)         ,     .     2    FilialID.  ,     FilialID  3?  ,    ,    .

     ,    .  ,       :








  Persons   ,     FilialID   1         DepartamentID   2. :








      AND  OR:








   ,     :   ,    1    2  3,   ,            3      .   , OR   ,  AND,      :     1       ,   ,      .   SQL   ,   ,     . ,          2  3,   :








 :








             SELECT,     :











5.  





5.1.     ORACLE


   ORACLE             ,  ,          .              , ,   .

 ,        SQL,           ORACLE,  SQL Developer     ,         !   !     .

    -       ORACLE        ,                   : https://prime-soft.biz/how-to-install (https://prime-soft.biz/how-to-install)

        ORACLE   , ,    ,     .   !

        ,      ,         SQL,    !   .

    https://oracle.com (https://oracle.com)           (       ).








    Oracle Database.

    Download Oracle Database 19c ( Oracle  19c),      .       Oracle,       .            : 32  64.        , 19.3, 18   12. ,  ,           Oracle. ,       . ,     Windows 10 x64, ,   , ,  Oracle 19.3.      Zip    .     ,    I reviewed and accept the Oracle License Agreement      ORACLE      .   .








  ,      .     ORACLE,         ORACLE.   ,  ORACLE      .  ,       ,  ,  ,              Oracle!        .       ,        ,    .

 ,   Oracle       zip.    .    ,          ,      . : D:\Downloads\ORACLE  D:\ORACLE.

    ,      Oracle. , ,    .    .      ʻ   ,        .       ,    , : MariaPC  Andry.

 ,    ,   setup.exe   .      ,     ,          :    .

  ,    Oracle.       Create and configure a single database (    ).        Next ().








         Desktop class  Server class   .      ,       . Server class    ,         .  Next.

    Use Virtual Account   Next.

  4      Oracle.








       Browse ().       Oracle    ,     (         ). ,          ,   .      : D:\ORCL  C:\ORACLE, , , D:\OracleBase.   ORACLE    : D:\ .

      Oracle   ,           .

   Character set ( )    Unicode AL32UTF8.

   Global database name     .  , , work, study, mybase        orcl.

        ORACLE.   .           ,  123,    Confirm    .  Next  ,    ORACLE    ORACLE. ,      .         ,   ORACLE  . ,            ,           ,   Yes.








   Oracle          ( ) ,         ,  Oracle    . ,  ,    .   ,     Ignore All (  )     Next.

 ,   ,  ,    .    Install ().

         , ,    .       .

     . !                   Oracle!




5.2.   SQL Developer     


 SQL Developer      Oracle       Oracle.    ,                 .  ,         ,    (, ),    SQL .         ,             Oracle.         ,       : PL/SQL Developer  TOAD.

    Oracle,  SQL Developer          . ,     Oracle  12,   SQL Developer   .       .

,    Oracle  , ,  ,  SQL Developer   .    .      . ?    !     Oracle: https://www.oracle.com/tools/downloads/sqldevdownloads.html (https://www.oracle.com/tools/downloads/sqldev-downloads.html)       .       Windows 10,      Download  .          .  zip,       .

       ,    SQL Developer!   ,            .  ,  SQL Developer  ,    .     ,     ,      .    ,      .








    :

Connection name ( )     , ,  ;

Username ( )      (. );

Password ()     .

       SYSTEM (  )  ,      .

    Save password ( ), ,  ,       ,   .








     ORACLE. ,     ,   : SYSTEM, SYS  SYSMAN.               ()     .    .       ,     ,         .        ,    , . , ,          .      .  :

Connection type ( )   Basic;

Role ()  ,        ,  default (  );

Hostname ( )       ,     .   ,        ,   localhost (   );

Port ()    (     ,    ,   ,     ,             )     1521,         ;

SID  Service name             ,     .     orcl.

 ,     ,   Test,   .      Status: Success (: ).    Save (),        Connect ().

           ,           ORACLE!       SQL Developer,                   .

          .











5.3.    


,    Connect      ,  ,   .

,       ,       ,    (). ,     Tables.








 ,         SQL.                SQL!        . ,      SQL,      SQL          .

   ( ) SQL  (   ),       Tools ()  SQL Worksheet (SQL ). , , Alt+F10. ,   ,        SQL         OK.








       ,    SQL        , ,  ,    SQL        .          ,    , , .          .

  SQL , ,     .








        .     :

https://primesoft.biz/download/dbscript1.sql (https://prime-soft.biz/download/dbscript1.sql)

  File ()  Open ()       (   )    .         Run Script ( ).

       !








   Tables ()        .      ,      SQL Developer,        Tables ()   Refresh ().

   Tables ()            ,               .     ? ,       .  . ,   ()  (  )       ,     ,         .             . ,    ,    (, )   SYSTEM.  ,        .       ,            SQL.     !

  ORACLE,  SQL Developer,  (  )             !    !

     .  .       SQL,  ,    ,      ! ,   !




6.  IN, LIKE  BETWEEN


    (, , ,   ,   )   SQL  ,       . ,        ,     12, 23, 48, 49, 54       , ,             OR,    :








 ,     OPER_CODE     ,  ,   .     SELECT   ,       Oper.

 ,      ,    .








,    IN    .    .    ,    :








 :








    ,     NAME     .

 ,      ,     ,    LIKE (like      . ,   ,   ).








       ,    ,     NAME   .        ,        .        NAME,           .     .         ,  ,  NAME     ,      . :








      :








   ! ,    :








 ,          ,     NAME       ,      .   ?               !     ,       ,         .








      :








       ,      ,   LIKE     :








 ,     NAME    :








  %    LIKE       _.       (, , ). ,      ,          ,   ,         ,      ,       :








    PersonCars ( )   CARREGNUMBER        ,       %.    ,       :








,   LIKE,     , ,  ,    PersonCars   ,      ,      . .   ,    ,  ,  ,  .        ,        .      ,  ,    ,              !

 ,    ,        . ,    ,     .     ,       . ,    :








    Persons,     BIRTHDATE             .         .      ,     BETWEEN:








 ,   ?         .  ,      01.01.1980  31.12.1989.

   BETWEEN,       ,      ,    ,  AND    :








!         . ,   ,      SQL.            ?                 ,  ,        Oracle.    !        ,  ,         .     SQL, Oracle ,    ,   ,            ,      ().  ,         (),   ,      ,   .  Oracle   ,      .       ,          .            ,     ,      !

       ,     .




7. 





7.1  to_date


,  ,      SQL    (    ,   ,      ),   SQL,      .      . , ,       . ORACLE,        ,     .

,  SQL    : '01.03.1980'.   , ORACLE  ,        , ,  ,          .         ,   .  , .

  ,      ,       .     SQL   .       ORACLE     ,    ,     .    .           .

      '21.01.1980',       ,   ,    ,        .        ,    .         .

 ,      SQL,           ,     .    ,    .     Oracle  to_date.

  to_date          ,      () .








   to_date:








      Persons ,   80.

 ,       (      ),  10  2019 :








  (            ):








  PersonID  ,  .   DateOrder  ,    DishID    .




7.2.  to_char


    Oracle.      ,  to_char      .     :      ,     ;      (     ).      ,        .

     .          .  ,   , ,       .      to_char!








 :








   SELECT      NAME, BIRTHDATE    BIRTHDATE    to_char.                .         ,    ,   to_char          :








:








    ,      .

      ,     :








:








     :








 ,  ,       ,    :








 ,         ()   .   ,    : , ,  ( ),   ..  , ,     , ,  :








:








       ( ),         to_char.








      !     !  to_char       ,    .     ,        ,     .

    ,  ,   ,  , ,    .           .   , ,     .     ,       ,        ,  .                ,    .   ,   ,      to_char     .

   ,  ,     :








:








        ,         D (. delimiter  ).          0  9. 0   .             .     0. ,  0  50 . 9   .         .      20 ,    .       20  55 ,  ,   ,       ,   ,       ,      (   ).   9  9,  ,      ,        .            , ,    .

   .    ,  :








:








      .     .  3        G.        .




7.3.  to_number


 to_number   to_char     ,        . :








:











8.   NULL ( )


     !        !    CanteenDishes ( ) .    , ,        :








:








      CookID ( ).      ,  ,      .    CookID .        SQL Developer   (null).     ,   ,    .

,        ,      ,   ,     (CookID). ,         .     . , ,    (    ,  ,      ).             .

,     ,      ,      :








 ,     ,    .   ,     NULL       .    .

       NULL   CookID,    IS  IS NOT.   :








  , :








   ,     CookID  (NULL).     , ,  ,   CookID ,    :








   ,    CookID  ,   :








   CookID      .     (   ,     ),        CookID   0,    NULL.       IS  IS NOT.         CookID,  , ,  IS NULL  .

 NULL           ,      ,    NULL.         SQL      NULL:








     5.

 ,     ,          .   ,   ,      NULL  -    , NULL      ,   .      NULL = NULL    (false),          ,    ,       .




  1


1. ,    ,   .

2.  ,    ,    .      .

3.   .

4.    ,    80 .

5.   ,    1  2.

6.   ,    ,    01.01.1980.

7.   ,      Ȼ.

8.    ,      70  100 .    ,  .

9.    ,      ,   .      (  CookID   ).     .



   1   .




   1


1. ,    ,   .








2.  ,    ,    .      .








3.   .








4.    ,    80 .








5.   ,    1  2.








6.   ,    ,    01.01.1980.








7.   ,      Ȼ.








8.    ,      70  100 .    ,  .








9.    ,      ,   .      (  CookID   ).     .








        Telegram : https://t.me/sql_oracle_databases (https://t.me/sql_oracle_databases)




9.     JOIN





9.1.   .  


          .          .

   SELECT   ,  ,        .  ,     SQL,     ,         .    ,      .           !

 ,          , (     ),       ,           .           ,  ,         .

 ,   ,    ,     ,   SQL   () .   .




9.2. LEFT JOIN.   


   ,   ,    SQL  (  LEFT JOIN)   .   !

       ,    .   .        .                 (  ),       .        .   :








     ID     ,   (  -).     .    ID_PERS   ,    .     .  ,   ID_PERS   ,   .   ,  BMW X3  Ford Mondeo    .     ,    4,    .




  .


   .

   ,     (https://www.litres.ru/book/ilya-leonidovich-hohlov/samouchitel-kurs-sql-bazy-dannyh-oracle-69188986/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


