sign up! Channels Courses
 

MAURO PICHILIANI (ENGLISH) - MrBool Space


20 last updates MAURO PICHILIANI (ENGLISH)

Video tutorial - Working with Cursors- Oracle PL SQL Course - Lesson 21


Duration: 17:16 min

Summary: The Oracle database uses the SQL language to handle data. This language is based on the manipulations of set or rows in a very fast way and the general recommendation is that developers create solutions based on that set parading. However, sometimes it is needed to create a row-by-row processing with the data that is stored inside a table. In order to address this need the Oracle PL/SQL language provides cursors that get the data from a SELECT statement and allow the developer to read and write one row at the time. This video shows how to create, open, manipulate and close implicit and explicit cursor inside a PL/SQL block to perform row-based operations. The video contains example cursors that are created with the same datatypes of the table’s columns and store many rows as needed.

-->">
3/31/2014 6:19:00 PM





Video tutorial - Handling exceptions - Oracle PL SQL Course - Lesson 22


Duration: 16:44 min

Summary: During the execution of lines inside a PL/SQL can error can occurs because of many reasons. In order to deal with this error Oracle provide a mechanism called exception that allow the developer to trap and handle and provide user-friendly messages to the user. This video discusses how to proper handle the messages generated by Oracle when an error happens inside PL/SQL block by using the EXCEPTION clause. Some examples show how to capture a specific message error and how to handle the technical message provided when such an errors occurs. Finally, the video demonstrate how to create a user-generated message to provide a more adequate description of what happened to the user instead of providing a raw technical explanation that cannot be understandable by the end user.

-->">
3/31/2014 6:19:00 PM





Video tutorial - Working with composite DataTypes - Oracle PL SQL Course - Lesson 20


Duration: 16:34 min

Summary:A variable in a PL/SQL block can have only one data type and can store a single value. However, there are many scenarios when the developer needs to store more than one value inside a single variable or even an entire row that comes from a table. For these situations the Oracle provide composite datatypes such as PL/SQL records and PL/SQL collections. This video explains how to use the TYPE command to create records that store multiple fields. Also, the video explain and show examples of how to create an INDEX BY TABLE which is very similar to the concept of arrays in a programming language. Then the video shows how to create those composite datatypes, how to store data from the tables on those variables and how to create a loop to perform a row-by-row type of processing with the composite data types.

-->">
3/28/2014 9:53:00 PM





Video tutorial - Control Structures - Oracle PL SQL Course - Lesson 19


Duration: 20:04 min

Summary:The control structure commands inside a PL/SQL block are the statements used to handle the flux of execution. They are used to implement business logic inside the block to program an algorithm. This video shows how to use the IF, ELSE, ELSEIF, CASE, LOOP, WHILE and FOR statements inside a PL/SQL block by explaining the purpose, the syntax and how these commands can be used in very simple examples. Once the developer know how to use those control structures he/she can develop many complex business logic inside the Oracle that can address different requirements inside the database without needing to program it on the application programming language.

-->">
3/27/2014 8:56:00 PM





Video tutorial - Interacting with the Oracle Server - Oracle PL SQL Course - Lesson 18


Duration: 17:08 min

Summary: A PL/SQL block may contain many lines of code to implement a business logic or an algorithm and it is subject to the syntax rules of the PL/SQL language. Besides traditional syntax elements it is also possible to use some SQL statements inside a PL/SQL block from the DML (Data Management Language) set of commands. This video shows how to use the SELECT, INSERT, UPDATE and DELETE statement commands inside a PL/SQL and focus on the two main requirements for a SELECT statement inside a PL/SQL block: the INTO clause to assign variable values and the need to affect only one row. This video also discusses some aspects of Oracle implicit and explicit cursors and their attributes.

-->">
12/26/2013 10:35:00 PM





Video tutorial - Writing Executable Instructions - Oracle PL SQL Course - Lesson 17


Duration: 17:29 min

Summary: A PL/SQL blocks is the basic unity for a PL/SQL programmers. This structure may contain many lines of code and it is very important to understand its rules. This video shows how to work with syntax elements of the PL/SQL language inside a block, including delimiters, compound sysmbols, identifiers, comments and nested blocks. The video also discuss invalid values for identifiers, how to name a block and the scope of variables inside a PL/SQL block when there is a block inside another block. Finally, some examples of common operators are presented and a recommendation about the use code conventions is covered.

-->">
12/23/2013 8:07:00 PM





Video tutorial - Declaring Variables - Oracle PL SQL Course - Lesson 16


Duration: 25:36 min

Summary: One of the most important concepts for a developer what work with an Oracle database is the PL/SQL block. This construct allow the creation of complex programing logic like any other programming language and also with the benefits of the SQL statements. This video explains the concept and components of a PL/SQL and focus on how to work with normal variables created in the DECLARE session of the block. The video also explain the two other types of variables that can be used only SQLPlus: host and bind variables. The examples shown in this video demonstrate how to create, assign, use and display the contents of the three types of variables (normal, host and bind).

