**Introduction**

This week we will continue our exploration of various SQL language features along with some Database and SQL Developer features.

This week we will look at:

- How to load data into the Database and How to extract data, in different formats
- Look at a subset of the analytic/statistical SQL functions (most enterprise Databases have over 400 statistical functions)
- Creating PL/SQL Procedures and Functions

**Notes**

Click here to download the Loading & Extracting Data notes.

Click here to download the Analytical SQL Functions notes.

Click here to download the PL/SQL, Procedures and Functions notes.

**Lab Exercises**

Exercise 1 – Loading and Extracting Data

This lab exercise requires you to practice coping data, extracting data and loading data for your Oracle schema.

[See examples in notes/slides]

Task 1 – Copying data

Using SQL create a copy of the DEPT and EMP tables. Give the new tables a name something like DEPT_COPY, EMP_COPY.

[See examples in notes/slides]

Write a query that will return the following attributes. The table for each attribute is given in brackets

EMPNO (EMP_COPY)

ENAME (EMP_COPY)

JOB (EMP_COPY)

SAL (EMP_COPY)

DNAME (DEPT_DEPT)

LOC (DEPT_DEPT)

The query will retrun one record per employee, containing some of the employee attributes, and joins to the DEPT table to return some of the department details for the emplyee.

[WHERE EMP.DEPTNO = DEPT.DEPTNO]

Using this query create a table that contains the results returned by the query. Name this new table EMP_DETAILS.

Task 2 – Extracting Data using SQL

Using the SQL features of creating data in different formats, practice using each one.

There is also a json format. Try that and see if it works

Create a file, using SQL, that exports the EMP_COPY and DEPT_COPY tables, using the following formats

json

csv

html

Open the html files in an internet browser and inspect the data

Make sure that these files are created correctly and does not have any additional or unnecessary elements in them.

See the Additional Exercises, for an exercise on loading these files into R and to inspect the data.

Task 3 – Extracting Data using SQL Developer

Use the Export table feature in SQL developer to create additional files for the EMP_COPY and DEPT_COPY tables.

Use different names to what was used in Task 2 above.

Inspect these files and compare the contents of these files to those created in Task 2.

Task 4 – Loading Data using SQL Developer

Drop the tables EMP_COPY and DEPT copy.

Using the Import feature of SQL Developer, load the data into your Oracle schema.

Do you encounter any errors? If so, how would you overcome these.

Compare the tables, data and structure of the EMP_COPY and DEPT_COPY tables with the original tables, the data and the table structures. Are there any differences? If so, why?

Exercise 2 – Analytical SQL Functions

This lab exercise requires you to practice using some of the hundreds of analytical SQL functions. In addition to the exercises/tasks listed below, you should spend some time exploring and experimenting with the many of these analytical SQL functions

[Open the Oracle SQL Language Reference Manual = RTFM]

[Also see the examples in the slides]

[15-20 minutes to complete Tasks 1-3. Complete remaining Tasks if time or as homework]

Task 1 – Practice using basic SQL Stats functions

Experiment with using the following stats functions in SQL, on the data sets you have in your Oracle Schema

AVG(): returns the mean

COUNT(): returns the population (or sample, depending on the row source)

POWER(a,n): returns the value of a to the nth power

SQRT(n): returns the square root of n

SUM(): returns the sum of the values in a set

STDDEV(): returns the standard deviation of a sample

STDDEV_POP(): returns the standard deviation of a population

VARIANCE(): returns the variance of a sample

VAR_POP(): returns the variance of a population

CORR(): correlation analysis

Task 2 – Having Clause

Take the SQL stats functions used in Task 2 and modify them to include the HAVING Clause.

You will need to think carefully on how and Why you are using the HAVING clause in each case.

What are you trying to show or demonstrate in each case.

Write 1-3 line description of what the benefit is of using the HAVING clause for each example

Task 3 – Sampling data

For one of your larger tables, containing the largest number of records, compare the results and records generated by using the SAMPLE and ORA_HASH functions, for the following sample sizes.

Sample = 5%

Sample = 10%

Sample = 15%

Sample = 20%

What differences can you find and/or observations that you see. Can you explain these?

Task 4 – PIVOT

Write a query that returns the average salary per department (full name)

Rewirte the query using the PIVOT command to display the display the departments as columns in the output

