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]:
%%sqlCREATE DATABASE memsql_example;USE memsql_example;
Out [1]:
3. Create the tables
Create the tables named departments, employees, and salaries.
In [2]:
%%sqlCREATE 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]:
%%sqlINSERT 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]:
%%sqlSELECT COUNT(*) from employees;
Out [4]:
COUNT(*) |
---|
10 |
The ORDER BY clause
List the ID and the name of each employee.
In [5]:
%%sqlSELECT 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]:
%%sqlSELECT 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]:
%%sqlSELECT 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]:
%%sqlSELECT id, name, hireDateFROM employeesWHERE 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]:
%%sqlSELECT e.name, d.name department FROMemployees e, departments dWHERE e.deptId = d.idORDER 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]:
%%sqlSELECT state, COUNT(*)from employeesgroup by stateORDER BY state;
Out [10]:
state | COUNT(*) |
---|---|
MS | 2 |
NY | 5 |
TX | 3 |
The MAX function
Highest salary amongst all employees.
In [11]:
%%sqlSELECT MAX(salary) FROM salaries;
Out [11]:
MAX(salary) |
---|
919124 |
The Subqueries
Employee with the highest salary.
In [12]:
%%sqlSELECT e.name, s.salaryFROM employees e, salaries sWHERE e.id = s.employeeId ands.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]:
%%sqlSELECT e.state, AVG(salary)FROM employees eJOIN salaries s on e.id = s.employeeIdGROUP BY e.stateORDER 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]:
%%sqlSELECT nameFROM employeesWHERE 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]:
%%sqlSELECT nameFROM employeesWHERE 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]:
%%sqlSELECT m.name, COUNT(*) countFROM employees mJOIN employees e ON m.id = e.managerIdGROUP BY m.idORDER 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]:
%%sqlSELECT m.name, COUNT(e.id) countFROM employees mLEFT JOIN employees e ON m.id = e.managerIdGROUP BY m.idORDER 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]:
%%sqlSELECT e.name employee_name, m.name manager_nameFROM employees eLEFT JOIN employees m ON e.managerId = m.idORDER 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]:
%%sqlSELECT 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;
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%%sqlSELECT 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;
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.