Useful SQL Patterns: Matching Nulls by Masking Nulls

SQL patterns, matching NULLs, masking NULLs
As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. In database development, SQL developers often find themselves returning to the same SQL statements. Learning about these now, early in your SQL journey, will help you work more efficiently. Today, in the first post of this series, we will consider the match by null SQL pattern related to SQL data matching.

Using Transactions to Prevent Database Errors

What happens when a database receives commands from two different users? We look at the problems that can arise and how to avoid them. Every time you execute a statement in your database, you change the database’s state. When working in a multi-user environment with asynchronous database access, it will sometimes happen that two users are trying to change the same record at the same time. If both of their statements is an operation that changes the database’s state, this can create erroneous results.

How to Sort Records with the ORDER BY Clause

Relational databases don’t store records in alphabetical, numerical, ascending, or in any particular order. The only way to order records in the result set is to use the ORDER BY clause. You can use this clause to order rows by a column, a list of columns, or an expression. You can also order rows using the CASE expression. In this post, we’ll take a look at the ORDER BY clause – how to write it, how it works, and what it does.

Oracle Top-N Queries for Absolute Beginners

It’s common to run a query using only part of a dataset – for example, the top 100 salespeople in a company. In this article, we’ll see how to use Oracle’s Top-N query method to query just these rows. Top-N queries retrieve a defined number of rows (top or bottom) from a result set. In other words, they find the best or worst of something – the ten best selling cars in a certain region, the five most popular routers, the 20 worst-performing stores, etc.

SQL Statistical Analysis Part 2: Calculating Centers of Distribution

My previous article explained how to calculate frequencies using T-SQL queries. Frequencies are used to analyze the distribution of discrete variables. Today, we’ll continue learning about statistics and SQL. In particular, we’ll focus on calculating centers of distribution. We’ll learn e.g. how to calculate the SQL median, what functions to use to calculate the SQL mode, and how to calculate various types of mean in SQL (geometric mean, harmonic mean and, of course, arithmetic mean).

Using CASE to Add Logic to a SELECT

As you write an SQL query, you may need to get values from multiple columns and change values from one form to another. The simple way to achieve this goal is to add a CASE expression to your SELECT statement. In this article, we’ll introduce you to the syntax, formats, and uses of the CASE expression. The CASE expression is a conditional expression: it evaluates data and returns a result.

Book Review: “Learn SQL the Hard Way” by Zed A. Shaw

“Learn SQL the Hard Way” by Zed A. Shaw is a great ebook for those who want to learn SQL essentials. At $19.99 USD, it may seem a bit expensive for an ebook, but you’re getting a DRM-free PDF, plus a whole bunch of explanatory videos and additional files. Who should read “Learn SQL the Hard Way”? Anyone interested in SQL basics – modifying databases or selecting information. Make no mistake about it: this book focuses heavily on the practical side of database language.

NULL Values and the GROUP BY Clause

We’ve already covered how to use the GROUP BY clause and some aggregation functions like SUM(), AVG(), MAX(), MIN(), COUNT(). In this article, we will explain how the GROUP BY clause works when NULL values are involved. We will also explain about using NULLs with the ORDER BY clause. In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. For more details, check out Wikipedia’s explanation of NULL in SQL.

Vertabelo Academy Review: “SQL Cookbook” by Anthony Molinaro

Recipes that make querying easy, even for the novice. In today’s information age, we have at our fingertips almost an overabundance of teaching and information on any and every subject. In the midst of this glut of information, it can be hard to decide which is the best source for our needs. But, fear not; we are here to assist you as you navigate through decisions about books on SQL topics!

How to Use LIKE in SQL: SQL Pattern Matching

SQL pattern matching is a very important and useful ability. In this article, we look at how you can perform it using LIKE in SQL. SQL Pattern matching is a very simple concept. It allows you to search strings and substrings and find certain characters or groups of characters. Apart from SQL, this operation can be performed in many other programming languages. In this article, we’ll examine how you can use LIKE in SQL to search substrings.

Understanding Numerical Data Types in SQL

-- -- -- Working with databases of any kind means working with data. This data can take a couple of predefined formats. As you start on your learning path with LearnSQL.com, you will start to understand SQL’s different data types. In this article, we will cover different variations of the SQL numeric data type. We’ll also examine some functions that convert data from one type to another.

