title
Learn SQL for Data Analysis in one hour (with sample dataset + 50 queries)

description
SQL is one of the most important skills to have for data people. In this comprehensive video, learn how to use SQL to perform common data analysis tasks. You will learn: ◈ How to use SELECT statement to answer business questions ◈ Working with WHERE clause ◈ Using AND, OR, NOT and combining them to create complex queries. ◈ Sorting query results using ORDER BY ◈ Combining data from two or more tables using JOINS ◈ Creating reports with GROUP BY ◈ More than 50 example queries, tips and ideas 🚨 PRE-REQUISITES: You need any SQL console (I am using the FREE MySQL Community Edition and you can download it from https://dev.mysql.com/downloads/installer/ ) You also need the Awesome Chocolates Dataset. See https://chandoo.org/wp/learn-sql-for-data-analysis/ for the sample files, instructions on data import and other SQL resources for you. 😃 Basic intro: If you want a simple introduction to SQL (for an interview or to brush up), see this video instead - https://youtu.be/yfWcZ6eot4g ⏱ IN THE VIDEO: 0:00 - Introduction 0:43 - Data setup, explanation & basic queries 6:24 - Adding calculated columns in SELECT 8:39 - Our first WHERE clause 10:28 - Ordering query results with ORDER BY 13:24 - More Where clause examples 16:42 - Between conditions 19:06 - Working with Dates 21:50 - Using other tables 23:47 - IN clause 24:36 - Pattern matching in SQL 26:30 - CASE operator and branching logic 30:30 - JOINS 36:32 - LEFT Join vs. RIGHT Join ~ EXPLAINED 38:28 - More Join examples 42:10 - Adding conditions to Joins 47:30 - GROUP BY and creating reports with SQL 54:50 - Showing Top 10 Products by Amount 57:12 - Closing remarks & next steps for you 📚 RESOURCES TO LEARN MORE SQL: 1) Practice the problems and scenarios discussed in the video with the awesome chocolates dataset. 2) Solve homework problems listed here - https://chandoo.org/wp/learn-sql-for-data-analysis/ 3) Get one or two SQL books. My recommendations are here - https://chandoo.org/wp/learn-sql-for-data-analysis/ 4) Consider going for an SQL course. Check the same page for my course suggestions. 5) PRACTICE, PRACTICE, PRACTICE! 🙄SQL ALTERNATIVES: If you don't want to learn SQL but still wish to work with data, then consider learning POWER QUERY, which offers similar capabilities as SQL. See this video 👉https://youtu.be/PiFAa_jjaEI 😎WATCH NEXT: Now that you know a bit of SQL, why not use it with Power BI to do visual data analysis? If you are new to Power BI, see my Power BI video to get started with that. 👇 https://youtu.be/uwe8C7K8fXY ~ 😍FOLLLOW ME Apart from YouTube, I regularly share content on below places. Feel free to check them out and give a follow. 💻 WEBSITE - https://chandoo.org/wp/ 📃 WEEKLY DATA TIPS NEWSLETTER - https://chandoo.org/wp/subscribe/ 📷 INSTAGRAM - https://www.instagram.com/chandoo.xlsx/ 🐤 TWITTER - https://twitter.com/r1c1/ ~ Select "good day" from days where person = "you" 😃 #sql #tutorial

detail
{'title': 'Learn SQL for Data Analysis in one hour (with sample dataset + 50 queries)', 'heatmap': [], 'summary': 'Learn and master sql through 50 queries covering select operations, where clauses, joins, and group buys, with a full-length example dataset and queries for download. the chapters also introduce basic sql queries and cover using sql statements, practice with where clauses and query operators, joining tables in sql, sql queries and table joins, and using group by in sql for creating pivot report style datasets.', 'chapters': [{'end': 348.441, 'segs': [{'end': 74.478, 'src': 'embed', 'start': 0.189, 'weight': 0, 'content': [{'end': 6.292, 'text': 'In this video, we are going to write 50 SQL queries so that you can learn, understand and master SQL.', 'start': 0.189, 'duration': 6.103}, {'end': 13.356, 'text': 'We are going to look at how to do select operations, how to write where clauses, how to use and or not,', 'start': 6.472, 'duration': 6.884}, {'end': 22.021, 'text': 'and how to combine all of these things how to build joins in SQL, how to do group buys, order, buys and so much more.', 'start': 13.356, 'duration': 8.665}, {'end': 26.063, 'text': 'The video has got lots of practical information and real world tips.', 'start': 22.201, 'duration': 3.862}, {'end': 31.465, 'text': 'And I have got a full length example data set as well as all the queries for you to download.', 'start': 26.403, 'duration': 5.062}, {'end': 34.145, 'text': 'So feel free to check the video description for that.', 'start': 31.805, 'duration': 2.34}, {'end': 41.228, 'text': 'As this video is quite detailed, I highly recommend that you set aside time and watch it in one setting for best results.', 'start': 34.366, 'duration': 6.862}, {'end': 41.788, 'text': "Let's go.", 'start': 41.508, 'duration': 0.28}, {'end': 50.23, 'text': 'We will be using the MySQL workbench to build our SQL queries.', 'start': 45.868, 'duration': 4.362}, {'end': 55.031, 'text': 'I have already preloaded our Awesome Chocolates database to this,', 'start': 50.77, 'duration': 4.261}, {'end': 60.873, 'text': 'but you will need to load this using the instructions that are available in the video description.', 'start': 55.031, 'duration': 5.842}, {'end': 66.635, 'text': 'Once you have the Awesome Chocolates, then we can go and create some SQL queries.', 'start': 62.034, 'duration': 4.601}, {'end': 70.917, 'text': 'To make a new query, here is my plus SQL button in the corner.', 'start': 67.175, 'duration': 3.742}, {'end': 74.478, 'text': 'And when I click on it, I will get my query editor here.', 'start': 71.417, 'duration': 3.061}], 'summary': 'Learn and master sql by writing 50 queries covering select operations, where clauses, joins, group buys, and more, with practical information and a full example dataset provided for download.', 'duration': 74.289, 'max_score': 0.189, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ189.jpg'}, {'end': 190.195, 'src': 'embed', 'start': 112.258, 'weight': 5, 'content': [{'end': 120.94, 'text': "If you're not familiar with the database and you don't know what tables are available themselves, you can use this simple query called show tables.", 'start': 112.258, 'duration': 8.682}, {'end': 127.461, 'text': 'And when you run this, it will list all the tables within the database.', 'start': 122.08, 'duration': 5.381}, {'end': 137.645, 'text': 'And to understand what is available within a specific table, you can also use the describe and then table name and you can run that query.', 'start': 128.715, 'duration': 8.93}, {'end': 141.289, 'text': 'And then that will show you what is in the sales table.', 'start': 138.706, 'duration': 2.583}, {'end': 150.139, 'text': 'So describe sales tells me that the sales table has these columns and this is the data type and some extra information about that.', 'start': 141.85, 'duration': 8.289}, {'end': 158.894, 'text': 'Normally when you are writing SQL queries, it is expected that you are familiar with the underlying tables and the relationships.', 'start': 152.426, 'duration': 6.468}, {'end': 162.838, 'text': "If you don't have that knowledge, writing SQL becomes really hard.", 'start': 159.434, 'duration': 3.404}, {'end': 168.926, 'text': 'So for our first example, we are just going to look at the sales table and then see what is there.', 'start': 163.719, 'duration': 5.207}, {'end': 172.624, 'text': 'this is where the select statement comes in.', 'start': 170.143, 'duration': 2.481}, {'end': 182.97, 'text': 'so we say select, and then if you simply say star, that means you want everything, all the columns from and then the table name sales.', 'start': 172.624, 'duration': 10.346}, {'end': 190.195, 'text': 'so select star from sales and then to run this particular statement, you can use the run command here,', 'start': 182.97, 'duration': 7.225}], 'summary': "Use 'show tables' to list all tables and 'describe' to view table columns and data types. sql queries require knowledge of underlying tables and relationships.", 'duration': 77.937, 'max_score': 112.258, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ112258.jpg'}, {'end': 328.522, 'src': 'embed', 'start': 304.687, 'weight': 9, 'content': [{'end': 314.073, 'text': 'Remember this is still listing all the data right now within the management that workbench that we are using our queries whenever they run.', 'start': 304.687, 'duration': 9.386}, {'end': 315.474, 'text': 'there is a limit of 1000 rows.', 'start': 314.073, 'duration': 1.401}, {'end': 324.239, 'text': 'So even though my sales table has around 7000 rows of data for the purpose of viewing it will only just show 1000 rows.', 'start': 315.714, 'duration': 8.525}, {'end': 328.522, 'text': "So if you want you can kind of say don't limit or set a higher limit.", 'start': 324.619, 'duration': 3.903}], 'summary': 'The management workbench has a limit of 1000 rows for viewing data, suggesting to set a higher limit.', 'duration': 23.835, 'max_score': 304.687, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ304687.jpg'}], 'start': 0.189, 'title': 'Learning sql through 50 queries', 'summary': "Provides a comprehensive guide on learning and mastering sql through 50 queries, covering select operations, where clauses, joins, group buys, and more, with a full-length example data set and queries for download. it also introduces basic sql queries, including 'show tables' and 'describe' to list available tables and table details, and 'select' to retrieve specific columns, with an emphasis on the importance of familiarity with underlying tables and relationships.", 'chapters': [{'end': 111.038, 'start': 0.189, 'title': 'Learning 50 sql queries', 'summary': 'Provides a comprehensive guide on learning and mastering sql through 50 queries, covering select operations, where clauses, joins, group buys, and more, with the provision of a full-length example data set and queries for download. it also emphasizes the use of mysql workbench for query construction and output visualization.', 'duration': 110.849, 'highlights': ['The video covers 50 SQL queries to learn and master SQL, including select operations, where clauses, joins, group buys, and more.', 'The chapter provides a full-length example data set and all the queries for download to facilitate practical learning.', 'Emphasizes the use of MySQL workbench for building SQL queries and visualizing output results.', 'The recommendation to set aside time and watch the video in one setting for best results is given.', 'Instructions for loading the Awesome Chocolates database are available in the video description.']}, {'end': 348.441, 'start': 112.258, 'title': 'Sql query basics: show, describe, and select', 'summary': "Introduces basic sql queries, including 'show tables' and 'describe' to list available tables and table details, and 'select' to retrieve specific columns, with an emphasis on the importance of familiarity with underlying tables and relationships.", 'duration': 236.183, 'highlights': ["The chapter introduces basic SQL queries, including 'show tables' and 'describe' to list available tables and table details, and 'select' to retrieve specific columns, with an emphasis on the importance of familiarity with underlying tables and relationships. This is the main highlight summarizing the introduction of basic SQL queries, the use of 'show tables,' 'describe,' and 'select' commands, and emphasizing the significance of understanding underlying table structures and relationships for effective SQL query writing.", "The 'show tables' query lists all the tables within the database, providing a quick overview of the available tables. The usage of 'show tables' query to list all tables within the database is highlighted, offering a quick overview of available tables.", "The 'describe' command provides information about the columns, data types, and additional details for a specific table, facilitating a better understanding of the table's structure. The significance of the 'describe' command in providing detailed information about the structure of a specific table, including columns, data types, and additional details, is emphasized.", "The 'select' statement is demonstrated for retrieving specific columns from a table, with the option to use 'star' for retrieving all columns or specify individual column names. Demonstration of the 'select' statement for retrieving specific columns from a table, either by using 'star' to retrieve all columns or specifying individual column names, is highlighted.", 'The limitation of 1000 rows for viewing query results is mentioned, with a suggestion to set a higher limit or remove the limit for comprehensive data viewing. The mention of the 1000-row limitation for viewing query results and the suggestion to set a higher limit or remove the limit for comprehensive data viewing is highlighted.']}], 'duration': 348.252, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ189.jpg', 'highlights': ['The video covers 50 SQL queries to learn and master SQL, including select operations, where clauses, joins, group buys, and more.', 'The chapter provides a full-length example data set and all the queries for download to facilitate practical learning.', 'Emphasizes the use of MySQL workbench for building SQL queries and visualizing output results.', 'The recommendation to set aside time and watch the video in one setting for best results is given.', 'Instructions for loading the Awesome Chocolates database are available in the video description.', "The chapter introduces basic SQL queries, including 'show tables' and 'describe' to list available tables and table details, and 'select' to retrieve specific columns, with an emphasis on the importance of familiarity with underlying tables and relationships.", "The 'show tables' query lists all the tables within the database, providing a quick overview of the available tables.", "The 'describe' command provides information about the columns, data types, and additional details for a specific table, facilitating a better understanding of the table's structure.", "The 'select' statement is demonstrated for retrieving specific columns from a table, with the option to use 'star' for retrieving all columns or specify individual column names.", 'The limitation of 1000 rows for viewing query results is mentioned, with a suggestion to set a higher limit or remove the limit for comprehensive data viewing.']}, {'end': 1110.351, 'segs': [{'end': 551.376, 'src': 'embed', 'start': 524.868, 'weight': 0, 'content': [{'end': 532.23, 'text': "Now let's understand how we can use the SELECT statement to impose an extra condition onto the query.", 'start': 524.868, 'duration': 7.362}, {'end': 536.891, 'text': 'That means I just want to look at all the sale amounts more than $10, 000.', 'start': 532.59, 'duration': 4.301}, {'end': 540.613, 'text': 'So this is where the WHERE clause comes into picture.', 'start': 536.892, 'duration': 3.721}, {'end': 543.834, 'text': 'We can simply say SELECT star from sales.', 'start': 541.013, 'duration': 2.821}, {'end': 551.376, 'text': "And then in the next line, you'll say where and here you can specify any number of conditions and combine them creatively.", 'start': 544.574, 'duration': 6.802}], 'summary': 'Using select statement to impose condition on sale amounts > $10,000 with where clause.', 'duration': 26.508, 'max_score': 524.868, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ524868.jpg'}, {'end': 628.208, 'src': 'embed', 'start': 598.793, 'weight': 2, 'content': [{'end': 603.476, 'text': 'So one way to think about this is this operation is like filtering in Excel.', 'start': 598.793, 'duration': 4.683}, {'end': 612.26, 'text': 'Essentially, when you set up a filter, the filter criteria that we give is either greater than, less than, or some other operation.', 'start': 604.056, 'duration': 8.204}, {'end': 614.922, 'text': "So whatever it is, that's where the where clause comes in.", 'start': 612.28, 'duration': 2.642}, {'end': 621.159, 'text': 'Using where clauses in SQL is one of the most important aspects when it comes to data analysis.', 'start': 615.632, 'duration': 5.527}, {'end': 628.208, 'text': "That's why we are going to spend next few minutes understanding different types of where operations and learn it at a deeper level.", 'start': 621.66, 'duration': 6.548}], 'summary': 'Understanding where clauses in sql is crucial for data analysis.', 'duration': 29.415, 'max_score': 598.793, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ598793.jpg'}, {'end': 676.582, 'src': 'embed', 'start': 644.47, 'weight': 3, 'content': [{'end': 648.095, 'text': 'So this is where the next clause of the query comes in the order clause.', 'start': 644.47, 'duration': 3.625}, {'end': 650.118, 'text': "So I'm just going to copy this here and.", 'start': 648.456, 'duration': 1.662}, {'end': 658.13, 'text': 'where amount greater than 10 000 and then you can say order by and then specify the column.', 'start': 651.966, 'duration': 6.164}, {'end': 659.031, 'text': 'so, for example,', 'start': 658.13, 'duration': 0.901}, {'end': 667.816, 'text': 'order by amount means this is gonna give you a result of all the amounts greater than ten thousand and then arrange them by the amount.', 'start': 659.031, 'duration': 8.785}, {'end': 670.718, 'text': 'order in the ascending order by default.', 'start': 667.816, 'duration': 2.902}, {'end': 676.582, 'text': "so control, enter on this and you'll see that you know lowest ten thousand greater than amount is ten thousand ten dollars.", 'start': 670.718, 'duration': 5.864}], 'summary': 'Query orders amounts > $10,000, arranged in ascending order.', 'duration': 32.112, 'max_score': 644.47, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ644470.jpg'}, {'end': 812.809, 'src': 'embed', 'start': 783.275, 'weight': 4, 'content': [{'end': 785.135, 'text': "And we'll then have P02.", 'start': 783.275, 'duration': 1.86}, {'end': 786.976, 'text': 'Again, it kind of kick starts from 16, 000, goes down.', 'start': 785.215, 'duration': 1.761}, {'end': 792.339, 'text': 'and then you will have p03, p04 like that.', 'start': 788.857, 'duration': 3.482}, {'end': 798.723, 'text': 'so this is how you can use where clause, as well as the order by, to impose multiple sort criteria.', 'start': 792.339, 'duration': 6.384}, {'end': 809.787, 'text': 'so where clause is like filtering the data in excel and order by is like sorting the data in excel, As I mentioned earlier,', 'start': 798.723, 'duration': 11.064}, {'end': 812.809, 'text': 'where clause is one of the most important things in SQL.', 'start': 809.787, 'duration': 3.022}], 'summary': 'Using where clause and order by in sql for multiple sort criteria.', 'duration': 29.534, 'max_score': 783.275, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ783275.jpg'}, {'end': 868.463, 'src': 'embed', 'start': 835.117, 'weight': 5, 'content': [{'end': 837.538, 'text': 'Select star from sales.', 'start': 835.117, 'duration': 2.421}, {'end': 842.371, 'text': 'where amount greater than 10, 000.', 'start': 839.23, 'duration': 3.141}, {'end': 845.913, 'text': 'And now we need to have the second condition.', 'start': 842.371, 'duration': 3.542}, {'end': 852.055, 'text': 'So amount should be greater than 10, 000 and date should be within 2022.', 'start': 846.173, 'duration': 5.882}, {'end': 854.196, 'text': 'So this is where the end keyword comes in.', 'start': 852.055, 'duration': 2.141}, {'end': 860.919, 'text': 'You can use and or not, et cetera, to kind of combine conditions and add multiple criteria.', 'start': 854.796, 'duration': 6.123}, {'end': 868.463, 'text': 'So amount greater than 10, 000 and sale date, and then here we can specify a date.', 'start': 861.379, 'duration': 7.084}], 'summary': 'Query sales data for amount > $10,000 and date within 2022.', 'duration': 33.346, 'max_score': 835.117, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ835117.jpg'}], 'start': 348.441, 'title': 'Using sql statements', 'summary': 'Covers using the select statement to retrieve, rearrange, calculate, and filter data, as well as using where and order by clauses with examples and explanations provided.', 'chapters': [{'end': 598.373, 'start': 348.441, 'title': 'Using select statement in sql', 'summary': 'Covers using the select statement in sql to retrieve data, rearrange columns, perform calculations, rename columns, impose conditions, and filter data based on specified criteria, with examples and explanations provided.', 'duration': 249.932, 'highlights': ['The chapter covers using the SELECT statement in SQL to retrieve data, rearrange columns, perform calculations, rename columns, impose conditions, and filter data based on specified criteria. The chapter provides a comprehensive overview of the various functionalities of the SELECT statement in SQL, including retrieving data, rearranging columns, performing calculations, renaming columns, imposing conditions, and filtering data based on specified criteria.', 'The WHERE clause allows for filtering data based on specified conditions, such as retrieving sale amounts more than $10,000. The WHERE clause enables the filtering of data based on specified conditions, such as retrieving sale amounts greater than $10,000, offering a method for refining data retrieval based on defined criteria.', "The tutorial demonstrates how to rename calculated columns using 'as' or synonyms, providing flexibility in column naming conventions. The tutorial illustrates the process of renaming calculated columns using 'as' or synonyms, offering flexibility in defining column names and enhancing the readability and usability of the resulting data."]}, {'end': 1110.351, 'start': 598.793, 'title': 'Using where and order by clauses in sql', 'summary': 'Explains the use of where and order by clauses in sql, including examples of using where to filter data and order by to sort data, with a demonstration of multiple sort criteria and the between keyword for filtering a range of values.', 'duration': 511.558, 'highlights': ['Using where clauses in SQL is one of the most important aspects when it comes to data analysis. Emphasizes the significance of where clauses in SQL for data analysis.', 'Demonstrates using order by clause to sort data based on specified column, including ascending and descending order. Illustrates the usage of order by clause to sort data in ascending and descending order.', 'Explains using where and order by clauses together to impose multiple sort criteria on the data. Illustrates the use of where and order by clauses together to impose multiple sort criteria on the data.', 'Shows examples of using where clause to filter data based on multiple conditions, such as amount greater than 10,000 and specific date. Demonstrates examples of using where clause to filter data based on multiple conditions, such as amount greater than 10,000 and specific date.', 'Demonstrates using the between keyword to filter data within a specified range, such as finding all the sales where the number of boxes is between 0 to 50. Illustrates the usage of the between keyword to filter data within a specified range, such as finding all the sales where the number of boxes is between 0 to 50.']}], 'duration': 761.91, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ348441.jpg', 'highlights': ['The chapter covers using the SELECT statement in SQL to retrieve data, rearrange columns, perform calculations, rename columns, impose conditions, and filter data based on specified criteria.', 'The WHERE clause allows for filtering data based on specified conditions, such as retrieving sale amounts more than $10,000.', 'Using where clauses in SQL is one of the most important aspects when it comes to data analysis.', 'Demonstrates using order by clause to sort data based on specified column, including ascending and descending order.', 'Explains using where and order by clauses together to impose multiple sort criteria on the data.', 'Shows examples of using where clause to filter data based on multiple conditions, such as amount greater than 10,000 and specific date.']}, {'end': 1807.798, 'segs': [{'end': 1157.114, 'src': 'embed', 'start': 1111.226, 'weight': 3, 'content': [{'end': 1118.471, 'text': "Hey, I just want to remind you that, while learning SQL is a good idea, if you don't practice, then you will forget most of the concepts.", 'start': 1111.226, 'duration': 7.245}, {'end': 1124.555, 'text': "That's why I have prepared many practice homework problems for you that are showing up on the screen.", 'start': 1118.991, 'duration': 5.564}, {'end': 1131.82, 'text': 'Feel free to refer to the video description and download these practice problems as well and try to solve them.', 'start': 1125.095, 'duration': 6.725}, {'end': 1133.921, 'text': 'Some of them are easy, some of them are hard.', 'start': 1131.9, 'duration': 2.021}, {'end': 1139.025, 'text': 'The hard ones require you to investigate a little bit more beyond what is covered in this video.', 'start': 1134.422, 'duration': 4.603}, {'end': 1144.788, 'text': 'but I highly encourage you to practice these so that you can learn and understand SQL better.', 'start': 1139.645, 'duration': 5.143}, {'end': 1145.868, 'text': 'Good luck.', 'start': 1144.808, 'duration': 1.06}, {'end': 1154.652, 'text': 'Our next where clause example is going to be, I want to see all the sales or all the shipments that are happening on the Fridays.', 'start': 1146.628, 'duration': 8.024}, {'end': 1157.114, 'text': 'So again, we have got the date here.', 'start': 1155.293, 'duration': 1.821}], 'summary': 'Practice sql with homework problems for better understanding. some easy, some hard. encouraged for improved learning.', 'duration': 45.888, 'max_score': 1111.226, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1111226.jpg'}, {'end': 1397.126, 'src': 'embed', 'start': 1366.437, 'weight': 4, 'content': [{'end': 1369.218, 'text': 'So here again, you can do this in a couple of ways.', 'start': 1366.437, 'duration': 2.781}, {'end': 1383.486, 'text': 'We can say select star from people where team equal to and then single quotes because this is text delish or team equal to juices.', 'start': 1369.318, 'duration': 14.168}, {'end': 1391.939, 'text': 'So here we are using the or condition instead of and condition because someone person cannot be in both teams.', 'start': 1386.192, 'duration': 5.747}, {'end': 1393.321, 'text': "They'll be in one of these two.", 'start': 1391.979, 'duration': 1.342}, {'end': 1397.126, 'text': 'So if they are in this or that, we want that.', 'start': 1393.821, 'duration': 3.305}], 'summary': "Using sql, select data from people based on two team conditions, using an 'or' condition for exclusivity.", 'duration': 30.689, 'max_score': 1366.437, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1366437.jpg'}, {'end': 1512.082, 'src': 'embed', 'start': 1451.473, 'weight': 1, 'content': [{'end': 1452.613, 'text': 'because these are text values.', 'start': 1451.473, 'duration': 1.14}, {'end': 1455.955, 'text': 'Everything needs to be in single quotes.', 'start': 1452.673, 'duration': 3.282}, {'end': 1462.853, 'text': "And when you run this, you'll get the same result.", 'start': 1459.79, 'duration': 3.063}, {'end': 1467.377, 'text': 'But now we are using the in clause to provide all the possible results.', 'start': 1462.913, 'duration': 4.464}, {'end': 1475.584, 'text': 'So in offers a more flexible way in which you can add multiple conditions and kind of get all the results that you want.', 'start': 1467.817, 'duration': 7.767}, {'end': 1483.13, 'text': 'Another common thing that we do in SQL is pattern matching.', 'start': 1480.128, 'duration': 3.002}, {'end': 1486.353, 'text': 'And this is where we can use the like operator in SQL.', 'start': 1483.751, 'duration': 2.602}, {'end': 1489.74, 'text': "So for example, I'm going to select all the people.", 'start': 1486.738, 'duration': 3.002}, {'end': 1501.708, 'text': 'So select start from people where salesperson like, and then I want to just see all the names that begin with B.', 'start': 1490.26, 'duration': 11.448}, {'end': 1503.509, 'text': "So we'll try a few different things.", 'start': 1501.708, 'duration': 1.801}, {'end': 1509.213, 'text': "Intuition tells me if I simply say like B, I'll get all the names that are having B.", 'start': 1504.77, 'duration': 4.443}, {'end': 1512.082, 'text': 'And we get nothing here.', 'start': 1510.962, 'duration': 1.12}], 'summary': "Using 'in' clause for flexible conditions and 'like' operator for pattern matching in sql.", 'duration': 60.609, 'max_score': 1451.473, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1451473.jpg'}, {'end': 1616.957, 'src': 'embed', 'start': 1585.108, 'weight': 0, 'content': [{'end': 1588.752, 'text': 'Apart from like and in and where, etc.', 'start': 1585.108, 'duration': 3.644}, {'end': 1593.077, 'text': 'SQL also offers many other operations that you can do on the data.', 'start': 1588.892, 'duration': 4.185}, {'end': 1599.364, 'text': "I'm going to just scratch the surface here by introducing one another operator, which is the case operator.", 'start': 1593.537, 'duration': 5.827}, {'end': 1602.79, 'text': "For the purpose of this, we'll need to go back to our sales table.", 'start': 1599.884, 'duration': 2.906}, {'end': 1607.72, 'text': 'So select star from sales just to kind of bring us back to the data.', 'start': 1602.81, 'duration': 4.91}, {'end': 1611.407, 'text': 'And then you can see we forgot the from here.', 'start': 1608.321, 'duration': 3.086}, {'end': 1616.957, 'text': 'So select star from sales and then it gives you this data.', 'start': 1614.116, 'duration': 2.841}], 'summary': 'Introduction to sql operations and case operator with sales data example.', 'duration': 31.849, 'max_score': 1585.108, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1585108.jpg'}], 'start': 1111.226, 'title': 'Sql practice, where clause, and query operators', 'summary': "Highlights the significance of sql practice, provides problems and examples for using the where clause, 'in' clause, 'like' operator, and 'case' operator, including practical demonstrations and encountered errors.", 'chapters': [{'end': 1427.941, 'start': 1111.226, 'title': 'Sql practice and where clause example', 'summary': "Emphasizes the importance of practicing sql, provides practice problems, and demonstrates using the where clause to filter data, including an error encountered and the usage of the 'or' condition to filter data from a table.", 'duration': 316.715, 'highlights': ['The chapter emphasizes the importance of practicing SQL and provides practice problems for better understanding. The speaker stresses the importance of practicing SQL to avoid forgetting concepts and provides practice problems for better understanding.', 'Demonstrates using the where clause to filter data based on specific conditions and encountered an error while attempting to reuse a column. The speaker demonstrates using the where clause to filter data based on specific conditions, encounters an error while attempting to reuse a column, and resorts to the usual route to achieve the desired result.', "Usage of the 'or' condition to filter data from a table based on multiple criteria. The speaker demonstrates using the 'or' condition to filter data from a table based on multiple criteria, ensuring the inclusion of data based on either of the specified conditions."]}, {'end': 1807.798, 'start': 1427.941, 'title': 'Sql query operators', 'summary': "Discusses the use of 'in' clause for flexible conditions, 'like' operator for pattern matching, and 'case' operator for categorizing data in sql, with examples and practical demonstrations.", 'duration': 379.857, 'highlights': ["The 'in' clause provides a flexible way to add multiple conditions and retrieve all the desired results in SQL. Using the 'in' clause allows adding multiple conditions to retrieve all the desired results, providing a flexible approach to querying the data.", "The 'like' operator in SQL allows pattern matching, such as finding names that begin with a specific letter or contain a specific letter anywhere in the name. The 'like' operator in SQL enables pattern matching, allowing for the retrieval of names based on specific patterns, such as names beginning with a certain letter or containing a specific letter.", "The 'case' operator in SQL allows for categorizing data based on specified conditions, providing a method for creating custom labels for different ranges of data values. The 'case' operator in SQL enables the categorization of data based on specified conditions, providing the ability to create custom labels for different ranges of data values, as demonstrated by categorizing sales amounts into different categories."]}], 'duration': 696.572, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1111226.jpg', 'highlights': ["The 'case' operator in SQL enables the categorization of data based on specified conditions, providing the ability to create custom labels for different ranges of data values, as demonstrated by categorizing sales amounts into different categories.", "Using the 'in' clause allows adding multiple conditions to retrieve all the desired results, providing a flexible approach to querying the data.", "The 'like' operator in SQL enables pattern matching, allowing for the retrieval of names based on specific patterns, such as names beginning with a certain letter or containing a specific letter.", 'The chapter emphasizes the importance of practicing SQL and provides practice problems for better understanding. The speaker stresses the importance of practicing SQL to avoid forgetting concepts and provides practice problems for better understanding.', "The speaker demonstrates using the 'or' condition to filter data from a table based on multiple criteria, ensuring the inclusion of data based on either of the specified conditions.", 'Demonstrates using the where clause to filter data based on specific conditions and encountered an error while attempting to reuse a column. The speaker demonstrates using the where clause to filter data based on specific conditions, encounters an error while attempting to reuse a column, and resorts to the usual route to achieve the desired result.']}, {'end': 2355.617, 'segs': [{'end': 1889.66, 'src': 'embed', 'start': 1850.014, 'weight': 0, 'content': [{'end': 1857.278, 'text': 'But if you want to learn a little bit more about how joints work and how to use them for many practical scenarios,', 'start': 1850.014, 'duration': 7.264}, {'end': 1859.278, 'text': 'you will need to do a little bit of further study.', 'start': 1857.278, 'duration': 2}, {'end': 1863.901, 'text': "I'll talk about some resources for learning SQL later on at the end of the video.", 'start': 1859.519, 'duration': 4.382}, {'end': 1866.062, 'text': "But for now, let's go and build some joints.", 'start': 1864.261, 'duration': 1.801}, {'end': 1874.385, 'text': 'Before I do that, I just want to save my SQL file because then I can come back and refer to these queries or I can share these examples with you.', 'start': 1867.196, 'duration': 7.189}, {'end': 1878.891, 'text': 'So to save this, I can simply go to file and then save script.', 'start': 1874.846, 'duration': 4.045}, {'end': 1882.376, 'text': 'And, you know, this will just open up my computer.', 'start': 1879.652, 'duration': 2.724}, {'end': 1885.5, 'text': "I'm gonna call this as queries one.", 'start': 1882.816, 'duration': 2.684}, {'end': 1888.099, 'text': "And then it's a .", 'start': 1886.798, 'duration': 1.301}, {'end': 1888.699, 'text': 'sql file.', 'start': 1888.099, 'duration': 0.6}, {'end': 1889.66, 'text': 'It gets saved here.', 'start': 1888.759, 'duration': 0.901}], 'summary': 'Learn about using joints for practical scenarios and save sql queries in a .sql file.', 'duration': 39.646, 'max_score': 1850.014, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1850014.jpg'}, {'end': 1962.799, 'src': 'embed', 'start': 1933.459, 'weight': 4, 'content': [{'end': 1939.466, 'text': "So it's a very simple database and things are kind of mean the same thing in two different tables.", 'start': 1933.459, 'duration': 6.007}, {'end': 1949.437, 'text': 'so, using this information, our very first query is going to be i just want to look at all the sales data and then see the person name also.', 'start': 1939.466, 'duration': 9.971}, {'end': 1955.814, 'text': 'so, for example, if i simply say select star from sales, We are seeing all the sales data.', 'start': 1949.437, 'duration': 6.377}, {'end': 1962.799, 'text': "We do have the SP ID here, SP01, SP02, SP12 here, but we don't know exactly who that person is.", 'start': 1956.275, 'duration': 6.524}], 'summary': 'Simple database with sales data needs query to display sales data with person names.', 'duration': 29.34, 'max_score': 1933.459, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1933459.jpg'}, {'end': 2338.774, 'src': 'embed', 'start': 2287.383, 'weight': 1, 'content': [{'end': 2290.745, 'text': 'But for better results, I recommend that you read a book or an article.', 'start': 2287.383, 'duration': 3.362}, {'end': 2295.449, 'text': 'I will link to some of them in the video description so that you know this all makes sense.', 'start': 2290.866, 'duration': 4.583}, {'end': 2303.654, 'text': 'If you have done some work within Excel, especially in Power Query, you would notice that Power Query also has the same wording there.', 'start': 2296.329, 'duration': 7.325}, {'end': 2306.816, 'text': 'It has the left join and right join when you merge the queries.', 'start': 2303.714, 'duration': 3.102}, {'end': 2308.658, 'text': "Let's go back.", 'start': 2308.197, 'duration': 0.461}, {'end': 2317.638, 'text': "Let's try one more type of join before we get into more complicated joins, which is just as we're seeing the people person names.", 'start': 2309.716, 'duration': 7.922}, {'end': 2322.22, 'text': "I want to see the product name that we're selling in these shipments, so we'll try a different one.", 'start': 2317.678, 'duration': 4.542}, {'end': 2325.1, 'text': "This time we'll join with the product table, so select.", 'start': 2322.24, 'duration': 2.86}, {'end': 2328.962, 'text': 'From sales S.', 'start': 2326.641, 'duration': 2.321}, {'end': 2331.182, 'text': 'And then S dot sale date.', 'start': 2328.962, 'duration': 2.22}, {'end': 2333.503, 'text': 'S dot amount.', 'start': 2332.443, 'duration': 1.06}, {'end': 2338.774, 'text': "It's showing me this red underlining.", 'start': 2336.653, 'duration': 2.121}], 'summary': 'Excel power query offers left and right joins when merging queries, along with a recommendation to read further resources for better results.', 'duration': 51.391, 'max_score': 2287.383, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2287383.jpg'}], 'start': 1807.798, 'title': 'Sql joins and joining tables in sql', 'summary': 'Introduces sql joins and emphasizes the need for further study, while also covering basics of joining tables in sql, including demonstrating how to perform a basic join to display sales data with corresponding person names and discussing different types of joins such as left join and right join.', 'chapters': [{'end': 1889.66, 'start': 1807.798, 'title': 'Introduction to sql joins', 'summary': 'Introduces sql joins, with a focus on combining data from multiple tables to create one output, and emphasizes the need for further study to fully understand practical scenarios, with a mention of saving sql queries.', 'duration': 81.862, 'highlights': ['Practical examples and explanations of SQL joins will be demonstrated in the subsequent part of the video.', 'The chapter emphasizes the need for further study to fully understand practical scenarios of using SQL joins.', 'The importance of saving SQL queries for future reference or sharing is emphasized.']}, {'end': 2355.617, 'start': 1889.68, 'title': 'Joining tables in sql', 'summary': 'Covers the basics of joining tables in sql, including explaining the structure of a simple database with four tables, demonstrating how to perform a basic join to display sales data with corresponding person names, and discussing different types of joins such as left join and right join.', 'duration': 465.937, 'highlights': ['The chapter introduces the structure of a simple database with four tables: sales, people, products, and geography. The database consists of four tables: sales, people, products, and geography, with columns such as SPID, GOID, PID, etc. linking the tables together.', 'The demonstration illustrates a basic join operation to display sales data with corresponding person names, using the sales and people tables. A basic join operation is demonstrated, where the sales table is joined with the people table to display sales data along with the corresponding person names.', "Different types of joins, including left join and right join, are explained with practical examples and comparisons to Excel's Power Query functionality. The chapter explains different types of joins such as left join and right join, and draws comparisons to Excel's Power Query functionality."]}], 'duration': 547.819, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ1807798.jpg', 'highlights': ['The chapter emphasizes the need for further study to fully understand practical scenarios of using SQL joins.', "Different types of joins, including left join and right join, are explained with practical examples and comparisons to Excel's Power Query functionality.", 'The demonstration illustrates a basic join operation to display sales data with corresponding person names, using the sales and people tables.', 'The importance of saving SQL queries for future reference or sharing is emphasized.', 'The chapter introduces the structure of a simple database with four tables: sales, people, products, and geography.']}, {'end': 2839.718, 'segs': [{'end': 2422.228, 'src': 'embed', 'start': 2355.617, 'weight': 0, 'content': [{'end': 2362.098, 'text': "but it's a good practice to kind of delimit the queries with that semicolon if you are building multiple queries in the same file.", 'start': 2355.617, 'duration': 6.481}, {'end': 2369.002, 'text': "So select sale date, amount and SPID from sales and then we will this time we'll use the left join.", 'start': 2363.277, 'duration': 5.725}, {'end': 2371.664, 'text': "There won't be any difference because the data is all there.", 'start': 2369.282, 'duration': 2.382}, {'end': 2382.492, 'text': "Products and then PR for consistency sake, whenever we refer to products, we'll say PR and people will say P that way both tables are with P.", 'start': 2372.184, 'duration': 10.308}, {'end': 2384.154, 'text': "So it's easy for you to distinguish.", 'start': 2382.492, 'duration': 1.662}, {'end': 2389.718, 'text': 'On pr.pid is equal to s.pid.', 'start': 2384.894, 'duration': 4.824}, {'end': 2393.38, 'text': "And then now that these are linked up, we probably don't need that.", 'start': 2390.698, 'duration': 2.682}, {'end': 2396.723, 'text': 'We are going to just say pr.product there.', 'start': 2393.42, 'duration': 3.303}, {'end': 2402.627, 'text': "And then let's just run this.", 'start': 2397.883, 'duration': 4.744}, {'end': 2407.47, 'text': "We'll get our product names here nicely printed for the amount.", 'start': 2403.367, 'duration': 4.103}, {'end': 2409.992, 'text': "So we know that on this day, we made $259 for Baker's Choco Chips.", 'start': 2407.49, 'duration': 2.502}, {'end': 2415.462, 'text': 'And then this continues like that.', 'start': 2414.161, 'duration': 1.301}, {'end': 2420.586, 'text': 'So this is my people join and then this is my product stable join.', 'start': 2416.103, 'duration': 4.483}, {'end': 2422.228, 'text': 'We are using left join.', 'start': 2421.187, 'duration': 1.041}], 'summary': 'Demonstrating sql query building with left join, identifying product sales', 'duration': 66.611, 'max_score': 2355.617, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2355617.jpg'}, {'end': 2468.761, 'src': 'embed', 'start': 2441.252, 'weight': 1, 'content': [{'end': 2445.395, 'text': "Just as you're joining two tables, you can join multiple tables also.", 'start': 2441.252, 'duration': 4.143}, {'end': 2447.376, 'text': "So that's what we're going to do next.", 'start': 2445.795, 'duration': 1.581}, {'end': 2453.32, 'text': "Just as we're showing product name here, I want to also see both product name and person name in one view.", 'start': 2447.696, 'duration': 5.624}, {'end': 2457.382, 'text': 'So this is where we can kind of combine both of these queries.', 'start': 2453.76, 'duration': 3.622}, {'end': 2458.683, 'text': "I'll be a little bit lazy.", 'start': 2457.722, 'duration': 0.961}, {'end': 2463.986, 'text': "I'm just going to copy my query from the top, paste it here and then.", 'start': 2458.723, 'duration': 5.263}, {'end': 2466.148, 'text': 'Copy this clause.', 'start': 2465.067, 'duration': 1.081}, {'end': 2468.761, 'text': 'paste this here.', 'start': 2467.981, 'duration': 0.78}], 'summary': 'Demonstrating joining multiple tables and combining queries.', 'duration': 27.509, 'max_score': 2441.252, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2441252.jpg'}, {'end': 2551.963, 'src': 'embed', 'start': 2520.143, 'weight': 3, 'content': [{'end': 2527.491, 'text': 'So, when you are building this, the join itself is like one kind of condition, because the IDs should match across the tables,', 'start': 2520.143, 'duration': 7.348}, {'end': 2530.594, 'text': 'but you can also introduce some extra criteria if you want.', 'start': 2527.491, 'duration': 3.103}, {'end': 2541.717, 'text': 'So for example, we are going to do the same thing, but this time We will add a condition.', 'start': 2531.015, 'duration': 10.702}, {'end': 2547.1, 'text': 'The condition would be where and then we can kind of build any condition.', 'start': 2542.618, 'duration': 4.482}, {'end': 2551.963, 'text': 'So for example, s.amount greater under 500.', 'start': 2547.14, 'duration': 4.823}], 'summary': 'Join tables on matching ids, and apply additional criteria like s.amount < 500.', 'duration': 31.82, 'max_score': 2520.143, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2520143.jpg'}, {'end': 2764.615, 'src': 'embed', 'start': 2734.628, 'weight': 4, 'content': [{'end': 2739.469, 'text': "You see that I'm not waiting for the auto suggest to come up and I'm typing these values myself.", 'start': 2734.628, 'duration': 4.841}, {'end': 2742.77, 'text': 'This is where knowing your data is really helpful.', 'start': 2739.989, 'duration': 2.781}, {'end': 2747.111, 'text': 'If you know your data well enough, then you can build better SQL queries.', 'start': 2742.79, 'duration': 4.321}, {'end': 2752.873, 'text': 'So one of the key suggestions that I have for you is anytime you are trying to learn or use SQL.', 'start': 2747.551, 'duration': 5.322}, {'end': 2757.134, 'text': 'First spend some time understanding the data model itself.', 'start': 2754.033, 'duration': 3.101}, {'end': 2764.615, 'text': 'What tables are there? What columns are there? How they are linked up or what goes where? Then you can build better SQL queries.', 'start': 2757.694, 'duration': 6.921}], 'summary': 'Understanding data model improves sql queries. know tables, columns, and relationships for better queries.', 'duration': 29.987, 'max_score': 2734.628, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2734628.jpg'}], 'start': 2355.617, 'title': 'Sql query and table joins', 'summary': 'Covers delimiting queries, left join usage, table aliasing, and retrieval of specific data, including an example of $259 revenue. it also explains sql table joins, adding conditions, and filtering data based on amount, team, and geography.', 'chapters': [{'end': 2422.228, 'start': 2355.617, 'title': 'Sql query building and left join usage', 'summary': "Discusses the practice of delimiting queries with a semicolon, using left join for consistency and distinguishing tables with aliases pr and p. it also demonstrates the usage of left join to retrieve product names and their corresponding amounts from the sales table, showcasing an example where $259 was made for baker's choco chips.", 'duration': 66.611, 'highlights': ['The importance of delimiting queries with a semicolon for building multiple queries in the same file.', 'Demonstration of using left join for consistency and distinguishing tables with aliases PR and P.', "Usage of left join to retrieve product names and their corresponding amounts from the sales table, showcasing an example where $259 was made for Baker's Choco Chips."]}, {'end': 2839.718, 'start': 2422.248, 'title': 'Sql table joins and conditions', 'summary': 'Discusses sql table joins and conditions, including examples of joining multiple tables, adding conditions on joins, and filtering data based on specific criteria, such as amount, team, and geography.', 'duration': 417.47, 'highlights': ['The chapter discusses joining multiple tables to access data from different tables, allowing the combination of queries to display multiple fields. Joining multiple tables to access combined data, combining queries to display multiple fields, accessing both product name and person name in a single view.', 'The chapter explains adding conditions on joins and demonstrates filtering data based on specific criteria, such as amount and team. Adding conditions on joins, filtering data based on specific criteria like amount and team, demonstrating filtering based on sales amount and team name.', 'The chapter provides insights into understanding the data model to build better SQL queries and emphasizes the importance of knowing the data well to enhance query building. Understanding the data model to build better SQL queries, emphasizing the importance of knowing the data well to enhance query building, suggesting spending time to understand the data model before using SQL.']}], 'duration': 484.101, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2355617.jpg', 'highlights': ['Demonstration of using left join for consistency and distinguishing tables with aliases PR and P.', 'The chapter discusses joining multiple tables to access data from different tables, allowing the combination of queries to display multiple fields.', 'The importance of delimiting queries with a semicolon for building multiple queries in the same file.', 'The chapter explains adding conditions on joins and demonstrates filtering data based on specific criteria, such as amount and team.', 'Understanding the data model to build better SQL queries, emphasizing the importance of knowing the data well to enhance query building, suggesting spending time to understand the data model before using SQL.', "Usage of left join to retrieve product names and their corresponding amounts from the sales table, showcasing an example where $259 was made for Baker's Choco Chips."]}, {'end': 3493.233, 'segs': [{'end': 2862.214, 'src': 'embed', 'start': 2840.178, 'weight': 2, 'content': [{'end': 2851.206, 'text': "So that's a really interesting way to explore the data and see a precise portion of the data that meets the criteria that you want by building the joins and where clauses.", 'start': 2840.178, 'duration': 11.028}, {'end': 2862.214, 'text': 'And that brings us to the very last part of our video on SQL learning itself, which is how to use group buys to create a report style data sets.', 'start': 2851.746, 'duration': 10.468}], 'summary': 'Learn how to use joins and group bys in sql for precise data analysis.', 'duration': 22.036, 'max_score': 2840.178, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2840178.jpg'}, {'end': 2912.834, 'src': 'embed', 'start': 2887.406, 'weight': 0, 'content': [{'end': 2893.608, 'text': 'What group by does is it lets you create kind of like a pivot report style things using SQL query.', 'start': 2887.406, 'duration': 6.202}, {'end': 2899.73, 'text': 'Many times all the data that you want is in the database, but it is at a too detailed level.', 'start': 2894.088, 'duration': 5.642}, {'end': 2902.191, 'text': 'And I just want to see the data at a higher level.', 'start': 2899.81, 'duration': 2.381}, {'end': 2904.092, 'text': "That's where the group bys are helpful.", 'start': 2902.251, 'duration': 1.841}, {'end': 2906.933, 'text': "So we'll try the simple ones first.", 'start': 2904.592, 'duration': 2.341}, {'end': 2912.834, 'text': "So select and then let's just group the data by the geo ID in the sales table itself.", 'start': 2907.133, 'duration': 5.701}], 'summary': 'Group by in sql creates pivot-style reports. useful for aggregating detailed data to a higher level.', 'duration': 25.428, 'max_score': 2887.406, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2887406.jpg'}, {'end': 2961.86, 'src': 'embed', 'start': 2939.877, 'weight': 1, 'content': [{'end': 2951.114, 'text': 'Basically, this is going to create a simple G1, G2, G3, G4, G5, G6 report telling you what was the total amount for each geography.', 'start': 2939.877, 'duration': 11.237}, {'end': 2958.479, 'text': 'because we are grouping by geo ID, I can do any operation and then that operation will be happening within that geography.', 'start': 2952.717, 'duration': 5.762}, {'end': 2961.86, 'text': 'So essentially this is like a pivot table.', 'start': 2958.859, 'duration': 3.001}], 'summary': 'Create a simple report to show total amount for each geography using pivot table.', 'duration': 21.983, 'max_score': 2939.877, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2939877.jpg'}, {'end': 3448.899, 'src': 'embed', 'start': 3418.531, 'weight': 4, 'content': [{'end': 3423.535, 'text': 'So limit is another operation on SQL that you can do when you limit the output.', 'start': 3418.531, 'duration': 5.004}, {'end': 3429.459, 'text': "It's going to just limit after running the query to limit the output to just the ten rows.", 'start': 3424.075, 'duration': 5.384}, {'end': 3432.601, 'text': "So that's really how you can get the top ten values.", 'start': 3429.639, 'duration': 2.962}, {'end': 3435.443, 'text': 'Hey, I hope you enjoyed that video on SQL.', 'start': 3433.001, 'duration': 2.442}, {'end': 3442.628, 'text': 'If you really like it, please feel free to share this video with one of your friends or colleagues so they can also learn about SQL.', 'start': 3436.083, 'duration': 6.545}, {'end': 3448.899, 'text': 'And if you are thinking this is all good, but how do I learn more about SQL?', 'start': 3443.795, 'duration': 5.104}], 'summary': 'Sql limit operation restricts output to top 10 rows.', 'duration': 30.368, 'max_score': 3418.531, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ3418531.jpg'}], 'start': 2840.178, 'title': 'Using sql group by for reporting', 'summary': 'Covers using group by in sql to create pivot report style datasets, enabling data aggregation and higher level visualization, with specific examples demonstrating total amounts for geographies; it also explains creating complex reports using sql joins and group by clauses, illustrating single and multiple level grouping, sort orders, and limiting output to top 10 results.', 'chapters': [{'end': 2988.105, 'start': 2840.178, 'title': 'Using group by in sql', 'summary': 'Discusses how to use group by in sql to create pivot report style datasets, allowing users to aggregate data and view it at a higher level, with a specific example demonstrating the total amount for each geography.', 'duration': 147.927, 'highlights': ['The chapter discusses how to use group by in SQL to create pivot report style datasets. Group by in SQL allows for the creation of pivot report style datasets, which helps in viewing data at a higher level and performing aggregations.', 'A specific example demonstrates the total amount for each geography using group by in SQL. An example is provided where the data is grouped by geo ID in the sales table, and the total amount for each geography is calculated using the sum function.']}, {'end': 3493.233, 'start': 2988.526, 'title': 'Sql grouping and reporting', 'summary': 'Covers the process of creating complex reports by using sql joins and group by clauses, including examples of grouping at single and multiple levels, applying sort orders, and limiting the output to the top 10 results.', 'duration': 504.707, 'highlights': ['Creating complex reports using SQL joins and group by clauses The chapter demonstrates the powerful way of creating reports by combining SQL joins and group by clauses, enabling the merging of data from multiple tables to generate complex reports.', 'Grouping at single and multiple levels The explanation of how to perform grouping at a single level or create multiple level grouping in SQL provides insights into organizing and summarizing data based on different criteria.', 'Applying sort orders and limiting output to top 10 results The demonstration of applying sort orders and limiting the output to the top 10 results in SQL provides practical techniques for organizing and focusing on key data points.']}], 'duration': 653.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/l8DCPaHc5TQ/pics/l8DCPaHc5TQ2840178.jpg', 'highlights': ['The chapter discusses how to use group by in SQL to create pivot report style datasets.', 'A specific example demonstrates the total amount for each geography using group by in SQL.', 'Creating complex reports using SQL joins and group by clauses.', 'Grouping at single and multiple levels in SQL provides insights into organizing and summarizing data.', 'Applying sort orders and limiting output to top 10 results in SQL provides practical techniques for organizing and focusing on key data points.']}], 'highlights': ['The video covers 50 SQL queries to learn and master SQL, including select operations, where clauses, joins, group buys, and more.', 'The chapter provides a full-length example data set and all the queries for download to facilitate practical learning.', 'Emphasizes the use of MySQL workbench for building SQL queries and visualizing output results.', 'The recommendation to set aside time and watch the video in one setting for best results is given.', 'Instructions for loading the Awesome Chocolates database are available in the video description.', 'The chapter covers using the SELECT statement in SQL to retrieve data, rearrange columns, perform calculations, rename columns, impose conditions, and filter data based on specified criteria.', 'The WHERE clause allows for filtering data based on specified conditions, such as retrieving sale amounts more than $10,000.', 'Using where clauses in SQL is one of the most important aspects when it comes to data analysis.', "The 'case' operator in SQL enables the categorization of data based on specified conditions, providing the ability to create custom labels for different ranges of data values, as demonstrated by categorizing sales amounts into different categories.", "Using the 'in' clause allows adding multiple conditions to retrieve all the desired results, providing a flexible approach to querying the data.", "The 'like' operator in SQL enables pattern matching, allowing for the retrieval of names based on specific patterns, such as names beginning with a certain letter or containing a specific letter.", 'The chapter emphasizes the importance of practicing SQL and provides practice problems for better understanding. The speaker stresses the importance of practicing SQL to avoid forgetting concepts and provides practice problems for better understanding.', 'The chapter emphasizes the need for further study to fully understand practical scenarios of using SQL joins.', "Different types of joins, including left join and right join, are explained with practical examples and comparisons to Excel's Power Query functionality.", 'The demonstration illustrates a basic join operation to display sales data with corresponding person names, using the sales and people tables.', 'Demonstration of using left join for consistency and distinguishing tables with aliases PR and P.', 'The chapter discusses joining multiple tables to access data from different tables, allowing the combination of queries to display multiple fields.', 'The chapter explains adding conditions on joins and demonstrates filtering data based on specific criteria, such as amount and team.', 'Understanding the data model to build better SQL queries, emphasizing the importance of knowing the data well to enhance query building, suggesting spending time to understand the data model before using SQL.', 'The chapter discusses how to use group by in SQL to create pivot report style datasets.', 'A specific example demonstrates the total amount for each geography using group by in SQL.', 'Creating complex reports using SQL joins and group by clauses.', 'Grouping at single and multiple levels in SQL provides insights into organizing and summarizing data.', 'Applying sort orders and limiting output to top 10 results in SQL provides practical techniques for organizing and focusing on key data points.']}