Basic Query Examples

This section explains how to run queries on SingleStore via a series of examples. For the sake of simplicity, the sample data used in the examples below is very small (10 rows or less per table) compared to typical workloads. The queries in the examples below consist of several database operations like index scans, full-table scans, joins, and aggregations.

First, connect to SingleStore (see the Connect to SingleStore section for details on how to connect). Then run the following queries to set up the data. You can also try this notebook in the portal using SingleStore Spaces

SQL

-- Create the database
 
CREATE DATABASE memsql_example;
USE memsql_example;
 
-- Create 3 tables: departments, employees, and salaries
 
CREATE TABLE departments (
 id int,
 name varchar(255),
 PRIMARY KEY (id)
);
 
CREATE TABLE employees (
 id int,
 deptId int,
 managerId int,
 name varchar(255),
 hireDate date,
 state char(2),
 PRIMARY KEY (id)
);
 
CREATE TABLE salaries (
 employeeId int,
 salary int,
 PRIMARY KEY (employeeId)
);
 
-- Populate each table with data
 
INSERT INTO departments (id, name) VALUES
 (1, 'Marketing'), (2, 'Finance'), (3, 'Sales'), (4, 'Customer Service');
 
INSERT INTO employees (id, deptId, managerId, name, hireDate, state) VALUES
 (1, 2, NULL, "Karly Steele", "2011-08-25", "NY"),
 (2, 1, 1, "Rhona Nichols", "2008-09-11", "TX"),
 (3, 4, 2, "Hedda Kent", "2005-10-27", "TX"),
 (4, 2, 1, "Orli Strong", "2001-07-01", "NY"),
 (5, 1, 1, "Leonard Haynes", "2011-05-30", "MS"),
 (6, 1, 5, "Colette Payne", "2002-10-22", "MS"),
 (7, 3, 4, "Cooper Hatfield", "2010-08-19", "NY"),
 (8, 2, 4, "Timothy Battle", "2001-01-21", "NY"),
 (9, 3, 1, "Doris Munoz", "2008-10-22", "NY"),
 (10, 4, 2, "Alea Wiggins", "2007-08-21", "TX");
 
INSERT INTO salaries (employeeId, salary) VALUES
 (1, 885219), (2, 451519), (3, 288905), (4, 904312), (5, 919124),
 (6, 101538), (7, 355077), (8, 900436), (9, 41557), (10, 556263);

Now let’s run a simple query to ask how many rows are in the employees table.

SQL

SELECT COUNT(*) from employees;

+----------+ | COUNT(*) | +----------+ | 10 | +----------+ 1 row in set (0.61 sec)

Note that the first time you run the query, it will take a significant amount of time (about a second) - this is because it is compiling the query. The plan is then saved so that the next time you run the query, it will be much faster. See the Code Generation section for more details.

Here is another simple query that lists the ID and name of each employee:

SQL

SELECT id, name FROM employees ORDER BY id;

+----+-----------------+ | id | name | +----+-----------------+ | 1 | Karly Steele | | 2 | Rhona Nichols | | 3 | Hedda Kent | | 4 | Orli Strong | | 5 | Leonard Haynes | | 6 | Colette Payne | | 7 | Cooper Hatfield | | 8 | Timothy Battle | | 9 | Doris Munoz | | 10 | Alea Wiggins | +----+-----------------+ 10 rows in set (0.73 sec)

Note

Queries without an ORDER BY clause will not have a guaranteed result set order, even if there is a primary key on the table.

SQL’s WHERE clause can be used to filter results. Here is a query that lists only employees that work in Texas:

SQL

SELECT id, name FROM employees WHERE state = 'TX' ORDER BY id;

+----+---------------+ | id | name | +----+---------------+ | 2 | Rhona Nichols | | 3 | Hedda Kent | | 10 | Alea Wiggins | +----+---------------+ 3 rows in set (0.74 sec)

Note that if you change the parameters in the query -- e.g. changing TX to NY -- SingleStore can reuse the same query plan, so the query does not need to be recompiled:

SQL

SELECT id, name FROM employees WHERE state = 'NY' ORDER BY id;

+----+-----------------+ | id | name | +----+-----------------+ | 1 | Karly Steele | | 4 | Orli Strong | | 7 | Cooper Hatfield | | 8 | Timothy Battle | | 9 | Doris Munoz | +----+-----------------+ 5 rows in set (0.00 sec)

Below are several more queries demonstrating common SQL operations.

Employees hired before 2002:

SQL

SELECT id, name, hireDate
 FROM employees
 WHERE hireDate < '2002-01-01'
 ORDER BY id;

+----+----------------+------------+ | id | name | hireDate | +----+----------------+------------+ | 4 | Orli Strong | 2001-07-01 | | 8 | Timothy Battle | 2001-01-21 | +----+----------------+------------+ 2 rows in set (0.77 sec)

List employees and their departments:

SQL

SELECT e.name, d.name department FROM
 employees e, departments d
 WHERE e.deptId = d.id
 ORDER BY name;

+-----------------+------------------+ | name | department | +-----------------+------------------+ | Alea Wiggins | Customer Service | | Colette Payne | Marketing | | Cooper Hatfield | Sales | | Doris Munoz | Sales | | Hedda Kent | Customer Service | | Karly Steele | Finance | | Leonard Haynes | Marketing | | Orli Strong | Finance | | Rhona Nichols | Marketing | | Timothy Battle | Finance | +-----------------+------------------+ 10 rows in set (0.93 sec)

