DEV Community: Jo

Link Jupyter Notebook to Github

Jo — Wed, 25 May 2022 17:38:50 +0000

This post will explore (and serve as a personal reference) for linking a jupyter notebook to github. This guide assumes you have a github account, basic knowledge of git and the command line, and is currently using Jupyter notebook.

Firstly, spin up jupyter notebook using your CLI and this command:



jupyter notebook

This should bring you to the jupyter notebook interface as seen as the screenshot below:

Click the dropdown on the right, and select "Python 3". This will spin up the IDE, which you can then name appropriately. For this example, I copy pasted code from W3Schools.

The below screenshot is the result:

Great. Now we have the code. In Jupyter, go to "File" and go to "Download As:", select "Notebook"(.ipynb)

Now, sign into your github account, and create a new repository. On the Quick set up page select the "…or create a new repository on the command line" option

Copy paste the code into your command line like so:

Go back to github and refresh the page.

Select "Upload files".
Choose the file you've just downloaded from the jupyter gui and select "Commit changes". This will then take you back to the file screen where you can now click on the file you just uploaded. The screenshot below shows the final result.

Your jupyter notebook work is now shareable to others.

Primary Keys in SQL

Jo — Mon, 11 Oct 2021 00:19:41 +0000

We want all our data entries in a table to be uniquely identifiable. A Primary key is a unique identifier on a row. A good example of a primary key is your twitter handle. No 2 twitter handles are the same, and your twitter handle cannot be NULL. You can change your twitter handle, but it always has to be unique (and twitter will not allow the change unless it is unique value).

CREATE TABLE unique_students (student_id INT NOT NULL,
name VARCHAR(100),
age INT,
PRIMARY KEY(student_id)
);

The below image will shows us what the above code creates in mysql:

Here we can see that the primary key is the student_id. However, it would be less efficient in this database to manually create our primary key. We can do this automatically with the auto_increment keyword.

CREATE TABLE unique_students (
student_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
age INT,
PRIMARY KEY(student_id)
);

The auto_increment id will add a 1 each time, so we no longer have to specify it when creating a new entry
In the highlighted section of the graphic below, we can see that we have 2 identical entries except they have different student_id's because they've been auto_incremented. They are actually 2 separate students who happen to have the same first name and age (which is quite common in a school setting).

The auto_increment now lies in the "Extra" field (use the DESC keyword to show the table's description.

That's all for this blog, folks. Thanks for checking it out :)

Cyclistic Capstone Project

Jo — Thu, 02 Sep 2021 00:24:37 +0000

This is an opportunity to analyze historical bicycle trip data in order to identify trends. Understanding how casual riders behave differently from paid riders with annual membership is important.

This analysis will help executives to make decisions about marketing programs and strategies to convert casual riders to riders with annual memberships.

Background:

Cyclistic is a bike-share program that features more than 58,000 bicycles and 600 docking stations. Cyclistic sets itself apart by also offering reclining bikes, hand tricycles, and cargo bikes, making bike-share more inclusive to people with disabilities and riders who can't use a standard two-wheeled bike. The majority of riders opt for traditional bikes; about 8% of riders use the assistive options. Cyclist users are more likely to ride for leisure, but about 30% use them to commute to work each day.

Stakeholders and Team

Lily Moreno: Director of Marketing
Marketing Analytics Team: Team responsible for collecting, analyzing, and reporting data.
Cyclistic Executive Team: The notoriously detailed-oriented executive team will decide whether to approve the recommended marketing program.

About the Company:

Cyclistic has 3 different pricing tiers: single-ride passes, full-day passes and annual memberships. Customers who purchase single ride or full day passses are referred to as causal riders. Customer who purchase annual memberships are Cyclistic members.

Cyclistic's finance analysts have determined that annual members are much more profitable than casual riders. Moreno believes that maximizing the number of annual members will be key to future growth. Rather than creating a marketing campaign that targets all new customers, Moreno believes there is a very good chance to convert casual riders into members.

Ask

Three questions will guide the future marketing program:
1) How do annual members and casual riders use Cyclistic bikes differently?

