DEV Community: Wendy Calderon

SQL: Timestamps

Wendy Calderon — Mon, 06 Jan 2020 23:47:56 +0000

The TIMESTAMP data type holds values that contain both date and time, fixed at 19 characters. The format of a TIMESTAMP is 'YYYY-MM-DD HH:MM:SS'

In DBMSs such as MySQL, DATETIME is a supported data type that, just like TIMESTAMP, is used to hold date and time values, but they're not entirely the same for the following reasons:

Size: DATETIME requires a few more bytes for data storing than TIMESTAMP.
As specified in the MySQL documentation, MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval, which does not occur for other types such as DATETIME. This is particularly convenient when working with different time zones.
Different supported ranges: '1000-01-01 00:00:00' to '9999-12-31 23:59:59' for DATETIME, '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC for TIMESTAMP.

Retrieving data from TIMESTAMP:
In order to fetch information from a TIMESTAMP object, we can use the extract() function, for which we specify the field from TIMESTAMP we want. Examples of this field would be:

Let's consider the following table to work on some examples:

All payments were made in november, so let's grab these bills along with the days they were paid:

We add the AS statement to name the resulting column from using EXTRACT.

If our bills table was larger, including payments to these bills all throughout the year, we could implement extract() and an aggregate function (SUM) to show, for example, the total amount of money paid in bills by month, with a query similar to this one:

The larger our databases, the more ways available to retrieve info!
Have fun!

SQL: Union Operator

Wendy Calderon — Mon, 02 Dec 2019 13:53:47 +0000

In our previous SQL posts we learned how to combine tables using the Join clause to produce results based on common columns among these tables. In this post, we will be talking about the UNION operator, which in simple words is used to combine the results of different SELECT statements into one results set. The following image shows it's basic syntax:

The UNION operator comes with some requirements that must be met in order to be able to implement it:

All SELECT statements or queries must have the same number of columns.
Said columns must appear in the same order in all queries.
These columns' data types must be the same or compatible.

Let's consider the following tables to study this operator:

Now, let's use it to combine these tables into one:

The results might look like this:

Notice we mentioned that the results might look like the previous image, and that's because we presented these results sorted by bill, but without an ORDER BY clause, the order of these results could vary.

When we use the UNION clause, all duplicate records are removed. If we want to keep these records, we should use the UNION ALL statement, which includes them in the final results set.

Had we used UNION ALL in our previous example, the columns mortgage and cellphones would have appeared twice in the results set.

Learn about SQL Inner Join, here.
Learn about SQL Outer Joins, here.

SQL: Outer Joins

Wendy Calderon — Sat, 30 Nov 2019 15:12:23 +0000

In our second part of learning how to work with the SQL Join clause, we will focus on the Outer Joins. The first part, where we talk about Inner Joins, can be found here.

So, what is an Outer Join? Just like an Inner Join, an Outer Join is used to query databases combining tables based on a related column. Different from an Inner Join, an Outer Join could be used to return data that is not common among tables.

There are various types of Outer Joins, and we will explain them based on examples. For said examples, let's consider the same two tables of our previous post:

Full Outer Join: This version of the Join clause will return all records from both tables, adding null where a match isn't present. In the next image we use the Venn Diagram to graphically explain the result of joining these two tables with the Full Outer Join clause, or simply called Full Join.

Let's fetch all records from out pets' names, the doctors who have seen them, and the date they were seen, implementing an Outer Join. The syntax would look like the following:

Since Full Joins are not supported on MySQL, here's the syntax to be used in this DBMS:

The results would be the following:

As you can see, we have two rows for our cat Daisy, who was seen by two different doctors and/or on two different dates. Our fish Jules has not been seen, that's why it doesn't return any information about a doctor or date, and these fields are filled with null.

Left Outer Join: This Join, also known as Left Join, returns all records from the first table (or the left table) with the matches from the second table (or right table), adding null where no matches are found.

If we want to see which species have been seen by which doctors, we can query our database with a Left Join to return these results.

We see all records from our pets table with it's corresponding matches from the checkups table, and blank spaces for those records from pets without a match in checkups. Remember, these blank spaces are to be considered as null.

The Right Outer Join works the same as the Left Join, but inverting the tables: it will return all records of the right or second table, with matches from the left or first table, and null values where no match is found. If we were to replace the previous query with a Right Join, it would return the same results, except for the last row, because the null value corresponds to the checkups table.

Left Outer Join with WHERE clause: Remember when we mentioned at the beginning of this post that one difference between the Inner Join and the Outer Join is that the last one allows us to return values that are not matches between both tables? This can be achieved by adding a WHERE statement to our query.

