title
MySQL Full Course for free 🐬 (2023)

description
#MySQL #SQL #tutorial MySQL SQL tutorial for beginners 🐬 ⭐ TIME STAMPS ⭐ #1 00:00:00 MySQL intro + installation 00:02:22 Windows installation 00:06:05 MAC OS installation #2 00:10:29 DATABASES #3 00:14:29 TABLES #4 00:22:38 INSERT ROWS #5 00:28:32 SELECT #6 00:33:32 UPDATE & DELETE #7 00:37:03 AUTOCOMMIT, COMMIT, ROLLBACK #8 00:39:41 CURRENT_DATE() & CURRENT_TIME() #9 00:42:26 UNIQUE #10 00:47:09 NOT NULL #11 00:50:01 CHECK #12 00:53:58 DEFAULT #13 01:02:16 PRIMARY KEYS #14 01:07:41 AUTO_INCREMENT #15 01:11:36 FOREIGN KEYS #16 01:19:52 JOINS #17 01:24:55 FUNCTIONS #18 01:28:40 AND, OR, NOT #19 01:34:37 WILD CARDS #20 01:38:55 ORDER BY #21 01:41:31 LIMIT #22 01:44:24 UNIONS #23 01:48:37 SELF JOINS #24 01:58:39 VIEWS #25 02:04:41 INDEXES #26 02:11:06 SUBQUERIES #27 02:17:52 GROUP BY #28 02:23:00 ROLLUP #29 02:26:50 ON DELETE #30 02:34:20 STORED PROCEDURES #31 02:42:22 TRIGGERS Copyright Disclaimer: This video is the intellectual property of Bro Code. All rights reserved. No part of this video may be reproduced, distributed, or transmitted in any form or by any means, including but not limited to recording, uploading, or other electronic or mechanical methods, without my written permission, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law.

