1208 Assignment 1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b1e95655",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pymongo in c:\\users\\hariprasad\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (4.10.1)\n",
      "Requirement already satisfied: dnspython<3.0.0,>=1.16.0 in c:\\users\\hariprasad\\appdata\\local\\programs\\python\\python312\\lib\\site-packages (from pymongo) (2.7.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip is available: 24.1 -> 24.3.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "pip install pymongo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b4fa04b",
   "metadata": {},
   "source": [
    "## Assignment 1\n",
    "**Deadline**: 8th December 2024\n",
    "\n",
    "This assessment consists of 20 MongoDB queries, ranging from easy to hard. You are required to use the following sample data:\n",
    "\n",
    "```json\n",
    "{\n",
    "    \"students\": [\n",
    "        {\"name\": \"Rohit\", \"age\": 23, \"math_score\": 85, \"physics_score\": 90, \"city\": \"New York\"},\n",
    "        {\"name\": \"Eram\", \"age\": 22, \"math_score\": 78, \"physics_score\": 75, \"city\": \"Los Angeles\"},\n",
    "        {\"name\": \"Madan\", \"age\": 24, \"math_score\": 95, \"physics_score\": 88, \"city\": \"Chicago\"},\n",
    "        {\"name\": \"Uvaish\", \"age\": 21, \"math_score\": 60, \"physics_score\": 65, \"city\": \"Houston\"},\n",
    "        {\"name\": \"Neha\", \"age\": 23, \"math_score\": 72, \"physics_score\": 80, \"city\": \"Phoenix\"}\n",
    "    ]\n",
    "}\n",
    "\n",
    "```\n",
    "\n",
    "Please submit your solutions by the given deadline.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e733b76",
   "metadata": {},
   "source": [
    "1. Insert the given sample data into a MongoDB collection called 'students'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "03d2d6e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data inserted successfully!\n"
     ]
    }
   ],
   "source": [
    "from pymongo import MongoClient\n",
    "\n",
    "\n",
    "client = MongoClient(\"mongodb://localhost:27017/\")\n",
    "db = client['assignment_db']  \n",
    "collection = db['students']\n",
    "\n",
    "\n",
    "students_data = [\n",
    "    {\"name\": \"Rohit\", \"age\": 23, \"math_score\": 85, \"physics_score\": 90, \"city\": \"New York\"},\n",
    "    {\"name\": \"Eram\", \"age\": 22, \"math_score\": 78, \"physics_score\": 75, \"city\": \"Los Angeles\"},\n",
    "    {\"name\": \"Madan\", \"age\": 24, \"math_score\": 95, \"physics_score\": 88, \"city\": \"Chicago\"},\n",
    "    {\"name\": \"Uvaish\", \"age\": 21, \"math_score\": 60, \"physics_score\": 65, \"city\": \"Houston\"},\n",
    "    {\"name\": \"Neha\", \"age\": 23, \"math_score\": 72, \"physics_score\": 80, \"city\": \"Phoenix\"}\n",
    "]\n",
    "\n",
    "\n",
    "collection.insert_many(students_data)\n",
    "print(\"Data inserted successfully!\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c63a46c2",
   "metadata": {},
   "source": [
    "2. Write a query to find all students who have a 'math_score' greater than 80."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2a6ca4b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942a'), 'name': 'Rohit', 'age': 23, 'math_score': 85, 'physics_score': 90, 'city': 'New York'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'physics_score': 88, 'city': 'Chicago'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\"math_score\": {\"$gt\": 80}})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f965853a",
   "metadata": {},
   "source": [
    "3. Write a query to find students whose 'age' is less than 23."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6140e412",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942b'), 'name': 'Eram', 'age': 22, 'math_score': 78, 'physics_score': 75, 'city': 'Los Angeles'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942d'), 'name': 'Uvaish', 'age': 21, 'math_score': 60, 'physics_score': 65, 'city': 'Houston'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\"age\": {\"$lt\": 23}})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88005dde",
   "metadata": {},
   "source": [
    "4. Write a query to return only the 'name' and 'math_score' of all students."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b274135f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'Rohit', 'math_score': 85}\n",
      "{'name': 'Eram', 'math_score': 78}\n",
      "{'name': 'Madan', 'math_score': 95}\n",
      "{'name': 'Uvaish', 'math_score': 60}\n",
      "{'name': 'Neha', 'math_score': 72}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({}, {\"name\": 1, \"math_score\": 1, \"_id\": 0})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a148d48",
   "metadata": {},
   "source": [
    "5. Write a query to find students from the city 'New York'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2acedafc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942a'), 'name': 'Rohit', 'age': 23, 'math_score': 85, 'physics_score': 90, 'city': 'New York'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\"city\": \"New York\"})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaa6f033",
   "metadata": {},
   "source": [
    "6. Write a query to update the 'physics_score' of 'Bob' to 85."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ae4d1f5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Update complete.\n"
     ]
    }
   ],
   "source": [
    "collection.update_one({\"name\": \"Bob\"}, {\"$set\": {\"physics_score\": 85}})\n",
    "print(\"Update complete.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1df67d31",
   "metadata": {},
   "source": [
    "7. Write a query to delete the student 'David' from the collection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "293c2347",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Student deleted successfully.\n"
     ]
    }
   ],
   "source": [
    "collection.delete_one({\"name\": \"David\"})\n",
    "print(\"Student deleted successfully.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad535a0b",
   "metadata": {},
   "source": [
    "8. Write a query to find all students where 'math_score' is between 70 and 90 (inclusive)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6a3c58a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942a'), 'name': 'Rohit', 'age': 23, 'math_score': 85, 'physics_score': 90, 'city': 'New York'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942b'), 'name': 'Eram', 'age': 22, 'math_score': 78, 'physics_score': 75, 'city': 'Los Angeles'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942e'), 'name': 'Neha', 'age': 23, 'math_score': 72, 'physics_score': 80, 'city': 'Phoenix'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\"math_score\": {\"$gte\": 70, \"$lte\": 90}})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfb2950f",
   "metadata": {},
   "source": [
    "9. Write a query to find students whose 'math_score' is greater than 'physics_score'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ac170ca2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942b'), 'name': 'Eram', 'age': 22, 'math_score': 78, 'physics_score': 75, 'city': 'Los Angeles'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'physics_score': 88, 'city': 'Chicago'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\"$expr\": {\"$gt\": [\"$math_score\", \"$physics_score\"]}})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cebc3d1a",
   "metadata": {},
   "source": [
    "10. Write a query to return students sorted by 'math_score' in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1b44a3bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'physics_score': 88, 'city': 'Chicago'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942a'), 'name': 'Rohit', 'age': 23, 'math_score': 85, 'physics_score': 90, 'city': 'New York'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942b'), 'name': 'Eram', 'age': 22, 'math_score': 78, 'physics_score': 75, 'city': 'Los Angeles'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942e'), 'name': 'Neha', 'age': 23, 'math_score': 72, 'physics_score': 80, 'city': 'Phoenix'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942d'), 'name': 'Uvaish', 'age': 21, 'math_score': 60, 'physics_score': 65, 'city': 'Houston'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find().sort(\"math_score\", -1)\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be69f913",
   "metadata": {},
   "source": [
    "11. Write a query using the aggregation framework to calculate the average 'math_score' of all students."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8041c7fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': None, 'average_math_score': 78.0}\n"
     ]
    }
   ],
   "source": [
    "result = collection.aggregate([\n",
    "    {\"$group\": {\"_id\": None, \"average_math_score\": {\"$avg\": \"$math_score\"}}}\n",
    "])\n",
    "for data in result:\n",
    "    print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf841104",
   "metadata": {},
   "source": [
    "12. Write a query to group students by 'city' and count the number of students in each city."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "11c79007",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': 'Chicago', 'student_count': 1}\n",
      "{'_id': 'New York', 'student_count': 1}\n",
      "{'_id': 'Houston', 'student_count': 1}\n",
      "{'_id': 'Phoenix', 'student_count': 1}\n",
      "{'_id': 'Los Angeles', 'student_count': 1}\n"
     ]
    }
   ],
   "source": [
    "result = collection.aggregate([\n",
    "    {\"$group\": {\"_id\": \"$city\", \"student_count\": {\"$sum\": 1}}}\n",
    "])\n",
    "for city in result:\n",
    "    print(city)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "422ec61c",
   "metadata": {},
   "source": [
    "13. Write a query to find students who either have 'math_score' greater than 80 or 'physics_score' greater than 85."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "260dcc6a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942a'), 'name': 'Rohit', 'age': 23, 'math_score': 85, 'physics_score': 90, 'city': 'New York'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'physics_score': 88, 'city': 'Chicago'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\n",
    "    \"$or\": [\n",
    "        {\"math_score\": {\"$gt\": 80}},\n",
    "        {\"physics_score\": {\"$gt\": 85}}\n",
    "    ]\n",
    "})\n",
    "for student in result:\n",
    "    print(student)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca4d6f63",
   "metadata": {},
   "source": [
    "14. Write a query to find students whose age is exactly 23 and live in 'Phoenix'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8743c54d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942e'), 'name': 'Neha', 'age': 23, 'math_score': 72, 'physics_score': 80, 'city': 'Phoenix'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\n",
    "    \"age\": 23,\n",
    "    \"city\": \"Phoenix\"\n",
    "})\n",
    "for student in result:\n",
    "    print(student)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f871c8d",
   "metadata": {},
   "source": [
    "15. Write a query to find students whose 'math_score' is not equal to 85."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "31dad59e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942b'), 'name': 'Eram', 'age': 22, 'math_score': 78, 'physics_score': 75, 'city': 'Los Angeles'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'physics_score': 88, 'city': 'Chicago'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942d'), 'name': 'Uvaish', 'age': 21, 'math_score': 60, 'physics_score': 65, 'city': 'Houston'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942e'), 'name': 'Neha', 'age': 23, 'math_score': 72, 'physics_score': 80, 'city': 'Phoenix'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\n",
    "    \"math_score\": {\"$ne\": 85}\n",
    "})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60ea1681",
   "metadata": {},
   "source": [
    "16. Write a query to find students whose 'name' starts with the letter 'A'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7bd53a1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = collection.find({\n",
    "    \"name\": {\"$regex\": \"^A\", \"$options\": \"i\"}\n",
    "})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed20ebf4",
   "metadata": {},
   "source": [
    "17. Write a query to find students whose 'city' is neither 'New York' nor 'Los Angeles'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "756b4281",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'physics_score': 88, 'city': 'Chicago'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942d'), 'name': 'Uvaish', 'age': 21, 'math_score': 60, 'physics_score': 65, 'city': 'Houston'}\n",
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942e'), 'name': 'Neha', 'age': 23, 'math_score': 72, 'physics_score': 80, 'city': 'Phoenix'}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find({\n",
    "    \"city\": {\"$nin\": [\"New York\", \"Los Angeles\"]}\n",
    "})\n",
    "for student in result:\n",
    "    print(student)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02cbae8c",
   "metadata": {},
   "source": [
    "18. Write a query to update all students with 'age' greater than 22 to add a new field 'graduated': true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "24708ab9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Modified 3 documents.\n"
     ]
    }
   ],
   "source": [
    "result = collection.update_many(\n",
    "    {\"age\": {\"$gt\": 22}},  \n",
    "    {\"$set\": {\"graduated\": True}})\n",
    "print(f\"Modified {result.modified_count} documents.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2212656",
   "metadata": {},
   "source": [
    "19. Write a query to remove the 'physics_score' field for students who live in 'Chicago'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "eb52a26e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Modified 1 documents.\n"
     ]
    }
   ],
   "source": [
    "result = collection.update_many(\n",
    "    {\"city\": \"Chicago\"},\n",
    "    {\"$unset\": {\"physics_score\": \"\"}})\n",
    "print(f\"Modified {result.modified_count} documents.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29fcb8f8",
   "metadata": {},
   "source": [
    "20. Write a query to find the student with the highest 'math_score'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4f647fd1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('6755d2607fe2f1dfcb70942c'), 'name': 'Madan', 'age': 24, 'math_score': 95, 'city': 'Chicago', 'graduated': True}\n"
     ]
    }
   ],
   "source": [
    "result = collection.find().sort(\"math_score\", -1).limit(1)\n",
    "for student in result: print(student)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}