Understanding Data: Mining Vs. Visualization

""
Here’s a reality check: Big Data has hit us like a speeding truck on the highway of business intelligence. In today’s digital age, we’re generating data about ourselves that were once considered private, and we’re doing it willingly! From what we eat and wear to where we are at all times, nearly everything is now public knowledge. The data generated is a potential diamond mine for everyone in business, from one-person companies to Fortune 500 A-Listers alike, all thanks to the Internet.

Must-Know SQL Terms for Beginners and Pros

Working with databases can seem daunting to a non-technical person. Right away, you’re bombarded with new terms that make your head spin. Database, database instance, table, SQL and others are some of the basic terms that you need to understand just to have a normal conversation with your technical colleagues. In this article, we’ll explore some basic database terminology you need to know to succeed. Imagine you’re attending a meeting with the development staff and suddenly feel as if you’re listening to white noise.

SQL Interview: Tips From Recruiter and Sample Questions with Solutions

Applying for an SQL-related position? You’ll likely have to pass an interview process. But you’ll be much better prepared for the interview if you know what to expect. This article sheds some light on the recruiter’s perspective to better prepare you for your interview. SQL Knowledge and Skills There are different SQL skills that an interview can test. These include: Skills related to any specific SQL database engine (like PostgreSQL, MySQL, or Oracle).

Correlated Subqueries in 5 Minutes

If you’re familiar with the famous Russian nesting doll, then SQL subqueries should be a peace of cake to understand—subqueries are just queries nested within queries. An SQL subquery is often called an “inner” query; the main query is usually called the “outer” query. This article covers everything you need to know about correlated subqueries. What Exactly is a Correlated Subquery? A correlated SQL subquery is just a subquery that is executed many times—once for each record (row) returned by the outer (main) query.

New Vertabelo Academy Exercise Set – Solve Basic SQL Questions with the SQL Practice Set

If you’re looking to retrieve any kind of information from a database, you need to speak its language. But querying databases is an art—and it demands practice. We spend a lot of time not only developing new courses but also speaking with our users and learning what they have to say about online learning. This is how we came up with the idea of creating an SQL practice set.

How to Become a Database Analyst

Curious about becoming a database analyst? Maybe you’ve taken some database courses at university and they really struck a chord. Or maybe you learned online. Now you’re thinking about making a career out of working with databases. Where would you start? What should you expect at each phase of your professional development? In this post, we’ll explore the challenging and exciting world of databases analysis. We’ll go from the very beginning of a career to the apex of professional success.

Extracting Data From a String in PostgreSQL

Learn how to extract data from strings in PostgreSQL using the split_part function. Quite often, we’d like to extract parts of a string when working with text values. A common example is when we have a full name and need to retrieve only the last name. In this article, we’ll examine split_part, a PostgreSQL string-related function that can be used to extract a substring. Why Use String Functions in Your Database?

12 Best Data Science Resources on the Internet

Data science is hot right now. If you want to learn more about it, where should you go? Online, of course! Check out our favorite data science sites. Whether you’re a beginner or a pro, these are sites you should know. Not so long ago, if you wanted information on a topic like data science, you had to look for it – either at your local library or at a university.

How to Create Good Visuals

In this article, we’ll take a look at guidelines you should follow to create compelling visuals. Our goal is to learn how to effectively convey information through graphics. Have you ever looked at raw data—spreadsheets of stray numbers—and struggled to make sense of it? We’ve all been there, but it’s no surprise—because the human brain processes visualizations and images 10,000 times faster than raw data. In fact, 80% of the information we absorb comes from visuals, and the remaining 20% is text.

Why Move Data from Excel to a Relational Database?

Excel allows business professionals to unlock the potential of their data – to a point. But when it comes to storing and manipulating large amounts of data, Excel can cause some problems. Excel has many good points. It’s often used by specialists to work with data related to their particular field. But it can come with problems, such as sluggish performance and difficult team collaboration. Even worse, Excel is vulnerable to the most dreaded problem of all: the accidental deletion of important data.

How not to show data on a π chart

