title
SQL Tutorial : SQL Full Course

description
MY UDEMY COURSES ARE 87.5% OFF TIL March 26th ($9.99) ONE IS FREE ➡️ Python Data Science Series for $9.99 : Highest Rated & Largest Python Udemy Course + 56 Hrs + 200 Videos + Data Science https://bit.ly/Master_Python_50 ➡️ C++ Programming Bootcamp Series for $9.99 : Over 23 Hrs + 53 Videos + Quizzes + Graded Assignments + New Videos Every Month https://bit.ly/C_Course_50 ➡️ FREE 15 hour Golang Course!!! : https://bit.ly/go-tutorial9 (100 Available) Get the Code : http://bit.ly/UltimateSQL Best SQL Book : https://amzn.to/2mEMbMO If you are looking to learn everything about SQL you are watching the right video. This video is a 500 page book in one video! It contains over 100 queries that will teach everything from basic queries through Stored Procedures, Functions, Triggers and Database Design. You'll learn about numerous SQL queries while building a real world sales transaction database. Take your time and create the database and enter the queries to maximize your learning potential. Like the channel? Consider becoming a Patreon and get access to exclusive videos! Check it out here: ►► https://www.patreon.com/derekbanas GET FREE STUFF FOR SUPPORTING MY TUTORIALS 1. Get a Free Stock : share.robinhood.com/derekb1560 2. Get 2 Free Audiobooks : https://amzn.to/2Y5FV2p THANK YOU TO MY PATREON SUPPORTERS LIKE : ckcoder.com vsolutions.be instagram.com/lumarycodes/ github.com/metabake greedygammon.com

