SQL Developer is a free tool that you can download from the Oracle.com website. It is available through Cougar Apps for each student in CIS 341. This assignment will introduce the concepts of database management and SQL programming.
This technique of calling a function using a view is often used in the process of handling data conversion from one legacy system to a next generation system.
Create an Excel file with 10 rows of data and 5 columns of salary data: First_Name
Last_Name
Employee_ID (primary Key) Salary
Start_Date
The 10 rows of data in the Excel document can have all different names, ID’s and salaries.
Create a JOB table by importing the Excel document from step #1.
Using SQL Developer create a Table called JOB The columns of the table are:
First_Name Last_Name
Employee_ID (primary Key and number) Salary (number field)
Start_Date (date type field)
Add 10 rows of data in the JOB table, all different names, ID’s and salaries.
Create a function called NEW_SALARY. Here is the code for the new function:
CREATE OR REPLACE FUNCTION NEW_SALARY(A NUMBER, B NUMBER)
RETURN NUMBER AS
BEGIN
RETURN (A + (A * B));
END;
Test the function
Should return the 472.5
SELECT NEW_SALARY(450, .05) FROM DUAL;
Create a view that calls the function
Using SQL Developer create a View called JOB2
The view needs to be all the columns of the JOB table along with the output from the
NEW_SALARY function as a column in the view. When you create the view name the column alias for the function NEW_AMOUNT. Here is the idea:
CREATE VIEW JOB2 as SELECT…
For example, once the view is created then the following query should result in 10 rows of data with the new salary of 5% more than the previous salary for each person.
SELECT JOB.LAST_NAME, job2.NEW_AMOUNT
FROM JOB, JOB2
WHERE JOB.EMPLID = JOB2.EMPLID;
Submit to Cougar Courses a PDF of the screen print showing the query output from the select in step 4 above. For partial credit submit a PDF of work done on previous steps.
The PDF needs to show your login ID (located in the upper right hand corner of the SQL developer tool). The SQL tab of the create view statement also has the name of the schema which is the login ID.
As reference the following site is an excellent overview of Oracle tools, Database concepts and the SQL Developer tool. Below are some examples of the website
( link is http://dba.fyicenter.com/faq/oracle/oracle_pl_sql_basics.html )