2) Why would casual riders buy Cyclistic annual memberships

3) How can Cyclistic use digital media to influence casual riders to become members.

1st step- Importing the data

Before any analysis or processing of the data can be done, we first need to import the data. I am choosing to use R for this analysis as the data is too large for spreadsheets. Below we are using the requisite packages and library in order to import the data.

We have imported all the requisite files. However, they are all still separated into individual months. We need them in one singular table.



  yearly_bike_data <- rbind(Jan_5_21, Feb_4_2021,March_9_2021, April_8_2021,May_7_2021,June_11_2021, July_15_2021,Aug_11_2020,Sept_4_2020,Oct_13_2020,Nov_4_2020, Dec_4_2020)

Let us now install all our requisite packages to start processing the data



install.packages("pacman")
library(pacman)
install.packages("tidyverse")
library(tidyverse)
install.packages("janitor")
library(janitor)



dim(yearly_bike_data)

We have now imported, and combined all the data from the last 12 months of bike data we have available.

Data Exploration

Now that we combined all the data, we should explore it before cleaning.



glimpse(yearly_bike_data)

Clean the data

We have lots of "NAs" in our data. Let us keep only the completed rows.



yearly_bike_data <- yearly_bike_data[complete.cases(yearly_bike_data), ] # Keep only the complete rows



dim(yearly_bike_data)

We have now removed 434,648 rows that had NAs. This will five us a more accurate data set with which to make calculations with:

We now need to make some of our columns more accessible:



#The below code is determining the ride length in seconds.
yearly_bike_data <- yearly_bike_data %>% mutate(ride_length = yearly_bike_data$ended_at - yearly_bike_data$started_at)
head(yearly_bike_data)

The above code gives us the length of each bike ride in seconds. Let us now determine the ride length in mins and hours by using the seconds_to_period function:



install.packages("lubridate")



library(lubridate)



yearly_bike_data <- yearly_bike_data %>% mutate(ride_length_time = seconds_to_period(yearly_bike_data$ride_length))

A useful thing to add would be the days of the week. Let us extrapolate and add that data to our data set:



 yearly_bike_data <- yearly_bike_data %>% mutate(day_of_week = weekdays(yearly_bike_data$started_at))

Now let us see what columns we have available to us:



colnames(yearly_bike_data)

We now need to clean up the started_at and ended_at columns by separating the date and time. This will make for easier analysis further on. In order to do this, we will use the separate function.



yearly_bike_data <- separate(yearly_bike_data,"started_at",into=c('start_date','start_time'), sep=' ')



yearly_bike_data <- separate(yearly_bike_data,"ended_at",into=c('end_date','end_time'), sep=' ')

We now have 4 new columns, start_date, start_time,end_date and end_time.

Now we have added 4 new columns to the table. We need to do further cleaning of the data to ensure data integrity.



min_ride <- min(yearly_bike_data$ride_length)
min_ride

I used the min function on the ride_length column and noticed that the shortest ride was in the negatives. Upon further investigation, I noticed that several rows contained negative numbers in the ride_length column:



min_ride <- arrange(yearly_bike_data,ride_length)
head(min_ride)

Based on the tibble above we can see that the ride_length column that has several thousand negative values, because, for a lot of cases, the rides appeared to be started in Dec 2020,but ending in November 2020. Since we can't time travel yet, we will need to remove those rows for the sake of data integrity:



yearly_bike_data <- yearly_bike_data[yearly_bike_data$ride_length > 0, ]
yearly_bike_data

In the code above, we removed all rows that contained time below 0. Now we run the minimum function again to make sure it worked.



min_ride <- min(yearly_bike_data$ride_length)
min_ride

After removing the negative numbers in the ride_length columns we now have a minimum time of 1 second.
Running this code resulted in 10,952 rows of inaccurate being removed. We still have a substantial amount of data to work with however, since we have over 4 million rows.