-->">
12/23/2013 3:13:00 PM





Video tutorial - Working with sequences, indexes and synonyms- Oracle PL SQL Course - Lesson 15


Duration: 15:36 min

Summary: The Oracle database contain many important objects that best be known by a PL/SQL Developer. This video explains and demonstrates how to work with sequences, indexes and synonyms. Sequences are objects employed to create numerical positive or negative sequences that can be used to generate values for columns that have primary keys. Indexes are objects that speed up the execution time of SQL statements such as the SELECT. Synonyms are objects that work like aliases to simplify the naming of object that is stored inside a schema. The video explain these objects and how examples of how to use then in basic usage scenarios.

-->">
11/29/2013 7:07:00 PM





Video tutorial - PLSQL Views - Oracle PL SQL Course - Lesson 14


Duration: 16:00 min

Summary: A view is an Oracle object that encapsulates an existing SELECT statement. Views can be used to many purposes include: bane complex SELECT statements that can be reused, hide sensitive data, and offer a new way to perform INSERT, UPDATE and DELETE statements on the data. This video explains how views can be used, how to create them, which are the basic usage scenario and how to work with then. The video also contains a discussion and the requirements needed to perform data change operations with views and briefly talk about naming columns and internal metadata views.

-->">
11/12/2013 10:41:00 PM





Video tutorial - Working with Constraints - Oracle PL SQL Course - Lesson 13


Duration: 20:08 min

Summary: The constraints are the recommend objects to guarantee the integrity inside the tables of an Oracle database and also to implement basic business rules that affect the data in order to make sure that no data, domain and referential integrity problems happen. This video explains the 5 basic constraints types that can be used on tables: NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK constraints. The examples demonstrate how to use then, their limitations, and the scenario that each constraint can be used. Also, the video show how alter, remove, rename, disable and enable existing constraint by accessing them with their names.

-->">
10/31/2013 9:58:00 PM





Video tutorial - Creating and Managing Tables - Oracle PL SQL Course - Lesson 12


Duration: 19:44 min

Summary: Tables are the most important object the Oracle database. These objects stored the data in the tabular format (columns and rows) and can have relationships with other tables. This video explains the CREATE OR REPLACE command to create tables and its columns. The definition of a table requires the name of the table and the specification of the columns’ names and datatypes. The examples showed include the creation, modification and removal of tables, the TRUNCATE command and also some discussion about the basic datatypes and the scenarios where a table can be renamed and have its columns changed or removed.

-->">
10/31/2013 9:13:00 PM





Video tutorial - Modeling and relationships - Oracle PL SQL Course - Lesson 11


Duration: 16:47 min

Summary: The modeling of entities, attributes and relationships is a very important task that everyone that work with databases must know how to perform. This video explain the basics of conceptual, logical and physical database modeling based on the sample HR database schema provided by Oracle. The process begin by the gathering of requirements phase that include data needs analysis, interviews and observation of existing system. Then, the next step is the conceptual modeling where the entities that should be on the final model are defined. The logical and the physical database modeling phases are also discussed on the video that focus on the practical aspects of the model creation.

-->">
10/30/2013 7:12:00 PM





Video tutorial - Manipulating Data - Oracle PL SQL Course - Lesson 10


Duration: 22:35 min

Summary: The set of statements of the SQL language that manipulate data, i.e. include, change or remove data, are part of the DML (Data Manipulation Language) statements. This video explains how to use the INSERT, UPDATE and DELETE statement in order to change the data on existing tables. The video shows examples of how to insert the data with and without the columns names, how to update the values of columns with the UPDATE statement and how to remove some rows of the tables with the DELETE statement. The examples shown in this video are based on the tables of the sample HR schema provided by the Oracle installation.

-->">
10/25/2013 1:35:00 AM





Video tutorial - SQLPlus options - Oracle PL SQL Course - Lesson 9


Duration: 15:45 min

Summary: SQL Plus is the command line tool used on the operation system console to send PL/SQL commands to an Oracle database. This tools is very important because it is found on every Oracle installation, can be used on a remote SSH shell session, and it is the default tool used on the examples, documentation and scripts provided by Oracle. This video shows how to connect to an Oracle instance, work with SQLplus variables, show and set parameters, and run scripts inside SQLPlus. The professional that knows very well how to use the SQLplus has the advantage over others that only know GUI tools since SQLPlus is the most employed tool used by all DBAs and professional that work with an Oracle database.

-->">
9/30/2013 9:31:00 PM





Video tutorial - Subqueries - Oracle PL SQL Course - Lesson 8


Duration: 16:33 min

