Postgres SQL and PLPGQL, PLTCL & PLPERL (1 of 3)

  1. Discuss the Theoretical Aspects of a Relational Database.

    2. See documentation on Oracle side (same principle).

  2. Describe the Postgres Implementation of the RDBMS and ORDBMS.

    3. It operates under a client-server arquitecture , where the server side is composed of:

      1. A Database system which consists of a directory structure, database files where data will live, shared catalog tables,etc. (Analoguos to Oracle would be (in the same order): OFA (Oracle Flexible Arcquitecture),Data Files,Data Dictionary), it is within this Database system that every database will reside.
      2. A process called postmaster which manages connections to the Postgres server process. In contrast to Oracle, postmaster acts like a dispatcher in that it manages connections for incoming clients, and passes them on to a server process.
      3. Server Process: These server process are the glue between the postmaster process and the Database system , although this relation would be stretched, it is lika an Oracle Instance.

      The client side can be composed of any of the following:

        1. An interactive environment to call SQL/Database commands, Postgres provides an environment called psql (similar to SQL*Plus) which is able to manipulate information in a database from the Database system.
        2. A driver implmentation (ODBC) designed to communicate and manipulate data in a database, through a Web-Server application or other means.

      It should be noted that as every incoming connection is handled by postmaster and passed to a server process, once this is done , the communication is establishd directly between the client and Server Process , the postmaster drops off the communication circle.

    1. Describe the use and benefits of PL/pgSQL, PLPERL & PLTCL

      2. PL/pgSQL is a Procedural Language extension to Postgres database. SQL is non-procedural language ,in it the programmer only describes what work to perform, how to perform the work is left to the "Planner/Optimizer", in contrast PL/pgSQL is like any 3GL procedural language, it requires step by step instructions defininig what to do next.

      PL/pgSQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL.In Postgres it is also possible to create functions in procedural languages like Perl and Tcl, which interspersed with SQL are called PLPerl and PLTcl respectively.

  1. Writing Basic SQL Statements

    1. List the capabilities of SQL SELECT statements 
      2.         
 select * ....  [everything] 
 select distinct .... [distinct values in selection] 
 select .......for update of ...[Aquires a lock]
    2. Execute a basic SELECT statement 
      3. select * from users where user_id=3 
select email,user_name from employees where name='Daniel'
    3. Differentiate between SQL statements and PSQL (Analogous to Oracle's Sql*Plus) commands.

      4. The difference between SQL and PSQL is very stright forward, any command which begins with an unquoted backslash (\ ) is considered a PSQL command, these commands are sometimes refered to as: Meta-commands ,some commands are:

        1. \connect : This commands connects to particular database specified as the argument.
        2. \d : Shows all columns of the specified table,view,index or sequence, the types and special attributes related to it, the \d+ command could also be used which shows any comments associated with the table,view or index.
        3. \df (regexp): This command lists all the functions with arguments and return types that match the regular expression .
        4. \distvS (regexp): In general the \d command could be followed by a letter i s t v or S where they respectively mean: index, sequence, table, view and System table,if a regular expression is provided it limits the search to that string, you may also add the + (plus sign) to indicate that extra comments be displayed.
        5. \dp : Is used to display permissions on the database. a
        6. \e : Escapes the psql environment and opens a text editor in the terminal ,it defaults to the text editor specified by the environment variables:PSQL_EDITOR,EDITOR or VISUAL (in that order).
        7. \echo (text): Echoes the text back to the terminal, normally used when the \o command is activated.
        8. \encoding (type): Sets the current encoding, if no type is specified it displays the currents client (psql) encoding.
        9. \i (file): Reads input from the specified file,this is typically used in conjunction with the \o command.
        10. \l : List all the databases in the current database system
        11. \o file : Pipes out all output to a specific file.
        12. \p : Displays current buffer
        13. \pset (parameters): This command along with various parameters determines how the output will displayed on the terminal

      NOTE: These are only the basic commands, there are various other shortcuts that are not mentioned, you should refer to the psql manual page for the complete list.

  2. Restricting and Sorting Data

    1. Limit the Rows retrieved by a query 
      2.  
 select animals from zoo where classification='mammals'; 

 select cars from lot where year > to_date('2000-01-01','YYYY-MM-DD') ; 

 select boat_names from triton where boat_names like 'ki%'; (The  %  acts as a wildcard,SQL compliance with Oracle)
    2. Sort the rows retrieved by a query 
      3.  select houses from city where city='Ensenada' order by block_id;

 select cuartos from hoteles order by precio desc;

 select cuarto from hoteles order by precio asc;[asc is the default, SQL complianance with Oracle]