Let us now determine how many people use Cyclistic in total:
In order to determine the total number of users we are going to count the users and use this information to fill out a pie chart:



table(yearly_bike_data['member_casual'])

Casual:1,724,745 Members:2,290,711



total_number_of_users <- c(1926094, 2523705)
pie(total_number_of_users)
pie_labels <- c("casual", "members")
colors <- c("#C5007C", "#8CC702")
pie(total_number_of_users, label=pie_labels, main = "Total Users", col=colors)
legend("bottomright", pie_labels, fill = colors)

From the code and visualization above, we can see that we currently have more members than casual users:

Let us create a months column as well:



yearly_bike_data <- yearly_bike_data %>% mutate(Months = 
case_when(
month(yearly_bike_data$start_date)==01 ~ "Jan",
                       month(yearly_bike_data$start_date)==02~"Feb",
                         month(yearly_bike_data$start_date)==03~"March",
                         month(yearly_bike_data$start_date)==04~"April",
                         month(yearly_bike_data$start_date)==05~"May",
                         month(yearly_bike_data$start_date)==06~"June",
                         month(yearly_bike_data$start_date)==07~"July",
                         month(yearly_bike_data$start_date)==08~"Aug",
                         month(yearly_bike_data$start_date)==09~"Sept",
                         month(yearly_bike_data$start_date)==10~"Oct",
                         month(yearly_bike_data$start_date)==11~"Nov",
                         month(yearly_bike_data$start_date)==12~"Dec"

 )
)
colnames(yearly_bike_data)

Let us plot a bar chart to easily show stakeholders the most popular day of biking:



#for both casual and members
ggplot(data=yearly_bike_data) + geom_bar(mapping=aes(x=day_of_week), fill="purple")+facet_wrap(~member_casual) +
  theme(panel.spacing = unit(4, "lines"), axis.text.x=element_text(angle=45, hjust=1))

Interpretation of Data for Weekdays

From the bar charts above,we can answer one of our main questions: "How do annual members and casual riders use Cyclistic bikes differently?"
It is clear that casual users of Cyclistic use the app much more on weekends than the rest of the week. The members, however, tend to use the app more uniformly throughout the entire week. This suggests that members are more likely to use the service as their main form of transport, while casual users of the app tend to use it more for recreational purposes (as highlighted by the dramatic increase of use on the weekend for that group)

To aid in our analysis further down the road, I decided to to break the 12 months worth of data into the 4 seasons:



yearly_bike_data <- yearly_bike_data %>% mutate(Seasons = 
case_when(
                        month(yearly_bike_data$start_date)==12|
                         month(yearly_bike_data$start_date)==01|
                         month(yearly_bike_data$start_date)==02 ~ "Winter",
                         month(yearly_bike_data$start_date)==03|
                         month(yearly_bike_data$start_date)==04|
                         month(yearly_bike_data$start_date)==05 ~ "Spring",
                         month(yearly_bike_data$start_date)==06|
                         month(yearly_bike_data$start_date)==07|
                         month(yearly_bike_data$start_date)==08 ~ "Summer",
                         month(yearly_bike_data$start_date)==09|
                         month(yearly_bike_data$start_date)==10|
                         month(yearly_bike_data$start_date)==11 ~ "Fall"

 )
)
colnames(yearly_bike_data)

Importance of Seasons for biking

We will be now be able to use the Seasons column to determine which season is the most popular for biking, and therefore, help determine a marketing strategy that will take this into consideration:



ggplot(data=yearly_bike_data) + geom_bar(mapping=aes(x=Seasons), fill="purple") + facet_wrap(~member_casual)

Based on the charts above, we can infer that, for both groups, Winter is the least popular month for biking, while Summer is the most popular month for biking.
Members tend to bike more in the fall than casual users.

Let us now do a count of the number of rows and columns we have currently:



dim(yearly_bike_data)

We have 4015456 rows and 19 columns of data.

Going back to our business requirements, the first ask was to determine "How do annual members and casual riders use Cyclistic bikes differently. We can also answer this question by finding the average, minimum , and maximum ride_length of each group of riders.

