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.

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.

SQL Advent Challenge 2017: Day 4

Task #4: How many letters did children from the United States, Canada, and Argentina write to Santa Claus? Select the names of these three countries and the number of letters their children sent. Sort the records by number of letters in ascending order. Answer 4 for the Crossword: The name of the first country in the result set.

SQL Advent Challenge 2017: Day 3

Task #3: Select the names of all children who come from Chile. Answer for the Crossword: The child’s name that is composed of the least number of letters.

SQL Advent Challenge 2017: Day 2

Task #2: Write a query that determines how many children come from the first country in the result set you obtained for the first task (all five-letter country names from the Santa Claus database, in ascending order). Display both the country name and the number of children who come from it. For the Crossword: The name of the country.

SQL Advent Challenge 2017: Day 2

Task #2: Write a query that determines how many children come from the first country in the result set you obtained for the first task (all five-letter country names from the Santa Claus database, in ascending order). Display both the country name and the number of children who come from it. For the Crossword: The name of the country.

SQL Advent Challenge 2017: Day 1

Task #1: Retrieve all five-letter country names from the Santa Claus database, and sort them in ascending order. Answer For the Crossword: The name of the last country in the result set.

SQL Advent Challenge 2017: Day 1

Task #1: Retrieve all five-letter country names from the Santa Claus database, and sort them in ascending order. Answer For the Crossword: The name of the last country in the result set.

Digging Into Data: Explore and Analyze Survey Results With SQL

Excel is a powerful beast that lets you analyze complex data. Yet, operating on big chunks of data can sometimes be a daunting task. Let’s take a look at how SQL can help. Today, we’ll tackle a common problem with importing data to an SQL database, using a real-life example. Suppose your company conducted a survey on the most popular programming trends and preferences, striving to meet the expectations of its users.

How Often Employees Are Running Late: Datetime And Interval Arithmetic in SQL

Computing Tardiness: Date, Time, and Interval Arithmetic in SQL In this article, we’re going to discuss some interesting operations we can perform with date-related data types in SQL. The SQL standard, which most relational databases comply with these days, specifies the date-related data types that must be present in relational databases. The most important of such data types are date, time, timestamp, and interval. Here’s a brief rundown of the differences between these data types:

SQL Hacks To Control Family Budget On Black Friday Weekend

If you’re in the US, chances are you’ve been eagerly awaiting the approach of Black Friday just as much as Thanksgiving. Though the shopping frenzy takes hold of nearly everyone, some people have to stick to their budgets and shop prudently. In this article, we’ll take a look at how generating an SQL report can help you track how much your family spent shopping on Black Friday. Storing Black Friday Purchases in a Database Before we can create an SQL report, we first need some data we can use.

Happy Thanksgiving! Using SQL to Prepare a Traditional Menu

Thanksgiving is a holiday many Americans celebrate with their families and friends, seated around a table loaded with a hearty feast. The most prominent of all dishes is, of course, the turkey. However, at least a few other specialties are also cooked for Thanksgiving. Do you find it hard to choose what to prepare? If so, we’ve got a solution for you: a special SQL query that will help you pick the best dishes.

SQL Crossword #1

How much SQL do you know? Test your knowledge with this crossword to find out! Download SQL Crossword #1 Psst! Be the first to post your answer in the comment section belowand get 50% OFF on the selected course.  

In Search of (Lost) Traditional Learning – Is It Viable in the 21st Century?

Has online learning killed traditional learning? Can they coexist? Or, together, can they form something great? When I decided to write about traditional ways of learning, I was sure there was something to write about. But I wasn’t sure exactly what. I looked at some traditional learning and teaching methods, which gave me pause. Nearly each one turned out to be not entirely traditional. Wherever I looked, digital influences were making inroads.