So let's say we want to see which of our pets has not had a checkup. We know, from our previous examples, that for our fish Jules we received null values when trying to return similar data. We could use this value to comform our new query, so that the result will show that Jules has not been checked:

Pretty cool, huh? And the same goes for the Right Outer Join with a WHERE clause.

Full Outer Join with WHERE clause: This is the type of Join that allows us to return the records unique to both tables. In other words, we would be getting everything but the matches.

We select the columns of our interest from the pets table, Full Outer Joining checkups on the column they both have in common, WHERE these column's records are null, meaning where they don't have a value.

Again, MySQL does not support Full Joins, so the query would be:

And our results:

Our tiny example only includes one record that is unique to one of the tables, the pets table to be exact, so this is the reason for our result.

Outer Joins are a powerful tool at our disposal, and hopefully these lines brought some clarity on the matter.

SQL: Inner Join

Wendy Calderon — Mon, 18 Nov 2019 13:26:19 +0000

In SQL we have various powerful clauses that make managing databases possible, but one that usually brings confusion along with it's functionality, is the JOIN clause.

The JOIN clause is used in a relational database to select records from two or more tables that share at least one column in common. There are different ways of joining tables, and in this post we will be discussing, through the use of an example, the one corresponding to the INNER JOIN.

INNER JOIN will compare the specified tables and select those rows that have matching values. It could be considered the default JOIN type, since we would get the same result without specifying the keyword INNER.

It's syntax is the following:

In the SELECT clause, we specify the columns we want to select, by chaining them to the table they correspond. Pro-tip: we only do this for columns that are not specific to one table. If we want to select a column that is specific to just one table, we don't have to specify the table name.

Now let's explain how it works, based on an example. The following image shows the tables to be considered.

In this example, we see that in the pets table we have a pet_id column that is common to the checkups table. The pet_id column in the pets table is referred to as Primary Key, and the one in the checkups table as Foreign Key.

The next image graphically explains the result of joining these two tables with the INNER clause, referencing the Venn Diagram.

Now, considering the tables above, if we wanted to get our pets names, their species and the date they had a checkup, the query would look like this:

Let's break it down: We select the columns we want, and since they are unique columns to each of the tables, we don't have to chain them to their tables. We select them from pets, and join the checkups table on the column both tables have in common (remember, the INNER keyword is optional). We could've selected from checkups and joined pets, as long as we join them on their common field, we would be getting the same result.

In a following post, we'll discuss the other ways available to join tables. See you there!

E.T.A.: The following post, where we discuss Outer Joins, can be found here.

Why learning SQL is so relevant to Developers

Wendy Calderon — Mon, 11 Nov 2019 14:35:18 +0000

A few weeks ago, whenever I heard the word SQL, my hands started to sweat. I knew it had to do with the fact that I had not been introduced to it the proper way, but at the same time, I was not aware of how important this skill is to further my career. I did some research on the matter, while adamantly sharpening up said skill, and thought it is worth sharing.

Credits: csc.barnard.edu

What is SQL? In simple words, it is the language used to interact with relational databases. It stands for Structured Query Language and it has been around for over 40 years. This interaction is possible through a Database Management System, a software that handles the storage, retrieval, and updating of data in a computer system.

As our applications and/or companies grow, more and more data needs to be managed, and this applies to basically any scenario. Imagine a company growing in size, that is keeping track of it’s employees information in a spreadsheet, storing personal data in one table, clock-in/clock-out times in another table, vacation schedules in a different table, etc. A simple task like getting the emergency contact information of the employee who took a break at 3:15pm today could take double, triple, you name it, the time a SQL query would take to retrieve this data from a relational database. This spreadsheet is an example of a flat database.

SQL is the most popular database query language, and since we have covered why storing large sets of data in a relational database is convenient, it’s clear to see why adding it to one’s skill set is important. It’s learning curve is also slight once you find the right resources.

Another important reason to consider learning SQL is the revenue; not only is this skill in high demand, it also pays well. According to Glassdoor, the average pay for a SQL Developer is approximately $84k.

Credits: glassdoor.com

In Stack Overflow’s 2019 Developer Survey results, SQL ranks as the third most popular technology among professional Developers, and it’s been in the top 5 since this breakdown started back in 2015.

Credits: Stack Overflow

When it comes to Database Management Systems, the 5 most popular in 2019 according to the same survey are MySQL, PostgreSQL, Microsoft SQL Server, SQLite and MongoDB, in that same order.