```{r

group and summarize}
ride_summary <-
yearly_bike_data %>%
group_by(member_casual) %>%
summarize(average_ride_time=mean(ride_length),
min_ride_length=min(ride_length),
max_ride_length=max(ride_length))

ride_summary

### Analysis of Findings:
Though we have more members than casual users of the app, casual users actually ride for longer than members. The average ride time of a 
casual user is 2511.95 seconds while that of a member is 891.89 seconds. The min ride length is the same at 1 second, and the max ride length of a casual user is 3,356,649 seconds and the max ride length of a member is 2,005,282 seconds. 
We can also find the mode of day_of_week to show which day is the most popular for biking.
```{r}


names(sort(-table(yearly_bike_data$day_of_week)))[1]

From this code, we can tell that Saturday is the most popular day of the week for biking.

Now, we want to find out which season is the most popular for biking:



(names(sort(-table(yearly_bike_data$Seasons)))[1])

The mode once again tells us that Summer is the most popular month for biking. Let us find out the second most popular month:



names(sort(-table(yearly_bike_data$Seasons)))[2]

The second most popular season for biking is fall. Let's check the third most popular season for biking:



names(sort(-table(yearly_bike_data$Seasons)))[3]

Spring is the third most popular season, which means winter is the least popular season for biking.

Let us make a chart to show this visually:



  ggplot(data=yearly_bike_data)+
     geom_bar(mapping=aes(x=Seasons,fill=member_casual))



  ggplot(data=yearly_bike_data)+
     geom_bar(mapping=aes(x=Seasons), fill="orange") +
 coord_polar("x")

The pie chart above shows how little the Cyclistic app is used in winter, as opposed to the rest of the months.

Based on my analysis of the data, I have some recommendations for the Cyclistics marketing team:
1) Converting casual users to members is a good idea, and should be moved forward with since the usage of the app is similar among groups.
2) I would increase marketing to the casual groups during the summer and point how much they would be saving if they were to become members. Since summer is when this group bikes the most, then they would be spending the most money on the app during this time and would probably appreciate a way to lower costs. For all users, I would incorporate a Spotify-esque approach where I would send them a "year in review" on how they used the app. This could include personalized details like: how much calories they burned for the year using Cyclistic, how much money saved by riding (instead of driving) etc.
3)For winter months I would give a discount to encourage members to continue using the app, and to encourage casual users to become users during this time.
4)The data also showed that casual users used the app more so on the weekends than on weekdays, where members were more likely to use the app every day. I would encourage casual users to use the app more in the weekdays by offering discounts on the weekdays, which would then translate to them using the app more and so being more inclined to become members.

How to make the most out of #100DaysOfCode

Jo — Fri, 07 May 2021 16:31:42 +0000

I am at the end of #100DaysOfCode and thought I would share some tips and tricks on how to successfully complete the challenge, my accomplishments, and what I would do differently in the future.

Tips and Tricks

1) The main goal of #100DaysOfCode is to code consecutive for 100 days consecutively. Was I successful in this? Actually no. My birthday occured in the middle of the challenge, so I took a few days of to travel and celebrate. Balance is key, so while this challenge provides structure, feel free to afford yourself some flexibility and grace. Life happens and you just may not be able to code for 100 days straight. I would even suggest calibrating this challenge to best fit your needs. If you don't want to code on the weekends, then don't. The challenge will take longer of course, but you need to do what's best for you.
Edit: After drafting this, I went through an extremely traumatic event that required me to take more time off. I almost decided to quit the challenge at d.82, but decided to continue. It helped with getting my life back to normal.

2) Don't think you have to code for hours at a time every day. There were some days where all I did was watch a Udemy video before going to bed. There were other days when I spent hours coding. For me, I liked doing something every day to keep new concepts fresh in my mind.

3) Set a goal. Setting a goal sets you up for success. For this challenge, I wanted to learn SQL. The system to learn that goal was to use the #100DaysOfCode challenge to learn SQL via online courses. The secondary goal was to finally start completing those Udemy courses I kept buying.