In this article, we’ll take a look at some of god-awful pie charts and hopefully learn a thing or two about good data visualization. March 14th is also known as PI Day. Mathematicians rejoice! π is a constant — the ratio of a circle’s circumference to its diameter — and it’s used in many different formulas. Baking and eating pies is super popular on this day — ’cause, you know, people just love their homophones.

Data Visualization for Your Business: Does it Really Matter?

Our world is bursting with data—the new digital age has dynamically inflated the volumes of data collected by businesses worldwide. By 2020, about 1.7 megabytes of new information will be created every second for every person on the planet. According to Forbes, The White House has already invested more than $200 million in big data projects. Can you imagine that? Seriously! Yet all the data that’s being generated by people, machines, online devices, and other sources doesn’t provide decision-makers with any valuable insights by itself.

Here’s Why You Should Learn SQL

We asked our users why they decided to learn SQL. This is what we’ve learned. At Vertabelo Academy, we help people with no computer science backgroundto understand the core concepts of databasesand data science. For example, we teach users how to extract the most important data from a database to build simple reportsand how to understand key statistical metrics in data analysis. We love to provide easy-to-understand examples that thoroughly depict complex technical concepts.

Learning to Think in SQL

Learning to think in SQL will help you understand what SQL queries do, how to create and change them, and how to create reports from the data you obtain. One of the greatest skills you can have in SQL programming is this the ability to change an existing SQL query so you get the results you need. When you are first starting out, you likely will not create SQL queries from scratch but rather reuse what others have written.

My Very First Script

We recently published a story from a Vertabelo Academy user who found her love thanks to SQL. On a similar note, I asked my colleagues to bring back old memories of writing their very first scripts. And yes, love is involved. Here at Vertabelo Academy, we’ve got some real OG programmers and data scientists making great content. They fell in love with data way before it was cool—and easy. So today, we’re jumping back in time to see what it was like to learn SQL before the Internet and Vertabelo Academy courses existed.

New Vertabelo Academy Course on Data Visualization: Share Your Insights With Everyone!

In today’s data-driven world, a good visualization goes a long way in helping people make sense of numbers. Every day at the office, we’re working hard to create programming and data science content that is accessible to everyone. We aim to produce content that is easy to understand, primarily for people with no IT background. And you know what? Ironically, this stuff ain’t easy even if you’re an IT specialist!

SELECT * FROM love

They say that if you do what you love, you’ll never work a day in your life. At Vertabelo Academy, we strongly believe in this adage. We’re creating courses because it’s what we enjoy doing. We’re passionate about SQL and data science, and we want to deliver the best content possible in these domains. To that end, we provide our users with practical modules because learning by doing is the best way to master a new skill.

Who Loves Whom?

Just in time for Valentine’s Day, we discover which of Gary and Sara’s friends are in love – and how to use the SQL JOIN statement to work with data in multiple tables! Did you know SQL is a matchmaker? That’s right; it loves to make data couples by linking information from two (or more!) tables using the JOIN statement. However, many SQL rookies run into problems using JOIN. So let’s go to a party and learn how to use JOIN the right way!

How to Deal with Unwanted Characters in SQL

Unwanted characters in text data can be a bit of a pain, but there’s an easy way to fix them! Sometimes, we’ll find extra characters inside our string data because our SQL queries didn’t work as expected. Moreover, these extra characters may sometimes be invisible, which really complicates things. In this article, we’ll examine some string-related SQL functions that can handle unwanted characters—visible or not! The Importance of Standardizing Strings in Your Database When we work with data populated or loaded as free text, the data quality is often quite poor.

How to Solve Capitalization Data Quality Issues

Misspelled names, typos, and text data quality issues in your database? Power up your queries! Use SQL string functions to address data quality issues related to capitalization. Sometimes, our SQL queries don’t work as expected because of data quality issues. In this article, we will examine some string-related SQL functions that can correct data quality issues related to capitalization. We’ll be using PostgreSQL in our examples, but similar functions are available in most database engines.

Introduction to ggplot2

Show, don’t tell! Share data insights in stunning color and display with ggplot2, a wonderful R package for visualizing data. Ggplot2: Grammar of Graphics The end of qualitative data analysis should be clear—beautiful data visualizations. We are visual beings, after all, and a picture tells us far more than raw numbers! Among the many visualization tools, one in particular stands out : ggplot2—a free, open-source, and easy-to-use package that has become a favorite among many R programmers.