5 SQL Functions for Manipulating Strings

-- -- -- -- SQL functions used for manipulating strings, commonly called string functions, are among most important SQL’s tools. In this post, we’ll look at five ways you can perform various operations on strings. There are many SQL functions that let you “edit” string data. As you learn SQL, you’ll see how useful they prove. Some sql trim off unneeded spaces or characters; others tell you how long a string is.

Preventing Common SQL Mistakes

Regardless of the engine you are using (SQL Server, mySQL, Oracle, etc), you can prevent common errors and simplify the debugging process. This article will discuss some of the common SQL mistakes you’ll face and will help you correct them easily. One of the best ways to prevent the most common SQL mistakes is to keep your queries clear and readable. It’s very easy to forget the ideas behind your code!

Strategies for Online Learning Success

Internet-based learning is very popular. Knowing your learning style and motivations will help you study smarter, not harder. We tend to spend a lot of time online these days. Between watching funny YouTube videos, catching up on news and celebrity gossip, and binge-watching our favorite Netflix series, we even find the time to learn new skills. But are we in a constant learning process? How can we pluck out – from the vast resources of the Internet – the exact courses and methods that will work for us (or you) personally?

5 Books That Will Grow Your SQL Skills

Which books will help you build your database skills? We look at five awesome SQL books. There’s a common saying that “all wise men read books”. I would say that all professionals read books – particularly those related to their field. There are many books about databases and SQL; most are tied to specific vendors (DB2, Oracle, MS SQL) but there are also plenty that are vendor-neutral. In this article, I’m going to review a few books that I would recommend to SQL learners at various levels of proficiency.

How to Organize SQL Queries When They Get Long

The first long SQL query you’ll have to deal with is likely to be hard for you to structure and understand. These five tips will teach you the best way to organize SQL queries, i.e. write and format them. As we all know, SQL queries are essential to database management. Without them, it would be extremely difficult to find and work with the information in a database. Query length depends on the type of information we need and the size of the database.

SQL Statistical Analysis Part 1: Calculating Frequencies and Histograms

Database and Business Intelligence (BI) developers create huge numbers of reports on a daily basis, and data analyses are an integral part of them. If you wonder whether you can perform statistical analysis in SQL, the answer is ‘yes’. Read my article to learn how to do this! Statistics are very useful as an initial stage of a more in-depth analysis, i.e. for data overview and data quality assessment. However, SQL statistical analysis possibilities are somewhat limited as there are not many statistical functions in SQL Server.

Improving Query Readability with Common Table Expressions

What is a Common Table Expression, or CTE? Where do you use them, and why? This post answers your questions. Simply put, Common Table Expressions (also known as WITH clauses) are essentially named subqueries. They also provide additional features like recursion. If you’re new to subqueries, I recommend you read the SQL Subqueries article before continuing. The main purpose of Common Table Expressions is to improve the design and readability of an SQL statement.

Understanding the Use of NULL in SQL Three-Valued Logic

NULLs are necessary in relational databases, and learning to use them is fundamental to SQL success. However, NULLs should also be handled with care, as we explain in this post. In relational databases, we don’t always have a value to put in a column. For example, suppose we have a table called “persons” that has “first_name”, “last_name”, “birth_date” and “marriage_date” columns. What value will we store in the “marriage_date” column for single persons?

Complete SQL Practice for Interviews

Congratulations! Your SQL skills were strong enough to get you that job interview! Now, if you only knew what SQL questions and practical exercises a recruiter might ask you to do… This article is meant as a SQL practice for interviews. I’ll help you prepare for the SQL and database aspects of your job interview. In a previous article, I explained how can you boost your career by learning SQL.

OLTP vs. OLAP — What’s the Difference?

There are two major camps in the relational database development world: OLTP and OLAP. In this post, we consider the differences and similarities of these two systems. First of all, both OLTP (on-line transactional processing) and OLAP (on-line analytical processing) are used in business applications, especially — although not exclusively — in data warehousing and analytics. Together, they form the two different sides of the analytics/warehousing coin: storing and manipulating the data on one hand and analyzing it on the other.

SQL Development? Structured Query Language and Apps

You know about using SQL with databases. How does SQL fit in with app development? Is there anything like SQL development? In this post, we look at the app development process, how it intersects with modeling a database, and what developers can do with SQL outside of their app. Obviously, the beginning of this process is defining your app — what it does, who it is for, how it will function and look, etc.