4) After you've chosen what you want to learn, try not to go overboard with resources. There are so many free and paid resources, and it's easy to try and want to learn everything. I would suggest that before doing the challenge, do some research on what you would like to learn, then find some resources to help you learn that topic. This will help provide structure so you won't feel like you're flailing in the wind.

5)Find a way to keep yourself accountable. For me, that was tweeting about it every day. You really don't have a way of knowing if people are paying attention, and in all honesty you have to assume that they don't care. But what if you have one person watching, you don't want to let them down do you? Learning is hard, and learning in public is even harder. Do it anyway.

TLDR: Be consistent, but flexible, set a goal and have a system for achieving that goal, find a few resources and stick to those to completion.

What did I Accomplish?

I started this challenge with the goal to learn SQL because I saw it in so many different job applications. SQL is needed for data analytics, data science, and back-end web development. I learnt mySQL and PostgreSQL as well.
I completed _____ Udemy courses, and later on in the challenge I started the Professional Data Analytics Course from Google. (I have finished the first of 8 courses and will be continuing the rest). Also later in the challenge, I started learning Python and will be continuing that as well.

I also started about my journey and wrote _ articles about SQL. Writing about sql allowed me to cement the knowledge I was gaining and improved my technical writing ability as well.
I not only improved my writing, but my presentation skills as well by including annotated screenshots and creating my own graphics in Canva as well. The tech world is huge, and all these skills, knowing how code, knowing how to write about technical topics, can all be useful. At the very least, it was helpful for me to go back and revisit topics I had written about so they served as a note taking function as well.

What would I do differently in the future.

There is not a lot I would change about my process. I will probably make my goals more defined the next go around, in terms of what I hope to achieve (for example, write more blogs to increase my dev dot to viewership, or choose a sql database tool and try to learn that deeply).

Thanks for reading! Hope these tips were helpful!

Popular string functions in MySQL - Replace and Reverse

Jo — Sat, 24 Apr 2021 14:37:46 +0000

REPLACE

The replace string function replaces a part of a string with another string. This function is case sensitive.

select replace('hello world', 'wor', '$$$');

Can you guess what the result of this would be?
Result: hello $$$rld
In the example above, we have replaced 'wor' with '$$$'.
The below screenshot uses replace on the "title" column in the books table and replaces all lower letter e's with the number 3

You can also combine the replace string function with other string functions:

select substring(replace(title, 'e', '3'),1,5) As weird_title from books;

The screenshot below shows the results: All the lowercase e's have been replaced by the number 3 and the substring function has been used to extrapolate the first 5 characters from the data.

REVERSE

The reverse string accepts one argument (the string to be reversed) and the reverses it.

select reverse('hello world');

Result is: dlrow olleh

That's the end of this article. Hope you found it useful!

Popular string functions in MySQL - CONCAT and SUBSTRING

Jo — Fri, 23 Apr 2021 16:37:06 +0000

There are some very helpful and fun string functions in MySQl and I've decided to review them here as I go through Colt Steele's MySQL Udemy course. This article will serve as my notes for that section of the course.

CONCAT

The CONCAT function concatenates two or more value together. This is helpful for formatting data that is useful to the person reading the output.



select concat(author_fname, " ", author_lname, " - ", title) AS Author
from books;

A variation of the CONCAT function is the CONCAT_WS function, which stands for CONCAT with separator. This is useful if you're separating multiple pieces of data with the same symbol between them. The first argument in the CONCAT_WS function is the operator used to separate all the the other individual arguments.



select concat_ws(' - ', author_fname, author_lname, title) 
AS Author
from books;

As you can see from the example above, there is now a dash between the author's first name, last name, and book title.

SUBSTRING

Another helpful function is substring. This function is useful for extrapolating data when you only need a part of the string. This function is also useful in combination with other functions to make even more powerful queries. The basic makeup of a simple substring query includes the string that data needs to be extrapolated from, and the location of the data.



