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.

Referential Constraints and Foreign Keys in MySQL

Foreign keys and referential constraints allow you to set relationships between tables and modify some of the database engine’s actions. This beginner’s guide explains referential integrity and foreign key use in MySQL. One of the most important aspects of database usage is being able to trust the information you store. Database engines provide several features that help you maintain the quality of your data, like defining required columns as NOT NULL and setting an exact data type for each column.

An Illustrated Guide to the SQL OUTER JOIN

We’ve already discussed the SQL CROSS JOIN and INNER JOIN statements. It’s time to explore another: OUTER JOIN. What is it? How does it work? Let’s find out! If you’ve read our other posts, you know that you can link the data in two or more database tables using one of the many types of SQL join operator. Today, we’ll discuss the three kinds of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

Jane Solves Her Business Analysis Problem

Jane has a data problem and an overdue report. Will SQL save the day? Jane works as a business analyst for a large London bank. As she walks to the station on Monday morning, she’s thinking about last week’s unfinished work. She couldn’t get the data she needed from IT, so she could not deliver her report on a financial product’s non-performing assets. She absolutely must submit the report to her manager today; she’s already delayed it for more than a week.

Common SQL Window Functions: Using Partitions with Ranking Functions

You’ve started your mastery of SQL window functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use SQL partitions with ranking functions. Mastering SQL window (or analytical) functions is a bumpy road, but it helps to break the journey into logical stages that build on each other. In the previous Common SQL Functions article, you learned about the various rank functions, which are the most basic form of window functions.

Common SQL Window Functions: Using Partitions with Ranking Functions

You’ve started your mastery of SQL window functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use SQL partitions with ranking functions. Mastering SQL window (or analytical) functions is a bumpy road, but it helps to break the journey into logical stages that build on each other. In the previous Common SQL Functions article, you learned about the various rank functions, which are the most basic form of window functions.

SQL Window Functions by Example

Interested in how SQL window functions work? We use some simple examples to get you started. SQL window functions are a bit different; they compute their result based on a set of rowsrather than on a single row. In fact, the “window” in “window function” refers to that set of rows. Window functions are similar to aggregate functions, but there is one important difference. When we use aggregate functions with the GROUP BYclause, we “lose” the individual rows.

How Recursive Common Table Expressions Work

Recursive Common Table Expressions are immensely useful when you’re querying hierarchical data. Let’s explore what makes them work. Common Table Expressions (CTEs) are some of the most useful constructions in SQL. Their main purpose is improving query design, which makes queries easier to read. One of the reasons CTEs are so popular is that they let you divide longer queries into shorter subqueries. These are easier to read and edit.

Opening the Door to SQL Window Functions

When you want to analyze data in a database, you need SQL window functions. What are they? How do they work? Read on to find out. There are two ways people use databases. One way is for the creation, modification and deletion of data. The other is analyzing the data, which means getting answers to specific questions. The more precise the questions you ask, the more tools you’ll need to answer them efficiently.

High Performance Statistical Queries –Skewness and Kurtosis

In descriptive statistics, the first four population moments include center, spread, skewness, and kurtosis or peakedness of a distribution. In this article, I am explaining the third and fourth population moments, the skewness and the kurtosis, and how to calculate them. Mean uses the values on the first degree in the calculation; therefore, it is the first population moment. Standard deviation uses the squared values and is therefore the second population moment.

An Illustrated Guide to the SQL INNER JOIN

What is an SQL INNER JOIN, and how does it work? Let’s find out! In my last article, I discussed the CROSS JOIN operation in SQL. Today, we’ll look at INNER JOIN and how to use it. Is it the same as a JOIN? How many tables can you link with an INNER JOIN? These are all good questions. Let’s look at the answers! What is an INNER JOIN? INNER JOIN combines data from multiple tables by joining them based on a matching record.

Common SQL Window Functions: Ranking Functions

Want to learn how to use SQL window functions? Ranking functions are a good place to start! Learning about SQL window functions usually comes after you’ve built a foundation in the language, but these powerful functions take your skills up a level. As you master them, you’ll find better ways to solve query problems. When used for business intelligence applications, SQL queries combine data retrieval and advanced computations. These operations are more complex than those used in OLTP systems.

5 Documentaries for People Who Love the Internet

Here’s a shock: not everything in the movies is true. But these five documentaries offer interesting insights into the Internet and its people. Non-tech people tend to consider coding, hacking, and anything remotely dev-y as mysterious and magical processes. This myth is helped along by the movie industry, and there’s nothing wrong with that. Movie watchers generally like thrilling, action-packed stories, and coding can be anything but exciting. But not every good movie needs to be filled with action; some get our brains going by presenting new ideas or interesting viewpoints.