Teamsql 54 results

Hierarchical Queries in Oracle

HIERARCHICAL QUERIES IN ORACLE WITH TEAMSQL Standard SQL doesn’t support hierarchical or recursive queries. However, in the relational data model we usually create tables with an intrinsic recursive nature. Many database engines add proprietary extensions to SQL to support recursive queries. In this article, we will explain how to query these kinds of tables in Oracle using TeamSQL. There are some different clauses in Oracle to use in hierarchical or recursive queries. We will cover the ...

Oracle Pivoting Queries in TeamSQL

ORACLE PIVOTING QUERIES IN TEAMSQL One interesting feature in Oracle is the PIVOT operator, which is available from Oracle version 11g. This feature is frequently used in data warehouse environments to solve cross queries in few lines of SQL code. In this article, we will explain how to use the PIVOT clause in Oracle to create powerful cross queries using TeamSQL. First of all, what are cross queries or pivot tables ?. A pivot table is a table of statistics that summarize the data of a ...

The Difference Between SQL and PL/SQL

If you’re familiar with Oracle Databases, you’ll have seen mentions to SQL and PL/SQL, which are both programming languages used to interact with said databases. In this article, we will cover what these two programming languages are, as well as when you should use which. SQL 101 When it comes to databases, SQL is the industry-standard programming language developers use to: Create, modify, or delete database objects Query, insert, update, or delete data into database objects...

Execute as a script in TeamSQL

In TeamSQL, there are two types of executing queries: Execute and Execute as a script.  If you execute long queries using the Execute button, you have to wait until the finish of the query to continue working on other tasks. You might have difficulty if you often use long queries. There is a solution to this problem in TeamSQL: Execute as a script. Execute as a script To execute a query as a script, you can use the "Execute as a script" button which appears clicking on the down arrow at ...

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

TeamSQL Connect & Execute #1: Building Backend Architectures with AWS CloudFormation

https://www.meetup.com/teamsql/events/254266862/ The TeamSQL community is proud to start a new meetup series called TeamSQL Meetup to gather its users and people interested in database management and development. We'll learn and demonstrate new technologies and discuss how to use them with our businesses in every meetup. On the 2nd of October 2018, we will meet at Istanbul's favorite startup base Kolektif House Levent for our first meetup; "AWS Workshop - VPC, EC2, RDS & Cloudformat...

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

TeamSQL SDK and Extensions

TeamSQL supports extensions. You can easily build your extensions in TypeScript using TeamSQL Extensions SDK and teamsql-cli. TeamSQL Extension Store is also available in this version. You can publish your extensions in TeamSQL Extension Store and let other users install and use your extension.       Documentations, Samples and Tutorials SDK Documenation: https://developer.teamsql.io Export to Google (Sample Code): https://github.com/Te...

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

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

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

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

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

Custom Snippets

What are snippets? Snippets are templates that you can use to help you 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 likely to run a query that works for MySQL against SQL Server (or vice versa). If you can’t remember the specific syntax, Intellisense can help you ...

Sharing to Slack

You can 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.         Read full article and tutorial on TeamSQL Blog.

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.

Filtering Data Grid

After executing a query that returns a large results set, TeamSQL allows you to filter the returned data without running another query. This allows you to analyze your results set without any additional queries or strain on your server. Let’s look at an example. This is what our initial results set looks like after executing a query.   Notice the search bar and funnel icon located above each column. These allow you to filter on one or more columns. If you have search ...

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

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

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

TeamSQL Extensions: Beautify SQL

Have you ever wished there was a more straightforward way of writing well-formatted SQL in your code editors? Well, in TeamSQL, there is. We recently introduced support for editor extensions, which are extensions that modify the way the editor in TeamSQL works. One of the first extensions that we created with this framework is Beautify SQL, which does exactly what you think it does: you type in SQL, Beautify SQL cleans it up.   Demo Let’s say that you wrote the following SQL ...

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

