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
STDDEVP(): returns the standard deviation of a population
VAR(): returns the variance of a sample
VARP(): 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 qery 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 PRECENT_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 annoymous block that prints out the message ‘Hello World’.

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

Remember to SET SERVEROUTPUT ON

Task 2
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 3
Write a procedure that calculates the new salary based on defined percentage pay rise. This value can be entered as a parameter.

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

The procedure should display the employee details, their current salary and their new salary.

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

Task 5
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