· 9 years ago · Dec 25, 2016, 02:28 AM
1{
2 "cells": [
3 {
4 "cell_type": "code",
5 "execution_count": 1,
6 "metadata": {
7 "collapsed": true
8 },
9 "outputs": [],
10 "source": [
11 "import sqlite3"
12 ]
13 },
14 {
15 "cell_type": "code",
16 "execution_count": 2,
17 "metadata": {
18 "collapsed": false
19 },
20 "outputs": [
21 {
22 "name": "stdout",
23 "output_type": "stream",
24 "text": [
25 "[]\n"
26 ]
27 }
28 ],
29 "source": [
30 "# Create a connection \n",
31 "conn = sqlite3.connect(':memory:')\n",
32 "c = conn.cursor()\n",
33 "c.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
34 "print(c.fetchall())"
35 ]
36 },
37 {
38 "cell_type": "code",
39 "execution_count": 3,
40 "metadata": {
41 "collapsed": false
42 },
43 "outputs": [
44 {
45 "data": {
46 "text/plain": [
47 "<sqlite3.Cursor at 0x7f65d8790810>"
48 ]
49 },
50 "execution_count": 3,
51 "metadata": {},
52 "output_type": "execute_result"
53 }
54 ],
55 "source": [
56 "# Create a Dept table\n",
57 "sql_create_Dept_table = \"\"\"\n",
58 "CREATE TABLE IF NOT EXISTS Dept (\n",
59 " Dno INT NOT NULL,\n",
60 " Dname VARCHAR(15) NOT NULL, \n",
61 " MgrSsn VARCHAR(9) NULL,\n",
62 " MgrStartDate DATETIME,\n",
63 " CONSTRAINT Dept_PK PRIMARY KEY (Dno),\n",
64 " CONSTRAINT Dept_SK UNIQUE (Dname),\n",
65 " CONSTRAINT Dept_FK FOREIGN KEY (MgrSsn) REFERENCES Employee(Ssn)\n",
66 ");\n",
67 "\"\"\"\n",
68 "c.execute(sql_create_Dept_table)"
69 ]
70 },
71 {
72 "cell_type": "code",
73 "execution_count": 4,
74 "metadata": {
75 "collapsed": false
76 },
77 "outputs": [
78 {
79 "data": {
80 "text/plain": [
81 "<sqlite3.Cursor at 0x7f65d8790810>"
82 ]
83 },
84 "execution_count": 4,
85 "metadata": {},
86 "output_type": "execute_result"
87 }
88 ],
89 "source": [
90 "# Create a Employee table\n",
91 "sql_create_Employee_table = \"\"\"\n",
92 "CREATE TABLE IF NOT EXISTS Employee (\n",
93 " Ssn VARCHAR(9) NOT NULL,\n",
94 " Name VARCHAR(15) NOT NULL,\n",
95 " Sex CHAR(1),\n",
96 " Bdate DATETIME,\n",
97 " Address VARCHAR(50),\n",
98 " Salary MONEY,\n",
99 " SuperSsn VARCHAR(9),\n",
100 " Dno INT NOT NULL,\n",
101 " CONSTRAINT Employee_PK PRIMARY KEY (Ssn),\n",
102 " CONSTRAINT Employee_FK1 FOREIGN KEY (SuperSsn) REFERENCES Employee(Ssn),\n",
103 " CONSTRAINT Employee_FK2 FOREIGN KEY (Dno) REFERENCES Dept(Dno) \n",
104 ");\n",
105 "\"\"\"\n",
106 "c.execute(sql_create_Employee_table)\n"
107 ]
108 },
109 {
110 "cell_type": "code",
111 "execution_count": 5,
112 "metadata": {
113 "collapsed": false
114 },
115 "outputs": [
116 {
117 "data": {
118 "text/plain": [
119 "<sqlite3.Cursor at 0x7f65d8790810>"
120 ]
121 },
122 "execution_count": 5,
123 "metadata": {},
124 "output_type": "execute_result"
125 }
126 ],
127 "source": [
128 "# Create a DeptLoc table\n",
129 "sql_create_DeptLoc_table = \"\"\"\n",
130 "CREATE TABLE DeptLoc (\n",
131 " Dno INT NOT NULL,\n",
132 " Dloc VARCHAR(15) NOT NULL, \n",
133 " CONSTRAINT DeptLoc_PK PRIMARY KEY (Dno, Dloc),\n",
134 " CONSTRAINT DeptLoc_FK FOREIGN KEY (Dno) REFERENCES Dept(Dno)\n",
135 ");\n",
136 "\n",
137 "\"\"\"\n",
138 "c.execute(sql_create_DeptLoc_table)"
139 ]
140 },
141 {
142 "cell_type": "code",
143 "execution_count": 6,
144 "metadata": {
145 "collapsed": false
146 },
147 "outputs": [
148 {
149 "data": {
150 "text/plain": [
151 "<sqlite3.Cursor at 0x7f65d8790810>"
152 ]
153 },
154 "execution_count": 6,
155 "metadata": {},
156 "output_type": "execute_result"
157 }
158 ],
159 "source": [
160 "# Create a Proj table\n",
161 "sql_create_Proj_table = \"\"\"\n",
162 "CREATE TABLE Proj (\n",
163 " Pno INT NOT NULL,\n",
164 " Pname VARCHAR(15) NOT NULL, \n",
165 " Ploc VARCHAR(15), \n",
166 " Dno INT NOT NULL,\n",
167 " CONSTRAINT Proj_PK PRIMARY KEY (Pno),\n",
168 " CONSTRAINT Proj_FK FOREIGN KEY (Dno) REFERENCES Dept(Dno)\n",
169 ");\n",
170 "\"\"\"\n",
171 "c.execute(sql_create_Proj_table)"
172 ]
173 },
174 {
175 "cell_type": "code",
176 "execution_count": 7,
177 "metadata": {
178 "collapsed": false
179 },
180 "outputs": [
181 {
182 "data": {
183 "text/plain": [
184 "<sqlite3.Cursor at 0x7f65d8790810>"
185 ]
186 },
187 "execution_count": 7,
188 "metadata": {},
189 "output_type": "execute_result"
190 }
191 ],
192 "source": [
193 "# Create a WorksOn table\n",
194 "sql_create_WorksOn_table = \"\"\"\n",
195 "CREATE TABLE WorksOn (\n",
196 " Ssn VARCHAR(9) NOT NULL,\n",
197 " Pno INT NOT NULL,\n",
198 " Hours Numeric NULL, \n",
199 " CONSTRAINT WorksOn_PK PRIMARY KEY (Ssn, Pno),\n",
200 " CONSTRAINT WorksOn_FK1 FOREIGN KEY (Ssn) REFERENCES Employee(Ssn),\n",
201 " CONSTRAINT WorksOn_FK2 FOREIGN KEY (Pno) REFERENCES Proj(Pno) \n",
202 ");\n",
203 "\"\"\"\n",
204 "c.execute(sql_create_WorksOn_table)"
205 ]
206 },
207 {
208 "cell_type": "code",
209 "execution_count": 8,
210 "metadata": {
211 "collapsed": false
212 },
213 "outputs": [
214 {
215 "data": {
216 "text/plain": [
217 "<sqlite3.Cursor at 0x7f65d8790810>"
218 ]
219 },
220 "execution_count": 8,
221 "metadata": {},
222 "output_type": "execute_result"
223 }
224 ],
225 "source": [
226 "# Create a Dependent table\n",
227 "sql_create_Dependent_table = \"\"\"\n",
228 "CREATE TABLE Dependent (\n",
229 " Ssn VARCHAR(9) NOT NULL,\n",
230 " DependName VARCHAR(15) NOT NULL, \n",
231 " Sex CHAR(1),\n",
232 " Bdate DATETIME,\n",
233 " Relation VARCHAR(8),\n",
234 " CONSTRAINT Dependent_PK PRIMARY KEY (Ssn, DependName),\n",
235 " CONSTRAINT WorksOn_FK FOREIGN KEY (Ssn) REFERENCES Employee(Ssn)\n",
236 ");\n",
237 "\"\"\"\n",
238 "c.execute(sql_create_Dependent_table)"
239 ]
240 },
241 {
242 "cell_type": "code",
243 "execution_count": 9,
244 "metadata": {
245 "collapsed": false
246 },
247 "outputs": [
248 {
249 "data": {
250 "text/plain": [
251 "<sqlite3.Cursor at 0x7f65d8790810>"
252 ]
253 },
254 "execution_count": 9,
255 "metadata": {},
256 "output_type": "execute_result"
257 }
258 ],
259 "source": [
260 "# Create a SetA table\n",
261 "sql_create_SetA_table = \"\"\"\n",
262 "CREATE TABLE SetA (\n",
263 " Id INT NOT NULL, \n",
264 " Name VARCHAR(15) , \n",
265 " CONSTRAINT SetA_PK PRIMARY KEY (Id)\n",
266 ");\n",
267 "\"\"\"\n",
268 "c.execute(sql_create_SetA_table)"
269 ]
270 },
271 {
272 "cell_type": "code",
273 "execution_count": 10,
274 "metadata": {
275 "collapsed": false
276 },
277 "outputs": [
278 {
279 "data": {
280 "text/plain": [
281 "<sqlite3.Cursor at 0x7f65d8790810>"
282 ]
283 },
284 "execution_count": 10,
285 "metadata": {},
286 "output_type": "execute_result"
287 }
288 ],
289 "source": [
290 "# Create a SetB table\n",
291 "sql_create_SetB_table = \"\"\"\n",
292 "CREATE TABLE IF NOT EXISTS SetB (\n",
293 " Id INT NOT NULL, \n",
294 " Name VARCHAR(15) , \n",
295 " CONSTRAINT SetB_PK PRIMARY KEY (Id)\n",
296 ");\n",
297 "\"\"\"\n",
298 "c.execute(sql_create_SetB_table)"
299 ]
300 },
301 {
302 "cell_type": "code",
303 "execution_count": 11,
304 "metadata": {
305 "collapsed": false
306 },
307 "outputs": [
308 {
309 "name": "stdout",
310 "output_type": "stream",
311 "text": [
312 "[(u'Dept',), (u'Employee',), (u'DeptLoc',), (u'Proj',), (u'WorksOn',), (u'Dependent',), (u'SetA',), (u'SetB',)]\n"
313 ]
314 }
315 ],
316 "source": [
317 "# Show all tables\n",
318 "c.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
319 "print(c.fetchall())"
320 ]
321 },
322 {
323 "cell_type": "code",
324 "execution_count": 12,
325 "metadata": {
326 "collapsed": false
327 },
328 "outputs": [
329 {
330 "data": {
331 "text/plain": [
332 "<sqlite3.Cursor at 0x7f65d8790810>"
333 ]
334 },
335 "execution_count": 12,
336 "metadata": {},
337 "output_type": "execute_result"
338 }
339 ],
340 "source": [
341 "# Insert records to SetA table\n",
342 "sql_insert_seta = \"\"\"INSERT INTO SetA ('Id', 'Name') VALUES\n",
343 "(01,'Frank'),\n",
344 "(03,'James'),\n",
345 "(04,'Lu'),\n",
346 "(05,'Lin'),\n",
347 "(06,'Lee'),\n",
348 "(07,'Allics');\"\"\"\n",
349 "c.execute(sql_insert_seta)"
350 ]
351 },
352 {
353 "cell_type": "code",
354 "execution_count": 13,
355 "metadata": {
356 "collapsed": false
357 },
358 "outputs": [
359 {
360 "data": {
361 "text/plain": [
362 "<sqlite3.Cursor at 0x7f65d8790810>"
363 ]
364 },
365 "execution_count": 13,
366 "metadata": {},
367 "output_type": "execute_result"
368 }
369 ],
370 "source": [
371 "# Insert records to SetB table\n",
372 "sql_insert_setb = \"\"\"\n",
373 "INSERT INTO SetB ('Id', 'Name') VALUES \n",
374 "(02,'Marry'),\n",
375 "(04,'Lu'),\n",
376 "(05,'Chang'),\n",
377 "(06,'Lee'),\n",
378 "(08,'Peter');\n",
379 "\"\"\"\n",
380 "c.execute(sql_insert_setb)"
381 ]
382 },
383 {
384 "cell_type": "code",
385 "execution_count": 14,
386 "metadata": {
387 "collapsed": false
388 },
389 "outputs": [
390 {
391 "data": {
392 "text/plain": [
393 "<sqlite3.Cursor at 0x7f65d8790810>"
394 ]
395 },
396 "execution_count": 14,
397 "metadata": {},
398 "output_type": "execute_result"
399 }
400 ],
401 "source": [
402 "# Insert records to Dept table\n",
403 "sql_insert_Dept= \"\"\"INSERT INTO Dept(Dno, Dname) VALUES\n",
404 "(1,'Head'),\n",
405 "(4,'Admin'),\n",
406 "(5,'R&D');\n",
407 "\"\"\"\n",
408 "c.execute(sql_insert_Dept)"
409 ]
410 },
411 {
412 "cell_type": "code",
413 "execution_count": 15,
414 "metadata": {
415 "collapsed": false
416 },
417 "outputs": [
418 {
419 "data": {
420 "text/plain": [
421 "<sqlite3.Cursor at 0x7f65d8790810>"
422 ]
423 },
424 "execution_count": 15,
425 "metadata": {},
426 "output_type": "execute_result"
427 }
428 ],
429 "source": [
430 "# Insert records to Employee table\n",
431 "sql_insert_emplyee = \"\"\"\n",
432 "INSERT INTO Employee VALUES ('100','James' ,'M','1937/11/10','450 Stone' ,55000,NULL ,1),\n",
433 "('400','Jennifer','F','1941/06/20','291 Berry' ,43000,'100',4),\n",
434 "('500','Frank' ,'M','1955/12/08','638 Voss' ,40000,'100',5),\n",
435 "('401','Alicia' ,'F','1968/01/19','332 Castle',25000,'400',4),\n",
436 "('402','Ahmad' ,'M','1969/03/29','980 Dallas',25000,'400',4),\n",
437 "('501','John' ,'M','1965/01/09','731 Fond' ,30000,'500',5),\n",
438 "('502','Ramesh' ,'M','1962/08/09','123 Fire' ,38000,'500',5),\n",
439 "('503','Joyce' ,'F','1972/07/08','452 Rice' ,27000,'500',5);\"\"\"\n",
440 "c.execute(sql_insert_emplyee)"
441 ]
442 },
443 {
444 "cell_type": "code",
445 "execution_count": 16,
446 "metadata": {
447 "collapsed": false
448 },
449 "outputs": [
450 {
451 "data": {
452 "text/plain": [
453 "<sqlite3.Cursor at 0x7f65d8790810>"
454 ]
455 },
456 "execution_count": 16,
457 "metadata": {},
458 "output_type": "execute_result"
459 }
460 ],
461 "source": [
462 "# Insert records to DeptLoc table\n",
463 "sql_insert_DeptLoc = \"\"\"\n",
464 "INSERT INTO DeptLoc VALUES \n",
465 "(1,'Houston'),\n",
466 "(4,'Stafford'),\n",
467 "(5,'Bellaire'),\n",
468 "(5,'Sugarland'),\n",
469 "(5,'Houston');\"\"\"\n",
470 "c.execute(sql_insert_DeptLoc)"
471 ]
472 },
473 {
474 "cell_type": "code",
475 "execution_count": 17,
476 "metadata": {
477 "collapsed": false
478 },
479 "outputs": [
480 {
481 "data": {
482 "text/plain": [
483 "<sqlite3.Cursor at 0x7f65d8790810>"
484 ]
485 },
486 "execution_count": 17,
487 "metadata": {},
488 "output_type": "execute_result"
489 }
490 ],
491 "source": [
492 "# Insert records to Proj table\n",
493 "sql_insert_proj = \"\"\"\n",
494 "INSERT INTO Proj VALUES\n",
495 "(1,'ProductX','Bellaire' ,5),\n",
496 "(2,'ProductY','Sugarland',5),\n",
497 "(3,'ProductZ','Houston' ,5),\n",
498 "(10,'iPhone' ,'Stafford' ,4),\n",
499 "(20,'BaceMan','Houston' ,1),\n",
500 "(30,'MotoAny','Stafford' ,4);\n",
501 "\"\"\"\n",
502 "c.execute(sql_insert_proj)"
503 ]
504 },
505 {
506 "cell_type": "code",
507 "execution_count": 18,
508 "metadata": {
509 "collapsed": false
510 },
511 "outputs": [
512 {
513 "data": {
514 "text/plain": [
515 "<sqlite3.Cursor at 0x7f65d8790810>"
516 ]
517 },
518 "execution_count": 18,
519 "metadata": {},
520 "output_type": "execute_result"
521 }
522 ],
523 "source": [
524 "# Insert records to WorksOn table\n",
525 "sql_insert_workson = \"\"\"\n",
526 "INSERT INTO WorksOn VALUES\n",
527 "('501', 1,32.5),\n",
528 "('501', 2,7.5),\n",
529 "('502', 3,40),\n",
530 "('503', 1,20),\n",
531 "('503', 2,20),\n",
532 "('500', 1,10),\n",
533 "('500', 2,10),\n",
534 "('500', 3,10),\n",
535 "('500',10,10),\n",
536 "('500',20,10),\n",
537 "('402',10,35),\n",
538 "('402',30,5),\n",
539 "('401',30,30),\n",
540 "('401',10,10),\n",
541 "('400',30,20),\n",
542 "('400',20,15),\n",
543 "('100',20,null);\n",
544 "\"\"\"\n",
545 "c.execute(sql_insert_workson)"
546 ]
547 },
548 {
549 "cell_type": "code",
550 "execution_count": 19,
551 "metadata": {
552 "collapsed": false
553 },
554 "outputs": [
555 {
556 "data": {
557 "text/plain": [
558 "<sqlite3.Cursor at 0x7f65d8790810>"
559 ]
560 },
561 "execution_count": 19,
562 "metadata": {},
563 "output_type": "execute_result"
564 }
565 ],
566 "source": [
567 "# Insert records to Dependent table\n",
568 "sql_insert_dependent = \"\"\"\n",
569 "INSERT INTO Dependent VALUES\n",
570 "('500','Alice' ,'F','1986/04/05','Daughter'),\n",
571 "('500','Theodore,Frank','M','1983/10/25','Son'),\n",
572 "('500','Joy' ,'F','1958/05/03','Spouse'),\n",
573 "('400','Abner' ,'M','1942/02/28','Spouse'),\n",
574 "('501','Michael' ,'M','1988/01/04','Son'),\n",
575 "('501','Alice,John' ,'F','1988/12/30','Daughter'),\n",
576 "('501','Michael,John' ,'M','1978/12/30','Son'),\n",
577 "('501','Elizabe' ,'F','1967/05/05','Spouse');\n",
578 "\"\"\"\n",
579 "c.execute(sql_insert_dependent)"
580 ]
581 },
582 {
583 "cell_type": "code",
584 "execution_count": 20,
585 "metadata": {
586 "collapsed": false
587 },
588 "outputs": [
589 {
590 "data": {
591 "text/plain": [
592 "<sqlite3.Cursor at 0x7f65d8790810>"
593 ]
594 },
595 "execution_count": 20,
596 "metadata": {},
597 "output_type": "execute_result"
598 }
599 ],
600 "source": [
601 "sql_update_dept = \"\"\"\n",
602 "UPDATE Dept SET MgrSsn = (case \n",
603 "when Dno=1 then '100'\n",
604 "when Dno=4 then '400'\n",
605 "when Dno=5 then '500' \n",
606 "end) WHERE Dno IN (1, 4, 5);\n",
607 "\"\"\"\n",
608 "c.execute(sql_update_dept)"
609 ]
610 },
611 {
612 "cell_type": "code",
613 "execution_count": 21,
614 "metadata": {
615 "collapsed": false
616 },
617 "outputs": [
618 {
619 "name": "stdout",
620 "output_type": "stream",
621 "text": [
622 "[(u'Dept',), (u'Employee',), (u'DeptLoc',), (u'Proj',), (u'WorksOn',), (u'Dependent',), (u'SetA',), (u'SetB',)]\n"
623 ]
624 }
625 ],
626 "source": [
627 "c.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
628 "print(c.fetchall())"
629 ]
630 },
631 {
632 "cell_type": "markdown",
633 "metadata": {},
634 "source": [
635 "## 8.21 In SQL, specify the following queries on the database in Figure 5.5 using the concept of nested queries and concepts described in this chapter. \n",
636 "![Alt text](q1.png \"Optional title\")"
637 ]
638 },
639 {
640 "cell_type": "markdown",
641 "metadata": {},
642 "source": [
643 "### 1.Retrieve the names of all employees who work in department that has the employee with the highest salary among all employees."
644 ]
645 },
646 {
647 "cell_type": "code",
648 "execution_count": 22,
649 "metadata": {
650 "collapsed": false
651 },
652 "outputs": [
653 {
654 "name": "stdout",
655 "output_type": "stream",
656 "text": [
657 "(u'James',)\n"
658 ]
659 }
660 ],
661 "source": [
662 "sql = \"\"\"\n",
663 "SELECT name \n",
664 "FROM employee\n",
665 "WHERE dno = \n",
666 " (SELECT dno \n",
667 " FROM employee \n",
668 " WHERE salary = \n",
669 " (SELECT MAX(salary)\n",
670 " FROM employee));\n",
671 "\"\"\"\n",
672 "c.execute(sql)\n",
673 "rows = c.fetchall()\n",
674 "for row in rows:\n",
675 " print(row)"
676 ]
677 },
678 {
679 "cell_type": "markdown",
680 "metadata": {},
681 "source": [
682 "### 2.Retrieve the names of all employees whose supervisor’s supervisor has ‘88866555’for Ssn."
683 ]
684 },
685 {
686 "cell_type": "code",
687 "execution_count": 23,
688 "metadata": {
689 "collapsed": false
690 },
691 "outputs": [],
692 "source": [
693 "sql = \"\"\"\n",
694 "SELECT name \n",
695 "FROM employee\n",
696 "WHERE SuperSsn = \n",
697 " (SELECT ssn \n",
698 " FROM employee\n",
699 " WHERE SuperSsn = 88866555)\n",
700 "\"\"\"\n",
701 "c.execute(sql)\n",
702 "rows = c.fetchall()\n",
703 "for row in rows:\n",
704 " print(row)"
705 ]
706 },
707 {
708 "cell_type": "markdown",
709 "metadata": {},
710 "source": [
711 "### 3.Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company."
712 ]
713 },
714 {
715 "cell_type": "code",
716 "execution_count": 24,
717 "metadata": {
718 "collapsed": false
719 },
720 "outputs": [
721 {
722 "name": "stdout",
723 "output_type": "stream",
724 "text": [
725 "(u'James',)\n",
726 "(u'Jennifer',)\n",
727 "(u'Frank',)\n",
728 "(u'John',)\n",
729 "(u'Ramesh',)\n",
730 "(u'Joyce',)\n"
731 ]
732 }
733 ],
734 "source": [
735 "sql = \"\"\"\n",
736 "SELECT name\n",
737 "FROM employee\n",
738 "WHERE salary >= 10000 \n",
739 "AND salary > \n",
740 " (SELECT MIN(salary)\n",
741 " FROM employee)\n",
742 "\"\"\"\n",
743 "c.execute(sql)\n",
744 "rows = c.fetchall()\n",
745 "for row in rows:\n",
746 " print(row)"
747 ]
748 },
749 {
750 "cell_type": "markdown",
751 "metadata": {},
752 "source": [
753 "## Consider the following view, DEPT_SUMMARY, defined on the COMPANY database of Figure 5.6(Please see Exercise 8.13) \n",
754 "```sql\n",
755 "CREATE VIEW DEPT_SUMMARY(D, C, Total_s, Average_s) \n",
756 "AS SELECT \n",
757 "Dno, COUNT(*), SUM(salary), AVG(Salary) \n",
758 "FROM \n",
759 "EMPLOYEE \n",
760 "GROUP BY \n",
761 "Dno;\n",
762 "``` \n",
763 "#### State which of following queries and updates would be allowed? If it is not allowed, please explain it. "
764 ]
765 },
766 {
767 "cell_type": "code",
768 "execution_count": 25,
769 "metadata": {
770 "collapsed": false
771 },
772 "outputs": [],
773 "source": [
774 "## Create DEPT_SUMMARY View\n",
775 "sql = \"\"\"\n",
776 "CREATE VIEW IF NOT EXISTS DEPT_SUMMARY(D, C, Total_s, Average_s) \n",
777 "AS SELECT \n",
778 "Dno, COUNT(*), SUM(salary), AVG(Salary) \n",
779 "FROM \n",
780 "EMPLOYEE \n",
781 "GROUP BY \n",
782 "Dno;\n",
783 "\"\"\"\n",
784 "c.execute(sql)\n",
785 "rows = c.fetchall()\n",
786 "for row in rows:\n",
787 " print(row)"
788 ]
789 },
790 {
791 "cell_type": "markdown",
792 "metadata": {},
793 "source": [
794 "### (a) SELECT * FROM DEPT_SUMMARY"
795 ]
796 },
797 {
798 "cell_type": "code",
799 "execution_count": 26,
800 "metadata": {
801 "collapsed": false
802 },
803 "outputs": [
804 {
805 "name": "stdout",
806 "output_type": "stream",
807 "text": [
808 "(1, 1, 55000, 55000.0)\n",
809 "(4, 3, 93000, 31000.0)\n",
810 "(5, 4, 135000, 33750.0)\n"
811 ]
812 }
813 ],
814 "source": [
815 "sql = \"\"\"\n",
816 "SELECT * \n",
817 "FROM DEPT_SUMMARY\n",
818 "\"\"\"\n",
819 "c.execute(sql)\n",
820 "rows = c.fetchall()\n",
821 "for row in rows:\n",
822 " print(row)"
823 ]
824 },
825 {
826 "cell_type": "markdown",
827 "metadata": {},
828 "source": [
829 "### (b) SELECT D, C FROM DEPT_SUMMARY WHERE TOTAL_S>100000;"
830 ]
831 },
832 {
833 "cell_type": "code",
834 "execution_count": 27,
835 "metadata": {
836 "collapsed": false
837 },
838 "outputs": [
839 {
840 "name": "stdout",
841 "output_type": "stream",
842 "text": [
843 "(5, 4)\n"
844 ]
845 }
846 ],
847 "source": [
848 "sql = \"\"\"\n",
849 "SELECT D, C \n",
850 "FROM DEPT_SUMMARY \n",
851 "WHERE TOTAL_S > 100000;\n",
852 "\"\"\"\n",
853 "c.execute(sql)\n",
854 "rows = c.fetchall()\n",
855 "for row in rows:\n",
856 " print(row)"
857 ]
858 },
859 {
860 "cell_type": "markdown",
861 "metadata": {},
862 "source": [
863 "### (c) SELECT D, AVERAGE_S FROM DEPT_SUMMARY WHERE C>( SELECT C FROM DEPT_SUMMARY WHERE D=4 );"
864 ]
865 },
866 {
867 "cell_type": "code",
868 "execution_count": 28,
869 "metadata": {
870 "collapsed": false
871 },
872 "outputs": [
873 {
874 "name": "stdout",
875 "output_type": "stream",
876 "text": [
877 "(5, 33750.0)\n"
878 ]
879 }
880 ],
881 "source": [
882 "sql = \"\"\"\n",
883 "SELECT D, AVERAGE_S \n",
884 "FROM DEPT_SUMMARY \n",
885 "WHERE C > \n",
886 " (SELECT C FROM DEPT_SUMMARY WHERE D=4);\n",
887 "\"\"\"\n",
888 "c.execute(sql)\n",
889 "rows = c.fetchall()\n",
890 "for row in rows:\n",
891 " print(row)"
892 ]
893 },
894 {
895 "cell_type": "markdown",
896 "metadata": {},
897 "source": [
898 "### (d) UPDATE DEPT_SUMMARY SET D=3 WHERE D=4;"
899 ]
900 },
901 {
902 "cell_type": "code",
903 "execution_count": 29,
904 "metadata": {
905 "collapsed": false
906 },
907 "outputs": [
908 {
909 "ename": "OperationalError",
910 "evalue": "cannot modify DEPT_SUMMARY because it is a view",
911 "output_type": "error",
912 "traceback": [
913 "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
914 "\u001b[0;31mOperationalError\u001b[0m Traceback (most recent call last)",
915 "\u001b[0;32m<ipython-input-29-d67c7e330e0a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m 4\u001b[0m \u001b[0mWHERE\u001b[0m \u001b[0mD\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m;\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5\u001b[0m \"\"\"\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 7\u001b[0m \u001b[0mrows\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 8\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
916 "\u001b[0;31mOperationalError\u001b[0m: cannot modify DEPT_SUMMARY because it is a view"
917 ]
918 }
919 ],
920 "source": [
921 "sql = \"\"\"\n",
922 "UPDATE DEPT_SUMMARY \n",
923 "SET D=3 \n",
924 "WHERE D=4;\n",
925 "\"\"\"\n",
926 "c.execute(sql)\n",
927 "rows = c.fetchall()\n",
928 "for row in rows:\n",
929 " print(row)"
930 ]
931 },
932 {
933 "cell_type": "markdown",
934 "metadata": {},
935 "source": [
936 "### (e) DELETE FROM DEPT_SUMMARY WHERE C>4;"
937 ]
938 },
939 {
940 "cell_type": "code",
941 "execution_count": 30,
942 "metadata": {
943 "collapsed": false
944 },
945 "outputs": [
946 {
947 "ename": "OperationalError",
948 "evalue": "cannot modify DEPT_SUMMARY because it is a view",
949 "output_type": "error",
950 "traceback": [
951 "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
952 "\u001b[0;31mOperationalError\u001b[0m Traceback (most recent call last)",
953 "\u001b[0;32m<ipython-input-30-624e27f5e6da>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m 3\u001b[0m \u001b[0mFROM\u001b[0m \u001b[0mDEPT_SUMMARY\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 4\u001b[0m WHERE C > 4;\"\"\"\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 6\u001b[0m \u001b[0mrows\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 7\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
954 "\u001b[0;31mOperationalError\u001b[0m: cannot modify DEPT_SUMMARY because it is a view"
955 ]
956 }
957 ],
958 "source": [
959 "sql = \"\"\"\n",
960 "DELETE \n",
961 "FROM DEPT_SUMMARY \n",
962 "WHERE C > 4;\n",
963 "\"\"\"\n",
964 "c.execute(sql)\n",
965 "rows = c.fetchall()\n",
966 "for row in rows:\n",
967 " print(row)"
968 ]
969 },
970 {
971 "cell_type": "code",
972 "execution_count": 31,
973 "metadata": {
974 "collapsed": false
975 },
976 "outputs": [
977 {
978 "name": "stdout",
979 "output_type": "stream",
980 "text": [
981 "(u'100', u'James', u'M', u'1937/11/10', u'450 Stone', 55000, None, 1)\n",
982 "(u'400', u'Jennifer', u'F', u'1941/06/20', u'291 Berry', 43000, u'100', 4)\n",
983 "(u'500', u'Frank', u'M', u'1955/12/08', u'638 Voss', 40000, u'100', 5)\n",
984 "(u'401', u'Alicia', u'F', u'1968/01/19', u'332 Castle', 25000, u'400', 4)\n",
985 "(u'402', u'Ahmad', u'M', u'1969/03/29', u'980 Dallas', 25000, u'400', 4)\n",
986 "(u'501', u'John', u'M', u'1965/01/09', u'731 Fond', 30000, u'500', 5)\n",
987 "(u'502', u'Ramesh', u'M', u'1962/08/09', u'123 Fire', 38000, u'500', 5)\n",
988 "(u'503', u'Joyce', u'F', u'1972/07/08', u'452 Rice', 27000, u'500', 5)\n"
989 ]
990 }
991 ],
992 "source": [
993 "c.execute(\"SELECT * FROM employee\")\n",
994 "rows = c.fetchall()\n",
995 "for row in rows:\n",
996 " print(row)"
997 ]
998 },
999 {
1000 "cell_type": "code",
1001 "execution_count": 32,
1002 "metadata": {
1003 "collapsed": false
1004 },
1005 "outputs": [
1006 {
1007 "name": "stdout",
1008 "output_type": "stream",
1009 "text": [
1010 "['Ssn', 'Name', 'Sex', 'Bdate', 'Address', 'Salary', 'SuperSsn', 'Dno']\n"
1011 ]
1012 }
1013 ],
1014 "source": [
1015 "num_fields = len(c.description)\n",
1016 "field_names = [i[0] for i in c.description]\n",
1017 "print(field_names)"
1018 ]
1019 },
1020 {
1021 "cell_type": "code",
1022 "execution_count": null,
1023 "metadata": {
1024 "collapsed": true
1025 },
1026 "outputs": [],
1027 "source": []
1028 },
1029 {
1030 "cell_type": "code",
1031 "execution_count": null,
1032 "metadata": {
1033 "collapsed": true
1034 },
1035 "outputs": [],
1036 "source": []
1037 }
1038 ],
1039 "metadata": {
1040 "anaconda-cloud": {},
1041 "kernelspec": {
1042 "display_name": "Python [conda root]",
1043 "language": "python",
1044 "name": "conda-root-py"
1045 },
1046 "language_info": {
1047 "codemirror_mode": {
1048 "name": "ipython",
1049 "version": 2
1050 },
1051 "file_extension": ".py",
1052 "mimetype": "text/x-python",
1053 "name": "python",
1054 "nbconvert_exporter": "python",
1055 "pygments_lexer": "ipython2",
1056 "version": "2.7.12"
1057 }
1058 },
1059 "nbformat": 4,
1060 "nbformat_minor": 1
1061}