title
SQL Expert Course | SQL Tutorial For Beginners | SQL Full Course | SQL For Beginners | Simplilearn
description
"
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=SQLExpSep12-Pjn4P39310g&utm_medium=DescritptionFirstFold&utm_source=youtube
🔥IIT Kanpur Professional Certificate Course In Data Analytics (India Only): https://www.simplilearn.com/iitk-professional-certificate-course-data-analytics?utm_campaign=SQLExpSep12-Pjn4P39310g&utm_medium=DescritptionFirstFold&utm_source=youtube
🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQLExpSep12-Pjn4P39310g&utm_medium=DescritptionFirstFold&utm_source=youtube
🔥Data Analyst Masters Program (Discount Code - YTBE15): https://www.simplilearn.com/data-analyst-masters-certification-training-course?utm_campaign=SQLExpSep12-Pjn4P39310g&utm_medium=DescritptionFirstFold&utm_source=youtube
This SQL expert course video covers everything to master structure query language using MySQL, PostgreSQL as well as SQL server. You will get an idea about built-in SQL functions, joins, subqueries, stored procedures and see the top SQL interview questions that are frequently asked in the interviews. If you want to become a SQL expert, then this is the right video for you!
Dataset Link - https://drive.google.com/drive/folders/1aLVazCdDKFyL5thzOH9j08gFPMj_kLQf
âś…Subscribe to our Channel to learn more about the top Technologies: https://bit.ly/2VT4WtH
⏩ Check out the SQL training videos: https://www.youtube.com/watch?v=pFq1pgli0OQ&list=PLEiEAq2VkUUKL3yPbn8yWnatjUg0P0I-Z
#SQLExpertCourse #BecomeSQLExpert #SQLTutorialForBeginners #SQLFullCourse #SQLCourseForBeginners #SQL #LearnSQLForBeginners #SQLForBeginners #SQLTutorial #SQLCourse #SQLTraining #LearnSQLIn8Hours #SQLStructuredQueryLanguage #Simplilearn
What is SQL?
SQL stands from Structured Query Language. It is used to communicate with several relational databases such as MySQL, Oracle and MS SQL Server. Using SQL queries, you can fetch, update, delete and manipulate data that is stored in the form of rows and columns. SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s. You can also permit users to perform specific tasks on tables, procedures, and views. SQL commands are divided into different types, such as data manipulation language (DML), data definition language (DDL), transaction control language (TCL), and data query language (DQL).
➡️ About Post Graduate Program In Data Analytics
This Data Analytics Program is ideal for all working professionals and prior programming knowledge is not required. It covers topics like data analysis, data visualization, regression techniques, and supervised learning in-depth via our applied learning model with live sessions by leading practitioners and industry projects.
âś… Key Features
- Post Graduate Program certificate and Alumni Association membership
- Exclusive hackathons and Ask me Anything sessions by IBM
- 8X higher live interaction in live online classes by industry experts
- Capstone from 3 domains and 14+ Data Analytics Projects with Industry datasets from Google PlayStore, Lyft, World Bank etc.
- Master Classes delivered by Purdue faculty and IBM experts
- Simplilearn's JobAssist helps you get noticed by top hiring companies
- Resume preparation and LinkedIn profile building
- 1:1 mock interview
- Career accelerator webinars
âś… Skills Covered
- Data Analytics
- Statistical Analysis using Excel
- Data Analysis Python and R
- Data Visualization Tableau and Power BI
- Linear and logistic regression modules
- Clustering using kmeans
- Supervised Learning
👉 Learn More at: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=SQLExpSep12-Pjn4P39310g&utm_medium=Descritption&utm_source=youtube
🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQLExpSep12-Pjn4P39310g&utm_medium=Descritption&utm_source=youtube
🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688
"
detail
{'title': 'SQL Expert Course | SQL Tutorial For Beginners | SQL Full Course | SQL For Beginners | Simplilearn', 'heatmap': [{'end': 2042.089, 'start': 1164.798, 'weight': 0.725}, {'end': 29029.441, 'start': 28848.394, 'weight': 0.998}], 'summary': "This sql expert course covers sql's business importance, er diagrams, mysql workbench installation, data manipulation, string functions, group by, joins, subqueries, triggers, postgresql basics, sql operations, functions, and constraints with practical examples and quantifiable data.", 'chapters': [{'end': 775.076, 'segs': [{'end': 106.832, 'src': 'embed', 'start': 62.471, 'weight': 0, 'content': [{'end': 67.835, 'text': 'The popularity of SQL has grown over the years and is one of the most sought after skills by employers.', 'start': 62.471, 'duration': 5.364}, {'end': 69.737, 'text': 'SQL is everywhere.', 'start': 68.656, 'duration': 1.081}, {'end': 72.638, 'text': 'It plays a critical role in almost every kind of job.', 'start': 70.217, 'duration': 2.421}, {'end': 79.281, 'text': 'Whether you are a software developer, data analyst, marketing analyst, product manager or a data scientist,', 'start': 72.978, 'duration': 6.303}, {'end': 81.722, 'text': 'SQL will make your life easier and save your time.', 'start': 79.281, 'duration': 2.441}, {'end': 84.903, 'text': 'SQL still remains the top language for data work.', 'start': 82.302, 'duration': 2.601}, {'end': 88.345, 'text': 'It is easy to learn and does not require any prior coding knowledge.', 'start': 85.363, 'duration': 2.982}, {'end': 95.508, 'text': 'Using SQL commands, you can unlock the power of business intelligence tools like Tableau and answer any questions related to the data.', 'start': 89.225, 'duration': 6.283}, {'end': 99.789, 'text': 'It can also integrate with scripting languages such as Python and R.', 'start': 95.868, 'duration': 3.921}, {'end': 106.832, 'text': 'According to Glassdoor, the average salary of a SQL developer is $88, 912 per annum in the United States.', 'start': 99.789, 'duration': 7.043}], 'summary': 'Sql is a highly sought-after skill, with an average salary of $88,912 per annum in the us, and plays a critical role in various jobs, integrating with other languages like python and r.', 'duration': 44.361, 'max_score': 62.471, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g62471.jpg'}, {'end': 189.696, 'src': 'embed', 'start': 149.027, 'weight': 2, 'content': [{'end': 152.892, 'text': 'Next, we will cover how to use GROUP BY and HAVING in SQL followed by JOINS.', 'start': 149.027, 'duration': 3.865}, {'end': 157.662, 'text': 'You will learn the different types of joint statements as well as sub queries in SQL.', 'start': 153.7, 'duration': 3.962}, {'end': 163.484, 'text': 'Moving further, you will understand triggers, stored procedures and window functions in MySQL.', 'start': 158.422, 'duration': 5.062}, {'end': 170.047, 'text': 'You will also get an idea about another important topic that is to write SQL queries with Python on the Jupyter Notebook.', 'start': 163.964, 'duration': 6.083}, {'end': 178.81, 'text': 'Then we will look at another free and open source relational database management system that is Postgres SQL with extensive hands-on demonstration.', 'start': 170.726, 'duration': 8.084}, {'end': 183.913, 'text': 'Finally, we will look at the top SQL interview questions that are frequently asked in the interviews.', 'start': 179.451, 'duration': 4.462}, {'end': 185.374, 'text': "So let's get started.", 'start': 184.474, 'duration': 0.9}, {'end': 186.475, 'text': 'What is a database?', 'start': 185.594, 'duration': 0.881}, {'end': 189.696, 'text': 'So, according to Oracle,', 'start': 187.975, 'duration': 1.721}], 'summary': 'Covering group by, joins, triggers, stored procedures, and more in sql. also, postgres sql and sql interview questions.', 'duration': 40.669, 'max_score': 149.027, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g149027.jpg'}, {'end': 237.74, 'src': 'embed', 'start': 212.291, 'weight': 7, 'content': [{'end': 218.476, 'text': 'You can organize data into tables, rows, columns and index it to make it easier to find relevant information.', 'start': 212.291, 'duration': 6.185}, {'end': 221.398, 'text': 'Now talking about some of the popular databases.', 'start': 219.136, 'duration': 2.262}, {'end': 224.89, 'text': 'We have MySQL database.', 'start': 223.089, 'duration': 1.801}, {'end': 226.992, 'text': 'We also have Oracle database.', 'start': 225.491, 'duration': 1.501}, {'end': 229.994, 'text': 'Then we have MongoDB which is a NoSQL database.', 'start': 227.412, 'duration': 2.582}, {'end': 233.276, 'text': 'Next we have Microsoft SQL Server.', 'start': 230.875, 'duration': 2.401}, {'end': 237.74, 'text': 'Next we have Apache Cassandra which is a free and open source NoSQL database.', 'start': 233.817, 'duration': 3.923}], 'summary': 'Organize data into databases like mysql, oracle, mongodb, microsoft sql server, and apache cassandra.', 'duration': 25.449, 'max_score': 212.291, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g212291.jpg'}, {'end': 367.895, 'src': 'embed', 'start': 341.099, 'weight': 5, 'content': [{'end': 344.94, 'text': 'SQL is also used for maintaining an already existing database.', 'start': 341.099, 'duration': 3.841}, {'end': 351.404, 'text': 'SQL is a powerful language for entering data, modifying data and extracting data in a database.', 'start': 345.781, 'duration': 5.623}, {'end': 360.168, 'text': 'SQL is extensively used as a client-server language to connect the front-end with the back-end, thus supporting the client-server architecture.', 'start': 352.264, 'duration': 7.904}, {'end': 367.895, 'text': 'SQL when deployed as Data Control Language helps protect your database from unauthorized access.', 'start': 361.073, 'duration': 6.822}], 'summary': 'Sql is a powerful language for data manipulation and database maintenance, extensively used in client-server architecture and for data security.', 'duration': 26.796, 'max_score': 341.099, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g341099.jpg'}, {'end': 526.081, 'src': 'embed', 'start': 498.591, 'weight': 4, 'content': [{'end': 508.543, 'text': 'SQL developer should be well versed with structured query language to create optimized SQL queries and refines the existing ones to extract information from the database.', 'start': 498.591, 'duration': 9.952}, {'end': 516.395, 'text': 'SQL developers run several diagnostic tests to keep a check on the server and the database.', 'start': 510.931, 'duration': 5.464}, {'end': 526.081, 'text': "In order to understand how to organize company's data, SQL developers must communicate with technical and non-technical persons from the business,", 'start': 516.995, 'duration': 9.086}], 'summary': 'Sql developers optimize queries, run diagnostic tests, and communicate with technical and non-technical personnel to organize company data.', 'duration': 27.49, 'max_score': 498.591, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g498591.jpg'}, {'end': 678.227, 'src': 'embed', 'start': 628.079, 'weight': 3, 'content': [{'end': 640.122, 'text': 'One should have a good understanding of various database management systems, such as MySQL, Microsoft Access, Oracle, PostgreSQL, Dbase, FoxPro,', 'start': 628.079, 'duration': 12.043}, {'end': 647.627, 'text': 'SQLite, IBM DB2, LibreOffice Base, MariaDB and Microsoft SQL Server.', 'start': 640.122, 'duration': 7.505}, {'end': 655.653, 'text': 'One should know integration of databases with data visualization software such as Power BI and Tableau,', 'start': 648.628, 'duration': 7.025}, {'end': 660.998, 'text': 'which helps businesses to make better decisions and give you an add-on as a SQL developer.', 'start': 655.653, 'duration': 5.345}, {'end': 666.702, 'text': 'One should develop critical thinking and problem solving skills to create optimized queries.', 'start': 661.778, 'duration': 4.924}, {'end': 672.865, 'text': "Now we'll see how a SQL developer is compensated in both United States and India.", 'start': 667.623, 'duration': 5.242}, {'end': 678.227, 'text': 'In the United States, SQL developers draw an average salary of $72, 282 per annum.', 'start': 673.765, 'duration': 4.462}], 'summary': 'Sql developers need to master various database systems, integrate with data visualization tools, and develop critical thinking skills. in the us, they earn an average of $72,282 per year.', 'duration': 50.148, 'max_score': 628.079, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g628079.jpg'}], 'start': 13.584, 'title': "Sql's business importance", 'summary': "Discusses sql's crucial role in business, emphasizing its extensive use, impact on various job roles, and the average $88,912 annual salary for a sql developer in the us. it also covers postgres sql fundamentals, developer skills, average salaries, and steps to proficiency, along with top hiring companies.", 'chapters': [{'end': 170.047, 'start': 13.584, 'title': 'Importance of sql in business', 'summary': 'Discusses the importance and relevance of sql in business, highlighting its widespread use by big companies, its critical role in various job roles, and the high average salary of a sql developer in the united states, which is $88,912 per annum.', 'duration': 156.463, 'highlights': ['SQL is used by big companies like Amazon, Google, Oracle, and Microsoft as well as startups, making it a highly sought after skill with an average salary of $88,912 per annum in the United States.', 'SQL plays a critical role in various job roles such as software developer, data analyst, marketing analyst, product manager, and data scientist, making it a top language for data work.', 'The chapter covers a comprehensive list of topics including learning the basics of SQL, becoming a SQL developer, installing MySQL Workbench on Windows, using popular SQL commands, learning numerical and text functions, understanding GROUP BY, HAVING, JOINS, sub queries, triggers, stored procedures, window functions in MySQL, and writing SQL queries with Python on the Jupyter Notebook.']}, {'end': 775.076, 'start': 170.726, 'title': 'Postgres sql and sql developer', 'summary': 'Covers the fundamentals of databases, features of sql, responsibilities and skill set of a sql developer, average salaries in the us and india, and steps to become a proficient sql developer, along with the top companies hiring sql developers.', 'duration': 604.35, 'highlights': ['SQL developers in the United States earn an average salary of $72, 282 per annum. In the United States, SQL developers draw an average salary of $72, 282 per annum.', 'Responsibilities of a SQL developer include fixing general database issues, running diagnostic tests, and communicating with technical/non-technical personnel to gather client requirements. A SQL developer is responsible for fixing general issues of the database, running diagnostic tests, and gathering client requirements.', 'SQL is extensively used as a client-server language to connect the front-end with the back-end, supporting the client-server architecture. SQL is extensively used as a client-server language, connecting the front-end with the back-end, thus supporting the client-server architecture.', 'Postgres SQL is a free and open-source relational database management system with extensive hands-on demonstration. Postgres SQL is a free and open-source relational database management system with extensive hands-on demonstration.', 'The popular databases include MySQL, Oracle, MongoDB (NoSQL), Microsoft SQL Server, Apache Cassandra (NoSQL), and PostgreSQL. Popular databases include MySQL, Oracle, MongoDB, Microsoft SQL Server, Apache Cassandra, and PostgreSQL.', 'Microsoft SQL Server and PostgreSQL are among the popular databases. Microsoft SQL Server and PostgreSQL are among the popular databases.']}], 'duration': 761.492, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g13584.jpg', 'highlights': ['SQL is used by big companies like Amazon, Google, Oracle, and Microsoft as well as startups, making it a highly sought after skill with an average salary of $88,912 per annum in the United States.', 'SQL plays a critical role in various job roles such as software developer, data analyst, marketing analyst, product manager, and data scientist, making it a top language for data work.', 'The chapter covers a comprehensive list of topics including learning the basics of SQL, becoming a SQL developer, installing MySQL Workbench on Windows, using popular SQL commands, learning numerical and text functions, understanding GROUP BY, HAVING, JOINS, sub queries, triggers, stored procedures, window functions in MySQL, and writing SQL queries with Python on the Jupyter Notebook.', 'SQL developers in the United States earn an average salary of $72, 282 per annum. In the United States, SQL developers draw an average salary of $72, 282 per annum.', 'Responsibilities of a SQL developer include fixing general database issues, running diagnostic tests, and communicating with technical/non-technical personnel to gather client requirements. A SQL developer is responsible for fixing general issues of the database, running diagnostic tests, and gathering client requirements.', 'SQL is extensively used as a client-server language to connect the front-end with the back-end, supporting the client-server architecture. SQL is extensively used as a client-server language, connecting the front-end with the back-end, thus supporting the client-server architecture.', 'Postgres SQL is a free and open-source relational database management system with extensive hands-on demonstration. Postgres SQL is a free and open-source relational database management system with extensive hands-on demonstration.', 'Popular databases include MySQL, Oracle, MongoDB, Microsoft SQL Server, Apache Cassandra, and PostgreSQL.', 'Microsoft SQL Server and PostgreSQL are among the popular databases. Microsoft SQL Server and PostgreSQL are among the popular databases.']}, {'end': 1560.705, 'segs': [{'end': 803.6, 'src': 'embed', 'start': 775.609, 'weight': 5, 'content': [{'end': 778.45, 'text': 'In this session, we will learn about ER Diagram.', 'start': 775.609, 'duration': 2.841}, {'end': 783.572, 'text': "Starting with what is an ER Diagram and why it's been so much used by the companies.", 'start': 778.97, 'duration': 4.602}, {'end': 789.995, 'text': 'Then we will learn about the symbols used in the ER Diagram and get familiar with the components of it.', 'start': 784.232, 'duration': 5.763}, {'end': 796.977, 'text': 'So hey everyone, I am Abhisar Avuja from Simply Learn and welcome to this video on ER Diagram in DBMS.', 'start': 790.795, 'duration': 6.182}, {'end': 803.6, 'text': 'But before we begin, if you love watching tech videos, subscribe to our channel and hit the bell icon to never miss an update.', 'start': 797.478, 'duration': 6.122}], 'summary': 'Session on er diagram covers its usage and symbols in dbms.', 'duration': 27.991, 'max_score': 775.609, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g775609.jpg'}, {'end': 864.944, 'src': 'embed', 'start': 816.342, 'weight': 2, 'content': [{'end': 821.366, 'text': 'To perform operations on this data, they should have a conceptual understanding of these databases.', 'start': 816.342, 'duration': 5.024}, {'end': 825.329, 'text': 'And this is done by understanding ER diagrams of their databases.', 'start': 821.967, 'duration': 3.362}, {'end': 828.452, 'text': "So let's understand what an ER diagram is.", 'start': 826.11, 'duration': 2.342}, {'end': 836.525, 'text': 'An entity relationship diagram describes the relationship of entities that needs to be stored in a database.', 'start': 830.233, 'duration': 6.292}, {'end': 840.608, 'text': 'ER Diagram is mainly a structural design for the database.', 'start': 837.286, 'duration': 3.322}, {'end': 846.393, 'text': 'It is a framework made using specialized symbols to define the relationship between entities.', 'start': 841.249, 'duration': 5.144}, {'end': 852.438, 'text': 'ER Diagrams are created based on the three main components, entities, attributes and relationships.', 'start': 847.013, 'duration': 5.425}, {'end': 857.162, 'text': "Let's understand the use of ER Diagram with the help of a real world example.", 'start': 853.138, 'duration': 4.024}, {'end': 864.944, 'text': 'Here, a school needs all its student records to be stored digitally, so they approach an IT company to do so.', 'start': 858.001, 'duration': 6.943}], 'summary': 'Er diagrams help in designing databases by defining entity relationships and attributes. it is a conceptual understanding of databases.', 'duration': 48.602, 'max_score': 816.342, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g816342.jpg'}, {'end': 959.353, 'src': 'embed', 'start': 934.393, 'weight': 0, 'content': [{'end': 943.18, 'text': 'ER diagram is a GUI representation of the logical structure of a database which gives a better understanding of the information to be stored in a database.', 'start': 934.393, 'duration': 8.787}, {'end': 952.047, 'text': 'Database designers can use ER diagrams as a blueprint which reduces complexity and helps them save time to build databases quickly.', 'start': 944.161, 'duration': 7.886}, {'end': 959.353, 'text': 'ER diagrams helps you identify the entities that exist in a system and the relationships between those entities.', 'start': 952.067, 'duration': 7.286}], 'summary': 'Er diagrams provide a visual representation of a database, aiding in database design, reducing complexity, and saving time for designers.', 'duration': 24.96, 'max_score': 934.393, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g934393.jpg'}, {'end': 1405.342, 'src': 'embed', 'start': 1374.822, 'weight': 4, 'content': [{'end': 1376.823, 'text': 'There are mainly four types of SQL commands.', 'start': 1374.822, 'duration': 2.001}, {'end': 1380.365, 'text': 'So first we have Data Definition Language or DDL.', 'start': 1377.603, 'duration': 2.762}, {'end': 1387.687, 'text': 'So DDL commands change the structure of the table like creating a table, deleting a table or altering a table.', 'start': 1381.664, 'duration': 6.023}, {'end': 1393.15, 'text': 'All the commands of DDL are auto committed which means it permanently save all the changes in the database.', 'start': 1387.887, 'duration': 5.263}, {'end': 1397.893, 'text': 'We have create, alter, drop and truncate as DDL commands.', 'start': 1394.051, 'duration': 3.842}, {'end': 1401.935, 'text': 'Next, we have data manipulation language or DML.', 'start': 1398.933, 'duration': 3.002}, {'end': 1405.342, 'text': 'So DML commands are used to modify a database.', 'start': 1402.941, 'duration': 2.401}], 'summary': 'The transcript covers four types of sql commands: ddl and dml, with ddl having commands like create, alter, drop, and truncate.', 'duration': 30.52, 'max_score': 1374.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g1374822.jpg'}], 'start': 775.609, 'title': 'Understanding er diagrams', 'summary': 'Covers the significance and components of er diagrams in dbms, emphasizing their relevance in managing large and constantly updating datasets for e-commerce companies, and explains their concept, components, and usage with a real-world example of a school database, highlighting their importance in communicating the logical structure of a database and their role in reducing complexity and saving time in database design.', 'chapters': [{'end': 815.761, 'start': 775.609, 'title': 'Understanding er diagram in dbms', 'summary': 'Covers the significance and components of er diagram in dbms, emphasizing its relevance in managing large and constantly updating datasets for e-commerce companies.', 'duration': 40.152, 'highlights': ['The ER Diagram is essential for managing large and constantly updating datasets for e-commerce companies.', 'It explains the significance and components of ER Diagram in DBMS.', 'It highlights the relevance of ER Diagram in managing data for e-commerce companies.']}, {'end': 1560.705, 'start': 816.342, 'title': 'Understanding er diagrams and database operations', 'summary': 'Explains the concept of er diagrams, including its components and usage, with a real-world example of a school database, highlighting the importance of er diagrams in communicating the logical structure of a database and their role in reducing complexity and saving time in database design.', 'duration': 744.363, 'highlights': ['Importance of ER Diagrams ER diagrams communicate the landscape of business to different teams in a company, supporting the business, and provide a better understanding of the information to be stored in a database, thus reducing complexity and helping save time to build databases quickly.', 'Components of ER Diagrams ER diagrams consist of entities, attributes, and relationships, with further classifications such as weak entities, key attributes, composite attributes, multi-valued attributes, and derived attributes, each serving specific purposes in defining database structures.', 'Types of Relationships in ER Diagrams ER diagrams depict various types of relationships, including one-to-one, one-to-many, many-to-one, and many-to-many relationships, providing a comprehensive view of how entities are interrelated in a database system.', 'SQL Commands and Data Types The chapter also covers SQL commands such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL), along with a breakdown of the basic SQL command structure and various data types and operators in SQL.']}], 'duration': 785.096, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g775609.jpg', 'highlights': ['ER Diagram is essential for managing large and constantly updating datasets for e-commerce companies.', 'ER diagrams communicate the landscape of business to different teams in a company, supporting the business, and provide a better understanding of the information to be stored in a database, thus reducing complexity and helping save time to build databases quickly.', 'Components of ER Diagrams consist of entities, attributes, and relationships, with further classifications such as weak entities, key attributes, composite attributes, multi-valued attributes, and derived attributes, each serving specific purposes in defining database structures.', 'ER diagrams depict various types of relationships, including one-to-one, one-to-many, many-to-one, and many-to-many relationships, providing a comprehensive view of how entities are interrelated in a database system.', 'The chapter also covers SQL commands such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL), along with a breakdown of the basic SQL command structure and various data types and operators in SQL.', 'It explains the significance and components of ER Diagram in DBMS.', 'It highlights the relevance of ER Diagram in managing data for e-commerce companies.', 'Types of Relationships in ER Diagrams provide a comprehensive view of how entities are interrelated in a database system.']}, {'end': 2462.094, 'segs': [{'end': 1644.55, 'src': 'embed', 'start': 1561.346, 'weight': 0, 'content': [{'end': 1568.367, 'text': 'Now let me take you to my MySQL workbench, where we will learn to write some of the important SQL commands, use different statements, functions,', 'start': 1561.346, 'duration': 7.021}, {'end': 1570.308, 'text': 'data types and operators that we just learned.', 'start': 1568.367, 'duration': 1.941}, {'end': 1574.089, 'text': 'In this session we will learn how to install MySQL workbench.', 'start': 1570.868, 'duration': 3.221}, {'end': 1577.354, 'text': 'then we will run some commands.', 'start': 1574.591, 'duration': 2.763}, {'end': 1586.622, 'text': "firstly, we will visit the official oracle website that is mysql.com, and now we'll move to the downloads page.", 'start': 1577.354, 'duration': 9.268}, {'end': 1593.388, 'text': 'now scroll down and click on mysql gpl downloads.', 'start': 1586.622, 'duration': 6.766}, {'end': 1599.313, 'text': 'now, under community downloads, click on mysql installer for windows.', 'start': 1593.388, 'duration': 5.925}, {'end': 1601.115, 'text': 'the current versions are available to download.', 'start': 1599.313, 'duration': 1.802}, {'end': 1609.226, 'text': 'I will choose this installer and click the download button now here just click on.', 'start': 1602.901, 'duration': 6.325}, {'end': 1613.73, 'text': 'no thanks, just start my download.', 'start': 1609.226, 'duration': 4.504}, {'end': 1622.717, 'text': 'once the installer has downloaded, open it, you may be prompted for permission.', 'start': 1613.73, 'duration': 8.987}, {'end': 1627.521, 'text': 'click yes, this opens the installer.', 'start': 1622.717, 'duration': 4.804}, {'end': 1629.803, 'text': 'you will be asked to choose the setup type.', 'start': 1627.521, 'duration': 2.282}, {'end': 1636.486, 'text': 'we will go with custom click next.', 'start': 1629.803, 'duration': 6.683}, {'end': 1639.268, 'text': 'now you have to select the products you want to install.', 'start': 1636.486, 'duration': 2.782}, {'end': 1644.55, 'text': 'we will install only the mysql server, mysql shell and the mysql workbench.', 'start': 1639.268, 'duration': 5.282}], 'summary': 'Learn mysql workbench: install mysql and run commands from official website mysql.com', 'duration': 83.204, 'max_score': 1561.346, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g1561346.jpg'}, {'end': 1792.212, 'src': 'embed', 'start': 1752.822, 'weight': 9, 'content': [{'end': 1760.305, 'text': 'and here also we will keep the default settings and click on next now to apply configuration.', 'start': 1752.822, 'duration': 7.483}, {'end': 1765.927, 'text': 'we will execute the process once all the configuration steps are complete.', 'start': 1760.305, 'duration': 5.622}, {'end': 1770.289, 'text': 'click finish now.', 'start': 1765.927, 'duration': 4.362}, {'end': 1772.209, 'text': 'you will see the installation is complete.', 'start': 1770.289, 'duration': 1.92}, {'end': 1774.89, 'text': 'it will launch mysql workbench and mysql shell.', 'start': 1772.209, 'duration': 2.681}, {'end': 1787.368, 'text': 'after clicking on finish now the shell and workbench has started.', 'start': 1774.89, 'duration': 12.478}, {'end': 1792.212, 'text': 'now we will connect by clicking on the root user.', 'start': 1787.368, 'duration': 4.844}], 'summary': 'Installation completed with default settings, launching mysql workbench and mysql shell, connected as root user.', 'duration': 39.39, 'max_score': 1752.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g1752822.jpg'}, {'end': 1993.545, 'src': 'embed', 'start': 1953.924, 'weight': 3, 'content': [{'end': 1960.429, 'text': 'these are the databases that are stored already in the database.', 'start': 1953.924, 'duration': 6.505}, {'end': 1966.974, 'text': 'now there are four databases, that is, information schema, mysql performance schema and sys.', 'start': 1960.429, 'duration': 6.545}, {'end': 1971.286, 'text': 'now we will select one of the database.', 'start': 1968.243, 'duration': 3.043}, {'end': 1981.895, 'text': 'we will use mysql.', 'start': 1971.286, 'duration': 10.609}, {'end': 1990.422, 'text': 'now we have selected the mysql database and now in this database we will see which tables are stored in this mysql database.', 'start': 1981.895, 'duration': 8.527}, {'end': 1993.545, 'text': 'to see that we will run a command show tables.', 'start': 1990.422, 'duration': 3.123}], 'summary': "There are four databases stored in the system, including mysql. we will use 'show tables' to view tables in the mysql database.", 'duration': 39.621, 'max_score': 1953.924, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g1953924.jpg'}, {'end': 2305.169, 'src': 'embed', 'start': 2272.653, 'weight': 4, 'content': [{'end': 2278.639, 'text': 'so let me first show you how to create a table that will be present inside the SQL intro database.', 'start': 2272.653, 'duration': 5.986}, {'end': 2290.681, 'text': "So I'll use the command create table and then I'll give my table name that is going to be employee underscore details.", 'start': 2280.256, 'duration': 10.425}, {'end': 2296.845, 'text': 'Next, the syntax is to give the column names.', 'start': 2292.402, 'duration': 4.443}, {'end': 2305.169, 'text': 'So my first column would be the name column, which is basically the employee name followed by the data type for this column.', 'start': 2297.265, 'duration': 7.904}], 'summary': "Demonstrates creation of a table 'employee_details' in sql intro database with a 'name' column.", 'duration': 32.516, 'max_score': 2272.653, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g2272653.jpg'}, {'end': 2435.922, 'src': 'embed', 'start': 2404.636, 'weight': 7, 'content': [{'end': 2407.217, 'text': 'For each of the columns we also give the data type.', 'start': 2404.636, 'duration': 2.581}, {'end': 2408.397, 'text': 'All right.', 'start': 2408.057, 'duration': 0.34}, {'end': 2409.518, 'text': 'So let me just run it.', 'start': 2408.537, 'duration': 0.981}, {'end': 2414.5, 'text': 'Okay So here you can see we have successfully created our first table.', 'start': 2410.698, 'duration': 3.802}, {'end': 2420.442, 'text': 'Now you can use the describe command to see the structure of the table.', 'start': 2415.82, 'duration': 4.622}, {'end': 2425.784, 'text': "I'll write describe emp underscore details.", 'start': 2423.183, 'duration': 2.601}, {'end': 2430.94, 'text': 'if I run this, there you go.', 'start': 2427.759, 'duration': 3.181}, {'end': 2433.501, 'text': 'so under field you can see the column names.', 'start': 2430.94, 'duration': 2.561}, {'end': 2435.922, 'text': 'then you have the data types.', 'start': 2433.501, 'duration': 2.421}], 'summary': 'Created first table with column names and data types.', 'duration': 31.286, 'max_score': 2404.636, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g2404636.jpg'}], 'start': 1561.346, 'title': 'Mysql workbench installation and basic commands', 'summary': "Covers mysql workbench installation process, including choosing the appropriate installer and versions, followed by running sql commands. it also details mysql server installation, setting passwords, applying configurations, and connecting to the server. additionally, it explains creating a database and a table named 'employee_details' with specific columns and adding data to the table.", 'chapters': [{'end': 1677.116, 'start': 1561.346, 'title': 'Installing mysql workbench and running commands', 'summary': 'Covers the installation of mysql workbench by visiting the official website, choosing the appropriate installer, and selecting the necessary products and versions, followed by running sql commands.', 'duration': 115.77, 'highlights': ['The chapter covers the installation process of MySQL Workbench, including visiting mysql.com, choosing the installer, and selecting the products and versions needed for the installation.', 'The installation process involves visiting the official Oracle website mysql.com, selecting the MySQL installer for Windows, and choosing the specific versions of MySQL server, MySQL shell, and MySQL Workbench to be installed.', 'The installation process requires selecting the appropriate products to be installed, including MySQL server, MySQL shell, and MySQL Workbench, and choosing the specific versions for each product.']}, {'end': 2225.512, 'start': 1677.116, 'title': 'Mysql installation and basic commands', 'summary': 'Details the installation of mysql server, including setting passwords, applying configurations, and connecting to the server. it also covers basic commands such as show databases, show tables, select, and describe, providing insights into existing databases, tables, and table structures.', 'duration': 548.396, 'highlights': ['The installation of MySQL server is detailed, including setting passwords, applying configurations, and connecting to the server. The installation process is described, including setting passwords for the root user, applying configurations, and successfully connecting to the server.', 'Basic MySQL commands such as show databases, show tables, select, and describe are explained, providing insights into existing databases, tables, and table structures. The use of basic MySQL commands to retrieve information about existing databases, tables, and table structures is demonstrated, enhancing understanding of the database environment.', 'Details on creating a table in MySQL are provided, covering the syntax and functionality of the create table command. The process of creating a table in MySQL is explained, including the syntax and functionality of the create table command, offering practical insights into table creation.']}, {'end': 2462.094, 'start': 2225.512, 'title': 'Creating database and table in sql', 'summary': "Explains how to create a database named 'sql intro' and a table named 'employee_details' with columns like name, age, gender, date of join, city, and salary. it also demonstrates using the describe command to view the table structure and adding data to the table using the insert command.", 'duration': 236.582, 'highlights': ["Creating a database named 'SQL intro' and a table named 'employee_details' The transcript details the process of creating a database named 'SQL intro' and a table named 'employee_details' with specified columns.", 'Using the describe command to view the table structure It explains the usage of the describe command to view the structure of the table, displaying column names, data types, and null value acceptance.', "Adding data to the table using the insert command The transcript demonstrates the usage of the insert command to add data to the 'employee_details' table, as well as provides a sample insert statement."]}], 'duration': 900.748, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g1561346.jpg', 'highlights': ['The installation process involves visiting the official Oracle website mysql.com, selecting the MySQL installer for Windows, and choosing the specific versions of MySQL server, MySQL shell, and MySQL Workbench to be installed.', 'The installation process requires selecting the appropriate products to be installed, including MySQL server, MySQL shell, and MySQL Workbench, and choosing the specific versions for each product.', 'The chapter covers the installation process of MySQL Workbench, including visiting mysql.com, choosing the installer, and selecting the products and versions needed for the installation.', 'Basic MySQL commands such as show databases, show tables, select, and describe are explained, providing insights into existing databases, tables, and table structures.', 'Details on creating a table in MySQL are provided, covering the syntax and functionality of the create table command.', 'The process of creating a table in MySQL is explained, including the syntax and functionality of the create table command, offering practical insights into table creation.', "Creating a database named 'SQL intro' and a table named 'employee_details' The transcript details the process of creating a database named 'SQL intro' and a table named 'employee_details' with specified columns.", 'Using the describe command to view the table structure It explains the usage of the describe command to view the structure of the table, displaying column names, data types, and null value acceptance.', "Adding data to the table using the insert command The transcript demonstrates the usage of the insert command to add data to the 'employee_details' table, as well as provides a sample insert statement.", 'The installation of MySQL server is detailed, including setting passwords, applying configurations, and connecting to the server. The installation process is described, including setting passwords for the root user, applying configurations, and successfully connecting to the server.']}, {'end': 3470.815, 'segs': [{'end': 2570.022, 'src': 'embed', 'start': 2544.163, 'weight': 0, 'content': [{'end': 2553.045, 'text': 'so we have the name, column, the age column, the state of join city salary, and these are the values that you can see here.', 'start': 2544.163, 'duration': 8.882}, {'end': 2558.706, 'text': "moving ahead now, let's say you want to see the unique city names present in the table.", 'start': 2553.045, 'duration': 5.661}, {'end': 2564.007, 'text': 'so in this case you can use the distinct keyword along with the column name in the select statement.', 'start': 2558.706, 'duration': 5.301}, {'end': 2570.022, 'text': 'so let me show you how you can print the distinct city names that are present in our table.', 'start': 2564.007, 'duration': 6.015}], 'summary': 'Demonstrating use of distinct keyword to obtain unique city names from a table.', 'duration': 25.859, 'max_score': 2544.163, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g2544163.jpg'}, {'end': 2829.402, 'src': 'embed', 'start': 2799.316, 'weight': 1, 'content': [{'end': 2804.518, 'text': 'so if you want to filter your table based on specific conditions, you can use where clause.', 'start': 2799.316, 'duration': 5.202}, {'end': 2808.96, 'text': 'now, where clause comes after you give your table name.', 'start': 2804.518, 'duration': 4.442}, {'end': 2815.562, 'text': 'so suppose you want to find the employees with age greater than 30, in this case you can use a where clause.', 'start': 2808.96, 'duration': 6.602}, {'end': 2816.843, 'text': 'so let me show you how to do it.', 'start': 2815.562, 'duration': 1.281}, {'end': 2829.402, 'text': "I'll write select star from my table name, that is employee details, and after this I'll use my where clause.", 'start': 2817.93, 'duration': 11.472}], 'summary': "Use 'where' clause to filter table based on specific conditions.", 'duration': 30.086, 'max_score': 2799.316, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g2799316.jpg'}, {'end': 3197.539, 'src': 'embed', 'start': 3155.076, 'weight': 2, 'content': [{'end': 3160.957, 'text': "Let's say we want to find the total salary of employees based on their gender.", 'start': 3155.076, 'duration': 5.881}, {'end': 3164.217, 'text': 'So in this case you can use the GROUP BY clause.', 'start': 3160.957, 'duration': 3.26}, {'end': 3170.559, 'text': "So I'll write SELECT, Let's say SEX comma.", 'start': 3164.217, 'duration': 6.342}, {'end': 3187.294, 'text': "I want to find the total sum of salary, as i'll give an alias name, let's say total salary from my table name, that is, employee details.", 'start': 3170.559, 'duration': 16.735}, {'end': 3193.119, 'text': 'next i am going to group it by sex.', 'start': 3187.294, 'duration': 5.825}, {'end': 3196.458, 'text': 'okay, let me run it.', 'start': 3193.119, 'duration': 3.339}, {'end': 3197.539, 'text': 'there you go.', 'start': 3196.458, 'duration': 1.081}], 'summary': 'Finding total salary by gender using group by clause.', 'duration': 42.463, 'max_score': 3155.076, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3155076.jpg'}, {'end': 3276.579, 'src': 'embed', 'start': 3249.725, 'weight': 3, 'content': [{'end': 3254.409, 'text': 'you can see the salary column is sorted in ascending order.', 'start': 3249.725, 'duration': 4.684}, {'end': 3262.956, 'text': 'now, suppose you want to sort the salary column and display it in descending order, you can use this keyword that is DESC.', 'start': 3254.409, 'duration': 8.547}, {'end': 3263.537, 'text': 'let me run it.', 'start': 3262.956, 'duration': 0.581}, {'end': 3266.373, 'text': 'you can see the output now.', 'start': 3265.27, 'duration': 1.103}, {'end': 3270.947, 'text': 'this time the salary is sorted in descending order and you have the other values as well.', 'start': 3266.373, 'duration': 4.574}, {'end': 3276.579, 'text': 'now let me show you some basic operations that you can do using the SELECT statement.', 'start': 3271.556, 'duration': 5.023}], 'summary': 'Demonstrated sorting salary column in descending order using desc keyword.', 'duration': 26.854, 'max_score': 3249.725, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3249725.jpg'}, {'end': 3338.697, 'src': 'embed', 'start': 3306.93, 'weight': 4, 'content': [{'end': 3310.554, 'text': "let's run it, you get minus 10..", 'start': 3306.93, 'duration': 3.624}, {'end': 3312.777, 'text': 'now there are some basic inbuilt functions.', 'start': 3310.554, 'duration': 2.223}, {'end': 3318.385, 'text': "there are a lot of inbuilt functions in SQL, but here I'll show you a few.", 'start': 3313.482, 'duration': 4.903}, {'end': 3321.928, 'text': 'suppose you want to find the length of a text or a string.', 'start': 3318.385, 'duration': 3.543}, {'end': 3324.209, 'text': 'you can use the length function.', 'start': 3321.928, 'duration': 2.281}, {'end': 3327.411, 'text': "so I'll write select and then use the length function.", 'start': 3324.209, 'duration': 3.202}, {'end': 3329.513, 'text': "I'll hit tab to autocomplete.", 'start': 3327.411, 'duration': 2.102}, {'end': 3338.697, 'text': "let's say I want to find the length of country, India, and I'll give an alias as total length.", 'start': 3329.513, 'duration': 9.184}], 'summary': 'Sql tutorial: demonstrating length function, finding length of text, and string.', 'duration': 31.767, 'max_score': 3306.93, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3306930.jpg'}], 'start': 2462.094, 'title': 'Sql data manipulation', 'summary': "Covers inserting six records into the 'emp details' table and displaying the table, using distinct keyword to retrieve unique city names, and employing count, sum, and average functions to calculate employee count, total salary, and average salary. additionally, it explains sql select, filter operations, operators such as between, and, group by, order by, and various functions for data manipulation.", 'chapters': [{'end': 2544.163, 'start': 2462.094, 'title': 'Database insert and display', 'summary': "Covers the process of inserting six records into the 'emp details' table using insert into statement and subsequently displaying the table using select statement.", 'duration': 82.069, 'highlights': ["The process of inserting six records into the 'emp details' table using INSERT INTO statement. It is mentioned that the values have been successfully inserted as six records into the 'emp details' table.", "Displaying the 'emp details' table using SELECT statement. The SELECT statement is used to display the table and its values, providing a visual representation of the inserted records."]}, {'end': 2746.167, 'start': 2544.163, 'title': 'Sql functions and distinct values', 'summary': 'Explains the usage of distinct keyword to retrieve unique city names from a table, using count, sum, and average functions to calculate employee count, total salary, and average salary respectively in sql.', 'duration': 202.004, 'highlights': ["The distinct keyword can be used to retrieve unique city names from a table by using the 'select distinct' statement, resulting in five unique city names being returned.", 'The count function in the select statement returns the total number of employees present in the table, which is six employees.', 'The sum function in the select statement calculates the total sum of salaries, providing the combined sum of all salaries present in the salary column.', 'The average function, AVG, can be used to find the average salary from the salary column, allowing for the calculation of the average salary.']}, {'end': 3004.65, 'start': 2746.167, 'title': 'Sql select and filter operations', 'summary': 'Explains how to select specific columns from a table, use the where clause to filter rows based on conditions, and employ the or and in operators to specify multiple conditions in sql queries, with examples demonstrating the results achieved.', 'duration': 258.483, 'highlights': ["Using SELECT statement to retrieve specific columns and all columns from a table. The speaker demonstrates using the SELECT statement to retrieve specific columns and all columns from a table, with an example showcasing the usage of 'SELECT *' to display all columns from the employee details table.", 'Utilizing the WHERE clause to filter rows based on specific conditions, such as age greater than 30 and retrieving the number of employees meeting the condition. The explanation covers the usage of the WHERE clause to filter rows based on specific conditions, such as age greater than 30, and showcases the result of the query, which highlights the number of employees meeting the given condition.', 'Demonstrating the use of WHERE clause to filter rows based on a specific condition, such as retrieving only female employees from the table. The speaker demonstrates how to use the WHERE clause to filter rows based on a specific condition, such as retrieving only female employees from the table, and presents the result showing the number of female employees found.', 'Illustrating the use of OR operator to retrieve records based on multiple conditions, such as employees from Chicago or Austin, and displaying the resulting records. The chapter explains the use of the OR operator to retrieve records based on multiple conditions, such as employees from Chicago or Austin, and provides an example displaying the resulting records based on the given conditions.', 'Explaining the usage of the IN operator to specify multiple conditions, such as employees from Chicago and Austin, and highlighting the similarity of results with the OR operator. The chapter illustrates the usage of the IN operator to specify multiple conditions, such as employees from Chicago and Austin, and emphasizes the similarity of results with the OR operator, presenting the resulting output for comparison.']}, {'end': 3470.815, 'start': 3004.65, 'title': 'Sql operators and functions', 'summary': 'Covers the usage of sql operators such as between, and, group by, and order by, along with various functions including arithmetic, string manipulation, and date and time functions, exemplifying their usage and impact on data manipulation and retrieval.', 'duration': 466.165, 'highlights': ['The BETWEEN operator in SQL allows selection of values within a given range, such as employees joining between 1st Jan 2000 and 31st Dec 2010, resulting in two employees, Jimmy and Mary, meeting this criterion. The BETWEEN operator in SQL enables selection of values within a specified range, demonstrated by identifying employees who joined between 1st Jan 2000 and 31st Dec 2010, yielding two employees, Jimmy and Mary, who met this condition.', 'Usage of the AND operator in WHERE clause to specify multiple conditions, demonstrated by filtering employees with age greater than 30 and male sex, resulting in two male employees meeting both criteria. Demonstration of utilizing the AND operator in the WHERE clause to specify multiple conditions, illustrated by filtering employees with age greater than 30 and male sex, resulting in the retrieval of two male employees meeting both conditions.', 'Demonstration of the GROUP BY statement to group rows based on gender and calculate the total salary for each gender, resulting in the summarized total salary for male and female employees. Illustration of employing the GROUP BY statement to group rows based on gender and compute the total salary for each gender, culminating in the summarized total salary for male and female employees.', 'Illustration of using the ORDER BY keyword to sort the result set in ascending and descending order based on the salary column, showcasing the default ascending order and the DESC keyword for descending order. Illustration of employing the ORDER BY keyword to sort the result set in ascending and descending order based on the salary column, exemplifying the default ascending order and the use of the DESC keyword for sorting in descending order.', 'Demonstration of basic operations and inbuilt functions in SQL, including addition, subtraction, length calculation, string repetition, and case conversion, showcasing their practical application and output. Practical demonstration of basic operations and inbuilt functions in SQL, encompassing addition, subtraction, length calculation, string repetition, and case conversion, elucidating their practical application and resultant output.', 'Exemplification of date and time functions in SQL, such as obtaining the current date and extracting the day from a date value, showcasing the practical usage and output of these functions. Illustration of date and time functions in SQL, including obtaining the current date and extracting the day from a date value, demonstrating the practical usage and output of these functions.']}], 'duration': 1008.721, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g2462094.jpg', 'highlights': ['Using the DISTINCT keyword to retrieve unique city names from a table, resulting in five unique city names being returned.', 'Demonstrating the use of the WHERE clause to filter rows based on specific conditions, such as age greater than 30 and retrieving the number of employees meeting the condition.', 'Illustration of employing the GROUP BY statement to group rows based on gender and compute the total salary for each gender, culminating in the summarized total salary for male and female employees.', 'Demonstration of employing the ORDER BY keyword to sort the result set in ascending and descending order based on the salary column, exemplifying the default ascending order and the use of the DESC keyword for sorting in descending order.', 'Practical demonstration of basic operations and inbuilt functions in SQL, encompassing addition, subtraction, length calculation, string repetition, and case conversion, elucidating their practical application and resultant output.']}, {'end': 4350.681, 'segs': [{'end': 3522.663, 'src': 'embed', 'start': 3472.401, 'weight': 1, 'content': [{'end': 3480.865, 'text': 'similarly, you can also display the current date and time, so for that you can use a function that is called now.', 'start': 3472.401, 'duration': 8.464}, {'end': 3484.066, 'text': 'so this will return the current date and time.', 'start': 3480.865, 'duration': 3.201}, {'end': 3488.548, 'text': 'you can see this is the date value and then we have the current time.', 'start': 3484.066, 'duration': 4.482}, {'end': 3494.094, 'text': 'all right, and this brings us to the end of our demo session.', 'start': 3488.548, 'duration': 5.546}, {'end': 3496.936, 'text': 'so let me just scroll through whatever we have learned.', 'start': 3494.094, 'duration': 2.842}, {'end': 3501.96, 'text': 'so first I showed you how you can see the databases present in MySQL.', 'start': 3496.936, 'duration': 5.024}, {'end': 3506.042, 'text': 'then we used one of the databases and checked the tables in it.', 'start': 3501.96, 'duration': 4.082}, {'end': 3510.646, 'text': 'then we created another database called SQL intro for our demo purpose.', 'start': 3506.042, 'duration': 4.604}, {'end': 3521.741, 'text': 'we use that database and then we created this table called employee details with column names like name, integer, the sex, date of join,', 'start': 3510.646, 'duration': 11.095}, {'end': 3522.663, 'text': 'city and salary.', 'start': 3521.741, 'duration': 0.922}], 'summary': 'Demo covered displaying date and time, mysql database operations, and table creation.', 'duration': 50.262, 'max_score': 3472.401, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3472401.jpg'}, {'end': 3613.491, 'src': 'embed', 'start': 3575.73, 'weight': 3, 'content': [{'end': 3579.834, 'text': 'We learnt about IN operator, the BETWEEN operator.', 'start': 3575.73, 'duration': 4.104}, {'end': 3585.926, 'text': 'Then we used an AND operator to select multiple conditions.', 'start': 3581.044, 'duration': 4.882}, {'end': 3593.31, 'text': 'Finally, we learned about GROUP BY, ORDER BY and some basic SQL operations.', 'start': 3587.367, 'duration': 5.943}, {'end': 3598.673, 'text': "Now it's time to explore some string functions in MySQL.", 'start': 3594.15, 'duration': 4.523}, {'end': 3603.355, 'text': 'So I have given a comment string functions.', 'start': 3601.454, 'duration': 1.901}, {'end': 3613.491, 'text': "first, let's say you want to convert a certain string into uppercase so i can write select.", 'start': 3605.185, 'duration': 8.306}], 'summary': 'Learned in, between, and operators, group by, order by, and basic sql operations. next is exploring string functions in mysql.', 'duration': 37.761, 'max_score': 3575.73, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3575730.jpg'}, {'end': 3989.76, 'src': 'embed', 'start': 3932.09, 'weight': 0, 'content': [{'end': 3936.233, 'text': 'okay, you see, here the result is very clear.', 'start': 3932.09, 'duration': 4.143}, {'end': 3944.198, 'text': 'we have the student id, the student name and the concatenated column that we created, which was name, underscore age,', 'start': 3936.233, 'duration': 7.965}, {'end': 3951.137, 'text': 'where we have the student name with a space followed by the age of the student.', 'start': 3944.198, 'duration': 6.939}, {'end': 3954.461, 'text': 'if you scroll down, you can see the rest of the results.', 'start': 3951.137, 'duration': 3.324}, {'end': 3963.19, 'text': "cool, now, moving ahead, let's see how the reverse function works in mysql.", 'start': 3954.461, 'duration': 8.729}, {'end': 3967.675, 'text': 'so the mysql reverse function returns a string with the characters printed in reverse order.', 'start': 3963.19, 'duration': 4.485}, {'end': 3971.754, 'text': 'So suppose I write select reverse.', 'start': 3968.453, 'duration': 3.301}, {'end': 3974.195, 'text': "I'll use the same string again.", 'start': 3972.774, 'duration': 1.421}, {'end': 3977.196, 'text': "Let's say I have India.", 'start': 3974.615, 'duration': 2.581}, {'end': 3980.237, 'text': "Let's run it.", 'start': 3979.657, 'duration': 0.58}, {'end': 3984.578, 'text': 'You will see all the characters printed in reverse order.', 'start': 3981.077, 'duration': 3.501}, {'end': 3989.76, 'text': 'Again, you can perform the same operation on a table as well.', 'start': 3985.158, 'duration': 4.602}], 'summary': 'Demonstration of concatenation and reverse functions in mysql, with clear results and example outputs.', 'duration': 57.67, 'max_score': 3932.09, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3932090.jpg'}, {'end': 4067.638, 'src': 'embed', 'start': 4022.363, 'weight': 4, 'content': [{'end': 4025.265, 'text': 'so let me show you what i mean.', 'start': 4022.363, 'duration': 2.902}, {'end': 4030.02, 'text': "i'll write select, replace.", 'start': 4025.265, 'duration': 4.755}, {'end': 4043.204, 'text': "i will pass in my input string, which is, let's say, orange is a vegetable, which is ideally incorrect.", 'start': 4030.02, 'duration': 13.184}, {'end': 4055.688, 'text': 'i am purposely writing this so that i can replace the word vegetable with fruit.', 'start': 4043.204, 'duration': 12.484}, {'end': 4067.638, 'text': 'okay, so what this replace function does is it is going to find where my word vegetable is within the string, my input string,', 'start': 4058.556, 'duration': 9.082}], 'summary': "Demonstrating the usage of the replace function to replace 'vegetable' with 'fruit' in 'orange is a vegetable.'", 'duration': 45.275, 'max_score': 4022.363, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g4022363.jpg'}], 'start': 3472.401, 'title': 'Mysql string functions', 'summary': 'Covers mysql string functions including concat, reverse, replace, trim, position, and ascii, with examples and outcomes, such as concatenating names and ages, reversing strings, replacing substrings, trimming spaces, finding positions, and obtaining ascii values.', 'chapters': [{'end': 3888.258, 'start': 3472.401, 'title': 'Mysql demo and string functions', 'summary': 'Covers a mysql demo with demonstrations on database viewing, table creation, data insertion, select statements, aggregate functions, where clause, operators, group by, order by, and string functions like upper, lower, lcase, character length, and concat.', 'duration': 415.857, 'highlights': ['The chapter covers a MySQL demo with demonstrations on database viewing, table creation, data insertion, select statements, aggregate functions, WHERE clause, operators, GROUP BY, ORDER BY, and string functions like upper, lower, lcase, character length, and concat. The chapter provides a comprehensive demonstration of MySQL with a focus on database operations, SQL statements, and string functions.', "The function now returns the current date and time. The 'now' function returns the current date and time in MySQL, providing real-time data retrieval capabilities.", "The demonstration includes the creation of a table called 'employee details' with columns for name, age, gender, date of join, city, and salary. A table named 'employee details' is created with specific columns for storing employee information, facilitating structured data storage.", 'The chapter illustrates the use of various SQL statements such as SELECT, WHERE, GROUP BY, ORDER BY, and different operators. Demonstrations on SQL statements, including SELECT, WHERE, GROUP BY, ORDER BY, and operators, are provided for data retrieval and manipulation.', 'String functions like upper, lower, lcase, character length, and concat are demonstrated with practical examples and their applications in MySQL. The practical applications and usage of string functions such as upper, lower, lcase, character length, and concat in MySQL are demonstrated in detail.']}, {'end': 4350.681, 'start': 3888.258, 'title': 'Mysql string functions', 'summary': 'Covers various string functions in mysql including concat, reverse, replace, trim, position, and ascii, with examples and their outcomes, such as concatenating names and ages, reversing strings, replacing substrings, trimming spaces, finding positions, and obtaining ascii values.', 'duration': 462.423, 'highlights': ["The CONCAT function concatenates the student name with a space followed by the age of the student, creating a new column 'name_age' from the 'students' table. It returns the student ID, student name, and the concatenated column 'name_age', illustrating the use of CONCAT to merge names and ages.", "The REVERSE function reverses the characters in a string, demonstrated by reversing the names of students from the 'students' table. It showcases the reversal of characters in a string, exemplified by reversing the names of students from the 'students' table.", "The REPLACE function replaces occurrences of a substring within a string with a new substring, as shown by replacing 'vegetable' with 'fruit' in the input string 'orange is a vegetable'. It exhibits the functionality of the REPLACE function by replacing 'vegetable' with 'fruit' in the input string 'orange is a vegetable'.", "The LTRIM, RTRIM, and TRIM functions are demonstrated to remove leading, trailing, and both leading and trailing spaces from a string, with examples showcasing their effects on the string 'India'. It demonstrates the usage of LTRIM, RTRIM, and TRIM functions to eliminate leading, trailing, and both leading and trailing spaces from a string, illustrated using the example of the string 'India'.", "The POSITION function is used to find the position of the first occurrence of a substring in a string, with an example locating the position of the word 'fruit' in the string 'orange is a fruit'. It exemplifies the application of the POSITION function to find the position of the word 'fruit' in the string 'orange is a fruit'.", "The ASCII function returns the ASCII value for a specific character, demonstrated by obtaining the ASCII value of the letter 'a'. It showcases the usage of the ASCII function to obtain the ASCII value of the letter 'a', depicting its functionality."]}], 'duration': 878.28, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g3472401.jpg', 'highlights': ["The CONCAT function concatenates the student name with a space followed by the age of the student, creating a new column 'name_age' from the 'students' table. It returns the student ID, student name, and the concatenated column 'name_age', illustrating the use of CONCAT to merge names and ages.", "The demonstration includes the creation of a table called 'employee details' with columns for name, age, gender, date of join, city, and salary. A table named 'employee details' is created with specific columns for storing employee information, facilitating structured data storage.", "The function now returns the current date and time. The 'now' function returns the current date and time in MySQL, providing real-time data retrieval capabilities.", 'The chapter covers a MySQL demo with demonstrations on database viewing, table creation, data insertion, select statements, aggregate functions, WHERE clause, operators, GROUP BY, ORDER BY, and string functions like upper, lower, lcase, character length, and concat. The chapter provides a comprehensive demonstration of MySQL with a focus on database operations, SQL statements, and string functions.', "The REPLACE function replaces occurrences of a substring within a string with a new substring, as shown by replacing 'vegetable' with 'fruit' in the input string 'orange is a vegetable'. It exhibits the functionality of the REPLACE function by replacing 'vegetable' with 'fruit' in the input string 'orange is a vegetable'.", "The REVERSE function reverses the characters in a string, demonstrated by reversing the names of students from the 'students' table. It showcases the reversal of characters in a string, exemplified by reversing the names of students from the 'students' table."]}, {'end': 5121.479, 'segs': [{'end': 4435.33, 'src': 'embed', 'start': 4382.315, 'weight': 0, 'content': [{'end': 4391.542, 'text': 'First, what is groupby in SQL? So the groupby statement or clause groups records into summary rows and returns one record for each group.', 'start': 4382.315, 'duration': 9.227}, {'end': 4397.986, 'text': 'It groups the rows with the same groupby item expressions and computes aggregate functions for the resulting group.', 'start': 4392.202, 'duration': 5.784}, {'end': 4406.798, 'text': 'A groupby clause is a part of select expression In each group no two rows have the same value for the grouping column or columns.', 'start': 4398.947, 'duration': 7.851}, {'end': 4411.141, 'text': 'Now below you can see the syntax of GROUP BY.', 'start': 4407.899, 'duration': 3.242}, {'end': 4417.784, 'text': 'So first we have the SELECT statement and then followed by the column names that we want to select.', 'start': 4411.841, 'duration': 5.943}, {'end': 4428.189, 'text': 'FROM we have the table name followed by the WHERE condition and next we have the GROUP BY clause and here we include the column names.', 'start': 4418.704, 'duration': 9.485}, {'end': 4431.371, 'text': 'Finally we have the ORDER BY and the column names.', 'start': 4428.869, 'duration': 2.502}, {'end': 4435.33, 'text': 'now, here is an example of the group by clause.', 'start': 4432.287, 'duration': 3.043}], 'summary': 'Groupby in sql groups records into summary rows and returns one record for each group.', 'duration': 53.015, 'max_score': 4382.315, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g4382315.jpg'}, {'end': 4493.298, 'src': 'embed', 'start': 4455.326, 'weight': 1, 'content': [{'end': 4460.869, 'text': 'we have the city to which the employees belong to and then we have the salary in dollars.', 'start': 4455.326, 'duration': 5.543}, {'end': 4464.611, 'text': "so actually we'll be using this employees table on MySQL Workbench as well.", 'start': 4460.869, 'duration': 3.742}, {'end': 4474.212, 'text': 'so if you were to find the average salary of employees in each department, so this is how your SQL query with group by clause would look like.', 'start': 4465.828, 'duration': 8.384}, {'end': 4480.414, 'text': 'so we have selected department and then we are using an aggregate function that is AVG, which is average,', 'start': 4474.212, 'duration': 6.202}, {'end': 4487.777, 'text': 'and we have chosen the salary column and here we have given an alias name, which is average underscore salary,', 'start': 4480.414, 'duration': 7.363}, {'end': 4493.298, 'text': 'which appears in the output you can see here from employees, and we have grouped it by department.', 'start': 4487.777, 'duration': 5.521}], 'summary': 'Using sql query with group by clause to find average salary of employees in each department.', 'duration': 37.972, 'max_score': 4455.326, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g4455326.jpg'}, {'end': 4584.885, 'src': 'embed', 'start': 4552.303, 'weight': 4, 'content': [{'end': 4558.184, 'text': 'Alright, now you can check the tables that are present in sql underscore intro database.', 'start': 4552.303, 'duration': 5.881}, {'end': 4565.309, 'text': 'If I write show tables, you can see the list of tables that are already present in this database.', 'start': 4559.824, 'duration': 5.485}, {'end': 4570.933, 'text': 'To do our demo and understand group by as well as having, let me first create an employee table.', 'start': 4565.829, 'duration': 5.104}, {'end': 4576.318, 'text': "So I'll write create table employees.", 'start': 4572.134, 'duration': 4.184}, {'end': 4584.885, 'text': "Next, I'll give my column name as employee underscore ID, which is the ID for each employee.", 'start': 4577.439, 'duration': 7.446}], 'summary': 'Creating employee table with employee_id column in sql intro database', 'duration': 32.582, 'max_score': 4552.303, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g4552303.jpg'}], 'start': 4350.681, 'title': 'Sql group by and average salary calculation', 'summary': "Introduces the basics of sql group by and having statements, along with an example of the group by clause. it also explains how to find the average salary of employees in each department using sql queries with group by and aggregate functions, and demonstrates the creation and manipulation of an 'employees' table in mysql workbench.", 'chapters': [{'end': 4435.33, 'start': 4350.681, 'title': 'Sql group by and having', 'summary': 'Introduces the basics of sql group by and having statements, explaining that the group by clause groups records into summary rows, computing aggregate functions for the resulting group, and provides an example of the group by clause.', 'duration': 84.649, 'highlights': ['The groupby statement or clause groups records into summary rows and returns one record for each group, computing aggregate functions for the resulting group.', 'The groupby clause is a part of the select expression, and in each group, no two rows have the same value for the grouping column or columns.', 'The syntax of GROUP BY includes the SELECT statement, followed by the column names, FROM the table name, WHERE condition, GROUP BY clause with column names, and ORDER BY with column names.']}, {'end': 5121.479, 'start': 4435.33, 'title': 'Finding average salary by department', 'summary': "Explains how to find the average salary of employees in each department using sql queries with group by and aggregate functions, and also demonstrates the creation and manipulation of an 'employees' table in mysql workbench.", 'duration': 686.149, 'highlights': ['The chapter explains how to find the average salary of employees in each department using SQL queries with group by and aggregate functions. The transcript provides a detailed explanation of using SQL queries with group by and aggregate functions to find the average salary of employees in each department.', "The transcript demonstrates the creation and manipulation of an 'employees' table in MySQL Workbench. The speaker demonstrates the process of creating an 'employees' table in MySQL Workbench, including defining columns, inserting records, and running SQL commands to analyze the table structure and data.", 'The transcript provides examples of using group by and aggregate functions to find average age and total salary of employees in each department, as well as ordering results based on employee count in each city. The transcript includes examples of using group by and aggregate functions to find average age and total salary of employees in each department, and also demonstrates ordering results based on employee count in each city.']}], 'duration': 770.798, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g4350681.jpg', 'highlights': ['The groupby statement or clause groups records into summary rows and returns one record for each group, computing aggregate functions for the resulting group.', 'The chapter explains how to find the average salary of employees in each department using SQL queries with group by and aggregate functions.', 'The transcript provides examples of using group by and aggregate functions to find average age and total salary of employees in each department, as well as ordering results based on employee count in each city.', 'The groupby clause is a part of the select expression, and in each group, no two rows have the same value for the grouping column or columns.', "The transcript demonstrates the creation and manipulation of an 'employees' table in MySQL Workbench.", 'The syntax of GROUP BY includes the SELECT statement, followed by the column names, FROM the table name, WHERE condition, GROUP BY clause with column names, and ORDER BY with column names.']}, {'end': 6167.789, 'segs': [{'end': 5194.221, 'src': 'embed', 'start': 5121.479, 'weight': 0, 'content': [{'end': 5127.124, 'text': 'suppose we want to find the number of employees that joined the company each year.', 'start': 5121.479, 'duration': 5.645}, {'end': 5129.746, 'text': 'we can use the year function on the date of joining column.', 'start': 5127.124, 'duration': 2.622}, {'end': 5135.332, 'text': 'then we can count the employee IDs and group the result by each year.', 'start': 5130.41, 'duration': 4.922}, {'end': 5138.334, 'text': 'so let me show you how to do it.', 'start': 5135.332, 'duration': 3.002}, {'end': 5140.895, 'text': "so I'll write select.", 'start': 5138.334, 'duration': 2.561}, {'end': 5146.097, 'text': "I'm going to extract year from the date of join column.", 'start': 5140.895, 'duration': 5.202}, {'end': 5149.359, 'text': "I'll give an alias as year.", 'start': 5146.097, 'duration': 3.262}, {'end': 5170.175, 'text': "next I'll count the employee id from my table name, that is, employees, and i am going to group it by year.", 'start': 5149.359, 'duration': 20.816}, {'end': 5170.796, 'text': 'date of join.', 'start': 5170.175, 'duration': 0.621}, {'end': 5178.761, 'text': 'i give a semicolon, all right.', 'start': 5170.796, 'duration': 7.965}, {'end': 5181.712, 'text': "so let's run this great.", 'start': 5178.761, 'duration': 2.951}, {'end': 5182.753, 'text': 'you see here,', 'start': 5181.712, 'duration': 1.041}, {'end': 5194.221, 'text': 'in the result we have the year that we have extracted from the date of join column and on the right you can see the total number of employees that joined the company each year.', 'start': 5182.753, 'duration': 11.468}], 'summary': 'Using sql, we extracted the year from the date of joining column and counted the number of employees who joined the company each year.', 'duration': 72.742, 'max_score': 5121.479, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g5121479.jpg'}, {'end': 5264.104, 'src': 'embed', 'start': 5224.71, 'weight': 2, 'content': [{'end': 5236.818, 'text': "So I'll write create table sales and the sales table will have columns such as the product ID, which is going to be of integer type.", 'start': 5224.71, 'duration': 12.108}, {'end': 5240.42, 'text': 'Then we have the selling price of the product.', 'start': 5237.458, 'duration': 2.962}, {'end': 5244.323, 'text': 'Now this will be a float value.', 'start': 5241.818, 'duration': 2.505}, {'end': 5250.175, 'text': 'Then we have the quantity sold for each of the products.', 'start': 5246.087, 'duration': 4.088}, {'end': 5252.681, 'text': "So I'll write quantity.", 'start': 5250.576, 'duration': 2.105}, {'end': 5258.763, 'text': 'quantity will be of integer type.', 'start': 5255.782, 'duration': 2.981}, {'end': 5264.104, 'text': 'next we have the state in which the item was sold and state.', 'start': 5258.763, 'duration': 5.341}], 'summary': 'Creating a sales table with product id, selling price, and quantity sold in integer and float data types.', 'duration': 39.394, 'max_score': 5224.71, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g5224710.jpg'}, {'end': 5394.11, 'src': 'embed', 'start': 5360.223, 'weight': 3, 'content': [{'end': 5371.566, 'text': "okay, now suppose you want to find the revenue for both the product IDs one to one and let's say, one to three,", 'start': 5360.223, 'duration': 11.343}, {'end': 5375.227, 'text': 'since we have just two product IDs here.', 'start': 5371.566, 'duration': 3.661}, {'end': 5377.747, 'text': 'so for that you can use the select query.', 'start': 5375.227, 'duration': 2.52}, {'end': 5380.568, 'text': "so I'll write select product ID.", 'start': 5377.747, 'duration': 2.821}, {'end': 5385.868, 'text': 'and Next I want to calculate the revenue.', 'start': 5380.568, 'duration': 5.3}, {'end': 5390.369, 'text': 'So revenue is nothing but selling price multiplied by the quantity.', 'start': 5386.368, 'duration': 4.001}, {'end': 5394.11, 'text': "So I'll use the sum function to find the total revenue.", 'start': 5390.969, 'duration': 3.141}], 'summary': 'Use select query to find revenue for product ids 1 to 1 and 1 to 3 by calculating selling price multiplied by quantity using the sum function.', 'duration': 33.887, 'max_score': 5360.223, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g5360223.jpg'}, {'end': 5600.226, 'src': 'embed', 'start': 5554.261, 'weight': 4, 'content': [{'end': 5561.889, 'text': "i'll write sum S dot sell underscore price.", 'start': 5554.261, 'duration': 7.628}, {'end': 5566.953, 'text': 'now here C and S are alias names.', 'start': 5561.889, 'duration': 5.064}, {'end': 5575.579, 'text': 'so if I subtract my cost price from the selling price, that will return the profit that was generated.', 'start': 5566.953, 'duration': 8.626}, {'end': 5581.703, 'text': 'I will multiply this with S dot quantity.', 'start': 5575.579, 'duration': 6.124}, {'end': 5588.036, 'text': 'close the bracket.', 'start': 5586.175, 'duration': 1.861}, {'end': 5596.743, 'text': "I'll give an alias name as profit from sales as S.", 'start': 5588.036, 'duration': 8.707}, {'end': 5600.226, 'text': 'so here S stands for the sales table.', 'start': 5596.743, 'duration': 3.483}], 'summary': 'Calculate profit from sales by multiplying selling price and quantity.', 'duration': 45.965, 'max_score': 5554.261, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g5554261.jpg'}, {'end': 5801.615, 'src': 'embed', 'start': 5765.922, 'weight': 6, 'content': [{'end': 5775.628, 'text': 'so if you see the output, we have the different city names, and these were the cities where the count of employees was greater than 2.', 'start': 5765.922, 'duration': 9.706}, {'end': 5781.572, 'text': "all right, so let's go to our mysql workbench and implement how having works.", 'start': 5775.628, 'duration': 5.944}, {'end': 5787.547, 'text': 'so suppose, want to find those departments where the average salary is greater than 75 000.', 'start': 5781.572, 'duration': 5.975}, {'end': 5792.29, 'text': 'you can use the having clause for this.', 'start': 5787.547, 'duration': 4.743}, {'end': 5801.615, 'text': 'so let me first run my table, which is employees.', 'start': 5792.29, 'duration': 9.325}], 'summary': "Using 'having' clause to find departments with average salary > $75,000.", 'duration': 35.693, 'max_score': 5765.922, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g5765922.jpg'}, {'end': 6049.724, 'src': 'embed', 'start': 6006.375, 'weight': 7, 'content': [{'end': 6011.921, 'text': 'okay, now you can also use a where clause along with the having clause in an sql statement.', 'start': 6006.375, 'duration': 5.546}, {'end': 6017.447, 'text': 'so suppose i want to find the cities that have more than two employees apart from houston.', 'start': 6011.921, 'duration': 5.526}, {'end': 6047.823, 'text': "so I can write my query as select city comma count star as EMP count from employees, where I'll give my condition city not equal to Houston.", 'start': 6017.447, 'duration': 30.376}, {'end': 6049.724, 'text': "I'll put it in double quotes.", 'start': 6048.183, 'duration': 1.541}], 'summary': 'Sql allows using where and having clauses together for specific queries, such as finding cities with more than two employees, excluding houston.', 'duration': 43.349, 'max_score': 6006.375, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g6006375.jpg'}, {'end': 6167.789, 'src': 'embed', 'start': 6136.624, 'weight': 5, 'content': [{'end': 6146.072, 'text': "so I'll write having average salary greater than 75, 000.", 'start': 6136.624, 'duration': 9.448}, {'end': 6148.013, 'text': 'This is another way to use the having clause.', 'start': 6146.072, 'duration': 1.941}, {'end': 6149.194, 'text': "Let's run this.", 'start': 6148.553, 'duration': 0.641}, {'end': 6150.434, 'text': 'All right.', 'start': 6150.114, 'duration': 0.32}, {'end': 6159.139, 'text': 'You can see we have department sales, finance, and HR, and you can see the employee count where the average salary was greater than 75, 000.', 'start': 6150.514, 'duration': 8.625}, {'end': 6167.789, 'text': 'Okay, so let me run you from the beginning what we did in our demo.', 'start': 6159.139, 'duration': 8.65}], 'summary': "Demonstration of using sql 'having' clause to filter departments with average salary > $75,000.", 'duration': 31.165, 'max_score': 6136.624, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g6136624.jpg'}], 'start': 5121.479, 'title': 'Sql functions, table creation, and having clause', 'summary': 'Provides guidance on using sql functions to analyze employee joining count, creating a sales table, calculating revenue and profit, and utilizing the having clause with specific examples and quantifiable data.', 'chapters': [{'end': 5223.729, 'start': 5121.479, 'title': 'Employee joining count by year', 'summary': 'Demonstrates how to use sql functions to extract the year from the date of joining column, count the number of employees who joined the company each year, and group the results by year, providing specific examples and quantifiable data.', 'duration': 102.25, 'highlights': ['The chapter provides a demonstration of using the year function to extract the year from the date of joining column in SQL, allowing for the analysis of employee joining trends over time.', 'It also showcases the process of counting the employee IDs and grouping the results by each year, revealing specific quantifiable data such as the total number of employees who joined the company in each year.']}, {'end': 5674.413, 'start': 5224.71, 'title': 'Creating sales table and calculating revenue and profit', 'summary': 'Covers creating a sales table with product id, selling price, quantity, and state, inserting 9 rows of information, calculating revenue using the sum function, and finding profit by subtracting cost from selling price and multiplying by quantity.', 'duration': 449.703, 'highlights': ['Creating a sales table with product ID, selling price, quantity, and state, and inserting 9 rows of information', 'Calculating revenue using the sum function to find total revenue generated from two product IDs', 'Finding profit by subtracting cost price from selling price and multiplying by quantity']}, {'end': 6167.789, 'start': 5674.413, 'title': 'Learning sql having clause', 'summary': 'Explains the concept of the having clause in sql, with examples of queries using aggregate functions to find departments with average salary greater than $75,000 and cities with total salary greater than $200,000, as well as using both where and having clause together.', 'duration': 493.376, 'highlights': ['Using having clause to find departments with average salary greater than $75,000 The chapter demonstrates a SQL query using the having clause to find departments where the average salary is greater than $75,000, resulting in three departments: sales, finance, and HR.', 'Using having clause to find cities with total salary greater than $200,000 A SQL query utilizing the having clause is shown to find cities where the total salary is greater than $200,000, identifying Chicago, Seattle, and Houston as the cities meeting the condition.', 'Using where and having clause together to exclude specific city The chapter explains how to use both where and having clause in an SQL statement to exclude specific city (Houston) while finding cities with more than two employees, resulting in information for Chicago and Seattle only.']}], 'duration': 1046.31, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g5121479.jpg', 'highlights': ['The chapter provides a demonstration of using the year function to extract the year from the date of joining column in SQL, allowing for the analysis of employee joining trends over time.', 'It also showcases the process of counting the employee IDs and grouping the results by each year, revealing specific quantifiable data such as the total number of employees who joined the company in each year.', 'Creating a sales table with product ID, selling price, quantity, and state, and inserting 9 rows of information', 'Calculating revenue using the sum function to find total revenue generated from two product IDs', 'Finding profit by subtracting cost price from selling price and multiplying by quantity', 'Using having clause to find departments with average salary greater than $75,000 The chapter demonstrates a SQL query using the having clause to find departments where the average salary is greater than $75,000, resulting in three departments: sales, finance, and HR.', 'Using having clause to find cities with total salary greater than $200,000 A SQL query utilizing the having clause is shown to find cities where the total salary is greater than $200,000, identifying Chicago, Seattle, and Houston as the cities meeting the condition.', 'Using where and having clause together to exclude specific city The chapter explains how to use both where and having clause in an SQL statement to exclude specific city (Houston) while finding cities with more than two employees, resulting in information for Chicago and Seattle only.']}, {'end': 8088.905, 'segs': [{'end': 6242.512, 'src': 'embed', 'start': 6167.789, 'weight': 0, 'content': [{'end': 6175.178, 'text': 'so first we created a table called employee, then we inserted 20 records to this table.', 'start': 6167.789, 'duration': 7.389}, {'end': 6184.769, 'text': 'next we explored a few sql commands like distinct, then we used average and finally we started with our group by clause,', 'start': 6175.178, 'duration': 9.591}, {'end': 6192.976, 'text': 'followed by looking at how group by can be used along with another table,', 'start': 6186.569, 'duration': 6.407}, {'end': 6200.404, 'text': 'and we joined two tables that was sales and product cost table to find out the profit.', 'start': 6192.976, 'duration': 7.428}, {'end': 6210.455, 'text': 'then you learned how to use the having clause, so we explored several different questions and learned how to use having an SQL.', 'start': 6200.404, 'duration': 10.051}, {'end': 6213.617, 'text': 'In this session, we will learn about joins in SQL.', 'start': 6210.796, 'duration': 2.821}, {'end': 6218.719, 'text': 'Joins are really important when you have to deal with data that is present on multiple tables.', 'start': 6214.417, 'duration': 4.302}, {'end': 6225.903, 'text': "I'll help you understand the basics of joins and make you learn the different types of joins with hands-on demonstrations on MySQL Workbench.", 'start': 6219.84, 'duration': 6.063}, {'end': 6229.185, 'text': "So let's get started with what are joins in SQL.", 'start': 6226.703, 'duration': 2.482}, {'end': 6235.408, 'text': 'SQL join statement or command is often used to fetch data present in multiple tables.', 'start': 6231.066, 'duration': 4.342}, {'end': 6242.512, 'text': 'SQL joins are used to combine rows of data from two or more tables based on a common field or column between them.', 'start': 6236.267, 'duration': 6.245}], 'summary': 'Created employee table, inserted 20 records. explored sql commands like distinct, average, and group by. learned about joins in sql and their importance in handling data from multiple tables.', 'duration': 74.723, 'max_score': 6167.789, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g6167789.jpg'}, {'end': 6408.926, 'src': 'embed', 'start': 6378.966, 'weight': 3, 'content': [{'end': 6381.087, 'text': 'now you can see the syntax here.', 'start': 6378.966, 'duration': 2.121}, {'end': 6388.049, 'text': 'so we have the select command and then we give the list of columns from table a, which you can see.', 'start': 6381.087, 'duration': 6.962}, {'end': 6394.612, 'text': 'here is the left table, followed by the inner join keyword and then the name of the table, that is b,', 'start': 6388.049, 'duration': 6.563}, {'end': 6399.716, 'text': 'on a common key column from both the tables A and B.', 'start': 6395.912, 'duration': 3.804}, {'end': 6404.922, 'text': 'Now let me take you to the MySQL Workbench and show you how Innerjoin works in reality.', 'start': 6399.716, 'duration': 5.206}, {'end': 6408.926, 'text': "So here I'll type MySQL.", 'start': 6404.922, 'duration': 4.004}], 'summary': 'Demonstrating sql innerjoin syntax and usage in mysql.', 'duration': 29.96, 'max_score': 6378.966, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g6378966.jpg'}, {'end': 7020.755, 'src': 'embed', 'start': 6985.68, 'weight': 6, 'content': [{'end': 6988.962, 'text': 'you can join the products and product lines table.', 'start': 6985.68, 'duration': 3.282}, {'end': 6990.763, 'text': 'so let me show you how to do it.', 'start': 6988.962, 'duration': 1.801}, {'end': 6998.146, 'text': "I'll write my select statement and choose my columns as product code.", 'start': 6991.663, 'duration': 6.483}, {'end': 7005.529, 'text': "then we have product name and let's say, I want the text description.", 'start': 6998.146, 'duration': 7.383}, {'end': 7008.97, 'text': "so I'll write this column name.", 'start': 7005.529, 'duration': 3.441}, {'end': 7020.755, 'text': "okay, then I'll use from my first table, that is, products inner join product lines.", 'start': 7008.97, 'duration': 11.785}], 'summary': 'Joining products and product lines tables to select specific columns.', 'duration': 35.075, 'max_score': 6985.68, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g6985680.jpg'}, {'end': 7085.432, 'src': 'embed', 'start': 7057.894, 'weight': 7, 'content': [{'end': 7068.231, 'text': 'alright, now suppose you want to find the revenue generated from each product order and the status of the product.', 'start': 7057.894, 'duration': 10.337}, {'end': 7074.138, 'text': 'to do this task we need to join three tables, that is, orders, order details and products.', 'start': 7068.231, 'duration': 5.907}, {'end': 7079.004, 'text': 'so first let me show you what are the columns we have in these three tables.', 'start': 7074.138, 'duration': 4.866}, {'end': 7081.367, 'text': 'we have obviously seen for the products table.', 'start': 7079.004, 'duration': 2.363}, {'end': 7085.432, 'text': 'now let me show you for orders and order details table.', 'start': 7081.367, 'duration': 4.065}], 'summary': 'Joining three tables to find revenue from each product order and product status.', 'duration': 27.538, 'max_score': 7057.894, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g7057894.jpg'}, {'end': 7362.348, 'src': 'embed', 'start': 7338.923, 'weight': 9, 'content': [{'end': 7346.588, 'text': 'you can see we have all the rows from the left table, that is A, and only the matching rows from the right table, that is B.', 'start': 7338.923, 'duration': 7.665}, {'end': 7356.638, 'text': 'so you can see this overlapped region and the syntax for sql left join is something like this.', 'start': 7346.588, 'duration': 10.05}, {'end': 7362.348, 'text': 'so you have the select statement and then you give the list of columns from table a, which is your left table.', 'start': 7356.638, 'duration': 5.71}], 'summary': 'Sql left join returns all rows from a and matching rows from b.', 'duration': 23.425, 'max_score': 7338.923, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g7338923.jpg'}, {'end': 7604.34, 'src': 'embed', 'start': 7543.711, 'weight': 8, 'content': [{'end': 7546.433, 'text': 'you will find a few more null values.', 'start': 7543.711, 'duration': 2.722}, {'end': 7547.154, 'text': 'you can see here', 'start': 7546.433, 'duration': 0.721}, {'end': 7551.578, 'text': 'There are two null values here for Customer number 168 and 169 there were no orders available.', 'start': 7547.194, 'duration': 4.384}, {'end': 7563.665, 'text': "So all right now, to check those customers who haven't placed any orders, you can use the null operator.", 'start': 7551.618, 'duration': 12.047}, {'end': 7565.686, 'text': "so what i'll do is here.", 'start': 7563.665, 'duration': 2.021}, {'end': 7567.268, 'text': "i'll just continue with this.", 'start': 7565.686, 'duration': 1.582}, {'end': 7577.216, 'text': "i'll use a where clause and write where order number is null.", 'start': 7567.268, 'duration': 9.948}, {'end': 7579.378, 'text': 'now let me run this.', 'start': 7577.216, 'duration': 2.162}, {'end': 7589.695, 'text': "okay, so here you can see there are 24 customers from the table that don't have any orders in their names.", 'start': 7579.378, 'duration': 10.317}, {'end': 7592.996, 'text': 'okay, now talking about right joins.', 'start': 7589.695, 'duration': 3.301}, {'end': 7600.059, 'text': 'so SQL, right joins statement returns all the rows from the right table and only matching rows from the left table.', 'start': 7592.996, 'duration': 7.063}, {'end': 7604.34, 'text': 'so here you can see we have our left table as A and the right table as B.', 'start': 7600.059, 'duration': 4.281}], 'summary': '24 customers have not placed any orders; sql right joins return all rows from the right table and matching rows from the left table.', 'duration': 60.629, 'max_score': 7543.711, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g7543711.jpg'}, {'end': 7953.442, 'src': 'embed', 'start': 7921.532, 'weight': 11, 'content': [{'end': 7929.476, 'text': 'Alright, now, suppose you want to know who is the reporting manager for each employee, so for that you can use a self join.', 'start': 7921.532, 'duration': 7.944}, {'end': 7932.797, 'text': 'so let me show you how to join this employees table.', 'start': 7929.476, 'duration': 3.321}, {'end': 7940.199, 'text': "I'll write select and then I'm going to use a function called concat within brackets.", 'start': 7932.797, 'duration': 7.402}, {'end': 7943.92, 'text': "I'll start with my alias name, that is m dot.", 'start': 7940.199, 'duration': 3.721}, {'end': 7947.941, 'text': "then I'll write last name.", 'start': 7943.92, 'duration': 4.021}, {'end': 7953.442, 'text': "I'm going to concat last name followed by a comma.", 'start': 7947.941, 'duration': 5.501}], 'summary': 'Using self-join to find reporting manager for each employee.', 'duration': 31.91, 'max_score': 7921.532, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g7921532.jpg'}], 'start': 6167.789, 'title': 'Sql joins and table summaries', 'summary': 'Covers the basics of sql joins, including different types and their importance, and provides hands-on demonstrations on mysql workbench. it emphasizes the usage of joins to combine data from multiple tables, with a focus on the practical application of sql commands like distinct, average, group by, and having. it also explains inner joins, left joins, right joins, and self joins, illustrating their usage with practical examples and syntax. the chapter further covers the process of joining tables in sql to retrieve information such as product details, revenue generated from each product order, and the status of the products, using examples from the classic models database.', 'chapters': [{'end': 6242.512, 'start': 6167.789, 'title': 'Understanding sql joins', 'summary': 'Covers the basics of sql joins, including different types and their importance, and provides hands-on demonstrations on mysql workbench. it emphasizes the usage of joins to combine data from multiple tables, with a focus on the practical application of sql commands like distinct, average, group by, and having.', 'duration': 74.723, 'highlights': ['SQL joins combine data from multiple tables based on a common field or column between them. Emphasizes the concept of SQL joins and their purpose in combining data from different tables, facilitating efficient data retrieval and analysis.', 'Importance of joins when dealing with data present on multiple tables. Stresses the significance of joins in handling data from diverse tables, highlighting their relevance in data analysis and query processing.', 'Hands-on demonstrations on MySQL Workbench for learning different types of joins. Underlines the practical approach to learning joins through hands-on demonstrations on MySQL Workbench, enhancing the understanding and application of join concepts.']}, {'end': 6883.681, 'start': 6242.812, 'title': 'Understanding sql joins', 'summary': 'Explains the concept of inner joins in sql, illustrating with examples the process of joining tables to retrieve relevant information such as finding phone numbers of customers who ordered a specific product and identifying students in multiple sports teams using select commands and syntax in mysql workbench.', 'duration': 640.869, 'highlights': ['The chapter explains the concept of inner joins in SQL, illustrating with examples the process of joining tables to retrieve relevant information such as finding phone numbers of customers who ordered a specific product and identifying students in multiple sports teams using select commands and syntax in MySQL Workbench. Explanation of inner joins, examples of joining tables, retrieving specific information, using select commands, syntax in MySQL Workbench', 'The SQL inner join statement returns all the rows from multiple tables as long as the conditions are met, providing a visual representation and syntax explanation, and demonstrating the practical application in MySQL Workbench. Functionality of SQL inner join, visual representation, syntax explanation, practical application in MySQL Workbench', 'The chapter provides a step-by-step demonstration of creating tables, inserting data, and executing inner join queries in MySQL Workbench, showcasing the process and results. Step-by-step demonstration, creating tables, inserting data, executing queries, showcasing process and results']}, {'end': 7316.567, 'start': 6883.681, 'title': 'Sql joining tables and summarizing data', 'summary': 'Covers the process of joining tables in sql to retrieve information such as product details, revenue generated from each product order, and the status of the products, using examples from the classic models database.', 'duration': 432.886, 'highlights': ['The chapter demonstrates how to join tables like products and product lines in the classic models database to retrieve product information, such as product code, name, and description.', 'An example of joining three tables, orders, order details, and products, to calculate the revenue generated from each product order using the sum function and group by clause is explained in detail.', 'The process of debugging and correcting errors in SQL syntax while writing the join and sum function is illustrated.']}, {'end': 7579.378, 'start': 7316.567, 'title': 'Sql left join explained', 'summary': 'Explains sql left join, demonstrating how to retrieve customer names and their order ids from the customers and orders tables, including handling null values, using practical examples and syntax.', 'duration': 262.811, 'highlights': ['SQL left join returns all rows from the left table and matching rows from the right table, demonstrated with practical examples and syntax. SQL left join returns all rows from the left table and matching rows from the right table, demonstrated with practical examples and syntax.', 'Demonstrates retrieving customer names and their order IDs from the customers and orders tables using SQL left join. Demonstrates retrieving customer names and their order IDs from the customers and orders tables using SQL left join.', "Illustrates handling null values to identify customers who haven't placed any orders using the null operator. Illustrates handling null values to identify customers who haven't placed any orders using the null operator.", 'Practical demonstration of using SQL left join to access specific columns from the left and right tables. Practical demonstration of using SQL left join to access specific columns from the left and right tables.']}, {'end': 8088.905, 'start': 7579.378, 'title': 'Understanding sql right joins and self joins', 'summary': 'Explains sql right joins and self joins using two tables, customers and employees, with 24 customers having no orders, and demonstrates a self-join to find the reporting manager for each employee.', 'duration': 509.527, 'highlights': ["The chapter explains SQL right joins and self joins using two tables, customers and employees, with 24 customers having no orders. It mentions that there are 24 customers from the table that don't have any orders in their names, demonstrating the application of right joins and discussing the SQL syntax for right joins.", 'It demonstrates a self-join to find the reporting manager for each employee. The transcript illustrates the use of a self-join to find the reporting manager for each employee in the employees table, using the concat function to display the manager and employee names, and ordering the results accordingly.']}], 'duration': 1921.116, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g6167789.jpg', 'highlights': ['Emphasizes the concept of SQL joins and their purpose in combining data from different tables, facilitating efficient data retrieval and analysis.', 'Stresses the significance of joins in handling data from diverse tables, highlighting their relevance in data analysis and query processing.', 'Underlines the practical approach to learning joins through hands-on demonstrations on MySQL Workbench, enhancing the understanding and application of join concepts.', 'Explanation of inner joins, examples of joining tables, retrieving specific information, using select commands, syntax in MySQL Workbench.', 'Functionality of SQL inner join, visual representation, syntax explanation, practical application in MySQL Workbench.', 'Step-by-step demonstration, creating tables, inserting data, executing queries, showcasing process and results.', 'Demonstrates how to join tables like products and product lines in the classic models database to retrieve product information, such as product code, name, and description.', 'An example of joining three tables, orders, order details, and products, to calculate the revenue generated from each product order using the sum function and group by clause is explained in detail.', "Illustrates handling null values to identify customers who haven't placed any orders using the null operator.", 'Practical demonstration of using SQL left join to access specific columns from the left and right tables.', "Mentions that there are 24 customers from the table that don't have any orders in their names, demonstrating the application of right joins and discussing the SQL syntax for right joins.", 'Demonstrates a self-join to find the reporting manager for each employee, using the concat function to display the manager and employee names, and ordering the results accordingly.']}, {'end': 9411.212, 'segs': [{'end': 8140.819, 'src': 'embed', 'start': 8113.883, 'weight': 0, 'content': [{'end': 8120.506, 'text': "Now this statement will work on other SQL databases like Microsoft SQL Server, but it won't work on MySQL Workbench.", 'start': 8113.883, 'duration': 6.623}, {'end': 8125.289, 'text': "I'll show you the right way of using full auto join on MySQL Workbench.", 'start': 8121.007, 'duration': 4.282}, {'end': 8133.495, 'text': "So to show full outer join, I'm going to first use a left join and then we'll also use a right join.", 'start': 8127.032, 'duration': 6.463}, {'end': 8136.197, 'text': "And finally we'll use a union operator.", 'start': 8133.555, 'duration': 2.642}, {'end': 8140.819, 'text': 'So the union operator is used to combine the result set of two or more select statements.', 'start': 8136.677, 'duration': 4.142}], 'summary': 'Demonstrating full outer join in mysql workbench using left join, right join, and union operator.', 'duration': 26.936, 'max_score': 8113.883, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g8113883.jpg'}, {'end': 8310.323, 'src': 'embed', 'start': 8259.186, 'weight': 1, 'content': [{'end': 8265.25, 'text': 'then we had inserted a few Rows to each of these tables.', 'start': 8259.186, 'duration': 6.064}, {'end': 8269.21, 'text': 'then we used this table to learn about inner join.', 'start': 8265.25, 'duration': 3.96}, {'end': 8273.232, 'text': 'next We used a database called classic models.', 'start': 8269.21, 'duration': 4.022}, {'end': 8274.572, 'text': 'It had multiple tables.', 'start': 8273.451, 'duration': 1.121}, {'end': 8282.815, 'text': 'So we explored all of these tables like products that was, product lines, orders, customers and employees and learnt how to use inner join, left join,', 'start': 8274.572, 'duration': 8.243}, {'end': 8287.156, 'text': 'self join, right join, as well as full outer join.', 'start': 8282.815, 'duration': 4.341}, {'end': 8295.679, 'text': 'in this video we will learn what is a sub query and look at the different types of sub queries, and then we learn sub queries with select statement,', 'start': 8287.156, 'duration': 8.523}, {'end': 8298.6, 'text': 'followed by sub queries with insert statement.', 'start': 8295.679, 'duration': 2.921}, {'end': 8305.382, 'text': 'moving further, we will learn sub queries with the update statement and finally we look at sub queries with delete statement.', 'start': 8298.6, 'duration': 6.782}, {'end': 8310.323, 'text': 'all this we will be doing on our mysql workbench.', 'start': 8305.382, 'duration': 4.941}], 'summary': 'Explored multiple tables in classic models database, learned various types of joins, and will cover subqueries with select, insert, update, and delete statements in mysql workbench.', 'duration': 51.137, 'max_score': 8259.186, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g8259186.jpg'}, {'end': 8725.745, 'src': 'embed', 'start': 8692.107, 'weight': 3, 'content': [{'end': 8704.285, 'text': "so I'm using the AVG function to find the average salary of all the employees from my table, that is, employees.", 'start': 8692.107, 'duration': 12.178}, {'end': 8710.91, 'text': "if I give a semicolon and run this, you'll see the output.", 'start': 8704.285, 'duration': 6.625}, {'end': 8715.774, 'text': 'so we have total 12 employees in the table whose salary is less than the average salary.', 'start': 8710.91, 'duration': 4.864}, {'end': 8720.617, 'text': 'now, if you want, you can check the average salary.', 'start': 8715.774, 'duration': 4.843}, {'end': 8725.745, 'text': 'so the average salary is $75, 350.', 'start': 8720.617, 'duration': 5.128}], 'summary': 'Using avg function to find average salary of 12 employees: $75,350.', 'duration': 33.638, 'max_score': 8692.107, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g8692107.jpg'}, {'end': 9378.713, 'src': 'embed', 'start': 9351.098, 'weight': 2, 'content': [{'end': 9361.404, 'text': 'okay. so it says 18 rows affected, which means there are total 18 employees in the table out of the 20 employees whose age is greater than 27.', 'start': 9351.098, 'duration': 10.306}, {'end': 9372.149, 'text': "now, if you see, I'll write select star from employees you can see the difference in the salaries.", 'start': 9361.404, 'duration': 10.745}, {'end': 9378.713, 'text': 'if I scroll to the right, you can see these are the updated salaries okay?', 'start': 9372.149, 'duration': 6.564}], 'summary': '18 employees have salaries updated after filtering by age > 27.', 'duration': 27.615, 'max_score': 9351.098, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g9351098.jpg'}], 'start': 8088.905, 'title': 'Using subqueries in sql', 'summary': 'Covers using subqueries in sql, including usage with insert and update statements, and demonstrates creating tables, writing subqueries, and obtaining quantifiable results, such as 12 employees having a salary less than the average salary of $75,350 and updating salaries of employees based on age resulting in 18 rows affected.', 'chapters': [{'end': 8350.534, 'start': 8088.905, 'title': 'Understanding sql full outer join', 'summary': "Introduces sql full outer join, explaining its syntax, usage, and demonstration on mysql workbench, followed by a recap of the session's activities including learning about inner join, left join, self join, right join, and full outer join, and also subqueries with select, insert, update, and delete statements.", 'duration': 261.629, 'highlights': ['The SQL full outer join returns all the rows when there is a match in either left or right table. This provides a clear definition of a full outer join, indicating that it returns all rows with matching conditions in either the left or right table.', "MySQL Workbench does not support full outer join by default, but there's a way to do it. This highlights a limitation of MySQL Workbench in supporting full outer join by default, but also mentions a workaround for achieving it.", 'Demonstration of using left join, right join, and union operator to emulate full outer join on MySQL Workbench. This showcases the process of emulating full outer join using left join, right join, and union operator on MySQL Workbench, providing practical insights into achieving the desired result.', 'Recap of session activities including learning about inner join, left join, self join, right join, and full outer join, and also subqueries with select, insert, update, and delete statements. This summarizes the diverse activities covered in the session, ranging from various types of joins (inner join, left join, self join, right join, and full outer join) to subqueries with select, insert, update, and delete statements, showcasing a comprehensive learning experience.', "Definition of a subquery as a select query enclosed inside another query, with the inner query getting executed first and its result returned to the outer query for further operation. This provides a clear definition and explanation of a subquery, detailing its structure and execution flow, emphasizing the inner query's execution preceding the operation of the outer query."]}, {'end': 8538.029, 'start': 8350.534, 'title': 'Writing sequel query for maximum salary', 'summary': 'Explains how to write a sequel query to display the department and name of the employee with the maximum salary, using subqueries to find the highest salary and retrieving corresponding department and employee names from the employees table.', 'duration': 187.495, 'highlights': ["The subquery is used to select the department with the maximum salary from the employees table, resulting in the department 'sales' having the maximum salary with an employee named Joseph earning $115,000. By using a subquery to find the department with the highest salary, the result shows that the 'sales' department has the maximum salary, with Joseph earning $115,000.", "Another subquery is utilized to find the name of the employee with the maximum salary in the employees table, resulting in the output being the employee name 'Joseph' with a salary of $115,000. A subquery is employed to find the employee with the highest salary, where the result shows that 'Joseph' has the maximum salary of $115,000.", 'The explanation of how SQL queries execute subqueries, where the inner query is executed first, followed by the outer query based on the result obtained from the inner query. The sequence of execution in SQL queries is explained, detailing how the inner query is executed first, and then the outer query is executed based on the result obtained.', 'Introduction to subqueries using select, update, delete, and insert statements, emphasizing their usage with the select statement. An introduction to subqueries is provided, highlighting their usage with different SQL statements, particularly with the select statement.']}, {'end': 8725.745, 'start': 8538.029, 'title': 'Using subquery to select employees with salary less than average', 'summary': 'Demonstrates using a subquery to select employees whose salary is less than the average salary, with a demonstration on mysql workbench, showcasing the creation of tables, writing subqueries, and obtaining the output with a quantifiable result of 12 employees having a salary less than the average salary of $75,350.', 'duration': 187.716, 'highlights': ['Using subquery to select employees with a salary less than the average salary, showcasing the creation of tables and writing subqueries.', 'Demonstration on MySQL workbench with the creation of tables and using the AVG function to find the average salary of all the employees.', 'Obtaining the quantifiable result of 12 employees having a salary less than the average salary of $75,350.', 'Showing 20 rows of information in the employees table, including employee name, employee ID, age, gender, date of join, department, city, and salary.']}, {'end': 9411.212, 'start': 8725.745, 'title': 'Using subqueries in sql', 'summary': 'Covers using subqueries in sql, including using subqueries with insert and update statements, with examples such as inserting records based on a condition and updating salaries of employees based on age, resulting in 18 rows affected with salaries updated by multiplying with a factor of 0.35 for employees with age greater than 27.', 'duration': 685.467, 'highlights': ['Using subqueries with update statement to update salaries of employees based on age The chapter demonstrates using a subquery with the update statement to update the salaries of employees by multiplying it with a factor of 0.35, specifically for employees with an age greater than 27, resulting in 18 rows affected.', 'Using subqueries with insert statement to insert records based on a condition The chapter illustrates using subqueries with the insert statement to fetch records from the products table based on a condition (selling price greater than $100) and insert them into the orders table, resulting in two records inserted for products with a selling price greater than 1000.']}], 'duration': 1322.307, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g8088905.jpg', 'highlights': ['Demonstration of using left join, right join, and union operator to emulate full outer join on MySQL Workbench.', 'Recap of session activities including learning about inner join, left join, self join, right join, and full outer join, and also subqueries with select, insert, update, and delete statements.', 'Using subqueries with update statement to update salaries of employees based on age resulting in 18 rows affected.', 'Obtaining the quantifiable result of 12 employees having a salary less than the average salary of $75,350.', 'Introduction to subqueries using select, update, delete, and insert statements, emphasizing their usage with the select statement.']}, {'end': 11400.577, 'segs': [{'end': 9466.688, 'src': 'embed', 'start': 9431.665, 'weight': 0, 'content': [{'end': 9438.73, 'text': 'You write DELETE from the table name, where clause, the operator value, followed by the inner query and within brackets.', 'start': 9431.665, 'duration': 7.065}, {'end': 9450.715, 'text': "so here we are going to use the employees table and what we are going to do is we'll delete the employees whose age is greater than equal to 27.", 'start': 9439.986, 'duration': 10.729}, {'end': 9454.758, 'text': "so let's see how you can do it all right.", 'start': 9450.715, 'duration': 4.043}, {'end': 9464.846, 'text': "so i'll give my comment as delete sub query.", 'start': 9454.758, 'duration': 10.088}, {'end': 9466.688, 'text': "so we'll follow the syntax that we saw.", 'start': 9464.846, 'duration': 1.842}], 'summary': 'Demonstrating how to use delete with a subquery to delete employees aged 27 or older.', 'duration': 35.023, 'max_score': 9431.665, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g9431665.jpg'}, {'end': 10851.046, 'src': 'embed', 'start': 10812.038, 'weight': 3, 'content': [{'end': 10820.284, 'text': 'okay, so if you see the employees table, we have the salary values here.', 'start': 10812.038, 'duration': 8.246}, {'end': 10831.912, 'text': "now. in case, while updating the table, If the salary is equal to $10, 000, we'll set the new salary to $85, 000..", 'start': 10820.284, 'duration': 11.628}, {'end': 10839.119, 'text': "Else, if the salary is less than $10, 000, we'll set the salary column values to $72, 000.", 'start': 10831.912, 'duration': 7.207}, {'end': 10844.425, 'text': "So let's go ahead and run this trigger query.", 'start': 10839.119, 'duration': 5.306}, {'end': 10846.427, 'text': 'All right.', 'start': 10846.147, 'duration': 0.28}, {'end': 10851.046, 'text': 'now let me use my update command.', 'start': 10848.004, 'duration': 3.042}], 'summary': 'Updating salaries: $10,000 updated to $85,000, < $10,000 updated to $72,000.', 'duration': 39.008, 'max_score': 10812.038, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g10812038.jpg'}, {'end': 10951.696, 'src': 'embed', 'start': 10919.349, 'weight': 2, 'content': [{'end': 10921.992, 'text': "So in this example we'll create a table called salary.", 'start': 10919.349, 'duration': 2.643}, {'end': 10926.356, 'text': "We'll store the deleted record in a new table using the before delete trigger.", 'start': 10922.812, 'duration': 3.544}, {'end': 10941.251, 'text': "So first I'll give my comment as before trigger comments are always useful since they make your query more readable.", 'start': 10927.797, 'duration': 13.454}, {'end': 10947.152, 'text': 'So let me now create my salary table.', 'start': 10944.81, 'duration': 2.342}, {'end': 10951.696, 'text': "So I'll write create table salary.", 'start': 10947.192, 'duration': 4.504}], 'summary': "Creating a 'salary' table with before delete trigger for storing deleted records.", 'duration': 32.347, 'max_score': 10919.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g10919349.jpg'}, {'end': 11411.1, 'src': 'embed', 'start': 11378.223, 'weight': 1, 'content': [{'end': 11380.665, 'text': 'so we saw if the age was less than zero.', 'start': 11378.223, 'duration': 2.442}, {'end': 11382.106, 'text': 'then we set these to zero.', 'start': 11380.665, 'duration': 1.441}, {'end': 11392.675, 'text': 'We inserted a few records to the customers table and purposely we inserted some negative age records and then we converted this age records to zero.', 'start': 11383.652, 'duration': 9.023}, {'end': 11396.896, 'text': 'Then we learned how an after insert trigger works.', 'start': 11394.755, 'duration': 2.141}, {'end': 11400.577, 'text': 'So we created two tables, customers one and message.', 'start': 11397.256, 'duration': 3.321}, {'end': 11411.1, 'text': "So wherever the customer's date of birth was missing, we passed in a message saying, hi, the name of the customer, please update your date of birth.", 'start': 11401.377, 'duration': 9.723}], 'summary': 'Negative age records were converted to zero using triggers. message triggered for missing date of birth.', 'duration': 32.877, 'max_score': 11378.223, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g11378223.jpg'}], 'start': 9412.993, 'title': 'Sql triggers and sub-queries', 'summary': 'Explores sub-queries with the delete statement, resulting in the deletion of 9 records of employees aged less than or equal to 32. it also covers mysql triggers, including before insert triggers and after insert triggers, with examples and demonstrations of their impact on database records. furthermore, it explains the utilization of sql triggers for updating salary values and storing deleted records, affecting six rows and demonstrating the process of setting new salary values based on specified conditions.', 'chapters': [{'end': 9617.203, 'start': 9412.993, 'title': 'Sub-query with delete statement', 'summary': 'Explores using sub-queries with the delete statement, demonstrating the syntax and execution of a delete query using sub-query, resulting in the deletion of 9 records of employees aged less than or equal to 32, and highlights the impact on the employees table.', 'duration': 204.21, 'highlights': ['The chapter demonstrates executing a DELETE query using sub-query, resulting in the deletion of 9 records of employees aged less than or equal to 32, impacting the employees table.', 'The chapter discusses using sub-queries with the DELETE statement, showcasing the syntax and execution of the query, including the inner query and its impact on the employees table.', 'The chapter covers using subqueries with the DELETE statement, showcasing the syntax and execution of the query, including the inner query, resulting in the deletion of 9 records of employees aged less than or equal to 32.']}, {'end': 10058.323, 'start': 9617.203, 'title': 'Mysql triggers', 'summary': 'Teaches about mysql triggers, including their syntax, usage, and an example of a before insert trigger, demonstrating how it automatically changes values in the database when certain conditions are met.', 'duration': 441.12, 'highlights': ['Triggers in SQL are user-defined commands invoked automatically when an event occurs, such as inserting, updating, or deleting records in a table. Explains the fundamental concept of triggers and their automatic invocation based on specific events.', "Syntax of creating a trigger involves 'create trigger' followed by the trigger name, trigger time, trigger event, 'for each row begin', SQL statements, and 'end'. Provides a detailed breakdown of the syntax for creating triggers in MySQL, outlining the necessary components.", "Demonstrates the creation of a 'before insert' trigger in MySQL, which automatically modifies values in the 'age' column if they are less than zero when inserting customer records into the 'customers' table. Illustrates the practical implementation of a trigger, showcasing how it can enforce specific conditions on data insertion."]}, {'end': 10746.274, 'start': 10058.323, 'title': 'After insert trigger & before update trigger', 'summary': "Explains how an after insert trigger works using examples to create tables, set triggers, and insert records, leading to the generation of messages for null date of birth entries, followed by the demonstration of how before update trigger works by creating an 'employees' table and inserting records.", 'duration': 687.951, 'highlights': ["After insert trigger command is created to generate messages for null date of birth entries in the 'customers1' table. The chapter demonstrates the creation of an after insert trigger command to generate messages for null date of birth entries in the 'customers1' table, as well as the insertion of records and the display of the message table.", "Creation of 'customers1' table with columns like ID, name, email ID, and birthdate. The process of creating the 'customers1' table with columns like ID, name, email ID, and birthdate is detailed, including the data types and constraints used for each column.", "Creation of 'message' table with columns like ID, message ID, and message. The creation of the 'message' table with columns like ID, message ID, and message, along with the setting of primary keys and resolution of errors during the table creation process.", "Demonstration of the after insert trigger generating messages for null date of birth entries. The after insert trigger successfully generates messages for null date of birth entries in the 'customers1' table by inserting records with null date of birth and displaying the resulting message table.", "Demonstration of the creation and insertion of records into the 'employees' table. The chapter illustrates the creation of the 'employees' table and the insertion of multiple records into the table using an insert query.", "Creation of before update trigger command for the 'employees' table. The creation of a before update trigger command for the 'employees' table is elaborated upon, including the use of the 'before update' command and the 'begin' keyword."]}, {'end': 11400.577, 'start': 10746.274, 'title': 'Sql trigger demo: salary update and before delete', 'summary': 'Covers the creation of triggers in sql to update salary values based on conditions and utilize before delete trigger to store deleted records in a new table, affecting six rows and demonstrating the process of setting new salary values based on specified conditions and utilizing before delete trigger to store deleted records.', 'duration': 654.303, 'highlights': ["Creation of triggers to update salary values based on conditions The speaker demonstrates setting new salary values, with the condition if the salary is equal to $10,000, then the new salary is set to $85,000; if it's less than $10,000, the salary is set to $72,000.", 'Demonstration of before delete trigger to store deleted records in a new table The speaker creates a before delete trigger to store deleted records in a new table, effectively demonstrating the process by deleting a record from the salary table and finding the deleted information in the new table.', 'Update affecting six rows The update command affects six rows, showcasing the practical application of the trigger query to update multiple records based on the specified conditions.', 'Insertion of three rows of information The speaker inserts three rows of information into the salary table, demonstrating the process and verifying the successful insertion.']}], 'duration': 1987.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g9412993.jpg', 'highlights': ['The chapter demonstrates executing a DELETE query using sub-query, resulting in the deletion of 9 records of employees aged less than or equal to 32, impacting the employees table.', "Demonstrates the creation of a 'before insert' trigger in MySQL, which automatically modifies values in the 'age' column if they are less than zero when inserting customer records into the 'customers' table.", 'Demonstration of before delete trigger to store deleted records in a new table The speaker creates a before delete trigger to store deleted records in a new table, effectively demonstrating the process by deleting a record from the salary table and finding the deleted information in the new table.', "Creation of triggers to update salary values based on conditions The speaker demonstrates setting new salary values, with the condition if the salary is equal to $10,000, then the new salary is set to $85,000; if it's less than $10,000, the salary is set to $72,000."]}, {'end': 14199.017, 'segs': [{'end': 11583.42, 'src': 'embed', 'start': 11548.17, 'weight': 0, 'content': [{'end': 11550.853, 'text': 'In such a scenario, you can use a subquery.', 'start': 11548.17, 'duration': 2.683}, {'end': 11554.035, 'text': 'So let me show you how to write a subquery.', 'start': 11551.573, 'duration': 2.462}, {'end': 11556.417, 'text': "I'll write the SELECT statement.", 'start': 11555.156, 'duration': 1.261}, {'end': 11562.002, 'text': "In the SELECT statement, I'll pass my column names that I want to display.", 'start': 11557.718, 'duration': 4.284}, {'end': 11565.045, 'text': 'So the column names I want are the employee name.', 'start': 11562.322, 'duration': 2.723}, {'end': 11569.849, 'text': 'Then I want the department of the employee and the salary of the employee.', 'start': 11565.065, 'duration': 4.784}, {'end': 11575.137, 'text': 'from my table name, that is, employees.', 'start': 11570.856, 'duration': 4.281}, {'end': 11583.42, 'text': "next I'll use a where condition where my salary should be greater than the average salary of all the employees.", 'start': 11575.137, 'duration': 8.283}], 'summary': 'Demonstrating how to write a subquery in a select statement to retrieve employee name, department, and salary, filtering for salaries greater than the average.', 'duration': 35.25, 'max_score': 11548.17, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g11548170.jpg'}, {'end': 12837.72, 'src': 'embed', 'start': 12800.257, 'weight': 2, 'content': [{'end': 12806.939, 'text': "so I'll call my stored procedure that is update underscore salary.", 'start': 12800.257, 'duration': 6.682}, {'end': 12810.199, 'text': "and this time I'm going to pass in two parameters.", 'start': 12806.939, 'duration': 3.26}, {'end': 12821.221, 'text': "the first parameter will be the employee name and next, with a comma, I'll give my new salary that I want to.", 'start': 12810.199, 'duration': 11.022}, {'end': 12830.817, 'text': "so my employee name, let's say, is Mary, and the salary I want to be updated is, let's say, $80, 000.", 'start': 12821.221, 'duration': 9.596}, {'end': 12837.72, 'text': "I'll give a semicolon and I'll run it.", 'start': 12830.817, 'duration': 6.903}], 'summary': "Updating employee mary's salary to $80,000 using stored procedure.", 'duration': 37.463, 'max_score': 12800.257, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g12800257.jpg'}, {'end': 13127.384, 'src': 'embed', 'start': 13099.458, 'weight': 3, 'content': [{'end': 13104.682, 'text': 'so a trigger is a special type of stored procedure that runs automatically when an event occurs in the database server.', 'start': 13099.458, 'duration': 5.224}, {'end': 13108.319, 'text': 'there are mainly three types of triggers in SQL.', 'start': 13105.658, 'duration': 2.661}, {'end': 13110.439, 'text': 'we have the data manipulation trigger.', 'start': 13108.319, 'duration': 2.12}, {'end': 13114.12, 'text': 'we have the data definition trigger and login triggers.', 'start': 13110.439, 'duration': 3.681}, {'end': 13118.701, 'text': 'in this example we learn how to use a before insert trigger.', 'start': 13114.12, 'duration': 4.581}, {'end': 13127.384, 'text': 'so we will create a simple students table that will have the students roll number, the age, the name and the students marks.', 'start': 13118.701, 'duration': 8.683}], 'summary': 'Sql triggers are used to run stored procedures automatically based on different types of events in the database server.', 'duration': 27.926, 'max_score': 13099.458, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g13099458.jpg'}, {'end': 13507.767, 'src': 'embed', 'start': 13475.361, 'weight': 4, 'content': [{'end': 13482.267, 'text': 'our trigger automatically converted the negative marks to 50, because when we created our trigger,', 'start': 13475.361, 'duration': 6.906}, {'end': 13487.751, 'text': 'we had set our marks to 50 in case the marks were less than 0.', 'start': 13482.267, 'duration': 5.484}, {'end': 13491.474, 'text': 'so this is how a trigger works now.', 'start': 13487.751, 'duration': 3.723}, {'end': 13494.96, 'text': 'you can also drop a trigger or delete a trigger.', 'start': 13491.474, 'duration': 3.486}, {'end': 13498.922, 'text': 'you can just write drop trigger followed by the trigger name.', 'start': 13494.96, 'duration': 3.962}, {'end': 13505.106, 'text': 'in this case, our trigger name is marks underscore virik phi.', 'start': 13498.922, 'duration': 6.184}, {'end': 13507.767, 'text': 'underscore st.', 'start': 13505.106, 'duration': 2.661}], 'summary': 'A trigger automatically converts negative marks to 50, set by the trigger, and can be dropped or deleted using specific commands.', 'duration': 32.406, 'max_score': 13475.361, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g13475361.jpg'}, {'end': 13569.136, 'src': 'embed', 'start': 13536.349, 'weight': 5, 'content': [{'end': 13540.17, 'text': 'views are created by joining one or more tables.', 'start': 13536.349, 'duration': 3.821}, {'end': 13545.292, 'text': "I'll give a comment as views in SQL.", 'start': 13540.17, 'duration': 5.122}, {'end': 13554.526, 'text': 'okay, now, to learn views, i am going to use my table which is present inside classic models database.', 'start': 13545.292, 'duration': 9.234}, {'end': 13557.868, 'text': 'now, this database, as i mentioned, we had downloaded.', 'start': 13554.526, 'duration': 3.342}, {'end': 13560.25, 'text': 'we had downloaded it from the internet.', 'start': 13557.868, 'duration': 2.382}, {'end': 13565.774, 'text': 'so first of all let me write use classic models.', 'start': 13560.25, 'duration': 5.524}, {'end': 13569.136, 'text': "so i'll switch my database first.", 'start': 13565.774, 'duration': 3.362}], 'summary': 'Learning sql views by using tables from classic models database.', 'duration': 32.787, 'max_score': 13536.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g13536349.jpg'}, {'end': 14071.566, 'src': 'embed', 'start': 14042.185, 'weight': 6, 'content': [{'end': 14048.148, 'text': "you can see here we don't have the cust underscore details view anymore.", 'start': 14042.185, 'duration': 5.963}, {'end': 14052.351, 'text': 'all right now, moving to our final section in this demo.', 'start': 14048.148, 'duration': 4.203}, {'end': 14056.742, 'text': 'Here we will learn about Windows functions.', 'start': 14053.641, 'duration': 3.101}, {'end': 14064.384, 'text': 'Windows functions were incorporated in MySQL in the 8.0 version.', 'start': 14060.703, 'duration': 3.681}, {'end': 14071.566, 'text': 'So Windows function in MySQL are useful applications in solving analytical problems.', 'start': 14066.425, 'duration': 5.141}], 'summary': 'Mysql 8.0 introduced windows functions, useful for analytical problems.', 'duration': 29.381, 'max_score': 14042.185, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g14042185.jpg'}], 'start': 11401.377, 'title': 'Sql subqueries, triggers, procedures & views', 'summary': 'Covers sql subqueries, triggers, stored procedures, and views, demonstrating practical scenarios and examples for using subqueries, stored procedures, and triggers to retrieve specific data, create automated actions, and generate new perspectives on data. it includes examples of subqueries to find employees with specific salaries, using stored procedures to update and retrieve data, and creating triggers to automate actions based on data changes.', 'chapters': [{'end': 11492.641, 'start': 11401.377, 'title': 'Sql subqueries & triggers introduction', 'summary': 'Covers subqueries, triggers, stored procedures, and views in sql, using mysql workbench on windows, and explains the concept of subqueries and their usage within sql queries while demonstrating practical scenarios.', 'duration': 91.264, 'highlights': ['The chapter covers subqueries, triggers, stored procedures, and views in SQL, using MySQL workbench on Windows, and explains the concept of subqueries and their usage within SQL queries while demonstrating practical scenarios.', 'Subqueries are queries within another SQL query embedded within the where clause, from clause, or having clause, and the chapter explores various scenarios where subqueries can be used.', 'The chapter also demonstrates the practical usage of before-update and before-delete triggers in SQL, providing insights into how triggers work and their significance in database management.', 'Additionally, the chapter introduces the concept of stored procedures and views in SQL, providing a comprehensive understanding of their functionalities and practical applications.', "The video tutorial will also delve into important window functions in SQL, enhancing the viewers' knowledge and skills in database management and query optimization."]}, {'end': 11803.672, 'start': 11492.641, 'title': 'Using subqueries in sql', 'summary': "Demonstrates how to use subqueries in sql to find employees with salaries greater than the average salary and john's salary, displaying 8 and 12 employees respectively, using the employees table.", 'duration': 311.031, 'highlights': ['Using a subquery to find employees with salaries greater than the average salary The subquery successfully identifies 8 employees with salaries greater than the average salary of $75,350, from the employees table.', "Using a subquery to find employees with salaries greater than John's salary Another subquery effectively displays 12 employees with salaries greater than John's salary of $67,000, from the employees table.", 'Demonstrating the use of subqueries with two different tables The chapter explains the use of subqueries with two different tables to display information, providing a versatile approach to data retrieval in SQL.']}, {'end': 12613.325, 'start': 11803.672, 'title': 'Using subqueries and stored procedures in sql', 'summary': 'Explains the use of subqueries to retrieve specific data and the creation of stored procedures, with examples of retrieving products with price less than $100 using a subquery and creating stored procedures to find players with more than six goals and displaying top employees by salary.', 'duration': 809.653, 'highlights': ["Using subqueries to retrieve specific data The chapter demonstrates the use of subqueries to fetch products with a price less than $100 by using the 'select' statement and the 'in' operator, resulting in 83 products fulfilling the criteria.", "Creating stored procedures to find players with more than six goals The tutorial showcases the creation of a stored procedure to identify players who have scored more than six goals in a tournament by using the 'create procedure' syntax and 'call' method, resulting in the identification of two top players.", "Creating stored procedures to display top employees by salary The transcript explains the creation of a stored procedure using an in parameter to display the top employees based on their salaries, utilizing the 'create procedure' syntax and 'limit' keyword to display the top three employees with the highest salaries."]}, {'end': 13118.701, 'start': 12613.325, 'title': 'Stored procedure and triggers in sql', 'summary': 'Covers the creation of stored procedure for updating salary and counting female employees, using in and out parameters, and introduces triggers in sql, including the types of triggers and an example of using a before insert trigger.', 'duration': 505.376, 'highlights': ["Creating a stored procedure to update salary using the update command and in parameters, successfully updating salary to $80,000 for employee Mary Demonstrates the creation of a stored procedure to update salary using the 'update' command and in parameters, successfully updating the salary for employee Mary to $80,000.", 'Creating a stored procedure to count female employees using an out parameter, resulting in 3 female employees being counted Illustrates the creation of a stored procedure to count female employees using an out parameter, resulting in the counting of 3 female employees.', 'Introduction to triggers in SQL, including the explanation of a trigger as a special type of stored procedure and the three main types of triggers Introduces triggers in SQL, explaining triggers as a special type of stored procedure and the three main types of triggers: data manipulation trigger, data definition trigger, and login triggers.']}, {'end': 13507.767, 'start': 13118.701, 'title': 'Creating students table and trigger', 'summary': 'Explains the process of creating a students table with roll number, age, name, and marks, and setting a trigger to automatically assign a value of 50 to marks less than 0, resulting in 2 out of 4 records having their negative marks changed to 50.', 'duration': 389.066, 'highlights': ['Trigger automatically sets marks to 50 for negative values The trigger is designed to set the marks to 50 if they are less than 0, resulting in 2 out of 4 records having their negative marks changed to 50.', 'Creation of students table with roll number, age, name, and marks The chapter covers the creation of a students table with roll number, age, name, and marks, providing a foundation for the subsequent trigger implementation.', 'Demonstration of trigger functionality with select query The trigger functionality is demonstrated through a select query, showing how the trigger automatically changed the negative marks to 50.']}, {'end': 14199.017, 'start': 13507.767, 'title': 'Sql views and windows functions', 'summary': 'Covers the creation of views in sql, including creating basic views and joining tables to create views, along with renaming, displaying, and deleting views. additionally, it covers the usage of windows functions in mysql to find the total combined salary of employees for each department.', 'duration': 691.25, 'highlights': ['Creation of views in SQL Explains the concept of views as virtual tables that do not store data but display data from other tables, and demonstrates creating a basic view and using a select statement to display the contents.', 'Joining tables to create views Demonstrates joining two tables to create a view, specifying columns from both tables, using an inner join, and displaying the contents of the created view.', 'Renaming, displaying, and deleting views Illustrates how to rename a view using the rename statement, display all views using a command, and delete a view using the drop command.', 'Usage of Windows functions in MySQL Describes the usage of Windows functions in MySQL to find the total combined salary of employees for each department by partitioning the table and printing the total salary.']}], 'duration': 2797.64, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g11401377.jpg', 'highlights': ['Demonstrating the use of subqueries with two different tables', 'Using subqueries to retrieve specific data', 'Creating a stored procedure to update salary using the update command and in parameters, successfully updating salary to $80,000 for employee Mary', 'Introduction to triggers in SQL, including the explanation of a trigger as a special type of stored procedure and the three main types of triggers', 'Trigger automatically sets marks to 50 for negative values', 'Creation of views in SQL', 'Usage of Windows functions in MySQL']}, {'end': 17456.567, 'segs': [{'end': 14233.006, 'src': 'embed', 'start': 14200.257, 'weight': 1, 'content': [{'end': 14204.299, 'text': "Now we'll explore a function which is called row number.", 'start': 14200.257, 'duration': 4.042}, {'end': 14210.901, 'text': 'Now the row number function gives a sequential integer to every row within its partition.', 'start': 14205.799, 'duration': 5.102}, {'end': 14215.282, 'text': 'So let me show you how to use the row number function.', 'start': 14211.901, 'duration': 3.381}, {'end': 14216.783, 'text': "I'll write select.", 'start': 14216.063, 'duration': 0.72}, {'end': 14231.465, 'text': 'row underscore number function over my column would be salary.', 'start': 14220.257, 'duration': 11.208}, {'end': 14233.006, 'text': 'so I will write order by salary.', 'start': 14231.465, 'duration': 1.541}], 'summary': 'Introducing the row number function for assigning sequential integers to rows within a partition.', 'duration': 32.749, 'max_score': 14200.257, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g14200257.jpg'}, {'end': 14302.084, 'src': 'embed', 'start': 14270.517, 'weight': 2, 'content': [{'end': 14272.819, 'text': 'you can see it starts from 1 and goes up till 20.', 'start': 14270.517, 'duration': 2.302}, {'end': 14281.787, 'text': 'okay, now this row number function can be used to find duplicate values in a table.', 'start': 14272.819, 'duration': 8.968}, {'end': 14284.289, 'text': "to show that, first I'll create a table.", 'start': 14281.787, 'duration': 2.502}, {'end': 14288.052, 'text': "I'll write create table.", 'start': 14284.289, 'duration': 3.763}, {'end': 14296.759, 'text': "let's say I'll give a random name, that is demo, and let's say we have in this table the student ID, which is of type integer,", 'start': 14288.052, 'duration': 8.707}, {'end': 14302.084, 'text': 'and we have the student name which is of type worker.', 'start': 14296.759, 'duration': 5.325}], 'summary': 'Demonstrates the row number function to find duplicates in a table with student id and name.', 'duration': 31.567, 'max_score': 14270.517, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g14270517.jpg'}, {'end': 14533.066, 'src': 'embed', 'start': 14507.678, 'weight': 0, 'content': [{'end': 14515.054, 'text': 'okay, now We are going to see another Windows function that is called rank function in MySQL.', 'start': 14507.678, 'duration': 7.376}, {'end': 14518.676, 'text': 'So the rank function assigns a rank to a particular column.', 'start': 14515.854, 'duration': 2.822}, {'end': 14524.24, 'text': 'There are gaps in the sequence of rank values when two or more rows have the same rank.', 'start': 14519.777, 'duration': 4.463}, {'end': 14526.961, 'text': 'So first of all, let me create a table.', 'start': 14525, 'duration': 1.961}, {'end': 14531.204, 'text': 'And the name of the table would be a random name.', 'start': 14528.242, 'duration': 2.962}, {'end': 14533.066, 'text': "We'll give it as, let's say, demo1.", 'start': 14531.244, 'duration': 1.822}], 'summary': 'Introducing mysql rank function for assigning ranks to columns.', 'duration': 25.388, 'max_score': 14507.678, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g14507678.jpg'}, {'end': 14746.947, 'src': 'embed', 'start': 14701.825, 'weight': 4, 'content': [{'end': 14709.127, 'text': 'so this function returns the value of the specified expression with respect to the first row in the window frame.', 'start': 14701.825, 'duration': 7.302}, {'end': 14728.083, 'text': 'all right, so what I am going to do is I am going to select the employee name, the age and salary, and I will write first underscore value,', 'start': 14709.127, 'duration': 18.956}, {'end': 14746.947, 'text': 'which is my function, and pass in my employee name, and then I will write over order by my column, that is, salary descending.', 'start': 14728.083, 'duration': 18.864}], 'summary': "Function returns value of specified expression. selects employee name, age, and salary. uses 'first_value' function with employee name, ordered by salary descending.", 'duration': 45.122, 'max_score': 14701.825, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g14701825.jpg'}, {'end': 15457.752, 'src': 'embed', 'start': 15423.538, 'weight': 5, 'content': [{'end': 15426.7, 'text': 'all right, made another mistake here.', 'start': 15423.538, 'duration': 3.162}, {'end': 15429.381, 'text': 'this this should be root.', 'start': 15426.7, 'duration': 2.681}, {'end': 15435.845, 'text': 'okay, you can see here mysql database connection successful, all right.', 'start': 15429.381, 'duration': 6.464}, {'end': 15443.23, 'text': 'next, we are now going to create a database that is mysql underscore python.', 'start': 15435.845, 'duration': 7.385}, {'end': 15444.471, 'text': "so i'll give a comment.", 'start': 15443.23, 'duration': 1.241}, {'end': 15450.985, 'text': 'create mysql underscore python database again.', 'start': 15444.471, 'duration': 6.514}, {'end': 15457.752, 'text': 'to create this database, i am going to create another user defined function using the def keyword.', 'start': 15450.985, 'duration': 6.767}], 'summary': "Creating a mysql database 'mysql_python' with successful connection and user-defined function.", 'duration': 34.214, 'max_score': 15423.538, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g15423538.jpg'}, {'end': 15784.541, 'src': 'embed', 'start': 15754.363, 'weight': 7, 'content': [{'end': 15757.664, 'text': "so let's go ahead and connect to our database that we have just created.", 'start': 15754.363, 'duration': 3.301}, {'end': 15763.706, 'text': 'I will write the comment as connect to database.', 'start': 15757.664, 'duration': 6.042}, {'end': 15768.968, 'text': 'now, to connect to our database, I am again going to create a user defined function using the def keyword.', 'start': 15763.706, 'duration': 5.262}, {'end': 15777.592, 'text': 'I will write create underscore db, which is for database underscore connection,', 'start': 15768.968, 'duration': 8.624}, {'end': 15784.541, 'text': 'and the parameters it will take is the host name followed by the username.', 'start': 15777.592, 'duration': 6.949}], 'summary': 'Creating a user-defined function to connect to a newly created database.', 'duration': 30.178, 'max_score': 15754.363, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g15754363.jpg'}, {'end': 16011.732, 'src': 'embed', 'start': 15959.272, 'weight': 8, 'content': [{'end': 15970.495, 'text': "I'll give another comment saying execute SQL queries, alright.", 'start': 15959.272, 'duration': 11.223}, {'end': 15994.848, 'text': "now to execute our SQL queries I'll use another user defined function which is execute underscore query and I'll pass in the parameters as connection and query.", 'start': 15972.375, 'duration': 22.473}, {'end': 15996.469, 'text': 'give a colon.', 'start': 15994.848, 'duration': 1.621}, {'end': 16006.331, 'text': "I'm going to write cursor equal to connection dot cursor.", 'start': 15996.469, 'duration': 9.862}, {'end': 16011.732, 'text': 'now this is used to establish a connection and run sql statements.', 'start': 16006.331, 'duration': 5.401}], 'summary': 'Using user-defined function execute_query to execute sql queries, and establishing a connection with cursor.', 'duration': 52.46, 'max_score': 15959.272, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g15959272.jpg'}, {'end': 16136.005, 'src': 'embed', 'start': 16105.095, 'weight': 9, 'content': [{'end': 16111.6, 'text': "now it's time for us to create our first table inside the mysql underscore python database.", 'start': 16105.095, 'duration': 6.505}, {'end': 16116.725, 'text': "so to do that I'm going to write my create command in SQL.", 'start': 16111.6, 'duration': 5.125}, {'end': 16124.58, 'text': 'So first we are going to assign an SQL command to a Python variable using triple quotes to create a multi-line string.', 'start': 16118.257, 'duration': 6.323}, {'end': 16126.681, 'text': 'So let me show you how to do that.', 'start': 16125.14, 'duration': 1.541}, {'end': 16136.005, 'text': "I'll write my variable name which is going to be create orders table.", 'start': 16127.821, 'duration': 8.184}], 'summary': 'Creating the first table in mysql python database using sql command is demonstrated.', 'duration': 30.91, 'max_score': 16105.095, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g16105095.jpg'}, {'end': 16522.373, 'src': 'embed', 'start': 16483.818, 'weight': 10, 'content': [{'end': 16499.148, 'text': "so I'll write insert into my table name, that is, orders followed by values, and now I'll start entering my records for each of the rows.", 'start': 16483.818, 'duration': 15.33}, {'end': 16503.309, 'text': "so first I'll give 101, which is the order ID.", 'start': 16499.148, 'duration': 4.161}, {'end': 16513.971, 'text': "then I'll give the customer's name, let's say Steve, and the product he had ordered is, let's say, laptop.", 'start': 16503.309, 'duration': 10.662}, {'end': 16518.812, 'text': "then I'll give my date in which the item was ordered.", 'start': 16513.971, 'duration': 4.841}, {'end': 16522.373, 'text': "let's say it is 2018.", 'start': 16518.812, 'duration': 3.561}], 'summary': "Entering records into 'orders' table: order id 101, customer steve, product laptop, ordered in 2018.", 'duration': 38.555, 'max_score': 16483.818, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g16483818.jpg'}, {'end': 17189.368, 'src': 'embed', 'start': 17156.674, 'weight': 11, 'content': [{'end': 17162.735, 'text': 'every order that was placed after 31st of December will be displayed, if you run it.', 'start': 17156.674, 'duration': 6.061}, {'end': 17166.816, 'text': 'so you can see here there are three orders in our table which were ordered after 31st of December 2018.', 'start': 17162.735, 'duration': 4.081}, {'end': 17176.221, 'text': "now, moving ahead, Let's write a seventh query.", 'start': 17166.816, 'duration': 9.405}, {'end': 17183.765, 'text': "Now let's see how the order by clause works in SQL.", 'start': 17180.423, 'duration': 3.342}, {'end': 17189.368, 'text': 'So you can filter your results based on a particular column or sort it based on a particular column.', 'start': 17184.005, 'duration': 5.363}], 'summary': 'Orders placed after 31st december 2018 will be displayed, showing three orders in the table. explains the usage of the order by clause in sql for filtering and sorting results.', 'duration': 32.694, 'max_score': 17156.674, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g17156674.jpg'}, {'end': 17275.039, 'src': 'embed', 'start': 17241.481, 'weight': 12, 'content': [{'end': 17255.248, 'text': 'so this will ensure your top or the most expensive products appear at the top and the least expensive products appear at the bottom alright.', 'start': 17241.481, 'duration': 13.767}, {'end': 17264.132, 'text': "next let's see how you can create a data frame from the given table.", 'start': 17255.248, 'duration': 8.884}, {'end': 17271.478, 'text': 'so, as you know, using jupyter, notebook and pandas you can create data frames and work on it very easily.', 'start': 17264.132, 'duration': 7.346}, {'end': 17275.039, 'text': 'so with this table also we can create our own data frame.', 'start': 17271.478, 'duration': 3.561}], 'summary': 'Sort products by price, create data frame using jupyter and pandas.', 'duration': 33.558, 'max_score': 17241.481, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g17241481.jpg'}], 'start': 14200.257, 'title': 'Mysql database operations', 'summary': "Demonstrates the usage of row number function, rank function, and first value function in mysql, creation of mysql database and tables, and performing various sql queries. it covers the successful creation of 'mysql_python' database, 'orders' table, and data frame from the table, with specific examples and explanations.", 'chapters': [{'end': 14507.678, 'start': 14200.257, 'title': 'Using row number function', 'summary': 'Demonstrates the use of the row number function to assign sequential integers to rows, identify duplicate values in a table, and order records based on specific columns, with examples and explanations.', 'duration': 307.421, 'highlights': ['The row number function assigns sequential integers to rows based on a specified partition and order, as demonstrated by assigning row numbers to employee records based on their salaries. The row number function assigns sequential integers to rows based on a specified partition and order, demonstrated by assigning row numbers to employee records based on their salaries.', 'The row number function can be utilized to identify duplicate records in a table, as shown by using the function to find duplicate student records based on their IDs and names. The row number function can be utilized to identify duplicate records in a table, demonstrated by using the function to find duplicate student records based on their IDs and names.', 'The demonstration includes creating a table, inserting records, and using the row number function to identify and display duplicate records within the table. The demonstration includes creating a table, inserting records, and using the row number function to identify and display duplicate records within the table.']}, {'end': 15027.637, 'start': 14507.678, 'title': 'Rank and first value functions in mysql', 'summary': 'Covers the rank function in mysql, demonstrating its usage with an example table and showcasing the gaps in rank values when rows have the same rank. it also explains the first value function, displaying its application in identifying the employee with the highest salary and using partition to determine the highest salary employee in each department.', 'duration': 519.959, 'highlights': ['The rank function assigns a rank to a particular column, demonstrating rank values with an example table and showcasing the gaps in rank values when rows have the same rank. Rank function assigns rank values to a column, demonstrating gaps in rank values when rows have the same rank.', 'The first value function returns the value of the specified expression with respect to the first row in the window frame, showcasing its application in identifying the employee with the highest salary and using partition to determine the highest salary employee in each department. First value function returns the value of the specified expression with respect to the first row, showcasing its application in identifying the employee with the highest salary and using partition to determine the highest salary employee in each department.']}, {'end': 15587.033, 'start': 15027.637, 'title': 'Mysql connection and database creation', 'summary': 'Covers the process of importing necessary libraries, creating a server connection function, and creating a mysql database, demonstrating the use of mysql.connector and pandas as pd. it includes the successful creation of the mysql_python database.', 'duration': 559.396, 'highlights': ['Importing necessary libraries The demonstration involves importing mysql.connector and pandas as pd to set up the environment for the subsequent steps.', "Creating server connection function The process includes defining a user-defined function 'create_server_connection' with three parameters: host name, username, and user password, and using exception handling techniques to establish a connection to the MySQL server.", "Creating MySQL database The chapter demonstrates the creation of the mysql_python database by defining a user-defined function 'create_database' and executing the necessary query using the MySQL cursor, resulting in the successful creation of the database."]}, {'end': 16405.706, 'start': 15588.826, 'title': 'Creating mysql database and tables', 'summary': "Details the creation of a mysql database named 'mysql_python', including the functions to create the database, establish a connection, execute sql queries, and create the 'orders' table with specific column details and data types. it also highlights the successful execution of each function and the sql query.", 'duration': 816.88, 'highlights': ["Created a function to create a new MySQL database named 'mysql_python' using SQL query 'CREATE DATABASE' and executed it using Python, with a successful result. New MySQL database creation", "Defined a function to establish a connection to the created database using host name, username, user password, and database name, with successful connection printing 'mysql database connection successful' upon execution. Successful database connection", 'Implemented a function to execute SQL queries using the connection and query parameters, including error handling to print an error message if the query fails, and verified the successful execution of the function. SQL query execution', "Constructed a Python variable 'create_orders_table' containing the SQL command to create a table named 'orders' with specific column names, data types, and constraints, and executed the query using the 'execute_query' function. Table creation with specific column details"]}, {'end': 17456.567, 'start': 16405.706, 'title': 'Creating mysql database table and performing queries', 'summary': "Demonstrates the successful creation of a table called 'orders' in a mysql database, insertion of six customer records with details such as customer name, product name, order date, quantity, unit price, and phone number, and execution of sql queries including select, where, distinct, order by, and creation of a data frame from the table.", 'duration': 1050.861, 'highlights': ["Successfully created a table called 'orders' in a MySQL database and inserted six customer records with details such as customer name, product name, order date, quantity, unit price, and phone number. The speaker demonstrates the creation of a table 'orders' in a MySQL database and insertion of six customer records with details such as customer name, product name, order date, quantity, unit price, and phone number.", 'Executed SQL queries including select, where, distinct, and order by to retrieve and filter data from the table. The speaker explains the execution of SQL queries including select, where, distinct, and order by to retrieve and filter data from the table.', "Created a data frame from the 'orders' table using Jupyter Notebook and Pandas, converting a list into a data frame with columns such as order ID, customer name, product name, order date, quantity, unit price, and phone number. The speaker demonstrates the creation of a data frame from the 'orders' table using Jupyter Notebook and Pandas, converting a list into a data frame with columns such as order ID, customer name, product name, order date, quantity, unit price, and phone number."]}], 'duration': 3256.31, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g14200257.jpg', 'highlights': ['Demonstrates the usage of row number function, rank function, and first value function in mysql, creation of mysql database and tables, and performing various sql queries.', 'The row number function assigns sequential integers to rows based on a specified partition and order, demonstrated by assigning row numbers to employee records based on their salaries.', 'The demonstration includes creating a table, inserting records, and using the row number function to identify and display duplicate records within the table.', 'The rank function assigns a rank to a particular column, demonstrating rank values with an example table and showcasing the gaps in rank values when rows have the same rank.', 'The first value function returns the value of the specified expression with respect to the first row, showcasing its application in identifying the employee with the highest salary and using partition to determine the highest salary employee in each department.', "The chapter demonstrates the creation of the mysql_python database by defining a user-defined function 'create_database' and executing the necessary query using the MySQL cursor, resulting in the successful creation of the database.", 'New MySQL database creation', "Defined a function to establish a connection to the created database using host name, username, user password, and database name, with successful connection printing 'mysql database connection successful' upon execution.", 'Implemented a function to execute SQL queries using the connection and query parameters, including error handling to print an error message if the query fails, and verified the successful execution of the function.', "Constructed a Python variable 'create_orders_table' containing the SQL command to create a table named 'orders' with specific column names, data types, and constraints, and executed the query using the 'execute_query' function.", "Successfully created a table called 'orders' in a MySQL database and inserted six customer records with details such as customer name, product name, order date, quantity, unit price, and phone number.", 'Executed SQL queries including select, where, distinct, and order by to retrieve and filter data from the table.', "Created a data frame from the 'orders' table using Jupyter Notebook and Pandas, converting a list into a data frame with columns such as order ID, customer name, product name, order date, quantity, unit price, and phone number."]}, {'end': 19343.476, 'segs': [{'end': 17886.463, 'src': 'embed', 'start': 17836.876, 'weight': 0, 'content': [{'end': 17843.138, 'text': 'In this tutorial, we will learn PostgreSQL or PostgreSQL in detail with an extensive demo session.', 'start': 17836.876, 'duration': 6.262}, {'end': 17850.101, 'text': "So in today's video, we will learn what PostgreSQL is and look at the history of PostgreSQL.", 'start': 17844.259, 'duration': 5.842}, {'end': 17857.844, 'text': 'We will learn the features of PostgreSQL and jump into performing PostgreSQL commands on the SQL cell and pgAdmin.', 'start': 17851.202, 'duration': 6.642}, {'end': 17860.866, 'text': "So let's begin by understanding what is PostgreSQL.", 'start': 17858.124, 'duration': 2.742}, {'end': 17866.715, 'text': 'PostgreSQL is an open-source, object-relational database management system.', 'start': 17862.213, 'duration': 4.502}, {'end': 17870.837, 'text': 'It stores data in rows with columns as different data attributes.', 'start': 17867.375, 'duration': 3.462}, {'end': 17879.38, 'text': "According to the DB Engine's ranking, PostgreSQL is currently ranked fourth in popularity amongst hundreds of databases worldwide.", 'start': 17871.537, 'duration': 7.843}, {'end': 17883.382, 'text': 'It allows you to store, process, and retrieve data safely.', 'start': 17880.04, 'duration': 3.342}, {'end': 17886.463, 'text': 'It was developed by a worldwide team of volunteers.', 'start': 17883.962, 'duration': 2.501}], 'summary': 'Postgresql is an open-source database ranked fourth in popularity, allowing safe data storage and retrieval.', 'duration': 49.587, 'max_score': 17836.876, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g17836876.jpg'}, {'end': 17962.59, 'src': 'embed', 'start': 17934.676, 'weight': 9, 'content': [{'end': 17939.298, 'text': "So PostgreSQL is the world's most advanced open source database and is free to download.", 'start': 17934.676, 'duration': 4.622}, {'end': 17945.561, 'text': 'It is compatible as it supports multiple operating systems such as Windows, Linux and Mac OS.', 'start': 17940.199, 'duration': 5.362}, {'end': 17948.802, 'text': 'It is highly secure, robust and reliable.', 'start': 17946.501, 'duration': 2.301}, {'end': 17953.985, 'text': 'PostgreSQL supports multiple programming interfaces such as C, C++, Java and Python.', 'start': 17949.323, 'duration': 4.662}, {'end': 17957.787, 'text': 'PostgreSQL is compatible with various data types.', 'start': 17955.305, 'duration': 2.482}, {'end': 17962.59, 'text': 'It can work with primitives like integers, numeric, string and boolean.', 'start': 17958.347, 'duration': 4.243}], 'summary': 'Postgresql is an advanced open source database, compatible with multiple os, supports various programming interfaces, and works with various data types.', 'duration': 27.914, 'max_score': 17934.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g17934676.jpg'}, {'end': 18033.708, 'src': 'embed', 'start': 18005.41, 'weight': 2, 'content': [{'end': 18008.892, 'text': 'We will also explore some of the popular inbuilt mathematical and string functions.', 'start': 18005.41, 'duration': 3.482}, {'end': 18018.278, 'text': 'Finally, we will see some of the advanced concepts in Postgres SQL that is to write case statements, sub queries and user defined functions.', 'start': 18009.713, 'duration': 8.565}, {'end': 18020.44, 'text': "So let's head over to the demo now.", 'start': 18019.019, 'duration': 1.421}, {'end': 18023.202, 'text': "okay, so let's now start with our demo.", 'start': 18020.74, 'duration': 2.462}, {'end': 18027.364, 'text': "so first we'll connect to postgresql using psql cell.", 'start': 18023.202, 'duration': 4.162}, {'end': 18029.605, 'text': 'so here, under type.', 'start': 18027.364, 'duration': 2.241}, {'end': 18033.708, 'text': "here to search, i'll search for psql.", 'start': 18029.605, 'duration': 4.103}], 'summary': 'Exploring inbuilt functions and advanced concepts in postgres sql for demo.', 'duration': 28.298, 'max_score': 18005.41, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g18005410.jpg'}, {'end': 18179.073, 'src': 'embed', 'start': 18149.386, 'weight': 10, 'content': [{'end': 18150.127, 'text': 'there you go.', 'start': 18149.386, 'duration': 0.741}, {'end': 18153.39, 'text': 'it says you are now connected to database sql underscore demo.', 'start': 18150.127, 'duration': 3.263}, {'end': 18155.312, 'text': 'so here we can now create tables.', 'start': 18153.39, 'duration': 1.922}, {'end': 18160.978, 'text': 'we can perform insert operation, select operation, update, delete, alter and much more.', 'start': 18155.312, 'duration': 5.666}, {'end': 18167.108, 'text': "Now I'll show you how to connect to Postgres SQL using pgAdmin.", 'start': 18163.007, 'duration': 4.101}, {'end': 18176.072, 'text': 'So when you install the Postgres SQL database, you will get the SQL cell and along with that you also have the pgAdmin.', 'start': 18167.108, 'duration': 8.964}, {'end': 18179.073, 'text': "So I'll just search for pg.", 'start': 18176.072, 'duration': 3.001}], 'summary': 'Demonstration of connecting to sql database and performing operations, followed by introduction to connecting to postgres sql using pgadmin.', 'duration': 29.687, 'max_score': 18149.386, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g18149386.jpg'}, {'end': 18260.852, 'src': 'embed', 'start': 18228.945, 'weight': 1, 'content': [{'end': 18235.231, 'text': 'now you can see, here we have the Postgres SQL database and the test database all right.', 'start': 18228.945, 'duration': 6.286}, {'end': 18238.955, 'text': 'now we also created one more database, which was SQL demo.', 'start': 18235.231, 'duration': 3.724}, {'end': 18244.792, 'text': 'so let me show you how to work on this.', 'start': 18240.729, 'duration': 4.063}, {'end': 18247.214, 'text': 'pg admin and the query tool.', 'start': 18244.792, 'duration': 2.422}, {'end': 18255.281, 'text': "all right, so I'll right click on SQL demo and I'll select query tool.", 'start': 18247.214, 'duration': 8.067}, {'end': 18260.852, 'text': "I'll just show you how to run a few commands on the query tool.", 'start': 18257.389, 'duration': 3.463}], 'summary': 'Demonstration of using pgadmin to work with postgres sql databases, including creation of sql demo database and running commands in the query tool.', 'duration': 31.907, 'max_score': 18228.945, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g18228945.jpg'}, {'end': 18602.471, 'src': 'embed', 'start': 18573.416, 'weight': 11, 'content': [{'end': 18585.442, 'text': 'okay, so you can see we have a table called movies in the SQL demo database now and here you can check the columns that we have just added.', 'start': 18573.416, 'duration': 12.026}, {'end': 18590.745, 'text': 'so our movies table has movie ID, movie name, genre and ratings all right.', 'start': 18585.442, 'duration': 5.303}, {'end': 18599.83, 'text': 'now there is another way to create a table the previous time we created using the SQL cell.', 'start': 18590.745, 'duration': 9.085}, {'end': 18602.471, 'text': "now I'll tell you how to create a table using the PGAdmin.", 'start': 18599.83, 'duration': 2.641}], 'summary': "Table 'movies' in sql demo database contains movie id, name, genre, and ratings. it can also be created using pgadmin.", 'duration': 29.055, 'max_score': 18573.416, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g18573416.jpg'}, {'end': 18891.935, 'src': 'embed', 'start': 18862.056, 'weight': 3, 'content': [{'end': 18864.277, 'text': 'Okay So let me just scroll down.', 'start': 18862.056, 'duration': 2.221}, {'end': 18870.467, 'text': 'alright. so here you can see I have used my insert command.', 'start': 18866.266, 'duration': 4.201}, {'end': 18880.151, 'text': 'so I have written insert into the name of the table, that is movies, and we have the movie ID, the movie name, movie genre and IMDB ratings,', 'start': 18870.467, 'duration': 9.684}, {'end': 18884.932, 'text': 'and these are the records or the rows.', 'start': 18880.151, 'duration': 4.781}, {'end': 18888.334, 'text': 'so we have the first record as movie, ID 101.', 'start': 18884.932, 'duration': 3.402}, {'end': 18891.935, 'text': 'the name of the movie is a very popular movie which is vertigo.', 'start': 18888.334, 'duration': 3.601}], 'summary': 'Demonstrated insert command to add movie records with movie id, name, genre, and imdb ratings.', 'duration': 29.879, 'max_score': 18862.056, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g18862056.jpg'}, {'end': 19019.397, 'src': 'embed', 'start': 18983.431, 'weight': 12, 'content': [{'end': 18986.114, 'text': 'let me go back to my pg admin.', 'start': 18983.431, 'duration': 2.683}, {'end': 18993.066, 'text': 'okay now, first and foremost, let me tell you how to update records in a table.', 'start': 18986.114, 'duration': 6.952}, {'end': 19003.731, 'text': 'so suppose you have an existing table and by mistake you have entered some wrong values and you want to update those records later,', 'start': 18993.066, 'duration': 10.665}, {'end': 19006.332, 'text': 'you can use the update query for that.', 'start': 19003.731, 'duration': 2.601}, {'end': 19019.397, 'text': "so i am going to update my movies table and i'll set the genre of movie, id 103, which is 12 angry men, from drama to drama and crime.", 'start': 19006.332, 'duration': 13.065}], 'summary': 'Explaining how to update records in a table using an example of changing the genre of a movie with id 103 from drama to drama and crime.', 'duration': 35.966, 'max_score': 18983.431, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g18983431.jpg'}, {'end': 19113.137, 'src': 'embed', 'start': 19082.561, 'weight': 4, 'content': [{'end': 19085.684, 'text': 'so it will first look for movie id 103.', 'start': 19082.561, 'duration': 3.123}, {'end': 19089.447, 'text': 'it will locate that movie and will change the genre to drama and crime.', 'start': 19085.684, 'duration': 3.763}, {'end': 19092.648, 'text': 'so now you can see the difference.', 'start': 19090.147, 'duration': 2.501}, {'end': 19096.989, 'text': 'earlier we had 12 angry men as drama as the movie genre.', 'start': 19092.648, 'duration': 4.341}, {'end': 19104.451, 'text': 'now if i run this update statement, okay, you can see we have successfully updated one record.', 'start': 19096.989, 'duration': 7.462}, {'end': 19106.672, 'text': 'now let me run the select statement again.', 'start': 19104.451, 'duration': 2.221}, {'end': 19113.137, 'text': 'okay, so here you can see if i scroll down, there you go.', 'start': 19106.672, 'duration': 6.465}], 'summary': 'Movie id 103 had its genre changed to drama and crime, successfully updating one record.', 'duration': 30.576, 'max_score': 19082.561, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19082561.jpg'}, {'end': 19164.489, 'src': 'embed', 'start': 19133.924, 'weight': 5, 'content': [{'end': 19142.554, 'text': "that is movies where, let's say, i want to delete the movie id 108, which is the lion king.", 'start': 19133.924, 'duration': 8.63}, {'end': 19151.245, 'text': "so i'll write where movie underscore id is equal to 108.", 'start': 19142.554, 'duration': 8.691}, {'end': 19154.186, 'text': 'this is one of the ways to delete this particular movie.', 'start': 19151.245, 'duration': 2.941}, {'end': 19160.648, 'text': "or you can give, let's say where movie name is equal to the lion king.", 'start': 19154.186, 'duration': 6.462}, {'end': 19164.489, 'text': "let me select this and i'll hit execute.", 'start': 19160.648, 'duration': 3.841}], 'summary': 'Deleting movie with id 108 (the lion king) by using movie id or name.', 'duration': 30.565, 'max_score': 19133.924, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19133924.jpg'}, {'end': 19215.747, 'src': 'embed', 'start': 19193.906, 'weight': 6, 'content': [{'end': 19202.597, 'text': "let's say we want to filter only those records for which the IMDB ratings of the movies is greater than 8.7.", 'start': 19193.906, 'duration': 8.691}, {'end': 19204.058, 'text': 'so this is my updated table.', 'start': 19202.597, 'duration': 1.461}, {'end': 19211.684, 'text': 'now i want to display only those records or those movies whose imdb ratings is greater than 8.7.', 'start': 19204.058, 'duration': 7.626}, {'end': 19215.747, 'text': "so we'll display 12 angry men, which is 9.", 'start': 19211.684, 'duration': 4.063}], 'summary': "Filtering movies with imdb ratings > 8.7, displaying '12 angry men' (rating 9).", 'duration': 21.841, 'max_score': 19193.906, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19193906.jpg'}, {'end': 19317.071, 'src': 'embed', 'start': 19276.209, 'weight': 7, 'content': [{'end': 19284.692, 'text': "okay, now let's say you want to return only those movies which have IMDB ratings between 8.5 and 9.", 'start': 19276.209, 'duration': 8.483}, {'end': 19288.793, 'text': 'so for that I am going to use another operator called between, along with the where clause.', 'start': 19284.692, 'duration': 4.101}, {'end': 19292.475, 'text': 'Let me show you how to use BETWEEN with WHERE clause.', 'start': 19289.813, 'duration': 2.662}, {'end': 19299.26, 'text': 'I will write SELECT star from movies WHERE.', 'start': 19292.475, 'duration': 6.785}, {'end': 19305.145, 'text': 'my imdb underscore ratings is BETWEEN.', 'start': 19299.26, 'duration': 5.885}, {'end': 19308.727, 'text': 'I will write 8.5.', 'start': 19305.145, 'duration': 3.582}, {'end': 19317.071, 'text': 'I will give an AND operator AND 9.0.', 'start': 19308.727, 'duration': 8.344}], 'summary': 'Using the between operator to filter movies with imdb ratings between 8.5 and 9.', 'duration': 40.862, 'max_score': 19276.209, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19276209.jpg'}], 'start': 17456.567, 'title': 'Sql commands and postgresql basics', 'summary': 'Demonstrates using sql update and delete commands, with an example of updating unit price and deleting a specific record. it also covers postgresql basics, including features, history, and sql commands, emphasizing its popularity and security. additionally, it covers postgresql sql demo, advanced concepts, creating and managing tables, and updating, deleting, and filtering records with examples and quantifiable data.', 'chapters': [{'end': 17755.96, 'start': 17456.567, 'title': 'Update and delete commands in sql', 'summary': 'Demonstrates how to use the update command to change the unit price of an order, updating the third row in the table from $50 to $45, and how to delete a specific record from the table, successfully deleting the order id 105.', 'duration': 299.393, 'highlights': ['The update command is used to change the unit price of an order, updating the third row in the table from $50 to $45, demonstrating the successful execution of the query.', 'The demonstration of how to delete a specific record from the table, successfully deleting the order ID 105, verifying its absence in the table through a select query.']}, {'end': 18004.358, 'start': 17755.96, 'title': 'Postgresql: features, history, and basic commands', 'summary': "Covers the basics of postgresql, including its features, history, and basic sql commands, such as select, update, and delete, and emphasizes postgresql's popularity, security, and compatibility with various data types.", 'duration': 248.398, 'highlights': ["PostgreSQL is currently ranked fourth in popularity amongst hundreds of databases worldwide. PostgreSQL's current popularity ranking.", "PostgreSQL is the world's most advanced open source database and is free to download. Highlighting PostgreSQL as the most advanced open source database.", "It is highly secure, robust and reliable. PostgreSQL supports multiple programming interfaces such as C, C++, Java and Python. Emphasizing PostgreSQL's security, reliability, and support for multiple programming interfaces.", "The chapter covers the basics of PostgreSQL, including its features, history, and basic SQL commands, such as SELECT, UPDATE, and DELETE, and emphasizes PostgreSQL's popularity, security, and compatibility with various data types. Summarizing the overall content of the chapter."]}, {'end': 18547.418, 'start': 18005.41, 'title': 'Postgresql sql demo and advanced concepts', 'summary': 'Covers connecting to postgresql using psql cell, creating databases, using pgadmin, running sql queries, and creating a table with detailed data types and sizes. it also demonstrates the use of inbuilt mathematical and string functions and advanced concepts like case statements and sub queries.', 'duration': 542.008, 'highlights': ['Connecting to PostgreSQL using psql cell and creating a new database named SQL_demo The demo covers connecting to PostgreSQL using psql cell, creating a new database named SQL_demo, and checking the version of PostgreSQL, which is 13.2. It also displays the existing databases and then connects to the newly created database.', 'Demonstrating the use of pgAdmin and running SQL queries The demonstration includes showcasing how to connect to PostgreSQL using pgAdmin, opening the query tool, and running SQL queries to check the version of PostgreSQL and perform basic mathematical operations like multiplication and addition.', "Creating a table named 'movies' with detailed data types and sizes The transcript explains how to create a table named 'movies' with columns such as movie ID (integer), movie name (varchar with a size of 40), movie genre (varchar with a size of 30), and IMDB ratings (real data type).", 'Exploring inbuilt mathematical and string functions and advanced concepts in Postgres SQL The session covers exploring inbuilt mathematical and string functions, as well as advanced concepts in Postgres SQL such as writing case statements, sub queries, and user-defined functions.']}, {'end': 19006.332, 'start': 18548.399, 'title': 'Creating and managing tables in postgresql', 'summary': 'Demonstrates creating tables in postgresql using pgadmin, adding columns with data types and constraints, inserting records, and updating table records.', 'duration': 457.933, 'highlights': ["Creating tables using PGAdmin The presenter demonstrates creating a table called 'students' using PGAdmin, adding columns such as student roll number, student name, and gender with specified data types and constraints such as not null and primary key.", "Inserting records into the 'movies' table The process of inserting eight records into the 'movies' table is shown, including details such as movie ID, movie name, genre, and IMDB ratings, with a successful execution of the insert command.", 'Updating table records Instructions are provided for updating records in a table using the update query, enabling users to correct any entered incorrect values.']}, {'end': 19343.476, 'start': 19006.332, 'title': 'Updating, deleting, and filtering records in postgres sql', 'summary': 'Covers updating the genre of a movie, deleting records from a table, and using the where clause to filter records based on imdb ratings, with examples and quantifiable data provided.', 'duration': 337.144, 'highlights': ["Updating the genre of the movie '12 Angry Men' from 'Drama' to 'Drama and Crime' by executing an update statement, resulting in successfully updating one record. 1 record updated", "Deleting the movie 'The Lion King' (ID: 108) from the table using the delete command, resulting in the successful deletion of the record. 1 record deleted", "Filtering records based on IMDB ratings, displaying movies with ratings greater than 8.7, and using the 'where' clause to retrieve specific records. Movies with IMDB ratings greater than 8.7 displayed", "Using the 'BETWEEN' operator with the 'where' clause to display movies with IMDB ratings between 8.5 and 9.0, showcasing specific records based on a defined range of ratings. Movies with IMDB ratings between 8.5 and 9.0 displayed"]}], 'duration': 1886.909, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g17456567.jpg', 'highlights': ["The chapter covers the basics of PostgreSQL, including its features, history, and basic SQL commands, emphasizing PostgreSQL's popularity, security, and compatibility with various data types.", 'Demonstrating the use of pgAdmin and running SQL queries, including checking the version of PostgreSQL and performing basic mathematical operations like multiplication and addition.', "Creating a table named 'movies' with detailed data types and sizes, and exploring inbuilt mathematical and string functions, as well as advanced concepts in Postgres SQL.", "Inserting records into the 'movies' table, including details such as movie ID, movie name, genre, and IMDB ratings, with a successful execution of the insert command.", "Updating the genre of the movie '12 Angry Men' from 'Drama' to 'Drama and Crime' by executing an update statement, resulting in successfully updating one record.", "Deleting the movie 'The Lion King' (ID: 108) from the table using the delete command, resulting in the successful deletion of the record.", "Filtering records based on IMDB ratings, displaying movies with ratings greater than 8.7, and using the 'where' clause to retrieve specific records.", "Using the 'BETWEEN' operator with the 'where' clause to display movies with IMDB ratings between 8.5 and 9.0, showcasing specific records based on a defined range of ratings.", 'PostgreSQL is currently ranked fourth in popularity amongst hundreds of databases worldwide.', 'It is highly secure, robust and reliable, and supports multiple programming interfaces such as C, C++, Java, and Python.', 'Connecting to PostgreSQL using psql cell and creating a new database named SQL_demo, checking the version of PostgreSQL, displaying existing databases, and then connecting to the newly created database.', 'Creating tables using PGAdmin, adding columns such as student roll number, student name, and gender with specified data types and constraints such as not null and primary key.', 'Updating table records, enabling users to correct any entered incorrect values.']}, {'end': 21333.015, 'segs': [{'end': 19430.157, 'src': 'embed', 'start': 19400.749, 'weight': 1, 'content': [{'end': 19412.373, 'text': 'okay, now you can also select particular columns from the table by specifying the column names.', 'start': 19400.749, 'duration': 11.624}, {'end': 19416.594, 'text': 'now here, in all the examples that we saw just now, we are using star.', 'start': 19412.373, 'duration': 4.221}, {'end': 19420.615, 'text': 'now star represents it will select all the columns in the table.', 'start': 19416.594, 'duration': 4.021}, {'end': 19425.636, 'text': 'if you want to select specific columns in the table, you can use the column names.', 'start': 19420.615, 'duration': 5.021}, {'end': 19430.157, 'text': 'so you can specify the column names in the select statement.', 'start': 19425.636, 'duration': 4.521}], 'summary': 'Select specific columns using column names in sql.', 'duration': 29.408, 'max_score': 19400.749, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19400749.jpg'}, {'end': 19956.975, 'src': 'embed', 'start': 19921.459, 'weight': 0, 'content': [{'end': 19925.221, 'text': 'that means we have 150 employees in our table.', 'start': 19921.459, 'duration': 3.762}, {'end': 19931.946, 'text': 'okay, now we are going to use this employees table and explore some advanced SQL commands.', 'start': 19925.221, 'duration': 6.725}, {'end': 19935.929, 'text': 'now there is an operator called extinct.', 'start': 19931.946, 'duration': 3.983}, {'end': 19953.453, 'text': 'so see, if I write select address from employees, this is going to give me 150 address of all the employees.', 'start': 19935.929, 'duration': 17.524}, {'end': 19954.454, 'text': "there's some problem here.", 'start': 19953.453, 'duration': 1.001}, {'end': 19956.114, 'text': 'i did a spelling mistake.', 'start': 19954.454, 'duration': 1.66}, {'end': 19956.975, 'text': 'there should be another d.', 'start': 19956.114, 'duration': 0.861}], 'summary': '150 employees in the table, exploring advanced sql commands and addressing a spelling mistake.', 'duration': 35.516, 'max_score': 19921.459, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19921459.jpg'}, {'end': 20381.559, 'src': 'embed', 'start': 20348.068, 'weight': 2, 'content': [{'end': 20350.609, 'text': 'so it has returned 19 rows of information.', 'start': 20348.068, 'duration': 2.541}, {'end': 20357.83, 'text': 'you can see all my country names are France and the salary is less than $80, 000.', 'start': 20350.609, 'duration': 7.221}, {'end': 20366.792, 'text': 'so this is how you can use or give multiple conditions in a WHERE clause using the AND operator.', 'start': 20357.83, 'duration': 8.962}, {'end': 20375.054, 'text': "now let's say you want to use the OR operator and let's say you want to know the employees who are from country,", 'start': 20366.792, 'duration': 8.262}, {'end': 20381.559, 'text': 'Germany or the department should be sales.', 'start': 20375.054, 'duration': 6.505}], 'summary': 'Query returned 19 rows with country=france and salary<$80,000. demonstrates use of and and or operators in where clause.', 'duration': 33.491, 'max_score': 20348.068, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g20348068.jpg'}, {'end': 20668.459, 'src': 'embed', 'start': 20595.926, 'weight': 3, 'content': [{'end': 20602.172, 'text': "I'll paste it here and here.", 'start': 20595.926, 'duration': 6.246}, {'end': 20615.005, 'text': 'after descending, I am going to write fetch first three row only.', 'start': 20602.172, 'duration': 12.833}, {'end': 20620.287, 'text': 'so my fetch is going to give me the first three rows from the top.', 'start': 20615.005, 'duration': 5.282}, {'end': 20620.947, 'text': 'there you go.', 'start': 20620.287, 'duration': 0.66}, {'end': 20631.412, 'text': 'it has given us the first three rows and you can see the top three employees that have the highest salary since we ordered it in descending order of salary.', 'start': 20620.947, 'duration': 10.465}, {'end': 20638.24, 'text': 'all right, You can also use the offset along with the fetch clause.', 'start': 20631.412, 'duration': 6.828}, {'end': 20643.104, 'text': "I'll copy this again and let me paste it here.", 'start': 20639.061, 'duration': 4.043}, {'end': 20652.112, 'text': "Now, after descending, I'm going to write offset, let's say three rows.", 'start': 20645.026, 'duration': 7.086}, {'end': 20659.136, 'text': 'and fetch first five rows only.', 'start': 20654.395, 'duration': 4.741}, {'end': 20668.459, 'text': 'so what this SQL query is going to do is it will skip the first three rows of information and then it is going to display the next five rows.', 'start': 20659.136, 'duration': 9.323}], 'summary': 'Sql query displays top 3 employees by salary. offset skips first 3 rows, displays next 5.', 'duration': 72.533, 'max_score': 20595.926, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g20595926.jpg'}, {'end': 20723.107, 'src': 'embed', 'start': 20697.193, 'weight': 5, 'content': [{'end': 20701.876, 'text': 'you forgot the full name of an employee, but you remember the few initials.', 'start': 20697.193, 'duration': 4.683}, {'end': 20707.379, 'text': 'so you can use the like operator to get an idea as to which employee name it is.', 'start': 20701.876, 'duration': 5.503}, {'end': 20714.545, 'text': "now let's explore some examples to learn how the like operator works in Postgres SQL.", 'start': 20708.564, 'duration': 5.981}, {'end': 20721.406, 'text': 'so suppose you want to know the employees whose name starts with A, so for that you can use the like operator.', 'start': 20714.545, 'duration': 6.861}, {'end': 20723.107, 'text': 'let me show you how to do it.', 'start': 20721.406, 'duration': 1.701}], 'summary': 'Using the like operator in postgres sql to search for employees by initials.', 'duration': 25.914, 'max_score': 20697.193, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g20697193.jpg'}, {'end': 20994.319, 'src': 'embed', 'start': 20960.827, 'weight': 6, 'content': [{'end': 20965.07, 'text': "so we'll explore a few mathematical functions now.", 'start': 20960.827, 'duration': 4.243}, {'end': 20969.394, 'text': "so let's say you want to find the total sum of salary for all the employees.", 'start': 20965.07, 'duration': 4.324}, {'end': 20974.237, 'text': 'so for that you can use the sum function that is available in SQL.', 'start': 20969.394, 'duration': 4.843}, {'end': 20988.156, 'text': "so I'll write sum and inside the sum function I'll give my column name, that is, salary, from my table name, that is, employees.", 'start': 20974.237, 'duration': 13.919}, {'end': 20989.417, 'text': "let's see the result.", 'start': 20988.156, 'duration': 1.261}, {'end': 20993.818, 'text': 'this will return one unique value.', 'start': 20989.417, 'duration': 4.401}, {'end': 20994.319, 'text': 'there you go.', 'start': 20993.818, 'duration': 0.501}], 'summary': 'Using the sql sum function to calculate total salary for all employees.', 'duration': 33.492, 'max_score': 20960.827, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g20960827.jpg'}, {'end': 21051.091, 'src': 'embed', 'start': 21018.051, 'weight': 7, 'content': [{'end': 21028.915, 'text': 'so since we are doing an operation of summing the salary column, we can give an alias to this operation by using the as keyword.', 'start': 21018.051, 'duration': 10.864}, {'end': 21039.307, 'text': "so if I write sum of salary as, let's say, total salary, then this becomes my output column.", 'start': 21028.915, 'duration': 10.392}, {'end': 21044.549, 'text': 'you can see the difference if I run this.', 'start': 21039.307, 'duration': 5.242}, {'end': 21048.03, 'text': 'okay, you can see now in the output we have the total salary.', 'start': 21044.549, 'duration': 3.481}, {'end': 21051.091, 'text': 'now this is much more readable than the previous one.', 'start': 21048.03, 'duration': 3.061}], 'summary': 'Summing the salary column and providing an alias makes the output more readable.', 'duration': 33.04, 'max_score': 21018.051, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21018051.jpg'}, {'end': 21119.812, 'src': 'embed', 'start': 21085.13, 'weight': 8, 'content': [{'end': 21088.113, 'text': 'you can see the average salary for all the employees.', 'start': 21085.13, 'duration': 2.983}, {'end': 21089.835, 'text': "it's around 81 000.", 'start': 21088.113, 'duration': 1.722}, {'end': 21098.983, 'text': 'okay, now there are two more important functions that sql provides us, which is max and minimum.', 'start': 21089.835, 'duration': 9.148}, {'end': 21112.391, 'text': "so if i write select maximum or max, which is the function name of salary, as, let's say, instead of total, I will write maximum.", 'start': 21098.983, 'duration': 13.408}, {'end': 21119.812, 'text': 'so this will return me the maximum salary of the employee.', 'start': 21112.391, 'duration': 7.421}], 'summary': 'Average salary is $81,000, sql provides functions for max and min salary.', 'duration': 34.682, 'max_score': 21085.13, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21085130.jpg'}], 'start': 19343.476, 'title': 'Sql operations and functions', 'summary': 'Covers sql operations like displaying movies by genre, using select statement for data retrieval, exploring sql commands and table alteration, sql operators and clauses, pattern matching with like operator, and sql functions with practical examples and quantifiable data.', 'chapters': [{'end': 19400.749, 'start': 19343.476, 'title': 'Displaying movies by genre', 'summary': "Demonstrates how to display movies with the genre 'action' using sql, showcasing the process of writing a query to filter and retrieve specific data from a table, with an example of displaying movies with the 'action' genre resulting in 'the dark knight' as the output.", 'duration': 57.273, 'highlights': ["Writing a SQL query to filter and retrieve specific data from a table, such as displaying movies with the genre 'action'.", "Example of querying the movies table for the genre 'action', resulting in 'The Dark Knight' as the output."]}, {'end': 19864.956, 'start': 19400.749, 'title': 'Using select statement in sql', 'summary': "Covers using the select statement in sql to display specific columns from a table, applying where clause with conditions such as imdb ratings less than 9.0, using 'in' operator to select records with imdb ratings 8.7 or 9.0, creating an employee table with columns like employee id, name, email, gender, department, address, and salary, and importing csv data into postgres sql.", 'duration': 464.207, 'highlights': ["Using 'in' operator to select records with IMDB ratings 8.7 or 9.0 The in operator is demonstrated to select all the columns from the movies table where the IMDB ratings are in the range of 8.7 or 9.0.", 'Creating an employee table with columns like employee ID, name, email, gender, department, address, and salary The process of creating an employee table with specified columns such as employee ID, name, email, gender, department, address, and salary is explained.', 'Using select statement to display specific columns from a table The usage of the select statement to display specific columns like movie name and movie genre from the table is illustrated.', "Importing CSV data into Postgres SQL The process of importing CSV data from the 'employee data.csv' file into Postgres SQL is demonstrated."]}, {'end': 20280.122, 'start': 19864.956, 'title': 'Exploring sql commands and table alteration', 'summary': "Demonstrates the import and verification of 150 rows of data, exploration of advanced sql commands such as 'distinct' and 'is null' operators, and the usage of 'order by' clause to sort data in ascending and descending order based on salary. additionally, it showcases the alteration of a column name from 'address' to 'country' using the 'alter table' command.", 'duration': 415.166, 'highlights': ['The chapter demonstrates the import and verification of 150 rows of data. The speaker imports and verifies the successful insertion of 150 rows of data into the employees table.', "Exploration of advanced SQL commands such as 'distinct' and 'is null' operators. The chapter explores the usage of the 'distinct' keyword to display unique country names and the 'is null' operator to display null values, showcasing the presence of 6 unique country names and 16 rows with null email IDs.", "Usage of 'order by' clause to sort data in ascending and descending order based on salary. The speaker demonstrates the usage of the 'order by' clause to sort employees based on their salary, showcasing the default ascending order and the display of salaries in descending order using the 'DESC' keyword.", "Alteration of column name from 'address' to 'country' using the 'alter table' command. The chapter showcases the usage of the 'alter table' command to rename the 'address' column to 'country', successfully changing the column name in the employees table."]}, {'end': 20668.459, 'start': 20281.244, 'title': 'Sql operators and clauses', 'summary': 'Explains how to use the and and or operators in sql to apply multiple conditions using the where clause, with practical examples resulting in 19 and 23 rows of information, and also demonstrates the usage of the limit, offset, and fetch clauses.', 'duration': 387.215, 'highlights': ['The AND operator is used to apply multiple conditions in the WHERE clause, resulting in 19 rows of information where the country is France and the salary is less than $80,000. The chapter demonstrates using the AND operator to select employees from France with a salary less than $80,000, resulting in 19 rows of information.', 'The OR operator is utilized to retrieve information where one of the conditions is true, resulting in 23 rows of data for employees from Germany or with the department as sales. The usage of the OR operator returns 23 rows of information for employees from Germany or with the department as sales, showcasing how one of the conditions being true is sufficient.', 'The explanation of the LIMIT clause showcases how to display the top five employees with the highest salary, resulting in the display of the top five employees. The demonstration of the LIMIT clause effectively displays the top five employees with the highest salary.', 'The usage of OFFSET in combination with the LIMIT clause is illustrated, showcasing how to skip the first three rows and display the next five rows of information. The chapter effectively illustrates the usage of OFFSET in combination with the LIMIT clause, skipping the first three rows and displaying the next five rows of information.', 'The FETCH clause is exemplified to display the top three employees with the highest salary, effectively retrieving and displaying the top three employees. The chapter provides a clear demonstration of using the FETCH clause to retrieve and display the top three employees with the highest salary.']}, {'end': 20960.827, 'start': 20668.459, 'title': 'Pattern matching with like operator in postgresql', 'summary': 'Explains the usage of the like operator in postgresql for pattern matching, with examples of finding employees whose names start with specific letters, end with specific letters, contain specific letters, and have specific letters at specific positions, demonstrating the functionality with quantifiable data such as the number of employees matching each pattern.', 'duration': 292.368, 'highlights': ['The like operator in PostgreSQL is used for pattern matching, such as finding employees whose names start with a specific letter, end with a specific letter, contain specific letters, or have specific letters at specific positions.', "Examples of using the like operator to find employees whose names start with 'A', 'S', end with 'D', contain 'ISH', or have 'U' as the second letter are demonstrated, showing quantifiable data of the number of employees matching each pattern.", "The usage of underscore (_) with the like operator is demonstrated to find employees whose names have 'U' as the second letter, with quantifiable data showing the number of employees matching this pattern.", "An error in the table name 'employee' is corrected to 'employees', emphasizing the importance of accurate table names in SQL queries."]}, {'end': 21333.015, 'start': 20960.827, 'title': 'Sql functions and operations', 'summary': 'Covers the usage of sql functions like sum, avg, max, min, and count to calculate total salary, average salary, maximum salary, minimum salary, and count of departments, showcasing examples and results with notable figures for each function.', 'duration': 372.188, 'highlights': ['The sum function in SQL is used to find the total sum of salary for all employees, returning one unique value. The sum function in SQL is demonstrated to find the total sum of salary for all employees, returning one unique value.', "Using an alias with the 'as' keyword in SQL helps in making the output columns more readable, as demonstrated with the sum function and its alias 'total salary'. The 'as' keyword in SQL is used to create an alias for the output columns, making them more readable, as demonstrated with the sum function and its alias 'total salary'.", 'The AVG function in SQL is used to find the average salary for all employees, with the example showcasing an average salary of around 81,000. The AVG function in SQL is demonstrated to find the average salary for all employees, showcasing an average salary of around 81,000.', 'SQL provides the functions MAX and MIN to find the maximum and minimum salary of the employees, with examples showcasing the highest salary as $1,19,616 and the lowest salary as $45,685. SQL provides the functions MAX and MIN to find the maximum and minimum salary of the employees, showcasing the highest salary as $1,19,616 and the lowest salary as $45,685.', 'The count function in SQL is used to find the total number of departments, returning 12 departments in the example. The count function in SQL is demonstrated to find the total number of departments, returning 12 departments in the example.']}], 'duration': 1989.539, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g19343476.jpg', 'highlights': ["Exploration of advanced SQL commands such as 'distinct' and 'is null' operators.", 'The usage of the select statement to display specific columns from a table.', 'The AND operator is used to apply multiple conditions in the WHERE clause, resulting in 19 rows of information where the country is France and the salary is less than $80,000.', 'The usage of OFFSET in combination with the LIMIT clause is illustrated, showcasing how to skip the first three rows and display the next five rows of information.', 'The FETCH clause is exemplified to display the top three employees with the highest salary, effectively retrieving and displaying the top three employees.', 'The like operator in PostgreSQL is used for pattern matching, such as finding employees whose names start with a specific letter, end with a specific letter, contain specific letters, or have specific letters at specific positions.', 'The sum function in SQL is used to find the total sum of salary for all employees, returning one unique value.', "Using an alias with the 'as' keyword in SQL helps in making the output columns more readable, as demonstrated with the sum function and its alias 'total salary'.", 'SQL provides the functions MAX and MIN to find the maximum and minimum salary of the employees, with examples showcasing the highest salary as $1,19,616 and the lowest salary as $45,685.']}, {'end': 22631.085, 'segs': [{'end': 21387.295, 'src': 'embed', 'start': 21361.495, 'weight': 0, 'content': [{'end': 21367.458, 'text': 'for example, you can find the average salary of employees in each country or city or department.', 'start': 21361.495, 'duration': 5.963}, {'end': 21373.824, 'text': 'So the group by clause is used in collaboration with the select statement to arrange identical data into groups.', 'start': 21368.499, 'duration': 5.325}, {'end': 21382.911, 'text': 'So suppose you want to find the average salary of the employees based on countries, you can use the group by clause.', 'start': 21374.584, 'duration': 8.327}, {'end': 21384.733, 'text': 'So let me show you how to do it.', 'start': 21383.472, 'duration': 1.261}, {'end': 21387.295, 'text': "I'll write select.", 'start': 21385.954, 'duration': 1.341}], 'summary': 'Using sql group by clause to find average salary by country, city, or department.', 'duration': 25.8, 'max_score': 21361.495, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21361495.jpg'}, {'end': 21555.996, 'src': 'embed', 'start': 21517.862, 'weight': 1, 'content': [{'end': 21532.826, 'text': "since i want to find the maximum salary for male and female employees, i'll give an alias name as maximum underscore salary from my table, that is,", 'start': 21517.862, 'duration': 14.964}, {'end': 21536.987, 'text': 'employees. group by.', 'start': 21532.826, 'duration': 4.161}, {'end': 21542.749, 'text': "i'll write gender okay, so let's run this.", 'start': 21536.987, 'duration': 5.762}, {'end': 21547.011, 'text': 'there you go.', 'start': 21546.371, 'duration': 0.64}, {'end': 21548.592, 'text': 'you can see.', 'start': 21547.011, 'duration': 1.581}, {'end': 21555.996, 'text': 'so one of the female employees had a highest salary of one lakh nineteen thousand six hundred and sixteen dollars,', 'start': 21548.592, 'duration': 7.404}], 'summary': 'Found max salary for male and female employees, female max salary: $119,616.', 'duration': 38.134, 'max_score': 21517.862, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21517862.jpg'}, {'end': 21611.846, 'src': 'embed', 'start': 21577.78, 'weight': 2, 'content': [{'end': 21583.985, 'text': "so i'll first select my country column and then i'm going to use the count function.", 'start': 21577.78, 'duration': 6.205}, {'end': 21592.952, 'text': "i'll write count, emp, underscore id from my table name, that is, employees.", 'start': 21583.985, 'duration': 8.967}, {'end': 21598.156, 'text': "I'm going to group it by country.", 'start': 21592.952, 'duration': 5.204}, {'end': 21604.18, 'text': 'so this query will give me the total number of employees from each country.', 'start': 21598.156, 'duration': 6.024}, {'end': 21608.243, 'text': 'you can see here Israel there are four employees.', 'start': 21604.18, 'duration': 4.063}, {'end': 21610.184, 'text': 'in Australia there are four employees.', 'start': 21608.243, 'duration': 1.941}, {'end': 21611.846, 'text': 'in Russia we have 80 employees.', 'start': 21610.184, 'duration': 1.662}], 'summary': 'Using sql, counted total employees by country: israel - 4, australia - 4, russia - 80.', 'duration': 34.066, 'max_score': 21577.78, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21577780.jpg'}, {'end': 21663.058, 'src': 'embed', 'start': 21636.086, 'weight': 3, 'content': [{'end': 21641.148, 'text': 'the having clause is used with the group by clause to return those rows that meet a condition.', 'start': 21636.086, 'duration': 5.062}, {'end': 21646.99, 'text': 'so suppose you want to find the countries in which the average salary is greater than eighty thousand dollars.', 'start': 21641.148, 'duration': 5.842}, {'end': 21649.932, 'text': 'so you can use the group by clause and the having clause to get the result.', 'start': 21646.99, 'duration': 2.942}, {'end': 21658.317, 'text': "so i'll write my select statement as select country comma.", 'start': 21651.255, 'duration': 7.062}, {'end': 21663.058, 'text': "i want the average salary, so i'll write avg of salary.", 'start': 21658.317, 'duration': 4.741}], 'summary': 'Using the having clause with group by to find countries with average salary > $80,000.', 'duration': 26.972, 'max_score': 21636.086, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21636086.jpg'}, {'end': 21798.617, 'src': 'embed', 'start': 21766.126, 'weight': 4, 'content': [{'end': 21770.787, 'text': "I'll write group by country.", 'start': 21766.126, 'duration': 4.661}, {'end': 21780.469, 'text': "next I'll write having count of employee ID less than 30.", 'start': 21770.787, 'duration': 9.682}, {'end': 21786.11, 'text': 'so this will return me the countries in which there are less than 30 employees.', 'start': 21780.469, 'duration': 5.641}, {'end': 21786.69, 'text': "let's run it.", 'start': 21786.11, 'duration': 0.58}, {'end': 21794.396, 'text': 'you can see here Israel, Australia, United States and Germany are the countries in which there are less than 30 employees.', 'start': 21788.015, 'duration': 6.381}, {'end': 21798.617, 'text': 'okay, now, if you want, you can use the order by clause as well.', 'start': 21794.396, 'duration': 4.221}], 'summary': 'Grouped by country, showing countries with less than 30 employees: israel, australia, united states, germany.', 'duration': 32.491, 'max_score': 21766.126, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21766126.jpg'}, {'end': 22124.529, 'src': 'embed', 'start': 22088.457, 'weight': 5, 'content': [{'end': 22091.859, 'text': 'After this, we need to give our table name from employees.', 'start': 22088.457, 'duration': 3.402}, {'end': 22103.917, 'text': "I'll order it by salary descending.", 'start': 22095.182, 'duration': 8.735}, {'end': 22108.94, 'text': "okay. so what I'm going to do here is I'll first select department,", 'start': 22103.917, 'duration': 5.023}, {'end': 22117.946, 'text': "country and salary column from my employees table and then I'm creating a new column that is salary range and I'm specifying the range.", 'start': 22108.94, 'duration': 9.006}, {'end': 22124.529, 'text': 'so I have three conditions here for low salary, for medium salary and high salary.', 'start': 22117.946, 'duration': 6.583}], 'summary': "Creating a new column 'salary range' with three conditions for low, medium, and high salary based on the employees' table.", 'duration': 36.072, 'max_score': 22088.457, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22088457.jpg'}, {'end': 22194.694, 'src': 'embed', 'start': 22172.283, 'weight': 6, 'content': [{'end': 22181.524, 'text': 'so suppose we want to find the employee name, department, country and salary of those employees whose salary is greater than the average salary.', 'start': 22172.283, 'duration': 9.241}, {'end': 22183.905, 'text': 'so in such cases you can use sub queries.', 'start': 22181.524, 'duration': 2.381}, {'end': 22187.328, 'text': 'now let me show you how to write a query inside another query.', 'start': 22183.905, 'duration': 3.423}, {'end': 22190.15, 'text': 'first I will write the select statement.', 'start': 22187.328, 'duration': 2.822}, {'end': 22194.694, 'text': 'I am going to select the employee name comma.', 'start': 22190.15, 'duration': 4.544}], 'summary': 'Using subqueries to find employees with salary > average.', 'duration': 22.411, 'max_score': 22172.283, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22172283.jpg'}, {'end': 22410.815, 'src': 'embed', 'start': 22342.13, 'weight': 7, 'content': [{'end': 22346.771, 'text': 'so there is a function called abs, which is used to find the absolute of a value.', 'start': 22342.13, 'duration': 4.641}, {'end': 22359.975, 'text': "so if i write select abs of, let's say, minus 100, it is going to return me positive 100 or just 100, because, as you know,", 'start': 22346.771, 'duration': 13.204}, {'end': 22365.556, 'text': 'the absolute of any value will remove the negative sign involved in that value.', 'start': 22359.975, 'duration': 5.581}, {'end': 22366.137, 'text': 'there you go.', 'start': 22365.556, 'duration': 0.581}, {'end': 22368.777, 'text': 'so our original input was minus 100.', 'start': 22366.137, 'duration': 2.64}, {'end': 22371.983, 'text': 'the absolute of minus 100 is plus 100.', 'start': 22368.777, 'duration': 3.206}, {'end': 22377.125, 'text': "next let's see another function that is called greatest.", 'start': 22371.983, 'duration': 5.142}, {'end': 22384.369, 'text': 'so the greatest function in postgresql will return the greatest number in a range of numbers.', 'start': 22377.125, 'duration': 7.244}, {'end': 22393.112, 'text': 'so suppose i write select greatest inside the greatest function.', 'start': 22384.369, 'duration': 8.743}, {'end': 22394.813, 'text': "i'll pass in a few numbers.", 'start': 22393.112, 'duration': 1.701}, {'end': 22396.574, 'text': "let's say two.", 'start': 22394.813, 'duration': 1.761}, {'end': 22398.495, 'text': "i'm just randomly passing a few numbers.", 'start': 22396.574, 'duration': 1.921}, {'end': 22408.074, 'text': "let's say 490, let's say 56.5 and let's say 70.", 'start': 22400.529, 'duration': 7.545}, {'end': 22409.374, 'text': "I'll give a semicolon.", 'start': 22408.074, 'duration': 1.3}, {'end': 22410.815, 'text': 'let me run this.', 'start': 22409.374, 'duration': 1.441}], 'summary': 'The abs function returns the absolute value, e.g., abs(-100) = 100. the greatest function in postgresql finds the largest number in a range, e.g., greatest(490, 56.5, 70) = 490.', 'duration': 68.685, 'max_score': 22342.13, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22342130.jpg'}, {'end': 22500.276, 'src': 'embed', 'start': 22461.666, 'weight': 9, 'content': [{'end': 22466.129, 'text': "let's say 54, divided by 10.", 'start': 22461.666, 'duration': 4.463}, {'end': 22471.999, 'text': 'as you can guess, the remainder is 4, and so is our result.', 'start': 22466.129, 'duration': 5.87}, {'end': 22475.241, 'text': 'you can see, it has written the remainder 54, divided by 10.', 'start': 22471.999, 'duration': 3.242}, {'end': 22476.922, 'text': 'the remainder is 4..', 'start': 22475.241, 'duration': 1.681}, {'end': 22484.528, 'text': "all right, i scroll down now let's see how to use the power function.", 'start': 22476.922, 'duration': 7.606}, {'end': 22489.772, 'text': "so i'll write select power.", 'start': 22484.528, 'duration': 5.244}, {'end': 22495.494, 'text': "let's say i want to know power 2, comma 3, which is 2 cube.", 'start': 22489.772, 'duration': 5.722}, {'end': 22497.215, 'text': 'that is 8.', 'start': 22495.494, 'duration': 1.721}, {'end': 22499.736, 'text': 'let me just run this.', 'start': 22497.215, 'duration': 2.521}, {'end': 22500.276, 'text': 'there you go.', 'start': 22499.736, 'duration': 0.54}], 'summary': 'Demonstrating remainder calculation and power function usage with an example.', 'duration': 38.61, 'max_score': 22461.666, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22461666.jpg'}], 'start': 21333.015, 'title': 'Sql functions and clauses', 'summary': "Introduces group by and having clauses in sql, explores their usage to find average salaries, maximum salaries, and total employees by country, demonstrates the usage of having clause in postgresql to filter rows based on aggregate functions, details the process of creating a new 'salary range' column using a sql case expression, covers using subqueries to find employees with salaries greater than the average, and introduces various postgresql functions.", 'chapters': [{'end': 21611.846, 'start': 21333.015, 'title': 'Exploring group by and having in sql', 'summary': 'Introduces the group by and having clauses in sql, demonstrating their usage to find average salaries by country, maximum salaries by gender, and total employees by country.', 'duration': 278.831, 'highlights': ['The group by clause is used to arrange identical data into groups, such as finding the average salary of employees based on countries.', 'Using the group by clause, the average salary of employees in each country was calculated, with the highest average salary found in the United States and the lowest in Germany.', 'The max function was used with the group by clause to find the maximum salary for male and female employees, revealing the highest female salary to be $119,616 and the highest male salary to be $117,654.', 'The count function was employed with the group by clause to determine the total number of employees from each country, showing 4 employees in Israel and Australia, and 80 employees in Russia.']}, {'end': 21864.389, 'start': 21611.846, 'title': 'Using having clause in postgresql', 'summary': 'Explores the usage of the having clause in postgresql to filter rows based on aggregate functions, resulting in the identification of countries with average salary greater than 80,000 dollars and those with less than 30 employees, showcasing russia and united states as having high average salaries, and israel, australia, united states, and germany as having less than 30 employees.', 'duration': 252.543, 'highlights': ['The having clause is used with the group by clause to return those rows that meet a condition, such as finding countries with average salary greater than 80,000 dollars. The having clause is used with the group by clause to filter rows based on aggregate functions, such as identifying countries with average salary greater than 80,000 dollars.', 'Using the count function with the having clause to find countries with less than 30 employees, resulting in the identification of Israel, Australia, United States, and Germany. The count function with the having clause is employed to identify countries with less than 30 employees, showcasing Israel, Australia, United States, and Germany as examples.', 'Employing the order by clause to arrange results in ascending order of employee IDs. The order by clause is utilized to arrange results in ascending order of employee IDs.', 'Exploring the usage of a case statement, similar to if else logic in other programming languages, to add conditional logic to queries. The case statement, akin to if else logic in other programming languages, is employed to add conditional logic to queries in PostgreSQL.']}, {'end': 22148.981, 'start': 21864.389, 'title': 'Creating salary ranges with sql case expression', 'summary': "Details the process of creating a new 'salary range' column based on specified salary conditions using a sql case expression in postgresql, resulting in low, medium, and high salary categories, and demonstrates the implementation and output.", 'duration': 284.592, 'highlights': ["The process involves creating a new 'salary range' column in the output by specifying salary conditions using a SQL case expression, resulting in low, medium, and high salary categories.", "The SQL query creates a new column 'salary range' based on specified salary conditions, categorizing salaries into low, medium, and high salary ranges for improved data analysis.", 'Explanation of writing comments in PostgreSQL and the flexibility in case sensitivity for codes and select statements in Postgres SQL.']}, {'end': 22341.193, 'start': 22150.555, 'title': 'Subqueries and inbuilt functions in postgres', 'summary': 'Covers using subqueries to find employees with salaries greater than the average, resulting in approximately 75 employees, and introduces inbuilt mathematical and string functions available in postgres sql.', 'duration': 190.638, 'highlights': ['Using subqueries to find employees with salaries greater than the average, resulting in approximately 75 employees.', 'Introduction to inbuilt mathematical and string functions available in Postgres SQL.', 'Demonstrating the process of writing subqueries inside another query to filter employees based on salary.', 'Explanation of the SQL statement to find the average salary from the table and compare it with individual employee salaries.', 'Use of inbuilt mathematical functions and string functions available in Postgres SQL.']}, {'end': 22631.085, 'start': 22342.13, 'title': 'Postgresql functions', 'summary': 'Covers various postgresql functions including abs, greatest, least, mod, power, sqrt, sine, cosine, tangent, ceiling, and floor, providing examples and results for each function.', 'duration': 288.955, 'highlights': ['The greatest function in PostgreSQL returns the greatest number in a range of numbers, such as 490, 56.5, and 70, with the result being 90.', 'The abs function is used to find the absolute value of a number, for example, abs(-100) returns 100.', 'The power function, when used as select power 2,3, returns 8 for 2 cubed and 125 for 5 cubed.', 'The sqrt function in PostgreSQL is used to find the square root of a number, for instance, sqrt(100) returns 10 and sqrt(144) returns 12.', 'The sine, cosine, and tangent functions are available in PostgreSQL for trigonometric calculations, such as sine of 0 returning 0 and sine of 90 returning 0.89.']}], 'duration': 1298.07, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g21333015.jpg', 'highlights': ['Using the group by clause, the average salary of employees in each country was calculated, with the highest average salary found in the United States and the lowest in Germany.', 'The max function was used with the group by clause to find the maximum salary for male and female employees, revealing the highest female salary to be $119,616 and the highest male salary to be $117,654.', 'The count function was employed with the group by clause to determine the total number of employees from each country, showing 4 employees in Israel and Australia, and 80 employees in Russia.', 'The having clause is used with the group by clause to return those rows that meet a condition, such as finding countries with average salary greater than 80,000 dollars.', 'Using the count function with the having clause to find countries with less than 30 employees, resulting in the identification of Israel, Australia, United States, and Germany.', "The process involves creating a new 'salary range' column in the output by specifying salary conditions using a SQL case expression, resulting in low, medium, and high salary categories.", 'Using subqueries to find employees with salaries greater than the average, resulting in approximately 75 employees.', 'The greatest function in PostgreSQL returns the greatest number in a range of numbers, such as 490, 56.5, and 70, with the result being 90.', 'The abs function is used to find the absolute value of a number, for example, abs(-100) returns 100.', 'The power function, when used as select power 2,3, returns 8 for 2 cubed and 125 for 5 cubed.']}, {'end': 23567.574, 'segs': [{'end': 22696.026, 'src': 'embed', 'start': 22659.246, 'weight': 0, 'content': [{'end': 22670.616, 'text': 'suppose i write select, give the function as character length and inside this function i am going to pass in a text.', 'start': 22659.246, 'duration': 11.37}, {'end': 22674.2, 'text': "let's say india is a democracy.", 'start': 22670.616, 'duration': 3.584}, {'end': 22680.134, 'text': 'this is my text.', 'start': 22678.393, 'duration': 1.741}, {'end': 22682.996, 'text': 'let me run this.', 'start': 22680.134, 'duration': 2.862}, {'end': 22692.423, 'text': 'okay, you can see the result here, which is 20, since there are 20 characters in my string that I have provided all right.', 'start': 22682.996, 'duration': 9.427}, {'end': 22696.026, 'text': "now there's another function called concat in Postgres SQL.", 'start': 22692.423, 'duration': 3.603}], 'summary': "Using character length function on text 'india is a democracy' returned a result of 20 characters in postgres sql.", 'duration': 36.78, 'max_score': 22659.246, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22659246.jpg'}, {'end': 22766.12, 'src': 'embed', 'start': 22743.042, 'weight': 1, 'content': [{'end': 22750.572, 'text': 'now what we have done is inside the concat function we have passed in separate strings And now, using the concat function,', 'start': 22743.042, 'duration': 7.53}, {'end': 22752.873, 'text': 'we want to merge the three strings.', 'start': 22750.572, 'duration': 2.301}, {'end': 22754.754, 'text': "Let's see what the result is.", 'start': 22753.193, 'duration': 1.561}, {'end': 22757.075, 'text': "I'll run it.", 'start': 22756.155, 'duration': 0.92}, {'end': 22758.156, 'text': 'All right.', 'start': 22757.836, 'duration': 0.32}, {'end': 22759.477, 'text': 'Let me just expand this.', 'start': 22758.396, 'duration': 1.081}, {'end': 22763.459, 'text': 'You can see here, we have concatenated the three strings successfully.', 'start': 22759.817, 'duration': 3.642}, {'end': 22766.12, 'text': 'So the output is PostgreSQL is interesting.', 'start': 22763.499, 'duration': 2.621}], 'summary': "Using the concat function, three strings were merged to successfully output 'postgresql is interesting.'", 'duration': 23.078, 'max_score': 22743.042, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22743042.jpg'}, {'end': 22986.537, 'src': 'embed', 'start': 22953.11, 'weight': 2, 'content': [{'end': 22957.713, 'text': 'so now we will learn how to write a function of our own in postgresql.', 'start': 22953.11, 'duration': 4.603}, {'end': 22965.127, 'text': "so let's create a function to count the total number of email ids that are present in our employees table.", 'start': 22957.713, 'duration': 7.414}, {'end': 22969.713, 'text': "so for this we'll write a function, a user defined function.", 'start': 22965.127, 'duration': 4.586}, {'end': 22974.379, 'text': 'so let me give my comment as user defined function.', 'start': 22969.713, 'duration': 4.666}, {'end': 22982.856, 'text': 'okay, so let me start by first writing create.', 'start': 22974.379, 'duration': 8.477}, {'end': 22986.537, 'text': 'so this is the syntax to write a function in postgresql.', 'start': 22982.856, 'duration': 3.681}], 'summary': 'Learn to create a user-defined function in postgresql to count email ids in the employees table.', 'duration': 33.427, 'max_score': 22953.11, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22953110.jpg'}, {'end': 23272.629, 'src': 'embed', 'start': 23217.174, 'weight': 3, 'content': [{'end': 23223.073, 'text': 'so here you can see there are 134 email ids present in our employees table.', 'start': 23217.174, 'duration': 5.899}, {'end': 23230.208, 'text': 'now one thing to mark is, there are total 150 employees in the table, but out of them, 134 employees have email ids.', 'start': 23223.073, 'duration': 7.135}, {'end': 23235.17, 'text': "the rest of them don't have, so they would ideally have null values.", 'start': 23230.208, 'duration': 4.962}, {'end': 23242.173, 'text': 'all right, so that brings us to the end of this demo session on postgres sql tutorial.', 'start': 23235.17, 'duration': 7.003}, {'end': 23243.473, 'text': 'let me go to the top.', 'start': 23242.173, 'duration': 1.3}, {'end': 23250.816, 'text': 'we have explored a lot, so we started with checking the version of postgres sql.', 'start': 23243.473, 'duration': 7.343}, {'end': 23256.599, 'text': 'then we saw how to perform basic mathematical operation, that is, to add, subtract, multiply.', 'start': 23250.816, 'duration': 5.783}, {'end': 23260.461, 'text': 'then we saw how to create a table that was movies.', 'start': 23257.399, 'duration': 3.062}, {'end': 23262.763, 'text': 'we inserted a few records to our movies table.', 'start': 23260.461, 'duration': 2.302}, {'end': 23267.286, 'text': 'then we used our select clause.', 'start': 23262.763, 'duration': 4.523}, {'end': 23269.007, 'text': 'we updated a few values.', 'start': 23267.286, 'duration': 1.721}, {'end': 23272.629, 'text': 'then we deleted one row of information.', 'start': 23269.007, 'duration': 3.622}], 'summary': '134 out of 150 employees have email ids in the table.', 'duration': 55.455, 'max_score': 23217.174, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g23217174.jpg'}, {'end': 23412.925, 'src': 'embed', 'start': 23386.015, 'weight': 5, 'content': [{'end': 23391.9, 'text': 'you can give your email IDs in the comment section and our team will share this SQL file with you over email.', 'start': 23386.015, 'duration': 5.885}, {'end': 23395.503, 'text': 'SQL is a very important technology that is used in the IT industry.', 'start': 23392.32, 'duration': 3.183}, {'end': 23402.348, 'text': 'Professionals working as application developers, web developers, data analysts, data scientists,', 'start': 23396.403, 'duration': 5.945}, {'end': 23405.831, 'text': 'software testers and cybersecurity experts need to know SQL.', 'start': 23402.348, 'duration': 3.483}, {'end': 23412.925, 'text': 'In this video, we will cover 30 important SQL questions that are most frequently asked in the interviews.', 'start': 23407, 'duration': 5.925}], 'summary': 'Sql is vital in it; 30 popular sql questions for developers, analysts, testers, and experts.', 'duration': 26.91, 'max_score': 23386.015, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g23386015.jpg'}], 'start': 22632.037, 'title': 'Postgresql sql functions and tutorial summary', 'summary': 'Covers the usage of postgres sql string functions like character length and concat, demonstrating their application with a character length of 20 and successful string merging. it also delves into built-in and user-defined functions in postgresql, along with a comprehensive sql tutorial summary encompassing various sql operations, including important sql questions frequently asked in interviews.', 'chapters': [{'end': 22766.12, 'start': 22632.037, 'title': 'Postgres sql string functions', 'summary': "Covers the usage of character length function to find the length of a text string and the concat function to merge multiple strings, demonstrating the length of a given text as 20 characters and successfully merging 'postgres sql', 'is', and 'interesting' into 'postgresql is interesting'.", 'duration': 134.083, 'highlights': ["Demonstrated the usage of character length function to find the length of a text string The character length function was used to find the length of the text 'India is a democracy', resulting in a length of 20 characters.", "Illustrated the usage of concat function to merge multiple strings The concat function was employed to successfully merge the strings 'Postgres SQL', 'is', and 'interesting' into 'PostgreSQL is interesting'."]}, {'end': 23217.174, 'start': 22767.532, 'title': 'Postgresql functions and user defined functions', 'summary': 'Covers built-in functions in postgresql such as left, right, mid, repeat, and reverse along with an example of creating a user-defined function to count the total number of email ids in the employees table.', 'duration': 449.642, 'highlights': ["The left function in PostgreSQL extracts the specified number of characters from the left of a string, demonstrated by extracting the first five characters from a given string. The left function in PostgreSQL extracts the specified number of characters from the left of a string. For example, extracting the first five characters from a given string 'India is a democracy'.", "The right function in PostgreSQL extracts the specified number of characters from the right of a string, demonstrated by extracting 12 characters from the right of a given string. The right function in PostgreSQL extracts the specified number of characters from the right of a string. For example, extracting 12 characters from the right of a given string 'India is a democracy'.", "The repeat function in PostgreSQL repeats a specified string a certain number of times, illustrated by repeating the string 'India' five times. The repeat function in PostgreSQL repeats a specified string a certain number of times. For example, repeating the string 'India' five times.", "The reverse function in PostgreSQL prints a string in reverse order, demonstrated by reversing the string 'India is a democracy'. The reverse function in PostgreSQL prints a string in reverse order. For example, reversing the string 'India is a democracy'.", 'An example of creating a user-defined function in PostgreSQL to count the total number of email ids in the employees table is provided. An example of creating a user-defined function in PostgreSQL to count the total number of email ids in the employees table is provided.']}, {'end': 23567.574, 'start': 23217.174, 'title': 'Sql tutorial summary', 'summary': 'Covered various sql operations including the demonstration of a postgres sql tutorial, with highlights on the number of employees with email ids, the different sql commands and functions, and the importance of sql in the it industry, including 30 important sql questions frequently asked in interviews.', 'duration': 350.4, 'highlights': ['There are 134 email ids present in our employees table out of 150 employees, indicating that 16 employees do not have email ids. This highlights the specific number of employees with email IDs, providing quantifiable data.', 'The chapter covered various SQL operations including creating tables, inserting records, using select, update, delete clauses, and exploring different operators and functions. This demonstrates the breadth of SQL operations covered in the tutorial.', 'SQL is important for professionals working as application developers, web developers, data analysts, data scientists, software testers, and cybersecurity experts. This emphasizes the relevance of SQL in the IT industry and lists the professionals who need to know SQL.']}], 'duration': 935.537, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g22632037.jpg', 'highlights': ['Demonstrated the usage of character length function to find the length of a text string, resulting in a length of 20 characters.', "Illustrated the usage of concat function to successfully merge the strings 'Postgres SQL', 'is', and 'interesting' into 'PostgreSQL is interesting'.", 'An example of creating a user-defined function in PostgreSQL to count the total number of email ids in the employees table is provided.', 'There are 134 email ids present in our employees table out of 150 employees, indicating that 16 employees do not have email ids.', 'The chapter covered various SQL operations including creating tables, inserting records, using select, update, delete clauses, and exploring different operators and functions.', 'SQL is important for professionals working as application developers, web developers, data analysts, data scientists, software testers, and cybersecurity experts.']}, {'end': 24341.181, 'segs': [{'end': 23622.546, 'src': 'embed', 'start': 23591.598, 'weight': 0, 'content': [{'end': 23601.361, 'text': "so I am using a min function to find the lowest salary in the table and then I'm grouping all the employees and the results by department.", 'start': 23591.598, 'duration': 9.763}, {'end': 23606.203, 'text': "so let's do this on mysql workbench.", 'start': 23601.361, 'duration': 4.842}, {'end': 23609.023, 'text': 'okay, so here I am on my mysql workbench.', 'start': 23606.203, 'duration': 2.82}, {'end': 23612.844, 'text': 'first let me go ahead and create a new SQL script.', 'start': 23609.023, 'duration': 3.821}, {'end': 23618.445, 'text': "so I'll click on this and that will create a new script.", 'start': 23612.844, 'duration': 5.601}, {'end': 23621.506, 'text': "okay, so here we'll write our commands.", 'start': 23618.445, 'duration': 3.061}, {'end': 23622.546, 'text': 'all right.', 'start': 23621.506, 'duration': 1.04}], 'summary': "Using 'min' function to find lowest salary, grouping employees by department in mysql workbench.", 'duration': 30.948, 'max_score': 23591.598, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g23591598.jpg'}, {'end': 23811.821, 'src': 'embed', 'start': 23778.954, 'weight': 1, 'content': [{'end': 23789.918, 'text': "you can see at the bottom, and in these seven departments you can see in sales 70 000, let's say, dollars was the lowest salary.", 'start': 23778.954, 'duration': 10.964}, {'end': 23793.339, 'text': 'in marketing we had 55 000 as the lowest salary.', 'start': 23789.918, 'duration': 3.421}, {'end': 23805.697, 'text': 'similarly, if i scroll down, we have other departments like tech, it, finance and hr, so these are the lowest salary in each department.', 'start': 23793.339, 'duration': 12.358}, {'end': 23811.821, 'text': 'all right now moving to our next question.', 'start': 23805.697, 'duration': 6.124}], 'summary': 'In sales, the lowest salary was $70,000, while in marketing it was $55,000. other departments had their own lowest salaries.', 'duration': 32.867, 'max_score': 23778.954, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g23778954.jpg'}, {'end': 23855.369, 'src': 'embed', 'start': 23832.037, 'weight': 2, 'content': [{'end': 23845.066, 'text': 'So, since I want to get the unique values from a column in a table, so the right option would be to use the distinct keyword in the query.', 'start': 23832.037, 'duration': 13.029}, {'end': 23849.227, 'text': 'so if I write select distinct, followed by the column name,', 'start': 23845.066, 'duration': 4.161}, {'end': 23855.369, 'text': "let's say you want to find the distinct cities or the distinct departments in the employees table.", 'start': 23849.227, 'duration': 6.142}], 'summary': "Use 'distinct' keyword to retrieve unique values from a column in a table.", 'duration': 23.332, 'max_score': 23832.037, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g23832037.jpg'}, {'end': 24102.037, 'src': 'embed', 'start': 24076.8, 'weight': 3, 'content': [{'end': 24083.448, 'text': "so first i'm finding the unique departments and then, using the length function, i'm finding the length of each department.", 'start': 24076.8, 'duration': 6.648}, {'end': 24085.569, 'text': "let's run and see.", 'start': 24083.448, 'duration': 2.121}, {'end': 24087.75, 'text': 'okay, you can see the result here.', 'start': 24085.569, 'duration': 2.181}, {'end': 24090.672, 'text': 'so sales, the length is 5.', 'start': 24087.75, 'duration': 2.922}, {'end': 24093.093, 'text': 'marketing, the length of marketing is 9.', 'start': 24090.672, 'duration': 2.421}, {'end': 24098.236, 'text': 'so basically you are counting the number of characters that are present in each of the departments.', 'start': 24093.093, 'duration': 5.143}, {'end': 24100.116, 'text': 'so in sales there are total five characters.', 'start': 24098.236, 'duration': 1.88}, {'end': 24102.037, 'text': 'in marketing there are total nine characters.', 'start': 24100.116, 'duration': 1.921}], 'summary': 'Identified unique departments and found their respective lengths, with sales at 5 and marketing at 9 characters.', 'duration': 25.237, 'max_score': 24076.8, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24076800.jpg'}, {'end': 24152.674, 'src': 'embed', 'start': 24123.406, 'weight': 4, 'content': [{'end': 24134.924, 'text': 'all right, moving to the seventh question, the question is what is the use of date diff function in sql?', 'start': 24123.406, 'duration': 11.518}, {'end': 24142.589, 'text': 'now, date diff returns the number of days between two, date or date, time or timestamp values.', 'start': 24134.924, 'duration': 7.665}, {'end': 24149.993, 'text': 'so date diff is another crucial inbuilt function that is present in mysql.', 'start': 24142.589, 'duration': 7.404}, {'end': 24152.674, 'text': 'so here is how you can use the date div function.', 'start': 24149.993, 'duration': 2.681}], 'summary': 'Date diff function in sql returns the number of days between two date or timestamp values, crucial in mysql.', 'duration': 29.268, 'max_score': 24123.406, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24123406.jpg'}], 'start': 23567.574, 'title': 'Mysql departmental salary search and sql querying basics', 'summary': 'Details finding the lowest departmental salaries in mysql using min function and group by department, resulting in identification of lowest salaries in seven departments, and covers sql querying basics, including fetching unique values using distinct keyword and inbuilt functions like length and datediff, with practical examples and results.', 'chapters': [{'end': 23811.821, 'start': 23567.574, 'title': 'Finding lowest departmental salaries in mysql', 'summary': 'Details the process of finding the lowest salary for each department in a mysql database table named employees using the min function and group by department, resulting in the identification of the lowest salaries in seven different departments.', 'duration': 244.247, 'highlights': ['The chapter details the process of finding the lowest salary for each department in a MySQL database table named employees using the min function and group by department The transcript explains the usage of the min function and group by department to find the lowest salary for each department in the MySQL database table named employees.', 'Identifying the lowest salaries in seven different departments The result of the query shows the lowest salaries in seven different departments, including sales with a lowest salary of $70,000 and marketing with a lowest salary of $55,000.', 'Explanation of the table structure and data in the employees table The transcript provides an overview of the structure and data present in the employees table, which includes 20 rows and columns such as employee id, name, age, gender, department, city, and salary.']}, {'end': 24341.181, 'start': 23811.821, 'title': 'Sql querying basics', 'summary': 'Covers sql querying basics, including fetching unique values from a column, using distinct keyword, and inbuilt functions like length and datediff, with practical examples and results.', 'duration': 529.36, 'highlights': ["Using distinct keyword to fetch unique values from a column in a table Using 'select distinct' followed by the column name to find unique values; demonstrated with practical examples and results.", 'Practical demonstration of using inbuilt length function to find the length of departments Using the inbuilt length function to find the length of each department in a table; demonstrated with practical examples and results.', 'Explanation of the use of dateDiff function in SQL with practical examples Demonstrating the use of dateDiff function to calculate the number of days between two dates or date time values; practical examples and results shown.']}], 'duration': 773.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g23567574.jpg', 'highlights': ['The chapter details the process of finding the lowest salary for each department in a MySQL database table named employees using the min function and group by department.', 'Identifying the lowest salaries in seven different departments, including sales with a lowest salary of $70,000 and marketing with a lowest salary of $55,000.', 'Using distinct keyword to fetch unique values from a column in a table.', 'Practical demonstration of using inbuilt length function to find the length of departments.', 'Explanation of the use of dateDiff function in SQL with practical examples.']}, {'end': 25123.373, 'segs': [{'end': 24471.638, 'src': 'embed', 'start': 24446.786, 'weight': 0, 'content': [{'end': 24452.748, 'text': 'so our query will return only those departments which have more than two employees.', 'start': 24446.786, 'duration': 5.962}, {'end': 24453.568, 'text': "let's run and see.", 'start': 24452.748, 'duration': 0.82}, {'end': 24455.649, 'text': 'there you go.', 'start': 24453.568, 'duration': 2.081}, {'end': 24464.493, 'text': 'so we have total four departments, that is, sales, product, tech and it, where we have more than two employees.', 'start': 24455.649, 'duration': 8.844}, {'end': 24471.638, 'text': 'you can see here sales has four employees, product has three, tech has four and IT has three.', 'start': 24464.493, 'duration': 7.145}], 'summary': 'Four departments have over two employees each.', 'duration': 24.852, 'max_score': 24446.786, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24446786.jpg'}, {'end': 24573.908, 'src': 'embed', 'start': 24536.074, 'weight': 2, 'content': [{'end': 24544.498, 'text': 'this means select all the employees where department is not equal to marketing.', 'start': 24536.074, 'duration': 8.424}, {'end': 24545.339, 'text': "now let's do this.", 'start': 24544.498, 'duration': 0.841}, {'end': 24556.862, 'text': "I'll move down a bit and we'll give a comment as details of employees apart from marketing.", 'start': 24547.398, 'duration': 9.464}, {'end': 24564.024, 'text': 'So the query would be select star from employees.', 'start': 24559.823, 'duration': 4.201}, {'end': 24568.326, 'text': "I'm using star because I want to display all the details.", 'start': 24565.405, 'duration': 2.921}, {'end': 24570.527, 'text': 'That means I want to display all the columns.', 'start': 24568.646, 'duration': 1.881}, {'end': 24573.908, 'text': "Then I'm going to use my where condition.", 'start': 24571.507, 'duration': 2.401}], 'summary': 'Select all employees not in marketing department to display all details.', 'duration': 37.834, 'max_score': 24536.074, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24536074.jpg'}, {'end': 24734.423, 'src': 'embed', 'start': 24677.63, 'weight': 3, 'content': [{'end': 24689.217, 'text': 'so here we are using just a where condition to filter our results, and my condition is the employee should have joined after May 2005.', 'start': 24677.63, 'duration': 11.587}, {'end': 24695.782, 'text': 'so I have written after 31st of May 2005 and before April 2010, which is before 31st of March 2010.', 'start': 24689.217, 'duration': 6.565}, {'end': 24696.262, 'text': "let's do this.", 'start': 24695.782, 'duration': 0.48}, {'end': 24712.849, 'text': "I'll give a comment here.", 'start': 24696.262, 'duration': 16.587}, {'end': 24732.602, 'text': "we'll write employees joined before April 2010 and after May 2005.", 'start': 24712.849, 'duration': 19.753}, {'end': 24734.423, 'text': "now let's write our SQL query.", 'start': 24732.602, 'duration': 1.821}], 'summary': 'Using sql to filter employees who joined between may 2005 and april 2010.', 'duration': 56.793, 'max_score': 24677.63, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24677630.jpg'}, {'end': 24883.164, 'src': 'embed', 'start': 24854.611, 'weight': 1, 'content': [{'end': 24864.938, 'text': 'so i have my employees table here and my command would look something like this so here we are going to explore how to write sub queries in sql.', 'start': 24854.611, 'duration': 10.327}, {'end': 24878.962, 'text': "so i'm using a sub query here where i'm trying to find the employees who have the top three salaries or the top three employees who have the highest salaries,", 'start': 24864.938, 'duration': 14.024}, {'end': 24883.164, 'text': "and from that I'm going to filter out my third highest salary employee.", 'start': 24878.962, 'duration': 4.202}], 'summary': 'Demonstrating subqueries in sql to find top 3 highest salaries and filter out third highest salary employee.', 'duration': 28.553, 'max_score': 24854.611, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24854611.jpg'}, {'end': 25098.057, 'src': 'embed', 'start': 25077.385, 'weight': 4, 'content': [{'end': 25087.251, 'text': 'now the way to do is either you can use a simple select clause and say employee id percentage, or modulus 2 equal to 0,', 'start': 25077.385, 'duration': 9.866}, {'end': 25093.434, 'text': 'which means i want to filter only those employees which have an even employee id.', 'start': 25087.251, 'duration': 6.183}, {'end': 25098.057, 'text': 'else what you can do is you can use a cte, as you can see here.', 'start': 25093.434, 'duration': 4.623}], 'summary': 'Filter employees with even employee id using select clause or cte.', 'duration': 20.672, 'max_score': 25077.385, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g25077385.jpg'}], 'start': 24341.501, 'title': 'Sql query examples and subqueries', 'summary': 'Covers sql query examples for specific conditions and subqueries including finding employees with specific criteria, and demonstrates the use of subqueries and common table expressions.', 'chapters': [{'end': 24829.318, 'start': 24341.501, 'title': 'Sql query examples and explanations', 'summary': 'Covers sql query examples including displaying departments with more than two employees, details of employees for all departments except marketing, and printing details of employees who joined before april 2010 and after may 2005, providing insights into the queries and their results.', 'duration': 487.817, 'highlights': ['The SQL query demonstrates displaying departments with more than two employees, resulting in four departments (sales, product, tech, IT) with more than two employees (sales: 4, product: 3, tech: 4, IT: 3). Four departments have more than two employees.', 'The explanation and SQL query for displaying details of employees for all departments except marketing, resulting in 18 rows of information, excluding employees from the marketing department. Excludes employees from the marketing department.', 'The SQL query to print details of employees who joined before April 2010 and after May 2005 using a where condition to filter the results based on the date of join. Filtering employees based on joining dates.']}, {'end': 25123.373, 'start': 24829.318, 'title': 'Sql subqueries and common table expressions', 'summary': 'Explores how to write sub queries in sql to find the employee with the third highest salary and how to print all the alternate records in a table, demonstrating the use of sub queries and common table expressions.', 'duration': 294.055, 'highlights': ['The sub query is used to find the employee with the third highest salary by querying the top three employees with the highest salaries and filtering out the third highest salary employee using SQL. Top three employees with the highest salaries are Joseph, Angela, and Jack. Jack is the employee who earns the third highest salary.', 'The chapter demonstrates the use of sub queries and common table expressions to print all the alternate records in a table by filtering odd or even employee IDs. Demonstrates filtering odd or even employee IDs using a simple select clause with modulus 2 or using a common table expression (CTE) to simplify complex joins and sub queries.']}], 'duration': 781.872, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g24341501.jpg', 'highlights': ['The SQL query demonstrates displaying departments with more than two employees, resulting in four departments (sales, product, tech, IT) with more than two employees (sales: 4, product: 3, tech: 4, IT: 3). Four departments have more than two employees.', 'The sub query is used to find the employee with the third highest salary by querying the top three employees with the highest salaries and filtering out the third highest salary employee using SQL. Top three employees with the highest salaries are Joseph, Angela, and Jack. Jack is the employee who earns the third highest salary.', 'The explanation and SQL query for displaying details of employees for all departments except marketing, resulting in 18 rows of information, excluding employees from the marketing department. Excludes employees from the marketing department.', 'The SQL query to print details of employees who joined before April 2010 and after May 2005 using a where condition to filter the results based on the date of join. Filtering employees based on joining dates.', 'The chapter demonstrates the use of sub queries and common table expressions to print all the alternate records in a table by filtering odd or even employee IDs. Demonstrates filtering odd or even employee IDs using a simple select clause with modulus 2 or using a common table expression (CTE) to simplify complex joins and sub queries.']}, {'end': 26116.008, 'segs': [{'end': 25150.668, 'src': 'embed', 'start': 25123.373, 'weight': 0, 'content': [{'end': 25129.351, 'text': 'I am using a few new functions, such as row number.', 'start': 25123.373, 'duration': 5.978}, {'end': 25134.555, 'text': 'This will create a row number for each of the rows or the records present in my table.', 'start': 25129.591, 'duration': 4.964}, {'end': 25144.904, 'text': "And then from this city, I'm going to use my filter clause or the where clause where RN, which is a row number percentage two equal to equal to one.", 'start': 25135.256, 'duration': 9.648}, {'end': 25149.247, 'text': 'So this will return only the odd records from the table.', 'start': 25144.944, 'duration': 4.303}, {'end': 25150.668, 'text': "Let's see how to do it.", 'start': 25149.827, 'duration': 0.841}], 'summary': 'Using row number to filter odd records from the table.', 'duration': 27.295, 'max_score': 25123.373, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g25123373.jpg'}, {'end': 25435.451, 'src': 'embed', 'start': 25403.644, 'weight': 1, 'content': [{'end': 25409.329, 'text': 'okay, so this is how you can print alternate records in a table.', 'start': 25403.644, 'duration': 5.685}, {'end': 25413.073, 'text': 'now moving to the next question.', 'start': 25409.329, 'duration': 3.744}, {'end': 25420.328, 'text': 'now, in the 13th question, we want to write an SQL query to fetch all the duplicate rows in a table.', 'start': 25413.073, 'duration': 7.255}, {'end': 25426.609, 'text': 'now this is another crucial interview question that is often asked in most of the interviews.', 'start': 25420.328, 'duration': 6.281}, {'end': 25435.451, 'text': 'now i have a table which is named as duplicate table and here you can see i have some employee id, name and age and a few records are duplicated.', 'start': 25426.609, 'duration': 8.842}], 'summary': 'Demonstrating printing alternate records and writing sql query for fetching duplicate rows from a table.', 'duration': 31.807, 'max_score': 25403.644, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g25403644.jpg'}, {'end': 25947.318, 'src': 'embed', 'start': 25919.211, 'weight': 2, 'content': [{'end': 25929.814, 'text': "I am subtracting the length after replacing the A's present in the employee name with a blank, and this value should be equal to 2..", 'start': 25919.211, 'duration': 10.603}, {'end': 25934.395, 'text': "The reason being we are only trying to find the employees who have 2 A's in their name.", 'start': 25929.814, 'duration': 4.581}, {'end': 25936.836, 'text': "Let's run and see the results.", 'start': 25935.275, 'duration': 1.561}, {'end': 25947.318, 'text': "Okay, if you see here, we have Sarah, Angela, Amelia and Maya who have two A's in their name.", 'start': 25938.49, 'duration': 8.828}], 'summary': "Finding employees with 2 a's in their names resulted in sarah, angela, amelia, and maya.", 'duration': 28.107, 'max_score': 25919.211, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g25919211.jpg'}, {'end': 26003.557, 'src': 'embed', 'start': 25976.816, 'weight': 3, 'content': [{'end': 25980.418, 'text': 'he is from Germany and also was the captain of Germany.', 'start': 25976.816, 'duration': 3.602}, {'end': 25985.162, 'text': 'so from the string, Michael Ballack, I am going to extract four characters, starting from the second position.', 'start': 25980.418, 'duration': 4.744}, {'end': 25990.011, 'text': 'you can use two inbuilt mysql functions to do this.', 'start': 25986.189, 'duration': 3.822}, {'end': 26000.475, 'text': 'the first is called substr, wherein we pass in the string, then we give the position from where we want to extract the characters.', 'start': 25990.011, 'duration': 10.464}, {'end': 26003.557, 'text': 'so here I am going to extract from the second position.', 'start': 26000.475, 'duration': 3.082}], 'summary': 'Michael ballack, captain of germany, extracted 4 characters from 2nd position', 'duration': 26.741, 'max_score': 25976.816, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g25976816.jpg'}], 'start': 25123.373, 'title': 'Sql data manipulation', 'summary': 'Covers various sql techniques such as using row number, modulo, and functions for manipulating employee data. it includes examples of retrieving odd and even records, finding duplicate records, manipulating employee names, and extracting substrings in mysql.', 'chapters': [{'end': 25403.644, 'start': 25123.373, 'title': 'Using row number and modulo in sql', 'summary': 'Demonstrates how to use the row number function and modulo operator to selectively retrieve odd and even records from a table, showcasing examples with employee ids and common table expressions.', 'duration': 280.271, 'highlights': ['The chapter demonstrates using the row number function and modulo operator to retrieve odd and even records from a table. It showcases examples with employee IDs and common table expressions, providing practical insights into selecting alternate records based on divisibility.', "The function 'row number' is used to create a row number for each record in a table, followed by filtering for odd or even records using the 'where' clause and modulo operator. This approach allows for the selective retrieval of records based on their divisibility, as demonstrated with practical examples using employee IDs.", 'The demonstration includes using a common table expression (CTE) to create a row number column and filter for odd or even records from the table. The CTE approach provides a structured method for generating row numbers and selectively retrieving records based on divisibility, offering a practical application in SQL queries.']}, {'end': 25800.418, 'start': 25403.644, 'title': "Sql duplicate records and employees with 2 a's", 'summary': "Covers retrieving duplicate records in a table by writing an sql query, demonstrating the process and results, followed by identifying employees with exactly two a's in their names using mysql workbench.", 'duration': 396.774, 'highlights': ['Retrieving duplicate records in a table by writing an SQL query The speaker demonstrates how to write an SQL query to fetch all the duplicate rows in a table, using the count function to compare employee IDs, names, and ages to identify duplicated records.', "Identifying employees with exactly two A's in their names using MySQL Workbench The speaker explains the process of identifying employees with exactly two A's in their names by using a SQL query that involves finding the length of the employee name and subtracting the length after replacing 'a' with a blank space to determine the presence of two A's."]}, {'end': 25947.318, 'start': 25800.418, 'title': 'Using functions to manipulate employee names', 'summary': "Demonstrates using functions like length, replace, and upper to manipulate employee names, such as converting them to uppercase, replacing specific characters, and finding the length after manipulation, resulting in identifying employees with 2 occurrences of the letter 'a'. the final query selects employees with 2 'a's in their names.", 'duration': 146.9, 'highlights': ["The final query selects employees with 2 'A's in their names, resulting in the identification of Sarah, Angela, Amelia, and Maya.", 'The chapter demonstrates using functions like length, replace, and upper to manipulate employee names, such as converting them to uppercase, replacing specific characters, and finding the length after manipulation.']}, {'end': 26116.008, 'start': 25947.678, 'title': 'Extracting substrings in mysql', 'summary': 'Demonstrates how to extract substrings in mysql using the substr and substring functions, with examples showing extraction of specific characters and positions from a given string.', 'duration': 168.33, 'highlights': ["The chapter explains the process of extracting four characters starting from the second position in the string 'Michael Ballack' using the substr and substring functions in MySQL.", "It provides examples and results of extracting specific characters and positions from the given string, such as 'ICHA' when starting from the second position and 'HAE' when starting from the fourth position."]}], 'duration': 992.635, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g25123373.jpg', 'highlights': ['The chapter demonstrates using the row number function and modulo operator to retrieve odd and even records from a table.', 'Retrieving duplicate records in a table by writing an SQL query.', "The final query selects employees with 2 'A's in their names, resulting in the identification of Sarah, Angela, Amelia, and Maya.", "The chapter explains the process of extracting four characters starting from the second position in the string 'Michael Ballack' using the substr and substring functions in MySQL."]}, {'end': 27143.349, 'segs': [{'end': 26475.888, 'src': 'embed', 'start': 26449.584, 'weight': 0, 'content': [{'end': 26459.828, 'text': "again i am going to use a self join here, wherein i'll join the same employees table, and this is how my query would look like.", 'start': 26449.584, 'duration': 10.244}, {'end': 26463.089, 'text': 'so i am selecting distinct employee id.', 'start': 26459.828, 'duration': 3.261}, {'end': 26464.649, 'text': 'so e dot employee id.', 'start': 26463.089, 'duration': 1.56}, {'end': 26466.05, 'text': 'e dot employee name.', 'start': 26464.649, 'duration': 1.401}, {'end': 26468.411, 'text': 'e dot salary from employees.', 'start': 26466.05, 'duration': 2.361}, {'end': 26475.888, 'text': "then i'm joining employees as E on employees E1 where E.salary equal to E1.salary,", 'start': 26468.411, 'duration': 7.477}], 'summary': 'Using self join to select distinct employee id, name, and salary from employees table.', 'duration': 26.304, 'max_score': 26449.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g26449584.jpg'}, {'end': 26616.085, 'src': 'embed', 'start': 26586.513, 'weight': 1, 'content': [{'end': 26592.295, 'text': 'So the question is to write an SQL query to print one row, twice in results from a table.', 'start': 26586.513, 'duration': 5.782}, {'end': 26599.918, 'text': 'So I have a table here called employees and I want to print one row twice in the results.', 'start': 26592.936, 'duration': 6.982}, {'end': 26605.461, 'text': 'So to solve such kind of problems, you need to use the union all operator in SQL.', 'start': 26600.679, 'duration': 4.782}, {'end': 26610.103, 'text': 'So union all combines the result sets of two or more select statements.', 'start': 26606.221, 'duration': 3.882}, {'end': 26616.085, 'text': 'It does not remove duplicate rows between the various select statements, all the rows are returned.', 'start': 26611.163, 'duration': 4.922}], 'summary': 'Use sql union all to print one row twice in results from a table.', 'duration': 29.572, 'max_score': 26586.513, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g26586513.jpg'}, {'end': 26789.194, 'src': 'embed', 'start': 26760.12, 'weight': 2, 'content': [{'end': 26767.384, 'text': 'So you can see here I have my table called num and it has just one column which is num ID or number ID.', 'start': 26760.12, 'duration': 7.264}, {'end': 26780.009, 'text': 'We have some numbers here and from these numbers I want to add 10 if the number is 0, 20 if the number is 1.', 'start': 26767.985, 'duration': 12.024}, {'end': 26783.051, 'text': 'else i would print the number itself.', 'start': 26780.009, 'duration': 3.042}, {'end': 26789.194, 'text': "so to answer this question, we'll use the case statement in sql.", 'start': 26783.051, 'duration': 6.143}], 'summary': 'Using sql case statement to add 10 or 20 to numbers based on condition.', 'duration': 29.074, 'max_score': 26760.12, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g26760120.jpg'}], 'start': 26116.008, 'title': 'Sql join, virtual table, query techniques, and case statements', 'summary': 'Discusses self join, virtual table creation, and querying techniques in sql, including examples and sql queries. it covers fetching employees with the same salary, using union all operator, and employing case statements to add values based on conditions and find sums of numbers.', 'chapters': [{'end': 26578.934, 'start': 26116.008, 'title': 'Self join and virtual table in sql', 'summary': 'Discusses the concept of self join in sql, where a table is joined to itself based on specific conditions, and the creation of a virtual table called a view in sql, along with examples and sql queries for each. it also covers the use of self join to fetch a list of employees with the same salary.', 'duration': 462.926, 'highlights': ['The chapter discusses the concept of self join in SQL, where a table is joined to itself based on specific conditions. Self join allows a table to be joined to itself, using a primary key and another column to match values, illustrated with an example of employees reporting to managers.', "It also covers the creation of a virtual table called a view in SQL, along with examples and SQL queries for each. A view is explained as a virtual table with rows and columns like a real table, created by selecting fields from one or more tables, with the answer being 'view' to a multiple choice question.", 'The use of self join to fetch a list of employees with the same salary is demonstrated through an SQL query and the corresponding results. A self join is used to fetch a list of employees with the same salary, ensuring that the employee IDs from both tables are different, resulting in the identification of employees with the same salary.']}, {'end': 27143.349, 'start': 26586.513, 'title': 'Sql query techniques and case statements', 'summary': 'Covers using the union all operator to print one row twice in results from a table, demonstrating its use in selecting employees from the hr department and utilizing case statements in sql to add values based on conditions and find the sum of positive and negative numbers from a table.', 'duration': 556.836, 'highlights': ['Using the union all operator to print one row twice in results from a table Demonstrated the use of the union all operator to print one row twice in results from a table, particularly in selecting employees from the HR department, showcasing its functionality and application.', 'Utilizing case statements in SQL to add values based on conditions Highlighted the use of case statements in SQL to add values based on conditions, specifically adding 10 when the number is zero, 20 when the number is one, and printing the number itself, providing a clear demonstration of its practical application.', 'Using case statements to find the sum of positive and negative numbers from a table Illustrated the utilization of case statements to find the sum of positive and negative numbers from a table, showcasing the logical conditions used to differentiate between positive and negative numbers and find their respective sums.']}], 'duration': 1027.341, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g26116008.jpg', 'highlights': ['The use of self join to fetch a list of employees with the same salary is demonstrated through an SQL query and the corresponding results.', 'Using the union all operator to print one row twice in results from a table Demonstrated the use of the union all operator to print one row twice in results from a table, particularly in selecting employees from the HR department, showcasing its functionality and application.', 'Utilizing case statements in SQL to add values based on conditions Highlighted the use of case statements in SQL to add values based on conditions, specifically adding 10 when the number is zero, 20 when the number is one, and printing the number itself, providing a clear demonstration of its practical application.']}, {'end': 29031.223, 'segs': [{'end': 27198.025, 'src': 'embed', 'start': 27168.999, 'weight': 0, 'content': [{'end': 27180.73, 'text': 'okay, now, moving ahead now, in question number 22 we want to list the difference between primary key and foreign key.', 'start': 27168.999, 'duration': 11.731}, {'end': 27184.12, 'text': 'so as you can see here.', 'start': 27182.199, 'duration': 1.921}, {'end': 27188.321, 'text': 'primary key is something that can uniquely identify a record in a table.', 'start': 27184.12, 'duration': 4.201}, {'end': 27198.025, 'text': 'so if you consider a school database, a primary key would ideally be the roll number or the registration number of the student,', 'start': 27188.321, 'duration': 9.704}], 'summary': 'Explaining the difference between primary key and foreign key in databases.', 'duration': 29.026, 'max_score': 27168.999, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g27168999.jpg'}, {'end': 27464.886, 'src': 'embed', 'start': 27429.866, 'weight': 1, 'content': [{'end': 27435.753, 'text': "let's say, I'll have my final column, that is age.", 'start': 27429.866, 'duration': 5.887}, {'end': 27442.721, 'text': "age is of type integer and here I'm going to check my age should always be greater than zero.", 'start': 27435.753, 'duration': 6.968}, {'end': 27446.806, 'text': "so I'll write check age greater than zero.", 'start': 27442.721, 'duration': 4.085}, {'end': 27459.724, 'text': 'okay, so our dummy SQL table has one primary key constraint and we have applied two check constraints on city and each column.', 'start': 27448.678, 'duration': 11.046}, {'end': 27462.865, 'text': "let's run this, okay.", 'start': 27459.724, 'duration': 3.141}, {'end': 27464.886, 'text': 'so we have created our table successfully.', 'start': 27462.865, 'duration': 2.021}], 'summary': 'Created a sql table with 1 primary key constraint and 2 check constraints.', 'duration': 35.02, 'max_score': 27429.866, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g27429866.jpg'}, {'end': 27768.14, 'src': 'embed', 'start': 27722.369, 'weight': 2, 'content': [{'end': 27728.253, 'text': 'This is another way to give the condition, or you can use the on operator.', 'start': 27722.369, 'duration': 5.884}, {'end': 27737.499, 'text': "Then I'll give where a.id is null.", 'start': 27729.694, 'duration': 7.805}, {'end': 27741.061, 'text': "Let's run and see the results.", 'start': 27739.5, 'duration': 1.561}, {'end': 27743.042, 'text': 'There you go.', 'start': 27742.662, 'duration': 0.38}, {'end': 27748.326, 'text': 'So 150, 275 are not present in table A but are present in table B.', 'start': 27743.663, 'duration': 4.663}, {'end': 27759.075, 'text': 'Okay, now moving ahead.', 'start': 27748.326, 'duration': 10.749}, {'end': 27762.357, 'text': "so now we'll look at the last set of five questions.", 'start': 27759.075, 'duration': 3.282}, {'end': 27768.14, 'text': 'so these questions are based on the popular Northwind sample database provided by Microsoft.', 'start': 27762.357, 'duration': 5.783}], 'summary': '150, 275 not in table a, 5 questions on northwind database.', 'duration': 45.771, 'max_score': 27722.369, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g27722369.jpg'}, {'end': 27878.926, 'src': 'embed', 'start': 27845.814, 'weight': 3, 'content': [{'end': 27853.876, 'text': 'I am going to change this to Northwind database so that we can utilize all the tables that are present.', 'start': 27845.814, 'duration': 8.062}, {'end': 27867.244, 'text': "so my 26th question was to find the customers who don't have any orders for that going to use two tables customers and orders.", 'start': 27853.876, 'duration': 13.368}, {'end': 27878.926, 'text': 'so let me first give a comment saying customers with no orders.', 'start': 27867.244, 'duration': 11.682}], 'summary': 'Using northwind database, finding customers with no orders.', 'duration': 33.112, 'max_score': 27845.814, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g27845814.jpg'}, {'end': 28230.84, 'src': 'embed', 'start': 28194.455, 'weight': 4, 'content': [{'end': 28198.117, 'text': 'okay. so let me first give my comment for the 27th question.', 'start': 28194.455, 'duration': 3.662}, {'end': 28203.112, 'text': 'So the question is to find all the month end orders.', 'start': 28199.81, 'duration': 3.302}, {'end': 28206.093, 'text': "So I'll just write month end orders.", 'start': 28203.172, 'duration': 2.921}, {'end': 28217.9, 'text': 'Okay Now to get the month end orders, we are going to use a built-in EO month function, which stands for end of month.', 'start': 28209.055, 'duration': 8.845}, {'end': 28219.901, 'text': "So let's start with the query.", 'start': 28218.64, 'duration': 1.261}, {'end': 28221.862, 'text': "I'll write select.", 'start': 28220.921, 'duration': 0.941}, {'end': 28230.84, 'text': 'we are going to select the employee ID comma.', 'start': 28224.619, 'duration': 6.221}], 'summary': 'Using the built-in eo month function to find month end orders.', 'duration': 36.385, 'max_score': 28194.455, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g28194455.jpg'}, {'end': 28485.069, 'src': 'embed', 'start': 28445.22, 'weight': 5, 'content': [{'end': 28457.703, 'text': "I'll use my where clause, where, and then I'm going to find the year from my order date, column, and this year should be 1997.", 'start': 28445.22, 'duration': 12.483}, {'end': 28473.504, 'text': "I'm going to group it by my ship country and I'll use order by my alias name.", 'start': 28457.703, 'duration': 15.801}, {'end': 28476.125, 'text': 'that was average freight.', 'start': 28473.504, 'duration': 2.621}, {'end': 28479.026, 'text': "and in descending order I'm going to sort it.", 'start': 28476.125, 'duration': 2.901}, {'end': 28484.288, 'text': "let's see the top five shipping countries who have the highest freight charges.", 'start': 28479.026, 'duration': 5.262}, {'end': 28485.069, 'text': 'these are the results.', 'start': 28484.288, 'duration': 0.781}], 'summary': 'Using sql, grouped and ordered data to find top 5 shipping countries with highest freight charges in 1997.', 'duration': 39.849, 'max_score': 28445.22, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g28445220.jpg'}, {'end': 28675.855, 'src': 'embed', 'start': 28631.021, 'weight': 6, 'content': [{'end': 28648.032, 'text': "I'll give this an alias name as total products given underscore and write products from my first table, that is, products given alias as p.", 'start': 28631.021, 'duration': 17.011}, {'end': 28658.5, 'text': "i'm going to use an inner join with my categories table and i'll give my alias as c on my common key column.", 'start': 28648.032, 'duration': 10.468}, {'end': 28660.381, 'text': 'that is p dot.', 'start': 28658.5, 'duration': 1.881}, {'end': 28665.025, 'text': 'category id is equal to c dot category id.', 'start': 28660.381, 'duration': 4.644}, {'end': 28675.855, 'text': "then I'm going to group my results based on the category names.", 'start': 28666.832, 'duration': 9.023}], 'summary': 'Joining tables to get total products per category.', 'duration': 44.834, 'max_score': 28631.021, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g28631021.jpg'}, {'end': 29029.441, 'src': 'heatmap', 'start': 28848.394, 'weight': 0.998, 'content': [{'end': 28857.717, 'text': 'and we must remember that late orders are those where the required date is less than equal to the shipped date.', 'start': 28848.394, 'duration': 9.323}, {'end': 28863.179, 'text': "so I'll start with select E dot.", 'start': 28857.717, 'duration': 5.462}, {'end': 28868.802, 'text': "I'll write employee ID comma.", 'start': 28863.179, 'duration': 5.623}, {'end': 28876.806, 'text': 'I want the first name comma.', 'start': 28868.802, 'duration': 8.004}, {'end': 28887.851, 'text': "i'm going to count star as late orders from my table, orders as o.", 'start': 28876.806, 'duration': 11.045}, {'end': 28892.013, 'text': "then i'll use my inner join.", 'start': 28887.851, 'duration': 4.162}, {'end': 28897.215, 'text': "i'll write employees as e.", 'start': 28892.013, 'duration': 5.202}, {'end': 28898.015, 'text': "it doesn't matter.", 'start': 28897.215, 'duration': 0.8}, {'end': 28917.381, 'text': "actually, if you use small e or capital e, then I'm going to use my on condition on E dot employee ID equal to O dot employee ID.", 'start': 28898.015, 'duration': 19.366}, {'end': 28920.164, 'text': 'so this is my common key column that is present in both the tables.', 'start': 28917.381, 'duration': 2.783}, {'end': 28928.359, 'text': "Then I'll use my where clause, where you should be careful and you should give the right clause.", 'start': 28921.274, 'duration': 7.085}, {'end': 28934.223, 'text': 'So my required date should be less than or equal to my shipped date.', 'start': 28928.999, 'duration': 5.224}, {'end': 28939.533, 'text': 'So this is my condition.', 'start': 28937.452, 'duration': 2.081}, {'end': 28953.403, 'text': "and then I'm going to group by E dot, employee ID comma E dot first name, and then I'll use my order by clause, order by my alias name.", 'start': 28939.533, 'duration': 13.87}, {'end': 28957.266, 'text': "that was late orders and I'll arrange it in descending order.", 'start': 28953.403, 'duration': 3.863}, {'end': 28962.986, 'text': "so let's run this and see our result.", 'start': 28959.164, 'duration': 3.822}, {'end': 28967.827, 'text': 'okay? so you have the employee IDs on the left,', 'start': 28962.986, 'duration': 4.841}, {'end': 28973.53, 'text': 'then you have the first name of the employee IDs and then you have the late orders for each of these employees.', 'start': 28967.827, 'duration': 5.703}, {'end': 28983.674, 'text': 'so if you see here, employee ID 4 has the highest number of late orders, followed by employee ID 3 and 8, as well as 9, who have 5 late orders.', 'start': 28973.53, 'duration': 10.144}, {'end': 28987.477, 'text': 'then we have the employee ID 2, which has 4 late orders.', 'start': 28983.674, 'duration': 3.803}, {'end': 28989.918, 'text': 'even employee ID 7 has 4 late orders.', 'start': 28987.477, 'duration': 2.441}, {'end': 28996.202, 'text': 'employee ID 5, that is, Steven, has only 1 late orders.', 'start': 28989.918, 'duration': 6.284}, {'end': 29006.928, 'text': 'so we are done with our demo section and we have covered all our 30 questions, out of which 25 questions were based and used on MySQL server.', 'start': 28996.202, 'duration': 10.726}, {'end': 29014.689, 'text': 'and to address the last five questions we used our northwind database that had lot of tables.', 'start': 29008.423, 'duration': 6.266}, {'end': 29019.733, 'text': 'you can see it here and using those tables we solved some interesting questions.', 'start': 29014.689, 'duration': 5.044}, {'end': 29023.116, 'text': 'so thank you all for watching this live session on SQL full course.', 'start': 29019.733, 'duration': 3.383}, {'end': 29024.597, 'text': 'we hope you enjoyed it.', 'start': 29023.116, 'duration': 1.481}, {'end': 29028.14, 'text': 'if you have any queries, then please feel free to put them in the comment section of the video.', 'start': 29024.597, 'duration': 3.543}, {'end': 29029.441, 'text': "we'll be happy to help you.", 'start': 29028.14, 'duration': 1.301}], 'summary': 'Query analysis shows employee late orders: id 4 has highest (6), followed by 3, 8, 9 (5), 2 and 7 (4), and steven (1). covered 30 questions, 25 on mysql server, 5 on northwind database.', 'duration': 181.047, 'max_score': 28848.394, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g28848394.jpg'}, {'end': 29023.116, 'src': 'embed', 'start': 28973.53, 'weight': 7, 'content': [{'end': 28983.674, 'text': 'so if you see here, employee ID 4 has the highest number of late orders, followed by employee ID 3 and 8, as well as 9, who have 5 late orders.', 'start': 28973.53, 'duration': 10.144}, {'end': 28987.477, 'text': 'then we have the employee ID 2, which has 4 late orders.', 'start': 28983.674, 'duration': 3.803}, {'end': 28989.918, 'text': 'even employee ID 7 has 4 late orders.', 'start': 28987.477, 'duration': 2.441}, {'end': 28996.202, 'text': 'employee ID 5, that is, Steven, has only 1 late orders.', 'start': 28989.918, 'duration': 6.284}, {'end': 29006.928, 'text': 'so we are done with our demo section and we have covered all our 30 questions, out of which 25 questions were based and used on MySQL server.', 'start': 28996.202, 'duration': 10.726}, {'end': 29014.689, 'text': 'and to address the last five questions we used our northwind database that had lot of tables.', 'start': 29008.423, 'duration': 6.266}, {'end': 29019.733, 'text': 'you can see it here and using those tables we solved some interesting questions.', 'start': 29014.689, 'duration': 5.044}, {'end': 29023.116, 'text': 'so thank you all for watching this live session on SQL full course.', 'start': 29019.733, 'duration': 3.383}], 'summary': 'Employee id 4 has the highest late orders (5), followed by ids 3, 8, and 9. covered 30 questions, 25 based on mysql, and 5 using northwind database.', 'duration': 49.586, 'max_score': 28973.53, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g28973530.jpg'}], 'start': 27143.349, 'title': 'Sql constraints, keys, and analysis', 'summary': 'Covers primary keys, foreign keys, unique keys, and check constraints in sql, demonstrating their differences and practical examples. it also showcases left join usage, finding top freight charges in 1997, and late orders analysis from the northwind database.', 'chapters': [{'end': 27605.074, 'start': 27143.349, 'title': 'Sql constraints and keys', 'summary': 'Covers the concept of primary keys, foreign keys, unique keys, and check constraints in sql, including their differences and practical examples, such as the limitation of values and index creation. it also demonstrates the application of check constraints through the creation of a dummy table and the insertion of records, showcasing the enforcement of constraints.', 'duration': 461.725, 'highlights': ['Primary key uniquely identifies a record in a table, such as the roll number or registration number of a student in a school. Primary key uniquely identifies a record in a table, such as the roll number or registration number of a student in a school.', 'Foreign key is a field in a table that is the primary key to another table, and it can accept null values. Foreign key is a field in a table that is the primary key to another table, and it can accept null values.', 'The differences between primary key and foreign key include the acceptance of null values and the limitation to one primary key but multiple foreign keys in a table. The differences between primary key and foreign key include the acceptance of null values and the limitation to one primary key but multiple foreign keys in a table.', 'Check constraints are used to limit or restrict the values that can be inserted into a column, ensuring conditions such as age greater than zero and specific values for columns, as demonstrated through the creation of a dummy table and record insertion. Check constraints are used to limit or restrict the values that can be inserted into a column, ensuring conditions such as age greater than zero and specific values for columns, as demonstrated through the creation of a dummy table and record insertion.']}, {'end': 28306.804, 'start': 27605.074, 'title': 'Sql check constraints and left join usage', 'summary': 'Covers the usage of check constraints in sql and demonstrates the use of left join to fetch non-matching values, along with finding customers without orders in the northwind database and identifying month-end orders using the end of month function in sql.', 'duration': 701.73, 'highlights': ['The left join is used to fetch values in table B not present in table A by specifying the condition a.id as null, resulting in the retrieval of non-matching values such as 150 and 275. The left join is utilized to retrieve non-matching values from table B by setting the condition a.id as null, which yields specific non-matching values like 150 and 275.', "Using the Northwind database, the process of finding customers without orders is demonstrated, revealing two customers without any orders by utilizing the 'not in' operator. The process of finding customers without orders is showcased using the Northwind database, uncovering two customers without any orders through the usage of the 'not in' operator.", 'The EO month function is employed to identify month-end orders, resulting in the retrieval of 26 rows of information representing the orders made on the last day of the month. The EO month function is utilized to identify month-end orders, leading to the retrieval of 26 rows representing orders made on the last day of the month.']}, {'end': 28709.342, 'start': 28306.804, 'title': 'Top 5 freight charges in 1997', 'summary': 'Outlines the process to find the top five countries with the highest freight charges in 1997 using microsoft server management studio, and displays the total number of products in each category using sql queries.', 'duration': 402.538, 'highlights': ['The process to find the top five countries with the highest freight charges in 1997 is demonstrated using SQL queries in Microsoft Server Management Studio. The explanation includes the use of SQL queries to identify the top five countries with the highest freight charges in 1997 and the process of calculating average freight prices for each country.', 'Displaying the total number of products in each category using SQL queries is illustrated. The transcript provides a detailed explanation of writing SQL queries to display the total number of products in each category, including the use of inner join, alias names, and grouping the results based on category names.']}, {'end': 29031.223, 'start': 28709.342, 'title': 'Late orders analysis', 'summary': 'Demonstrates how to use the northwind database to find the list of late orders for all employees, revealing that employee id 4 has the highest number of late orders, followed by employee id 3, 8, and 9, who have 5 late orders each, while employee id 2 and 7 have 4 late orders, and employee id 5 has only 1 late order, covering 30 questions with 25 based on mysql server and 5 on the northwind database.', 'duration': 321.881, 'highlights': ['The employee ID 4 has the highest number of late orders, followed by employee ID 3 and 8, as well as 9, who have 5 late orders.', 'Employee ID 2 and 7 have 4 late orders, while employee ID 5 has only 1 late order.', 'The chapter covers 30 questions, with 25 based and used on MySQL server, and the last five questions utilized the Northwind database.']}], 'duration': 1887.874, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Pjn4P39310g/pics/Pjn4P39310g27143349.jpg', 'highlights': ['Primary key uniquely identifies a record in a table, such as the roll number or registration number of a student in a school.', 'Check constraints are used to limit or restrict the values that can be inserted into a column, ensuring conditions such as age greater than zero and specific values for columns.', 'The left join is utilized to retrieve non-matching values from table B by setting the condition a.id as null, which yields specific non-matching values like 150 and 275.', "The process of finding customers without orders is showcased using the Northwind database, uncovering two customers without any orders through the usage of the 'not in' operator.", 'The EO month function is utilized to identify month-end orders, leading to the retrieval of 26 rows representing orders made on the last day of the month.', 'The explanation includes the use of SQL queries to identify the top five countries with the highest freight charges in 1997 and the process of calculating average freight prices for each country.', 'The transcript provides a detailed explanation of writing SQL queries to display the total number of products in each category, including the use of inner join, alias names, and grouping the results based on category names.', 'The employee ID 4 has the highest number of late orders, followed by employee ID 3 and 8, as well as 9, who have 5 late orders.', 'The chapter covers 30 questions, with 25 based and used on MySQL server, and the last five questions utilized the Northwind database.']}], 'highlights': ['SQL is used by big companies like Amazon, Google, Oracle, and Microsoft as well as startups, making it a highly sought after skill with an average salary of $88,912 per annum in the United States.', 'SQL plays a critical role in various job roles such as software developer, data analyst, marketing analyst, product manager, and data scientist, making it a top language for data work.', 'The chapter covers a comprehensive list of topics including learning the basics of SQL, becoming a SQL developer, installing MySQL Workbench on Windows, using popular SQL commands, learning numerical and text functions, understanding GROUP BY, HAVING, JOINS, sub queries, triggers, stored procedures, window functions in MySQL, and writing SQL queries with Python on the Jupyter Notebook.', 'ER Diagram is essential for managing large and constantly updating datasets for e-commerce companies.', 'The installation process involves visiting the official Oracle website mysql.com, selecting the MySQL installer for Windows, and choosing the specific versions of MySQL server, MySQL shell, and MySQL Workbench to be installed.', 'Using the DISTINCT keyword to retrieve unique city names from a table, resulting in five unique city names being returned.', "The CONCAT function concatenates the student name with a space followed by the age of the student, creating a new column 'name_age' from the 'students' table.", 'The groupby statement or clause groups records into summary rows and returns one record for each group, computing aggregate functions for the resulting group.', 'The chapter provides a demonstration of using the year function to extract the year from the date of joining column in SQL, allowing for the analysis of employee joining trends over time.', 'Emphasizes the concept of SQL joins and their purpose in combining data from different tables, facilitating efficient data retrieval and analysis.', 'Demonstration of using left join, right join, and union operator to emulate full outer join on MySQL Workbench.', 'Demonstrates the usage of row number function, rank function, and first value function in mysql, creation of mysql database and tables, and performing various sql queries.', "The chapter covers the basics of PostgreSQL, including its features, history, and basic SQL commands, emphasizing PostgreSQL's popularity, security, and compatibility with various data types.", "Exploration of advanced SQL commands such as 'distinct' and 'is null' operators.", 'The SQL query demonstrates displaying departments with more than two employees, resulting in four departments (sales, product, tech, IT) with more than two employees (sales: 4, product: 3, tech: 4, IT: 3). Four departments have more than two employees.', 'The chapter demonstrates using the row number function and modulo operator to retrieve odd and even records from a table.', 'The use of self join to fetch a list of employees with the same salary is demonstrated through an SQL query and the corresponding results.', 'Primary key uniquely identifies a record in a table, such as the roll number or registration number of a student in a school.', 'The chapter covers 30 questions, with 25 based and used on MySQL server, and the last five questions utilized the Northwind database.']}