curso de oralcle SQL y PL/SQL (I)

Wilmer Barrios | domingo, junio 21, 2009 | |

  1. Overview of Relational Databases, SQL and PL/SQL
    1. Discuss the Theoretical and Physical Aspects of a Relational database
    2. E.F. Codd Fathered the concept of the Relational Datamodel. A relational model organizes DATA into TABLES and only TABLES. Tables are the lingua franca of the relational model.
      /-------------/        /------------------/     /------------/
      / Relational /--------/ Named Attributes /-----/ Tuples /
      / Tables / / (Columns or / / (Rows or /
      /-------------/ / Fields ) / / Records) /
      /------------------/ /------------/

      A row and column intersection is called a "cell" The columns are placeholders, having data types such as character or integer.The rows themselves are the data. A relational table must meet the following criteria:
      1. 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
      2. Data stored under columns must be of the same data type
      3. Each row is unique (No duplicate rows)
      4. Columns have no order in them
      5. Rows have no order in them
      6. Columns have a unique name
      7. 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.
      1. The information Rule :Information is to be represented as data stored in cells.
      2. The guaranteed access rule : Each data item must be accesible by a comination of table name + primary key of the row + column name .
      3. NULLS must be used in a consistent manner: Nulls should be simply missing data and have no values.
      4. An active, online data dictonary should be stored as relational tables and accesible through the regular data access language.
      5. 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.
      6. 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.
      7. There must be set-level inserts, updates and deletes :
      8. 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.
      9. 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.
      10. Integrity Dependance : Integrity rules should be stored in the data dictonary.
      11. Distribution Independance : A database should continue to work properly even if distributed.
      12. 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 . Follow these rules to avoid contention :
      1. Separate Tables and Indexes
      2. Place large Tables and Indexes on disks of their own
      3. Place frequently joined tables on separate disks, or cluster them.
      4. Place infrequently joined tables on the same disks if necessary (if your short on disks)
      5. Separate the RDBMS software from tables and indexes.
      6. Separate the Data Dictonary from tables and indexes.
      7. Separate the (undo) rollback logs and redo logs onto their own disks if possible.
      8. Use RAID 1 for undo or redo logs
      9. Use RAID 3 or 5 for Table Data.
      10. Use RAID 0 for indexes.
    1. Describe the Oracle Implementation the RDBMS and ORDBMS.
    2. Oracle is entry-level SQL-92 complaint.It Offers SQL*Plus as an interactive SQL command interpreter which can run noninteractive scripts, and a procedural programming language known as PL/SQL ( modeled after ADA and Pascal ) (stored procedures and triggers are written in PL/SQL )
      Oracle offers pre-compilers ( for embedded SQL) and a 3GL function called OCI ( Oracle Call Interface).

      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:

      1. Relationships as Datatypes
      2. Inheritance
      3. Collections as Datatypes, including nesting (containers)
      4. User-defined (extensible) datatypes
      5. Improved large objects (LOBs)

      Oracle extended the already complex RDBMS with the following:

      1. Object Types: Records or classes
      2. Object Views :
      3. Object Language: Extensions to the Oracle SQL and PL/SQL
      4. Object APIs : Objects supported through Oracle precompilers PL/SQL, OCI.
      5. 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 :

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

      Extended Oracle SQL manages the object types with the commands:

    3. Describe the use and benefits of PL/SQL
    4. 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.

      1. Writing Basic SQL Statements
        1. 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]
        3. Execute a basic SELECT statement
        4. select * from users where user_id=3
          select email,user_name from employees where name='Daniel'
        5. Differentiate between SQL statements and SQL*Plus commands.


          PL/SQL commands : BEGIN, DECLARE


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

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

          select count(*) from classifieds where to_date(expires,'YYYY-MM-DD') >= to_date(sysdate,'YYYY-MM-DD') or expires is null;
        3. Sort the rows retrieved by a query
        4.  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;

      3. Single Row Functions
        1. Describe various types of functions available in SQL.
        2. Character, Date and Number Functions.

        3. Use character,date and number functions in SELECT statements
        4. There are functions returning Character Values :
          1. CHR : Returns the character value given character number

            select CHR(37) a, CHR(100) b, CHR(110) c from dual; RETURNS:

          2. A B C
            - - -
            % d n
          3. CONCAT :Returns string1 concatenated with string2

            select CONCAT('perro','gato') from dual; RETURNS:


          5. 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
          7. LOWER :Returns the string in all lowercase

            select LOWER ('ESTO ES paRA el FUNcioNaMienTo de oracle ') "Minusculas" from dual; RETURNS:

            esto es para el funcionamiento de oracle


          There are numeric functions :
          1. ABS : Returns the absolute value of a number

            select ABS(-24),ABS(-414),ABS(24) from dual; RETURNS:

          2. ABS(-24) ABS(-414) ABS(24)
            ---------- ---------- ----------
            24 414 24
          3. FLOOR: Returns the largest integer equal to or less than the number

            select FLOOR(-131.7), FLOOR(23.5) from dual; RETURNS:

          4. FLOOR(-131.7) FLOOR(23.5)
            ------------- -----------
            -132 23
          5. 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:

          6.             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 :
          1. ADD_MONTHS: Returns the date plus x months specfied.

            select ADD_MONTHS('1999-12-2',12) from dual; RETURNS:

          2. ADD_MONTHS

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

        5. Describe the use of conversion functions.
        6. 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

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

            2. 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');
            3. View data that generally does not meet a join condition by using outer joins
            4. 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.

            5. Join a Table to Itself
            6. select * from parientes a,parientes b;
              select * from mexico c,mexico y;

          1. Aggregating Data Using Group Function
            1. Identify the available group functions
            2. The different group functions are:

              Only meaningful in trusted Oracle: GLB,LUB
            3. Describe the use of group functions
            4. 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.

            5. Group data using GROUP BY clause
            6. 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: In the previous example notice than in the having clause one cannot use the "alias name" Total used in the query, this is a restriction on the group by and having clauses , they cannot use aliased columns.
            7. Include or exclude grouped rows by using the HAVING clause
            8. 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;

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

              WHERE IN and WHERE NOT IN

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

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

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

          4. Producing Readable Output with SQL*Plus
            1. Produce queries that requiere an input variable
            2. 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.

            3. Customize de SQL*Plus Environment
            4. 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

            5. Produce more Readable output
            6. 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;
            7. Create and execute script files
            8. 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
              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.

            9. Save Customizations
            10. 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 ]
              1. 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.
              2. CREATE : Creates the file, if the file already exists an error is returned.
              3. REPLACE : Replaces the file.
              4. APPEND : Appends the content of the SQL Buffer to the file.

  1. Manipulating Data
    1. Describe each DML statement

    3. Insert a row into a table
    4.  insert into parientes(miembro,nombre) values (12,'Alexia');
      insert into relatives(member,name) values(12,'Alexia');
    5. Update a row in a table
    6.  update parientes set apellido='Rubio' where miembro=12;
      update relatives set lastname='Rubio' where member=12;
    7. Delete a row in a table
    8.  delete parientes where miembro=12; 
    9. Control Transactions
    10. 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;

      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

  2. 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
      1. 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.
      2. varchar(n): A variable length character string, that will take up space accordingly
    6. Alter Table Definitions
    7. 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

      ALTER TABLE emp
      PCTFREE 30 PCTUSED 60;
    8. Drop,rename and truncate tables.
    9. 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.

  3. 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 integer primary key,
      nombre_titulo varchar2(45),
      edad integer,

      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

  1. Creating Views
  2. Oracle Data Dictonary
  3. Other Database objects
  4. Controlling User Access
  5. Declaring Variables
  6. Writing Executable Statements
  7. Interacting with the Oracle Server
  8. Writing Control Structures
  9. Working with Composite Datatypes
  10. Writing Explicit Cursors
  11. Advanced Explicit Cursor Concepts
  12. Handling Exceptions
Share this article

0 comentarios :

Copyright © 2017 MyBiosWeb
Distributed By My Themes | Template Design By BioHosting