Basic Query Examples
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