curso de oralcle SQL y PL/SQL (I)

Overview of Relational Databases, SQL and PL/SQL

Discuss the Theoretical and Physical Aspects of a Relational database

/-------------/        /------------------/     /------------/
/  Relational /--------/ Named Attributes /-----/   Tuples   /
/     Tables  /        /    (Columns or   /     /  (Rows or  /
/-------------/        /       Fields )   /     /   Records) /
                      /------------------/     /------------/

The data stored in the cells must be atomic. Each cell can only hold one piece of data.When a cell contains more than one piece of information this is known as information coding
Data stored under columns must be of the same data type
Each row is unique (No duplicate rows)
Columns have no order in them
Rows have no order in them
Columns have a unique name
Two fundamental integrity rules: entity integrity rule :States that the primary key cannot be totally or partially empty. referential integrity rule : States that the foreign key must either be null or match currently existing value of the primary key that it references.

CODDS TWELVE RULES

The information Rule :Information is to be represented as data stored in cells.
The guaranteed access rule : Each data item must be accesible by a comination of table name + primary key of the row + column name .
NULLS must be used in a consistent manner: Nulls should be simply missing data and have no values.
An active, online data dictonary should be stored as relational tables and accesible through the regular data access language.
The data access language must provide all means of access and be the only means of access, except possibly for low level routines : If you can access a table thorugh a utility other than SQL it might violate this rule.
All views that can be updatable should be updatable: If you can join three tables as the basis of a view, but cannot update the view directly it violates this rule.
There must be set-level inserts, updates and deletes :
Physical data independence : An application cannot depend on a physical restructuring. If a file is moved to another disk it should not break the application.
Logical Data Independance : An application should not depend on a logical restructuring. If a file is split in two it should not break the application.
Integrity Dependance : Integrity rules should be stored in the data dictonary.
Distribution Independance : A database should continue to work properly even if distributed.
The nonsubversion rule: If low-level access is permitted it should not bypass security or integrity rules.

The major objective of physical design is to eliminate or at least minimize contention

Separate Tables and Indexes
Place large Tables and Indexes on disks of their own
Place frequently joined tables on separate disks, or cluster them.
Place infrequently joined tables on the same disks if necessary (if your short on disks)
Separate the RDBMS software from tables and indexes.
Separate the Data Dictonary from tables and indexes.
Separate the (undo) rollback logs and redo logs onto their own disks if possible.
Use RAID 1 for undo or redo logs
Use RAID 3 or 5 for Table Data.
Use RAID 0 for indexes.

Describe the Oracle Implementation the RDBMS and ORDBMS.

One of the major reasons to want an RDBMS with an Object-orientated interface (OORDBMS) is so that object orientated programs can communicate directly with the object-portion of the RDBMS instead of having to handle the object-relational mapping (composition-decompostion) dynamically within the code. Normally this situation was resolved through constructs such as cursors and software such as precompilers (Pro*C) this is known as impedance mismatch .

Oracle helps alleviate the problem of object-orientated developement and RDBMS back-end situation , with the following built-in object-orientated capabilities:

Relationships as Datatypes
Inheritance
Collections as Datatypes, including nesting (containers)
User-defined (extensible) datatypes
Improved large objects (LOBs)

Oracle extended the already complex RDBMS with the following:

Object Types: Records or classes
Object Views :
Object Language: Extensions to the Oracle SQL and PL/SQL
Object APIs : Objects supported through Oracle precompilers PL/SQL, OCI.
Object Portability : Through the object type translator (OTT) which can port for example an Oracle8 object type to a C++ class.

Despite these advancements Oracle does not support multiple inheritance, polymorphism, or constraints on object attributes (such as referential integrity).The Oracle8 Open Type System (OTS) is a repositary for all object types, as well as external object types from other languages or systems.

Within OTS, there is a datatype hierarchy that has as its foundation the built-in Oracle8 datatypes (VARCHAR2,NUMBER,etc).Also user defined datatypes can be built on any of the built-in datatypes plus previously user-defined datatypes. When creating user-defined datatypes these can be used :

As a column of a relational table
As an attribute within another object type.
As part of an object view of relational tables.
As the basis for an object table.
As the basis for PL/SQL variables.