select substring('Jowayne', 1,2)

The result of the example above would give me "Jo", as the Jowayne is the string to be analyzed, the 1 is location where I need the extrapolation to begin, and 2 is where I need it to end.
You can also choose to just use one number(the starting index) and the function will automatically start at that number and go till the end, like so:



select substring('Jowayne',3)

The result of this would be "wayne" since I started at 3.
The substring function also accommodates negative numbers. The substring would begin at the end of the string so:



select substring('Jowayne',3)

The result would be 'yne'.

And as I referenced before, you can use substring with other string functions:



select concat(substring(title, 1, 10),'...') from books;

That's it for this post! Thanks for reading :)

SQL Review - Useful queries for mySQL

Jo — Wed, 21 Apr 2021 01:42:00 +0000

This post will cover some useful mySQL queries that I am just now discovering and that would have been really helpful when I just started my journey of learning sql. Alas, we live and we learn.

Show databases

The

show databases;

command shows you a list of the databases available.

Use database_name

The

use database;

command allows you to switch between databases. I've found this one pretty helpful, pretty helpful I'd been double, triple, and quadrouple clicking a database name in mySQL in order to navigate among databases.

Show table

The

show table

command shows you the tables in a database (which could be especially useful after the use database command)

Desc table_name

The

DESC table_name;

command shows field, type, key, null value, and default values of a table. This is very helpful for troubleshooting.The extra column would hold something like "auto _increment" for a primary key.

Show Warnings

The

SHOW warnings

command shows what the warning message is if your query is not correct in some way.

SQL Review - GROUP BY Query

Jo — Sun, 11 Apr 2021 00:43:54 +0000

The GROUP BY clause is mostly used with aggregate functions in sql. Aggregate functions in sql are different from single row functions in one main way: Single row functions return a single result row for EVERY row of a queried table or view. Aggregate functions performs a calculation on a set of values in a column and returns one single value. The 5 most common aggregate functions in sql are: COUNT(), MAX(), MIN(), AVG(),SUM(). These do exactly what they sound like they do, for example:

select max(supply) from fruit_imports;

The other main difference between aggregate functions and single row functions is that while you use the "WHERE" clause to filter information in a single row function, you would use the "HAVING" clause to filter information in an aggregate function query (examples and screenshots coming up, don't worry)

In the graphic above, we see the anatomy of a GROUP BY query.

We will be working with the example below to discuss the GROUP BY query.

Any non-aggregate (not a MIN,MAX,COUNT,AVERAGE or SUM) column must be mentioned in the GROUP BY clause. Please see the screenshot below:

In the screenshot above, there are two columns, the sum_supply column and the state column. The sum_supply column is the aggregate column and the state is the non-aggregate column. The state column is the one that is included in the GROUP BY clause because it is non-aggregated column.

HAVING

Having is used to filter group data. If you are filtering data with the HAVING clause, it must come after the GROUP BY clause. HAVING is used on aggregate functions in lieu of the WHERE clause.

SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
HAVING COUNT(*)>2;

The results in the screenshot show all the employee names that are mentioned more than 2 times. That means in the full list of employee first names, there are 3 Roslyns and 3 Billies.

Thanks for reading! Hope this informative. Please feel free to comment, clarify or add to this :)

SQL Review - How to create an ERD in MySQL

Jo — Mon, 22 Mar 2021 20:32:14 +0000

During my self study of SQL, I've encountered the concept of the ERD or the Entity Relationship Diagram, which seems quite interesting as it can be useful in building your database. It also seems gives you a visual representation of the tables in your database which I assume can also aid in problem solving.

In order to create an ERD in MySQL, select Database > Reverse Engineer, as demonstrated by the graphic below:

Then, select the Continue button

MySQL will now ask for your password, which will be the same password you used to create the database initially.

Next, you will be asked to select a database to work with. Select a database, then select "Continue".

Select "Execute"

Tada, there is your ERD.

SQL Review - Differences Between MySQL and PostgreSQL

