Basic Queries

This document introduce the basic queries of SynxDB Elastic.

SynxDB Elastic is a high-performance, highly parallel data warehouse developed based on PostgreSQL and Greenplum. Here are some examples of the basic query syntax.

SELECT: Used to retrieve data from databases & tables.

SELECT * FROM employees;  -- Queries all data in the employees table.

Conditional query (WHERE): Used to filter result sets based on certain conditions.

SELECT * FROM employees WHERE salary > 50000;  -- Queries employee information with salary exceeding 50,000.

ORDER BY: Used to sort query results by one or more columns.

SELECT * FROM employees ORDER BY salary DESC;  -- Sorts employee information in descending order by salary.

Aggregation functions: such as COUNT, SUM, AVG, MAX, MIN, used for calculating statistics from datasets.
```
SELECT AVG(salary) FROM employees;  -- Calculates the average salary of employees.
```

GROUP BY: Used in conjunction with aggregation functions to group result sets.

SELECT department, COUNT(*) FROM employees GROUP BY department;  -- Counts the number of employees by department.

Limit the number of results (LIMIT): used to limit the number of rows returned by the query result.

SELECT * FROM employees LIMIT 10;  -- Only queries the information of the first 10 employees.

Join query (JOIN): used to combine data from two or more tables based on related columns.

SELECT employees.name, departments.name
FROM employees
JOIN departments ON employees.department_id = departments.id;  -- Queries employees and their corresponding department names.

Subquery: Nested queries in another SQL query.

SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');  -- Queries all employees working in New York.

The above is just a brief overview of the basic query syntax in SynxDB Elastic. SynxDB Elastic also provides more advanced queries and functions to help developers perform complex data operations and analyses.