Oracle SQL and PL/SQL (1 of 3)

  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.
      CODDS TWELVE RULES
      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:

       CREATE TYPE , ALTER TYPE , DROP TYPE , GRANT/REVOKE TYPE  
    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
    2.         
       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.
    6. 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:

  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:

      4.   
        
                            CONCAT('P
        		    ---------
        		    perrogato
        		    
      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:

      6.   
                         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:

      8.   
                        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 :
      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
        		  ----------
        		  2000-12-02
        

        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.

  4. 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;
           

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

      AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE

      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;
                   

  6. 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
    6. WHERE EXISTS and WHERE NOT EXISTS

      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 
                     where clasificados.email= usuarios.email);
      
      select name,email
      from users 
      where exists (select 1
                     from classifieds 
      	       where classifieds.email = users.email);
      

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

  8. 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 <column name> format a<size of column>
      

      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.

    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.