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

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

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

Sharing And Managing Shared Results

TeamSQL allows you to share the set of data returned after you execute a query via hyperlink. This means that you can share this data with somebody regardless of whether the person uses TeamSQL or not. The user to whom you’re sharing simply navigates to the URL you provide to see the data.   Share Results Before you can share your results, you’ll need to execute a query to return a set of data.   Once you’ve returned a set of results, you can right-click and ...

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

How To Share Folders

In this article, we will cover how you can share the folders you’ve created in TeamSQL. You can share them with individuals or with groups of individuals. To get started, navigate to the Saved Queries area of TeamSQL. You’ll see a list of all the queries you saved, as well as the folders you’ve created to store them.     Select the folder you want to share. You’ll see the options associated with that folder appear in the toolbar at the top. Click Share to ...

Getting Started with Charts in TeamSQL

Prerequisites Before you begin, please be sure to: Download and install TeamSQL Sign up for a TeamSQL account and use your credentials to log into your local instance of TeamSQL Connect to a database that contains the data you want to chart.   Creating a Chart The first thing you’ll need to do is open up a new query window.   Write your query and execute it so that you return the data you want to chart.     Notice that there’s ...

PostgreSQL Snippet List

Snippets are available with TeamSQL Pro Plan. First item is the label of snippet so you can start writing one of the labels in TeamSQL Query Editor and use them.       SELECT * FROM... Simple SELECT command SELECT * FROM with WHERE clause... Simple SELECT command with WHERE clause SELECT with INNER JOIN... SELECT Command with INNER JOIN INSERT INTO... Insert into a table on "public" INSERT INTO with SELECT... Insert into a table on "public" using ...

Microsoft SQL Server Snippets

Snippets are available with TeamSQL Pro Plan. First item is the label of snippet so you can start writing one of the labels in TeamSQL Query Editor and use them.       SELECT * FROM... Simple SELECT command SELECT * FROM with WHERE clause... Simple SELECT command with WHERE clause SELECT with INNER JOIN... SELECT Command with INNER JOIN INSERT INTO... Insert into a table on "dbo" INSERT INTO with SELECT... Insert into a table on "dbo" using SELECT...

Redshift Snippet List

Snippets are available with TeamSQL Pro Plan. First item is the label of snippet so you can start writing one of the labels in TeamSQL Query Editor and use them.       SELECT * FROM... Simple SELECT command SELECT * FROM with WHERE clause... Simple SELECT command with WHERE clause SELECT with INNER JOIN... SELECT Command with INNER JOIN INSERT INTO... Insert into a table on "public" INSERT INTO with SELECT... Insert into a table on "public" using ...

Exporting Data to CSV or JSON in TeamSQL

You can easily export the data contained in your database tables in one of the following formats: CSV JSON Tab Separated       Read full article and tutorial on TeamSQL Blog.

TeamSQL Application Preferences

TeamSQL ships with a standard set of settings, called preferences, that you can change as you'd like. To access and set your application preferences, go to File > Preferences.   There are two tabs, each of which contains a different set of settings: General Keyboard   General Settings The general tab includes the settings and preferences that affect how your editor window behaves.   In the General tab, you can set the following parameters:...

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

Is TeamSQL written in Java?

No! We are using Electron and everything is mostly written in HTML, Node.js and TypeScript under the hood.    

Is TeamSQL open source?

