My omnium-gatherom of scripts and source code.
stau.space
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "metadata": {
6 "id": "fsMwG9oTuv8p"
7 },
8 "source": [
9 "**Hagan una copia de este notebook y comincen a ejecutar las ventanas.**"
10 ]
11 },
12 {
13 "cell_type": "code",
14 "execution_count": 283,
15 "metadata": {
16 "executionInfo": {
17 "elapsed": 4,
18 "status": "ok",
19 "timestamp": 1742331515737,
20 "user": {
21 "displayName": "Carlos J Corrada Bravo",
22 "userId": "05726728043350562289"
23 },
24 "user_tz": 240
25 },
26 "id": "leyJgQpLyb9y"
27 },
28 "outputs": [],
29 "source": [
30 "import sqlite3\n",
31 "import pandas as pd\n",
32 "\n",
33 "conn = sqlite3.connect(':memory:')\n",
34 "\n",
35 "create_statements = [\n",
36 "'''\n",
37 "create table classroom\n",
38 "\t(building\t\tvarchar(15),\n",
39 "\t room_number\t\tvarchar(7),\n",
40 "\t capacity\t\tnumeric(4,0),\n",
41 "\t primary key (building, room_number)\n",
42 "\t);\n",
43 "\n",
44 "''',\n",
45 "'''\n",
46 "create table department\n",
47 "\t(dept_name\t\tvarchar(20),\n",
48 "\t building\t\tvarchar(15),\n",
49 "\t budget\t\t numeric(12,2) check (budget > 0),\n",
50 "\t primary key (dept_name)\n",
51 "\t);\n",
52 "\n",
53 "''',\n",
54 "'''\n",
55 "create table course\n",
56 "\t(course_id\t\tvarchar(8),\n",
57 "\t title\t\t\tvarchar(50),\n",
58 "\t dept_name\t\tvarchar(20),\n",
59 "\t credits\t\tnumeric(2,0) check (credits > 0),\n",
60 "\t primary key (course_id),\n",
61 "\t foreign key (dept_name) references department\n",
62 "\t\ton delete set null\n",
63 "\t);\n",
64 "\n",
65 "''',\n",
66 "'''\n",
67 "create table instructor\n",
68 "\t(ID\t\t\tvarchar(5),\n",
69 "\t name\t\t\tvarchar(20) not null,\n",
70 "\t dept_name\t\tvarchar(20),\n",
71 "\t salary\t\t\tnumeric(8,2) check (salary > 29000),\n",
72 "\t primary key (ID),\n",
73 "\t foreign key (dept_name) references department\n",
74 "\t\ton delete set null\n",
75 "\t);\n",
76 "''',\n",
77 "'''\n",
78 "create table section\n",
79 "\t(course_id\t\tvarchar(8),\n",
80 " sec_id\t\t\tvarchar(8),\n",
81 "\t semester\t\tvarchar(6)\n",
82 "\t\tcheck (semester in ('Fall', 'Winter', 'Spring', 'Summer')),\n",
83 "\t year\t\t\tnumeric(4,0) check (year > 1701 and year < 2100),\n",
84 "\t building\t\tvarchar(15),\n",
85 "\t room_number\t\tvarchar(7),\n",
86 "\t time_slot_id\t\tvarchar(4),\n",
87 "\t primary key (course_id, sec_id, semester, year),\n",
88 "\t foreign key (course_id) references course\n",
89 "\t\ton delete cascade,\n",
90 "\t foreign key (building, room_number) references classroom\n",
91 "\t\ton delete set null\n",
92 "\t);\n",
93 "\n",
94 "''',\n",
95 "'''\n",
96 "create table teaches\n",
97 "\t(ID\t\t\tvarchar(5),\n",
98 "\t course_id\t\tvarchar(8),\n",
99 "\t sec_id\t\t\tvarchar(8),\n",
100 "\t semester\t\tvarchar(6),\n",
101 "\t year\t\t\tnumeric(4,0),\n",
102 "\t primary key (ID, course_id, sec_id, semester, year),\n",
103 "\t foreign key (course_id,sec_id, semester, year) references section\n",
104 "\t\ton delete cascade,\n",
105 "\t foreign key (ID) references instructor\n",
106 "\t\ton delete cascade\n",
107 "\t);\n",
108 "\n",
109 "''',\n",
110 "'''\n",
111 "create table student\n",
112 "\t(ID\t\t\tvarchar(5),\n",
113 "\t name\t\t\tvarchar(20) not null,\n",
114 "\t dept_name\t\tvarchar(20),\n",
115 "\t tot_cred\t\tnumeric(3,0) check (tot_cred >= 0),\n",
116 "\t primary key (ID),\n",
117 "\t foreign key (dept_name) references department\n",
118 "\t\ton delete set null\n",
119 "\t);\n",
120 "\n",
121 "''',\n",
122 "'''\n",
123 "create table takes\n",
124 "\t(ID\t\t\tvarchar(5),\n",
125 "\t course_id\t\tvarchar(8),\n",
126 "\t sec_id\t\t\tvarchar(8),\n",
127 "\t semester\t\tvarchar(6),\n",
128 "\t year\t\t\tnumeric(4,0),\n",
129 "\t grade\t\t varchar(2),\n",
130 "\t primary key (ID, course_id, sec_id, semester, year),\n",
131 "\t foreign key (course_id,sec_id, semester, year) references section\n",
132 "\t\ton delete cascade,\n",
133 "\t foreign key (ID) references student\n",
134 "\t\ton delete cascade\n",
135 "\t);\n",
136 "\n",
137 "''',\n",
138 "'''\n",
139 "create table advisor\n",
140 "\t(s_ID\t\t\tvarchar(5),\n",
141 "\t i_ID\t\t\tvarchar(5),\n",
142 "\t primary key (s_ID),\n",
143 "\t foreign key (i_ID) references instructor (ID)\n",
144 "\t\ton delete set null,\n",
145 "\t foreign key (s_ID) references student (ID)\n",
146 "\t\ton delete cascade\n",
147 "\t);\n",
148 "\n",
149 "''',\n",
150 "'''\n",
151 "create table time_slot\n",
152 "\t(time_slot_id\t\tvarchar(4),\n",
153 "\t day\t\t\tvarchar(1),\n",
154 "\t start_hr\t\tnumeric(2) check (start_hr >= 0 and start_hr < 24),\n",
155 "\t start_min\t\tnumeric(2) check (start_min >= 0 and start_min < 60),\n",
156 "\t end_hr\t\t\tnumeric(2) check (end_hr >= 0 and end_hr < 24),\n",
157 "\t end_min\t\tnumeric(2) check (end_min >= 0 and end_min < 60),\n",
158 "\t primary key (time_slot_id, day, start_hr, start_min)\n",
159 "\t);\n",
160 "\n",
161 "''',\n",
162 "'''\n",
163 "create table prereq\n",
164 "\t(course_id\t\tvarchar(8),\n",
165 "\t prereq_id\t\tvarchar(8),\n",
166 "\t primary key (course_id, prereq_id),\n",
167 "\t foreign key (course_id) references course\n",
168 "\t\ton delete cascade,\n",
169 "\t foreign key (prereq_id) references course\n",
170 "\t);\n",
171 "'''\n",
172 "]\n",
173 "\n",
174 "for create_statement in create_statements:\n",
175 " conn.execute(create_statement)\n"
176 ]
177 },
178 {
179 "cell_type": "code",
180 "execution_count": 284,
181 "metadata": {
182 "colab": {
183 "base_uri": "https://localhost:8080/"
184 },
185 "executionInfo": {
186 "elapsed": 4,
187 "status": "ok",
188 "timestamp": 1742331515743,
189 "user": {
190 "displayName": "Carlos J Corrada Bravo",
191 "userId": "05726728043350562289"
192 },
193 "user_tz": 240
194 },
195 "id": "hTHhb7JtGiHn",
196 "outputId": "1e12c597-e6f1-43c0-eb34-a1478ef5ff44"
197 },
198 "outputs": [
199 {
200 "name": "stdout",
201 "output_type": "stream",
202 "text": [
203 " type name tbl_name rootpage \\\n",
204 "0 table classroom classroom 2 \n",
205 "1 table department department 4 \n",
206 "2 table course course 6 \n",
207 "3 table instructor instructor 8 \n",
208 "4 table section section 10 \n",
209 "5 table teaches teaches 12 \n",
210 "6 table student student 14 \n",
211 "7 table takes takes 16 \n",
212 "8 table advisor advisor 18 \n",
213 "9 table time_slot time_slot 20 \n",
214 "10 table prereq prereq 22 \n",
215 "\n",
216 " sql \n",
217 "0 CREATE TABLE classroom\\n\\t(building\\t\\tvarchar... \n",
218 "1 CREATE TABLE department\\n\\t(dept_name\\t\\tvarch... \n",
219 "2 CREATE TABLE course\\n\\t(course_id\\t\\tvarchar(8... \n",
220 "3 CREATE TABLE instructor\\n\\t(ID\\t\\t\\tvarchar(5)... \n",
221 "4 CREATE TABLE section\\n\\t(course_id\\t\\tvarchar(... \n",
222 "5 CREATE TABLE teaches\\n\\t(ID\\t\\t\\tvarchar(5),\\n... \n",
223 "6 CREATE TABLE student\\n\\t(ID\\t\\t\\tvarchar(5),\\n... \n",
224 "7 CREATE TABLE takes\\n\\t(ID\\t\\t\\tvarchar(5),\\n\\t... \n",
225 "8 CREATE TABLE advisor\\n\\t(s_ID\\t\\t\\tvarchar(5),... \n",
226 "9 CREATE TABLE time_slot\\n\\t(time_slot_id\\t\\tvar... \n",
227 "10 CREATE TABLE prereq\\n\\t(course_id\\t\\tvarchar(8... \n"
228 ]
229 }
230 ],
231 "source": [
232 "query = \"SELECT * FROM sqlite_master WHERE type='table';\"\n",
233 "# query = \"SELECT * FROM sqlite_master WHERE 1;\"\n",
234 "result = pd.read_sql_query(query, conn)\n",
235 "print(result)"
236 ]
237 },
238 {
239 "cell_type": "code",
240 "execution_count": 285,
241 "metadata": {
242 "colab": {
243 "base_uri": "https://localhost:8080/"
244 },
245 "executionInfo": {
246 "elapsed": 9,
247 "status": "ok",
248 "timestamp": 1742331515753,
249 "user": {
250 "displayName": "Carlos J Corrada Bravo",
251 "userId": "05726728043350562289"
252 },
253 "user_tz": 240
254 },
255 "id": "bfOLtuPU_r8Q",
256 "outputId": "859a37fc-a56b-4c51-e30a-357073b7e404"
257 },
258 "outputs": [
259 {
260 "name": "stdout",
261 "output_type": "stream",
262 "text": [
263 "Empty DataFrame\n",
264 "Columns: [building, room_number, capacity]\n",
265 "Index: []\n"
266 ]
267 }
268 ],
269 "source": [
270 "query = \"SELECT * FROM classroom WHERE 1;\"\n",
271 "result = pd.read_sql_query(query, conn)\n",
272 "print(result)"
273 ]
274 },
275 {
276 "cell_type": "code",
277 "execution_count": 286,
278 "metadata": {
279 "colab": {
280 "base_uri": "https://localhost:8080/"
281 },
282 "executionInfo": {
283 "elapsed": 4,
284 "status": "ok",
285 "timestamp": 1742331515758,
286 "user": {
287 "displayName": "Carlos J Corrada Bravo",
288 "userId": "05726728043350562289"
289 },
290 "user_tz": 240
291 },
292 "id": "2N4KEUWJHXG6",
293 "outputId": "6713d359-4c59-4a5e-80ff-8b54b9f9866a"
294 },
295 "outputs": [
296 {
297 "data": {
298 "text/plain": [
299 "<sqlite3.Cursor at 0x7feb42c8bb40>"
300 ]
301 },
302 "execution_count": 286,
303 "metadata": {},
304 "output_type": "execute_result"
305 }
306 ],
307 "source": [
308 "\n",
309 "classroom_data = [('Science Bldg', '101', 60),\n",
310 "('Liberal Arts', '201', 30),\n",
311 "('Engineering', '301', 40)]\n",
312 "\n",
313 "conn.executemany('INSERT INTO classroom VALUES (?, ?, ?);', classroom_data)\n"
314 ]
315 },
316 {
317 "cell_type": "code",
318 "execution_count": 287,
319 "metadata": {
320 "colab": {
321 "base_uri": "https://localhost:8080/",
322 "height": 143
323 },
324 "executionInfo": {
325 "elapsed": 111,
326 "status": "ok",
327 "timestamp": 1742331516025,
328 "user": {
329 "displayName": "Carlos J Corrada Bravo",
330 "userId": "05726728043350562289"
331 },
332 "user_tz": 240
333 },
334 "id": "_xNadgL9LO2L",
335 "outputId": "6910f27d-b3e9-4c63-d9b4-31f2dd16be0d"
336 },
337 "outputs": [
338 {
339 "data": {
340 "text/html": [
341 "<div>\n",
342 "<style scoped>\n",
343 " .dataframe tbody tr th:only-of-type {\n",
344 " vertical-align: middle;\n",
345 " }\n",
346 "\n",
347 " .dataframe tbody tr th {\n",
348 " vertical-align: top;\n",
349 " }\n",
350 "\n",
351 " .dataframe thead th {\n",
352 " text-align: right;\n",
353 " }\n",
354 "</style>\n",
355 "<table border=\"1\" class=\"dataframe\">\n",
356 " <thead>\n",
357 " <tr style=\"text-align: right;\">\n",
358 " <th></th>\n",
359 " <th>building</th>\n",
360 " <th>room_number</th>\n",
361 " <th>capacity</th>\n",
362 " </tr>\n",
363 " </thead>\n",
364 " <tbody>\n",
365 " <tr>\n",
366 " <th>0</th>\n",
367 " <td>Science Bldg</td>\n",
368 " <td>101</td>\n",
369 " <td>60</td>\n",
370 " </tr>\n",
371 " <tr>\n",
372 " <th>1</th>\n",
373 " <td>Liberal Arts</td>\n",
374 " <td>201</td>\n",
375 " <td>30</td>\n",
376 " </tr>\n",
377 " <tr>\n",
378 " <th>2</th>\n",
379 " <td>Engineering</td>\n",
380 " <td>301</td>\n",
381 " <td>40</td>\n",
382 " </tr>\n",
383 " </tbody>\n",
384 "</table>\n",
385 "</div>"
386 ],
387 "text/plain": [
388 " building room_number capacity\n",
389 "0 Science Bldg 101 60\n",
390 "1 Liberal Arts 201 30\n",
391 "2 Engineering 301 40"
392 ]
393 },
394 "execution_count": 287,
395 "metadata": {},
396 "output_type": "execute_result"
397 }
398 ],
399 "source": [
400 "query = \"SELECT * from classroom;\"\n",
401 "result = pd.read_sql_query(query, conn)\n",
402 "result"
403 ]
404 },
405 {
406 "cell_type": "code",
407 "execution_count": 288,
408 "metadata": {
409 "colab": {
410 "base_uri": "https://localhost:8080/"
411 },
412 "executionInfo": {
413 "elapsed": 3,
414 "status": "ok",
415 "timestamp": 1742331516029,
416 "user": {
417 "displayName": "Carlos J Corrada Bravo",
418 "userId": "05726728043350562289"
419 },
420 "user_tz": 240
421 },
422 "id": "ccJ45ol6LeWb",
423 "outputId": "001f1430-6bb0-4ad7-e900-18644f88580d"
424 },
425 "outputs": [
426 {
427 "data": {
428 "text/plain": [
429 "<sqlite3.Cursor at 0x7feb42c897c0>"
430 ]
431 },
432 "execution_count": 288,
433 "metadata": {},
434 "output_type": "execute_result"
435 }
436 ],
437 "source": [
438 "query = \"delete from classroom;\"\n",
439 "conn.execute(query)\n"
440 ]
441 },
442 {
443 "cell_type": "code",
444 "execution_count": 289,
445 "metadata": {
446 "executionInfo": {
447 "elapsed": 12,
448 "status": "ok",
449 "timestamp": 1742331516042,
450 "user": {
451 "displayName": "Carlos J Corrada Bravo",
452 "userId": "05726728043350562289"
453 },
454 "user_tz": 240
455 },
456 "id": "CgessFBPjJkH"
457 },
458 "outputs": [],
459 "source": [
460 "ClassroomData = [('ZL7101', '011', 66), ('Yb5300', '597', 24), ('lT9721', '108', 10), ('De7318', '918', 21), ('eP9048', '475', 84)]\n",
461 "DepartmentData = [('Physics', 'Qb3378', 2711.38), ('Computer Science', 'TE1052', 34961.28), ('Mathematics', 'Fk2019', 5822.68), ('Biology', 'wX8320', 18377.14)]\n",
462 "CourseData = [('rjtj402', 'American history glass.', 'Physics', 3), ('EcYE597', 'Think keep sit spring.', 'Biology', 4), ('XlAB441', 'Voice billion.', 'Biology', 4), ('Pmwg764', 'Oil wall.', 'Computer Science', 1), ('Uefz257', 'Answer experience.', 'Computer Science', 1), ('fYSu038', 'War at.', 'Physics', 4), ('mgzm416', 'May stand guess.', 'Computer Science', 1), ('lLnt386', 'Spend move.', 'Biology', 4), ('viid787', 'Close certain national research.', 'Physics', 1), ('OfWY200', 'Trip always.', 'Biology', 1)]\n",
463 "InstructorData = [('65364', 'Tammy Henderson', 'Mathematics', 87706.72), ('92814', 'James Cunningham', 'Physics', 75124.4), ('81643', 'Russell Sanders', 'Biology', 89512.49), ('57372', 'Aaron Castillo', 'Mathematics', 47440.65), ('46726', 'Brooke Vargas', 'Computer Science', 61737.16)]\n",
464 "SectionData = [('viid787', '95', 'Fall', 2016, 'ZL7101', '011', 'nkH'), ('fYSu038', '97', 'Winter', 2022, 'De7318', '918', 'ZQE'), ('XlAB441', '07', 'Fall', 2002, 'eP9048', '918', 'QZg'), ('XlAB441', '05', 'Fall', 2017, 'Yb5300', '597', 'YEe'), ('rjtj402', '29', 'Spring', 2024, 'ZL7101', '475', 'vwW'), ('XlAB441', '44', 'Spring', 2014, 'ZL7101', '918', 'RRL'), ('Uefz257', '59', 'Fall', 2005, 'lT9721', '597', 'sKm'), ('Uefz257', '84', 'Winter', 2024, 'De7318', '011', 'eIS'), ('OfWY200', '93', 'Summer', 2024, 'lT9721', '918', 'vGv'), ('fYSu038', '57', 'Summer', 2015, 'ZL7101', '918', 'rfW')]\n",
465 "StudentData = [('89981', 'Dawn Taylor', 'Biology', 43), ('06003', 'Wanda Black', 'Biology', 132), ('68144', 'Susan Robinson', 'Biology', 75), ('81095', 'Crystal Woods', 'Mathematics', 31), ('28991', 'Travis Sanchez', 'Computer Science', 106), ('28865', 'Zachary Knight', 'Biology', 30), ('99705', 'Jacob Pittman', 'Biology', 26), ('60991', 'Marcus Gonzalez', 'Biology', 96), ('86970', 'Robert Clayton', 'Mathematics', 49), ('65273', 'Rebecca Lin', 'Mathematics', 27)]\n",
466 "TakesData = [('81095', 'Uefz257', '84', 'Winter', 2024, 'D'), ('99705', 'XlAB441', '07', 'Fall', 2002, 'C'), ('06003', 'viid787', '95', 'Fall', 2016, 'D'), ('28991', 'Uefz257', '59', 'Fall', 2005, 'D'), ('86970', 'Uefz257', '84', 'Winter', 2024, 'D'), ('68144', 'Uefz257', '84', 'Winter', 2024, 'C'), ('28865', 'fYSu038', '57', 'Summer', 2015, 'I'), ('86970', 'fYSu038', '57', 'Summer', 2015, 'B'), ('86970', 'XlAB441', '07', 'Fall', 2002, 'F'), ('60991', 'Uefz257', '84', 'Winter', 2024, 'I')]\n",
467 "AdvisorData = [('89981', '65364'), ('68144', '92814'), ('99705', '81643'), ('81095', '57372')]\n",
468 "TimeSlotData = [('wLx', 'Su', 8, 34, 20, 58), ('Ale', 'W', 13, 20, 21, 36), ('Obn', 'Su', 4, 53, 17, 5), ('XmM', 'Su', 8, 17, 13, 25), ('NJU', 'Su', 18, 17, 16, 7)]\n",
469 "PrereqData = [('rjtj402', 'lLnt386'), ('rjtj402', 'fYSu038'), ('XlAB441', 'lLnt386'), ('Uefz257', 'viid787'), ('mgzm416', 'XlAB441'), ('lLnt386', 'Uefz257')]\n",
470 "TeachesData = [('57372', 'viid787', '95', 'Fall', 2016), ('57372', 'fYSu038', '97', 'Winter', 2022), ('81643', 'XlAB441', '07', 'Fall', 2002), ('57372', 'XlAB441', '05', 'Fall', 2017), ('57372', 'rjtj402', '29', 'Spring', 2024), ('92814', 'XlAB441', '44', 'Spring', 2014), ('57372', 'Uefz257', '59', 'Fall', 2005), ('57372', 'Uefz257', '84', 'Winter', 2024), ('92814', 'OfWY200', '93', 'Summer', 2024), ('92814', 'fYSu038', '57', 'Summer', 2015)]\n"
471 ]
472 },
473 {
474 "cell_type": "code",
475 "execution_count": 290,
476 "metadata": {
477 "colab": {
478 "base_uri": "https://localhost:8080/"
479 },
480 "executionInfo": {
481 "elapsed": 128,
482 "status": "ok",
483 "timestamp": 1742331516171,
484 "user": {
485 "displayName": "Carlos J Corrada Bravo",
486 "userId": "05726728043350562289"
487 },
488 "user_tz": 240
489 },
490 "id": "1Jw9zoPbjTNu",
491 "outputId": "d3fb519b-0aac-445f-f897-e22740024c8e"
492 },
493 "outputs": [
494 {
495 "data": {
496 "text/plain": [
497 "<sqlite3.Cursor at 0x7feb42c89840>"
498 ]
499 },
500 "execution_count": 290,
501 "metadata": {},
502 "output_type": "execute_result"
503 }
504 ],
505 "source": [
506 "conn.executemany('INSERT INTO classroom VALUES (?, ?, ?);', ClassroomData)\n",
507 "conn.executemany('INSERT INTO department VALUES (?, ?, ?);', DepartmentData)\n",
508 "conn.executemany('INSERT INTO course VALUES (?, ?, ?, ?);', CourseData)\n",
509 "conn.executemany('INSERT INTO instructor VALUES (?, ?, ?, ?);', InstructorData)\n",
510 "conn.executemany('INSERT INTO section VALUES (?, ?, ?, ?, ?, ?, ?);', SectionData)\n",
511 "conn.executemany('INSERT INTO student VALUES (?, ?, ?, ?);', StudentData)\n",
512 "conn.executemany('INSERT INTO takes VALUES (?, ?, ?,?, ?, ?);', TakesData)\n",
513 "conn.executemany('INSERT INTO advisor VALUES (?, ?);', AdvisorData)\n",
514 "conn.executemany('INSERT INTO time_slot VALUES (?, ?, ?, ?, ?, ?);', TimeSlotData)\n",
515 "conn.executemany('INSERT INTO prereq VALUES (?, ?);', PrereqData)\n",
516 "conn.executemany('INSERT INTO teaches VALUES (?, ?, ?, ?, ?);', TeachesData)\n"
517 ]
518 },
519 {
520 "cell_type": "code",
521 "execution_count": 255,
522 "metadata": {
523 "colab": {
524 "base_uri": "https://localhost:8080/"
525 },
526 "executionInfo": {
527 "elapsed": 3,
528 "status": "ok",
529 "timestamp": 1742331516175,
530 "user": {
531 "displayName": "Carlos J Corrada Bravo",
532 "userId": "05726728043350562289"
533 },
534 "user_tz": 240
535 },
536 "id": "FE92bKHZE3Fe",
537 "outputId": "52560fce-69f4-403f-d8cc-8dc3a91d5a1b"
538 },
539 "outputs": [
540 {
541 "data": {
542 "text/plain": [
543 "[('viid787', '95', 'Fall', 2016, 'ZL7101', '011', 'nkH'),\n",
544 " ('fYSu038', '97', 'Winter', 2022, 'De7318', '918', 'ZQE'),\n",
545 " ('XlAB441', '07', 'Fall', 2002, 'eP9048', '918', 'QZg'),\n",
546 " ('XlAB441', '05', 'Fall', 2017, 'Yb5300', '597', 'YEe'),\n",
547 " ('rjtj402', '29', 'Spring', 2024, 'ZL7101', '475', 'vwW'),\n",
548 " ('XlAB441', '44', 'Spring', 2014, 'ZL7101', '918', 'RRL'),\n",
549 " ('Uefz257', '59', 'Fall', 2005, 'lT9721', '597', 'sKm'),\n",
550 " ('Uefz257', '84', 'Winter', 2024, 'De7318', '011', 'eIS'),\n",
551 " ('OfWY200', '93', 'Summer', 2024, 'lT9721', '918', 'vGv'),\n",
552 " ('fYSu038', '57', 'Summer', 2015, 'ZL7101', '918', 'rfW')]"
553 ]
554 },
555 "execution_count": 255,
556 "metadata": {},
557 "output_type": "execute_result"
558 }
559 ],
560 "source": [
561 "SectionData"
562 ]
563 },
564 {
565 "cell_type": "code",
566 "execution_count": 256,
567 "metadata": {
568 "colab": {
569 "base_uri": "https://localhost:8080/",
570 "height": 363
571 },
572 "executionInfo": {
573 "elapsed": 12,
574 "status": "ok",
575 "timestamp": 1742331516188,
576 "user": {
577 "displayName": "Carlos J Corrada Bravo",
578 "userId": "05726728043350562289"
579 },
580 "user_tz": 240
581 },
582 "id": "7voguoHnkJZr",
583 "outputId": "4ae4a1af-7941-4f21-b2b0-ae4110826808"
584 },
585 "outputs": [
586 {
587 "data": {
588 "text/html": [
589 "<div>\n",
590 "<style scoped>\n",
591 " .dataframe tbody tr th:only-of-type {\n",
592 " vertical-align: middle;\n",
593 " }\n",
594 "\n",
595 " .dataframe tbody tr th {\n",
596 " vertical-align: top;\n",
597 " }\n",
598 "\n",
599 " .dataframe thead th {\n",
600 " text-align: right;\n",
601 " }\n",
602 "</style>\n",
603 "<table border=\"1\" class=\"dataframe\">\n",
604 " <thead>\n",
605 " <tr style=\"text-align: right;\">\n",
606 " <th></th>\n",
607 " <th>course_id</th>\n",
608 " <th>sec_id</th>\n",
609 " <th>semester</th>\n",
610 " <th>year</th>\n",
611 " <th>building</th>\n",
612 " <th>room_number</th>\n",
613 " <th>time_slot_id</th>\n",
614 " </tr>\n",
615 " </thead>\n",
616 " <tbody>\n",
617 " <tr>\n",
618 " <th>0</th>\n",
619 " <td>viid787</td>\n",
620 " <td>95</td>\n",
621 " <td>Fall</td>\n",
622 " <td>2016</td>\n",
623 " <td>ZL7101</td>\n",
624 " <td>011</td>\n",
625 " <td>nkH</td>\n",
626 " </tr>\n",
627 " <tr>\n",
628 " <th>1</th>\n",
629 " <td>fYSu038</td>\n",
630 " <td>97</td>\n",
631 " <td>Winter</td>\n",
632 " <td>2022</td>\n",
633 " <td>De7318</td>\n",
634 " <td>918</td>\n",
635 " <td>ZQE</td>\n",
636 " </tr>\n",
637 " <tr>\n",
638 " <th>2</th>\n",
639 " <td>XlAB441</td>\n",
640 " <td>07</td>\n",
641 " <td>Fall</td>\n",
642 " <td>2002</td>\n",
643 " <td>eP9048</td>\n",
644 " <td>918</td>\n",
645 " <td>QZg</td>\n",
646 " </tr>\n",
647 " <tr>\n",
648 " <th>3</th>\n",
649 " <td>XlAB441</td>\n",
650 " <td>05</td>\n",
651 " <td>Fall</td>\n",
652 " <td>2017</td>\n",
653 " <td>Yb5300</td>\n",
654 " <td>597</td>\n",
655 " <td>YEe</td>\n",
656 " </tr>\n",
657 " <tr>\n",
658 " <th>4</th>\n",
659 " <td>rjtj402</td>\n",
660 " <td>29</td>\n",
661 " <td>Spring</td>\n",
662 " <td>2024</td>\n",
663 " <td>ZL7101</td>\n",
664 " <td>475</td>\n",
665 " <td>vwW</td>\n",
666 " </tr>\n",
667 " <tr>\n",
668 " <th>5</th>\n",
669 " <td>XlAB441</td>\n",
670 " <td>44</td>\n",
671 " <td>Spring</td>\n",
672 " <td>2014</td>\n",
673 " <td>ZL7101</td>\n",
674 " <td>918</td>\n",
675 " <td>RRL</td>\n",
676 " </tr>\n",
677 " <tr>\n",
678 " <th>6</th>\n",
679 " <td>Uefz257</td>\n",
680 " <td>59</td>\n",
681 " <td>Fall</td>\n",
682 " <td>2005</td>\n",
683 " <td>lT9721</td>\n",
684 " <td>597</td>\n",
685 " <td>sKm</td>\n",
686 " </tr>\n",
687 " <tr>\n",
688 " <th>7</th>\n",
689 " <td>Uefz257</td>\n",
690 " <td>84</td>\n",
691 " <td>Winter</td>\n",
692 " <td>2024</td>\n",
693 " <td>De7318</td>\n",
694 " <td>011</td>\n",
695 " <td>eIS</td>\n",
696 " </tr>\n",
697 " <tr>\n",
698 " <th>8</th>\n",
699 " <td>OfWY200</td>\n",
700 " <td>93</td>\n",
701 " <td>Summer</td>\n",
702 " <td>2024</td>\n",
703 " <td>lT9721</td>\n",
704 " <td>918</td>\n",
705 " <td>vGv</td>\n",
706 " </tr>\n",
707 " <tr>\n",
708 " <th>9</th>\n",
709 " <td>fYSu038</td>\n",
710 " <td>57</td>\n",
711 " <td>Summer</td>\n",
712 " <td>2015</td>\n",
713 " <td>ZL7101</td>\n",
714 " <td>918</td>\n",
715 " <td>rfW</td>\n",
716 " </tr>\n",
717 " </tbody>\n",
718 "</table>\n",
719 "</div>"
720 ],
721 "text/plain": [
722 " course_id sec_id semester year building room_number time_slot_id\n",
723 "0 viid787 95 Fall 2016 ZL7101 011 nkH\n",
724 "1 fYSu038 97 Winter 2022 De7318 918 ZQE\n",
725 "2 XlAB441 07 Fall 2002 eP9048 918 QZg\n",
726 "3 XlAB441 05 Fall 2017 Yb5300 597 YEe\n",
727 "4 rjtj402 29 Spring 2024 ZL7101 475 vwW\n",
728 "5 XlAB441 44 Spring 2014 ZL7101 918 RRL\n",
729 "6 Uefz257 59 Fall 2005 lT9721 597 sKm\n",
730 "7 Uefz257 84 Winter 2024 De7318 011 eIS\n",
731 "8 OfWY200 93 Summer 2024 lT9721 918 vGv\n",
732 "9 fYSu038 57 Summer 2015 ZL7101 918 rfW"
733 ]
734 },
735 "execution_count": 256,
736 "metadata": {},
737 "output_type": "execute_result"
738 }
739 ],
740 "source": [
741 "query = \"SELECT * from section;\"\n",
742 "result = pd.read_sql_query(query, conn)\n",
743 "result"
744 ]
745 },
746 {
747 "cell_type": "code",
748 "execution_count": 257,
749 "metadata": {
750 "colab": {
751 "base_uri": "https://localhost:8080/",
752 "height": 363
753 },
754 "executionInfo": {
755 "elapsed": 42,
756 "status": "ok",
757 "timestamp": 1742331639399,
758 "user": {
759 "displayName": "Carlos J Corrada Bravo",
760 "userId": "05726728043350562289"
761 },
762 "user_tz": 240
763 },
764 "id": "-lqhRNy7EQUN",
765 "outputId": "a8834b4d-ba6b-4096-c309-40be61ffecfa"
766 },
767 "outputs": [
768 {
769 "data": {
770 "text/html": [
771 "<div>\n",
772 "<style scoped>\n",
773 " .dataframe tbody tr th:only-of-type {\n",
774 " vertical-align: middle;\n",
775 " }\n",
776 "\n",
777 " .dataframe tbody tr th {\n",
778 " vertical-align: top;\n",
779 " }\n",
780 "\n",
781 " .dataframe thead th {\n",
782 " text-align: right;\n",
783 " }\n",
784 "</style>\n",
785 "<table border=\"1\" class=\"dataframe\">\n",
786 " <thead>\n",
787 " <tr style=\"text-align: right;\">\n",
788 " <th></th>\n",
789 " <th>ID</th>\n",
790 " <th>course_id</th>\n",
791 " <th>sec_id</th>\n",
792 " <th>semester</th>\n",
793 " <th>year</th>\n",
794 " <th>grade</th>\n",
795 " </tr>\n",
796 " </thead>\n",
797 " <tbody>\n",
798 " <tr>\n",
799 " <th>0</th>\n",
800 " <td>81095</td>\n",
801 " <td>Uefz257</td>\n",
802 " <td>84</td>\n",
803 " <td>Winter</td>\n",
804 " <td>2024</td>\n",
805 " <td>D</td>\n",
806 " </tr>\n",
807 " <tr>\n",
808 " <th>1</th>\n",
809 " <td>99705</td>\n",
810 " <td>XlAB441</td>\n",
811 " <td>07</td>\n",
812 " <td>Fall</td>\n",
813 " <td>2002</td>\n",
814 " <td>C</td>\n",
815 " </tr>\n",
816 " <tr>\n",
817 " <th>2</th>\n",
818 " <td>06003</td>\n",
819 " <td>viid787</td>\n",
820 " <td>95</td>\n",
821 " <td>Fall</td>\n",
822 " <td>2016</td>\n",
823 " <td>D</td>\n",
824 " </tr>\n",
825 " <tr>\n",
826 " <th>3</th>\n",
827 " <td>28991</td>\n",
828 " <td>Uefz257</td>\n",
829 " <td>59</td>\n",
830 " <td>Fall</td>\n",
831 " <td>2005</td>\n",
832 " <td>D</td>\n",
833 " </tr>\n",
834 " <tr>\n",
835 " <th>4</th>\n",
836 " <td>86970</td>\n",
837 " <td>Uefz257</td>\n",
838 " <td>84</td>\n",
839 " <td>Winter</td>\n",
840 " <td>2024</td>\n",
841 " <td>D</td>\n",
842 " </tr>\n",
843 " <tr>\n",
844 " <th>5</th>\n",
845 " <td>68144</td>\n",
846 " <td>Uefz257</td>\n",
847 " <td>84</td>\n",
848 " <td>Winter</td>\n",
849 " <td>2024</td>\n",
850 " <td>C</td>\n",
851 " </tr>\n",
852 " <tr>\n",
853 " <th>6</th>\n",
854 " <td>28865</td>\n",
855 " <td>fYSu038</td>\n",
856 " <td>57</td>\n",
857 " <td>Summer</td>\n",
858 " <td>2015</td>\n",
859 " <td>I</td>\n",
860 " </tr>\n",
861 " <tr>\n",
862 " <th>7</th>\n",
863 " <td>86970</td>\n",
864 " <td>fYSu038</td>\n",
865 " <td>57</td>\n",
866 " <td>Summer</td>\n",
867 " <td>2015</td>\n",
868 " <td>B</td>\n",
869 " </tr>\n",
870 " <tr>\n",
871 " <th>8</th>\n",
872 " <td>86970</td>\n",
873 " <td>XlAB441</td>\n",
874 " <td>07</td>\n",
875 " <td>Fall</td>\n",
876 " <td>2002</td>\n",
877 " <td>F</td>\n",
878 " </tr>\n",
879 " <tr>\n",
880 " <th>9</th>\n",
881 " <td>60991</td>\n",
882 " <td>Uefz257</td>\n",
883 " <td>84</td>\n",
884 " <td>Winter</td>\n",
885 " <td>2024</td>\n",
886 " <td>I</td>\n",
887 " </tr>\n",
888 " </tbody>\n",
889 "</table>\n",
890 "</div>"
891 ],
892 "text/plain": [
893 " ID course_id sec_id semester year grade\n",
894 "0 81095 Uefz257 84 Winter 2024 D\n",
895 "1 99705 XlAB441 07 Fall 2002 C\n",
896 "2 06003 viid787 95 Fall 2016 D\n",
897 "3 28991 Uefz257 59 Fall 2005 D\n",
898 "4 86970 Uefz257 84 Winter 2024 D\n",
899 "5 68144 Uefz257 84 Winter 2024 C\n",
900 "6 28865 fYSu038 57 Summer 2015 I\n",
901 "7 86970 fYSu038 57 Summer 2015 B\n",
902 "8 86970 XlAB441 07 Fall 2002 F\n",
903 "9 60991 Uefz257 84 Winter 2024 I"
904 ]
905 },
906 "execution_count": 257,
907 "metadata": {},
908 "output_type": "execute_result"
909 }
910 ],
911 "source": [
912 "query = \"SELECT * from takes;\"\n",
913 "result = pd.read_sql_query(query, conn)\n",
914 "result"
915 ]
916 },
917 {
918 "cell_type": "code",
919 "execution_count": 258,
920 "metadata": {
921 "executionInfo": {
922 "elapsed": 3,
923 "status": "ok",
924 "timestamp": 1742331516202,
925 "user": {
926 "displayName": "Carlos J Corrada Bravo",
927 "userId": "05726728043350562289"
928 },
929 "user_tz": 240
930 },
931 "id": "N31sz6MBlSRU"
932 },
933 "outputs": [],
934 "source": [
935 "# conn.close()\n"
936 ]
937 },
938 {
939 "cell_type": "markdown",
940 "metadata": {
941 "id": "GIft6pG9tw26"
942 },
943 "source": [
944 "#### Ejercicios:\n",
945 "\n",
946 "1. Encuentra los nombres de todos los instructores.\n",
947 "2. Lista todos los cursos con menos de 3 créditos.\n",
948 "3. Muestra los nombres de los estudiantes junto con los nombres de los departamentos a los que pertenecen.\n",
949 "4. Calcula el salario promedio de los instructores en el departamento de 'Computer Science'.\n",
950 "5. Encuentra el número de estudiantes inscritos en cada departamento.\n",
951 "6. Muestra los nombres de los estudiantes y los títulos de los cursos que están tomando este semestre.\n",
952 "7. Lista estudiantes que toman o han tomado clases en el edificio \"ZL7101\"\n",
953 "8. Lista los cursos que tienen más estudiantes inscritos que el promedio de inscripciones de todos los cursos.\n",
954 "9. Encuentra los instructores que enseñan más de un curso, incluyendo los títulos de los cursos que enseñan.\n",
955 "10. Entra datos para que la pregunta 6 no salga vacía.\n",
956 "\n",
957 "#### Instrucciones:\n",
958 "- Utiliza el esquema de base de datos proporcionado para realizar cada uno de los ejercicios.\n",
959 "- Verifica tus resultados ejecutando las consultas.\n",
960 "- Intenta resolver los ejercicios en orden, ya que están diseñados para aumentar gradualmente en dificultad."
961 ]
962 },
963 {
964 "cell_type": "markdown",
965 "metadata": {
966 "id": "aM7NyMASBxdN"
967 },
968 "source": [
969 "1. Encuentra los nombres de todos los instructores.\n"
970 ]
971 },
972 {
973 "cell_type": "code",
974 "execution_count": 259,
975 "metadata": {},
976 "outputs": [
977 {
978 "data": {
979 "text/html": [
980 "<div>\n",
981 "<style scoped>\n",
982 " .dataframe tbody tr th:only-of-type {\n",
983 " vertical-align: middle;\n",
984 " }\n",
985 "\n",
986 " .dataframe tbody tr th {\n",
987 " vertical-align: top;\n",
988 " }\n",
989 "\n",
990 " .dataframe thead th {\n",
991 " text-align: right;\n",
992 " }\n",
993 "</style>\n",
994 "<table border=\"1\" class=\"dataframe\">\n",
995 " <thead>\n",
996 " <tr style=\"text-align: right;\">\n",
997 " <th></th>\n",
998 " <th>name</th>\n",
999 " </tr>\n",
1000 " </thead>\n",
1001 " <tbody>\n",
1002 " <tr>\n",
1003 " <th>0</th>\n",
1004 " <td>Tammy Henderson</td>\n",
1005 " </tr>\n",
1006 " <tr>\n",
1007 " <th>1</th>\n",
1008 " <td>James Cunningham</td>\n",
1009 " </tr>\n",
1010 " <tr>\n",
1011 " <th>2</th>\n",
1012 " <td>Russell Sanders</td>\n",
1013 " </tr>\n",
1014 " <tr>\n",
1015 " <th>3</th>\n",
1016 " <td>Aaron Castillo</td>\n",
1017 " </tr>\n",
1018 " <tr>\n",
1019 " <th>4</th>\n",
1020 " <td>Brooke Vargas</td>\n",
1021 " </tr>\n",
1022 " </tbody>\n",
1023 "</table>\n",
1024 "</div>"
1025 ],
1026 "text/plain": [
1027 " name\n",
1028 "0 Tammy Henderson\n",
1029 "1 James Cunningham\n",
1030 "2 Russell Sanders\n",
1031 "3 Aaron Castillo\n",
1032 "4 Brooke Vargas"
1033 ]
1034 },
1035 "execution_count": 259,
1036 "metadata": {},
1037 "output_type": "execute_result"
1038 }
1039 ],
1040 "source": [
1041 "query = \"SELECT name from instructor;\"\n",
1042 "result = pd.read_sql_query(query, conn)\n",
1043 "result"
1044 ]
1045 },
1046 {
1047 "cell_type": "markdown",
1048 "metadata": {},
1049 "source": [
1050 "2. Lista todos los cursos con menos de 3 créditos."
1051 ]
1052 },
1053 {
1054 "cell_type": "code",
1055 "execution_count": 260,
1056 "metadata": {},
1057 "outputs": [
1058 {
1059 "data": {
1060 "text/html": [
1061 "<div>\n",
1062 "<style scoped>\n",
1063 " .dataframe tbody tr th:only-of-type {\n",
1064 " vertical-align: middle;\n",
1065 " }\n",
1066 "\n",
1067 " .dataframe tbody tr th {\n",
1068 " vertical-align: top;\n",
1069 " }\n",
1070 "\n",
1071 " .dataframe thead th {\n",
1072 " text-align: right;\n",
1073 " }\n",
1074 "</style>\n",
1075 "<table border=\"1\" class=\"dataframe\">\n",
1076 " <thead>\n",
1077 " <tr style=\"text-align: right;\">\n",
1078 " <th></th>\n",
1079 " <th>course_id</th>\n",
1080 " <th>title</th>\n",
1081 " <th>dept_name</th>\n",
1082 " <th>credits</th>\n",
1083 " </tr>\n",
1084 " </thead>\n",
1085 " <tbody>\n",
1086 " <tr>\n",
1087 " <th>0</th>\n",
1088 " <td>Pmwg764</td>\n",
1089 " <td>Oil wall.</td>\n",
1090 " <td>Computer Science</td>\n",
1091 " <td>1</td>\n",
1092 " </tr>\n",
1093 " <tr>\n",
1094 " <th>1</th>\n",
1095 " <td>Uefz257</td>\n",
1096 " <td>Answer experience.</td>\n",
1097 " <td>Computer Science</td>\n",
1098 " <td>1</td>\n",
1099 " </tr>\n",
1100 " <tr>\n",
1101 " <th>2</th>\n",
1102 " <td>mgzm416</td>\n",
1103 " <td>May stand guess.</td>\n",
1104 " <td>Computer Science</td>\n",
1105 " <td>1</td>\n",
1106 " </tr>\n",
1107 " <tr>\n",
1108 " <th>3</th>\n",
1109 " <td>viid787</td>\n",
1110 " <td>Close certain national research.</td>\n",
1111 " <td>Physics</td>\n",
1112 " <td>1</td>\n",
1113 " </tr>\n",
1114 " <tr>\n",
1115 " <th>4</th>\n",
1116 " <td>OfWY200</td>\n",
1117 " <td>Trip always.</td>\n",
1118 " <td>Biology</td>\n",
1119 " <td>1</td>\n",
1120 " </tr>\n",
1121 " </tbody>\n",
1122 "</table>\n",
1123 "</div>"
1124 ],
1125 "text/plain": [
1126 " course_id title dept_name credits\n",
1127 "0 Pmwg764 Oil wall. Computer Science 1\n",
1128 "1 Uefz257 Answer experience. Computer Science 1\n",
1129 "2 mgzm416 May stand guess. Computer Science 1\n",
1130 "3 viid787 Close certain national research. Physics 1\n",
1131 "4 OfWY200 Trip always. Biology 1"
1132 ]
1133 },
1134 "execution_count": 260,
1135 "metadata": {},
1136 "output_type": "execute_result"
1137 }
1138 ],
1139 "source": [
1140 "query = '''\n",
1141 "SELECT * from course\n",
1142 "where course.credits <3 ;\n",
1143 "'''\n",
1144 "result = pd.read_sql_query(query, conn)\n",
1145 "result"
1146 ]
1147 },
1148 {
1149 "cell_type": "markdown",
1150 "metadata": {},
1151 "source": [
1152 "3. Muestra los nombres de los estudiantes junto con los nombres de los departamentos a los que pertenecen."
1153 ]
1154 },
1155 {
1156 "cell_type": "code",
1157 "execution_count": 261,
1158 "metadata": {},
1159 "outputs": [
1160 {
1161 "data": {
1162 "text/html": [
1163 "<div>\n",
1164 "<style scoped>\n",
1165 " .dataframe tbody tr th:only-of-type {\n",
1166 " vertical-align: middle;\n",
1167 " }\n",
1168 "\n",
1169 " .dataframe tbody tr th {\n",
1170 " vertical-align: top;\n",
1171 " }\n",
1172 "\n",
1173 " .dataframe thead th {\n",
1174 " text-align: right;\n",
1175 " }\n",
1176 "</style>\n",
1177 "<table border=\"1\" class=\"dataframe\">\n",
1178 " <thead>\n",
1179 " <tr style=\"text-align: right;\">\n",
1180 " <th></th>\n",
1181 " <th>name</th>\n",
1182 " <th>dept_name</th>\n",
1183 " </tr>\n",
1184 " </thead>\n",
1185 " <tbody>\n",
1186 " <tr>\n",
1187 " <th>0</th>\n",
1188 " <td>Dawn Taylor</td>\n",
1189 " <td>Biology</td>\n",
1190 " </tr>\n",
1191 " <tr>\n",
1192 " <th>1</th>\n",
1193 " <td>Wanda Black</td>\n",
1194 " <td>Biology</td>\n",
1195 " </tr>\n",
1196 " <tr>\n",
1197 " <th>2</th>\n",
1198 " <td>Susan Robinson</td>\n",
1199 " <td>Biology</td>\n",
1200 " </tr>\n",
1201 " <tr>\n",
1202 " <th>3</th>\n",
1203 " <td>Crystal Woods</td>\n",
1204 " <td>Mathematics</td>\n",
1205 " </tr>\n",
1206 " <tr>\n",
1207 " <th>4</th>\n",
1208 " <td>Travis Sanchez</td>\n",
1209 " <td>Computer Science</td>\n",
1210 " </tr>\n",
1211 " <tr>\n",
1212 " <th>5</th>\n",
1213 " <td>Zachary Knight</td>\n",
1214 " <td>Biology</td>\n",
1215 " </tr>\n",
1216 " <tr>\n",
1217 " <th>6</th>\n",
1218 " <td>Jacob Pittman</td>\n",
1219 " <td>Biology</td>\n",
1220 " </tr>\n",
1221 " <tr>\n",
1222 " <th>7</th>\n",
1223 " <td>Marcus Gonzalez</td>\n",
1224 " <td>Biology</td>\n",
1225 " </tr>\n",
1226 " <tr>\n",
1227 " <th>8</th>\n",
1228 " <td>Robert Clayton</td>\n",
1229 " <td>Mathematics</td>\n",
1230 " </tr>\n",
1231 " <tr>\n",
1232 " <th>9</th>\n",
1233 " <td>Rebecca Lin</td>\n",
1234 " <td>Mathematics</td>\n",
1235 " </tr>\n",
1236 " </tbody>\n",
1237 "</table>\n",
1238 "</div>"
1239 ],
1240 "text/plain": [
1241 " name dept_name\n",
1242 "0 Dawn Taylor Biology\n",
1243 "1 Wanda Black Biology\n",
1244 "2 Susan Robinson Biology\n",
1245 "3 Crystal Woods Mathematics\n",
1246 "4 Travis Sanchez Computer Science\n",
1247 "5 Zachary Knight Biology\n",
1248 "6 Jacob Pittman Biology\n",
1249 "7 Marcus Gonzalez Biology\n",
1250 "8 Robert Clayton Mathematics\n",
1251 "9 Rebecca Lin Mathematics"
1252 ]
1253 },
1254 "execution_count": 261,
1255 "metadata": {},
1256 "output_type": "execute_result"
1257 }
1258 ],
1259 "source": [
1260 "query = '''\n",
1261 "SELECT name, dept_name from student;\n",
1262 "'''\n",
1263 "result = pd.read_sql_query(query, conn)\n",
1264 "result"
1265 ]
1266 },
1267 {
1268 "cell_type": "markdown",
1269 "metadata": {},
1270 "source": [
1271 "4. Calcula el salario promedio de los instructores en el departamento de 'Computer Science'."
1272 ]
1273 },
1274 {
1275 "cell_type": "code",
1276 "execution_count": 262,
1277 "metadata": {},
1278 "outputs": [
1279 {
1280 "data": {
1281 "text/html": [
1282 "<div>\n",
1283 "<style scoped>\n",
1284 " .dataframe tbody tr th:only-of-type {\n",
1285 " vertical-align: middle;\n",
1286 " }\n",
1287 "\n",
1288 " .dataframe tbody tr th {\n",
1289 " vertical-align: top;\n",
1290 " }\n",
1291 "\n",
1292 " .dataframe thead th {\n",
1293 " text-align: right;\n",
1294 " }\n",
1295 "</style>\n",
1296 "<table border=\"1\" class=\"dataframe\">\n",
1297 " <thead>\n",
1298 " <tr style=\"text-align: right;\">\n",
1299 " <th></th>\n",
1300 " <th>avg(salary)</th>\n",
1301 " </tr>\n",
1302 " </thead>\n",
1303 " <tbody>\n",
1304 " <tr>\n",
1305 " <th>0</th>\n",
1306 " <td>61737.16</td>\n",
1307 " </tr>\n",
1308 " </tbody>\n",
1309 "</table>\n",
1310 "</div>"
1311 ],
1312 "text/plain": [
1313 " avg(salary)\n",
1314 "0 61737.16"
1315 ]
1316 },
1317 "execution_count": 262,
1318 "metadata": {},
1319 "output_type": "execute_result"
1320 }
1321 ],
1322 "source": [
1323 "query = '''\n",
1324 "SELECT avg(salary) from instructor\n",
1325 "where instructor.dept_name = \"Computer Science\"\n",
1326 "'''\n",
1327 "result = pd.read_sql_query(query, conn)\n",
1328 "result"
1329 ]
1330 },
1331 {
1332 "cell_type": "markdown",
1333 "metadata": {},
1334 "source": [
1335 "5. Encuentra el número de estudiantes inscritos en cada departamento."
1336 ]
1337 },
1338 {
1339 "cell_type": "code",
1340 "execution_count": 263,
1341 "metadata": {},
1342 "outputs": [
1343 {
1344 "data": {
1345 "text/html": [
1346 "<div>\n",
1347 "<style scoped>\n",
1348 " .dataframe tbody tr th:only-of-type {\n",
1349 " vertical-align: middle;\n",
1350 " }\n",
1351 "\n",
1352 " .dataframe tbody tr th {\n",
1353 " vertical-align: top;\n",
1354 " }\n",
1355 "\n",
1356 " .dataframe thead th {\n",
1357 " text-align: right;\n",
1358 " }\n",
1359 "</style>\n",
1360 "<table border=\"1\" class=\"dataframe\">\n",
1361 " <thead>\n",
1362 " <tr style=\"text-align: right;\">\n",
1363 " <th></th>\n",
1364 " <th>dept_name</th>\n",
1365 " <th>count</th>\n",
1366 " </tr>\n",
1367 " </thead>\n",
1368 " <tbody>\n",
1369 " <tr>\n",
1370 " <th>0</th>\n",
1371 " <td>Biology</td>\n",
1372 " <td>6</td>\n",
1373 " </tr>\n",
1374 " <tr>\n",
1375 " <th>1</th>\n",
1376 " <td>Computer Science</td>\n",
1377 " <td>1</td>\n",
1378 " </tr>\n",
1379 " <tr>\n",
1380 " <th>2</th>\n",
1381 " <td>Mathematics</td>\n",
1382 " <td>3</td>\n",
1383 " </tr>\n",
1384 " </tbody>\n",
1385 "</table>\n",
1386 "</div>"
1387 ],
1388 "text/plain": [
1389 " dept_name count\n",
1390 "0 Biology 6\n",
1391 "1 Computer Science 1\n",
1392 "2 Mathematics 3"
1393 ]
1394 },
1395 "execution_count": 263,
1396 "metadata": {},
1397 "output_type": "execute_result"
1398 }
1399 ],
1400 "source": [
1401 "query = '''\n",
1402 "SELECT distinct dept_name, count(dept_name) as count from student\n",
1403 "group by student.dept_name;\n",
1404 "'''\n",
1405 "result = pd.read_sql_query(query, conn)\n",
1406 "result"
1407 ]
1408 },
1409 {
1410 "cell_type": "markdown",
1411 "metadata": {},
1412 "source": [
1413 "6. Muestra los nombres de los estudiantes y los títulos de los cursos que están tomando este semestre."
1414 ]
1415 },
1416 {
1417 "cell_type": "code",
1418 "execution_count": 270,
1419 "metadata": {},
1420 "outputs": [
1421 {
1422 "data": {
1423 "text/html": [
1424 "<div>\n",
1425 "<style scoped>\n",
1426 " .dataframe tbody tr th:only-of-type {\n",
1427 " vertical-align: middle;\n",
1428 " }\n",
1429 "\n",
1430 " .dataframe tbody tr th {\n",
1431 " vertical-align: top;\n",
1432 " }\n",
1433 "\n",
1434 " .dataframe thead th {\n",
1435 " text-align: right;\n",
1436 " }\n",
1437 "</style>\n",
1438 "<table border=\"1\" class=\"dataframe\">\n",
1439 " <thead>\n",
1440 " <tr style=\"text-align: right;\">\n",
1441 " <th></th>\n",
1442 " <th>name</th>\n",
1443 " <th>title</th>\n",
1444 " </tr>\n",
1445 " </thead>\n",
1446 " <tbody>\n",
1447 " </tbody>\n",
1448 "</table>\n",
1449 "</div>"
1450 ],
1451 "text/plain": [
1452 "Empty DataFrame\n",
1453 "Columns: [name, title]\n",
1454 "Index: []"
1455 ]
1456 },
1457 "execution_count": 270,
1458 "metadata": {},
1459 "output_type": "execute_result"
1460 }
1461 ],
1462 "source": [
1463 "query = '''\n",
1464 "select name, title\n",
1465 "from (select ID, title, semester\n",
1466 " from takes natural join course) natural join student\n",
1467 "where semester = \"Spring\";\n",
1468 "'''\n",
1469 "result = pd.read_sql_query(query, conn)\n",
1470 "result"
1471 ]
1472 },
1473 {
1474 "cell_type": "markdown",
1475 "metadata": {},
1476 "source": [
1477 "7. Lista estudiantes que toman o han tomado clases en el edificio \"ZL7101\""
1478 ]
1479 },
1480 {
1481 "cell_type": "code",
1482 "execution_count": 265,
1483 "metadata": {},
1484 "outputs": [
1485 {
1486 "data": {
1487 "text/html": [
1488 "<div>\n",
1489 "<style scoped>\n",
1490 " .dataframe tbody tr th:only-of-type {\n",
1491 " vertical-align: middle;\n",
1492 " }\n",
1493 "\n",
1494 " .dataframe tbody tr th {\n",
1495 " vertical-align: top;\n",
1496 " }\n",
1497 "\n",
1498 " .dataframe thead th {\n",
1499 " text-align: right;\n",
1500 " }\n",
1501 "</style>\n",
1502 "<table border=\"1\" class=\"dataframe\">\n",
1503 " <thead>\n",
1504 " <tr style=\"text-align: right;\">\n",
1505 " <th></th>\n",
1506 " <th>ID</th>\n",
1507 " <th>name</th>\n",
1508 " <th>dept_name</th>\n",
1509 " <th>tot_cred</th>\n",
1510 " </tr>\n",
1511 " </thead>\n",
1512 " <tbody>\n",
1513 " <tr>\n",
1514 " <th>0</th>\n",
1515 " <td>06003</td>\n",
1516 " <td>Wanda Black</td>\n",
1517 " <td>Biology</td>\n",
1518 " <td>132</td>\n",
1519 " </tr>\n",
1520 " <tr>\n",
1521 " <th>1</th>\n",
1522 " <td>28865</td>\n",
1523 " <td>Zachary Knight</td>\n",
1524 " <td>Biology</td>\n",
1525 " <td>30</td>\n",
1526 " </tr>\n",
1527 " <tr>\n",
1528 " <th>2</th>\n",
1529 " <td>86970</td>\n",
1530 " <td>Robert Clayton</td>\n",
1531 " <td>Mathematics</td>\n",
1532 " <td>49</td>\n",
1533 " </tr>\n",
1534 " <tr>\n",
1535 " <th>3</th>\n",
1536 " <td>99705</td>\n",
1537 " <td>Jacob Pittman</td>\n",
1538 " <td>Biology</td>\n",
1539 " <td>26</td>\n",
1540 " </tr>\n",
1541 " </tbody>\n",
1542 "</table>\n",
1543 "</div>"
1544 ],
1545 "text/plain": [
1546 " ID name dept_name tot_cred\n",
1547 "0 06003 Wanda Black Biology 132\n",
1548 "1 28865 Zachary Knight Biology 30\n",
1549 "2 86970 Robert Clayton Mathematics 49\n",
1550 "3 99705 Jacob Pittman Biology 26"
1551 ]
1552 },
1553 "execution_count": 265,
1554 "metadata": {},
1555 "output_type": "execute_result"
1556 }
1557 ],
1558 "source": [
1559 "query = '''\n",
1560 "select distinct *\n",
1561 "from (select ID from (select course_id from section where section.building = \"ZL7101\") natural join takes) natural join student;\n",
1562 "'''\n",
1563 "result = pd.read_sql_query(query, conn)\n",
1564 "result"
1565 ]
1566 },
1567 {
1568 "cell_type": "markdown",
1569 "metadata": {},
1570 "source": [
1571 "8. Lista los cursos que tienen más estudiantes inscritos que el promedio de inscripciones de todos los cursos."
1572 ]
1573 },
1574 {
1575 "cell_type": "code",
1576 "execution_count": 266,
1577 "metadata": {},
1578 "outputs": [
1579 {
1580 "data": {
1581 "text/html": [
1582 "<div>\n",
1583 "<style scoped>\n",
1584 " .dataframe tbody tr th:only-of-type {\n",
1585 " vertical-align: middle;\n",
1586 " }\n",
1587 "\n",
1588 " .dataframe tbody tr th {\n",
1589 " vertical-align: top;\n",
1590 " }\n",
1591 "\n",
1592 " .dataframe thead th {\n",
1593 " text-align: right;\n",
1594 " }\n",
1595 "</style>\n",
1596 "<table border=\"1\" class=\"dataframe\">\n",
1597 " <thead>\n",
1598 " <tr style=\"text-align: right;\">\n",
1599 " <th></th>\n",
1600 " <th>course_id</th>\n",
1601 " <th>sec_id</th>\n",
1602 " </tr>\n",
1603 " </thead>\n",
1604 " <tbody>\n",
1605 " <tr>\n",
1606 " <th>0</th>\n",
1607 " <td>Uefz257</td>\n",
1608 " <td>84</td>\n",
1609 " </tr>\n",
1610 " </tbody>\n",
1611 "</table>\n",
1612 "</div>"
1613 ],
1614 "text/plain": [
1615 " course_id sec_id\n",
1616 "0 Uefz257 84"
1617 ]
1618 },
1619 "execution_count": 266,
1620 "metadata": {},
1621 "output_type": "execute_result"
1622 }
1623 ],
1624 "source": [
1625 "query = '''\n",
1626 "select course_id, sec_id\n",
1627 "from (select avg(c) as average, c as population, t.*\n",
1628 " from (select count(ID) as c, takes.*\n",
1629 " from takes\n",
1630 " group by course_id) as t)\n",
1631 "where population > average;\n",
1632 "'''\n",
1633 "result = pd.read_sql_query(query, conn)\n",
1634 "result"
1635 ]
1636 },
1637 {
1638 "cell_type": "markdown",
1639 "metadata": {},
1640 "source": [
1641 "9. Encuentra los instructores que enseñan más de un curso, incluyendo los títulos de los cursos que enseñan."
1642 ]
1643 },
1644 {
1645 "cell_type": "code",
1646 "execution_count": 267,
1647 "metadata": {},
1648 "outputs": [
1649 {
1650 "data": {
1651 "text/html": [
1652 "<div>\n",
1653 "<style scoped>\n",
1654 " .dataframe tbody tr th:only-of-type {\n",
1655 " vertical-align: middle;\n",
1656 " }\n",
1657 "\n",
1658 " .dataframe tbody tr th {\n",
1659 " vertical-align: top;\n",
1660 " }\n",
1661 "\n",
1662 " .dataframe thead th {\n",
1663 " text-align: right;\n",
1664 " }\n",
1665 "</style>\n",
1666 "<table border=\"1\" class=\"dataframe\">\n",
1667 " <thead>\n",
1668 " <tr style=\"text-align: right;\">\n",
1669 " <th></th>\n",
1670 " <th>ID</th>\n",
1671 " <th>title</th>\n",
1672 " </tr>\n",
1673 " </thead>\n",
1674 " <tbody>\n",
1675 " <tr>\n",
1676 " <th>0</th>\n",
1677 " <td>57372</td>\n",
1678 " <td>Answer experience.</td>\n",
1679 " </tr>\n",
1680 " <tr>\n",
1681 " <th>1</th>\n",
1682 " <td>92814</td>\n",
1683 " <td>Trip always.</td>\n",
1684 " </tr>\n",
1685 " </tbody>\n",
1686 "</table>\n",
1687 "</div>"
1688 ],
1689 "text/plain": [
1690 " ID title\n",
1691 "0 57372 Answer experience.\n",
1692 "1 92814 Trip always."
1693 ]
1694 },
1695 "execution_count": 267,
1696 "metadata": {},
1697 "output_type": "execute_result"
1698 }
1699 ],
1700 "source": [
1701 "query = '''\n",
1702 "select ID, title\n",
1703 "from (select count(course_id) as c, teaches.*\n",
1704 " from teaches\n",
1705 " group by ID) natural join course\n",
1706 "where c > 1\n",
1707 "'''\n",
1708 "result = pd.read_sql_query(query, conn)\n",
1709 "result"
1710 ]
1711 },
1712 {
1713 "cell_type": "markdown",
1714 "metadata": {},
1715 "source": [
1716 "10. Entra datos para que la pregunta 6 no salga vacía."
1717 ]
1718 },
1719 {
1720 "cell_type": "code",
1721 "execution_count": 292,
1722 "metadata": {},
1723 "outputs": [
1724 {
1725 "data": {
1726 "text/html": [
1727 "<div>\n",
1728 "<style scoped>\n",
1729 " .dataframe tbody tr th:only-of-type {\n",
1730 " vertical-align: middle;\n",
1731 " }\n",
1732 "\n",
1733 " .dataframe tbody tr th {\n",
1734 " vertical-align: top;\n",
1735 " }\n",
1736 "\n",
1737 " .dataframe thead th {\n",
1738 " text-align: right;\n",
1739 " }\n",
1740 "</style>\n",
1741 "<table border=\"1\" class=\"dataframe\">\n",
1742 " <thead>\n",
1743 " <tr style=\"text-align: right;\">\n",
1744 " <th></th>\n",
1745 " <th>ID</th>\n",
1746 " <th>title</th>\n",
1747 " <th>semester</th>\n",
1748 " <th>name</th>\n",
1749 " <th>dept_name</th>\n",
1750 " <th>tot_cred</th>\n",
1751 " </tr>\n",
1752 " </thead>\n",
1753 " <tbody>\n",
1754 " <tr>\n",
1755 " <th>0</th>\n",
1756 " <td>81095</td>\n",
1757 " <td>American history glass.</td>\n",
1758 " <td>Spring</td>\n",
1759 " <td>Crystal Woods</td>\n",
1760 " <td>Mathematics</td>\n",
1761 " <td>31</td>\n",
1762 " </tr>\n",
1763 " <tr>\n",
1764 " <th>1</th>\n",
1765 " <td>99705</td>\n",
1766 " <td>American history glass.</td>\n",
1767 " <td>Spring</td>\n",
1768 " <td>Jacob Pittman</td>\n",
1769 " <td>Biology</td>\n",
1770 " <td>26</td>\n",
1771 " </tr>\n",
1772 " </tbody>\n",
1773 "</table>\n",
1774 "</div>"
1775 ],
1776 "text/plain": [
1777 " ID title semester name dept_name \\\n",
1778 "0 81095 American history glass. Spring Crystal Woods Mathematics \n",
1779 "1 99705 American history glass. Spring Jacob Pittman Biology \n",
1780 "\n",
1781 " tot_cred \n",
1782 "0 31 \n",
1783 "1 26 "
1784 ]
1785 },
1786 "execution_count": 292,
1787 "metadata": {},
1788 "output_type": "execute_result"
1789 }
1790 ],
1791 "source": [
1792 "# newSectionData = [('rjtj402', '42', 'Spring', 2025, 'ZL7101', '475', 'vwW')]\n",
1793 "# conn.executemany('insert into section values (?, ?, ?, ?, ?, ?, ?)', newSectionData)\n",
1794 "\n",
1795 "# newTakesData = [('81095', 'rjtj402', '42', 'Spring', 2025, 'D'), ('99705', 'rjtj402', '42', 'Spring', 2025, 'C')]\n",
1796 "# conn.executemany('insert into takes values (?, ?, ?, ?, ?, ?)', newTakesData)\n",
1797 "\n",
1798 "query = '''\n",
1799 "select *\n",
1800 "from (select ID, title, semester\n",
1801 " from takes natural join course) natural join student\n",
1802 "where semester = \"Spring\";\n",
1803 "'''\n",
1804 "result = pd.read_sql_query(query, conn)\n",
1805 "result"
1806 ]
1807 },
1808 {
1809 "cell_type": "code",
1810 "execution_count": 40,
1811 "metadata": {
1812 "colab": {
1813 "base_uri": "https://localhost:8080/",
1814 "height": 89
1815 },
1816 "executionInfo": {
1817 "elapsed": 20,
1818 "status": "ok",
1819 "timestamp": 1742332468670,
1820 "user": {
1821 "displayName": "Carlos J Corrada Bravo",
1822 "userId": "05726728043350562289"
1823 },
1824 "user_tz": 240
1825 },
1826 "id": "ogZapTNzB2S1",
1827 "outputId": "92311a33-f514-4bda-e7d8-d34040e927a0"
1828 },
1829 "outputs": [
1830 {
1831 "data": {
1832 "application/vnd.google.colaboratory.intrinsic+json": {
1833 "summary": "{\n \"name\": \"result\",\n \"rows\": 1,\n \"fields\": [\n {\n \"column\": \"avg(salary)\",\n \"properties\": {\n \"dtype\": \"number\",\n \"std\": null,\n \"min\": 72304.28400000001,\n \"max\": 72304.28400000001,\n \"num_unique_values\": 1,\n \"samples\": [\n 72304.28400000001\n ],\n \"semantic_type\": \"\",\n \"description\": \"\"\n }\n }\n ]\n}",
1834 "type": "dataframe",
1835 "variable_name": "result"
1836 },
1837 "text/html": [
1838 "\n",
1839 " <div id=\"df-5a7cdadb-9f99-4a27-8906-1a91d5d9814f\" class=\"colab-df-container\">\n",
1840 " <div>\n",
1841 "<style scoped>\n",
1842 " .dataframe tbody tr th:only-of-type {\n",
1843 " vertical-align: middle;\n",
1844 " }\n",
1845 "\n",
1846 " .dataframe tbody tr th {\n",
1847 " vertical-align: top;\n",
1848 " }\n",
1849 "\n",
1850 " .dataframe thead th {\n",
1851 " text-align: right;\n",
1852 " }\n",
1853 "</style>\n",
1854 "<table border=\"1\" class=\"dataframe\">\n",
1855 " <thead>\n",
1856 " <tr style=\"text-align: right;\">\n",
1857 " <th></th>\n",
1858 " <th>avg(salary)</th>\n",
1859 " </tr>\n",
1860 " </thead>\n",
1861 " <tbody>\n",
1862 " <tr>\n",
1863 " <th>0</th>\n",
1864 " <td>72304.284</td>\n",
1865 " </tr>\n",
1866 " </tbody>\n",
1867 "</table>\n",
1868 "</div>\n",
1869 " <div class=\"colab-df-buttons\">\n",
1870 "\n",
1871 " <div class=\"colab-df-container\">\n",
1872 " <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5a7cdadb-9f99-4a27-8906-1a91d5d9814f')\"\n",
1873 " title=\"Convert this dataframe to an interactive table.\"\n",
1874 " style=\"display:none;\">\n",
1875 "\n",
1876 " <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
1877 " <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
1878 " </svg>\n",
1879 " </button>\n",
1880 "\n",
1881 " <style>\n",
1882 " .colab-df-container {\n",
1883 " display:flex;\n",
1884 " gap: 12px;\n",
1885 " }\n",
1886 "\n",
1887 " .colab-df-convert {\n",
1888 " background-color: #E8F0FE;\n",
1889 " border: none;\n",
1890 " border-radius: 50%;\n",
1891 " cursor: pointer;\n",
1892 " display: none;\n",
1893 " fill: #1967D2;\n",
1894 " height: 32px;\n",
1895 " padding: 0 0 0 0;\n",
1896 " width: 32px;\n",
1897 " }\n",
1898 "\n",
1899 " .colab-df-convert:hover {\n",
1900 " background-color: #E2EBFA;\n",
1901 " box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
1902 " fill: #174EA6;\n",
1903 " }\n",
1904 "\n",
1905 " .colab-df-buttons div {\n",
1906 " margin-bottom: 4px;\n",
1907 " }\n",
1908 "\n",
1909 " [theme=dark] .colab-df-convert {\n",
1910 " background-color: #3B4455;\n",
1911 " fill: #D2E3FC;\n",
1912 " }\n",
1913 "\n",
1914 " [theme=dark] .colab-df-convert:hover {\n",
1915 " background-color: #434B5C;\n",
1916 " box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
1917 " filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
1918 " fill: #FFFFFF;\n",
1919 " }\n",
1920 " </style>\n",
1921 "\n",
1922 " <script>\n",
1923 " const buttonEl =\n",
1924 " document.querySelector('#df-5a7cdadb-9f99-4a27-8906-1a91d5d9814f button.colab-df-convert');\n",
1925 " buttonEl.style.display =\n",
1926 " google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
1927 "\n",
1928 " async function convertToInteractive(key) {\n",
1929 " const element = document.querySelector('#df-5a7cdadb-9f99-4a27-8906-1a91d5d9814f');\n",
1930 " const dataTable =\n",
1931 " await google.colab.kernel.invokeFunction('convertToInteractive',\n",
1932 " [key], {});\n",
1933 " if (!dataTable) return;\n",
1934 "\n",
1935 " const docLinkHtml = 'Like what you see? Visit the ' +\n",
1936 " '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
1937 " + ' to learn more about interactive tables.';\n",
1938 " element.innerHTML = '';\n",
1939 " dataTable['output_type'] = 'display_data';\n",
1940 " await google.colab.output.renderOutput(dataTable, element);\n",
1941 " const docLink = document.createElement('div');\n",
1942 " docLink.innerHTML = docLinkHtml;\n",
1943 " element.appendChild(docLink);\n",
1944 " }\n",
1945 " </script>\n",
1946 " </div>\n",
1947 "\n",
1948 "\n",
1949 " <div id=\"id_7df07159-d39c-4f55-8db4-bd9e0af72d46\">\n",
1950 " <style>\n",
1951 " .colab-df-generate {\n",
1952 " background-color: #E8F0FE;\n",
1953 " border: none;\n",
1954 " border-radius: 50%;\n",
1955 " cursor: pointer;\n",
1956 " display: none;\n",
1957 " fill: #1967D2;\n",
1958 " height: 32px;\n",
1959 " padding: 0 0 0 0;\n",
1960 " width: 32px;\n",
1961 " }\n",
1962 "\n",
1963 " .colab-df-generate:hover {\n",
1964 " background-color: #E2EBFA;\n",
1965 " box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
1966 " fill: #174EA6;\n",
1967 " }\n",
1968 "\n",
1969 " [theme=dark] .colab-df-generate {\n",
1970 " background-color: #3B4455;\n",
1971 " fill: #D2E3FC;\n",
1972 " }\n",
1973 "\n",
1974 " [theme=dark] .colab-df-generate:hover {\n",
1975 " background-color: #434B5C;\n",
1976 " box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
1977 " filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
1978 " fill: #FFFFFF;\n",
1979 " }\n",
1980 " </style>\n",
1981 " <button class=\"colab-df-generate\" onclick=\"generateWithVariable('result')\"\n",
1982 " title=\"Generate code using this dataframe.\"\n",
1983 " style=\"display:none;\">\n",
1984 "\n",
1985 " <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
1986 " width=\"24px\">\n",
1987 " <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
1988 " </svg>\n",
1989 " </button>\n",
1990 " <script>\n",
1991 " (() => {\n",
1992 " const buttonEl =\n",
1993 " document.querySelector('#id_7df07159-d39c-4f55-8db4-bd9e0af72d46 button.colab-df-generate');\n",
1994 " buttonEl.style.display =\n",
1995 " google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
1996 "\n",
1997 " buttonEl.onclick = () => {\n",
1998 " google.colab.notebook.generateWithVariable('result');\n",
1999 " }\n",
2000 " })();\n",
2001 " </script>\n",
2002 " </div>\n",
2003 "\n",
2004 " </div>\n",
2005 " </div>\n"
2006 ],
2007 "text/plain": [
2008 " avg(salary)\n",
2009 "0 72304.284"
2010 ]
2011 },
2012 "execution_count": 40,
2013 "metadata": {},
2014 "output_type": "execute_result"
2015 }
2016 ],
2017 "source": [
2018 "query = \"SELECT avg(salary) from instructor;\"\n",
2019 "result = pd.read_sql_query(query, conn)\n",
2020 "result"
2021 ]
2022 },
2023 {
2024 "cell_type": "code",
2025 "execution_count": 41,
2026 "metadata": {
2027 "colab": {
2028 "base_uri": "https://localhost:8080/",
2029 "height": 206
2030 },
2031 "executionInfo": {
2032 "elapsed": 21,
2033 "status": "ok",
2034 "timestamp": 1742332548059,
2035 "user": {
2036 "displayName": "Carlos J Corrada Bravo",
2037 "userId": "05726728043350562289"
2038 },
2039 "user_tz": 240
2040 },
2041 "id": "VxJvpBlDHA4p",
2042 "outputId": "b200e5fa-71b1-40bf-be4c-df93ddc969f4"
2043 },
2044 "outputs": [
2045 {
2046 "data": {
2047 "application/vnd.google.colaboratory.intrinsic+json": {
2048 "summary": "{\n \"name\": \"result\",\n \"rows\": 5,\n \"fields\": [\n {\n \"column\": \"title\",\n \"properties\": {\n \"dtype\": \"string\",\n \"num_unique_values\": 5,\n \"samples\": [\n \"Answer experience.\",\n \"Trip always.\",\n \"May stand guess.\"\n ],\n \"semantic_type\": \"\",\n \"description\": \"\"\n }\n }\n ]\n}",
2049 "type": "dataframe",
2050 "variable_name": "result"
2051 },
2052 "text/html": [
2053 "\n",
2054 " <div id=\"df-30a99356-551c-4d8e-9d09-bced5cc3f232\" class=\"colab-df-container\">\n",
2055 " <div>\n",
2056 "<style scoped>\n",
2057 " .dataframe tbody tr th:only-of-type {\n",
2058 " vertical-align: middle;\n",
2059 " }\n",
2060 "\n",
2061 " .dataframe tbody tr th {\n",
2062 " vertical-align: top;\n",
2063 " }\n",
2064 "\n",
2065 " .dataframe thead th {\n",
2066 " text-align: right;\n",
2067 " }\n",
2068 "</style>\n",
2069 "<table border=\"1\" class=\"dataframe\">\n",
2070 " <thead>\n",
2071 " <tr style=\"text-align: right;\">\n",
2072 " <th></th>\n",
2073 " <th>title</th>\n",
2074 " </tr>\n",
2075 " </thead>\n",
2076 " <tbody>\n",
2077 " <tr>\n",
2078 " <th>0</th>\n",
2079 " <td>Oil wall.</td>\n",
2080 " </tr>\n",
2081 " <tr>\n",
2082 " <th>1</th>\n",
2083 " <td>Answer experience.</td>\n",
2084 " </tr>\n",
2085 " <tr>\n",
2086 " <th>2</th>\n",
2087 " <td>May stand guess.</td>\n",
2088 " </tr>\n",
2089 " <tr>\n",
2090 " <th>3</th>\n",
2091 " <td>Close certain national research.</td>\n",
2092 " </tr>\n",
2093 " <tr>\n",
2094 " <th>4</th>\n",
2095 " <td>Trip always.</td>\n",
2096 " </tr>\n",
2097 " </tbody>\n",
2098 "</table>\n",
2099 "</div>\n",
2100 " <div class=\"colab-df-buttons\">\n",
2101 "\n",
2102 " <div class=\"colab-df-container\">\n",
2103 " <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-30a99356-551c-4d8e-9d09-bced5cc3f232')\"\n",
2104 " title=\"Convert this dataframe to an interactive table.\"\n",
2105 " style=\"display:none;\">\n",
2106 "\n",
2107 " <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
2108 " <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
2109 " </svg>\n",
2110 " </button>\n",
2111 "\n",
2112 " <style>\n",
2113 " .colab-df-container {\n",
2114 " display:flex;\n",
2115 " gap: 12px;\n",
2116 " }\n",
2117 "\n",
2118 " .colab-df-convert {\n",
2119 " background-color: #E8F0FE;\n",
2120 " border: none;\n",
2121 " border-radius: 50%;\n",
2122 " cursor: pointer;\n",
2123 " display: none;\n",
2124 " fill: #1967D2;\n",
2125 " height: 32px;\n",
2126 " padding: 0 0 0 0;\n",
2127 " width: 32px;\n",
2128 " }\n",
2129 "\n",
2130 " .colab-df-convert:hover {\n",
2131 " background-color: #E2EBFA;\n",
2132 " box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
2133 " fill: #174EA6;\n",
2134 " }\n",
2135 "\n",
2136 " .colab-df-buttons div {\n",
2137 " margin-bottom: 4px;\n",
2138 " }\n",
2139 "\n",
2140 " [theme=dark] .colab-df-convert {\n",
2141 " background-color: #3B4455;\n",
2142 " fill: #D2E3FC;\n",
2143 " }\n",
2144 "\n",
2145 " [theme=dark] .colab-df-convert:hover {\n",
2146 " background-color: #434B5C;\n",
2147 " box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
2148 " filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
2149 " fill: #FFFFFF;\n",
2150 " }\n",
2151 " </style>\n",
2152 "\n",
2153 " <script>\n",
2154 " const buttonEl =\n",
2155 " document.querySelector('#df-30a99356-551c-4d8e-9d09-bced5cc3f232 button.colab-df-convert');\n",
2156 " buttonEl.style.display =\n",
2157 " google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
2158 "\n",
2159 " async function convertToInteractive(key) {\n",
2160 " const element = document.querySelector('#df-30a99356-551c-4d8e-9d09-bced5cc3f232');\n",
2161 " const dataTable =\n",
2162 " await google.colab.kernel.invokeFunction('convertToInteractive',\n",
2163 " [key], {});\n",
2164 " if (!dataTable) return;\n",
2165 "\n",
2166 " const docLinkHtml = 'Like what you see? Visit the ' +\n",
2167 " '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
2168 " + ' to learn more about interactive tables.';\n",
2169 " element.innerHTML = '';\n",
2170 " dataTable['output_type'] = 'display_data';\n",
2171 " await google.colab.output.renderOutput(dataTable, element);\n",
2172 " const docLink = document.createElement('div');\n",
2173 " docLink.innerHTML = docLinkHtml;\n",
2174 " element.appendChild(docLink);\n",
2175 " }\n",
2176 " </script>\n",
2177 " </div>\n",
2178 "\n",
2179 "\n",
2180 "<div id=\"df-573d43b3-a6f0-4882-a2cf-4bbbbc2065ce\">\n",
2181 " <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-573d43b3-a6f0-4882-a2cf-4bbbbc2065ce')\"\n",
2182 " title=\"Suggest charts\"\n",
2183 " style=\"display:none;\">\n",
2184 "\n",
2185 "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
2186 " width=\"24px\">\n",
2187 " <g>\n",
2188 " <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
2189 " </g>\n",
2190 "</svg>\n",
2191 " </button>\n",
2192 "\n",
2193 "<style>\n",
2194 " .colab-df-quickchart {\n",
2195 " --bg-color: #E8F0FE;\n",
2196 " --fill-color: #1967D2;\n",
2197 " --hover-bg-color: #E2EBFA;\n",
2198 " --hover-fill-color: #174EA6;\n",
2199 " --disabled-fill-color: #AAA;\n",
2200 " --disabled-bg-color: #DDD;\n",
2201 " }\n",
2202 "\n",
2203 " [theme=dark] .colab-df-quickchart {\n",
2204 " --bg-color: #3B4455;\n",
2205 " --fill-color: #D2E3FC;\n",
2206 " --hover-bg-color: #434B5C;\n",
2207 " --hover-fill-color: #FFFFFF;\n",
2208 " --disabled-bg-color: #3B4455;\n",
2209 " --disabled-fill-color: #666;\n",
2210 " }\n",
2211 "\n",
2212 " .colab-df-quickchart {\n",
2213 " background-color: var(--bg-color);\n",
2214 " border: none;\n",
2215 " border-radius: 50%;\n",
2216 " cursor: pointer;\n",
2217 " display: none;\n",
2218 " fill: var(--fill-color);\n",
2219 " height: 32px;\n",
2220 " padding: 0;\n",
2221 " width: 32px;\n",
2222 " }\n",
2223 "\n",
2224 " .colab-df-quickchart:hover {\n",
2225 " background-color: var(--hover-bg-color);\n",
2226 " box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
2227 " fill: var(--button-hover-fill-color);\n",
2228 " }\n",
2229 "\n",
2230 " .colab-df-quickchart-complete:disabled,\n",
2231 " .colab-df-quickchart-complete:disabled:hover {\n",
2232 " background-color: var(--disabled-bg-color);\n",
2233 " fill: var(--disabled-fill-color);\n",
2234 " box-shadow: none;\n",
2235 " }\n",
2236 "\n",
2237 " .colab-df-spinner {\n",
2238 " border: 2px solid var(--fill-color);\n",
2239 " border-color: transparent;\n",
2240 " border-bottom-color: var(--fill-color);\n",
2241 " animation:\n",
2242 " spin 1s steps(1) infinite;\n",
2243 " }\n",
2244 "\n",
2245 " @keyframes spin {\n",
2246 " 0% {\n",
2247 " border-color: transparent;\n",
2248 " border-bottom-color: var(--fill-color);\n",
2249 " border-left-color: var(--fill-color);\n",
2250 " }\n",
2251 " 20% {\n",
2252 " border-color: transparent;\n",
2253 " border-left-color: var(--fill-color);\n",
2254 " border-top-color: var(--fill-color);\n",
2255 " }\n",
2256 " 30% {\n",
2257 " border-color: transparent;\n",
2258 " border-left-color: var(--fill-color);\n",
2259 " border-top-color: var(--fill-color);\n",
2260 " border-right-color: var(--fill-color);\n",
2261 " }\n",
2262 " 40% {\n",
2263 " border-color: transparent;\n",
2264 " border-right-color: var(--fill-color);\n",
2265 " border-top-color: var(--fill-color);\n",
2266 " }\n",
2267 " 60% {\n",
2268 " border-color: transparent;\n",
2269 " border-right-color: var(--fill-color);\n",
2270 " }\n",
2271 " 80% {\n",
2272 " border-color: transparent;\n",
2273 " border-right-color: var(--fill-color);\n",
2274 " border-bottom-color: var(--fill-color);\n",
2275 " }\n",
2276 " 90% {\n",
2277 " border-color: transparent;\n",
2278 " border-bottom-color: var(--fill-color);\n",
2279 " }\n",
2280 " }\n",
2281 "</style>\n",
2282 "\n",
2283 " <script>\n",
2284 " async function quickchart(key) {\n",
2285 " const quickchartButtonEl =\n",
2286 " document.querySelector('#' + key + ' button');\n",
2287 " quickchartButtonEl.disabled = true; // To prevent multiple clicks.\n",
2288 " quickchartButtonEl.classList.add('colab-df-spinner');\n",
2289 " try {\n",
2290 " const charts = await google.colab.kernel.invokeFunction(\n",
2291 " 'suggestCharts', [key], {});\n",
2292 " } catch (error) {\n",
2293 " console.error('Error during call to suggestCharts:', error);\n",
2294 " }\n",
2295 " quickchartButtonEl.classList.remove('colab-df-spinner');\n",
2296 " quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
2297 " }\n",
2298 " (() => {\n",
2299 " let quickchartButtonEl =\n",
2300 " document.querySelector('#df-573d43b3-a6f0-4882-a2cf-4bbbbc2065ce button');\n",
2301 " quickchartButtonEl.style.display =\n",
2302 " google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
2303 " })();\n",
2304 " </script>\n",
2305 "</div>\n",
2306 "\n",
2307 " <div id=\"id_ccdccc03-be8b-428e-9c9f-e5291130f2a2\">\n",
2308 " <style>\n",
2309 " .colab-df-generate {\n",
2310 " background-color: #E8F0FE;\n",
2311 " border: none;\n",
2312 " border-radius: 50%;\n",
2313 " cursor: pointer;\n",
2314 " display: none;\n",
2315 " fill: #1967D2;\n",
2316 " height: 32px;\n",
2317 " padding: 0 0 0 0;\n",
2318 " width: 32px;\n",
2319 " }\n",
2320 "\n",
2321 " .colab-df-generate:hover {\n",
2322 " background-color: #E2EBFA;\n",
2323 " box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
2324 " fill: #174EA6;\n",
2325 " }\n",
2326 "\n",
2327 " [theme=dark] .colab-df-generate {\n",
2328 " background-color: #3B4455;\n",
2329 " fill: #D2E3FC;\n",
2330 " }\n",
2331 "\n",
2332 " [theme=dark] .colab-df-generate:hover {\n",
2333 " background-color: #434B5C;\n",
2334 " box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
2335 " filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
2336 " fill: #FFFFFF;\n",
2337 " }\n",
2338 " </style>\n",
2339 " <button class=\"colab-df-generate\" onclick=\"generateWithVariable('result')\"\n",
2340 " title=\"Generate code using this dataframe.\"\n",
2341 " style=\"display:none;\">\n",
2342 "\n",
2343 " <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
2344 " width=\"24px\">\n",
2345 " <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
2346 " </svg>\n",
2347 " </button>\n",
2348 " <script>\n",
2349 " (() => {\n",
2350 " const buttonEl =\n",
2351 " document.querySelector('#id_ccdccc03-be8b-428e-9c9f-e5291130f2a2 button.colab-df-generate');\n",
2352 " buttonEl.style.display =\n",
2353 " google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
2354 "\n",
2355 " buttonEl.onclick = () => {\n",
2356 " google.colab.notebook.generateWithVariable('result');\n",
2357 " }\n",
2358 " })();\n",
2359 " </script>\n",
2360 " </div>\n",
2361 "\n",
2362 " </div>\n",
2363 " </div>\n"
2364 ],
2365 "text/plain": [
2366 " title\n",
2367 "0 Oil wall.\n",
2368 "1 Answer experience.\n",
2369 "2 May stand guess.\n",
2370 "3 Close certain national research.\n",
2371 "4 Trip always."
2372 ]
2373 },
2374 "execution_count": 41,
2375 "metadata": {},
2376 "output_type": "execute_result"
2377 }
2378 ],
2379 "source": [
2380 "# prompt: Lista todos los cursos con menos de 3 créditos.\n",
2381 "\n",
2382 "query = \"\"\"\n",
2383 "SELECT title\n",
2384 "FROM course\n",
2385 "WHERE credits < 3;\n",
2386 "\"\"\"\n",
2387 "result = pd.read_sql_query(query, conn)\n",
2388 "result\n"
2389 ]
2390 }
2391 ],
2392 "metadata": {
2393 "colab": {
2394 "authorship_tag": "ABX9TyOGJ3BmVuEnbvOTwSJH5Bc7",
2395 "provenance": []
2396 },
2397 "kernelspec": {
2398 "display_name": "Python 3 (ipykernel)",
2399 "language": "python",
2400 "name": "python3"
2401 },
2402 "language_info": {
2403 "codemirror_mode": {
2404 "name": "ipython",
2405 "version": 3
2406 },
2407 "file_extension": ".py",
2408 "mimetype": "text/x-python",
2409 "name": "python",
2410 "nbconvert_exporter": "python",
2411 "pygments_lexer": "ipython3",
2412 "version": "3.12.9"
2413 }
2414 },
2415 "nbformat": 4,
2416 "nbformat_minor": 4
2417}