title
SQL Full Course | SQL For Beginners | Mysql Full Course | SQL Training | Simplilearn

description
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=SQLFSJun15&utm_medium=DescritptionFF&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=SQLFSJun15&utm_medium=DescritptionFF&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQLFSJun15&utm_medium=DescritptionFF&utm_source=youtube 🔥Data Analyst Masters Program (Discount Code - YTBE15): https://www.simplilearn.com/data-analyst-masters-certification-training-course?utm_campaign=SQLFSJun15&utm_medium=DescritptionFF&utm_source=youtube This SQL full course or MySQL full 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. 0:00:00 SQL Full Course 0:02:57 What is SQL? 0:05:54 What are ER Diagrams 0:15:05 Types of SQL Commands 0:18:19 How to install MYSQL on Windows? 0:25:40 MYSQL built-in functions Explained 1:04:54 How Group by and Having Clauses Work? 1:07:23 Practical demonstration of Group by and having Clause in MySQL 1:35:39 What are Joins in SQL? 1:38:00 What is an Inner Join? 1:54:16 What is Left Join? 1:58:40 What is the Right Join? 2:07:03 What is a Full outer Join? 2:10:42 What is a Subquery? 2:32:25 Triggers in SQL Explained 3:13:30 What are Stored procedures in SQL? 3:37:53 How to use Views in SQL? 4:01:46 How to use SQL with python 4:14:50 Establishing a connection with SQL Database using Python 4:18:02 How to create SQL tables using python 4:26:25 Inserting and Updating data using Python 4:32:30 Querying tables using SQl commands with python 4:49:20 What is PostgreSQL? 5:06:22 How to insert records in PostgreSQL? 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 #SQLTutorialForBeginners #SQLFullCourse #SQLCourseForBeginners #AdvancedSQL #SQL #LearnSQLForBeginners #SQLForBeginners #SQLTutorial #SQLCourse #SQLTraining #LearnSQLIn8Hours #SQLStructuredQueryLanguage #SQLForBeginners #Simplilearn ➡️ 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=SQLFSJun15&utm_medium=Descritption&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQLFSJun15&utm_medium=Descritption&utm_source=youtube 🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688