detail
{'title': 'MySQL Full Course for free 🐬 (2023)', 'heatmap': [{'end': 870.598, 'start': 644.836, 'weight': 0.774}, {'end': 1297.891, 'start': 971.247, 'weight': 1}, {'end': 2378.496, 'start': 2159.583, 'weight': 0.877}, {'end': 3574.523, 'start': 3448.016, 'weight': 0.744}, {'end': 4322.507, 'start': 4208.055, 'weight': 0.89}, {'end': 4865.291, 'start': 4750.162, 'weight': 0.702}, {'end': 6265.267, 'start': 6151.474, 'weight': 0.777}, {'end': 7239.314, 'start': 7125.498, 'weight': 0.767}, {'end': 7672.937, 'start': 7559.882, 'weight': 0.726}], 'summary': 'This full mysql course covers sql basics, mysql setup, table creation, data manipulation, constraints, functions, logical operators, database functions, operations, triggers, and stored procedures, providing practical examples and insights for managing and organizing data efficiently in a mysql environment.', 'chapters': [{'end': 91.785, 'segs': [{'end': 33.288, 'src': 'embed', 'start': 0.129, 'weight': 0, 'content': [{'end': 1.352, 'text': "hey, what's going on everybody?", 'start': 0.129, 'duration': 1.223}, {'end': 1.973, 'text': "it's your bro.", 'start': 1.352, 'duration': 0.621}, {'end': 8.026, 'text': "hope you're doing well, and in this video i'm going to show you how we can start writing sql using the mysql workbench.", 'start': 1.973, 'duration': 6.053}, {'end': 10.492, 'text': 'so sit back, relax and enjoy the show.', 'start': 8.026, 'duration': 2.466}, {'end': 14.033, 'text': "Alright, let's get started everybody.", 'start': 12.432, 'duration': 1.601}, {'end': 18.337, 'text': "SQL, it's an acronym meaning Structured Query Language.", 'start': 14.334, 'duration': 4.003}, {'end': 23.601, 'text': 'SQL is used to create, retrieve, update, and delete data from a database.', 'start': 18.717, 'duration': 4.884}, {'end': 27.064, 'text': 'Suppose we own a business, like the Krusty Krab from SpongeBob.', 'start': 23.881, 'duration': 3.183}, {'end': 33.288, 'text': 'If we had to manually track all of the transactions by pen and paper, Well, that would be a lot of extra work.', 'start': 27.244, 'duration': 6.044}], 'summary': 'Learn to write sql using mysql workbench for managing business data efficiently.', 'duration': 33.159, 'max_score': 0.129, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso129.jpg'}, {'end': 91.785, 'src': 'embed', 'start': 44.213, 'weight': 1, 'content': [{'end': 48.075, 'text': 'A table in a relational database, it resembles an Excel spreadsheet.', 'start': 44.213, 'duration': 3.862}, {'end': 49.736, 'text': "There's rows and columns.", 'start': 48.315, 'duration': 1.421}, {'end': 54.438, 'text': 'Tables within a relational database can form, well, relationships with one another.', 'start': 50.036, 'duration': 4.402}, {'end': 56.739, 'text': 'And that is done by this concept of keys.', 'start': 54.758, 'duration': 1.981}, {'end': 58.861, 'text': "which I'll explain in a further topic.", 'start': 57.159, 'duration': 1.702}, {'end': 61.343, 'text': "Then there's non-relational databases.", 'start': 59.261, 'duration': 2.082}, {'end': 65.626, 'text': "That's where our data is organized in any format but a table.", 'start': 61.803, 'duration': 3.823}, {'end': 72.192, 'text': 'This could include JSON files, key-value pairs, graph data structures, entities of that nature.', 'start': 65.906, 'duration': 6.286}, {'end': 76.275, 'text': 'To utilize data in a relational database, we would use SQL.', 'start': 72.632, 'duration': 3.643}, {'end': 84.002, 'text': 'Then with a non-relational database, we would use a different language named NoSQL, meaning not only SQL.', 'start': 76.575, 'duration': 7.427}, {'end': 91.785, 'text': 'But since this is an SQL series, we will be working with SQL and relational databases, not non-relational databases.', 'start': 84.522, 'duration': 7.263}], 'summary': 'Relational databases use tables and sql, non-relational use nosql.', 'duration': 47.572, 'max_score': 44.213, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso44213.jpg'}], 'start': 0.129, 'title': 'Sql and relational databases', 'summary': 'Introduces sql and relational databases, explaining their usage in managing and organizing data, with a comparison between relational and non-relational databases and the importance of sql in relational database management.', 'chapters': [{'end': 91.785, 'start': 0.129, 'title': 'Introduction to sql and relational databases', 'summary': 'Introduces sql and relational databases, explaining their usage in managing and organizing data, with a comparison between relational and non-relational databases, and the importance of sql in relational database management.', 'duration': 91.656, 'highlights': ['SQL is used to create, retrieve, update, and delete data from a database. SQL is a versatile language used for various operations on a database, providing flexibility in data management.', 'Relational databases use tables with rows and columns that can form relationships with each other through keys. Illustrates the structure of relational databases and the concept of relationships, essential for organizing and connecting data.', 'Non-relational databases organize data in formats like JSON files, key-value pairs, and graph data structures. Briefly explains the diverse formats used in non-relational databases, indicating their flexibility in data organization.']}], 'duration': 91.656, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso129.jpg', 'highlights': ['SQL is a versatile language used for various operations on a database, providing flexibility in data management.', 'Relational databases use tables with rows and columns that can form relationships with each other through keys.', 'Non-relational databases organize data in formats like JSON files, key-value pairs, and graph data structures.']}, {'end': 845.072, 'segs': [{'end': 154.941, 'src': 'embed', 'start': 92.265, 'weight': 0, 'content': [{'end': 99.328, 'text': 'To write SQL statements, we would need the help of a special piece of software known as a database management system.', 'start': 92.265, 'duration': 7.063}, {'end': 102.43, 'text': 'People shorten this to simply DBMS.', 'start': 99.649, 'duration': 2.781}, {'end': 107.636, 'text': "It's a workspace for us to write SQL statements, and generally just work with our database.", 'start': 102.97, 'duration': 4.666}, {'end': 109.178, 'text': "It'll make our lives easier.", 'start': 108.057, 'duration': 1.121}, {'end': 111.542, 'text': 'There are different DBMS systems.', 'start': 109.679, 'duration': 1.863}, {'end': 119.812, 'text': "you can use, one of which is MySQL, Microsoft SQL Server, Oracle and PostgreSQL, but there's still many more out there.", 'start': 111.542, 'duration': 8.27}, {'end': 127.938, 'text': "Each of these database management systems all use SQL, but there's subtle nuances between the syntax of each database management system.", 'start': 120.313, 'duration': 7.625}, {'end': 133.762, 'text': "If you're familiar with one database management system, transitioning to another will take little to no effort at all.", 'start': 128.398, 'duration': 5.364}, {'end': 142.268, 'text': "In this series, we will be working with the MySQL database management system, and I'll show you how to download that for both Windows and Mac OS.", 'start': 134.282, 'duration': 7.986}, {'end': 149.277, 'text': "Hey everybody, in this topic I'm going to explain how we can download MySQL using the Windows operating system.", 'start': 143.493, 'duration': 5.784}, {'end': 152.64, 'text': 'First of all, head to this website, mysql.com.', 'start': 149.597, 'duration': 3.043}, {'end': 154.941, 'text': 'Then go to the Downloads tab.', 'start': 153.28, 'duration': 1.661}], 'summary': 'Dbms like mysql, microsoft sql server, oracle, and postgresql use sql with subtle syntax differences. transitioning between them is easy.', 'duration': 62.676, 'max_score': 92.265, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso92265.jpg'}, {'end': 821.303, 'src': 'embed', 'start': 790.357, 'weight': 1, 'content': [{'end': 793.699, 'text': "How about alter? There's two features for beginners I'll mention.", 'start': 790.357, 'duration': 3.342}, {'end': 795.681, 'text': 'Setting a database to read-only.', 'start': 794.1, 'duration': 1.581}, {'end': 797.562, 'text': 'The other is enabling encryption.', 'start': 796.061, 'duration': 1.501}, {'end': 799.824, 'text': "Let's set our database to be read-only.", 'start': 797.882, 'duration': 1.942}, {'end': 803.386, 'text': 'Type alter database.', 'start': 800.284, 'duration': 3.102}, {'end': 805.488, 'text': 'The name of the database.', 'start': 804.487, 'duration': 1.001}, {'end': 811.059, 'text': 'Read only equals one.', 'start': 807.538, 'duration': 3.521}, {'end': 813.92, 'text': 'This statement would make our database read only.', 'start': 811.76, 'duration': 2.16}, {'end': 821.303, 'text': "If a database is in read only mode, we can't make any modifications to it, but we can still access the data within.", 'start': 813.94, 'duration': 7.363}], 'summary': 'Enabling read-only mode and encryption for database beginners.', 'duration': 30.946, 'max_score': 790.357, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso790357.jpg'}], 'start': 92.265, 'title': 'Dbms, sql, and mysql setup', 'summary': 'Introduces dbms, sql, and various systems like mysql, microsoft sql server, oracle, and postgresql. it also covers how to download, set up mysql on windows and mac os, including creating, using, and dropping a database.', 'chapters': [{'end': 133.762, 'start': 92.265, 'title': 'Intro to dbms and sql', 'summary': 'Introduces the necessity of dbms for writing sql statements and highlights the different dbms systems available, including mysql, microsoft sql server, oracle, and postgresql, and emphasizes the ease of transitioning between them.', 'duration': 41.497, 'highlights': ['DBMS is essential for writing SQL statements and working with databases, such as MySQL, Microsoft SQL Server, Oracle, and PostgreSQL.', "Transitioning between different DBMS systems requires little to no effort if you're familiar with one system.", 'Each DBMS system uses SQL, but there are subtle nuances in the syntax of each system.']}, {'end': 845.072, 'start': 134.282, 'title': 'Downloading and using mysql', 'summary': 'Explains how to download and set up mysql on windows and mac os, covering the process of downloading, installing, and using mysql, including setting up a local instance and creating, using, and dropping a database.', 'duration': 710.79, 'highlights': ['The chapter explains how to download and set up MySQL on Windows and Mac OS. The transcript provides a detailed guide on downloading and setting up MySQL on both Windows and Mac OS, offering comprehensive instructions for each operating system.', 'The process of downloading, installing, and using MySQL is covered, including setting up a local instance. The transcript details the process of downloading, installing, and using MySQL, including setting up a local instance for both Windows and Mac OS.', 'Creating, using, and dropping a database is explained, along with setting a database to read-only and enabling encryption. The chapter covers creating, using, and dropping a database, as well as setting a database to read-only and enabling encryption, providing a comprehensive overview of database management.']}], 'duration': 752.807, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso92265.jpg', 'highlights': ['DBMS is essential for writing SQL statements and working with databases, such as MySQL, Microsoft SQL Server, Oracle, and PostgreSQL.', 'The chapter covers creating, using, and dropping a database, as well as setting a database to read-only and enabling encryption, providing a comprehensive overview of database management.', 'The transcript provides a detailed guide on downloading and setting up MySQL on both Windows and Mac OS, offering comprehensive instructions for each operating system.', 'Each DBMS system uses SQL, but there are subtle nuances in the syntax of each system.', 'The process of downloading, installing, and using MySQL is covered, including setting up a local instance.']}, {'end': 1599.878, 'segs': [{'end': 900.225, 'src': 'embed', 'start': 870.838, 'weight': 0, 'content': [{'end': 874.719, 'text': "In this video, I'm going to show you how we can make some tables in MySQL.", 'start': 870.838, 'duration': 3.881}, {'end': 881.68, 'text': 'A table in a relational database, they consist of rows and columns, kind of like an Excel spreadsheet.', 'start': 875.119, 'duration': 6.561}, {'end': 888.201, 'text': "In this topic, we're going to create the table and the columns, but we'll be populating the rows in the next video.", 'start': 882.04, 'duration': 6.161}, {'end': 893.922, 'text': "To create a table, you'll type create table, then the name of the table.", 'start': 888.521, 'duration': 5.401}, {'end': 896.683, 'text': "I'll create a table named employees.", 'start': 894.302, 'duration': 2.381}, {'end': 900.225, 'text': 'Then add a set of parentheses, semicolon at the end.', 'start': 897.583, 'duration': 2.642}], 'summary': "Creating a table named 'employees' in mysql.", 'duration': 29.387, 'max_score': 870.838, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso870838.jpg'}, {'end': 960.74, 'src': 'embed', 'start': 928.843, 'weight': 1, 'content': [{'end': 934.465, 'text': "After each column, we need to set the data type of what we're storing within each column exactly.", 'start': 928.843, 'duration': 5.622}, {'end': 941.227, 'text': 'Is the data going to be whole integers, decimals, text, a timestamp? Those are data types.', 'start': 934.965, 'duration': 6.262}, {'end': 944.868, 'text': 'An employee ID, that could be a whole integer.', 'start': 941.947, 'duration': 2.921}, {'end': 949.109, 'text': 'The data type will be int, add int after the column name.', 'start': 945.468, 'duration': 3.641}, {'end': 952.211, 'text': "A first name, that's a series of characters.", 'start': 950.049, 'duration': 2.162}, {'end': 960.74, 'text': 'The data type for some text would be var char, then within parentheses, the maximum amount of characters.', 'start': 952.892, 'duration': 7.848}], 'summary': 'Defining data types for columns: int for employee id, var char for first name.', 'duration': 31.897, 'max_score': 928.843, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso928843.jpg'}, {'end': 1297.891, 'src': 'heatmap', 'start': 971.247, 'weight': 1, 'content': [{'end': 974.348, 'text': 'Maybe our hourly pay is in dollars and cents.', 'start': 971.247, 'duration': 3.101}, {'end': 976.189, 'text': "We'll need a decimal portion.", 'start': 974.829, 'duration': 1.36}, {'end': 977.77, 'text': 'There is a data type.', 'start': 976.83, 'duration': 0.94}, {'end': 979.811, 'text': 'That is decimal.', 'start': 978.751, 'duration': 1.06}, {'end': 981.572, 'text': 'Add a set of parentheses.', 'start': 980.512, 'duration': 1.06}, {'end': 985.795, 'text': "We'll add the maximum amount of digits for our decimal.", 'start': 982.353, 'duration': 3.442}, {'end': 987.676, 'text': 'I think 5 should be good.', 'start': 986.615, 'duration': 1.061}, {'end': 989.517, 'text': 'Then a precision.', 'start': 988.616, 'duration': 0.901}, {'end': 991.618, 'text': '2 for two decimal places.', 'start': 989.537, 'duration': 2.081}, {'end': 1003.058, 'text': "The reason that I set the maximum digits of my hourly pay to be five is that I don't anticipate anybody's hourly pay being over $999 and 99 cents per hour.", 'start': 992.608, 'duration': 10.45}, {'end': 1004.159, 'text': "That's pretty absurd.", 'start': 1003.398, 'duration': 0.761}, {'end': 1005.48, 'text': 'I think five is enough.', 'start': 1004.579, 'duration': 0.901}, {'end': 1007.402, 'text': 'Then we have higher date.', 'start': 1006.08, 'duration': 1.322}, {'end': 1010.244, 'text': 'There is a data type, which is date.', 'start': 1008.162, 'duration': 2.082}, {'end': 1013.908, 'text': "If you need to work with dates, otherwise there's date time.", 'start': 1010.504, 'duration': 3.404}, {'end': 1016.55, 'text': 'If you need to include the time, just the date is fine.', 'start': 1013.968, 'duration': 2.582}, {'end': 1018.492, 'text': "Let's execute this script.", 'start': 1017.171, 'duration': 1.321}, {'end': 1022.477, 'text': 'We have created our table employees.', 'start': 1020.575, 'duration': 1.902}, {'end': 1028.603, 'text': 'If I were to refresh my schemas window, underneath tables we have our employees table.', 'start': 1023.218, 'duration': 5.385}, {'end': 1031.445, 'text': "Now I'll show you how we can select a table.", 'start': 1029.564, 'duration': 1.881}, {'end': 1039.534, 'text': 'If you need to select your table, you can type select asterisk from the name of the table.', 'start': 1032.287, 'duration': 7.247}, {'end': 1043.559, 'text': "Employees Let's take a look.", 'start': 1040.575, 'duration': 2.984}, {'end': 1046.047, 'text': "Here's our table so far.", 'start': 1044.807, 'duration': 1.24}, {'end': 1048.728, 'text': "There's no rows, but there are columns.", 'start': 1046.627, 'duration': 2.101}, {'end': 1053.049, 'text': 'We have employee ID, first name, last name, hourly pay, and hire date.', 'start': 1049.268, 'duration': 3.781}, {'end': 1065.051, 'text': 'If you need to rename a table, you can type rename table the original name employees to whatever the new name is.', 'start': 1053.729, 'duration': 11.322}, {'end': 1066.692, 'text': 'Perhaps workers.', 'start': 1065.852, 'duration': 0.84}, {'end': 1067.592, 'text': "Let's try that.", 'start': 1066.992, 'duration': 0.6}, {'end': 1072.445, 'text': 'Rename table employees to workers was successful.', 'start': 1069.602, 'duration': 2.843}, {'end': 1078.753, 'text': 'If I refresh my schemas window, the table employees is now known as workers.', 'start': 1073.026, 'duration': 5.727}, {'end': 1080.274, 'text': "But I think that's stupid.", 'start': 1079.293, 'duration': 0.981}, {'end': 1081.516, 'text': "Let's go back and change it.", 'start': 1080.414, 'duration': 1.102}, {'end': 1085.781, 'text': 'Rename table workers to employees.', 'start': 1083.078, 'duration': 2.703}, {'end': 1093.968, 'text': 'To drop a table, I discussed this earlier, you would type drop table the name of the table.', 'start': 1087.605, 'duration': 6.363}, {'end': 1100.231, 'text': "I don't want to drop this, so I'm not going to execute this statement, but that's how you would drop a table.", 'start': 1094.648, 'duration': 5.583}, {'end': 1102.612, 'text': 'Drop table the name of the table.', 'start': 1100.531, 'duration': 2.081}, {'end': 1107.074, 'text': 'If you need to alter a table, there is the alter keyword.', 'start': 1103.713, 'duration': 3.361}, {'end': 1108.955, 'text': "Let's add a phone number.", 'start': 1108.035, 'duration': 0.92}, {'end': 1115.038, 'text': 'Alter table employees.', 'start': 1110.916, 'duration': 4.122}, {'end': 1119.335, 'text': "I'm gonna write this next part of the statement on a new line.", 'start': 1116.954, 'duration': 2.381}, {'end': 1128.439, 'text': 'What do we want to add? What about a phone number? Add phone number.', 'start': 1121.116, 'duration': 7.323}, {'end': 1132.821, 'text': "Then we'll need to list the data type of this column.", 'start': 1130.6, 'duration': 2.221}, {'end': 1134.922, 'text': 'Varchar is good.', 'start': 1134.142, 'duration': 0.78}, {'end': 1138.183, 'text': 'Max size of 15 characters.', 'start': 1136.282, 'duration': 1.901}, {'end': 1141.645, 'text': 'Then we will end this statement.', 'start': 1140.344, 'duration': 1.301}, {'end': 1145.141, 'text': 'Execute the script.', 'start': 1144.221, 'duration': 0.92}, {'end': 1147.042, 'text': 'All right, we have altered our table.', 'start': 1145.441, 'duration': 1.601}, {'end': 1149.022, 'text': "Let's take a look at our table.", 'start': 1147.782, 'duration': 1.24}, {'end': 1151.163, 'text': 'Select asterisk.', 'start': 1149.423, 'duration': 1.74}, {'end': 1152.203, 'text': 'Asterisk means all.', 'start': 1151.323, 'duration': 0.88}, {'end': 1153.244, 'text': "I don't know if I explained that.", 'start': 1152.243, 'duration': 1.001}, {'end': 1157.185, 'text': 'Select all from employees.', 'start': 1153.684, 'duration': 3.501}, {'end': 1164.647, 'text': 'We have employee ID, first name, last name, hourly pay, hire date, then our column phone number.', 'start': 1158.825, 'duration': 5.822}, {'end': 1168.208, 'text': "Let's rename phone number and change it to something else.", 'start': 1165.867, 'duration': 2.341}, {'end': 1170.985, 'text': 'we will again use the alter keyword.', 'start': 1169.244, 'duration': 1.741}, {'end': 1175.329, 'text': 'Alter table employees.', 'start': 1171.926, 'duration': 3.403}, {'end': 1186.137, 'text': "Rename column phone number to let's say email.", 'start': 1178.211, 'duration': 7.926}, {'end': 1192.783, 'text': "Okay then, let's take a look again.", 'start': 1186.157, 'duration': 6.626}, {'end': 1195.285, 'text': 'Select all from employees.', 'start': 1193.443, 'duration': 1.842}, {'end': 1202.381, 'text': 'Yeah, employee ID, first name, last name, hourly pay, hire date, then email.', 'start': 1198.459, 'duration': 3.922}, {'end': 1205.823, 'text': 'However, the data type has not changed for email.', 'start': 1203.001, 'duration': 2.822}, {'end': 1214.808, 'text': "I'm going to refresh my schemas, then underneath information, underneath employees, our email column has a max size of 15 characters.", 'start': 1206.563, 'duration': 8.245}, {'end': 1215.968, 'text': "Let's change that.", 'start': 1215.188, 'duration': 0.78}, {'end': 1233.545, 'text': 'Alter table employees modify column email, then the new data type var char.', 'start': 1217.769, 'duration': 15.776}, {'end': 1237.249, 'text': 'maybe 100 characters for an email.', 'start': 1233.545, 'duration': 3.704}, {'end': 1240.753, 'text': "let's execute this statement.", 'start': 1237.249, 'duration': 3.504}, {'end': 1242.535, 'text': 'refresh our schemas.', 'start': 1240.753, 'duration': 1.782}, {'end': 1244.937, 'text': 'yeah, now our email column has a size of 100 characters.', 'start': 1242.535, 'duration': 2.402}, {'end': 1249.565, 'text': 'Maybe we need to change the position of our email column.', 'start': 1246.704, 'duration': 2.861}, {'end': 1253.126, 'text': 'If you need to move columns around, this is how you can do so.', 'start': 1250.225, 'duration': 2.901}, {'end': 1257.387, 'text': "Let's move our email column so it's after our last name column.", 'start': 1253.706, 'duration': 3.681}, {'end': 1270.15, 'text': 'To do that, we would type alter table, the name of the table, modify the name of the column to be moved, email in this case, then the data type.', 'start': 1258.147, 'duration': 12.003}, {'end': 1275.151, 'text': 'This email column has a data type of varchar100.', 'start': 1272.39, 'duration': 2.761}, {'end': 1277.773, 'text': 'Then after.', 'start': 1276.932, 'duration': 0.841}, {'end': 1281.997, 'text': 'Then the name of the column we would like our email column to follow.', 'start': 1278.574, 'duration': 3.423}, {'end': 1285.66, 'text': 'I need my email column to come after my last name column.', 'start': 1282.577, 'duration': 3.083}, {'end': 1289.003, 'text': 'I will type after last name.', 'start': 1286.441, 'duration': 2.562}, {'end': 1292.626, 'text': 'Then afterwards I might as well just display everything.', 'start': 1290.384, 'duration': 2.242}, {'end': 1295.429, 'text': 'Select all from employees.', 'start': 1292.946, 'duration': 2.483}, {'end': 1297.891, 'text': 'You can write more than one SQL statement.', 'start': 1295.949, 'duration': 1.942}], 'summary': 'The speaker explains sql table creation, modification, and data selection, including setting decimal precision and altering column data types and positions.', 'duration': 326.644, 'max_score': 971.247, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso971247.jpg'}, {'end': 1128.439, 'src': 'embed', 'start': 1094.648, 'weight': 4, 'content': [{'end': 1100.231, 'text': "I don't want to drop this, so I'm not going to execute this statement, but that's how you would drop a table.", 'start': 1094.648, 'duration': 5.583}, {'end': 1102.612, 'text': 'Drop table the name of the table.', 'start': 1100.531, 'duration': 2.081}, {'end': 1107.074, 'text': 'If you need to alter a table, there is the alter keyword.', 'start': 1103.713, 'duration': 3.361}, {'end': 1108.955, 'text': "Let's add a phone number.", 'start': 1108.035, 'duration': 0.92}, {'end': 1115.038, 'text': 'Alter table employees.', 'start': 1110.916, 'duration': 4.122}, {'end': 1119.335, 'text': "I'm gonna write this next part of the statement on a new line.", 'start': 1116.954, 'duration': 2.381}, {'end': 1128.439, 'text': 'What do we want to add? What about a phone number? Add phone number.', 'start': 1121.116, 'duration': 7.323}], 'summary': "Demonstration of sql table alteration, including adding a phone number to the 'employees' table.", 'duration': 33.791, 'max_score': 1094.648, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1094648.jpg'}, {'end': 1195.285, 'src': 'embed', 'start': 1158.825, 'weight': 3, 'content': [{'end': 1164.647, 'text': 'We have employee ID, first name, last name, hourly pay, hire date, then our column phone number.', 'start': 1158.825, 'duration': 5.822}, {'end': 1168.208, 'text': "Let's rename phone number and change it to something else.", 'start': 1165.867, 'duration': 2.341}, {'end': 1170.985, 'text': 'we will again use the alter keyword.', 'start': 1169.244, 'duration': 1.741}, {'end': 1175.329, 'text': 'Alter table employees.', 'start': 1171.926, 'duration': 3.403}, {'end': 1186.137, 'text': "Rename column phone number to let's say email.", 'start': 1178.211, 'duration': 7.926}, {'end': 1192.783, 'text': "Okay then, let's take a look again.", 'start': 1186.157, 'duration': 6.626}, {'end': 1195.285, 'text': 'Select all from employees.', 'start': 1193.443, 'duration': 1.842}], 'summary': "The transcript discusses renaming the 'phone number' column to 'email' in the 'employees' table.", 'duration': 36.46, 'max_score': 1158.825, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1158825.jpg'}, {'end': 1372.489, 'src': 'embed', 'start': 1346.323, 'weight': 2, 'content': [{'end': 1352.188, 'text': "In the next video, we're either going to talk about data types, or learn about inserting rows into our tables.", 'start': 1346.323, 'duration': 5.865}, {'end': 1355.431, 'text': "I haven't decided yet, but we'll probably cover one of those too.", 'start': 1352.628, 'duration': 2.803}, {'end': 1359.485, 'text': 'Hey everybody.', 'start': 1358.885, 'duration': 0.6}, {'end': 1363.226, 'text': "In this topic, I'm going to show you how we can insert rows into a table.", 'start': 1359.605, 'duration': 3.621}, {'end': 1365.247, 'text': 'We have a table named Employees.', 'start': 1363.486, 'duration': 1.761}, {'end': 1368.388, 'text': 'I will select everything from my table Employees.', 'start': 1365.747, 'duration': 2.641}, {'end': 1372.489, 'text': 'Here are the columns.', 'start': 1371.728, 'duration': 0.761}], 'summary': 'Tutorial on inserting rows into the employees table.', 'duration': 26.166, 'max_score': 1346.323, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1346323.jpg'}], 'start': 847.294, 'title': 'Mysql table creation and sql data manipulation', 'summary': 'Explains how to create mysql tables with specific columns such as employee id, first name, last name, hourly pay, and hired date, limiting hourly pay to $999.99 per hour. it also covers working with sql tables and columns, including altering and inserting data, and understanding data types.', 'chapters': [{'end': 1010.244, 'start': 847.294, 'title': 'Creating mysql tables', 'summary': 'Explains how to create and define columns for a mysql table, such as employee id, first name, last name, hourly pay, and hired date, specifying their respective data types, with hourly pay limited to a maximum of $999.99 per hour.', 'duration': 162.95, 'highlights': ["To create a table, you'll type create table, then the name of the table, followed by a set of parentheses containing the columns and their respective data types.", 'The data types for the columns include int for employee ID, var char for first and last names, decimal for hourly pay with a maximum of 5 digits and a precision of 2, and date for the hired date.']}, {'end': 1599.878, 'start': 1010.504, 'title': 'Working with sql tables and columns', 'summary': 'Covers creating, altering, and inserting data into sql tables, including renaming, dropping, and modifying table columns, with a demonstration of inserting multiple rows at once and understanding data types.', 'duration': 589.374, 'highlights': ['Demonstrated how to insert rows into a table, including specifying data types and formatting dates, and showed the process of manually inserting rows one by one as well as inserting multiple rows at once Demonstrated inserting rows for 5 employees, including specific details such as employee ID, first name, last name, hourly pay, and hire date', "Explained the process of renaming, dropping, and modifying table columns, including changing the position of a column and dropping a column from the table Demonstrated renaming the table 'employees' to 'workers' and back to 'employees', altering the column 'phone number' to 'email', modifying the data type and size of the 'email' column, and dropping the 'email' column", "Covered the process of creating, altering, and working with SQL tables, including selecting, renaming, and dropping tables, and adding new columns to the table Covered creating a table named 'employees', altering the table to add a new column 'phone number', and demonstrating the process of dropping a table"]}], 'duration': 752.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso847294.jpg', 'highlights': ["To create a table, you'll type create table, then the name of the table, followed by a set of parentheses containing the columns and their respective data types.", 'The data types for the columns include int for employee ID, var char for first and last names, decimal for hourly pay with a maximum of 5 digits and a precision of 2, and date for the hired date.', 'Demonstrated how to insert rows into a table, including specifying data types and formatting dates, and showed the process of manually inserting rows one by one as well as inserting multiple rows at once Demonstrated inserting rows for 5 employees, including specific details such as employee ID, first name, last name, hourly pay, and hire date', "Explained the process of renaming, dropping, and modifying table columns, including changing the position of a column and dropping a column from the table Demonstrated renaming the table 'employees' to 'workers' and back to 'employees', altering the column 'phone number' to 'email', modifying the data type and size of the 'email' column, and dropping the 'email' column", "Covered the process of creating, altering, and working with SQL tables, including selecting, renaming, and dropping tables, and adding new columns to the table Covered creating a table named 'employees', altering the table to add a new column 'phone number', and demonstrating the process of dropping a table"]}, {'end': 2526.096, 'segs': [{'end': 1742.016, 'src': 'embed', 'start': 1713.153, 'weight': 2, 'content': [{'end': 1716.156, 'text': "In this video, I'm gonna show you how we can select data from a table.", 'start': 1713.153, 'duration': 3.003}, {'end': 1724.224, 'text': 'To query all of the data from a table, we would type select asterisk, meaning all, from the name of the table.', 'start': 1716.476, 'duration': 7.748}, {'end': 1727.286, 'text': 'In my case, we have table employees.', 'start': 1725.024, 'duration': 2.262}, {'end': 1733.611, 'text': 'This statement, select all from employees, will give me all columns and all rows.', 'start': 1727.887, 'duration': 5.724}, {'end': 1736.492, 'text': 'But sometimes you may not want all of the data.', 'start': 1734.071, 'duration': 2.421}, {'end': 1737.873, 'text': "Here's a scenario.", 'start': 1736.913, 'duration': 0.96}, {'end': 1742.016, 'text': 'Your boss gives you a task of retrieving the full name of every employee.', 'start': 1738.053, 'duration': 3.963}], 'summary': "Demonstrates selecting data from a table, using 'select all from employees' as an example.", 'duration': 28.863, 'max_score': 1713.153, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1713153.jpg'}, {'end': 1849.592, 'src': 'embed', 'start': 1818.821, 'weight': 3, 'content': [{'end': 1820.182, 'text': 'Four, Patrick.', 'start': 1818.821, 'duration': 1.361}, {'end': 1821.643, 'text': 'I think you get the idea.', 'start': 1820.602, 'duration': 1.041}, {'end': 1822.804, 'text': "Here's an exercise.", 'start': 1821.924, 'duration': 0.88}, {'end': 1828.529, 'text': "Let's find all the data from employees where the first name equals SpongeBob.", 'start': 1822.964, 'duration': 5.565}, {'end': 1834.634, 'text': 'Where first name equals SpongeBob.', 'start': 1829.39, 'duration': 5.244}, {'end': 1842.228, 'text': 'This query will return employee ID three, the first name, last name, hourly pay, and hire date.', 'start': 1835.905, 'duration': 6.323}, {'end': 1843.749, 'text': "Here's another example.", 'start': 1842.649, 'duration': 1.1}, {'end': 1849.592, 'text': "Let's find all employees that have an hourly pay greater than or equal to 15.", 'start': 1844.009, 'duration': 5.583}], 'summary': 'Query for employees with first name spongebob and hourly pay >= 15.', 'duration': 30.771, 'max_score': 1818.821, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1818821.jpg'}, {'end': 1919.515, 'src': 'embed', 'start': 1892.253, 'weight': 4, 'content': [{'end': 1896.777, 'text': 'This query returns two results, Eugene Krabs and Squidward Tentacles.', 'start': 1892.253, 'duration': 4.524}, {'end': 1898.838, 'text': 'They both match this criteria.', 'start': 1897.337, 'duration': 1.501}, {'end': 1902.421, 'text': 'And in this case, we use the less than or equals to operator.', 'start': 1899.359, 'duration': 3.062}, {'end': 1906.685, 'text': 'Another operator you should know about is the not comparison operator.', 'start': 1902.922, 'duration': 3.763}, {'end': 1909.227, 'text': "It's an exclamation point and an equal sign.", 'start': 1907.045, 'duration': 2.182}, {'end': 1912.069, 'text': 'We will check if something is not equal.', 'start': 1909.547, 'duration': 2.522}, {'end': 1919.515, 'text': "Let's find where employee ID does not equal one.", 'start': 1913.65, 'duration': 5.865}], 'summary': 'Query returns 2 results, using <= operator and not comparison operator, checking for employee id not equal to 1.', 'duration': 27.262, 'max_score': 1892.253, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1892253.jpg'}, {'end': 2055.081, 'src': 'embed', 'start': 1994.991, 'weight': 0, 'content': [{'end': 2005.034, 'text': 'It can be everything or specific columns from a table of your choosing where the results equal some criteria that you set.', 'start': 1994.991, 'duration': 10.043}, {'end': 2006.994, 'text': "It really depends on what you're looking for.", 'start': 2005.554, 'duration': 1.44}, {'end': 2009.535, 'text': 'And that is how to query data from a table.', 'start': 2007.554, 'duration': 1.981}, {'end': 2013.857, 'text': 'Hey, everybody.', 'start': 2013.237, 'duration': 0.62}, {'end': 2018.12, 'text': "In this video, I'm going to show you how we can update and delete data from a table.", 'start': 2014.057, 'duration': 4.063}, {'end': 2021.141, 'text': 'In my example, we have a table of employees.', 'start': 2018.88, 'duration': 2.261}, {'end': 2027.184, 'text': 'However, Sheldon Plankton is missing some information, an hourly pay and a hire date.', 'start': 2021.921, 'duration': 5.263}, {'end': 2028.865, 'text': "Let's update those fields.", 'start': 2027.765, 'duration': 1.1}, {'end': 2040.592, 'text': 'To update some data in a table, we would use the update keyword, the name of the table, employees in my example, Then set.', 'start': 2029.285, 'duration': 11.307}, {'end': 2045.516, 'text': "Which column would we like to interact with first? Let's interact with hourly pay.", 'start': 2041.153, 'duration': 4.363}, {'end': 2049.358, 'text': 'Set hourly pay.', 'start': 2046.276, 'duration': 3.082}, {'end': 2055.081, 'text': "Let's pay Plankton $10.25 per hour.", 'start': 2051.039, 'duration': 4.042}], 'summary': "Demonstrating how to update data in a table, setting sheldon plankton's hourly pay to $10.25 per hour.", 'duration': 60.09, 'max_score': 1994.991, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1994991.jpg'}, {'end': 2128.1, 'src': 'embed', 'start': 2087.592, 'weight': 6, 'content': [{'end': 2092.636, 'text': 'To update multiple columns, you can change more than one field at once.', 'start': 2087.592, 'duration': 5.044}, {'end': 2098.755, 'text': 'After your first change, you can add a comma Then change another field.', 'start': 2093.456, 'duration': 5.299}, {'end': 2102.978, 'text': "Let's change Plankton's hire date to be the following.", 'start': 2099.515, 'duration': 3.463}, {'end': 2107.381, 'text': 'Hire date equals 10-23-0-07.', 'start': 2103.558, 'duration': 3.823}, {'end': 2109.362, 'text': "I'll make his hourly pay 10.50.", 'start': 2107.541, 'duration': 1.821}, {'end': 2111.403, 'text': "I'm feeling generous.", 'start': 2109.362, 'duration': 2.041}, {'end': 2112.904, 'text': 'Here we are.', 'start': 2111.443, 'duration': 1.461}, {'end': 2114.125, 'text': "Plankton's hourly pay is now $10.50.", 'start': 2112.924, 'duration': 1.201}, {'end': 2128.1, 'text': 'His hire date is January 7th, 2023.', 'start': 2114.125, 'duration': 13.975}], 'summary': "Updated plankton's hire date to january 7th, 2023 and hourly pay to $10.50.", 'duration': 40.508, 'max_score': 2087.592, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso2087592.jpg'}, {'end': 2378.496, 'src': 'heatmap', 'start': 2153.419, 'weight': 7, 'content': [{'end': 2158.502, 'text': 'To update all of the rows within a column, you would exclude the WHERE clause.', 'start': 2153.419, 'duration': 5.083}, {'end': 2165.526, 'text': 'If I were to set hourlyPay equal to 1025, that would affect all of the rows.', 'start': 2159.583, 'duration': 5.943}, {'end': 2171.669, 'text': 'After executing this statement, The hourly pay for everybody is now 1025.', 'start': 2166.166, 'duration': 5.503}, {'end': 2176.952, 'text': "That's an example of how you could set a column to be one consistent value.", 'start': 2171.669, 'duration': 5.283}, {'end': 2185.096, 'text': 'To delete a row from a table, you would type delete from the name of the table.', 'start': 2178.112, 'duration': 6.984}, {'end': 2187.858, 'text': "Now don't write just the statement.", 'start': 2186.037, 'duration': 1.821}, {'end': 2190.319, 'text': 'It will delete all rows in your table.', 'start': 2188.478, 'duration': 1.841}, {'end': 2191.7, 'text': "Here's an example.", 'start': 2190.899, 'duration': 0.801}, {'end': 2193.041, 'text': 'Do not do this.', 'start': 2191.96, 'duration': 1.081}, {'end': 2195.342, 'text': 'I will delete from employees.', 'start': 2193.661, 'duration': 1.681}, {'end': 2198.363, 'text': 'There, my whole table is gone.', 'start': 2196.923, 'duration': 1.44}, {'end': 2199.684, 'text': 'Do not do this.', 'start': 2198.804, 'duration': 0.88}, {'end': 2202.765, 'text': 'Be sure to add a WHERE clause when deleting from a table.', 'start': 2200.004, 'duration': 2.761}, {'end': 2204.785, 'text': 'Otherwise, this will delete all your rows.', 'start': 2203.105, 'duration': 1.68}, {'end': 2213.288, 'text': 'WHERE employee ID equals 6.', 'start': 2205.485, 'duration': 7.803}, {'end': 2214.948, 'text': 'Yep, and Plankton is no longer there.', 'start': 2213.288, 'duration': 1.66}, {'end': 2221.17, 'text': 'All right, everybody, that is a super quick video on how to update and delete data from a table.', 'start': 2214.968, 'duration': 6.202}, {'end': 2224.388, 'text': 'Hey everybody.', 'start': 2223.868, 'duration': 0.52}, {'end': 2228.369, 'text': "In this topic, I'm going to explain auto commit, commit, and rollback.", 'start': 2224.568, 'duration': 3.801}, {'end': 2229.91, 'text': 'Auto commit is a mode.', 'start': 2228.729, 'duration': 1.181}, {'end': 2232.41, 'text': 'By default, auto commit is set to on.', 'start': 2230.11, 'duration': 2.3}, {'end': 2236.972, 'text': 'Whenever you execute a transaction within MySQL, that transaction is saved.', 'start': 2232.571, 'duration': 4.401}, {'end': 2241.293, 'text': 'What if we were to make a transaction and we need to undo that transaction?', 'start': 2237.332, 'duration': 3.961}, {'end': 2245.094, 'text': 'For example, what if we accidentally delete all of the rows of this table?', 'start': 2241.733, 'duration': 3.361}, {'end': 2247.335, 'text': "Don't do what I do, but I'll give you a demonstration.", 'start': 2245.414, 'duration': 1.921}, {'end': 2254.077, 'text': 'I accidentally type in delete from employees, but I forget to add a where clause.', 'start': 2248.155, 'duration': 5.922}, {'end': 2256.298, 'text': 'Well, all my rows are now gone.', 'start': 2254.697, 'duration': 1.601}, {'end': 2265.441, 'text': "How do I undo these changes? Well, what we're going to do is set auto commit to equal off.", 'start': 2256.578, 'duration': 8.863}, {'end': 2271.703, 'text': 'With this setting set to off, our transactions will not save automatically.', 'start': 2267.221, 'duration': 4.482}, {'end': 2275.384, 'text': 'We would need to manually save each transaction.', 'start': 2272.503, 'duration': 2.881}, {'end': 2277.385, 'text': 'It creates a save point, so to say.', 'start': 2275.904, 'duration': 1.481}, {'end': 2284.758, 'text': "I'm going to create a save point by typing commit, then execute.", 'start': 2279.676, 'duration': 5.082}, {'end': 2290.339, 'text': "Now I'm going to select my table, select all from the name of my table.", 'start': 2285.418, 'duration': 4.921}, {'end': 2295.481, 'text': "Then I'm gonna go ahead and delete all the rows.", 'start': 2293.46, 'duration': 2.021}, {'end': 2299.742, 'text': 'Delete from employees.', 'start': 2297.541, 'duration': 2.201}, {'end': 2304.091, 'text': 'Oh shoot, but I forgot to add a where clause.', 'start': 2301.75, 'duration': 2.341}, {'end': 2305.552, 'text': 'My whole table is gone now.', 'start': 2304.311, 'duration': 1.241}, {'end': 2309.414, 'text': 'Luckily, we created a save point with that commit statement.', 'start': 2306.372, 'duration': 3.042}, {'end': 2315.317, 'text': 'To undo these changes, I can execute the rollback statement.', 'start': 2310.234, 'duration': 5.083}, {'end': 2323.04, 'text': 'This will restore my current transaction back to the previous save point where we used commit.', 'start': 2318.058, 'duration': 4.982}, {'end': 2332.169, 'text': 'Now, if I were to select all from the name of my table, Our table is back to what it previously was.', 'start': 2323.821, 'duration': 8.348}, {'end': 2340.234, 'text': "And again, to save any changes, let's say I delete my whole table again, delete from employees.", 'start': 2333.19, 'duration': 7.044}, {'end': 2345.058, 'text': 'If I want to save this change, I would commit.', 'start': 2341.695, 'duration': 3.363}, {'end': 2350.181, 'text': 'Then again, I will select my table.', 'start': 2348.5, 'duration': 1.681}, {'end': 2356.359, 'text': 'And that last change is now saved.', 'start': 2354.758, 'duration': 1.601}, {'end': 2357.58, 'text': 'My whole table is gone.', 'start': 2356.599, 'duration': 0.981}, {'end': 2361.623, 'text': 'So yeah, that is auto commit, commit, and rollback.', 'start': 2358.741, 'duration': 2.882}, {'end': 2364.385, 'text': 'By default, auto commit is set to be on.', 'start': 2362.143, 'duration': 2.242}, {'end': 2367.227, 'text': 'Any transactions after executing are saved.', 'start': 2364.906, 'duration': 2.321}, {'end': 2371.29, 'text': 'If that mode is set to off, you can commit any changes manually.', 'start': 2367.588, 'duration': 3.702}, {'end': 2373.892, 'text': 'To undo any changes, use that rollback statement.', 'start': 2371.59, 'duration': 2.302}, {'end': 2378.496, 'text': 'So yeah, that is auto commit, commit, and rollback in MySQL.', 'start': 2374.313, 'duration': 4.183}], 'summary': 'Updating or deleting rows without where clause affects all data. auto commit, commit, and rollback can manage transactions in mysql.', 'duration': 61.529, 'max_score': 2153.419, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso2153419.jpg'}, {'end': 2290.339, 'src': 'embed', 'start': 2256.578, 'weight': 10, 'content': [{'end': 2265.441, 'text': "How do I undo these changes? Well, what we're going to do is set auto commit to equal off.", 'start': 2256.578, 'duration': 8.863}, {'end': 2271.703, 'text': 'With this setting set to off, our transactions will not save automatically.', 'start': 2267.221, 'duration': 4.482}, {'end': 2275.384, 'text': 'We would need to manually save each transaction.', 'start': 2272.503, 'duration': 2.881}, {'end': 2277.385, 'text': 'It creates a save point, so to say.', 'start': 2275.904, 'duration': 1.481}, {'end': 2284.758, 'text': "I'm going to create a save point by typing commit, then execute.", 'start': 2279.676, 'duration': 5.082}, {'end': 2290.339, 'text': "Now I'm going to select my table, select all from the name of my table.", 'start': 2285.418, 'duration': 4.921}], 'summary': 'Setting auto commit to off requires manual transaction saving and creates save points.', 'duration': 33.761, 'max_score': 2256.578, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso2256578.jpg'}, {'end': 2393.018, 'src': 'embed', 'start': 2362.143, 'weight': 9, 'content': [{'end': 2364.385, 'text': 'By default, auto commit is set to be on.', 'start': 2362.143, 'duration': 2.242}, {'end': 2367.227, 'text': 'Any transactions after executing are saved.', 'start': 2364.906, 'duration': 2.321}, {'end': 2371.29, 'text': 'If that mode is set to off, you can commit any changes manually.', 'start': 2367.588, 'duration': 3.702}, {'end': 2373.892, 'text': 'To undo any changes, use that rollback statement.', 'start': 2371.59, 'duration': 2.302}, {'end': 2378.496, 'text': 'So yeah, that is auto commit, commit, and rollback in MySQL.', 'start': 2374.313, 'duration': 4.183}, {'end': 2382.65, 'text': 'hey, everybody in this topic.', 'start': 2381.189, 'duration': 1.461}, {'end': 2387.414, 'text': 'i have a super quick video on getting the current date and time in mysql.', 'start': 2382.65, 'duration': 4.764}, {'end': 2389.895, 'text': "let's create a temporary table at the end of this topic.", 'start': 2387.414, 'duration': 2.481}, {'end': 2393.018, 'text': "we'll delete it, create table.", 'start': 2389.895, 'duration': 3.123}], 'summary': 'In mysql, auto commit is on by default, allowing saving of transactions, with the option to manually commit changes and use rollback statement to undo changes.', 'duration': 30.875, 'max_score': 2362.143, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso2362143.jpg'}, {'end': 2450.705, 'src': 'embed', 'start': 2421.071, 'weight': 11, 'content': [{'end': 2424.054, 'text': 'then i will select all from our table test.', 'start': 2421.071, 'duration': 2.983}, {'end': 2430.298, 'text': 'We have three columns a date, a time and a date time, which combines both.', 'start': 2425.336, 'duration': 4.962}, {'end': 2437.42, 'text': 'So how do we get the current date? Maybe we need to create a timestamp of when some event happened, maybe a higher date for our employees.', 'start': 2430.798, 'duration': 6.622}, {'end': 2445.443, 'text': "So I'm going to insert into the name of my table, test, some values.", 'start': 2438, 'duration': 7.443}, {'end': 2450.705, 'text': "For the current date, there's a built-in function.", 'start': 2447.824, 'duration': 2.881}], 'summary': 'Selecting data from a table with three columns, inserting values using a built-in function.', 'duration': 29.634, 'max_score': 2421.071, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso2421071.jpg'}], 'start': 1601.96, 'title': 'Sql and mysql data manipulation', 'summary': 'Covers inserting, updating, and deleting rows in mysql tables, sql data querying, and operators, with practical examples and specific sql commands, offering flexibility in data manipulation and retrieval.', 'chapters': [{'end': 1787.31, 'start': 1601.96, 'title': 'Mysql table data manipulation', 'summary': 'Covers inserting rows into a mysql table, demonstrating errors when inserting missing data, and selecting specific columns from a table, allowing for flexibility in data retrieval and manipulation.', 'duration': 185.35, 'highlights': ['Demonstrates inserting a new employee into a table with missing data, resulting in an error due to the missing hourly pay and hire date, showcasing the importance of specifying all required columns for insertion.', 'Illustrates the process of selecting specific columns from a table, allowing for flexible data retrieval and manipulation based on the requirements, ensuring efficiency in querying specific data.', "Provides guidance on querying all data from a table using the 'select asterisk' statement, demonstrating the capability to retrieve all columns and rows from a table for comprehensive data analysis and manipulation."]}, {'end': 2021.141, 'start': 1787.57, 'title': 'Sql data querying and operators', 'summary': 'Introduces sql data querying and operators, including examples of selecting specific data from a table based on criteria, such as employee id, first name, hourly pay, hire date, and using comparison and null operators, with examples yielding specific results and explaining the process.', 'duration': 233.571, 'highlights': ['Selecting specific data from a table based on criteria, such as employee ID, first name, hourly pay, hire date The examples illustrate selecting all from employees where employee ID equals one, finding data where first name equals SpongeBob, and filtering employees with hourly pay greater than or equal to 15.', "Examples of using comparison and null operators to yield specific results The transcript demonstrates the use of comparison operators like 'greater than or equal to' for hourly pay, 'less than or equal to' for hire date, and 'not equal' for employee ID, as well as the 'is null' and 'is not null' operators for filtering data based on the absence or presence of values.", 'Explanation of the process of querying data from a table and the flexibility of criteria setting The chapter provides a comprehensive explanation of how to query data from a table, emphasizing the flexibility to select specific columns based on user-defined criteria, demonstrating the process with various examples.']}, {'end': 2152.999, 'start': 2021.921, 'title': 'Updating employee data in sql', 'summary': 'Explains how to update employee data in a sql table, including setting hourly pay, changing hire dates, and setting fields to null, with examples and specific sql commands.', 'duration': 131.078, 'highlights': ["To update a specific field in a SQL table, such as hourly pay, the 'update' keyword and 'set' command are used, specifying the table name and the desired value, e.g., setting Plankton's hourly pay to $10.25 per hour.", "When updating multiple columns, after the first change, additional changes can be made by adding a comma and specifying the next field to be updated, e.g., changing Plankton's hire date to January 7th, 2023, and increasing his hourly pay to $10.50.", "To set a field to null, the syntax 'field = null' is used, for example, setting Plankton's hire date to null, indicating a possible termination."]}, {'end': 2526.096, 'start': 2153.419, 'title': 'Mysql data manipulation and time functions', 'summary': 'Covers updating and deleting rows in a table, explaining auto commit, commit, and rollback, and demonstrating how to get the current date and time in mysql, with a focus on practical examples and functions.', 'duration': 372.677, 'highlights': ['The hourly pay for everybody is now 1025. Executing a statement to set hourly pay to 1025 affects all rows, demonstrating the impact of updating without a WHERE clause.', 'Be sure to add a WHERE clause when deleting from a table. Otherwise, this will delete all your rows. Emphasizing the importance of using a WHERE clause when deleting from a table to avoid unintentional deletion of all rows.', 'Auto commit is a mode set to on by default in MySQL. Explaining the default setting of auto commit mode in MySQL, where transactions are automatically saved.', 'Setting auto commit to off creates a save point, allowing manual saving of transactions. Describing how turning off auto commit mode creates a save point, enabling manual saving of transactions and providing control over changes.', 'Demonstrating the usage of current_date, current_time, and now functions to retrieve the current date and time in MySQL. Illustrating the practical usage of built-in functions to obtain the current date and time in MySQL, with specific examples and their implications.']}], 'duration': 924.136, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso1601960.jpg', 'highlights': ['Covers inserting, updating, and deleting rows in mysql tables, sql data querying, and operators, with practical examples and specific sql commands, offering flexibility in data manipulation and retrieval.', 'Illustrates the process of selecting specific columns from a table, allowing for flexible data retrieval and manipulation based on the requirements, ensuring efficiency in querying specific data.', "Provides guidance on querying all data from a table using the 'select asterisk' statement, demonstrating the capability to retrieve all columns and rows from a table for comprehensive data analysis and manipulation.", 'Selecting specific data from a table based on criteria, such as employee ID, first name, hourly pay, hire date The examples illustrate selecting all from employees where employee ID equals one, finding data where first name equals SpongeBob, and filtering employees with hourly pay greater than or equal to 15.', "Examples of using comparison and null operators to yield specific results The transcript demonstrates the use of comparison operators like 'greater than or equal to' for hourly pay, 'less than or equal to' for hire date, and 'not equal' for employee ID, as well as the 'is null' and 'is not null' operators for filtering data based on the absence or presence of values.", "To update a specific field in a SQL table, such as hourly pay, the 'update' keyword and 'set' command are used, specifying the table name and the desired value, e.g., setting Plankton's hourly pay to $10.25 per hour.", "When updating multiple columns, after the first change, additional changes can be made by adding a comma and specifying the next field to be updated, e.g., changing Plankton's hire date to January 7th, 2023, and increasing his hourly pay to $10.50.", 'The hourly pay for everybody is now 1025. Executing a statement to set hourly pay to 1025 affects all rows, demonstrating the impact of updating without a WHERE clause.', 'Be sure to add a WHERE clause when deleting from a table. Otherwise, this will delete all your rows. Emphasizing the importance of using a WHERE clause when deleting from a table to avoid unintentional deletion of all rows.', 'Auto commit is a mode set to on by default in MySQL. Explaining the default setting of auto commit mode in MySQL, where transactions are automatically saved.', 'Setting auto commit to off creates a save point, allowing manual saving of transactions. Describing how turning off auto commit mode creates a save point, enabling manual saving of transactions and providing control over changes.', 'Demonstrating the usage of current_date, current_time, and now functions to retrieve the current date and time in MySQL. Illustrating the practical usage of built-in functions to obtain the current date and time in MySQL, with specific examples and their implications.']}, {'end': 5223.533, 'segs': [{'end': 3574.523, 'src': 'heatmap', 'start': 3448.016, 'weight': 0.744, 'content': [{'end': 3462.446, 'text': 'you would instead type alter table, the name of the table, alter the name of the column, price set default constraint, then the value so zero.', 'start': 3448.016, 'duration': 14.43}, {'end': 3465.889, 'text': 'And that appears to have worked.', 'start': 3464.768, 'duration': 1.121}, {'end': 3470.732, 'text': "Let's select our products table, select all from products.", 'start': 3466.569, 'duration': 4.163}, {'end': 3474.455, 'text': 'Then we will insert some new rows.', 'start': 3473.014, 'duration': 1.441}, {'end': 3483.695, 'text': 'insert into products than our values.', 'start': 3476.713, 'duration': 6.982}, {'end': 3486.536, 'text': 'So we had four rows.', 'start': 3485.255, 'duration': 1.281}, {'end': 3496.538, 'text': '104 That was a straw.', 'start': 3486.556, 'duration': 9.982}, {'end': 3498.979, 'text': '105 was a napkin.', 'start': 3496.558, 'duration': 2.421}, {'end': 3500.199, 'text': '106 was a fork.', 'start': 3498.999, 'duration': 1.2}, {'end': 3510.624, 'text': '107 is a spoon.', 'start': 3508.803, 'duration': 1.821}, {'end': 3513.265, 'text': 'Then finish the statement with a semicolon.', 'start': 3511.324, 'duration': 1.941}, {'end': 3518.507, 'text': "So since we set that default constraint, we don't need to explicitly list the price.", 'start': 3513.945, 'duration': 4.562}, {'end': 3520.567, 'text': 'But we are missing one thing though.', 'start': 3519.227, 'duration': 1.34}, {'end': 3524.829, 'text': "When I execute this statement, the column count doesn't match.", 'start': 3521.388, 'duration': 3.441}, {'end': 3533.439, 'text': "So when we insert into products, we will list what we're inserting explicitly, the product ID and the product name.", 'start': 3525.649, 'duration': 7.79}, {'end': 3541.647, 'text': 'After our table name, within parentheses, type product ID, then product name.', 'start': 3533.899, 'duration': 7.748}, {'end': 3542.948, 'text': 'Now this should work.', 'start': 3542.267, 'duration': 0.681}, {'end': 3546.453, 'text': "Yeah, and here's our products table.", 'start': 3545.253, 'duration': 1.2}, {'end': 3553.376, 'text': 'With our four inserts, all of the prices were set to zero, and we did not need to explicitly state that this time.', 'start': 3546.994, 'duration': 6.382}, {'end': 3557.237, 'text': "If you don't specify a value, you'll use whatever the default is.", 'start': 3554.136, 'duration': 3.101}, {'end': 3559.998, 'text': "Here's another example, I think you'll like this one.", 'start': 3558.017, 'duration': 1.981}, {'end': 3562.239, 'text': "Let's say we have a table of transactions.", 'start': 3560.418, 'duration': 1.821}, {'end': 3570.582, 'text': 'After inserting a new transaction, we will insert a timestamp of when that transaction took place, and that timestamp will be the default.', 'start': 3563.059, 'duration': 7.523}, {'end': 3574.523, 'text': 'Create table transactions.', 'start': 3571.102, 'duration': 3.421}], 'summary': 'Altering table column to set default constraint, inserting new rows, and using default values in sql.', 'duration': 126.507, 'max_score': 3448.016, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso3448016.jpg'}, {'end': 3765.329, 'src': 'embed', 'start': 3741.768, 'weight': 1, 'content': [{'end': 3749.298, 'text': 'The primary key constraint can be applied to a column where each value in that column must both be unique and not null.', 'start': 3741.768, 'duration': 7.53}, {'end': 3752.221, 'text': "It's typically used as a unique identifier.", 'start': 3749.718, 'duration': 2.503}, {'end': 3754.384, 'text': 'For example, I live in the United States.', 'start': 3752.501, 'duration': 1.883}, {'end': 3759.006, 'text': 'Each citizen within the United States has a unique social security number.', 'start': 3754.784, 'duration': 4.222}, {'end': 3765.329, 'text': 'There is a strong possibility that two citizens in the United States share the same first name and last name.', 'start': 3759.386, 'duration': 5.943}], 'summary': 'Primary key ensures unique, non-null values; e.g., unique social security numbers in the united states.', 'duration': 23.561, 'max_score': 3741.768, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso3741768.jpg'}, {'end': 4086.532, 'src': 'embed', 'start': 4061.243, 'weight': 3, 'content': [{'end': 4066.285, 'text': "Hey everybody, in today's topic I'm going to explain the auto increment attribute in MySQL.", 'start': 4061.243, 'duration': 5.042}, {'end': 4070.766, 'text': 'The auto increment attribute can be applied to a column that is set as a key.', 'start': 4066.545, 'duration': 4.221}, {'end': 4075.888, 'text': 'Whenever we insert a new row, our primary key can be populated automatically.', 'start': 4071.166, 'duration': 4.722}, {'end': 4078.929, 'text': 'Then each subsequent row is auto incremented.', 'start': 4076.168, 'duration': 2.761}, {'end': 4083.091, 'text': "I'm going to recreate this table transactions, but first I need to drop it.", 'start': 4079.229, 'duration': 3.862}, {'end': 4086.532, 'text': 'Drop table transactions.', 'start': 4084.231, 'duration': 2.301}], 'summary': 'Explaining auto increment in mysql for primary key columns.', 'duration': 25.289, 'max_score': 4061.243, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso4061243.jpg'}, {'end': 4322.507, 'src': 'heatmap', 'start': 4208.055, 'weight': 0.89, 'content': [{'end': 4216.618, 'text': 'To do that, we would type alter table, the name of the table, auto, increment.', 'start': 4208.055, 'duration': 8.563}, {'end': 4219.899, 'text': "Let's begin at 1000 instead of one.", 'start': 4217.619, 'duration': 2.28}, {'end': 4223.741, 'text': "Then I'm gonna go ahead and drop all the rows.", 'start': 4221.8, 'duration': 1.941}, {'end': 4224.701, 'text': "We'll start fresh.", 'start': 4224.001, 'duration': 0.7}, {'end': 4229.523, 'text': 'Delete from transactions.', 'start': 4226.442, 'duration': 3.081}, {'end': 4234.845, 'text': 'Select all from transactions.', 'start': 4231.144, 'duration': 3.701}, {'end': 4239.326, 'text': "Okay, then let's insert those rows again and see what happens.", 'start': 4236.544, 'duration': 2.782}, {'end': 4245.59, 'text': 'Insert into transactions our values.', 'start': 4240.306, 'duration': 5.284}, {'end': 4249.693, 'text': '499 was the first amount.', 'start': 4248.752, 'duration': 0.941}, {'end': 4253.455, 'text': 'This value is for our amount column.', 'start': 4250.793, 'duration': 2.662}, {'end': 4259.299, 'text': 'Now this transaction ID, it begins at 1000.', 'start': 4255.176, 'duration': 4.123}, {'end': 4260.52, 'text': 'Then it should auto increment.', 'start': 4259.299, 'duration': 1.221}, {'end': 4265.836, 'text': 'The next value was 289.', 'start': 4261.921, 'duration': 3.915}, {'end': 4272.421, 'text': 'Our next transaction ID is 1001, 338, 1002, then 499, 1003.', 'start': 4265.836, 'duration': 6.585}, {'end': 4281.547, 'text': 'All right everybody, that is the auto increment feature.', 'start': 4272.421, 'duration': 9.126}, {'end': 4285.25, 'text': "It's a keyword that can be applied to a column that is set as a key.", 'start': 4282.007, 'duration': 3.243}, {'end': 4290.453, 'text': 'Whenever we insert a new row, our key will be incremented by one for each row that we insert.', 'start': 4285.57, 'duration': 4.883}, {'end': 4293.456, 'text': 'But yeah, that is auto increment in MySQL.', 'start': 4290.714, 'duration': 2.742}, {'end': 4300.409, 'text': "Hey everybody, so in this topic, I'm gonna explain what the foreign key constraint is.", 'start': 4296.206, 'duration': 4.203}, {'end': 4306.833, 'text': 'A foreign key, think of it as a primary key from one table that can be found within a different table.', 'start': 4300.429, 'duration': 6.404}, {'end': 4311.337, 'text': 'Using a foreign key, we can establish a link between two tables.', 'start': 4307.274, 'duration': 4.063}, {'end': 4313.799, 'text': "There's two primary benefits to this.", 'start': 4311.877, 'duration': 1.922}, {'end': 4322.507, 'text': 'In my transactions table, if I were to take a look at the customer ID of who initiated this transaction, I could refer to the customers table,', 'start': 4314.219, 'duration': 8.288}], 'summary': 'Demonstration of using auto increment and foreign key in mysql.', 'duration': 114.452, 'max_score': 4208.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso4208055.jpg'}, {'end': 4325.29, 'src': 'embed', 'start': 4300.429, 'weight': 0, 'content': [{'end': 4306.833, 'text': 'A foreign key, think of it as a primary key from one table that can be found within a different table.', 'start': 4300.429, 'duration': 6.404}, {'end': 4311.337, 'text': 'Using a foreign key, we can establish a link between two tables.', 'start': 4307.274, 'duration': 4.063}, {'end': 4313.799, 'text': "There's two primary benefits to this.", 'start': 4311.877, 'duration': 1.922}, {'end': 4322.507, 'text': 'In my transactions table, if I were to take a look at the customer ID of who initiated this transaction, I could refer to the customers table,', 'start': 4314.219, 'duration': 8.288}, {'end': 4325.29, 'text': 'then find the first and last name of that customer.', 'start': 4322.507, 'duration': 2.783}], 'summary': 'Using foreign keys, we can link tables and access related data, such as customer names from the transactions table.', 'duration': 24.861, 'max_score': 4300.429, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso4300429.jpg'}, {'end': 4865.291, 'src': 'heatmap', 'start': 4750.162, 'weight': 0.702, 'content': [{'end': 4761.809, 'text': 'delete from customers where customer ID equals 3.', 'start': 4750.162, 'duration': 11.647}, {'end': 4762.269, 'text': 'We were right.', 'start': 4761.809, 'duration': 0.46}, {'end': 4763.51, 'text': "It doesn't appear that we can do so.", 'start': 4762.309, 'duration': 1.201}, {'end': 4766.492, 'text': 'Cannot delete or update a parent row.', 'start': 4764.09, 'duration': 2.402}, {'end': 4768.613, 'text': 'A foreign key constraint fails.', 'start': 4767.052, 'duration': 1.561}, {'end': 4772.043, 'text': "So yeah, that's the foreign key constraint.", 'start': 4770.202, 'duration': 1.841}, {'end': 4777.284, 'text': "It's a primary key from one table that's found also within a different table.", 'start': 4772.443, 'duration': 4.841}, {'end': 4782.886, 'text': "But when we're working with that different table, we would refer to that key as a foreign key.", 'start': 4777.725, 'duration': 5.161}, {'end': 4786.348, 'text': "We'll have more practice with this when we reach the topic on joins.", 'start': 4783.146, 'duration': 3.202}, {'end': 4789.929, 'text': 'But yeah, that is the foreign key constraint in MySQL.', 'start': 4786.688, 'duration': 3.241}, {'end': 4794.215, 'text': 'Hey everybody, welcome back again.', 'start': 4792.734, 'duration': 1.481}, {'end': 4797.335, 'text': "In today's video, I'm going to be explaining joins in MySQL.", 'start': 4794.235, 'duration': 3.1}, {'end': 4806.238, 'text': 'A join is a clause that is used to combine rows from two or more tables based on a related column between them, such as a foreign key.', 'start': 4797.736, 'duration': 8.502}, {'end': 4807.559, 'text': "Here's an example.", 'start': 4806.738, 'duration': 0.821}, {'end': 4808.579, 'text': 'I have two tables.', 'start': 4807.779, 'duration': 0.8}, {'end': 4811.681, 'text': 'a table of transactions, and a table of customers.', 'start': 4809.139, 'duration': 2.542}, {'end': 4814.324, 'text': 'Think of these two tables as a Venn diagram.', 'start': 4812.122, 'duration': 2.202}, {'end': 4816.606, 'text': 'Transactions will be the table on the left.', 'start': 4814.624, 'duration': 1.982}, {'end': 4818.788, 'text': 'Customers will be the table on the right.', 'start': 4816.986, 'duration': 1.802}, {'end': 4822.792, 'text': 'Whatever data they have in common is the middle part of our Venn diagram.', 'start': 4819.068, 'duration': 3.724}, {'end': 4826.676, 'text': 'For my demonstration to make more sense, I will need to add a few extra rows.', 'start': 4823.152, 'duration': 3.524}, {'end': 4829.178, 'text': 'Feel free to pause the video if you need to catch up.', 'start': 4827.216, 'duration': 1.962}, {'end': 4832.5, 'text': 'I will insert into transactions a new row.', 'start': 4829.778, 'duration': 2.722}, {'end': 4834.482, 'text': 'The amount is $1.', 'start': 4832.86, 'duration': 1.622}, {'end': 4835.963, 'text': 'The customer ID is null.', 'start': 4834.482, 'duration': 1.481}, {'end': 4840.526, 'text': 'So not all transactions can have a customer ID, that foreign key.', 'start': 4836.403, 'duration': 4.123}, {'end': 4841.828, 'text': "Here's a scenario.", 'start': 4840.947, 'duration': 0.881}, {'end': 4845.63, 'text': 'Suppose that somebody comes in, pays for a soda with cash.', 'start': 4841.968, 'duration': 3.662}, {'end': 4852.316, 'text': "Well, we wouldn't have a customer ID, right? If a customer instead paid with a credit card, we could track who that customer was.", 'start': 4845.951, 'duration': 6.365}, {'end': 4854.317, 'text': 'There may be a customer ID.', 'start': 4852.656, 'duration': 1.661}, {'end': 4861.646, 'text': "Who initiated that credit card charge? I'm going to insert this row, and here's our new transactions table.", 'start': 4854.417, 'duration': 7.229}, {'end': 4865.291, 'text': 'Not all rows have a customer ID.', 'start': 4863.088, 'duration': 2.203}], 'summary': 'Explaining foreign key constraints and joins in mysql.', 'duration': 115.129, 'max_score': 4750.162, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso4750162.jpg'}, {'end': 4822.792, 'src': 'embed', 'start': 4797.736, 'weight': 2, 'content': [{'end': 4806.238, 'text': 'A join is a clause that is used to combine rows from two or more tables based on a related column between them, such as a foreign key.', 'start': 4797.736, 'duration': 8.502}, {'end': 4807.559, 'text': "Here's an example.", 'start': 4806.738, 'duration': 0.821}, {'end': 4808.579, 'text': 'I have two tables.', 'start': 4807.779, 'duration': 0.8}, {'end': 4811.681, 'text': 'a table of transactions, and a table of customers.', 'start': 4809.139, 'duration': 2.542}, {'end': 4814.324, 'text': 'Think of these two tables as a Venn diagram.', 'start': 4812.122, 'duration': 2.202}, {'end': 4816.606, 'text': 'Transactions will be the table on the left.', 'start': 4814.624, 'duration': 1.982}, {'end': 4818.788, 'text': 'Customers will be the table on the right.', 'start': 4816.986, 'duration': 1.802}, {'end': 4822.792, 'text': 'Whatever data they have in common is the middle part of our Venn diagram.', 'start': 4819.068, 'duration': 3.724}], 'summary': 'A join clause combines rows from tables based on related columns, like a venn diagram.', 'duration': 25.056, 'max_score': 4797.736, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso4797736.jpg'}], 'start': 2526.796, 'title': 'Mysql constraints and functions', 'summary': 'Covers unique, not null, and check constraints in mysql, default constraints in sql, mysql auto increment and foreign key constraints, with practical examples and insights into joining tables and using functions.', 'chapters': [{'end': 3242.283, 'start': 2526.796, 'title': 'Mysql constraints explained', 'summary': 'Covers unique, not null, and check constraints in mysql, including examples and syntax, emphasizing the importance and application of each constraint in database management.', 'duration': 715.487, 'highlights': ['The chapter covers unique, not null, and check constraints in MySQL The transcript provides explanations and examples of the unique, not null, and check constraints in MySQL, offering a comprehensive overview of these essential database management tools.', 'The unique constraint ensures that all values in a column are all different The unique constraint in MySQL ensures that all values in a column are distinct, preventing duplicate entries and maintaining data integrity, as demonstrated with examples in the transcript.', "The not null constraint ensures that the value within a column can't be null The not null constraint in MySQL ensures that a column cannot contain null values, providing data validation and enforcing the presence of non-null values, as illustrated in the provided examples.", 'The check constraint is used to limit what values can be placed in a column The check constraint in MySQL restricts the values that can be inserted into a column, offering a method to enforce specific conditions on column values, as explained and exemplified in the chapter.']}, {'end': 3714.529, 'start': 3242.643, 'title': 'Default constraint in sql', 'summary': 'Explains the use of default constraints in sql, including setting default values for columns, altering existing tables to include default constraints, and using default constraints for timestamp values, illustrated through examples of adding free items to a products table and automatically including transaction dates in a transactions table.', 'duration': 471.886, 'highlights': ['Setting default values for columns can simplify the process of inserting new rows, as demonstrated by adding free items to the products table, where default constraint is used to set the price of napkins, straws, forks, and spoons to $0.00 without explicitly specifying the price. Default constraint used to set the price of napkins, straws, forks, and spoons to $0.00', 'The process of altering existing tables to include default constraints is explained, where the alter table command is used to set default constraints for columns, such as setting the default value for the price column to be $0.00 in the products table. Demonstration of altering existing tables to include default constraints', 'Illustration of using default constraints for timestamp values in the transactions table, where the now function is used as the default constraint for the transaction date column to automatically include the date and time of the transaction. Use of default constraints for timestamp values through the now function']}, {'end': 4272.421, 'start': 3716.021, 'title': 'Mysql constraints & auto increment', 'summary': 'Explains the default constraint, primary key constraint, and auto increment attribute in mysql, emphasizing unique identifiers and automatic value population for primary keys and demonstrating their application with specific examples and syntax.', 'duration': 556.4, 'highlights': ['The primary key constraint can be applied to a column where each value in that column must both be unique and not null. The primary key constraint ensures that each value in the specified column is unique and not null, serving as a unique identifier for the table. This constraint can only be applied to one column in a table.', 'The auto increment feature can only be applied to a column that is set as a key, and whenever a new row is inserted, the primary key is populated automatically and each subsequent row is auto incremented. The auto increment attribute, applied to a key column, automatically populates the primary key for new rows and increments it for subsequent rows, simplifying the insertion process for primary key values.', 'The default constraint allows setting a default value for a column, which is automatically included when inserting a row. The default constraint enables the automatic inclusion of a specified default value in a column when inserting a new row, providing convenience in data insertion and ensuring consistency.']}, {'end': 4720.229, 'start': 4272.421, 'title': 'Mysql foreign key constraint', 'summary': 'Explains the concept of foreign key constraints, their benefits, and the process of creating and applying them in mysql, with the example of establishing a link between the customers and transactions table, and setting auto increment for the transactions table.', 'duration': 447.808, 'highlights': ['The foreign key constraint in MySQL allows for the establishment of a link between two tables, providing the benefit of referencing data from one table in another, preventing actions that would break the link. A foreign key in MySQL establishes a link between two tables, allowing for referencing data from one table in another. It prevents actions that would break the link between the tables.', 'The process of creating and applying a foreign key constraint involves specifying the foreign key column, referencing the second table and its primary key column, and possibly giving the foreign key a unique name. To create and apply a foreign key constraint, the process involves specifying the foreign key column, referencing the second table and its primary key column, and optionally giving the foreign key a unique name.', 'Setting auto increment for a column in MySQL allows for the automatic incrementation of the key for each new row inserted, with a demonstration of setting auto increment to 1,000 for the transactions table and inserting new rows with specified values. In MySQL, setting auto increment for a column enables the automatic incrementation of the key for each new row inserted. In the example, auto increment is set to 1,000 for the transactions table, and new rows are inserted with specified values.']}, {'end': 5223.533, 'start': 4721.198, 'title': 'Mysql joins & functions', 'summary': 'Explains foreign key constraints, inner joins, left joins, and right joins in mysql, demonstrating how to link tables based on a related column and showcasing the count, max, and min functions, with examples and practical insights.', 'duration': 502.335, 'highlights': ['A join is a clause used to combine rows from two or more tables based on a related column, such as a foreign key, like customer ID. Explains the fundamental concept of a join and its purpose in linking tables based on a related column.', 'Demonstrates creating an inner join between the transactions and customers tables based on their matching customer IDs, excluding rows without a customer ID. Illustrates the practical implementation of an inner join and its impact on selecting matching rows from two tables.', 'Explains the functionality of left join, displaying everything from the left table and pulling in relevant data from the right table if a matching customer ID exists. Provides insight into the functionality of left join and its impact on displaying data from the left table while incorporating relevant data from the right table.', 'Discusses the operation of right join, displaying the entire table on the right and pulling in any matching rows from the left table. Provides an explanation of right join and its impact on displaying all data from the right table while including matching rows from the left table.', 'Introduces the count function to calculate the number of rows within a column, showcasing its usage to count the number of transactions within the amount column. Demonstrates the practical application of the count function to calculate the number of transactions within a specific column.', 'Illustrates the usage of the max function to find the maximum value within a column and assigns an alias to the result for a clearer representation. Provides a practical example of using the max function to find the maximum value within a column and assigning an alias for improved clarity.', 'Provides an example of using the min function to find the minimum value within a column and assigning an alias to the result. Demonstrates the practical usage of the min function to find the minimum value within a column and assigning an alias for a better representation.']}], 'duration': 2696.737, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso2526796.jpg', 'highlights': ['The foreign key constraint in MySQL allows for the establishment of a link between two tables, providing the benefit of referencing data from one table in another, preventing actions that would break the link.', 'The primary key constraint can be applied to a column where each value in that column must both be unique and not null. The primary key constraint ensures that each value in the specified column is unique and not null, serving as a unique identifier for the table. This constraint can only be applied to one column in a table.', 'A join is a clause used to combine rows from two or more tables based on a related column, such as a foreign key, like customer ID. Explains the fundamental concept of a join and its purpose in linking tables based on a related column.', 'The auto increment feature can only be applied to a column that is set as a key, and whenever a new row is inserted, the primary key is populated automatically and each subsequent row is auto incremented. The auto increment attribute, applied to a key column, automatically populates the primary key for new rows and increments it for subsequent rows, simplifying the insertion process for primary key values.', 'Setting auto increment for a column in MySQL allows for the automatic incrementation of the key for each new row inserted, with a demonstration of setting auto increment to 1,000 for the transactions table and inserting new rows with specified values. In MySQL, setting auto increment for a column enables the automatic incrementation of the key for each new row inserted. In the example, auto increment is set to 1,000 for the transactions table, and new rows are inserted with specified values.']}, {'end': 5923.876, 'segs': [{'end': 5293.667, 'src': 'embed', 'start': 5223.533, 'weight': 0, 'content': [{'end': 5226.175, 'text': 'You could do average, which is AVG.', 'start': 5223.533, 'duration': 2.642}, {'end': 5230.8, 'text': 'As average, the average order is $3.45.', 'start': 5227.196, 'duration': 3.604}, {'end': 5233.883, 'text': 'We can find the sum of a column by using the sum function.', 'start': 5230.8, 'duration': 3.083}, {'end': 5234.243, 'text': 'As sum.', 'start': 5233.923, 'duration': 0.32}, {'end': 5243.322, 'text': 'The sum of all of our transactions was $17.25.', 'start': 5240.441, 'duration': 2.881}, {'end': 5249.646, 'text': "For this next example, we're going to concatenate the first and last name of our employees.", 'start': 5243.322, 'duration': 6.324}, {'end': 5254.048, 'text': 'Select all from employees.', 'start': 5251.066, 'duration': 2.982}, {'end': 5258.87, 'text': 'We have two columns, one for a first name, the other for a last name.', 'start': 5255.829, 'duration': 3.041}, {'end': 5266.194, 'text': "We're going to combine these two columns together with the concat function, then create a new column named full name.", 'start': 5259.351, 'duration': 6.843}, {'end': 5267.575, 'text': "Here's how to do that.", 'start': 5266.854, 'duration': 0.721}, {'end': 5282.584, 'text': "select concat first name, last name, then i'll add an alias as full name.", 'start': 5270.16, 'duration': 12.424}, {'end': 5284.424, 'text': "here's what we have so far.", 'start': 5282.584, 'duration': 1.84}, {'end': 5288.106, 'text': 'so we are concatenating the first and last names of our employees.', 'start': 5284.424, 'duration': 3.682}, {'end': 5293.667, 'text': 'however, we should separate each name with a space within our concat function.', 'start': 5288.106, 'duration': 5.561}], 'summary': 'Using sql functions to calculate average order, total sum, and concatenate employee names.', 'duration': 70.134, 'max_score': 5223.533, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso5223533.jpg'}, {'end': 5347.961, 'src': 'embed', 'start': 5315.598, 'weight': 3, 'content': [{'end': 5317.839, 'text': 'But yeah, those are functions in MySQL.', 'start': 5315.598, 'duration': 2.241}, {'end': 5322.046, 'text': 'Why hello again, everybody.', 'start': 5320.865, 'duration': 1.181}, {'end': 5323.847, 'text': "Guess who? It's me again.", 'start': 5322.406, 'duration': 1.441}, {'end': 5327.949, 'text': 'Today I will be explaining logical operators in MySQL.', 'start': 5323.867, 'duration': 4.082}, {'end': 5333.193, 'text': 'I would think of them as keywords that are used to combine more than one condition.', 'start': 5328.33, 'duration': 4.863}, {'end': 5338.196, 'text': 'For my examples to make more sense, I will add one more column, a job column.', 'start': 5333.453, 'duration': 4.743}, {'end': 5340.797, 'text': "So let's alter our table of employees.", 'start': 5338.756, 'duration': 2.041}, {'end': 5344.539, 'text': 'alter table employees.', 'start': 5341.418, 'duration': 3.121}, {'end': 5347.961, 'text': 'add column job.', 'start': 5344.539, 'duration': 3.422}], 'summary': 'Explaining logical operators in mysql with examples and altering table', 'duration': 32.363, 'max_score': 5315.598, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso5315598.jpg'}, {'end': 5548.551, 'src': 'embed', 'start': 5514.911, 'weight': 4, 'content': [{'end': 5516.951, 'text': "He's not a cook, but he is a cashier.", 'start': 5514.911, 'duration': 2.04}, {'end': 5522.013, 'text': 'With the OR logical operator, only one condition needs to be true.', 'start': 5517.751, 'duration': 4.262}, {'end': 5526.754, 'text': 'Whereas with the AND logical operator, both conditions must be true.', 'start': 5522.633, 'duration': 4.121}, {'end': 5531.396, 'text': "So that's the main difference between the AND as well as OR logical operators.", 'start': 5527.454, 'duration': 3.942}, {'end': 5535.777, 'text': 'With the NOT logical operator, NOT is a little different.', 'start': 5532.296, 'duration': 3.481}, {'end': 5538.538, 'text': 'NOT basically reverses anything you say.', 'start': 5536.297, 'duration': 2.241}, {'end': 5542.159, 'text': "Let's find any employees that are NOT a manager.", 'start': 5539.118, 'duration': 3.041}, {'end': 5548.551, 'text': 'where not job equals manager.', 'start': 5543.447, 'duration': 5.104}], 'summary': 'Logical operators include and, or, and not, with not reversing conditions.', 'duration': 33.64, 'max_score': 5514.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso5514911.jpg'}, {'end': 5662.59, 'src': 'embed', 'start': 5595.677, 'weight': 6, 'content': [{'end': 5603.62, 'text': "It's very similar to the and logical operator, except people prefer to use the between logical operator when working with the same column,", 'start': 5595.677, 'duration': 7.943}, {'end': 5604.86, 'text': 'just because of its readability.', 'start': 5603.62, 'duration': 1.24}, {'end': 5611.723, 'text': "Let's find any employees where the hire date is between January 4th and January 7th.", 'start': 5605.541, 'duration': 6.182}, {'end': 5623.149, 'text': 'Where hire date between two values, 2023, 01, 04, and 2023-01-07.', 'start': 5611.943, 'duration': 11.206}, {'end': 5631.733, 'text': 'So we have Spongebob, Patrick, Sandy, Sheldon.', 'start': 5623.709, 'duration': 8.024}, {'end': 5634.894, 'text': "There's also the in logical operator.", 'start': 5632.553, 'duration': 2.341}, {'end': 5637.696, 'text': 'We can find any values that are within a set.', 'start': 5635.194, 'duration': 2.502}, {'end': 5649.461, 'text': "Let's find where job is in cook, cashier, or a janitor.", 'start': 5638.336, 'duration': 11.125}, {'end': 5655.362, 'text': 'Yep, we have a cashier, a cook, a cook, and a janitor.', 'start': 5652.079, 'duration': 3.283}, {'end': 5659.927, 'text': 'There is more than one way to write a query where you need to match more than one condition.', 'start': 5656.143, 'duration': 3.784}, {'end': 5662.59, 'text': 'Really, the best way depends on your dataset.', 'start': 5660.347, 'duration': 2.243}], 'summary': 'Using between and in logical operators to filter data, showing multiple ways to write queries.', 'duration': 66.913, 'max_score': 5595.677, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso5595677.jpg'}], 'start': 5223.533, 'title': 'Sql functions and logical operators in mysql', 'summary': 'Covers basic sql functions like avg, sum, and concat, with quantifiable data, and explains logical operators such as and, or, not, between, and in in mysql, with practical examples.', 'chapters': [{'end': 5315.238, 'start': 5223.533, 'title': 'Sql functions', 'summary': 'Covers basic sql functions including avg for average order value of $3.45, sum function indicating a total transaction of $17.25, and concat function for combining first and last names of employees into a new column named full name.', 'duration': 91.705, 'highlights': ['The sum function calculated the total transaction amount to be $17.25.', 'The average order value using the AVG function was $3.45.', 'The concat function combined the first and last names of employees into a new column named full name, demonstrating how to use aliases in SQL.']}, {'end': 5923.876, 'start': 5315.598, 'title': 'Logical operators in mysql', 'summary': 'Explains logical operators in mysql, detailing the usage of logical operators such as and, or, not, between, and in to combine and check multiple conditions in sql queries, with examples and practical applications.', 'duration': 608.278, 'highlights': ['Logical operators such as AND, OR, and NOT are used to combine more than one condition in SQL queries, allowing for precise data retrieval based on multiple criteria. The chapter explains how logical operators such as AND, OR, and NOT are used to combine more than one condition in SQL queries, enabling precise data retrieval based on multiple criteria.', 'The AND logical operator is used to find results that match multiple criteria, requiring all conditions to be true, while the OR operator returns results if at least one condition is true. The AND logical operator ensures that all specified conditions must be true, while the OR operator returns results if at least one condition is true, providing flexibility in query conditions.', 'The NOT logical operator reverses the specified condition, excluding data that meets the defined criteria, and it can be combined with other logical operators for more specific data retrieval. The NOT logical operator reverses the specified condition, excluding data that meets the defined criteria, and it can be combined with other logical operators for more specific data retrieval.', 'The BETWEEN logical operator is used to determine if a value lies within a specified range, providing a convenient way to filter data based on a continuous range of values. The BETWEEN logical operator is used to determine if a value lies within a specified range, providing a convenient way to filter data based on a continuous range of values.', 'The IN logical operator is utilized to find values that are present within a specified set, offering a flexible approach for querying data based on multiple specified values. The IN logical operator is utilized to find values that are present within a specified set, offering a flexible approach for querying data based on multiple specified values.']}], 'duration': 700.343, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso5223533.jpg', 'highlights': ['The sum function calculated the total transaction amount to be $17.25.', 'The average order value using the AVG function was $3.45.', 'The concat function combined the first and last names of employees into a new column named full name, demonstrating how to use aliases in SQL.', 'Logical operators such as AND, OR, and NOT are used to combine more than one condition in SQL queries, allowing for precise data retrieval based on multiple criteria.', 'The AND logical operator is used to find results that match multiple criteria, requiring all conditions to be true, while the OR operator returns results if at least one condition is true.', 'The NOT logical operator reverses the specified condition, excluding data that meets the defined criteria, and it can be combined with other logical operators for more specific data retrieval.', 'The BETWEEN logical operator is used to determine if a value lies within a specified range, providing a convenient way to filter data based on a continuous range of values.', 'The IN logical operator is utilized to find values that are present within a specified set, offering a flexible approach for querying data based on multiple specified values.']}, {'end': 8091.119, 'segs': [{'end': 6274.612, 'src': 'heatmap', 'start': 6151.474, 'weight': 0, 'content': [{'end': 6156.836, 'text': "Let's order by last name, then limit one.", 'start': 6151.474, 'duration': 5.362}, {'end': 6160.437, 'text': 'That would return Bubble Bass.', 'start': 6158.397, 'duration': 2.04}, {'end': 6161.998, 'text': "It's an alphabetical order.", 'start': 6161.038, 'duration': 0.96}, {'end': 6166.42, 'text': 'Or we could do last name descending limit one.', 'start': 6162.818, 'duration': 3.602}, {'end': 6170.958, 'text': 'Poppy Puff has the name in least alphabetical order, I guess.', 'start': 6167.537, 'duration': 3.421}, {'end': 6175.458, 'text': 'Basically speaking, limit limits the number of records that are returned.', 'start': 6171.618, 'duration': 3.84}, {'end': 6181.379, 'text': "It's very useful in combination with the order by clause, which we learned about in the previous topic.", 'start': 6176.058, 'duration': 5.321}, {'end': 6184.4, 'text': 'Now with the limit clause, you can add an offset.', 'start': 6181.999, 'duration': 2.401}, {'end': 6188.341, 'text': 'Limit one one.', 'start': 6185.7, 'duration': 2.641}, {'end': 6190.641, 'text': 'The first number is the offset.', 'start': 6189.141, 'duration': 1.5}, {'end': 6193.681, 'text': 'Limit to one record after the first.', 'start': 6191.361, 'duration': 2.32}, {'end': 6196.702, 'text': 'That should technically return Larry Lobster.', 'start': 6194.382, 'duration': 2.32}, {'end': 6198.447, 'text': 'Yep, Larry.', 'start': 6197.847, 'duration': 0.6}, {'end': 6204.509, 'text': 'So limit two would add an offset of two, which returns Bubble Bass.', 'start': 6199.247, 'duration': 5.262}, {'end': 6208.33, 'text': 'Limit three would return Poppy Puff.', 'start': 6205.929, 'duration': 2.401}, {'end': 6215.351, 'text': 'Using an offset would be very helpful when working with a large data set if you need to display your records on different pages.', 'start': 6209.07, 'duration': 6.281}, {'end': 6219.552, 'text': 'Suppose that our data set is maybe 100 customers.', 'start': 6215.872, 'duration': 3.68}, {'end': 6222.373, 'text': 'I need to display 10 customers per page.', 'start': 6220.013, 'duration': 2.36}, {'end': 6226.92, 'text': 'So the first 10 would be just limit 10.', 'start': 6222.833, 'duration': 4.087}, {'end': 6230.021, 'text': "But you know, my data set's really small, so it's not going to be apparent.", 'start': 6226.92, 'duration': 3.101}, {'end': 6235.122, 'text': 'If I need the next set of 10 customers, I can add an offset of 10.', 'start': 6230.421, 'duration': 4.701}, {'end': 6237.083, 'text': "Although I don't have that many customers to begin with.", 'start': 6235.122, 'duration': 1.961}, {'end': 6246.886, 'text': 'The next set of 10 customers would be an offset of 20, then display 10, you know, then 30, 40, so on and so forth.', 'start': 6237.443, 'duration': 9.443}, {'end': 6248.847, 'text': "so yeah, that's the limit clause.", 'start': 6246.886, 'duration': 1.961}, {'end': 6253.25, 'text': 'you will limit the results that are queried type, limit, then some number.', 'start': 6248.847, 'duration': 4.403}, {'end': 6254.81, 'text': 'you could add an offset.', 'start': 6253.25, 'duration': 1.56}, {'end': 6258.572, 'text': 'so display some number of records after an offset.', 'start': 6254.81, 'duration': 3.762}, {'end': 6261.274, 'text': 'but yeah, that is the limit clause in mysql.', 'start': 6258.572, 'duration': 2.702}, {'end': 6265.267, 'text': 'Hey, welcome back.', 'start': 6264.226, 'duration': 1.041}, {'end': 6267.968, 'text': 'Today I will be explaining the union operator.', 'start': 6265.407, 'duration': 2.561}, {'end': 6272.331, 'text': 'The union operator combines the results of two or more select statements.', 'start': 6268.168, 'duration': 4.163}, {'end': 6274.612, 'text': 'I have created two additional tables.', 'start': 6272.751, 'duration': 1.861}], 'summary': 'Explains limit clause with examples and its use with offset. also introduces the union operator in mysql.', 'duration': 25.765, 'max_score': 6151.474, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso6151474.jpg'}, {'end': 6651.263, 'src': 'embed', 'start': 6626.557, 'weight': 1, 'content': [{'end': 6634.883, 'text': "I'm going to use a self join to replace our referral ID column with the first and last name of the customer that referred one of these people.", 'start': 6626.557, 'duration': 8.326}, {'end': 6637.846, 'text': "So I'm going to select all.", 'start': 6635.624, 'duration': 2.222}, {'end': 6647.678, 'text': "Now, when using a self join, We'll take from our table customers, then interjoin customers.", 'start': 6638.686, 'duration': 8.992}, {'end': 6651.263, 'text': "Basically, we're just joining another copy of a table to itself.", 'start': 6647.999, 'duration': 3.264}], 'summary': 'Using self join to replace referral id with customer names.', 'duration': 24.706, 'max_score': 6626.557, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso6626557.jpg'}, {'end': 7239.314, 'src': 'heatmap', 'start': 7125.498, 'weight': 0.767, 'content': [{'end': 7132.46, 'text': "They're made up of fields and columns from one or more real tables, and they can be interacted with as if they were a real table.", 'start': 7125.498, 'duration': 6.962}, {'end': 7133.801, 'text': "So here's an example.", 'start': 7132.861, 'duration': 0.94}, {'end': 7137.242, 'text': "I'm going to select all from my employees table.", 'start': 7134.461, 'duration': 2.781}, {'end': 7146.787, 'text': 'Our boss, Mr. Krabs, he would like us to create an employee attendance sheet made up of just the first and last names of all the employees.', 'start': 7139.963, 'duration': 6.824}, {'end': 7153.091, 'text': 'Well, I could create a view that is made from the first and last name columns of the employees table.', 'start': 7147.288, 'duration': 5.803}, {'end': 7157.014, 'text': "Now, a view isn't a real table, but it can behave as if it were.", 'start': 7153.492, 'duration': 3.522}, {'end': 7164.759, 'text': "I may want to create a view of the employee first and last names instead of another table because we try not to repeat data if we don't have to.", 'start': 7157.394, 'duration': 7.365}, {'end': 7169.342, 'text': 'If I had two tables, one of employees, And another of employee attendance.', 'start': 7165.179, 'duration': 4.163}, {'end': 7172.725, 'text': 'If I need to remove an employee, I would need to do so in two places.', 'start': 7169.743, 'duration': 2.982}, {'end': 7174.927, 'text': "With the view, it's always up to date.", 'start': 7173.246, 'duration': 1.681}, {'end': 7179.211, 'text': 'Any changes to one or more of these real tables will also update the view.', 'start': 7175.347, 'duration': 3.864}, {'end': 7182.894, 'text': "So let's create a view of the first and last name of our employees table.", 'start': 7179.631, 'duration': 3.263}, {'end': 7187.958, 'text': 'To create a view, you would type create view, then the name of the view.', 'start': 7183.554, 'duration': 4.404}, {'end': 7191.681, 'text': "Let's say employee attendance view.", 'start': 7188.158, 'duration': 3.523}, {'end': 7195.04, 'text': "Then as, I'm gonna zoom in a little bit.", 'start': 7192.318, 'duration': 2.722}, {'end': 7204.485, 'text': "What would we like to select? Let's select first name, last name, from a real table.", 'start': 7195.9, 'duration': 8.585}, {'end': 7207.267, 'text': "Let's say from employees.", 'start': 7204.745, 'duration': 2.522}, {'end': 7211.429, 'text': 'So everything was successful.', 'start': 7210.068, 'duration': 1.361}, {'end': 7213.811, 'text': "Let's refresh our schemas window.", 'start': 7212.25, 'duration': 1.561}, {'end': 7218.981, 'text': 'Underneath the Views category, we have a new view, Employee Attendance.', 'start': 7214.938, 'duration': 4.043}, {'end': 7224.384, 'text': "I'm going to select all from that view name.", 'start': 7219.721, 'duration': 4.663}, {'end': 7227.426, 'text': 'Employee Attendance.', 'start': 7225.265, 'duration': 2.161}, {'end': 7230.048, 'text': "And let's see what we got.", 'start': 7229.127, 'duration': 0.921}, {'end': 7231.849, 'text': "Yeah, here's our attendance sheet.", 'start': 7230.388, 'duration': 1.461}, {'end': 7235.671, 'text': 'We have the first and last name columns from the Employees table.', 'start': 7232.069, 'duration': 3.602}, {'end': 7239.314, 'text': 'This view can be interacted with as if it were a real table.', 'start': 7236.172, 'duration': 3.142}], 'summary': 'Creating a view of employee first and last names from the employees table to avoid data repetition and ensure automatic updates.', 'duration': 113.816, 'max_score': 7125.498, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso7125498.jpg'}, {'end': 7164.759, 'src': 'embed', 'start': 7132.861, 'weight': 2, 'content': [{'end': 7133.801, 'text': "So here's an example.", 'start': 7132.861, 'duration': 0.94}, {'end': 7137.242, 'text': "I'm going to select all from my employees table.", 'start': 7134.461, 'duration': 2.781}, {'end': 7146.787, 'text': 'Our boss, Mr. Krabs, he would like us to create an employee attendance sheet made up of just the first and last names of all the employees.', 'start': 7139.963, 'duration': 6.824}, {'end': 7153.091, 'text': 'Well, I could create a view that is made from the first and last name columns of the employees table.', 'start': 7147.288, 'duration': 5.803}, {'end': 7157.014, 'text': "Now, a view isn't a real table, but it can behave as if it were.", 'start': 7153.492, 'duration': 3.522}, {'end': 7164.759, 'text': "I may want to create a view of the employee first and last names instead of another table because we try not to repeat data if we don't have to.", 'start': 7157.394, 'duration': 7.365}], 'summary': 'Creating a view for an employee attendance sheet with first and last names from the employees table.', 'duration': 31.898, 'max_score': 7132.861, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso7132861.jpg'}, {'end': 7672.937, 'src': 'heatmap', 'start': 7559.882, 'weight': 0.726, 'content': [{'end': 7562.183, 'text': 'Only a new customer comes in every once in a while.', 'start': 7559.882, 'duration': 2.301}, {'end': 7565.784, 'text': "Let's reduce the time it takes to search for a customer.", 'start': 7562.823, 'duration': 2.961}, {'end': 7573.226, 'text': 'With creating an index, how exactly do we want to find the customer? Believe it or not, we do have an index with our customer ID already.', 'start': 7566.384, 'duration': 6.842}, {'end': 7577.127, 'text': 'A customer may give you their last name and or first name.', 'start': 7573.686, 'duration': 3.441}, {'end': 7578.788, 'text': "Let's create an index for these.", 'start': 7577.608, 'duration': 1.18}, {'end': 7580.648, 'text': "We'll start with last name though.", 'start': 7579.428, 'duration': 1.22}, {'end': 7583.269, 'text': 'To show the current indexes of a table.', 'start': 7581.109, 'duration': 2.16}, {'end': 7590.512, 'text': 'you would type show indexes from the name of the table.', 'start': 7583.269, 'duration': 7.243}, {'end': 7593.713, 'text': 'here are the current indexes for our customers table.', 'start': 7590.512, 'duration': 3.201}, {'end': 7596.574, 'text': "we do have one already and that's for our customer id.", 'start': 7593.713, 'duration': 2.861}, {'end': 7598.334, 'text': 'that is the primary key.', 'start': 7596.574, 'duration': 1.76}, {'end': 7605.417, 'text': 'we can search for a customer by their customer id fairly quickly, but not so much by their last name or their first name.', 'start': 7598.334, 'duration': 7.083}, {'end': 7608.938, 'text': "let's apply an index to those columns to create an index.", 'start': 7605.417, 'duration': 3.521}, {'end': 7613.766, 'text': 'you would type create index, Then a name.', 'start': 7608.938, 'duration': 4.828}, {'end': 7616.667, 'text': 'I will apply an index to our last name column.', 'start': 7614.406, 'duration': 2.261}, {'end': 7621.308, 'text': 'I will name this index last name IDX meaning index.', 'start': 7616.847, 'duration': 4.461}, {'end': 7626.109, 'text': 'On the table, customers in this case.', 'start': 7622.888, 'duration': 3.221}, {'end': 7628.029, 'text': 'Then list a column.', 'start': 7627.109, 'duration': 0.92}, {'end': 7630.99, 'text': 'I will apply an index to our last name.', 'start': 7628.849, 'duration': 2.141}, {'end': 7634.651, 'text': "We will be doing a lot of searching by a customer's last name.", 'start': 7631.87, 'duration': 2.781}, {'end': 7636.331, 'text': 'So I might as well apply an index.', 'start': 7634.871, 'duration': 1.46}, {'end': 7638.991, 'text': "So let's execute the statement.", 'start': 7637.211, 'duration': 1.78}, {'end': 7641.072, 'text': "Let's show our indexes again.", 'start': 7639.792, 'duration': 1.28}, {'end': 7645.997, 'text': 'Show indexes from customers.', 'start': 7642.193, 'duration': 3.804}, {'end': 7650.621, 'text': 'And here is our new index, last name index.', 'start': 7647.638, 'duration': 2.983}, {'end': 7653.564, 'text': "It's applied to our column last name.", 'start': 7651.422, 'duration': 2.142}, {'end': 7658.108, 'text': 'If I were to search for a customer by last name, that process will be sped up now.', 'start': 7654.244, 'duration': 3.864}, {'end': 7666.836, 'text': 'Select all from customers where last name is equal to puff.', 'start': 7659.649, 'duration': 7.187}, {'end': 7672.937, 'text': 'So my dataset is already very small to begin with.', 'start': 7670.253, 'duration': 2.684}], 'summary': 'Creating an index for last name in the customer table speeds up search process.', 'duration': 113.055, 'max_score': 7559.882, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso7559882.jpg'}, {'end': 7763.079, 'src': 'embed', 'start': 7730.683, 'weight': 3, 'content': [{'end': 7740.352, 'text': "mysql has what's known as a leftmost prefix with indexes, so we will search by last name, then first name.", 'start': 7730.683, 'duration': 9.669}, {'end': 7745.283, 'text': "Let's execute the statement.", 'start': 7744.161, 'duration': 1.122}, {'end': 7746.586, 'text': 'Show our indexes.', 'start': 7745.644, 'duration': 0.942}, {'end': 7750.493, 'text': 'Show indexes from customers.', 'start': 7747.427, 'duration': 3.066}, {'end': 7754.521, 'text': "Here's our last name, first name index.", 'start': 7752.817, 'duration': 1.704}, {'end': 7756.154, 'text': "There's a sequence.", 'start': 7755.353, 'duration': 0.801}, {'end': 7759.516, 'text': "We're telling MySQL to search by a last name.", 'start': 7756.734, 'duration': 2.782}, {'end': 7763.079, 'text': "However, if there's also a first name, include that too.", 'start': 7759.916, 'duration': 3.163}], 'summary': 'Using leftmost prefix indexes in mysql for searching by last name, then first name.', 'duration': 32.396, 'max_score': 7730.683, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso7730683.jpg'}, {'end': 7898.924, 'src': 'embed', 'start': 7868.164, 'weight': 4, 'content': [{'end': 7870.105, 'text': 'Today I need to explain subqueries.', 'start': 7868.164, 'duration': 1.941}, {'end': 7873.707, 'text': "They can be pretty complicated, but I'll try my best to explain it simply.", 'start': 7870.285, 'duration': 3.422}, {'end': 7877.529, 'text': 'A subquery is just a query within another query.', 'start': 7873.927, 'duration': 3.602}, {'end': 7880.691, 'text': 'You write a query and close it within a set of parentheses.', 'start': 7877.89, 'duration': 2.801}, {'end': 7886.695, 'text': 'Whatever value or values are returned from the subquery, you would use within an outer query.', 'start': 7881.051, 'duration': 5.644}, {'end': 7888.416, 'text': "I'll give you a few demonstrations.", 'start': 7886.995, 'duration': 1.421}, {'end': 7890.677, 'text': 'I have an employees table.', 'start': 7889.716, 'duration': 0.961}, {'end': 7898.924, 'text': "Our manager, Mr. Krabs, he needs us to compare every employee's hourly pay with the average hourly pay of our employees table.", 'start': 7891.057, 'duration': 7.867}], 'summary': 'Explaining subqueries, using employee hourly pay comparison with average pay.', 'duration': 30.76, 'max_score': 7868.164, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso7868164.jpg'}], 'start': 5924.096, 'title': 'Mysql database functions', 'summary': 'Delves into mysql database functions including order by, limit clause, union operator, self joins, referral and reporting, views, indexes, and subqueries, providing practical applications and examples in a mysql environment.', 'chapters': [{'end': 6274.612, 'start': 5924.096, 'title': 'Mysql order by & limit clause', 'summary': 'Explains the order by clause in mysql, which sorts query results in ascending or descending order based on specified columns, and the limit clause, used to restrict the number of records queried, allowing pagination and combining it with the order by clause. it also introduces the union operator for combining results of multiple select statements.', 'duration': 350.516, 'highlights': ["The order by clause allows sorting of query results in ascending or descending order based on specified columns. It enables alphabetical or reverse alphabetical ordering of employees' names and allows ordering by multiple columns, demonstrating the use of 'ASC' and 'DESC' for specifying the order.", 'The limit clause restricts the number of records queried, allowing pagination and combining with the order by clause. It demonstrates limiting the display of customers and combining the limit clause with the order by clause to achieve alphabetical or reverse alphabetical ordering.', 'Introduction of the union operator for combining results of two or more select statements. It mentions the function of the union operator in combining results of select statements from multiple tables.']}, {'end': 6536.159, 'start': 6274.772, 'title': 'Union operator and self joins', 'summary': 'Covers the use of the union operator to combine results of select statements, ensuring the same number of columns, and explains self joins for comparing rows within the same table and displaying hierarchical data.', 'duration': 261.387, 'highlights': ['The union operator combines the results of two or more select statements with the same number of columns. The union operator is used to print a list of all income versus expenses, joining select statements with the union operator.', "The importance of having the same number of columns in select statements for using the union operator is emphasized. It is highlighted that a union won't work if the select statements have a different number of columns, and using distinct columns can resolve this issue.", 'The difference between union and union all is explained, demonstrating how union all includes duplicates. The difference between union and union all is demonstrated by inserting and deleting a value in the customers table to show the inclusion and exclusion of duplicates.', 'The concept of self joins is introduced as a method for comparing rows of the same table and displaying hierarchical data. Self joins are defined as any type of join where another copy of a table is joined to itself, and they are used to compare rows of the same table and display hierarchical data.']}, {'end': 7088.641, 'start': 6536.579, 'title': 'Self join for referral and reporting', 'summary': 'Explains the process of implementing a self join in mysql to track customer referrals and employee reporting relationships, including altering tables, populating columns, using aliases, and joining tables to display relevant information.', 'duration': 552.062, 'highlights': ['The process of creating a referral system within a customer table, including setting referral IDs, updating customer information, and using a self join to replace the referral ID with customer names. Creation of referral system, setting referral IDs, updating customer information', 'The use of aliases in a self join to link tables, including assigning nicknames to tables and stitching them together with a join. Utilizing aliases in self join, linking tables with aliases', 'Conducting a self join to display employee reporting relationships, including setting supervisor IDs, using aliases, and displaying the hierarchy of reporting relationships. Displaying employee reporting relationships, setting supervisor IDs, hierarchy of reporting relationships']}, {'end': 7451.204, 'start': 7089.001, 'title': 'Understanding views in databases', 'summary': 'Explains the concept of views in databases, detailing their creation, usage, and benefits such as real-time updates and reduction of data redundancy, using examples from a mysql environment.', 'duration': 362.203, 'highlights': ['Views are virtual tables made up of fields and columns from one or more real tables. Views in databases are virtual tables composed of fields and columns from real tables, enabling interaction as if they were real tables.', 'The creation and usage of views in MySQL, such as creating a view of employee attendance and customer emails. The process of creating and using views in MySQL is demonstrated through examples, including creating views for employee attendance and customer emails.', 'Benefits of using views, including real-time updates when the underlying real tables are modified and reduction of data redundancy. Views offer benefits such as automatic updates when real tables are modified and the avoidance of data redundancy, resulting in efficient data management.']}, {'end': 8091.119, 'start': 7451.484, 'title': 'Mysql views, indexes & subqueries', 'summary': 'Covers the benefits of views in mysql, the purpose and impact of indexes on search and update operations, and the usage of subqueries to compare and filter data based on specified conditions.', 'duration': 639.635, 'highlights': ["Indexes are used to speed up searching in a specific column, though updating takes longer, and their effectiveness depends on the table's usage. Applying an index to a column in MySQL can speed up the process of selecting or searching for values, but it can significantly increase the time taken for updating the table. The effectiveness of indexes depends on the table's usage, making it essential to consider the trade-offs between search speed and update time.", 'Creating multi-column indexes can optimize searches for combinations of columns, enhancing the search process based on specific sequences. Using multi-column indexes in MySQL allows for optimized searches based on sequences, enabling efficient retrieval of data based on combinations of columns. The order of columns in the index is crucial, as MySQL utilizes a leftmost prefix with indexes to optimize search operations.', 'Subqueries enable the comparison and filtering of data based on specified conditions, providing a flexible and powerful tool for complex data analysis. Subqueries in MySQL enable the comparison and filtering of data based on specified conditions, allowing for complex data analysis and providing a flexible tool for retrieving and manipulating information within a database. They can be used to compare values, calculate averages, and filter data based on specific criteria.', "Using subqueries, one can compare every employee's hourly pay with the average hourly pay, and filter records based on conditions like hourly pay greater than the average. Subqueries in MySQL enable the comparison of individual records with aggregate values, such as comparing every employee's hourly pay with the average hourly pay. Additionally, subqueries can be used to filter records based on specific conditions, as demonstrated by filtering employees with hourly pay greater than the calculated average."]}], 'duration': 2167.023, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso5924096.jpg', 'highlights': ['Delves into mysql database functions including order by, limit clause, union operator, self joins, referral and reporting, views, indexes, and subqueries, providing practical applications and examples in a mysql environment.', 'The process of creating a referral system within a customer table, including setting referral IDs, updating customer information, and using a self join to replace the referral ID with customer names.', 'The creation and usage of views in MySQL, such as creating a view of employee attendance and customer emails.', 'Using multi-column indexes in MySQL allows for optimized searches based on sequences, enabling efficient retrieval of data based on combinations of columns.', 'Subqueries in MySQL enable the comparison and filtering of data based on specified conditions, allowing for complex data analysis and providing a flexible tool for retrieving and manipulating information within a database.']}, {'end': 9718.968, 'segs': [{'end': 8233.779, 'src': 'embed', 'start': 8206.974, 'weight': 3, 'content': [{'end': 8215, 'text': 'Select the first name and last name from customers where the customer ID is one of these values, one, two, or three.', 'start': 8206.974, 'duration': 8.026}, {'end': 8216.761, 'text': 'You could do the inverse too.', 'start': 8215.74, 'duration': 1.021}, {'end': 8225.107, 'text': 'Find the first name and last name of every customer where the customer ID is not in the subquery.', 'start': 8217.38, 'duration': 7.727}, {'end': 8230.735, 'text': 'This query would return every customer that has never placed an order.', 'start': 8226.67, 'duration': 4.065}, {'end': 8233.779, 'text': "Let's send them a coupon in the mail to convince them to place an order.", 'start': 8231.055, 'duration': 2.724}], 'summary': 'Retrieve first and last names of customers with specific customer ids or no orders.', 'duration': 26.805, 'max_score': 8206.974, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso8206974.jpg'}, {'end': 8289.028, 'src': 'embed', 'start': 8262.236, 'weight': 2, 'content': [{'end': 8266.405, 'text': 'whatever value or values are returned, we can use within a larger query.', 'start': 8262.236, 'duration': 4.169}, {'end': 8269.45, 'text': 'And well, yeah, those are subqueries in MySQL.', 'start': 8266.846, 'duration': 2.604}, {'end': 8276.221, 'text': 'Hello everybody, today I will be explaining the GROUP BY clause.', 'start': 8272.638, 'duration': 3.583}, {'end': 8280.843, 'text': 'The GROUP BY clause will aggregate all rows by a specific column.', 'start': 8276.501, 'duration': 4.342}, {'end': 8289.028, 'text': "It's often used with aggregate functions, such as the SUM function, MAX, MIN, AVERAGE, COUNT, just to name a few.", 'start': 8281.364, 'duration': 7.664}], 'summary': 'Explaining the group by clause in mysql for aggregating rows by specific column and using aggregate functions like sum, max, min, average, count.', 'duration': 26.792, 'max_score': 8262.236, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso8262236.jpg'}, {'end': 8592.017, 'src': 'embed', 'start': 8561.542, 'weight': 0, 'content': [{'end': 8563.984, 'text': 'For example, you could group by an order date.', 'start': 8561.542, 'duration': 2.442}, {'end': 8568.367, 'text': 'What are the total sales per date? Or by a customer ID.', 'start': 8564.664, 'duration': 3.703}, {'end': 8573.271, 'text': 'How many times has each customer visited our establishment? Those are just a few examples.', 'start': 8568.867, 'duration': 4.404}, {'end': 8577.154, 'text': 'And well, yeah, that is the GROUP BY clause in MySQL.', 'start': 8573.931, 'duration': 3.223}, {'end': 8581.488, 'text': "Hey again, it's me.", 'start': 8580.267, 'duration': 1.221}, {'end': 8585.451, 'text': "Today I'm going to be explaining the rollup clause, like a fruit rollup.", 'start': 8581.708, 'duration': 3.743}, {'end': 8588.134, 'text': "It's an extension of the group by clause.", 'start': 8585.772, 'duration': 2.362}, {'end': 8592.017, 'text': 'It produces another row and shows the grand total.', 'start': 8588.594, 'duration': 3.423}], 'summary': 'Explains group by and rollup clauses in mysql, illustrating their usage with examples.', 'duration': 30.475, 'max_score': 8561.542, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso8561542.jpg'}, {'end': 8819.55, 'src': 'embed', 'start': 8788.122, 'weight': 1, 'content': [{'end': 8790.063, 'text': "It's an extension of the group-by clause.", 'start': 8788.122, 'duration': 1.941}, {'end': 8794.345, 'text': 'It produces another row and shows, essentially, the grand total.', 'start': 8790.583, 'duration': 3.762}, {'end': 8797.227, 'text': "It's also known as a super aggregate value.", 'start': 8794.765, 'duration': 2.462}, {'end': 8801.837, 'text': 'To include a rollup, after the group by clause, just add with rollup.', 'start': 8797.867, 'duration': 3.97}, {'end': 8803.561, 'text': "It's probably good for accounting.", 'start': 8802.318, 'duration': 1.243}, {'end': 8807.01, 'text': "And well, yeah, that's the rollup clause in MySQL.", 'start': 8803.982, 'duration': 3.028}, {'end': 8814.547, 'text': 'Why hello there everybody, today I will be explaining the onDelete clause.', 'start': 8810.424, 'duration': 4.123}, {'end': 8819.55, 'text': "There's two versions, onDeleteSetNull and onDeleteCascade.", 'start': 8814.967, 'duration': 4.583}], 'summary': 'Mysql rollup clause adds grand total, useful for accounting.', 'duration': 31.428, 'max_score': 8788.122, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso8788122.jpg'}, {'end': 9291.062, 'src': 'embed', 'start': 9262.319, 'weight': 4, 'content': [{'end': 9265.301, 'text': 'so today i gotta explain stored procedures.', 'start': 9262.319, 'duration': 2.982}, {'end': 9269.585, 'text': 'a stored procedure is prepared sql code that you can save.', 'start': 9265.301, 'duration': 4.284}, {'end': 9271.407, 'text': "it's great if there's a query that you write.", 'start': 9269.585, 'duration': 1.822}, {'end': 9274.21, 'text': 'often, for example, i have this statement.', 'start': 9271.407, 'duration': 2.803}, {'end': 9275.331, 'text': "it's very verbose.", 'start': 9274.21, 'duration': 1.121}, {'end': 9276.412, 'text': "there's a lot we have to write.", 'start': 9275.331, 'duration': 1.081}, {'end': 9281.375, 'text': 'If this statement is something I have to write often, I could save it and reuse it later.', 'start': 9276.912, 'duration': 4.463}, {'end': 9288.921, 'text': 'This statement will interjoin the transactions table and my customers table, then display all customers that made a transaction.', 'start': 9281.776, 'duration': 7.145}, {'end': 9291.062, 'text': 'This statement is very verbose.', 'start': 9289.341, 'duration': 1.721}], 'summary': 'Stored procedures are reusable sql code, helpful for frequently used queries.', 'duration': 28.743, 'max_score': 9262.319, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso9262319.jpg'}], 'start': 8091.839, 'title': 'Mysql database operations', 'summary': 'Covers subqueries, group by and rollup clause, ondeletesetnull, ondeletecascade, and stored procedures in mysql, providing practical examples and applications for data manipulation and query optimization.', 'chapters': [{'end': 8289.028, 'start': 8091.839, 'title': 'Subqueries in mysql', 'summary': 'Explains how to use subqueries in mysql to retrieve customer names who have placed orders, highlighting the process of using subqueries to filter and manipulate data, and the potential applications of subqueries in identifying and targeting specific customer segments.', 'duration': 197.189, 'highlights': ['The subquery returns the customer IDs 1, 2, and 3 who have placed orders, showcasing the process of using subqueries to filter and retrieve specific data.', 'Demonstrates the use of the IN operator and subquery to retrieve the first name and last name of customers who have placed orders, providing a practical example of applying subqueries to retrieve related data.', 'Illustrates the potential application of subqueries in targeting specific customer segments by identifying customers who have never placed an order and suggesting sending them coupons to encourage purchases.', 'Explains the concept of subqueries as queries within larger queries, emphasizing the step-by-step visualization of the process and the utilization of values returned from subqueries in larger queries.', 'Introduces the GROUP BY clause, highlighting its function of aggregating rows by a specific column and its common usage with aggregate functions like SUM, MAX, MIN, AVERAGE, and COUNT.']}, {'end': 8814.547, 'start': 8289.308, 'title': 'Mysql group by and rollup clause', 'summary': 'Discusses the usage of the group by clause in mysql to aggregate data using functions such as sum, max, min, average, and count, as well as the introduction of the rollup clause to produce grand totals and super aggregate values.', 'duration': 525.239, 'highlights': ['The GROUP BY clause is used to aggregate data by a specific column and is often combined with aggregate functions such as SUM, MAX, MIN, AVERAGE, COUNT to calculate total sales per date or the number of times each customer visited the establishment. The GROUP BY clause in MySQL is used to aggregate data by a specific column and is often combined with aggregate functions such as SUM, MAX, MIN, AVERAGE, COUNT to calculate total sales per date or the number of times each customer visited the establishment.', 'The ROLLUP clause, an extension of the GROUP BY clause, produces another row to show the grand total or super aggregate values, and is useful for accounting purposes. The ROLLUP clause, an extension of the GROUP BY clause, produces another row to show the grand total or super aggregate values, and is useful for accounting purposes.', 'Usage of the ROLLUP clause to calculate the grand total of amounts per order date and the total number of transactions for each date, as well as to display the number of orders per customer ID and the hourly pay of employees with a grand total. The ROLLUP clause is used to calculate the grand total of amounts per order date and the total number of transactions for each date, as well as to display the number of orders per customer ID and the hourly pay of employees with a grand total.']}, {'end': 9262.319, 'start': 8814.967, 'title': 'Understanding ondeletesetnull and ondeletecascade', 'summary': 'Discusses the concepts of ondeletesetnull and ondeletecascade in mysql, demonstrating how the former replaces foreign key values with null and the latter deletes the entire row, with practical examples and their implications on tables and data integrity.', 'duration': 447.352, 'highlights': ['The chapter explains the concepts of onDeleteSetNull and onDeleteCascade in MySQL, offering practical examples and implications on data integrity. The chapter provides a clear explanation of the concepts of onDeleteSetNull and onDeleteCascade with practical examples and implications on data integrity.', 'It demonstrates how onDeleteSetNull replaces foreign key values with null and onDeleteCascade deletes the entire row, showcasing the impact on tables and data. The demonstration shows how onDeleteSetNull replaces foreign key values with null, while onDeleteCascade deletes the entire row, illustrating their impact on tables and data.', 'The chapter highlights the use of onDeleteSetNull and onDeleteCascade in updating existing tables and setting foreign key constraints. The chapter emphasizes the use of onDeleteSetNull and onDeleteCascade in updating existing tables and setting foreign key constraints.', 'It explains the process of adding the onDeleteSetNull and onDeleteCascade clauses to tables, along with practical demonstrations of their effects on data manipulation. The explanation covers the process of adding the onDeleteSetNull and onDeleteCascade clauses to tables, accompanied by practical demonstrations of their effects on data manipulation.']}, {'end': 9718.968, 'start': 9262.319, 'title': 'Stored procedures explanation', 'summary': 'Explains stored procedures, a form of prepared sql code that can be saved and reused, and demonstrates how to create, invoke, and drop stored procedures in mysql, highlighting its efficiency in reducing verbosity and simplifying query execution.', 'duration': 456.649, 'highlights': ["Stored procedure is prepared SQL code that can be saved and reused to reduce verbosity and simplify query execution. It's great if there's a query that you write often, for example, interjoining transactions table and customers table, then displaying all customers that made a transaction.", "Demonstration of creating a stored procedure to select all from the customers table and the process of changing the delimiter temporarily in MySQL. Creating a stored procedure named 'getCustomers' to select all from the customers table and the process of changing the delimiter temporarily in MySQL to allow the statement to execute successfully.", "Explanation of creating a stored procedure to find a customer by their customer ID, including setting up a parameter and invoking the stored procedure to retrieve customer information. Creating a stored procedure named 'FindCustomer' to find a customer by their customer ID, setting up a parameter for the customer ID, and invoking the stored procedure to retrieve customer information.", "Illustration of creating a stored procedure to find a customer by their first and last name and invoking the stored procedure to retrieve customer information. Creating a stored procedure named 'find customer' to find a customer by their first and last name and invoking the stored procedure to retrieve customer information."]}], 'duration': 1627.129, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso8091839.jpg', 'highlights': ['Covers subqueries, group by and rollup clause, ondeletesetnull, ondeletecascade, and stored procedures in mysql, providing practical examples and applications for data manipulation and query optimization.', 'The ROLLUP clause, an extension of the GROUP BY clause, produces another row to show the grand total or super aggregate values, and is useful for accounting purposes.', 'The GROUP BY clause is used to aggregate data by a specific column and is often combined with aggregate functions such as SUM, MAX, MIN, AVERAGE, COUNT to calculate total sales per date or the number of times each customer visited the establishment.', 'The subquery returns the customer IDs 1, 2, and 3 who have placed orders, showcasing the process of using subqueries to filter and retrieve specific data.', 'Stored procedure is prepared SQL code that can be saved and reused to reduce verbosity and simplify query execution.']}, {'end': 10748.749, 'segs': [{'end': 9745.332, 'src': 'embed', 'start': 9719.508, 'weight': 0, 'content': [{'end': 9725.655, 'text': 'A few of the benefits is that it reduces network traffic, it increases performance, and is more secure.', 'start': 9719.508, 'duration': 6.147}, {'end': 9731.321, 'text': 'An administrator can grant permission to a user or an application to use a stored procedure.', 'start': 9726.055, 'duration': 5.266}, {'end': 9735.725, 'text': 'One of the downsides, though, is that it increases memory usage of every connection.', 'start': 9731.721, 'duration': 4.004}, {'end': 9739.629, 'text': 'And well, everybody, those are stored procedures in MySQL.', 'start': 9736.226, 'duration': 3.403}, {'end': 9743.11, 'text': 'hey, everybody.', 'start': 9742.469, 'duration': 0.641}, {'end': 9745.332, 'text': 'today i need to explain triggers.', 'start': 9743.11, 'duration': 2.222}], 'summary': 'Stored procedures in mysql offer benefits like reducing network traffic and increasing performance, but they can also increase memory usage of every connection.', 'duration': 25.824, 'max_score': 9719.508, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso9719508.jpg'}, {'end': 9835.68, 'src': 'embed', 'start': 9802.1, 'weight': 1, 'content': [{'end': 9804.881, 'text': 'Then select all from our employees table.', 'start': 9802.1, 'duration': 2.781}, {'end': 9808.683, 'text': "Let's see what we have.", 'start': 9807.682, 'duration': 1.001}, {'end': 9809.343, 'text': 'There we are.', 'start': 9808.803, 'duration': 0.54}, {'end': 9810.643, 'text': "There's our salary column.", 'start': 9809.403, 'duration': 1.24}, {'end': 9814.245, 'text': "Let's update our employees table.", 'start': 9811.904, 'duration': 2.341}, {'end': 9822.847, 'text': 'Set our salary column equal to the hourly pay times.', 'start': 9816.54, 'duration': 6.307}, {'end': 9830.995, 'text': "To calculate an employee's salary using an hourly pay, Sheldon Plankton, for example, take the hourly pay, $10 per hour.", 'start': 9823.608, 'duration': 7.387}, {'end': 9835.68, 'text': "There's typically 40 hours in a work week, then 52 weeks in a year.", 'start': 9831.436, 'duration': 4.244}], 'summary': 'Updating employees table salary based on $10 per hour for 40 hours a week, 52 weeks a year.', 'duration': 33.58, 'max_score': 9802.1, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso9802100.jpg'}, {'end': 9889.93, 'src': 'embed', 'start': 9864.453, 'weight': 3, 'content': [{'end': 9871.017, 'text': "Whenever we update an employee's hourly pay, I would like to also update the salary automatically with the trigger.", 'start': 9864.453, 'duration': 6.564}, {'end': 9877.242, 'text': "I don't want to have to calculate every employee's salary manually, you know, using a calculator like I just did.", 'start': 9871.538, 'duration': 5.704}, {'end': 9878.963, 'text': "We're going to create a trigger.", 'start': 9877.902, 'duration': 1.061}, {'end': 9881.344, 'text': 'Create trigger.', 'start': 9879.823, 'duration': 1.521}, {'end': 9883.566, 'text': 'Then we need a name for this trigger.', 'start': 9882.285, 'duration': 1.281}, {'end': 9887.729, 'text': "Before we update the hourly pay, we're going to do something.", 'start': 9884.206, 'duration': 3.523}, {'end': 9889.93, 'text': 'I will name this trigger before..', 'start': 9888.349, 'duration': 1.581}], 'summary': 'Automate salary update by creating trigger for hourly pay changes.', 'duration': 25.477, 'max_score': 9864.453, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso9864453.jpg'}, {'end': 10385.975, 'src': 'embed', 'start': 10331.479, 'weight': 5, 'content': [{'end': 10332.259, 'text': 'Maybe taxes.', 'start': 10331.479, 'duration': 0.78}, {'end': 10334.9, 'text': 'And zero.', 'start': 10334.48, 'duration': 0.42}, {'end': 10341.884, 'text': 'I will calculate the expense total of my salaries row.', 'start': 10338.743, 'duration': 3.141}, {'end': 10347.045, 'text': 'Update expenses.', 'start': 10345.024, 'duration': 2.021}, {'end': 10356.366, 'text': "I will set the expense total equal to, I'll use a nested query.", 'start': 10347.065, 'duration': 9.301}, {'end': 10365.268, 'text': 'Select the sum of salary from employees.', 'start': 10357.927, 'duration': 7.341}, {'end': 10375.628, 'text': 'where our expense name column right here is equal to salaries.', 'start': 10367.122, 'duration': 8.506}, {'end': 10382.633, 'text': 'This is all of the salaries combined from the employees table.', 'start': 10379.651, 'duration': 2.982}, {'end': 10385.975, 'text': 'The total is $254,280.', 'start': 10383.294, 'duration': 2.681}], 'summary': 'Calculated total salaries expense is $254,280.', 'duration': 54.496, 'max_score': 10331.479, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso10331479.jpg'}, {'end': 10637.426, 'src': 'embed', 'start': 10610.544, 'weight': 4, 'content': [{'end': 10616.427, 'text': 'When we insert a new employee, more specifically a salary, our expense total of salaries will be updated.', 'start': 10610.544, 'duration': 5.883}, {'end': 10618.909, 'text': "One last example, then I promise we're done.", 'start': 10617.128, 'duration': 1.781}, {'end': 10626.713, 'text': "Let's create a trigger that will update this value when we change an employee salary, and that will probably be the most complicated one.", 'start': 10619.529, 'duration': 7.184}, {'end': 10637.426, 'text': "Let's create a trigger after salary update.", 'start': 10628.581, 'duration': 8.845}], 'summary': 'Creating a trigger to update expense total when inserting a new employee and changing salary.', 'duration': 26.882, 'max_score': 10610.544, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso10610544.jpg'}, {'end': 10748.749, 'src': 'embed', 'start': 10734.939, 'weight': 2, 'content': [{'end': 10738.261, 'text': 'Such as when we insert, update, or delete something from a table.', 'start': 10734.939, 'duration': 3.322}, {'end': 10743.285, 'text': 'A few of the benefits is that you can check data, handle errors, and audit tables.', 'start': 10738.701, 'duration': 4.584}, {'end': 10745.366, 'text': "It's a really helpful tool to have.", 'start': 10743.865, 'duration': 1.501}, {'end': 10748.749, 'text': 'And well everybody, those are triggers in MySQL.', 'start': 10745.827, 'duration': 2.922}], 'summary': 'Mysql triggers offer benefits like data checking, error handling, and table auditing.', 'duration': 13.81, 'max_score': 10734.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso10734939.jpg'}], 'start': 9719.508, 'title': 'Mysql triggers and stored procedures', 'summary': 'Explains the benefits and downsides of stored procedures, provides a detailed guide on creating triggers, and showcases automation of salary calculations, including examples of salary updates and expense management, resulting in increased performance and efficiency in mysql databases.', 'chapters': [{'end': 10009.858, 'start': 9719.508, 'title': 'Stored procedures and triggers in mysql', 'summary': 'Explains the benefits and downsides of stored procedures, and provides a detailed guide on creating triggers, including an example on updating employee salaries automatically based on hourly pay, with a focus on increasing performance and reducing manual effort.', 'duration': 290.35, 'highlights': ['Stored procedures reduce network traffic, increase performance, and enhance security. Stored procedures provide benefits such as reducing network traffic, improving performance, and enhancing security.', 'Creating triggers to automatically update employee salaries based on hourly pay, reducing manual effort. The chapter provides a detailed guide on creating triggers to automatically update employee salaries based on hourly pay, reducing manual effort.', 'Explanation of creating triggers before or after specific events, such as insert, update, or delete. The chapter explains the process of creating triggers before or after specific events, such as insert, update, or delete.', 'Demonstration of using MySQL to calculate and update employee salaries based on hourly pay, with specific examples and calculations. The chapter provides a demonstration of using MySQL to calculate and update employee salaries based on hourly pay, including specific examples and calculations.', 'Detailed steps for creating and executing triggers in MySQL, including checking for triggers in the schema. The chapter provides detailed steps for creating and executing triggers in MySQL, including checking for triggers in the schema.']}, {'end': 10241.427, 'start': 10012.48, 'title': 'Automating salary calculations with triggers', 'summary': "Explores the automation of updating salaries and creating triggers in a database, including setting mr. krabs' salary to $104,000, increasing all employees' hourly pay by $1, deleting an employee, and creating a trigger to calculate salary upon insertion of a new employee.", 'duration': 228.947, 'highlights': ["Mr. Krabs' salary is updated to over six figures, $104,000, from $53,000, reflecting a substantial increase in his annual pay.", "All employees' hourly pay is increased by $1, resulting in a universal raise for the entire workforce.", 'Creation of a trigger to calculate the salary when inserting a new employee, ensuring automated salary computation for new hires.', 'Deletion of Plankton from the employee database, demonstrating the removal of an employee using SQL commands.']}, {'end': 10748.749, 'start': 10242.147, 'title': 'Managing expenses with mysql triggers', 'summary': "Introduces the creation and use of triggers in mysql, demonstrating their application in managing expenses by automatically updating the expense total when inserting, updating, or deleting employee salaries, and showcases how a trigger can be used to update the expense total when an employee's salary is changed, resulting in a more accurate and efficient expense tracking system.", 'duration': 506.602, 'highlights': ['Triggers were created to update the expense total when inserting, updating, or deleting employee salaries, resulting in automatic adjustments to the expense total. Demonstrates the application of triggers to manage expenses by automatically updating the expense total when inserting, updating, or deleting employee salaries.', 'The expense total was recalculated using a nested query to sum the salaries of all employees, resulting in a total expense of $254,280. Illustrates the use of a nested query to calculate the expense total based on the sum of salaries from the employees table, resulting in a total expense of $254,280.', "A trigger was created to update the expense total when an employee's salary is changed, resulting in a more accurate and efficient expense tracking system. Showcases the creation of a trigger to update the expense total when an employee's salary is changed, enhancing the accuracy and efficiency of the expense tracking system."]}], 'duration': 1029.241, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/5OdVJbNCSso/pics/5OdVJbNCSso9719508.jpg', 'highlights': ['Stored procedures reduce network traffic, increase performance, and enhance security.', 'Demonstration of using MySQL to calculate and update employee salaries based on hourly pay, with specific examples and calculations.', 'Detailed steps for creating and executing triggers in MySQL, including checking for triggers in the schema.', 'Creation of a trigger to calculate the salary when inserting a new employee, ensuring automated salary computation for new hires.', 'Triggers were created to update the expense total when inserting, updating, or deleting employee salaries, resulting in automatic adjustments to the expense total.', 'The expense total was recalculated using a nested query to sum the salaries of all employees, resulting in a total expense of $254,280.']}], 'highlights': ['The process of downloading, installing, and using MySQL is covered, including setting up a local instance.', 'The primary key constraint ensures that each value in the specified column is unique and not null, serving as a unique identifier for the table.', 'The examples illustrate selecting all from employees where employee ID equals one, finding data where first name equals SpongeBob, and filtering employees with hourly pay greater than or equal to 15.', 'Auto commit is a mode set to on by default in MySQL, where transactions are automatically saved.', 'The foreign key constraint in MySQL allows for the establishment of a link between two tables, providing the benefit of referencing data from one table in another, preventing actions that would break the link.', 'The sum function calculated the total transaction amount to be $17.25.', 'The process of creating a referral system within a customer table, including setting referral IDs, updating customer information, and using a self join to replace the referral ID with customer names.', 'The ROLLUP clause, an extension of the GROUP BY clause, produces another row to show the grand total or super aggregate values, and is useful for accounting purposes.', 'Stored procedures reduce network traffic, increase performance, and enhance security.', 'Creation of a trigger to calculate the salary when inserting a new employee, ensuring automated salary computation for new hires.']}