Jo — Sun, 28 Feb 2021 02:24:05 +0000

I am currently working through a Udemy course where the instructor is using PostgreSQL and I am using MySQL with the result being that I have to do things a bit differently in order to achieve the same results. I thought this would be an efficient way to learn the nuances of both systems simultaneously (I also have access to PostgreSQL on my MAC). Currently I am learning about single line functions, and encountered a pretty big difference with how both databases handle the capitalization of the first letter of a word. With PostgreSQL it is as easy as:


 INITCAP('hello')

result is 'Hello'

In MySQL however, it gets a bit more complicated:


 CONCAT(UPPER(SUBSTRING('hello',1,1)),LOWER(SUBSTRING('hello',2)))

Result is also Hello.

The image below shows a running example in MySQL where instead of using a string I have used the name of one of columns in the employees table.

I decided to keep track of the differences to use for future references since I spent several minutes googling how one would capitalize the first letter of a word in MySQL. Future Jo will thank me I'm sure.

I will continue adding here as I encounter more differences between the two databases.

Differences in Concatenation Between PostgreSQL and MySQL

Concatenation is briefly mentioned above but I thought I would go into more in depth here.
In PostgreSQL we concatenate strings or columns by using 2 pipe symbols(in some programming languages like javascript, the || means 'or'.
In PostgreSQL it is used to concatenate like so:
SELECT hire_date ||' '|| '09:00' FROM employees;

I included the ||' '|| to put some space between the date and the time.

PostgreSQL also makes use of the CONCAT function in the same way that MySQL, offering even further flexibility.

In MySQL, we use use the CONCAT function to concatenate:
SELECT CONCAT (hire_date,' ', '09:00') AS Date FROM employees;

Here we see the CONCAT can take multiple expressions and they are separated by commas, not | |.

Differences in date in MySQL and PostgreSQL

When converting dates in Postgresql, we use the to_char function which accepts the column we want to convert, as well as the format with which we wish to convert as demonstrated by so:
SELECT to_char(hire_date, 'yyyy/mm/dd') FROM employees
The year, month and day do not need to be separated by a slash(/). They can also be separated by a dash(-) or a space.
My favorite part about working with dates in PostgreSQL is how flexible and intuitive it seems to be, just check out the example below:
The code is:
SELECT hire_date, to_char (hire_date, 'yyyy Mon day the dd') FROM employees;

The results as displayed are in the format 2006 Apr thursday the 20

Things work a bit differently in MySQL as the date_format function is used instead of the to_char. MySQL also seems less intuitive in terms of writing the date format
SELECT date_format(hire_date,'%b %D %Y') from employees;
Upon reading the above, it may not be clear how the function would actually convert the date.

Here is a reference for what the symbols mean MySQL Date references

Subtle Differences between Trunc and Truncate in MySQL and PostgreSQL

As I was testing out my sql queries in my two different databases, I realized that there are subtle differences in the way both databases handle the trunc function that may be a bit confusing initially.
The MySQL TRUNCATE() function truncates a number to the specified number of decimal places.
You must specify the number of decimal places when using the Truncate function in MySQL. If you don't, you will see this error returned to you :
The TRUNCATE() function truncates a number to the specified number of decimal places.

In Postgresql, the function we use to accomplish the same thing is called TRUNC(), and you do not need to specify decimal numbers the way you do in MySQL. At least if you don't it will not explicitly return an error.

Boolean Expressions

I have started my THIRD Udemy SQL course (applause please) and while some of the information has become repetitive, in a lot of ways, it isn't. I am still learning new ways to do different things and how am still learning about the different nuances of PostgreSQL and MySQL.
In this particular course, I was introduced to Boolean expressions which I think is a really helpful way of displaying information. The query used to display Boolean expression is exactly the same in both databases but the results are slightly different (at least, they are aesthetically different in how they're represented) But, the mean the same thing:


 first_name, last_name, salary, (salary > 140000) 
AS SalaryOver140K
FROM employees
Order By SalaryOver140K;

Here are the results in mySQL

Conversely, here are the records in PostgreSQL

In mySQL, the 0 represents false and the 1 represents True. This representation is a lot more clear in PostgreSQL, which tells you clearly if the condition is "True" or "False"

How to Complete a Udemy Course

Jo — Wed, 17 Feb 2021 19:12:18 +0000

If you're anything like me, you have a Udemy account filled with courses that you bought on one of the site's many sales. Udemy is indeed a developer's catnip. We just can't get enough. We're gonna be so productive and learn all the things!
But if we're being honest, buying a course is usually the first and only step we make. Life happens, we get busy. But the cycle continues because the courses (when they're on sale) are so cheap. So tempting. There are worse things to be addicted than Udemy courses. At least we bought the course. It's the thought that counts right? Where's my cookie for spending 10 bucks on a Udemy course instead of Chipotle?!

This year I decided that instead of buying more Udemy courses, I would actually start working through the ones I had purchased. I had decided to start learning SQL, and luckily I had already stashed 3 SQL courses in my Udemy account. I picked the shortest one to give myself some motivation. It was a 4 hour Oracle course, and I incorporated it into my #100daysofcode challenge. Some days I would only watch one video, others I would watch 3. I tried to not let more than a couple days pass without at least watching one video. These are baby steps, well not even steps since it took me about 29 days to finish the course. 29 days! For a 4 hour course! But I completed it, instead of allowing to sit there for another year. I have moved on to another SQL course, this one about 10 hours long. I look forward to completing that one as well.

TLDR:

Don't buy any more courses
Choose the shortest course you already own on a subject matter you'd like to learn
Watch at least one video a day, no matter how long it takes.

Hope this helps motivate someone to stop buying and start doing :)

