Manuscripts Is Elf Schooling

Topics: Hands-On 01 - Developer Tools (Schema Builder) Schema Builder allows you to define the tables, views, snapshots, synonyms, constraints, and table relationships that will make up your database. You can visualize your database design, including the tables, columns, data types, and relationships, and then execute that design. Query Builder will facilitate you to write “SQL” code by providing a visual interface to the database objects being linked. In this Hands-On, you will learn how to: Create the “students” table, Define columns, Define attributes, Define Primary key, Define require field, Define Unique Key, Grant Privileges to public, Check what other users can access, Remove table, and Check table access. More Resources by Google: Manuscript Open the “Schema Builder” tool. Login as “iself” password “schooling,” and click on “connect.” Click on the “OK” button. Maximize the window. Choose the “Schema” option, select “Create Table”, then click on ”Table.” Drag your “plus sign” cursor and release it. Resize your object. Choose the “Data” option and click on “Rename Table.” Change the “Table1” name to “Students,” and then click “OK” to rename the table. Double click on the plus sign to add new columns. Type “Student_id;” its column attributes; and make it a Primary Key. Click on "new column." Type "first_name," its column attributes; and make it a required field. Click on "new column." Type "last_name," its column attributes; and make it a required field. Click on "new column." Type "ssn" for social security number, its column attributes; and make it an unique key. Click “OK.” This is the table that will be created.

Choose the "schema” option, and click on "Show SQL." Here is your “create table” statement. Click on “COMMIT” to create the table, and then Close the window. Now, the table was created. Choose the "File" option, and click on "Set privileges.” Assign the “Students” table to the public. Give select and insert privileges to the public. Click on "apply," and then close the window. Now, the public users can only “insert” and “select” from the table. Now, lets drop the table. Choose the “Schema" option and click on “Remove.” The gray color indicates the object was removed. Exit from “schema builder," and Click on "yes" to commit changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 02 - Developer Tools (Query Builder) Query Builder facilitates you to write your simple or complex query. You can save your query at your client-side. It allows you to set your table relationships and visualize your database objects that being linked, including the tables, columns, and datatypes. In this Hands-On, you will learn how to: Create a query, Join Multiple tables (the “dept” table and the “emp” table), Set table relationship, Execute Query, Sort query, Add where clause condition, Listing query, Saving query. More Resources by Google: Manuscript Open the “Query Builder” tool. Login as “iself” password schooling, and then click “CONNECT.” Click on the “OK” button. Click "OK" to create new query. Select the "department" table and then click on “include.” Select the “Employee” table and click on “include.”

Close the window. Maximize the "Query Builder" window. Click on the "set table relationship” icon. Type “dept.deptno” the department number of the department table on the "A" box; and “emp.deptno” the department number of the employee table on the "B" box. Then Click "OK." Select the department name column, the employee name column and the salary column. Click on the “Show SQL” icon. Notice: The “where” clause in the sql statement. Click "execute." Check the output. Click on the "department name" column. Choose the "sort ascending” icon; and then go to the “Show SQL” window and execute the query. Table was sorted. Close the window. Click on the condition box and add a “where” clause to display all the employees of the sales department whom their salaries are more than 2000. Press the enter key. Click on the “Show SQL” icon. Notice: The where clause condition were added to the “sql” statement. Execute the statement. Check the output and close the “Show SQL” window. Click on the “Query” window; and then choose the “File” option and click on “Save as” or “Save” to save the query in the “iself” folder. Click “OK.” Name the file “emp_salary” and click on “Save;” and exit From “Query Builder.” Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 03 – Procedure Builder

Procedure Builder allows you to develop stored procedures, functions, packages, and triggers in Oracle. Many developers have found the SQL*PLUS and favorite text editor approach cumbersome. You use Procedure Builder for ease of writing program development, debugging, and version control. In this Hands-On, you will learn how to: Creating PL/SQL Procedure using Procedure Builder, add a record into the “dept” table using PL/SQL. In this Hands-On, you will learn the basic fundamentals of PL/SQL block. The Core Subjects are: 1- Developing a PL/SQL block 2- Types of block 3- Anonymous or unnamed block 4- Components of a PL/SQL block a. Declaration b. Body or Execution c. Exception 5- Cursor a. Implicit cursor b. Explicit cursor 6- Parameters in cursor More Resources by Google: Manuscript Go to “MS-DOS.” Change directory to the iself directory and login to “sqlplus” as "iself/schooling."

>> cd ..\iself >>sqlplus iself/schooling Write a PL/SQL block to use only the "body" section with PL/SQL statement.

>>

begin null; end; /

Use the slash (/) to compile and run the block. Add the "declaration" section with no variables. Then compile and run the block.

>>

declare -- no variable begin null; end; /

Use the “Set serveroutput on” to display the buffer used by dbms_output.

>>

set serveroutput on

Write a PL/SQL block, to output the "Hello iselfschooling" message.

>>

begin dbms_output.put_line('Hello iselfschooling'); end; /

describe the department table.

>> desc dept Write a PL/SQL block, to declare a department name variable with the same datatype of the department name. Then assign "HR" to the variable and output the variable.

>>

declare v_dname varchar2(14); begin v_dname := 'HR'; dbms_output.put_line(v_dname); end; /

Save the PL/SQL block as “test_myblock.” Then go to notepad and open the PL/sql block from the “iself” directory. Use the %type keyword, to declare a variable as the same datatype and size of the department name column of the dept table. Then save the file. Go to “SQLPLUS.” Get the file. And run it. The same output!

>>

declare v_dname dept.dname%type; begin v_dname := 'HR'; dbms_output.put_line(v_dname);

end; / Go back to “notepad,” use the %rowtype keyword to declare a variable row type. Assign ‘HR’ to the “department name” item of the defined variable row. Output the variable.

>>

declare v_drec dept%rowtype; begin v_drec.dname := 'HR'; dbms_output.put_line(v_drec.dname); end; /

Then save the file. Go to “SQLPLUS.” Get the file. And run it. The same output! Back to “Notepad.” Use the implicit cursor to query the department table information where deptno = 30. Check if no record was found then print “Record was not found.” Else print the department name only.

>>

declare v_drec dept%rowtype; begin select deptno, dname, loc into v_drec.deptno,v_drec.dname, v_drec.loc from dept where deptno = 30; if sql%notfound then dbms_output.put_line('Record was not found.'); else dbsm_output.put_line(v_drec.dname); end if; end; /

Then save the file. Go to “SQLPLUS.” Get the file. And run it. Back to “Notepad” Modify the PL/SQL block to move the entire record into the variable row. Then save the file. Go to “SQLPLUS.” Get the file. And run it. The same result! Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 04 - Developer Tools (Form Builder) You use Form Builder to simplify for the creation of data-entry screens, also known as Forms. Forms are the applications that connect to a database, retrieve information requested by the user, present it in a layout specified by Form designer, and allow the user to modify or add information. Form Builder allows you to build forms quickly and easily. In this Hands-On, you learn how to: Create a Datablock for the “Customer” table, Create a layout, Use “content” canvas, Use “execute query”, Navigate a table, Use next, previous record, Enter query, Manipulate table’s record, Insert, Update, Delete and Save record. More Resources by Google: Manuscript Open the "Form Builder" tool. Click on "Cancel." Highlight "Database Objects." Choose "File," then "Connect." Login in as “iself” password “schooling,” then click “CONNECT.” Expand "Database Objects," to see all schemas. Highlight module1. Choose “File,” select “Save as” to store the new object in the “iself” folder and save it as customer data entry. "customer_de." DE stands for Data Entry.

