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]:

%%sql
CREATE DATABASE memsql_example;
USE memsql_example;

Out [1]:

3. Create the tables

Create the tables named departments, employees, and salaries.

In [2]:

%%sql
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)
);

Out [2]:

4. Populate the tables

Insert the data into the tables.

In [3]:

%%sql
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);

Out [3]:

5. Let's Query!

The SELECT statement

Ask how many rows are in the employees table.

In [4]:

%%sql
SELECT COUNT(*) from employees;

Out [4]:

COUNT(*)
10

The ORDER BY clause

List the ID and the name of each employee.

In [5]:

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

Out [5]:

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

The WHERE clause

List the employees that work in Texas.

In [6]:

%%sql
SELECT id, name FROM employees WHERE state = 'TX' ORDER BY id;

Out [6]:

id	name
2	Rhona Nichols
3	Hedda Kent
10	Alea Wiggins

You can use the same above query by replacing state = 'NY' to list the employees that work in New York.

In [7]:

%%sql
SELECT id, name FROM employees WHERE state = 'NY' ORDER BY id;

Out [7]:

id	name
1	Karly Steele
4	Orli Strong
7	Cooper Hatfield
8	Timothy Battle
9	Doris Munoz

List the employees hired before 2002.

In [8]:

%%sql
SELECT id, name, hireDate
  FROM employees
  WHERE hireDate < '2002-01-01'
  ORDER BY id;

Out [8]:

id	name	hireDate
4	Orli Strong	2001-07-01
8	Timothy Battle	2001-01-21

List employees and their departments.

In [9]:

%%sql
SELECT e.name, d.name department FROM
  employees e, departments d
  WHERE e.deptId = d.id
  ORDER BY name;

Out [9]:

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

The COUNT function

List the number of employees in each state.

In [10]:

%%sql
SELECT state, COUNT(*)
  from employees
  group by state
  ORDER BY state;

Out [10]:

state	COUNT(*)
MS	2
NY	5
TX	3

The MAX function

Highest salary amongst all employees.

In [11]:

%%sql
SELECT MAX(salary) FROM salaries;

Out [11]:

MAX(salary)
919124

The Subqueries

Employee with the highest salary.

In [12]:

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

Out [12]:

name	salary
Leonard Haynes	919124

The AVG function

Average salary of employees in each state.

In [13]:

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

Out [13]:

state	AVG(salary)
MS	510331.0000
NY	617320.2000
TX	432229.0000

The IN operator

List of managers.

In [14]:

%%sql
SELECT name
  FROM employees
  WHERE id IN (SELECT managerId FROM employees)
  ORDER BY name;

Out [14]:

name
Karly Steele
Leonard Haynes
Orli Strong
Rhona Nichols

The NOT IN operator

List of non-managers.

In [15]:

%%sql
SELECT name
  FROM employees
  WHERE id NOT IN (SELECT managerId FROM employees)
  ORDER BY name;

Out [15]:

name
Alea Wiggins
Colette Payne
Cooper Hatfield
Doris Munoz
Hedda Kent
Timothy Battle

The Joins

Number of employees reporting to each manager.

In [16]:

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

Out [16]:

name	count
Karly Steele	4
Rhona Nichols	2
Orli Strong	2
Leonard Haynes	1

Number of employees reporting to each employee.

In [17]:

%%sql
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;

Out [17]:

name	count
Karly Steele	4
Rhona Nichols	2
Orli Strong	2
Leonard Haynes	1
Alea Wiggins	0
Timothy Battle	0
Cooper Hatfield	0
Doris Munoz	0
Hedda Kent	0
Colette Payne	0

Manager of each employee.

In [18]:

%%sql
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;

Out [18]:

employee_name	manager_name
Karly Steele	None
Rhona Nichols	Karly Steele
Orli Strong	Karly Steele
Leonard Haynes	Karly Steele
Doris Munoz	Karly Steele
Colette Payne	Leonard Haynes
Timothy Battle	Orli Strong
Cooper Hatfield	Orli Strong
Alea Wiggins	Rhona Nichols
Hedda Kent	Rhona Nichols

Total salary of the employees reporting to each manager.

In [19]:

%%sql
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;

Out [19]:

name	sum(salary)
Karly Steele	2316512
Orli Strong	1255513
Rhona Nichols	845168
Leonard Haynes	101538

Employees in the finance department earning more than their manager.

In [20]:

%%sql
%%sql
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;

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.

License

This Notebook has been released under the Apache 2.0 open source license.

Basic Query Examples

Notebook

Basic Query Examples

1. Create a Workspace

2. Create the Database

3. Create the tables

4. Populate the tables

5. Let's Query!

The SELECT statement

The ORDER BY clause

The WHERE clause

The COUNT function

The MAX function

The Subqueries

The AVG function

The IN operator

The NOT IN operator

The Joins

Details

About this Template

Tags

License