To sum up, just like any other technical skill in the Development field, SQL helps solve important problems in any organization that deals with large amounts of data. It is also an intuitive query language, in high demand and with great profit.

Scratch: Not just for kids!

Wendy Calderon — Mon, 04 Nov 2019 13:47:26 +0000

Scratch is a free web-based graphical programming language created by the Massachusetts Institute of Technology Media Lab (MIT Media Lab), geared primarily towards children, but don't let that fool you, it is challenging enough for adults as well.

I was introduced to Scratch while seeking the CS50 Certification from Harvard University. In the first week (week 0), we are asked to build a game, interactive story or animation using this block-based visual programming language.

To start working on your first project, sign up to their site, click on the create button and let the fun begin! Your browser should be showing something like this:

The toolbar at the top will allow you to change languages, save your work (even to your computer), access short tutorials, rename your project, share it, and see it live. Fun fact: Scratch has been translated into 70+ languages!

Sprites: These are characters that we can animate in our story. They go from the default cat we see, to apples, toys, you name it! To add a new sprite, click on the cat-looking icon at the bottom right-hand corner. We also have to option to load our own sprite, or draw/paint it! Most sprites have more than one costume, this is what allows us simulate a walking cat, or a dancing ballerina, in other words, changing our sprites' costumes will let us animate them. When we click on a sprite, we'll also have the option to rotate them, make them visible or not at a certain point, or modify how they look.

Another cool feature we could add to our sprites, is sound! When you click on the sound tab you will see the pre-defined sounds each sprite has, but will also be able to add new ones by clicking on the speakers-looking icon on the bottom left-hand corner. You can choose sounds from the ones already on Scratch, or upload, even record your own! All these sounds can then be edited, making them faster, slower, fade in or out, reverse them, etc.

Backdrops: These are the backgrounds of your project. You could choose from the pre-loaded ones, draw or upload your own. The backdrops can be changed at any point throughout your project.

The Code tab: Here's where we put together scripts to animate our sprites and compose our game or animation. All the blocks are draggable and act as magnets that easily connect to one another.

Motion: Here we can control how or where to our sprites move, by specifying the horizontal and vertical axis (x and y), instructing it to "look" to the mouse pointer, etc.
Looks: Just as it's name denotes, here we can edit a sprite's appearance. Also, we can make our sprite "say" or "think" something, by showing a dialog box to the user. This coding section is the one that allows us to control the visibility of a sprite and costumes/backdrop changes.
Sounds: Control how and when a sound appears.
Event: The blocks in this section are the ones we use to start a script, that's why they cannot be connected to the bottom or another block, except for the broadcast ones, which allow a script to send a signal to another (or others), to possibly start or end an action.
Control: Lists the options to control actions by adding loops, conditionals, wait time, etc.
Sensing: Cool control feature to wait for specific signals to proceed with whatever action we've instructed our sprite.
Operators: Perfom basic math operations, as well as provide ranges to pick a random value, add logical operations that result in booleans, etc
Variables: Just like just about any other programming language, variables allow us to store values in them.
My Blocks: Think of blocks as the methods that help us keep our code DRY (Do Not Repeat Yourself), where we define those actions that will probably be used more than once within a script.

Hopefully these notes will inspire you to build the next Scratch project! It's fun, visually pleasant and challenging. Enjoy!

binary: what? why? how?

Wendy Calderon — Mon, 28 Oct 2019 14:32:39 +0000

In simple words, binary refers to something that is composed of or relates to two things; something that can be broken into two groups or categories. In the context of computing and mathematics, binary is a numeric system consisting of just 0's and 1's.

Everything needs to be translated into these 0's and 1's, so our computers get the appropiate signals to respond accordingly to the instructions and/or tasks we want them to perform. This translation is possible thanks to, among other components, the thousands, even billions of tiny transistors that when receiving voltage turn ON, representing 1's, and in the absence of said voltage turn OFF, representing 0's.

So now that we know what binary is, and why it is relevant to the computing world, let's look at how we can convert the numbers we are used to working with, to the Binary System.

These numbers that we use on a daily basis, are part of the Decimal System, based on powers of 10.

As per the previous image, we can see that if we have a number such as 532, it is the result of the following operation:

Now let's say we have a sequence of digits representing a binary number, such as 101. How can we turn this number into our known "base ten" numbers? While the Decimal System uses powers of ten, the Binary System uses powers of two:

So knowing that the Binary System is "base two", we could convert numbers to "base ten" by considering their position from left to right, and summing up the values where a 1 is present.

Each of these binary digits are considered bits, and 8 bits conform a byte. Do these terms sound familiar? More on this on another post :)