> For the complete documentation index, see [llms.txt](https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/week-10-cypher-queries.md).
# Week 10 - Cypher Queries
This week's lab focuses on writing and understanding Cypher queries. **Let's use the database that we created last week (week 9 lab sheet) as the example.**
## 1. READ
Write queries that find each of the following:
1. All nodes in the database.
```cypher
MATCH (n) RETURN n
```
2. All products in the database.
```cypher
MATCH (n:Product) RETURN n
```
3. Up to 5 products in the database.
```cypher
MATCH (n:Product) RETURN n LIMIT 5
```
4. All products whose prices are greater than 20. We will talk about `toFloat()` in [Section 2. UPDATE.](#id-2.-update)
MATCH (n:Product)
WHERE toFloat(n.purchasePrice) > 20
RETURN n
5. All sales which contain a product with the brand "Mega".
```cypher
MATCH (s:Sale)-[:CONTAINS]->(p:Product {brand: "Mega"})
RETURN s
```
Alternatively, If you don't need to reference the "Product" node itself, you can simplify the query by removing "p".
```cypher
MATCH (s:Sale)-[:CONTAINS]->(:Product {brand: "Mega"})
RETURN s
```
6. All shops that have sales of a product with the brand "Mega".
```cypher
MATCH (s:Shop)<-[:SOLD_IN]-(:Sale)-[:CONTAINS]->(:Product{brand:"Mega"})
RETURN s
```
7. Same query as 6, but return the path.
```cypher
MATCH p=(s:Shop)<-[:SOLD_IN]-(:Sale)-[:CONTAINS]->(:Product{brand:"Mega"})
RETURN p
```
8. In this example dataset, our relationships don't have properties. However, what if we need to query based on a property of a relationship? Let's assume there's a "year" property on the "SOLD\_IN" relationship. Here's how you can modify the query to include this relationship property:
```cypher
MATCH (p:Product {brand: "Mega"})-[:CONTAINS]-(s:Sale)-[:SOLD_IN]-(sh:Shop)
WHERE s.date STARTS WITH '2021'
RETURN DISTINCT sh
```
If there's a "year" property on the "SOLD\_IN" relationship, the query should be:
```cypher
MATCH (p:Product {brand: "Mega"})-[:CONTAINS]->(s:Sale)-[r:SOLD_IN {year: 2021}]->(sh:Shop)
RETURN DISTINCT sh
```
The output of the above query should be (no changes, no records) as there is no "year" property on the "SOLD\_IN" relationship.
{% hint style="warning" %}
Your turn: What does Query 8 mean?
{% endhint %}
9. Find the path with a length of 4 from the product with ID "TAB\_1" to the product with ID "TSH\_9":
```cypher
MATCH p=(:Product {productID: "TAB_1"})-[*4]-(:Product {productID: "TSH_9"})
RETURN p
```
For more information on path queries:
{% hint style="info" %}
**NOTE**: All the variables used in the queries, such as "s", "n", and "p", are self-defined. You can replace these variable names with any valid variable name of your choice.
{% endhint %}
## 2. UPDATE
Sometimes, records such as product price might need updating. Instead of uploading a new CSV file, we can write queries to update the information directly in the database.
1. Updates a product's price to a different price.
Check the current price of the product with ID: "BED\_3" and its type:
MATCH (p:Product {productID: "BED_3"})
RETURN p.purchasePrice
Alternatively, you can return the node and hover over the property to see its type:
You can see that the `purchasePrice` is stored as a string. This is because when we created nodes from a CSV file (see Week9), we did not specify the price as a float, so **Neo4j automatically treated every variable as a string.**
Two methods to change the type:
**Method 1:** Changing the type when creating the database from a CSV file, you can use conversion functions such as `toInteger()`, `toFloat()`, `toString()`, `toBoolean()`. You can learn more about these functions in the Neo4j documentation: [Scalar functions](https://neo4j.com/docs/cypher-manual/current/functions/scalar/)
For example:
```cypher
LOAD CSV WITH HEADERS FROM 'file:///products.csv' AS row
CREATE (d:Product {
productID: row.`Product`,
category: row.Category,
subcategory: row.`Sub category`,
productSize: row.Size,
purchasePrice: toFloat(row.`Purchase price`),
color: row.Color,
brand:row.Brand
})
```
In this example, `toFloat()` is used to convert the `purchasePrice` property from a string to a float during node creation.
**Method 2:** Updating the type after creating:
```cypher
MATCH (p:Product)
SET p.purchasePrice = toFloat(p.purchasePrice)
```
{% hint style="warning" %}
Your turn: compare Figure 1 with Figure 3. What's the difference between their results?
{% endhint %}
Now, let's update the price:
```cypher
MATCH (p:Product {productID: "BED_3"})
SET p.purchasePrice = 125.0
```
{% hint style="warning" %}
Your turn:
* Verify whether the price of product "BED\_3" has been correctly updated to 125.0.
* Choose a shop and update its size to a different value.
* Update the "unitPrice" property of all Sale nodes to float.
* Without changing the type of "quantity" for sale nodes, update the sale whose "SaleID" is "S000004969" to have a quantity of "5.0".
* Remove the "subcategory" property from all product nodes.
{% endhint %}
## 3. DELETE
1. Deletes a specific shop in the database
```cypher
MATCH (n:Shop {shopID: "shop_14"})
DETACH DELETE n
```
2. Deletes a relationship between a specific Shop and a specific Sale
```cypher
MATCH p=(n:Sale{saleID: "S000001480"})-[r:SOLD_IN]->(s:Shop{shopID:"shop_0"})
DELETE r
```
{% hint style="warning" %}
Your turn:
1. What is the difference between "DELETE" and "DETACH DELETE":
2. Delete all nodes and relationships in the database.
{% endhint %}
