Sign in or start a free trial to avail of this feature.
1. Course Introduction
In this lesson, we learn about how we can manipulate our data by changing the data type. We also demonstrate the main areas covered by this course, date functions, string functions, and null handling functions.
Data Manipulation (00:15)
This course focuses on different methods for manipulating the data we extract from the database. The ability to do this isn’t a part of the core skill set of someone who can run efficient SQL queries, but it is indispensable when troubleshooting errors or working with problematic data.
Course Structure (00:48)
In this course, we cover 3 main types of data manipulation.
We start by learning about methods for manipulating date types. This includes date based calculations and extracting date components such as year, quarter, month, week and day.
We then move onto functions and operators that can be used to manipulate text or string based data. We learn how to change the case of our data, how to combine strings together using the concatenation operator and how to split strings using the LEFT, RIGHT, POSITION, and CHAR_LENGTH functions.
Finally, we learn how to handle null values. To this end, we learn about methods for replacing nulls as well as methods for adding null values in order to avoid error messages.
In this course, we'll be focusing on different ways we can interact with the data in our database.
We'll do this by implementing data type manipulation.
Data type manipulation refers to when we use functions and operators to change the data within the selected columns.
The nature of the manipulation depends on the data type we're working with.
While all other courses in this learning plan cover essential skills for creating effective SQL queries, data manipulation functionality is an advanced concept which is only useful in specific circumstances, particularly those relating to troubleshooting errors.
Through this course, we'll cover data manipulation in three sections.
First, we'll learn about manipulating dates by using certain functions.
This is useful because date columns are often too granular and complex by themselves, so they often need to be manipulated in some way in order to be useful.
Second, we'll investigate ways of adjusting strings of text data.
We'll explore methods for changing the case of strings, combining multiple text columns into a single column, and even parsing a single text column into multiple separate columns.
Third, we'll learn about handling null values using functions.
Null values can be either a hindrance that prevent our calculations from outputting values in the way we'd expect, or they can be a useful tool in overcoming issues such as divide-by-zero errors.
Note that unlike the other courses in the SQL learning plan, this course will focus more on functionality exclusive to the PostgreSQL dialect. The principles covered in this course will be relevant to all SQL dialects. However, those not using PostgreSQL, may have to research the different ways to implement this functionality in their chosen dialect.
We'll stop the lesson here.
In the next lesson, we'll learn about functions that can be used with date and time data.