SQL Tutorials 21 results

Subquery Operators

In SQL we can use a subquery in several parts of the query. We can use a subquery in the WHERE clause (which is the natural place for the subquery), in the FROM clause, in the SELECT list, or even in the HAVING clause. Moreover, we can use subqueries in the UPDATE, DELETE and INSERT statements. However, in this article, we will focus on subqueries used in the WHERE clause and especially on the operator used to compare the results of the subquery.   THE EASY EXAMPLE The first example ...

Correlated Subqueries With TeamSQL

Correlated subqueries are a different type of subqueries. In some situations, you must use a correlated subquery to obtain the correct reply or result data set. In this article, we will cover how to identify or detect these situations in which you must use a correlated subquery. We also show some examples of correlated subqueries, and we will even talk about how to avoid overusing correlated subqueries.   IN WHAT SITUATIONS NEED CORRELATED SUBQUERIES? There are some data questions ...

Relational Algebra and TeamSQL – Part II

Sometimes is good to dive into the foundations of something, to find new elements to add more power to our current skills. This article is the second part of the previous week Relational Algebra article. In Relational Algebra, there are several data operations, and almost all of them have a simple SQL statement representing the same process. However, there is one Algebra Relational operator that is not simple to express in SQL: The division operator. In this article, we will cover the division ...

Relational Algebra and TeamSQL

In this article about Algebra Relational, we will cover a very rare in SQL operation called division. This operator has not an equivalent implementation in SQL. However, there is a technique to implement division in SQL, and we will explain here.   Relational Algebra: Division, what is it? As any other algebra, relational algebra has operations (like addition subtraction multiplication division in numeric algebra). The fundamental operations are Selection and Projection. We will base ...

Recursive Queries with TeamSQL

In many relational database models, we have recursive data structures; it’s true that the relational data model is not oriented to support recursive data structures, and is also true that SQL, (as a query language) doesn’t have a healthy way to manage this recursive structures. However, every database engine has a particular clause to maintain the recursive data structures. In this article, we will see how to create recursive queries in PostgreSQL using TeamSQL.   A Recursive ...

Slow Query Improvement

HOW A GOOD INDEX STRATEGY CAN HELP POLICE SUCCESS As SQL developers, we are usually focused on creating queries which return the correct results. This is a good principle. However, there are more things to take into consideration when you create a query. One of them is the query performance. You think that query response time is not essential.? In this article, we will cover one example where query response time is critical.   SCENE ONE: 911 CALL CENTER Let’s suppose we are at the ...

What Is JSON?

JSON is a data interchange format that is designed to be lightweight and easy to work with. It’s quite popular in web applications, and it can be considerably more flexible than a traditional relational data model. Several database engines support JSON, so you can store JSON data and use functions to operate on it. This includes decomposing, transforming, or even creating JSON data from regular relational data.   What Is JSON? JSON is an acronym for JavaScript Object Notation. It ...

Tweens: The Power Of Set Operations In SQL

In relational databases, it is frequent to have many to many relationships. We can have a customer-product relation, a student-course relation, physician-patient relation among many other forms. In this article, we are going to solve a complex problem, a query which is complicated to write for many developers. In these days all of us know what a streaming video platform is. Moreover, perhaps many of us have a monthly bill from one of the biggest streaming platforms: Netflix. The problem we ...

XPATH Language Usage With An Example In PostgreSQL

In these days XML documents are everywhere. They are mainly used to exchange information between applications; however, they are also used for many other purposes. Moreover, almost all database engines supported by TeamSQL include XML as a data type. In this article we use TeamSQL to store XML documents on a database table, then we extract elements from the XML by using XPath expressions, and finally, we execute queries inside the XML document.   XML Example XML documents are very ...

ALL YOU WANT TO KNOW ABOUT VIEWS

WHAT IS A VIEW? In a short sentence, a view is a “saved query” associated with a name. Once you create the view (or save the query), you can use it as a table in other queries. Trying to explain with more detail what a view is, we can say that a view is another type of database objects like tables, indexes or stored procedures. From the point of view of a developer who is trying to write a SELECT query, a view is like any other regular table. The view can be used in the FROM clause as any ...

DATE / TIME ARITHMETIC WITH TEAMSQL

