This week we will continue exploring writing more SQL functionality, building upon what we did last week. This week is divided into:
- Extracting & Loading data
- How manipulate and transform data in different formats
Notes & Videos
Click here to download the Loading & Extracting Data notes.
Click here to download Data Manipulation & Transformation Notes
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]
[Exercise 1 should take approx 35-45 minutes to complete. Complete remaining Tasks if time or as homework]
[Directories, File names and paths to files should not contain spaces]
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 one query returning the following attributes. Create a new table based on this query. The table for each attribute is given in brackets.
HINT: write the SQL query first and test it works and returns the correct data/rows. Then use it to create the table.
The query will return 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 employee.
[WHERE EMP_COPY.DEPTNO = DEPT_COPY.DEPTNO]
Using this query to create a table containing the results returned by the query. Name this new table EMP_DETAILS.
Task 2 – Extracting Data using SQL
Using the SQL features/commands 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
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.
HINT: You might need to make minor edits to this files
Additional Exercise (Optional): Using R and/or Python load these files into a dataframe and 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 using the files created in Task 3.
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?
Lab Exercise 2 – SQL Manipulation Functions
[The following tasks should take approx. 30-40mins to complete.]
Task 1 – Some Date processing
Find out the day of the week you were born on, and what days of the week you celebrated your 10th, 18th, 21st, etc birthday on.
Task 2 – Date and Time processing
Write out today’s date in standard format used in EU and in USA. Include the time in standard 12 hour format and in 24 hour format.
Task 3 -Basic string processing
Take your full name as a string and separate it out into its individual tokens.
Convert the string to have Initial Caps, All Caps and all Lower Case.
Task 4 – Reformatting
Reformat the following string
Brendan, Tierney, Kevin St, Dublin 8, Ireland
to the following format
brendan |tierney |kevin st |dublin 8. |ireland |
Convert each string component to lower case, remove the comma, replace to have fixed width of 10 characters, separated by the pipe (|) symbol
Task 5 – Working with names
Take the following names. Using the SQL functions reformat them into their correct form (example given)
input = ‘mcDonald’ output = ‘McDonald
input = “o’reilly” output = “O’Reilly”
Task 6 – How old are you
Using SYSDATE and your date of birth calculate your age. Apply additional formatting or calculations to the display your age in Days, Years, etc
Task 7 – When you are 65
Display the following information about the date of your 65th birthday.
- How many months between today and your 65th birthday
- How many days between today and your 65th
- Display the Day of the week – The full day name
- Display the month, with full name
Lab Exercise 3 – Regular Expressions (Optional)
To learn more about using Regular Expressions in SQL check out these posts.
Lab Exercise 4 – Additional SQL Queries (Optional)
Using the tables and data that was loaded last week in Exercise 3, write SQL queries to answer the following questions.
[this lab exercise will take approx 20-30min to complete]
3-1 – List the Minimum, Maximum and Average salaries where job title is ‘MANAGER’
3-2 – List details of employees who has a commission that is greater than 50% of their salary
3-3 – List the employees and their length of service (based on HIREDATE)
3-4 – List the average salary by each job title
3-5 – List the average salary by each location
3-6 – Create a new table that has the same structure as EMPLOYEE and call it MANAGERS. Write a SQL statement that will inserted into the MANAGERS tables all employees who have the job title of ‘MANAGER’ and ‘PRESIDENT’