Notice that the “window” object was created by default. Highlight "Data Blocks,” and click on the "create” icon. Use the “Data Block Wizard” and click "OK." Click “NEXT.” Click “NEXT” again. Click on “browse.” Highlight the "customer” table; then click "OK." To choose all columns, click on the two arrow signs. To choose selected columns, click on the one arrow sign. Practice this. And then select all columns, and click “next.” Use the default checkmark radio button, and click "Finish." Click Next.” Select the "new canvas" option. Here are the different types of canvases to select. Select "content," then click “Next.” Select all the columns. Click “Next.” Change size or prompt if needed. Click “Next.” Select "Form," if you want one record at a time. Select "Tabular," if you want more than one record at a time. Then click “next.” Type the "Frame Title" and click "next." Click "Finish." This is the output layout screen. Make some window adjustments and then run the form. The object module should be compiled successfully before executing the Form. Click on the "Execute Query" icon. Click on the "Next Record" icon to navigate to the next record.

Click on the "Previous Record" icon to navigate to the previous record. This is an easy way to navigate through the “Customer” table. Click on the "Enter Query" icon to query selected records. Query all the customers whom their creditlimits are more than 7000. These are all customers whom their creditlimits are more than 7000 dollars. Query all customers whom their names start with the letter "E." These are all customers whom their names start with the letter "E." Query all customers whom their names start with the letter "E" and their creditlimits are more than 7000 dollars. You only have one customer. Now, query all customers. Click "Insert Record" to add new customer. Duplicate the last record, if you don’t want to type all duplication. Apply the changes. Click "next record" and "previous record" to navigate through the records and the one was added. Click "Save" to commit the insert statement. Click "Remove Record" to delete the record. The record was deleted. You can also lock the record. Exit the FORM Runtime. Click “YES” to save changes. Click “OK” for acknowledgement. Save the Form. Now, you should practice this over and over, until you become a master at it. Good Luck Topics: Hands-On 05 - Developer Tools (Report Builder) Now, your client is a stock’s broker that keeps track of its customers’ stock transaction. You have been hired to write them reports based on their reports layout requirements. Your client wants you to create a simple listing report to show list of the stock trades by using stocks table for their brokerage company. The user layout format requirement for date is MM-DD-YY, for dollars is $99,999.90 and for quantity number is NNN.N0. You will learn how to: use report wizard, use object navigator, use report builder, save report, use “data model”, use property palette, work on query and group box, see report

style, use tabular style, navigating through report’s record, change the format mask for dollar, numeric and date items. More Resources by Google: Manuscript Open the "Report Builder" tool. Highlight "Database Objects." Choose "File," then "Connect." Login as “iself” password schooling, then click “CONNECT.” Highlight the "untitled" report. Choose “File,” and select “Save as.” Go to the "iself" folder, and save it as report number 1 stock history ("rpt01_stock_history"). Double click on the "Data Model" icon. Click on the "sql" icon. Then drag the plus sign cursor and click it anywhere in the “Data Model” screen. Query the “Stocks” table sorted by the symbol column. Click “OK.” On the “SQL” box, right click and select “property palette.” Change the name to a more meaningful naming convention. This is the query. Close the window. Do the same for the "groups" box. Choose "Report wizard." Type the report Title. Choose the style you need. Notice! The report style would be displayed on the left screen. Choose a different style to display its report style. Checkmark the "Tabular" style. Click “NEXT.” Click “NEXT” again. Select all the columns to be displayed. Click “NEXT.” Click “NEXT.” Click “NEXT” again. Change the labels, if needed.

Use the default template and click “finish.” Here is the report. Format the report layout. To resize an object , select it and drag its handler to the preferred size. To move an object, select and drag it while the cursor is on the object. This is a simple report. Click on the "next page" or "previous page" icon to navigate through the report. Do the same with the "first page" or "last page" icon. Use the “zoom in” and “zoom out” icon to preview the report. To change the "format mask" of a column, the column should be selected. Apply the “$” sign, "add decimal place," and the “right justify” format to the all currency columns (Todays Low, Todays High, and current price) Apply commas to the “traded today” column. Make any adjustment you like. Select the "trade date" column and open its "property palette." Change the date "Format Mask" property and make it “year 2000 complaint.” Notice! The date format was changed. Navigate through the report pages. And then save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 06 - Developer Tools (Graphics Builder) Graphics Builder lets you to create interactive graphical displays of the data in a database. You can include these graphics in your forms or reports. Graphics Builder provides a complete set of drawing and editing tools, along with a “Chart Wizard” to simplify the process of using the tools. In this Hands-On, you learn how to: Create a chart, Write a query, Execute a query, Modify a query, and Save a chart. You will create a chart to show a salary distribution in each department. More Resources by Google: Manuscript Open the “Graphics Builder” tool. Create a chart to show a salary distribution in each department. Click on the "chart” icon. Drag the cursor and click anywhere on the “layout editor.”

Type the query name. Write a query to show the total salaries paid for each department. Click "execute" and login as iself password “schooling” then click “CONNECT.” Check the query. Click “OK.” Type the chart’s name and it's title (Salary Distributions). Select the type and subtype. Click “OK.” This is the chart. Change the department number to department name. Click on the “SQL” icon. Join the employee table with the department table and add the department name to the query. Click “OK.” Click “Yes,” to remove the department number from the chart. This is much easier to understand, than the previous chart. Save the chart in the “iself” folder using the “file system” option. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 01: Tab canvas, Master-detail relationship Create an easy to use data entry Form for your client, for an order entry system they wish to have developed. Working with you, the User has requested a more robust application. Create a Form that contains three tabs for each Entity. The tabs should be labeled as "Customers", "Orders", and "Items." In this Hands-On, you will learn how to: Use tab canvas, Master-Detail relationship. More Resources by Google: Manuscript Collapse the customer data entry Form. Select “FORMS” and click on the "create" icon. Choose the “File” option, then select “save as” to store the form in the "iself" folder.

Name it, "customer_orders." Select "data blocks" and click on the "create" icon. Use the “Data Block Wizard” option. Click “OK” Click "Next." Click on "Browse"; and select the "customer" table and then “OK.” Select all columns. Click on “NEXT” and then "Finish." Use the "Tab Canvas" type with a “new tab page”, then click “NEXT.” Select all the columns. Click “Next.” Click “Next” again. Next. Type the "Frame title" and then click “next” and “finish.” This is the form layout. Collapse the customer “data blocks.” Create a new tab page for the customer's order. Select “Data Blocks” and click on the "create" icon. Click “OK.” Then "Next." Type the table name "ord."—for the order table. Then "refresh." Select all columns; then click "next." Click "create relationship" to establish master-detail relationship. Click “OK;” and unmark "Auto-join data blocks." Then click "Create relationship" and “OK.” Use “Customer” as the Master table and click “OK.” Change "detail item" and "master item." This is the relationship. Change "tab page" to "new tab page” and click "next." Select all columns and then deselect “custid.” Since the customer is known. Use "tabular" since there are more than one order for each customer. Type "Title"; “Records Displayed”; and checkmark “Display Scrollbar.” Now, there are two canvases: One for customer information and the other for their orders. Collapse these two "data blocks." Select "Data Blocks" and create a new “data block.”

