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 911 call ...
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?
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 documents are very flexible ...
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 ...
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.
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
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 ...
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.
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’ll need to be able to grab data from multiple tables. One of the ways to do this is by using joins, and 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 join is a way of linking data from two or more tables based on a column shared between the tables. ...
Using the 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 ...