/-------------/ /------------------/ /------------/
/ 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:
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:
Oracle extended the already complex RDBMS with the following:
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 :
Extended Oracle SQL manages the object types with the commands:
CREATE TYPE , ALTER TYPE , DROP TYPE , GRANT/REVOKE TYPE
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.
select * ....[everything]select distinct ....[distinct values in selection]select :whatever,:other ....[using bind variables]select .......for update of ...[Aquires a lock]
select * from users where user_id=3 select email,user_name from employees where name='Daniel'
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:
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;
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;
Character, Date and Number Functions.
select CHR(37) a, CHR(100) b, CHR(110) c from dual; RETURNS:
A B C - - - % d n
select CONCAT('perro','gato') from dual; RETURNS:
CONCAT('P
---------
perrogato
select INITCAP('esto ES paRA el FUNcioNaMienTo de oracle ') "La prueba" from dual; RETURNS:
La prueba
-----------------------------------------
Esto Es Para El Funcionamiento De Oracle
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
select ABS(-24),ABS(-414),ABS(24) from dual; RETURNS:
ABS(-24) ABS(-414) ABS(24)
---------- ---------- ----------
24 414 24
select FLOOR(-131.7), FLOOR(23.5) from dual; RETURNS:
FLOOR(-131.7) FLOOR(23.5)
------------- -----------
-132 23
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
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
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.
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, VARIANCEGroup 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.
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.
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');
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.
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;
Will prompt:
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 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
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 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 ]
.sql is used, you can also use the a directory path for the file, if no path is specified then the SQLPATH is used.