{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "fsMwG9oTuv8p" }, "source": [ "**Hagan una copia de este notebook y comincen a ejecutar las ventanas.**" ] }, { "cell_type": "code", "execution_count": 283, "metadata": { "executionInfo": { "elapsed": 4, "status": "ok", "timestamp": 1742331515737, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "leyJgQpLyb9y" }, "outputs": [], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect(':memory:')\n", "\n", "create_statements = [\n", "'''\n", "create table classroom\n", "\t(building\t\tvarchar(15),\n", "\t room_number\t\tvarchar(7),\n", "\t capacity\t\tnumeric(4,0),\n", "\t primary key (building, room_number)\n", "\t);\n", "\n", "''',\n", "'''\n", "create table department\n", "\t(dept_name\t\tvarchar(20),\n", "\t building\t\tvarchar(15),\n", "\t budget\t\t numeric(12,2) check (budget > 0),\n", "\t primary key (dept_name)\n", "\t);\n", "\n", "''',\n", "'''\n", "create table course\n", "\t(course_id\t\tvarchar(8),\n", "\t title\t\t\tvarchar(50),\n", "\t dept_name\t\tvarchar(20),\n", "\t credits\t\tnumeric(2,0) check (credits > 0),\n", "\t primary key (course_id),\n", "\t foreign key (dept_name) references department\n", "\t\ton delete set null\n", "\t);\n", "\n", "''',\n", "'''\n", "create table instructor\n", "\t(ID\t\t\tvarchar(5),\n", "\t name\t\t\tvarchar(20) not null,\n", "\t dept_name\t\tvarchar(20),\n", "\t salary\t\t\tnumeric(8,2) check (salary > 29000),\n", "\t primary key (ID),\n", "\t foreign key (dept_name) references department\n", "\t\ton delete set null\n", "\t);\n", "''',\n", "'''\n", "create table section\n", "\t(course_id\t\tvarchar(8),\n", " sec_id\t\t\tvarchar(8),\n", "\t semester\t\tvarchar(6)\n", "\t\tcheck (semester in ('Fall', 'Winter', 'Spring', 'Summer')),\n", "\t year\t\t\tnumeric(4,0) check (year > 1701 and year < 2100),\n", "\t building\t\tvarchar(15),\n", "\t room_number\t\tvarchar(7),\n", "\t time_slot_id\t\tvarchar(4),\n", "\t primary key (course_id, sec_id, semester, year),\n", "\t foreign key (course_id) references course\n", "\t\ton delete cascade,\n", "\t foreign key (building, room_number) references classroom\n", "\t\ton delete set null\n", "\t);\n", "\n", "''',\n", "'''\n", "create table teaches\n", "\t(ID\t\t\tvarchar(5),\n", "\t course_id\t\tvarchar(8),\n", "\t sec_id\t\t\tvarchar(8),\n", "\t semester\t\tvarchar(6),\n", "\t year\t\t\tnumeric(4,0),\n", "\t primary key (ID, course_id, sec_id, semester, year),\n", "\t foreign key (course_id,sec_id, semester, year) references section\n", "\t\ton delete cascade,\n", "\t foreign key (ID) references instructor\n", "\t\ton delete cascade\n", "\t);\n", "\n", "''',\n", "'''\n", "create table student\n", "\t(ID\t\t\tvarchar(5),\n", "\t name\t\t\tvarchar(20) not null,\n", "\t dept_name\t\tvarchar(20),\n", "\t tot_cred\t\tnumeric(3,0) check (tot_cred >= 0),\n", "\t primary key (ID),\n", "\t foreign key (dept_name) references department\n", "\t\ton delete set null\n", "\t);\n", "\n", "''',\n", "'''\n", "create table takes\n", "\t(ID\t\t\tvarchar(5),\n", "\t course_id\t\tvarchar(8),\n", "\t sec_id\t\t\tvarchar(8),\n", "\t semester\t\tvarchar(6),\n", "\t year\t\t\tnumeric(4,0),\n", "\t grade\t\t varchar(2),\n", "\t primary key (ID, course_id, sec_id, semester, year),\n", "\t foreign key (course_id,sec_id, semester, year) references section\n", "\t\ton delete cascade,\n", "\t foreign key (ID) references student\n", "\t\ton delete cascade\n", "\t);\n", "\n", "''',\n", "'''\n", "create table advisor\n", "\t(s_ID\t\t\tvarchar(5),\n", "\t i_ID\t\t\tvarchar(5),\n", "\t primary key (s_ID),\n", "\t foreign key (i_ID) references instructor (ID)\n", "\t\ton delete set null,\n", "\t foreign key (s_ID) references student (ID)\n", "\t\ton delete cascade\n", "\t);\n", "\n", "''',\n", "'''\n", "create table time_slot\n", "\t(time_slot_id\t\tvarchar(4),\n", "\t day\t\t\tvarchar(1),\n", "\t start_hr\t\tnumeric(2) check (start_hr >= 0 and start_hr < 24),\n", "\t start_min\t\tnumeric(2) check (start_min >= 0 and start_min < 60),\n", "\t end_hr\t\t\tnumeric(2) check (end_hr >= 0 and end_hr < 24),\n", "\t end_min\t\tnumeric(2) check (end_min >= 0 and end_min < 60),\n", "\t primary key (time_slot_id, day, start_hr, start_min)\n", "\t);\n", "\n", "''',\n", "'''\n", "create table prereq\n", "\t(course_id\t\tvarchar(8),\n", "\t prereq_id\t\tvarchar(8),\n", "\t primary key (course_id, prereq_id),\n", "\t foreign key (course_id) references course\n", "\t\ton delete cascade,\n", "\t foreign key (prereq_id) references course\n", "\t);\n", "'''\n", "]\n", "\n", "for create_statement in create_statements:\n", " conn.execute(create_statement)\n" ] }, { "cell_type": "code", "execution_count": 284, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 4, "status": "ok", "timestamp": 1742331515743, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "hTHhb7JtGiHn", "outputId": "1e12c597-e6f1-43c0-eb34-a1478ef5ff44" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " type name tbl_name rootpage \\\n", "0 table classroom classroom 2 \n", "1 table department department 4 \n", "2 table course course 6 \n", "3 table instructor instructor 8 \n", "4 table section section 10 \n", "5 table teaches teaches 12 \n", "6 table student student 14 \n", "7 table takes takes 16 \n", "8 table advisor advisor 18 \n", "9 table time_slot time_slot 20 \n", "10 table prereq prereq 22 \n", "\n", " sql \n", "0 CREATE TABLE classroom\\n\\t(building\\t\\tvarchar... \n", "1 CREATE TABLE department\\n\\t(dept_name\\t\\tvarch... \n", "2 CREATE TABLE course\\n\\t(course_id\\t\\tvarchar(8... \n", "3 CREATE TABLE instructor\\n\\t(ID\\t\\t\\tvarchar(5)... \n", "4 CREATE TABLE section\\n\\t(course_id\\t\\tvarchar(... \n", "5 CREATE TABLE teaches\\n\\t(ID\\t\\t\\tvarchar(5),\\n... \n", "6 CREATE TABLE student\\n\\t(ID\\t\\t\\tvarchar(5),\\n... \n", "7 CREATE TABLE takes\\n\\t(ID\\t\\t\\tvarchar(5),\\n\\t... \n", "8 CREATE TABLE advisor\\n\\t(s_ID\\t\\t\\tvarchar(5),... \n", "9 CREATE TABLE time_slot\\n\\t(time_slot_id\\t\\tvar... \n", "10 CREATE TABLE prereq\\n\\t(course_id\\t\\tvarchar(8... \n" ] } ], "source": [ "query = \"SELECT * FROM sqlite_master WHERE type='table';\"\n", "# query = \"SELECT * FROM sqlite_master WHERE 1;\"\n", "result = pd.read_sql_query(query, conn)\n", "print(result)" ] }, { "cell_type": "code", "execution_count": 285, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 9, "status": "ok", "timestamp": 1742331515753, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "bfOLtuPU_r8Q", "outputId": "859a37fc-a56b-4c51-e30a-357073b7e404" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Empty DataFrame\n", "Columns: [building, room_number, capacity]\n", "Index: []\n" ] } ], "source": [ "query = \"SELECT * FROM classroom WHERE 1;\"\n", "result = pd.read_sql_query(query, conn)\n", "print(result)" ] }, { "cell_type": "code", "execution_count": 286, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 4, "status": "ok", "timestamp": 1742331515758, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "2N4KEUWJHXG6", "outputId": "6713d359-4c59-4a5e-80ff-8b54b9f9866a" }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 286, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "classroom_data = [('Science Bldg', '101', 60),\n", "('Liberal Arts', '201', 30),\n", "('Engineering', '301', 40)]\n", "\n", "conn.executemany('INSERT INTO classroom VALUES (?, ?, ?);', classroom_data)\n" ] }, { "cell_type": "code", "execution_count": 287, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 143 }, "executionInfo": { "elapsed": 111, "status": "ok", "timestamp": 1742331516025, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "_xNadgL9LO2L", "outputId": "6910f27d-b3e9-4c63-d9b4-31f2dd16be0d" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingroom_numbercapacity
0Science Bldg10160
1Liberal Arts20130
2Engineering30140
\n", "
" ], "text/plain": [ " building room_number capacity\n", "0 Science Bldg 101 60\n", "1 Liberal Arts 201 30\n", "2 Engineering 301 40" ] }, "execution_count": 287, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"SELECT * from classroom;\"\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "code", "execution_count": 288, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 3, "status": "ok", "timestamp": 1742331516029, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "ccJ45ol6LeWb", "outputId": "001f1430-6bb0-4ad7-e900-18644f88580d" }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 288, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"delete from classroom;\"\n", "conn.execute(query)\n" ] }, { "cell_type": "code", "execution_count": 289, "metadata": { "executionInfo": { "elapsed": 12, "status": "ok", "timestamp": 1742331516042, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "CgessFBPjJkH" }, "outputs": [], "source": [ "ClassroomData = [('ZL7101', '011', 66), ('Yb5300', '597', 24), ('lT9721', '108', 10), ('De7318', '918', 21), ('eP9048', '475', 84)]\n", "DepartmentData = [('Physics', 'Qb3378', 2711.38), ('Computer Science', 'TE1052', 34961.28), ('Mathematics', 'Fk2019', 5822.68), ('Biology', 'wX8320', 18377.14)]\n", "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", "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", "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", "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", "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", "AdvisorData = [('89981', '65364'), ('68144', '92814'), ('99705', '81643'), ('81095', '57372')]\n", "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", "PrereqData = [('rjtj402', 'lLnt386'), ('rjtj402', 'fYSu038'), ('XlAB441', 'lLnt386'), ('Uefz257', 'viid787'), ('mgzm416', 'XlAB441'), ('lLnt386', 'Uefz257')]\n", "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" ] }, { "cell_type": "code", "execution_count": 290, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 128, "status": "ok", "timestamp": 1742331516171, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "1Jw9zoPbjTNu", "outputId": "d3fb519b-0aac-445f-f897-e22740024c8e" }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 290, "metadata": {}, "output_type": "execute_result" } ], "source": [ "conn.executemany('INSERT INTO classroom VALUES (?, ?, ?);', ClassroomData)\n", "conn.executemany('INSERT INTO department VALUES (?, ?, ?);', DepartmentData)\n", "conn.executemany('INSERT INTO course VALUES (?, ?, ?, ?);', CourseData)\n", "conn.executemany('INSERT INTO instructor VALUES (?, ?, ?, ?);', InstructorData)\n", "conn.executemany('INSERT INTO section VALUES (?, ?, ?, ?, ?, ?, ?);', SectionData)\n", "conn.executemany('INSERT INTO student VALUES (?, ?, ?, ?);', StudentData)\n", "conn.executemany('INSERT INTO takes VALUES (?, ?, ?,?, ?, ?);', TakesData)\n", "conn.executemany('INSERT INTO advisor VALUES (?, ?);', AdvisorData)\n", "conn.executemany('INSERT INTO time_slot VALUES (?, ?, ?, ?, ?, ?);', TimeSlotData)\n", "conn.executemany('INSERT INTO prereq VALUES (?, ?);', PrereqData)\n", "conn.executemany('INSERT INTO teaches VALUES (?, ?, ?, ?, ?);', TeachesData)\n" ] }, { "cell_type": "code", "execution_count": 255, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 3, "status": "ok", "timestamp": 1742331516175, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "FE92bKHZE3Fe", "outputId": "52560fce-69f4-403f-d8cc-8dc3a91d5a1b" }, "outputs": [ { "data": { "text/plain": [ "[('viid787', '95', 'Fall', 2016, 'ZL7101', '011', 'nkH'),\n", " ('fYSu038', '97', 'Winter', 2022, 'De7318', '918', 'ZQE'),\n", " ('XlAB441', '07', 'Fall', 2002, 'eP9048', '918', 'QZg'),\n", " ('XlAB441', '05', 'Fall', 2017, 'Yb5300', '597', 'YEe'),\n", " ('rjtj402', '29', 'Spring', 2024, 'ZL7101', '475', 'vwW'),\n", " ('XlAB441', '44', 'Spring', 2014, 'ZL7101', '918', 'RRL'),\n", " ('Uefz257', '59', 'Fall', 2005, 'lT9721', '597', 'sKm'),\n", " ('Uefz257', '84', 'Winter', 2024, 'De7318', '011', 'eIS'),\n", " ('OfWY200', '93', 'Summer', 2024, 'lT9721', '918', 'vGv'),\n", " ('fYSu038', '57', 'Summer', 2015, 'ZL7101', '918', 'rfW')]" ] }, "execution_count": 255, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SectionData" ] }, { "cell_type": "code", "execution_count": 256, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 363 }, "executionInfo": { "elapsed": 12, "status": "ok", "timestamp": 1742331516188, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "7voguoHnkJZr", "outputId": "4ae4a1af-7941-4f21-b2b0-ae4110826808" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
course_idsec_idsemesteryearbuildingroom_numbertime_slot_id
0viid78795Fall2016ZL7101011nkH
1fYSu03897Winter2022De7318918ZQE
2XlAB44107Fall2002eP9048918QZg
3XlAB44105Fall2017Yb5300597YEe
4rjtj40229Spring2024ZL7101475vwW
5XlAB44144Spring2014ZL7101918RRL
6Uefz25759Fall2005lT9721597sKm
7Uefz25784Winter2024De7318011eIS
8OfWY20093Summer2024lT9721918vGv
9fYSu03857Summer2015ZL7101918rfW
\n", "
" ], "text/plain": [ " course_id sec_id semester year building room_number time_slot_id\n", "0 viid787 95 Fall 2016 ZL7101 011 nkH\n", "1 fYSu038 97 Winter 2022 De7318 918 ZQE\n", "2 XlAB441 07 Fall 2002 eP9048 918 QZg\n", "3 XlAB441 05 Fall 2017 Yb5300 597 YEe\n", "4 rjtj402 29 Spring 2024 ZL7101 475 vwW\n", "5 XlAB441 44 Spring 2014 ZL7101 918 RRL\n", "6 Uefz257 59 Fall 2005 lT9721 597 sKm\n", "7 Uefz257 84 Winter 2024 De7318 011 eIS\n", "8 OfWY200 93 Summer 2024 lT9721 918 vGv\n", "9 fYSu038 57 Summer 2015 ZL7101 918 rfW" ] }, "execution_count": 256, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"SELECT * from section;\"\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "code", "execution_count": 257, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 363 }, "executionInfo": { "elapsed": 42, "status": "ok", "timestamp": 1742331639399, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "-lqhRNy7EQUN", "outputId": "a8834b4d-ba6b-4096-c309-40be61ffecfa" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IDcourse_idsec_idsemesteryeargrade
081095Uefz25784Winter2024D
199705XlAB44107Fall2002C
206003viid78795Fall2016D
328991Uefz25759Fall2005D
486970Uefz25784Winter2024D
568144Uefz25784Winter2024C
628865fYSu03857Summer2015I
786970fYSu03857Summer2015B
886970XlAB44107Fall2002F
960991Uefz25784Winter2024I
\n", "
" ], "text/plain": [ " ID course_id sec_id semester year grade\n", "0 81095 Uefz257 84 Winter 2024 D\n", "1 99705 XlAB441 07 Fall 2002 C\n", "2 06003 viid787 95 Fall 2016 D\n", "3 28991 Uefz257 59 Fall 2005 D\n", "4 86970 Uefz257 84 Winter 2024 D\n", "5 68144 Uefz257 84 Winter 2024 C\n", "6 28865 fYSu038 57 Summer 2015 I\n", "7 86970 fYSu038 57 Summer 2015 B\n", "8 86970 XlAB441 07 Fall 2002 F\n", "9 60991 Uefz257 84 Winter 2024 I" ] }, "execution_count": 257, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"SELECT * from takes;\"\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "code", "execution_count": 258, "metadata": { "executionInfo": { "elapsed": 3, "status": "ok", "timestamp": 1742331516202, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "N31sz6MBlSRU" }, "outputs": [], "source": [ "# conn.close()\n" ] }, { "cell_type": "markdown", "metadata": { "id": "GIft6pG9tw26" }, "source": [ "#### Ejercicios:\n", "\n", "1. Encuentra los nombres de todos los instructores.\n", "2. Lista todos los cursos con menos de 3 créditos.\n", "3. Muestra los nombres de los estudiantes junto con los nombres de los departamentos a los que pertenecen.\n", "4. Calcula el salario promedio de los instructores en el departamento de 'Computer Science'.\n", "5. Encuentra el número de estudiantes inscritos en cada departamento.\n", "6. Muestra los nombres de los estudiantes y los títulos de los cursos que están tomando este semestre.\n", "7. Lista estudiantes que toman o han tomado clases en el edificio \"ZL7101\"\n", "8. Lista los cursos que tienen más estudiantes inscritos que el promedio de inscripciones de todos los cursos.\n", "9. Encuentra los instructores que enseñan más de un curso, incluyendo los títulos de los cursos que enseñan.\n", "10. Entra datos para que la pregunta 6 no salga vacía.\n", "\n", "#### Instrucciones:\n", "- Utiliza el esquema de base de datos proporcionado para realizar cada uno de los ejercicios.\n", "- Verifica tus resultados ejecutando las consultas.\n", "- Intenta resolver los ejercicios en orden, ya que están diseñados para aumentar gradualmente en dificultad." ] }, { "cell_type": "markdown", "metadata": { "id": "aM7NyMASBxdN" }, "source": [ "1. Encuentra los nombres de todos los instructores.\n" ] }, { "cell_type": "code", "execution_count": 259, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
name
0Tammy Henderson
1James Cunningham
2Russell Sanders
3Aaron Castillo
4Brooke Vargas
\n", "
" ], "text/plain": [ " name\n", "0 Tammy Henderson\n", "1 James Cunningham\n", "2 Russell Sanders\n", "3 Aaron Castillo\n", "4 Brooke Vargas" ] }, "execution_count": 259, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"SELECT name from instructor;\"\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "2. Lista todos los cursos con menos de 3 créditos." ] }, { "cell_type": "code", "execution_count": 260, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
course_idtitledept_namecredits
0Pmwg764Oil wall.Computer Science1
1Uefz257Answer experience.Computer Science1
2mgzm416May stand guess.Computer Science1
3viid787Close certain national research.Physics1
4OfWY200Trip always.Biology1
\n", "
" ], "text/plain": [ " course_id title dept_name credits\n", "0 Pmwg764 Oil wall. Computer Science 1\n", "1 Uefz257 Answer experience. Computer Science 1\n", "2 mgzm416 May stand guess. Computer Science 1\n", "3 viid787 Close certain national research. Physics 1\n", "4 OfWY200 Trip always. Biology 1" ] }, "execution_count": 260, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT * from course\n", "where course.credits <3 ;\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "3. Muestra los nombres de los estudiantes junto con los nombres de los departamentos a los que pertenecen." ] }, { "cell_type": "code", "execution_count": 261, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namedept_name
0Dawn TaylorBiology
1Wanda BlackBiology
2Susan RobinsonBiology
3Crystal WoodsMathematics
4Travis SanchezComputer Science
5Zachary KnightBiology
6Jacob PittmanBiology
7Marcus GonzalezBiology
8Robert ClaytonMathematics
9Rebecca LinMathematics
\n", "
" ], "text/plain": [ " name dept_name\n", "0 Dawn Taylor Biology\n", "1 Wanda Black Biology\n", "2 Susan Robinson Biology\n", "3 Crystal Woods Mathematics\n", "4 Travis Sanchez Computer Science\n", "5 Zachary Knight Biology\n", "6 Jacob Pittman Biology\n", "7 Marcus Gonzalez Biology\n", "8 Robert Clayton Mathematics\n", "9 Rebecca Lin Mathematics" ] }, "execution_count": 261, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT name, dept_name from student;\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "4. Calcula el salario promedio de los instructores en el departamento de 'Computer Science'." ] }, { "cell_type": "code", "execution_count": 262, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avg(salary)
061737.16
\n", "
" ], "text/plain": [ " avg(salary)\n", "0 61737.16" ] }, "execution_count": 262, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT avg(salary) from instructor\n", "where instructor.dept_name = \"Computer Science\"\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "5. Encuentra el número de estudiantes inscritos en cada departamento." ] }, { "cell_type": "code", "execution_count": 263, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dept_namecount
0Biology6
1Computer Science1
2Mathematics3
\n", "
" ], "text/plain": [ " dept_name count\n", "0 Biology 6\n", "1 Computer Science 1\n", "2 Mathematics 3" ] }, "execution_count": 263, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "SELECT distinct dept_name, count(dept_name) as count from student\n", "group by student.dept_name;\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "6. Muestra los nombres de los estudiantes y los títulos de los cursos que están tomando este semestre." ] }, { "cell_type": "code", "execution_count": 270, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nametitle
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [name, title]\n", "Index: []" ] }, "execution_count": 270, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "select name, title\n", "from (select ID, title, semester\n", " from takes natural join course) natural join student\n", "where semester = \"Spring\";\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "7. Lista estudiantes que toman o han tomado clases en el edificio \"ZL7101\"" ] }, { "cell_type": "code", "execution_count": 265, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IDnamedept_nametot_cred
006003Wanda BlackBiology132
128865Zachary KnightBiology30
286970Robert ClaytonMathematics49
399705Jacob PittmanBiology26
\n", "
" ], "text/plain": [ " ID name dept_name tot_cred\n", "0 06003 Wanda Black Biology 132\n", "1 28865 Zachary Knight Biology 30\n", "2 86970 Robert Clayton Mathematics 49\n", "3 99705 Jacob Pittman Biology 26" ] }, "execution_count": 265, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "select distinct *\n", "from (select ID from (select course_id from section where section.building = \"ZL7101\") natural join takes) natural join student;\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "8. Lista los cursos que tienen más estudiantes inscritos que el promedio de inscripciones de todos los cursos." ] }, { "cell_type": "code", "execution_count": 266, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
course_idsec_id
0Uefz25784
\n", "
" ], "text/plain": [ " course_id sec_id\n", "0 Uefz257 84" ] }, "execution_count": 266, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "select course_id, sec_id\n", "from (select avg(c) as average, c as population, t.*\n", " from (select count(ID) as c, takes.*\n", " from takes\n", " group by course_id) as t)\n", "where population > average;\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "9. Encuentra los instructores que enseñan más de un curso, incluyendo los títulos de los cursos que enseñan." ] }, { "cell_type": "code", "execution_count": 267, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IDtitle
057372Answer experience.
192814Trip always.
\n", "
" ], "text/plain": [ " ID title\n", "0 57372 Answer experience.\n", "1 92814 Trip always." ] }, "execution_count": 267, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = '''\n", "select ID, title\n", "from (select count(course_id) as c, teaches.*\n", " from teaches\n", " group by ID) natural join course\n", "where c > 1\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "10. Entra datos para que la pregunta 6 no salga vacía." ] }, { "cell_type": "code", "execution_count": 292, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IDtitlesemesternamedept_nametot_cred
081095American history glass.SpringCrystal WoodsMathematics31
199705American history glass.SpringJacob PittmanBiology26
\n", "
" ], "text/plain": [ " ID title semester name dept_name \\\n", "0 81095 American history glass. Spring Crystal Woods Mathematics \n", "1 99705 American history glass. Spring Jacob Pittman Biology \n", "\n", " tot_cred \n", "0 31 \n", "1 26 " ] }, "execution_count": 292, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# newSectionData = [('rjtj402', '42', 'Spring', 2025, 'ZL7101', '475', 'vwW')]\n", "# conn.executemany('insert into section values (?, ?, ?, ?, ?, ?, ?)', newSectionData)\n", "\n", "# newTakesData = [('81095', 'rjtj402', '42', 'Spring', 2025, 'D'), ('99705', 'rjtj402', '42', 'Spring', 2025, 'C')]\n", "# conn.executemany('insert into takes values (?, ?, ?, ?, ?, ?)', newTakesData)\n", "\n", "query = '''\n", "select *\n", "from (select ID, title, semester\n", " from takes natural join course) natural join student\n", "where semester = \"Spring\";\n", "'''\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 89 }, "executionInfo": { "elapsed": 20, "status": "ok", "timestamp": 1742332468670, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "ogZapTNzB2S1", "outputId": "92311a33-f514-4bda-e7d8-d34040e927a0" }, "outputs": [ { "data": { "application/vnd.google.colaboratory.intrinsic+json": { "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}", "type": "dataframe", "variable_name": "result" }, "text/html": [ "\n", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
avg(salary)
072304.284
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", " \n", " \n", " \n", "
\n", "\n", "
\n", "
\n" ], "text/plain": [ " avg(salary)\n", "0 72304.284" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = \"SELECT avg(salary) from instructor;\"\n", "result = pd.read_sql_query(query, conn)\n", "result" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 206 }, "executionInfo": { "elapsed": 21, "status": "ok", "timestamp": 1742332548059, "user": { "displayName": "Carlos J Corrada Bravo", "userId": "05726728043350562289" }, "user_tz": 240 }, "id": "VxJvpBlDHA4p", "outputId": "b200e5fa-71b1-40bf-be4c-df93ddc969f4" }, "outputs": [ { "data": { "application/vnd.google.colaboratory.intrinsic+json": { "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}", "type": "dataframe", "variable_name": "result" }, "text/html": [ "\n", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
title
0Oil wall.
1Answer experience.
2May stand guess.
3Close certain national research.
4Trip always.
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", " \n", "\n", "\n", "\n", " \n", "
\n", "\n", "
\n", " \n", " \n", " \n", "
\n", "\n", "
\n", "
\n" ], "text/plain": [ " title\n", "0 Oil wall.\n", "1 Answer experience.\n", "2 May stand guess.\n", "3 Close certain national research.\n", "4 Trip always." ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# prompt: Lista todos los cursos con menos de 3 créditos.\n", "\n", "query = \"\"\"\n", "SELECT title\n", "FROM course\n", "WHERE credits < 3;\n", "\"\"\"\n", "result = pd.read_sql_query(query, conn)\n", "result\n" ] } ], "metadata": { "colab": { "authorship_tag": "ABX9TyOGJ3BmVuEnbvOTwSJH5Bc7", "provenance": [] }, "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.9" } }, "nbformat": 4, "nbformat_minor": 4 }