detail
{'title': 'SQL Full Course | SQL For Beginners | Mysql Full Course | SQL Training | Simplilearn', 'heatmap': [], 'summary': 'This sql full course provides comprehensive training on sql fundamentals, covering popular commands, functions, joins, triggers, stored procedures, and python integration, with practical examples and demonstrations using mysql workbench. it also introduces database fundamentals, er diagrams, mysql server installation, and covers string functions, data analysis, subqueries, triggers, views, window functions, and sql operations. the course further extends to postgresql, sql query techniques, practical examples, and sql concepts, with emphasis on practical applications and quantifiable data.', 'chapters': [{'end': 234.373, 'segs': [{'end': 94.184, 'src': 'embed', 'start': 59.212, 'weight': 0, 'content': [{'end': 62.894, 'text': 'You will see how to use the top numerical and text functions in SQL.', 'start': 59.212, 'duration': 3.682}, {'end': 68.498, 'text': 'Next, we will cover how to use GROUP BY and HAVING in SQL followed by JOINS.', 'start': 63.795, 'duration': 4.703}, {'end': 73.581, 'text': 'So you will learn the different types of JOIN statements as well as sub-queries in SQL.', 'start': 68.958, 'duration': 4.623}, {'end': 79.225, 'text': 'Moving further, we will understand triggers, stored procedures and window functions in MySQL.', 'start': 73.941, 'duration': 5.284}, {'end': 85.296, 'text': 'You will get an idea about another important topic that is to write SQL queries with Python on Jupyter Notebook.', 'start': 79.952, 'duration': 5.344}, {'end': 94.184, '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': 85.837, 'duration': 8.347}], 'summary': 'Learn sql functions, group by, joins, triggers, stored procedures, and postgres sql.', 'duration': 34.972, 'max_score': 59.212, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU59212.jpg'}], 'start': 12.196, 'title': 'Sql and database fundamentals', 'summary': 'Provides a comprehensive full course video on sql, covering basics, popular commands, functions, group by, joins, triggers, stored procedures, python integration, and postgres sql. it also introduces databases, emphasizing dbms, relational database structure, and popular databases like mysql, oracle, mongodb, sql server, cassandra, and postgresql, along with an overview of sql and table structures.', 'chapters': [{'end': 117.538, 'start': 12.196, 'title': 'Sql full course by simply learn', 'summary': 'Covers a comprehensive full course video on sql, including learning sql basics, popular commands, functions, group by, joins, triggers, stored procedures, python integration, postgres sql, becoming a sql developer, and sql interview questions.', 'duration': 105.342, 'highlights': ['The chapter covers a comprehensive full course video on SQL, including learning SQL basics, popular commands, functions, GROUP BY, JOINS, triggers, stored procedures, Python integration, Postgres SQL, becoming a SQL developer, and SQL interview questions.', 'The video will help in learning SQL commands using MySQL, PostgreSQL, and SQL Server.', 'The course includes understanding entity relationship diagrams, installing MySQL Workbench, and writing SQL queries with Python on Jupyter Notebook.', 'We will learn about databases, how data is stored in relational databases, and some popular databases.', 'The chapter will also provide an extensive hands-on demonstration of Postgres SQL and cover the top SQL interview questions frequently asked in interviews.']}, {'end': 234.373, 'start': 118.198, 'title': 'Introduction to databases and sql', 'summary': 'Introduces the concept of databases, emphasizing the role of dbms and the structure of relational databases, and also covers popular databases such as mysql, oracle, mongodb, microsoft sql server, apache cassandra, and postgresql, along with an overview of sql and table structures.', 'duration': 116.175, 'highlights': ['A database is an organized collection of structured information or data stored electronically, with relational databases utilizing tables for data storage and retrieval.', 'Popular databases include MySQL, Oracle, MongoDB, Microsoft SQL Server, Apache Cassandra, and PostgreSQL, with SQL being the domain specific language for communication with databases.', 'SQL commands facilitate storage, processing, analysis, and manipulation of databases, while table structures in a database consist of columns (fields) and rows (records or tuples).']}], 'duration': 222.177, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU12196.jpg', 'highlights': ['The chapter covers a comprehensive full course video on SQL, including learning SQL basics, popular commands, functions, GROUP BY, JOINS, triggers, stored procedures, Python integration, Postgres SQL, becoming a SQL developer, and SQL interview questions.', 'The video will help in learning SQL commands using MySQL, PostgreSQL, and SQL Server.', 'A database is an organized collection of structured information or data stored electronically, with relational databases utilizing tables for data storage and retrieval.', 'Popular databases include MySQL, Oracle, MongoDB, Microsoft SQL Server, Apache Cassandra, and PostgreSQL, with SQL being the domain specific language for communication with databases.']}, {'end': 1087.738, 'segs': [{'end': 821.565, 'src': 'embed', 'start': 791.349, 'weight': 7, 'content': [{'end': 793.911, 'text': "Let's see the second one, one-to-many relationship.", 'start': 791.349, 'duration': 2.562}, {'end': 802.016, 'text': 'When a single element of an entity is associated with more than one element of another entity is called one-to-many relationship.', 'start': 794.511, 'duration': 7.505}, {'end': 809.34, 'text': 'In the below example, a customer can place many orders, but a particular order cannot be placed by many customers.', 'start': 802.636, 'duration': 6.704}, {'end': 813.162, 'text': 'Now, we will have a look at many-to-one relationship.', 'start': 810.241, 'duration': 2.921}, {'end': 821.565, 'text': 'When more than one element of an entity is related to a single element of another entity It is called many to one relationship.', 'start': 813.803, 'duration': 7.762}], 'summary': 'Explained one-to-many and many-to-one relationships in databases.', 'duration': 30.216, 'max_score': 791.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU791349.jpg'}, {'end': 891.893, 'src': 'embed', 'start': 856.702, 'weight': 1, 'content': [{'end': 859.143, 'text': 'First, identify all the entities in the system.', 'start': 856.702, 'duration': 2.441}, {'end': 863.366, 'text': 'Embed all the entities in a rectangular shape and label them appropriately.', 'start': 859.604, 'duration': 3.762}, {'end': 868.03, 'text': 'This could be a customer, a manager, an order, an invoice, a schedule, etc.', 'start': 863.947, 'duration': 4.083}, {'end': 875.098, 'text': 'Identify relationships between entities and connect them using a diamond in the middle illustrating the relationship.', 'start': 869.153, 'duration': 5.945}, {'end': 876.9, 'text': 'Do not connect relationships.', 'start': 875.619, 'duration': 1.281}, {'end': 883.245, 'text': 'Connect attributes with entities and label them appropriately and the attribute should be in oval shape.', 'start': 877.921, 'duration': 5.324}, {'end': 891.893, 'text': 'Assure that each entity only appears a single time and eradicate any redundant entities or relationships in the year diagram.', 'start': 884.066, 'duration': 7.827}], 'summary': 'Identify and label entities, relationships, and attributes in a system diagram, avoiding redundancies and ensuring clarity.', 'duration': 35.191, 'max_score': 856.702, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU856702.jpg'}, {'end': 996.49, 'src': 'embed', 'start': 951.88, 'weight': 0, 'content': [{'end': 954.541, 'text': 'third, we have data control language or dcl.', 'start': 951.88, 'duration': 2.661}, {'end': 959.22, 'text': 'So DCL commands allow you to control access to data within the database.', 'start': 955.495, 'duration': 3.725}, {'end': 967.091, 'text': 'These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users.', 'start': 960.101, 'duration': 6.99}, {'end': 972.278, 'text': 'So we have grant and revoke which are the examples of data control language.', 'start': 968.333, 'duration': 3.945}, {'end': 977.439, 'text': 'Finally, we have something called as Transaction Control Language or TCL.', 'start': 973.177, 'duration': 4.262}, {'end': 981.441, 'text': 'So TCL commands allow the user to manage database transactions.', 'start': 978.14, 'duration': 3.301}, {'end': 984.543, 'text': 'Commit and Rollback are example of TCL.', 'start': 982.142, 'duration': 2.401}, {'end': 987.785, 'text': "Now let's see the basic SQL command structure.", 'start': 985.304, 'duration': 2.481}, {'end': 991.967, 'text': 'So first, we have the SELECT statement.', 'start': 988.905, 'duration': 3.062}, {'end': 996.49, 'text': 'So here you specify the various column names that you want to fetch from the table.', 'start': 992.687, 'duration': 3.803}], 'summary': 'Dcl commands control data access and privileges, with examples like grant and revoke. tcl commands manage transactions, with examples like commit and rollback.', 'duration': 44.61, 'max_score': 951.88, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU951880.jpg'}], 'start': 234.954, 'title': 'Understanding sql, er diagrams, and database design', 'summary': "Covers sql features, applications, and compatibility with various database systems, as well as er diagrams' use, components, and real-world applications. it also explains the importance of er diagram in communicating database structure, its components, and relationships, and provides a comprehensive understanding of sql commands, data types, and operators.", 'chapters': [{'end': 450.016, 'start': 234.954, 'title': 'Understanding sql and er diagrams', 'summary': 'Covers the features and applications of sql, including its compatibility with various database systems, efficient data extraction, and client-server architecture support. it also introduces the concept of er diagrams, explaining their use, components, and real-world application.', 'duration': 215.062, 'highlights': ['SQL is extensively used as a client-server language to connect the front-end with the back-end, thus supporting the client-server architecture.', 'SQL lets you access any information stored in a relational database.', 'ER Diagram is mainly a structural design for the database, created using specialized symbols to define the relationship between entities.', 'The chapter also introduces the concept of ER Diagrams, explaining their use, components, and real-world application.']}, {'end': 1087.738, 'start': 450.897, 'title': 'Utilizing er diagram for database design', 'summary': 'Explains the importance of er diagram in communicating the database structure, its components, and relationships, and provides a comprehensive understanding of sql commands, data types, and operators.', 'duration': 636.841, 'highlights': ['The logical structure of the database provided by ER diagram communicates the landscape of business to different teams in the company, which is eventually needed to support the business. 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. Database designers can use ER Diagrams as a blueprint which reduces complexity and helps them save time to build databases quickly. ER Diagrams helps you identify the entities that exist in a system and the relationships between those entities. After knowing its uses, now we should get familiar with the symbols used in ER diagram.', 'There are three main components of VIA diagram, entity, attribute and relationship. Entities have weak entity, attributes are further classified into key attribute, composite attribute, multivalued attribute and derived attribute. Relationships are also classified into one-to-one relationships, one-to-many relationships, many-to-one relationships and many-to-many relationships.', 'First, identify all the entities in the system. Embed all the entities in a rectangular shape and label them appropriately. This could be a customer, a manager, an order, an invoice, a schedule, etc. Identify relationships between entities and connect them using a diamond in the middle illustrating the relationship. Do not connect relationships. Connect attributes with entities and label them appropriately and the attribute should be in oval shape. Assure that each entity only appears a single time and eradicate any redundant entities or relationships in the year diagram. Make sure your ER diagram supports all the data provided to design the database. Make effective use of colors to highlight key areas in your diagrams.', "Finally, we have something called as Transaction Control Language or TCL. So TCL commands allow the user to manage database transactions. Commit and Rollback are examples of TCL. Now let's see the basic SQL command structure. So first, we have the SELECT statement. So here you specify the various column names that you want to fetch from the table. We write the table name using the FROM statement. Next, We have the WHERE clause to filter out our table based on some conditions. So you can see here WHERE condition 1, condition 2 and so on. Then we have the GROUP BY clause that takes various column names. So you can write GROUP BY column 1, column 2 and so on. Next, we have the HAVING clause to filter out tables based on groups. Finally, we have the ORDER BY clause to filter out the result in ascending or descending order."]}], 'duration': 852.784, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU234954.jpg', 'highlights': ['SQL is extensively used as a client-server language to connect the front-end with the back-end, thus supporting the client-server architecture.', 'ER Diagram is mainly a structural design for the database, created using specialized symbols to define the relationship between entities.', 'The logical structure of the database provided by ER diagram communicates the landscape of business to different teams in the company, which is eventually needed to support the business.', 'Database designers can use ER Diagrams as a blueprint which reduces complexity and helps them save time to build databases quickly.', 'There are three main components of VIA diagram, entity, attribute and relationship. Entities have weak entity, attributes are further classified into key attribute, composite attribute, multivalued attribute and derived attribute.', 'TCL commands allow the user to manage database transactions. Commit and Rollback are examples of TCL.', 'SQL lets you access any information stored in a relational database.', 'ER Diagrams helps you identify the entities that exist in a system and the relationships between those entities.', 'The chapter also introduces the concept of ER Diagrams, explaining their use, components, and real-world application.', 'The chapter provides a comprehensive understanding of SQL commands, data types, and operators.']}, {'end': 3020.525, 'segs': [{'end': 1370.316, 'src': 'embed', 'start': 1314.397, 'weight': 3, 'content': [{'end': 1319.262, 'text': 'now we will connect by clicking on the root user.', 'start': 1314.397, 'duration': 4.865}, {'end': 1322.906, 'text': 'it will ask for a password, enter the password and it will connect successfully.', 'start': 1319.262, 'duration': 3.644}, {'end': 1349.771, 'text': 'Now the webbench has started.', 'start': 1348.131, 'duration': 1.64}, {'end': 1351.912, 'text': 'Now we will just connect the server.', 'start': 1350.652, 'duration': 1.26}, {'end': 1355.152, 'text': 'So first we will open command prompt.', 'start': 1353.192, 'duration': 1.96}, {'end': 1367.375, 'text': 'Now we will reach the path where mysql files are present.', 'start': 1364.654, 'duration': 2.721}, {'end': 1370.316, 'text': 'We will go to this PC.', 'start': 1369.135, 'duration': 1.181}], 'summary': 'Connecting as root user, entering password, starting webbench, and accessing mysql files on pc.', 'duration': 55.919, 'max_score': 1314.397, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU1314397.jpg'}, {'end': 1653.854, 'src': 'embed', 'start': 1624.499, 'weight': 0, 'content': [{'end': 1629.703, 'text': "So I'll use the command use world, which is the database name.", 'start': 1624.499, 'duration': 5.204}, {'end': 1631.684, 'text': 'Now let me run it.', 'start': 1630.883, 'duration': 0.801}, {'end': 1634.646, 'text': "So currently I'm using the world database.", 'start': 1632.084, 'duration': 2.562}, {'end': 1641.711, 'text': 'So to display the tables that are present in the world database, I can use the show command and write show tables.', 'start': 1635.366, 'duration': 6.345}, {'end': 1649.51, 'text': "give a semicolon and I'll hit ctrl enter this time to run it alright.", 'start': 1644.065, 'duration': 5.445}, {'end': 1653.854, 'text': 'so you can see the tables that are present inside this world database.', 'start': 1649.51, 'duration': 4.344}], 'summary': "Display tables in world database using 'show tables' command.", 'duration': 29.355, 'max_score': 1624.499, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU1624499.jpg'}, {'end': 2090.627, 'src': 'embed', 'start': 2037.222, 'weight': 1, 'content': [{'end': 2041.603, 'text': 'then we have mary, this dwayne, sarah and amy.', 'start': 2037.222, 'duration': 4.381}, {'end': 2042.644, 'text': 'all right.', 'start': 2041.603, 'duration': 1.041}, {'end': 2051.053, 'text': 'so let me go ahead and run this, so this will help you insert the values in the table that we have created.', 'start': 2042.644, 'duration': 8.409}, {'end': 2054.295, 'text': 'You can see we have successfully inserted six records.', 'start': 2051.793, 'duration': 2.502}, {'end': 2060.783, 'text': 'Now to display the records, let me use the SELECT statement.', 'start': 2055.638, 'duration': 5.145}, {'end': 2064.347, 'text': "So I'm using SELECT star from EMP underscore details.", 'start': 2061.043, 'duration': 3.304}, {'end': 2070.456, 'text': 'If I run this, you can see my table here and the values it has.', 'start': 2065.933, 'duration': 4.523}, {'end': 2078.92, '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': 2071.196, 'duration': 7.724}, {'end': 2084.685, 'text': "Moving ahead, now let's say you want to see the unique city names present in the table.", 'start': 2080.083, 'duration': 4.602}, {'end': 2090.627, 'text': 'So in this case, you can use the distinct keyword along with the column name in the select statement.', 'start': 2085.725, 'duration': 4.902}], 'summary': 'Six records successfully inserted, unique city names displayed using distinct keyword.', 'duration': 53.405, 'max_score': 2037.222, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU2037222.jpg'}], 'start': 1087.758, 'title': 'Mysql workbench and server installation, sql intro, and operations', 'summary': 'Covers the process of installing mysql workbench, mysql server installation, creating databases and tables, using sql aggregate functions, selecting and filtering operations, and basic sql operations and functions.', 'chapters': [{'end': 1203.151, 'start': 1087.758, 'title': 'Learning mysql workbench installation', 'summary': 'Covers the process of installing mysql workbench, including visiting the official oracle website, downloading the mysql installer for windows, and selecting and installing the mysql server, mysql shell, and mysql workbench.', 'duration': 115.393, 'highlights': ['Visiting the official Oracle website mysql.com and downloading the MySQL Installer for Windows.', 'Selecting and installing the MySQL server, MySQL shell, and MySQL Workbench.']}, {'end': 1752.534, 'start': 1204.332, 'title': 'Mysql server installation and configuration', 'summary': 'Outlines the installation and configuration process for mysql server, including setting up the server, connecting to the server, and running basic sql commands, with a focus on default settings and commands such as show databases, show tables, select, and describe, showcasing the process of server installation and database management.', 'duration': 548.202, 'highlights': ['The installation and configuration process for MySQL server is detailed, including setting up the server, executing the configuration steps, and launching MySQL Workbench and MySQL Shell.', 'Connecting to the MySQL server using root user and running commands in MySQL Workbench, such as show databases and show tables, is explained with practical demonstrations.', 'Running basic SQL commands, including show databases, show tables, select, and describe, to manage databases and tables is illustrated, covering aspects such as displaying existing databases, tables, table rows, and table structure.']}, {'end': 2037.222, 'start': 1752.534, 'title': 'Sql intro: creating database and table', 'summary': "Covers the creation of a sql database named 'sql intro' and a table 'employee details' with columns for employee name, age, gender, date of join, city, and salary, and demonstrates adding data to the table.", 'duration': 284.688, 'highlights': ["The chapter covers the creation of a SQL database named 'sql intro' and a table 'employee details' with columns for employee name, age, gender, date of join, city, and salary, and demonstrates adding data to the table.", "The command 'create database' is used to create a new database named 'sql_intro' in SQL.", "The 'create table' command is utilized to create a table named 'employee_details' within the 'sql_intro' database, with columns for employee details such as name, age, gender, date of join, city, and salary, along with their respective data types and constraints.", "The 'insert into' command is employed to add records to the 'employee_details' table, with examples including employee names, ages, genders, dates of join, cities, and salaries."]}, {'end': 2273.185, 'start': 2037.222, 'title': 'Sql aggregate functions', 'summary': 'Covers inserting records into a table, displaying records, printing distinct city names, using inbuilt aggregate functions such as count, sum, and average to retrieve quantifiable data, and using alias names for readability.', 'duration': 235.963, 'highlights': ['The chapter covers using inbuilt aggregate functions in SQL such as count, sum, and average to retrieve quantifiable data.', 'Instructions on inserting records into a table and displaying records, with a successful insertion of six records and a demonstration of displaying the table values.', 'Demonstration of printing distinct city names from a table using the select distinct statement, resulting in the retrieval of five unique city names.', 'Explanation of using alias names for readability, with an example of giving an alias to the resultant output of the count function.']}, {'end': 2544.427, 'start': 2273.185, 'title': 'Sql select and filter operations', 'summary': 'Covers selecting specific columns using the select statement, filtering rows with the where clause based on conditions such as age and gender, utilizing or and in operators for multiple conditions, and using the between operator to select values within a given range.', 'duration': 271.242, 'highlights': ['SQL provides the BETWEEN operator that selects values within a given range.', 'Using the IN operator to specify multiple conditions in a SQL query.', 'The OR operator in SQL for displaying records based on multiple conditions.', 'Utilizing the WHERE clause in SQL to filter rows based on specific conditions.', 'Selecting specific columns using the SELECT statement in SQL.']}, {'end': 3020.525, 'start': 2544.987, 'title': 'Sql basic operations and functions', 'summary': 'Covers sql operations such as selecting employees based on date of join and using and operator, grouping employees by gender to find total salary, sorting records in ascending and descending order based on salary, performing basic operations like addition and subtraction, and using inbuilt functions like length, repeat, upper, lower, cur, day, and now.', 'duration': 475.538, 'highlights': ['Grouping employees by gender to find total salary using the group by clause', 'Sorting records in descending order based on salary using the DESC keyword', 'Selecting employees based on date of join and using the AND operator to specify multiple conditions', 'Performing basic operations like addition and subtraction using the select statement', 'Using inbuilt functions like length, repeat, upper, lower, cur, day, and now to manipulate text, strings, and date values']}], 'duration': 1932.767, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU1087758.jpg', 'highlights': ["Creating a SQL database 'sql intro' and a table 'employee details' with columns for employee name, age, gender, date of join, city, and salary.", 'Installing MySQL server, MySQL shell, and MySQL Workbench from the official Oracle website mysql.com.', 'Using inbuilt aggregate functions in SQL such as count, sum, and average to retrieve quantifiable data.', 'Grouping employees by gender to find total salary using the group by clause.', 'Connecting to the MySQL server using root user and running commands in MySQL Workbench, such as show databases and show tables.']}, {'end': 3779.896, 'segs': [{'end': 3253.344, 'src': 'embed', 'start': 3219.462, 'weight': 0, 'content': [{'end': 3222.943, 'text': 'you can use the character length function.', 'start': 3219.462, 'duration': 3.481}, {'end': 3226.143, 'text': "I'll write select.", 'start': 3222.943, 'duration': 3.2}, {'end': 3241.366, 'text': "Use the function character length and I'm again going to pass in my String as India, as, let's say, total length.", 'start': 3226.143, 'duration': 15.223}, {'end': 3242.506, 'text': "Let's run it.", 'start': 3241.366, 'duration': 1.14}, {'end': 3250.742, 'text': "this time I'm going to hit ctrl enter to run my SQL command and there you go, it has given us the right result, which is 5,", 'start': 3242.506, 'duration': 8.236}, {'end': 3253.344, 'text': 'because India has 5 characters in it.', 'start': 3250.742, 'duration': 2.602}], 'summary': "Using character length function in sql, 'india' has 5 characters.", 'duration': 33.882, 'max_score': 3219.462, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU3219462.jpg'}, {'end': 3392.436, 'src': 'embed', 'start': 3291.85, 'weight': 2, 'content': [{'end': 3296.653, 'text': 'it has given me total 20 rows of information.', 'start': 3291.85, 'duration': 4.803}, {'end': 3298.274, 'text': 'this is not readable.', 'start': 3296.653, 'duration': 1.621}, {'end': 3306.252, 'text': 'actually, let me also display these student names so that we can compare their length.', 'start': 3298.274, 'duration': 7.978}, {'end': 3313.298, 'text': "all right, I'll run this again and now you can see the result.", 'start': 3306.252, 'duration': 7.046}, {'end': 3319.363, 'text': 'so Joseph has six characters, Niles has six, Vipul has five, Anubhav has seven.', 'start': 3313.298, 'duration': 6.065}, {'end': 3330.272, 'text': 'similarly, if you see, Akshay has six, Tanishq has seven, Raghav has six, Cummins has seven, Rabada has six, so on and so forth.', 'start': 3319.363, 'duration': 10.909}, {'end': 3338.412, 'text': 'now, instead of using this character length, you can also use the function char length.', 'start': 3330.272, 'duration': 8.14}, {'end': 3339.673, 'text': 'it will work the same way.', 'start': 3338.412, 'duration': 1.261}, {'end': 3341.913, 'text': "let's see the result.", 'start': 3339.673, 'duration': 2.24}, {'end': 3342.513, 'text': 'there you go.', 'start': 3341.913, 'duration': 0.6}, {'end': 3344.373, 'text': 'it has given us the same result.', 'start': 3342.513, 'duration': 1.86}, {'end': 3348.494, 'text': 'you can either use character length or char length.', 'start': 3344.373, 'duration': 4.121}, {'end': 3351.655, 'text': "now there's another very interesting function called concat.", 'start': 3348.494, 'duration': 3.161}, {'end': 3355.735, 'text': 'so the concat function adds two or more expressions together.', 'start': 3351.655, 'duration': 4.08}, {'end': 3363.457, 'text': "let's say, i'll write select, we'll use the function concatenate.", 'start': 3355.735, 'duration': 7.722}, {'end': 3373.69, 'text': "the function is actually concat and I'm going to pass in my string values.", 'start': 3363.457, 'duration': 10.233}, {'end': 3381.538, 'text': "let's say, India is in Asia.", 'start': 3373.69, 'duration': 7.848}, {'end': 3388.693, 'text': "let's run this and see our result.", 'start': 3385.911, 'duration': 2.782}, {'end': 3392.436, 'text': 'you can see here it has concatenated everything.', 'start': 3388.693, 'duration': 3.743}], 'summary': 'The transcript demonstrates the use of character length, char length, and concat functions in sql, with specific examples and results provided.', 'duration': 100.586, 'max_score': 3291.85, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU3291850.jpg'}], 'start': 3021.145, 'title': 'Mysql string functions', 'summary': 'Covers the basics of mysql, database and table creation, along with string functions like concat, reverse, replace, and trim, with usage examples and quantifiable data.', 'chapters': [{'end': 3348.494, 'start': 3021.145, 'title': 'Mysql intro & string functions', 'summary': 'Covers the basics of mysql including database creation, table creation, record insertion, and sql operations, followed by a detailed explanation of string functions in mysql, such as converting case and finding the length of a string.', 'duration': 327.349, 'highlights': ['The chapter covers the basics of MySQL including database creation, table creation, record insertion, and SQL operations.', 'The detailed explanation of string functions in MySQL, such as converting case and finding the length of a string.']}, {'end': 3779.896, 'start': 3348.494, 'title': 'Mysql string functions', 'summary': 'Covers the usage of concat, reverse, replace, left trim, right trim, and trim functions in mysql, demonstrating their usage and effects with string data, along with examples and quantifiable data.', 'duration': 431.402, 'highlights': ['The concat function adds two or more expressions together, allowing for the concatenation of string values and the creation of concatenated columns from a table, demonstrated through examples with quantifiable results.', 'The reverse function returns a string with characters printed in reverse order, applicable to individual strings as well as columns in a table, illustrated with examples and quantifiable results.', 'The replace function replaces all occurrences of a substring within a string with a new substring, demonstrated through an example with quantifiable results, showcasing the correct replacement of the specified word within the input string.', 'The left trim function removes leading space characters from a string, showcasing its functionality through examples and the quantifiable effect of removing leading spaces from a string.', 'The right trim function removes trailing space characters from a string, exemplified through examples and the quantifiable effect of removing trailing spaces from a string.', 'The trim function deletes both leading and trailing spaces from a string, illustrated through examples and the demonstration of its functionality in removing both leading and trailing spaces.']}], 'duration': 758.751, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU3021145.jpg', 'highlights': ['The concat function adds two or more expressions together, allowing for the concatenation of string values and the creation of concatenated columns from a table, demonstrated through examples with quantifiable results.', 'The reverse function returns a string with characters printed in reverse order, applicable to individual strings as well as columns in a table, illustrated with examples and quantifiable results.', 'The replace function replaces all occurrences of a substring within a string with a new substring, demonstrated through an example with quantifiable results, showcasing the correct replacement of the specified word within the input string.', 'The detailed explanation of string functions in MySQL, such as converting case and finding the length of a string.', 'The chapter covers the basics of MySQL including database creation, table creation, record insertion, and SQL operations.', 'The left trim function removes leading space characters from a string, showcasing its functionality through examples and the quantifiable effect of removing leading spaces from a string.', 'The right trim function removes trailing space characters from a string, exemplified through examples and the quantifiable effect of removing trailing spaces from a string.', 'The trim function deletes both leading and trailing spaces from a string, illustrated through examples and the demonstration of its functionality in removing both leading and trailing spaces.']}, {'end': 4648.53, 'segs': [{'end': 3962.38, 'src': 'embed', 'start': 3934.948, 'weight': 5, 'content': [{'end': 3938.869, 'text': 'Now, below you can see the syntax of GROUP BY.', 'start': 3934.948, 'duration': 3.921}, {'end': 3945.732, 'text': 'So first we have the SELECT statement and then followed by the column names that we want to select.', 'start': 3938.869, 'duration': 6.863}, {'end': 3955.895, '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': 3945.732, 'duration': 10.163}, {'end': 3958.396, 'text': 'finally we have the ORDER BY and the column names.', 'start': 3955.895, 'duration': 2.501}, {'end': 3962.38, 'text': 'now here is an example of the group by clause.', 'start': 3959.318, 'duration': 3.062}], 'summary': 'Explains syntax of group by with select, from, where, group by, and order by.', 'duration': 27.432, 'max_score': 3934.948, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU3934948.jpg'}, {'end': 4047.854, 'src': 'embed', 'start': 4020.329, 'weight': 7, 'content': [{'end': 4027.693, 'text': 'so here in the output you can see we have the department names and the average salary of the employees in each department.', 'start': 4020.329, 'duration': 7.364}, {'end': 4033.176, 'text': 'now let me take you to my mysql workbench, where we will implement, group by and solve specific problems.', 'start': 4027.693, 'duration': 5.483}, {'end': 4037.999, 'text': 'okay, so i am on my mysql workbench, so let me make my connection.', 'start': 4033.176, 'duration': 4.823}, {'end': 4047.854, 'text': "first i'll enter the password, so this will open my SQL editor.", 'start': 4037.999, 'duration': 9.855}], 'summary': 'Analyzing department names and average employee salaries in mysql workbench', 'duration': 27.525, 'max_score': 4020.329, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4020329.jpg'}, {'end': 4111.893, 'src': 'embed', 'start': 4080.454, 'weight': 3, 'content': [{'end': 4085.217, 'text': 'Now you can check the tables that are present in SQL underscore intro database.', 'start': 4080.454, 'duration': 4.763}, {'end': 4092.341, 'text': 'If I write show tables, you can see the list of tables that are already present in this database.', 'start': 4086.858, 'duration': 5.483}, {'end': 4097.944, 'text': 'To do our demo and understand group by as well as having, let me first create an employee table.', 'start': 4092.862, 'duration': 5.082}, {'end': 4103.348, 'text': "So I'll write create table employees.", 'start': 4099.166, 'duration': 4.182}, {'end': 4111.893, 'text': "Next, I'll give my column name as employee underscore ID, which is the ID for each employee.", 'start': 4104.469, 'duration': 7.424}], 'summary': 'Creating an employee table in sql intro database.', 'duration': 31.439, 'max_score': 4080.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4080454.jpg'}, {'end': 4444.081, 'src': 'embed', 'start': 4397.809, 'weight': 0, 'content': [{'end': 4400.05, 'text': 'So for this, you need to use the group by clause.', 'start': 4397.809, 'duration': 2.241}, {'end': 4404.553, 'text': "I'll give a comment here.", 'start': 4402.912, 'duration': 1.641}, {'end': 4410.216, 'text': 'I want to find the average age in each department.', 'start': 4404.833, 'duration': 5.383}, {'end': 4418.789, 'text': "So I'll write select department comma.", 'start': 4412.245, 'duration': 6.544}, {'end': 4430.636, 'text': "I'll write average of age from employees group by department.", 'start': 4420.01, 'duration': 10.626}, {'end': 4444.081, 'text': 'Now, if I run this, you can see here we have our seven departments on the left and on the right.', 'start': 4433.737, 'duration': 10.344}], 'summary': 'Use group by clause to find average age in each department.', 'duration': 46.272, 'max_score': 4397.809, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4397809.jpg'}, {'end': 4619.237, 'src': 'embed', 'start': 4574.228, 'weight': 2, 'content': [{'end': 4587.358, 'text': "I'll write select count of, let's say, employee ID and I want to know the city as well from employees.", 'start': 4574.228, 'duration': 13.13}, {'end': 4597.035, 'text': 'group by city, and next you can use the order by clause.', 'start': 4589.22, 'duration': 7.815}, {'end': 4609.471, 'text': "I'll write order by count of employee id and i'll write desc, which stands for descending.", 'start': 4597.035, 'duration': 12.436}, {'end': 4619.237, 'text': 'if i run this query, you can see here on the left you have the count of employees and on the right you can see the city names.', 'start': 4609.471, 'duration': 9.766}], 'summary': 'Query to count employees by city and order by count in descending order.', 'duration': 45.009, 'max_score': 4574.228, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4574228.jpg'}], 'start': 3779.896, 'title': 'Mysql string functions and sql group by', 'summary': 'Covers mysql string functions such as trim, position, and ascii, with examples. it also introduces sql group by clause, explaining syntax, usage, and demonstrating implementation for finding average employee salaries by department in mysql workbench.', 'chapters': [{'end': 3877.71, 'start': 3779.896, 'title': 'Mysql string functions', 'summary': 'Explains the usage of mysql string functions including trim, position, and ascii, demonstrating their applications and outputs with examples.', 'duration': 97.814, 'highlights': ["The position function returns the position of the first occurrence of a substring in a string, such as finding 'fruit' in the string 'orange is a fruit', yielding the result of 13th position.", "The ASCII function returns the ASCII value for a specific character, like the ASCII value of the letter 'a' being 97.", "The trim function removes leading and trailing spaces from a string, demonstrated by trimming the string 'india' to yield a length of five characters."]}, {'end': 4648.53, 'start': 3877.71, 'title': 'Introduction to sql group by and having', 'summary': 'Introduces the group by clause in sql, explaining its syntax and usage, and demonstrates its implementation by finding the average salary of employees in each department and other aggregate functions on a sample employee table in mysql workbench.', 'duration': 770.82, 'highlights': ['The group by clause in SQL groups records into summary rows and returns one record for each group, computing aggregate functions for the resulting group.', 'An example of the group by clause is demonstrated by finding the average salary of employees in each department using the AVG function and the department column.', 'The process of creating an employee table in MySQL Workbench is explained, including defining column names, data types, and inserting records.', 'Usage of aggregate functions such as AVG, COUNT, and SUM with the group by clause to find average age, total salary, and total number of employees in each department and city is demonstrated.', 'Demonstration of using the order by clause with group by to order the results based on employee counts in descending order for each city.']}], 'duration': 868.634, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU3779896.jpg', 'highlights': ['The group by clause in SQL groups records into summary rows and returns one record for each group, computing aggregate functions for the resulting group.', 'Demonstration of using the order by clause with group by to order the results based on employee counts in descending order for each city.', 'Usage of aggregate functions such as AVG, COUNT, and SUM with the group by clause to find average age, total salary, and total number of employees in each department and city is demonstrated.', "The position function returns the position of the first occurrence of a substring in a string, such as finding 'fruit' in the string 'orange is a fruit', yielding the result of 13th position.", "The ASCII function returns the ASCII value for a specific character, like the ASCII value of the letter 'a' being 97.", "The trim function removes leading and trailing spaces from a string, demonstrated by trimming the string 'india' to yield a length of five characters.", 'An example of the group by clause is demonstrated by finding the average salary of employees in each department using the AVG function and the department column.', 'The process of creating an employee table in MySQL Workbench is explained, including defining column names, data types, and inserting records.']}, {'end': 5694.819, 'segs': [{'end': 4674.967, 'src': 'embed', 'start': 4648.53, 'weight': 2, 'content': [{'end': 4656.777, 'text': 'suppose we want to find the number of employees that joined the company each year, we can use the year function on the date of joining column.', 'start': 4648.53, 'duration': 8.247}, {'end': 4661.182, 'text': 'then we can count the employee IDs and group the result by each year.', 'start': 4657.441, 'duration': 3.741}, {'end': 4663.663, 'text': 'So let me show you how to do it.', 'start': 4662.363, 'duration': 1.3}, {'end': 4666.584, 'text': "So I'll write select.", 'start': 4665.364, 'duration': 1.22}, {'end': 4672.266, 'text': "I'm going to extract year from the date of join column.", 'start': 4667.925, 'duration': 4.341}, {'end': 4674.967, 'text': "I'll give an alias as year.", 'start': 4673.126, 'duration': 1.841}], 'summary': 'Using the year function to count employees joining each year.', 'duration': 26.437, 'max_score': 4648.53, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4648530.jpg'}, {'end': 4984.843, 'src': 'embed', 'start': 4955.059, 'weight': 3, 'content': [{'end': 4965.768, 'text': 'so here you can see we have the two product IDs 121 and 123, and here you can see the revenue that was generated from these two products.', 'start': 4955.059, 'duration': 10.709}, {'end': 4975.658, 'text': "alright, now let's say we have to find the total profit that was made from both the products 1 to 1 and 1 to 3.", 'start': 4965.768, 'duration': 9.89}, {'end': 4977.779, 'text': "so for that I'll create another table.", 'start': 4975.658, 'duration': 2.121}, {'end': 4982.581, 'text': 'now this table will have the cost price of both the products.', 'start': 4977.779, 'duration': 4.802}, {'end': 4984.843, 'text': 'so let me create the table.', 'start': 4982.581, 'duration': 2.262}], 'summary': 'Analyzing product ids 121 and 123, revenue, and total profit.', 'duration': 29.784, 'max_score': 4955.059, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4955059.jpg'}, {'end': 5156.085, 'src': 'embed', 'start': 5019.041, 'weight': 4, 'content': [{'end': 5023.783, 'text': 'now let me insert a few values into the c product table.', 'start': 5019.041, 'duration': 4.742}, {'end': 5029.385, 'text': "so i'll write insert into c underscore product.", 'start': 5023.783, 'duration': 5.602}, {'end': 5034.927, 'text': "i'll give my values for one to one.", 'start': 5029.385, 'duration': 5.542}, {'end': 5057.173, 'text': "let's say the Cost price was $270 for each and next we have my product as 123 and let's say the cost price for product 123 was $250..", 'start': 5034.927, 'duration': 22.246}, {'end': 5059.475, 'text': "let's insert these two values.", 'start': 5057.173, 'duration': 2.302}, {'end': 5064.499, 'text': "okay. next we'll join our sales table and the product cost table.", 'start': 5059.475, 'duration': 5.024}, {'end': 5070.684, 'text': 'so this will give us the profit that was generated for each of the products.', 'start': 5064.499, 'duration': 6.185}, {'end': 5079.631, 'text': "so i'll write select c dot product underscore id.", 'start': 5070.684, 'duration': 8.947}, {'end': 5088.92, 'text': "comma. i'll write sum S dot sell underscore price.", 'start': 5079.631, 'duration': 9.289}, {'end': 5093.983, 'text': 'now here C and S are alias names.', 'start': 5088.92, 'duration': 5.063}, {'end': 5102.609, 'text': 'so if I subtract my cost price from the selling price that will return the profit that was generated.', 'start': 5093.983, 'duration': 8.626}, {'end': 5108.733, 'text': 'I will multiply this with S dot quantity.', 'start': 5102.609, 'duration': 6.124}, {'end': 5115.067, 'text': 'close the bracket.', 'start': 5113.206, 'duration': 1.861}, {'end': 5123.774, 'text': "I'll give an alias name as profit from sales as S.", 'start': 5115.067, 'duration': 8.707}, {'end': 5127.257, 'text': 'so here S stands for the sales table.', 'start': 5123.774, 'duration': 3.483}, {'end': 5140.007, 'text': "I'm going to use inner join C underscore product table as the alias name should be C,", 'start': 5127.257, 'duration': 12.75}, {'end': 5149.922, 'text': 'where S dot product underscore ID is equal to C dot product underscore ID.', 'start': 5140.007, 'duration': 9.915}, {'end': 5156.085, 'text': 'We are using product underscore ID because this column is the common column to both the tables.', 'start': 5150.563, 'duration': 5.522}], 'summary': 'Inserting values into product table and calculating profit from sales using sql queries.', 'duration': 137.044, 'max_score': 5019.041, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU5019041.jpg'}, {'end': 5533.422, 'src': 'embed', 'start': 5466.969, 'weight': 0, 'content': [{'end': 5474.773, 'text': 'so so the different cities are chicago, seattle and houston, where the total salary was greater than two hundred thousand dollars.', 'start': 5466.969, 'duration': 7.804}, {'end': 5479.035, 'text': 'now, suppose you want to find the departments that have more than two employees.', 'start': 5474.773, 'duration': 4.262}, {'end': 5482.116, 'text': "so let's see how to do it.", 'start': 5479.035, 'duration': 3.081}, {'end': 5491.441, 'text': "i'll write select department comma this time, since i want to find the number of employees, i'm going to use the count function.", 'start': 5482.116, 'duration': 9.325}, {'end': 5504.805, 'text': "I'll write count star as employee underscore count or EMP underscore count, which is my alias name from employees.", 'start': 5492.355, 'duration': 12.45}, {'end': 5518.695, 'text': "Next I'll group it by department having, I'll give my condition count star greater than two.", 'start': 5507.487, 'duration': 11.208}, {'end': 5521.017, 'text': "Let's run this.", 'start': 5520.276, 'duration': 0.741}, {'end': 5533.422, 'text': 'okay, so you have departments such as sales, product tech and IT where there are more than two employees.', 'start': 5522.995, 'duration': 10.427}], 'summary': 'Total salary > $200k in chicago, seattle, houston. departments with >2 employees: sales, product tech, it.', 'duration': 66.453, 'max_score': 5466.969, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU5466969.jpg'}], 'start': 4648.53, 'title': 'Sql data analysis', 'summary': 'Covers using sql to extract employee joining count by year, creating sales and profit tables, and understanding sql having clause. it includes examples such as generating $1100 profit for product id 121 and $840 profit for product id 123, and finding cities with more than two employees.', 'chapters': [{'end': 4751.732, 'start': 4648.53, 'title': 'Employee joining count by year', 'summary': 'Demonstrates using sql to extract the number of employees joining the company each year by applying the year function, counting employee ids, and grouping the result by year, with illustrative data showing the number of employees joining in different years.', 'duration': 103.202, 'highlights': ['Using SQL to extract the number of employees joining the company each year by applying the year function, counting employee IDs, and grouping the result by year, with illustrative data showing the number of employees joining in different years.', 'Illustrative data showing the number of employees that joined the company each year, such as one employee in 2005 and two employees in 2009.', 'Demonstrating the use of group by to join two or more tables together to perform operations.']}, {'end': 5214.667, 'start': 4751.732, 'title': 'Creating sales and profit tables', 'summary': 'Covers creating a sales table with product id, selling price, quantity, and state, inserting values, calculating revenue using sum function and group by, creating a product cost table, joining sales and product cost table to calculate profit, generating $1100 profit for product id 121 and $840 profit for product id 123.', 'duration': 462.935, 'highlights': ['Creating a sales table with product ID, selling price, quantity, and state', 'Calculating revenue using sum function and group by for product IDs 121 and 123', 'Generating $1100 profit for product ID 121 and $840 profit for product ID 123']}, {'end': 5694.819, 'start': 5214.667, 'title': 'Understanding sql having clause', 'summary': 'Explains the having clause in sql, demonstrating its syntax and usage through examples, including finding cities with more than two employees, departments with average salary greater than $75,000, and cities with total salary greater than $200,000.', 'duration': 480.152, 'highlights': ['The having clause in SQL operates on grouped records and returns rows where aggregate function results matched with given conditions only.', 'Demonstrates syntax and usage through examples, including finding cities with more than two employees, departments with average salary greater than $75,000, and cities with total salary greater than $200,000.', 'Shows how to use the having clause along with aggregate functions and the where clause in SQL queries.']}], 'duration': 1046.289, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU4648530.jpg', 'highlights': ['Using SQL to extract the number of employees joining the company each year by applying the year function, counting employee IDs, and grouping the result by year, with illustrative data showing the number of employees joining in different years.', 'Creating a sales table with product ID, selling price, quantity, and state', 'The having clause in SQL operates on grouped records and returns rows where aggregate function results matched with given conditions only.', 'Illustrative data showing the number of employees that joined the company each year, such as one employee in 2005 and two employees in 2009.', 'Calculating revenue using sum function and group by for product IDs 121 and 123', 'Demonstrating the use of group by to join two or more tables together to perform operations.', 'Generating $1100 profit for product ID 121 and $840 profit for product ID 123', 'Demonstrates syntax and usage through examples, including finding cities with more than two employees, departments with average salary greater than $75,000, and cities with total salary greater than $200,000.', 'Shows how to use the having clause along with aggregate functions and the where clause in SQL queries.']}, {'end': 7617.417, 'segs': [{'end': 6365.264, 'src': 'embed', 'start': 6336.302, 'weight': 0, 'content': [{'end': 6340.044, 'text': 'so c dot name is equal to f dot name.', 'start': 6336.302, 'duration': 3.742}, {'end': 6345.467, 'text': 'so based on this name column from both the table, my energon operation will be performed.', 'start': 6340.044, 'duration': 5.423}, {'end': 6348.448, 'text': "so let's just run it.", 'start': 6345.467, 'duration': 2.981}, {'end': 6349.529, 'text': 'there you go.', 'start': 6348.448, 'duration': 1.081}, {'end': 6355.132, 'text': 'so stuart, johnson and hayden are the only three students who are part of both the teams.', 'start': 6349.529, 'duration': 5.603}, {'end': 6361.703, 'text': 'all right, you can also individually select each of the columns from both the tables.', 'start': 6355.132, 'duration': 6.571}, {'end': 6365.264, 'text': "so let's say i write select c dot.", 'start': 6361.703, 'duration': 3.561}], 'summary': 'Energon operation performed based on matching names from tables. stuart, johnson, and hayden are only students in both teams.', 'duration': 28.962, 'max_score': 6336.302, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU6336302.jpg'}, {'end': 6512.706, 'src': 'embed', 'start': 6480.546, 'weight': 3, 'content': [{'end': 6484.304, 'text': "Let's see what we have in product lines.", 'start': 6480.546, 'duration': 3.758}, {'end': 6495.88, 'text': 'if I run it, you see here we have the product line, which is the primary key for this table.', 'start': 6489.898, 'duration': 5.982}, {'end': 6500.182, 'text': 'then we have the textual description for each of the products.', 'start': 6495.88, 'duration': 4.302}, {'end': 6504.343, 'text': 'this is basically some sort of an advertisement.', 'start': 6500.182, 'duration': 4.161}, {'end': 6512.706, 'text': 'all right now, suppose you want to find the product code, the product name and the text description for each of the products.', 'start': 6504.343, 'duration': 8.363}], 'summary': 'Analyzing product lines to retrieve product code, name, and description.', 'duration': 32.16, 'max_score': 6480.546, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU6480546.jpg'}, {'end': 6585.225, 'src': 'embed', 'start': 6547.812, 'weight': 1, 'content': [{'end': 6561.612, 'text': 'I can use using the common key column that is, product line.', 'start': 6547.812, 'duration': 13.8}, {'end': 6567.525, 'text': 'Close the bracket.', 'start': 6566.704, 'duration': 0.821}, {'end': 6568.586, 'text': "I'll give a semicolon.", 'start': 6567.585, 'duration': 1.001}, {'end': 6571.189, 'text': 'And if I run it, there you go.', 'start': 6569.347, 'duration': 1.842}, {'end': 6573.912, 'text': 'So you can see the different product codes.', 'start': 6571.79, 'duration': 2.122}, {'end': 6578.998, 'text': 'Then we have the different product names and the textual description for each of the products.', 'start': 6574.633, 'duration': 4.365}, {'end': 6583.843, 'text': 'So this we did by joining the products table and the product lines table.', 'start': 6579.819, 'duration': 4.024}, {'end': 6585.225, 'text': 'All right.', 'start': 6584.925, 'duration': 0.3}], 'summary': 'Joined products and product lines using common key column, product line.', 'duration': 37.413, 'max_score': 6547.812, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU6547812.jpg'}, {'end': 7324.794, 'src': 'embed', 'start': 7300.646, 'weight': 4, 'content': [{'end': 7307.671, 'text': 'okay, so you can see, i have my customer name selected from the customers table, the phone number of the customer.', 'start': 7300.646, 'duration': 7.025}, {'end': 7310.653, 'text': 'then we have the employee number and the email address.', 'start': 7307.671, 'duration': 2.982}, {'end': 7313.095, 'text': 'so let me run it.', 'start': 7310.653, 'duration': 2.442}, {'end': 7317.858, 'text': "okay, there's some problem, all right, so the table name is customers.", 'start': 7313.095, 'duration': 4.763}, {'end': 7321.06, 'text': "actually, let's run it once again.", 'start': 7317.858, 'duration': 3.202}, {'end': 7324.794, 'text': 'there you go.', 'start': 7323.553, 'duration': 1.241}], 'summary': "Demonstrating customer data retrieval with table name 'customers'.", 'duration': 24.148, 'max_score': 7300.646, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU7300646.jpg'}, {'end': 7617.417, 'src': 'embed', 'start': 7588.604, 'weight': 2, 'content': [{'end': 7591.788, 'text': 'so you have your two columns as manager and employee.', 'start': 7588.604, 'duration': 3.184}, {'end': 7597.461, 'text': 'so for employee, louis bonder, The manager is Gerard Bonder.', 'start': 7591.788, 'duration': 5.673}, {'end': 7604.466, 'text': 'Similarly, if I scroll down, you have there are multiple employees reporting to this particular manager.', 'start': 7598.321, 'duration': 6.145}, {'end': 7613.754, 'text': 'Similarly, we have our manager as Anthony Bow and we have different employees who are reporting to this particular manager and so on.', 'start': 7604.506, 'duration': 9.248}, {'end': 7617.417, 'text': 'All right, moving ahead.', 'start': 7615.956, 'duration': 1.461}], 'summary': 'Multiple employees report to different managers in the organization.', 'duration': 28.813, 'max_score': 7588.604, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU7588604.jpg'}], 'start': 5694.819, 'title': 'Sql joins and their applications', 'summary': 'Covers the basics of sql joins, including different types and their significance in combining data from multiple tables, practical demonstrations using mysql workbench, and examples of inner, left, and right joins, with a focus on retrieving specific data and identifying customers with no orders.', 'chapters': [{'end': 5769.551, 'start': 5694.819, 'title': 'Introduction to sql joins', 'summary': 'Covers the basics of sql joins, including different types and their significance in combining data from multiple tables, as well as practical demonstrations using mysql workbench. it highlights the creation and exploration of sql commands, including distinct, average, group by, and having, along with the process of joining two tables to find profit.', 'duration': 74.732, 'highlights': ['SQL JOINs are used to combine rows of data from two or more tables based on a common field or column between them.', 'Practical demonstrations using MySQL Workbench to learn the basics of joins and different types of joins.', 'Exploration of SQL commands such as distinct, average, group by, and having, along with the process of joining two tables to find profit.']}, {'end': 6413.494, 'start': 5769.851, 'title': 'Understanding sql inner joins', 'summary': 'Explains the concept of sql inner joins, demonstrating their application through examples involving the retrieval of specific data from multiple tables and creation of tables using mysql workbench.', 'duration': 643.643, 'highlights': ['SQL Inner Join', 'Retrieval of Specific Data using Inner Join', 'Demonstration of Inner Join Syntax', 'Creation of Tables using MySQL Workbench']}, {'end': 6843.579, 'start': 6413.494, 'title': 'Database tables and joins', 'summary': 'Covers the exploration of tables like customers, employees, orders, and products in the classic models database, demonstrating the use of select statements and inner joins to retrieve specific information and calculate revenue.', 'duration': 430.085, 'highlights': ['The tables in the classic models database include customers, employees, office details, orders, payments, products, and product lines, each containing specific information.', 'The product table includes information on product names, unique product codes, vendors, descriptions, quantity in stock, buying prices, and MSRP.', 'The process of joining the products and product lines tables using select statements allows the retrieval of product codes, names, and textual descriptions, facilitating comprehensive data access and analysis.', 'An attempt to join the orders, order details, and products tables to calculate revenue encounters syntax errors, requiring debugging and correction of table names.']}, {'end': 7104.238, 'start': 6843.579, 'title': 'Sql left join example', 'summary': 'Discusses the concept of sql left join, demonstrating its syntax and application to retrieve customer names, order numbers, and shipment status from the customers and orders tables, while also highlighting the use of the null operator to identify customers with no orders.', 'duration': 260.659, 'highlights': ['The SQL left join statement returns all the rows from the left table and the matching rows from the right table, as demonstrated with the customers and orders tables, providing insights into customer names, order numbers, and shipment status.', 'The demonstration of the SQL left join includes the retrieval of customer names, order numbers, and shipment status from the customers and orders tables, showcasing the application of the left join syntax and its functionality.', 'The use of the null operator to identify customers with no orders is highlighted, showcasing its application in filtering and retrieving specific data from the tables.']}, {'end': 7617.417, 'start': 7104.238, 'title': 'Sql right joins and self-joins', 'summary': 'Explains sql right joins and self-joins, with examples of joining tables and finding reporting managers using self-joins, involving 24 customers without orders, and demonstrating how to fetch specific columns from two tables and use self-joins to find reporting managers.', 'duration': 513.179, 'highlights': ['The chapter explains SQL right joins and self-joins', 'Involves 24 customers without orders', 'Demonstrates how to fetch specific columns from two tables', 'Shows how to find reporting managers using self-joins']}], 'duration': 1922.598, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU5694819.jpg', 'highlights': ['SQL JOINs are used to combine rows of data from two or more tables based on a common field or column between them.', 'The tables in the classic models database include customers, employees, office details, orders, payments, products, and product lines, each containing specific information.', 'The SQL left join statement returns all the rows from the left table and the matching rows from the right table, as demonstrated with the customers and orders tables, providing insights into customer names, order numbers, and shipment status.', 'Practical demonstrations using MySQL Workbench to learn the basics of joins and different types of joins.', 'The process of joining the products and product lines tables using select statements allows the retrieval of product codes, names, and textual descriptions, facilitating comprehensive data access and analysis.']}, {'end': 8270.425, 'segs': [{'end': 7999.738, 'src': 'embed', 'start': 7970.538, 'weight': 3, 'content': [{'end': 7976.423, 'text': "where I'm using the condition salary equal to, and then I'm passing in my subquery or the inner query.", 'start': 7970.538, 'duration': 5.885}, {'end': 7979.806, 'text': "so first I'm selecting the maximum salary.", 'start': 7976.423, 'duration': 3.383}, {'end': 7982.068, 'text': 'this will return a particular value.', 'start': 7979.806, 'duration': 2.262}, {'end': 7989.694, 'text': 'that is the highest salary from the table, and if you see our table, the highest salary is $115, 000.', 'start': 7982.068, 'duration': 7.626}, {'end': 7993.876, 'text': 'so our query becomes select employee name from employees where salary equal to $115, 000.', 'start': 7989.694, 'duration': 4.182}, {'end': 7999.738, 'text': "so the employee name is Joseph here, and that's the output.", 'start': 7993.876, 'duration': 5.862}], 'summary': 'Using subquery to find employee with highest salary $115,000, output: joseph.', 'duration': 29.2, 'max_score': 7970.538, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU7970538.jpg'}, {'end': 8094.275, 'src': 'embed', 'start': 8065.055, 'weight': 5, 'content': [{'end': 8069.138, 'text': 'so here is an example that we will perform on our mysql workbench.', 'start': 8065.055, 'duration': 4.083}, {'end': 8078.723, 'text': 'so in this example we want to select all the employees who have a salary less than average salary for all the employees.', 'start': 8069.138, 'duration': 9.585}, {'end': 8079.683, 'text': 'this is the output.', 'start': 8078.723, 'duration': 0.96}, {'end': 8083.045, 'text': "so let's do this on my mysql workbench.", 'start': 8079.683, 'duration': 3.362}, {'end': 8085.727, 'text': 'all right, so let me log into my local instance.', 'start': 8083.045, 'duration': 2.682}, {'end': 8088.471, 'text': "I'll give my password.", 'start': 8087.23, 'duration': 1.241}, {'end': 8094.275, 'text': "Okay, so you can see I'm on my MySQL workbench.", 'start': 8091.533, 'duration': 2.742}], 'summary': 'Demonstration of selecting employees with salary less than average using mysql workbench.', 'duration': 29.22, 'max_score': 8065.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8065055.jpg'}, {'end': 8202.893, 'src': 'embed', 'start': 8175.36, 'weight': 1, 'content': [{'end': 8180.942, 'text': 'We have the employee name, the employee ID, age, gender, date of join, department, city and salary.', 'start': 8175.36, 'duration': 5.582}, {'end': 8183.963, 'text': 'So this is the same table that we saw in our slides.', 'start': 8181.522, 'duration': 2.441}, {'end': 8188.345, 'text': "Okay Now for our sub query, I'll write select.", 'start': 8185.184, 'duration': 3.161}, {'end': 8194.987, 'text': 'I want to choose the employee name, the department and the salary.', 'start': 8189.346, 'duration': 5.641}, {'end': 8202.893, 'text': 'there should be a comma here instead of a period.', 'start': 8198.592, 'duration': 4.301}], 'summary': 'Subquery selects employee name, department, and salary from employee table.', 'duration': 27.533, 'max_score': 8175.36, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8175360.jpg'}, {'end': 8270.425, 'src': 'embed', 'start': 8237.939, 'weight': 0, 'content': [{'end': 8242.8, 'text': 'so we have total 12 employees in the table whose salary is less than the average salary.', 'start': 8237.939, 'duration': 4.861}, {'end': 8247.644, 'text': 'now, if you want, you can check the average salary.', 'start': 8242.8, 'duration': 4.844}, {'end': 8252.796, 'text': 'so the average salary is $75, 350.', 'start': 8247.644, 'duration': 5.152}, {'end': 8257.859, 'text': 'now the employees who have a salary less than the average salary.', 'start': 8252.796, 'duration': 5.063}, {'end': 8266.083, 'text': 'so these are the people all right now moving back to our slides.', 'start': 8257.859, 'duration': 8.224}, {'end': 8270.425, 'text': "okay, now let's see how you can use sub queries with the insert statement.", 'start': 8266.083, 'duration': 4.342}], 'summary': 'Total 12 employees have salary less than the average of $75,350.', 'duration': 32.486, 'max_score': 8237.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8237939.jpg'}], 'start': 7619.178, 'title': 'Sql joins and subqueries', 'summary': 'Covers full outer join in sql with mysql workbench, using left join, right join, and union operator, and explains subqueries with examples, including finding the department with the maximum salary and demonstrating subqueries with the select statement on mysql workbench.', 'chapters': [{'end': 7877.041, 'start': 7619.178, 'title': 'Understanding full outer join', 'summary': 'Explains how to perform a full outer join in sql using mysql workbench, demonstrating the use of left join, right join, and union operator to combine the result set of two or more select statements.', 'duration': 257.863, 'highlights': ['The union operator is used to combine the result set of two or more select statements, performing a vertical join.', 'Demonstrated the use of left join and right join to achieve a full outer join on MySQL Workbench, resulting in successful retrieval of customer names and their respective orders.', 'Explained the concept of a subquery as a select query enclosed inside another query, with the inner query getting executed before the outer query operation.']}, {'end': 8270.425, 'start': 7877.061, 'title': 'Sql sub queries and examples', 'summary': 'Explains how to write a sql query to display the department with the maximum salary from the employees table, including an example of a subquery and its output, and also demonstrates subqueries with the select statement using an example on mysql workbench.', 'duration': 393.364, 'highlights': ['The chapter explains how to write a SQL query to display the department with the maximum salary from the employees table.', 'The chapter includes an example of a subquery and its output, demonstrating how to find the name of the employee with the maximum salary in the employees table.', 'The chapter demonstrates subqueries with the select statement using an example on MySQL workbench.']}], 'duration': 651.247, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU7619178.jpg', 'highlights': ['The union operator is used to combine the result set of two or more select statements, performing a vertical join.', 'Demonstrated the use of left join and right join to achieve a full outer join on MySQL Workbench, resulting in successful retrieval of customer names and their respective orders.', 'Explained the concept of a subquery as a select query enclosed inside another query, with the inner query getting executed before the outer query operation.', 'The chapter explains how to write a SQL query to display the department with the maximum salary from the employees table.', 'The chapter includes an example of a subquery and its output, demonstrating how to find the name of the employee with the maximum salary in the employees table.', 'The chapter demonstrates subqueries with the select statement using an example on MySQL workbench.']}, {'end': 8938.242, 'segs': [{'end': 8302.173, 'src': 'embed', 'start': 8271.877, 'weight': 1, 'content': [{'end': 8276.058, 'text': 'Now, the insert statement uses the data written from the subquery to insert into another table.', 'start': 8271.877, 'duration': 4.181}, {'end': 8278.661, 'text': 'So this is how the syntax looks like.', 'start': 8276.959, 'duration': 1.702}, {'end': 8285.764, 'text': 'So you write insert into table name, followed by select individual columns, or start from the table.', 'start': 8278.821, 'duration': 6.943}, {'end': 8290.868, 'text': 'use the where clause and then you give the operator, followed by the inner query or the subquery.', 'start': 8285.764, 'duration': 5.104}, {'end': 8296.509, 'text': 'so here we will explore a table called products table.', 'start': 8292.105, 'duration': 4.404}, {'end': 8302.173, 'text': 'we are going to fetch few records from the products table based on a condition that is,', 'start': 8296.509, 'duration': 5.664}], 'summary': "Explains using insert statement with subquery to fetch records from 'products' table.", 'duration': 30.296, 'max_score': 8271.877, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8271877.jpg'}, {'end': 8702.448, 'src': 'embed', 'start': 8671.503, 'weight': 5, 'content': [{'end': 8673.564, 'text': 'there is another mistake here.', 'start': 8671.503, 'duration': 2.061}, {'end': 8676.285, 'text': "let's debug the mistake now.", 'start': 8673.564, 'duration': 2.721}, {'end': 8679.026, 'text': 'this should be product id instead of product underscore in.', 'start': 8676.285, 'duration': 2.741}, {'end': 8682.287, 'text': "let's insert again, all right.", 'start': 8680.563, 'duration': 1.724}, {'end': 8686.495, 'text': 'so we have successfully inserted two records to our table, that is, orders.', 'start': 8682.287, 'duration': 4.208}, {'end': 8688.84, 'text': "now let's see the orders table.", 'start': 8686.495, 'duration': 2.345}, {'end': 8690.663, 'text': "i'll write select star from orders.", 'start': 8688.84, 'duration': 1.823}, {'end': 8694.602, 'text': 'if i run it, there you go.', 'start': 8692.58, 'duration': 2.022}, {'end': 8702.448, 'text': 'so there were two products from our product table that were jewelry and laptop, which have a selling price greater than one thousand dollars.', 'start': 8694.602, 'duration': 7.846}], 'summary': 'Debugged and inserted 2 records into orders table, showing products with price > $1000.', 'duration': 30.945, 'max_score': 8671.503, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8671503.jpg'}, {'end': 8818.262, 'src': 'embed', 'start': 8783.756, 'weight': 2, 'content': [{'end': 8788.258, 'text': "before we see the subquery, let's see what we have in the table employees underscore b.", 'start': 8783.756, 'duration': 4.502}, {'end': 8794.13, 'text': 'This is basically a replica of the employees table.', 'start': 8791.209, 'duration': 2.921}, {'end': 8795.431, 'text': 'There you go.', 'start': 8794.951, 'duration': 0.48}, {'end': 8798.793, 'text': 'It has the same records that our employees table has.', 'start': 8796.211, 'duration': 2.582}, {'end': 8804.455, 'text': 'We are going to use both the employees table and the employees underscore B table to update our records.', 'start': 8798.993, 'duration': 5.462}, {'end': 8818.262, 'text': "So I'll write update employees set salary equal to, let me bring this to the next line.", 'start': 8805.556, 'duration': 12.706}], 'summary': 'Using subquery to update employee salaries.', 'duration': 34.506, 'max_score': 8783.756, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8783756.jpg'}, {'end': 8888.436, 'src': 'embed', 'start': 8857.129, 'weight': 0, 'content': [{'end': 8861.974, 'text': "So I'm going to update the records of the employees table specifically for the salary column.", 'start': 8857.129, 'duration': 4.845}, {'end': 8872.743, 'text': "So I'm checking if the age is greater than 27, then we'll multiply the salaries of the employees with a factor of 0.35 in the employees table.", 'start': 8862.534, 'duration': 10.209}, {'end': 8874.885, 'text': 'Let me just run this.', 'start': 8873.744, 'duration': 1.141}, {'end': 8876.527, 'text': "Then we'll see our output.", 'start': 8875.526, 'duration': 1.001}, {'end': 8888.436, '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': 8878.132, 'duration': 10.304}], 'summary': "Update 18 employees' salaries by multiplying with 0.35.", 'duration': 31.307, 'max_score': 8857.129, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8857129.jpg'}, {'end': 8938.242, 'src': 'embed', 'start': 8905.604, 'weight': 3, 'content': [{'end': 8918.022, 'text': 'Okay Now, if you check for employees who have an age less than or equal to 27, for example, Marcus whose age is 25, his salary is the same.', 'start': 8905.604, 'duration': 12.418}, {'end': 8919.703, 'text': "We haven't updated his salary.", 'start': 8918.282, 'duration': 1.421}, {'end': 8925.684, 'text': 'Then if you see, if you have, okay, there is one more employee, Maya.', 'start': 8920.443, 'duration': 5.241}, {'end': 8930.305, 'text': "We haven't updated the salary of Maya because the age is less than 27.", 'start': 8926.764, 'duration': 3.541}, {'end': 8931.685, 'text': 'All right.', 'start': 8930.305, 'duration': 1.38}, {'end': 8938.242, 'text': "Now let's go back to our slides again.", 'start': 8936.641, 'duration': 1.601}], 'summary': "Employees under 27, like marcus and maya, haven't had their salaries updated.", 'duration': 32.638, 'max_score': 8905.604, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8905604.jpg'}], 'start': 8271.877, 'title': 'Using subqueries for insert statements and creating tables', 'summary': 'Explains using subqueries to insert specific records into another table based on a condition, and covers creating tables for products and orders, inserting records, and using subqueries for selecting and updating records.', 'chapters': [{'end': 8323.465, 'start': 8271.877, 'title': 'Using subqueries for insert statements', 'summary': 'Explains how to use a subquery to insert specific records from one table into another table based on a condition, such as fetching records from the products table where the selling price is greater than $100 and inserting them into the orders table.', 'duration': 51.588, 'highlights': ['Using subqueries to insert data into another table based on specific conditions, such as fetching records where the selling price is greater than $100 and inserting them into the orders table.', 'Syntax for the insert statement with subquery: insert into table name, select individual columns or start from the table, use the where clause, followed by the operator, and the inner query or subquery.']}, {'end': 8938.242, 'start': 8325.688, 'title': 'Creating tables and using subqueries', 'summary': 'Covers creating tables for products and orders, inserting records into the tables, using a subquery with the insert statement to select and insert specific records, and using a subquery with the update statement to update salaries of employees based on age criteria.', 'duration': 612.554, 'highlights': ["Creating 'products' table with columns for product ID, item, selling price, and product type", "Inserting records into the 'products' table", "Creating 'orders' table with columns for order ID, product sold, and selling price", "Using a subquery with the insert statement to select and insert specific records into the 'orders' table", 'Using a subquery with the update statement to update salaries of employees based on age criteria']}], 'duration': 666.365, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8271877.jpg', 'highlights': ['Using subqueries to insert data into another table based on specific conditions, such as fetching records where the selling price is greater than $100 and inserting them into the orders table.', 'Syntax for the insert statement with subquery: insert into table name, select individual columns or start from the table, use the where clause, followed by the operator, and the inner query or subquery.', "Creating 'products' table with columns for product ID, item, selling price, and product type", "Creating 'orders' table with columns for order ID, product sold, and selling price", "Using a subquery with the insert statement to select and insert specific records into the 'orders' table", 'Using a subquery with the update statement to update salaries of employees based on age criteria']}, {'end': 11399.742, 'segs': [{'end': 9415.022, 'src': 'embed', 'start': 9358.157, 'weight': 10, 'content': [{'end': 9361.999, 'text': 'so a delimiter is a marker for the end of each command.', 'start': 9358.157, 'duration': 3.842}, {'end': 9370.983, 'text': "next I'll write my create trigger followed by the trigger name.", 'start': 9361.999, 'duration': 8.984}, {'end': 9378.566, 'text': "I'll give a valid trigger name such as each underscore verify.", 'start': 9370.983, 'duration': 7.583}, {'end': 9384.669, 'text': "I'll write before trigger.", 'start': 9378.566, 'duration': 6.103}, {'end': 9395.277, 'text': 'it should be before insert on my table name would be customers.', 'start': 9384.669, 'duration': 10.608}, {'end': 9415.022, 'text': "then i'll write for each row if new dot age is less than 0, then set new dot age equal to 0, which means,", 'start': 9395.277, 'duration': 19.745}], 'summary': "Create a trigger named 'each_verify' to set age to 0 if less than 0 during insert on 'customers' table.", 'duration': 56.865, 'max_score': 9358.157, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU9358157.jpg'}, {'end': 9696.66, 'src': 'embed', 'start': 9661.058, 'weight': 5, 'content': [{'end': 9665.7, 'text': "next we'll have the email id of the customer.", 'start': 9661.058, 'duration': 4.642}, {'end': 9674.643, 'text': 'this is again going to be of varka type and the column we are interested in is the birthday column.', 'start': 9665.7, 'duration': 8.943}, {'end': 9677.184, 'text': "so i'll write birth date.", 'start': 9674.643, 'duration': 2.541}, {'end': 9681.706, 'text': 'this is going to be of type date, okay.', 'start': 9677.184, 'duration': 4.522}, {'end': 9686.067, 'text': "so let's create our table all right.", 'start': 9681.706, 'duration': 4.361}, {'end': 9695.158, 'text': "now we'll create our second table, which is going to be message.", 'start': 9686.067, 'duration': 9.091}, {'end': 9696.66, 'text': "let's create the columns.", 'start': 9695.158, 'duration': 1.502}], 'summary': 'Creating tables with customer email and birthday data.', 'duration': 35.602, 'max_score': 9661.058, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU9661058.jpg'}, {'end': 10133.232, 'src': 'embed', 'start': 10106.401, 'weight': 9, 'content': [{'end': 10115.703, 'text': 'so for those two values you have the messages hi, nancy, please update your data birth, and then you have hi, alice, please update your data birth.', 'start': 10106.401, 'duration': 9.302}, {'end': 10122.104, 'text': 'now you can give a space between hi and the name of the customer.', 'start': 10115.703, 'duration': 6.401}, {'end': 10123.004, 'text': 'all right.', 'start': 10122.104, 'duration': 0.9}, {'end': 10130.506, 'text': "now, moving ahead, let's understand how before update trigger works, so i'll give a comment here as before update.", 'start': 10123.004, 'duration': 7.502}, {'end': 10133.232, 'text': 'Okay.', 'start': 10132.972, 'duration': 0.26}], 'summary': 'Two messages sent to update data birth, before update trigger explained.', 'duration': 26.831, 'max_score': 10106.401, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU10106401.jpg'}, {'end': 10366.15, 'src': 'embed', 'start': 10326.579, 'weight': 1, 'content': [{'end': 10330.64, 'text': "I'll end the if statement.", 'start': 10326.579, 'duration': 4.061}, {'end': 10339.061, 'text': "I'll close the delimiter by giving a semicolon.", 'start': 10330.64, 'duration': 8.421}, {'end': 10343.822, 'text': 'okay, so if you see the employees table.', 'start': 10339.061, 'duration': 4.761}, {'end': 10347.483, 'text': 'we have the salary values here now.', 'start': 10343.822, 'duration': 3.661}, {'end': 10358.943, 'text': "in case, while updating the table, If the salary is equal to $10, 000, we'll set the new salary to $85, 000..", 'start': 10347.483, 'duration': 11.46}, {'end': 10366.15, 'text': "Else, if the salary is less than $10, 000, we'll set the salary column values to $72, 000.", 'start': 10358.943, 'duration': 7.207}], 'summary': 'Updating salary values based on conditions: $85,000 if equal to $10,000, $72,000 if less.', 'duration': 39.571, 'max_score': 10326.579, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU10326579.jpg'}, {'end': 10563.132, 'src': 'embed', 'start': 10519.713, 'weight': 3, 'content': [{'end': 10523.615, 'text': 'let me run the create table command.', 'start': 10519.713, 'duration': 3.902}, {'end': 10525.516, 'text': 'okay, so we have created our salary table.', 'start': 10523.615, 'duration': 1.901}, {'end': 10530.198, 'text': 'now the next thing is to insert a few values.', 'start': 10525.516, 'duration': 4.682}, {'end': 10533.62, 'text': "so I'll write insert into salary.", 'start': 10530.198, 'duration': 3.422}, {'end': 10547.106, 'text': "I'll give my column names as EID comma valid from comma amount I'll write values.", 'start': 10533.62, 'duration': 13.486}, {'end': 10562.332, 'text': "my first employee ID would be 101, followed by a date, let's say 2005, May and 01, which is 1st of May 2005.", 'start': 10550.089, 'duration': 12.243}, {'end': 10563.132, 'text': "let's say the amount is $55, 000.", 'start': 10562.332, 'duration': 0.8}], 'summary': 'Created salary table with eid 101, valid from 2005, may 01, amount $55,000.', 'duration': 43.419, 'max_score': 10519.713, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU10519713.jpg'}, {'end': 10905.268, 'src': 'embed', 'start': 10845.663, 'weight': 6, 'content': [{'end': 10856.83, 'text': 'let me run this query, okay, so you have successfully deleted one of the records in the table.', 'start': 10845.663, 'duration': 11.167}, {'end': 10861.433, 'text': 'now if you see our other table, that is salary delete.', 'start': 10856.83, 'duration': 4.603}, {'end': 10869.188, 'text': 'you will find this information present in the salary delete column.', 'start': 10864.042, 'duration': 5.146}, {'end': 10875.236, 'text': 'so we had deleted one row of information, that is for employee ID 103, which you can find it here.', 'start': 10869.188, 'duration': 6.048}, {'end': 10882.104, 'text': 'now the final column, that is delete at, shows the time at which the information was deleted or the row was deleted.', 'start': 10875.236, 'duration': 6.868}, {'end': 10891.16, 'text': 'alright. so let me go to the top and just give you a recap of what we did in this demo.', 'start': 10883.655, 'duration': 7.505}, {'end': 10894.362, 'text': 'so first we created a database called triggers.', 'start': 10891.16, 'duration': 3.202}, {'end': 10905.268, 'text': 'then we started with before insert trigger, where we inserted customer table and then we verified the age of the customers.', 'start': 10894.362, 'duration': 10.906}], 'summary': 'Deleted one record from table, tracked in salary delete column, showing time of deletion. created triggers database, inserted and verified customer age.', 'duration': 59.605, 'max_score': 10845.663, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU10845663.jpg'}, {'end': 10959.77, 'src': 'embed', 'start': 10928.424, 'weight': 0, 'content': [{'end': 10938.151, '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': 10928.424, 'duration': 9.727}, {'end': 10941.973, 'text': 'all right.', 'start': 10940.532, 'duration': 1.441}, {'end': 10951.922, 'text': 'now, moving ahead, we saw how before update trigger works and finally we learnt about before delete trigger.', 'start': 10941.973, 'duration': 9.949}, {'end': 10954.925, 'text': 'so in this video we will learn sub queries in SQL.', 'start': 10951.922, 'duration': 3.003}, {'end': 10959.77, 'text': "we'll also have a look at stored procedures and learn about triggers in SQL.", 'start': 10954.925, 'duration': 4.845}], 'summary': 'Introduced missing date of birth message, covered update and delete triggers, and will cover subqueries, stored procedures, and sql triggers in upcoming video.', 'duration': 31.346, 'max_score': 10928.424, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU10928424.jpg'}, {'end': 11074.456, 'src': 'embed', 'start': 11044.284, 'weight': 8, 'content': [{'end': 11050.387, 'text': "within this table I'll write select star from employees.", 'start': 11044.284, 'duration': 6.103}, {'end': 11052.508, 'text': 'let me execute it.', 'start': 11050.387, 'duration': 2.121}, {'end': 11059.192, 'text': "okay, you can see here we have the employee ID, employee name, age, gender, there's date of join, department,", 'start': 11052.508, 'duration': 6.684}, {'end': 11063.794, 'text': 'city and salary and we have information for 20 employees.', 'start': 11059.192, 'duration': 4.602}, {'end': 11067.956, 'text': 'if I scroll down, you can see there are 20 employees present in our table.', 'start': 11063.794, 'duration': 4.162}, {'end': 11074.456, 'text': "So let's say you want to find the employees whose salary is greater than the average salary.", 'start': 11069.175, 'duration': 5.281}], 'summary': 'Table displays data for 20 employees, including id, name, age, gender, date of join, department, city, and salary. seeking employees with salary exceeding average.', 'duration': 30.172, 'max_score': 11044.284, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11044284.jpg'}, {'end': 11130.902, 'src': 'embed', 'start': 11093.321, 'weight': 11, 'content': [{'end': 11096.862, 'text': 'Then I want the department of the employee and the salary of the employee.', 'start': 11093.321, 'duration': 3.541}, {'end': 11102.173, 'text': 'from my table name, that is, employees.', 'start': 11097.89, 'duration': 4.283}, {'end': 11110.458, 'text': "next i'll use a where condition where my salary should be greater than the average salary of all the employees.", 'start': 11102.173, 'duration': 8.285}, {'end': 11113.9, 'text': "so i'll write salary greater than.", 'start': 11110.458, 'duration': 3.442}, {'end': 11117.182, 'text': 'after this i am going to write my sub query.', 'start': 11113.9, 'duration': 3.282}, {'end': 11130.902, 'text': "so i'll give select average of salary from my table name, that is, employees, and I'll close the bracket and give a semicolon.", 'start': 11117.182, 'duration': 13.72}], 'summary': 'Extract department and salary from employees where salary > average.', 'duration': 37.581, 'max_score': 11093.321, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11093321.jpg'}, {'end': 11406.75, 'src': 'embed', 'start': 11380.011, 'weight': 4, 'content': [{'end': 11385.196, 'text': 'if you click on this, it will take you to the link where you can download the database.', 'start': 11380.011, 'duration': 5.185}, {'end': 11392.703, 'text': 'so they have this download link which says download mysql sample database and the name of the database is classic models.', 'start': 11385.196, 'duration': 7.507}, {'end': 11399.742, 'text': 'alright, so we are going to use this classic models database throughout our demo session.', 'start': 11392.703, 'duration': 7.039}, {'end': 11406.75, 'text': 'if i expand the tables section, you can see there are a lot of tables that are present inside this classic models database.', 'start': 11399.742, 'duration': 7.008}], 'summary': 'A database named classic models with multiple tables is available for download for the demo session.', 'duration': 26.739, 'max_score': 11380.011, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11380011.jpg'}], 'start': 8940.023, 'title': 'Sql subqueries, triggers, and functions', 'summary': "Covers subqueries with the delete statement, deleting 9 records from the employees table with age less than or equal to 32. it explains sql triggers and demonstrates a 'before insert' trigger, correcting negative age values. also, it introduces mysql triggers, demonstrating a trigger sending a message for null 'birthdate' values. additionally, it covers subqueries in sql, including practical scenarios and usage with different tables, and introduces mysql workbench for practical demonstrations.", 'chapters': [{'end': 9144.237, 'start': 8940.023, 'title': 'Subquery with delete statement', 'summary': 'Covers the syntax and usage of subqueries with the delete statement, including an example where 9 records were deleted from the employees table where age was less than or equal to 32, resulting in 11 remaining employees with ages greater than 32.', 'duration': 204.214, 'highlights': ['9 records deleted from the employees table where age was less than or equal to 32, leaving 11 remaining employees with ages greater than 32.', "Detailed explanation of the syntax for a delete query using a subquery, including the use of inner brackets and the 'in' operator.", 'Explanation of using subqueries with delete statements, demonstrating how to delete records based on a condition using an inner query.']}, {'end': 9591.158, 'start': 9144.237, 'title': 'Understanding sql triggers', 'summary': "Explains the concept of sql triggers, their syntax, and an example of a 'before insert' trigger with a demonstration on mysql workbench, resulting in the automatic correction of negative age values to 0 for certain customer records.", 'duration': 446.921, 'highlights': ['The chapter explains the concept of SQL triggers and their syntax for creating triggers in MySQL, including the trigger time, event, and the structure of trigger queries.', "A 'before insert' trigger is demonstrated by creating a table called customers and using a trigger to automatically set the age value to 0 for records with a negative age when inserted into the table, resulting in the correction of age values for specific customer records.", "A step-by-step demonstration on MySQL Workbench illustrates the process of creating triggers, including creating a new database, moving inside the database, checking for existing tables, and creating a 'before insert' trigger to handle negative age values when inserting records into the customers table."]}, {'end': 10133.232, 'start': 9591.158, 'title': 'Mysql trigger example', 'summary': "Introduces mysql triggers and demonstrates how to create tables, define triggers, and insert records, with an emphasis on a trigger that sends a message when a null value is detected in the 'birthdate' column in the 'customers' table.", 'duration': 542.074, 'highlights': ["A trigger is created to send a message when a null value is detected in the 'birthdate' column in the 'customers' table", "Creation of 'customers' table with specific column types and constraints", "Insertion of records into the 'customers' table with deliberate null values for 'birthdate'", "Output of the trigger displaying messages for records with null 'birthdate'"]}, {'end': 10928.424, 'start': 10135.715, 'title': 'Before update and delete triggers demo', 'summary': 'Demonstrates how before update and delete triggers work by creating tables, inserting records, and executing trigger commands to modify and store data, affecting a total of seven and one record respectively.', 'duration': 792.709, 'highlights': ['Demonstrated the before update trigger by modifying the salary of employees based on conditions, affecting a total of seven records.', 'Executed a before delete trigger to store deleted records in a new table, affecting one record and displaying the deletion timestamp.', 'Created and inserted data into tables to showcase the functionality of triggers in a database environment.']}, {'end': 11399.742, 'start': 10928.424, 'title': 'Sub queries and sql functions', 'summary': 'Covers sub queries in sql, including scenarios where they can be used, such as finding employees with salary greater than the average salary, employees with salary greater than a specific employee (e.g. john), and using sub queries with different tables. the chapter also introduces the usage of mysql workbench on windows for practical demonstrations.', 'duration': 471.318, 'highlights': ['The chapter covers sub queries in SQL and demonstrates scenarios where they can be used, such as finding employees with salary greater than the average salary and employees with salary greater than a specific employee (e.g. John).', 'The chapter introduces the usage of MySQL workbench on Windows for practical demonstrations.', 'The chapter demonstrates the usage of sub queries with different tables.']}], 'duration': 2459.719, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU8940023.jpg', 'highlights': ['9 records deleted from the employees table where age was less than or equal to 32, leaving 11 remaining employees with ages greater than 32.', "Detailed explanation of the syntax for a delete query using a subquery, including the use of inner brackets and the 'in' operator.", 'Explanation of using subqueries with delete statements, demonstrating how to delete records based on a condition using an inner query.', 'Demonstrated the before update trigger by modifying the salary of employees based on conditions, affecting a total of seven records.', 'Executed a before delete trigger to store deleted records in a new table, affecting one record and displaying the deletion timestamp.', "A 'before insert' trigger is demonstrated by creating a table called customers and using a trigger to automatically set the age value to 0 for records with a negative age when inserted into the table, resulting in the correction of age values for specific customer records.", "A step-by-step demonstration on MySQL Workbench illustrates the process of creating triggers, including creating a new database, moving inside the database, checking for existing tables, and creating a 'before insert' trigger to handle negative age values when inserting records into the customers table.", 'The chapter covers sub queries in SQL and demonstrates scenarios where they can be used, such as finding employees with salary greater than the average salary and employees with salary greater than a specific employee (e.g. John).', 'The chapter explains the concept of SQL triggers and their syntax for creating triggers in MySQL, including the trigger time, event, and the structure of trigger queries.', 'The chapter introduces the usage of MySQL workbench on Windows for practical demonstrations.', "A trigger is created to send a message when a null value is detected in the 'birthdate' column in the 'customers' table", "Creation of 'customers' table with specific column types and constraints", "Insertion of records into the 'customers' table with deliberate null values for 'birthdate'", "Output of the trigger displaying messages for records with null 'birthdate'", 'The chapter demonstrates the usage of sub queries with different tables.']}, {'end': 13034.798, 'segs': [{'end': 11426.608, 'src': 'embed', 'start': 11399.742, 'weight': 0, 'content': [{'end': 11406.75, 'text': 'if i expand the tables section, you can see there are a lot of tables that are present inside this classic models database.', 'start': 11399.742, 'duration': 7.008}, {'end': 11410.595, 'text': 'we have cricket customers as employees, office.', 'start': 11406.75, 'duration': 3.845}, {'end': 11413.017, 'text': "there's orders, order lines and many more.", 'start': 11410.595, 'duration': 2.422}, {'end': 11420.103, 'text': "So for our sub query we'll be using two tables, that is, order details and products table.", 'start': 11413.999, 'duration': 6.104}, {'end': 11426.608, 'text': 'First, let me show you the content that is present inside the products table first.', 'start': 11420.784, 'duration': 5.824}], 'summary': 'The classic models database contains numerous tables, including cricket customers, employees, and orders. a subquery will use the order details and products tables.', 'duration': 26.866, 'max_score': 11399.742, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11399742.jpg'}, {'end': 11510.582, 'src': 'embed', 'start': 11485.241, 'weight': 6, 'content': [{'end': 11493.668, 'text': 'for this scenario we are going to use two different tables and we are going to write a sub query.', 'start': 11485.241, 'duration': 8.427}, {'end': 11502.257, 'text': 'okay, so if you see, here in the order details table we have a column called price, each.', 'start': 11493.668, 'duration': 8.589}, {'end': 11510.582, 'text': 'I want to display the product code, the product name and the MSRP of the products which have a price of each product less than hundred dollars.', 'start': 11502.257, 'duration': 8.325}], 'summary': 'Use subquery to display product code, name, and msrp for products with price < $100.', 'duration': 25.341, 'max_score': 11485.241, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11485241.jpg'}, {'end': 11931.993, 'src': 'embed', 'start': 11883.795, 'weight': 3, 'content': [{'end': 11890.998, 'text': 'if I run this, you can see we have two players in our table who have scored more than six goals,', 'start': 11883.795, 'duration': 7.203}, {'end': 11894.68, 'text': 'so we consider them as the top players in a particular tournament.', 'start': 11890.998, 'duration': 3.682}, {'end': 11902.983, 'text': 'all right now there are other methods that you can use while creating a stored procedure.', 'start': 11894.68, 'duration': 8.303}, {'end': 11907.155, 'text': 'one of the methods is by using an in parameter.', 'start': 11903.832, 'duration': 3.323}, {'end': 11916.001, 'text': 'so when you define an in parameter inside a stored procedure, the calling program has to pass an argument to the stored procedure.', 'start': 11907.155, 'duration': 8.846}, {'end': 11919.183, 'text': "so I'll give a comment.", 'start': 11916.001, 'duration': 3.182}, {'end': 11924.387, 'text': 'stored procedure, using in parameter alright.', 'start': 11919.183, 'duration': 5.204}, {'end': 11931.993, 'text': "so for this example, I'll create a procedure that will fetch or display the top records of employees based on their salaries.", 'start': 11924.387, 'duration': 7.606}], 'summary': 'Two players scored more than six goals, considered top players in the tournament. creating a stored procedure using an in parameter to fetch top employee records based on salaries.', 'duration': 48.198, 'max_score': 11883.795, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11883795.jpg'}, {'end': 12286.454, 'src': 'embed', 'start': 12236.195, 'weight': 2, 'content': [{'end': 12242.077, 'text': "i'll write begin and write my update command or update statement.", 'start': 12236.195, 'duration': 5.882}, {'end': 12248.479, 'text': "i'll write update table name, that is, employee details.", 'start': 12242.077, 'duration': 6.402}, {'end': 12268.1, 'text': 'set salary equal to new underscore salary where name is equal to my temporary variable, that is temp underscore name.', 'start': 12248.479, 'duration': 19.621}, {'end': 12277.309, 'text': "so this is my update command and I'll end the delimiter.", 'start': 12268.1, 'duration': 9.209}, {'end': 12278.59, 'text': 'all right.', 'start': 12277.309, 'duration': 1.281}, {'end': 12279.751, 'text': "so let's run this.", 'start': 12278.59, 'duration': 1.161}, {'end': 12286.454, 'text': 'okay, we have successfully created our stored procedure.', 'start': 12283.112, 'duration': 3.342}], 'summary': 'Created stored procedure to update employee salaries', 'duration': 50.259, 'max_score': 12236.195, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU12236195.jpg'}, {'end': 12809.871, 'src': 'embed', 'start': 12760.785, 'weight': 4, 'content': [{'end': 12768.048, 'text': 'I am going to use a before insert trigger.', 'start': 12760.785, 'duration': 7.263}, {'end': 12775.272, 'text': 'so I will write before insert on my table name, that is student.', 'start': 12768.048, 'duration': 7.224}, {'end': 12797.829, 'text': 'next I will write for each row if new dot marks is less than 0, then will set new dot marks equal to 50.', 'start': 12775.272, 'duration': 22.557}, {'end': 12801.95, 'text': 'so this is my condition.', 'start': 12797.829, 'duration': 4.121}, {'end': 12809.871, 'text': 'first will check before inserting if any student has marks less than 0, will assign a value 50 to that student,', 'start': 12801.95, 'duration': 7.921}], 'summary': 'Using a before insert trigger to set marks to 50 if less than 0', 'duration': 49.086, 'max_score': 12760.785, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU12760785.jpg'}], 'start': 11399.742, 'title': 'Sql for data analysis and automation', 'summary': 'Covers subqueries for product price analysis involving 110 products and 83 products with price less than $100, sql stored procedures for analyzing player performance, usage of in and out parameters, and triggers for automating data processes such as setting minimum marks for student records.', 'chapters': [{'end': 11594.298, 'start': 11399.742, 'title': 'Sub query for product price analysis', 'summary': 'Explains how to use a sub query to find the product code, product name, and msrp of products with a price less than $100, involving 110 different products and 83 products with a price less than $100.', 'duration': 194.556, 'highlights': ['There are 110 different products present in the products table.', 'There are 83 products in the table with a price less than $100.', 'The order details table contains a thousand records.']}, {'end': 11883.795, 'start': 11594.298, 'title': 'Sql stored procedures', 'summary': "Introduces stored procedures in sql, explaining how to create a stored procedure to return a list of players who have scored more than six goals in a tournament, and demonstrates the process with a specific example using the sql database 'sql_iq'. the chapter also covers the syntax for creating and calling stored procedures, as well as addressing common errors.", 'duration': 289.497, 'highlights': ['The chapter introduces stored procedures in SQL, explaining how to create a stored procedure to return a list of players who have scored more than six goals in a tournament.', "Demonstrates the process with a specific example using the SQL database 'SQL_IQ', which has a table called 'players' with columns for player ID, name, country, and number of goals scored.", 'Covers the syntax for creating and calling stored procedures, as well as addressing common errors during the process.']}, {'end': 12645.733, 'start': 11883.795, 'title': 'Stored procedures and triggers in sql', 'summary': 'Covers the creation and usage of stored procedures in sql, including usage of in parameters to fetch top records based on salaries and update employee details. it also includes the creation of a stored procedure using an out parameter to count the total number of female employees in a table, as well as an introduction to triggers in sql.', 'duration': 761.938, 'highlights': ['Creation of stored procedure to fetch top records based on salaries using in parameter', 'Usage of in parameter to update employee details', 'Creation of stored procedure using out parameter to count total female employees', 'Introduction to triggers in SQL']}, {'end': 13034.798, 'start': 12645.733, 'title': 'Creating table with trigger for student records', 'summary': 'Explains the creation of a student table with roll number, age, name, and marks, along with the implementation of a trigger to automatically set marks to 50 if they are less than 0, resulting in 2 out of 4 records having their negative marks converted to 50.', 'duration': 389.065, 'highlights': ['Two out of four student records had negative marks converted to 50', 'Creation of student table with roll number, age, name, and marks', 'Implementation of trigger to set marks to 50 if less than 0', 'Demonstration of inserting four rows of student information']}], 'duration': 1635.056, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU11399742.jpg', 'highlights': ['There are 110 different products present in the products table.', 'There are 83 products in the table with a price less than $100.', 'The order details table contains a thousand records.', 'The chapter introduces stored procedures in SQL, explaining how to create a stored procedure to return a list of players who have scored more than six goals in a tournament.', "Demonstrates the process with a specific example using the SQL database 'SQL_IQ', which has a table called 'players' with columns for player ID, name, country, and number of goals scored.", 'Two out of four student records had negative marks converted to 50', 'Creation of stored procedure to fetch top records based on salaries using in parameter', 'Usage of in parameter to update employee details', 'Creation of stored procedure using out parameter to count total female employees', 'Introduction to triggers in SQL']}, {'end': 14410.239, 'segs': [{'end': 13373.014, 'src': 'embed', 'start': 13325.907, 'weight': 3, 'content': [{'end': 13341.456, 'text': "so I'll write from products table I'll give an alias as P followed by inner join my other table, that is product lines.", 'start': 13325.907, 'duration': 15.549}, {'end': 13347.479, 'text': "as let's say pl on the common column that is product line.", 'start': 13341.456, 'duration': 6.023}, {'end': 13355.764, 'text': 'so p dot product line is equal to.', 'start': 13347.479, 'duration': 8.285}, {'end': 13363.369, 'text': "i'll give a space pl dot product line.", 'start': 13355.764, 'duration': 7.605}, {'end': 13373.014, 'text': 'okay. so here we have used an inner join to fetch specific columns from both the tables And our view name is product underscore description.', 'start': 13363.369, 'duration': 9.645}], 'summary': 'Using inner join to fetch columns from products and product lines, creating view product_description.', 'duration': 47.107, 'max_score': 13325.907, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13325907.jpg'}, {'end': 13480.603, 'src': 'embed', 'start': 13444.208, 'weight': 0, 'content': [{'end': 13445.709, 'text': 'I want to change this name to.', 'start': 13444.208, 'duration': 1.501}, {'end': 13454.874, 'text': "let's say, I'll give vehicle description, since all our products are related to some of the other vehicle.", 'start': 13445.709, 'duration': 9.165}, {'end': 13456.595, 'text': "so I'll write vehicle description.", 'start': 13454.874, 'duration': 1.721}, {'end': 13464.851, 'text': 'okay, let us run it all right.', 'start': 13460.888, 'duration': 3.963}, {'end': 13470.596, 'text': 'so here you can see i have renamed my view.', 'start': 13464.851, 'duration': 5.745}, {'end': 13480.603, 'text': "so here, if i just refresh it and i'll expand this, you can see we have the curse details view and we have the vehicle underscore description view.", 'start': 13470.596, 'duration': 10.007}], 'summary': "Renamed view to 'vehicle description' for vehicle-related products.", 'duration': 36.395, 'max_score': 13444.208, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13444208.jpg'}, {'end': 13591.415, 'src': 'embed', 'start': 13552.926, 'weight': 5, 'content': [{'end': 13559.027, 'text': "let's say I want to delete customer underscore details or cust underscore details view.", 'start': 13552.926, 'duration': 6.101}, {'end': 13565.409, 'text': "I'll write drop view, cust underscore details.", 'start': 13559.027, 'duration': 6.382}, {'end': 13569.21, 'text': "let's run it.", 'start': 13565.409, 'duration': 3.801}, {'end': 13575.172, 'text': "you can see here we don't have the cust underscore details view anymore.", 'start': 13569.21, 'duration': 5.962}, {'end': 13579.373, 'text': 'all right now, moving to our final section in this demo.', 'start': 13575.172, 'duration': 4.201}, {'end': 13583.769, 'text': 'Here we will learn about Windows functions.', 'start': 13580.686, 'duration': 3.083}, {'end': 13591.415, 'text': 'Windows functions were incorporated in MySQL in the 8.0 version.', 'start': 13587.732, 'duration': 3.683}], 'summary': 'Demonstration of deleting a view and introduction to windows functions in mysql 8.0', 'duration': 38.489, 'max_score': 13552.926, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13552926.jpg'}, {'end': 13661.018, 'src': 'embed', 'start': 13628.639, 'weight': 9, 'content': [{'end': 13632.562, 'text': 'so here we have 20 employees in our table.', 'start': 13628.639, 'duration': 3.923}, {'end': 13638.367, 'text': 'using this table we are going to find the combined salary of the employees for each department.', 'start': 13632.562, 'duration': 5.805}, {'end': 13642.59, 'text': 'so we will partition our table by department and print the total salary.', 'start': 13638.968, 'duration': 3.622}, {'end': 13648.112, 'text': 'and this we are going to do using some windows functions in mysql.', 'start': 13642.59, 'duration': 5.522}, {'end': 13661.018, 'text': 'so I will write select I want the employee name, the age of the employee and the department of the employee comma.', 'start': 13648.112, 'duration': 12.906}], 'summary': 'Using mysql, finding combined salary per department for 20 employees.', 'duration': 32.379, 'max_score': 13628.639, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13628639.jpg'}, {'end': 13894.45, 'src': 'embed', 'start': 13852.759, 'weight': 2, 'content': [{'end': 13857.161, 'text': 'the name is Shane.', 'start': 13852.759, 'duration': 4.402}, {'end': 13857.701, 'text': 'give a comma.', 'start': 13857.161, 'duration': 0.54}, {'end': 13861.464, 'text': "I'll insert the second student name, 102.", 'start': 13859.423, 'duration': 2.041}, {'end': 13868.249, 'text': 'the name is Bradley.', 'start': 13861.464, 'duration': 6.785}, {'end': 13872.552, 'text': 'give a comma, this time for 103.', 'start': 13868.249, 'duration': 4.303}, {'end': 13875.594, 'text': 'we have two records.', 'start': 13872.552, 'duration': 3.042}, {'end': 13879.316, 'text': "let's say the name of the student is Herat.", 'start': 13875.594, 'duration': 3.722}, {'end': 13879.877, 'text': 'give a comma.', 'start': 13879.316, 'duration': 0.561}, {'end': 13884, 'text': "I'll copy this and we'll paste it again.", 'start': 13879.877, 'duration': 4.123}, {'end': 13888.846, 'text': 'so we have duplicated 103.', 'start': 13884, 'duration': 4.846}, {'end': 13891.468, 'text': 'Next we have 104..', 'start': 13888.846, 'duration': 2.622}, {'end': 13894.45, 'text': "The name of the student, let's say, is Nathan.", 'start': 13891.468, 'duration': 2.982}], 'summary': 'Two records created, with student ids 102 and 103 duplicated.', 'duration': 41.691, 'max_score': 13852.759, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13852759.jpg'}, {'end': 14042.085, 'src': 'embed', 'start': 13985.186, 'weight': 1, 'content': [{'end': 13992.411, 'text': "okay, then I'll write order by st underscore id.", 'start': 13985.186, 'duration': 7.225}, {'end': 13993.231, 'text': 'close the bracket.', 'start': 13992.411, 'duration': 0.82}, {'end': 13998.715, 'text': "I'll give an alias as ronam from my table.", 'start': 13993.231, 'duration': 5.484}, {'end': 14000.816, 'text': 'that is demo.', 'start': 13998.715, 'duration': 2.101}, {'end': 14003.418, 'text': "let's just run it.", 'start': 14000.816, 'duration': 2.602}, {'end': 14004.238, 'text': 'you can see here.', 'start': 14003.418, 'duration': 0.82}, {'end': 14012.629, 'text': 'okay, let me just delete n from here and do it again.', 'start': 14004.238, 'duration': 8.391}, {'end': 14017.45, 'text': 'alright, if you see, here there is just one student in the name Shane.', 'start': 14012.629, 'duration': 4.821}, {'end': 14027.413, 'text': 'we have one student in the name Bradley, but here, if you see for Herat, the second record, it says 2, which means there are two records for Herat,', 'start': 14017.45, 'duration': 9.963}, {'end': 14033.374, 'text': 'and if I scroll down, there is one record for Nathan and there are two records for Kevin, which means Kevin is also repeated.', 'start': 14027.413, 'duration': 5.961}, {'end': 14042.085, 'text': 'okay, Now we are going to see another Windows function that is called rank function in MySQL.', 'start': 14033.374, 'duration': 8.711}], 'summary': 'Demonstration of using rank function in mysql to identify repeated records.', 'duration': 56.899, 'max_score': 13985.186, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13985186.jpg'}, {'end': 14196.395, 'src': 'embed', 'start': 14170.183, 'weight': 6, 'content': [{'end': 14175.324, 'text': 'so here, if you mark so for variable a, 101, the test rank is 1.', 'start': 14170.183, 'duration': 5.141}, {'end': 14177.785, 'text': 'for 102, the test rank is 2.', 'start': 14175.324, 'duration': 2.461}, {'end': 14187.869, 'text': 'but for this value, which is 103, the test rank is repeated because there was a repetition for 103.', 'start': 14177.785, 'duration': 10.084}, {'end': 14190.551, 'text': 'so we have skipped the rank 4 here.', 'start': 14187.869, 'duration': 2.682}, {'end': 14196.395, 'text': 'for 104, the rank is 5 now for 105, the rank is 6 now for 106.', 'start': 14190.551, 'duration': 5.844}], 'summary': 'Variable a ranks: 101=1, 102=2, 103=3, 104=5, 105=6, 106=7', 'duration': 26.212, 'max_score': 14170.183, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU14170183.jpg'}], 'start': 13034.798, 'title': 'Sql views and window functions', 'summary': 'Introduces sql views, demonstrating the creation and usage of views to display specific customer details, and covers the usage of window functions in mysql to find combined salaries, duplicate values, sequential integers, ranks, and highest salaries in departments.', 'chapters': [{'end': 13223.339, 'start': 13034.798, 'title': 'Introduction to sql views', 'summary': 'Introduces the concept of sql views, explaining that views are virtual tables that do not store data, created by joining tables, and demonstrates the creation of a view to display specific customer details from a table, resulting in the display of customer names, phone numbers, and cities.', 'duration': 188.541, 'highlights': ['Views are virtual tables that do not store data of their own but display data stored in other tables.', 'Creation of a view to display specific customer details from a table, resulting in the display of customer names, phone numbers, and cities.', 'Introduction to SQL views as a crucial concept, widely used in SQL.']}, {'end': 13717.282, 'start': 13223.339, 'title': 'Creating views with joins & using windows functions', 'summary': 'Demonstrates how to create views by joining tables, rename, view, display, and delete views, and use windows functions to find the combined salary of employees for each department using mysql.', 'duration': 493.943, 'highlights': ['The chapter demonstrates how to create views by joining two tables, displaying records, and fetching specific columns using an inner join.', "The process of renaming, displaying, and deleting views is explained, including using commands like 'rename table', 'display views', and 'drop view'.", 'The chapter introduces the concept of windows functions in MySQL 8.0 and demonstrates how to use them to find the combined salary of employees for each department.']}, {'end': 14410.239, 'start': 13718.464, 'title': 'Window functions in mysql', 'summary': 'Covers the usage of row number, rank, and first value functions in mysql, demonstrating their applications in finding duplicate values, creating sequential integers, assigning ranks, and identifying the highest salary in each department.', 'duration': 691.775, 'highlights': ['The row number function assigns sequential integers to each row within its partition, demonstrated by applying it to the salary column and ordering by salary, resulting in sequential integers from 1 to 20.', "The row number function is used to find duplicate values in a table, illustrated by creating a table 'demo' and using the function to display the duplicate records, showing the presence of duplicated student records.", "The rank function assigns ranks to a column, demonstrated by applying it to the 'var_a' column and displaying the ranks, illustrating how the function handles repetition and creates gaps in rank values.", 'The first value function returns the value of the specified expression with respect to the first row in the window frame, exemplified by using it to identify the employee with the highest salary and to display the employee with the highest salary in each department.']}], 'duration': 1375.441, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU13034798.jpg', 'highlights': ['Introduction to SQL views as a crucial concept, widely used in SQL.', 'Creation of a view to display specific customer details from a table.', 'The chapter demonstrates how to create views by joining two tables.', 'The process of renaming, displaying, and deleting views is explained.', 'The row number function assigns sequential integers to each row within its partition.', 'The rank function assigns ranks to a column, handling repetition and creating gaps.', 'The first value function returns the value of the specified expression with respect to the first row.', 'The row number function is used to find duplicate values in a table.', 'The chapter introduces the concept of windows functions in MySQL 8.0.', 'Using window functions in MySQL to find the combined salary of employees for each department.']}, {'end': 16127.296, 'segs': [{'end': 15794.986, 'src': 'embed', 'start': 15762.998, 'weight': 0, 'content': [{'end': 15769.502, 'text': "next I'll create a quantity column to keep track of the number of quantities that were ordered.", 'start': 15762.998, 'duration': 6.504}, {'end': 15776.901, 'text': 'this is of type integer.', 'start': 15771.84, 'duration': 5.061}, {'end': 15784.463, 'text': 'my next column would be unit price, which will basically have information about the price of each unit of product.', 'start': 15776.901, 'duration': 7.562}, {'end': 15793.186, 'text': "unit price can be of type float and finally I'll have the phone number of the customer.", 'start': 15784.463, 'duration': 8.723}, {'end': 15794.986, 'text': "I'll write phone number.", 'start': 15793.186, 'duration': 1.8}], 'summary': 'Creating quantity and unit price columns of type integer and float respectively, and adding a phone number column for customer details.', 'duration': 31.988, 'max_score': 15762.998, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU15762998.jpg'}, {'end': 15969.689, 'src': 'embed', 'start': 15917.022, 'weight': 2, 'content': [{'end': 15926.672, 'text': 'if you see, here, in our execute underscore query function, instead of cursor, I have written cursor.', 'start': 15917.022, 'duration': 9.65}, {'end': 15927.773, 'text': 'so r is missing.', 'start': 15926.672, 'duration': 1.101}, {'end': 15932.218, 'text': "let's rerun this and now let's run this again.", 'start': 15927.773, 'duration': 4.445}, {'end': 15932.738, 'text': 'there you go.', 'start': 15932.218, 'duration': 0.52}, {'end': 15935.901, 'text': 'you can see here my SQL database connection successful.', 'start': 15932.738, 'duration': 3.163}, {'end': 15937.383, 'text': 'even our query was also successful.', 'start': 15935.901, 'duration': 1.482}, {'end': 15945.924, 'text': 'now, if you want to recheck if the table, that is, orders, was created or not, you can check it on the mysql workbench.', 'start': 15939.363, 'duration': 6.561}, {'end': 15948.165, 'text': 'so let me show you how to do it.', 'start': 15945.924, 'duration': 2.241}, {'end': 15955.026, 'text': 'so i am on my mysql workbench and under mysql underscore python database you have something called as tables.', 'start': 15948.165, 'duration': 6.861}, {'end': 15958.286, 'text': "let me just right click and i'll select refresh all.", 'start': 15955.026, 'duration': 3.26}, {'end': 15960.827, 'text': 'there you go, you can see this arrow.', 'start': 15958.286, 'duration': 2.541}, {'end': 15965.408, 'text': 'just click on this arrow and here you can see we have a table called orders.', 'start': 15960.827, 'duration': 4.581}, {'end': 15967.228, 'text': 'so we have created our table called orders.', 'start': 15965.408, 'duration': 1.82}, {'end': 15969.689, 'text': 'now you can check the columns as well.', 'start': 15967.948, 'duration': 1.741}], 'summary': "Successfully executed sql query, created 'orders' table, and verified connection in mysql workbench.", 'duration': 52.667, 'max_score': 15917.022, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU15917022.jpg'}], 'start': 14410.239, 'title': 'Sql and mysql database operations', 'summary': 'Covers sql operations including subqueries, stored procedures, triggers, views, and windows functions, along with mysql database operations such as connection, creation, table creation, and population with examples and tools like mysql workbench and jupyter notebook. it demonstrates creating a database, executing sql queries, and inserting records using python.', 'chapters': [{'end': 14554.668, 'start': 14410.239, 'title': 'Sql tutorial: subqueries, stored procedures, triggers, views, and windows functions', 'summary': 'Covered subqueries, stored procedures, triggers, views, and windows functions in sql, demonstrating the usage and operations with examples and tools such as mysql workbench and jupyter notebook.', 'duration': 144.429, 'highlights': ['Subqueries, stored procedures, triggers, views, and windows functions were demonstrated with practical examples using MySQL Workbench and Jupyter Notebook.', 'The tutorial included the usage of subqueries in SQL, the creation of stored procedures with in and out parameters, writing triggers, working with views, and exploring windows functions.', 'The chapter highlighted the practical demonstration of using SQL with Python in Jupyter Notebook and emphasized the installation process of MySQL Workbench and Jupyter Notebook.', 'The tutorial showcased the practical application of subqueries, stored procedures, triggers, views, and windows functions in SQL, offering a comprehensive understanding of these concepts.']}, {'end': 15114.064, 'start': 14554.668, 'title': 'Mysql connection and database creation', 'summary': 'Demonstrates importing necessary libraries, creating a server connection function, successfully connecting to the mysql database, and creating a database using a user-defined function.', 'duration': 559.396, 'highlights': ["The chapter demonstrates creating a database using a user-defined function, create_database, and successfully connecting to the MySQL database, displaying the message 'mysql database connection successful'.", 'The chapter details the process of creating a server connection function, create_server_connection, and importing necessary libraries such as mysql.connector and pandas as pd.', 'The chapter explains the use of the mysql.connector.connect method to establish a session with the mysql server, passing in three parameters - host name, username, and user password.', 'The chapter outlines the process of handling errors using the try and accept blocks, displaying error messages in case of exceptions during the database creation process.']}, {'end': 15794.986, 'start': 15115.856, 'title': 'Creating mysql database and tables', 'summary': 'Discusses creating a mysql database, connecting to the database, executing sql queries, and creating a table with relevant column names and data types, ultimately demonstrating the successful completion of each task.', 'duration': 679.13, 'highlights': ['The chapter demonstrates creating a new database named mysql_python and successfully executes the creation function, verified through MySQL Workbench.', 'The process of connecting to the newly created database using a user-defined function, create_db_connection, is explained, along with exception handling techniques, resulting in a successful connection.', 'The creation of a user-defined function, execute_query, to execute SQL queries and handle exceptions is detailed, resulting in the successful execution of SQL queries.', 'The process of creating a table named orders with relevant column names and data types is outlined, ensuring readability and clarity in the SQL command.']}, {'end': 16127.296, 'start': 15794.986, 'title': 'Creating and populating orders table', 'summary': "Demonstrates creating a table 'orders' in the mysql database using python, successfully inserting six records with customer details and their purchased items.", 'duration': 332.31, 'highlights': ["A demonstration of creating a table 'orders' in the MySQL database using Python is shown, including the successful insertion of six records with customer details and their purchased items.", "The process of inserting six records into the 'orders' table, containing customer IDs, names, purchased items, order dates, quantities, prices, and phone numbers, is detailed.", "The successful connection to the MySQL database and execution of the query to create the 'orders' table is shown, along with a successful check for the table's creation in the MySQL Workbench."]}], 'duration': 1717.057, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU14410239.jpg', 'highlights': ['Subqueries, stored procedures, triggers, views, and windows functions were demonstrated with practical examples using MySQL Workbench and Jupyter Notebook.', 'The tutorial showcased the practical application of subqueries, stored procedures, triggers, views, and windows functions in SQL, offering a comprehensive understanding of these concepts.', "The chapter demonstrates creating a database using a user-defined function, create_database, and successfully connecting to the MySQL database, displaying the message 'mysql database connection successful'.", "The process of inserting six records into the 'orders' table, containing customer IDs, names, purchased items, order dates, quantities, prices, and phone numbers, is detailed."]}, {'end': 16981.869, 'segs': [{'end': 16329.237, 'src': 'embed', 'start': 16296.553, 'weight': 0, 'content': [{'end': 16310.22, 'text': "I'll use my formatting f error give a colon followed by a space within single quotes inside curly braces I'll give err and close my double quotes.", 'start': 16296.553, 'duration': 13.667}, {'end': 16311.961, 'text': "Let's run it.", 'start': 16311.281, 'duration': 0.68}, {'end': 16313.142, 'text': 'All right.', 'start': 16312.842, 'duration': 0.3}, {'end': 16314.93, 'text': 'So now we are all set.', 'start': 16313.829, 'duration': 1.101}, {'end': 16326.456, 'text': "Now we are going to use our select clause having, whereby then we'll see how to use order by clause some inbuilt functions, we'll update some records,", 'start': 16315.89, 'duration': 10.566}, {'end': 16329.237, 'text': 'delete some records and do a lot of other stuff.', 'start': 16326.456, 'duration': 2.781}], 'summary': 'Demonstrating sql queries with select, order by, update, delete, and more', 'duration': 32.684, 'max_score': 16296.553, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16296553.jpg'}, {'end': 16458.193, 'src': 'embed', 'start': 16431.96, 'weight': 1, 'content': [{'end': 16438.344, 'text': 'you can see here we have successfully printed all the rows in our table, which is orders.', 'start': 16431.96, 'duration': 6.384}, {'end': 16442.065, 'text': 'you can see we have six records in total.', 'start': 16438.344, 'duration': 3.721}, {'end': 16445.868, 'text': 'now we are going to explore a few more queries.', 'start': 16442.065, 'duration': 3.803}, {'end': 16450.051, 'text': 'so let me just copy this and we are going to edit in the same query.', 'start': 16445.868, 'duration': 4.183}, {'end': 16451.772, 'text': "i'll paste it here.", 'start': 16450.051, 'duration': 1.721}, {'end': 16458.193, 'text': "next, let's say you want to display individual columns from the table and not all the columns.", 'start': 16451.772, 'duration': 6.421}], 'summary': 'Successfully printed all 6 rows from the orders table.', 'duration': 26.233, 'max_score': 16431.96, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16431960.jpg'}, {'end': 16516.089, 'src': 'embed', 'start': 16489.044, 'weight': 2, 'content': [{'end': 16494.725, 'text': 'the first column is the customer name and then we have the respective phone numbers.', 'start': 16489.044, 'duration': 5.681}, {'end': 16499.366, 'text': 'okay, now let me just paste that query again.', 'start': 16494.725, 'duration': 4.641}, {'end': 16503.588, 'text': 'now we are going to see how you can use an ill-built function.', 'start': 16499.366, 'duration': 4.222}, {'end': 16505.309, 'text': 'that is,', 'start': 16503.588, 'duration': 1.721}, {'end': 16516.089, 'text': 'in our table we have the order date and from the order date we are only going to display the different years that are present in the order date.', 'start': 16505.309, 'duration': 10.78}], 'summary': 'Demonstrating how to display different years from the order date in a table.', 'duration': 27.045, 'max_score': 16489.044, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16489044.jpg'}, {'end': 16611.895, 'src': 'embed', 'start': 16579.132, 'weight': 3, 'content': [{'end': 16580.651, 'text': "let's run it.", 'start': 16579.132, 'duration': 1.519}, {'end': 16588.574, 'text': 'you can see, 2018 and 2019 are the unique year values that are present in the order date column.', 'start': 16580.651, 'duration': 7.923}, {'end': 16596.169, 'text': "okay, now, moving ahead, Let's write our fifth query, and this time we are going to explore how you can use the WHERE clause.", 'start': 16588.574, 'duration': 7.595}, {'end': 16601.151, 'text': "So I'll change this to Q5 before I write my query.", 'start': 16597.509, 'duration': 3.642}, {'end': 16611.895, 'text': "So let's say you want to display all the orders that were ordered before 31st of December 2018.", 'start': 16602.472, 'duration': 9.423}], 'summary': 'Query to display orders before 31st dec 2018 using where clause.', 'duration': 32.763, 'max_score': 16579.132, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16579132.jpg'}, {'end': 16981.869, 'src': 'embed', 'start': 16952.796, 'weight': 4, 'content': [{'end': 16964.882, 'text': 'so here i am creating a empty list first and then i am creating a for loop and i am appending the results to my empty list.', 'start': 16952.796, 'duration': 12.086}, {'end': 16965.542, 'text': 'here you can see.', 'start': 16964.882, 'duration': 0.66}, {'end': 16972.666, 'text': 'i have created my column list and using pd dot data frame, i am converting the list into a data frame.', 'start': 16965.542, 'duration': 7.124}, {'end': 16976.828, 'text': 'if i run this, this is append and not appends.', 'start': 16972.666, 'duration': 4.162}, {'end': 16979.488, 'text': 'All right, you can see.', 'start': 16978.348, 'duration': 1.14}, {'end': 16981.869, 'text': 'we have our data frame ready.', 'start': 16979.488, 'duration': 2.381}], 'summary': 'Creating a data frame from a list using a for loop and pd.dataframe', 'duration': 29.073, 'max_score': 16952.796, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16952796.jpg'}], 'start': 16127.296, 'title': 'Sql database connection and python sql queries', 'summary': 'Details the process of creating a successful sql database connection, executing a query to insert data, and creating a user defined function. it also covers python sql queries for data retrieval, manipulation, and data frame creation with examples.', 'chapters': [{'end': 16219.047, 'start': 16127.296, 'title': 'Creating sql database connection and user defined functions', 'summary': 'Details the process of creating a successful sql database connection and executing a query to insert data, followed by the creation of a user defined function to read and display the results.', 'duration': 91.751, 'highlights': ['The process involves creating a successful SQL database connection followed by executing a query to insert data.', 'The chapter discusses the creation of a user defined function named df read underscore query to read and display the results.']}, {'end': 16981.869, 'start': 16219.047, 'title': 'Python sql queries and data manipulation', 'summary': 'Covers python sql queries for data retrieval, manipulation, and data frame creation, including select, where, order by clauses, and built-in functions, with examples demonstrating data extraction and filtering, resulting in a data frame creation.', 'duration': 762.822, 'highlights': ['The chapter covers Python SQL queries for data retrieval, manipulation, and data frame creation.', 'Examples demonstrate data extraction and filtering using select, where, order by clauses, and built-in functions.', 'A data frame is created from the retrieved data using pandas and displayed.']}], 'duration': 854.573, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16127296.jpg', 'highlights': ['The chapter covers Python SQL queries for data retrieval, manipulation, and data frame creation.', 'Examples demonstrate data extraction and filtering using select, where, order by clauses, and built-in functions.', 'A data frame is created from the retrieved data using pandas and displayed.', 'The process involves creating a successful SQL database connection followed by executing a query to insert data.', 'The chapter discusses the creation of a user defined function named df read underscore query to read and display the results.']}, {'end': 18870.56, 'segs': [{'end': 18099.47, 'src': 'embed', 'start': 18037.146, 'weight': 3, 'content': [{'end': 18038.768, 'text': 'genre is of type varchar.', 'start': 18037.146, 'duration': 1.622}, {'end': 18049.891, 'text': "I'll give the size as, let's say, 30 and my final and the last column will have the imdb ratings.", 'start': 18042.065, 'duration': 7.826}, {'end': 18059.618, 'text': "so I'll write imdb underscore ratings.", 'start': 18049.891, 'duration': 9.727}, {'end': 18065.962, 'text': 'now the ratings will be of type real, since it can have floating or decimal point values.', 'start': 18059.618, 'duration': 6.344}, {'end': 18070.646, 'text': "if I close the bracket, I'll give a semicolon and I'll hit enter.", 'start': 18065.962, 'duration': 4.684}, {'end': 18071.046, 'text': 'there you go.', 'start': 18070.646, 'duration': 0.4}, {'end': 18074.483, 'text': 'So we have successfully created a table called movies.', 'start': 18071.462, 'duration': 3.021}, {'end': 18077.764, 'text': 'Now let me go back to my PG admin.', 'start': 18075.463, 'duration': 2.301}, {'end': 18078.884, 'text': 'All right.', 'start': 18078.544, 'duration': 0.34}, {'end': 18081.525, 'text': 'So here I have my database that is SQL demo.', 'start': 18079.404, 'duration': 2.121}, {'end': 18085.786, 'text': "I'll just right click on this and click on refresh.", 'start': 18082.085, 'duration': 3.701}, {'end': 18089.867, 'text': 'Now, let me go to schemas.', 'start': 18087.327, 'duration': 2.54}, {'end': 18094.268, 'text': "I'll just scroll down a bit here under schemas.", 'start': 18090.667, 'duration': 3.601}, {'end': 18095.869, 'text': 'We have something called as tables.', 'start': 18094.348, 'duration': 1.521}, {'end': 18099.47, 'text': 'Let me expand this.', 'start': 18096.629, 'duration': 2.841}], 'summary': "Created a table named 'movies' with columns 'genre' (varchar), 'size' (varchar(30)), and 'imdb_ratings' (real).", 'duration': 62.324, 'max_score': 18037.146, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU18037146.jpg'}, {'end': 18546.47, 'src': 'embed', 'start': 18510.496, 'weight': 1, 'content': [{'end': 18513.178, 'text': 'let me go back to my pg admin.', 'start': 18510.496, 'duration': 2.682}, {'end': 18520.144, 'text': 'okay now, first and foremost, let me tell you how to update records in a table.', 'start': 18513.178, 'duration': 6.966}, {'end': 18530.812, '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': 18520.144, 'duration': 10.668}, {'end': 18532.494, 'text': 'you can use the update query for that.', 'start': 18530.812, 'duration': 1.682}, {'end': 18546.47, 'text': "so I'm 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': 18533.405, 'duration': 13.065}], 'summary': 'Learn how to update records in a table using pgadmin.', 'duration': 35.974, 'max_score': 18510.496, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU18510496.jpg'}, {'end': 18720.323, 'src': 'embed', 'start': 18656.81, 'weight': 0, 'content': [{'end': 18669.61, 'text': "so you will write delete from the table name, that is movies where let's say i want to delete the movie id 108, which is the lion king.", 'start': 18656.81, 'duration': 12.8}, {'end': 18678.333, 'text': "so i'll write where movie underscore id is equal to 108.", 'start': 18669.61, 'duration': 8.723}, {'end': 18686.276, 'text': "this is one of the ways to delete this particular movie, or you can give, let's say, where movie name is equal to the lion king.", 'start': 18678.333, 'duration': 7.943}, {'end': 18692.772, 'text': "let me select this and i'll hit execute.", 'start': 18687.711, 'duration': 5.061}, {'end': 18704.675, 'text': 'now, if i run my select query again, you see, this time it has returned seven rows and you cannot find movie with movie id 108.', 'start': 18692.772, 'duration': 11.903}, {'end': 18707.056, 'text': 'that was the lion king.', 'start': 18704.675, 'duration': 2.381}, {'end': 18710.177, 'text': 'so we have deleted it all right.', 'start': 18707.056, 'duration': 3.121}, {'end': 18714.579, 'text': 'next We are going to learn about where clause in Postgres SQL.', 'start': 18710.177, 'duration': 4.402}, {'end': 18720.323, 'text': "So to learn where clause, I'll be using the same movie table again.", 'start': 18715.3, 'duration': 5.023}], 'summary': 'Demonstrating deletion of movie with id 108 and learning about where clause in postgres sql.', 'duration': 63.513, 'max_score': 18656.81, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU18656810.jpg'}], 'start': 16982.489, 'title': 'Postgresql and sql operations', 'summary': 'Covers postgresql basics, sql commands execution, and database operations. it includes creating databases, tables, and performing operations such as updating and deleting records. it also includes specific examples of using sql commands with quantifiable data like database popularity and version.', 'chapters': [{'end': 17283.015, 'start': 16982.489, 'title': 'Sql update and delete commands', 'summary': 'Covers how to use the update command to change the unit price of an order in a table, updating a specific record from $50 to $45 and how to delete a record from the table, demonstrating the successful execution of both commands.', 'duration': 300.526, 'highlights': ['The update command is used to change the unit price of an order, updating a specific record from $50 to $45, which is demonstrated through a SQL query and its successful execution.', 'The delete command is utilized to remove a specific record from the table, exemplified by deleting the order ID 105, and the successful execution of the delete query is confirmed.', 'The chapter demonstrates the process of using SQL commands to update and delete records in a table, showcasing the successful execution of the update and delete queries with specific examples and verification of the changes.']}, {'end': 17531.442, 'start': 17283.015, 'title': 'Introduction to postgresql and basic sql commands', 'summary': 'Covers the basics of postgresql, including creating a server connection, executing queries, and exploring sql commands. postgresql is the fourth most popular database, and the tutorial will cover basic commands like select, update, and delete.', 'duration': 248.427, 'highlights': ['PostgreSQL is currently ranked fourth in popularity amongst hundreds of databases worldwide according to the DB engines ranking.', 'PostgreSQL supports multiple operating systems such as Windows, Linux, and MacOS, making it highly compatible.', 'The chapter covers basic SQL commands such as SELECT, UPDATE, and DELETE, along with filtering data using WHERE and HAVING clauses, grouping data using GROUP BY, and ordering results using the ORDER BY clause.']}, {'end': 18074.483, 'start': 17532.489, 'title': 'Postgres sql basics and database operations', 'summary': "Covers connecting to postgres sql using psql and pg admin, creating databases, performing basic operations, and creating a table with specific data types and sizes. the version of postgresql 13.2 is checked and a new database 'sql_demo' is created, along with a table 'movies' with columns like movie id, name, genre, and imdb ratings.", 'duration': 541.994, 'highlights': ["Creating a table called 'movies' with specific data types and sizes", "Creating a new database 'sql_demo'", 'Checking the version of PostgreSQL 13.2', 'Connecting to Postgres SQL using PG admin', 'Performing basic operations using PostgreSQL commands']}, {'end': 18532.494, 'start': 18075.463, 'title': 'Working with postgresql tables', 'summary': "Covers creating, deleting, inserting and updating tables in postgresql, including creating a 'students' table with columns such as roll number, name, and gender, adding 8 records to the 'movies' table, and describing the table structure.", 'duration': 457.031, 'highlights': ["Creating a 'students' table with columns such as roll number, name, and gender, with constraints like 'not null' and 'primary key'.", "Adding 8 records to the 'movies' table using the insert command and verifying the successful insertion.", "Describing the table structure using the 'backslash d' command in SQL cell and providing details about column names, data types, and constraints."]}, {'end': 18870.56, 'start': 18533.405, 'title': 'Update and delete in sql', 'summary': "Covers updating and deleting records in a database table, with examples such as updating the genre of a movie from 'drama' to 'drama and crime', and deleting a movie by id. it also teaches using the where clause to filter records based on imdb ratings, such as filtering movies with ratings greater than 8.7 or between 8.5 and 9.0.", 'duration': 337.155, 'highlights': ['Teaching how to use the WHERE clause to filter records based on IMDB ratings', "Example of updating the genre of a movie from 'drama' to 'drama and crime'", 'Demonstration of deleting a movie by ID']}], 'duration': 1888.071, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU16982489.jpg', 'highlights': ['PostgreSQL is currently ranked fourth in popularity amongst hundreds of databases worldwide according to the DB engines ranking.', 'The update command is used to change the unit price of an order, updating a specific record from $50 to $45, which is demonstrated through a SQL query and its successful execution.', 'The delete command is utilized to remove a specific record from the table, exemplified by deleting the order ID 105, and the successful execution of the delete query is confirmed.', "Creating a 'students' table with columns such as roll number, name, and gender, with constraints like 'not null' and 'primary key'.", "Creating a table called 'movies' with specific data types and sizes"]}, {'end': 20860.074, 'segs': [{'end': 18900.823, 'src': 'embed', 'start': 18870.56, 'weight': 1, 'content': [{'end': 18878.745, 'text': "all right now, moving ahead, let's say you want to display the movies whose movie genre is action.", 'start': 18870.56, 'duration': 8.185}, {'end': 18884.799, 'text': 'you can see in our table we have a few movies whose genre is action movie.', 'start': 18878.745, 'duration': 6.054}, {'end': 18887.179, 'text': 'so you can do that as well.', 'start': 18884.799, 'duration': 2.38}, {'end': 18897.882, 'text': "I'll write select star from movies where the movie genre I'm writing this time in one line.", 'start': 18887.179, 'duration': 10.703}, {'end': 18900.823, 'text': 'you can break it into two lines as well.', 'start': 18897.882, 'duration': 2.941}], 'summary': "To display action movies, use 'select star from movies where movie genre is action' in sql.", 'duration': 30.263, 'max_score': 18870.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU18870560.jpg'}, {'end': 19044.238, 'src': 'embed', 'start': 19005.169, 'weight': 9, 'content': [{'end': 19007.111, 'text': 'let me run it there you go.', 'start': 19005.169, 'duration': 1.942}, {'end': 19015.157, 'text': 'so these are the movie names and the movie genres you can see that have an imdb ratings less than 9.0.', 'start': 19007.111, 'duration': 8.046}, {'end': 19022.963, 'text': 'all right, like how you saw the between operator, there is one more operator that you can use with the where clause, that is the in operator.', 'start': 19015.157, 'duration': 7.806}, {'end': 19028.488, 'text': 'so the in operator works like a OR clause or an OR operator.', 'start': 19022.963, 'duration': 5.525}, {'end': 19044.238, 'text': "so let's say I want to select all the columns from my movies table where the IMDB ratings is in 8.7 or 9.0.", 'start': 19028.488, 'duration': 15.75}], 'summary': 'Query for movie names and genres with imdb ratings less than 9.0 using the in operator.', 'duration': 39.069, 'max_score': 19005.169, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19005169.jpg'}, {'end': 19132.28, 'src': 'embed', 'start': 19076.272, 'weight': 17, 'content': [{'end': 19078.814, 'text': 'then we inserted a few records to our tables.', 'start': 19076.272, 'duration': 2.542}, {'end': 19088.863, 'text': 'we saw how you can delete a table from your database and we have performed a few operations like update, delete, and we saw how a where clause works.', 'start': 19078.814, 'duration': 10.049}, {'end': 19096.827, 'text': "now it's time to load a employee CSV file or a CSV data set to Postgres SQL.", 'start': 19090.004, 'duration': 6.823}, {'end': 19098.748, 'text': "so I'll tell you how you can do that.", 'start': 19096.827, 'duration': 1.921}, {'end': 19103.87, 'text': 'but first of all, before loading or inserting the records, we need to create an employee table.', 'start': 19098.748, 'duration': 5.122}, {'end': 19111.313, 'text': 'so let me first go ahead and create a new table called employees in our SQL underscore demo database.', 'start': 19103.87, 'duration': 7.443}, {'end': 19118.016, 'text': "so I'll write create table.", 'start': 19111.313, 'duration': 6.703}, {'end': 19122.628, 'text': 'my name of the table would be employees.', 'start': 19118.016, 'duration': 4.612}, {'end': 19126.913, 'text': "next I'm going to give my column names.", 'start': 19122.628, 'duration': 4.285}, {'end': 19130.117, 'text': 'so my first column would be employee ID.', 'start': 19126.913, 'duration': 3.204}, {'end': 19132.28, 'text': 'so the employee ID will be of type integer.', 'start': 19130.117, 'duration': 2.163}], 'summary': 'Demonstrated table creation, record insertion, and csv loading in postgres sql.', 'duration': 56.008, 'max_score': 19076.272, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19076272.jpg'}, {'end': 19452.306, 'src': 'embed', 'start': 19422.318, 'weight': 0, 'content': [{'end': 19423.439, 'text': 'Alright, let me close this.', 'start': 19422.318, 'duration': 1.121}, {'end': 19424.839, 'text': 'There you go.', 'start': 19424.419, 'duration': 0.42}, {'end': 19431.183, 'text': 'It says 150 rows affected, which means we have inserted 150 rows of information to our employees table.', 'start': 19424.92, 'duration': 6.263}, {'end': 19433.844, 'text': 'You can see we have the employee ID.', 'start': 19432.083, 'duration': 1.761}, {'end': 19434.804, 'text': 'These are all unique.', 'start': 19434.024, 'duration': 0.78}, {'end': 19438.386, 'text': 'We have the employee name, the email, we have the address and the salary.', 'start': 19434.984, 'duration': 3.402}, {'end': 19440.067, 'text': 'Let me scroll down.', 'start': 19439.247, 'duration': 0.82}, {'end': 19448.543, 'text': 'so that, okay, you can see we have 150 rows of information.', 'start': 19442.699, 'duration': 5.844}, {'end': 19452.306, 'text': 'that means we have 150 employees in our table.', 'start': 19448.543, 'duration': 3.763}], 'summary': 'Successfully inserted 150 rows into employees table, confirming 150 employees with unique ids, names, emails, addresses, and salaries.', 'duration': 29.988, 'max_score': 19422.318, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19422318.jpg'}, {'end': 19513.35, 'src': 'embed', 'start': 19485.928, 'weight': 3, 'content': [{'end': 19490.81, 'text': "if I run this again, I'll query will return 150 rows.", 'start': 19485.928, 'duration': 4.882}, {'end': 19493.811, 'text': 'you can see, we have the different country names under address.', 'start': 19490.81, 'duration': 3.001}, {'end': 19499.753, 'text': "that is Russia, we have France, there's United States, we have Germany.", 'start': 19493.811, 'duration': 5.942}, {'end': 19502.254, 'text': 'okay, and I think we have Israel as well.', 'start': 19499.753, 'duration': 2.501}, {'end': 19508.086, 'text': 'yeah, now, suppose you want to display only the unique address or the country names.', 'start': 19502.254, 'duration': 5.832}, {'end': 19513.35, 'text': 'you can use the distinct keyword before the column name.', 'start': 19508.086, 'duration': 5.264}], 'summary': 'Query will return 150 rows with different country names. use distinct keyword for unique address or country names.', 'duration': 27.422, 'max_score': 19485.928, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19485928.jpg'}, {'end': 19660.92, 'src': 'embed', 'start': 19622.599, 'weight': 21, 'content': [{'end': 19630.823, 'text': "now the order by is used to order your result in a particular format, let's say in ascending or descending order.", 'start': 19622.599, 'duration': 8.224}, {'end': 19639.247, 'text': "so the way to use is, let's say I want to select all the employees from my table.", 'start': 19630.823, 'duration': 8.424}, {'end': 19647.089, 'text': "so I'll write select star from employees order by.", 'start': 19639.247, 'duration': 7.842}, {'end': 19654.715, 'text': "I want to order the employees based on their salary, so I'll write order by salary.", 'start': 19647.089, 'duration': 7.626}, {'end': 19658.939, 'text': 'let me select and run it.', 'start': 19654.715, 'duration': 4.224}, {'end': 19660.92, 'text': 'okay, there is some problem.', 'start': 19658.939, 'duration': 1.981}], 'summary': 'Using order by to sort employees by salary encountered a problem.', 'duration': 38.321, 'max_score': 19622.599, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19622599.jpg'}, {'end': 19780.613, 'src': 'embed', 'start': 19746.431, 'weight': 22, 'content': [{'end': 19749.092, 'text': 'so I want to rename a column.', 'start': 19746.431, 'duration': 2.661}, {'end': 19753.034, 'text': 'you can do this using the alter command in PostgreSQL.', 'start': 19749.092, 'duration': 3.942}, {'end': 19755.956, 'text': 'so let me show you how to rename this column.', 'start': 19753.034, 'duration': 2.922}, {'end': 19756.776, 'text': 'that is address.', 'start': 19755.956, 'duration': 0.82}, {'end': 19765.48, 'text': 'so I will write alter table, followed by the table name, which is employees.', 'start': 19756.776, 'duration': 8.704}, {'end': 19775.505, 'text': 'then I am going to use rename column address.', 'start': 19765.48, 'duration': 10.025}, {'end': 19778.552, 'text': "I'll write to.", 'start': 19777.372, 'duration': 1.18}, {'end': 19780.613, 'text': 'I want to change it to country.', 'start': 19778.552, 'duration': 2.061}], 'summary': "Renamed column 'address' to 'country' in postgresql.", 'duration': 34.182, 'max_score': 19746.431, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19746431.jpg'}, {'end': 19842.128, 'src': 'embed', 'start': 19808.327, 'weight': 11, 'content': [{'end': 19814.337, 'text': "So this time I'm going to tell you how an AND and an OR operator works in SQL.", 'start': 19808.327, 'duration': 6.01}, {'end': 19820.581, 'text': 'So you can use the and and or operator along with the where clause.', 'start': 19816.26, 'duration': 4.321}, {'end': 19828.944, 'text': "So let's say I want to select the employees who are from France and their salary is less than $80, 000.", 'start': 19821.161, 'duration': 7.783}, {'end': 19831.545, 'text': 'So let me show you how to do it.', 'start': 19828.944, 'duration': 2.601}, {'end': 19842.128, 'text': "I'll write select star from employees where I'm going to give two conditions.", 'start': 19832.265, 'duration': 9.863}], 'summary': 'Explains how to use and and or operators in sql to select employees from france with salary less than $80,000.', 'duration': 33.801, 'max_score': 19808.327, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU19808327.jpg'}, {'end': 20195.523, 'src': 'embed', 'start': 20158.476, 'weight': 19, 'content': [{'end': 20165.302, 'text': 'all right, You can also use the offset along with the fetch clause.', 'start': 20158.476, 'duration': 6.826}, {'end': 20170.127, 'text': "I'll copy this again and let me paste it here.", 'start': 20166.143, 'duration': 3.984}, {'end': 20179.177, 'text': "Now after descending, I'm going to write offset, let's say three rows.", 'start': 20170.147, 'duration': 9.03}, {'end': 20186.219, 'text': 'and fetch first five rows only.', 'start': 20181.457, 'duration': 4.762}, {'end': 20195.523, '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': 20186.219, 'duration': 9.304}], 'summary': 'Using sql offset and fetch to skip 3 rows and display next 5 rows.', 'duration': 37.047, 'max_score': 20158.476, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20158476.jpg'}, {'end': 20250.192, 'src': 'embed', 'start': 20224.257, 'weight': 2, 'content': [{'end': 20228.941, 'text': 'you forgot the full name of an employee, but you remember the few initials.', 'start': 20224.257, 'duration': 4.684}, {'end': 20234.465, 'text': 'so you can use the like operator to get an idea as to which employee name it is.', 'start': 20228.941, 'duration': 5.524}, {'end': 20241.608, 'text': "now let's explore some examples to learn how the like operator works in postgres sql.", 'start': 20235.626, 'duration': 5.982}, {'end': 20248.491, 'text': 'so suppose you want to know the employees whose name starts with a, so for that you can use the like operator.', 'start': 20241.608, 'duration': 6.883}, {'end': 20250.192, 'text': 'let me show you how to do it.', 'start': 20248.491, 'duration': 1.701}], 'summary': 'Learn how to use the like operator in postgres sql for finding employees with specific initials.', 'duration': 25.935, 'max_score': 20224.257, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20224257.jpg'}, {'end': 20397.992, 'src': 'embed', 'start': 20335.558, 'weight': 5, 'content': [{'end': 20339.401, 'text': 'so there are 10 employees in the table whose name starts with s.', 'start': 20335.558, 'duration': 3.843}, {'end': 20347.287, 'text': "okay, let's copy the query again, and this time i want to know the employees whose name ends with d.", 'start': 20339.401, 'duration': 7.886}, {'end': 20357.835, 'text': "now the way to do it is instead of a percentage, i'll write this time percentage d, which means at the beginning it can have any letter,", 'start': 20347.287, 'duration': 10.548}, {'end': 20363.038, 'text': 'but the last letter in the string or in the name should be ending with d.', 'start': 20357.835, 'duration': 5.203}, {'end': 20369.142, 'text': 'now let me copy and run this.', 'start': 20365.08, 'duration': 4.062}, {'end': 20372.745, 'text': 'so there are 13 employees in the table whose name ends with a D.', 'start': 20369.142, 'duration': 3.603}, {'end': 20375.266, 'text': 'you can see it here all right.', 'start': 20372.745, 'duration': 2.521}, {'end': 20384.17, 'text': "now let's say you want to find the employees whose name contains ish or have ish in their names.", 'start': 20375.266, 'duration': 8.904}, {'end': 20386.05, 'text': 'so the way to do is something like this.', 'start': 20384.17, 'duration': 1.88}, {'end': 20397.992, 'text': "so i'll copy this now here instead of a percentage, i'll replace this with percentage ish percentage.", 'start': 20386.05, 'duration': 11.942}], 'summary': '10 employees with names starting with s, 13 ending with d, and containing ish.', 'duration': 62.434, 'max_score': 20335.558, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20335558.jpg'}, {'end': 20521.378, 'src': 'embed', 'start': 20454.486, 'weight': 7, 'content': [{'end': 20459.748, 'text': 'so the beginning can start with A, B, C, D or any of the 26 alphabets we have.', 'start': 20454.486, 'duration': 5.262}, {'end': 20467.47, 'text': 'then then it should contain U as the second letter, followed by any other letter or letters.', 'start': 20459.748, 'duration': 7.722}, {'end': 20470.331, 'text': 'let me run this.', 'start': 20467.47, 'duration': 2.861}, {'end': 20476.614, 'text': 'okay, so there are 10 employees in the table whose name has a U as the second letter.', 'start': 20470.331, 'duration': 6.283}, {'end': 20478.554, 'text': 'you can see these.', 'start': 20476.614, 'duration': 1.94}, {'end': 20487.909, 'text': 'okay, now, moving ahead, let me show you how you can use basic SQL functions or inbuilt functions.', 'start': 20478.554, 'duration': 9.355}, {'end': 20492.152, 'text': "so we'll explore a few mathematical functions now.", 'start': 20487.909, 'duration': 4.243}, {'end': 20496.456, 'text': "so let's say you want to find the total sum of salary for all the employees.", 'start': 20492.152, 'duration': 4.304}, {'end': 20501.319, 'text': 'so for that you can use the sum function that is available in SQL.', 'start': 20496.456, 'duration': 4.863}, {'end': 20515.235, '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': 20501.319, 'duration': 13.916}, {'end': 20516.496, 'text': "let's see the result.", 'start': 20515.235, 'duration': 1.261}, {'end': 20520.897, 'text': 'this will return one unique value.', 'start': 20516.496, 'duration': 4.401}, {'end': 20521.378, 'text': 'there you go.', 'start': 20520.897, 'duration': 0.481}], 'summary': "10 employees have 'u' as the second letter in their names. demonstrated use of sql sum function to find total salary.", 'duration': 66.892, 'max_score': 20454.486, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20454486.jpg'}, {'end': 20578.164, 'src': 'embed', 'start': 20545.134, 'weight': 4, 'content': [{'end': 20555.979, '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': 20545.134, 'duration': 10.845}, {'end': 20566.376, 'text': "so if I write sum of salary as, let's say, total salary, then this becomes my output column.", 'start': 20555.979, 'duration': 10.397}, {'end': 20571.671, 'text': 'you can see the difference if I run this.', 'start': 20566.376, 'duration': 5.295}, {'end': 20575.102, 'text': 'okay, you can see now in the output we have the total salary.', 'start': 20571.671, 'duration': 3.431}, {'end': 20578.164, 'text': 'now this is much more readable than the previous one.', 'start': 20575.102, 'duration': 3.062}], 'summary': 'Summing salary column as total salary for improved readability.', 'duration': 33.03, 'max_score': 20545.134, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20545134.jpg'}, {'end': 20642.704, 'src': 'embed', 'start': 20612.201, 'weight': 14, 'content': [{'end': 20614.642, 'text': 'You can see the average salary for all the employees.', 'start': 20612.201, 'duration': 2.441}, {'end': 20615.823, 'text': "It's around $81, 000.", 'start': 20615.182, 'duration': 0.641}, {'end': 20616.163, 'text': 'Okay Now.', 'start': 20615.823, 'duration': 0.34}, {'end': 20626.055, 'text': 'there are two more important functions that SQL provides us, which is max and minimum.', 'start': 20620.512, 'duration': 5.543}, {'end': 20639.462, 'text': "so if I write select maximum or max, which is the function name of salary, as, let's say, instead of total, I'll write maximum.", 'start': 20626.055, 'duration': 13.407}, {'end': 20642.704, 'text': 'so this will return me the maximum salary of the employee.', 'start': 20639.462, 'duration': 3.242}], 'summary': 'Average salary is $81,000. sql functions include max and min.', 'duration': 30.503, 'max_score': 20612.201, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20612201.jpg'}, {'end': 20822.873, 'src': 'embed', 'start': 20792.084, 'weight': 8, 'content': [{'end': 20800.83, 'text': 'so what we are going to do is wherever the department has a null value, we are going to assign a new department called analytics.', 'start': 20792.084, 'duration': 8.746}, {'end': 20804.473, 'text': 'so earlier we have also learned how to use the update command.', 'start': 20800.83, 'duration': 3.643}, {'end': 20806.414, 'text': 'so i am going to show it again.', 'start': 20804.473, 'duration': 1.941}, {'end': 20809.717, 'text': "so we'll write update followed by the table name, that is employees.", 'start': 20806.414, 'duration': 3.303}, {'end': 20822.873, 'text': 'i am going to set my column that is department equal to within single quotes.', 'start': 20809.717, 'duration': 13.156}], 'summary': "Replacing null department values with 'analytics' in the employees table.", 'duration': 30.789, 'max_score': 20792.084, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20792084.jpg'}], 'start': 18870.56, 'title': 'Sql data operations', 'summary': "Covers sql queries for displaying movies by genre, data manipulation, table operations, and & or operators, and pattern matching with quantifiable data such as displaying one action movie, inserting 150 rows, and finding 16 names starting with 'a'.", 'chapters': [{'end': 18927.817, 'start': 18870.56, 'title': 'Displaying movies by genre', 'summary': 'Discusses how to display movies with a specific genre using sql, showcasing the process with an example of selecting action movies from a table, resulting in the display of one movie, the dark knight.', 'duration': 57.257, 'highlights': ['Using SQL to select movies by genre, demonstrated by the example of selecting action movies from a table.', "The process involves writing a query using 'SELECT * FROM movies WHERE movie_genre = 'action'' to display movies with the specified genre.", "The example query results in the display of one movie, 'the dark knight', whose genre is action."]}, {'end': 19392.042, 'start': 18927.817, 'title': 'Sql data manipulation and csv import', 'summary': 'Covers sql queries for selecting specific columns, using where clause with in operator to filter records, creating a new table and importing csv data into postgresql.', 'duration': 464.225, 'highlights': ['The chapter covers SQL queries for selecting specific columns', 'Using WHERE clause with IN operator to filter records', 'Creating a new table in SQL', 'Importing CSV data into PostgreSQL']}, {'end': 19807.206, 'start': 19392.042, 'title': 'Sql table operations & advanced commands', 'summary': 'Demonstrates table operations in sql, including inserting 150 rows of information, exploring distinct keyword to display unique country names, using is null operator to display null values, and employing order by clause to order results based on salary in ascending and descending order.', 'duration': 415.164, 'highlights': ['Successfully inserted 150 rows of information to the employees table', 'Using distinct keyword to display unique country names present in the address column, resulting in six unique country names', 'Displayed 16 rows of information where the email ID is null, indicating around 10 percent of employees do not have an email ID', 'Employed order by clause to order employees based on salary in ascending and descending order', "Successfully renamed the 'address' column to 'country' using the alter command"]}, {'end': 20195.523, 'start': 19808.327, 'title': 'Sql and & or operators, limit, offset, and fetch', 'summary': 'Explains how to use the and and or operators in sql to apply multiple conditions in the where clause, with examples and their corresponding results. it also discusses the usage of the limit, offset, and fetch clauses to restrict and display specific rows in a table, demonstrated with sql queries and their outcomes.', 'duration': 387.196, 'highlights': ['Using AND operator with WHERE clause', 'Using OR operator with WHERE clause', 'Applying LIMIT clause to display top rows', 'Utilizing OFFSET clause to skip rows', 'Demonstrating FETCH clause with OFFSET']}, {'end': 20487.909, 'start': 20195.523, 'title': 'Pattern matching with like operator in postgresql', 'summary': "Explores the usage of the like operator in postgresql to perform pattern matching on employee names, yielding 16 employees whose names start with 'a', 10 with names starting with 's', 13 with names ending with 'd', 1 with 'ish' in the name, and 10 with 'u' as the second letter.", 'duration': 292.386, 'highlights': ['The like operator is used for pattern matching in PostgreSQL, allowing users to find strings that match specific patterns.', "Usage of the like operator to find employee names starting with 'A' yields 16 matches.", "Using the like operator to locate names starting with 'S' returns 10 matching employees.", "Applying the like operator to identify names ending with 'D' results in 13 matches.", "Pattern matching using the like operator to find names containing 'ish' results in one matching employee.", "Using the like operator to find names with 'U' as the second letter yields 10 matches."]}, {'end': 20860.074, 'start': 20487.909, 'title': 'Sql functions and data manipulation', 'summary': 'Explains the usage of sql functions such as sum, avg, max, min, and count to calculate total salary, average salary, maximum and minimum salaries, as well as the count of departments, while also demonstrating data manipulation through the update command.', 'duration': 372.165, 'highlights': ['The sum function in SQL is used to find the total sum of salary for all the employees, returning one unique value.', 'Explains the usage of alias to make the output column more readable when using sum function in SQL.', 'Demonstrates the usage of avg function to calculate the average salary for all the employees, resulting in an average salary of around $81,000.', 'Illustrates the functionality of max and min functions in SQL to find the maximum and minimum salaries of the employees, returning $1,19,616 as the highest and $45,685 as the lowest salary, respectively.', 'Demonstrates the usage of count function in SQL to find the total number of distinct departments, revealing 12 departments.', "Shows the process of updating the department column in the employees table by replacing null values with a new department name 'analytics' using the update command in SQL."]}], 'duration': 1989.514, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU18870560.jpg', 'highlights': ['Successfully inserted 150 rows of information to the employees table', 'Using SQL to select movies by genre, demonstrated by the example of selecting action movies from a table', "Using the like operator to find employee names starting with 'A' yields 16 matches", 'Using distinct keyword to display unique country names present in the address column, resulting in six unique country names', 'Explains the usage of alias to make the output column more readable when using sum function in SQL', "Using the like operator to locate names starting with 'S' returns 10 matching employees", "Using the like operator to identify names ending with 'D' results in 13 matches", "Using the like operator to find names with 'U' as the second letter yields 10 matches", "Using the process of updating the department column in the employees table by replacing null values with a new department name 'analytics' using the update command in SQL", 'Using WHERE clause with IN operator to filter records', "Applying the like operator to identify names ending with 'D' results in 13 matches", 'Using AND operator with WHERE clause', 'Using OR operator with WHERE clause', 'The sum function in SQL is used to find the total sum of salary for all the employees, returning one unique value', 'Demonstrates the usage of avg function to calculate the average salary for all the employees, resulting in an average salary of around $81,000', 'Demonstrates the functionality of max and min functions in SQL to find the maximum and minimum salaries of the employees, returning $1,19,616 as the highest and $45,685 as the lowest salary, respectively', "Using the example query results in the display of one movie, 'the dark knight', whose genre is action", 'Creating a new table in SQL', 'Importing CSV data into PostgreSQL', 'Utilizing OFFSET clause to skip rows', 'Demonstrating FETCH clause with OFFSET', 'Employed order by clause to order employees based on salary in ascending and descending order', "Successfully renamed the 'address' column to 'country' using the alter command", "Using the like operator to locate names starting with 'S' returns 10 matching employees", "Using the like operator to find names with 'U' as the second letter yields 10 matches", "Using the like operator to find names starting with 'A' yields 16 matches", "Using the like operator to locate names starting with 'S' returns 10 matching employees", "Using the like operator to find names with 'U' as the second letter yields 10 matches", "Using the like operator to find names starting with 'A' yields 16 matches"]}, {'end': 21813.706, 'segs': [{'end': 20914.377, 'src': 'embed', 'start': 20888.562, 'weight': 0, 'content': [{'end': 20895.565, 'text': 'for example, you can find the average salary of employees in each country or city or department.', 'start': 20888.562, 'duration': 7.003}, {'end': 20901.668, 'text': 'so the group by clause is used in collaboration with the select statement to arrange identical data into groups.', 'start': 20895.565, 'duration': 6.103}, {'end': 20908.69, 'text': 'so Suppose you want to find the average salary of the employees based on countries.', 'start': 20901.668, 'duration': 7.022}, {'end': 20909.992, 'text': 'you can use the group by clause.', 'start': 20908.69, 'duration': 1.302}, {'end': 20911.794, 'text': 'So let me show you how to do it.', 'start': 20910.553, 'duration': 1.241}, {'end': 20914.377, 'text': "I'll write select.", 'start': 20913.035, 'duration': 1.342}], 'summary': 'Using group by clause to find average salary by country, city, or department.', 'duration': 25.815, 'max_score': 20888.562, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20888562.jpg'}, {'end': 21059.893, 'src': 'embed', 'start': 21023.452, 'weight': 1, 'content': [{'end': 21026.234, 'text': "now let's see one more example, using group by.", 'start': 21023.452, 'duration': 2.782}, {'end': 21030.877, 'text': 'suppose this time you want to find the maximum salary of male and female employees.', 'start': 21026.234, 'duration': 4.643}, {'end': 21031.798, 'text': 'you can do that too.', 'start': 21030.877, 'duration': 0.921}, {'end': 21033.919, 'text': 'so let me show you how to do it.', 'start': 21032.478, 'duration': 1.441}, {'end': 21035.701, 'text': "so I'll write select.", 'start': 21033.919, 'duration': 1.782}, {'end': 21038.584, 'text': 'this time we want to find the maximum salary based on gender.', 'start': 21035.701, 'duration': 2.883}, {'end': 21044.95, 'text': "so I'll select my gender column comma and this time I'll use my max function.", 'start': 21038.584, 'duration': 6.366}, {'end': 21059.893, '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': 21044.95, 'duration': 14.943}], 'summary': 'Demonstrating use of group by to find maximum salary by gender.', 'duration': 36.441, 'max_score': 21023.452, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21023452.jpg'}, {'end': 21118.12, 'src': 'embed', 'start': 21088.632, 'weight': 2, 'content': [{'end': 21093.333, 'text': 'all right, now, suppose you want to find the count of employees based on each country.', 'start': 21088.632, 'duration': 4.701}, {'end': 21096.754, 'text': 'you can use the count function along with the group by clause.', 'start': 21093.333, 'duration': 3.421}, {'end': 21104.836, 'text': "so i'll write the select statement select, since i want to count the employees based on each country.", 'start': 21096.754, 'duration': 8.082}, {'end': 21111.057, 'text': "so i'll first select my country column and then i'm going to use the count function.", 'start': 21104.836, 'duration': 6.221}, {'end': 21118.12, 'text': "i'll write count emp, underscore id from my table name, that is, employees.", 'start': 21111.057, 'duration': 7.063}], 'summary': 'Use count function with group by clause to find employee count per country.', 'duration': 29.488, 'max_score': 21088.632, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21088632.jpg'}, {'end': 21174.057, 'src': 'embed', 'start': 21145.581, 'weight': 3, 'content': [{'end': 21148.065, 'text': 'let me scroll down.', 'start': 21145.581, 'duration': 2.484}, {'end': 21156.616, 'text': "okay, now it's time to explore one more clause, a very important clause that is used in postgresql, that is, having.", 'start': 21148.065, 'duration': 8.551}, {'end': 21159.239, 'text': 'so the having clause works like the where clause.', 'start': 21156.616, 'duration': 2.623}, {'end': 21162.504, 'text': 'the difference is that where clause cannot be used with aggregate functions,', 'start': 21159.239, 'duration': 3.265}, {'end': 21167.409, 'text': 'The having clause is used with the group by clause to return those rows that meet a condition.', 'start': 21163.163, 'duration': 4.246}, {'end': 21174.057, 'text': 'So suppose you want to find the countries in which the average salary is greater than $80, 000.', 'start': 21168.23, 'duration': 5.827}], 'summary': "The 'having' clause in postgresql is used with the group by clause to return rows meeting a condition, such as finding countries with average salary > $80,000.", 'duration': 28.476, 'max_score': 21145.581, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21145581.jpg'}, {'end': 21269.568, 'src': 'embed', 'start': 21241.599, 'weight': 4, 'content': [{'end': 21244.582, 'text': 'let me just run it now.', 'start': 21241.599, 'duration': 2.983}, {'end': 21245.223, 'text': 'there you go.', 'start': 21244.582, 'duration': 0.641}, {'end': 21251.591, 'text': 'so we have russia and united states where the average salary is greater than eighty thousand dollars.', 'start': 21245.223, 'duration': 6.368}, {'end': 21251.851, 'text': 'all right.', 'start': 21251.591, 'duration': 0.26}, {'end': 21263.983, 'text': "now, let's say you want to find the count of employees in each country where there are less than 30 employees.", 'start': 21254.656, 'duration': 9.327}, {'end': 21267.026, 'text': 'so for this I am going to use the count function.', 'start': 21263.983, 'duration': 3.043}, {'end': 21269.568, 'text': 'first let me select the country column.', 'start': 21267.026, 'duration': 2.542}], 'summary': 'Russia and united states have average salary > $80,000. count employees in each country with < 30 employees using count function.', 'duration': 27.969, 'max_score': 21241.599, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21241599.jpg'}, {'end': 21390.07, 'src': 'embed', 'start': 21307.541, 'weight': 5, 'content': [{'end': 21313.205, 'text': 'so this will return me the countries in which there are less than 30 employees.', 'start': 21307.541, 'duration': 5.664}, {'end': 21315.107, 'text': "let's run it.", 'start': 21313.205, 'duration': 1.902}, {'end': 21320.251, 'text': 'you can see here Israel, Australia, United States and Germany are the countries in which there are less than 30 employees.', 'start': 21315.107, 'duration': 5.144}, {'end': 21325.699, 'text': 'okay, now, if you want, you can use the order by clause as well.', 'start': 21321.477, 'duration': 4.222}, {'end': 21338.123, 'text': 'so suppose I will write here order by count of employee ID.', 'start': 21325.699, 'duration': 12.424}, {'end': 21347.654, 'text': 'so what this will do is it will arrange my result in ascending order of employee ID count.', 'start': 21338.123, 'duration': 9.531}, {'end': 21348.315, 'text': 'there you can see.', 'start': 21347.654, 'duration': 0.661}, {'end': 21357.167, 'text': 'we have successfully arranged our result in ascending order of employee IDs.', 'start': 21348.315, 'duration': 8.852}, {'end': 21365.955, 'text': 'okay, next we are going to explore One more feature of PostgreSQL, that is of using a case statement.', 'start': 21357.167, 'duration': 8.788}, {'end': 21372.319, 'text': 'Now in PostgreSQL the case expression is same as if else statement in any other programming language.', 'start': 21366.835, 'duration': 5.484}, {'end': 21377.262, 'text': 'It allows you to add if else logic to the query to form a powerful query.', 'start': 21373.099, 'duration': 4.163}, {'end': 21383.326, 'text': "Now let me just scroll down and I'll show you how to use a case statement.", 'start': 21379.563, 'duration': 3.763}, {'end': 21390.07, 'text': 'This is very similar to your if else statement that you use on Excel in C++ in Python and or any other programming language.', 'start': 21384.106, 'duration': 5.964}], 'summary': 'Using postgresql to filter countries with less than 30 employees and demonstrate ordering and case statements.', 'duration': 82.529, 'max_score': 21307.541, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21307541.jpg'}, {'end': 21441.933, 'src': 'embed', 'start': 21416.175, 'weight': 6, 'content': [{'end': 21424.141, 'text': 'now, if the salary is greater than $55, 000 and if it is less than $80, 000, we are going to assign a value that is medium salary.', 'start': 21416.175, 'duration': 7.966}, {'end': 21428.604, 'text': "if the salary is greater than $80, 000, we'll assign a value high salary.", 'start': 21424.141, 'duration': 4.463}, {'end': 21433.527, 'text': 'so all this we are going to do using our case expression in PostgreSQL.', 'start': 21428.604, 'duration': 4.923}, {'end': 21437.45, 'text': "so I'll start with my select statement.", 'start': 21433.527, 'duration': 3.923}, {'end': 21441.933, 'text': 'but before that, let me show you how to write a comment in PostgreSQL.', 'start': 21437.45, 'duration': 4.483}], 'summary': 'Using a case expression in postgresql to categorize salaries into low, medium, and high based on specific ranges.', 'duration': 25.758, 'max_score': 21416.175, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21416175.jpg'}, {'end': 21721.762, 'src': 'embed', 'start': 21699.362, 'weight': 7, 'content': [{'end': 21708.15, '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': 21699.362, 'duration': 8.788}, {'end': 21710.552, 'text': 'So in such cases, you can use sub queries.', 'start': 21708.61, 'duration': 1.942}, {'end': 21713.614, 'text': 'Now let me show you how to write a query inside another query.', 'start': 21710.992, 'duration': 2.622}, {'end': 21717.218, 'text': "first i'll write the select statement.", 'start': 21714.395, 'duration': 2.823}, {'end': 21721.762, 'text': 'i am going to select the employee name comma.', 'start': 21717.218, 'duration': 4.544}], 'summary': 'Using subqueries to find employees with salary > average.', 'duration': 22.4, 'max_score': 21699.362, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21699362.jpg'}], 'start': 20860.074, 'title': 'Sql group by, having, count functions and case expressions', 'summary': 'Introduces sql group by and having clauses, exploring their usage to calculate statistics, such as average and maximum salary, and count of employees based on different categories in postgresql, along with examples of using having clause to filter rows based on aggregate functions and writing subqueries to find employees with salaries greater than the average salary.', 'chapters': [{'end': 21143.816, 'start': 20860.074, 'title': 'Understanding sql group by clause', 'summary': 'Introduces the group by and having clauses in sql, demonstrating their use to group data and calculate statistics, such as average and maximum salary, and count of employees based on different categories, with examples of their application in postgresql, including finding average salary by country and maximum salary by gender.', 'duration': 283.742, 'highlights': ['The group by clause is used to arrange identical data into groups, such as finding the average salary of employees based on countries.', 'The order by clause can be used after the group by clause to arrange the results based on a specific column, such as arranging the average salary in descending order.', 'Using the group by clause and select statement, one can find the maximum salary for male and female employees, as demonstrated by calculating the maximum salary for each gender in the given dataset.', 'The count function, in combination with the group by clause and select statement, can be utilized to find the count of employees based on different categories, as showcased by counting the employees from each country in the dataset.']}, {'end': 21390.07, 'start': 21145.581, 'title': 'Postgresql: exploring having and count functions', 'summary': 'Explores the usage of the having clause in postgresql to filter rows based on aggregate functions, demonstrating examples of finding countries with average salary greater than $80,000 and countries with less than 30 employees, and also introduces the usage of the order by clause and the case statement.', 'duration': 244.489, 'highlights': ['The having clause works like the where clause, used with the group by clause to return rows meeting a condition.', 'Using the having clause to find countries with average salary greater than $80,000, resulting in Russia and United States meeting the condition.', 'Demonstrating the usage of the count function and the having clause to find countries with less than 30 employees.', 'Introducing the usage of the order by clause to arrange results in ascending order based on employee ID count.', 'Introducing the usage of case statement in PostgreSQL to add if-else logic to the queries.']}, {'end': 21813.706, 'start': 21391.445, 'title': 'Sql case expression and subqueries', 'summary': "Demonstrates how to use case expressions in postgresql to create a new column 'salary range' based on salary conditions, followed by an example of writing subqueries to find employees with salaries greater than the average salary.", 'duration': 422.261, 'highlights': ["Using case expressions in PostgreSQL to create a new column 'salary range' based on salary conditions", 'Demonstrating the use of comments in PostgreSQL for script readability', 'Illustrating the concept of subqueries to find employees with salaries greater than the average salary']}], 'duration': 953.632, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU20860074.jpg', '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 and select statement, one can find the maximum salary for male and female employees, as demonstrated by calculating the maximum salary for each gender in the given dataset.', 'The count function, in combination with the group by clause and select statement, can be utilized to find the count of employees based on different categories, as showcased by counting the employees from each country in the dataset.', 'The having clause works like the where clause, used with the group by clause to return rows meeting a condition.', 'Using the having clause to find countries with average salary greater than $80,000, resulting in Russia and United States meeting the condition.', 'Demonstrating the usage of the count function and the having clause to find countries with less than 30 employees.', "Using case expressions in PostgreSQL to create a new column 'salary range' based on salary conditions", 'Illustrating the concept of subqueries to find employees with salaries greater than the average salary', 'Introducing the usage of the order by clause to arrange results in ascending order based on employee ID count.', 'Introducing the usage of case statement in PostgreSQL to add if-else logic to the queries.']}, {'end': 23330.007, 'segs': [{'end': 21838.581, 'src': 'embed', 'start': 21813.706, 'weight': 0, 'content': [{'end': 21821.268, 'text': 'so we have around 75 employees whose average salary or whose salary is greater than the average salary.', 'start': 21813.706, 'duration': 7.562}, {'end': 21828.919, 'text': "all right now, moving ahead, this time I'm going to tell you how to use some inbuilt functions.", 'start': 21822.717, 'duration': 6.202}, {'end': 21834.86, 'text': 'we learn some inbuilt mathematical functions and string functions that are available in Postgres SQL.', 'start': 21828.919, 'duration': 5.941}, {'end': 21837.061, 'text': "so I'll just give a comment.", 'start': 21834.86, 'duration': 2.201}, {'end': 21838.581, 'text': "there's another way to write a comment.", 'start': 21837.061, 'duration': 1.52}], 'summary': 'Around 75 employees earn above average salary. learning inbuilt functions in postgres sql.', 'duration': 24.875, 'max_score': 21813.706, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21813706.jpg'}, {'end': 22186.309, 'src': 'embed', 'start': 22159.116, 'weight': 3, 'content': [{'end': 22162.557, 'text': 'or the nearest lowest integer to any provided decimal value.', 'start': 22159.116, 'duration': 3.441}, {'end': 22171.702, 'text': 'Okay Now that we saw how to use mathematical functions, there are a few string functions available in Postgres SQL.', 'start': 22163.758, 'duration': 7.944}, {'end': 22173.603, 'text': "So let's explore them as well.", 'start': 22172.202, 'duration': 1.401}, {'end': 22175.204, 'text': "I'll write string functions.", 'start': 22173.883, 'duration': 1.321}, {'end': 22180.965, 'text': 'okay, scroll down, cool.', 'start': 22177.262, 'duration': 3.703}, {'end': 22186.309, 'text': "there's a function called character length that gives you the length of a text string.", 'start': 22180.965, 'duration': 5.344}], 'summary': 'Introduction to postgres sql string functions and character length function.', 'duration': 27.193, 'max_score': 22159.116, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU22159116.jpg'}, {'end': 22770.994, 'src': 'embed', 'start': 22744.676, 'weight': 1, 'content': [{'end': 22750.063, 'text': 'so here you can see there are 134 email ids present in our employees table.', 'start': 22744.676, 'duration': 5.387}, {'end': 22757.727, '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': 22750.063, 'duration': 7.664}, {'end': 22764.47, 'text': "the rest of them don't have, so they would ideally have null values alright.", 'start': 22757.727, 'duration': 6.743}, {'end': 22769.693, 'text': 'so that brings us to the end of this demo session on postgres sql tutorial.', 'start': 22764.47, 'duration': 5.223}, {'end': 22770.994, 'text': 'let me go to the top.', 'start': 22769.693, 'duration': 1.301}], 'summary': 'Out of 150 employees, 134 have email ids in the table.', 'duration': 26.318, 'max_score': 22744.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU22744676.jpg'}, {'end': 22896.386, 'src': 'embed', 'start': 22861.313, 'weight': 4, 'content': [{'end': 22871.5, 'text': 'we saw how to use basic inbuilt PostgreSQL functions like sum, average minimum count maximum.', 'start': 22861.313, 'duration': 10.187}, {'end': 22880.015, 'text': 'next, we saw how to update a value in a column using postgresql update command.', 'start': 22871.5, 'duration': 8.515}, {'end': 22882.657, 'text': 'we learnt how to use groupby.', 'start': 22880.015, 'duration': 2.642}, {'end': 22886.219, 'text': 'then we learnt how to use having clause.', 'start': 22882.657, 'duration': 3.562}, {'end': 22889.541, 'text': 'then we learnt how to use case expressions in postgresql.', 'start': 22886.219, 'duration': 3.322}, {'end': 22896.386, 'text': 'so we saw how case expression is similar to our ifelse in any other programming language.', 'start': 22889.541, 'duration': 6.845}], 'summary': 'Learned postgresql functions: sum, average, min, max, update, groupby, having, case expressions.', 'duration': 35.073, 'max_score': 22861.313, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU22861313.jpg'}, {'end': 22948.703, 'src': 'embed', 'start': 22919.847, 'weight': 2, 'content': [{'end': 22925.912, 'text': 'In this session, we will learn who is the SQL developer and what are the responsibilities in a company.', 'start': 22919.847, 'duration': 6.065}, {'end': 22930.515, 'text': 'Then we will focus on the skills required to become a successful SQL developer.', 'start': 22926.492, 'duration': 4.023}, {'end': 22934.817, 'text': 'We will see the expected average salary in both US and India.', 'start': 22931.075, 'duration': 3.742}, {'end': 22941.52, 'text': 'We will also discuss how to become a SQL developer and look at the top companies hiring for SQL developers.', 'start': 22935.457, 'duration': 6.063}, {'end': 22948.703, 'text': 'So hey everyone, I am Abhisar Ahuja from Simply Learn and welcome to this video on how to become a SQL developer.', 'start': 22942.14, 'duration': 6.563}], 'summary': 'Learn about sql developer roles, skills, salaries, and job opportunities.', 'duration': 28.856, 'max_score': 22919.847, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU22919847.jpg'}], 'start': 21813.706, 'title': 'Postgresql sql functions, usage, and developer role', 'summary': 'Covers inbuilt mathematical and string functions in postgres sql, with an average salary of around 75 employees being greater than the average salary. additionally, it discusses various postgresql functions and their usage, including user-defined functions, resulting in 134 email ids out of 150 employees. furthermore, it explains the role and responsibilities of a sql developer, required skills, and average salaries in the us and india.', 'chapters': [{'end': 21868.275, 'start': 21813.706, 'title': 'Postgres sql functions', 'summary': 'Covers the usage of inbuilt mathematical and string functions in postgres sql, with an average salary of around 75 employees being greater than the average salary.', 'duration': 54.569, 'highlights': ['In Postgres SQL, around 75 employees have salaries greater than the average salary.', 'The chapter demonstrates the usage of inbuilt mathematical and string functions in Postgres SQL.', 'A detailed explanation of writing comments using forward slash and asterisk in Postgres SQL is provided.']}, {'end': 22294.602, 'start': 21869.192, 'title': 'Postgresql functions and usage', 'summary': 'Discusses various postgresql functions including abs, greatest, least, mod, power, sqrt, sine, cos, tan, ceiling, floor, character length, and concat, providing examples and their respective results.', 'duration': 425.41, 'highlights': ["The greatest function in PostgreSQL returns the greatest number in a range of numbers, for example, using 'select greatest' with values 4, 90, 56.5, and 70 will result in 90 as the largest number.", "The least function in PostgreSQL returns the least number present in a range of numbers, for example, using 'select least' with values 2, 4, 90, 56.5, and 70 will result in 2 as the least number.", "The mod function in PostgreSQL returns the remainder of a division, for example, using 'select mod 54, divided by 10' will result in the remainder 4.", "The power function in PostgreSQL is used to find the power of a number, for example, using 'select power 2, 3' will result in 8, and 'select power 5, 3' will result in 125.", "The sqrt function in PostgreSQL is used to find the square root of a number, for example, using 'sqrt 100' will result in 10, and 'sqrt 144' will result in 12.", "Trigonometric functions like sine, cos, and tan are available in PostgreSQL, for example, using 'sine 0' will result in 0, and 'sine 90' will result in 0.89.", "The ceiling function in PostgreSQL returns the next highest integer, for example, using 'ceiling 6.45' will result in 7.", "The floor function in PostgreSQL returns the next lowest integer, for example, using 'floor 6.45' will result in 6.", "The character length function in PostgreSQL provides the length of a text string, for example, using 'character length' with the text 'India is a democracy' will result in 20.", "The concat function in PostgreSQL is used to merge or combine multiple strings, for example, using 'concat' with the strings 'Postgres SQL', 'is', and 'interesting' will result in 'Postgres SQL is interesting'."]}, {'end': 22919.427, 'start': 22294.602, 'title': 'Postgresql functions and user defined function', 'summary': 'Covers the usage of postgresql string functions like left, right, mid, repeat, and reverse along with creating a user-defined function to count the total number of email ids in the employees table, resulting in 134 email ids out of 150 employees, and concludes with a review of the tutorial content.', 'duration': 624.825, 'highlights': ['Created a user-defined function to count the total number of email ids present in the employees table, resulting in 134 email ids out of 150 employees.', 'Explained the usage of PostgreSQL string functions like left, right, repeat, and reverse.', 'Covered a wide range of topics in PostgreSQL including basic mathematical operations, table creation, data manipulation, WHERE clause, DISTINCT keyword, ORDER BY clause, and various operators and functions.']}, {'end': 23330.007, 'start': 22919.847, 'title': 'Sql developer role and skills', 'summary': 'Explains the role and responsibilities of a sql developer, required skills, and average salaries in the us and india, highlighting the essential sql commands, database management systems, and major companies hiring for sql developers.', 'duration': 410.16, 'highlights': ['SQL developers in the United States earn an average salary of $72,282 per annum, while in India, the average salary is Rs 4,49,532.', 'The essential SQL commands include create, drop, alter, truncate, insert, update, delete, grant, revoke, commit, rollback, and save point commands.', 'Major companies recruiting SQL developers include American Express, Ernst & Young, WhatsApp, Amazon, Dell, and Google.']}], 'duration': 1516.301, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU21813706.jpg', 'highlights': ['Around 75 employees in Postgres SQL have salaries greater than the average salary', 'Created a user-defined function resulting in 134 email ids out of 150 employees', 'Explained the role and responsibilities of SQL developers in the US and India', 'Covered inbuilt mathematical and string functions in Postgres SQL', 'Discussed various PostgreSQL functions and their usage']}, {'end': 24276.005, 'segs': [{'end': 23478.562, 'src': 'embed', 'start': 23430.848, 'weight': 0, 'content': [{'end': 23441.494, 'text': 'Now, another important difference between WHERE and HAVING is that the WHERE clause is executed before the execution of the GROUP BY clause and after the execution of the FROM clause.', 'start': 23430.848, 'duration': 10.646}, {'end': 23445.176, 'text': 'On the other hand, the HAVING clause is executed after groups are created.', 'start': 23441.494, 'duration': 3.682}, {'end': 23454.781, 'text': 'Now we will learn more about WHERE and HAVING and understand the usage better when we tackle practical problems in this video.', 'start': 23445.176, 'duration': 9.605}, {'end': 23460.244, 'text': 'Now moving to our next question, which is How is DROP different from TRUNCATE?', 'start': 23454.781, 'duration': 5.463}, {'end': 23463.811, 'text': 'So drop and truncate are two different commands.', 'start': 23461.189, 'duration': 2.622}, {'end': 23472.558, 'text': 'So drop command is used to drop the whole table, removing the table definition and its contents, while truncate deletes all the rows from the table.', 'start': 23464.892, 'duration': 7.666}, {'end': 23478.562, 'text': 'We can drop the whole table structure in one go, so the view of the table does not exist.', 'start': 23473.879, 'duration': 4.683}], 'summary': 'The where and having clauses differ in execution timing. drop removes table structure, while truncate deletes table rows.', 'duration': 47.714, 'max_score': 23430.848, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU23430848.jpg'}, {'end': 23685.991, 'src': 'embed', 'start': 23655.384, 'weight': 2, 'content': [{'end': 23664.095, 'text': "I'll select my department column, which is DEPT comma, and then to calculate the lowest salary or to find the lowest salary,", 'start': 23655.384, 'duration': 8.711}, {'end': 23666.037, 'text': "i'm going to use the min function.", 'start': 23664.095, 'duration': 1.942}, {'end': 23671.221, 'text': 'so that will return the minimum salary present in the salary column.', 'start': 23666.037, 'duration': 5.184}, {'end': 23685.991, 'text': "so i'll write min salary, as i'm giving an alias name, as, let's say, lowest underscore salary from my table name, that is, employees.", 'start': 23671.221, 'duration': 14.77}], 'summary': 'Selects lowest salary using min function from department column in employees table.', 'duration': 30.607, 'max_score': 23655.384, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU23655384.jpg'}, {'end': 23789.185, 'src': 'embed', 'start': 23766.536, 'weight': 3, 'content': [{'end': 23779.581, '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': 23766.536, 'duration': 13.045}, {'end': 23783.743, 'text': 'so if I write select distinct, followed by the column name,', 'start': 23779.581, 'duration': 4.162}, {'end': 23789.185, 'text': "let's say you want to find the distinct cities or the distinct departments in the employees table.", 'start': 23783.743, 'duration': 5.442}], 'summary': "Use 'distinct' keyword to get unique values from a column in a table.", 'duration': 22.649, 'max_score': 23766.536, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU23766536.jpg'}, {'end': 24034.639, 'src': 'embed', 'start': 24011.302, 'weight': 4, 'content': [{'end': 24017.973, 'text': "so first i'm finding the unique departments and then, using the length function, i'm finding the length of each department.", 'start': 24011.302, 'duration': 6.671}, {'end': 24020.074, 'text': "let's run and see.", 'start': 24017.973, 'duration': 2.101}, {'end': 24022.254, 'text': 'okay, you can see the result here.', 'start': 24020.074, 'duration': 2.18}, {'end': 24025.196, 'text': 'so sales, the length is 5.', 'start': 24022.254, 'duration': 2.942}, {'end': 24027.617, 'text': 'marketing, the length of marketing is 9.', 'start': 24025.196, 'duration': 2.421}, {'end': 24032.759, 'text': 'so basically you are counting the number of characters that are present in each of the departments.', 'start': 24027.617, 'duration': 5.142}, {'end': 24034.639, 'text': 'so in sales there are total 5 characters.', 'start': 24032.759, 'duration': 1.88}], 'summary': 'Analyzing unique departments: sales (5), marketing (9) characters.', 'duration': 23.337, 'max_score': 24011.302, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24011302.jpg'}, {'end': 24086.628, 'src': 'embed', 'start': 24057.906, 'weight': 5, 'content': [{'end': 24068.389, 'text': 'all right now, moving to the seventh question, the question is what is the use of dative function in sql?', 'start': 24057.906, 'duration': 10.483}, {'end': 24076.6, 'text': 'Now, date diff returns the number of days between two, date or date, time or timestamp values.', 'start': 24069.434, 'duration': 7.166}, {'end': 24083.526, 'text': 'So date diff is another crucial inbuilt function that is present in MySQL.', 'start': 24077.08, 'duration': 6.446}, {'end': 24086.628, 'text': 'So here is how you can use the date diff function.', 'start': 24084.507, 'duration': 2.121}], 'summary': 'Date diff function in sql returns the number of days between two date, time, or timestamp values. it is a crucial inbuilt function in mysql.', 'duration': 28.722, 'max_score': 24057.906, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24057906.jpg'}], 'start': 23330.908, 'title': 'Sql concepts and functions', 'summary': 'Covers 30 sql interview questions, including differences between where and having clauses, drop and truncate commands, using min function to find lowest salaries for 7 departments, and applying sql distinct keyword and inbuilt functions to retrieve unique values and perform calculations on department data.', 'chapters': [{'end': 23502.08, 'start': 23330.908, 'title': 'Sql interview questions', 'summary': 'Covers 30 important sql questions frequently asked in interviews, addressing the difference between where and having clauses, and the distinctions between drop and truncate commands.', 'duration': 171.172, 'highlights': ['The WHERE and HAVING clauses are explained, highlighting their differences in functionality, application, and execution order within SQL, essential for professionals working as application developers, data analysts, and software testers.', 'The distinction between DROP and TRUNCATE commands is detailed, covering their functionalities in removing table data and structure, as well as the handling of integrity constraints, pertinent for SQL practitioners in various roles.']}, {'end': 23746.323, 'start': 23502.08, 'title': 'Finding lowest salary for each department', 'summary': 'Demonstrates how to use the min function in mysql to find the lowest salary for employees in each department, resulting in 7 departments with their respective lowest salaries.', 'duration': 244.243, 'highlights': ['The chapter demonstrates how to use the min function in MySQL to find the lowest salary for employees in each department, resulting in 7 departments with their respective lowest salaries.', 'The employees table contains 20 rows with columns such as employee id, employee name, age, gender, department, city, and salary.', "The query uses the min function to calculate the lowest salary, assigns an alias 'lowest_salary', and groups the results by department, displaying the lowest salaries for each department."]}, {'end': 24276.005, 'start': 23746.323, 'title': 'Using sql distinct and inbuilt functions', 'summary': 'Covers the usage of sql distinct keyword to fetch unique values from a column, including an example of finding 7 unique departments out of 20 records, and demonstrates the application of inbuilt functions such as length and date diff to find the length of departments and the number of days between dates, respectively.', 'duration': 529.682, 'highlights': ['Demonstrating the usage of distinct keyword to fetch unique values from a column, including finding 7 unique departments out of 20 records.', 'Applying the inbuilt length function to find the length of each department, displaying the number of characters in each department.', 'Illustrating the use of date diff function to calculate the total number of days between specific dates, for example, 10 days between 10th April 2021 and 30th March 2021.']}], 'duration': 945.097, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU23330908.jpg', 'highlights': ['The WHERE and HAVING clauses are explained, essential for SQL professionals.', 'The distinction between DROP and TRUNCATE commands is detailed, pertinent for SQL practitioners.', 'Using the min function to find lowest salaries for 7 departments is demonstrated.', 'Demonstrating the usage of distinct keyword to fetch unique values from a column.', 'Applying the inbuilt length function to find the length of each department.', 'Illustrating the use of date diff function to calculate the total number of days between specific dates.']}, {'end': 26050.532, 'segs': [{'end': 24406.156, 'src': 'embed', 'start': 24381.285, 'weight': 0, 'content': [{'end': 24387.266, 'text': 'so our query will return only those departments which have more than two employees.', 'start': 24381.285, 'duration': 5.981}, {'end': 24389.007, 'text': "let's run and see.", 'start': 24387.266, 'duration': 1.741}, {'end': 24390.167, 'text': 'there you go.', 'start': 24389.007, 'duration': 1.16}, {'end': 24398.994, 'text': 'so we have total four departments, that is, sales, product, tech and it, where we have more than two employees.', 'start': 24390.167, 'duration': 8.827}, {'end': 24406.156, 'text': 'you can see here sales has four employees, product has three, tech has four and IT has three.', 'start': 24398.994, 'duration': 7.162}], 'summary': 'Query identified 4 departments with more than 2 employees: sales (4), product (3), tech (4), it (3).', 'duration': 24.871, 'max_score': 24381.285, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24381285.jpg'}, {'end': 24546.499, 'src': 'embed', 'start': 24508.431, 'weight': 1, 'content': [{'end': 24521.165, 'text': "where department not equal to marketing, i'll put marketing under quotes.", 'start': 24508.431, 'duration': 12.734}, {'end': 24525.266, 'text': "let's run it and see the results.", 'start': 24521.165, 'duration': 4.101}, {'end': 24526.506, 'text': 'there you go.', 'start': 24525.266, 'duration': 1.24}, {'end': 24540.03, 'text': 'so we have total 18 rows of information and if you see the department column, we have all the departments apart from marketing.', 'start': 24526.506, 'duration': 13.524}, {'end': 24546.499, 'text': 'now, if you remember, our employees table had total 20 rows of information, out of which two are missing,', 'start': 24540.03, 'duration': 6.469}], 'summary': 'Query resulted in 18 rows of non-marketing department data, leaving 2 rows missing.', 'duration': 38.068, 'max_score': 24508.431, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24508431.jpg'}, {'end': 24668.945, 'src': 'embed', 'start': 24612.155, 'weight': 2, 'content': [{'end': 24623.721, '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': 24612.155, 'duration': 11.566}, {'end': 24630.306, 'text': 'so I have written after 31st of May 2005 and before April 2010, which is before 31st of March 2010.', 'start': 24623.721, 'duration': 6.585}, {'end': 24630.766, 'text': "let's do this.", 'start': 24630.306, 'duration': 0.46}, {'end': 24645.07, 'text': "I'll give a comment.", 'start': 24630.766, 'duration': 14.304}, {'end': 24667.104, 'text': 'here will write employees joined before April 2010 and after May 2005.', 'start': 24645.07, 'duration': 22.034}, {'end': 24668.945, 'text': "now let's write our SQL query.", 'start': 24667.104, 'duration': 1.841}], 'summary': 'Filter employees who joined after may 2005 and before april 2010 using sql query.', 'duration': 56.79, 'max_score': 24612.155, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24612155.jpg'}, {'end': 24817.69, 'src': 'embed', 'start': 24789.117, 'weight': 3, 'content': [{'end': 24799.464, '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': 24789.117, 'duration': 10.347}, {'end': 24813.487, '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': 24799.464, 'duration': 14.023}, {'end': 24817.69, 'text': "and from that i'm going to filter out my third highest salary employee.", 'start': 24813.487, 'duration': 4.203}], 'summary': 'Demonstrating sub queries in sql to find top 3 employees with highest salaries.', 'duration': 28.573, 'max_score': 24789.117, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24789117.jpg'}, {'end': 25343.833, 'src': 'embed', 'start': 25293.789, 'weight': 4, 'content': [{'end': 25295.829, 'text': "so let's run it and see the results.", 'start': 25293.789, 'duration': 2.04}, {'end': 25296.77, 'text': 'there you go.', 'start': 25295.829, 'duration': 0.941}, {'end': 25304.752, 'text': 'so here, if you mark, i have my row number column created and i am filtering only the odd records in the table.', 'start': 25296.77, 'duration': 7.982}, {'end': 25315.036, 'text': 'so it starts with 1, 3, 5, 7 and goes up to 19, and similarly, here you can see the employee ids, the name, the age, gender and other information.', 'start': 25304.752, 'duration': 10.284}, {'end': 25325.484, 'text': 'now, if you were to print the even records in the table, the alternate even records, you can use rn, %2, equal to 0.', 'start': 25315.036, 'duration': 10.448}, {'end': 25328.166, 'text': "let's see the difference.", 'start': 25325.484, 'duration': 2.682}, {'end': 25329.887, 'text': "I'll run this.", 'start': 25328.166, 'duration': 1.721}, {'end': 25330.947, 'text': 'there you go.', 'start': 25329.887, 'duration': 1.06}, {'end': 25338.17, 'text': 'so I have my even row number starting from 2, which goes up to 20.', 'start': 25330.947, 'duration': 7.223}, {'end': 25343.833, 'text': 'okay, so this is how you can print alternate records in a table.', 'start': 25338.17, 'duration': 5.663}], 'summary': 'Filtering odd and even records in a table, resulting in 10 odd and 10 even rows.', 'duration': 50.044, 'max_score': 25293.789, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU25293789.jpg'}, {'end': 25430.415, 'src': 'embed', 'start': 25401.667, 'weight': 6, 'content': [{'end': 25410.709, 'text': 'so i am going to select my employee id, name and age, and then count all the employee ids, the names and age,', 'start': 25401.667, 'duration': 9.042}, {'end': 25413.85, 'text': 'and compare them with whether they are greater than one or not.', 'start': 25410.709, 'duration': 3.141}, {'end': 25423.048, 'text': "so if they are greater than one, then I'll see that particular record is duplicated or is present more than one time in the table.", 'start': 25413.85, 'duration': 9.198}, {'end': 25425.811, 'text': "let's do this.", 'start': 25423.048, 'duration': 2.763}, {'end': 25430.415, 'text': 'implementation on our workbench, okay.', 'start': 25425.811, 'duration': 4.604}], 'summary': 'Select employee id, name, age, count and compare for duplicates.', 'duration': 28.748, 'max_score': 25401.667, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU25401667.jpg'}, {'end': 25769.287, 'src': 'embed', 'start': 25724.75, 'weight': 8, 'content': [{'end': 25732.454, 'text': "okay, now let me go ahead and write a sql query and then i'll explain you what i'm trying to do.", 'start': 25724.75, 'duration': 7.704}, {'end': 25734.935, 'text': "i'll write select.", 'start': 25732.454, 'duration': 2.481}, {'end': 25743.559, 'text': "i'm using the length function and in the length function i'm going to replace.", 'start': 25734.935, 'duration': 8.624}, {'end': 25760.923, 'text': "then i'll use another function called upper, on top of employee name, write a and then replace it with a blank it.", 'start': 25743.559, 'duration': 17.364}, {'end': 25769.287, 'text': "so I'm using quotations to represent blank from employees.", 'start': 25760.923, 'duration': 8.364}], 'summary': 'Demonstrating sql query with length and replace functions on employee names.', 'duration': 44.537, 'max_score': 25724.75, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU25724750.jpg'}, {'end': 25881.844, 'src': 'embed', 'start': 25853.309, 'weight': 7, 'content': [{'end': 25864.312, 'text': "from that 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': 25853.309, 'duration': 11.003}, {'end': 25868.915, 'text': "The reason being, we are only trying to find the employees who have 2 A's in their name.", 'start': 25864.312, 'duration': 4.603}, {'end': 25871.377, 'text': "Let's run and see the results.", 'start': 25869.796, 'duration': 1.581}, {'end': 25881.844, 'text': "Okay, if you see here, we have Sara, Angela, Amelia and Maya who have 2 A's in their name.", 'start': 25872.998, 'duration': 8.846}], 'summary': "Subtract length of names after replacing a's, equals 2, 4 employees found.", 'duration': 28.535, 'max_score': 25853.309, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU25853309.jpg'}, {'end': 25938.082, 'src': 'embed', 'start': 25911.257, 'weight': 9, 'content': [{'end': 25914.919, 'text': 'he is from germany and also was the captain of germany.', 'start': 25911.257, 'duration': 3.662}, {'end': 25919.682, 'text': 'so from the string michael ballack, i am going to extract four characters starting from the second position.', 'start': 25914.919, 'duration': 4.763}, {'end': 25924.516, 'text': 'you can use two inbuilt mysql functions to do this.', 'start': 25920.694, 'duration': 3.822}, {'end': 25934.98, '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': 25924.516, 'duration': 10.464}, {'end': 25938.082, 'text': 'so here I am going to extract from the second position.', 'start': 25934.98, 'duration': 3.102}], 'summary': 'Michael ballack, captain of germany, extracted 4 characters from 2nd position.', 'duration': 26.825, 'max_score': 25911.257, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU25911257.jpg'}], 'start': 24276.005, 'title': 'Sql query techniques', 'summary': 'Covers sql query techniques such as filtering, subqueries, common table expressions, and string manipulation. it includes examples of writing sql queries to display specific data, using row number for filtering, and addressing interview questions on sql queries.', 'chapters': [{'end': 24763.841, 'start': 24276.005, 'title': 'Sql queries and data analysis', 'summary': 'Covers writing sql queries to display departments with more than two employees, details of employees for all departments except marketing, and details of employees who joined before april 2010 and after may 2005.', 'duration': 487.836, 'highlights': ['The SQL query to display departments with more than two employees returned four departments: sales (4 employees), product (3 employees), tech (4 employees), and IT (3 employees).', 'The SQL query to display details of employees for all departments except marketing returned 18 rows of information, excluding employees from the marketing department.', "The SQL query to print details of employees who joined before April 2010 and after May 2005 demonstrated the use of the 'where' condition with date comparisons to filter the results based on join dates."]}, {'end': 25057.895, 'start': 24763.841, 'title': 'Sql subqueries and common table expressions', 'summary': 'Demonstrates using subqueries to find the employee with the third highest salary and printing alternate records from a table, involving sql commands and explanations.', 'duration': 294.054, 'highlights': ['The chapter demonstrates using a subquery to find the employee with the third highest salary by ordering the salaries and using limit and order by commands in SQL.', 'It also illustrates using SQL to print alternate records from a table using the modulus operator or a common table expression (CTE) for filtering even or odd employee IDs.']}, {'end': 25343.833, 'start': 25057.895, 'title': 'Using row number for filtering', 'summary': 'Explains how to use the row number function to filter and print alternate records from a table, resulting in the display of odd and even employee ids, with the odd records being printed using a common table expression (cte).', 'duration': 285.938, 'highlights': ['Using row number and filter clause to print odd records', 'Using row number and modulo to print even records', 'Demonstrating the application of row number for creating row numbers']}, {'end': 25722.545, 'start': 25343.833, 'title': 'Sql interview questions', 'summary': "Covers writing sql queries to fetch duplicate rows and employees with exactly two 'a's in their names, with examples and explanations, including the demonstration of the queries and their results on a mysql workbench.", 'duration': 378.712, 'highlights': ['The query to fetch duplicate rows involves selecting employee ID, name, and age, and using the count function to compare them with whether they are greater than one or not, resulting in the identification of the duplicated record with the employee ID 101, name Sam, and age 40.', "The process to display employees with exactly two 'A's in their names is demonstrated by finding the length of the employee name and then subtracting the length after replacing 'A' with a blank, and if the subtraction is equal to 2, then that particular employee is identified, resulting in the extraction of employees like Sarah and Angela with two 'A's in their names, including uppercase and lowercase instances."]}, {'end': 26050.532, 'start': 25724.75, 'title': 'Sql query functions and string extraction', 'summary': "Explains the use of sql functions like length, replace, upper, substr, and substring to manipulate and extract strings in sql queries, exemplified by finding employees with 2 a's in their names and extracting characters from a given string.", 'duration': 325.782, 'highlights': ["The chapter demonstrates the use of SQL functions like length, replace, and upper to manipulate strings, exemplified by converting employee names to uppercase and replacing 'A' with a blank to find employees with 2 A's in their names.", "It illustrates the use of SQL functions substr and substring to extract characters from a given string, showcased by extracting four characters starting from the second position in the string 'Michael Ballack'.", "The chapter showcases the application of SQL functions like substr and substring to extract characters from a string, as demonstrated by extracting characters starting from the fourth position in the string 'Michael Ballack'."]}], 'duration': 1774.527, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU24276005.jpg', 'highlights': ['The SQL query to display departments with more than two employees returned four departments: sales (4 employees), product (3 employees), tech (4 employees), and IT (3 employees).', 'The SQL query to display details of employees for all departments except marketing returned 18 rows of information, excluding employees from the marketing department.', "The SQL query to print details of employees who joined before April 2010 and after May 2005 demonstrated the use of the 'where' condition with date comparisons to filter the results based on join dates.", 'The chapter demonstrates using a subquery to find the employee with the third highest salary by ordering the salaries and using limit and order by commands in SQL.', 'Using row number and filter clause to print odd records', 'Using row number and modulo to print even records', 'The query to fetch duplicate rows involves selecting employee ID, name, and age, and using the count function to compare them with whether they are greater than one or not, resulting in the identification of the duplicated record with the employee ID 101, name Sam, and age 40.', "The process to display employees with exactly two 'A's in their names is demonstrated by finding the length of the employee name and then subtracting the length after replacing 'A' with a blank, and if the subtraction is equal to 2, then that particular employee is identified, resulting in the extraction of employees like Sarah and Angela with two 'A's in their names, including uppercase and lowercase instances.", "The chapter demonstrates the use of SQL functions like length, replace, and upper to manipulate strings, exemplified by converting employee names to uppercase and replacing 'A' with a blank to find employees with 2 A's in their names.", "It illustrates the use of SQL functions substr and substring to extract characters from a given string, showcased by extracting four characters starting from the second position in the string 'Michael Ballack'.", "The chapter showcases the application of SQL functions like substr and substring to extract characters from a string, as demonstrated by extracting characters starting from the fourth position in the string 'Michael Ballack'."]}, {'end': 28953.299, 'segs': [{'end': 26076.872, 'src': 'embed', 'start': 26050.532, 'weight': 5, 'content': [{'end': 26054.893, 'text': 'so H, A and E are the next three characters starting from the fourth position.', 'start': 26050.532, 'duration': 4.361}, {'end': 26057.834, 'text': 'so hence this is the result.', 'start': 26054.893, 'duration': 2.941}, {'end': 26058.594, 'text': 'all right.', 'start': 26057.834, 'duration': 0.76}, {'end': 26066.986, 'text': 'now, moving ahead, my 16th question is on how does self join work?', 'start': 26058.594, 'duration': 8.392}, {'end': 26070.048, 'text': 'so self join joins a table to itself.', 'start': 26066.986, 'duration': 3.062}, {'end': 26076.872, 'text': "so the table must contain a column x that acts as the primary key and a different column, let's say y,", 'start': 26070.048, 'duration': 6.824}], 'summary': 'Self-join works by joining a table to itself, using a primary key and another column.', 'duration': 26.34, 'max_score': 26050.532, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26050532.jpg'}, {'end': 26394.355, 'src': 'embed', 'start': 26363.454, 'weight': 6, 'content': [{'end': 26369.317, 'text': 'so view is a virtual table that has rows and columns as they are in a real table in the database.', 'start': 26363.454, 'duration': 5.863}, {'end': 26373.659, 'text': 'now we can create a view by selecting fields from one or more tables present in the database.', 'start': 26369.317, 'duration': 4.342}, {'end': 26378.628, 'text': 'next, moving to the 18th question.', 'start': 26375.307, 'duration': 3.321}, {'end': 26384.111, 'text': 'so here we want to write an SQL query to fetch the list of employees with the same salary.', 'start': 26378.628, 'duration': 5.483}, {'end': 26394.355, 'text': 'again I am going to use a self join here, wherein I will join the same employees table, and this is how my query would look like.', 'start': 26384.111, 'duration': 10.244}], 'summary': 'Using sql to fetch list of employees with same salary, employing self join.', 'duration': 30.901, 'max_score': 26363.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26363454.jpg'}, {'end': 26475.138, 'src': 'embed', 'start': 26410.391, 'weight': 14, 'content': [{'end': 26420.176, 'text': "because I want to fetch the list of employees with the same salary and I'm ensuring that the employee IDs from both the tables are different.", 'start': 26410.391, 'duration': 9.785}, {'end': 26422.397, 'text': "let's do it.", 'start': 26420.176, 'duration': 2.221}, {'end': 26438.665, 'text': "I'll give a comment here and write employees with same salary and I'll start with my query.", 'start': 26422.397, 'duration': 16.268}, {'end': 26454.596, 'text': "I'll write select distinct E dot employee ID comma.", 'start': 26438.665, 'duration': 15.931}, {'end': 26475.138, 'text': "I'll write E dot employee name, comma E dot salary from employees as e, then employees as e1.", 'start': 26454.596, 'duration': 20.542}], 'summary': 'Fetching employees with same salary, ensuring different ids', 'duration': 64.747, 'max_score': 26410.391, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26410391.jpg'}, {'end': 26550.608, 'src': 'embed', 'start': 26521.038, 'weight': 10, 'content': [{'end': 26526.8, 'text': 'So the question is to write an SQL query to print one row twice in results from a table.', 'start': 26521.038, 'duration': 5.762}, {'end': 26534.423, 'text': 'So I have a table here called employees and I want to print one row twice in the results.', 'start': 26527.44, 'duration': 6.983}, {'end': 26539.964, 'text': 'So to solve such kind of problems, you need to use the union all operator in SQL.', 'start': 26535.203, 'duration': 4.761}, {'end': 26544.626, 'text': 'So union all combines the result sets of two or more select statements.', 'start': 26540.725, 'duration': 3.901}, {'end': 26550.608, 'text': 'It does not remove duplicate rows between the various select statements, all the rows are returned.', 'start': 26545.686, 'duration': 4.922}], 'summary': 'Use sql union all operator to print one row twice in results.', 'duration': 29.57, 'max_score': 26521.038, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26521038.jpg'}, {'end': 26726.287, 'src': 'embed', 'start': 26694.622, 'weight': 11, 'content': [{'end': 26701.885, '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': 26694.622, 'duration': 7.263}, {'end': 26710.549, 'text': 'We have some numbers here and from these numbers I want to add 10 if the number is 0, 20.', 'start': 26702.505, 'duration': 8.044}, {'end': 26717.557, 'text': 'if the number is 1, else i would print the number itself.', 'start': 26710.549, 'duration': 7.008}, {'end': 26723.704, 'text': "so to answer this question, we'll use the case statement in sql.", 'start': 26717.557, 'duration': 6.147}, {'end': 26726.287, 'text': "so the case statement is sql's way of handling.", 'start': 26723.704, 'duration': 2.583}], 'summary': 'Using a sql case statement to modify numbers based on conditions.', 'duration': 31.665, 'max_score': 26694.622, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26694622.jpg'}, {'end': 26959.075, 'src': 'embed', 'start': 26931.715, 'weight': 12, 'content': [{'end': 26935.957, 'text': 'so again, for this we are using the case statement.', 'start': 26931.715, 'duration': 4.242}, {'end': 26940.06, 'text': 'you can see here i have used two case statements.', 'start': 26935.957, 'duration': 4.103}, {'end': 26947.145, 'text': "i'm writing select and then i'm using the case to find if the number is greater than zero, which means it's a positive number.", 'start': 26940.06, 'duration': 7.085}, {'end': 26954.411, 'text': "so i'm finding out the sum and then i'm checking case when the integer is less than zero.", 'start': 26947.145, 'duration': 7.266}, {'end': 26959.075, 'text': "so it's a negative number and i'm finding the sum for the negative numbers as well.", 'start': 26954.411, 'duration': 4.664}], 'summary': 'Using case statements to find sum for positive and negative numbers.', 'duration': 27.36, 'max_score': 26931.715, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26931715.jpg'}, {'end': 27181.929, 'src': 'embed', 'start': 27122.869, 'weight': 7, 'content': [{'end': 27132.537, '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': 27122.869, 'duration': 9.668}, {'end': 27138.441, 'text': 'because that is unique to all the students in a school.', 'start': 27132.537, 'duration': 5.904}, {'end': 27143.886, 'text': 'now foreign key is the field in the table that is primary key to another table.', 'start': 27138.441, 'duration': 5.445}, {'end': 27151.264, 'text': 'suppose you have another table called courses to which the students have enrolled to.', 'start': 27145.08, 'duration': 6.184}, {'end': 27162.972, 'text': 'now, if you have a roll number or a registration number column in the course table, that becomes the foreign key.', 'start': 27151.264, 'duration': 11.708}, {'end': 27171.778, 'text': "now the other difference is primary key don't accept or cannot accept null values, while foreign key can accept null values.", 'start': 27162.972, 'duration': 8.806}, {'end': 27181.929, 'text': 'you can have only one primary key in a table, but you can have more than one foreign key in a table.', 'start': 27172.992, 'duration': 8.937}], 'summary': 'Primary key uniquely identifies data, while foreign key links tables. primary key cannot accept null values, only one per table; foreign key can accept null values and multiple per table.', 'duration': 59.06, 'max_score': 27122.869, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU27122869.jpg'}, {'end': 27377.23, 'src': 'embed', 'start': 27337.975, 'weight': 9, 'content': [{'end': 27342.256, 'text': 'city column is of type var, char or varying character.', 'start': 27337.975, 'duration': 4.281}, {'end': 27350.839, 'text': "I'll give the size as 30 and here I'm going to apply my check constraint to this city column.", 'start': 27342.256, 'duration': 8.583}, {'end': 27364.385, 'text': "I'm going to check the city should take only values as Mumbai.", 'start': 27350.839, 'duration': 13.546}, {'end': 27370.267, 'text': "let's say, I'll have my final column, that is age.", 'start': 27364.385, 'duration': 5.882}, {'end': 27373.448, 'text': "age is of type integer and here I'm going to check.", 'start': 27370.267, 'duration': 3.181}, {'end': 27377.23, 'text': 'my age should always be greater than zero.', 'start': 27373.448, 'duration': 3.782}], 'summary': 'Applying a check constraint to city column, size 30, and ensuring age is always greater than zero.', 'duration': 39.255, 'max_score': 27337.975, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU27337975.jpg'}, {'end': 27592.175, 'src': 'embed', 'start': 27557.763, 'weight': 13, 'content': [{'end': 27570.051, 'text': 'so given two tables, A and B, you want to write a query to fetch values in table B that are not present in table A.', 'start': 27557.763, 'duration': 12.288}, {'end': 27573.674, 'text': 'now, to answer this question, you can use the left join.', 'start': 27570.051, 'duration': 3.623}, {'end': 27583.105, 'text': 'So what left join does is it returns all the rows from the left table and only matching rows from the right table.', 'start': 27574.716, 'duration': 8.389}, {'end': 27592.175, 'text': "And I've used my condition as a.id which is in the right table should be null.", 'start': 27584.507, 'duration': 7.668}], 'summary': 'Use left join to fetch values in table b not present in table a.', 'duration': 34.412, 'max_score': 27557.763, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU27557763.jpg'}, {'end': 27746.545, 'src': 'embed', 'start': 27716.857, 'weight': 0, 'content': [{'end': 27720.638, 'text': 'So my next question, which is based on Microsoft SQL Server.', 'start': 27716.857, 'duration': 3.781}, {'end': 27727.78, 'text': "The question is using the Northwind database, find the customers who don't have any orders.", 'start': 27721.398, 'duration': 6.382}, {'end': 27732.941, 'text': 'Okay, so I am on my Microsoft SQL Server Management Studio.', 'start': 27728.84, 'duration': 4.101}, {'end': 27734.681, 'text': 'So this is how the interface looks like.', 'start': 27733.061, 'duration': 1.62}, {'end': 27738.182, 'text': 'And here you can see I have a list of databases.', 'start': 27735.402, 'duration': 2.78}, {'end': 27742.843, 'text': "Now to solve the last five questions, I'm going to use the Northwind database.", 'start': 27738.762, 'duration': 4.081}, {'end': 27746.545, 'text': "If I expand this, I'll click on the plus sign.", 'start': 27743.404, 'duration': 3.141}], 'summary': 'Using microsoft sql server, accessing northwind database to find customers with no orders.', 'duration': 29.688, 'max_score': 27716.857, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU27716857.jpg'}, {'end': 28165.365, 'src': 'embed', 'start': 28128.951, 'weight': 1, 'content': [{'end': 28134.335, 'text': 'okay, so let me first give my comment for the 27th question.', 'start': 28128.951, 'duration': 5.384}, {'end': 28137.677, 'text': 'so the question is to find all the month end orders.', 'start': 28134.335, 'duration': 3.342}, {'end': 28143.561, 'text': "so i'll just write month end orders.", 'start': 28137.677, 'duration': 5.884}, {'end': 28153.148, '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': 28143.561, 'duration': 9.587}, {'end': 28155.449, 'text': "so let's start with the query.", 'start': 28153.148, 'duration': 2.301}, {'end': 28156.39, 'text': "i'll write select.", 'start': 28155.449, 'duration': 0.941}, {'end': 28165.365, 'text': 'we are going to select the employee ID comma.', 'start': 28159.144, 'duration': 6.221}], 'summary': 'Using a built-in end of month function to find month end orders.', 'duration': 36.414, 'max_score': 28128.951, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU28128951.jpg'}, {'end': 28269.053, 'src': 'embed', 'start': 28241.309, 'weight': 2, 'content': [{'end': 28253.9, 'text': 'you see this is 28th of feb, then we have 31st of july, we have 30th of june, so on and so forth.', 'start': 28241.309, 'duration': 12.591}, {'end': 28260.665, 'text': 'so these are the list of all the orders that were placed on the last day of the month.', 'start': 28253.9, 'duration': 6.765}, {'end': 28269.053, 'text': 'coming to the 28th question, We want to find the top five countries with the highest freight charges in the year 1997..', 'start': 28260.665, 'duration': 8.388}], 'summary': 'Identify top 5 countries with highest freight charges in 1997.', 'duration': 27.744, 'max_score': 28241.309, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU28241309.jpg'}, {'end': 28474.657, 'src': 'embed', 'start': 28444.236, 'weight': 3, 'content': [{'end': 28453.178, 'text': 'So both these tables we are going to use to find a solution to this problem that is to display the total number of products in each category.', 'start': 28444.236, 'duration': 8.942}, {'end': 28459.159, 'text': 'First let me give my comment to display the total number of products in each category.', 'start': 28454.838, 'duration': 4.321}, {'end': 28466.967, 'text': "I'll just write print total products okay.", 'start': 28459.159, 'duration': 7.808}, {'end': 28474.657, 'text': 'so to solve this problem we are going to use our two tables.', 'start': 28466.967, 'duration': 7.69}], 'summary': 'Using two tables to display total products in each category.', 'duration': 30.421, 'max_score': 28444.236, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU28444236.jpg'}, {'end': 28673.46, 'src': 'embed', 'start': 28643.078, 'weight': 4, 'content': [{'end': 28655.545, 'text': 'on the left hand side you have the different category names and on the right you have the total number of products that belong to each of the categories.', 'start': 28643.078, 'duration': 12.467}, {'end': 28661.189, 'text': 'all right now, moving on to the final question.', 'start': 28655.545, 'duration': 5.644}, {'end': 28669.694, 'text': 'so the last question is to use the northwind database and using this database we want to find the list of late orders for all the employees.', 'start': 28661.189, 'duration': 8.505}, {'end': 28673.46, 'text': 'So this is a real world problem that often occurs.', 'start': 28671.058, 'duration': 2.402}], 'summary': 'Analyze northwind database to find late orders for employees.', 'duration': 30.382, 'max_score': 28643.078, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU28643078.jpg'}], 'start': 26050.532, 'title': 'Sql concepts and practical examples', 'summary': 'Covers various sql concepts such as self join, virtual tables, union all, case statements, key differences, and constraints, with practical examples and demonstrations, including analyzing the northwind database with 91 unique customers and 830 total orders.', 'chapters': [{'end': 26513.429, 'start': 26050.532, 'title': 'Self join in sql and virtual tables', 'summary': 'Covers the concept of self join in sql, demonstrated using a table of employee managers, and also discusses virtual tables in sql, with examples and sql queries for both concepts, including a demonstration of fetching employees with the same salary using self join.', 'duration': 462.897, 'highlights': ['Self join joins a table to itself using a common column, demonstrated with a table of employee managers, showcasing how to return the name of the manager for each employee.', 'Explanation of a virtual table in SQL, highlighting the concept of a view as a virtual table with rows and columns similar to a real table in the database.', 'Demonstration of writing an SQL query to fetch the list of employees with the same salary using a self join, along with the results showing employees with the same salaries.']}, {'end': 27094.036, 'start': 26521.038, 'title': 'Sql union all and case statements', 'summary': 'Discusses using sql union all operator to print one row twice in results from a table and using case statements to add 10 or 20 based on conditions, demonstrating the results of the queries.', 'duration': 572.998, 'highlights': ['Using union all operator to print one row twice in results', 'Using case statements to add 10 or 20 based on conditions', 'Using case statements to find the sum of positive and negative values']}, {'end': 27682.831, 'start': 27094.036, 'title': 'Sql key differences and constraints', 'summary': 'Covers key differences between primary and foreign keys, unique keys, and check constraints in sql, along with practical examples for using check constraints and left join to fetch non-matching values between two tables.', 'duration': 588.795, 'highlights': ['Primary key uniquely identifies a record and does not accept null values.', 'Foreign key is a field in a table that is a primary key to another table and can accept null values.', 'Check constraints limit or restrict the values that can be inserted into a column.', 'Left join is used to fetch values in table B that are not present in table A.']}, {'end': 28241.309, 'start': 27682.831, 'title': 'Microsoft sql server: analyzing northwind database', 'summary': 'Covers using microsoft sql server to analyze the northwind sample database, including finding customers with no orders and identifying month-end orders, with 91 unique customers and 830 total orders, allowing the use of distinct queries to derive the missing customers and end-of-month orders.', 'duration': 558.478, 'highlights': ['The Northwind database contains 91 unique customers and 830 total orders, with two customers missing from the orders table, implying they have no orders.', 'Using a built-in end of month function, 26 month-end orders are identified based on the order date column in the Northwind database.']}, {'end': 28953.299, 'start': 28241.309, 'title': 'Sql query examples and solutions', 'summary': 'Covers various sql query examples and solutions, including finding top five countries with the highest freight charges in 1997, displaying the total number of products in each category, and finding the list of late orders for all employees using the northwind database.', 'duration': 711.99, 'highlights': ['Finding the top five countries with the highest freight charges in 1997', 'Displaying the total number of products in each category', 'Finding the list of late orders for all employees using the Northwind database']}], 'duration': 2902.767, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/AA7i2GcTGwU/pics/AA7i2GcTGwU26050532.jpg', 'highlights': ['Analyzing the Northwind database with 91 unique customers and 830 total orders', 'Using a built-in end of month function, 26 month-end orders are identified', 'Finding the top five countries with the highest freight charges in 1997', 'Displaying the total number of products in each category', 'Finding the list of late orders for all employees using the Northwind database', 'Self join joins a table to itself using a common column', 'Explanation of a virtual table in SQL, highlighting the concept of a view', 'Primary key uniquely identifies a record and does not accept null values', 'Foreign key is a field in a table that is a primary key to another table', 'Check constraints limit or restrict the values that can be inserted into a column', 'Using union all operator to print one row twice in results', 'Using case statements to add 10 or 20 based on conditions', 'Using case statements to find the sum of positive and negative values', 'Left join is used to fetch values in table B that are not present in table A', 'Demonstration of writing an SQL query to fetch the list of employees with the same salary using a self join']}], 'highlights': ['The sql full course covers comprehensive training on sql fundamentals, popular commands, functions, joins, triggers, stored procedures, and python integration, with practical examples and demonstrations using mysql workbench.', 'SQL is extensively used as a client-server language to connect the front-end with the back-end, supporting the client-server architecture.', "Creating a SQL database 'sql intro' and a table 'employee details' with columns for employee name, age, gender, date of join, city, and salary.", 'The concat function adds two or more expressions together, allowing for the concatenation of string values and the creation of concatenated columns from a table, demonstrated through examples with quantifiable results.', 'The group by clause in SQL groups records into summary rows and returns one record for each group, computing aggregate functions for the resulting group.', 'Using SQL to extract the number of employees joining the company each year by applying the year function, counting employee IDs, and grouping the result by year, with illustrative data showing the number of employees joining in different years.', 'SQL JOINs are used to combine rows of data from two or more tables based on a common field or column between them.', 'The union operator is used to combine the result set of two or more select statements, performing a vertical join.', 'Using subqueries to insert data into another table based on specific conditions, such as fetching records where the selling price is greater than $100 and inserting them into the orders table.', '9 records deleted from the employees table where age was less than or equal to 32, leaving 11 remaining employees with ages greater than 32.', 'Introduction to SQL views as a crucial concept, widely used in SQL.', 'Subqueries, stored procedures, triggers, views, and windows functions were demonstrated with practical examples using MySQL Workbench and Jupyter Notebook.', 'PostgreSQL is currently ranked fourth in popularity amongst hundreds of databases worldwide according to the DB engines ranking.', 'The group by clause is used to arrange identical data into groups, such as finding the average salary of employees based on countries.', 'The WHERE and HAVING clauses are explained, essential for SQL professionals.', 'The SQL query to display departments with more than two employees returned four departments: sales (4 employees), product (3 employees), tech (4 employees), and IT (3 employees).', 'Analyzing the Northwind database with 91 unique customers and 830 total orders']}