Choose the same options. Click on “Browse.” And select the “item” table. Select all columns. Un-checkmark "Auto-join data blocks;" and click on "create relationship" and then “OK.” Use the order (“ORD”) table as the Master table. Establish master and detail relationship. Remember always you can delete the relationship and create it again. Select "new tab page," then click "next." Select all columns except the order ID column (“ordid”), since the order is known. Select "tabular," since there are more than one item in an order. Type “Frame Title,” “Records displayed” and checkmark “Display Scrollbar.” Navigate through the tab pages. Notice that page3, 5, and 7 don't mean anything. Select page3, right click and open its "property palette." Change the “name” and “label” to customer. Press the enter key. Notice! The name always changes to upper case. Click on page5 to replace its properties and do the same for “Orders.” Be sure to press the enter key to confirm the changes. Click on page7 to replace its properties and do the same for “Items.” Then close the window. Now, the tabs are more meaningful. Navigate through the tab pages. Collapse the objects to view items easier in the ”object navigator” window. Select “customer_orders,” and right click to open its "property palette." Change "First Navigation Data Block" to “Customer.” Close the window. Click on the "Run" icon to compile and execute the module. Click "execute query" to query the customer information. Navigate through the “tab” options. On the "orders" tab, select an item.

Notice on the “items” tab, all its items correspond to the selected order. Repeat this. On the customer tab, position the cursor on “Customer ID” and navigate through the table by clicking on “NEXT Record.” Do the same for the previous record. Click on "Enter Query.” Type 106, and click "Execute Query." Navigate through the customer’s orders and its items. Close the window. Save the changes. Remove the layout screen. Remove the “customer_de” program. Practice to collapse and expand an object in the “object navigator” window. Then remove the customer_orders Form. Click yes, to apply the changes. Practice to open the Form from the “iself” folder. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 02: Tooltip, Hint, Format Mask, Properties Build Help functionality on the data entry Form of Hands-On 01, at the items' level. During your prototyping iterations, the User thought it would be useful to have a Help option on the Form. A Help function will enable them to view descriptions of Form items as they move from item to item., and see helpful hints for each, on format requirements and content. For example, date should be keyed in the format MM-DD-YY, or currency amounts displayed with the dollar '$' sign, with format mask $99,999.99. In this “Hands-On” you will learn how to: Use and change the Help, Hint, and format mask of properties. More Resources by Google: Manuscript Open the “customer_orders” form from the “iself” folder. Click on the “customer_orders” form. When the color changes to blue. Change and then save the Form name as version 02. (This way the original form is untouched.) Choose the "Tools" option and select "Layout Editor."

Adjust the window if needed. Select the "custid.” And open its "property palette” using right click. Go to the “Tool tip” property and type "Enter customer id." Click on the “Name” item. Notice: the “custid” is changed to “name.” Set "Display Hint" to "yes"; then type the hint on the "Hint" Box. Press the entry key and then close the window. You can do the same process for each item in the canvas. You will change the “Comments” item properties and its layout. Select the customer's frame and click on the “delete” key to remove it. Select the "Comments" item and expand it to allow for multi-lines. Open its "property palette." Set "multi-line" to "Yes." Close the window. Go to the "Orders" tab. Delete its frame. Open the “orderdate” property and change its "format mask." Press the enter key to apply all the changes then close the window. Do the same for the "shipdate" item. Press the enter key and close the window. Select the "total" item and open its property palette. Change its format mask. (to $99,999.00.) Change “justification” to right. Close the window. Go to the "items" tab. Select "itemtot" and open its property palette. Change its "justification" and "format mask" properties. Do the same for the "actualprice" item. Repeat the same for the quantity item ("Qty"), but don't change it's format mask. Remove its frame; and then navigate through the tab pages. Before to run the application, collapse all the "Data Blocks" objects. Click on the "Run” icon, to compile and run the application. If there are any errors in the compilation, you will get them this time.

Click 'Execute Query.' Navigate through the application and test the modification changes. Notice! On the “Orders” tab page, the format mask of “orderdate,” “shipdate,” and “Total items” were changed. On the “Items” tap page, the format mask of “actualprice,” “qty,” and “itemtot” were changed. Try to navigate to see the changes. Notice! The quantity item (“qty”) doesn't have comma. Close the form. Open the “Qty” item property and change it's format mask. Close the property window and run it again. Click “Execute Query” and navigate through its records. Go to the customer tab and test the help function. This is an example of tooltip. This is an example of help hint. On the comments box, write multi-lines comments to test the modification. Save the changes. Changes were saved. Navigate through the records and see the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 03: LOVs, Record Group Add a List Of Values (LOV) to the Form. Provide the User with a list of customer names by adding a LOV on the Form. This will help the data entry clerks find a customer quickly, as they are working. This should help the data entry clerks not to ask their customers’ ID. The list should contain at a minimum, the customer's name and ID. In this Hands-On, you will learn how to: use the List of Values (LOV), and group records. More Resources by Google: Manuscript Save the Form name as version 03. Choose the "Tools" option, these are the Form tools that you can use. Select "layout editor."

Select the LOVs object known as "List of values." Then click on the "create" icon. Click “OK.” Click "Next." Write the “sql” query statement. Then click “next.” Select all the columns. Click “Next.” Click on the "return value" box; then click on "Look up return item." Select the "customer.custid" item. Click "OK." Click “Next.” You see "custid" in the “return value” box. Click “next.” Type the title. Apply changes if needed. Click “next.” Click “next” again. Move “Custid” to “Assigned item.” Click on "Finish." Open Record Groups’ property palette. Change the name to a more meaningful naming convention; and then press the enter key. Open the “LOVs” property palette. Notice that the “Record Group” name was changed here, too. Change the name to a more meaningful naming convention. Go to the “Customer” data block. Open the "custid" property palette. Notice that the list of values was assigned to it. Close the window. Click on the "Run” icon, to compile and run the application. Notice the when the cursor is on “custid,” the "list of values" message will be displayed. Click on the "Enter Query" icon to query a specific customer.

Click on “HELP”, to see all the function keys. Notice that the “F9” key is used for the “list of values” item. Press “F9,” while the cursor is in the "custid" item. A list of all the customers will be displayed. Select "Every Mountain" and then click “OK.” Notice that its “Customer id” was returned. Click on “Execute Query,” and navigate through the application and test the changes. Click on “Enter Query,” and Press on the “F9” function. Type the letter "W." then select the customer. Click "Execute Query." Navigate through the application. Then close the window and save the form. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 04: Radio Buttons Add Radio Buttons to Form to query the tables. The User has indicated that she/he keeps track of her/his salesmen's commission plans. The company has a commission plan "A", "B", "C", and Blank for a commission plan is a not applicable to salesman. You need to perform the following: 1. Change the commission plan column to the radio buttons. The Radio Buttons are useful when there are two or more possible values but only one value can be true. Change the "order tab page" to implement the radio buttons. In this Hands-On, you will learn how to: Add and use Radio Buttons. More Resources by Google: Manuscript Change the "commission plan" item from the "text item" to the “radio button." Before any changes, first wait and plan to picture the changes. Expand “Data Blocks.” Save the Form name as version 04. Form was saved. To expand an object, click on the plus sign. To collapse an object, click on the minus sign.

Expand the order's data blocks then its items. Open the “Commission Plan” property palette. Change its item type to "Radio Group." Notice the icon next to the commission plan. Change "Initial Value" to "A," then press the enter key or click on any box to apply the changes. Close the window. Notice that the commission plan has disappeared. Expand commission plan. Do some layout adjustments before creating the radio button. Select the shipdate, total and scrollbar items and move the cursor inside any handlers and drag the selected objects to the right. You can also click on the right arrow key to move the objects. Try it number of times. Deselect the object. Click on the “radio button” icon. The cursor changes to a plus sign. Click on the layout and select the radio group "commission plan" and click on “OK.” Open its property palette. Change its name to A. Change “Label” to blank. Change "radio button value" to A. Change "Prompt" to A. Change "Prompt Attachment Edge" to top. Close the window. Make some layout adjustments. Copy the object and paste it three times and place them in the “commission plan” radio group. Use the right arrow key to move them. Notice that the copy of the object goes to the top of an existing object. Open their property palettes. And change their "name", their "radio button value", and their "prompt" properties. The blank means that the commission plan is not applicable to this transaction. Close the window. Do some layout adjustments. Navigate through the tab pages layout.

