title
SQL Tutorial for Beginners in Hindi (SQL Full Course) - Great Learning

description
🚀 Master SQL & Data Science with Great Learning! 🎓 Explore our expert-led programs like https://www.mygreatlearning.com/pg-program-data-science-and-business-analytics-course?utm_source=GLYT&utm_medium=youtube&utm_campaign=description&utm_content=sql_tutorial and https://www.mygreatlearning.com/pg-program-data-science-course for in-depth knowledge. Enroll now for career advancement! This SQL tutorial will give you a full understanding of SQL in Hindi. You will learn about various SQL commands, how to use them, and how to apply them to real-world scenarios. By the end of this tutorial, you will be able to confidently work with databases and manipulate data using SQL. SQL is abbreviated as Standard Query Language. SQL is a Query language that lets you store, access, and update data in the database, and it also helps create a new database and implement functions. Knowledge of SQL is a step toward becoming a software developer. Great Learning’s SQL Tutorial for beginner Hindi helps you master vital foundations and advanced SQL topics that would help the learner manage and administer the MYSQL database. You will learn the powerful Structured Query Language (SQL) to build and manipulate the database by acquiring knowledge on intrinsic topics like DBMS, DDL, DBML, SQL operators, etc. You will also learn the administration skills required to manage the database. 🔥1000+ Free Courses With Free Certificates: https://www.mygreatlearning.com/academy?ambassador_code=GLYT_DES_1dWCqjhvF58&utm_source=GLYT&utm_campaign=GLYT_DES_1dWCqjhvF58 🏁 Topics Covered: 00:00:00 - Agenda 00:01:18 - Introduction to DBMS & SQL 00:03:55 - Types of Data Models 00:09:09 - ER Diagram 00:09:39 - What is DBMS 00:14:17 - Introduction to RDBMS 00:16:14 - Features of RDBMS 00:18:30 - RDBMS vs Traditional Approach 00:19:10 - Normalization Subsets of Normalization 00:22:28 - Functional Dependency 00:25:42 - Types of Functional Dependency 00:27:55 - First Normal Form 1NF 00:30:20 - Second Normal Form 2NF 00:33:14 - Third normal form 3NF 00:34:44 - What is SQL? 00:36:06 - Why SQL? 00:36:54 - SQL Terms 00:37:48 - SQL Data Types 00:39:38 - SQL Constraints 00:42:50 - SQL Command Groups 00:43:06 - DDL 00:55:39 - DML 01:05:42 - DCL 01:05:45 - TCL 01:09:53 - SQL Operators 01:35:38 - SQL Functions 01:51:12 - SQL Joins 02:30:15 - Lab Session Free SQL Courses With Free Certificates: ✔ My SQL Basics course: https://glacad.me/3DX1rWU ✔ SQL Interview Questions and Answers: https://www.mygreatlearning.com/academy/learn-for-free/courses/sql-interview-questions-and-answers?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP22 ✔ SQL Projects for Beginners: https://www.mygreatlearning.com/academy/learn-for-free/courses/sql-projects-for-beginners?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP22 ✔ SQL for Data Science: https://www.mygreatlearning.com/academy/learn-for-free/courses/sql-for-data-science?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP22 ✔ SQL with Python: https://www.mygreatlearning.com/academy/learn-for-free/courses/sql-with-python?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP22 ✔ Advanced SQL: https://www.mygreatlearning.com/academy/learn-for-free/courses/advanced-sql?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP22 Here is a list of our other full course videos you can check out: ✔ Data Science Tutorial: https://www.youtube.com/watch?v=u2zsY-2uZiE&t=680s ✔ Python for Data Science: https://www.youtube.com/watch?v=edvg4eHi_Mw&t=15700s ✔ Machine Learning with Python: https://www.youtube.com/watch?v=RnFGwxJwx-0&t=8732s ✔ Statistics for Data Science: https://www.youtube.com/watch?v=Vfo5le26IhY&t=189s ✔ Tableau Training for Beginners: https://www.youtube.com/watch?v=6mBtTNggkUk&t=1735s ✔ Reinforcement Learning Tutorial: https://www.youtube.com/watch?v=f8bnkro3yXY&t=9940s ⚡ About Great Learning: With more than 5.4 Million+ learners in 170+ countries, Great Learning, a part of the BYJU'S group, is a leading global edtech company for professional and higher education offering industry-relevant programs in the blended, classroom, and purely online modes across technology, data and business domains. These programs are developed in collaboration with top institutions like Stanford Executive Education, MIT Professional Education, The University of Texas at Austin, NUS, IIT Madras, IIT Bombay & more. 🔹 For more updates on courses and tips follow us on: ✅ Telegram: https://t.me/GreatLearningAcademy ✅ Facebook: https://www.facebook.com/GreatLearnin... ✅ LinkedIn: https://www.linkedin.com/company/grea... ✅ Follow our Blog: https://glacad.me/GL_Blog #sql #sqltutorial #sqlfullcourse #sqlforbeginners #sqltraining #GreatLearning