15 Best SQL Articles for Beginners Published in 2017

For this post, I’ve gathered 15 of the most interesting SQL articles published in 2017. If you’re an SQL beginner and are interested in learning more about databases and SQL queries, or if you’d just like to expand your knowledge of SQL, this article is perfect for you. Let’s dive right in! Top 3 Vertabelo Academy articles I’ll start off the list with three of the most popular Vertabelo Academy articles.

18 Most Important SQL Functions to Learn ASAP

Mastering the basics for a smooth start to SQL Learning a new programing language can seem intimidating. Like any other language, a programming language has a large vocabulary that you need to master. In this article, we’ll look at some of the most important SQL functions that you need to know. SQL is a rich language with many useful functions for working with different data types. Once you practice with some of the most important SQL functions, you’ll gain enough confidence to move on to more advanced material.

SQL Advent Challenge 2017: Day 24 (Final Call)

Unfortunately, all good things must come to an end—this is your last query task for this season! We hope you’ve enjoyed the challenges so far. The good news, however, is that you got to practice your SQL skills. At this point, you’re only one step away from completing ourcrosswordand getting a Vertabelo Academy discount on a course of your choosing. Task #24: How much did Santa Claus pay for each toy he purchased from the musiccategory?

SQL Advent Challenge 2017: Get 25% OFF for Helping Santa (updated list of tasks)

Wanna be part of this year’s Christmas success? Santa needs your help organizing his Christmas deliveries. For this occasion, we’ve prepared a special Christmas database. While working on tasks, you’ll help Santa Claus deliver gifts to children. For this upcoming Christmas, we’ve prepared a 24-Day SQL Advent challenge. From now until Christmas Eve, we’ll publish daily SQL tasks for you to complete. If you’ve completed or recently started our SQL Basics course, or if you already have basic SQL knowledge, then this event is perfect for you!

SQL Advent Challenge 2017: Day 23

Task #23: Santa Claus ordered a gift for a particular child on December 12th, but that gift has not been delivered yet. Who is the child? Answer 23 for the crossword: The child’s name. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 22

Task #22: Find the name of the toy that most children asked for in their letters. Select the toy name and the number of requests for this toy. Answer 22 for the crossword: The toy name. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

How to Draw a Christmas Tree in SQL

You can use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. But you can also use SQL just for fun, without any business requirements stifling your creativity. So, get out your jolly hat and prepare to sing O Christmas Tree as we create some quirky art with plain old SQL. Today, we’re going to generate some holiday-themed ASCII art, just for fun. That’s right.

SQL Advent Challenge 2017: Day 21

Task #21: Santa Claus wants to buy some surprises for children from Argentina who either didn’t ask him for any toys or simply didn’t send him any letters. Help Santa Claus find the names of these children. Sort the records by child name in ascending order. Answer 21 for the crossword: The name of the last child in the result set. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 20

Task #20: Retrieve the name of the child who did not write a letter to Santa and whose country name is composed of two words. Answer 20 for the crossword: The child’s name. Challenge Accepted!? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 19

Task #19: In which store did Santa Claus spend the most on gifts? Answer 19 for the crossword: The second word in the store name. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 18

Task #18: How many letters were written by children from countries other than those in which there was at least one naughty child? Answer 18 for the crossword: The number returned by the query, with the digits reversed. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 17

Task #17: Which children were naughty this year, and what countries are they from? Sort the records by country name in descending order and by child name in ascending order. Answer 17 for the crossword: The name of the last child in the result set. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 16

Task #16: Retrieve the largest number of toys that children asked for on a particular day during the period from December 6, 2017 through December 16, 2017. Answer 16 for the crossword: The number returned by your query. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

High-Performance Statistical Queries: Dependencies Between Discrete Variables

In my previous article, we looked at how you can calculate linear dependencies between two continuous variables with covariance and correlation. Both methods use the means of the two variables in their calculations. However, mean values and other population moments make no sense for categorical (nominal) variables. For instance, if you denote “Clerical” as 1 and “Professional” as 2 for an occupation variable, what does the average of 1.5 signify?

SQL Advent Challenge 2017: Day 15