Run the Form. Click “Execute Query” and navigate through the customers. Test and check the changes. When you are done with all the testing, then close the application and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 05: Display items, Summary and Function columns Perform the following: 1- Modify the "Items" tab canvas layout to generate a new item called "itemtotal". This field will be populated from other “Item” table’s column during order entry. (quantity * actualprice) 2- Add a display field to calculate the grand total of all calculated field call "itemtotal" for each customer's order. In this Hands-On, you will learn how to: display fields, summary and formula functions. More Resources by Google: Manuscript Save the Form name as version 05. Go to the “Items” tab and move the “itemtot” column and scrollbar to right. The “Block” box should be the "item" block. Select “Display item” and click the cursor next to the “quantity” item. Adjust its size. And open its property palette. Change “name” to itemtotal. Change “Justification” to right. Change “Datatype” to Number. And its “format mask.” Change "Calculation Mode" to Formula, and type the formula on the “Formula” box. The colon next to an item, references to a binding variable on the screen such as actualprice and quantity. Change "database item" to no, because this is not a column from the item table. Change "prompt" to "itemtotal.” Change "Prompt attachment edge” to “Top.” Then close the window. Check the changes. Add another "Display Item" to calculate the totals of “itemtotal.” Notice that the “Block” box on the canvas window points to the “item” block. And the “Number of Items Displayed” on this object is 5.

Open it's property palette. Change the name. Change “Justification” to “Right.” Change “Data Type” to “Number.” Change “Calculation Mode” to “Summary.” Here, the formula property is not applicable. Change “Summary Function” to “SUM.” Change “Summarized block” to “Item.” Change “summarized Item” to itemtotal. Change “Number of items displayed” to 1. Change “Database item” to “no.” Type the prompt property. Change “Prompt attachment edge” to “Start.” Then, close the window. Make some window adjustments. Open the item “data blocks” property palette. Change "Query all records" to "yes." Close the window. Navigate the application's layout. Then run the application. Click “Execute query” and navigate through the application. Notice that the itemtotal column matches with the itemtot column. There is no “Format mask” for the “order total” item. Close the window, Open the order total property palette. Change its format mask. Close the window and run the application. Execute the query and navigate through the application. When you are done with all your testing, then close the application and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 06: Triggers (POST-QUERY) Your application only shows products' ID. Your client wants to see product's description since product's ID does not tell them what is the product. Remove duplicate item information from "Items" tab canvas; and add a new item to display product's description in the "Items" tab canvas. In this Hands-On, you will learn how to: use the POST-QUERY trigger. More Resources by Google:

Manuscript Save the Form name as version 06. Adjust the window, then select the "itemtot" column and press the "delete" function key. to remove the object since it is duplicated. Make some window adjustment to make space for the product description. Add a display item in the blank space and make some sizing adjustments. Open its property palette. Change name to “PRODUCT_DESC.” Change "database item" to "no." On “Prompt” type “Product Description.” Change “Prompt Attachment edge” to " Top.” Then close the window. Do some window adjustments if needed. Notice that the "product description" is not in the item table, therefore you need to create a trigger to query that information. Create a "Post-Query" trigger for the item's “data blocks.” Write a query to move "product description" into the display item for each "prodid" on the screen. Compile it. Successfully compiled. Make an error, to see the error messages. Correct the error and compile it again. Successfully compiled. Close the window. Navigate through the application layout. Then run the application. Execute the query and then navigate through the application. Check the changes. Test the "product description." Check if the description is right. Close the window and save all the changes. Now, you should practice this over and over, until you become a master at it.

Good Luck Topics: Hands-On 07 - Horizontal Toolbar, Push Button, Trigger (when-buttonpressed) Adding more functionality to the application. The client needs a horizontal toolbar to have on their Form. The toolbar should contain all the frequently used options. This will make ease of use of some options. The following are the list of all the frequently used functions they expect to use: Move to previous record, Move to next record, Add new record, Delete current record, Save work, and Exit the application. In this Hands-On, you will learn how to: create and use horizontal toolbar, create and use push button item, create "WHEN-PUTTON-PRESSED" trigger, write and compile trigger. More Resources by Google: Manuscript Save the Form name as version 7. Expand "Canvases" and click on the "create" icon. Open the new canvas property palette. Change “name” to "PB_BLOCK.” Change "Canvas type" to "Horizontal toolbar." Close the window. Open the PB_BLOCK canvas layout. Notice that, it is too big to be the "horizontal toolbar" canvas. Change the size. Navigate through these two canvases. Expand “Data Blocks” and Create a new "data block manually." Open its Property Palette. Name it the same as the "Horizontal toolbar” canvas. Change "Database Data Block" to “NO.” Then close the window. Create and resize a push button. Be sure that the default block is PB_BLOCK. Copy and paste the push button item five times. Open the first "push button" property palette. Change "name."

Blank the "label." Set “iconic” to "yes." On “Icon Filename,” type "w_prev." This is an oracle reserve word. Change "Keyboard Navigators" and "Mouse Navigators” to “NO.” Then close the window. Repeat the process for the rest of the push button items. Change name to a more meaningful naming convention. On “Icon Filename,” use "w_next" for next record. Change name to a more meaningful naming convention On “Icon Filename,” use "addrow" for adding a record. Change name to a more meaningful naming convention On “Icon Filename,” use "delrow" for deleting a record. Change name to a more meaningful naming convention On “Icon Filename,” use "save" for saving the application transactions. Change name to a more meaningful naming convention On “Icon Filename,” use "exit" for exiting the application. While holding the control key, click on the objects you want to select. And then open their property palette. Change “height” to 20 and press the enter key. Close the window. Make some layout adjustments if needed. Notice that these push buttons have no functionality. -Navigate through the layout screen to do your changes if needed and then create the triggers. Create a "WHEN-BUTTON-PRESSED" trigger for the previous push button item. Type "previous_record.” Then compile it. Successfully compiled. Make a typo error to see the error messages. Correct the error and compile it again. Then close the window. Repeat this process for all the other triggers. Notice on different ways of doing this. Done. Check the application layout. Then run the application.

Here, the only canvas you can see is: the horizontal toolbar canvas. There is no indication of the other canvas. You should drag the window to see the other canvas. You can either close the window or drag to resize the window to use the “Customer’s Order” application. Resize the window and execute the query. Test the push button functionality. After the testing is done, close the window and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 08 – Sizing , Layout appearances Resizing and changing the canvas Form. On pervious Hands-On the canvas was not size correctly. In order to use the application your client has to drag the window. You have been assigned to fix this problem. The requirement from you client is: to make sure your layout was size so your client doesn't need to drag or expand the window size. You will learn how to: size your canvas, change the horizontal toolbar canvas. More Resources by Google: Manuscript Show the “Customer’s Order” canvas with its horizontal toolbar canvas. Expand “Canvases.” Save the Form name as version 8. Click on the pb_block canvas either from the layout editor window or the object navigator window. Then drag the middle handler, to the preferred size. The white line is the border of the "horizontal toolbar canvas." And the black line is the border of the content canvas. See how it works. Run the application Notice the window was not sized enough and still you need to resize the window. Execute the application and navigate through the database records. Close the application. Drag the black line so the “Customer’s Order” application fits in it. This is good enough. Run the application. This looks much better.

