Database Development 23 results

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

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

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

MySQL Snippet List

    Snippets are available with TeamSQL Pro Plan. 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 "current database" INSERT INTO with SELECT... Insert into a table on "current database" using SELECT DELETE FROM... Delete rows from a table on "current database" UPDATE with WHERE clause... Up...

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.

Basic Math Operations

TeamSQL comes with built-in, basic math 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. To get a quick overview of our data, we begin by selecting the data cells we want included in our calculations.   As soon as we do this, we can see the results of our math operations at the bottom right of the TeamSQL window...

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

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  

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

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