TeamSQL is not a public repository, yet. However TeamSQL is written in Javascript / HTML and built on Electron, so practically all our code is accessible through Chrome DevTools (we allow our users to use DevTools inside TeamSQL, so they always know what's going on under the hood). Our initial plan is to make important parts of TeamSQL open source and start to receive pull requests. We opened our SQL Statement Parser as the first step of this effort. (https://github.com/TeamSQ...

Can I download TeamSQL now?

Currently there are many people using TeamSQL in their routine development cycles and we want to make sure that our support system scales efficiently as the number of users grows. We also want to keep communicating with everyone to let the product fit to our user base's needs. So we are rolling out TeamSQL at a controlled pace and we are trying hard to make the process run faster.  

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  

Using Keyboard Shortcuts in TeamSQL (macOS)

Open search box — search for any item ⌘ + ⇧ + F Open search box — search for table names ⌘ + ⇧ + T Open search box — search for functions and stored procedures ⌘ + ⇧ + P Import SQL file ⌘ + O Execute query ⌘ + R Save query ⌘ + S Open tab by index ⌘ + [1–9] Example: ⌘ + 1 opens the first tab Open a new tab using the current connection ⌘ + N Open a new tab after prompting the user to select a connection ⌘ + ⇧ + N Close the ...

Using Keyboard Shortcuts in TeamSQL (Windows & Linux)

Open search box — search for any item Ctrl + ⇧ + F Open search box — search for table names Ctrl + ⇧ + T Open search box — search for functions and stored procedures Ctrl + ⇧ + P Import SQL file Ctrl + O Execute query Ctrl + R Save query Ctrl + S Open tab by index Ctrl + [1–9] Example: ⌘ + 1 opens the first tab Open a new tab using the current connection Ctrl + N Open a new tab after prompting the user to select a connection Ctrl + ⇧ + N Close ...

Connection Microsoft SQL Server over TCP/IP

You must enable TCP/IP connection of Microsoft SQL Server, so you can connect it to from TeamSQL. It's required. Please check this out to enable TCP/IP Connection of Microsoft SQL Server : https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx Checks these first: Start the "SQL SERVER BROWSER" Service in Windows services if it's not already started. See: https://technet.microsoft.com/en-us/library/ms165734(v=sql.90).aspx Host name cannot be "." (dot) SQL Server ...

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

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

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

Visualize your data with charts in TeamSQL

TeamSQL offers a new and redesigned feature to create your own charts based on query resulting data. The Charts feature of TeamSQL is now more powerful with advanced aggregation and visualization options. TeamSQL Charts is all free during Release Candidate period. Visualize your data by creating the charts you need, right in TeamSQL. You can easily create bar, area, line, pie, scatter, funnel, and bullet charts, as well as heat maps, using the data you return from your database. You ...

TeamSQL Extensions: Google Maps

One of the biggest perks to using TeamSQL is its extensibility. One size does not fit all, and what your colleague needs in their data client might not be what you need in yours. As such, TeamSQL will ship with a default set of features, and you can add as much as you’d like to create the client that works best for you! Today, we will take a look at one of the extensions currently available in the Extensions Store: Google Maps.   What does the Google Maps Extension do? So let’s ...

Sharing Your TeamSQL Folders

In our opinion, one of the most significant benefits to using TeamSQL is the ability to share your work with your teammates, either individually or with groups of individuals, with ease. Rather than going through the traditional practice of exporting your query, saving it somewhere, and then forwarding the access directions to your colleagues, you can let TeamSQL do the heavy lifting. Just provide information about who you’d like to share with and the recipients’ email addresses, and ...

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

TeamSQL Charts: Scatter Plots

Today, we’ll finish up our blog series on the types of charts you can create using TeamSQL’s new charting feature by covering the final chart type available: scatter plots. Let’s say that we need to look at the correlation between height (in centimeters) and weight (in kilograms). Our returned data looks like this:       As always, once we return data, we can launch the TeamSQL charts wizard by clicking on Charts. Select the Scatter option, set your dimen...

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

Custom Snippets

TeamSQL has supported snippets for some time, but we’re introducing custom snippets to help you work more effectively.   What are the snippets? From our snippets post: Snippets are templates that can help you to build longer queries. Because TeamSQL knows which database you’re working in, you’ll get the right snippet at the right time (if you work in multiple types of databases, you’ll know how easy it is to get similar-but-different syntaxes mixed up). Now, you’re less ...

Math Operations on Selected Cells

TeamSQL now comes with built-in, basic operations, so you have easy access to summary statistics for your data, such as totals, counts, averages, and maximum and minimum values. You can have these operations run on any set of cells you’ve highlighted. Here’s how you can get this information. Let’s say that we’ve executed a query and have the following sales information:       To get a quick overview of our data, we begin by selecting the data cells we want to ...

TeamSQL Charts: Using the Bar Chart Feature to Create Funnel Charts

In today’s post, we’ll revisit bar charts, which we covered a couple of days ago. However, we will modify our use of bar charts a bit so that we can create a funnel-like chart. If you haven’t already, please read our Charts 101 blog post before beginning this post. Let’s say that you have a sales progression, and you want a funnel chart that shows the change in engagement as you proceed along the sales pathway. Here’s what our data looks like:     When we ...

Working with Area Charts in TeamSQL

In this article, we’ll take a look at creating area charts in TeamSQL. If you haven’t already, please review our summary of working with the charts functionality. For our chart today, we’ll be looking at the number of weapons contained in the stockpiles of the USA and the USSR for the period beginning in 1950 and ending in 1960. Here’s what our data looks like in TeamSQL.   As always, once we have actionable data returned from our query, we can choose to graph our data ...

Creating Bar Charts in TeamSQL

We’ve already covered the basics on how to work with charts in TeamSQL, so today, we’ll go over how to create a specific type of charts in TeamSQL: bar charts. In today’s examples, we’ll be working with historical population data, but you can use whatever data you’d like. Here’s what our data looks like:   Let’s say that I want to compare the population level of each continent for the year 2012. I can launch the charts wizard by clicking on Charts, which is located ...

Table Structure Previews

One of TeamSQL’s newest features is the ability to preview the structure of a particular table. You’ll be able to view the table’s column definitions, as well as information about any indexes that might be on the table.     You can access this information in one of two ways: the first is to open up a pop-up window containing the table structure information, and the second is view it in the Connections Pane. Begin by hovering over the name of a table in the left-hand ...

TeamSQL Snippets

If you’ve upgraded to the TeamSQL Pro Plan, you have access to the Snippets feature, which is designed to help you do more with less typing. In this post, we’ll talk about what snippets are and what snippets available to those using TeamSQL with their MySQL, PostgreSQL, and SQL Servers.     What are Snippets? Snippets are templates that help you to build longer queries. Because TeamSQL knows which database you’re working in, you’ll get the right snippet at the ...

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

Working with Teams in TeamSQL

In this article, we will cover the features TeamSQL has that enables easy collaboration between you and your colleagues.   Creating a Team To create a new team, which you can use to group any number of users, navigate to Teams > Create New Team using the main toolbar at the top. You will be prompted to provide a name for your new team.   Click Create to proceed. On the Teams & Management window that displays, you see your newly-created team, along ...