
Basic Query Examples
Notebook

Note
This notebook can be run on a Free Starter Workspace. To create a Free Starter Workspace navigate to Start using the left nav. You can also use your existing Standard or Premium workspace with this Notebook.
![]() | ![]() |
This notebook demonstrates how to run queries on SingleStore through a series of examples. For simplicity, the sample data in these examples is limited to 10 rows or less per table, which is much smaller than typical workloads. The examples cover various database operations, including index scans, full-table scans, joins, and aggregations.
1. Create a Workspace
To create a workspace, refer to the Creating and Using Workspaces.
2. Create the Database
Select the workspace in your notebook, and create a database.
In [1]:
1
%%sql2
CREATE DATABASE memsql_example;3
USE memsql_example;
3. Create the tables
Create the tables named departments, employees, and salaries.
In [2]:
1
%%sql2
CREATE TABLE departments /* Creating table for sample data. */(3
id int,4
name varchar(255),5
PRIMARY KEY (id)6
);7
8
CREATE TABLE employees /* Creating table for sample data. */(9
id int,10
deptId int,11
managerId int,12
name varchar(255),13
hireDate date,14
state char(2),15
PRIMARY KEY (id)16
);17
18
CREATE TABLE salaries /* Creating table for sample data. */(19
employeeId int,20
salary int,21
PRIMARY KEY (employeeId)22
);
4. Populate the tables
Insert the data into the tables.
In [3]:
1
%%sql2
INSERT INTO departments (id, name) VALUES3
(1, 'Marketing'), (2, 'Finance'), (3, 'Sales'), (4, 'Customer Service');4
5
INSERT INTO employees (id, deptId, managerId, name, hireDate, state) VALUES6
(1, 2, NULL, "Karly Steele", "2011-08-25", "NY"),7
(2, 1, 1, "Rhona Nichols", "2008-09-11", "TX"),8
(3, 4, 2, "Hedda Kent", "2005-10-27", "TX"),9
(4, 2, 1, "Orli Strong", "2001-07-01", "NY"),10
(5, 1, 1, "Leonard Haynes", "2011-05-30", "MS"),11
(6, 1, 5, "Colette Payne", "2002-10-22", "MS"),12
(7, 3, 4, "Cooper Hatfield", "2010-08-19", "NY"),13
(8, 2, 4, "Timothy Battle", "2001-01-21", "NY"),14
(9, 3, 1, "Doris Munoz", "2008-10-22", "NY"),15
(10, 4, 2, "Alea Wiggins", "2007-08-21", "TX");16
17
INSERT INTO salaries (employeeId, salary) VALUES18
(1, 885219), (2, 451519), (3, 288905), (4, 904312), (5, 919124),19
(6, 101538), (7, 355077), (8, 900436), (9, 41557), (10, 556263);
5. Let's Query!
The SELECT statement
Ask how many rows are in the employees table.
In [4]:
1
%%sql2
SELECT COUNT(*) from employees;
The ORDER BY clause
List the ID and the name of each employee.
In [5]:
1
%%sql2
SELECT id, name FROM employees ORDER BY id;
The WHERE clause
List the employees that work in Texas.
In [6]:
1
%%sql2
SELECT id, name FROM employees WHERE state = 'TX' ORDER BY id;
You can use the same above query by replacing state = 'NY' to list the employees that work in New York.
In [7]:
1
%%sql2
SELECT id, name FROM employees WHERE state = 'NY' ORDER BY id;
List the employees hired before 2002.
In [8]:
1
%%sql2
SELECT id, name, hireDate3
FROM employees4
WHERE hireDate < '2002-01-01'5
ORDER BY id;
List employees and their departments.
In [9]:
1
%%sql2
SELECT e.name, d.name department FROM3
employees e, departments d4
WHERE e.deptId = d.id5
ORDER BY name;
The COUNT function
List the number of employees in each state.
In [10]:
1
%%sql2
SELECT state, COUNT(*)3
from employees4
group by state5
ORDER BY state;
The MAX function
Highest salary amongst all employees.
In [11]:
1
%%sql2
SELECT MAX(salary) FROM salaries;
The Subqueries
Employee with the highest salary.
In [12]:
1
%%sql2
SELECT e.name, s.salary3
FROM employees e, salaries s4
WHERE e.id = s.employeeId and5
s.salary = (SELECT MAX(salary) FROM salaries);
The AVG function
Average salary of employees in each state.
In [13]:
1
%%sql2
SELECT e.state, AVG(salary)3
FROM employees e4
JOIN salaries s on e.id = s.employeeId5
GROUP BY e.state6
ORDER BY e.state;
The IN operator
List of managers.
In [14]:
1
%%sql2
SELECT name3
FROM employees4
WHERE id IN (SELECT managerId FROM employees)5
ORDER BY name;
The NOT IN operator
List of non-managers.
In [15]:
1
%%sql2
SELECT name3
FROM employees4
WHERE id NOT IN (SELECT managerId FROM employees)5
ORDER BY name;
The Joins
Number of employees reporting to each manager.
In [16]:
1
%%sql2
SELECT m.name, COUNT(*) count3
FROM employees m4
JOIN employees e ON m.id = e.managerId5
GROUP BY m.id6
ORDER BY count DESC;
Number of employees reporting to each employee.
In [17]:
1
%%sql2
SELECT m.name, COUNT(e.id) count3
FROM employees m4
LEFT JOIN employees e ON m.id = e.managerId5
GROUP BY m.id6
ORDER BY count desc;
Manager of each employee.
In [18]:
1
%%sql2
SELECT e.name employee_name, m.name manager_name3
FROM employees e4
LEFT JOIN employees m ON e.managerId = m.id5
ORDER BY manager_name;
Total salary of the employees reporting to each manager.
In [19]:
1
%%sql2
SELECT m.name, sum(salary)3
FROM employees m4
JOIN employees e ON m.id = e.managerId5
JOIN salaries s ON s.employeeId = e.id6
GROUP BY m.id7
ORDER BY SUM(salary) DESC;
Employees in the finance department earning more than their manager.
In [20]:
1
%%sql2
%%sql3
SELECT e.name employee_name, se.salary employee_salary, m.name manager_name, sm.salary manager_salary4
FROM employees e5
JOIN salaries se ON e.id = se.employeeId6
JOIN employees m ON m.id = e.managerId7
JOIN salaries sm ON sm.employeeId = m.id8
JOIN departments d ON d.id = e.deptId9
WHERE d.name = 'Finance'10
AND sm.salary < se.salary11
ORDER BY employee_salary, manager_salary;
Learn more about SingleStore notebooks here.

Details
About this Template
Examples of basic SQL queries covering various database operations include index scans, full-table scans, joins, and aggregations.
This Notebook can be run in Shared Tier, Standard and Enterprise deployments.
Tags
License
This Notebook has been released under the Apache 2.0 open source license.
See Notebook in action
Launch this notebook in SingleStore and start executing queries instantly.