Execute the query. Navigate through the application and use all the new push buttons to test the changes. Add and delete a record. Close the window and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 09: Trigger (PRE-QUERY), set dynamically properties On the "Orders" tab canvas layout, include two radio buttons to allow the user to manipulate the order of displayed records by order date. You have been assigned to create two radio buttons, Labeled "Ascending" and "Descending" to sort displayed record order by "order date." In this Hands-On, you will learn how to: create and use pre-query trigger, set dynamically block property on a table's data block and execute the query using trigger. More Resources by Google: Manuscript Save the Form name as version 9. Open the “Orders” Tap Page canvas. Create the radio button triggers to sort the block by the order date. Select the "Data Blocks" item and create a new data block manually. Open its property palette. Change "name" to a more meaningful naming convention. And press the enter key. Change "Database Data Block" to "no." Close the window. On the “BLOCK” box, select the sort_block item. Create a radio button. Click “new” to create a new “Radio Group” for sorting. Notice on the "object navigator," a radio group and radio button were created. Open the “Radio Group” property palette. Change the "name" property to a more meaningful naming convention and then press the enter key. Change "initial Value" to "orderdate asc." Then press the enter key and click “OK.” Change "Database Item" to "no." Close the window. Open the “Radio Button” property palette. Change the "name" property to a more meaningful naming convention. Change "radio button value" to "orderdate ascending."

-You can also change some other properties. Change "Label" to "Ascending." Close the window. Right click to open property palette or copy and paste the Radio Button item. Place it in the “sort_select” Radio Group. Click “OK.” Move the new "radio button" item. Open its property. Change the "name." Change the "label." Change "radio button value" to "orderdate desc." Then close the window. Click on the "Text" icon; and type 'sort order date.' Then click outside of the text box. Change the appearance of the text. Create a "WHEN-RADIO-CHANGED" trigger. Type “W” then “R.” And select the trigger. Write a PL/SQL procedure to go to the order block and then execute the query statement. Compile the trigger and close the window. Create a "PRE-QUERY" trigger for the “order” data blocks. Write a PL/SQL procedure to change the “order_by” property of the “order” data block. Notice that the “sort_select” value is changed based on the radio button you select. Compile the trigger. Close the window. Run the application. Click “Execute query.” Navigate through the application and check the sort radio button. After the testing is done, close the window and save the changes Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 10 – Sequence object, Trigger (pre-insert) Generate a unique customer ID and assign it to each new customer that is added to a table. Your client requires to find what last customer ID was entered into their system, then she/he increment it by 1 and use that number. This is not very professional way of doing business specially if you have multiple transactions to enter data. You have been assigned, to add a sequence number to custid starting with 200 and incremented by 1. So, your customer ID would start from 200, 201, 202, and so on.

In this Hands-On, you will learn how to: create sequence object, create and use "PREINSERT" trigger. More Resources by Google: Manuscript Save the Form name as version 10. Create a sequence number to increment the customer id number when you insert a new customer. Go to MS-DOS Prompt. Login to “sqlplus” as “iself” password “schooling.” Create a sequence object that starts from 200 and is incremented by 1. Use the “Next Value” Function to increment the sequence number. Repeat to run the query. Close the MS-DOS window or minimize it. Open the "custid" property palette. Change the "Required" property to "NO." Create a "PRE-INSERT" trigger for the “Customer” data block. Type “P” and “R,” and then select the trigger. Write a select statement to assign a new sequence number to “custid.” Compile the trigger and then close the window. Run the application. Click “Execute query.” Navigate through the customers information. Click on the "insert" icon. Now, the form is on the “insert” mode. Type the new customer information. Remember that the “Customer ID” is going to be generated. Save the record. New “Customer ID" was generated. Add more customers. Duplicate the previous record. Notice that even though the Customer ID is copied, the new "Customer ID" will be assigned to it. Click on "save."

Notice that "custid" is changed to the new number (205). Navigate through the application. Then, close the application and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 11: Call form Create an application for user product’s price data entry. This application will be used to query the product's price table, and update, delete and insert the product's price information. The window size for product price should be small enough to fit on a small portion of the application's window. The users’ requirements state that they want to have a push button object to click on it and then run the product's price application from the main application. You should modify their application to provide such request. You will learn how to: call another form. More Resources by Google: Manuscript Save the Form name as version 11. Open the items "tap" canvas. Create a push button that calls the “Product Price” data entry application. First, you should create a new Form for this application.. Select "Forms" and click on the "create" icon. Save the Form in the iself folder as "prod_de." Short for product price data entry. Create a "Data block" using the "data block wizard" option. Select the "Price" table and all of it’s columns. Use the default canvas option. Select all the columns. Select the “tabular” option. Type the "frame title." Change "records displayed." Checkmark “Display Scrollbar.” Then click on “Finish.” Open its “Window” property palette. Change the "X” position to 50. Change the "Width" length to 300.

Change the "Height" length to 150. Then, close the window. This is the boarder of the display window. Close the window. Save the Form in the iself folder. Then compile it. Click on the “Push Button” icon then click and drag it onto the layout editor. Open it's property palette. Change the name property to a more meaningful naming convention; then press the enter key. Change "Label" to “Product Data Entry.” Change the Keyboard and Mouse Navigate to "NO.” Close the window. Notice that on the object navigator this push button is not in the “item” block. Move it to the “Item” block. Notice that its location was changed; and that five items were displayed. Open it's property palette and change "number of items displayed" to 1. Press the enter key and close the window. Move the push button item. Right Click on the push button to choose "smart triggers," and select "WHEN-BUTTONPRESSED." Write a PL/SQL procedure to open the “Product Price” data entry application. Compile the trigger; and close the window. Run the application. Click “Execute Query.” Navigate through the application options. Click on the “Product Data Entry” push button. Then click “Execute Query” again. All products will be displayed. Click on the "Enter Query" icon to query all product prices for a specific product. Close the window. Navigate through the application to check all the changes.

When you are done with the testing, close the application and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 12: Global variable, trigger (when-new-form-instance) Now, your client notice it would be better for them if they could get to specific product's price rather than entire table. They want you to add more functionality to their application to display only current item information. The current product would be defined by the position of your cursor. You have been assigned to modify your application to query only pricing information for a product whenever your current cursor position is. You will learn how to: use and assign global variable, pass global variable from one form to another, use global variable to execute query base on that parameter, create and use "WHEN-NEW-FORM-INSTANCE" trigger. More Resources by Google: Manuscript Save the “Customer orders” Form name as version 12. Save the “Product Price Data Entry” Form name as version 2. Change the application to query all the product prices for an item that the cursor is on. Expand the “Product Price Data Entry” Form. Select “Data Blocks.” Create a new data block manually. Change its name. Open it's property palette. Change "Database Data Block" to "NO." Close the window. Create a new item. Open it's property palette. Change the "name" item to “TEMP_VALUE.” Change its datatype. Change "database item" to “NO.” Close the window. Select “Prodid” on the price data block; and open its property palette. For “Copy Value From Item” type "control_block.temp_value." Close the window. This shows where the “temp_value” item is coming from. Create a "WHEN-NEW-FORM-INSTANCE" trigger at Form level. Type “W” and “N” and select the trigger.

