- Overview of Relational Databases, SQL and PL/SQL
- Discuss the Theoretical and Physical Aspects of a Relational database 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.
- 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
- Data stored under columns must be of the same data type
- Each row is unique (No duplicate rows)
- Columns have no order in them
- Rows have no order in them
- Columns have a unique name
- 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.
- The information Rule :Information is to be represented as data stored in cells.
- The guaranteed access rule : Each data item must be accesible by a comination of table name + primary key of the row + column name .
- NULLS must be used in a consistent manner: Nulls should be simply missing data and have no values.
- An active, online data dictonary should be stored as relational tables and accesible through the regular data access language.
- 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.
- 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.
- There must be set-level inserts, updates and deletes :
- 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.
- 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.
- Integrity Dependance : Integrity rules should be stored in the data dictonary.
- Distribution Independance : A database should continue to work properly even if distributed.
- The nonsubversion rule: If low-level access is permitted it should not bypass security or integrity rules.
/-------------/ /------------------/ /------------/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:
/ Relational /--------/ Named Attributes /-----/ Tuples /
/ Tables / / (Columns or / / (Rows or /
/-------------/ / Fields ) / / Records) /
/------------------/ /------------/
CODDS TWELVE RULES
- Separate Tables and Indexes
- Place large Tables and Indexes on disks of their own
- Place frequently joined tables on separate disks, or cluster them.
- Place infrequently joined tables on the same disks if necessary (if your short on disks)
- Separate the RDBMS software from tables and indexes.
- Separate the Data Dictonary from tables and indexes.
- Separate the (undo) rollback logs and redo logs onto their own disks if possible.
- Use RAID 1 for undo or redo logs
- Use RAID 3 or 5 for Table Data.
- Use RAID 0 for indexes.
- Describe the Oracle Implementation the RDBMS and ORDBMS. 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 )
- Relationships as Datatypes
- Inheritance
- Collections as Datatypes, including nesting (containers)
- User-defined (extensible) datatypes
- Improved large objects (LOBs)
- Object Types: Records or classes
- Object Views :
- Object Language: Extensions to the Oracle SQL and PL/SQL
- Object APIs : Objects supported through Oracle precompilers PL/SQL, OCI.
- Object Portability : Through the object type translator (OTT) which can port for example an Oracle8 object type to a C++ class.
- As a column of a relational table
- As an attribute within another object type.
- As part of an object view of relational tables.
- As the basis for an object table.
- As the basis for PL/SQL variables.
- Describe the use and benefits of PL/SQL
- Writing Basic SQL Statements
- List the capabilities of SQL SELECT statements
- Execute a basic SELECT statement
- Differentiate between SQL statements and SQL*Plus commands.
- Restricting and Sorting Data
- Limit the Rows retrieved by a query
- Sort the rows retrieved by a query
- Single Row Functions
- Describe various types of functions available in SQL.
- Use character,date and number functions in SELECT statements There are functions returning Character Values :
- CHR : Returns the character value given character number
select CHR(37) a, CHR(100) b, CHR(110) c from dual;
RETURNS: - CONCAT :Returns string1 concatenated with string2
select CONCAT('perro','gato') from dual;
RETURNS: - 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: - LOWER :Returns the string in all lowercase
select LOWER ('ESTO ES paRA el FUNcioNaMienTo de oracle ') "Minusculas" from dual;
RETURNS: - ABS : Returns the absolute value of a number
select ABS(-24),ABS(-414),ABS(24) from dual;
RETURNS: - FLOOR: Returns the largest integer equal to or less than the number
select FLOOR(-131.7), FLOOR(23.5) from dual;
RETURNS: - 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: - ADD_MONTHS: Returns the date plus x months specfied.
select ADD_MONTHS('1999-12-2',12) from dual;
RETURNS: - Describe the use of conversion functions.
- 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
- Subqueries
- 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
.sql
is 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.
- The major objective of physical design is to eliminate or at least minimize contention . Follow these rules to avoid contention :
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:
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.
A B C
- - -
% d n
CONCAT('P
---------
perrogato
La prueba
-----------------------------------------
Esto Es Para El Funcionamiento De Oracle
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
ABS(-24) ABS(-414) ABS(24)
---------- ---------- ----------
24 414 24
FLOOR(-131.7) FLOOR(23.5)
------------- -----------
-132 23
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
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.
v. Displaying Data from Multiple Tables
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, VARIANCEOnly 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 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;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
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;
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
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
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 ]
- Manipulating Data
- Describe each DML statement
- Insert a row into a table
- Update a row in a table
- Delete a row in a table
- Control Transactions
- Creating and Managing Tables
- Describe the main database objects
- Create Tables
- Describe the Datatypes that can be used when specifying column definitions
- 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.
- varchar(n): A variable length character string, that will take up space accordingly
- Other Datatype: CHAR,NCHAR,VARCHAR2, NVARCHAR,DATE,NUMBER,LONG,RAW,LONG RAW, MISLABEL, BLOB,CLOB,NCLOB,BFILE,ROWID y UROWID
- Alter Table Definitions
- Drop,rename and truncate tables.
- Including Constraints
- Describe constraints
- Describe constraints
- Create and maintain constriants
-
not null
: Requires a value for this column. -
unique
: Two rows cant have the same value in this column ( side effect, Oracle creates an index on this column ) -
primary key
:Same asunique
except that no row can have a null value, also it is possible to refer from other tables to this key. -
check
: Limit the range of the column. Example:rango integer check ( rango > 6 and rango <= 9)
-
references
: This column can only contain values present in another tables primary key . Example:usuario not null references empleado
INSERT, UPDATE, DELETE, (INSERT, SET TRANSACTION, EXPLAIN PLAN)
insert into parientes(miembro,nombre) values (12,'Alexia');
insert into relatives(member,name) values(12,'Alexia');
update parientes set apellido='Rubio' where miembro=12;
update relatives set lastname='Rubio' where member=12;
delete parientes where miembro=12;
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;
commit;
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
INDEX, SEQUENCE, TABLE ,VIEW,CLUSTER, PROCEDURE,SYNONYM,TRIGGER, CONSUMER GROUP,RESOURCE PLAN ,FUNCTION,INDEXTYPE, JAVA CLASS, JAVA RESOURCE, LIBRARY, OPERATOR, PACKAGE, PACKAGE BODY, QUEUE.
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);
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
STORAGE (MAXEXTENTS 75) LOGGING;
ALTER TABLE emp
PCTFREE 30 PCTUSED 60;
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.
A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.
A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.
Constraints can also apply to multiple columns:
create table los_mejores (
identificacion integer primary key,
nombre_titulo varchar2(45),
edad integer,
unique(edad,identificacion)
);
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
- Creating Views
- Oracle Data Dictonary
- Other Database objects
- Controlling User Access
- Declaring Variables
- Writing Executable Statements
- Interacting with the Oracle Server
- Writing Control Structures
- Working with Composite Datatypes
- Writing Explicit Cursors
- Advanced Explicit Cursor Concepts
- Handling Exceptions
Comentarios