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

Subqueries

Describe the Types of problems that subqueries can solve.

The ability to generate a query based on the condition of changing data that is located in another table.

Define subqueries.

It can query a table restricting the rows returned on the basis of another queried table.

List the type of queries

WHERE EXISTS and WHERE NOT EXISTS

WHERE IN and WHERE NOT IN

Write single-row and multiple-row subqueries

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 
               where clasificados.email= usuarios.email);

select name,email
from users 
where exists (select 1
               from classifieds 
	       where classifieds.email = users.email);

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

Multiple-Column Subqueries

Write Multiple Column Subqueries

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');

Describe and explain the behavior of subqueries when NULL values are retrieved.

Testing if behaviour is compliant with Oracle (See how Oracle executes this )

Write subqueries in FROM clause

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 );

Producing Readable Output with PSQL(Postgres's SQL*Plus)

Produce queries that requiere an input variable

Customize de PSQL Environment

Produce more Readable output

Create and execute script files

Save Customizations

Manipulating Data

Describe each DML statement

Insert a row into a table

 insert into parientes(miembro,nombre) values (12,'Alexia');
 insert into relatives(member,name) values(12,'Alexia');

Update a row in a table

 update parientes set apellido='Rubio' where miembro=12; 
 update relatives set lastname='Rubio' where member=12;

Delete a row in a table

 delete parientes where miembro=12; 

Control Transactions

Creating and Managing Tables

Describe the main database objects

INDEX, SEQUENCE, TABLE ,VIEW...

Create Tables

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);
 

Describe the Datatypes that can be used when specifying column definitions

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.

Alter Table Definitions

Drop,rename and truncate tables.

Including Constraints

Describe constraints

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

Describe constraints

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

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,
		     unique(edad,identificacion) 
		     ); 

		     create table los_mejores (
		     identificacion PRIMARY KEY DEFAULT('user_id'),
		     nombre_titulo  varchar(45),
		     edad           integer,
		     check (nombre_titulo <> '')
		     ); 

Creating Views

Describe a View

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.

Create a View

Retrieve data thorugh a view

Retrieve data thorugh a view

Drop View

Postgres Data Dictonary

Describe the data dictonary views a user may access

Query the data from the data dictonary

Other Database objects

Describe database objects and their uses

Sequences are used to provide an order number list, they are normally used for primary keys in tables.

Create,maintain and use sequences

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.

Create and maintain indexes

Create private and public synonyms

Controlling User Access

Create users

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 daniel WITH PASSWORD 'd43SfgStAda' CREATEDB;
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.

Create groups (Oracle's roles) to ease setup and maintanence of the security model

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).

Use the GRANT and REVOKE statements to grant and revoke object priviliges.

Postgres like Oracle allows privileges to be assigned on an object|privilege|(user|group) basis.

Declaring Variables

List the benefits of PL/pgSQL

Describe the basic PL/pgSQL block and its sections

Describe the significance of variables in PL/pgSQL

Declare PL/pgSQL variables

Execute a PL/pgSQL block

See the following examples

Writing Executable Statements

Describe the significance of the executable section

Write statements in the executable section

Describe the rules of nested blocks

Execute and test a PL/pgSQL block

Use coding conventions

Interacting with the Oracle Server

Write a successful SELECT statement in PL/psSQL

Declare a datatype and size of a PL/pgSQL variable dynamically

Write DML statements in PL/pgSQL

Control Transactions in PL/pgSQL

Determine the outcome of SQL and DML statements

Writing Control Structures

Identify the uses and types of control strucutures

Construct an IF statement

Construct and Identify different Loop Statements

Use logic tables

Control Back Flow using nested loops and labels

Working with Composite Datatypes

Create user-defined PL/pgSQL records

Create a Record with the %ROWTYPE attribute

Create a PL/pgSQL table

Create a PL/pgSQL Table of Records.

Describe the difference between records, tables, and tables of records

Writing Explicit Cursors

Distinguish between an implicit and an explicit cursor

Use a PL/pgSQL record variable

Write a cursor FOR loop

Advanced Explicit Cursor Concepts

Write a cursor that uses parameters

Determine when a FOR UPDATE clause in a cursor is required

Determine when to use a WHERE CURRENT OF clause.

Write a cursor that uses a subquery.

Handling Exceptions

Define PL/psSQL exceptions

Recognize Unhandled exceptions

List and use different types of PL/psSQL exeception handlers

Trap unanticapted errors

Describe the effect of exception propagation in the nested block

Customize PL/psSQL exception messages