Move the global prodid item value into the temp_value item. Then go to the “price” data block and execute the query function. Compile the trigger and close the window. Highlight the Form and save it. Then compile it. On the other Form, right click on “push button” to choose “Smart Trigger” and select the "WHEN-BUTTON-PRESSED" trigger. Change the program name to the new version (version 2). Move the product id item value into the global product id item. Compile the trigger and then close the window. Run the application. Click on “Execute Query.” Navigate through the application. Highlight a product id and then click on the “Product Price Data Entry” push button. Now, you only see information about that specific product id not all the products. Repeat the process for some other items. Minimize the "Product Price Data Entry" window. Navigate through some other application's functions. Then, close all the windows and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck. Topics: Hands-On 13: Main menu Your client loves what you have done for them. But they would like you add the same functionality you did at Hands-On 12 to do it by using the main menu. You have been assigned to modify the main menu and add the “CALL FORM” function from Hands-On 12 to their window's main menu. In this Hands-On, you will learn how to: create or modify default main menu, use and assign global variable from one form to another, use global variable to execute query base on that parameter, create and use "WHEN-NEW-FORM-INSTANCE" trigger. More Resources by Google: Manuscript Save the Form name as version 13. Change the application to invoke the “Product Price Data Entry” FORM from the application main menu.

Highlight the “Menu” item in the "object navigator” and open the predefined “Oracle default menu” from the Forms folder. Open the default menu. Save it as "cust_menu" in the “iself” folder. Change the menu name to "Cust_menu.” And open its property palette. Notice that the name was changed. Then close the window. Double click on the "cust_menu" icon. Click on the "up arrow" icon to collapse each menu list item. Just to make it easier to read. Select the “Field” sub-menu, and then click on the "create right" icon. Change "New Item" to "Data." Click on the "create down" icon. Change "new item" to "product price," then right click on it and open it's property palette. Here, you could also change the name and other properties from here. Close the window. Right click on it and open the "PL/SQL Editor." Write a PL/SQL block to store the “product id” item into the “global product id” item. Open the new version of the “Product Price Data Entry” Form from the iself folder. Compile and close the window. Delete the “Edit” sub-menu item. Try to delete some other sub-menu items if not needed. Close the menu editor. Highlight "cust_menu," save the changes. Then compile the menu. The compile module is saved in the same folder of its source module. Open the “Customer Orders” Form property palette to assign the new menu. Type the new menu name. Close the window and run the Form. Notice that the main menu was changed. The “Data” sub-menu was added. Click on “Execute query.” Navigate through the application Form. Highlight a product id, then click on the "Product Data Entry” button. Close the window. Do the same by using the main menu option.

The results are the same. Here, you can change, delete, insert a product price for a selected product. Then, close the windows and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 14: Pop up menu The user wants you to add more functionality into the application. On the "comments" item on the "Customers" tab, when they type their comments, they are not able to cut, paste, and copy words or lines. You have been assigned to add the following functions into the "comments" item in their application: "CUT", "PASTE", and "COPY." In this Hands-On, you will learn how to: create "pop-up" menu, with following functionality "CUT", "PASTE", and "COPY." More Resources by Google: Manuscript Save the Form name as version 14. Select the "popup menus" item and click on the "create" icon. Change the "popup menu" name to “Editor Tools.” Double click on its icon. Change "new item" to "Cut" and press the enter key. Click "create down," change "new item" to "copy," and press the enter key. Click "create down" again, change "new item" to "paste," and press the enter key. Select "cut,” right click on it and open it's property palette. Change “Menu item Type" to "Magic" and "Magic item" to "Cut." Close the window. Do the same for "copy" and "paste." Change "Magic item" to "Copy." Change "Magic item" to "Paste." Close the windows. Open the comments' property palette. Assign "editor_tools" to "popup menu."

Close the window Compile and run the application Form. Click on “Execute query.” Move the cursor to the "Comments" item and right click on it.. The "popup menu" is displayed. These are its options. Select a portion of the text, right click on it, then copy and paste it. Select a portion of the text, right click on it and cut the selected text. Save the changes. Copy the whole text and paste it to the “NEXT” record. Save the transaction. Navigate through the application Form. After testing the application, close the window and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 15: Move objects, Layout appearances Now all their functional requirements have been met. The user now focuses on the appearance of the application. They would like you to move and add some objects. Perform the following to satisfy these cosmetic requirements: 1. Add a logo to their application. 2. The logo should be printed on each tab canvases. Add the logo's to each tab. In this Hands-On, you will learn how to: move objects around, create or copy, paste the logo. More Resources by Google: Manuscript Save the Form name as version 15. Max the window. Change the items' size if needed. To change the object size: select the object handler and drag it. Change the item's location if needed. To change the object location: select the object and drag it while the cursor is in side of the object handlers. Multiple items can be selected. You can also press on the up, down, right and left arrow keys to move the selected object.

To unselect the object, click out side of the selected object. Use your artistic talent to make a nice company logo using the boiler plate options. You can change the graphic boiler plate to any of the data blocks. Leave it at “sort_block.” Select the logo item, then copy and paste it into the other tap pages. Make space for the company logo. Paste the logo. Navigate through the tap pages layout. Compile and run the application Form. Click on “Execute query.” Navigate through the application Form. After testing the application, close the window and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 16: Run Application from desktop Run the application from the desktop. In this Hands-On, you will learn how to: run your application from desktop. More Resources by Google: Manuscript Create a new "shortcut." Right click, select “New,” and then click on the “Shortcut” option. Type the runtime form; use the compiled application Form as a parameter. Click “next.” Type the application name and click “Finish.” Double click on the icon to run the application Form. Enter the username and password, then click “Connect.” Click on “Execute query.” Navigate through the application Form. Test all the features and options, then close it.

Good Luck! Topics: Hands-On 01: Tabular Now, your client is a stock’s broker that keeps track of its customers’ stock transaction. You have been hired to write them reports based on their reports layout requirements. Your client wants you to create a simple listing report to show list of the stock trades by using stocks table for their brokerage company. The user layout format requirement for date is MM-DD-YY, for dollars is $99,999.90 and for quantity number is NNN.N0. You will learn how to: use report wizard, use object navigator, use report builder, save report, use “data model”, use property palette, work on query and group box, see report style, use tabular style, navigating through report’s record, change the format mask for dollar, numeric and date items. More Resources by Google: Manuscript Open the "Report Builder" tool. Highlight "Database Objects." Choose "File," then "Connect." Login as “iself” password schooling, then click “CONNECT.” Highlight the "untitled" report. Choose “File,” and select “Save as.” Go to the "iself" folder, and save it as report number 1 stock history ("rpt01_stock_history"). Double click on the "Data Model" icon. Click on the "sql" icon. Then drag the plus sign cursor and click it anywhere in the “Data Model” screen. Query the “Stocks” table sorted by the symbol column. Click “OK.” On the “SQL” box, right click and select “property palette.” Change the name to a more meaningful naming convention. This is the query. Close the window. Do the same for the "groups" box. Choose "Report wizard." Type the report Title. Choose the style you need. Notice! The report style would be displayed on the left screen. Choose a different style to display its report style.

