This section explains how to run queries on SingleStore DB 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 memsql (see the MySQL command-line client sub-section for details on how to connect). Then run the following queries to set up the data:
-- 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.
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:
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)
SQL’s where clause can be used to filter results. Here is a query that only lists employees that work in Texas:
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 - we are able to reuse the same query plan, so the query does not need to be recompiled:
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:
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:
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:
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:
select max(salary) from salaries;
+-------------+
| max(salary) |
+-------------+
| 919124 |
+-------------+
1 row in set (0.58 sec)
Employee with the highest salary:
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:
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:
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:
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:
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:
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:
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:
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:
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 DB, see the SQL Reference Overview section.