How to Develop an Extension that Allows You to Open URLs Returned by Your Queries

In this tutorial, we will show you how to develop an extension that allows you to open a URL returned in your query results.   Prerequisites TeamSQL. You must also have a TeamSQL account that you use to log into your TeamSQL account — if you don’t already have one, you’ll be prompted to create an account the first time you launch TeamSQL Node.js. Node.js comes with [npm](https://docs.npmjs.com/getting-started/installing-node), but because the latter is updated more ...

How to Create an Extension: Open a Google Map

This tutorial will show you how to open a Google Map using a given set of coordinates. These coordinates come from the results set you to get after executing a query in TeamSQL.   Prerequisites Before you begin, you must install: TeamSQL: You must also have a TeamSQL account that you use to log into your TeamSQL account — if you don’t already have one, you’ll be prompted to create an account the first time you launch TeamSQL Node.js: Node.js comes with npm, but ...

How to Create an Extension: Signing S3 URLs

This tutorial will show you how to create an extension that allows you to created signed S3 URLs by providing your AWS Access and Secret Keys just once.   Prerequisites TeamSQL. You must also have a TeamSQL account that you use to log into your TeamSQL account — if you don’t already have one, you’ll be prompted to create an account the first time you launch TeamSQL Node.js: comes with [npm](https://docs.npmjs.com/getting-started/installing-node), but because the latter is ...

Intercom Extension for TeamSQL

TeamSQL’s new extensions allow you to add optional functionality to your TeamSQL instance. One of the first extensions we’re releasing is for Intercom Lookup, which will enable you to cross-reference data you’ve returned in TeamSQL and look up users in your Intercom account using their email addresses, user IDs, or Intercom IDs.   Installing Intercom Extension     Click on “Install” button and after the installation is finished, “Reload” button will ...

Filtering Your Data Grid

After executing a query that returns a massive results set, TeamSQL allows you to filter the returned data without running another query. This process will enable you to analyze your results set without any additional queries or strain on your server. Let’s look at an example. The screenshot below is what our initial results set looks like after executing a query. Notice the search bar and funnel icon located above each column. These allow you to filter on one or more columns. If you ...

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

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

Using TeamSQL Snippets with Redshift

If you’ve upgraded to the TeamSQL Pro Plan, you have access to the Snippets feature, which we think you’ll love. In this post, we’ll talk about what snippets are and the snippets available to those using TeamSQL with their Redshift databases. We’ll begin by covering three Redshift-specific snippets and end by including the usual CRUD commands at the end of the post.       What are Snippets? Snippets are templates that help you can use to build longer ...

Sharing Data Via Hyperlink

If you’ve ever wanted to share data with someone, but that person wasn’t a user of TeamSQL, you had to export the data and send it to that person. He or she would then have to import the data into the appropriate app to see your dataset. This process isn’t necessarily complicated, but wouldn’t it be nice if there was a more natural way to share data with someone? Try TeamSQL for free: https://teamsql.io/ As another feature facilitating secure collaboration, you can now share TeamSQL ...

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

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

Providing Feedback on TeamSQL

Thank you for taking part in our private beta and using TeamSQL. While we spend a lot of time testing to ensure that everything works as flawlessly as possible, we realize that your help in polishing our product is just as valuable. We use GitHub for issue tracking, so we welcome you to log any of the following to our GitHub repository: Bugs Usability issues Unintended features Feature requests       Not only will this making tracking input easier, ...

TeamSQL Quick Start for macOS

This guide is designed to get you up and running with TeamSQL as quickly as possible.     Installation Download TeamSQL. Drag and drop the TeamSQL icon onto the icon for your Applications folder.   3. Wait for the files to copy to your Applications folder. Launch TeamSQL 1. Once TeamSQL has been installed, you can launch it using its icon located in your Launchpad. 2. You will be asked to register to use TeamSQL.   If you ...