Checkmark the "Tabular" style. Click “NEXT.” Click “NEXT” again. Select all the columns to be displayed. Click “NEXT.” Click “NEXT.” Click “NEXT” again. Change the labels, if needed. Use the default template and click “finish.” Here is the report. Format the report layout. To resize an object , select it and drag its handler to the preferred size. To move an object, select and drag it while the cursor is on the object. This is a simple report. Click on the "next page" or "previous page" icon to navigate through the report. Do the same with the "first page" or "last page" icon. Use the “zoom in” and “zoom out” icon to preview the report. To change the "format mask" of a column, the column should be selected. Apply the “$” sign, "add decimal place," and the “right justify” format to the all currency columns (Todays Low, Todays High, and current price) Apply commas to the “traded today” column. Make any adjustment you like. Select the "trade date" column and open its "property palette." Change the date "Format Mask" property and make it “year 2000 complaint.” Notice! The date format was changed. Navigate through the report pages. And then save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 02: Group left Now, you need to define a control break on their stocks symbol and company name from previous report and also to display one company only on a page. No more than one company should be in a page. You will learn how to: use report wizard, use report style “group left”, use template, use pre-viewer, use zoom in and out, use the body section in the layout model in the object navigator, change repeating object’s property palette. More Resources by Google:

Manuscript Open the report number 1 from the “iself” folder. Run the report. Maximize the window. Click on the “Zoom out” icon. And then navigate through the output report. Notice that the stock symbol and company name are repeated. Create a control break to eliminate the duplication. Click on the “report wizard” icon. Checkmark the “group left” radio button, then click next. Click “Next” again. Move the “symbol” and company name” item to the “group fields” box. Drag "company" to level 1, then click “NEXT.” Click “Next” again. Calculate the total, minimum, and maximum number of traded shares. Click “Next.” Change the label, if needed. Click “Next.” Use the default template and click “finish.” Navigate through the report output. Notice that multiple companies are on each page. Change the report to print each company information on a separate page. Restore the window. On the object navigator, expand “Layout Model,” “Main Section”, then “BODY.” Open the first "repeating group" property palette. Change "maximum records per page" to 1 and press the enter key, then close the window. Maximize the window and navigate through the report. Notice that each company is on a separate page. After testing the output report, go to the object navigator window, select the report and save the changes. Save the report as report number 2. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 03: Mailing Label

Now, user client ask you to create a report that outputs mailing labels. First they ask you to generate mailing labels with three columns and then change it to 1.5” X 3.5” label format. You will learn how to: use report style “mailing label,” use the “query builder,” generate three columns label, change the font and size, change it to two columns label, use handlers on the layout model. More Resources by Google: Manuscript Remove the report from “object navigator.” Click on the "create" icon to create a new report. Use the “report wizard” option. Checkmark "mailing label." Use "query builder" to write a sql query. Include the "customers" table, then close the window, and checkmark the columns you need. Click on the "sql" icon to show the sql statement. Close the window. And click “OK.” The sql statement was generated for you. Click “NEXT.” Select the columns into "mailing label text." Use "New line", "space", "Dash", "comma", and "period" if you need them in the "mailing label text." Notice my selection. Click “NEXT.” Select the draft template; then click “NEXT,” and “Finish.” This is an example of a three column label. Select the output object and change its font type and size. Change the three column label to the two column label. Click on the “layout model” icon. Make the “Object Navigator” window and the “Layout Model” window side by side. On “object navigator,” expand "Layout Model," “Main Section,” and “Body.” The frame item can be selected either by using the “Object Navigator” or the “Layout Model” in the report editor.

Select the first "repeating group" frame. Drag the frame to 1.5 by 3.5 inches. Click on the text boilerplate. Notice the handlers; drag them to the "repeating group" frame. Select the text and change the font type and size. Run the report. This is an example of a two column label. Change only the customer name font. Select the “untitled” report and save it as report number 3 in the "iself" folder. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 04: Matrix You was assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock. 1- Put grid around each number of shares for easy reading. 2- Get the totals for each column and row. 3- Change the cell color to red if its value is greater than 2000 and do the same for rows and columns subtotal. You will learn how to: use query builder, set table relationship, change properties from property palette, use report style matrix, make subtotal for rows and columns, use the “select parent frame” icon, create grid, use the conditional formatting. More Resources by Google: Manuscript Highlight the “Reports” item and click on the "create" icon. Build a new report manually. Add a new "sql object" in the “data model.” Use "query builder" and include the "customers" and "portfolio" tables. Set their table relationship. Select the columns. Check the query, and close the window. Click OK to exit.

The sql statement is generated. Click “OK.” Open the "q_1" property palette and change the name to a more meaningful naming convention. Close the window. Click on the “report wizard” icon. Type your title and checkmark the "matrix" radio button. Click “NEXT.” Select last_name as a "matrix row field." Select “Stock_symbol” as a "matrix column field." Select the sum of the shares_owned as a "cell." Make subtotals for rows and columns. No label for the sum of the shares owned. Make any label changes if needed. Use a template report and click “finish.” This is the customers portfolio report. Use the layout report editor to change the report layout. Click on the “Layout Model” icon. Change the size of each item. Use the "select parent frame" icon to go to the parent's column and resize it. When finished resizing, click on the “run” icon to run the report. Change the name item alignment to left or right. Select the cells and change their alignment and format mask. Do the same for the columns' subtotals. Navigate through the report. Select the cells, “right click” and open the "conditional formatting" option. Click “new” and change the shares owned value to red if it is greater than 2000. Click “OK.” Check the report. Go to the last page, change the alignment and mask format for the row's subtotals; and change the color if their values are more than 7,000.

Go to the first page. Do the same for the columns' subtotal; and change the color if their values are more than 10,000. After testing the report output, highlight the report and save it as report number 4 in the iself folder. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 05: Form Letter Now, you have been asked to write a “Form Letter” to sent to each customer who own Oracle (orcl) shares. You are supposed to notify them of a stock split and tell them the number of shares the broker hold for them, their size of the split and the post-split amount and their current stock market values. You will learn how to: use the report style “Form Letter,” use the “query builder,” set table relationships, define column from the “query builder,” use sub-query, use of data model, layout model, use of boilerplate, write the letter and pass the column to the letter. More Resources by Google: Manuscript Highlight the “Reports” item and create a new report. Use the “report wizard” option. Checkmark "Form Letter" and click on “next.” Use "query builder" and include the “customers,” “portfolio,” and “stocks” tables. Set table relationships between “customers” and “portfolio” tables. Set table relationships between “portfolio” and “stocks” tables. Select the columns you need. A column needs to be added to the stocks table to calculate the stock market value using the current price. Define the column on the portfolio box to double the shares number and select the column, too. Define a column on the stocks box to calculate the stock market value and select the column, too. Check the SQL statement, and close the window.

Select only the oracle shares holders. Click “OK.” This is the query. More “Where Clause Condition” can be added here. This sub query will make sure that the stock's market value is calculated based on the current stock's price. Click “NEXT.” Select all the columns that will be used in the letter. Select the template of your choice and click on “FINISH.” Click on the “Data Model” icon. Open the “Q-1” property palette and change its name. Open “report editor.” Click on “repeating group” on “object navigator.” Notice the handlers on “report editor.” Drag the frame. Click on the text boilerplate item and drag its handlers, too. Write the company letter. Now you hold and after the spilt that amount will be double. Then print for them their current stocks market value as of that day. Run the report. Navigate through the report. You can also change the text on the “live pre-viewer.” Notice that the number of shares got double. Put a dollar sign ($) next to the stock market values. Navigate through all the Oracle's stock's holders. After testing the output report, highlight the report and save it as report number 5 in the iself folder. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 06: Group above

