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

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

Using TeamSQL with Redis

TeamSQL now supports Redis, a data structure store used as a database, cache, and message broker. Here’s how to connect to your Redis instance and what it’s like working with Redis in TeamSQL. Connecting TeamSQL to Your Redis Environment Launch TeamSQL and sign in to your TeamSQL account.       Click the plus sign next to Connections to bring up the Add Connection wizard.   Select Redis as your Database Type to get the ...

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

Connecting to Amazon Redshift with TeamSQL

This article covers how you can connect TeamSQL to your Amazon Redshift database servers, as well as how you can connect using SSL.     NOTE: You can connect to AWS Redshift with TeamSQL, a multi-platform DB client that works with Redshift, PostgreSQL, MySQL & Microsoft SQL Server and runs on Mac, Linux and Windows. You can download TeamSQL Beta for free: https://teamsql.io/   In the Explorer pane, click on the plus icon next to CONNECTIONS. &nb...

Adding a New MySQL Connection via SSH Tunnel

In the Explorer pane, click on the plus icon next to CONNECTIONS.     This launches the Add Connection window where you can create new connections from TeamSQL to your server(s).   To create a connection to a MySQL database set the Database Type to MySQL.   Note how the window now displays Create a new MySQL connection at the top. To connect to your MySQL database server, you’ll need to provide the following paramet...

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

MySQL JSON Support & Using JSON Fields

If you’re integrating TeamSQL with MySQL version 5.7.8 (or later), you can access data in JSON (JavaScript Object Notation) documents.   We’ll start by creating our table. We can easily create tables in TeamSQL by running a Create procedure using a query window. CREATE TABLE `sakila`.`json_examples`( `first_name` VARCHAR(50), `last_name` VARCHAR(50), `languages_spoken` JSON) ENGINE=`InnoDB` AUTO_INCREMENT=1;   We’ll create a simple three-column table that will store ...

Import Data into Redshift Using the COPY Command

Importing a large amount of data into Redshift is easy using the COPY command. To demonstrate this, we’ll import the publicly available dataset “Twitter Data for Sentiment Analysis” (see Sentiment140 for additional information).   NOTE: You can connect to AWS Redshift with TeamSQL, a multi-platform DB client that works with Redshift, PostgreSQL, MySQL & Microsoft SQL Server and runs on Mac, Linux and Windows. You can download TeamSQL for free: https://teamsql.io/  ...