Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
{
"cells": [
{
"cell_type": "markdown",
"id": "12245d8b",
"metadata": {},
"source": [
"# 1.Create a database called temp_db. Open a cursor to this newly created database.\n"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "9ed5138c",
"metadata": {},
"outputs": [],
"source": [
"import sqlite3"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "18c8a92e",
"metadata": {},
"outputs": [],
"source": [
"con=sqlite3.connect('temp.db')"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "ef23ba56",
"metadata": {},
"outputs": [],
"source": [
"cur=con.cursor()"
]
},
{
"cell_type": "markdown",
"id": "cce1047c",
"metadata": {},
"source": [
"# 2.In the database temp_db created in Q.No.1, create a table called emp with the attributes empno, ename, salary and deptno\\\n"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "f3b266a9",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<sqlite3.Cursor at 0x28748138420>"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cur.execute('create table if not exists emp(empno INTEGER,ename TEXT,salary INTEGER,deptno INTEGER)')"
]
},
{
"cell_type": "markdown",
"id": "cb3ef104",
"metadata": {},
"source": [
"# 3.In the emp table created in Q.No. 2, insert any five records.\n"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "8a5ba19c",
"metadata": {},
"outputs": [],
"source": [
"def insert(empno,ename,salary,deptno):\n",
" cur.execute('insert into emp values(?,?,?,?)',(empno,ename,salary,deptno))\n",
" con.commit()\n"
]
},
{
"cell_type": "code",
"execution_count": 35,
"id": "bc821dcd",
"metadata": {},
"outputs": [],
"source": [
"insert(1,'Ramesh',15000,10)\n",
"insert(2,'Mahesh',20000,20)\n",
"insert(3,'Suresh',25000,30)\n",
"insert(4,'Rajeev',30000,40)\n",
"insert(5,'Rakesh',35000,50)"
]
},
{
"cell_type": "markdown",
"id": "e2834c9d",
"metadata": {},
"source": [
"# 4.From the emp table, display all the records.\n"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "2c68ffaa",
"metadata": {},
"outputs": [],
"source": [
"def view():\n",
" con=sqlite3.connect('temp.db')\n",
" cur=con.cursor()\n",
" cur.execute('select * from emp')\n",
" rows=cur.fetchall()\n",
" con.close()\n",
" return rows"
]
},
{
"cell_type": "code",
"execution_count": 37,
"id": "ef88c786",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[(1, 'Ramesh', 15000, 10),\n",
" (2, 'Mahesh', 20000, 20),\n",
" (3, 'Suresh', 25000, 30),\n",
" (4, 'Rajeev', 30000, 40),\n",
" (5, 'Rakesh', 35000, 50)]"
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"view()"
]
},
{
"cell_type": "markdown",
"id": "e7839e1c",
"metadata": {},
"source": [
"# 5.From the emp table, display the records of only those employees whose salary is greater than 15000 and who belong to deptno 10\n"
]
},
{
"cell_type": "code",
"execution_count": 38,
"id": "e2c1af16",
"metadata": {},
"outputs": [],
"source": [
"def view1():\n",
" con=sqlite3.connect('temp.db')\n",
" cur=con.cursor()\n",
" cur.execute('select * from emp where salary>15000 and deptno==10')\n",
" rows=cur.fetchall()\n",
" con.close()\n",
" return rows"
]
},
{
"cell_type": "code",
"execution_count": 39,
"id": "def61175",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"view1()"
]
},
{
"cell_type": "markdown",
"id": "7230fa91",
"metadata": {},
"source": [
"# 6.From the emp table, display the records of only those employees whose salary lies between 20000 and 25000 and who do not belong to the deptno 10\n"
]
},
{
"cell_type": "code",
"execution_count": 41,
"id": "3f8f96a9",
"metadata": {},
"outputs": [],
"source": [
"def view2():\n",
" con=sqlite3.connect('temp.db')\n",
" cur=con.cursor()\n",
" cur.execute('select * from emp where salary>=20000 and salary<=25000 and deptno!=10')\n",
" rows=cur.fetchall()\n",
" con.close()\n",
" return rows"
]
},
{
"cell_type": "code",
"execution_count": 43,
"id": "960e4cf4",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[(2, 'Mahesh', 20000, 20), (3, 'Suresh', 25000, 30)]"
]
},
"execution_count": 43,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"view2()"
]
},
{
"cell_type": "markdown",
"id": "2da73d21",
"metadata": {},
"source": [
"# 7.From the emp table, display the records of only those employees whose name begins with M.\n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "1835ec4e",
"metadata": {},
"outputs": [],
"source": [
"def view3():\n",
" con=sqlite3.connect('temp.db')\n",
" cur=con.cursor()\n",
" cur.execute('select * from emp WHERE ename LIKE M%')\n",
" rows=cur.fetchall()\n",
" con.close()\n",
" return rows"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "5cc5582f",
"metadata": {},
"outputs": [
{
"ename": "NameError",
"evalue": "name 'sqlite3' is not defined",
"output_type": "error",
"traceback": [
"\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[1;31mNameError\u001b[0m Traceback (most recent call last)",
"\u001b[1;32m<ipython-input-2-67eb3e508396>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mview3\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
"\u001b[1;32m<ipython-input-1-32d169362967>\u001b[0m in \u001b[0;36mview3\u001b[1;34m()\u001b[0m\n\u001b[0;32m 1\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mview3\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mcon\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msqlite3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'temp.db'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m 3\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcon\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 4\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'select * from emp WHERE ename LIKE M%'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m 5\u001b[0m \u001b[0mrows\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
"\u001b[1;31mNameError\u001b[0m: name 'sqlite3' is not defined"
]
}
],
"source": [
"view3()"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "8bde6574",
"metadata": {},
"outputs": [
{
"ename": "SyntaxError",
"evalue": "invalid syntax (<ipython-input-4-fe8b8d82c6d4>, line 1)",
"output_type": "error",
"traceback": [
"\u001b[1;36m File \u001b[1;32m\"<ipython-input-4-fe8b8d82c6d4>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m SELECT * FROM emp WHERE NAME LIKE 'H%';\u001b[0m\n\u001b[1;37m ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
]
}
],
"source": [
" SELECT * FROM emp WHERE NAME LIKE 'H%';"
]
},
{
"cell_type": "markdown",
"id": "640ad39c",
"metadata": {},
"source": [
"# 8.In the emp table, delete the records of those employees who belong to deptno 30\n"
]
},
{
"cell_type": "code",
"execution_count": 52,
"id": "7abaad15",
"metadata": {},
"outputs": [],
"source": [
"def view4():\n",
" con=sqlite3.connect('temp.db')\n",
" cur=con.cursor()\n",
" cur.execute('delete from emp where deptno==30')\n",
" rows=cur.fetchall()\n",
" con.close()\n",
" return rows"
]
},
{
"cell_type": "code",
"execution_count": 53,
"id": "87e818a7",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 53,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"view4()"
]
},
{
"cell_type": "markdown",
"id": "1e4cb06b",
"metadata": {},
"source": [
"# 9.In the emp table, update the salary of an employee by 10 % by accepting the empno from the user.\n"
]
},
{
"cell_type": "code",
"execution_count": 60,
"id": "1d43b9af",
"metadata": {},
"outputs": [],
"source": [
"def update(g):\n",
" con=sqlite3.connect(\"temp.db\")\n",
" cur=con.cursor()\n",
" cur.execute(\"update emp set salary=salary+(salary*10/100) where empno=?\",(g,))\n",
" con.commit()\n",
" con.close()"
]
},
{
"cell_type": "code",
"execution_count": 61,
"id": "164f3b69",
"metadata": {},
"outputs": [],
"source": [
"update(1)"
]
},
{
"cell_type": "code",
"execution_count": 62,
"id": "4de31fac",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[(1, 'Ramesh', 16500, 10),\n",
" (2, 'Mahesh', 20000, 20),\n",
" (3, 'Suresh', 25000, 30),\n",
" (4, 'Rajeev', 30000, 40),\n",
" (5, 'Rakesh', 35000, 50)]"
]
},
"execution_count": 62,
"metadata": {},
"output_type": "execute_result"
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"Exception in callback BaseSelectorEventLoop._read_from_self()\n",
"handle: <Handle BaseSelectorEventLoop._read_from_self()>\n",
"Traceback (most recent call last):\n",
" File \"C:\\Users\\ayyan\\anaconda3\\lib\\asyncio\\events.py\", line 80, in _run\n",
" self._context.run(self._callback, *self._args)\n",
" File \"C:\\Users\\ayyan\\anaconda3\\lib\\asyncio\\selector_events.py\", line 120, in _read_from_self\n",
" data = self._ssock.recv(4096)\n",
"ConnectionResetError: [WinError 10054] An existing connection was forcibly closed by the remote host\n",
"Exception in callback BaseSelectorEventLoop._read_from_self()\n",
"handle: <Handle BaseSelectorEventLoop._read_from_self()>\n",
"Traceback (most recent call last):\n",
" File \"C:\\Users\\ayyan\\anaconda3\\lib\\asyncio\\events.py\", line 80, in _run\n",
" self._context.run(self._callback, *self._args)\n",
" File \"C:\\Users\\ayyan\\anaconda3\\lib\\asyncio\\selector_events.py\", line 120, in _read_from_self\n",
" data = self._ssock.recv(4096)\n",
"ConnectionResetError: [WinError 10054] An existing connection was forcibly closed by the remote host\n"
]
}
],
"source": [
"view()"
]
},
{
"cell_type": "markdown",
"id": "1f4da0ec",
"metadata": {},
"source": [
"# 10.Delete the emp table.\n"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "86625a9e",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<sqlite3.Cursor at 0x215ca5a0570>"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"con=sqlite3.connect(\"temp.db\")\n",
"cur=con.cursor()\n",
"cur.execute('drop table emp')\n"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "4595a9f5",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"view()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "00f37f2a",
"metadata": {},
"outputs": [],
"source": []
}
],
"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.9.12"
}
},
"nbformat": 4,
"nbformat_minor": 5
}