Extended Oracle SQL manages the object types with the commands:

 CREATE TYPE , ALTER TYPE , DROP TYPE , GRANT/REVOKE TYPE

Describe the use and benefits of PL/SQL

PL/SQL is a Procedural Language extension to Oracle's version of ANSI standard SQL. SQL is non-procedural language , the programmer only describes what work to perform. How to perform the work is left to the "Oracle Optimizer", in contrast PL/SQL is like any 3GL procedural language, it requires step by step instructions defininig what to do next.

PL/SQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL. This results in a robust, powerful language suited for designing complex applications.

Writing Basic SQL Statements

List the capabilities of SQL SELECT statements

       
 select * ....  [everything]
 select distinct .... [distinct values in selection]
 select :whatever,:other ....  [using bind variables]
 select .......for update of ...[Aquires a lock]

Execute a basic SELECT statement

select * from users where user_id=3
select email,user_name from employees where name='Daniel'

Differentiate between SQL statements and SQL*Plus commands.

SQL* Plus commands are: COPY, EDIT, EXECUTE,EXIT, GET, HOST,QUIT, PASSWORD,RUN,SAVE,SET,SPOOL,START

SQL commands are: ALTER, ANALYZE, AUDIT,CONNECT, CREATE, DELETE ,DROP , GRANT , INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, SET ROLE, SET TRANSACTION, TRUNCATE, UPDATE.

PL/SQL commands : BEGIN, DECLARE

Other SQL*Plus commands: APPEND,CHANGE, CLEAR BUFFER,DEL,INPUT,LIST, DEFINE ,SHOW,RECOVER , DESCRIBE

NOTE: In case disabling of any of the previous commands is required, this can be done through the PRODUCT_USER_PROFILE table:

Restricting and Sorting Data

Limit the Rows retrieved by a query

 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 '__uk%';

select count(*) from classifieds where to_date(expires,'YYYY-MM-DD') >= to_date(sysdate,'YYYY-MM-DD') or expires is null;

Sort the rows retrieved by a query

 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

select empleados from empresa order by upper(nombres) asc;

select rios from continente order by lugar,longitud,profundidad;

Single Row Functions

Describe various types of functions available in SQL.

Character, Date and Number Functions.

Use character,date and number functions in SELECT statements

There are functions returning Character Values :

CHR : Returns the character value given character number
select CHR(37) a, CHR(100) b, CHR(110) c from dual; RETURNS:


       A B C
       - - -
       % d n

