Databases 16 results

Avoiding Duplication Of Records

The issue of avoiding duplicate records in relational databases is one of the most common issues or problems. The primary mechanism to prevent that are the primary keys, however, in many databases or tables, primary keys are not defined. We are not going to discuss if not having a primary key is unacceptable or acceptable under the specific circumstance. We know that occurs, especially on data warehouses schemas. Then, in this environment where we don’t have primary keys or unique constraints ...

A Day At Races With TeamSQL 🏎

A DAY AT THE RACES WITH TEAMSQL In this article, we took a public database with real and historical data about formula one races located at http://ergast.com/mrd/db/, we imported into a PostgreSQL database engine, and then we start querying using TeamSQL trying to cover and use a broad spectrum type of business queries and at the same time using different SQL clauses. We expect you can find some of the exciting queries presented here usable and applicable perhaps to your business data. ...

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

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

Adding a new database connection

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 parameters to TeamSQL:   ...

Do you support Oracle?

We're planning to support Oracle, DB2, ElasticSearch, MongoDB, Couchbase, Redis and others. Oracle will be the first one we're going to add in upcoming releases.  

Which database engines will you support?

In addition to MySQL, Microsoft SQL Server, PostgreSQL and AWS Redshift, we will be supporting those engines in the following weeks: Oracle MariaDB Elastic (ElasticSearch) mongoDB redis Cassandra AWS Aurora SQLite  

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. App Simplification It might seem counterintuitive that you can simplify your toolset by adding another product (in this case, TeamSQL), but if TeamSQL works ...

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

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 parameters to TeamSQL:   Con...

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 – COPY Command

Importing a significant 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/ ...