SQL Review - WHERE, SELECT

Jo — Thu, 28 Jan 2021 21:16:43 +0000

I've recently started teaching myself SQL as part of the #100daysofcode challenge and I thought I would make some notes in order to review some of the things I've learnt.
SQl is the language used for storing, manipulating and retrieving data. The SQL Language is used with MySQL, SQL Server, MS Access, Oracle and other systems.

SQL stands for Structured Query Language. It can execute, retrieve, insert, update, delete and create records in a table in a database.

SQL Statements

Most of the actions you need to perform on a db are done with SQL statements.

Ex.
SELECT * FROM employees;
This line of code is using the SELECT statement to query all the column names from the employees table using the * (which means all columns)

SQL keywords are not case sensitive, so SELECT is the same as select, but it is common practice to to capitalize keywords.

SELECT SYNTAX

SELECT Syntax is as follows:

SELECT column1, column2 FROM table_name;

An example of this is:

SELECT age, salary 
FROM employees;

Where age and salary are both columns in the employees table.

SELECT DISTINCT

The SELECT DISTINCT keyword is used to retrieve unique values in a table, so values will not be repeated in the result set. Since a column can often have duplicate values, using the DISTINCT keyword is a good way of removing those duplicates.

For example, customers may purchase the same kind of fruit. You may just want the data for the kind of fruit purchased, and not necessarily all data for all the fruit each individual purchased.

Instead of typing SELECT Fruit FROM Customers;
We would type:
SELECT DISTINCT Fruit FROM Customers;

We can get even more specific while using the DISTINCT keyword.
For ex:

SELECT COUNT (DISTINCT Fruit) FROM Customers;

As the keyword COUNT may suggest, this query will count the number of distinct values in a column and return that value to you.

The WHERE Clause

The WHERE clause is used to filter information in a query statement.
The syntax is as follows:
SELECT column1 FROM table_name WHERE condition;

For ex:

SELECT * FROM employees
WHERE State = "Idaho";

This will return all the information about employees who live in Idaho.

Text and Numeric Fields

You must use single or double quotes around text fields in SQL. The opposite is true for numeric fields, as they do not require quotes and will result in an error.

Note: You must have a semicolon at the end of each query for it to run. You can't just do whatever, this isn't javascript ;)

That's it for Part 1. Thanks for reading!