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.

Introduction to Reporting with SQL — the Ultimate Tutorial for Business Professionals

Working with the financial aspects of large and small enterprises can be a daunting task for a business professional. In this article, we’ll look at several ways of constructing the perfect SQL report. You’ve probably already heard about SQL from your colleagues or in other areas of your career. If you’re here, you’ve likely concluded that learning SQL will make your professional life easier – and you’re right!

5 Great Benefits of Learning SQL

If you want your business to succeed in 2017, it’s absolutely critical that you understand your data. If you want your business to succeed in 2017, it’s absolutely critical that you understand your data. Don’t fall for the common trap—analyzing data isn’t just a concern for those competing at national or corporate levels. In fact, all businesses have plenty of data that they can use to potentially improve their workflow.

Non-Syntactic SQL Errors Spotted

A look at common errors that arise when writing code as an SQL beginner. Debugging, Headaches, and SQL If you’re new to SQL, you’ve probably already run into a myriad of errors. Don’t worry — you’re not alone. All developers make mistakes; it’s a normal part of the development process. The key to mastering any programming language, SQL being no exception, is understanding where the most common mistakes lie.

Descriptive Statistics: Calculating the Mean in Various Data Series

The mean (average) is one of the most valuable and most frequently used measures in descriptive statistics. Why is it so widely used, and why is it important to know how to calculate the arithmetic mean? Perhaps the most convincing argument is that the mean is used in virtually every area of life. With the arithmetic mean, you can calculate the average daily television viewing time for citizens of a given country, average volume of coffee drunk by a typical American, average annual temperature in your city, or the average amount you spend on food in a typical week.

Spooky Scary NULL: Unexpected Danger Lurking in Your Database

Relational databases often store the value NULL in their tables. If you’re not familiar with NULL and what it is, you could run into unexpected problems. In this article, we’ll take a look at how the IN and EXISTS operators work with NULL. What Is NULL? In SQL, NULL is a special value indicating that a particular piece of data doesn’t exist in the database. For SQL beginners, it’s often a source of problems.

Popular SQL Interview Questions for Business Analysts

SQL is an extremely desirable skill for anyone in today’s market—not just those in the IT sector. It’s becoming increasingly common for employers to require at least a basic knowledge of SQL in professions related to finance, statistics, banking, and business analytics. In this article, we will focus on the SQL interview questions one can expect during an interview for a business analyst (BA) position. What is a Business Analyst?

Why Learn SQL?

Nowadays, data are gathered from a variety of sources, and information is of great value to organizations. However, with an increasing amount of data comes the need for databases that can store information for retrieval and analysis by trained specialists. Thus, proper data analysis is in high demand in our digital world. Relational databases, which logically group information into chunks, are basically used to store and organize large amounts of data.

Learn SQL Views in 30 Minutes

Views aren’t complicated – if you’ve got half an hour, we’ll get you started writing SQL queries using views! Let’s start by answering the question “What is a view in SQL?’. A view is a database object (as is a table, an index, or a stored procedure). Like a table, you can query a view and extract the information in it. It can be used in the FROM clause of a SELECT, and you can reference view columns in clauses like SELECT, WHERE and GROUP BY among other clauses as well.

Organize Your SQL Queries with CTEs

Common table expressions (CTEs) allow you to structure and organize your SQL code. Being able to write organized queries is a necessity when you begin to move deeper into SQL, so if you want to become an SQL master, you need to know CTEs. The CTE has been part of standard SQL for some time now. CTEs – which are also called WITH statements – are available in all major RDBMS.

SQL Window Functions By Explanation

In our previous post, we explained how SQL window functions work by example. We started with some very simple, basic functions. Let’s extend it by explaining subclauses in window functions. SQL window functions are a great way to compute results from a set of rows rather than a single row. As you know from our first article, the “window” in window function refers to the set of rows.

Long SQL Query vs. Recursive SQL Query

Recursion is one of the central ideas in computer science. We can define it as a method for solving problems where the solution of the problem depends on solving a smaller instance of a problem. If this sounds complicated do not fret, in this article we will learn about recursion in SQL that you can practice and deepen in Vertabelo Academy. Recursion is a way of solving hierarchical problems we find in data with common SQL.

High Performance Statistical Queries: Linear Dependencies Between Continuous Variables

In my previous articles, I dealt with analyses of only a single variable. Now it is time to check whether two variables of interest are independent or somehow related. For example, a person’s height positively correlates with shoe size. Taller people have larger shoe sizes, and shorter people have smaller shoe sizes. You can find this and many more examples of positive associations at: http://examples.yourdictionary.com/positive-correlation-examples.html. A negative association is also possible.

An Illustrated Guide to Multiple Join

So far, our articles in the “An Illustrated Guide” series have explained several join types: INNER JOINs, OUTER JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN), CROSS JOIN, self-join and non-equi join. In this final article of the series, we show you how to create SQL queries that match data from multiple tables using one or more join types. Join Types in SQL Queries Before we start discussing example SQL queries that use multiple join types, let’s do a short recap of the join types we’ve covered so far, just to be sure you understand the differences.