CONCAT :Returns string1 concatenated with string2
select CONCAT('perro','gato') from dual; RETURNS:

 

                   CONCAT('P
      ---------
      perrogato

INITCAP :Returns the string with the first character in every word capatilzed and the rest lowercase
select INITCAP('esto ES paRA el FUNcioNaMienTo de oracle ') "La prueba" from dual; RETURNS:

 
                La prueba
   -----------------------------------------
   Esto Es Para El Funcionamiento De Oracle

LOWER :Returns the string in all lowercase
select LOWER ('ESTO ES paRA el FUNcioNaMienTo de oracle ') "Minusculas" from dual; RETURNS:

 
               Minusculas
  -----------------------------------------
  esto es para el funcionamiento de oracle

Other Character functions include : LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLS_UPPER, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, SUBSTRB, TRANSLATE, UPPER, ASCII, INSTR, INSTRB, LENGTH, LENGTHB, NLSSORT

There are numeric functions :

ABS : Returns the absolute value of a number
select ABS(-24),ABS(-414),ABS(24) from dual; RETURNS:


             ABS(-24)  ABS(-414)      ABS(24)
       ---------- ---------- ----------
        24   414       24

FLOOR: Returns the largest integer equal to or less than the number
select FLOOR(-131.7), FLOOR(23.5) from dual; RETURNS:


            FLOOR(-131.7) FLOOR(23.5)
      ------------- -----------
        -132        23

ROUND : Returns the left number rounded to right number places of the decimal point
select ROUND(1.57),ROUND(1.57,1),ROUND(20.87,2) from dual; RETURNS:

            ROUND(1.57) ROUND(1.57,1) ROUND(20.87,2)
     ----------- ------------- --------------
        2       1.6    20.87

Other Number functions include: ACOS, ASIN, ATAN, ATAN2, CEIL, COS, COSH, EXP, LN, LOG, MOD, POWER, SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC

There are date functions :

ADD_MONTHS: Returns the date plus x months specfied.
select ADD_MONTHS('1999-12-2',12) from dual; RETURNS:



                 ADD_MONTHS
    ----------
    2000-12-02

Other Date functions include: LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND, SYSDATE, TRUNC

Describe the use of conversion functions.

These are mostly used within PL/SQL in order to coerce a datatype to explicitly be converted into another type,the most commonly user conversion functions are TO_DATE and TO_CHAR. Among other conversion functions are : CHARTROWID, CONVERT, HEXTORAW, RAWTOHEX, ROWIDTOCHAR, TO_LABEL, TO_MULTI_BYTE, TO_NUMBER, TO_SINGLE_BYTE.

v. Displaying Data from Multiple Tables

Write SELECT statements to access data from more than one table using equality and nonequality joins.


          select * from restaurantes where sucursal <> 42 ;
    select * from r estuant where branch <> 42 ;
          select * from comida where comida = upper('Italiana');
    select * from menu where main_course = upper('Italian');

View data that generally does not meet a join condition by using outer joins

The (+) sign indicates that in case the select column contains a null it should also be inlcuded

select * from nombres a,edad b where  b.apellido=a.apellido (+);
select * from names a, age b where b.lastname = a.lastname(+);

In case, the column lastname(apellido) for the table names(nombres) was null, the outer join (+) would still display all other information. If the outer join (+) is omitted and lastname(apellido) in table names(nombres) is null then all the other info from names(nombres) will not be displayed.

Join a Table to Itself

select * from parientes a,parientes b;
select * from mexico c,mexico y;

Aggregating Data Using Group Function

Identify the available group functions

The different group functions are:

Describe the use of group functions

Group functions return a single result based on many rows, as opposed to single-row functions.e.g The group COUNT function returns the number of rows returned. These functions are valid in the select list of a query and the GROUP BY clause ONLY.

Most functions can accept qualifiers as their arguments . These qualifiers are DISTINCT and ALL . If the DISTINCT qualifier is passed, then only distinct values returned by the query are considered. The ALL qualifier causes the function to consider all of the values returned by the query. If none is specified then ALL is the default.

Group data using GROUP BY clause

select nombre,count(compras) from usuarios group by nombre;
select name,count(sales) from customers group by name;
select automoviles, avg(costo) "Total" from mis_automoviles group by automoviles having avg(costo) > 10000;
select cars, avg(cost) "Total" from my_cars group by cars having avg(cost) > 10000;

NOTE:

having

Total

group by

having

cannot use aliased columns.

Include or exclude grouped rows by using the HAVING clause

select modelo from automoviles group by modelo having color=rojo;
select model from cars group by model having color="red";
select * from usuarios group by edad having edad > 35 ;
select * from usuarios group by age having age > 35;

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

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.

When using NOT IN , in case the subquery returns a NULL no rows are selected for the main query , this is different from IN in which case if a NULL value is returned no special effect takes place.

When using NOT EXISTS if the subquery returns NULL (no rows selected) the main query will return its rows accordingly, and viceversa if the NOT EXISTS subquery returns at least one row then the main query will return no rows selected

In the case of EXISTS if the subquery returns at least on row the main query returns its values accordingly, and viceversa if the EXISTS subquery returns NULL (no rows selected) then the main query will return no rows selected.

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 SQL*Plus

Produce queries that requiere an input variable

The input variable is defined with " & ", like so:

select * from parientes where miembro > &Numero_de_Familiar;
Will prompt:

Enter value for numero_de_familiar:

After a value is given the query will execute accordingly.

Customize de SQL*Plus Environment

There are two files which are used to customize the SQL*Plus environment glogin.sql and login.sql .

The glogin.sql is used as a global setup file, and the login.sql is intended for individual use. Both contain SQL*Plus commands or SQL statements that are executed every time Oracle invokes SQL*Plus.

The glogin.sql file is located in $ORACLE_HOME/sqlplus/admin directory. This file is also sometimes known as the site profile , and as mentioned earlier it contains SQL*Plus commands, SQL statements, and PL/SQL blocks that are to be executed by every SQL*Plus user at the start of his session.

SQL*Plus uses two environment variables SQLPATH and editor . SQLPATH is the variable used to locate the file login.sql .SQLPATH is also used to identify the location of SQL scripts that you run from SQL*Plus. The variable editor is used to define the text editor of preference to edit SQL*Plus commands. In any of the login scripts it can be defined as: define _editor=emacs

Produce more Readable output

This can be done with various variables, like PAGESIZE,HEADING,etc.The most common one is defining a column name so that it takes up a certain space on the screen

     column nombres format a15;
    column appedllidos format a15;
    column direccion format a30;

Create and execute script files

One of the most commonly used commands in SQL*Plus in order to produce a more readable output is:

 column  format a

This command is commonly declared in the login scripts for columns in tables that are heavily accessed, so it provides a more cleaner display. Other variables are also used, following is the creation of a script within SQL*Plus.


       SQL> SET PAGESIZE 0 (Turns off all page formating information ( columns,headings,page breaks,etc) )
       SQL> SET HEADING OFF ( Turns off the display of column headings )
       SQL> SET FEEDBACK OFF ( Suppresses the display of the number of rows retuned by the query)
       SQL> SET VERIFY OFF ( Does not display the text of a SQL command before and after SQL*Plus replaces
                       substitution variables with values )
       SQL> SET ECHO OFF  (Suppresses the listing of SQL commands in the eliminartablas.sql  as they are
                     executed)
       SQL> SPOOL eliminartablas.sql  (Starts spooling and saving commands entered at the SQL*Plus prompt
                                 and query results into the file eliminartablas.sql)
       SQL> Select 'DELETE ' || TABLE_NAME ||';'
        2  FROM DBA_TABLES
        3  WHERE OWNER='DANIEL';
           DELET ACCTS;
           DELETE ACCT_ADDRS;
                  |
                  |
                  |
           DELETE STOCKS;
       SQL>SPOOL OFF (Stops spooling and closes the file eliminartablas.sql)

Now if the script eliminartables.sql is run it will delete every table from the select statment.

Save Customizations

Once the correct customization commands are made they are stored in the SQL*Plus buffer. To check this SQL*Buffer you issue the command list .

This buffer can now be saved to either the local login.sql or glogin.sql to be run everytime sqlplus strats with the command SAVE :


        SAVE file_name[ext] [CREATE | REPLACE | APPEND ]

file_name[ext]: Is the name of the OS file name, if you do not specify an extension for the file, then the default .sql is used, you can also use the a directory path for the file, if no path is specified then the SQLPATH is used.
CREATE : Creates the file, if the file already exists an error is returned.
REPLACE : Replaces the file.
APPEND : Appends the content of the SQL Buffer to the file.

Manipulating Data

Describe each DML statement

INSERT, UPDATE, DELETE, (INSERT, SET TRANSACTION, EXPLAIN PLAN)

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

commit and rollback savepoint . When a savepoint is defined within a statement, it can be rolledback up to that particular point. Example:

 insert into nomina(sueldo) values(1000000);
savepoint excelente;
insert into nomina(sueldo) values(100000);
savepoint bueno;
insert into nomina(sueldo) values(10000);
savepoint salario_mexicano;
commit;

Even though the commit was already issued, we can still rollaback up to a savepoint location with:

 ROLLBACK to bueno

That would rollback the insert with a value of 10000

Creating and Managing Tables

Describe the main database objects

INDEX, SEQUENCE, TABLE ,VIEW,CLUSTER, PROCEDURE,SYNONYM,TRIGGER, CONSUMER GROUP,RESOURCE PLAN ,FUNCTION,INDEXTYPE, JAVA CLASS, JAVA RESOURCE, LIBRARY, OPERATOR, PACKAGE, PACKAGE BODY, QUEUE.

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

char(n): A fixed length character string,will take up 200 bytes regardless of how long the string actually is. The maximum length of char is 2000 bytes.
varchar(n): A variable length character string, that will take up space accordingly
Other Datatype: CHAR,NCHAR,VARCHAR2, NVARCHAR,DATE,NUMBER,LONG,RAW,LONG RAW, MISLABEL, BLOB,CLOB,NCLOB,BFILE,ROWID y UROWID

Alter Table Definitions

To drop a column :

                 alter table mifamilia drop column no_sirve;
                alter table myfamily drop column irrelevant;

To add a column :

                 alter table mifamilia add (telefono number(15));
   alter table myfamily add (telephone number(15));

To rename a column (two steps):

                 alter table mifamilia set unused parientes;(This deactivates the column)
   alter table mifamilia add (familiares varchar2(40));(Created a new column)

When the column is marked unused, the information is not deleted it still exists. So you can then copy the data from the unused column (parientes) to the new column (familiares).

To change variable type in column :


               alter table mifamily modify(nombre varchar2(43));

  NOTE: To modify the Datatype the column must be all null.

Alter storage parameter for the table:

                ALTER TABLE branch MODIFY PARTITION branch_mex
  STORAGE (MAXEXTENTS 75) LOGGING;


  ALTER TABLE emp
  PCTFREE 30 PCTUSED 60;

Drop,rename and truncate tables.

To Drop a table:

              drop table esta_no_sirve;
       drop table useless;

To Rename a table:

              alter table mal_nombre rename to buen_nombre;
       alter table bad_name rename to good_name;

or the rename command can also be used: rename mal_nombre to buen_nombre

To Truncate:

              truncate table se_perdera_todo;
       truncate table all_is_lost;

NOTE: A truncate statement cannot be rollbacked. Truncate is used to RESET the STORAGE parameters to the values when the table or cluster was created. Deleting rows with the TRUNCATE statement can be more efficient than dropping or re-creating a table. Dropping and re-creating a table invalidates the tables dependent objects , the priviliges on the table have to be regranted , and also requires re-creating the tables indexes, integrity constraints, triggers, and of course respecify its storage parameteres. TRUNCATING has none of these efffcts. thus its usefullness.

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

not null : Requires a value for this column.
unique : Two rows cant have the same value in this column ( side effect, Oracle creates an index on this column )
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.
check : Limit the range of the column. Example: rango integer check ( rango > 6 and rango <= 9)
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 integer primary key,
       nombre_titulo  varchar2(45),
       edad           integer,
       unique(edad,identificacion)
       );

       create table los_mejores (
       identificacion integer primary key,
       nombre_titulo  varchar2(45),
       edad           integer,
       check (edad <=35 or identificacion <=1000 )
       );

