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

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

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

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

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

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

DATE / TIME ARITHMETIC WITH TEAMSQL

In this article, we will cover some interesting operations involving date and time data values in SQL. The SQL standard specifies the following date and time-related data types that must be present in relational databases: date, time, timestamp and interval. -  date: Represents a single day. Example 15/12/2018 -  time: Represents the time with hour, minutes, seconds. Example 09:14:30 -  timestamp: Represents a point in the timeline. Example 2018-12-15 09:14:30 -  interval: Repres...

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

Keep your data safe with QuerySense

I will start this article with a real-life story: As a database consultant, I do a lot of different works. I write technical articles,  do some other database tasks like SQL development and database administration. So, I have a lot of SQL clients, and tools installed on my computer. One sunny day I begin to work in the morning. While I am enjoying my coffee I opened my SQL client, and I run a simple script to create and populate a table for an article. Before I create a table, I try a “drop ...

Analyze A/B Tests in TeamSQL

In these days A/B Tests are a standard tool used by owners of e-commerce websites. In a few words we can say that A/B tests are like the speedometer of a race car, they serve to measure the most critical metric of an e-commerce site: The conversion rate. In this article, we will explain how to create a chart in TeamSQL to see, compare and evaluate how an e-commerce website is performing.   First step: To have a metric One of the most critical metrics in these days to measure the ...

Charting Your Data with TeamSQL

TeamSQL’s charts feature lets you visualize your database queries in just a few clicks. You can select from a variety of different chart types, perform complex data aggregation and sort, and customize the appearance of your charts right from TeamSQL. Charts work with any database supported by TeamSQL and can even be exported as images. In this post, we’ll demonstrate TeamSQL Charts using this sample MySQL database, which contains employee and salary records for a fictional company. ...

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

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

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

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

TeamSQL Charts: Pie Charts

We’ve been covering some of the new charting features in TeamSQL, and today, we’ll take a look at the pie chart. If you’re already familiar with how the basic charting functionality works in TeamSQL, read on, but if you aren’t, please take a look at our overview post. In today’s example, we’ll be looking at the market share each of the major browsers held in the period from January to May of 2015. Here’s what our data looks like returned from the database.   We click ...

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

Creating Line Charts in TeamSQL

Today, we continue our series on creating charts in TeamSQL. If you haven’t already, please see the summary of the charts features before reading this article. The chart we’ll be looking at today is a line chart. Let’s say that we want to track employee growth over the past couple of years and that our data looks like this: Once I have actionable data, I can click Charts to launch the chart creation wizard. I select the Line chart option and indicate that I want year on the ...

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

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

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

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

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

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

Which One to Choose: Cast vs. Convert

When working with Microsoft’s SQL Server (and by extension, T-SQL), you have two options when making explicit conversions: CAST and CONVERT. Many people use them frequently, and quite often interchangeably, but there are some cases when one is a better choice than the other. This post covers the similarities and the differences between the two, as well as when you’re better off using one over the other. Similarities When doing explicit data conversions, CAST and CONVERT are available ...

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

The Seven Types of SQL Joins

It’s unlikely that you’ll be working exclusively with data from one table. So, you need to be able to grab data from multiple tables. One of the ways to do this is by using joins. In relational databases, joins are what make relational databases relational. This post provides a quick refresher of the four main types of SQL Joins and three of its variants. What is a join? A SQL join is a Structured Query Language (SQL) way of linking data from two or more tables based on a column shared ...

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

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

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

Saving and Managing Queries in TeamSQL

You can easily save the queries you execute in TeamSQL for future reference.     You can initiate a save by clicking Save Query, using CTRL + S (in Linux or Windows), or using ⌘ + S (in macOS). You will be prompted to provide a name for your query if this is the first time you’ve saved.   TeamSQL now displays the name of your query in the query’s tab, as well as in the left-hand navigation bar.   Viewing Your Saved Queries In addition to accessing ...

Exporting Data Using TeamSQL

    You can easily export the data contained in your database tables in one of the following formats: CSV JSON Tab Separated 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/   How to Export Data 1. Launch TeamSQL, and connect to your database server by ...