In this article, we will cover some interesting operations involving date and time data values in SQL. The SQL standard specifies the following date and time-related data types that must be present in relational databases: date, time, timestamp and interval. -  date: Represents a single day. Example 15/12/2018 -  time: Represents the time with hour, minutes, seconds. Example 09:14:30 -  timestamp: Represents a point in the timeline. Example 2018-12-15 09:14:30 -  interval: Repres...

SQL COALESCE Function

The first time I saw the term COALESCE in a SQL manual, I thought it was a scientific function for some complex calculation. However, later I found that it is a straightforward and handy function. In this article, I will show several examples to explain how to use the COALESCE function.   THE REASON BEHIND COALESCE: THE NULL VALUE Before go deep with COALESCE function, the reader needs to understand how the NULL value works in some expressions. A NULL value in a table column means ...

Emulating UPSERT in PostgreSQL

Upsert is an exciting and powerful SQL statement not supported on all database managers. In this article, we will cover a technique to emulate the upsert statement with other regular SQL statements in PostgreSQL. You can apply this technique to other database engines too. The UPSERT statement We use "UPSERT" statement when we have two tables. One is the destination table, where we will do updates or inserts (hence the name UPSERT), and the other table is the source table, where the informat...

Why You Should Use TeamSQL Instead of the Command Line for Redis

Redis doesn’t require much regarding software, and in fact, you probably went straight into working with Redis using the command-line interface (CLI). While there’s no arguing with simplicity, primarily if it works, here’s why you might consider using TeamSQL to manage your Redis environments instead of relying on CLI-based tools. App Simplification It might seem counterintuitive that you can simplify your toolset by adding another product (in this case, TeamSQL), but if TeamSQL works ...

Sharing Your Query Results to Slack

TeamSQL is always looking for ways to make collaborating with your colleagues on SQL queries easier, and today, you have one more way to share the results of your SQL query: via Slack! You can now run a query and send the results to the Slack channel, group, or user of your choice. Your colleagues can then access the data by clicking on the hyperlink provided by TeamSQL. To keep your data safe, you can choose to encrypt and password-protect your results set. How to Share Your Query Results ...

The GROUP BY Clause

The GROUP BY statement is often used with aggregate functions (such as COUNT, MAX, and SUM) so that you can group a set of rows by one or more columns. Here’s a high-level overview of how you can use it, as well as information on how it works and some tips to keep in mind. As an example, here’s a table with some pets data.     We can count the number of each species present in our table using the GROUP BY clause.   How the GROUP BY Clause Works &nb...

Deleting Duplicate Data

In most cases, you don’t want duplicate rows in your database. The best practice is to set up your insert process so that data that is already in your tables aren’t added again, but what if that horse has left that barn and you already have duplicates?     In this post, we’ll show you how you remove duplicate data in your MySQL databases.   At a Glance There are just a few necessary steps when removing duplicate data from your table: Back up your ...

Which One to Choose: Cast vs. Convert

When working with Microsoft’s SQL Server (and by extension, T-SQL), you have two options when making explicit conversions: CAST and CONVERT. Many people use them frequently, and quite often interchangeably, but there are some cases when one is a better choice than the other. This post covers the similarities and the differences between the two, as well as when you’re better off using one over the other. Similarities When doing explicit data conversions, CAST and CONVERT are available ...

SQL Injection 101

  SQL injection attacks occur when someone executes malicious SQL statements (also called payloads) that control a database server, typically in a manner not favorable to you. Any SQL-based database is vulnerable to these types of attacks, and it is one of the oldest, most common, and most dangerous forms of attack that someone can direct toward your database. All it takes is one failure for an entire database to be compromised. In some cases, the attacker can even run commands ...

The Seven Types of SQL Joins

It’s unlikely that you’ll be working exclusively with data from one table. So, you need to be able to grab data from multiple tables. One of the ways to do this is by using joins. In relational databases, joins are what make relational databases relational. This post provides a quick refresher of the four main types of SQL Joins and three of its variants. What is a join? A SQL join is a Structured Query Language (SQL) way of linking data from two or more tables based on a column shared ...

CREATE Procedure and INSERT INTO Statement Templates

TeamSQL comes with built-in templates to guide you as you create new procedures or statements that change your databases. In this article, we’ll cover using the CREATE Procedure and INSERT INTO Statement Templates.   Procedure Template: CREATE In a new TeamSQL query tab, begin typing CREATE. Notice that, as you type, the drop-down menu of options filters. When you see CREATE PROCEDURE highlighted, you can hit the ENTER key to populate the window with a CREATE stored procedure ...