SQL Tutorials 11 results

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 a 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...

Emulating UPSERT in PostgreSQL

Upsert is an interesting 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 in other database engines too.     The UPSERT statement The upsert statement is used 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 ...

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

Redis doesn’t require much in terms of 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, especially 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 tool set by adding another product (in this case, ...

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 ...

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 basic steps when removing duplicate data from your table: Back up your table! ...

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 ...

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 types 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 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. For ...

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 ...