An Illustrated Guide to the SQL Non-Equi Join

Did you know that an SQL join doesn’t have to be based on identical matches? In this post, we look at the non-equi join, which uses ‘non-equal’ operators to match records. We’ve already discussed several types of joins, including self-joins and CROSS JOIN, INNER JOIN and OUTER JOIN. These types of joins typically appear with the equals sign (=). However, some joins use conditions other than the equals (=) sign.

An Illustrated Guide to the SQL Self-Join

What is an SQL self-join and how does it work? When should it be used? We’ll provide answers to those questions! In SQL, we can combine data from multiple tables by using a JOIN operator. JOIN has several variants; we’ve already discussed CROSS JOIN, INNER JOIN, and OUTER JOIN. Most of the time, these operators join data from two or more different tables. In this article, however, we will explain how to join records from the same table.

So You Want to Be a Data Scientist?

A career in data science is hot right now. What is a data scientist, and how can you become one? Very few of us said “I want to grow up to be a data scientist” when we were kids. But now, in the age of Big Data and economic uncertainty, a career in data science is looking mighty attractive. If you like the idea of working with information and earning a good paycheck, read on.

What Is Vertabelo’s SQL Cheat Sheet?

Everyone, from rookie programmers to SQL ninjas, needs a bit of help now and again. Enter Vertabelo's SQL Cheat Sheet! Working with databases is hard. There's a lot of data to manage, which can be daunting. But the main challenge many database users face is the SQL commands themselves. It's all right when you're using the same familiar ones day after day, but what about the commands you dust off once or twice a year?

Data Science: Market Demand or Just Hype?

Earth’s 7.5 billion people together use several billion different devices, generating an annual global IP traffic of more than one zettabyte. Out of these impressive numbers rises a new field above the others — “data science”. Is data science an inevitable reality, or will it yet be dismissed as just another “wave of the future” that never came to be? A few days ago I signed a new employment contract with my company.

The 5 Highest Paying SQL and Database Jobs

IT provides a wide and growing job market, offering many excellent opportunities in both career development and earning potential. Today we’ll take a look at several very interesting SQL/Database-related jobs. Every year, many in the work force set their sights and aim for new and better jobs. When it comes to jobs, “better” means different things to different people, but earning potential is almost always a top priority. Enter the growing field of IT, in which new challenges wait around every corner!

How to Track Down Duplicate Values in a Table

When it comes to information management, duplicates present one of the most common challenges to data quality. In this article, I’ll explain how it is possible to find and distinguish duplicate names with the help of the SQL data programming language. I really like my maiden name. The reason I like it so much is because it’s rare. My maiden name (first with last) provided a unique identifier on platforms such as LinkedIn, Facebook, Twitter and similar.

Common SQL Window Functions: Positional Functions

Positional SQL window functions deal with data’s location in the set. In this post, we explain LEAD, LAG, and other positional functions. SQL window functions allow us to aggregate data while still using individual row values. We’ve already dealt with ranking functions and the use of partitions. In this post, we’ll examine positional window functions, which are extremely helpful in reporting and summarizing data. Specifically, we’ll look at LAG, LEAD, FIRST_VALUE and LAST_VALUE.

Four Reasons Aspiring Data Scientists Must Learn SQL

Isn’t data science way too advanced for simple SQL? Nope! SQL can help you build a foundation for your data science career. Let’s see how. Data science is hot right now. What if you could predict the next market crash? Or contain the spread of Ebola? Or accurately predict a health crisis months or even years before it happens? Data scientists are working hard on these kinds of projects, and they are earning healthy salaries in the process.

Kill Online Distractions: 5 Tools to Help You Stay Focused

Phones, emails, social media accounts, and about a hundred other things are constantly competing for our attention. Can tech tools help us stay focused? A lot of apps say that they help humans work better and faster. But the truth is that the more interactive tools we use, the more focus-shattering notifications we get. In many ways, technology has gotten us into this problem. Can it help us get out of it?

Kill Online Distractions: 5 Tools to Help You Stay Focused

Phones, emails, social media accounts, and about a hundred other things are constantly competing for our attention. Can tech tools help us stay focused? A lot of apps say that they help humans work better and faster. But the truth is that the more interactive tools we use, the more focus-shattering notifications we get. In many ways, technology has gotten us into this problem. Can it help us get out of it?

SQL Mythbuster – 5 Reasons Why No One Should Be Afraid of SQL

Anyone can learn SQL. It’s not as hard as you think! In today’s world, even those in non-technical jobs need some technical skills. And you don’t have to be a hard-core nerd to get these skills. Let me tell you my own story. A few years ago, I wasn’t considering learning SQL or anything else that I labelled “technical”. My background is in sociology, journalism, and scriptwriting, and I thought computer languages were pretty sci-fi.