# 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. [https://neo4j.com/docs/cypher-manual/current/clauses/remove/#:\~:text=The%20REMOVE%20clause%20is%20used,label%20on%20it%2C%20nothing%20happens.](https://neo4j.com/docs/cypher-manual/current/clauses/remove/)
{% 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 %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/week-10-cypher-queries.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