The Complete Beginner’s Guide to SQL Fundamentals

Vertabelo Academy is a great place to learn SQL. If you’re a complete beginner, it’s best to have an overview of what SQL is, what a database is, and how they work together. In this article, you’ll find a complete guide to SQL fundamentals. SQL Fundamentals: Database Let’s begin our guide to SQL with basic definitions. You might have already heard that SQL is used with databases. What exactly is a database?

Finding the Perfect SQL Job

So you have some SQL skills and you’re looking for a job that will use them. What are your options? SQL is everywhere, and there’s a huge demand for people with database management skills. This is especially the case when companies start implementing Big Data solutions and strategies. There’s no arguing that SQL is a must-have skill. If you’re already proficient, how can you put your expertise to practical use in the job market?

A Day in the Life of a SQL Developer

What is a SQL developer? And what does a SQL developer do? Describing a “typical” day for a SQL developer is not easy. When your daily work is using various technologies to create interesting database-oriented products, very few days are alike! Each day brings a new and intriguing challenge. Nevertheless, I’ll have a go at explaining what everyday things a SQL developer might do. Morning: Arrive at Work What does a SQL developer do after arriving at the office?

Performing Calculations on Date- and Time-Related Values

Relational databases support several date and time data types. In this article, we’ll look at several arithmetic operations we can do on these types. These operations are logical and understandable, even for the beginning SQL coder. Let’s first briefly explain the main data types used for dates and times. Keep in mind that data types may differ by database engine, so check your database documentation for specifics before you start working with them.

Getting the Hang of the GROUP BY Clause

GROUP BY is an important part of the SQL SELECT statement. But new SQL coders can run into some problems when this clause is used incorrectly. Here’s how to avoid those issues. You’re learning SQL. You know how to SELECT some data FROM a table and how to filter the data with a WHERE clause. You can process the data using aggregate functions (MIN, MAX, SUM, AVG, and others). But when you’re dealing with a lot of data, you may need to narrow it down even more.

SQL and Your Next Job

The tech sector is experiencing strong job growth. Should SQL be on your list of must-have skills? If you’re even remotely interested in finding a new job, you’ve probably noticed that IT jobs are hot. Not only are a lot of these jobs available, they offer some pretty great opportunities to challenge yourself, grow, and build new skills. And the compensation tends to be pretty awesome too. In today’s world, technology is evolving at an almost daily pace, and this is producing a flood of data that has to be managed.

How to Recognize SQL Text Data Type

How can you store textual information in database tables? Thanks to this post, you’ll learn the characteristics of every text data type in SQL Note: This post is intended for readers familiar with SQL data definition language (DDL) and the DDL CREATE statement. To learn more about DDL, check out Vertabelo SQL Academy. Most data types are classified as NUMERIC, CHARACTER, or DATE. In this post, we’re going to focus on each CHARACTER or text data type in SQL.

Refine Results with SQL Set Operators

Using UNION, UNION ALL, EXCEPT, and INTERSECT to manage SQL query results. The function of SQL set operators is pretty simple; they allow us to combine results from different SQL queries into one result set. The best way to understand how set operators work is to use visual methods like the Venn diagram. For those of you not familiar with Venn diagrams, they are two circles that represent items or collections of items.

6 Common SQL Syntax Mistakes All Learners Make

We all make mistakes when learning a new language – especially at the beginning. New words, complicated grammar… Everyone needs time to master a language. But when we get immediate feedback, we can progress more quickly. The same goes for learning SQL. In this article, we’ll look at 6 common SQL syntax mistakes most SQL learners make. Mastering SQL Queries Interactive SQL courses are one way to get immediate feedback on your queries.

8 Tips Absolute Beginners Can Use to Fix SQL Queries

Code errors are common – and frustrating. And when you’re just learning SQL, it can be very challenging to find and fix your mistakes. In this post, we’ll show you eight ways to solve or eliminate common SQL coding errors. Today, we’ll talk about some tips the SQL beginner can use to avoid several common errors. These tips work in any database environment. As we go along, we’ll be showing some error messages.

How to Learn SQL: 6 Ideas for Newbies