detail
{'title': 'SQL Tutorial : SQL Full Course', 'heatmap': [{'end': 994.256, 'start': 907.548, 'weight': 0.745}, {'end': 2477.744, 'start': 2392.981, 'weight': 1}], 'summary': 'This sql tutorial video provides a comprehensive course on mysql, covering installation, database fundamentals, data organization, management, sql commands, joins, unions, regular expressions, views, stored programs, error handling, and database operations, compressing a 500-page book into one video.', 'chapters': [{'end': 491.689, 'segs': [{'end': 311.979, 'src': 'embed', 'start': 273.616, 'weight': 0, 'content': [{'end': 276.498, 'text': "You're just basically going to be clicking on next a couple times.", 'start': 273.616, 'duration': 2.882}, {'end': 278.919, 'text': "You're going to have to type in your root password here.", 'start': 276.518, 'duration': 2.401}, {'end': 281.04, 'text': "Whenever you do, you'll be able to click on next.", 'start': 279.159, 'duration': 1.881}, {'end': 283.762, 'text': 'You can see here the connection was successful.', 'start': 281.4, 'duration': 2.362}, {'end': 284.322, 'text': "That's good.", 'start': 283.802, 'duration': 0.52}, {'end': 285.103, 'text': 'Click on next.', 'start': 284.382, 'duration': 0.721}, {'end': 286.824, 'text': 'And then just click on execute.', 'start': 285.503, 'duration': 1.321}, {'end': 288.505, 'text': "Everything's going to be completed.", 'start': 287.244, 'duration': 1.261}, {'end': 289.705, 'text': 'And you can click on finish.', 'start': 288.705, 'duration': 1}, {'end': 291.246, 'text': 'And then just click on next.', 'start': 290.126, 'duration': 1.12}, {'end': 298.07, 'text': 'And I chose to start the MySQL Workbench after the setup as well as the shell.', 'start': 291.867, 'duration': 6.203}, {'end': 298.911, 'text': 'Click on finish.', 'start': 298.271, 'duration': 0.64}, {'end': 303.112, 'text': "And this is the MySQL Workbench, which is what we're going to be using in this tutorial.", 'start': 299.429, 'duration': 3.683}, {'end': 304.413, 'text': 'And this is the shell.', 'start': 303.352, 'duration': 1.061}, {'end': 311.979, 'text': "We're not going to use it, but you will be able to use the shell based off of what I'm going to teach you in MySQL Workbench.", 'start': 304.573, 'duration': 7.406}], 'summary': 'Set up mysql workbench by clicking through prompts and finishing the installation process.', 'duration': 38.363, 'max_score': 273.616, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To273616.jpg'}, {'end': 491.689, 'src': 'embed', 'start': 459.001, 'weight': 3, 'content': [{'end': 461.843, 'text': 'And then we just come over here and double click on this.', 'start': 459.001, 'duration': 2.842}, {'end': 464.444, 'text': 'Enter in your root password, click on OK.', 'start': 462.303, 'duration': 2.141}, {'end': 467.465, 'text': "And then MySQL Workbench opens up and everything's looking good.", 'start': 464.704, 'duration': 2.761}, {'end': 468.205, 'text': 'all right.', 'start': 467.865, 'duration': 0.34}, {'end': 472.65, 'text': 'so now you have mysql workbench and mysql on numerous other different things installed.', 'start': 468.205, 'duration': 4.445}, {'end': 476.714, 'text': "everything's ready for the tutorial and so now i'm going to jump into the tutorial.", 'start': 472.65, 'duration': 4.064}, {'end': 480.197, 'text': 'okay, so a small part of this tutorial is going to be slides.', 'start': 476.714, 'duration': 3.483}, {'end': 484.281, 'text': 'the vast majority of it is going to be me typing in code and executing it,', 'start': 480.197, 'duration': 4.084}, {'end': 491.689, 'text': "but i just wanted to let you know right there i'm just going to use some slides to help explain the overview of what we're going to be doing.", 'start': 484.281, 'duration': 7.408}], 'summary': 'Setting up mysql workbench and mysql for tutorial', 'duration': 32.688, 'max_score': 459.001, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To459001.jpg'}], 'start': 0.179, 'title': 'Mysql installation and tutorial', 'summary': 'Covers a comprehensive sql tutorial using mysql, including installation on both windows and mac os, with a table of contents for easy navigation, compressing a 500-page book into one video. it also provides a detailed guide on setting up mysql on windows and macos, covering the installation, configuration, and usage of mysql workbench, with emphasis on resolving download and installation issues.', 'chapters': [{'end': 37.923, 'start': 0.179, 'title': 'Ultimate sql tutorial with mysql', 'summary': 'Covers a comprehensive sql tutorial using mysql, including installation on both windows and mac os, with a table of contents for easy navigation, compressing a 500-page book into one video.', 'duration': 37.744, 'highlights': ['The tutorial covers everything about SQL using MySQL, compressing a 500-page book into one video.', 'Provides guidance on installation on both Windows and Mac OS for MySQL and MySQL Workbench.', 'Includes a table of contents for easy navigation throughout the video.']}, {'end': 491.689, 'start': 38.043, 'title': 'Setting up mysql on windows and macos', 'summary': 'Provides a detailed guide on setting up mysql on windows and macos, covering the installation, configuration, and usage of mysql workbench, with emphasis on resolving download and installation issues.', 'duration': 453.646, 'highlights': ["The chapter provides a step-by-step guide on downloading and installing MySQL on Windows, including resolving download issues by repeatedly clicking 'retry' until successful.", 'The process of setting up MySQL on macOS is detailed, highlighting the installation of MySQL Workbench and addressing potential download and installation challenges.', 'The usage of MySQL Workbench and shell is demonstrated for Windows, with a brief explanation of their functionalities and their correlation to the tutorial.']}], 'duration': 491.51, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To179.jpg', 'highlights': ['Covers a comprehensive SQL tutorial using MySQL, compressing a 500-page book into one video.', 'Provides guidance on installation on both Windows and Mac OS for MySQL and MySQL Workbench.', 'Includes a table of contents for easy navigation throughout the video.', "The chapter provides a step-by-step guide on downloading and installing MySQL on Windows, including resolving download issues by repeatedly clicking 'retry' until successful.", 'The process of setting up MySQL on macOS is detailed, highlighting the installation of MySQL Workbench and addressing potential download and installation challenges.', 'The usage of MySQL Workbench and shell is demonstrated for Windows, with a brief explanation of their functionalities and their correlation to the tutorial.']}, {'end': 1090.811, 'segs': [{'end': 857.752, 'src': 'embed', 'start': 808.62, 'weight': 3, 'content': [{'end': 814.687, 'text': "The state, I'm going to say that that is going to be two characters in length.", 'start': 808.62, 'duration': 6.067}, {'end': 820.132, 'text': 'I could also come in here and go characters and two zip.', 'start': 814.847, 'duration': 5.285}, {'end': 825.076, 'text': "now there's going to be different types of ways to store numbers.", 'start': 820.132, 'duration': 4.944}, {'end': 828.099, 'text': "I'm going to show you that in a second the specifics of them.", 'start': 825.076, 'duration': 3.023}, {'end': 835.285, 'text': "but I'm going to use a medium int here because that makes sense for the size of the zip code that I'm going to be expected.", 'start': 828.099, 'duration': 7.186}, {'end': 838.427, 'text': "I'm going to say that I expect this data to be unsigned.", 'start': 835.285, 'duration': 3.142}, {'end': 844.39, 'text': "that means I don't have a negative zip code and again i'm going to say that i require it, so not null.", 'start': 838.427, 'duration': 5.963}, {'end': 848.551, 'text': 'null is not a available piece of data to put inside of here.', 'start': 844.39, 'duration': 4.161}, {'end': 850.951, 'text': "i'm going to say that i expect a phone number.", 'start': 848.551, 'duration': 2.4}, {'end': 855.192, 'text': "let's just come in here and do this whoops, wrong thing down here.", 'start': 850.951, 'duration': 4.241}, {'end': 855.952, 'text': 'there we are.', 'start': 855.192, 'duration': 0.76}, {'end': 857.752, 'text': "i'm going to say that i want my phone number.", 'start': 855.952, 'duration': 1.8}], 'summary': 'Defining data types: state, zip, phone number; using medium int for zip code, expecting unsigned and not null data.', 'duration': 49.132, 'max_score': 808.62, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To808620.jpg'}, {'end': 994.256, 'src': 'heatmap', 'start': 907.548, 'weight': 0.745, 'content': [{'end': 916.532, 'text': "In that situation, I'm going to say that I want to use a timestamp data type, which is going to allow me to store both dates as well as times.", 'start': 907.548, 'duration': 8.984}, {'end': 920.473, 'text': "And I'll say that I want that to be definitely put inside of there.", 'start': 916.992, 'duration': 3.481}, {'end': 923.414, 'text': "And then you're going to see our ID.", 'start': 920.853, 'duration': 2.561}, {'end': 925.595, 'text': 'This is going to be our primary key.', 'start': 923.734, 'duration': 1.861}, {'end': 934.117, 'text': 'This is going to be a unique number that is going to be specific to each individual customer that I input into the database.', 'start': 925.615, 'duration': 8.502}, {'end': 936.038, 'text': 'It is going to be an integer.', 'start': 934.697, 'duration': 1.341}, {'end': 940.059, 'text': "It is going to be unsigned, so it's not going to be negative.", 'start': 936.058, 'duration': 4.001}, {'end': 942.239, 'text': 'It is going to be not null.', 'start': 940.439, 'duration': 1.8}, {'end': 945.54, 'text': 'I can also go down to the next line without causing any type of an error.', 'start': 942.579, 'duration': 2.961}, {'end': 949.985, 'text': "I'm also going to say that I want this to be auto incremented.", 'start': 946.001, 'duration': 3.984}, {'end': 957.293, 'text': "So, the first time I enter a customer into the database, it's going to receive a value of one, and the next number is going to be two,", 'start': 950.085, 'duration': 7.208}, {'end': 958.394, 'text': 'and so forth and so on.', 'start': 957.293, 'duration': 1.101}, {'end': 959.776, 'text': "That's what auto increment means.", 'start': 958.434, 'duration': 1.342}, {'end': 962.919, 'text': "And then finally, I'm going to say that it's a primary key.", 'start': 960.437, 'duration': 2.482}, {'end': 967.124, 'text': "And that just means it's a unique key that is going to represent each customer.", 'start': 963.32, 'duration': 3.804}, {'end': 974.246, 'text': 'Then after I go and finish defining what I want my table to look like, I just click on this lightning bolt up here.', 'start': 967.423, 'duration': 6.823}, {'end': 977.048, 'text': 'And then I can come over here on the right side.', 'start': 974.566, 'duration': 2.482}, {'end': 980.249, 'text': "Well, first off, if you come down here, you're going to see there's no errors.", 'start': 977.108, 'duration': 3.141}, {'end': 981.21, 'text': "So that's a good thing.", 'start': 980.309, 'duration': 0.901}, {'end': 985.131, 'text': 'Then I can come over here and right click on this and say refresh all.', 'start': 981.59, 'duration': 3.541}, {'end': 988.873, 'text': "And now you're going to see that I have customer table right here.", 'start': 985.532, 'duration': 3.341}, {'end': 994.256, 'text': "And if I click on it, you're going to see all the specifics of how that customer table is set up.", 'start': 989.073, 'duration': 5.183}], 'summary': 'Using timestamp data type, setting primary key as unsigned integer with auto increment, ensuring no errors in table setup', 'duration': 86.708, 'max_score': 907.548, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To907548.jpg'}, {'end': 974.246, 'src': 'embed', 'start': 950.085, 'weight': 0, 'content': [{'end': 957.293, 'text': "So, the first time I enter a customer into the database, it's going to receive a value of one, and the next number is going to be two,", 'start': 950.085, 'duration': 7.208}, {'end': 958.394, 'text': 'and so forth and so on.', 'start': 957.293, 'duration': 1.101}, {'end': 959.776, 'text': "That's what auto increment means.", 'start': 958.434, 'duration': 1.342}, {'end': 962.919, 'text': "And then finally, I'm going to say that it's a primary key.", 'start': 960.437, 'duration': 2.482}, {'end': 967.124, 'text': "And that just means it's a unique key that is going to represent each customer.", 'start': 963.32, 'duration': 3.804}, {'end': 974.246, 'text': 'Then after I go and finish defining what I want my table to look like, I just click on this lightning bolt up here.', 'start': 967.423, 'duration': 6.823}], 'summary': 'Database assigns unique customer ids incrementally starting from 1.', 'duration': 24.161, 'max_score': 950.085, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To950085.jpg'}, {'end': 1048.555, 'src': 'embed', 'start': 1027.039, 'weight': 1, 'content': [{'end': 1037.346, 'text': 'well, the decimal data type and decimals are exact numeric data values, and whenever you are defining them, you use the form of decimal,', 'start': 1027.039, 'duration': 10.307}, {'end': 1042.589, 'text': 'and then six is going to represent the number of significant digits of the entire number,', 'start': 1037.346, 'duration': 5.243}, {'end': 1048.555, 'text': 'while this second value here is going to represent the number of decimal precision-wise.', 'start': 1043.05, 'duration': 5.505}], 'summary': 'Decimal data type ensures exact numeric values with 6 significant digits and 2 decimal precision.', 'duration': 21.516, 'max_score': 1027.039, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1027039.jpg'}], 'start': 492.034, 'title': 'Database fundamentals and mysql database creation', 'summary': 'Covers database fundamentals, including tables, rows, columns, keys, and relationships, and provides a practical guide to creating a mysql database with specific data types, constraints, and numeric formats.', 'chapters': [{'end': 622.397, 'start': 492.034, 'title': 'Database fundamentals', 'summary': 'Explains the concept of databases, including tables, rows, and columns, primary and foreign keys, and one-to-many relationships, with examples and explanations to ensure understanding.', 'duration': 130.363, 'highlights': ['Databases are structured into rows and columns, containing many tables of organized data and using commands called queries to interact with the data.', 'Primary keys define unique entities in tables, while non-primary keys are non-unique keys used for reference.', "Foreign keys in a table refer to a row of data in a separate table, creating relationships, and it's possible to have multiple foreign keys in a table but only one primary key.", "Customers' information and countries in tables are used as examples to illustrate primary and foreign keys, and one-to-many relationships in databases."]}, {'end': 1090.811, 'start': 622.797, 'title': 'Mysql database creation and data types', 'summary': 'Explains how to create a mysql database, including creating a new schema, applying a query to create a database, and defining a customer table with specific data types and constraints. it also provides information on common numeric data types and the format for date, date time, timestamp, and time.', 'duration': 468.014, 'highlights': ['Creating a MySQL Database Explains the process of creating a new schema and applying a query to create a sales database, with step-by-step instructions.', 'Defining a Customer Table Details the process of defining a customer table, including specifying data types, constraints, and primary key, providing specific examples of fields such as first name, last name, email, and more.', 'Explanation of Common Numeric Data Types Provides information on common numeric data types, including their minimum and maximum values, and discusses the deprecation of the float data type and its replacement with the decimal data type.', 'Format for Date, Date Time, Timestamp, and Time Presents the format and maximum values for date, date time, timestamp, and time data types, offering detailed insights into the structure of these data types and the range of values they can store.']}], 'duration': 598.777, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To492034.jpg', 'highlights': ['Creating a MySQL Database Explains the process of creating a new schema and applying a query to create a sales database, with step-by-step instructions.', 'Format for Date, Date Time, Timestamp, and Time Presents the format and maximum values for date, date time, timestamp, and time data types, offering detailed insights into the structure of these data types and the range of values they can store.', 'Defining a Customer Table Details the process of defining a customer table, including specifying data types, constraints, and primary key, providing specific examples of fields such as first name, last name, email, and more.', 'Databases are structured into rows and columns, containing many tables of organized data and using commands called queries to interact with the data.', 'Primary keys define unique entities in tables, while non-primary keys are non-unique keys used for reference.']}, {'end': 2012.265, 'segs': [{'end': 1315.876, 'src': 'embed', 'start': 1285.133, 'weight': 6, 'content': [{'end': 1287.594, 'text': 'Alright so this is just going to be salesperson.', 'start': 1285.133, 'duration': 2.461}, {'end': 1289.894, 'text': "So I'm going to go salesperson.", 'start': 1287.894, 'duration': 2}, {'end': 1292.335, 'text': 'And largely this is going to be exactly the same.', 'start': 1290.114, 'duration': 2.221}, {'end': 1294.555, 'text': "Remember I said company isn't going to be here.", 'start': 1292.355, 'duration': 2.2}, {'end': 1295.436, 'text': 'So get rid of that.', 'start': 1294.615, 'duration': 0.821}, {'end': 1299.356, 'text': "And we're going to change date entered into date hired.", 'start': 1295.896, 'duration': 3.46}, {'end': 1301.477, 'text': 'So put date hired inside of there.', 'start': 1299.777, 'duration': 1.7}, {'end': 1304.318, 'text': 'And other than that everything else is exactly the same.', 'start': 1301.837, 'duration': 2.481}, {'end': 1306.518, 'text': "So let's just come in here and click our lightning bolt.", 'start': 1304.398, 'duration': 2.12}, {'end': 1308.888, 'text': 'And then come up here where it says refresh all.', 'start': 1306.806, 'duration': 2.082}, {'end': 1311.912, 'text': 'And now we can see the salesperson has been properly entered.', 'start': 1309.249, 'duration': 2.663}, {'end': 1315.876, 'text': 'You can see here an example of different table data.', 'start': 1312.312, 'duration': 3.564}], 'summary': 'Salesperson data entered with date hired, sales data refreshed.', 'duration': 30.743, 'max_score': 1285.133, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1285133.jpg'}, {'end': 1434.089, 'src': 'embed', 'start': 1392.394, 'weight': 0, 'content': [{'end': 1393.895, 'text': 'and then what are we going to do after that?', 'start': 1392.394, 'duration': 1.501}, {'end': 1398.379, 'text': "we're going to give it a unique id, which is going to be this guy right here.", 'start': 1393.895, 'duration': 4.484}, {'end': 1402.601, 'text': "so let's just copy that out of there and paste that inside of there, and that's all we need to do.", 'start': 1398.379, 'duration': 4.222}, {'end': 1409.224, 'text': 'And then we can come in and click on our little lightning bolt and come over to tables and refresh all.', 'start': 1402.941, 'duration': 6.283}, {'end': 1412.365, 'text': "and now we can see product type is right here and everything's looking good.", 'start': 1409.224, 'duration': 3.141}, {'end': 1422.229, 'text': 'You can see an example here of our sales orders which once again we got from that invoice real world thing that we worked with previously.', 'start': 1412.725, 'duration': 9.504}, {'end': 1430.405, 'text': "So basically what I'm going to be putting inside of here for each of the individual sales orders is the item ID.", 'start': 1422.669, 'duration': 7.736}, {'end': 1434.089, 'text': 'the quantity discounts, whether it was taxable or not.', 'start': 1431.007, 'duration': 3.082}], 'summary': 'Adding unique id to items, refreshing tables, and inputting sales order details.', 'duration': 41.695, 'max_score': 1392.394, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1392394.jpg'}, {'end': 1497.121, 'src': 'embed', 'start': 1471.48, 'weight': 3, 'content': [{'end': 1478.045, 'text': "but whenever you look at this gigantic table, you're going to see a lot of problems, and this is where we get into.", 'start': 1471.48, 'duration': 6.565}, {'end': 1481.849, 'text': 'better organizing data so that it is easier to work with.', 'start': 1478.587, 'duration': 3.262}, {'end': 1486.533, 'text': 'Tables, as I have mentioned previously, should not contain duplicate data.', 'start': 1482.27, 'duration': 4.263}, {'end': 1497.121, 'text': 'So, for example, if we had individual items ordered all in one table, as well as sales persons and purchase order numbers and time of sales,', 'start': 1486.633, 'duration': 10.488}], 'summary': 'Organize data to avoid duplicate entries and improve work efficiency.', 'duration': 25.641, 'max_score': 1471.48, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1471480.jpg'}, {'end': 1830.505, 'src': 'embed', 'start': 1787.975, 'weight': 4, 'content': [{'end': 1791.277, 'text': 'And I could go and give it a default value, but whatever.', 'start': 1787.975, 'duration': 3.302}, {'end': 1792.778, 'text': "I'll just keep it the way it is right there.", 'start': 1791.317, 'duration': 1.461}, {'end': 1795.84, 'text': "Price And I'm going to have that be not null.", 'start': 1793.118, 'duration': 2.722}, {'end': 1796.461, 'text': 'Okay Okay.', 'start': 1796.241, 'duration': 0.22}, {'end': 1801.969, 'text': "So you can see I'm breaking up the descriptions of all these individual tables into individual pieces.", 'start': 1796.806, 'duration': 5.163}, {'end': 1805.07, 'text': 'So this is going to be an item to specific information.', 'start': 1802.029, 'duration': 3.041}, {'end': 1808.612, 'text': 'Click that, tables, and refresh all.', 'start': 1805.09, 'duration': 3.522}, {'end': 1813.234, 'text': 'And now you can see that item is right here and all the data types that are defined for it.', 'start': 1809.052, 'duration': 4.182}, {'end': 1815.716, 'text': "Now I'm going to create my sales orders.", 'start': 1813.595, 'duration': 2.121}, {'end': 1819.418, 'text': 'So come in here, get rid of all of this.', 'start': 1816.296, 'duration': 3.122}, {'end': 1825.701, 'text': "And this table is only going to have information that's going to be pertaining to the order.", 'start': 1820.218, 'duration': 5.483}, {'end': 1830.505, 'text': "And it's not going to contain information about products and prices and all those other different things.", 'start': 1826.081, 'duration': 4.424}], 'summary': 'Creating database tables with specific information and defining data types.', 'duration': 42.53, 'max_score': 1787.975, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1787975.jpg'}, {'end': 1871.959, 'src': 'embed', 'start': 1849.82, 'weight': 5, 'content': [{'end': 1857.893, 'text': "So I'm going to say sales order and then What am I going to have? Well, I need to define the two people that are doing business.", 'start': 1849.82, 'duration': 8.073}, {'end': 1861.314, 'text': "So we're going to have customer ID integer.", 'start': 1858.413, 'duration': 2.901}, {'end': 1866.276, 'text': "It's going to be unsigned and we must have a value for it.", 'start': 1861.754, 'duration': 4.522}, {'end': 1871.959, 'text': "What else am I going to have? Well, I'm agreeing the two people are doing business.", 'start': 1866.716, 'duration': 5.243}], 'summary': 'Defining sales order with customer id as unsigned integer.', 'duration': 22.139, 'max_score': 1849.82, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1849820.jpg'}], 'start': 1090.811, 'title': 'Organizing sales order data', 'summary': 'Covers organizing sales order data by breaking down a sales order table into smaller tables, creating separate tables for sales items, products, and sales orders, and using primary and foreign keys for linking the tables. it also emphasizes the principles of database design and table creation, and the maximum storage limits for variable character, blob, enums, and sets, with 65,535 characters for blobs being the highest limit.', 'chapters': [{'end': 1134.1, 'start': 1090.811, 'title': 'Data storage and constraints', 'summary': 'Covers the maximum number of characters that can be stored in variable character, blob, enums, and sets, with 65,535 characters for blobs being the highest limit.', 'duration': 43.289, 'highlights': ['A blob can store up to 65,535 characters, which is the maximum size for storing an 8-bit number.', 'Variable character can store a maximum of 255 characters.', 'Enums and sets store distinct elements, with enums being able to store elements like male or female.', "The chapter also discusses the 'not null' constraint for data."]}, {'end': 1451.941, 'start': 1134.14, 'title': 'Database design and table creation', 'summary': 'Explains the principles of database design and table creation, emphasizing the importance of using primary and foreign keys, normalizing databases, and creating tables to represent real-world objects with separate elements for each piece of information. it also demonstrates the creation of tables, such as customer, salesperson, and product type, with specific data types and constraints.', 'duration': 317.801, 'highlights': ['The chapter explains the principles of database design and table creation, emphasizing the importance of using primary and foreign keys, normalizing databases, and creating tables to represent real-world objects with separate elements for each piece of information.', 'It also demonstrates the creation of tables, such as customer, salesperson, and product type, with specific data types and constraints.', 'Each table should represent one real world object, and each column should store one piece of information about that object, using either primary or foreign keys or both to relate tables and reduce redundant data.', 'The chapter provides a step-by-step demonstration of creating tables like customer, salesperson, and product type, with specific data types and constraints, such as unique keys, auto increment, and default values.']}, {'end': 2012.265, 'start': 1452.361, 'title': 'Organizing sales order data', 'summary': 'Explains the process of breaking down a sales order table into smaller tables to better organize and avoid duplication, with key points including the creation of separate tables for sales items, products, and sales orders, and the use of primary and foreign keys for linking the tables.', 'duration': 559.904, 'highlights': ['Explaining the need for better organizing data to avoid duplication and make it easier to work with The transcript emphasizes the importance of organizing data to prevent problems caused by duplicate data in a sales order table, highlighting the need for a more efficient and manageable structure.', 'Creating separate tables for sales items, products, and sales orders to break down the sales order into smaller, more manageable pieces The speaker outlines the process of breaking down the sales order into smaller and more manageable pieces by creating separate tables for sales items, products, and sales orders, enhancing the ease of working with the data.', 'Utilizing primary and foreign keys for linking the tables to avoid data duplication The use of primary and foreign keys is highlighted as a method for linking the created tables, emphasizing the importance of unique identifiers and referencing keys in different tables to prevent data duplication.']}], 'duration': 921.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To1090811.jpg', 'highlights': ['A blob can store up to 65,535 characters, the maximum size for an 8-bit number.', 'Variable character can store a maximum of 255 characters.', 'Enums and sets store distinct elements, with enums being able to store elements like male or female.', 'The chapter explains the principles of database design and table creation, emphasizing the importance of using primary and foreign keys, normalizing databases, and creating tables to represent real-world objects with separate elements for each piece of information.', 'Creating separate tables for sales items, products, and sales orders to break down the sales order into smaller, more manageable pieces.', 'Utilizing primary and foreign keys for linking the tables to avoid data duplication.', 'The chapter provides a step-by-step demonstration of creating tables like customer, salesperson, and product type, with specific data types and constraints, such as unique keys, auto increment, and default values.', 'Explaining the need for better organizing data to avoid duplication and make it easier to work with.', 'The transcript emphasizes the importance of organizing data to prevent problems caused by duplicate data in a sales order table, highlighting the need for a more efficient and manageable structure.']}, {'end': 2678.784, 'segs': [{'end': 2155.989, 'src': 'embed', 'start': 2131.389, 'weight': 3, 'content': [{'end': 2138.434, 'text': 'And this is just going to simulate picking up a quantity of an individual item with a certain discount and a certain tax rate.', 'start': 2131.389, 'duration': 7.045}, {'end': 2150.304, 'text': 'And the item itself is hidden in the item table and specifically a hundred percent defined specifically there in a way that makes it easy to refer to it just with its ID.', 'start': 2138.795, 'duration': 11.509}, {'end': 2155.989, 'text': "And the reason we're doing all this is if I were to go and list color, size or anything else,", 'start': 2150.644, 'duration': 5.345}], 'summary': 'Simulating picking up a quantity of an item with discount and tax rate for easy reference by id.', 'duration': 24.6, 'max_score': 2131.389, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To2131389.jpg'}, {'end': 2487.911, 'src': 'heatmap', 'start': 2392.981, 'weight': 0, 'content': [{'end': 2395.543, 'text': "We're also going to be able to create an index.", 'start': 2392.981, 'duration': 2.562}, {'end': 2401.146, 'text': "And let's say we want to create an index based off of a single column.", 'start': 2396.303, 'duration': 4.843}, {'end': 2412.162, 'text': 'We can say create index transaction ID on transaction name.', 'start': 2401.618, 'duration': 10.544}, {'end': 2413.582, 'text': 'Create that.', 'start': 2413.002, 'duration': 0.58}, {'end': 2418.224, 'text': 'We could also come in and create an index based off of two columns.', 'start': 2414.043, 'duration': 4.181}, {'end': 2426.447, 'text': "So let's change this to transaction ID to on and then say transaction name as well as payment type.", 'start': 2418.624, 'duration': 7.823}, {'end': 2427.467, 'text': 'Execute that.', 'start': 2426.787, 'duration': 0.68}, {'end': 2428.828, 'text': 'And we created another one.', 'start': 2427.788, 'duration': 1.04}, {'end': 2438.373, 'text': "We'd also be able to come in here and delete data that is in a table just by saying truncate table transaction.", 'start': 2429.226, 'duration': 9.147}, {'end': 2440.435, 'text': "There's nothing in it right now, but that doesn't matter.", 'start': 2438.393, 'duration': 2.042}, {'end': 2441.336, 'text': "That'll work.", 'start': 2440.455, 'duration': 0.881}, {'end': 2448.742, 'text': 'And then finally, we could drop the table all together just by saying drop table transaction.', 'start': 2441.716, 'duration': 7.026}, {'end': 2449.682, 'text': "And it's gone.", 'start': 2449.182, 'duration': 0.5}, {'end': 2450.703, 'text': 'You can see over here.', 'start': 2449.702, 'duration': 1.001}, {'end': 2451.604, 'text': 'All right.', 'start': 2450.723, 'duration': 0.881}, {'end': 2452.773, 'text': 'So there you go.', 'start': 2452.031, 'duration': 0.742}, {'end': 2457.222, 'text': "There's a whole bunch of different ways in which we can alter columns and tables.", 'start': 2452.813, 'duration': 4.409}, {'end': 2461.131, 'text': "And now I'm going to show you how to alter tables inside the workbench.", 'start': 2457.984, 'duration': 3.147}, {'end': 2466.216, 'text': "Now you're also going to be able to view, as well as alter tables,", 'start': 2461.573, 'duration': 4.643}, {'end': 2472.961, 'text': 'just by coming over to your table and right clicking on it and then coming down to where it says alter table.', 'start': 2466.216, 'duration': 6.745}, {'end': 2477.744, 'text': 'You can see then that it gives you all the information about your table in this situation.', 'start': 2473.301, 'duration': 4.443}, {'end': 2479.005, 'text': "It's our customer table.", 'start': 2477.784, 'duration': 1.221}, {'end': 2483.768, 'text': 'You can change the name just by clicking on it and then typing in a different name.', 'start': 2479.485, 'duration': 4.283}, {'end': 2487.911, 'text': 'You can change all of the different constraints as well as the data types.', 'start': 2484.228, 'duration': 3.683}], 'summary': 'Demonstrates creating and altering indexes and tables in a database through sql commands, including truncating and dropping tables.', 'duration': 94.93, 'max_score': 2392.981, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To2392981.jpg'}, {'end': 2566.066, 'src': 'embed', 'start': 2541.552, 'weight': 1, 'content': [{'end': 2547.635, 'text': "I'm going to first off go in here and insert some information into our product type table.", 'start': 2541.552, 'duration': 6.083}, {'end': 2555.018, 'text': 'To do so, you just go insert into whatever your name is for the table you want to insert into.', 'start': 2548.295, 'duration': 6.723}, {'end': 2556.419, 'text': 'This is going to be product type.', 'start': 2555.038, 'duration': 1.381}, {'end': 2561.284, 'text': "You're then going to list the pieces of data you want to enter by their name.", 'start': 2556.803, 'duration': 4.481}, {'end': 2564.165, 'text': "So I'm going to say I want to enter name and ID.", 'start': 2561.304, 'duration': 2.861}, {'end': 2566.066, 'text': 'Follow that up with values.', 'start': 2564.465, 'duration': 1.601}], 'summary': 'Inserting information into product type table with name and id.', 'duration': 24.514, 'max_score': 2541.552, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To2541552.jpg'}], 'start': 2012.265, 'title': 'Database management', 'summary': 'Covers setting up sales orders and items, defining attributes and relationships, and simulating sales item information entry, as well as altering database tables, including adding, modifying, and dropping columns, renaming tables, creating indexes, and manipulating data, with practical examples.', 'chapters': [{'end': 2155.989, 'start': 2012.265, 'title': 'Setting up sales order and sales items', 'summary': 'Describes the process of setting up sales order and sales items in a database, including defining their attributes and relationships, along with simulating the entry of sales item information.', 'duration': 143.724, 'highlights': ['The chapter describes the process of setting up sales order and sales items in a database It outlines the steps involved in setting up sales orders and sales items, including defining their attributes and relationships.', 'Defining attributes and relationships of sales order and sales items The transcript details the attributes and relationships of sales orders and sales items, such as credit card details, purchase order number, and the relationship between product types and individual products.', 'Simulating the entry of sales item information It explains the simulation of entering sales item information, including the process of linking each item to the order using the sales order ID and defining the item in a way that makes it easy to refer to it by its ID.']}, {'end': 2678.784, 'start': 2156.38, 'title': 'Database table alterations and data manipulation', 'summary': 'Explains how to alter database tables, including adding, modifying, and dropping columns, renaming tables, creating indexes, and manipulating data, and demonstrates inserting data into tables with examples.', 'duration': 522.404, 'highlights': ['The chapter explains how to alter database tables, including adding, modifying, and dropping columns, renaming tables, creating indexes, and manipulating data. The process of altering database tables is explained, covering various operations such as adding, modifying, and dropping columns, renaming tables, and creating indexes.', 'Inserting data into tables is demonstrated with examples, including listing the column names and inserting data in order without listing the column names. The demonstration includes inserting data into tables, showing examples of listing the column names and inserting data in order without listing the column names.']}], 'duration': 666.519, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To2012265.jpg', 'highlights': ['The chapter covers setting up sales orders and items in a database.', 'Explains altering database tables: adding, modifying, and dropping columns, renaming tables, creating indexes, and manipulating data.', 'Describes simulating the entry of sales item information, including linking items to orders and defining items for easy reference.', 'Details defining attributes and relationships of sales orders and items, such as credit card details and purchase order number.']}, {'end': 3814.275, 'segs': [{'end': 2881.009, 'src': 'embed', 'start': 2848.717, 'weight': 0, 'content': [{'end': 2852.44, 'text': 'So purchase order number, which was an integer right here.', 'start': 2848.717, 'duration': 3.723}, {'end': 2856.543, 'text': "I'm going to make it into a big int instead that's unsigned and not null.", 'start': 2852.46, 'duration': 4.083}, {'end': 2857.644, 'text': 'Execute that.', 'start': 2856.903, 'duration': 0.741}, {'end': 2863.834, 'text': "You can see down here response is where there would be an error message if there was an error, but there isn't.", 'start': 2858.049, 'duration': 5.785}, {'end': 2866.516, 'text': 'So we come in here and we can refresh this.', 'start': 2864.114, 'duration': 2.402}, {'end': 2871.7, 'text': "And now you can see it's a big int and it's going to be able to hold my gigantic purchase order numbers.", 'start': 2866.896, 'duration': 4.804}, {'end': 2881.009, 'text': 'Had an other error here also with my sales order in which I was trying to auto-generate the secret codes.', 'start': 2872.24, 'duration': 8.769}], 'summary': 'Changed purchase order number to big int, resolved error in sales order code generation.', 'duration': 32.292, 'max_score': 2848.717, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To2848717.jpg'}, {'end': 3522.612, 'src': 'embed', 'start': 3489.551, 'weight': 2, 'content': [{'end': 3494.095, 'text': "And to do that, we'll say we would do all of our conditions inside of where clauses.", 'start': 3489.551, 'duration': 4.544}, {'end': 3498.539, 'text': "So we'll say state not equal to California.", 'start': 3494.155, 'duration': 4.384}, {'end': 3502.682, 'text': 'Run it, and then we just get all of our states excluding California.', 'start': 3499.059, 'duration': 3.623}, {'end': 3507.6, 'text': 'The in phrase can also be used to test if a value is inside of a list.', 'start': 3503.197, 'duration': 4.403}, {'end': 3522.612, 'text': "So we have select distinct states from customer and then we'll say where state in and we'll say that we just want those states that match with either California or New Jersey.", 'start': 3507.961, 'duration': 14.651}], 'summary': 'Using where clauses to exclude california and select states in a list.', 'duration': 33.061, 'max_score': 3489.551, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To3489551.jpg'}, {'end': 3667.305, 'src': 'embed', 'start': 3640.647, 'weight': 1, 'content': [{'end': 3649.137, 'text': 'And in this situation where we join tables while checking for equality between a common column, this is called an equal join.', 'start': 3640.647, 'duration': 8.49}, {'end': 3654.159, 'text': "We're also going to be able to define multiple different join conditions with logical operators.", 'start': 3649.457, 'duration': 4.702}, {'end': 3660.222, 'text': 'So we have item ID and price from our item table and our sales table.', 'start': 3654.559, 'duration': 5.663}, {'end': 3667.305, 'text': "Let's say we wanted to come in here and keep this part right here where we're joining using the foreign key and the primary key.", 'start': 3660.622, 'duration': 6.683}], 'summary': 'Equal joins are used to combine tables based on common columns; multiple join conditions can be defined with logical operators.', 'duration': 26.658, 'max_score': 3640.647, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To3640647.jpg'}, {'end': 3762.941, 'src': 'embed', 'start': 3685.244, 'weight': 3, 'content': [{'end': 3689.466, 'text': "So let's say we wanted to get the orders quantity and the total sale.", 'start': 3685.244, 'duration': 4.222}, {'end': 3696.449, 'text': "So we could say select sales order and we'll get our ID.", 'start': 3689.926, 'duration': 6.523}, {'end': 3701.632, 'text': 'Follow that up with sales item quantity.', 'start': 3696.829, 'duration': 4.803}, {'end': 3706.413, 'text': 'and the individual item price,', 'start': 3703.19, 'duration': 3.223}, {'end': 3720.125, 'text': "and then we want to say sales item quantity times the item price and we'll go and create an alias which is going to be total,", 'start': 3706.413, 'duration': 13.712}, {'end': 3730.659, 'text': "and we'll say that we want to get that from the sales order table, which we will join, to our sales item table.", 'start': 3720.125, 'duration': 10.534}, {'end': 3736.44, 'text': 'And the condition is going to be that our sales item,', 'start': 3731.24, 'duration': 5.2}, {'end': 3750.243, 'text': 'sales order ID is equal to oops I accidentally put a space inside of there is going to be equal to our sales order ID.', 'start': 3736.44, 'duration': 13.803}, {'end': 3751.623, 'text': 'And then we can do another join.', 'start': 3750.543, 'duration': 1.08}, {'end': 3762.941, 'text': "And we'll say join item on item ID equal to sales item item ID.", 'start': 3752.318, 'duration': 10.623}], 'summary': 'Retrieve orders quantity and total sale by joining sales order and sales item tables.', 'duration': 77.697, 'max_score': 3685.244, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To3685244.jpg'}], 'start': 2678.784, 'title': 'Sql commands and data retrieval', 'summary': 'Covers inputting and querying data using sql commands, including inserting and altering data, querying with conditional and logical operators, using sql commands for data analysis, and retrieving data from multiple tables using joins, unions, and various operators.', 'chapters': [{'end': 3159.565, 'start': 2678.784, 'title': 'Database data input and querying', 'summary': 'Covers the process of inputting data into a database using sql commands, including inserting product, customer, salesperson, and sales item data, and altering table data types to accommodate larger numbers. it also demonstrates querying data using conditional and logical operators, such as selecting sales items with a discount greater than 15%.', 'duration': 480.781, 'highlights': ['The process of inputting data into a database using SQL commands, including inserting product, customer, salesperson, and sales item data. The speaker demonstrates the process of inputting various types of data into the database, such as product, customer, salesperson, and sales item data, through SQL commands, ensuring the proper formatting and execution of the commands.', 'Altering table data types to accommodate larger numbers, such as changing the purchase order number and credit card secret code to big integer and small integer respectively. The speaker explains the need to alter table data types to accommodate larger numbers, demonstrating the process by changing the purchase order number to a big integer and the credit card secret code to a small integer, ensuring the successful execution of the alterations.', 'Querying data using conditional and logical operators, such as selecting sales items with a discount greater than 15%. The chapter demonstrates how to query data using conditional and logical operators, specifically showcasing the process of selecting sales items with a discount greater than 15%, and executing the query to view the results.']}, {'end': 3529.377, 'start': 3160.065, 'title': 'Using sql commands for data analysis', 'summary': 'Explains how to use sql commands such as select, where, between, order by, limit, concat, as, sum, distinct, and in to manipulate and analyze data, including examples of filtering, sorting, and aggregating data.', 'duration': 369.312, 'highlights': ['Explaining the use of SQL commands such as SELECT, WHERE, BETWEEN, ORDER BY, LIMIT, CONCAT, AS, SUM, DISTINCT, and IN The chapter extensively covers various SQL commands for data manipulation and analysis, including SELECT, WHERE, BETWEEN, ORDER BY, LIMIT, CONCAT, AS, SUM, DISTINCT, and IN.', 'Demonstrating filtering and sorting using WHERE, ORDER BY, and DISTINCT The chapter provides examples of filtering and sorting data using WHERE, ORDER BY, and DISTINCT, such as finding orders in December 2018 and listing states where customers are located.', 'Illustrating data aggregation using SUM and LIMIT Examples of data aggregation using SUM and LIMIT are given, including calculating the total value of business shoes and retrieving the top discounts.', "Showcasing data combination and aliasing with CONCAT and AS The usage of data combination and aliasing with CONCAT and AS is demonstrated, including combining first name and last name and aliasing the output as 'name'."]}, {'end': 3814.275, 'start': 3529.887, 'title': 'Using queries to retrieve data from multiple tables', 'summary': 'Discusses using inner joins, outer joins, and unions to retrieve data from multiple tables, demonstrating examples of joining two and three tables while highlighting the use of primary and foreign keys, logical operators, and arithmetic operators in sql.', 'duration': 284.388, 'highlights': ['The chapter covers using inner joins, outer joins, and unions to retrieve data from multiple tables, with a focus on the most common join, the inner join.', 'It demonstrates joining two tables using primary and foreign keys as well as ordering the results.', 'The chapter also shows how to join three tables to retrieve specific information and calculates the total sale using arithmetic operators.', 'It explains the use of logical operators and equal joins in defining multiple join conditions.']}], 'duration': 1135.491, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To2678784.jpg', 'highlights': ['Covers inputting and querying data using SQL commands', 'Explaining the use of SQL commands such as SELECT, WHERE, BETWEEN, ORDER BY, LIMIT, CONCAT, AS, SUM, DISTINCT, and IN', 'Querying data using conditional and logical operators, such as selecting sales items with a discount greater than 15%', 'Illustrating data aggregation using SUM and LIMIT', 'Showcasing data combination and aliasing with CONCAT and AS', 'The chapter covers using inner joins, outer joins, and unions to retrieve data from multiple tables']}, {'end': 4808.388, 'segs': [{'end': 3849.748, 'src': 'embed', 'start': 3814.295, 'weight': 4, 'content': [{'end': 3821.741, 'text': "Another thing is going to be available to you, but it's kind of frowned upon and it's you're not supposed to do it, but you can do it.", 'start': 3814.295, 'duration': 7.446}, {'end': 3829.528, 'text': "So I'm going to show you is actually defining the on condition with where instead of on.", 'start': 3821.761, 'duration': 7.767}, {'end': 3833.011, 'text': 'So you could actually come in and go select item and price.', 'start': 3829.968, 'duration': 3.043}, {'end': 3849.748, 'text': 'from our item table as well as our sales items table, and then you could say where item ID is equal to sales item dot,', 'start': 3833.581, 'duration': 16.167}], 'summary': "You can define on condition with where, but it's frowned upon.", 'duration': 35.453, 'max_score': 3814.295, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To3814295.jpg'}, {'end': 4234.664, 'src': 'embed', 'start': 4205.075, 'weight': 1, 'content': [{'end': 4208.938, 'text': "Let's say I wanted to find any customers whose name begins with an M.", 'start': 4205.075, 'duration': 3.863}, {'end': 4231.543, 'text': 'So I can say first name and last name from customer and then where first name like and then follow that up with M and then I can put a percent sign inside of there and that is going to give me my matches.', 'start': 4209.518, 'duration': 22.025}, {'end': 4234.664, 'text': 'And you can see there is a good number of them.', 'start': 4232.203, 'duration': 2.461}], 'summary': "Query for customers with names starting with 'm' returned a good number of matches.", 'duration': 29.589, 'max_score': 4205.075, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To4205075.jpg'}, {'end': 4532.537, 'src': 'embed', 'start': 4504.978, 'weight': 2, 'content': [{'end': 4508.241, 'text': "And you're going to get the same type of results, but just wanted to run that.", 'start': 4504.978, 'duration': 3.263}, {'end': 4512.245, 'text': 'So you can combine all those different little tools and patterns.', 'start': 4508.682, 'duration': 3.563}, {'end': 4522.195, 'text': 'And you could also could come in here and say that you wanted to match for last names that contain letters W through Z, for example.', 'start': 4512.906, 'duration': 9.289}, {'end': 4525.2, 'text': 'put those in brackets and there you go.', 'start': 4522.915, 'duration': 2.285}, {'end': 4531.234, 'text': "And you could do a last names that have at least two L's inside of them.", 'start': 4526.062, 'duration': 5.172}, {'end': 4532.537, 'text': 'So go like this.', 'start': 4531.375, 'duration': 1.162}], 'summary': 'Combining tools and patterns to match specific last names with certain criteria.', 'duration': 27.559, 'max_score': 4504.978, 'thumbnail': ''}, {'end': 4686.246, 'src': 'embed', 'start': 4657.034, 'weight': 0, 'content': [{'end': 4664.457, 'text': "But let's say we wanted to find those months that had at least two or more customers with a birthday in them.", 'start': 4657.034, 'duration': 7.423}, {'end': 4670.199, 'text': 'Well to do that we have another command called halving and you have to put halving after group by.', 'start': 4664.877, 'duration': 5.322}, {'end': 4678.082, 'text': 'So we could say something like having amount greater than one and run it.', 'start': 4670.939, 'duration': 7.143}, {'end': 4686.246, 'text': "And now you can see it's only giving you the results where we have two or more people having birthdays in individual months.", 'start': 4678.542, 'duration': 7.704}], 'summary': "Using the 'having' command after 'group by' helps identify months with at least two customers with birthdays.", 'duration': 29.212, 'max_score': 4657.034, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To4657034.jpg'}], 'start': 3814.295, 'title': 'Sql joins, unions, like, and regular expressions in mysql', 'summary': 'Covers inner joins, left outer joins, cross joins, and unions as well as the usage of like and regular expressions in sql. it also explains the basics of regular expressions and demonstrates their application in mysql, including grouping, counting records, and using aggregate functions.', 'chapters': [{'end': 4138.808, 'start': 3814.295, 'title': 'Sql joins and unions', 'summary': 'Covers inner joins, left outer joins, cross joins, and unions in sql, with examples and recommendations provided.', 'duration': 324.513, 'highlights': ["The chapter explains inner joins, which can be defined using the 'where' condition instead of 'on', allowing the retrieval of specific data from multiple tables, such as selecting items and prices where the item ID is equal to sales item dot item ID and the price is greater than 120.", 'It discusses left outer joins, emphasizing their common usage over right outer joins, with an example of using a left join to retrieve data from the product and item tables based on a specified condition, and ordering the results by name.', 'Explaining cross joins, it demonstrates how a cross join includes data from each row in both tables, producing a large set of results, with an example of selecting sales order ID and quantity from the item table, cross joined with the sales item table, and ordering the results by sales order ID.', 'Lastly, the chapter covers unions, highlighting the ability to combine the results of multiple select statements with the requirement that each result must return the same number of columns and data in each column, and the data in each column must have the same data type, illustrated through an example of combining customer and salesperson data for December birthdays.']}, {'end': 4345.874, 'start': 4138.828, 'title': 'Sql like and regular expressions', 'summary': 'Covers the usage of like and regular expressions in sql, including examples of using like to find string matches and how to use null and regular expressions in queries.', 'duration': 207.046, 'highlights': ['The usage of like to find string matches and checking for null in SQL queries The chapter explains how to use the like operator to find string matches in SQL queries, including using percent sign for zero or more characters and underscore for any single character. It also covers how to check for null values in SQL queries.', 'Examples of using like to find string matches based on specific patterns The chapter provides examples of using like to find customers whose names begin with a specific letter or match a certain pattern, demonstrating the practical application of like operator in SQL queries.', 'Introduction to regular expressions in SQL and its complexity The chapter introduces regular expressions in SQL, highlighting their complexity and the availability of an entire tutorial dedicated to regular expressions. It also mentions providing an overview and examples of regular expression patterns.']}, {'end': 4531.234, 'start': 4346.275, 'title': 'Regular expressions basics', 'summary': 'Explains the basics of regular expressions, including the use of characters like period, star, plus, caret, dollar sign, and brackets, as well as examples of matching patterns for first names and last names, showcasing the versatility and applications of regular expressions.', 'duration': 184.959, 'highlights': ['The caret symbol representing the beginning of a string match, and the dollar sign representing the end, allowing for precise string matching patterns.', "The use of curly brackets and N to match N instances of a character, enabling specific quantity-based matching like matching for two L's in a word.", "The demonstration of practical examples such as matching for first names starting with 'MA' and last names containing specific letters like 'EZ' or 'SON', showcasing the versatility and applications of regular expressions.", 'The explanation of using brackets to match a range of letters, exemplifying the flexibility of regular expressions to match specific letter combinations within last names.', 'The introduction of the or symbol to match for one thing or another, providing a flexible approach to pattern matching using regular expressions.']}, {'end': 4808.388, 'start': 4531.375, 'title': 'Using regular expressions in mysql', 'summary': "Covers using regular expressions in mysql to summarize results, including examples of grouping and counting records, as well as demonstrating the use of 'having' command and aggregate built-in functions.", 'duration': 277.013, 'highlights': ["The chapter covers using regular expressions in MySQL to summarize results, including examples of grouping and counting records, as well as demonstrating the use of 'having' command and aggregate built-in functions. The chapter provides an overview of using regular expressions in MySQL to summarize results, including grouping by unique values and counting records. It also demonstrates the use of 'having' command to filter results and showcases various aggregate built-in functions such as average, rounding, trigonometric functions, and conversion functions.", "The tutorial shows how to use group by to return a single row for each unique value, and provides an example of counting the individual amounts. The tutorial demonstrates using group by to return a single row for each unique value and provides an example of counting the individual amounts using the 'count' function, showcasing how to display the total number of records that match.", "The tutorial explains the use of the 'having' command to filter results, showing an example of filtering months with at least two or more customers having birthdays. The tutorial explains the use of the 'having' command to filter results, demonstrating an example of filtering months with at least two or more customers having birthdays, allowing for the display of results that meet specific criteria."]}], 'duration': 994.093, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To3814295.jpg', 'highlights': ["The chapter covers using regular expressions in MySQL to summarize results, including examples of grouping and counting records, as well as demonstrating the use of 'having' command and aggregate built-in functions.", "The chapter explains inner joins, which can be defined using the 'where' condition instead of 'on', allowing the retrieval of specific data from multiple tables, such as selecting items and prices where the item ID is equal to sales item dot item ID and the price is greater than 120.", 'The usage of like to find string matches and checking for null in SQL queries The chapter explains how to use the like operator to find string matches in SQL queries, including using percent sign for zero or more characters and underscore for any single character.', 'The chapter introduces regular expressions in SQL, highlighting their complexity and the availability of an entire tutorial dedicated to regular expressions. It also mentions providing an overview and examples of regular expression patterns.', 'The tutorial shows how to use group by to return a single row for each unique value, and provides an example of counting the individual amounts.']}, {'end': 5894.271, 'segs': [{'end': 5309.155, 'src': 'embed', 'start': 5276.768, 'weight': 1, 'content': [{'end': 5279.872, 'text': "And now you're going to see over in views that there is a view.", 'start': 5276.768, 'duration': 3.104}, {'end': 5281.413, 'text': 'And there it is right there.', 'start': 5280.292, 'duration': 1.121}, {'end': 5285.918, 'text': "You're also going to be able to see our different results here if we go and look at it.", 'start': 5281.794, 'duration': 4.124}, {'end': 5289.182, 'text': "And how we're going to do that is.", 'start': 5286.42, 'duration': 2.762}, {'end': 5296.327, 'text': "let's just select all of this stuff and to see our purchase order overview, which is going to be stored in the database.", 'start': 5289.182, 'duration': 7.145}, {'end': 5301.37, 'text': "And it's going to update also as other parts of the database update on its own.", 'start': 5296.527, 'duration': 4.843}, {'end': 5309.155, 'text': "So I'm going to say select everything from the purchase order overview.", 'start': 5301.39, 'duration': 7.765}], 'summary': 'Demonstration of viewing purchase order overview in database.', 'duration': 32.387, 'max_score': 5276.768, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To5276768.jpg'}, {'end': 5391.803, 'src': 'embed', 'start': 5364.257, 'weight': 0, 'content': [{'end': 5368.561, 'text': "It can't have any aggregate functions in it, nor can it have group by or having.", 'start': 5364.257, 'duration': 4.304}, {'end': 5375.506, 'text': 'So knowing that I need to change my view that I had previously, and this would be the new format of it.', 'start': 5368.901, 'duration': 6.605}, {'end': 5384.393, 'text': "Basically what I had to change here about this is I had to remove the aggregate functions cause I didn't have any of the other issues.", 'start': 5375.706, 'duration': 8.687}, {'end': 5388.418, 'text': 'So that means I have to get rid of concat and all that stuff.', 'start': 5384.873, 'duration': 3.545}, {'end': 5391.803, 'text': "Let's just come in here and go sales F name.", 'start': 5388.498, 'duration': 3.305}], 'summary': 'The new format of the view does not include aggregate functions or group by, requiring the removal of concat and other related functions.', 'duration': 27.546, 'max_score': 5364.257, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To5364257.jpg'}, {'end': 5873.019, 'src': 'embed', 'start': 5816.873, 'weight': 2, 'content': [{'end': 5818.834, 'text': 'And then the condition,', 'start': 5816.873, 'duration': 1.961}, {'end': 5828.338, 'text': 'which is going to be that the supplier is going to be equal to the supplier that they passed into the stored procedure up here.', 'start': 5818.834, 'duration': 9.504}, {'end': 5829.638, 'text': 'See? This guy right there.', 'start': 5828.418, 'duration': 1.22}, {'end': 5830.659, 'text': 'And there it is.', 'start': 5830.039, 'duration': 0.62}, {'end': 5834.258, 'text': 'And then after that, I can output this information.', 'start': 5831.195, 'duration': 3.063}, {'end': 5839.222, 'text': 'And how you output information from a stored procedure is with using select.', 'start': 5834.758, 'duration': 4.464}, {'end': 5842.684, 'text': 'So I can concatenate or join information.', 'start': 5839.642, 'duration': 3.042}, {'end': 5848.209, 'text': "So I'll say supplier name and then separate everything with commas.", 'start': 5842.724, 'duration': 5.485}, {'end': 5853.233, 'text': "And I'll say inventory value dollar sign.", 'start': 5848.789, 'duration': 4.444}, {'end': 5857.473, 'text': 'And then after that, I can say price sum.', 'start': 5853.772, 'duration': 3.701}, {'end': 5862.855, 'text': 'And then if I want to give it a custom title as output, for example.', 'start': 5857.953, 'duration': 4.902}, {'end': 5865.516, 'text': "And that's everything I need to do.", 'start': 5863.355, 'duration': 2.161}, {'end': 5866.436, 'text': 'Oh, actually, no.', 'start': 5865.596, 'duration': 0.84}, {'end': 5868.337, 'text': "I'm going to go get supplier value.", 'start': 5866.596, 'duration': 1.741}, {'end': 5873.019, 'text': "So I'll get all the values for whatever I throw inside of here.", 'start': 5869.037, 'duration': 3.982}], 'summary': 'The code processes supplier information and outputs it using stored procedures and select statements.', 'duration': 56.146, 'max_score': 5816.873, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To5816873.jpg'}], 'start': 4808.548, 'title': 'Working with views, managing views, and stored programs', 'summary': 'Covers using aggregate functions to obtain inventory statistics, including a total inventory value of $7,231.58, creating and managing views for purchase order information, and creating stored programs such as procedures and functions to retrieve data and calculate inventory value.', 'chapters': [{'end': 5245.133, 'start': 4808.548, 'title': 'Aggregate functions & working with views', 'summary': 'Covers the usage of aggregate functions to obtain inventory statistics, including the total inventory value of $7,231.58, and creating views to store purchase order information by combining data from multiple tables.', 'duration': 436.585, 'highlights': ['Aggregate functions used to obtain inventory statistics Summing the prices of all items in the database yields a total inventory value of $7,231.58, and other aggregate functions such as counting items, calculating average price, and finding maximum and minimum prices are also demonstrated.', "Creation of a view to store purchase order information A view named 'Purchase Order Overview' is created to combine data from various tables including sales order, sales item, item, customer, product, and salesperson, to store purchase order information such as purchase order number, company, quantity, product supplier, individual product name, item's price, and total."]}, {'end': 5464.628, 'start': 5246.328, 'title': 'Creating and managing views', 'summary': 'Covers the process of creating and managing views, including storing data, updating views automatically, dropping views, and the benefits of using views to hide or change data from users. it also explains the limitations for making a view updatable and provides an example of modifying a view to meet the requirements.', 'duration': 218.3, 'highlights': ['Creating and storing views in the database The process involves running a query to store data in a view, which can be refreshed and updated automatically as other parts of the database change.', 'Dropping views and their benefits The ability to drop a view if no longer needed and the benefit of using views to hide or change data from unauthorized users.', 'Limitations for making a view updatable The restrictions include not using distinct union, aggregate functions, group by, or having in the view.', 'Example of modifying a view to meet updatable requirements An example is provided where aggregate functions are removed from a view to make it updatable, along with the process of dropping the old view and creating the new one.']}, {'end': 5894.271, 'start': 5464.648, 'title': 'Stored programs: creation and examples', 'summary': 'Discusses the creation and examples of stored programs, including stored procedures, functions, triggers, and events, with a focus on creating a stored procedure to retrieve customer company names and another to calculate inventory value based on supplier, showcasing the use of variables, queries, and output. it concludes with the successful execution of the examples.', 'duration': 429.623, 'highlights': ['Creation of stored procedures and functions The chapter explains the creation of stored procedures and functions, illustrating the ability to execute stored procedures with applications like PHP and stored functions using SQL queries.', 'Types of stored programs: Triggers and events The chapter introduces triggers that execute on insert, update, or delete commands and events that execute at scheduled times, expanding the understanding of different types of stored programs.', 'Demonstration of creating a stored procedure to retrieve customer company names A detailed demonstration is provided on how to create a stored procedure to retrieve customer company names, including defining the database, handling delimiters, and executing the stored procedure to obtain the desired results.', 'Creation of a procedure to calculate inventory value based on supplier The chapter showcases the creation of a procedure to calculate inventory value based on a supplier, emphasizing the declaration of variables, issuing queries, and using select statements to output the calculated inventory value.']}], 'duration': 1085.723, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To4808548.jpg', 'highlights': ["Creation of a view to store purchase order information A view named 'Purchase Order Overview' is created to combine data from various tables including sales order, sales item, item, customer, product, and salesperson, to store purchase order information such as purchase order number, company, quantity, product supplier, individual product name, item's price, and total.", 'Aggregate functions used to obtain inventory statistics Summing the prices of all items in the database yields a total inventory value of $7,231.58, and other aggregate functions such as counting items, calculating average price, and finding maximum and minimum prices are also demonstrated.', 'Creation of stored procedures and functions The chapter explains the creation of stored procedures and functions, illustrating the ability to execute stored procedures with applications like PHP and stored functions using SQL queries.', 'Types of stored programs: Triggers and events The chapter introduces triggers that execute on insert, update, or delete commands and events that execute at scheduled times, expanding the understanding of different types of stored programs.']}, {'end': 6901.093, 'segs': [{'end': 6049.048, 'src': 'embed', 'start': 6024.311, 'weight': 0, 'content': [{'end': 6030.115, 'text': "And we're going to be taking this from the customer database, of course.", 'start': 6024.311, 'duration': 5.804}, {'end': 6032.297, 'text': 'So from customer.', 'start': 6030.175, 'duration': 2.122}, {'end': 6039.982, 'text': "And then we'll say where month birth date is equal to.", 'start': 6032.317, 'duration': 7.665}, {'end': 6042.143, 'text': 'the month that they passed in.', 'start': 6040.842, 'duration': 1.301}, {'end': 6049.048, 'text': "So they'll be able to pass in a month and it'll automatically send you back all the customers who have a birth date in that month.", 'start': 6042.223, 'duration': 6.825}], 'summary': 'Creating a query to retrieve customers based on birth month from the database.', 'duration': 24.737, 'max_score': 6024.311, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6024311.jpg'}, {'end': 6141.922, 'src': 'embed', 'start': 6111.892, 'weight': 8, 'content': [{'end': 6117.68, 'text': "Another thing I don't know if I even showed you is if we go and refresh all under the stored procedures,", 'start': 6111.892, 'duration': 5.788}, {'end': 6125.47, 'text': "you're going to see all of those procedures that we have created so far, all stored inside of the database, as well as the view that we had before.", 'start': 6117.68, 'duration': 7.79}, {'end': 6126.712, 'text': 'So pretty cool stuff.', 'start': 6125.65, 'duration': 1.062}, {'end': 6131.655, 'text': 'Now what I want to do is go and demonstrate if and else if and else.', 'start': 6126.992, 'duration': 4.663}, {'end': 6132.656, 'text': 'All right.', 'start': 6132.195, 'duration': 0.461}, {'end': 6141.922, 'text': "So what we're going to do here is we are going to check for our monthly orders and to see if we are performing well or not.", 'start': 6132.716, 'duration': 9.206}], 'summary': 'Demonstrating stored procedures and checking monthly orders for performance.', 'duration': 30.03, 'max_score': 6111.892, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6111892.jpg'}, {'end': 6437.112, 'src': 'embed', 'start': 6402.586, 'weight': 3, 'content': [{'end': 6416.438, 'text': "Again, do another win and we'll say total orders equal to five then and do another one inside of here and we'll say that this is on target.", 'start': 6402.586, 'duration': 13.852}, {'end': 6424.906, 'text': "And then finally, as a default, we'll say else and paste in another one again.", 'start': 6418.48, 'duration': 6.426}, {'end': 6427.568, 'text': "And in this situation, we'll say doing good.", 'start': 6425.446, 'duration': 2.122}, {'end': 6431.228, 'text': 'as a default.', 'start': 6430.487, 'duration': 0.741}, {'end': 6437.112, 'text': 'And then after we have all those done, we have to come in and say end case.', 'start': 6431.788, 'duration': 5.324}], 'summary': 'Total orders equal to five, on target, doing good.', 'duration': 34.526, 'max_score': 6402.586, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6402586.jpg'}, {'end': 6513.759, 'src': 'embed', 'start': 6482.714, 'weight': 4, 'content': [{'end': 6484.515, 'text': "So I'm going to show you the while loop first.", 'start': 6482.714, 'duration': 1.801}, {'end': 6487.135, 'text': "And I'm going to call this loop test.", 'start': 6485.035, 'duration': 2.1}, {'end': 6490.536, 'text': 'And of course, change this to loop test.', 'start': 6488.275, 'duration': 2.261}, {'end': 6494.397, 'text': 'And this is going to receive a value.', 'start': 6491.876, 'duration': 2.521}, {'end': 6495.337, 'text': "I'm just going to call it i.", 'start': 6494.457, 'duration': 0.88}, {'end': 6500.569, 'text': "And then down inside of begin, I'm going to declare a couple variables.", 'start': 6496.365, 'duration': 4.204}, {'end': 6503.991, 'text': "So I'm going to declare j, which is going to be an integer.", 'start': 6500.609, 'duration': 3.382}, {'end': 6508.655, 'text': "And it's going to have a default starting value of 1.", 'start': 6504.652, 'duration': 4.003}, {'end': 6510.297, 'text': 'Spell declare right, of course.', 'start': 6508.655, 'duration': 1.642}, {'end': 6513.759, 'text': "Then down inside of here, I'm going to declare another one.", 'start': 6511.137, 'duration': 2.622}], 'summary': 'Demonstrating a while loop and variable declaration for i and j.', 'duration': 31.045, 'max_score': 6482.714, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6482714.jpg'}, {'end': 6709.186, 'src': 'embed', 'start': 6683.645, 'weight': 2, 'content': [{'end': 6697.317, 'text': "first thing I'm going to do inside of begin is say declare finished, And this will be integer default zero, which is going to stand for false.", 'start': 6683.645, 'duration': 13.672}, {'end': 6700.199, 'text': 'And whenever this is set to the value of one,', 'start': 6697.977, 'duration': 2.222}, {'end': 6705.183, 'text': "we know that we've reached the end of the list and we know that we can just output the information at that point.", 'start': 6700.199, 'duration': 4.984}, {'end': 6709.186, 'text': 'Also going to declare company name.', 'start': 6705.784, 'duration': 3.402}], 'summary': "Initializing 'finished' variable as integer 0, indicating end of list. also declaring company name.", 'duration': 25.541, 'max_score': 6683.645, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6683645.jpg'}, {'end': 6901.093, 'src': 'embed', 'start': 6867.587, 'weight': 5, 'content': [{'end': 6877.651, 'text': "So first off, we're going to say set at company list and equal to nothing in the beginning, of course.", 'start': 6867.587, 'duration': 10.064}, {'end': 6886.555, 'text': "Then we'll call get company or companies is what I called it, right? Get companies.", 'start': 6878.092, 'duration': 8.463}, {'end': 6887.816, 'text': 'Yep, there it is.', 'start': 6886.996, 'duration': 0.82}, {'end': 6889.797, 'text': 'And go like this.', 'start': 6888.676, 'duration': 1.121}, {'end': 6894.031, 'text': "And what we'll pass into it is our company list, which is going to be populated.", 'start': 6890.41, 'duration': 3.621}, {'end': 6901.093, 'text': 'And then after we have all that done, we can just say select and output our company list.', 'start': 6895.851, 'duration': 5.242}], 'summary': 'Initializing an empty company list, populating it with data, and selecting it for output.', 'duration': 33.506, 'max_score': 6867.587, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6867587.jpg'}], 'start': 5894.431, 'title': 'Sql procedures and cursors', 'summary': 'Demonstrates creating procedures to retrieve customer data with multiple values, analyzing monthly orders using stored procedures, and iterating through rows using cursors to generate a list of company names.', 'chapters': [{'end': 6132.656, 'start': 5894.431, 'title': 'Using multiple values in procedures', 'summary': 'Demonstrates how to create a procedure to get customer birthdays, using multiple values and out parameters to retrieve information from a customer database, allowing the passing of a month to retrieve customer data, while also showcasing the creation of stored procedures in a database.', 'duration': 238.225, 'highlights': ['The chapter demonstrates how to create a procedure to get customer birthdays It explains the process of creating a procedure to retrieve customer birthdays using multiple values and out parameters.', 'Using multiple values and out parameters to retrieve information from a customer database The chapter shows the usage of multiple values and out parameters to retrieve various customer data such as birth date, first name, last name, and phone number from a customer database.', 'Passing a month to retrieve customer data It demonstrates the capability to pass a month as a parameter to retrieve customer data with birthdays in that specific month from the database.', 'Showcasing the creation of stored procedures in a database The chapter also highlights the creation of stored procedures in a database and how they can be utilized to organize and execute specific tasks efficiently.']}, {'end': 6635.146, 'start': 6132.716, 'title': 'Monthly orders analysis', 'summary': 'Details how to check and analyze monthly orders, using stored procedures to categorize the performance as good, bad, or on target, along with demonstrating the use of loops to calculate the sum of values and comparing while and repeat loops, and introducing the concept of cursors.', 'duration': 502.43, 'highlights': ['The chapter details how to check and analyze monthly orders The transcript explains the process of checking and analyzing monthly orders to determine performance.', 'Using stored procedures to categorize the performance as good, bad, or on target Stored procedures are used to categorize the performance of monthly orders as good, bad, or on target based on a certain threshold, with examples of SQL code provided.', 'Demonstrating the use of loops to calculate the sum of values and comparing while and repeat loops The chapter demonstrates the use of while and repeat loops to calculate the sum of values, providing examples and comparisons between the two loop types.', 'Introducing the concept of cursors An introduction to the concept of cursors is provided, indicating that the chapter covers a broad range of topics related to data analysis and manipulation.']}, {'end': 6901.093, 'start': 6635.627, 'title': 'Using cursors to iterate through rows', 'summary': 'Illustrates the usage of cursors in sql to iterate through rows from the customer table and generate a comma-separated list of company names, demonstrating the declaration of a cursor, error handling, looping, and memory release.', 'duration': 265.466, 'highlights': ['The usage of cursors in SQL to iterate through rows from the customer table and generate a comma-separated list of company names Demonstrates the practical application of cursors in SQL to iterate through rows and work with them individually, resulting in a comma-separated list of company names.', 'Declaration of a cursor, error handling, looping, and memory release Explains the process of declaring a cursor for the customer list, implementing error handling in case no more companies are found, looping through the result set, and releasing the memory assigned for the cursor.']}], 'duration': 1006.662, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To5894431.jpg', 'highlights': ['Using multiple values and out parameters to retrieve customer data', 'Demonstrates creating procedures to retrieve customer data', 'Using stored procedures to categorize the performance of monthly orders', 'Passing a month to retrieve customer data', 'Showcasing the creation of stored procedures in a database', 'Demonstrating the use of loops to calculate the sum of values', 'Introducing the concept of cursors', 'The usage of cursors in SQL to iterate through rows', 'Declaration of a cursor, error handling, looping, and memory release']}, {'end': 8253.673, 'segs': [{'end': 7710.373, 'src': 'embed', 'start': 7683.48, 'weight': 3, 'content': [{'end': 7688.476, 'text': "And what's important here is we have to define the data type that's going to be returned.", 'start': 7683.48, 'duration': 4.996}, {'end': 7691.179, 'text': 'This is going to return a decimal.', 'start': 7689.557, 'duration': 1.622}, {'end': 7694.503, 'text': "So returns decimal, and we can put it on the second line if we'd like.", 'start': 7691.5, 'duration': 3.003}, {'end': 7701.11, 'text': "We're then going to say deterministic reads SQL.", 'start': 7694.963, 'duration': 6.147}, {'end': 7710.373, 'text': 'and deterministic means it returns the same result when the same exact argument is going to be passed inside of it.', 'start': 7703.004, 'duration': 7.369}], 'summary': 'Defining a decimal data type for a deterministic sql function.', 'duration': 26.893, 'max_score': 7683.48, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To7683480.jpg'}, {'end': 7925.466, 'src': 'embed', 'start': 7860.815, 'weight': 0, 'content': [{'end': 7867.361, 'text': "we're going to actually call this from a different query, as long as no error occurs doesn't look like it has.", 'start': 7860.815, 'duration': 6.546}, {'end': 7875.349, 'text': "so let's go over here, do a refresh and we can look over in functions and we can say get order total there, it is right there.", 'start': 7867.361, 'duration': 7.988}, {'end': 7876.85, 'text': 'Then we can come.', 'start': 7875.769, 'duration': 1.081}, {'end': 7878.432, 'text': "let's close this.", 'start': 7876.85, 'duration': 1.582}, {'end': 7881.574, 'text': 'go file new query tab, close that.', 'start': 7878.432, 'duration': 3.142}, {'end': 7882.195, 'text': 'There we are.', 'start': 7881.714, 'duration': 0.481}, {'end': 7902.313, 'text': "And what we're going to be able to do now is do something like select sales order ID, get order total, pass in sales order ID,", 'start': 7882.595, 'duration': 19.718}, {'end': 7920.536, 'text': "display it as order total and then we're going to get this from our sales order table and then order by sales order,", 'start': 7903.868, 'duration': 16.668}, {'end': 7925.466, 'text': 'as long as the function works right and it looks like it did.', 'start': 7922.963, 'duration': 2.503}], 'summary': "Calling 'get order total' function on sales order ids, displaying order total from sales order table, with no errors.", 'duration': 64.651, 'max_score': 7860.815, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To7860815.jpg'}], 'start': 6904.814, 'title': 'Sql error handling and database operations', 'summary': 'Covers error handling in procedures and mysql exception handling, with a focus on creating custom error messages, executing transactions, and demonstrating stored functions and triggers for database operations. it provides examples and practical usage scenarios for handling errors and performing database operations effectively.', 'chapters': [{'end': 7138.503, 'start': 6904.814, 'title': 'Error handling and custom messages', 'summary': 'Covers error handling in procedures to provide understandable error messages and avoid errors, including creating custom error messages and handling default errors, with examples of using exit handlers and producing error messages for specific and unknown errors.', 'duration': 233.689, 'highlights': ['The chapter covers error handling in procedures to provide understandable error messages and avoid errors. The chapter focuses on the importance of error handling in procedures to provide clear error messages and prevent potential errors.', 'creating custom error messages and handling default errors, with examples of using exit handlers It explains the process of creating custom error messages and handling default errors using exit handlers for specific and unknown errors.', 'producing error messages for specific and unknown errors. It demonstrates the process of producing error messages for specific errors and handling unknown errors to enhance error handling in procedures.']}, {'end': 7626.147, 'start': 7140.267, 'title': 'Mysql exception handling and transactions', 'summary': 'Demonstrates how to handle built-in and user-defined exceptions in mysql, preventing duplicate data entry and executing transactions, with a focus on error handling and committing or rolling back sql statements.', 'duration': 485.88, 'highlights': ["Demonstrating built-in exception handling by preventing the use of an existing key 'id1', resulting in an error message Illustrates the use of built-in exception handling by preventing the use of an existing key 'id1', leading to the generation of an error message.", 'Implementing custom error handling to block duplicate data entry in the product type table Shows the implementation of custom error handling to prevent duplicate data entry in the product type table, including declaring a custom error event and defining an exit handler for the custom error.', 'Explaining the concept of transactions, including committing and rolling back SQL statements based on error conditions Explains the concept of transactions in MySQL, emphasizing the ability to commit or roll back SQL statements based on error conditions, with an example of inserting sales items within a transaction.']}, {'end': 7942.846, 'start': 7626.548, 'title': 'Stored functions and getordertotal', 'summary': 'Discusses the creation of stored functions, focusing on the example of creating a stored function called getordertotal, which calculates the total value of an order by summing the product of sales item quantity and item price, and demonstrates its usage with a query to display sales order ids along with their total values.', 'duration': 316.298, 'highlights': ['Creation of stored function GetOrderTotal The transcript focuses on the creation of a stored function named GetOrderTotal, which is designed to calculate the total value of an order by summing the product of sales item quantity and item price.', 'Usage of stored function with a query The transcript demonstrates the usage of the GetOrderTotal stored function with a query to display sales order IDs along with their total values, showcasing the practical application of the stored function.', 'Explanation of stored functions The chapter provides an explanation of stored functions, including the limitation that functions can only return a single value, and emphasizes the importance of defining the data type to be returned.']}, {'end': 8253.673, 'start': 7943.45, 'title': 'Sql triggers and procedures', 'summary': 'Introduces the creation of a procedure to check for the existence of a customer with a new first and last name in a database using sql triggers, and demonstrates the implementation and testing of the created procedure, showcasing the usage of mysql.', 'duration': 310.223, 'highlights': ['Creation of a procedure to check for the existence of a customer with a new first and last name in a database using SQL triggers. The speaker demonstrates the creation of a procedure in SQL to check if a customer with a new first and last name already exists in the database, aiming to throw an error if a match is found, showcasing the usage of SQL triggers and procedures.', 'Implementation and testing of the created procedure, showcasing the usage of MySQL. The speaker implements and tests the created procedure by inserting new customer data and demonstrates the usage of MySQL for checking the existence of customers with new first and last names, showcasing practical application of the SQL procedure.', 'Demonstration of using SQL triggers and procedures in MySQL. The transcript provides a detailed demonstration of creating and utilizing SQL triggers and procedures in MySQL to automate actions when certain database events occur, such as inserting new customer data and checking for existing records.']}], 'duration': 1348.859, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/XqIk2PwP0To/pics/XqIk2PwP0To6904814.jpg', 'highlights': ['Demonstration of using SQL triggers and procedures in MySQL. Detailed demonstration of creating and utilizing SQL triggers and procedures in MySQL to automate actions when certain database events occur.', 'Creation of a procedure to check for the existence of a customer with a new first and last name in a database using SQL triggers. Demonstrates the creation of a procedure in SQL to check if a customer with a new first and last name already exists in the database, aiming to throw an error if a match is found.', 'Explaining the concept of transactions, including committing and rolling back SQL statements based on error conditions. Explains the concept of transactions in MySQL, emphasizing the ability to commit or roll back SQL statements based on error conditions.', 'Implementing custom error handling to block duplicate data entry in the product type table. Shows the implementation of custom error handling to prevent duplicate data entry in the product type table, including declaring a custom error event and defining an exit handler for the custom error.']}], 'highlights': ['Covers a comprehensive SQL tutorial using MySQL, compressing a 500-page book into one video.', "Creation of a view to store purchase order information A view named 'Purchase Order Overview' is created to combine data from various tables including sales order, sales item, item, customer, product, and salesperson, to store purchase order information such as purchase order number, company, quantity, product supplier, individual product name, item's price, and total.", 'Demonstration of using SQL triggers and procedures in MySQL. Detailed demonstration of creating and utilizing SQL triggers and procedures in MySQL to automate actions when certain database events occur.', 'Explaining the concept of transactions, including committing and rolling back SQL statements based on error conditions. Explains the concept of transactions in MySQL, emphasizing the ability to commit or roll back SQL statements based on error conditions.', 'Explains altering database tables: adding, modifying, and dropping columns, renaming tables, creating indexes, and manipulating data.']}