(Previous) < 1 2 3 4 5 > (Next)

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:

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

Only meaningful in trusted Oracle: GLB,LUB

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

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

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 ] 
	 

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.

(Previous) < 1 2 3 4 5 > (Next)