Task 5 – LEAD and LAG

Write a query that returns some of the details of each employee, ordered by salary in accending order

Alter the query to add an additional column that displays the salary of the previous person in the results set

Alter the query to add an additional column that displays the salary of the next person in the results set

Add the following columns

– Difference in salary between the employee and the person who earns just below them

– Difference in salary between the employee and the person who earsns just more than them

Task 6 – Ranking Staff

Using your basic query that returns the details of employees and their salary

– Use the RANK function applied to all the data

– Use the PERCENT_RANK function applied to all the data

– Use the CUME_RANK function applied to all the data

Using the PARTITION BY clause change the above queries to rank the results based on each department.

Exercise 3 – PL/SQL

This lab exercise gets you to practice writing PL/SQL.

Check out the link to the chapter Fundamentals of PL/SQL Language.

[Take examples from notes/slides and run them in SQL Developer before attempting the following Tasks]

[20-25 minutes to complete Tasks 1-3. If time complete remaining Tasks or as homework]

Task 1 – Hello World

Write a PL/SQL anonymous block that prints out the message ‘Hello World’.

Write a PL/SQL anonymous block that counts the number of records in the employee table and prints the results.

Remember to SET SERVEROUTPUT ON, or turn on DBMS OUTPUT in SQL Developer

Task 2

Write a PL/SQL anonymous block to print the current date, in three different formats.

Task 3

Write a PL/SQL anonymous block to perform a calculation and print the results.

Create a variables to store:

– Retirement Age

– Person Date of Birth

Calculate their current age and how long they have until retirement age.

Print these two calculated numbers in a formatted string, containing text to explain what these values mean.

Task 4

Write a PL/SQL anonymous block that selects some of the attributes from the EMP and DEPT tables, join the data to form one record.

Only select data from 1 record. Use a SELECT … INTO… statement

Display the results on the screen.

Task 5

Write a PL/SQL anonymous block that will select all employees and will print out some of their details to the screen.

Use a Cursor FOR Loop.

Task 6

Write a procedure that calculates the new salary, for an employee, based on defined percentage pay rise. This value can be entered as a parameter (in a procedure) or defined in a variable in an anonymous block.

The pay rise should only be applied to employees who earn less than 1601 and do not receive a commission payment.

You will need to define a Cursor with a select statement to select the appropriate values. Then update the records (update statement) to have the new values.

The procedure should only display the employee details, their current salary and their new salary, for those that were updated.

Task 7

Update the procedure create in Task 5 to include the appropriate exception handling.

Task 8

Write a procedure that will update an Employee salary based on a percentage pay rise.

The procedure should accept two parameters, employee number and percentage pay rise.

The procedure should update the employee salary to the new salary.

Task 9

Write a function that calculate the tax payable on a salary. No data should be updated in this process.

The function should take as input a salary about and use the following rules to calculate the tax amount.

if salary is less than 25000 then tax is 10%

if salary is between 25001 and 40000 then tax is 20%

if salary is between 40001 and 60000 then tax is 30%

if salary is greater than 60000 then tax is 40%

Create a new version of the EMP_SALARY procedure to include a call to this tax function and include the tax amount in the output along with an additional value containing the salary and the tax amount.

Exercise 4 (Optional)

Task 1

Perform some regression analysis on the Employee salary data using the following function

– REGR_SLOPE

– REGR_INTERCEPT

– REGR_COUNT

Task 2

Alter these questions to partition the results by

– job_id

– department

These should be done as separate queries

Task 3

Use the ROLLUP function, on your employee salary query, to calculate aggregated results by

– department

– department and job_id

Modify your previous query to use the CUBE function install of the ROLLUP function.

Examine how the results are different between the usage of the ROLLUP and CUBE functions.

Can you think of other scenarios when you would use one of these functions instead of the other.

Task 4

For the DEPT table, create stored procedures to

– Insert new rows into a table using the supplied parameter values

– Update data in a table for rows that match the supplied parameter values

– Delete rows from a table that match the supplied parameter values

– Query a table and retrieve data based on supplied parameter values

These procedures are typically used by REST services.

**Additional Reading/Resources**

Oracle SQL Language Reference – Functions chapter

Fundamentals of PL/SQL

ANSI SQL vs Traditional SQL: how to write JOINS => two versions of the same standard