Task #15: What gifts did Santa Claus buy for children from the World of Sweets store? Sort the results by gift name in ascending order. Answer 15 for the crossword: The name of the last gift in the result set. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 14

Task #14: Retrieve the names of all stores from which Santa Claus purchased gifts for children. Sort the results by store name in ascending order. Answer 14 For the Crossword: The first word in the last store name of the result set. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 13

Task #13: By coincidence, all children who sent Santa a letter on December 14th asked for gifts from some categories. Determine names of these categories. Answer 13 for the Crossword: Name of one of the categories. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 12

Task #12: Select the number of letters that Santa Claus received on each day of the period from December 1, 2017 through December 5, 2017. Answer 12 For the Crossword: The spelled-out version of the number of letters Santa received on December 4th. Challenge Accepted? Use the SQL Advent Challenge sandbox to solve tasks and practice writing all kinds of SQL queries.

Busy Business Professionals: Simplify Your SQL Code with Recursive Queries

Hey SQL users! Are you repeating the same query in every report? Are your queries getting too complicated? Organize them with recursive queries! Too many SQL reports can lead to clutter on your desktop and in your head. And is it really necessary to code each of them separately? Ad-hoc queries can share much of the same SQL code with managerial reports and even regulatory reports. Suppose you’ve been writing basic SQL queries for a while.

SQL Advent Challenge 2017: Day 11

Task 11: For each country in which at least one child forgot to ask Santa for a gift, select the country name and the number of letters children sent from that country. Sort the records by country name in ascending order. Answer 11 for the Crossword: The name of the last country in the result set. Ready to Challenge? Use the SQL Advent Challenge sandbox to solve tasks or practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 10

Task 10: Which children wrote letters to Santa Claus but forgot to ask him for gifts? Retrieve the names of these children and the countries from which they come. Sort the records by child name in ascending order. Answer 10 for the crossword: The name of the third child in the result set. Ready to Challenge? Use the SQL Advent Challenge sandbox to solve tasks or practice writing all kinds of SQL queries.

SQL Advent Challenge 2017: Day 9

Task 9: Select the names of the countries from which Santa Claus received the most letters. You can use the result of the previous task to write your query. Answer 9 for the crossword: The country name that is composed of one word.

SQL Advent Challenge 2017: Day 8

Task #8: What is the largest number of letters that Santa Claus received from among the various countries on his list? Answer For the Crossword: The spelled-out version of the number your query returned.

SQL Advent Challenge 2017: Day 7

Task #7: Retrieve the names of countries from which there are no children on Santa Claus’s list. Sort the results by country name in ascending order. Answer for the crossword: The name of the first country in the result set.

SQL Advent Challenge 2017: Day 7

Task #7: Retrieve the names of countries from which there are no children on Santa Claus’s list. Sort the results by country name in ascending order. Answer for the crossword: The name of the first country in the result set.

The SQL Coalesce Function: Handling Null Values

Though the COALESCE function may seem complex, it’s actually very straightforward and useful. In this short article, we’ll look at several examples of how the COALESCE function can be used to work with NULL values in SQL. The Need for Coalesce Before we dive into the COALESCE function in detail, you should understand how NULL values behave in expressions. Simply put, a value of NULL indicates that there is currently no value for a particular entry in a table column.

SQL Advent Challenge 2017: Day 6

Task 6: How many letters did children from each country starting with the letter ‘P’ send to Santa Claus? Sort the records by country name in ascending order. Answer for the crossword: The name of the last country in the result set.

New Vertabelo Academy Course: Learn How to Work with Spatial Data with PostGIS

Managing spatial data is useful but challenging – learn how to do it in PostgreSQL with our latest course. At Vertabelo Academy, we believe that learning by doing is the best way to master a new skill. So we’re excited to introduce a new, easy-to-understand course in PostGIS. PostGIS is a spatial extension for object-relational PostgreSQL databases. It stores geographical data objects, allowing SQL queries to process them as they would other pieces of data.

SQL Advent Challenge 2017: Day 5

Task #5: Which children sent more than two letters to Santa Claus? Retrieve the children’s names, their country names, and the number of letters they sent. Sort the records by children’s names in ascending order. Answer for the crossword: The name of the first child in the result set.