Summary: The SELECT statement is the most used SQL command. It contains several clauses such as WHERE, FROM, GROUP BY, HAVING, ORDER BY, and others. This video shows how to use a SELECT statement inside another SELECT in such a way that it is called subquery. The video explain the context, the details, the requirements and the usage of subqueries with aggregated functions and with the operators IN(), ANY(), and ALL(). The video also explain that subqueries should be used with caution on specific scenarios and that they can be replaced by a JOIN on specific situations. The examples are shown based on the tables of the HR sample databases provided by Oracle during the installation of the Oracle 11g Express edition.

-->">
9/27/2013 8:41:00 PM





Video tutorial - Aggregating data - Oracle PL SQL Course - Lesson 7


Duration: 20:04 min

Summary: Tables in a Oracle database usually contains many rows and it is common to work with these rows using aggregation, which is the a calculation that group several rows of a column and return only one value. This video explain how to work the aggregation functions AVG(), COUNT(), MAX(), and MIN() in order to obtain a data summary. Also, the GROUP BY and the HAVING clause of the SELECT statement are presented through examples in the HR database schema provided by Oracle. Finally, the video shows how to combine the GROUP BY, HAVING, and the ORDER BY clauses to great a report that aggregate and group data by two different columns.

-->">
9/27/2013 7:58:00 PM





Video tutorial - Showing data from multiple tables - Oracle PL SQL Course - Lesson 6


Duration: 25:04 min

Summary: Modern database models contains many tables that are related through relationships. Any professional that work with databases must know how to work with more than one table in a SELECT statement. Therefore, this video explain how to use more than one table on the SELECT statetment via aliases and how to perform simple joins in the tables of the sample HR schema provided by Oracle. The examples include equijoins, non-equijoins, right and left outer joins, self joins, and ANSI joins. All the statements are explained and described according to the rows they return based on the data model of the HR schema represented in an Entity Relationship (ER) diagram.

-->">
9/26/2013 1:32:00 AM





Video tutorial - Single-Row Functions - Oracle PL SQL Course - Lesson 5


Duration: 22:06 min

Summary: Single-row functions are the primary way to simply modify data when reading rows in a Oracle table. These functions can receive none, one or more parameters and returns a single value. This video explain the character, number, date, conversion and general categories of functions manipulates the data. The characters functions shown include LOWER(), UPPER(), INITCAP(), CONCAT(), SUBSTR(), INSTR(), LENGTH(), LPAD(), RPAD() and TRIM(). The number functions shown include the ROUND() and the TRUNC() functions. The date functions shown include the SYSDATE, MONTHS_BETWEE(), ADD_MONTHS(), ROUND() and TRUNC(). The conversion functions shown are the TO_CHAR(), TO_NUMBER() and TO_DATE(). The general functions presented are the NVL(), NVL2(), DECODE() and the CASE structure.

-->">
9/20/2013 8:18:00 PM





Video tutorial - Filtering and sorting data - Oracle PL SQL Course - Lesson 4


Duration: 24:15 min

Summary: The SELECT statement have many clauses. In order to filer the rows returned by the Oracle database in a SELECT statement we employ the WHERE classer with one or more filtering conditions that specify how we want to filter the data. To sort the data we use the ORDER BY clause. This video explain how the WHERE and the ORDER BY clauses of the SELECT statement work in the PL/SQL language and provide many examples of the operators that can be used in the WHERE clause, include the equal (=), BETWEEN, IN() and LIKE. The video also show some examples of the operators AND, OR and NOT to control how more than one filter can be used to filter the rows in a SELECT statement. Finally, the ORDER BY clause is explained with the ASC and DESC column modifiers.

-->">
9/20/2013 1:05:00 AM





Video tutorial - How to work with basic SELECT Statements - Oracle PL SQL Course - Lesson 3


Duration: 24:00 min

Summary: The SELECT is the most important SQL statement. It allows the user to read data and format report according to requirements to answer business questions. This video shows the basic syntax of the SELECT statement with the FROM clauses. The examples demonstrate how to specify several columns, how to rename a column (use of an alias), how to perform operation that manipulate columns (append and sum values), and how to eliminate duplicate values with the DISTINCT clause. The examples are executed on the HR sample database provided by Oracle and an Entity Relationship diagram is shown in the video in order to help the understanding of how the SELECT statements works.

-->">
8/22/2013 11:25:00 PM





 

pichiliani@uol.com.br

Mauro Pichiliani has the Master of Science degree on collaborative systems by the Aeronatics Institute of Technology (ITA) in Brazil. He is a specialist on database technologies with more than 8 years of experience on the industry, using products such as SQL Server, Oracle, MySQL and PostgreSQL. He achieved the Microsoft’s MCP, MCDBA and MCTS certifications and currently wrotes for a printed Brazilian publication called SQL Magazine and for the on-line web site iMasters (www.imasters.com.br). His e-mail address is pichiliani@uol.com.br
Archive updates
 2014
 2013
 2012
 2011
 2010
 2009
 2008

Stats:
Total posts: 130
What people think:
Technical content:
People learning:
His posts are helpful?
78 5