Number of employees in each state:

SQL

SELECT state, COUNT(*)
 from employees
 group by state
 ORDER BY state;

+-------+----------+ | state | COUNT(*) | +-------+----------+ | MS | 2 | | NY | 5 | | TX | 3 | +-------+----------+ 3 rows in set (0.82 sec)

Highest salary amongst all employees:

SQL

SELECT MAX(salary) FROM salaries;

+-------------+ | MAX(salary) | +-------------+ | 919124 | +-------------+ 1 row in set (0.58 sec)

Employee with the highest salary:

SQL

SELECT e.name, s.salary
 FROM employees e, salaries s
 WHERE e.id = s.employeeId and
 s.salary = (SELECT MAX(salary) FROM salaries);

+----------------+--------+ | name | salary | +----------------+--------+ | Leonard Haynes | 919124 | +----------------+--------+ 1 row in set (0.98 sec)

Average salary of employees in each state:

SQL

SELECT e.state, AVG(salary)
 FROM employees e
 JOIN salaries s on e.id = s.employeeId
 GROUP BY e.state
 ORDER BY e.state;

+-------+-------------+ | state | AVG(salary) | +-------+-------------+ | MS | 510331.0000 | | NY | 617320.2000 | | TX | 432229.0000 | +-------+-------------+ 3 rows in set (1.72 sec)

List of managers:

SQL

SELECT name
 FROM employees
 WHERE id IN (SELECT managerId FROM employees)
 ORDER BY name;

+----------------+ | name | +----------------+ | Karly Steele | | Leonard Haynes | | Orli Strong | | Rhona Nichols | +----------------+ 4 rows in set (1.40 sec)

List of non-managers:

SQL

SELECT name
 FROM employees
 WHERE id NOT IN (SELECT managerId FROM employees)
 ORDER BY name;

+-----------------+ | name | +-----------------+ | Alea Wiggins | | Colette Payne | | Cooper Hatfield | | Doris Munoz | | Hedda Kent | | Timothy Battle | +-----------------+ 6 rows in set (1.43 sec)

Number of employees reporting to each manager:

SQL

SELECT m.name, COUNT(*) count
 FROM employees m
 JOIN employees e ON m.id = e.managerId
 GROUP BY m.id
 ORDER BY count DESC;

+----------------+-------+ | name | count | +----------------+-------+ | Karly Steele | 4 | | Orli Strong | 2 | | Rhona Nichols | 2 | | Leonard Haynes | 1 | +----------------+-------+ 4 rows in set (0.95 sec)

Number of employees reporting to each employee:

SQL

SELECT m.name, COUNT(e.id) count
 FROM employees m
 LEFT JOIN employees e ON m.id = e.managerId
 GROUP BY m.id
 ORDER BY count desc;

+-----------------+-------+ | name | count | +-----------------+-------+ | Karly Steele | 4 | | Rhona Nichols | 2 | | Orli Strong | 2 | | Leonard Haynes | 1 | | Doris Munoz | 0 | | Alea Wiggins | 0 | | Cooper Hatfield | 0 | | Hedda Kent | 0 | | Timothy Battle | 0 | | Colette Payne | 0 | +-----------------+-------+ 10 rows in set (0.84 sec)

Manager of each employee:

SQL

SELECT e.name employee_name, m.name manager_name
 FROM employees e
 LEFT JOIN employees m ON e.managerId = m.id
 ORDER BY manager_name;

+-----------------+----------------+ | employee_name | manager_name | +-----------------+----------------+ | Karly Steele | NULL | | Doris Munoz | Karly Steele | | Rhona Nichols | Karly Steele | | Orli Strong | Karly Steele | | Leonard Haynes | Karly Steele | | Colette Payne | Leonard Haynes | | Timothy Battle | Orli Strong | | Cooper Hatfield | Orli Strong | | Alea Wiggins | Rhona Nichols | | Hedda Kent | Rhona Nichols | +-----------------+----------------+ 10 rows in set (1.04 sec)

Total salary of the employees reporting to each manager:

SQL

SELECT m.name, sum(salary)
 FROM employees m
 JOIN employees e ON m.id = e.managerId
 JOIN salaries s ON s.employeeId = e.id
 GROUP BY m.id
 ORDER BY SUM(salary) DESC;

+----------------+-------------+ | name | SUM(salary) | +----------------+-------------+ | Karly Steele | 2316512 | | Orli Strong | 1255513 | | Rhona Nichols | 845168 | | Leonard Haynes | 101538 | +----------------+-------------+ 4 rows in set (1.86 sec)

Employees in the finance department earning more than their manager:

SQL

SELECT e.name employee_name, se.salary employee_salary, m.name manager_name, sm.salary manager_salary
FROM employees e
JOIN salaries se ON e.id = se.employeeId
JOIN employees m ON m.id = e.managerId
JOIN salaries sm ON sm.employeeId = m.id
JOIN departments d ON d.id = e.deptId
WHERE d.name = 'Finance'
 AND sm.salary < se.salary
ORDER BY employee_salary, manager_salary;

+---------------+-----------------+--------------+----------------+ | employee_name | employee_salary | manager_name | manager_salary | +---------------+-----------------+--------------+----------------+ | Orli Strong | 904312 | Karly Steele | 885219 | +---------------+-----------------+--------------+----------------+ 1 row in set (1.46 sec)

For documentation on all the types of queries supported by SingleStore, see SQL Reference.

Basic Query Examples

Was this article helpful?

Was this article helpful?