Based on the user requirements, you should create a master-detail report that shows the portfolio holdings of each customer of the broker company, including their current stock's market value. As user requirements: 1- Use format mask NN,NN0 for shares owned and $99,999.09 for stock’s market value. 2- Print each customer’s stock information in a separate page so company can mail them to each of its customer. You will learn how to: use a master-detail report, build a new report manually, create multiple “sql query,” create data link, use the formula column, use the summary column, use the report “group above” style, use object navigator, layout model, main section, use property palette. More Resources by Google: Manuscript Remove all of the reports from the "object navigator." Highlight each report and click on the delete icon. Be sure to save them before removing them. Create a new report, using the “Build a new report manually” option. Save the “UNTITLED” report as report number 6 (customer portfolio). Click on the "sql" icon to create "sql query." Type the customers sql statement. Create another sql query. Type the portfolio sql statement. Open the property palette for each "query" and "group" and change their names to a more meaningful naming convention. Click on "data link." Notice that the cursor is changed to a cross. Move the cross cursor to the "ID" item and drag it to the "customer_id" item of the portfolio group. Now, the link was established. Move the "customer_id" item to the outside of the group box. This will create a control break on customer_id. Click on the "Formula Column" icon. Drag the cross to the Group portfolio and click on it again. Open it’s property palette.

Change the name. Double click on the PL/SQL Formula box. Write a PL/SQL block to calculate the customer stock market value. Get the current price for the current stock then calculate the market value. Compile it. Successfully compiled. Close the windows. Click on the "summary column" icon and move the cross cursor to the "group customer id" and click on the box. Open its property palette. Change the name. Change "source" to "CF market value." Change the "reset at" to the "group customer id" Close the window. Click on the report wizard icon. Type the title. Checkmark the "group above" style. Move all the items to "displayed groups" using the down arrow option. Move all the items that they need to be displayed to "Displayed fields." Change the Labels if needed. Click “NEXT.” Select a template, then click “Finish.” Change the column size and their format mask. Align their labels. Highlight the “Last Name” label and press the "delete" function key. Align the customer last name. Change its color. Go to "object navigator." Expand the "Layout Model" item. Open the first repeating group Property Palette from the “Body” item. Change "maximum records per page" to 1. Close the window. Now, it is one customer per page. Navigate through the output report.

Make an adjustment if needed. Navigate through it again. After testing the report output, highlight the report and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 07: Template You were asked to create a template for your client with their own logo. Then run a report to use their new created template. You will learn how to: create or modify an existing template, save the template with new name, change the default, delete the logo, replace the new logo, save your template. More Resources by Google: Manuscript Highlight the “template” item and open an existing template. First, find where the default Oracle templates are? It is easier to change an existing “template” rather than to create it from scratch. As of today, all the templates are on the “US” folder. Select the one that is closest to your layout template. Save it as a new name in the "ISELF" folder. Double click on the "Layout Model" icon. Check the “Report Style” list box. Notice that each report style has its own layout. Navigate through them. Select the default template. Delete the report layout logo and import your own organization logo. Here, we use "iselfschooling" logo. Use excellent quality. Then click “OK.” Adjust the logo size. Save the template. Then remove the template from the "object navigator." Run report number 6 using the new template. Click on the "report wizard" icon and then “template.” Checkmark the "template file" radio button. Click on “Browse” and open the new created template.

Click on “Apply” and then “finish.” This is the new report with its new template. Navigate through the report pages. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 08: Layout appearances Be creative and artistic to change the appearance of your report. Use boilerplate to change the title and etc. Use your imagination that has no limit You will learn how to: use boilerplate. More Resources by Google: Manuscript Click on the "layout Model" icon. Click on the "Edit Margin" icon. Use your creative imagination and change the report title. There is no limit on your creativities. Notice that how we change the report title. Run the report. Navigate through the report pages. Highlight the report and save it as report number 7. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 09: Parameters Now, you should create a simple report to list the name of customers including their shares owned. The user wants to execute this report just against a specific company’s symbol. They want to use company’s symbol as a parameter to pass to the report and at the end it calculates also the totals and average of shares sold for the company. You will learn how to: use the “group above,” use the “query builder,” set table relationship, create and use binding variable, calculate total and average shares, delete the object from layout. More Resources by Google: Manuscript Highlight the “Reports” item and create a new report.

Use the “report wizard.” Un-checkmark the "welcome" screen. Type the report title and checkmark the "group above" radio button.. Use the "query builder." Include the "customers" and "portfolio" tables. Set their table relationships. Checkmark all the columns that the report needs. Click “OK.” Here is the sql statement.. Add more where condition statement. The colon next to the symbol (:symbol) means: pass a symbol as a parameter to the report. This message should be displayed. Click "OK." Select the "Stock Symbol" item as a "group fields." Select all the items. Calculate the total and average shares sold for a company. Change the label if needed. Run the report. The parameter form is opened. Enter the company’s symbol. Run the report. Hold the control key and click on these objects. Then press the "delete" function key to remove them. Objects were removed. Do all the items format mask adjustment. Go to the “Object navigator” and highlight the “UNTITLED” report and save it as report number 8. Remove report 7 from "object navigator."

Run the report with different company symbol. Run the report. This company doesn't exist. Run the report against different companies. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 10: Drill Down or Push Button report You have done so good and your client asks you to create a drill down report on the company’s stock information so they get more detailed information about that company’s shares holders. Your report should have push button to call company’s shares holder from the previous Hands-On 09. The report should show for the security shown on the screen all customers of that security, the amount of their position, and the total number of shares held. You will learn how to: use push button to execute another report, use or pass parameter, use the “select parent frame” icon, use or open objects property, compile report, use the boilerplate, write and compile PL/SQL trigger, use “runtime pre-viewer.” More Resources by Google: Manuscript Open report number 2 (stock history). Save it as report number 8. Run the report. Navigate through the report. Here, add a push button to list the company's shares holders. Click on the "layout model" icon. Click on the company item, and click on the "Parent Frame" icon until the handlers do not change. Drag the handler to make more space. Click again on the company item, and click once on the "Parent Frame" icon. Then drag the handler. Click on the push button icon and move the cross cursor to the “layout editor” and click and drag to make a push button. Right click on it and open its property. Type “Customer's list” on the text box. Change the “type” property to "pl/sql” and then double click on the “pl/sql” trigger.

Write a code to call report number 8 (customers list). The binding variable is used to pass the company symbol as a parameter. Write all the exception. Compile the trigger. Successfully compiled. Close the window. Close the property palette. Close the “Layout Model” window. Remove report 7, since we don’t need it. Open report 8 -- customers list. Compile the report just to make sure the executable code is created. Then remove the report. Run the new report. To test the push button, you should use the "runtime pre-viewer” option. Click on "customer's list" to run the report. Navigate through the report pages and then click on the customer's list to see the company's shares holders. After the testing was done, close the windows and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck! Topics: Hands-On 11: LOVs (List Of Values) Since your parameter report on the Hands-On 09 is very useful for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol. Also add more parameter to pass such as the destination of printer, and so on. You will learn how to: use parameter, modify parameter properties using property palette, use query builder, add more parameters to existing parameter Form. More Resources by Google: Manuscript Remove report number 8 from "Object Navigator." Open report number 8 -- customers list. Then save it as report number 9. Double click on the "Layout Model" icon."

Click on the "Parameter Form" icon. Click on the "Parameter Form Builder" icon. Change the title. Select the Destination type. Notice that the “Symbol” binding variable has already been selected. Then click "OK." Open the “Symbol” property palette. Type “ORCL” as its initial value. Double click on the "list of values" property. Checkmark the “Select Statement” radio button and go to “Query Builder.” Include the "Stocks" table and close the window. Select the symbol column and check the query. Change the “ALL” to distinct. Click "OK" and close the property window. Close “Parameter Form Builder.” Highlight the report and run it. Open each list box. Change the report parameter if needed. Run the report. Repeat this process for different companies. --after two company. At the end, highlight the report and save the changes. Now, you should practice this over and over, until you become a master at it. Good Luck!