In case you want a more friendly message, and not a cryptic constraint violated you can name the constraint like so:

                     create table los_mejores (
       identificacion integer primary key,
       nombre_titulo  varchar2(45),
       edad           integer,
       constraint los_primeros 
       check (edad <=35 or identificacion <=1000 )
       );

If the table already existed:

                    alter table mifamilia add(constraint los_primeros check(edad <>1000));
      alter table myfamily add(constraint the_first check(age <> 1000));

To delete a constraint from an existing table:

                    alter table mifamilia drop primary key;
      alter table myfamily drop primary key;

Or if you check the constraint name of the table in USER_CONSTRAINTS or ALL_CONSTRAINTS then you can drop the constraint by constraint_name

                   alter table mifamilia drop constraint los_primeros;
     alter table myfamily drop constraint the_first;

In case the constraint is referenced by another table then the previous commands will fail unless the cascade parameter is specified.

Be the best in your profession!. Whether you are pursuing OCP certification or not, hone your skills and stay on par with the Elite!.

For the small payment of $10.00 Dlls U.S you gain access to this document, a great investement compared to your potential increase in productivity and/or the price of a complete Book.

Payment via PayPal:

Oracle SQL and PL/SQL Section II

Creating Views
Oracle Data Dictonary
Other Database objects
Controlling User Access
Declaring Variables
Writing Executable Statements
Interacting with the Oracle Server
Writing Control Structures
Working with Composite Datatypes
Writing Explicit Cursors
Advanced Explicit Cursor Concepts
Handling Exceptions

MyBiosWeb