Database_Connectivity_MySQL .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c90c5026",
   "metadata": {},
   "source": [
    "### To run MySQL in Python, you need to have the following requirements:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3b17573",
   "metadata": {},
   "source": [
    "MySQL Server: Install MySQL Server on your machine. You can download it from the official MySQL website (https://dev.mysql.com/downloads/mysql/).\n",
    "\n",
    "MySQL Connector/Python: Install the MySQL Connector/Python library, which allows Python to connect and interact with MySQL databases. You can install it using pip:\n",
    "\n",
    "\tpip install mysql-connector-python\n",
    "\n",
    "Once you have these requirements in place, you can import the mysql.connector module in your Python script to establish a connection to the MySQL server and execute queries.\n",
    "\n",
    "Here's an example of a Python script that connects to a MySQL database and executes a simple query:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "be3582f7",
   "metadata": {},
   "source": [
    "import mysql.connector\n",
    "\n",
    "# Connect to MySQL Server\n",
    "conn = mysql.connector.connect(\n",
    "    host='localhost',\n",
    "    user='your_username',\n",
    "    password='your_password',\n",
    "    database='your_database_name'\n",
    ")\n",
    "\n",
    "# Create a cursor object to execute queries\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Execute a query\n",
    "query = 'SELECT * FROM your_table_name'\n",
    "cursor.execute(query)\n",
    "\n",
    "# Fetch and print the query results\n",
    "results = cursor.fetchall()\n",
    "for row in results:\n",
    "    print(row)\n",
    "\n",
    "# Close the cursor and connection\n",
    "cursor.close()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "raw",
   "id": "379a393a",
   "metadata": {},
   "source": [
    "# Create a new table:\n",
    "import mysql.connector\n",
    "\n",
    "conn = mysql.connector.connect(\n",
    "    host='localhost',\n",
    "    user='your_username',\n",
    "    password='your_password',\n",
    "    database='your_database_name'\n",
    ")\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "create_table_query = '''\n",
    "CREATE TABLE your_table_name (\n",
    "    column1 datatype1,\n",
    "    column2 datatype2,\n",
    "    ...\n",
    ")\n",
    "'''\n",
    "\n",
    "cursor.execute(create_table_query)\n",
    "\n",
    "cursor.close()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2c0f964e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['CLIENTNO', 'NAME', 'CITY', 'PINCODE', 'STATE', 'BALDUE']\n",
      "('C00003', 'Chhaya Bankar', 'Mumbai', 400057, 'Maharashtra', 5000)\n",
      "('C00004', 'Ashwini Joshi', 'Bangalore', 560001, 'Karnataka', 0)\n",
      "('C00005', 'Hansel Colaco', 'Mumbai', 400060, 'Maharashtra', 2000)\n",
      "('C00006', 'Deepak Sharma', 'Mangalore', 560050, 'Karnataka', 0)\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "# Connect to MySQL Server\n",
    "conn = mysql.connector.connect(\n",
    "    host='localhost',\n",
    "    user='root',\n",
    "    password='0000',\n",
    "    database='assignments'\n",
    ")\n",
    "\n",
    "# Create a cursor object to execute queries\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Execute a query\n",
    "query = 'SELECT * FROM Client_Master'\n",
    "cursor.execute(query)\n",
    "\n",
    "# Fetch the column names\n",
    "columns = [col[0] for col in cursor.description]\n",
    "\n",
    "# Print the column names\n",
    "print(columns)\n",
    "\n",
    "# Fetch and print the query results\n",
    "results = cursor.fetchall()\n",
    "for row in results:\n",
    "    print(row)\n",
    "\n",
    "# Close the cursor and connection if work complete\n",
    "#cursor.close()\n",
    "\n",
    "# If you close connnnection than when you run new query than you need to re established the database connection\n",
    "#conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "795e97fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Table\n",
    "\n",
    "create_table_query = '''\n",
    "CREATE TABLE Pranik(\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    name VARCHAR(255),\n",
    "    age INT,\n",
    "    email VARCHAR(255)\n",
    ")\n",
    "'''\n",
    "\n",
    "cursor.execute(create_table_query)\n",
    "\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "035310d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# cursor.execute(\"drop table Pranik\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "35a98df4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert single record at a time into a table:\n",
    "\n",
    "insert_query = '''\n",
    "INSERT INTO pranik(name, age, email) VALUES (%s, %s, %s)\n",
    "'''\n",
    "values = ('Nikhil Yadav', 20, 'yadavnikhilrao@gmail.com')\n",
    "\n",
    "cursor.execute(insert_query, values)\n",
    "\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0205b903",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert data into a table\n",
    "\n",
    "insert_query = '''\n",
    "INSERT INTO pranik(name, age, email) VALUES (%s, %s, %s)\n",
    "'''\n",
    "values = [\n",
    "    ('Nikhil Yadav', 20, 'yadavnikhilrao@gmail.com'),\n",
    "    ('Prakriti Yadav', 19, 'pranikmovies@gmail.com'),\n",
    "    ('Akhil Yadav', 18, 'shooterakku@gmail.com')\n",
    "]\n",
    "\n",
    "cursor.executemany(insert_query, values)\n",
    "\n",
    "# Commit the changes to the database\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "90d77749",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Nikhil Yadav', 20, 'yadavnikhilrao@gmail.com')\n",
      "(2, 'Nikhil Yadav', 20, 'yadavnikhilrao@gmail.com')\n",
      "(3, 'Prakriti Yadav', 19, 'pranikmovies@gmail.com')\n",
      "(4, 'Akhil Yadav', 18, 'shooterakku@gmail.com')\n"
     ]
    }
   ],
   "source": [
    "# Select data from a table:\n",
    "\n",
    "select_query = '''\n",
    "SELECT * FROM pranik\n",
    "'''\n",
    "\n",
    "cursor.execute(select_query)\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b3fce8b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Update data in a table:\n",
    "\n",
    "update_query = '''\n",
    "UPDATE pranik SET age = %s WHERE id = %s\n",
    "'''\n",
    "values = (22, 1)\n",
    "\n",
    "cursor.execute(update_query, values)\n",
    "\n",
    "conn.commit()\n",
    "\n",
    "# close old cursor for fetching nr=ew records from database\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "27c77bbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Nikhil Yadav', 22, 'yadavnikhilrao@gmail.com')\n",
      "(2, 'Nikhil Yadav', 20, 'yadavnikhilrao@gmail.com')\n",
      "(3, 'Prakriti Yadav', 19, 'pranikmovies@gmail.com')\n",
      "(4, 'Akhil Yadav', 18, 'shooterakku@gmail.com')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select data from a table:\n",
    "\n",
    "cursor = conn.cursor()\n",
    "select_query = '''\n",
    "SELECT * FROM pranik\n",
    "'''\n",
    "\n",
    "cursor.execute(select_query)\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)\n",
    "    \n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f97074d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete data from a table:\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "delete_query = '''\n",
    "DELETE FROM pranik WHERE id = %s\n",
    "'''\n",
    "values = (2,)\n",
    "\n",
    "cursor.execute(delete_query, values)\n",
    "\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1c988e2a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Prakriti Yadav',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select specific columns with conditions:\n",
    "\n",
    "# Create a cursor object\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Select data from a table\n",
    "select_query = '''\n",
    "SELECT name FROM pranik WHERE age = 19\n",
    "'''\n",
    "\n",
    "# Execute the query\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Fetch and print the query results\n",
    "rows = cursor.fetchall()\n",
    "for row in rows:\n",
    "    print(row)\n",
    "\n",
    "# Close the cursor and connection\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6361c6a9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Nikhil Yadav', 22, 'yadavnikhilrao@gmail.com')\n",
      "(3, 'Prakriti Yadav', 19, 'pranikmovies@gmail.com')\n",
      "(4, 'Akhil Yadav', 18, 'shooterakku@gmail.com')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select data with sorting:\n",
    "\n",
    "# Create a cursor object\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Select data with sorting\n",
    "select_query = '''\n",
    "SELECT * FROM pranik ORDER BY age DESC\n",
    "''' #ASC\n",
    "\n",
    "# Execute the query\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Fetch and print the query results\n",
    "rows = cursor.fetchall()\n",
    "for row in rows:\n",
    "    print(row)\n",
    "\n",
    "# Close the cursor and connection\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d46db466",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Nikhil Yadav', 22, 'yadavnikhilrao@gmail.com')\n",
      "(3, 'Prakriti Yadav', 19, 'pranikmovies@gmail.com')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select data with a limit:\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "select_query = '''\n",
    "SELECT * FROM pranik LIMIT 2\n",
    "'''\n",
    "\n",
    "cursor.execute(select_query)\n",
    "\n",
    "rows = cursor.fetchall()\n",
    "\n",
    "for row in rows:\n",
    "    print(row)\n",
    "\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53598a89",
   "metadata": {},
   "source": [
    "#### Certainly! Here is the arranged code for the queries in a logical order from basics to advanced, without any repeated queries:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37831298",
   "metadata": {},
   "source": [
    "Please note that you should replace `'localhost'`, `'root'`, `'0000'`, `'your_database_name'`, `'your_table_name'`, and other values with your specific MySQL server, username, password, database, and table details. Additionally, adjust the column names, conditions, and other parts of the queries according to your specific requirements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e8c9e79",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import mysql.connector\n",
    "\n",
    "# Connect to MySQL Server\n",
    "conn = mysql.connector.connect(\n",
    "    host='localhost',\n",
    "    user='root',\n",
    "    password='0000',\n",
    "    database='your_database_name'\n",
    ")\n",
    "\n",
    "# Create a cursor object to execute queries\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Query 1: Create a table\n",
    "create_table_query = '''\n",
    "CREATE TABLE IF NOT EXISTS your_table_name (\n",
    "    column1 datatype1,\n",
    "    column2 datatype2,\n",
    "    ...\n",
    ")\n",
    "'''\n",
    "cursor.execute(create_table_query)\n",
    "\n",
    "# Query 2: Insert a single record into the table\n",
    "insert_query = '''\n",
    "INSERT INTO your_table_name (column1, column2, ...)\n",
    "VALUES (%s, %s, ...)\n",
    "'''\n",
    "values = [\n",
    "    (value1, value2, ...),\n",
    "    (value1, value2, ...),\n",
    "    ...\n",
    "]\n",
    "cursor.executemany(insert_query, values)\n",
    "\n",
    "# Query 3: Select distinct values from a column\n",
    "select_distinct_query = '''\n",
    "SELECT DISTINCT column_name FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_distinct_query)\n",
    "\n",
    "# Query 4: Update data in the table\n",
    "update_query = '''\n",
    "UPDATE your_table_name\n",
    "SET column1 = new_value1, column2 = new_value2, ...\n",
    "WHERE condition\n",
    "'''\n",
    "cursor.execute(update_query)\n",
    "\n",
    "# Query 5: Delete data from the table\n",
    "delete_query = '''\n",
    "DELETE FROM your_table_name\n",
    "'''\n",
    "cursor.execute(delete_query)\n",
    "\n",
    "# Query 6: Select data with GROUP BY\n",
    "select_group_by_query = '''\n",
    "SELECT column1, aggregate_function(column2)\n",
    "FROM your_table_name\n",
    "GROUP BY column1\n",
    "'''\n",
    "cursor.execute(select_group_by_query)\n",
    "\n",
    "# Query 7: Select data with conditions\n",
    "select_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE condition\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 8: Select data with multiple conditions (AND)\n",
    "select_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE condition1 AND condition2\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 9: Select data with LIKE operator\n",
    "select_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column LIKE '%value%'\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 10: Select data with aggregate functions\n",
    "select_query = '''\n",
    "SELECT aggregate_function(column) FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 11: Select data with JOIN\n",
    "select_query = '''\n",
    "SELECT t1.column1, t2.column2\n",
    "FROM table1 AS t1\n",
    "JOIN table2 AS t2 ON t1.id = t2.id\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 12: Select data with ORDER BY\n",
    "select_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "ORDER BY column1 ASC, column2 DESC\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 13: Select data with LIMIT and OFFSET\n",
    "select_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "LIMIT 10 OFFSET 5\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 14: Select data with DISTINCT\n",
    "select_query = '''\n",
    "SELECT DISTINCT column FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 15: Select data with GROUP BY (aggregate function)\n",
    "select_query = '''\n",
    "SELECT column, aggregate_function(column) FROM your_table_name\n",
    "GROUP BY column\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 16: Select data with GROUP BY and HAVING\n",
    "select_query = '''\n",
    "SELECT column, aggregate_function(column) FROM your_table_name\n",
    "GROUP BY column\n",
    "HAVING condition\n",
    "'''\n",
    "cursor.execute(select_query)\n",
    "\n",
    "# Query 17: Update data with condition\n",
    "update_query = '''\n",
    "UPDATE your_table_name\n",
    "SET column = new_value\n",
    "WHERE condition\n",
    "'''\n",
    "cursor.execute(update_query)\n",
    "conn.commit()\n",
    "\n",
    "# Query 18: Delete data\n",
    "\n",
    " with condition\n",
    "delete_query = '''\n",
    "DELETE FROM your_table_name\n",
    "WHERE condition\n",
    "'''\n",
    "cursor.execute(delete_query)\n",
    "conn.commit()\n",
    "\n",
    "# Query 19: Insert multiple records into the table\n",
    "insert_query = '''\n",
    "INSERT INTO your_table_name (column1, column2, ...)\n",
    "VALUES (%s, %s, ...), (%s, %s, ...), ...\n",
    "'''\n",
    "values = [\n",
    "    (value1, value2, ...),\n",
    "    (value1, value2, ...),\n",
    "    ...\n",
    "]\n",
    "cursor.executemany(insert_query, values)\n",
    "conn.commit()\n",
    "\n",
    "# Query 20: Select the maximum value from a column\n",
    "select_max_query = '''\n",
    "SELECT MAX(column) FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_max_query)\n",
    "\n",
    "# Query 21: Select the minimum value from a column\n",
    "select_min_query = '''\n",
    "SELECT MIN(column) FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_min_query)\n",
    "\n",
    "# Query 22: Count the number of rows in a table\n",
    "count_query = '''\n",
    "SELECT COUNT(*) FROM your_table_name\n",
    "'''\n",
    "cursor.execute(count_query)\n",
    "\n",
    "# Query 23: Select data with IN operator\n",
    "select_in_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column IN (value1, value2, ...)\n",
    "'''\n",
    "cursor.execute(select_in_query)\n",
    "\n",
    "# Query 24: Select data with NOT IN operator\n",
    "select_not_in_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column NOT IN (value1, value2, ...)\n",
    "'''\n",
    "cursor.execute(select_not_in_query)\n",
    "\n",
    "# Query 25: Select data with LIKE operator\n",
    "select_like_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column LIKE '%value%'\n",
    "'''\n",
    "cursor.execute(select_like_query)\n",
    "\n",
    "# Query 26: Select distinct values from a column\n",
    "select_distinct_query = '''\n",
    "SELECT DISTINCT column FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_distinct_query)\n",
    "\n",
    "# Query 27: Select data with BETWEEN operator\n",
    "select_between_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column BETWEEN value1 AND value2\n",
    "'''\n",
    "cursor.execute(select_between_query)\n",
    "\n",
    "# Query 28: Select data with IS NULL\n",
    "select_null_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column IS NULL\n",
    "'''\n",
    "cursor.execute(select_null_query)\n",
    "\n",
    "# Query 29: Select data with IS NOT NULL\n",
    "select_not_null_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column IS NOT NULL\n",
    "'''\n",
    "cursor.execute(select_not_null_query)\n",
    "\n",
    "# Query 30: Select data with multiple conditions (AND)\n",
    "select_and_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE condition1 AND condition2\n",
    "'''\n",
    "cursor.execute(select_and_query)\n",
    "\n",
    "# Query 31: Select data with multiple conditions (OR)\n",
    "select_or_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE condition1 OR condition2\n",
    "'''\n",
    "cursor.execute(select_or_query)\n",
    "\n",
    "# Query 32: Select data with IN operator\n",
    "select_in_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column IN (value1, value2, ...)\n",
    "'''\n",
    "cursor.execute(select_in_query)\n",
    "\n",
    "# Query 33: Select data with NOT IN operator\n",
    "select_not_in_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column NOT IN (value1, value2, ...)\n",
    "'''\n",
    "cursor.execute(select_not_in_query)\n",
    "\n",
    "# Query 34: Select data with LIKE operator\n",
    "select_like_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column LIKE '%value%'\n",
    "'''\n",
    "cursor.execute(select_like_query)\n",
    "\n",
    "# Query 35: Select data with REGEXP operator\n",
    "select_regexp_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column REGEXP 'pattern'\n",
    "'''\n",
    "cursor.execute(select_regexp_query)\n",
    "\n",
    "# Query 36: Select calculated columns\n",
    "select_calculated_query = '''\n",
    "SELECT column1 + column2 AS sum FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_calculated_query)\n",
    "\n",
    "# Query 37: Select data with GROUP BY\n",
    "\n",
    "\n",
    "select_group_by_query = '''\n",
    "SELECT column, aggregate_function(column)\n",
    "FROM your_table_name\n",
    "GROUP BY column\n",
    "'''\n",
    "cursor.execute(select_group_by_query)\n",
    "\n",
    "# Query 38: Select data with subquery in WHERE clause\n",
    "select_subquery_query = '''\n",
    "SELECT * FROM your_table_name\n",
    "WHERE column IN (\n",
    "    SELECT column FROM another_table\n",
    "    WHERE condition\n",
    ")\n",
    "'''\n",
    "cursor.execute(select_subquery_query)\n",
    "\n",
    "# Query 39: Select data with EXISTS operator\n",
    "select_exists_query = '''\n",
    "SELECT * FROM your_table_name t1\n",
    "WHERE EXISTS (\n",
    "    SELECT * FROM another_table t2\n",
    "    WHERE t1.column = t2.column\n",
    ")\n",
    "'''\n",
    "cursor.execute(select_exists_query)\n",
    "\n",
    "# Query 40: Select data with self-join\n",
    "select_self_join_query = '''\n",
    "SELECT t1.column, t2.column\n",
    "FROM your_table_name t1\n",
    "JOIN your_table_name t2 ON t1.id = t2.id\n",
    "'''\n",
    "cursor.execute(select_self_join_query)\n",
    "\n",
    "# Query 41: Select data with GROUP BY and HAVING\n",
    "select_having_query = '''\n",
    "SELECT column, aggregate_function(column)\n",
    "FROM your_table_name\n",
    "GROUP BY column\n",
    "HAVING condition\n",
    "'''\n",
    "cursor.execute(select_having_query)\n",
    "\n",
    "# Query 42: Update data with subquery in SET clause\n",
    "update_subquery_query = '''\n",
    "UPDATE your_table_name\n",
    "SET column = (\n",
    "    SELECT column FROM another_table\n",
    "    WHERE condition\n",
    ")\n",
    "WHERE condition\n",
    "'''\n",
    "cursor.execute(update_subquery_query)\n",
    "conn.commit()\n",
    "\n",
    "# Query 43: Delete data with subquery in WHERE clause\n",
    "delete_subquery_query = '''\n",
    "DELETE FROM your_table_name\n",
    "WHERE column IN (\n",
    "    SELECT column FROM another_table\n",
    "    WHERE condition\n",
    ")\n",
    "'''\n",
    "cursor.execute(delete_subquery_query)\n",
    "conn.commit()\n",
    "\n",
    "# Query 44: Cross Join\n",
    "cross_join_query = '''\n",
    "SELECT * FROM table1\n",
    "CROSS JOIN table2\n",
    "'''\n",
    "cursor.execute(cross_join_query)\n",
    "\n",
    "# Query 45: Group_concat with GROUP BY\n",
    "group_concat_query = '''\n",
    "SELECT column, GROUP_CONCAT(another_column)\n",
    "FROM your_table_name\n",
    "GROUP BY column\n",
    "'''\n",
    "cursor.execute(group_concat_query)\n",
    "\n",
    "# Query 46: Left Join with WHERE clause\n",
    "left_join_query = '''\n",
    "SELECT t1.column1, t2.column2\n",
    "FROM table1 t1\n",
    "LEFT JOIN table2 t2 ON t1.id = t2.id\n",
    "WHERE condition\n",
    "'''\n",
    "cursor.execute(left_join_query)\n",
    "\n",
    "# Query 47: Conditional expression in SELECT\n",
    "select_conditional_query = '''\n",
    "SELECT column,\n",
    "    CASE\n",
    "        WHEN condition1 THEN value1\n",
    "        WHEN condition2 THEN value2\n",
    "        ELSE value3\n",
    "    END\n",
    "FROM your_table_name\n",
    "'''\n",
    "cursor.execute(select_conditional_query)\n",
    "\n",
    "# Query 48: Window function with PARTITION BY\n",
    "window_function_query = '''\n",
    "SELECT column, aggregate_function(column) OVER (PARTITION BY partition_column)\n",
    "FROM your_table_name\n",
    "'''\n",
    "cursor.execute(window_function_query)\n",
    "\n",
    "# Query 49: Union\n",
    "union_query = '''\n",
    "SELECT column1 FROM table1\n",
    "UNION\n",
    "SELECT column2 FROM table2\n",
    "'''\n",
    "cursor.execute(union_query)\n",
    "\n",
    "# Query 50: EXISTS with subquery\n",
    "exists_subquery_query = '''\n",
    "SELECT * FROM your_table_name t1\n",
    "WHERE EXISTS (\n",
    "    SELECT * FROM another_table t2\n",
    "    WHERE t1.column = t2.column\n",
    ")\n",
    "'''\n",
    "cursor.execute(exists_subquery_query)\n",
    "\n",
    "# Query 51: Group by with HAVING and COUNT\n",
    "group_by_having_query = '''\n",
    "SELECT column, COUNT(*) FROM your_table_name\n",
    "GROUP BY column\n",
    "HAVING COUNT(*) > 1\n",
    "'''\n",
    "cursor.execute(group_by_having_query)\n",
    "\n",
    "# Query 52: Self-join\n",
    "self_join_query = '''\n",
    "SELECT t1.column, t2.column\n",
    "FROM your_table_name t1\n",
    "JOIN your_table_name t2 ON t1.id\n",
    "\n",
    " = t2.id\n",
    "'''\n",
    "cursor.execute(self_join_query)\n",
    "\n",
    "# Query 53: Window function with PARTITION BY and ORDER BY\n",
    "window_function_order_query = '''\n",
    "SELECT column, aggregate_function(column) OVER (PARTITION BY partition_column ORDER BY column)\n",
    "FROM your_table_name\n",
    "'''\n",
    "cursor.execute(window_function_order_query)\n",
    "\n",
    "# Query 54: Cross Apply\n",
    "cross_apply_query = '''\n",
    "SELECT *\n",
    "FROM table1\n",
    "CROSS APPLY table2\n",
    "'''\n",
    "cursor.execute(cross_apply_query)\n",
    "\n",
    "# Query 55: Full Outer Join\n",
    "full_outer_join_query = '''\n",
    "SELECT *\n",
    "FROM table1\n",
    "FULL OUTER JOIN table2 ON table1.id = table2.id\n",
    "'''\n",
    "cursor.execute(full_outer_join_query)\n",
    "\n",
    "# Fetch and print the results\n",
    "results = cursor.fetchall()\n",
    "for row in results:\n",
    "    print(row)\n",
    "\n",
    "# Close the cursor and connection\n",
    "cursor.close()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73d88793",
   "metadata": {},
   "source": [
    "###  `By yadavnikhilrao`"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}