· 9 years ago · Dec 26, 2016, 06:54 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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 0x7fee404aa880>"
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 "(5,'New York');\"\"\"\n",
471 "c.execute(sql_insert_DeptLoc)"
472 ]
473 },
474 {
475 "cell_type": "code",
476 "execution_count": 17,
477 "metadata": {
478 "collapsed": false
479 },
480 "outputs": [
481 {
482 "data": {
483 "text/plain": [
484 "<sqlite3.Cursor at 0x7fee404aa880>"
485 ]
486 },
487 "execution_count": 17,
488 "metadata": {},
489 "output_type": "execute_result"
490 }
491 ],
492 "source": [
493 "# Insert records to Proj table\n",
494 "sql_insert_proj = \"\"\"\n",
495 "INSERT INTO Proj VALUES\n",
496 "(1,'ProductX','Bellaire' ,5),\n",
497 "(2,'ProductY','Sugarland',5),\n",
498 "(3,'ProductZ','Houston' ,5),\n",
499 "(10,'iPhone' ,'Stafford' ,4),\n",
500 "(20,'BaceMan','Houston' ,1),\n",
501 "(30,'MotoAny','Stafford' ,4);\n",
502 "\"\"\"\n",
503 "c.execute(sql_insert_proj)"
504 ]
505 },
506 {
507 "cell_type": "code",
508 "execution_count": 18,
509 "metadata": {
510 "collapsed": false
511 },
512 "outputs": [
513 {
514 "data": {
515 "text/plain": [
516 "<sqlite3.Cursor at 0x7fee404aa880>"
517 ]
518 },
519 "execution_count": 18,
520 "metadata": {},
521 "output_type": "execute_result"
522 }
523 ],
524 "source": [
525 "# Insert records to WorksOn table\n",
526 "sql_insert_workson = \"\"\"\n",
527 "INSERT INTO WorksOn VALUES\n",
528 "('501', 1,32.5),\n",
529 "('501', 2,7.5),\n",
530 "('502', 3,40),\n",
531 "('503', 1,20),\n",
532 "('503', 2,20),\n",
533 "('500', 1,10),\n",
534 "('500', 2,10),\n",
535 "('500', 3,10),\n",
536 "('500',10,10),\n",
537 "('500',20,10),\n",
538 "('402',10,35),\n",
539 "('402',30,5),\n",
540 "('401',30,30),\n",
541 "('401',10,10),\n",
542 "('400',30,20),\n",
543 "('400',20,15),\n",
544 "('100',20,null);\n",
545 "\"\"\"\n",
546 "c.execute(sql_insert_workson)"
547 ]
548 },
549 {
550 "cell_type": "code",
551 "execution_count": 19,
552 "metadata": {
553 "collapsed": false
554 },
555 "outputs": [
556 {
557 "data": {
558 "text/plain": [
559 "<sqlite3.Cursor at 0x7fee404aa880>"
560 ]
561 },
562 "execution_count": 19,
563 "metadata": {},
564 "output_type": "execute_result"
565 }
566 ],
567 "source": [
568 "# Insert records to Dependent table\n",
569 "sql_insert_dependent = \"\"\"\n",
570 "INSERT INTO Dependent VALUES\n",
571 "('500','Alice' ,'F','1986/04/05','Daughter'),\n",
572 "('500','Theodore,Frank','M','1983/10/25','Son'),\n",
573 "('500','Joy' ,'F','1958/05/03','Spouse'),\n",
574 "('400','Abner' ,'M','1942/02/28','Spouse'),\n",
575 "('501','Michael' ,'M','1988/01/04','Son'),\n",
576 "('501','Alice,John' ,'F','1988/12/30','Daughter'),\n",
577 "('501','Michael,John' ,'M','1978/12/30','Son'),\n",
578 "('501','Elizabe' ,'F','1967/05/05','Spouse');\n",
579 "\"\"\"\n",
580 "c.execute(sql_insert_dependent)"
581 ]
582 },
583 {
584 "cell_type": "code",
585 "execution_count": 20,
586 "metadata": {
587 "collapsed": false
588 },
589 "outputs": [
590 {
591 "data": {
592 "text/plain": [
593 "<sqlite3.Cursor at 0x7fee404aa880>"
594 ]
595 },
596 "execution_count": 20,
597 "metadata": {},
598 "output_type": "execute_result"
599 }
600 ],
601 "source": [
602 "sql_update_dept = \"\"\"\n",
603 "UPDATE Dept SET MgrSsn = (case \n",
604 "when Dno=1 then '100'\n",
605 "when Dno=4 then '400'\n",
606 "when Dno=5 then '500' \n",
607 "end) WHERE Dno IN (1, 4, 5);\n",
608 "\"\"\"\n",
609 "c.execute(sql_update_dept)"
610 ]
611 },
612 {
613 "cell_type": "code",
614 "execution_count": 21,
615 "metadata": {
616 "collapsed": false
617 },
618 "outputs": [
619 {
620 "name": "stdout",
621 "output_type": "stream",
622 "text": [
623 "[(u'Dept',), (u'Employee',), (u'DeptLoc',), (u'Proj',), (u'WorksOn',), (u'Dependent',), (u'SetA',), (u'SetB',)]\n"
624 ]
625 }
626 ],
627 "source": [
628 "c.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
629 "print(c.fetchall())"
630 ]
631 },
632 {
633 "cell_type": "markdown",
634 "metadata": {},
635 "source": [
636 "1.找出有åƒ與製作iPhone這項Project的員工姓åÂÂ還有他的薪水跟工作時數"
637 ]
638 },
639 {
640 "cell_type": "code",
641 "execution_count": 22,
642 "metadata": {
643 "collapsed": false
644 },
645 "outputs": [
646 {
647 "name": "stdout",
648 "output_type": "stream",
649 "text": [
650 "(u'Frank', 40000, 10)\n",
651 "(u'Ahmad', 25000, 35)\n",
652 "(u'Alicia', 25000, 10)\n"
653 ]
654 }
655 ],
656 "source": [
657 "sql = \"\"\"\n",
658 "select e.name, e.salary, w.hours from employee e, workson w, proj p where p.pno = w.pno and e.ssn = w.ssn \n",
659 "and p.pname = 'iPhone';\n",
660 "\"\"\"\n",
661 "c.execute(sql)\n",
662 "rows = c.fetchall()\n",
663 "for row in rows:\n",
664 " print(row)"
665 ]
666 },
667 {
668 "cell_type": "markdown",
669 "metadata": {},
670 "source": [
671 "2.顯示部門的據點åÂÂ稱，但åª找出都是女性員工的部門。"
672 ]
673 },
674 {
675 "cell_type": "code",
676 "execution_count": 23,
677 "metadata": {
678 "collapsed": false
679 },
680 "outputs": [
681 {
682 "name": "stdout",
683 "output_type": "stream",
684 "text": [
685 "(u'Stafford',)\n",
686 "(u'Bellaire',)\n",
687 "(u'Houston',)\n",
688 "(u'New York',)\n",
689 "(u'Sugarland',)\n"
690 ]
691 }
692 ],
693 "source": [
694 "sql = \"\"\"\n",
695 "select distinct dl.dloc from dept d, employee e, deptloc dl where dl.dno = d.dno and e.dno = d.dno and e.sex = 'F';\n",
696 "\"\"\"\n",
697 "c.execute(sql)\n",
698 "rows = c.fetchall()\n",
699 "for row in rows:\n",
700 " print(row)"
701 ]
702 },
703 {
704 "cell_type": "markdown",
705 "metadata": {},
706 "source": [
707 "3.找出有åƒ與ProductAè·ŸProductX的部門，然後列出該部門裡所有的員工姓åÂÂè·Ÿçâ€Å¸Ã¦â€”Â¥ X"
708 ]
709 },
710 {
711 "cell_type": "code",
712 "execution_count": 24,
713 "metadata": {
714 "collapsed": false
715 },
716 "outputs": [
717 {
718 "name": "stdout",
719 "output_type": "stream",
720 "text": [
721 "(u'Frank', u'1955/12/08')\n",
722 "(u'John', u'1965/01/09')\n",
723 "(u'Ramesh', u'1962/08/09')\n",
724 "(u'Joyce', u'1972/07/08')\n"
725 ]
726 }
727 ],
728 "source": [
729 "sql = \"\"\"\n",
730 "select e.name, e.bdate from employee e where e.dno in (select dno from proj where pname = 'ProductA' or pname = 'ProductX')\n",
731 "\"\"\"\n",
732 "c.execute(sql)\n",
733 "rows = c.fetchall()\n",
734 "for row in rows:\n",
735 " print(row)"
736 ]
737 },
738 {
739 "cell_type": "markdown",
740 "metadata": {},
741 "source": [
742 "4.ä¾Â照性別åš分類，顯示性別跟å„別的人數個數和平å‡薪資。"
743 ]
744 },
745 {
746 "cell_type": "code",
747 "execution_count": 25,
748 "metadata": {
749 "collapsed": false
750 },
751 "outputs": [
752 {
753 "name": "stdout",
754 "output_type": "stream",
755 "text": [
756 "(u'F', 3, 31666.666666666668)\n",
757 "(u'M', 5, 37600.0)\n"
758 ]
759 }
760 ],
761 "source": [
762 "sql = \"\"\"\n",
763 "select sex, count(*), avg(salary) from employee group by sex; \n",
764 "\"\"\"\n",
765 "c.execute(sql)\n",
766 "rows = c.fetchall()\n",
767 "for row in rows:\n",
768 " print(row)"
769 ]
770 },
771 {
772 "cell_type": "markdown",
773 "metadata": {},
774 "source": [
775 "5.列出員工的代號跟姓åÂÂ，該員工需è¦Â在計劃裡的工時介在5~10之間，並ä¸â€Ã¦Å“‰è¦ªå±¬è³‡æ–™çš„å“¡å·¥"
776 ]
777 },
778 {
779 "cell_type": "code",
780 "execution_count": 26,
781 "metadata": {
782 "collapsed": false
783 },
784 "outputs": [
785 {
786 "name": "stdout",
787 "output_type": "stream",
788 "text": [
789 "(u'500', u'Frank')\n",
790 "(u'501', u'John')\n"
791 ]
792 }
793 ],
794 "source": [
795 "sql = \"\"\"\n",
796 "select distinct e.ssn, e.name from employee e, workson w, dependent dp \n",
797 "where e.ssn = w.ssn and e.ssn = dp.ssn and w.hours between 5 and 10;\n",
798 "\"\"\"\n",
799 "c.execute(sql)\n",
800 "rows = c.fetchall()\n",
801 "for row in rows:\n",
802 " print(row)"
803 ]
804 },
805 {
806 "cell_type": "markdown",
807 "metadata": {},
808 "source": [
809 "6.找出Dependent裡DependName是Joy他的Relation跟Bdate。?"
810 ]
811 },
812 {
813 "cell_type": "code",
814 "execution_count": 27,
815 "metadata": {
816 "collapsed": false
817 },
818 "outputs": [
819 {
820 "name": "stdout",
821 "output_type": "stream",
822 "text": [
823 "(u'Spouse', u'1958/05/03')\n"
824 ]
825 }
826 ],
827 "source": [
828 "sql = \"\"\"\n",
829 "select relation, bdate from dependent where dependname = 'Joy';\n",
830 "\"\"\"\n",
831 "c.execute(sql)\n",
832 "rows = c.fetchall()\n",
833 "for row in rows:\n",
834 " print(row)"
835 ]
836 },
837 {
838 "cell_type": "markdown",
839 "metadata": {},
840 "source": [
841 "7.算出æ¯Â個員工工作的總時數跟接了幾個計劃，顯示員工姓åÂÂ跟總時數還有計劃個數。"
842 ]
843 },
844 {
845 "cell_type": "code",
846 "execution_count": 28,
847 "metadata": {
848 "collapsed": false
849 },
850 "outputs": [
851 {
852 "name": "stdout",
853 "output_type": "stream",
854 "text": [
855 "(u'Ahmad', 40, 2)\n",
856 "(u'Alicia', 40, 2)\n",
857 "(u'Frank', 50, 5)\n",
858 "(u'James', None, 1)\n",
859 "(u'Jennifer', 35, 2)\n",
860 "(u'John', 40.0, 2)\n",
861 "(u'Joyce', 40, 2)\n",
862 "(u'Ramesh', 40, 1)\n"
863 ]
864 }
865 ],
866 "source": [
867 "sql = \"\"\"\n",
868 "select e.name, sum(w.hours), count(w.pno) from employee e, workson w where e.ssn = w.ssn group by e.name;\n",
869 "\"\"\"\n",
870 "c.execute(sql)\n",
871 "rows = c.fetchall()\n",
872 "for row in rows:\n",
873 " print(row)"
874 ]
875 },
876 {
877 "cell_type": "markdown",
878 "metadata": {},
879 "source": [
880 "8.找出沒有工作時數的員工，顯示其åÂÂå—"
881 ]
882 },
883 {
884 "cell_type": "code",
885 "execution_count": 29,
886 "metadata": {
887 "collapsed": false
888 },
889 "outputs": [
890 {
891 "name": "stdout",
892 "output_type": "stream",
893 "text": [
894 "(u'James',)\n"
895 ]
896 }
897 ],
898 "source": [
899 "sql = \"\"\"\n",
900 "select name from employee where ssn in (select ssn from workson where hours is null)\n",
901 "\"\"\"\n",
902 "c.execute(sql)\n",
903 "rows = c.fetchall()\n",
904 "for row in rows:\n",
905 " print(row)"
906 ]
907 },
908 {
909 "cell_type": "markdown",
910 "metadata": {},
911 "source": [
912 "9.顯示員工的姓åÂÂ跟其薪資，該員工必須其上å¸ä¸Â是部門主管"
913 ]
914 },
915 {
916 "cell_type": "code",
917 "execution_count": 30,
918 "metadata": {
919 "collapsed": false
920 },
921 "outputs": [
922 {
923 "name": "stdout",
924 "output_type": "stream",
925 "text": [
926 "(u'James', 55000)\n",
927 "(u'Jennifer', 43000)\n",
928 "(u'Frank', 40000)\n"
929 ]
930 }
931 ],
932 "source": [
933 "sql = \"\"\"\n",
934 "select distinct e.name, e.salary from employee e, dept d where d.dno = e.dno and e.superssn != d.mgrssn or e.superssn is null ;\n",
935 "\"\"\"\n",
936 "c.execute(sql)\n",
937 "rows = c.fetchall()\n",
938 "for row in rows:\n",
939 " print(row)"
940 ]
941 },
942 {
943 "cell_type": "markdown",
944 "metadata": {},
945 "source": [
946 "10.查出全部部門的據點(åŒ個地點ä¸Â能é‡Â覆顯示) ? X"
947 ]
948 },
949 {
950 "cell_type": "code",
951 "execution_count": 31,
952 "metadata": {
953 "collapsed": false
954 },
955 "outputs": [
956 {
957 "name": "stdout",
958 "output_type": "stream",
959 "text": [
960 "(u'Houston',)\n",
961 "(u'Stafford',)\n",
962 "(u'Bellaire',)\n",
963 "(u'Sugarland',)\n",
964 "(u'New York',)\n"
965 ]
966 }
967 ],
968 "source": [
969 "#??????\n",
970 "sql = \"\"\"\n",
971 "select distinct dloc from deptloc;\n",
972 "\"\"\"\n",
973 "c.execute(sql)\n",
974 "rows = c.fetchall()\n",
975 "for row in rows:\n",
976 " print(row)"
977 ]
978 },
979 {
980 "cell_type": "markdown",
981 "metadata": {},
982 "source": [
983 "11.列出沒有åƒ與計劃工作的員工姓å ? V"
984 ]
985 },
986 {
987 "cell_type": "code",
988 "execution_count": 32,
989 "metadata": {
990 "collapsed": false
991 },
992 "outputs": [],
993 "source": [
994 "sql = \"\"\"SELECT name\n",
995 "FROM EMPLOYEE\n",
996 "WHERE NOT EXISTS (SELECT *\n",
997 "FROM WORKSON\n",
998 "WHERE SSN=SSN)\n",
999 "\"\"\"\n",
1000 "c.execute(sql)\n",
1001 "rows = c.fetchall()\n",
1002 "for row in rows:\n",
1003 " print(row)"
1004 ]
1005 },
1006 {
1007 "cell_type": "markdown",
1008 "metadata": {},
1009 "source": [
1010 "12.哪幾個部門是ä½Â在New York?"
1011 ]
1012 },
1013 {
1014 "cell_type": "code",
1015 "execution_count": 33,
1016 "metadata": {
1017 "collapsed": false
1018 },
1019 "outputs": [
1020 {
1021 "name": "stdout",
1022 "output_type": "stream",
1023 "text": [
1024 "(u'R&D',)\n"
1025 ]
1026 }
1027 ],
1028 "source": [
1029 "sql = \"\"\"\n",
1030 "select d.dname from dept d, deptloc dl where d.dno = dl.dno and dl.dloc = 'New York';\n",
1031 "\"\"\"\n",
1032 "c.execute(sql)\n",
1033 "rows = c.fetchall()\n",
1034 "for row in rows:\n",
1035 " print(row)"
1036 ]
1037 },
1038 {
1039 "cell_type": "markdown",
1040 "metadata": {},
1041 "source": [
1042 "13.顯示部門代號跟部門åÂÂ稱還有該部門有多少ä½Â員工。"
1043 ]
1044 },
1045 {
1046 "cell_type": "code",
1047 "execution_count": 120,
1048 "metadata": {
1049 "collapsed": false
1050 },
1051 "outputs": [
1052 {
1053 "name": "stdout",
1054 "output_type": "stream",
1055 "text": [
1056 "(1, u'Head', 1)\n",
1057 "(4, u'Admin', 3)\n",
1058 "(5, u'R&D', 4)\n"
1059 ]
1060 }
1061 ],
1062 "source": [
1063 "sql = \"\"\"\n",
1064 "select d.dno, d.dname, count(*) from dept d, employee e where d.dno = e.dno group by d.dno;\n",
1065 "\"\"\"\n",
1066 "c.execute(sql)\n",
1067 "rows = c.fetchall()\n",
1068 "for row in rows:\n",
1069 " print(row)"
1070 ]
1071 },
1072 {
1073 "cell_type": "markdown",
1074 "metadata": {},
1075 "source": [
1076 "14.找出沒有在Houstonè¨Â立據點的部門，並列出該部門的主管姓åÂÂ跟其地å€"
1077 ]
1078 },
1079 {
1080 "cell_type": "code",
1081 "execution_count": 34,
1082 "metadata": {
1083 "collapsed": false
1084 },
1085 "outputs": [
1086 {
1087 "name": "stdout",
1088 "output_type": "stream",
1089 "text": [
1090 "(u'James', u'450 Stone')\n",
1091 "(u'Frank', u'638 Voss')\n"
1092 ]
1093 }
1094 ],
1095 "source": [
1096 "sql = \"\"\"\n",
1097 "select name, address from employee where ssn in \n",
1098 "(select d.mgrssn from dept d, deptloc dp where d.dno = dp.dno and dp.dloc = 'Houston');\n",
1099 "\"\"\"\n",
1100 "c.execute(sql)\n",
1101 "rows = c.fetchall()\n",
1102 "for row in rows:\n",
1103 " print(row)"
1104 ]
1105 },
1106 {
1107 "cell_type": "markdown",
1108 "metadata": {},
1109 "source": [
1110 "15.列出所有員工的代號跟姓åÂÂ還有其主管的代號跟姓å ? X"
1111 ]
1112 },
1113 {
1114 "cell_type": "code",
1115 "execution_count": 40,
1116 "metadata": {
1117 "collapsed": false
1118 },
1119 "outputs": [
1120 {
1121 "name": "stdout",
1122 "output_type": "stream",
1123 "text": [
1124 "(u'100', u'James', u'M', u'1937/11/10', u'450 Stone', 55000, None, 1, 1, u'Head', u'100', None)\n",
1125 "(u'400', u'Jennifer', u'F', u'1941/06/20', u'291 Berry', 43000, u'100', 4, 4, u'Admin', u'400', None)\n",
1126 "(u'500', u'Frank', u'M', u'1955/12/08', u'638 Voss', 40000, u'100', 5, 5, u'R&D', u'500', None)\n"
1127 ]
1128 }
1129 ],
1130 "source": [
1131 "sql = \"\"\"\n",
1132 "select * from employee e, dept d where e.ssn = d.mgrssn;\n",
1133 "\"\"\"\n",
1134 "c.execute(sql)\n",
1135 "rows = c.fetchall()\n",
1136 "for row in rows:\n",
1137 " print(row)"
1138 ]
1139 },
1140 {
1141 "cell_type": "markdown",
1142 "metadata": {},
1143 "source": [
1144 "16.列出親屬的姓åÂÂ跟關係，而列出的必須是部門主管的親屬。"
1145 ]
1146 },
1147 {
1148 "cell_type": "code",
1149 "execution_count": 41,
1150 "metadata": {
1151 "collapsed": false
1152 },
1153 "outputs": [
1154 {
1155 "name": "stdout",
1156 "output_type": "stream",
1157 "text": [
1158 "(u'Abner', u'Spouse')\n",
1159 "(u'Alice', u'Daughter')\n",
1160 "(u'Joy', u'Spouse')\n",
1161 "(u'Theodore,Frank', u'Son')\n"
1162 ]
1163 }
1164 ],
1165 "source": [
1166 "sql = \"\"\"\n",
1167 "select dp.dependname, dp.relation from dependent dp where dp.ssn in (select mgrssn from dept)\n",
1168 "\"\"\"\n",
1169 "c.execute(sql)\n",
1170 "rows = c.fetchall()\n",
1171 "for row in rows:\n",
1172 " print(row)"
1173 ]
1174 },
1175 {
1176 "cell_type": "markdown",
1177 "metadata": {},
1178 "source": [
1179 "17.找出負責Fishing這項計劃的部門，並列出æ¯â€Ã¨Â©Â²Ã©Æ’¨é–€æ‰€æœ‰å“¡å·¥é‚„è¦Â年長的員工姓å X"
1180 ]
1181 },
1182 {
1183 "cell_type": "code",
1184 "execution_count": 44,
1185 "metadata": {
1186 "collapsed": false
1187 },
1188 "outputs": [
1189 {
1190 "name": "stdout",
1191 "output_type": "stream",
1192 "text": [
1193 "(u'R&D', u'1972/07/08')\n"
1194 ]
1195 }
1196 ],
1197 "source": [
1198 "sql = \"\"\"\n",
1199 "select d.dname, max(e.bdate) from employee e, proj p, dept d where e.dno = d.dno and p.dno = d.dno and p.pname = 'ProductZ';\n",
1200 "\"\"\"\n",
1201 "c.execute(sql)\n",
1202 "rows = c.fetchall()\n",
1203 "for row in rows:\n",
1204 " print(row)"
1205 ]
1206 },
1207 {
1208 "cell_type": "markdown",
1209 "metadata": {},
1210 "source": [
1211 "18.找出部門所承接的計劃裡部門據點跟計劃地點一樣的計劃，最後顯示部門åÂÂ稱跟計劃åÂÂ稱。? V"
1212 ]
1213 },
1214 {
1215 "cell_type": "code",
1216 "execution_count": 49,
1217 "metadata": {
1218 "collapsed": false
1219 },
1220 "outputs": [
1221 {
1222 "name": "stdout",
1223 "output_type": "stream",
1224 "text": [
1225 "(u'R&D', u'ProductX')\n",
1226 "(u'R&D', u'ProductY')\n",
1227 "(u'R&D', u'ProductZ')\n",
1228 "(u'Admin', u'iPhone')\n",
1229 "(u'Head', u'BaceMan')\n",
1230 "(u'Admin', u'MotoAny')\n"
1231 ]
1232 }
1233 ],
1234 "source": [
1235 "sql = \"\"\"\n",
1236 "select d.dname, p.pname from proj p, dept d, deptloc dp where p.dno = d.dno and dp.dno = d.dno and dp.dloc = p.ploc;\n",
1237 "\"\"\"\n",
1238 "c.execute(sql)\n",
1239 "rows = c.fetchall()\n",
1240 "for row in rows:\n",
1241 " print(row)"
1242 ]
1243 },
1244 {
1245 "cell_type": "markdown",
1246 "metadata": {},
1247 "source": [
1248 "19.列出員工åÂÂ稱跟他的薪水以åŠ他的部門主管åÂÂå—，並ä¸â€Ã¤Â»Â¥Ã¨â€“ªæ°´éž減排åºÂ，如果åŒ樣薪水å†Â以員工åÂÂ稱來排庠X"
1249 ]
1250 },
1251 {
1252 "cell_type": "code",
1253 "execution_count": 59,
1254 "metadata": {
1255 "collapsed": false
1256 },
1257 "outputs": [
1258 {
1259 "name": "stdout",
1260 "output_type": "stream",
1261 "text": [
1262 "(u'James', 55000)\n",
1263 "(u'Jennifer', 43000)\n",
1264 "(u'Frank', 40000)\n",
1265 "(u'Ramesh', 38000)\n",
1266 "(u'John', 30000)\n",
1267 "(u'Joyce', 27000)\n",
1268 "(u'Alicia', 25000)\n",
1269 "(u'Ahmad', 25000)\n"
1270 ]
1271 }
1272 ],
1273 "source": [
1274 "sql = \"\"\"\n",
1275 "select e.name, e.salary from employee e order by e.salary desc, e.name desc\n",
1276 "\"\"\"\n",
1277 "c.execute(sql)\n",
1278 "rows = c.fetchall()\n",
1279 "for row in rows:\n",
1280 " print(row)"
1281 ]
1282 },
1283 {
1284 "cell_type": "markdown",
1285 "metadata": {},
1286 "source": [
1287 "20.找出員工薪水å°Âæ–¼50000而ä¸â€Ã¥â‚¬â€¹Ã¦â€¢Â¸Ã©â€šâ€žÃ¨Â¶â€¦Ã©ÂŽå…©å€‹ä»¥ä¸Šçš„部門，請顯示出該部門åÂÂ稱還有薪水ä¸Â足50000的員工個數。"
1288 ]
1289 },
1290 {
1291 "cell_type": "code",
1292 "execution_count": 60,
1293 "metadata": {
1294 "collapsed": false
1295 },
1296 "outputs": [
1297 {
1298 "name": "stdout",
1299 "output_type": "stream",
1300 "text": [
1301 "(u'R&D', 4)\n"
1302 ]
1303 }
1304 ],
1305 "source": [
1306 "sql = \"\"\"\n",
1307 "select d.dname, count(*) from dept d, employee e where d.dno = e.dno and d.dno in (select d.dno from employee e, dept d group by e.dno having count(e.salary < 50000));\n",
1308 "\"\"\"\n",
1309 "c.execute(sql)\n",
1310 "rows = c.fetchall()\n",
1311 "for row in rows:\n",
1312 " print(row)"
1313 ]
1314 },
1315 {
1316 "cell_type": "code",
1317 "execution_count": 58,
1318 "metadata": {
1319 "collapsed": false
1320 },
1321 "outputs": [
1322 {
1323 "name": "stdout",
1324 "output_type": "stream",
1325 "text": [
1326 "(1, u'ProductX', u'Bellaire', 5)\n",
1327 "(2, u'ProductY', u'Sugarland', 5)\n",
1328 "(3, u'ProductZ', u'Houston', 5)\n",
1329 "(10, u'iPhone', u'Stafford', 4)\n",
1330 "(20, u'BaceMan', u'Houston', 1)\n",
1331 "(30, u'MotoAny', u'Stafford', 4)\n"
1332 ]
1333 }
1334 ],
1335 "source": [
1336 "c.execute(\"SELECT * FROM proj\")\n",
1337 "rows = c.fetchall()\n",
1338 "for row in rows:\n",
1339 " print(row)"
1340 ]
1341 },
1342 {
1343 "cell_type": "code",
1344 "execution_count": 54,
1345 "metadata": {
1346 "collapsed": false
1347 },
1348 "outputs": [
1349 {
1350 "name": "stdout",
1351 "output_type": "stream",
1352 "text": [
1353 "['Pno', 'Pname', 'Ploc', 'Dno']\n"
1354 ]
1355 }
1356 ],
1357 "source": [
1358 "num_fields = len(c.description)\n",
1359 "field_names = [i[0] for i in c.description]\n",
1360 "print(field_names)"
1361 ]
1362 },
1363 {
1364 "cell_type": "code",
1365 "execution_count": 51,
1366 "metadata": {
1367 "collapsed": false
1368 },
1369 "outputs": [
1370 {
1371 "name": "stdout",
1372 "output_type": "stream",
1373 "text": [
1374 "(1, u'Head', u'100', None)\n",
1375 "(4, u'Admin', u'400', None)\n",
1376 "(5, u'R&D', u'500', None)\n"
1377 ]
1378 }
1379 ],
1380 "source": [
1381 "c.execute(\"SELECT * FROM dept\")\n",
1382 "rows = c.fetchall()\n",
1383 "for row in rows:\n",
1384 " print(row)"
1385 ]
1386 },
1387 {
1388 "cell_type": "code",
1389 "execution_count": 52,
1390 "metadata": {
1391 "collapsed": false
1392 },
1393 "outputs": [
1394 {
1395 "name": "stdout",
1396 "output_type": "stream",
1397 "text": [
1398 "(1, u'Houston')\n",
1399 "(4, u'Stafford')\n",
1400 "(5, u'Bellaire')\n",
1401 "(5, u'Sugarland')\n",
1402 "(5, u'Houston')\n",
1403 "(5, u'New York')\n"
1404 ]
1405 }
1406 ],
1407 "source": [
1408 "c.execute(\"SELECT * FROM deptloc\")\n",
1409 "rows = c.fetchall()\n",
1410 "for row in rows:\n",
1411 " print(row)"
1412 ]
1413 },
1414 {
1415 "cell_type": "code",
1416 "execution_count": null,
1417 "metadata": {
1418 "collapsed": true
1419 },
1420 "outputs": [],
1421 "source": []
1422 }
1423 ],
1424 "metadata": {
1425 "anaconda-cloud": {},
1426 "kernelspec": {
1427 "display_name": "Python [conda root]",
1428 "language": "python",
1429 "name": "conda-root-py"
1430 },
1431 "language_info": {
1432 "codemirror_mode": {
1433 "name": "ipython",
1434 "version": 2
1435 },
1436 "file_extension": ".py",
1437 "mimetype": "text/x-python",
1438 "name": "python",
1439 "nbconvert_exporter": "python",
1440 "pygments_lexer": "ipython2",
1441 "version": "2.7.12"
1442 }
1443 },
1444 "nbformat": 4,
1445 "nbformat_minor": 1
1446}