Learning a new skill can be a daunting task, especially in programming. SQL is not immune to this. Luckily, with the Internet and the explosion of programming-related information out there, there are a lot of options you can use to undergo your SQL training! But we then come to a new roadblock. Where to begin? How will we know which option is best for our budget, skill level, and schedule?

Correlated Subquery in SQL: A Beginner’s Guide

Sometimes, using a SQL correlated subquery is the only way to solve a statement. But these subqueries can be very slow. In this post, we’ll talk about when to use a correlated subquery, why, and how to do it. Subqueries are an important resource for increasing the expressive power of SQL. If you haven’t read our previous article, subqueries are simply a SELECT statement inside another SELECT. We can use them in different places inside a SELECT, such as in the WHERE, HAVING, or FROM clauses.

Five Common SQL Syntax Errors

As you learn SQL, watch out for these common coding mistakes You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message. Don’t despair! Coding mistakes are common in any programming language, and SQL is no exception. In this post, we’ll discuss five common SQL syntax errors people make when writing code.

SQL Errors: Five Common SQL Mistakes

As you learn SQL, watch out for these common coding mistakes You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message. Don’t despair! Coding errors are common in any programming language, and SQL is no exception. In this post, we’ll discuss five common mistakes people make when writing SQL. Watch Your Language (and Syntax) The most common SQL error is a syntax error.

GROUP BY Clause: How Well Do You Know It?

The database can do different kinds of computations: it can add and subtract, multiply and divide, it can do computations on dates, it can count rows and sum row values, and so on. It can also do quite sophisticated statistics. The GROUP BY clause is the most basic way to compute statistics in SQL. It can be quite tough for beginners but it is really powerful. Let’s look at the details of the GROUP BY clause, starting with the basics.

Do it in SQL: Recursive SQL Tree Traversal

In the previous article, I described how to use Common Table Expressions to find the shortest path in a directed graph. That example could be hard to follow, I admit. Let’s do something much more common, something that is implemented on almost every website – a menu. Instead of writing the code, we’ll take advantage of the SQL tree structure writing just one query. We’ll use CTEs for PostgreSQL and the hierarchical query clause for Oracle.

SQL joins

A SQL JOIN is a method to retrieve data from two or more database tables. This article presents a basic overview of what data from a particular SQL join will look like. A popular way of understanding SQL joins is to visualize them using Venn diagrams, so each example have corresponding Venn diagram, appropriate SELECT statement and the result table. There are a few major kinds of SQL joins:

SQL Subqueries

The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in from and where clauses, and explore the difference between correlated and nested subqueries. First, let’s start with an example database. To present some of these statements we need to have an example table and fill it with some data.

How to Tackle SQL NULLs: The NULLIF Function

Usually we try to avoid NULLs, but sometimes we need to have a NULL instead of an actual value. When this need pops up, a helpful but little-known SQL function called NULLIF makes it possible. This post will introduce you to NULLIF and demonstrate its use in two example cases. In the article How to Tackle NULLS: The COALESCE Function , we got to know the COALESCE function. It returns the first not-null expression.

SQL JOINs for Beginners

You’re probably already familiar with simple SQL queries, such as “SELECT * FROM table”. Now you are wondering what to do when you have multiple tables, and you want to join them. Exactly! JOIN is the key. In this SQL JOINs tutorial for beginners, you will learn how to connect data from multiple tables. What are SQL JOINs? Databases usually have more than one table. JOINs are an SQL construction used to join data from two or more tables.

How to Tackle SQL NULLs: COALESCE function

It’s inevitable that some data in the database has no value what in SQL is represented by the NULL keyword. “No value” here is different from zero, false, or an empty string (but with exceptions! In Oracle database, NULL is the same as string of zero length). During playing with the database, treating NULLs are becoming more and more troublesome, therefore >MySQL standard provides help with some of the functions, like COALESCE.

Should I Learn SQL? 4 Convincing Reasons

There are 250+ computer programming languages in popular use. Why should you learn SQL, or Structured Query Language? Why Should You Learn SQL? Why learn SQL? To answer this question, you should start with reflecting on your professional life. Are you interested in boosting your career? Wondering what skills are worth investing in? If you’re currently in your dream job, still searching for it, or even thinking about starting your own business, it pays to develop your technical skills.

GET ACCESS TO EXPERT CONTENT

Over 85.000 happy students
and counting!