- Displaying Data from Multiple Tables
- Write SELECT statements to access data from more than one table using equality and nonequality joins.
- View data that generally does not meet a join condition by using outer joins
- Join a Table to Itself
- Aggregating Data Using Group Function
- Identify the available group functions
- Describe the use of group functions
- Group data using GROUP BY clause
- Include or exclude grouped rows by using the HAVING clause
- Describe the Types of problems that subqueries can solve.
- Define subqueries.
- List the type of queries
- Write single-row and multiple-row subqueries
- Multiple-Column Subqueries
- Write Multiple Column Subqueries
- Describe and explain the behavior of subqueries when NULL values are retrieved.
- Write subqueries in FROM clause
- Producing Readable Output with SQL*Plus
- Produce queries that requiere an input variable
- Customize de SQL*Plus Environment
- Produce more Readable output
- Create and execute script files
- Save Customizations
- file_name[ext]: Is the name of the OS file name, if you do not specify an extension for the file, then the default
.sqlis 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.
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');
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.
select * from parientes a,parientes b; select * from mexico c,mexico y;
The different group functions are:AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE
Only meaningful in trusted Oracle: GLB,LUB
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
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.
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
havingclauses , they cannot use aliased columns.
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;
The ability to generate a query based on the condition of changing data that is located in another table.
It can query a table restricting the rows returned on the basis of another queried table.
WHERE EXISTS and
WHERE NOT EXISTS
WHERE IN and
WHERE NOT IN
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);
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');
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.
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.
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 );
The input variable is defined with " & ", like so:
select * from parientes where miembro > &Numero_de_Familiar;
Enter value for numero_de_familiar:
After a value is given the query will execute accordingly.
There are two files which are used to customize the SQL*Plus environment
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.
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
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:
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;
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.
Once the correct customization commands are made they are stored in the SQL*Plus buffer. To check this SQL*Buffer you issue the command
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 file_name[ext] [CREATE | REPLACE | APPEND ]