Basic Query Examples
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
This section explains how to run queries on SingleStore via a series of examples.
First, connect to SingleStore (see the Connect to SingleStore section for details on how to connect).
-- Create the databaseCREATE DATABASE memsql_example;USE memsql_example;-- Create 3 tables: departments, employees, and salariesCREATE 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 dataINSERT 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.
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)
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.
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.
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, hireDateFROM employeesWHERE 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 FROMemployees e, departments dWHERE e.deptId = d.idORDER 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 employeesgroup by stateORDER 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.salaryFROM employees e, salaries sWHERE e.id = s.employeeId ands.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 eJOIN salaries s on e.id = s.employeeIdGROUP BY e.stateORDER 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 nameFROM employeesWHERE 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 nameFROM employeesWHERE 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(*) countFROM employees mJOIN employees e ON m.id = e.managerIdGROUP BY m.idORDER 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) countFROM employees mLEFT JOIN employees e ON m.id = e.managerIdGROUP BY m.idORDER 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_nameFROM employees eLEFT JOIN employees m ON e.managerId = m.idORDER 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 mJOIN employees e ON m.id = e.managerIdJOIN salaries s ON s.employeeId = e.idGROUP BY m.idORDER 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_salaryFROM employees eJOIN salaries se ON e.id = se.employeeIdJOIN employees m ON m.id = e.managerIdJOIN salaries sm ON sm.employeeId = m.idJOIN departments d ON d.id = e.deptIdWHERE d.name = 'Finance'AND sm.salary < se.salaryORDER 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.
Last modified: September 12, 2024