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

  1. Subqueries
    1. Describe the Types of problems that subqueries can solve.
    2. The ability to generate a query based on the condition of changing data that is located in another table.

    3. Define subqueries.
    4. It can query a table restricting the rows returned on the basis of another queried table.

    5. List the type of queries


    7. Write single-row and multiple-row subqueries
    8. select * from nombres where exists ( select empleados from asegurados );
      select * from names where exists (select employees from insured); 
      select numero_usuario,email 
      from usuarios
      where exists ( select 1 
                     from clasificados 
      select name,email
      from users 
      where exists (select 1
                     from classifieds 
      	       where =;

      Note that Postgres supports the same "aliasing/shortcut" mechanism used in Oracle, using order number from the original query.(Sql compliance with Oracle)

  2. Multiple-Column Subqueries
    1. Write Multiple Column Subqueries
    2. select miembro from parientes where nombres in ( select * from parientes_lejanos where appelido like '%R');
      select member from relatives where name in (select * from foreign_relatives where lastname like '%R');
    3. Describe and explain the behavior of subqueries when NULL values are retrieved.
    4. Testing if behaviour is compliant with Oracle (See how Oracle executes this )

    5. Write subqueries in FROM clause
    6. select barcos_veloces,propietarios,costo from barcos where propietarios in 
      (select 2 from usuarios where valor_neto > 10000000 );
      select fast_boats,owners,costs from boats where owners in
      (select 2 from users where net_worth > 10000000 );

  3. Producing Readable Output with PSQL(Postgres's SQL*Plus)
    1. Produce queries that requiere an input variable
    2. Customize de PSQL Environment
    3. Produce more Readable output
    4. Create and execute script files
    5. Save Customizations

  4. Manipulating Data
    1. Describe each DML statement
    2. Insert a row into a table
    3.  insert into parientes(miembro,nombre) values (12,'Alexia');
       insert into relatives(member,name) values(12,'Alexia');
    4. Update a row in a table
    5.  update parientes set apellido='Rubio' where miembro=12; 
       update relatives set lastname='Rubio' where member=12;
    6. Delete a row in a table
    7.  delete parientes where miembro=12; 
    8. Control Transactions

  5. Creating and Managing Tables
    1. Describe the main database objects

    3. Create Tables
    4. create table alumnos (
       nombre varchar2(25), 
       apellido varchar2(25),
       matricula integer primary key,
       salon number );   
       create tables pupils (
       name varchar2(25),
       lastname varchar2(25),
       id integers primary key,
       classroom number);
    5. Describe the Datatypes that can be used when specifying column definitions
    6. There are various Datatypes which you can use in table definitions, however you are very likely to use a few extensively, these may be:

        1. For text: char(n) & varchar(n) (Where (n) is an upper limit)
        2. For numbers: decimal(n) & int4 (Where (n) is a precision of up to ~8000 digits; and int4 a number between -2147483648 to +2147483647)
        3. For Dates and times: timestamp,date,time & interval ( Where timestamp is year-to-microsecond precision, date is year-to-date precision, time is only time of day, and interval is used for time intervals.
    7. Alter Table Definitions
    8. Drop,rename and truncate tables.

  6. Including Constraints
    1. Describe constraints
    2. A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.

    3. Describe constraints
    4. A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.

    5. Create and maintain constriants
      1. not null : Requires a value for this column.
      2. unique : Two rows cant have the same value in this column ( side effect, Oracle creates an index on this column )
      3. primary key :Same as unique except that no row can have a null value, also it is possible to refer from other tables to this key.
      4. check : Limit the range of the column. Example: rango integer check ( rango > 6 and rango <= 9)
      5. references : This column can only contain values present in another tables primary key . Example:usuario not null references empleado

      Constraints can also apply to multiple columns:

                           create table los_mejores (
      		     identificacion CHAR(5) CONSTRAINT thisid PRIMARY KEY,
      		     nombre_titulo  varchar(45) NOT NULL,
      		     edad           date,
      		     create table los_mejores (
      		     identificacion PRIMARY KEY DEFAULT('user_id'),
      		     nombre_titulo  varchar(45),
      		     edad           integer,
      		     check (nombre_titulo <> '')

  7. Creating Views
    1. Describe a View
    2. When a certain query is used, it is often beneficial that instead of typing the long query every time, a view be created, the other reason a View might be created is Privacy, perhaps the developer should only have access to certain parts of the table, thus he is only granted permissions to the view. The actual query takes place in the same way as would a normal SQL query.

    3. Create a View
    4. Retrieve data thorugh a view
    5. Retrieve data thorugh a view
    6. Drop View

  8. Postgres Data Dictonary
    1. Describe the data dictonary views a user may access
    2. Query the data from the data dictonary

  9. Other Database objects
    1. Describe database objects and their uses
    2. Sequences are used to provide an order number list, they are normally used for primary keys in tables.

    3. Create,maintain and use sequences
    4. To create a sequence the create sequence command is used, its functionality is very similar to Oracle's, see following examples:

         CREATE SEQUENCE user_id START 1000;
         INSERT INTO users VALUES(NEXTVAL('user_id'),'Gabriel Lopez');

      The first command creates a sequence called user_id which starts at 1000 , the second line inserts data using the sequence; Postgres uses a similar way of calling sequence values, however the notation is inverted, in Oracle user_id.NEXTVAL in Postgres the NEXTVAL function is decalred and then the sequence_name.

      Other options to the create sequence are: CYCLE, MINVALUE,MAXVALUE, INCREMENT which indicate respectively : In case the MAXVALUE is reached reCYCLE the sequence starting at MINVALUE; the minimum value a sequence can have defaults to 1 (limit:-2147483647); the maximum value a sequnece can have, defaults to 2147483647; and INCREMENT which defaults to 1.

      Another option is CACHE this option defaults to 1, what this function does is preallocate a certain number of sequence values into memory, allowing faster access. While this may allow for faster sequence access, it is possible to lose sequence numbers if a session is terminated, a fact you may be familiar with if you use sequences in Oracle,since this value also defaults to 5.

    5. Create and maintain indexes
    6. Create private and public synonyms

  10. Controlling User Access
    1. Create users
    2. Every user that has access to a database is defined in a table called pg_shadow , to add a new user to the pg_shadow table the command createuser is used, this command can be issued directly from the shell if the user has the appropriate privileges; another possbilitiy to add a user to the pg_shadow table is with the create user in psql , this allows for finer settings for the user like: password expiration, specific userid and group membership.Examples:

      CREATE USER developer WITH PASSWORD 'eey245VCrwr' VALID UNTIL 'Sep 19 2002';

      The first user is created with database creation permission, while the second user is assigned a limited time access to the database.

    3. Create groups (Oracle's roles) to ease setup and maintanence of the security model
    4. Postgres defines groups to ease administration privileges on a database, the table name pg_group contains the relevant information, in order to add a new group the CREATE GROUP command is used, some examples of this command:

      CREATE GROUP developers;
      CREATE GROUP dbas WITH USER daniel, julio, roberto;

      The first line simply creates a group, while the second line includes specific users defined in the database (those defined in pg_user).

    5. Use the GRANT and REVOKE statements to grant and revoke object priviliges.
    6. Postgres like Oracle allows privileges to be assigned on an object|privilege|(user|group) basis.

  11. Declaring Variables
    1. List the benefits of PL/pgSQL
    2. Describe the basic PL/pgSQL block and its sections
    3. Describe the significance of variables in PL/pgSQL
    4. Declare PL/pgSQL variables
    5. Execute a PL/pgSQL block
    6. See the following examples

  12. Writing Executable Statements
    1. Describe the significance of the executable section
    2. Write statements in the executable section
    3. Describe the rules of nested blocks
    4. Execute and test a PL/pgSQL block
    5. Use coding conventions

  13. Interacting with the Oracle Server
    1. Write a successful SELECT statement in PL/psSQL
    2. Declare a datatype and size of a PL/pgSQL variable dynamically
    3. Write DML statements in PL/pgSQL
    4. Control Transactions in PL/pgSQL
    5. Determine the outcome of SQL and DML statements

  14. Writing Control Structures
    1. Identify the uses and types of control strucutures
    2. Construct an IF statement
    3. Construct and Identify different Loop Statements
    4. Use logic tables
    5. Control Back Flow using nested loops and labels

  15. Working with Composite Datatypes
    1. Create user-defined PL/pgSQL records
    2. Create a Record with the %ROWTYPE attribute
    3. Create a PL/pgSQL table
    4. Create a PL/pgSQL Table of Records.
    5. Describe the difference between records, tables, and tables of records

  16. Writing Explicit Cursors
    1. Distinguish between an implicit and an explicit cursor
    2. Use a PL/pgSQL record variable
    3. Write a cursor FOR loop

  17. Advanced Explicit Cursor Concepts
    1. Write a cursor that uses parameters
    2. Determine when a FOR UPDATE clause in a cursor is required
    3. Determine when to use a WHERE CURRENT OF clause.
    4. Write a cursor that uses a subquery.

  18. Handling Exceptions
    1. Define PL/psSQL exceptions
    2. Recognize Unhandled exceptions
    3. List and use different types of PL/psSQL exeception handlers
    4. Trap unanticapted errors
    5. Describe the effect of exception propagation in the nested block
    6. Customize PL/psSQL exception messages