> For the complete documentation index, see [llms.txt](https://gchandra.gitbook.io/big-data-and-tools-with-nosql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://gchandra.gitbook.io/big-data-and-tools-with-nosql/nosql/neo4j/examples/putting-it-all-together.md).

# Putting it all together

```cypher
match (n) detach delete n;

create (n:Student{id:101,firstname:"Rachel",lastname:"Green",gender:"F",dob:"2000-01-01"});
create (n:Student{id:102,firstname:"Monica",lastname:"Geller",gender:"F",dob:"2000-02-01"});
create (n:Student{id:103,firstname:"Ross",lastname:"Geller",gender:"M",dob:"1999-01-05"});
create (n:Student{id:104,firstname:"Chandler",lastname:"Bing",gender:"M",dob:"1999-02-07"});
create (n:Student{id:105,firstname:"Phoebe",lastname:"Buffay",gender:"F",dob:"1998-03-07"});
create (n:Student{id:106,firstname:"Joey",lastname:"Tribianni",gender:"M",dob:"1999-07-08"});
create (n:Student{id:107,firstname:"Janice",gender:"F",dob:"2000-07-08"});

CREATE CONSTRAINT cons_stuid_notnull IF NOT EXISTS FOR (n:Student) REQUIRE n.id IS NOT NULL;
CREATE CONSTRAINT cons_stuid_unique IF NOT EXISTS FOR (n:Student) REQUIRE n.id IS UNIQUE; 
create (n:Student{id:108,firstname:"Gunther",gender:"M",dob:"1995-07-08"});

create (t:Course{id:"C001",name:"Applied DB"});
create (t:Course{id:"C002",name:"Big Data"});
create (t:Course{id:"C003",name:"Data Warehousing"});
create (t:Course{id:"C004",name:"Web Programming"});
create (t:Course{id:"C005",name:"Rust Programming"});

create (z:Faculty{id:"F001",firstname:"Ganesh",lastname:"Chandra"});
create (z:Faculty{id:"F002",firstname:"Jack",lastname:"Myers"});
create (z:Faculty{id:"F003",firstname:"Tony",lastname:"Brietzman"});

```

```cypher
MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 101 AND b.id = 'C001' AND c.id='F001'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"A",semester:"Fall2021"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 101 AND b.id = 'C003' AND c.id='F003'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"B",semester:"Spring2022"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 102 AND b.id = 'C001' AND c.id='F001'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"B+",semester:"Spring2022"}]-(a);


MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 101 AND b.id = 'C003' AND c.id='F003'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"B",semester:"Spring2022"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 103 AND b.id = 'C003' AND c.id='F003'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"A-",semester:"Spring2022"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 104 AND b.id = 'C004' AND c.id='F002'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"A",semester:"Fall2021"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 105 AND b.id = 'C005' AND c.id='F001'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"A",semester:"Spring2022"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 106 AND b.id = 'C004' AND c.id='F001'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"B+",semester:"Fall2021"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 106 AND b.id = 'C002' AND c.id='F002'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"A",semester:"Spring2022"}]-(a);

MATCH (a:Student),(b:Course),(c:Faculty) WHERE a.id = 107 AND b.id = 'C005' AND c.id='F001'
MERGE (c)-[:TEACHING]->(b)
MERGE (b)<-[:TAKING {grade:"A",semester:"Fall2021"}]-(a);
```

Queries

1. **Retrieve All Students**

```cypher
MATCH (n:Student) RETURN n;
```

2. **Find a Specific Student by ID**

```cypher
MATCH (n:Student {id:101}) RETURN n;
```

**3. List All Courses**

```cypher
MATCH (n:Course) RETURN n.name;
```

**4. Find Students Taking a Specific Course**

```cypher
MATCH (s:Student)-[:TAKING]->(c:Course {id:"C001"}) 
RETURN s.firstname, s.lastname;
```

**5. List Courses Taught by a Specific Faculty**

```cypher
MATCH (f:Faculty {id:"F001"})-[:TEACHING]->(c:Course) 
RETURN f.firstname, f.lastname, collect(c.name) AS Courses;
```

**6. Find Students and Their Grades for a Specific Course**

```cypher
MATCH (s:Student)-[r:TAKING]->(c:Course {id:"C002"}) 
RETURN s.firstname, s.lastname, r.grade;
```

####

**7. Find Average Grade for Each Course**

```cypher
MATCH (s:Student)-[r:TAKING]->(c:Course) 
RETURN c.name, avg(toFloat(r.grade)) AS AverageGrade
ORDER BY AverageGrade DESC;
```

**8. Identify Students Taking Courses with a Specific Faculty**

```cypher
MATCH (s:Student)-[:TAKING]->(c:Course)<-[:TEACHING]-(f:Faculty {id:"F001"}) 
RETURN s.firstname, s.lastname, collect(c.name) AS Courses;
```

**9. Create a Friendship Relationship Between Students**

```cypher
MATCH (s1:Student {id:101}), (s2:Student {id:102}) 
MERGE (s1)-[:FRIENDS_WITH]->(s2);
```

Creates a "FRIENDS\_WITH" relationship between two students.

**10. Find Students and the Number of Courses They Are Taking**

```cypher
MATCH (s:Student)-[:TAKING]->(c:Course) 
RETURN s.firstname, s.lastname, count(c) AS CoursesTaken
ORDER BY CoursesTaken DESC;
```

Counts how many courses each student is taking.

**11. Find the Most Popular Course**

```cypher
MATCH (s:Student)-[:TAKING]->(c:Course) 
WITH c, count(s) AS StudentCount 
ORDER BY StudentCount DESC 
LIMIT 1 
RETURN c.name, StudentCount;
```

**12. Students and Their Friends Taking the Same Course**

```cypher
MATCH (s:Student)-[:FRIENDS_WITH]->(friend:Student)-[:TAKING]->(course:Course), (s)-[:TAKING]->(course) 
RETURN s.firstname, friend.firstname, course.name;
```

13. Search by Semester & Grade

```cypher
MATCH (s:Student)-[r:TAKING]->(c:Course) 
WHERE r.semester = "Spring2022" 
AND r.grade =~ "[A-C].*|D+" 
RETURN s.id AS StudentID, s.firstname AS FirstName, s.lastname AS LastName, c.id AS CourseID, c.name AS CourseName, r.grade AS Grade

```

14. Show all connected nodes for a given node (upto 4 levels)

```cypher
MATCH (ross:Student {firstname:"Ross"})-[*1..4]-(connectedNodes)
RETURN ross, connectedNodes;
```

15. Show all connected nodes&#x20;

```cypher
MATCH (ross:Student {firstname:"Ross"})-[*1..]-(connectedNodes)
RETURN ross, connectedNodes;

```