detail
{'title': 'SQL Tutorial for Beginners in Hindi (SQL Full Course) - Great Learning', 'heatmap': [{'end': 2038.833, 'start': 1923.788, 'weight': 0.704}, {'end': 2678.284, 'start': 2565.245, 'weight': 0.737}, {'end': 3317.727, 'start': 2886.802, 'weight': 0.893}, {'end': 3959.618, 'start': 3847.762, 'weight': 0.798}, {'end': 4283.019, 'start': 4168.399, 'weight': 0.712}, {'end': 5456.809, 'start': 5359.341, 'weight': 0.929}, {'end': 5790.242, 'start': 5665.208, 'weight': 0.886}, {'end': 6096.603, 'start': 5987.453, 'weight': 0.806}, {'end': 6959.734, 'start': 6737.452, 'weight': 0.916}, {'end': 7705.311, 'start': 7269.722, 'weight': 1}], 'summary': 'This sql tutorial for beginners in hindi covers sql fundamentals, database normalization, data management, operators, functions, joins, queries, data analysis in cricket, sql functions, table creation, geolocation, and bank holiday tables, emphasizing practical examples and their applications in database management.', 'chapters': [{'end': 1300.155, 'segs': [{'end': 82.358, 'src': 'embed', 'start': 3.854, 'weight': 0, 'content': [{'end': 6.675, 'text': 'Hey guys, you are all welcome to this Great Learning session.', 'start': 3.854, 'duration': 2.821}, {'end': 13.438, 'text': 'Today we will learn a very important language which is Structured Query Language.', 'start': 6.735, 'duration': 6.703}, {'end': 16.52, 'text': 'If you look around, we have data everywhere.', 'start': 13.958, 'duration': 2.562}, {'end': 22.262, 'text': 'And if we want to deal with this data properly, then we need to know about SQL.', 'start': 16.56, 'duration': 5.702}, {'end': 27.905, 'text': 'So, after knowing the importance of SQL, we have brought this full course.', 'start': 24.543, 'duration': 3.362}, {'end': 30.026, 'text': "So, first of all, let's see this agenda.", 'start': 28.225, 'duration': 1.801}, {'end': 37.93, 'text': 'So, we will start by knowing what the database is, then we will understand the database, then we will know the introduction of DBMS and SQL.', 'start': 30.486, 'duration': 7.444}, {'end': 42.452, 'text': 'After that, we will understand what normalization is and what its importance is.', 'start': 37.95, 'duration': 4.502}, {'end': 50.899, 'text': 'In SQL, we will work with different languages like DDL, DML, DCL, and TCL.', 'start': 44.413, 'duration': 6.486}, {'end': 56.244, 'text': 'In SQL, we will work with different operators and functions.', 'start': 51.239, 'duration': 5.005}, {'end': 58.385, 'text': 'Another important concept in SQL is Joins.', 'start': 56.324, 'duration': 2.061}, {'end': 63.069, 'text': 'So, we will work well with joins.', 'start': 61.608, 'duration': 1.461}, {'end': 71.612, 'text': 'And finally, there will be a comprehensive lab session in which we will implement three mini projects and take three different data sets.', 'start': 63.169, 'duration': 8.443}, {'end': 75.034, 'text': 'In that, we will implement some structured query language.', 'start': 71.712, 'duration': 3.322}, {'end': 77.314, 'text': 'So, we will start the session now.', 'start': 75.054, 'duration': 2.26}, {'end': 82.358, 'text': "Let's understand what a database is.", 'start': 78.955, 'duration': 3.403}], 'summary': 'Learn sql in this comprehensive course covering database, dbms, normalization, different sql languages, operators, functions, joins, and three mini projects with structured query language.', 'duration': 78.504, 'max_score': 3.854, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF583854.jpg'}, {'end': 421.242, 'src': 'embed', 'start': 386.973, 'weight': 3, 'content': [{'end': 389.174, 'text': "Let's understand what is Relational Data Model.", 'start': 386.973, 'duration': 2.201}, {'end': 397.819, 'text': 'In Relational Data Model, whatever data you have, you store that data in the form of rows and columns.', 'start': 389.714, 'duration': 8.105}, {'end': 402.524, 'text': 'You have rows and columns in the table.', 'start': 400.1, 'duration': 2.424}, {'end': 405.369, 'text': 'This is tabular data and it is a relational data model.', 'start': 402.564, 'duration': 2.805}, {'end': 421.242, 'text': 'If you look at every individual column, then every individual column will tell you about a specific attribute or a specific feature.', 'start': 412.538, 'duration': 8.704}], 'summary': 'Relational data model stores data in rows and columns, representing tabular data with specific attributes.', 'duration': 34.269, 'max_score': 386.973, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF58386973.jpg'}, {'end': 572.78, 'src': 'embed', 'start': 545.882, 'weight': 4, 'content': [{'end': 549.144, 'text': 'So, these are the basic features of our different types of data models.', 'start': 545.882, 'duration': 3.262}, {'end': 551.846, 'text': "After this, let's look at the ER diagram.", 'start': 549.224, 'duration': 2.622}, {'end': 561.713, 'text': 'What is the ER diagram? It is an entity relationship diagram, which gives the table or the relationship a relationship blueprint.', 'start': 551.866, 'duration': 9.847}, {'end': 567.256, 'text': "So, let's assume that this is the ER diagram of the employee table.", 'start': 562.113, 'duration': 5.143}, {'end': 572.78, 'text': 'So what is the relationship between the employees with different features?', 'start': 567.376, 'duration': 5.404}], 'summary': 'Introduction to data models and er diagram for employee relationships.', 'duration': 26.898, 'max_score': 545.882, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF58545882.jpg'}, {'end': 622.551, 'src': 'embed', 'start': 594.199, 'weight': 5, 'content': [{'end': 597.623, 'text': 'The full form of DBMS is database management system.', 'start': 594.199, 'duration': 3.424}, {'end': 608.066, 'text': 'So now you have a database, but whatever data you have stored in that database, if you want to deal with it properly,', 'start': 598.163, 'duration': 9.903}, {'end': 610.907, 'text': 'then you need a management system.', 'start': 608.066, 'duration': 2.841}, {'end': 618.989, 'text': 'So if we take an example of this, suppose you have bought a laptop, but if you want to work with that laptop,', 'start': 611.087, 'duration': 7.902}, {'end': 622.551, 'text': 'then you will need an operating system like windows, linux or ios.', 'start': 619.409, 'duration': 3.142}], 'summary': 'Dbms is essential for managing database data effectively.', 'duration': 28.352, 'max_score': 594.199, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF58594199.jpg'}, {'end': 683.504, 'src': 'embed', 'start': 655.823, 'weight': 6, 'content': [{'end': 659.125, 'text': 'These are different operations in a database management system.', 'start': 655.823, 'duration': 3.302}, {'end': 666.891, 'text': 'As it is written here, the most basic operation is that we have to store data, so we can store new files in it.', 'start': 659.165, 'duration': 7.726}, {'end': 668.412, 'text': 'New files are our tables.', 'start': 666.911, 'duration': 1.501}, {'end': 670.516, 'text': 'So you can store new tables in it.', 'start': 668.953, 'duration': 1.563}, {'end': 674.586, 'text': 'After storing new tables, you can also add individual records.', 'start': 670.596, 'duration': 3.99}, {'end': 683.504, 'text': "So, if we take the employee table again, let's assume that initially you have 100 employees.", 'start': 678.1, 'duration': 5.404}], 'summary': 'Database management system involves storing data and adding new tables and records, with an example of initially having 100 employees.', 'duration': 27.681, 'max_score': 655.823, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF58655823.jpg'}, {'end': 1037.307, 'src': 'embed', 'start': 971.765, 'weight': 7, 'content': [{'end': 974.928, 'text': 'So, these are the advantages of RDBMS.', 'start': 971.765, 'duration': 3.163}, {'end': 976.83, 'text': 'After this, we will see some features of it.', 'start': 975.009, 'duration': 1.821}, {'end': 988.454, 'text': 'We store the data in the form of a table.', 'start': 983.072, 'duration': 5.382}, {'end': 998.537, 'text': 'An important point of RDBMS is that the database gives us the concept of primary key.', 'start': 989.134, 'duration': 9.403}, {'end': 1003.358, 'text': 'With the help of primary key, we can uniquely identify all the individual records in the database.', 'start': 999.057, 'duration': 4.301}, {'end': 1011.425, 'text': "Let's say you have 500 employees in your company.", 'start': 1008.942, 'duration': 2.483}, {'end': 1015.69, 'text': 'We have to identify those 500 employees individually.', 'start': 1011.445, 'duration': 4.245}, {'end': 1022.938, 'text': 'Because every employee is unique, it is not possible that every employee has a clone.', 'start': 1015.71, 'duration': 7.228}, {'end': 1024.56, 'text': 'Because that is not possible.', 'start': 1023.238, 'duration': 1.322}, {'end': 1032.743, 'text': "That's why we give a unique employee code or employee ID to every employee.", 'start': 1025, 'duration': 7.743}, {'end': 1037.307, 'text': 'Because there can be cases where two employees have the same name.', 'start': 1032.763, 'duration': 4.544}], 'summary': 'Rdbms advantages include table storage, primary key for unique record identification, and unique employee ids for 500 employees.', 'duration': 65.542, 'max_score': 971.765, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF58971765.jpg'}, {'end': 1114.754, 'src': 'embed', 'start': 1060.224, 'weight': 11, 'content': [{'end': 1065.189, 'text': 'And because both are different people, both should have different employee IDs.', 'start': 1060.224, 'duration': 4.965}, {'end': 1070.935, 'text': 'So in this way we will be able to identify that both are different employees.', 'start': 1065.209, 'duration': 5.726}, {'end': 1074.087, 'text': 'Then we have foreign keys.', 'start': 1072.526, 'duration': 1.561}, {'end': 1076.969, 'text': 'We identify uniqueness in one individual table.', 'start': 1074.107, 'duration': 2.862}, {'end': 1081.492, 'text': 'Similarly, we can find uniqueness across tables.', 'start': 1077.029, 'duration': 4.463}, {'end': 1083.033, 'text': 'We can use foreign keys.', 'start': 1081.532, 'duration': 1.501}, {'end': 1089.278, 'text': "When we are retrieving data, let's say you have a huge data set with millions of rows and thousands of columns.", 'start': 1083.053, 'duration': 6.225}, {'end': 1106.356, 'text': 'So if you want to retrieve records from these big data sets, then you need indexes to process them quickly.', 'start': 1099.465, 'duration': 6.891}, {'end': 1108.199, 'text': 'So RDBMS will also provide you with that.', 'start': 1106.376, 'duration': 1.823}, {'end': 1114.754, 'text': "Now let's understand the difference between traditional and RDBMS approach.", 'start': 1111.292, 'duration': 3.462}], 'summary': 'Different employee ids for unique identification; rdbms provides indexes for quick data retrieval.', 'duration': 54.53, 'max_score': 1060.224, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581060224.jpg'}, {'end': 1176.575, 'src': 'embed', 'start': 1147.329, 'weight': 9, 'content': [{'end': 1149.471, 'text': "So now let's understand what is normalization.", 'start': 1147.329, 'duration': 2.142}, {'end': 1154.677, 'text': 'This is a very important concept in SQL or database management system.', 'start': 1149.511, 'duration': 5.166}, {'end': 1161.645, 'text': 'When you have a very big table with a lot of data, then it has to be brought into a proper structure.', 'start': 1154.697, 'duration': 6.948}, {'end': 1166.09, 'text': 'So to bring it into a proper structure, we use normalization.', 'start': 1161.805, 'duration': 4.285}, {'end': 1176.575, 'text': 'Normalization helps us to decompose larger complex tables into simpler and smaller ones.', 'start': 1169.793, 'duration': 6.782}], 'summary': 'Normalization in sql helps decompose large tables into simpler and smaller ones.', 'duration': 29.246, 'max_score': 1147.329, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581147329.jpg'}], 'start': 3.854, 'title': 'Sql, database, data models & dbms', 'summary': 'Introduces sql, databases, data modeling, and dbms; covers relational data model, rdbms operations, advantages, and data management basics, emphasizing the importance of sql in managing and accessing data, and its benefits in terms of storage space and processing time.', 'chapters': [{'end': 354.331, 'start': 3.854, 'title': 'Introduction to sql and database', 'summary': 'Introduces structured query language (sql) and databases, emphasizing the importance of sql in managing and accessing data, covering topics such as database organization, data modeling, and sql operations, culminating in a comprehensive lab session implementing structured query language.', 'duration': 350.477, 'highlights': ['Structured Query Language (SQL) is the focus of the session, emphasizing its importance in managing and accessing data. The session emphasizes the importance of SQL in properly dealing with the abundance of data, highlighting its significance in the 21st century with the generation of terabytes of data every second.', 'The session covers database organization, data modeling, and SQL operations, including DDL, DML, DCL, TCL, operators, functions, and joins. The session covers various aspects of database organization, data modeling, and SQL operations, encompassing topics such as normalization, DDL, DML, DCL, TCL, operators, functions, and joins.', 'A comprehensive lab session is planned, implementing structured query language with three mini projects and different data sets. The session culminates in a comprehensive lab session where participants will implement structured query language through three mini projects using different data sets.']}, {'end': 655.563, 'start': 357.743, 'title': 'Data models & dbms overview', 'summary': 'Covers data models including the relational data model, hierarchical data model, and network data model, with a focus on the relational data model storing data in tabular format, and the overview of the database management system (dbms) as the system required for proper interaction with the stored data.', 'duration': 297.82, 'highlights': ['The most majorly used data model is the Relational Data Model, where data is stored in rows and columns. The relevance of the Relational Data Model as the most widely used data model.', 'In the Relational Data Model, data is stored in tabular format with rows and columns, where each column represents a specific attribute or feature. Explanation of the tabular format and the representation of attributes in the Relational Data Model.', 'The Hierarchical Data Model is described as having a tree-based structure with a single root node and multiple branches representing child nodes. Overview of the structure of the Hierarchical Data Model.', 'The Network Data Model is characterized by a graph-based model where every node represents a record, allowing for multiple parent and child nodes. Description of the graph-based structure and the flexibility of parent-child relationships in the Network Data Model.', 'The ER diagram is introduced as an entity relationship diagram, providing a relationship blueprint for tables or relationships in a database. Introduction and purpose of the ER diagram in defining relationships within a database.', 'The overview of a database management system (DBMS) is provided, emphasizing its role as a system required for proper interaction with the stored data. Overview of the necessity and purpose of a database management system (DBMS) for interacting with stored data.']}, {'end': 1011.425, 'start': 655.823, 'title': 'Dbms & rdbms operations and advantages', 'summary': 'Covers the basic operations of a database management system including storing, adding, retrieving, modifying, and removing data, with the advantages of data sharing, security, and backup & recovery. it also introduces the concept of rdbms and its advantages, focusing on the structured format using tables and the concept of primary key for unique identification of records.', 'duration': 355.602, 'highlights': ['The chapter covers the basic operations of a database management system including storing, adding, retrieving, modifying, and removing data. It explains the fundamental operations of a DBMS including storing new tables, adding individual records, retrieving data, modifying data, and removing records.', 'The advantages of data sharing, security, and backup & recovery are discussed in relation to DBMS. It mentions the advantages of data sharing across applications, good security, and backup & recovery features in a DBMS, ensuring data integrity and confidentiality.', 'The concept of RDBMS and its advantages are introduced, focusing on the structured format using tables and the concept of primary key for unique identification of records. It introduces RDBMS, emphasizing the structured format using rows and columns, and the concept of primary key for uniquely identifying individual records in the database.']}, {'end': 1300.155, 'start': 1011.445, 'title': 'Database management basics', 'summary': 'Explains the importance of unique employee ids, foreign keys, indexes, rdbms approach, normalization, and the need for normalization in managing a large dataset, highlighting the benefits in terms of storage space and processing time.', 'duration': 288.71, 'highlights': ['Normalization helps to decompose larger complex tables into simpler and smaller ones, reducing unnecessary storage space and processing time. Importance of normalization in managing large datasets, reducing storage space and processing time.', 'Unique employee IDs are necessary to identify different individuals even if they share similar attributes such as name, age, salary, and department. Importance of unique employee IDs in differentiating individuals with similar attributes.', 'Foreign keys are used to identify uniqueness within individual tables and across tables. Utilization of foreign keys to ensure uniqueness within and across tables.', 'Indexes are essential for processing large datasets quickly in RDBMS. Significance of indexes in processing large datasets efficiently.', 'Explanation of the traditional approach of storing data in normal files and the requirement of data in tabular format for RDBMS systems. Comparison between traditional approach and RDBMS systems in terms of data storage format.']}], 'duration': 1296.301, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF583854.jpg', 'highlights': ['Structured Query Language (SQL) is crucial for managing and accessing data, especially in the 21st century with the generation of terabytes of data every second.', 'Comprehensive coverage of database organization, data modeling, and SQL operations, including DDL, DML, DCL, TCL, operators, functions, and joins.', 'Implementation of structured query language through three mini projects using different data sets in a comprehensive lab session.', 'Relational Data Model is the most widely used data model, storing data in tabular format with rows and columns.', 'Introduction and purpose of the ER diagram in defining relationships within a database.', 'Overview of the necessity and purpose of a database management system (DBMS) for interacting with stored data.', 'Explanation of the fundamental operations of a DBMS including storing, adding, retrieving, modifying, and removing data.', 'Advantages of data sharing, security, and backup & recovery in a DBMS, ensuring data integrity and confidentiality.', 'Introduction of RDBMS, emphasizing the structured format using rows and columns, and the concept of primary key for uniquely identifying individual records in the database.', 'Importance of normalization in managing large datasets, reducing storage space and processing time.', 'Importance of unique employee IDs in differentiating individuals with similar attributes.', 'Utilization of foreign keys to ensure uniqueness within and across tables.', 'Significance of indexes in processing large datasets efficiently.', 'Comparison between traditional approach and RDBMS systems in terms of data storage format.']}, {'end': 2366.103, 'segs': [{'end': 1354.176, 'src': 'embed', 'start': 1300.155, 'weight': 0, 'content': [{'end': 1308.797, 'text': 'because there is a lot of duplicate information in your body table, you can remove the duplicate data with normalization.', 'start': 1300.155, 'duration': 8.642}, {'end': 1318.14, 'text': 'For example, we have a database with 4 columns Student details, course details, pre-requisite and result details.', 'start': 1309.697, 'duration': 8.443}, {'end': 1324.182, 'text': 'In student details we have student ID, name, date of birth, etc.', 'start': 1318.14, 'duration': 6.042}, {'end': 1333.005, 'text': 'For now, keep this table in your mind, and I would like to tell you that there are some problems in this table.', 'start': 1324.202, 'duration': 8.803}, {'end': 1339.315, 'text': 'For now, keep thinking about the problems in this table.', 'start': 1335.287, 'duration': 4.028}, {'end': 1344.246, 'text': 'Later, when I explain the concepts of 1NF and 2NF, it will be clear to you what the problem is in this table.', 'start': 1339.335, 'duration': 4.911}, {'end': 1354.176, 'text': 'So now to understand the different normal forms 1NF, 2NF, 3NF, we have to know the functional dependency first.', 'start': 1346.63, 'duration': 7.546}], 'summary': 'Remove duplicate data with normalization in database tables.', 'duration': 54.021, 'max_score': 1300.155, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581300155.jpg'}, {'end': 1480.316, 'src': 'embed', 'start': 1453.797, 'weight': 8, 'content': [{'end': 1460.082, 'text': 'So this functional dependency marks is functionally dependent on student and course.', 'start': 1453.797, 'duration': 6.285}, {'end': 1466.828, 'text': 'And similarly here course name is dependent on course and grade is dependent on marks.', 'start': 1460.963, 'duration': 5.865}, {'end': 1480.316, 'text': 'Suppose the student gets A grade, what does it mean? Obviously, the range of the marks will be defined, so we are setting the grade accordingly.', 'start': 1468.888, 'duration': 11.428}], 'summary': 'Functional dependency between student, course, marks, and grades impacts grading criteria.', 'duration': 26.519, 'max_score': 1453.797, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581453797.jpg'}, {'end': 1566.559, 'src': 'embed', 'start': 1532.425, 'weight': 3, 'content': [{'end': 1539.007, 'text': 'So here student details and course details gives us exactly one value of marks.', 'start': 1532.425, 'duration': 6.582}, {'end': 1541.208, 'text': 'So this is called functional dependency.', 'start': 1539.068, 'duration': 2.14}, {'end': 1544.71, 'text': 'So again we have two types of functional dependency.', 'start': 1541.248, 'duration': 3.462}, {'end': 1550.232, 'text': 'One is partial functional dependency and second is transitive dependency.', 'start': 1544.75, 'duration': 5.482}, {'end': 1553.333, 'text': 'So first we will know what is partial functional dependency.', 'start': 1550.252, 'duration': 3.081}, {'end': 1566.559, 'text': 'So in partial functional dependency attribute Q is partially dependent on attribute P if and only if it is dependent on the subset of attribute P.', 'start': 1554.173, 'duration': 12.386}], 'summary': 'Functional dependency involves one value of marks; two types: partial and transitive dependency.', 'duration': 34.134, 'max_score': 1532.425, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581532425.jpg'}, {'end': 1651.139, 'src': 'embed', 'start': 1614.013, 'weight': 4, 'content': [{'end': 1615.194, 'text': 'Here we have an example.', 'start': 1614.013, 'duration': 1.181}, {'end': 1617.216, 'text': 'The course is dependent on the student.', 'start': 1615.214, 'duration': 2.002}, {'end': 1631.046, 'text': 'marks is dependent on the course.', 'start': 1627.984, 'duration': 3.062}, {'end': 1635.589, 'text': 'so the grade of the student is dependent on the student.', 'start': 1631.046, 'duration': 4.543}, {'end': 1639.991, 'text': "so if we just say that we got A grade, then it doesn't make any sense.", 'start': 1635.589, 'duration': 4.402}, {'end': 1649.177, 'text': 'so you have to tell which student in which course this grade.', 'start': 1639.991, 'duration': 9.186}, {'end': 1651.139, 'text': "Otherwise, obviously, it won't make sense.", 'start': 1649.217, 'duration': 1.922}], 'summary': 'Grades depend on students and courses, not standalone.', 'duration': 37.126, 'max_score': 1614.013, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581614013.jpg'}, {'end': 1708.316, 'src': 'embed', 'start': 1682.335, 'weight': 5, 'content': [{'end': 1688.501, 'text': 'This means that all the attributes in the column should be single-valued attributes.', 'start': 1682.335, 'duration': 6.166}, {'end': 1693.065, 'text': 'If they are multi-valued attributes then you have to convert them into single-valued attributes.', 'start': 1688.581, 'duration': 4.484}, {'end': 1701.351, 'text': "and there can't be any repeating elements in it means there can't be any duplicate values or group of elements in it.", 'start': 1695.027, 'duration': 6.324}, {'end': 1708.316, 'text': 'so if these two conditions are satisfied, then you can keep data in first normal form.', 'start': 1701.351, 'duration': 6.965}], 'summary': 'Data in first normal form requires single-valued attributes and no repeating elements.', 'duration': 25.981, 'max_score': 1682.335, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581682335.jpg'}, {'end': 2038.833, 'src': 'heatmap', 'start': 1923.788, 'weight': 0.704, 'content': [{'end': 1926.17, 'text': 'So, this partial dependency is not required.', 'start': 1923.788, 'duration': 2.382}, {'end': 1928.051, 'text': 'I need individual tables of these.', 'start': 1926.25, 'duration': 1.801}, {'end': 1932.695, 'text': 'So, what will I do? I will separate the student table here.', 'start': 1928.071, 'duration': 4.624}, {'end': 1937.278, 'text': 'I will take the student name, date of birth and put it in a table.', 'start': 1932.715, 'duration': 4.563}, {'end': 1940.08, 'text': 'Again, I will give the course name and prerequisite.', 'start': 1937.298, 'duration': 2.782}, {'end': 1942.622, 'text': 'I will put it in a separate table.', 'start': 1941.101, 'duration': 1.521}, {'end': 1946.904, 'text': 'Again, I will take duration and date of exam, I will put it in a separate table.', 'start': 1942.642, 'duration': 4.262}, {'end': 1950.246, 'text': 'So, in this way, I am separating the partial dependency.', 'start': 1947.204, 'duration': 3.042}, {'end': 1955.489, 'text': 'So, split or decompose the tables to remove partial dependencies.', 'start': 1950.406, 'duration': 5.083}, {'end': 1958.651, 'text': 'So, if there is confusion, I will repeat it again here.', 'start': 1956.149, 'duration': 2.502}, {'end': 1963.774, 'text': 'Partial dependency is that the course is partially dependent on the student.', 'start': 1959.011, 'duration': 4.763}, {'end': 1968.316, 'text': 'I will have to remove this partial dependence.', 'start': 1966.195, 'duration': 2.121}, {'end': 1974.02, 'text': 'Again, the result is partially dependent on the course.', 'start': 1968.376, 'duration': 5.644}, {'end': 1975.241, 'text': 'I will have to remove that.', 'start': 1974.12, 'duration': 1.121}, {'end': 1978.763, 'text': 'So, if I remove it, then it will become our to and if.', 'start': 1975.741, 'duration': 3.022}, {'end': 1981.264, 'text': 'We have divided our student table.', 'start': 1978.803, 'duration': 2.461}, {'end': 1984.947, 'text': 'Again, we have separated our course table.', 'start': 1981.364, 'duration': 3.583}, {'end': 1987.368, 'text': 'And we have also separated our result table.', 'start': 1985.107, 'duration': 2.261}, {'end': 1996.653, 'text': 'Then we have to bring our table in 3NF.', 'start': 1990.55, 'duration': 6.103}, {'end': 2001.935, 'text': 'For 3NF, first the table needs to be in 2NF.', 'start': 1997.173, 'duration': 4.762}, {'end': 2009.298, 'text': 'Then there needs to be no transitive dependency between non-key attributes and key attributes.', 'start': 2002.335, 'duration': 6.963}, {'end': 2011.379, 'text': "So, let's understand this again.", 'start': 2009.898, 'duration': 1.481}, {'end': 2015.3, 'text': 'So, we have this course table or result table.', 'start': 2011.919, 'duration': 3.381}, {'end': 2021.562, 'text': 'So, here our grade is transitive dependency on marks.', 'start': 2015.7, 'duration': 5.862}, {'end': 2026.524, 'text': 'So, marks, as you can see, this sequence is student, course, marks, grade.', 'start': 2022.003, 'duration': 4.521}, {'end': 2032.667, 'text': 'Means grade is dependent on marks, marks is dependent on course and course is dependent on student.', 'start': 2026.544, 'duration': 6.123}, {'end': 2038.833, 'text': 'But I will have to separate the grade and marks.', 'start': 2033.849, 'duration': 4.984}], 'summary': 'Decompose tables to remove partial dependencies and achieve 3nf.', 'duration': 115.045, 'max_score': 1923.788, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581923788.jpg'}, {'end': 2197.91, 'src': 'embed', 'start': 2167.236, 'weight': 2, 'content': [{'end': 2170.797, 'text': "Now let's understand the importance of SQL.", 'start': 2167.236, 'duration': 3.561}, {'end': 2176.379, 'text': 'SQL allows the different users to communicate i.e. access and manipulate the database.', 'start': 2171.177, 'duration': 5.202}, {'end': 2183.341, 'text': 'SQL allows you to create and manipulate the database.', 'start': 2177.299, 'duration': 6.042}, {'end': 2187.282, 'text': 'SQL also allows users to retrieve data from a database.', 'start': 2183.781, 'duration': 3.501}, {'end': 2197.91, 'text': 'So if your database has a lot of information, then if we take that college database or employee database or any organization database,', 'start': 2188.223, 'duration': 9.687}], 'summary': 'Sql is important for communication, creating/manipulating databases, and retrieving data.', 'duration': 30.674, 'max_score': 2167.236, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582167236.jpg'}, {'end': 2280.38, 'src': 'embed', 'start': 2251.95, 'weight': 1, 'content': [{'end': 2256.172, 'text': 'right, this is important because SQL works with RDBMS.', 'start': 2251.95, 'duration': 4.222}, {'end': 2258.033, 'text': 'what is RDBMS?', 'start': 2256.172, 'duration': 1.861}, {'end': 2265.176, 'text': 'relational database management system and, in relational database management system, in rows and columns format.', 'start': 2258.033, 'duration': 7.143}, {'end': 2268.677, 'text': 'Then we will see different SQL data types.', 'start': 2265.476, 'duration': 3.201}, {'end': 2272.118, 'text': 'First of all, we have numeric data types.', 'start': 2268.717, 'duration': 3.401}, {'end': 2273.298, 'text': 'These are different types.', 'start': 2272.138, 'duration': 1.16}, {'end': 2277.779, 'text': 'Bit, TinyInt, SmallInt, Int and BigInt.', 'start': 2273.738, 'duration': 4.041}, {'end': 2280.38, 'text': 'These are different sizes of integer.', 'start': 2277.819, 'duration': 2.561}], 'summary': 'Introduction to sql and rdbms, covering data types including bit, tinyint, smallint, int, and bigint.', 'duration': 28.43, 'max_score': 2251.95, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582251950.jpg'}], 'start': 1300.155, 'title': 'Database normalization and sql', 'summary': 'Covers the process of data normalization with 4 columns, addressing problems in the table, and introduces functional dependency and normal forms 1nf, 2nf, 3nf. it also discusses functional dependency, partial dependency, and transitive dependency in the database, explaining normalization to achieve proper data structure. additionally, it introduces sql and its importance in accessing, manipulating, and retrieving data from a database, along with an overview of sql data types.', 'chapters': [{'end': 1354.176, 'start': 1300.155, 'title': 'Data normalization and functional dependency', 'summary': 'Discusses the process of removing duplicate data through normalization in a database with 4 columns, and introduces the concept of functional dependency and the different normal forms 1nf, 2nf, 3nf, to address the problems in the table.', 'duration': 54.021, 'highlights': ['The chapter explains the process of removing duplicate data through normalization in a database with 4 columns, including Student details, course details, pre-requisite, and result details.', 'Introduces the concept of functional dependency and the different normal forms 1NF, 2NF, 3NF, to address the problems in the table.', 'Emphasizes the importance of understanding functional dependency first in order to comprehend the different normal forms.']}, {'end': 2088.632, 'start': 1354.196, 'title': 'Database functional dependency', 'summary': 'Discusses functional dependency, partial dependency, and transitive dependency in a database, and explains the process of normalization to achieve first, second, and third normal forms, to remove partial and transitive dependencies, and create a proper data structure.', 'duration': 734.436, 'highlights': ['The chapter explains that marks of a student are functionally dependent on the course and the student, and gives examples to illustrate the concept of functional dependency. Examples of marks being dependent on the course and student are provided.', 'The concept of partial functional dependency is defined, stating that an attribute is partially dependent on another attribute if it is dependent on a subset of that attribute, and it is related to student and course details as well as marks and grade. Definition and examples of partial functional dependency are provided, relating to student and course details, as well as marks and grade.', 'Transitive dependency is explained using the example of course being dependent on the student, marks being dependent on the course, and the grade of the student being dependent on the student, illustrating the need to specify the student and the course for a particular grade to make sense. Explanation and examples of transitive dependency are provided, emphasizing the need to specify the student and course for a particular grade to make sense.', 'The process of normalization to achieve first, second, and third normal forms is detailed, highlighting the need for atomic attributes, conversion of multi-valued attributes into single-valued attributes, and the removal of repeating elements. Detailed explanation of the process of normalization to achieve first, second, and third normal forms is provided, highlighting the need for atomic attributes and the removal of repeating elements.']}, {'end': 2366.103, 'start': 2089.112, 'title': 'Introduction to sql and data types', 'summary': 'Introduces sql as structured query language and explains its importance in accessing, manipulating, and retrieving data from a database, along with an overview of sql data types including numeric, character, and date types.', 'duration': 276.991, 'highlights': ['SQL allows different users to communicate, access, and manipulate the database, and retrieve data from a database. SQL facilitates communication, access, manipulation, and retrieval of data from a database, enabling interaction among different users.', 'Introduction to SQL data types including numeric types (Bit, TinyInt, SmallInt, Int, BigInt) and character/string types (Char, VarChar, Text), as well as date types (Date, Time, Timestamp, Year). Overview of SQL data types encompassing numeric types (Bit, TinyInt, SmallInt, Int, BigInt), character/string types (Char, VarChar, Text), and date types (Date, Time, Timestamp, Year).', 'Explanation of the importance of SQL in working with databases, and the necessity of a language like SQL to interact with databases in a tabular format. Emphasizes the significance of SQL in database operations and the need for a language like SQL to interact with databases in a tabular format.']}], 'duration': 1065.948, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF581300155.jpg', 'highlights': ['The process of normalization in a database with 4 columns: Student details, course details, pre-requisite, and result details.', 'Introduction to SQL data types: numeric types (Bit, TinyInt, SmallInt, Int, BigInt), character/string types (Char, VarChar, Text), and date types (Date, Time, Timestamp, Year).', 'Explanation of the importance of SQL in working with databases and the necessity of a language like SQL to interact with databases in a tabular format.', 'The concept of partial functional dependency, providing examples related to student and course details, as well as marks and grade.', 'Transitive dependency explained using examples, emphasizing the need to specify the student and course for a particular grade to make sense.', 'The process of normalization to achieve first, second, and third normal forms, highlighting the need for atomic attributes and the removal of repeating elements.', 'SQL facilitates communication, access, manipulation, and retrieval of data from a database, enabling interaction among different users.', 'Emphasizes the importance of understanding functional dependency first in order to comprehend the different normal forms.', 'Marks of a student are functionally dependent on the course and the student, with examples to illustrate the concept of functional dependency.']}, {'end': 4183.548, 'segs': [{'end': 2393.029, 'src': 'embed', 'start': 2366.103, 'weight': 0, 'content': [{'end': 2369.644, 'text': 'After that, you have to store miscellaneous data.', 'start': 2366.103, 'duration': 3.541}, {'end': 2372.604, 'text': 'If you want to store JSON data, then you can do that.', 'start': 2369.724, 'duration': 2.88}, {'end': 2375.445, 'text': 'If you want to store XML data, then you can also do that.', 'start': 2372.724, 'duration': 2.721}, {'end': 2378.006, 'text': 'These are the different types of data types.', 'start': 2375.645, 'duration': 2.361}, {'end': 2381.196, 'text': 'So, we have seen the data types.', 'start': 2379.093, 'duration': 2.103}, {'end': 2382.857, 'text': 'Now, there are some constraints in SQL.', 'start': 2381.216, 'duration': 1.641}, {'end': 2393.029, 'text': 'So, whenever you store data in a table, there are some basic rules that you want to follow about that data.', 'start': 2383.338, 'duration': 9.691}], 'summary': 'You can store json or xml data in sql tables with specific constraints.', 'duration': 26.926, 'max_score': 2366.103, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582366103.jpg'}, {'end': 2678.284, 'src': 'heatmap', 'start': 2524.353, 'weight': 1, 'content': [{'end': 2529.394, 'text': 'So wherever there are IDs, if you are taking the student database, then you can see the student ID.', 'start': 2524.353, 'duration': 5.041}, {'end': 2531.915, 'text': 'if there is an employee table, then employee ID.', 'start': 2529.394, 'duration': 2.521}, {'end': 2534.295, 'text': 'if there are teachers, then teacher ID.', 'start': 2531.915, 'duration': 2.38}, {'end': 2540.577, 'text': 'So, whenever you give a unique ID, then you are ensuring that every record is uniquely identified.', 'start': 2534.595, 'duration': 5.982}, {'end': 2543.338, 'text': 'Then there are some checks.', 'start': 2541.477, 'duration': 1.861}, {'end': 2547.199, 'text': 'Suppose you want to set some rain or some check, you can do it with this.', 'start': 2543.358, 'duration': 3.841}, {'end': 2552.101, 'text': 'Then we saw an example where we had a lot of data.', 'start': 2547.219, 'duration': 4.882}, {'end': 2559.063, 'text': 'Suppose there are millions of rows and you have 1000 columns and you want to retrieve data quickly.', 'start': 2552.121, 'duration': 6.942}, {'end': 2561.524, 'text': 'In this case, you can use index.', 'start': 2559.143, 'duration': 2.381}, {'end': 2564.025, 'text': 'These are some basic constraints.', 'start': 2561.684, 'duration': 2.341}, {'end': 2567.266, 'text': "Now let's see the subsets of SQL.", 'start': 2565.245, 'duration': 2.021}, {'end': 2576.772, 'text': 'In SQL, we have different sub-query languages like DDL, DML, DCL and TCL.', 'start': 2567.346, 'duration': 9.426}, {'end': 2585.718, 'text': 'DDL is Data Definition Language, DML is Data Manipulation Language, TCL is Data Control Language and TCL is Transaction Control Language.', 'start': 2577.152, 'duration': 8.566}, {'end': 2588.699, 'text': 'So, we understand each language very well.', 'start': 2586.198, 'duration': 2.501}, {'end': 2594.062, 'text': 'So, we will start with DDL which is Data Definition Language.', 'start': 2588.739, 'duration': 5.323}, {'end': 2599.345, 'text': 'So, we can create new objects with DDL or remove them.', 'start': 2594.443, 'duration': 4.902}, {'end': 2601.787, 'text': 'So, these are the commands in DDL.', 'start': 2599.365, 'duration': 2.422}, {'end': 2604.728, 'text': 'First of all, we have the CREATE command.', 'start': 2601.827, 'duration': 2.901}, {'end': 2610.171, 'text': 'So, with the CREATE command, either we can create a database or a table.', 'start': 2604.748, 'duration': 5.423}, {'end': 2619.399, 'text': 'For example, if I want to create a new database, it can be a student table or an employee table.', 'start': 2613.533, 'duration': 5.866}, {'end': 2624.484, 'text': 'Similarly, if I want to create a new table in a database, I can do it with the create command.', 'start': 2619.419, 'duration': 5.065}, {'end': 2635.418, 'text': 'After that, I have the alter command.', 'start': 2632.756, 'duration': 2.662}, {'end': 2640.302, 'text': 'With the alter command, I can alter a database structure.', 'start': 2635.438, 'duration': 4.864}, {'end': 2646.647, 'text': 'Suppose I already have a table, but I want to add a new column to that table.', 'start': 2640.322, 'duration': 6.325}, {'end': 2651.811, 'text': 'For that, I can use the alter command.', 'start': 2646.887, 'duration': 4.924}, {'end': 2657.675, 'text': 'Similarly, if I want to I have to change the data type of the column.', 'start': 2652.112, 'duration': 5.563}, {'end': 2664.738, 'text': 'Suppose I have kept salary as integer initially, then I have to convert it into decimal by changing the integer.', 'start': 2657.695, 'duration': 7.043}, {'end': 2667.139, 'text': 'I can use alter command for that.', 'start': 2664.758, 'duration': 2.381}, {'end': 2669.82, 'text': 'After that, I will have the drop command.', 'start': 2667.219, 'duration': 2.601}, {'end': 2672.961, 'text': 'With the drop command, I can permanently delete the table.', 'start': 2670.64, 'duration': 2.321}, {'end': 2678.284, 'text': 'If I want to permanently delete the table or database, I will use the drop command.', 'start': 2673.061, 'duration': 5.223}], 'summary': 'Sql basics: unique ids for record identification, indexes for quick data retrieval, and subsets of sql (ddl, dml, dcl, tcl)', 'duration': 80.375, 'max_score': 2524.353, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582524353.jpg'}, {'end': 2844.721, 'src': 'embed', 'start': 2813.91, 'weight': 4, 'content': [{'end': 2818.114, 'text': 'So we go to mysql workbench and implement all this.', 'start': 2813.91, 'duration': 4.204}, {'end': 2824.193, 'text': 'so this is my mysql workbench.', 'start': 2821.152, 'duration': 3.041}, {'end': 2827.754, 'text': 'so here I can execute any code.', 'start': 2824.193, 'duration': 3.561}, {'end': 2829.975, 'text': 'so first of all I will make my table.', 'start': 2827.754, 'duration': 2.221}, {'end': 2833.197, 'text': 'so here I will write create table.', 'start': 2829.975, 'duration': 3.222}, {'end': 2836.058, 'text': 'after that I will have to write the name of the table.', 'start': 2833.197, 'duration': 2.861}, {'end': 2838.198, 'text': 'so the name of the table is employees.', 'start': 2836.058, 'duration': 2.14}, {'end': 2839.539, 'text': 'I have given round braces.', 'start': 2838.198, 'duration': 1.341}, {'end': 2844.721, 'text': 'then inside this I will give the name of the column and the data type of the column.', 'start': 2839.539, 'duration': 5.182}], 'summary': "Using mysql workbench to create a table named 'employees' with columns and data types.", 'duration': 30.811, 'max_score': 2813.91, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582813910.jpg'}, {'end': 3317.727, 'src': 'heatmap', 'start': 2886.802, 'weight': 0.893, 'content': [{'end': 2889.803, 'text': 'And I will set the primary key as the employee ID.', 'start': 2886.802, 'duration': 3.001}, {'end': 2894.524, 'text': 'So, I will send the employee ID column in the primary key.', 'start': 2889.823, 'duration': 4.701}, {'end': 2902.806, 'text': "Then I'll just put a semicolon here.", 'start': 2900.626, 'duration': 2.18}, {'end': 2907.007, 'text': 'So you have to remember this when your command is finished.', 'start': 2902.846, 'duration': 4.161}, {'end': 2909.508, 'text': 'After that, you have to put a semicolon here.', 'start': 2907.027, 'duration': 2.481}, {'end': 2911.588, 'text': 'Then you have to execute it.', 'start': 2909.548, 'duration': 2.04}, {'end': 2915.169, 'text': 'So what you are seeing here is the lightning bolt type.', 'start': 2911.628, 'duration': 3.541}, {'end': 2916.429, 'text': 'Click here.', 'start': 2915.189, 'duration': 1.24}, {'end': 2918.649, 'text': 'You can execute with this.', 'start': 2916.529, 'duration': 2.12}, {'end': 2924.63, 'text': 'So here you can see create table employees zero rows affected.', 'start': 2919.349, 'duration': 5.281}, {'end': 2927.671, 'text': 'This means we have successfully made this table.', 'start': 2924.65, 'duration': 3.021}, {'end': 2934.361, 'text': 'So, if we want to glance at the table, we will use the SELECT query.', 'start': 2929.158, 'duration': 5.203}, {'end': 2939.063, 'text': 'We will learn about it later, but I will show you initially.', 'start': 2934.501, 'duration': 4.562}, {'end': 2949.558, 'text': 'So, if you want to see the table that you have made, you will write SELECT, then you will write star from.', 'start': 2939.103, 'duration': 10.455}, {'end': 2953.922, 'text': 'then you will write the name of the table, which will be employees.', 'start': 2949.558, 'duration': 4.364}, {'end': 2956.385, 'text': 'then you execute this.', 'start': 2953.922, 'duration': 2.463}, {'end': 2963.071, 'text': 'we have successfully made a table here employee id, first name, last name and salary.', 'start': 2956.385, 'duration': 6.686}, {'end': 2975.939, 'text': "this star here, star, will show you all the records inside the table, Because we haven't inserted any record here, so it is showing us null, null,", 'start': 2963.071, 'duration': 12.868}, {'end': 2976.78, 'text': 'null and null.', 'start': 2975.939, 'duration': 0.841}, {'end': 2982.921, 'text': 'If we want to see the logical structure of the table, then we can use the describe command.', 'start': 2976.84, 'duration': 6.081}, {'end': 2989.183, 'text': 'So I will write describe here and then I will write the name of the table.', 'start': 2983.041, 'duration': 6.142}, {'end': 2990.763, 'text': 'That will be employees.', 'start': 2989.203, 'duration': 1.56}, {'end': 2992.867, 'text': 'then I will execute it.', 'start': 2991.966, 'duration': 0.901}, {'end': 2996.93, 'text': 'as you can see, it is giving me the whole structure of the table here.', 'start': 2992.867, 'duration': 4.063}, {'end': 2998.551, 'text': 'first of all, it is the field.', 'start': 2996.93, 'duration': 1.621}, {'end': 2999.091, 'text': 'and what is the field??', 'start': 2998.551, 'duration': 0.54}, {'end': 2999.952, 'text': 'it is the name of the column.', 'start': 2999.111, 'duration': 0.841}, {'end': 3001.633, 'text': 'so all these are the names of the columns.', 'start': 2999.952, 'duration': 1.681}, {'end': 3004.855, 'text': 'then it is the type, the data type of all of these columns.', 'start': 3001.633, 'duration': 3.222}, {'end': 3005.756, 'text': 'then null.', 'start': 3004.855, 'duration': 0.901}, {'end': 3006.717, 'text': 'where can it be null?', 'start': 3005.756, 'duration': 0.961}, {'end': 3009.399, 'text': 'so in the employee id it cannot be null.', 'start': 3006.977, 'duration': 2.422}, {'end': 3011.02, 'text': 'in the rest it can be null.', 'start': 3009.399, 'duration': 1.621}, {'end': 3012.901, 'text': 'then, which is my primary key?', 'start': 3011.02, 'duration': 1.881}, {'end': 3015.443, 'text': 'this is my employee id.', 'start': 3012.941, 'duration': 2.502}, {'end': 3021.587, 'text': "it is the primary key And I haven't set any default value and I haven't given any extra information.", 'start': 3015.443, 'duration': 6.144}, {'end': 3026.491, 'text': 'So, I have successfully created my employee table.', 'start': 3021.607, 'duration': 4.884}, {'end': 3035.877, 'text': 'After creating the employee table, I got to know later that I have to add a new column to it.', 'start': 3026.991, 'duration': 8.886}, {'end': 3040.16, 'text': 'So, initially, I only had the employee ID, first name, last name and salary.', 'start': 3035.938, 'duration': 4.222}, {'end': 3042.522, 'text': 'After that, I have to add a new column to it.', 'start': 3040.18, 'duration': 2.342}, {'end': 3051.026, 'text': 'So, if we want to alter or modify something in the table, we will use the alter table command.', 'start': 3043.903, 'duration': 7.123}, {'end': 3056.568, 'text': 'So, we will write alter table and then the name of the table.', 'start': 3051.186, 'duration': 5.382}, {'end': 3060.729, 'text': 'So, the name of the table will be employees, alter table employees.', 'start': 3056.588, 'duration': 4.141}, {'end': 3069.873, 'text': 'Then if I want to add a column in it, then add column, then I will give the name of the column, contact, then the data type of the column, integer.', 'start': 3061.19, 'duration': 8.683}, {'end': 3075.637, 'text': 'So in our Employees table I will add a new column.', 'start': 3070.834, 'duration': 4.803}, {'end': 3078.9, 'text': 'here I will write alter table.', 'start': 3075.637, 'duration': 3.263}, {'end': 3084.224, 'text': 'then the name of the table will be employees, add column.', 'start': 3078.9, 'duration': 5.324}, {'end': 3087.446, 'text': 'then the name of the column is contact.', 'start': 3084.224, 'duration': 3.222}, {'end': 3091.449, 'text': 'then I will have to give data type integer right.', 'start': 3087.446, 'duration': 4.003}, {'end': 3096.6, 'text': 'I have successfully added it.', 'start': 3093.977, 'duration': 2.623}, {'end': 3098.481, 'text': 'Now I can use the describe method.', 'start': 3096.66, 'duration': 1.821}, {'end': 3105.327, 'text': 'I will write here, describe where the name of the table is employees.', 'start': 3098.541, 'duration': 6.786}, {'end': 3111.193, 'text': 'And as you can see here, I have added a new column, contact.', 'start': 3107.77, 'duration': 3.423}, {'end': 3112.174, 'text': 'In the same way.', 'start': 3111.213, 'duration': 0.961}, {'end': 3118.679, 'text': 'I can use the select command here select, start from.', 'start': 3112.174, 'duration': 6.505}, {'end': 3127.443, 'text': 'I will write employees and this table is giving me, and initially I had salary and now I have added this contact column.', 'start': 3119.855, 'duration': 7.588}, {'end': 3132.208, 'text': 'After this I have to change the column name using alter table command.', 'start': 3127.643, 'duration': 4.565}, {'end': 3141.197, 'text': 'So in the previous command we used alter table employees add column.', 'start': 3132.688, 'duration': 8.509}, {'end': 3142.937, 'text': 'because we had to add a column.', 'start': 3141.557, 'duration': 1.38}, {'end': 3144.398, 'text': 'here we have written add column.', 'start': 3142.937, 'duration': 1.461}, {'end': 3150.679, 'text': 'now here we have to change the name of the column, so we will write rename column.', 'start': 3144.398, 'duration': 6.281}, {'end': 3154.981, 'text': 'so the whole command is alter table employees rename column.', 'start': 3150.679, 'duration': 4.302}, {'end': 3162.082, 'text': 'then here name of original column, then here name of column, which you would want to change to.', 'start': 3154.981, 'duration': 7.101}, {'end': 3164.783, 'text': 'so first the name of the column was contact.', 'start': 3162.082, 'duration': 2.701}, {'end': 3167.384, 'text': 'I want to change it to job code.', 'start': 3164.783, 'duration': 2.601}, {'end': 3171.046, 'text': 'Then I will use the same alter table command.', 'start': 3168.524, 'duration': 2.522}, {'end': 3174.289, 'text': 'I will write alter table.', 'start': 3171.106, 'duration': 3.183}, {'end': 3179.534, 'text': 'Then the name of the table is employees because I have to rename it.', 'start': 3174.409, 'duration': 5.125}, {'end': 3183.857, 'text': 'I will write rename column here.', 'start': 3180.154, 'duration': 3.703}, {'end': 3185.999, 'text': 'Then initially the name of the column was contact.', 'start': 3183.877, 'duration': 2.122}, {'end': 3190.663, 'text': 'Then I have to change the column to contact and code it as job.', 'start': 3186.039, 'duration': 4.624}, {'end': 3200.483, 'text': 'Right We have successfully executed this.', 'start': 3190.683, 'duration': 9.8}, {'end': 3203.164, 'text': 'Then we will use describe.', 'start': 3200.503, 'duration': 2.661}, {'end': 3208.425, 'text': 'The name of the table is employees.', 'start': 3203.444, 'duration': 4.981}, {'end': 3213.707, 'text': 'And in this way we have changed the name of this column.', 'start': 3210.526, 'duration': 3.181}, {'end': 3225.159, 'text': 'Right Then we will go and see how to delete all the records in this table.', 'start': 3217.694, 'duration': 7.465}, {'end': 3228.06, 'text': 'So here we will use truncate table.', 'start': 3225.199, 'duration': 2.861}, {'end': 3238.146, 'text': 'So whatever values are inside it, if we want to remove the values completely, delete the records, then we will use truncate table.', 'start': 3228.1, 'duration': 10.046}, {'end': 3238.427, 'text': 'So I..', 'start': 3238.347, 'duration': 0.08}, {'end': 3244.228, 'text': 'truncate table.', 'start': 3242.486, 'duration': 1.742}, {'end': 3251.534, 'text': 'truncate fair table, fair table.', 'start': 3244.228, 'duration': 7.306}, {'end': 3260.503, 'text': 'name is employees and i will execute it right now.', 'start': 3251.534, 'duration': 8.969}, {'end': 3269.008, 'text': 'i will write again select star from Employees.', 'start': 3260.503, 'duration': 8.505}, {'end': 3270.589, 'text': 'now I will execute it.', 'start': 3269.008, 'duration': 1.581}, {'end': 3275.771, 'text': "You will see a null because we haven't inserted any records in it.", 'start': 3271.009, 'duration': 4.762}, {'end': 3279.372, 'text': "And because we haven't inserted any records, the result is also showing null here.", 'start': 3275.831, 'duration': 3.541}, {'end': 3284.134, 'text': 'So, we have a drop table near the truncate command.', 'start': 3279.392, 'duration': 4.742}, {'end': 3285.394, 'text': 'So, drop deletes the entire table.', 'start': 3284.154, 'duration': 1.24}, {'end': 3290.276, 'text': 'So, we will write drop table and then we will give the name of the table which is employees.', 'start': 3285.454, 'duration': 4.822}, {'end': 3302.831, 'text': 'Then I will write here drop table employees.', 'start': 3297.185, 'duration': 5.646}, {'end': 3304.953, 'text': 'I execute it.', 'start': 3302.851, 'duration': 2.102}, {'end': 3308.617, 'text': 'So we have dropped this employees table.', 'start': 3305.013, 'duration': 3.604}, {'end': 3316.345, 'text': 'Now I will write select star from employees.', 'start': 3308.697, 'duration': 7.648}, {'end': 3317.727, 'text': "Now let's execute it.", 'start': 3316.365, 'duration': 1.362}], 'summary': 'A tutorial on creating, modifying, and deleting a table in a database, including adding and renaming columns and removing records, is demonstrated using sql commands.', 'duration': 430.925, 'max_score': 2886.802, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582886802.jpg'}, {'end': 3087.446, 'src': 'embed', 'start': 3026.991, 'weight': 5, 'content': [{'end': 3035.877, 'text': 'After creating the employee table, I got to know later that I have to add a new column to it.', 'start': 3026.991, 'duration': 8.886}, {'end': 3040.16, 'text': 'So, initially, I only had the employee ID, first name, last name and salary.', 'start': 3035.938, 'duration': 4.222}, {'end': 3042.522, 'text': 'After that, I have to add a new column to it.', 'start': 3040.18, 'duration': 2.342}, {'end': 3051.026, 'text': 'So, if we want to alter or modify something in the table, we will use the alter table command.', 'start': 3043.903, 'duration': 7.123}, {'end': 3056.568, 'text': 'So, we will write alter table and then the name of the table.', 'start': 3051.186, 'duration': 5.382}, {'end': 3060.729, 'text': 'So, the name of the table will be employees, alter table employees.', 'start': 3056.588, 'duration': 4.141}, {'end': 3069.873, 'text': 'Then if I want to add a column in it, then add column, then I will give the name of the column, contact, then the data type of the column, integer.', 'start': 3061.19, 'duration': 8.683}, {'end': 3075.637, 'text': 'So in our Employees table I will add a new column.', 'start': 3070.834, 'duration': 4.803}, {'end': 3078.9, 'text': 'here I will write alter table.', 'start': 3075.637, 'duration': 3.263}, {'end': 3084.224, 'text': 'then the name of the table will be employees, add column.', 'start': 3078.9, 'duration': 5.324}, {'end': 3087.446, 'text': 'then the name of the column is contact.', 'start': 3084.224, 'duration': 3.222}], 'summary': "Added a new 'contact' column of data type integer to the 'employees' table using alter table command.", 'duration': 60.455, 'max_score': 3026.991, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF583026991.jpg'}, {'end': 3394.731, 'src': 'embed', 'start': 3364.645, 'weight': 7, 'content': [{'end': 3374.455, 'text': 'We can create the table, alter the table structure, drop the table, or rename the columns.', 'start': 3364.645, 'duration': 9.81}, {'end': 3378.178, 'text': 'Data Definition Language deals with the table structure.', 'start': 3374.475, 'duration': 3.703}, {'end': 3387.566, 'text': 'And we can manipulate the data in the table with this data manipulation language.', 'start': 3381.141, 'duration': 6.425}, {'end': 3391.369, 'text': 'So we have three commands, insert, update, and delete.', 'start': 3387.926, 'duration': 3.443}, {'end': 3394.731, 'text': 'So if we want to insert data, we will use the insert command.', 'start': 3391.729, 'duration': 3.002}], 'summary': 'Ddl deals with table structure. dml has 3 commands: insert, update, delete.', 'duration': 30.086, 'max_score': 3364.645, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF583364645.jpg'}, {'end': 4029.122, 'src': 'heatmap', 'start': 3847.762, 'weight': 9, 'content': [{'end': 3852.343, 'text': 'I will change it to Hathway.', 'start': 3847.762, 'duration': 4.581}, {'end': 3855.364, 'text': 'Last name is equal to Hathway.', 'start': 3852.383, 'duration': 2.981}, {'end': 3866.172, 'text': 'Where employee id is equal to, i will write 1.', 'start': 3855.984, 'duration': 10.188}, {'end': 3869.055, 'text': 'so now see the change here.', 'start': 3866.172, 'duration': 2.883}, {'end': 3873.922, 'text': 'initially here was Jennifer Aniston, so i changed it with update command to Jennifer Hathaway.', 'start': 3869.055, 'duration': 4.867}, {'end': 3882.01, 'text': 'After that we have delete command, so if we want to delete any record, we can delete it like this.', 'start': 3877.748, 'duration': 4.262}, {'end': 3884.752, 'text': 'So we will write delete from employees where employee id is in 101 and 103.', 'start': 3882.03, 'duration': 2.722}, {'end': 3887.313, 'text': 'So wherever employee id is 101 or 103, we will delete those records.', 'start': 3884.752, 'duration': 2.561}, {'end': 3890.275, 'text': 'So with this you will delete these records.', 'start': 3887.714, 'duration': 2.561}, {'end': 3908.109, 'text': "So, let's say I want to delete these two records, record1 and record4.", 'start': 3903.746, 'duration': 4.363}, {'end': 3909.67, 'text': "So, I'll write a command here.", 'start': 3908.189, 'duration': 1.481}, {'end': 3915.513, 'text': "So, I'll write delete from, then the name of the table is employees.", 'start': 3909.69, 'duration': 5.823}, {'end': 3933.244, 'text': 'Delete from employees where the employee ID in 1, 4.', 'start': 3915.573, 'duration': 17.671}, {'end': 3934.365, 'text': "Now let's see the result.", 'start': 3933.244, 'duration': 1.121}, {'end': 3938.708, 'text': 'As you can see, I have removed Employee ID 1 and Employee ID 4.', 'start': 3934.385, 'duration': 4.323}, {'end': 3943.511, 'text': 'So that was DML.', 'start': 3938.708, 'duration': 4.803}, {'end': 3946.273, 'text': "Now let's understand what is DCL.", 'start': 3943.531, 'duration': 2.742}, {'end': 3948.514, 'text': 'DCL stands for Data Control Language.', 'start': 3946.313, 'duration': 2.201}, {'end': 3952.136, 'text': 'Data Control Language is very important for security purposes.', 'start': 3949.154, 'duration': 2.982}, {'end': 3956.117, 'text': "Let's take a banking example.", 'start': 3953.777, 'duration': 2.34}, {'end': 3959.618, 'text': "Let's say people deposit and withdraw amounts.", 'start': 3956.157, 'duration': 3.461}, {'end': 3961.799, 'text': 'These things should be on priority.', 'start': 3959.638, 'duration': 2.161}, {'end': 3964.559, 'text': "Let's say every employee in the bank has these things.", 'start': 3961.819, 'duration': 2.74}, {'end': 3968.16, 'text': "Let's say they can transfer the amount from one account to another.", 'start': 3964.579, 'duration': 3.581}, {'end': 3984.123, 'text': 'That is why security is very important here.', 'start': 3980.282, 'duration': 3.841}, {'end': 3989.465, 'text': 'Suppose 100 people work in the bank and 50 people have access to the database.', 'start': 3984.203, 'duration': 5.262}, {'end': 3993.066, 'text': 'We have to restrict the access of those 50 people.', 'start': 3989.485, 'duration': 3.581}, {'end': 3996.807, 'text': 'For this, we have two commands, one is grant and revoke.', 'start': 3993.086, 'duration': 3.721}, {'end': 4001.208, 'text': 'We can give access to the user through grant and we can withdraw access through revoke.', 'start': 3997.227, 'duration': 3.981}, {'end': 4019.896, 'text': 'So here if there is a user and I want to give him access to the account details, then I can use the grant command for that.', 'start': 4008.59, 'duration': 11.306}, {'end': 4029.122, 'text': 'So I will write grant and whatever columns or whatever features I want to give access to that user, I will write it here.', 'start': 4019.936, 'duration': 9.186}], 'summary': 'Demonstrates dml and dcl commands with examples, including delete and grant/revoke, for data manipulation and security purposes in a database environment.', 'duration': 113.549, 'max_score': 3847.762, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF583847762.jpg'}, {'end': 4183.548, 'src': 'embed', 'start': 4115.028, 'weight': 12, 'content': [{'end': 4123.953, 'text': 'So, if you have a very big organization or if you take any website, for example, you take the website of Amazon or Flipkart.', 'start': 4115.028, 'duration': 8.925}, {'end': 4130.238, 'text': 'So, in all these websites, there are thousands of transactions in every second.', 'start': 4123.974, 'duration': 6.264}, {'end': 4138.224, 'text': 'So, when so many transactions are happening at once, then you have to keep in mind that all the transactions should be done properly.', 'start': 4130.259, 'duration': 7.965}, {'end': 4144.107, 'text': 'Suppose one transaction is over, then you have to commit that transaction.', 'start': 4138.564, 'duration': 5.543}, {'end': 4146.268, 'text': 'For this, you have commit command.', 'start': 4144.127, 'duration': 2.141}, {'end': 4149.649, 'text': 'So all the transactions will be saved till the survey.', 'start': 4146.328, 'duration': 3.321}, {'end': 4151.951, 'text': 'Suppose any transaction is wrong.', 'start': 4150.069, 'duration': 1.882}, {'end': 4157.694, 'text': "For example, if we take the employee table, then suppose we have stored the employee's salary wrong.", 'start': 4151.97, 'duration': 5.724}, {'end': 4159.654, 'text': 'That is a big mistake.', 'start': 4157.754, 'duration': 1.9}, {'end': 4167.098, 'text': 'So if we want to roll back it or if we want to go to the previous point, then we can use the rollback command.', 'start': 4159.694, 'duration': 7.404}, {'end': 4174.263, 'text': 'Then there are some things where we assume that this is an important thing where we can come back.', 'start': 4168.399, 'duration': 5.864}, {'end': 4176.584, 'text': 'So these are the save points.', 'start': 4174.582, 'duration': 2.002}, {'end': 4183.548, 'text': "So suppose you are working on a very big project and you don't know exactly whether you have to save it now or not.", 'start': 4176.604, 'duration': 6.944}], 'summary': 'In high-transaction websites like amazon or flipkart, thousands of transactions occur per second. database commands like commit, rollback, and save points are crucial for ensuring proper transaction management.', 'duration': 68.52, 'max_score': 4115.028, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF584115028.jpg'}], 'start': 2366.103, 'title': 'Sql fundamentals and data management', 'summary': 'Covers sql data types, constraints, and basics, including ddl commands, altering tables, table operations, database security, and transaction control. it emphasizes data integrity, uniqueness, and security using quantifiable commands such as create, alter, drop, grant, and revoke, and explains the impact of ddl and dml commands on database structure and transactions.', 'chapters': [{'end': 2547.199, 'start': 2366.103, 'title': 'Sql data types and constraints', 'summary': 'Covers different data types in sql and explains constraints such as not null, default, unique, primary key, and checks, emphasizing the importance of ensuring data integrity and uniqueness.', 'duration': 181.096, 'highlights': ['The chapter covers different data types in SQL and explains constraints such as not null, default, unique, primary key, and checks. It explains the different data types in SQL and elaborates on constraints like not null, default, unique, primary key, and checks.', 'Emphasizes the importance of ensuring data integrity and uniqueness. It highlights the significance of ensuring data integrity and uniqueness through constraints like not null, default, unique, and primary key.']}, {'end': 3026.491, 'start': 2547.219, 'title': 'Sql basics and ddl commands', 'summary': 'Covers the basics of sql and delves into data definition language (ddl) commands including create, alter, drop, truncate, and rename, with examples and their impact on database structure.', 'duration': 479.272, 'highlights': ['The chapter covers the basics of SQL and different sub-query languages like DDL, DML, DCL, and TCL. Introduces the coverage of SQL basics and different sub-query languages in SQL.', 'Explains the DDL commands including CREATE, ALTER, DROP, TRUNCATE, and RENAME, providing syntax and use cases for each command. Detailed explanation of DDL commands and their usage.', 'Demonstrates the creation of a table using DDL commands in MySQL Workbench, including syntax and execution, resulting in successful table creation. Step-by-step demonstration of creating a table using DDL commands in MySQL Workbench and successful execution.']}, {'end': 3213.707, 'start': 3026.991, 'title': 'Altering table and adding columns', 'summary': 'Explains the process of altering a table by adding a new column, changing the column name, and using commands like alter table, add column, and rename column to modify the table structure in sql.', 'duration': 186.716, 'highlights': ["Using the 'alter table' command, a new column 'contact' with the data type integer was successfully added to the 'employees' table.", "The process of changing the column name from 'contact' to 'job code' was executed using the 'alter table' command and verified with the 'describe' command for the 'employees' table.", "Demonstrating the use of 'alter table' to add and then rename a column in the 'employees' table, providing a practical example of modifying a table's structure in SQL."]}, {'end': 3959.618, 'start': 3217.694, 'title': 'Sql table operations', 'summary': 'Covers the sql table operations including deleting all records, dropping a table, and performing data manipulation and control language commands. it explains the usage of truncate table, drop table, insert, update, and delete commands, along with the distinction between ddl and dml, and the importance of dcl for security purposes.', 'duration': 741.924, 'highlights': ['The chapter covers the SQL table operations including deleting all records, dropping a table, and performing data manipulation and control language commands. The chapter discusses the usage of truncate table and drop table commands, as well as insert, update, and delete commands.', 'It explains the usage of truncate table, drop table, insert, update, and delete commands, along with the distinction between DDL and DML. The chapter provides a clear explanation of the usage of truncate table, drop table, insert, update, and delete commands, and also highlights the difference between Data Definition Language (DDL) and Data Manipulation Language (DML).', 'The importance of DCL for security purposes is emphasized. The chapter underlines the significance of Data Control Language (DCL) for security purposes, using a banking example to illustrate the importance of DCL in managing access rights and privileges.']}, {'end': 4183.548, 'start': 3959.638, 'title': 'Database security and transaction control', 'summary': 'Discusses database security using grant and revoke commands, emphasizing the importance of data control and transaction control language in managing transactions, with a focus on committing, rolling back, and creating save points for large projects.', 'duration': 223.91, 'highlights': ['The chapter emphasizes the importance of security by restricting access to database for 50 employees out of 100 in the bank. 50 out of 100 employees have access to the database, illustrating the need to restrict access to ensure security.', 'It explains the grant and revoke commands for controlling user access to specific columns or features in the database. Grant and revoke commands are used to give or withdraw access to specific columns or features for users, providing precise control over data access.', 'The chapter highlights the significance of transaction control language in managing multiple transactions, with examples from large organizations like Amazon and Flipkart. Transaction control language is crucial for managing numerous transactions, demonstrated through examples of thousands of transactions occurring per second on websites like Amazon and Flipkart.', 'It discusses the commit command for ensuring the proper completion and saving of transactions in the database. The commit command ensures that all transactions are properly saved, promoting data integrity and accuracy in the database.', 'It explains the rollback command for reverting incorrect transactions, with a focus on rectifying errors such as storing incorrect employee salaries. The rollback command allows for reverting incorrect transactions, addressing critical errors such as storing inaccurate employee salaries in the database.']}], 'duration': 1817.445, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF582366103.jpg', 'highlights': ['The chapter covers different data types in SQL and explains constraints such as not null, default, unique, primary key, and checks.', 'Emphasizes the importance of ensuring data integrity and uniqueness.', 'Introduces the coverage of SQL basics and different sub-query languages in SQL.', 'Detailed explanation of DDL commands and their usage.', 'Demonstrates the creation of a table using DDL commands in MySQL Workbench, including syntax and execution, resulting in successful table creation.', "Using the 'alter table' command, a new column 'contact' with the data type integer was successfully added to the 'employees' table.", "Demonstrating the use of 'alter table' to add and then rename a column in the 'employees' table, providing a practical example of modifying a table's structure in SQL.", 'The chapter discusses the usage of truncate table and drop table commands, as well as insert, update, and delete commands.', 'The chapter provides a clear explanation of the usage of truncate table, drop table, insert, update, and delete commands, and also highlights the difference between Data Definition Language (DDL) and Data Manipulation Language (DML).', 'The chapter underlines the significance of Data Control Language (DCL) for security purposes, using a banking example to illustrate the importance of DCL in managing access rights and privileges.', '50 out of 100 employees have access to the database, illustrating the need to restrict access to ensure security.', 'Grant and revoke commands are used to give or withdraw access to specific columns or features for users, providing precise control over data access.', 'Transaction control language is crucial for managing numerous transactions, demonstrated through examples of thousands of transactions occurring per second on websites like Amazon and Flipkart.', 'The commit command ensures that all transactions are properly saved, promoting data integrity and accuracy in the database.', 'The rollback command allows for reverting incorrect transactions, addressing critical errors such as storing inaccurate employee salaries in the database.']}, {'end': 5954.934, 'segs': [{'end': 4240.707, 'src': 'embed', 'start': 4209.671, 'weight': 5, 'content': [{'end': 4218.725, 'text': "So suppose I just want to see the first name column or just want to see the same record where someone's salary is is more than $300,000?", 'start': 4209.671, 'duration': 9.054}, {'end': 4230.758, 'text': 'or if I want to see records on that condition, then I can use where clause like here it is written select star from employees.', 'start': 4218.725, 'duration': 12.033}, {'end': 4240.707, 'text': 'where employee id is equal to 101 Means, I will get the same record from this table whose employee ID is 101..', 'start': 4230.758, 'duration': 9.949}], 'summary': 'Using a where clause to filter records based on conditions in a database query.', 'duration': 31.036, 'max_score': 4209.671, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF584209671.jpg'}, {'end': 5100.877, 'src': 'embed', 'start': 5066.843, 'weight': 2, 'content': [{'end': 5071.725, 'text': 'That means when you say 300,000 and 500,000, then these two values will also be included.', 'start': 5066.843, 'duration': 4.882}, {'end': 5073.785, 'text': "So that's why I got these two.", 'start': 5071.745, 'duration': 2.04}, {'end': 5082.722, 'text': 'Matt Damon whose salary is exactly $300,000 and Charlie Harpo whose salary is exactly $500,000.', 'start': 5073.905, 'duration': 8.817}, {'end': 5084.564, 'text': 'So I have used between operator.', 'start': 5082.722, 'duration': 1.842}, {'end': 5087.466, 'text': 'Now we will work with like operator.', 'start': 5084.584, 'duration': 2.882}, {'end': 5090.789, 'text': 'So this is our complete table.', 'start': 5087.486, 'duration': 3.303}, {'end': 5100.877, 'text': 'So suppose I have to extract all the records from this table whose last name starts with L.', 'start': 5090.889, 'duration': 9.988}], 'summary': 'Using sql operators, extracted records with specific salaries and last names from a table.', 'duration': 34.034, 'max_score': 5066.843, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585066843.jpg'}, {'end': 5195.098, 'src': 'embed', 'start': 5124.096, 'weight': 3, 'content': [{'end': 5131.298, 'text': 'L after this percentage symbol means after that either one character or multiple characters can be there.', 'start': 5124.096, 'duration': 7.202}, {'end': 5133.079, 'text': 'So I am executing this.', 'start': 5132.178, 'duration': 0.901}, {'end': 5140.662, 'text': 'so this is the result Jennifer Lopez and Matt Leblon, whose last name starts with L.', 'start': 5134.141, 'duration': 6.521}, {'end': 5142.683, 'text': 'and then finally we have in operator left.', 'start': 5140.662, 'duration': 2.021}, {'end': 5143.983, 'text': 'so in operator again.', 'start': 5142.683, 'duration': 1.3}, {'end': 5149.204, 'text': 'assume I have to take all those records whose salary is exactly 100,000, 300,000 or 500,000.', 'start': 5143.983, 'duration': 5.221}, {'end': 5166.835, 'text': 'so here I will write select star from employees Where salary in.', 'start': 5149.204, 'duration': 17.631}, {'end': 5171.218, 'text': 'here I will write 100,000, then 300,000, then 500,000..', 'start': 5166.835, 'duration': 4.383}, {'end': 5174.42, 'text': 'So I have extracted these three records here.', 'start': 5171.218, 'duration': 3.202}, {'end': 5191.495, 'text': "So Jennifer Aniston's salary is exactly 100,000, Matt Damon's salary is exactly 300,000 and Charlie Harper's salary is exactly 500,000.", 'start': 5175.421, 'duration': 16.074}, {'end': 5195.098, 'text': 'And then we have the final command which is distinct operator.', 'start': 5191.496, 'duration': 3.602}], 'summary': "Using 'in' operator to filter salaries: 100,000, 300,000, 500,000. final result: jennifer aniston: $100,000, matt damon: $300,000, charlie harper: $500,000.", 'duration': 71.002, 'max_score': 5124.096, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585124096.jpg'}, {'end': 5456.809, 'src': 'heatmap', 'start': 5320.644, 'weight': 1, 'content': [{'end': 5325.305, 'text': 'Then in the same way, suppose I want a minimum salary.', 'start': 5320.644, 'duration': 4.661}, {'end': 5330.989, 'text': 'So, select minimum of salary from employees.', 'start': 5327.688, 'duration': 3.301}, {'end': 5332.41, 'text': 'This is $100,000.', 'start': 5331.029, 'duration': 1.381}, {'end': 5337.272, 'text': 'If I want average salary, then I will write select average of salary from employees.', 'start': 5332.41, 'duration': 4.862}, {'end': 5339.633, 'text': 'And average salary is $350,000.', 'start': 5337.992, 'duration': 1.641}, {'end': 5343.174, 'text': 'And now if I want count of all records here.', 'start': 5339.633, 'duration': 3.541}, {'end': 5346.676, 'text': 'I will write select count of star from employees.', 'start': 5343.194, 'duration': 3.482}, {'end': 5362.484, 'text': 'And it tells me that there are 6 records in this table.', 'start': 5359.341, 'duration': 3.143}, {'end': 5366.709, 'text': 'So we have seen the aggregate functions, now we will work with the groupby clause.', 'start': 5362.985, 'duration': 3.724}, {'end': 5374.157, 'text': 'When we want to know a data-related group, then we can use groupby.', 'start': 5366.729, 'duration': 7.428}, {'end': 5380.704, 'text': 'So here is our employee table and we have added a new column in the employee table, department ID.', 'start': 5374.317, 'duration': 6.387}, {'end': 5387.068, 'text': 'And now we need the maximum salary from this table with respect to each department.', 'start': 5381.224, 'duration': 5.844}, {'end': 5390.871, 'text': 'So as you can see here we have 3 department IDs.', 'start': 5387.489, 'duration': 3.382}, {'end': 5391.952, 'text': '10, 11 and 12.', 'start': 5391.011, 'duration': 0.941}, {'end': 5398.917, 'text': "So I don't need the individual maximum salary or I don't need the maximum salary from the entire table.", 'start': 5391.952, 'duration': 6.965}, {'end': 5408.763, 'text': 'What I need is the maximum salary with respect to department ID 10, with respect to department ID 11 and with respect to department ID 12.', 'start': 5399.377, 'duration': 9.386}, {'end': 5411.784, 'text': 'In this case, I will use the GROUP BY clause.', 'start': 5408.763, 'duration': 3.021}, {'end': 5413.644, 'text': 'The syntax is quite easy.', 'start': 5412.184, 'duration': 1.46}, {'end': 5417.145, 'text': 'First, you will start with the SELECT query.', 'start': 5414.024, 'duration': 3.121}, {'end': 5422.527, 'text': 'Then, what do I need? Maximum of salary or department ID from employees.', 'start': 5417.265, 'duration': 5.262}, {'end': 5429.869, 'text': 'And after GROUP BY clause, you will give the column name with which you want to group the data.', 'start': 5423.027, 'duration': 6.842}, {'end': 5437.531, 'text': 'And because I need maximum salary with respect to department ID, here I will give the name of department ID after GROUP BY.', 'start': 5429.929, 'duration': 7.602}, {'end': 5444.106, 'text': 'So, we will go to MySQL Workbench and first of all we will see our Employees table.', 'start': 5438.865, 'duration': 5.241}, {'end': 5450.347, 'text': 'So, I will write here SELECT ** FROM and the name of the table is EMPLOYEES.', 'start': 5444.146, 'duration': 6.201}, {'end': 5456.809, 'text': 'And I have to add a new column in it which will be DEPARTMENT.', 'start': 5452.528, 'duration': 4.281}], 'summary': 'Demonstration of sql aggregate functions and group by clause with quantifiable data: minimum salary $100,000, average salary $350,000, 6 records in the table, and retrieval of maximum salary with respect to department ids 10, 11, and 12.', 'duration': 41.84, 'max_score': 5320.644, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585320644.jpg'}, {'end': 5700.852, 'src': 'embed', 'start': 5665.208, 'weight': 0, 'content': [{'end': 5675.712, 'text': 'Then after this, suppose I want to know what is the average salary or what is the maximum salary with respect to a department.', 'start': 5665.208, 'duration': 10.504}, {'end': 5678.113, 'text': 'So, I will have to use the group by clause here.', 'start': 5675.732, 'duration': 2.381}, {'end': 5687.457, 'text': 'So, I will write here, suppose I want the first name, maximum of salary with respect to each department.', 'start': 5678.133, 'duration': 9.324}, {'end': 5700.852, 'text': 'so I will type select first name and what I want maximum of salary, so maximum of salary and department.', 'start': 5688.377, 'duration': 12.475}], 'summary': 'Using sql group by clause to find average and maximum salaries per department.', 'duration': 35.644, 'max_score': 5665.208, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585665208.jpg'}, {'end': 5790.242, 'src': 'heatmap', 'start': 5665.208, 'weight': 0.886, 'content': [{'end': 5675.712, 'text': 'Then after this, suppose I want to know what is the average salary or what is the maximum salary with respect to a department.', 'start': 5665.208, 'duration': 10.504}, {'end': 5678.113, 'text': 'So, I will have to use the group by clause here.', 'start': 5675.732, 'duration': 2.381}, {'end': 5687.457, 'text': 'So, I will write here, suppose I want the first name, maximum of salary with respect to each department.', 'start': 5678.133, 'duration': 9.324}, {'end': 5700.852, 'text': 'so I will type select first name and what I want maximum of salary, so maximum of salary and department.', 'start': 5688.377, 'duration': 12.475}, {'end': 5709.064, 'text': 'also I want from employees, or I would want to group this with department column.', 'start': 5700.852, 'duration': 8.212}, {'end': 5717.613, 'text': 'This means I will get the name and salary of those employees who earn the highest salary in their respective departments.', 'start': 5709.465, 'duration': 8.148}, {'end': 5723.607, 'text': 'So now we know that Jennifer gets the highest salary in the sales department.', 'start': 5720.606, 'duration': 3.001}, {'end': 5727.829, 'text': 'Charlie gets the highest salary in the tech department, Jennifer gets the highest salary in the marketing department,', 'start': 5723.607, 'duration': 4.222}, {'end': 5731.69, 'text': 'Charlie gets the highest salary in the content department and Sheldon gets the highest salary in the support department.', 'start': 5727.829, 'duration': 3.861}, {'end': 5735.271, 'text': 'So these are the employees who have the highest salaries in their departments.', 'start': 5731.73, 'duration': 3.541}, {'end': 5739.765, 'text': 'So this is group by clause.', 'start': 5738.424, 'duration': 1.341}, {'end': 5743.87, 'text': 'After group by clause, we have a having clause.', 'start': 5739.785, 'duration': 4.085}, {'end': 5749.295, 'text': 'Normally, when we are given a condition, we use where clause for it.', 'start': 5744.691, 'duration': 4.604}, {'end': 5755.922, 'text': 'But if we use an aggregate function, then we cannot use where clause to specify a condition.', 'start': 5749.796, 'duration': 6.126}, {'end': 5768.211, 'text': 'So whenever you use groupby and aggregate function with groupby, you have to use having.', 'start': 5760.327, 'duration': 7.884}, {'end': 5772.293, 'text': 'And this having clause will always come after groupby.', 'start': 5768.752, 'duration': 3.541}, {'end': 5775.255, 'text': "You can't use having before groupby.", 'start': 5772.433, 'duration': 2.822}, {'end': 5790.242, 'text': 'So, here we have this table and we need average salary with respect to those departments where at least two employees are there.', 'start': 5778.016, 'duration': 12.226}], 'summary': 'Using group by and having clauses to find max salaries and average salaries in different departments.', 'duration': 125.034, 'max_score': 5665.208, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585665208.jpg'}], 'start': 4183.848, 'title': 'Sql operators and functions', 'summary': 'Covers sql where clause, logical and comparison operators, aggregate functions, and group by clause, with examples of extracting specific records and performing calculations, such as average, count, max, minimum, and sum, and obtaining insights about department-wise maximum and average salaries.', 'chapters': [{'end': 4334.889, 'start': 4183.848, 'title': 'Sql where clause and operators', 'summary': 'Covers the usage of the where clause in sql, allowing for the extraction of specific records based on conditions, with examples and explanations for logical operators like and, or, not.', 'duration': 151.041, 'highlights': ['The where clause in SQL allows for the extraction of specific records based on conditions, as demonstrated by selecting records where employee ID is 101 or salary is exactly $100,000.', 'The syntax for using the where clause is straightforward: SELECT star from employees, followed by the where clause and the specified condition, such as employee ID equal to 101.', 'In addition to the where clause, the chapter introduces the logical operators AND, OR, NOT to further refine conditions for extracting records.']}, {'end': 5029.365, 'start': 4334.909, 'title': 'Logical and comparison operators in sql', 'summary': 'Covers the logical operators and, or, and not with examples, as well as the comparison operators for extracting records based on specific conditions, such as salary ranges and unique values, in sql.', 'duration': 694.456, 'highlights': ['The AND operator gives a true result only when both operands are true, and the OR operator gives a true result when any operand is true. The AND operator and OR operator behavior is explained, demonstrating how they yield true results based on the truth values of the operands.', 'Examples of using logical operators to extract records from the employee table based on specific conditions are provided, such as using AND to satisfy multiple conditions and OR to satisfy either of the conditions. The use of logical operators (AND, OR) for extracting specific records from the employee table based on conditions (e.g., first name and salary) is demonstrated.', 'Examples of using comparison operators in SQL, such as less than, greater than, and not equal to, to extract records based on salary conditions, are illustrated. Demonstration of using comparison operators (e.g., less than, greater than, not equal to) to extract records from the employee table based on salary conditions is provided.', 'Explanation of special operators in SQL, including BETWEEN, LIKE, IS NULL, IN, and DISTINCT, for extracting records within a range, matching string patterns, checking null values, extracting specific values, and obtaining unique records, is presented. The explanation of special operators (BETWEEN, LIKE, IS NULL, IN, DISTINCT) in SQL and their usage for extracting specific records, including those within a range, matching string patterns, checking null values, and obtaining unique records, is provided.']}, {'end': 5362.484, 'start': 5029.465, 'title': 'Sql operators and aggregate functions', 'summary': "Covers the usage of sql operators like between, like, in and aggregate functions including average, count, max, minimum and sum to retrieve specific records and perform calculations on the table 'employees', demonstrating the usage and results of each operation.", 'duration': 333.019, 'highlights': ['Using the between operator, records with salaries between $300,000 and $500,000 were extracted, resulting in 3 records, including Matt Damon with a salary of $300,000 and Charlie Harpo with a salary of $500,000. The between operator was used to extract records with salaries between $300,000 and $500,000, resulting in 3 records, including Matt Damon with a salary of $300,000 and Charlie Harpo with a salary of $500,000.', "The like operator was employed to extract records with last names starting with 'L', yielding 2 results: Jennifer Lopez and Matt Leblon. The like operator was used to extract records with last names starting with 'L', resulting in 2 records: Jennifer Lopez and Matt Leblon.", 'The in operator retrieved records with salaries exactly matching $100,000, $300,000, and $500,000, resulting in 3 records: Jennifer Aniston ($100,000), Matt Damon ($300,000), and Charlie Harper ($500,000). The in operator was used to retrieve records with salaries exactly matching $100,000, $300,000, and $500,000, resulting in 3 records: Jennifer Aniston ($100,000), Matt Damon ($300,000), and Charlie Harper ($500,000).', "The distinct operator was utilized to extract unique first names from the table 'employees', resulting in 3 unique names: Jennifer, Charlie, and Matt. The distinct operator was used to extract unique first names from the table 'employees', resulting in 3 unique names: Jennifer, Charlie, and Matt.", "Various aggregate functions were demonstrated, including average salary ($350,000), minimum salary ($100,000), maximum salary ($600,000), and the total number of records (6) in the table 'employees'. Various aggregate functions were demonstrated, including average salary ($350,000), minimum salary ($100,000), maximum salary ($600,000), and the total number of records (6) in the table 'employees'."]}, {'end': 5954.934, 'start': 5362.985, 'title': 'Understanding group by clause in sql', 'summary': 'Explains the usage of the group by clause in sql to obtain the maximum and average salary with respect to each department, showcasing examples of grouping and aggregation, including insights about the highest earning employees and conditions for using the having clause.', 'duration': 591.949, 'highlights': ['The GROUP BY clause is used to obtain the maximum salary with respect to each department, showcasing examples of grouping and aggregation. Usage of GROUP BY clause, obtaining maximum salary, examples of grouping and aggregation', 'Insights about the employees earning the highest salary in their respective departments are showcased, indicating the usage of GROUP BY clause in SQL. Highest earning employees in respective departments, usage of GROUP BY clause', "Demonstration of the HAVING clause's usage to specify conditions for obtaining the average salary with respect to departments with at least two employees. Usage of HAVING clause, specifying conditions for average salary, demonstration of its usage"]}], 'duration': 1771.086, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF584183848.jpg', 'highlights': ['The GROUP BY clause is used to obtain the maximum salary with respect to each department, showcasing examples of grouping and aggregation.', "Various aggregate functions were demonstrated, including average salary ($350,000), minimum salary ($100,000), maximum salary ($600,000), and the total number of records (6) in the table 'employees'.", 'Using the between operator, records with salaries between $300,000 and $500,000 were extracted, resulting in 3 records, including Matt Damon with a salary of $300,000 and Charlie Harpo with a salary of $500,000.', 'The in operator retrieved records with salaries exactly matching $100,000, $300,000, and $500,000, resulting in 3 records: Jennifer Aniston ($100,000), Matt Damon ($300,000), and Charlie Harper ($500,000).', "The like operator was employed to extract records with last names starting with 'L', yielding 2 results: Jennifer Lopez and Matt Leblon.", 'The where clause in SQL allows for the extraction of specific records based on conditions, as demonstrated by selecting records where employee ID is 101 or salary is exactly $100,000.']}, {'end': 6656.618, 'segs': [{'end': 6096.603, 'src': 'heatmap', 'start': 5966.889, 'weight': 0, 'content': [{'end': 5972.175, 'text': 'so this is groupby and having clause and after that we have orderby clause.', 'start': 5966.889, 'duration': 5.286}, {'end': 5981.666, 'text': 'so whenever we have to arrange our data in an ascending order or descending order, then we can use orderby clause.', 'start': 5972.175, 'duration': 9.491}, {'end': 5983.549, 'text': 'its syntax is very easy.', 'start': 5981.666, 'duration': 1.883}, {'end': 5986.873, 'text': 'first you will see select star from employees.', 'start': 5983.549, 'duration': 3.324}, {'end': 5990.117, 'text': 'After that you will give the order by keyword.', 'start': 5987.453, 'duration': 2.664}, {'end': 5995.424, 'text': 'Then you will give the name of the column according to which you have to order by.', 'start': 5990.417, 'duration': 5.007}, {'end': 6000.411, 'text': 'Because I have to order this table with salary, so I will write salary here.', 'start': 5995.645, 'duration': 4.766}, {'end': 6011.784, 'text': 'So as you can see here initially it was 10, 15, 20 then I am giving salary and I am arranging salary in descending order.', 'start': 6003.195, 'duration': 8.589}, {'end': 6019.932, 'text': 'So if I want to arrange salary in descending order then I will use DESC keyword after the column name.', 'start': 6012.064, 'duration': 7.868}, {'end': 6023.496, 'text': 'And in this way I have arranged salary in descending order.', 'start': 6020.352, 'duration': 3.144}, {'end': 6030.057, 'text': 'So, I will apply the same command here.', 'start': 6026.015, 'duration': 4.042}, {'end': 6035.7, 'text': 'So, I have to arrange my table in decreasing order of salaries.', 'start': 6030.077, 'duration': 5.623}, {'end': 6042.443, 'text': 'So, here I will write SELECT STAR FROM EMPLOYEES.', 'start': 6036.2, 'duration': 6.243}, {'end': 6045.645, 'text': 'Then I have to write ORDER BY.', 'start': 6042.844, 'duration': 2.801}, {'end': 6046.666, 'text': 'So, I will write.', 'start': 6045.665, 'duration': 1.001}, {'end': 6052.188, 'text': 'And according to which column I have to order, that will be SALARY.', 'start': 6047.706, 'duration': 4.482}, {'end': 6054.349, 'text': 'And I want the salary in descending order.', 'start': 6052.208, 'duration': 2.141}, {'end': 6058.285, 'text': 'Right, so this is my descending order.', 'start': 6056.403, 'duration': 1.882}, {'end': 6060.528, 'text': 'So here you are also seeing the employee ID.', 'start': 6058.305, 'duration': 2.223}, {'end': 6066.214, 'text': 'Employee ID is going from 8 to 1 and the salary is going from $800,000 to $100,000.', 'start': 6060.588, 'duration': 5.626}, {'end': 6069.037, 'text': 'So now we will work with the union operator.', 'start': 6066.214, 'duration': 2.823}, {'end': 6072.441, 'text': 'So suppose we have two tables and I want to select.', 'start': 6069.077, 'duration': 3.364}, {'end': 6090.738, 'text': 'The SQL Union is used to combine the result set of two or more SELECT statements.', 'start': 6081.329, 'duration': 9.409}, {'end': 6092.299, 'text': 'removing duplicates.', 'start': 6090.738, 'duration': 1.561}, {'end': 6096.603, 'text': 'Each SELECT statement with the union must have same number of columns.', 'start': 6093.1, 'duration': 3.503}], 'summary': 'Demonstration of using sql orderby clause to arrange salary data in descending order.', 'duration': 56.607, 'max_score': 5966.889, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585966889.jpg'}, {'end': 6125.075, 'src': 'embed', 'start': 6093.1, 'weight': 4, 'content': [{'end': 6096.603, 'text': 'Each SELECT statement with the union must have same number of columns.', 'start': 6093.1, 'duration': 3.503}, {'end': 6099.044, 'text': 'So when you are using Union operator,', 'start': 6097.063, 'duration': 1.981}, {'end': 6107.627, 'text': 'then you have to keep in mind that in the first select statement and in the second select statement you are giving the same number of columns.', 'start': 6099.044, 'duration': 8.583}, {'end': 6111.049, 'text': 'It is not possible that you are selecting two columns.', 'start': 6107.728, 'duration': 3.321}, {'end': 6115.031, 'text': 'from the first select statement and from the second select statement, you are selecting three columns.', 'start': 6111.049, 'duration': 3.982}, {'end': 6117.872, 'text': 'That will be an error or you will not get any result.', 'start': 6115.331, 'duration': 2.541}, {'end': 6125.075, 'text': 'After that the selected columns must be of similar data types and must be in the same order in each select statement.', 'start': 6117.892, 'duration': 7.183}], 'summary': 'When using union operator, ensure both select statements have same number of columns and similar data types in same order.', 'duration': 31.975, 'max_score': 6093.1, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586093100.jpg'}, {'end': 6191.442, 'src': 'embed', 'start': 6163.093, 'weight': 3, 'content': [{'end': 6166.555, 'text': 'So in both of them we have categoryId and productName.', 'start': 6163.093, 'duration': 3.462}, {'end': 6175.139, 'text': 'So now with Union Operator, All the product names have to be unionized.', 'start': 6167.055, 'duration': 8.084}, {'end': 6182.46, 'text': 'So as you can see, I am selecting the entire product name from the first select statement.', 'start': 6175.179, 'duration': 7.281}, {'end': 6186.321, 'text': 'When you select product name from product1, you will get all these names.', 'start': 6182.781, 'duration': 3.54}, {'end': 6191.442, 'text': 'Similarly, when you select product name from product2, you will get all these names.', 'start': 6186.341, 'duration': 5.101}], 'summary': 'Using union operator to combine all product names from two categories.', 'duration': 28.349, 'max_score': 6163.093, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586163093.jpg'}, {'end': 6605.862, 'src': 'embed', 'start': 6580.25, 'weight': 6, 'content': [{'end': 6588.355, 'text': 'So whatever our first select statement was giving and second select statement was giving, we have brought this result by uniting them here.', 'start': 6580.25, 'duration': 8.105}, {'end': 6595.395, 'text': 'So after union we have an extension which we will call union all.', 'start': 6591.513, 'duration': 3.882}, {'end': 6602.54, 'text': 'So the only difference between union and union all is that you will not get duplicate values in union.', 'start': 6595.435, 'duration': 7.105}, {'end': 6604.561, 'text': 'You will get duplicate values in union all.', 'start': 6602.56, 'duration': 2.001}, {'end': 6605.862, 'text': 'So the syntax is the same.', 'start': 6604.581, 'duration': 1.281}], 'summary': 'Union all allows duplicate values in the result set.', 'duration': 25.612, 'max_score': 6580.25, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586580250.jpg'}, {'end': 6662.419, 'src': 'embed', 'start': 6628.524, 'weight': 5, 'content': [{'end': 6629.845, 'text': 'After that, Samsung is here again.', 'start': 6628.524, 'duration': 1.321}, {'end': 6631.465, 'text': 'So, the duplicate is here again.', 'start': 6629.865, 'duration': 1.6}, {'end': 6632.506, 'text': 'Again, HP and HP.', 'start': 6631.485, 'duration': 1.021}, {'end': 6634.206, 'text': 'HP is here again twice.', 'start': 6632.526, 'duration': 1.68}, {'end': 6640.029, 'text': 'So, you can see that the duplicates will be present when you use Union All.', 'start': 6634.246, 'duration': 5.783}, {'end': 6642.85, 'text': 'So, I will just write Union All here.', 'start': 6640.249, 'duration': 2.601}, {'end': 6648.876, 'text': 'Right, so here you have samsung, samsung twice, hp, hp twice.', 'start': 6644.915, 'duration': 3.961}, {'end': 6656.618, 'text': 'So now here is another syntax equal to union which we will call intersect operator.', 'start': 6649.276, 'duration': 7.342}, {'end': 6662.419, 'text': 'So intersect will give us the common elements between the two.', 'start': 6656.858, 'duration': 5.561}], 'summary': 'Samsung and hp duplicates present when using union all. intersect operator gives common elements.', 'duration': 33.895, 'max_score': 6628.524, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586628524.jpg'}], 'start': 5966.889, 'title': 'Data sorting and sql union operator', 'summary': 'Explains using the order by clause for data sorting in ascending or descending order, with a syntax example and demonstration of sorting salary. it also covers the sql union operator, demonstrating its application on product tables, resulting in a unionized list of product names, with an example of union all and intersect operator.', 'chapters': [{'end': 6058.285, 'start': 5966.889, 'title': 'Using orderby clause for data sorting', 'summary': 'Explains how to use the order by clause to arrange data in ascending or descending order, with a syntax example and demonstration of sorting salary in descending order.', 'duration': 91.396, 'highlights': ["The syntax to use the ORDER BY clause is straightforward: 'SELECT * FROM employees ORDER BY column_name'.", "Descending order is achieved by adding the 'DESC' keyword after the column name.", 'Demonstrating the sorting of salary in descending order, showcasing the rearrangement from 10, 15, 20 to descending order.']}, {'end': 6656.618, 'start': 6058.305, 'title': 'Sql union operator', 'summary': 'Explains the sql union operator used to combine the result set of two or more select statements, ensuring the same number of columns and similar data types, and demonstrates its application on product tables, resulting in a unionized list of product names, with an example of union all and intersect operator.', 'duration': 598.313, 'highlights': ['The SQL Union is used to combine the result set of two or more SELECT statements, ensuring the same number of columns and similar data types. The SQL Union operator is demonstrated to combine the result set of two or more SELECT statements, emphasizing the importance of having the same number of columns and similar data types.', 'Demonstration of applying Union Operator on product1 and product2 tables to unionize all product names, removing duplicates. A detailed demonstration is provided on applying the Union Operator on product1 and product2 tables to unionize all product names and remove duplicates, showcasing the practical application of the SQL Union operator.', 'Explanation of Union All operator, displaying duplicate values when used, and syntax comparison with the Union operator. The chapter explains the Union All operator, highlighting the presence of duplicate values when used, and provides a syntax comparison with the Union operator for clarity on the differences between the two operators.', 'Introduction of the intersect operator as another syntax, presenting an additional method for combining result sets. An introduction to the intersect operator is provided, offering an additional method for combining result sets, which expands the understanding of operators available for manipulating data in SQL.']}], 'duration': 689.729, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF585966889.jpg', 'highlights': ['Demonstrating the sorting of salary in descending order, showcasing the rearrangement from 10, 15, 20 to descending order.', "The syntax to use the ORDER BY clause is straightforward: 'SELECT * FROM employees ORDER BY column_name'.", "Descending order is achieved by adding the 'DESC' keyword after the column name.", 'Demonstration of applying Union Operator on product1 and product2 tables to unionize all product names, removing duplicates.', 'Demonstration of the SQL Union operator to combine the result set of two or more SELECT statements, emphasizing the importance of having the same number of columns and similar data types.', 'Introduction of the intersect operator as another syntax, presenting an additional method for combining result sets.', 'Explanation of Union All operator, displaying duplicate values when used, and syntax comparison with the Union operator.']}, {'end': 7619.677, 'segs': [{'end': 6713.845, 'src': 'embed', 'start': 6687.525, 'weight': 0, 'content': [{'end': 6692.451, 'text': 'Obviously, both will have some information that you will have to see at the same time.', 'start': 6687.525, 'duration': 4.926}, {'end': 6696.155, 'text': 'So, you will join both the tables on the basis of a condition.', 'start': 6692.491, 'duration': 3.664}, {'end': 6705.381, 'text': 'SQL Joins combines rows and columns from two or more tables based on the related column between them in a database.', 'start': 6698.477, 'duration': 6.904}, {'end': 6707.742, 'text': 'These are different types of SQL Joins.', 'start': 6705.401, 'duration': 2.341}, {'end': 6713.845, 'text': 'We have Inner Join, Left Join, Right Join, Cartesian Join and Full Outer Join.', 'start': 6708.182, 'duration': 5.663}], 'summary': 'Sql joins combine tables based on related columns. types: inner, left, right, cartesian, full outer.', 'duration': 26.32, 'max_score': 6687.525, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586687525.jpg'}, {'end': 6959.734, 'src': 'heatmap', 'start': 6737.452, 'weight': 0.916, 'content': [{'end': 6743.257, 'text': 'For example, we have table A and table B and we have applied a condition.', 'start': 6737.452, 'duration': 5.805}, {'end': 6754.087, 'text': 'And all the records that satisfy this condition in table A and table B, we will show in the final result.', 'start': 6743.277, 'duration': 10.81}, {'end': 6756.549, 'text': 'Then we have left join.', 'start': 6754.187, 'duration': 2.362}, {'end': 6761.654, 'text': 'In left join, We will apply a condition, which you always do.', 'start': 6756.629, 'duration': 5.025}, {'end': 6773.127, 'text': 'So now, the left table, whether any record satisfies that condition or not, you take all the records from the left table.', 'start': 6761.674, 'duration': 11.453}, {'end': 6777.932, 'text': 'But from the right table, you will take the same record that satisfies this condition.', 'start': 6773.527, 'duration': 4.405}, {'end': 6782.439, 'text': 'Then there is right join which is opposite to left join.', 'start': 6779.534, 'duration': 2.905}, {'end': 6786.184, 'text': 'In right join, you will select all the records from the right side table.', 'start': 6782.459, 'duration': 3.725}, {'end': 6792.995, 'text': 'But you will select the records from the left side table which satisfy this condition.', 'start': 6786.204, 'duration': 6.791}, {'end': 6801.543, 'text': 'Full outer join will give you all the records from left and right side table.', 'start': 6795.759, 'duration': 5.784}, {'end': 6810.488, 'text': 'If there is any case where this condition is not satisfied then you will put a null value there.', 'start': 6801.603, 'duration': 8.885}, {'end': 6811.748, 'text': 'Then there is self-join.', 'start': 6810.508, 'duration': 1.24}, {'end': 6814.77, 'text': 'In self-join you will join one table with it.', 'start': 6811.768, 'duration': 3.002}, {'end': 6816.291, 'text': 'Suppose we have a table A.', 'start': 6814.79, 'duration': 1.501}, {'end': 6826.04, 'text': 'So, if you want to join A with B, then you will give an alias to A.', 'start': 6820.453, 'duration': 5.587}, {'end': 6831.486, 'text': 'For example, you will call A as B, then you will do A self-join B.', 'start': 6826.04, 'duration': 5.446}, {'end': 6836.231, 'text': 'Then you are joining the same table with the same person on the basis of a condition.', 'start': 6831.486, 'duration': 4.745}, {'end': 6840.433, 'text': 'And then finally we have Cartesian Join or Cross Join.', 'start': 6836.972, 'duration': 3.461}, {'end': 6843.114, 'text': 'So this gives us a cross product.', 'start': 6840.533, 'duration': 2.581}, {'end': 6845.795, 'text': 'So we have table A and table B.', 'start': 6843.194, 'duration': 2.601}, {'end': 6850.597, 'text': 'So suppose you have 3 records in table A and 4 records in table B.', 'start': 6845.795, 'duration': 4.802}, {'end': 6857.819, 'text': 'And when you apply Cartesian Join on these two tables, you will get 12 records in the final result.', 'start': 6850.597, 'duration': 7.222}, {'end': 6865.329, 'text': 'So, first of all we will start with inner join.', 'start': 6862.008, 'duration': 3.321}, {'end': 6868.61, 'text': 'So, as I told you, when inner join gives us a condition,', 'start': 6865.349, 'duration': 3.261}, {'end': 6876.892, 'text': 'then we will get the same record from both the left and right tables which satisfy this condition.', 'start': 6868.61, 'duration': 8.282}, {'end': 6878.112, 'text': 'And this is our syntax.', 'start': 6876.992, 'duration': 1.12}, {'end': 6880.853, 'text': 'So, we will write select.', 'start': 6878.132, 'duration': 2.721}, {'end': 6886.534, 'text': 'After select, we will list the columns from table 1 and table 2.', 'start': 6880.953, 'duration': 5.581}, {'end': 6888.834, 'text': 'So, here we will write table1.column1.', 'start': 6886.534, 'duration': 2.3}, {'end': 6896.561, 'text': 'So, which column do you want from which table? You can use dot operator like this.', 'start': 6891.175, 'duration': 5.386}, {'end': 6900.125, 'text': 'Column 1 from table 1, column 2 from table 2 and so on.', 'start': 6896.942, 'duration': 3.183}, {'end': 6902.007, 'text': 'So, you have given a list of columns.', 'start': 6900.185, 'duration': 1.822}, {'end': 6905.391, 'text': 'From, after that you will write table 1, left side table.', 'start': 6902.027, 'duration': 3.364}, {'end': 6909.274, 'text': 'After that you will give keywords inner join because you are applying inner join.', 'start': 6906.052, 'duration': 3.222}, {'end': 6911.956, 'text': 'After that you will give the name of the right side table.', 'start': 6909.314, 'duration': 2.642}, {'end': 6917.919, 'text': 'After that you will set the condition by giving on keyword.', 'start': 6912.076, 'duration': 5.843}, {'end': 6924.723, 'text': 'What is the condition here? The common column should be equal to both the records.', 'start': 6917.979, 'duration': 6.744}, {'end': 6926.224, 'text': 'Here you have set the condition.', 'start': 6925.043, 'duration': 1.181}, {'end': 6933.667, 'text': 'Here is our example, this is our employee table and this is our department table.', 'start': 6927.745, 'duration': 5.922}, {'end': 6942.409, 'text': 'And what are we doing here? We are just taking the same records where department ID is common in employees and department table.', 'start': 6934.147, 'duration': 8.262}, {'end': 6944.61, 'text': 'So here you are seeing department ID 10, 11, 12, here 10, 11, 13, 14.', 'start': 6942.429, 'duration': 2.181}, {'end': 6947.071, 'text': 'So we will just extract department ID 10 and 11 here.', 'start': 6944.61, 'duration': 2.461}, {'end': 6959.734, 'text': 'so 10 and 11 is IT and 11 is marketing.', 'start': 6954.853, 'duration': 4.881}], 'summary': 'Explains various types of sql joins including inner, left, right, full outer, self-join, and cartesian join, with examples and quantifiable data.', 'duration': 222.282, 'max_score': 6737.452, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586737452.jpg'}, {'end': 6942.409, 'src': 'embed', 'start': 6912.076, 'weight': 2, 'content': [{'end': 6917.919, 'text': 'After that you will set the condition by giving on keyword.', 'start': 6912.076, 'duration': 5.843}, {'end': 6924.723, 'text': 'What is the condition here? The common column should be equal to both the records.', 'start': 6917.979, 'duration': 6.744}, {'end': 6926.224, 'text': 'Here you have set the condition.', 'start': 6925.043, 'duration': 1.181}, {'end': 6933.667, 'text': 'Here is our example, this is our employee table and this is our department table.', 'start': 6927.745, 'duration': 5.922}, {'end': 6942.409, 'text': 'And what are we doing here? We are just taking the same records where department ID is common in employees and department table.', 'start': 6934.147, 'duration': 8.262}], 'summary': 'Setting condition for joining tables based on common column values.', 'duration': 30.333, 'max_score': 6912.076, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586912076.jpg'}, {'end': 7353.629, 'src': 'embed', 'start': 7328.056, 'weight': 1, 'content': [{'end': 7333.378, 'text': 'And we will get the records from the right table where the columns are matched.', 'start': 7328.056, 'duration': 5.322}, {'end': 7340.501, 'text': 'So, wherever the records are not matched from the right table, you will get the null value.', 'start': 7333.438, 'duration': 7.063}, {'end': 7345.802, 'text': 'As you can see here, this is our Employees table and this is our Department table.', 'start': 7341.638, 'duration': 4.164}, {'end': 7348.164, 'text': 'And we are applying left join here.', 'start': 7346.342, 'duration': 1.822}, {'end': 7350.746, 'text': 'So here you have written left outer join.', 'start': 7348.184, 'duration': 2.562}, {'end': 7353.629, 'text': 'So here you can write either left outer join or left join.', 'start': 7350.766, 'duration': 2.863}], 'summary': 'Using left join to get unmatched records from the right table, resulting in null values.', 'duration': 25.573, 'max_score': 7328.056, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF587328056.jpg'}], 'start': 6656.858, 'title': 'Sql joins and their syntax', 'summary': 'Covers sql joins including inner join, left join, right join, cartesian join, and full outer join, with syntax, examples, and their importance in data analysis and database management, facilitating the combination of data from related columns. it also explains table joining conditions, data insertion, and usage of joins in sql, providing practical examples for database management.', 'chapters': [{'end': 6911.956, 'start': 6656.858, 'title': 'Sql joins and their types', 'summary': 'Explains sql joins and their types such as inner join, left join, right join, cartesian join, and full outer join, each with its syntax and examples, enabling the combination of data from two or more tables based on related columns, vital for data analysis and database management.', 'duration': 255.098, 'highlights': ['SQL Joins and their types The chapter extensively covers SQL Joins and their types, including Inner Join, Left Join, Right Join, Cartesian Join, and Full Outer Join, providing crucial information for data analysis and database management.', 'Inner Join The explanation of Inner Join includes the process of combining rows and columns from two tables based on a specified condition, with a clear syntax and a practical example, demonstrating its importance in data analysis and database management.', 'Left Join The chapter provides a comprehensive understanding of Left Join, highlighting its functionality, syntax, and practical application, crucial for database management and data analysis.', 'Right Join The section thoroughly explains the concept of Right Join, elucidating its purpose, syntax, and significance in combining data from multiple tables, essential for database management and data analysis.', 'Cartesian Join or Cross Join The chapter delves into the details of Cartesian Join, emphasizing its ability to produce a cross product, with a clear example illustrating the increase in records when applying Cartesian Join, providing essential insights for database management and data analysis.']}, {'end': 7224.29, 'start': 6912.076, 'title': 'Sql table join and data insertion', 'summary': "Explains the concept of setting conditions for joining tables in sql to extract relevant records based on a common column, alongside a detailed example of creating and populating a 'department' table with specific records.", 'duration': 312.214, 'highlights': ['Explaining the concept of setting conditions for joining tables in SQL to extract relevant records based on a common column The speaker explains the process of setting conditions for joining tables in SQL to extract relevant records based on a common column, emphasizing the importance of establishing a common column and providing an example using employee and department tables.', "Creating and populating a 'department' table with specific records in SQL The speaker provides a detailed example of creating a 'department' table in SQL, demonstrating the process of defining columns, inserting specific records, and updating records, such as 'content', 'support', 'sales', 'HR', and 'operations' departments with their respective locations."]}, {'end': 7619.677, 'start': 7224.37, 'title': 'Sql joins and their syntax', 'summary': 'Explains the syntax and usage of inner join, left join, right join, full outer join, and cross join in sql, demonstrating how to combine and retrieve data from the employees and department tables, with examples of common records and null values.', 'duration': 395.307, 'highlights': ['The chapter explains the syntax and usage of inner join, left join, right join, full outer join, and cross join in SQL, demonstrating how to combine and retrieve data from the employees and department tables, with examples of common records and null values.', "Inner join is applied to extract common records from the employees and department tables based on the matching 'department' column, retrieving the first name and salary from the employee table and the department name and location from the department table.", 'Left join is demonstrated to retrieve all records from the left table (employees) and matching records from the right table (department), with null values for unmatched records from the right table.', 'Right join is illustrated to retrieve all records from the right table and matching records from the left table, with null values for unmatched records from the left table.', 'Full outer join is explained as combining the results of left and right outer joins using the union operator, showcasing the union of left and right join result sets and the inclusion of null values for unmatched records from both tables.', 'Cross join is exemplified to produce a Cartesian product, yielding 12 records from 4 x 3, as demonstrated with examples using table 1 and table 2.']}], 'duration': 962.819, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF586656858.jpg', 'highlights': ['The chapter extensively covers SQL Joins and their types, including Inner Join, Left Join, Right Join, Cartesian Join, and Full Outer Join, providing crucial information for data analysis and database management.', 'The chapter explains the syntax and usage of inner join, left join, right join, full outer join, and cross join in SQL, demonstrating how to combine and retrieve data from the employees and department tables, with examples of common records and null values.', 'Explaining the concept of setting conditions for joining tables in SQL to extract relevant records based on a common column The speaker explains the process of setting conditions for joining tables in SQL to extract relevant records based on a common column, emphasizing the importance of establishing a common column and providing an example using employee and department tables.']}, {'end': 8895.402, 'segs': [{'end': 7652.628, 'src': 'embed', 'start': 7619.737, 'weight': 0, 'content': [{'end': 7633.488, 'text': 'I will write here select star from employees cross join department.', 'start': 7619.737, 'duration': 13.751}, {'end': 7639.165, 'text': 'And execute it and you will get the result.', 'start': 7635.524, 'duration': 3.641}, {'end': 7640.305, 'text': 'So, I have so many records.', 'start': 7639.185, 'duration': 1.12}, {'end': 7643.426, 'text': 'So, we have covered all the major concepts.', 'start': 7640.325, 'duration': 3.101}, {'end': 7644.886, 'text': 'Now, we will start the lab session.', 'start': 7643.446, 'duration': 1.44}, {'end': 7649.588, 'text': 'In this lab session, we will take many new datasets and implement queries on them.', 'start': 7644.926, 'duration': 4.662}, {'end': 7652.628, 'text': 'So, first of all, we will work with these two datasets CSV files.', 'start': 7649.628, 'duration': 3}], 'summary': 'Lab session covers major concepts, with many records and new datasets for query implementation.', 'duration': 32.891, 'max_score': 7619.737, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF587619737.jpg'}, {'end': 7789.676, 'src': 'embed', 'start': 7762.542, 'weight': 1, 'content': [{'end': 7765.485, 'text': 'I am currently working with this world database.', 'start': 7762.542, 'duration': 2.943}, {'end': 7767.808, 'text': 'So here I will right click.', 'start': 7765.525, 'duration': 2.283}, {'end': 7775.553, 'text': 'after right clicking, you have this option table data import wizard, then browse from here.', 'start': 7768.971, 'duration': 6.582}, {'end': 7781.594, 'text': 'so table data import wizard allows you to easily import csv, json data files.', 'start': 7775.553, 'duration': 6.041}, {'end': 7785.095, 'text': 'so here i have to browse whatever file i have to load.', 'start': 7781.594, 'duration': 3.501}, {'end': 7788.536, 'text': 'so here, on desktop, my file is available.', 'start': 7785.095, 'duration': 3.441}, {'end': 7789.676, 'text': 'i will write here cricket1.csv.', 'start': 7788.536, 'duration': 1.14}], 'summary': 'Working with world database, importing csv/json data files from desktop.', 'duration': 27.134, 'max_score': 7762.542, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF587762542.jpg'}, {'end': 8179.418, 'src': 'embed', 'start': 8129.605, 'weight': 2, 'content': [{'end': 8136.01, 'text': 'select player name, Popularity.', 'start': 8129.605, 'duration': 6.405}, {'end': 8139.533, 'text': 'From cricket 1.', 'start': 8136.01, 'duration': 3.523}, {'end': 8145.797, 'text': 'Where Popularity is greater than here, I will write subquery.', 'start': 8139.533, 'duration': 6.264}, {'end': 8158.021, 'text': 'So subquery will be select Average of popularity from cricket one.', 'start': 8145.797, 'duration': 12.224}, {'end': 8166.022, 'text': 'so I will first show you by implementing this sub query.', 'start': 8158.021, 'duration': 8.001}, {'end': 8170.603, 'text': 'so in team, underscore should be here.', 'start': 8166.022, 'duration': 4.581}, {'end': 8172.524, 'text': 'I will write underscore here.', 'start': 8170.603, 'duration': 1.921}, {'end': 8179.418, 'text': 'so the average popularity of all players is 9.833.', 'start': 8176.357, 'duration': 3.061}], 'summary': 'Average popularity of players in cricket 1 team is 9.833.', 'duration': 49.813, 'max_score': 8129.605, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588129605.jpg'}, {'end': 8240.915, 'src': 'embed', 'start': 8212.635, 'weight': 3, 'content': [{'end': 8222.222, 'text': 'This means I need to know the list of players who have played test match 1 and test match 2.', 'start': 8212.635, 'duration': 9.587}, {'end': 8225.364, 'text': 'So, you can understand the difference between 1 and 3.', 'start': 8222.222, 'duration': 3.142}, {'end': 8231.428, 'text': 'So, in 1, we were knowing that the player has played either Test Match 1 or Test Match 2.', 'start': 8225.364, 'duration': 6.064}, {'end': 8240.915, 'text': 'But when you are looking at Query 3, here we will get the list of all the players where they have played Test Match 1 and 3.', 'start': 8231.428, 'duration': 9.487}], 'summary': 'List players who played test match 1 and 3.', 'duration': 28.28, 'max_score': 8212.635, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588212635.jpg'}, {'end': 8450.427, 'src': 'embed', 'start': 8409.751, 'weight': 4, 'content': [{'end': 8414.635, 'text': 'This means that both the players have played the test match.', 'start': 8409.751, 'duration': 4.884}, {'end': 8434.309, 'text': 'Now we need to extract player idruns and player name from cricketone and show the list of players whose average score is more than 3k.', 'start': 8424.094, 'duration': 10.215}, {'end': 8447.064, 'text': 'So here I will write select player ID runs player name from cricket 1 where runs is greater than here.', 'start': 8440.538, 'duration': 6.526}, {'end': 8450.427, 'text': 'again you will write sub query select average of runs from cricket 1..', 'start': 8447.064, 'duration': 3.363}], 'summary': 'Extract player id and name from cricketone, display players with average score > 3k.', 'duration': 40.676, 'max_score': 8409.751, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588409751.jpg'}, {'end': 8668.508, 'src': 'embed', 'start': 8631.199, 'weight': 5, 'content': [{'end': 8636.784, 'text': 'Similarly, we have to extract all the players from table 2 whose name starts with Y.', 'start': 8631.199, 'duration': 5.585}, {'end': 8638.005, 'text': 'I will write Y%.', 'start': 8636.784, 'duration': 1.221}, {'end': 8642.269, 'text': 'here We have 3 players, Yuvraj Yadav and Yusuf, whose name starts with Y.', 'start': 8638.005, 'duration': 4.264}, {'end': 8664.725, 'text': 'After this, we have to write a query which will help us to extract all those cricket players whose name does not end with T.', 'start': 8654.275, 'duration': 10.45}, {'end': 8668.508, 'text': 'So here you will use not like operator.', 'start': 8664.725, 'duration': 3.783}], 'summary': 'Extract 3 players with names starting with y and exclude those ending with t.', 'duration': 37.309, 'max_score': 8631.199, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588631199.jpg'}, {'end': 8762.47, 'src': 'embed', 'start': 8734.609, 'weight': 6, 'content': [{'end': 8739.633, 'text': 'Which is the combination of cricket1.csv and cricket2.csv.', 'start': 8734.609, 'duration': 5.024}, {'end': 8741.935, 'text': 'So if I want to work with this then I have to load it.', 'start': 8739.673, 'duration': 2.262}, {'end': 8743.977, 'text': 'So I right click here and load it too.', 'start': 8741.955, 'duration': 2.022}, {'end': 8746.519, 'text': 'Table data import wizard.', 'start': 8744.978, 'duration': 1.541}, {'end': 8748.201, 'text': 'I have to browse.', 'start': 8747.06, 'duration': 1.141}, {'end': 8750.703, 'text': 'And its name is newcricut.csv.', 'start': 8748.922, 'duration': 1.781}, {'end': 8762.47, 'text': 'Next, this is new cricket and I am storing it in the world database and all these columns are present in it.', 'start': 8754.002, 'duration': 8.468}], 'summary': 'Combining cricket1.csv and cricket2.csv to create newcricut.csv, storing it in the world database.', 'duration': 27.861, 'max_score': 8734.609, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588734609.jpg'}, {'end': 8822.131, 'src': 'embed', 'start': 8793.307, 'weight': 7, 'content': [{'end': 8796.33, 'text': 'from here it will be new cricket.', 'start': 8793.307, 'duration': 3.023}, {'end': 8803.062, 'text': 'So this is how I have loaded this new data set.', 'start': 8798.097, 'duration': 4.965}, {'end': 8810.009, 'text': 'So now I have to extract those players from this data set where the charisma value is null.', 'start': 8803.262, 'duration': 6.747}, {'end': 8812.912, 'text': 'So here you can see the table right?', 'start': 8810.59, 'duration': 2.322}, {'end': 8817.257, 'text': 'So here the charisma value is null for some records right?', 'start': 8812.952, 'duration': 4.305}, {'end': 8822.131, 'text': 'so I have to extract all the records whose charisma value is null.', 'start': 8817.93, 'duration': 4.201}], 'summary': 'Extract records with null charisma value from new cricket dataset.', 'duration': 28.824, 'max_score': 8793.307, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588793307.jpg'}, {'end': 8895.402, 'src': 'embed', 'start': 8865.107, 'weight': 8, 'content': [{'end': 8871.05, 'text': 'And then, I would have to separate all player IDs into single numeric IDs.', 'start': 8865.107, 'duration': 5.943}, {'end': 8874.572, 'text': 'So, initially, the player IDs were like this.', 'start': 8871.53, 'duration': 3.042}, {'end': 8876.593, 'text': 'PL1, PL2, PL3, PL4 and so on.', 'start': 8874.712, 'duration': 1.881}, {'end': 8879.034, 'text': 'So, I have to change it to 1, 2, 3, 4 like this.', 'start': 8876.613, 'duration': 2.421}, {'end': 8888.299, 'text': 'Where PL1 is 1, PL2 is 2, PL3 is 3.', 'start': 8879.054, 'duration': 9.245}, {'end': 8892.261, 'text': 'So I have to implement all these changes.', 'start': 8888.299, 'duration': 3.962}, {'end': 8895.402, 'text': 'To do these changes I will use substring.', 'start': 8892.321, 'duration': 3.081}], 'summary': 'Convert player ids from alphanumeric to numeric using substring', 'duration': 30.295, 'max_score': 8865.107, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588865107.jpg'}], 'start': 7619.737, 'title': 'Sql queries and data analysis in cricket', 'summary': 'Covers a sql lab session with the focus on implementing queries on cricket1.csv and cricket2.csv, importing csv files into mysql workbench, executing sql queries, comparing data, using subqueries, analyzing test match players, and optimizing sql queries for performance.', 'chapters': [{'end': 7762.502, 'start': 7619.737, 'title': 'Sql lab session with cricket data', 'summary': 'Covers a sql lab session with a focus on implementing queries on two datasets, cricket1.csv and cricket2.csv, to find the list of players who have played in the first or second test match and the process of loading csv files.', 'duration': 142.765, 'highlights': ['The chapter covers a SQL lab session with a focus on implementing queries on two datasets, cricket1.csv and cricket2.csv. The lab session involves working with two datasets, cricket1.csv and cricket2.csv, to implement queries.', 'Finding the list of players who have played in the first or second test match using the Union Operator. Using the Union Operator to combine the lists of players who have played in the first and second test matches to find the players who were present in either match.', 'The process of loading a CSV file is demonstrated. Demonstrating the process of loading a CSV file for working with the datasets.']}, {'end': 8212.635, 'start': 7762.542, 'title': 'Importing and querying cricket data', 'summary': 'Covers the process of importing csv files into the mysql workbench using the table data import wizard, creating new tables in the database, executing sql queries to retrieve and compare data from the imported tables, and using subqueries to find players with popularity higher than the average.', 'duration': 450.093, 'highlights': ["The process of importing csv files into the MySQL Workbench using the table data import wizard and creating new tables in the database. The wizard allows easy import of csv and json data files, with a demonstration of importing cricket data into the 'world' database.", 'Executing SQL queries to retrieve and compare data from the imported tables. Demonstration of executing SQL queries to retrieve and compare data from the imported cricket tables, including displaying player names and executing the Union Operator between two Select Statements.', 'Using subqueries to find players with popularity higher than the average. Explanation and implementation of a subquery to find players with popularity higher than the average popularity of all the players in the team, with a demonstrated average popularity of 9.833.']}, {'end': 8384.877, 'start': 8212.635, 'title': 'Comparing list of players in test matches', 'summary': 'Explains the difference between using or and and operators to retrieve a list of players who have played test match 1 and test match 2, and demonstrates the implementation using sql queries.', 'duration': 172.242, 'highlights': ['The chapter explains the difference between using OR and AND operators to retrieve a list of players who have played Test Match 1 and Test Match 2, and demonstrates the implementation using SQL queries.', "The difference between using OR and AND operators to retrieve the list of players who have played Test Match 1 and Test Match 2 is highlighted, emphasizing the use of the 'in' operator in SQL queries.", 'The implementation of the comparison of player IDs between Test Match 1 and Test Match 2 using SQL queries is demonstrated, focusing on retrieving the common player IDs and displaying the player names and IDs.']}, {'end': 8549.218, 'start': 8387.719, 'title': 'Test match players analysis', 'summary': "Discusses how to analyze test match players based on their runs, average score, and selecting players with scores higher than 3k and 50, with the team's average score being 39.33.", 'duration': 161.499, 'highlights': ['The chapter focuses on selecting players from test match 1 whose player ID is common in test match 2, and extracting player ID runs and player name from cricketone to show the list of players whose average score is more than 3k.', 'The average score of the team was 39.33, and the players selected have scores higher than this average.', 'The chapter also involves extracting all the players whose score is more than 50.']}, {'end': 8895.402, 'start': 8549.218, 'title': 'Sql query performance optimization', 'summary': 'Discusses optimizing sql queries, including filtering by player name and charisma value, loading and combining datasets, and transforming player ids from alphanumeric to numeric format.', 'duration': 346.184, 'highlights': ["Using the 'like' operator to extract player names starting with 'Y' and ending with 'V' from cricket 1 and 2, resulting in 3 players (Yadav, Yuvraj, and Yusuf). 3 players have names starting with Y and ending with V.", "Applying the 'not like' operator to exclude players whose names end with 'T' from cricket 1, resulting in the exclusion of 2 players (Virat and Rohit). 2 players have names ending with T, which are excluded.", 'Loading and combining datasets from cricket1.csv and cricket2.csv into a new dataset named newcricket.csv for further analysis. Merging cricket1.csv and cricket2.csv into newcricket.csv.', "Using the 'null' keyword to extract players with null charisma values from the new cricket dataset, resulting in 4 players with null values. 4 players have null charisma values.", 'Implementing the transformation of alphanumeric player IDs (PL1, PL2, etc.) to numeric format (1, 2, etc.) using substring. Transforming alphanumeric player IDs to numeric format.']}], 'duration': 1275.665, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF587619737.jpg', 'highlights': ['The chapter covers a SQL lab session with a focus on implementing queries on two datasets, cricket1.csv and cricket2.csv.', 'Importing csv files into the MySQL Workbench using the table data import wizard and creating new tables in the database.', 'Using subqueries to find players with popularity higher than the average.', 'The chapter explains the difference between using OR and AND operators to retrieve a list of players who have played Test Match 1 and Test Match 2.', 'The chapter focuses on selecting players from test match 1 whose player ID is common in test match 2, and extracting player ID runs and player name from cricketone to show the list of players whose average score is more than 3k.', "Using the 'like' operator to extract player names starting with 'Y' and ending with 'V' from cricket 1 and 2, resulting in 3 players (Yadav, Yuvraj, and Yusuf).", 'Loading and combining datasets from cricket1.csv and cricket2.csv into a new dataset named newcricket.csv for further analysis.', "Using the 'null' keyword to extract players with null charisma values from the new cricket dataset, resulting in 4 players with null values.", 'Implementing the transformation of alphanumeric player IDs (PL1, PL2, etc.) to numeric format (1, 2, etc.) using substring.']}, {'end': 9602.77, 'segs': [{'end': 9014.797, 'src': 'embed', 'start': 8895.422, 'weight': 0, 'content': [{'end': 8901.564, 'text': 'So here I will write select player id then substring then it will take two parameters.', 'start': 8895.422, 'duration': 6.142}, {'end': 8905.406, 'text': 'In the first parameter we will send the name of the column.', 'start': 8901.624, 'duration': 3.782}, {'end': 8910.948, 'text': 'In the second parameter, we will give the index value which we will have to extract.', 'start': 8906.306, 'duration': 4.642}, {'end': 8912.169, 'text': 'So here I am giving 3.', 'start': 8910.968, 'duration': 1.201}, {'end': 8918.091, 'text': 'This means that I am extracting the index number 3 from this whole string.', 'start': 8912.169, 'duration': 5.922}, {'end': 8944.79, 'text': 'So here I need player ID and substring of player ID.', 'start': 8934.825, 'duration': 9.965}, {'end': 8953.315, 'text': 'So substring of player ID and this will be 3 from and this table is cricket1.', 'start': 8945.391, 'duration': 7.924}, {'end': 8959.502, 'text': 'And as you can see, initially PL1 is 1 and PL2 is 2.', 'start': 8955.2, 'duration': 4.302}, {'end': 8961.763, 'text': 'Similarly, I have changed all the records.', 'start': 8959.502, 'duration': 2.261}, {'end': 8970.346, 'text': 'And finally, we have to write a SQL query by which we can extract all the players whose charisma is more than 25.', 'start': 8962.503, 'duration': 7.843}, {'end': 8973.588, 'text': 'This is a very simple command.', 'start': 8970.346, 'duration': 3.242}, {'end': 8981.417, 'text': 'You just have to use the WHERE clause and give the condition WHERE charisma is greater than 25.', 'start': 8973.608, 'duration': 7.809}, {'end': 8990.86, 'text': 'then select star from new cricket table.', 'start': 8981.417, 'duration': 9.443}, {'end': 9005.794, 'text': 'select star from new cricket where charisma is greater than 25.', 'start': 8990.86, 'duration': 14.934}, {'end': 9009.915, 'text': 'So the charisma of all these players is more than 25..', 'start': 9005.794, 'duration': 4.121}, {'end': 9012.496, 'text': 'So that was our first lab exercise.', 'start': 9009.915, 'duration': 2.581}, {'end': 9014.797, 'text': 'Now we will see our second lab exercise.', 'start': 9012.516, 'duration': 2.281}], 'summary': 'Extracted player ids and substrings, then filtered players with charisma > 25.', 'duration': 119.375, 'max_score': 8895.422, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588895422.jpg'}, {'end': 9099.109, 'src': 'embed', 'start': 9062.094, 'weight': 1, 'content': [{'end': 9063.755, 'text': 'Now, I have added this bank database as well.', 'start': 9062.094, 'duration': 1.661}, {'end': 9066.936, 'text': 'If I want to load any tables in it, I will write use of bank here.', 'start': 9063.795, 'duration': 3.141}, {'end': 9070.198, 'text': 'So, I have created my table in this way.', 'start': 9066.976, 'duration': 3.222}, {'end': 9086.731, 'text': 'So now after creating a table, I have to create a new table in the database, whose name will be bank details.', 'start': 9080.663, 'duration': 6.068}, {'end': 9099.109, 'text': 'and it will have all these columns Product Quantity, Price, Purchase Cost and Estimated Sales Price.', 'start': 9091.483, 'duration': 7.626}], 'summary': 'Added bank database and created a table with columns: product quantity, price, purchase cost, estimated sales price.', 'duration': 37.015, 'max_score': 9062.094, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF589062094.jpg'}, {'end': 9563.855, 'src': 'embed', 'start': 9525.341, 'weight': 5, 'content': [{'end': 9532.148, 'text': 'I have to write alter table, then table name is bank details, then either I can write add or add column.', 'start': 9525.341, 'duration': 6.807}, {'end': 9542.037, 'text': 'So I will write add geolocation which is column name which I want to add and data type is varchar and size is 20.', 'start': 9532.168, 'duration': 9.869}, {'end': 9543.799, 'text': 'so I will add a new column.', 'start': 9542.037, 'duration': 1.762}, {'end': 9547.322, 'text': 'for that I have to write ALTER TABLE.', 'start': 9543.799, 'duration': 3.523}, {'end': 9563.855, 'text': 'then the name of the table will be BANK DETAILS, ADD COLUMN, and this will be GEO LOCATION and its size is varchar10.', 'start': 9547.322, 'duration': 16.533}], 'summary': "Add a new column 'geolocation' of data type varchar(20) to the table 'bank details'.", 'duration': 38.514, 'max_score': 9525.341, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF589525341.jpg'}], 'start': 8895.422, 'title': 'Sql functions and table creation', 'summary': "Covers sql substring function for string extraction and creating a 'bank' database with a 'bank details' table including columns like product and quantity, while also altering the table to add a new column 'geolocation'. an example of extracting player ids and writing a sql query to select players with charisma greater than 25 is included.", 'chapters': [{'end': 9014.797, 'start': 8895.422, 'title': 'Sql query and substring extraction', 'summary': 'Covers how to extract specific parts of a string using sql substring function, with an example of extracting player ids and writing a sql query to select players with charisma greater than 25.', 'duration': 119.375, 'highlights': ['The substring function is used to extract specific parts of a string by providing the column name and index value, demonstrated with an example of extracting player IDs from a table.', 'A SQL query example is provided to select players with charisma greater than 25 using the WHERE clause, indicating a simple command to extract relevant data.', 'Demonstration of changing records in a table and extracting players with charisma greater than 25, showcasing practical application of the SQL concepts.']}, {'end': 9602.77, 'start': 9015.317, 'title': 'Creating database and table in sql', 'summary': "Covers creating a new database 'bank' and a table 'bank details' with columns product, quantity, price, purchase cost, and estimated sales price, adding new records, and altering the table to add a new column 'geolocation'.", 'duration': 587.453, 'highlights': ["Creating a new database 'bank' and a table 'bank details' with columns Product, Quantity, Price, Purchase Cost, and Estimated Sales Price.", "Adding new records to the table 'bank details' with specific values for the columns Product, Quantity, Price, Purchase Cost, and Estimated Sales Price.", "Altering the table 'bank details' to add a new column 'geolocation' with data type varchar and size 20."]}], 'duration': 707.348, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF588895422.jpg', 'highlights': ['Demonstration of changing records in a table and extracting players with charisma greater than 25, showcasing practical application of the SQL concepts.', "Creating a new database 'bank' and a table 'bank details' with columns Product, Quantity, Price, Purchase Cost, and Estimated Sales Price.", "Adding new records to the table 'bank details' with specific values for the columns Product, Quantity, Price, Purchase Cost, and Estimated Sales Price.", 'The substring function is used to extract specific parts of a string by providing the column name and index value, demonstrated with an example of extracting player IDs from a table.', 'A SQL query example is provided to select players with charisma greater than 25 using the WHERE clause, indicating a simple command to extract relevant data.', "Altering the table 'bank details' to add a new column 'geolocation' with data type varchar and size 20."]}, {'end': 10693.616, 'segs': [{'end': 9672.546, 'src': 'embed', 'start': 9635.961, 'weight': 0, 'content': [{'end': 9654.293, 'text': 'What do I want? Geo location from bank details where here condition will be product is equal to pay card.', 'start': 9635.961, 'duration': 18.332}, {'end': 9658.237, 'text': "So now let's see what is the answer.", 'start': 9655.776, 'duration': 2.461}, {'end': 9664.721, 'text': 'I get NULL because we have added a new column but we have not inserted any values in that new column.', 'start': 9658.338, 'duration': 6.383}, {'end': 9672.546, 'text': 'So after that I have to find out how many characters does the product pay card have in the bank details table.', 'start': 9664.741, 'duration': 7.805}], 'summary': 'Finding geo location from bank details based on product equal to pay card, resulting in null due to new column with no values. subsequently, determining the character count for the product pay card in the bank details table.', 'duration': 36.585, 'max_score': 9635.961, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF589635961.jpg'}, {'end': 10064.481, 'src': 'embed', 'start': 10036.485, 'weight': 1, 'content': [{'end': 10041.588, 'text': 'then first column is holiday and its data type is date, which will give me date.', 'start': 10036.485, 'duration': 5.103}, {'end': 10043.089, 'text': 'then start time.', 'start': 10041.588, 'duration': 1.501}, {'end': 10045.29, 'text': 'its data type is date time, then end time.', 'start': 10043.089, 'duration': 2.201}, {'end': 10047.552, 'text': 'its data type is time stamp.', 'start': 10045.29, 'duration': 2.262}, {'end': 10051.754, 'text': 'so like this, i have to create this table.', 'start': 10047.552, 'duration': 4.202}, {'end': 10057.438, 'text': 'so i will write here create table, and name of table will be bank holidays.', 'start': 10051.754, 'duration': 5.684}, {'end': 10064.481, 'text': 'So I will write bank holidays here and I just have to add the name of the column in it.', 'start': 10058.496, 'duration': 5.985}], 'summary': "Create a table 'bank holidays' with columns: holiday (date) and start/end time (timestamp)", 'duration': 27.996, 'max_score': 10036.485, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF5810036485.jpg'}, {'end': 10494.689, 'src': 'embed', 'start': 10454.8, 'weight': 2, 'content': [{'end': 10456.821, 'text': 'so I will write update bank details.', 'start': 10454.8, 'duration': 2.021}, {'end': 10465.825, 'text': 'set end time is equal to UTC underscore time stamp.', 'start': 10456.821, 'duration': 9.004}, {'end': 10469.848, 'text': 'so here update bank holidays set here instead of holiday.', 'start': 10465.825, 'duration': 4.023}, {'end': 10478.303, 'text': 'I will write end time and here it will be UTC time stamp.', 'start': 10469.848, 'duration': 8.455}, {'end': 10486.446, 'text': 'I will write UTC time stamp here, then execute it now.', 'start': 10478.303, 'duration': 8.143}, {'end': 10489.187, 'text': "let's see the change here.", 'start': 10486.446, 'duration': 2.741}, {'end': 10494.689, 'text': 'select star from bank details.', 'start': 10489.187, 'duration': 5.502}], 'summary': 'Updating bank details with utc timestamp and checking for changes in bank details.', 'duration': 39.889, 'max_score': 10454.8, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF5810454800.jpg'}], 'start': 9602.85, 'title': 'Geolocation and bank holiday tables', 'summary': "Discusses querying geolocation for 'pay card', altering data type in 'bank details' table, creating 'bank holidays' table with date and timestamp columns, and using date add function to modify date values.", 'chapters': [{'end': 10013.752, 'start': 9602.85, 'title': 'Geolocation for product pay card', 'summary': "Discusses querying geolocation values for the product 'pay card' and altering the data type of the 'product' field in the 'bank details' table from char to var char, including quantifiable data such as the number of characters in the 'pay card' and the changes required to avoid data truncation.", 'duration': 410.902, 'highlights': ["Querying geolocation values for the product 'pay card' in the 'bank details' table and receiving NULL due to the absence of inserted values in the new column. NULL value returned", "Counting the number of characters in the 'pay card' product, which is revealed to be 7, and utilizing the charLength function to retrieve the count of characters for 'pay card' in the 'bank details' table. Number of characters: 7", "Altering the data type of the 'product' field in the 'bank details' table from char to var char, and subsequently adjusting the size from 10 to 6, leading to an error due to data truncation. Error encountered during size adjustment", "Determining the minimum size required for the 'product' field based on the number of characters in the 'pay card' and 'pay points', which is found to be 9 to avoid data truncation errors. Minimum size requirement: 9 characters"]}, {'end': 10385.555, 'start': 10014.373, 'title': 'Creating bank holidays table', 'summary': 'Covers creating a table named bank holidays with three columns - holiday (data type: date), start time (data type: date time), and end time (data type: timestamp), inserting and updating records in the table, and using date add function to modify date values.', 'duration': 371.182, 'highlights': ["Creating a table named bank holidays with three columns - holiday, start time, and end time, and their respective data types: date, date time, and timestamp. The table 'bank holidays' is created with three columns - holiday (date), start time (date time), and end time (timestamp).", "Inserting today's date details in all fields of bank holidays table and updating the holiday field to postpone the date by adding one day using the date add function. The process involves inserting today's date details in all fields of the table and updating the holiday field to postpone the date by adding one day using the date add function.", 'Demonstrating the usage of the update command with the date add function to increment the date by one day in the holiday column. The update command with the date add function is demonstrated to increment the date by one day in the holiday column.']}, {'end': 10693.616, 'start': 10388.126, 'title': 'Updating timestamp and displaying data', 'summary': 'Covers updating timestamps using utc time stamp, displaying data with aliases, and retrieving the first record from a table using sql commands.', 'duration': 305.49, 'highlights': ["Updating the end time column with UTC timestamp The speaker demonstrates updating the end time column with a UTC timestamp using the command 'update bank details set end time = UTC_timestamp', showcasing the practical application of using UTC time in SQL commands.", "Displaying a column with a new name using aliases The tutorial explains how to display a column with a new name using the 'select column_name as new_column_name from table_name' syntax, emphasizing the usage of aliases for presenting data in SQL queries.", "Retrieving the first record from a table using 'LIMIT' keyword The final command demonstrates the usage of the 'LIMIT' keyword to retrieve the first record from a table by executing the command 'select * from bank details limit 1', highlighting the practical application of limiting query results in SQL."]}], 'duration': 1090.766, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/1dWCqjhvF58/pics/1dWCqjhvF589602850.jpg', 'highlights': ["Querying geolocation values for the product 'pay card' in the 'bank details' table and receiving NULL due to the absence of inserted values in the new column. NULL value returned", "Creating a table named bank holidays with three columns - holiday, start time, and end time, and their respective data types: date, date time, and timestamp. The table 'bank holidays' is created with three columns - holiday (date), start time (date time), and end time (timestamp).", "Updating the end time column with UTC timestamp The speaker demonstrates updating the end time column with a UTC timestamp using the command 'update bank details set end time = UTC_timestamp', showcasing the practical application of using UTC time in SQL commands."]}], 'highlights': ['SQL is crucial for managing and accessing data in the 21st century.', 'Comprehensive coverage of database organization, data modeling, and SQL operations.', 'Structured query language implemented through three mini projects using different data sets.', 'Relational Data Model is the most widely used data model.', 'Introduction and purpose of the ER diagram in defining relationships within a database.', 'Explanation of the fundamental operations of a DBMS.', 'Advantages of data sharing, security, and backup & recovery in a DBMS.', 'Introduction of RDBMS and the concept of primary key for uniquely identifying individual records.', 'Importance of normalization in managing large datasets.', 'Introduction to SQL data types: numeric types, character/string types, and date types.', 'Explanation of the importance of SQL in working with databases.', 'The process of normalization to achieve first, second, and third normal forms.', 'SQL facilitates communication, access, manipulation, and retrieval of data from a database.', 'Emphasizes the importance of understanding functional dependency first in order to comprehend the different normal forms.', 'Detailed explanation of DDL commands and their usage.', 'Demonstrating the creation of a table using DDL commands in MySQL Workbench.', "Usage of 'alter table' command to add and rename a column in the 'employees' table.", 'Usage of truncate table and drop table commands, as well as insert, update, and delete commands.', 'Significance of Data Control Language (DCL) for security purposes.', 'Grant and revoke commands are used to give or withdraw access to specific columns or features for users.', 'Transaction control language is crucial for managing numerous transactions.', 'The commit command ensures that all transactions are properly saved.', 'The rollback command allows for reverting incorrect transactions.', 'Demonstration of various aggregate functions and operators in SQL.', 'Demonstration of the sorting of salary in descending order.', 'Demonstration of applying Union Operator on product1 and product2 tables to unionize all product names.', 'Introduction of the intersect operator as another syntax for combining result sets.', 'Explanation of Union All operator, displaying duplicate values when used.', 'Extensive coverage of SQL Joins and their types.', 'Demonstration of the syntax and usage of inner join, left join, right join, full outer join, and cross join in SQL.', 'SQL lab session with a focus on implementing queries on two datasets, cricket1.csv and cricket2.csv.', 'Using subqueries to find players with popularity higher than the average.', 'Demonstration of changing records in a table and extracting players with charisma greater than 25.', "Creating a new database 'bank' and a table 'bank details' with columns Product, Quantity, Price, Purchase Cost, and Estimated Sales Price.", "Querying geolocation values for the product 'pay card' in the 'bank details' table and receiving NULL.", 'Creating a table named bank holidays with three columns - holiday, start time, and end time.', 'Updating the end time column with UTC timestamp.']}