########################################
#### Select and Return Records

# SQL: Select everything from the users table.
SELECT u.*
FROM users as u;

# Cypher: match a simple pattern: all nodes with the label `User` and RETURN them.
MATCH (u:User)
RETURN u;

########################################
#### Field Access, Ordering and Paging

# SQL
SELECT p.ProductName, p.UnitPrice
FROM products as p
ORDER BY p.UnitPrice DESC
LIMIT 10;

# Cypher
MATCH (p:Product)
RETURN p.productName, p.unitPrice
ORDER BY p.unitPrice DESC
LIMIT 10;

########################################
#### Filter by Equality

# SQL
SELECT p.ProductName, p.UnitPrice
FROM products AS p
WHERE p.ProductName = 'Chocolade';

# Cypher
MATCH (p:Product)
WHERE p.productName = "Chocolade"
RETURN p.productName, p.unitPrice;

# There is a shortcut in Cypher if you match for a labeled node with a certain attribute.
MATCH (p:Product {productName:"Chocolade"})
RETURN p.productName, p.unitPrice;

#### Filter by List/Range

# SQL
SELECT p.ProductName, p.UnitPrice
FROM products as p
WHERE p.ProductName IN ('Chocolade','Chai');

# Cypher
MATCH (p:Product)
WHERE p.productName IN ['Chocolade','Chai']
RETURN p.productName, p.unitPrice;

########################################
#### Filter by Multiple Numeric and Textual Predicates

# SQL
SELECT p.ProductName, p.UnitPrice
FROM products AS p
WHERE p.ProductName LIKE 'C%' AND p.UnitPrice > 100;

# Cypher
MATCH (p:Product)
WHERE p.productName STARTS WITH "C" AND p.unitPrice > 100
RETURN p.productName, p.unitPrice;

########################################
#### Query by Connected Records

# SQL
SELECT DISTINCT c.CompanyName
FROM customers AS c
JOIN orders AS o ON (c.CustomerID = o.CustomerID)
JOIN order_details AS od ON (o.OrderID = od.OrderID)
JOIN products AS p ON (od.ProductID = p.ProductID)
WHERE p.ProductName = 'Chocolade';

# Cypher
MATCH (p:Product {productName:"Chocolade"})<-[:PRODUCT]-(:Order)<-[:PURCHASED]-(c:Customer)
RETURN distinct c.companyName;

########################################
### Indexes
#### Query Examples

# SQL
CREATE INDEX person_single_property_index ON person (firstname)
CREATE INDEX person_composite_index ON person (firstname, lastname)

# Cypher
CREATE INDEX ON :Person(firstname)
CREATE INDEX ON :Person(firstname, lastname)

#### Unique Constraint

# SQL
ALTER TABLE user ADD CONSTRAINT user_unique_constraint UNIQUE (username)

# Cypher
CREATE CONSTRAINT ON (user:User) ASSERT user.username IS UNIQUE