Skip to content

Commit 7dc9ca6

Browse files
NishantGhanateNishantGhanate
committed
Dbms addded
1 parent 404037d commit 7dc9ca6

File tree

4 files changed

+672
-0
lines changed

4 files changed

+672
-0
lines changed

‎SQL/.ipynb_checkpoints/Dbms-checkpoint.ipynb‎

Lines changed: 333 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,333 @@
1+
{
2+
"cells": [
3+
{
4+
"cell_type": "code",
5+
"execution_count": 2,
6+
"metadata": {},
7+
"outputs": [],
8+
"source": [
9+
"# importing module\n",
10+
"import sqlite3\n",
11+
"import os"
12+
]
13+
},
14+
{
15+
"cell_type": "code",
16+
"execution_count": 6,
17+
"metadata": {},
18+
"outputs": [],
19+
"source": [
20+
"# current working Folder\n",
21+
"scriptDir = os.path.dirname(os.path.realpath('__file__'))\n",
22+
"# connecting to the database \n",
23+
"connection = sqlite3.connect(scriptDir + os.path.sep + \"myTable.db\")\n",
24+
"# cursor \n",
25+
"crsr = connection.cursor()\n",
26+
"\n",
27+
"# Q(1 & 3). Create Table & apply constraint NOT NULL & UNIQUE\n",
28+
"sql_command = \"\"\" CREATE TABLE Subjects (\n",
29+
" ID INTEGER PRIMARY KEY AUTOINCREMENT, \n",
30+
" SubjectName varchar(255) NOT NULL UNIQUE,\n",
31+
" SubjectHours varchar(255) NOT NULL\n",
32+
"); \"\"\"\n",
33+
"\n",
34+
"# execute the statement\n",
35+
"crsr.execute(sql_command)\n",
36+
"# To save the changes in the files. Never skip this. \n",
37+
"# If we skip this, nothing will be saved in the database.\n",
38+
"connection.commit()\n"
39+
]
40+
},
41+
{
42+
"cell_type": "code",
43+
"execution_count": 7,
44+
"metadata": {},
45+
"outputs": [],
46+
"source": [
47+
"sql_command = \"\"\"INSERT INTO Subjects VALUES (NULL,\"Machine Learning\", \"100\");\"\"\"\n",
48+
"crsr.execute(sql_command)\n",
49+
"connection.commit()"
50+
]
51+
},
52+
{
53+
"cell_type": "code",
54+
"execution_count": 8,
55+
"metadata": {},
56+
"outputs": [
57+
{
58+
"name": "stdout",
59+
"output_type": "stream",
60+
"text": [
61+
"(1, 'Machine Learning', '100')\n"
62+
]
63+
}
64+
],
65+
"source": [
66+
"crsr.execute(\"SELECT * FROM Subjects\") \n",
67+
" \n",
68+
"# store all the fetched data in the ans variable\n",
69+
"ans = crsr.fetchall() \n",
70+
" \n",
71+
"# loop to print all the data\n",
72+
"for i in ans:\n",
73+
" print(i)"
74+
]
75+
},
76+
{
77+
"cell_type": "code",
78+
"execution_count": 9,
79+
"metadata": {},
80+
"outputs": [],
81+
"source": [
82+
"#(Q.4) Alter already existing table name\n",
83+
"\n",
84+
"sql_command = \"\"\" Alter table Subjects rename to Subjects_details; \"\"\"\n",
85+
"crsr.execute(sql_command)\n",
86+
"connection.commit()\n"
87+
]
88+
},
89+
{
90+
"cell_type": "code",
91+
"execution_count": 10,
92+
"metadata": {},
93+
"outputs": [
94+
{
95+
"name": "stdout",
96+
"output_type": "stream",
97+
"text": [
98+
"(1, 'Machine Learning', '100')\n"
99+
]
100+
}
101+
],
102+
"source": [
103+
"crsr.execute(\"SELECT * FROM Subjects_details\") \n",
104+
"ans = crsr.fetchall() \n",
105+
"for i in ans:\n",
106+
" print(i)"
107+
]
108+
},
109+
{
110+
"cell_type": "code",
111+
"execution_count": 11,
112+
"metadata": {},
113+
"outputs": [],
114+
"source": [
115+
"#(Q.4) Update using where clause\n",
116+
"\n",
117+
"sql_command = \"\"\" UPDATE Subjects_details SET SubjectHours = '120' WHERE SubjectName = 'Machine Learning' ; \"\"\"\n",
118+
"crsr.execute(sql_command)\n",
119+
"connection.commit()\n"
120+
]
121+
},
122+
{
123+
"cell_type": "code",
124+
"execution_count": 12,
125+
"metadata": {},
126+
"outputs": [
127+
{
128+
"name": "stdout",
129+
"output_type": "stream",
130+
"text": [
131+
"(1, 'Machine Learning', '120')\n"
132+
]
133+
}
134+
],
135+
"source": [
136+
"crsr.execute(\"SELECT * FROM Subjects_details\") \n",
137+
"ans = crsr.fetchall() \n",
138+
"for i in ans:\n",
139+
" print(i)"
140+
]
141+
},
142+
{
143+
"cell_type": "code",
144+
"execution_count": 14,
145+
"metadata": {},
146+
"outputs": [],
147+
"source": [
148+
"sql_command = \"\"\"INSERT INTO Subjects_details VALUES (NULL,\"Network Security\", \"80\");\"\"\"\n",
149+
"crsr.execute(sql_command)\n",
150+
"\n",
151+
"sql_command = \"\"\"INSERT INTO Subjects_details VALUES (NULL,\"Python Programming\", \"90\");\"\"\"\n",
152+
"crsr.execute(sql_command)\n",
153+
"\n",
154+
"sql_command = \"\"\"INSERT INTO Subjects_details VALUES (NULL,\"Software Enterprise\", \"50\");\"\"\"\n",
155+
"crsr.execute(sql_command)\n",
156+
"\n",
157+
"sql_command = \"\"\"INSERT INTO Subjects_details VALUES (NULL,\"API\", \"50\");\"\"\"\n",
158+
"crsr.execute(sql_command)\n",
159+
"\n",
160+
"connection.commit()\n"
161+
]
162+
},
163+
{
164+
"cell_type": "code",
165+
"execution_count": 15,
166+
"metadata": {},
167+
"outputs": [
168+
{
169+
"name": "stdout",
170+
"output_type": "stream",
171+
"text": [
172+
"(1, 'Machine Learning', '120')\n",
173+
"(2, 'Network Security', '80')\n",
174+
"(3, 'Python Programming', '90')\n",
175+
"(4, 'Software Enterprise', '50')\n",
176+
"(5, 'API', '50')\n"
177+
]
178+
}
179+
],
180+
"source": [
181+
"crsr.execute(\"SELECT * FROM Subjects_details\") \n",
182+
"ans = crsr.fetchall() \n",
183+
"for i in ans:\n",
184+
" print(i)"
185+
]
186+
},
187+
{
188+
"cell_type": "code",
189+
"execution_count": 22,
190+
"metadata": {},
191+
"outputs": [
192+
{
193+
"name": "stdout",
194+
"output_type": "stream",
195+
"text": [
196+
"(1, 'Machine Learning', '120')\n",
197+
"(4, 'Software Enterprise', '50')\n",
198+
"(5, 'API', '50')\n"
199+
]
200+
}
201+
],
202+
"source": [
203+
"# (Q.7) Create Views'\n",
204+
"\n",
205+
"crsr.execute ( \"\"\" \n",
206+
"\n",
207+
"CREATE VIEW [Subject_H] AS\n",
208+
"SELECT ID, SubjectName , SubjectHours\n",
209+
"FROM Subjects_details\n",
210+
"WHERE SubjectHours < '80' ; \"\"\")\n",
211+
"\n",
212+
"crsr.execute(\" SELECT * FROM [Subject_H]; \") \n",
213+
"\n",
214+
"\n",
215+
"ans = crsr.fetchall() \n",
216+
"for i in ans:\n",
217+
" print(i)\n",
218+
"\n"
219+
]
220+
},
221+
{
222+
"cell_type": "code",
223+
"execution_count": 23,
224+
"metadata": {},
225+
"outputs": [
226+
{
227+
"name": "stdout",
228+
"output_type": "stream",
229+
"text": [
230+
"(5, 'API', '50')\n",
231+
"(1, 'Machine Learning', '120')\n",
232+
"(2, 'Network Security', '80')\n",
233+
"(3, 'Python Programming', '90')\n",
234+
"(4, 'Software Enterprise', '50')\n"
235+
]
236+
}
237+
],
238+
"source": [
239+
"# (Q.8) ORDER BY , ASC|DESC\n",
240+
"\n",
241+
"crsr.execute(\"SELECT * FROM Subjects_details ORDER BY SubjectName ASC\")\n",
242+
"ans = crsr.fetchall() \n",
243+
"for i in ans:\n",
244+
" print(i)"
245+
]
246+
},
247+
{
248+
"cell_type": "code",
249+
"execution_count": 26,
250+
"metadata": {},
251+
"outputs": [
252+
{
253+
"name": "stdout",
254+
"output_type": "stream",
255+
"text": [
256+
"(4, 'Software Enterprise', '50')\n",
257+
"(3, 'Python Programming', '90')\n",
258+
"(2, 'Network Security', '80')\n",
259+
"(1, 'Machine Learning', '120')\n",
260+
"(5, 'API', '50')\n"
261+
]
262+
}
263+
],
264+
"source": [
265+
"crsr.execute(\"SELECT * FROM Subjects_details ORDER BY SubjectName DESC\")\n",
266+
"ans = crsr.fetchall() \n",
267+
"for i in ans:\n",
268+
" print(i)"
269+
]
270+
},
271+
{
272+
"cell_type": "code",
273+
"execution_count": 27,
274+
"metadata": {
275+
"scrolled": true
276+
},
277+
"outputs": [
278+
{
279+
"name": "stdout",
280+
"output_type": "stream",
281+
"text": [
282+
"(1, 'API')\n",
283+
"(1, 'Machine Learning')\n",
284+
"(1, 'Network Security')\n",
285+
"(1, 'Python Programming')\n",
286+
"(1, 'Software Enterprise')\n"
287+
]
288+
}
289+
],
290+
"source": [
291+
"# (Q.9) GROUP BY \n",
292+
"# The GROUP BY statement is often used with aggregate functions\n",
293+
"# (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.\n",
294+
"\n",
295+
"crsr.execute(\"SELECT COUNT(SubjectHours) , SubjectName FROM Subjects_details GROUP BY SubjectName\")\n",
296+
"ans = crsr.fetchall() \n",
297+
"for i in ans:\n",
298+
" print(i)"
299+
]
300+
},
301+
{
302+
"cell_type": "code",
303+
"execution_count": null,
304+
"metadata": {},
305+
"outputs": [],
306+
"source": [
307+
"\n",
308+
"\n"
309+
]
310+
}
311+
],
312+
"metadata": {
313+
"kernelspec": {
314+
"display_name": "Python 3",
315+
"language": "python",
316+
"name": "python3"
317+
},
318+
"language_info": {
319+
"codemirror_mode": {
320+
"name": "ipython",
321+
"version": 3
322+
},
323+
"file_extension": ".py",
324+
"mimetype": "text/x-python",
325+
"name": "python",
326+
"nbconvert_exporter": "python",
327+
"pygments_lexer": "ipython3",
328+
"version": "3.6.5"
329+
}
330+
},
331+
"nbformat": 4,
332+
"nbformat_minor": 2
333+
}

0 commit comments

Comments
 (0)