SingleStore logo

  • Pricing

User Icon
LoginL
Start free
SingleStore logo
Notebook Icon

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In
SingleStore Spaces/Basic Query Examples
New

Basic Query Examples

Notebook

SingleStore Notebooks

Basic Query Examples

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
%%sql
2
CREATE DATABASE memsql_example;
3
USE memsql_example;

3. Create the tables

Create the tables named departments, employees, and salaries.

In [2]:

1
%%sql
2
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
%%sql
2
INSERT INTO departments (id, name) VALUES
3
  (1, 'Marketing'), (2, 'Finance'), (3, 'Sales'), (4, 'Customer Service');
4


5
INSERT INTO employees (id, deptId, managerId, name, hireDate, state) VALUES
6
  (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) VALUES
18
  (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
%%sql
2
SELECT COUNT(*) from employees;

The ORDER BY clause

List the ID and the name of each employee.

In [5]:

1
%%sql
2
SELECT id, name FROM employees ORDER BY id;

The WHERE clause

List the employees that work in Texas.

In [6]:

1
%%sql
2
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
%%sql
2
SELECT id, name FROM employees WHERE state = 'NY' ORDER BY id;

List the employees hired before 2002.

In [8]:

1
%%sql
2
SELECT id, name, hireDate
3
  FROM employees
4
  WHERE hireDate < '2002-01-01'
5
  ORDER BY id;

List employees and their departments.

In [9]:

1
%%sql
2
SELECT e.name, d.name department FROM
3
  employees e, departments d
4
  WHERE e.deptId = d.id
5
  ORDER BY name;

The COUNT function

List the number of employees in each state.

In [10]:

1
%%sql
2
SELECT state, COUNT(*)
3
  from employees
4
  group by state
5
  ORDER BY state;

The MAX function

Highest salary amongst all employees.

In [11]:

1
%%sql
2
SELECT MAX(salary) FROM salaries;

The Subqueries

Employee with the highest salary.

In [12]:

1
%%sql
2
SELECT e.name, s.salary
3
  FROM employees e, salaries s
4
  WHERE e.id = s.employeeId and
5
    s.salary = (SELECT MAX(salary) FROM salaries);

The AVG function

Average salary of employees in each state.

In [13]:

1
%%sql
2
SELECT e.state, AVG(salary)
3
  FROM employees e
4
  JOIN salaries s on e.id = s.employeeId
5
  GROUP BY e.state
6
  ORDER BY e.state;

The IN operator

List of managers.

In [14]:

1
%%sql
2
SELECT name
3
  FROM employees
4
  WHERE id IN (SELECT managerId FROM employees)
5
  ORDER BY name;

The NOT IN operator

List of non-managers.

In [15]:

1
%%sql
2
SELECT name
3
  FROM employees
4
  WHERE id NOT IN (SELECT managerId FROM employees)
5
  ORDER BY name;

The Joins

Number of employees reporting to each manager.

In [16]:

1
%%sql
2
SELECT m.name, COUNT(*) count
3
  FROM employees m
4
  JOIN employees e ON m.id = e.managerId
5
  GROUP BY m.id
6
  ORDER BY count DESC;

Number of employees reporting to each employee.

In [17]:

1
%%sql
2
SELECT m.name, COUNT(e.id) count
3
  FROM employees m
4
  LEFT JOIN employees e ON m.id = e.managerId
5
  GROUP BY m.id
6
  ORDER BY count desc;

Manager of each employee.

In [18]:

1
%%sql
2
SELECT e.name employee_name, m.name manager_name
3
  FROM employees e
4
  LEFT JOIN employees m ON e.managerId = m.id
5
  ORDER BY manager_name;

Total salary of the employees reporting to each manager.

In [19]:

1
%%sql
2
SELECT m.name, sum(salary)
3
  FROM employees m
4
  JOIN employees e ON m.id = e.managerId
5
  JOIN salaries s ON s.employeeId = e.id
6
  GROUP BY m.id
7
  ORDER BY SUM(salary) DESC;

Employees in the finance department earning more than their manager.

In [20]:

1
%%sql
2
%%sql
3
SELECT e.name employee_name, se.salary employee_salary, m.name manager_name, sm.salary manager_salary
4
FROM employees e
5
JOIN salaries se ON e.id = se.employeeId
6
JOIN employees m ON m.id = e.managerId
7
JOIN salaries sm ON sm.employeeId = m.id
8
JOIN departments d ON d.id = e.deptId
9
WHERE d.name = 'Finance'
10
  AND sm.salary < se.salary
11
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.

Notebook Icon

This Notebook can be run in Shared Tier, Standard and Enterprise deployments.

Tags

startersql

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.