title
SQL Advanced Tutorial | Advanced SQL Tutorial With Examples | SQL For Beginners | Simplilearn

description
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=SQlAdvancedTutorial-M-55BmjOuXY&utm_medium=Descriptionff&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=SQlAdvancedTutorial-M-55BmjOuXY&utm_medium=Descriptionff&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQlAdvancedTutorial-M-55BmjOuXY&utm_medium=Descriptionff&utm_source=youtube 🔥Data Analyst Masters Program (Discount Code - YTBE15): https://www.simplilearn.com/data-analyst-masters-certification-training-course?utm_campaign=SQlAdvancedTutorial-M-55BmjOuXY&utm_medium=Descriptionff&utm_source=youtube This video on SQL Advanced Tutorial will help you learn the important advanced SQL queries for performing complex operations. Advanced SQL Tutorial With Examples will help you learn how to write subqueries and stored procedures. You will get an idea about triggers and views in SQL. Finally, we'll explore some of the important window functions in MySQL. Following are the topics covered in this SQL for beginners video: 00:00:00 SQL Advanced Tutorial 00:11:19 Stored Procedures 00:28:05 Triggers in SQL 00:35:36 Views in SQL 00:44:25 Windows Functions in SQL Dataset Link - https://drive.google.com/drive/folders/1xjbfMeZbbK27RPyRVvEGW6kEk2rdCSLG ✅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/playlist?list=PLEiEAq2VkUUKL3yPbn8yWnatjUg0P0I-Z #SQLAdvancedTutorial #SQLTutorial #SQLTutorialForBeginners #SQLCourse #SQLTraining #SQLForBeginners #SQLCourse #SQLTraining #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=SQlAdvancedTutorial-M-55BmjOuXY&utm_medium=Description&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQlAdvancedTutorial-M-55BmjOuXY&utm_medium=Description&utm_source=youtube 🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688

detail
{'title': 'SQL Advanced Tutorial | Advanced SQL Tutorial With Examples | SQL For Beginners | Simplilearn', 'heatmap': [{'end': 1739.655, 'start': 1651.767, 'weight': 0.821}, {'end': 2168.414, 'start': 2090.766, 'weight': 0.7}, {'end': 2746.618, 'start': 2704.814, 'weight': 0.741}, {'end': 2814.563, 'start': 2772.369, 'weight': 0.808}], 'summary': 'The tutorial covers advanced sql topics, including subqueries, stored procedures, triggers, views, and mysql 8.0 windows functions, with practical examples such as finding employees with salaries above average, creating stored procedures for player lists, using triggers for automatic mark setting, and demonstrating row number, rank, and first value functions for assigning ranks and retrieving highest salaries and employees by department.', 'chapters': [{'end': 666.55, 'segs': [{'end': 91.944, 'src': 'embed', 'start': 9.198, 'weight': 0, 'content': [{'end': 12.061, 'text': 'hello everyone and welcome to another video tutorial on SQL.', 'start': 9.198, 'duration': 2.863}, {'end': 16.846, 'text': "by simply learn, in today's video we will learn advanced SQL queries.", 'start': 12.061, 'duration': 4.785}, {'end': 19.969, 'text': 'in our previous videos we have learned the basics of SQL.', 'start': 16.846, 'duration': 3.123}, {'end': 23.272, 'text': 'that includes where group by having as well as joins in SQL.', 'start': 19.969, 'duration': 3.303}, {'end': 26.569, 'text': 'So in this video we will learn sub queries in SQL.', 'start': 24.167, 'duration': 2.402}, {'end': 31.434, 'text': "We'll also have a look at stored procedures and learn about triggers in SQL.", 'start': 27.17, 'duration': 4.264}, {'end': 36.158, 'text': "We'll cover views in SQL and look at some of the important Windows functions in SQL.", 'start': 32.014, 'duration': 4.144}, {'end': 40.302, 'text': "Now to learn all of these, we'll be using the MySQL Workbench on Windows.", 'start': 36.919, 'duration': 3.383}, {'end': 43.365, 'text': "So let's get started with sub queries in SQL.", 'start': 41.203, 'duration': 2.162}, {'end': 46.948, 'text': 'So let me head over to my MySQL Workbench.', 'start': 44.225, 'duration': 2.723}, {'end': 51.625, 'text': 'so currently I am on my mysql workbench.', 'start': 49.144, 'duration': 2.481}, {'end': 56.067, 'text': "let me connect to the local instance, so I'll give my password.", 'start': 51.625, 'duration': 4.442}, {'end': 62.75, 'text': "I'll click on ok, alright.", 'start': 56.067, 'duration': 6.683}, {'end': 66.491, 'text': 'so this is my mysql workbench query editor.', 'start': 62.75, 'duration': 3.741}, {'end': 68.892, 'text': 'so first we are going to learn sub queries.', 'start': 66.491, 'duration': 2.401}, {'end': 75.635, 'text': 'let me give a comment and write sub queries.', 'start': 68.892, 'duration': 6.743}, {'end': 79.475, 'text': "alright. So first of all, let's understand what a subquery is.", 'start': 75.635, 'duration': 3.84}, {'end': 86.341, 'text': 'So a subquery is a query within another SQL query that is embedded within the where clause, from clause or having clause.', 'start': 80.035, 'duration': 6.306}, {'end': 91.944, 'text': "so we'll explore a few scenarios where we can use sub queries.", 'start': 88.123, 'duration': 3.821}], 'summary': 'Advanced sql tutorial covering subqueries, stored procedures, triggers, views, and windows functions using mysql workbench.', 'duration': 82.746, 'max_score': 9.198, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY9198.jpg'}, {'end': 169.133, 'src': 'embed', 'start': 141.445, 'weight': 2, 'content': [{'end': 146.706, 'text': "So let's say you want to find the employees whose salary is greater than the average salary.", 'start': 141.445, 'duration': 5.261}, {'end': 150.127, 'text': 'In such a scenario, you can use a subquery.', 'start': 147.447, 'duration': 2.68}, {'end': 153.308, 'text': 'So let me show you how to write a subquery.', 'start': 150.848, 'duration': 2.46}, {'end': 155.709, 'text': "I'll write the SELECT statement.", 'start': 154.449, 'duration': 1.26}, {'end': 161.271, 'text': "In the SELECT statement, I'll pass my column names that I want to display.", 'start': 156.989, 'duration': 4.282}, {'end': 164.331, 'text': 'So the column names I want are the employee name.', 'start': 161.611, 'duration': 2.72}, {'end': 169.133, 'text': 'Then I want the department of the employee and the salary of the employee.', 'start': 165.572, 'duration': 3.561}], 'summary': 'Demonstration of using a subquery to find employees with salary greater than average.', 'duration': 27.688, 'max_score': 141.445, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY141445.jpg'}, {'end': 582.832, 'src': 'embed', 'start': 557.494, 'weight': 4, 'content': [{'end': 565.92, 'text': 'for this scenario, we are going to use two different tables and we are going to write a sub query.', 'start': 557.494, 'duration': 8.426}, {'end': 574.51, 'text': 'okay, so if you see here in the order details table we have a column called price each.', 'start': 565.92, 'duration': 8.59}, {'end': 582.832, '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': 574.51, 'duration': 8.322}], 'summary': 'Using subquery to display product code, name, and msrp for products priced under $100.', 'duration': 25.338, 'max_score': 557.494, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY557494.jpg'}], 'start': 9.198, 'title': 'Advanced sql queries and using subqueries', 'summary': "Covers advanced sql topics including sub queries, stored procedures, triggers, views, and important windows functions using mysql workbench, with a focus on understanding sub queries and their usage scenarios. it also demonstrates the use of subqueries in sql to find employees with salaries greater than the average, above a specific employee's salary, and products with prices less than $100, showcasing the practical application of subqueries with detailed examples and relevant sql commands.", 'chapters': [{'end': 91.944, 'start': 9.198, 'title': 'Advanced sql queries tutorial', 'summary': 'Covers advanced sql topics including sub queries, stored procedures, triggers, views, and important windows functions using mysql workbench, with a focus on understanding sub queries and their usage scenarios.', 'duration': 82.746, 'highlights': ['The chapter covers sub queries, stored procedures, triggers, views, and important Windows functions in SQL, with a focus on understanding sub queries and their usage scenarios.', 'Subqueries are queries embedded within another SQL query that is in the where clause, from clause, or having clause.', 'The tutorial will explore scenarios where sub queries can be used to enhance SQL queries.']}, {'end': 666.55, 'start': 91.944, 'title': 'Using subqueries in sql', 'summary': "Demonstrates the use of subqueries in sql to find employees with salaries greater than the average, above a specific employee's salary, and products with prices less than $100, showcasing the practical application of subqueries with detailed examples and relevant sql commands.", 'duration': 574.606, 'highlights': ['The chapter demonstrates the use of subqueries to find employees with salaries greater than the average.', "The chapter explains how to use subqueries to find employees with salaries greater than a specific employee's salary.", 'The chapter demonstrates the use of subqueries with two different tables to find products with prices less than $100.']}], 'duration': 657.352, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY9198.jpg', 'highlights': ['The chapter covers sub queries, stored procedures, triggers, views, and important Windows functions in SQL, with a focus on understanding sub queries and their usage scenarios.', 'The tutorial will explore scenarios where sub queries can be used to enhance SQL queries.', 'The chapter demonstrates the use of subqueries to find employees with salaries greater than the average.', "The chapter explains how to use subqueries to find employees with salaries greater than a specific employee's salary.", 'The chapter demonstrates the use of subqueries with two different tables to find products with prices less than $100.', 'Subqueries are queries embedded within another SQL query that is in the where clause, from clause, or having clause.']}, {'end': 1668.999, 'segs': [{'end': 695.479, 'src': 'embed', 'start': 666.55, 'weight': 3, 'content': [{'end': 675.876, 'text': 'okay, now we learn another advanced concept in SQL which is known as stored procedures.', 'start': 666.55, 'duration': 9.326}, {'end': 684.202, 'text': "I'll just give a comment saying stored procedure.", 'start': 675.876, 'duration': 8.326}, {'end': 688.877, 'text': "okay, So first let's understand what is a stored procedure.", 'start': 684.202, 'duration': 4.675}, {'end': 695.479, 'text': 'A stored procedure is an SQL code that you can save so that the code can be reused over and over again.', 'start': 689.817, 'duration': 5.662}], 'summary': 'Introduction to stored procedures in sql for code reusability.', 'duration': 28.929, 'max_score': 666.55, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY666550.jpg'}, {'end': 775.826, 'src': 'embed', 'start': 741.032, 'weight': 4, 'content': [{'end': 747.769, 'text': "so I'll write a stored procedure that will return the list of top players who have scored more than six goals in a tournament.", 'start': 741.032, 'duration': 6.737}, {'end': 756.192, 'text': 'so first of all, let me begin by using my sql underscore iq database.', 'start': 747.769, 'duration': 8.423}, {'end': 759.574, 'text': "we'll run it.", 'start': 756.192, 'duration': 3.382}, {'end': 763.955, 'text': 'so now we are inside the sql underscore iq database.', 'start': 759.574, 'duration': 4.381}, {'end': 775.826, 'text': 'let me select star from players to show the values that we have in the players table.', 'start': 763.955, 'duration': 11.871}], 'summary': 'Creating a stored procedure to return top players with over 6 goals in a tournament using sql_iq database.', 'duration': 34.794, 'max_score': 741.032, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY741032.jpg'}, {'end': 1029.429, 'src': 'embed', 'start': 1005.68, 'weight': 1, 'content': [{'end': 1012.923, 'text': 'so we have a table in our SQL underscore IQ database which is called employee details.', 'start': 1005.68, 'duration': 7.243}, {'end': 1014.083, 'text': "I'm going to use this table.", 'start': 1012.923, 'duration': 1.16}, {'end': 1020.626, 'text': 'you can see we have the name of the employee, the age, sex, then we have the date of join, city and salary.', 'start': 1014.083, 'duration': 6.543}, {'end': 1021.846, 'text': 'using this table,', 'start': 1020.626, 'duration': 1.22}, {'end': 1029.429, 'text': "I'll create a procedure that will fetch or display the top records of employees based on their salaries and we'll use the in parameter.", 'start': 1021.846, 'duration': 7.583}], 'summary': 'Create a procedure to display top employee records based on salary using sql iq database.', 'duration': 23.749, 'max_score': 1005.68, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY1005680.jpg'}, {'end': 1255.713, 'src': 'embed', 'start': 1225.499, 'weight': 2, 'content': [{'end': 1239.923, 'text': "okay, now, instead of a select statement inside a stored procedure, you can also use other statements, let's say update.", 'start': 1225.499, 'duration': 14.424}, {'end': 1244.426, 'text': "so i'll create a stored procedure to update the salary of a particular employee.", 'start': 1239.923, 'duration': 4.503}, {'end': 1250.29, 'text': "so in this procedure, instead of select statement, we'll use the update command.", 'start': 1244.426, 'duration': 5.864}, {'end': 1254.392, 'text': "in this example we'll use the in operator twice.", 'start': 1250.29, 'duration': 4.102}, {'end': 1255.713, 'text': 'so let me show you how to do it.', 'start': 1254.392, 'duration': 1.321}], 'summary': 'Demonstrating using update command in a stored procedure to update employee salary.', 'duration': 30.214, 'max_score': 1225.499, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY1225499.jpg'}, {'end': 1603.801, 'src': 'embed', 'start': 1578.158, 'weight': 0, 'content': [{'end': 1583.201, 'text': 'I am creating a new stored procedure that is sp underscore count employees.', 'start': 1578.158, 'duration': 5.043}, {'end': 1591.025, 'text': 'using this stored procedure, I am going to count the total number of female employees that are present in our table, emp underscore details.', 'start': 1583.201, 'duration': 7.824}, {'end': 1596.968, 'text': 'so I have used my out parameter and I am creating a new variable called total underscore emps.', 'start': 1591.025, 'duration': 5.943}, {'end': 1598.049, 'text': 'the data type is integer.', 'start': 1596.968, 'duration': 1.081}, {'end': 1603.801, 'text': 'here in the select statement, I am counting the names of the employees and the result.', 'start': 1598.999, 'duration': 4.802}], 'summary': "Creating stored procedure 'sp_count_employees' to count total female employees.", 'duration': 25.643, 'max_score': 1578.158, 'thumbnail': ''}], 'start': 666.55, 'title': 'Stored procedures in sql', 'summary': 'Introduces the concept of stored procedures in sql and demonstrates creating procedures to retrieve player lists, use in parameter for salary-based employee records, update statements, and creating procedures with out parameters for employee count.', 'chapters': [{'end': 956.048, 'start': 666.55, 'title': 'Stored procedures in sql', 'summary': 'Introduces the concept of stored procedures in sql, demonstrating the creation of a procedure to retrieve the list of players who have scored more than six goals in a tournament from a database, sql_iq.', 'duration': 289.498, 'highlights': ['Stored procedures are reusable SQL code that can be saved for repetitive use.', 'Creation of a stored procedure to retrieve players who have scored more than six goals in a tournament.', "Demonstration of executing a stored procedure using the 'call' method.", 'Identification and correction of errors in the stored procedure creation process.']}, {'end': 1225.499, 'start': 956.048, 'title': 'Using in parameter in stored procedure', 'summary': 'Discusses the creation and usage of a stored procedure with an in parameter to fetch and display the top records of employees based on their salaries, resulting in the identification of the top three employees with the highest salaries.', 'duration': 269.451, 'highlights': ['Creation of stored procedure to fetch top records of employees based on salaries', "Usage of 'in' parameter and call of the stored procedure", 'Identification of top three employees with highest salaries']}, {'end': 1452.004, 'start': 1225.499, 'title': 'Using update statement in stored procedure', 'summary': 'Demonstrates how to use the update statement inside a stored procedure to update the salary of an employee, showcasing the successful updating of a salary from 70,000 to 80,000 dollars for employee mary, with one row affected in the table.', 'duration': 226.505, 'highlights': ['Demonstrating the use of update statement in a stored procedure to update employee salary', 'Showing the successful update of the salary in the table', 'Displaying the existing records in the employee_details table']}, {'end': 1668.999, 'start': 1452.004, 'title': 'Creating stored procedure with out parameter', 'summary': 'Explains how to create a stored procedure using an out parameter to count the total number of female employees in a table, with an output variable of total employees as an integer.', 'duration': 216.995, 'highlights': ["The chapter demonstrates creating a stored procedure 'SP_count_employees' with an out parameter 'total_EMPs' of data type integer to count the number of female employees in the 'EMP_details' table, using the into keyword to assign the count of female employees to the output variable.", "The process involves writing a select statement to count the total employees from the 'EMP_details' table where the sex is female, and storing the result in the 'total_EMPs' variable.", "After creating the stored procedure, the chapter shows how to call the procedure and retrieve the count of female employees using the 'call' statement and select query."]}], 'duration': 1002.449, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY666550.jpg', 'highlights': ["Creation of a stored procedure 'SP_count_employees' to count female employees", 'Creation of stored procedure to fetch top records of employees based on salaries', 'Demonstrating the use of update statement in a stored procedure to update employee salary', 'Stored procedures are reusable SQL code that can be saved for repetitive use', 'Creation of a stored procedure to retrieve players who have scored more than six goals in a tournament']}, {'end': 2094.246, 'segs': [{'end': 1747.402, 'src': 'embed', 'start': 1698.745, 'weight': 0, 'content': [{'end': 1703.968, 'text': 'so a trigger is a special type of stored procedure that runs automatically when an event occurs in the database server.', 'start': 1698.745, 'duration': 5.223}, {'end': 1707.602, 'text': 'there are mainly three types of triggers in SQL.', 'start': 1704.961, 'duration': 2.641}, {'end': 1709.722, 'text': 'we have the data manipulation trigger.', 'start': 1707.602, 'duration': 2.12}, {'end': 1713.404, 'text': 'we have the data definition trigger and login triggers.', 'start': 1709.722, 'duration': 3.682}, {'end': 1717.985, 'text': 'in this example we learn how to use a before insert trigger.', 'start': 1713.404, 'duration': 4.581}, {'end': 1726.667, 'text': 'so we will create a simple students table that will have the students role number, the age, the name and the students marks.', 'start': 1717.985, 'duration': 8.682}, {'end': 1731.409, 'text': "so before inserting the records to our table, we'll check if the marks are less than zero.", 'start': 1726.667, 'duration': 4.742}, {'end': 1739.655, 'text': "so in case the marks are less than 0, our trigger will automatically set the marks to a random value, let's say 50.", 'start': 1732.469, 'duration': 7.186}, {'end': 1747.402, 'text': "so let's go ahead and create our table that is students.", 'start': 1739.655, 'duration': 7.747}], 'summary': 'Introduction to sql triggers, focusing on before insert trigger for setting marks to a random value if less than zero.', 'duration': 48.657, 'max_score': 1698.745, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY1698745.jpg'}, {'end': 1925.766, 'src': 'embed', 'start': 1887.747, 'weight': 6, 'content': [{'end': 1893.432, 'text': "I'll write end if semicolon and I'll close the delimiter.", 'start': 1887.747, 'duration': 5.685}, {'end': 1897.075, 'text': 'so this is my trigger command.', 'start': 1893.432, 'duration': 3.643}, {'end': 1897.516, 'text': "I'll run it.", 'start': 1897.075, 'duration': 0.441}, {'end': 1901.336, 'text': 'it says trigger already exists.', 'start': 1898.655, 'duration': 2.681}, {'end': 1905.016, 'text': 'in this case we need to update the trigger name.', 'start': 1901.336, 'duration': 3.68}, {'end': 1914.959, 'text': "let's say I'll write marks underscore, verify, underscore, student for ST.", 'start': 1905.016, 'duration': 9.943}, {'end': 1915.679, 'text': "let's run it again.", 'start': 1914.959, 'duration': 0.72}, {'end': 1925.766, 'text': 'okay, there is an error here because in our table the column name is mark and not marks.', 'start': 1915.679, 'duration': 10.087}], 'summary': "Updating trigger command to 'marks_verify_student' for st, encountered an error due to incorrect column name.", 'duration': 38.019, 'max_score': 1887.747, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY1887747.jpg'}, {'end': 2094.246, 'src': 'embed', 'start': 2060.795, 'weight': 4, 'content': [{'end': 2074.641, 'text': 'so originally we had inserted for 502 the marks was minus 20.5 and for 504, for Jacobs, the marks was minus 12.5.', 'start': 2060.795, 'duration': 13.846}, {'end': 2081.543, 'text': 'our trigger automatically converted the negative marks to 50, because when we created our trigger,', 'start': 2074.641, 'duration': 6.902}, {'end': 2087.045, 'text': 'we had set our marks to 50 in case the marks were less than 0.', 'start': 2081.543, 'duration': 5.502}, {'end': 2090.766, 'text': 'so this is how a trigger works now.', 'start': 2087.045, 'duration': 3.721}, {'end': 2094.246, 'text': 'you can also drop a trigger or delete a trigger.', 'start': 2090.766, 'duration': 3.48}], 'summary': 'Trigger converted -20.5 to 50 for 502, -12.5 to 50 for 504.', 'duration': 33.451, 'max_score': 2060.795, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2060795.jpg'}], 'start': 1668.999, 'title': 'Sql triggers', 'summary': "Covers sql triggers, explaining their types and functionality, and provides examples of before insert triggers and implementing triggers to automatically set marks, preparing the audience for advanced sql operations. it also includes the creation of a student table with roll number, age, name, and marks, and observes the trigger's effect on inserting records.", 'chapters': [{'end': 1717.985, 'start': 1668.999, 'title': 'Sql triggers overview', 'summary': 'Covers the concept of triggers in sql, explaining their types and functionality, and provides an example of a before insert trigger, preparing the audience for advanced sql operations.', 'duration': 48.986, 'highlights': ['Triggers in SQL are a special type of stored procedure that runs automatically when an event occurs in the database server, serving as a powerful tool for database automation.', 'There are three main types of triggers in SQL: data manipulation triggers, data definition triggers, and login triggers, providing a comprehensive overview of trigger functionalities.', "The tutorial includes a practical example of a before insert trigger, demonstrating its application in database operations, enhancing the audience's understanding of trigger implementation."]}, {'end': 2094.246, 'start': 1717.985, 'title': 'Creating students table and implementing trigger', 'summary': "Covers the creation of a student table with roll number, age, name, and marks, and the implementation of a trigger to automatically set marks to 50 if they are less than 0, with an example of inserting records and observing the trigger's effect.", 'duration': 376.261, 'highlights': ['The chapter demonstrates the creation of a student table with roll number, age, name, and marks, and the implementation of a trigger to automatically set marks to 50 if they are less than 0.', 'The trigger successfully converted the negative marks to 50 for the students with roll numbers 502 and 504, as intended.', 'An example of inserting records was provided, where the trigger automatically adjusted the marks for students with negative marks, demonstrating the functionality of the implemented trigger.', 'The trigger command was initially met with errors due to mismatched column names, requiring adjustments to the trigger name and column references for successful execution.']}], 'duration': 425.247, 'thumbnail': '', 'highlights': ['Triggers in SQL are a special type of stored procedure that runs automatically when an event occurs in the database server, serving as a powerful tool for database automation.', 'There are three main types of triggers in SQL: data manipulation triggers, data definition triggers, and login triggers, providing a comprehensive overview of trigger functionalities.', "The tutorial includes a practical example of a before insert trigger, demonstrating its application in database operations, enhancing the audience's understanding of trigger implementation.", 'The chapter demonstrates the creation of a student table with roll number, age, name, and marks, and the implementation of a trigger to automatically set marks to 50 if they are less than 0.', 'The trigger successfully converted the negative marks to 50 for the students with roll numbers 502 and 504, as intended.', 'An example of inserting records was provided, where the trigger automatically adjusted the marks for students with negative marks, demonstrating the functionality of the implemented trigger.', 'The trigger command was initially met with errors due to mismatched column names, requiring adjustments to the trigger name and column references for successful execution.']}, {'end': 2798.299, 'segs': [{'end': 2218.004, 'src': 'embed', 'start': 2187.552, 'weight': 2, 'content': [{'end': 2191.594, 'text': 'so this is my customer table, which is present inside classic models database.', 'start': 2187.552, 'duration': 4.042}, {'end': 2196.616, 'text': 'it has the contact last name, the contact first name, the customer name, customer number.', 'start': 2191.594, 'duration': 5.022}, {'end': 2199.937, 'text': 'we have the address, state country and other information.', 'start': 2196.616, 'duration': 3.321}, {'end': 2206.981, 'text': "now I'll write a basic view command using this customer table.", 'start': 2201.4, 'duration': 5.581}, {'end': 2218.004, 'text': "the way to write is I'll write create view, followed by the view name which is cust, underscore details.", 'start': 2206.981, 'duration': 11.023}], 'summary': "Creating a basic view 'cust_details' from customer table in classic models database.", 'duration': 30.452, 'max_score': 2187.552, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2187552.jpg'}, {'end': 2316.953, 'src': 'embed', 'start': 2289.474, 'weight': 0, 'content': [{'end': 2301.497, 'text': "the phone number and the city of the different customers that we have in our table all right now let's learn how you can create views using joins.", 'start': 2289.474, 'duration': 12.023}, {'end': 2303.858, 'text': "so we'll join two different tables and create a view.", 'start': 2301.497, 'duration': 2.361}, {'end': 2310.151, 'text': "So for that I'm going to use my products table and the products lines table.", 'start': 2304.73, 'duration': 5.421}, {'end': 2316.953, 'text': "I'm talking about the products table and the product lines table present inside classic models database.", 'start': 2310.831, 'duration': 6.122}], 'summary': 'Creating views by joining products and product lines tables.', 'duration': 27.479, 'max_score': 2289.474, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2289474.jpg'}, {'end': 2542.848, 'src': 'embed', 'start': 2500.748, 'weight': 3, 'content': [{'end': 2504.991, 'text': 'so to rename a description you can use the rename statement.', 'start': 2500.748, 'duration': 4.243}, {'end': 2516.461, 'text': "I'll write rename table, product underscore description, which is my old name.", 'start': 2504.991, 'duration': 11.47}, {'end': 2517.982, 'text': 'I want to change this name to.', 'start': 2516.461, 'duration': 1.521}, {'end': 2527.13, 'text': "let's say, I'll give vehicle description, since all our products are related to some of the other vehicle.", 'start': 2517.982, 'duration': 9.148}, {'end': 2528.851, 'text': "so I'll write vehicle description.", 'start': 2527.13, 'duration': 1.721}, {'end': 2537.103, 'text': 'okay, let us run it all right.', 'start': 2533.2, 'duration': 3.903}, {'end': 2542.848, 'text': 'so here you can see i have renamed my view.', 'start': 2537.103, 'duration': 5.745}], 'summary': "Renamed 'product_description' to 'vehicle_description' in the database view.", 'duration': 42.1, 'max_score': 2500.748, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2500748.jpg'}, {'end': 2607.138, 'src': 'embed', 'start': 2568.578, 'weight': 4, 'content': [{'end': 2583.007, 'text': 'show full tables where table underscore type is equal to within single quote.', 'start': 2568.578, 'duration': 14.429}, {'end': 2585.268, 'text': "I'll write view.", 'start': 2583.007, 'duration': 2.261}, {'end': 2590.472, 'text': 'so this is the command that will display all the views that are present inside a database.', 'start': 2585.268, 'duration': 5.204}, {'end': 2593.913, 'text': 'there is some error here.', 'start': 2590.472, 'duration': 3.441}, {'end': 2596.114, 'text': "let's debug the error.", 'start': 2593.913, 'duration': 2.201}, {'end': 2598.915, 'text': 'this should be okay.', 'start': 2596.114, 'duration': 2.801}, {'end': 2603.416, 'text': 'so instead of table types, it should be table type equal to view.', 'start': 2598.915, 'duration': 4.501}, {'end': 2605.077, 'text': "let's run it.", 'start': 2603.416, 'duration': 1.661}, {'end': 2607.138, 'text': 'you can see the two different views that we have.', 'start': 2605.077, 'duration': 2.061}], 'summary': 'Command displays all views in database - 2 views present.', 'duration': 38.56, 'max_score': 2568.578, 'thumbnail': ''}, {'end': 2670.866, 'src': 'embed', 'start': 2641.464, 'weight': 5, 'content': [{'end': 2647.427, 'text': "you can see here we don't have the cust underscore details view anymore.", 'start': 2641.464, 'duration': 5.963}, {'end': 2651.629, 'text': 'all right now, moving to our final section in this demo.', 'start': 2647.427, 'duration': 4.202}, {'end': 2656.017, 'text': 'Here we will learn about Windows functions.', 'start': 2652.935, 'duration': 3.082}, {'end': 2663.662, 'text': 'Windows functions were incorporated in MySQL in the 8.0 version.', 'start': 2659.979, 'duration': 3.683}, {'end': 2670.866, 'text': 'So Windows functions in MySQL are useful applications in solving analytical problems.', 'start': 2665.703, 'duration': 5.163}], 'summary': 'Mysql 8.0 introduced windows functions, useful for analytical problem-solving.', 'duration': 29.402, 'max_score': 2641.464, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2641464.jpg'}, {'end': 2738.933, 'src': 'embed', 'start': 2704.814, 'weight': 1, 'content': [{'end': 2710.619, 'text': 'using this table we are going to find the combined salary of the employees for each department.', 'start': 2704.814, 'duration': 5.805}, {'end': 2714.84, 'text': 'so we will partition our table by department and print the total salary.', 'start': 2711.218, 'duration': 3.622}, {'end': 2720.383, 'text': 'and this we are going to do using some windows functions in mysql.', 'start': 2714.84, 'duration': 5.543}, {'end': 2733.27, 'text': 'so I will write select I want the employee name, the age of the employee and the department of the employee comma.', 'start': 2720.383, 'duration': 12.887}, {'end': 2738.933, 'text': 'next I will write the sum of salary over.', 'start': 2733.27, 'duration': 5.663}], 'summary': 'Using window functions in mysql to find combined salary by department.', 'duration': 34.119, 'max_score': 2704.814, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2704814.jpg'}, {'end': 2746.618, 'src': 'heatmap', 'start': 2704.814, 'weight': 0.741, 'content': [{'end': 2710.619, 'text': 'using this table we are going to find the combined salary of the employees for each department.', 'start': 2704.814, 'duration': 5.805}, {'end': 2714.84, 'text': 'so we will partition our table by department and print the total salary.', 'start': 2711.218, 'duration': 3.622}, {'end': 2720.383, 'text': 'and this we are going to do using some windows functions in mysql.', 'start': 2714.84, 'duration': 5.543}, {'end': 2733.27, 'text': 'so I will write select I want the employee name, the age of the employee and the department of the employee comma.', 'start': 2720.383, 'duration': 12.887}, {'end': 2738.933, 'text': 'next I will write the sum of salary over.', 'start': 2733.27, 'duration': 5.663}, {'end': 2746.618, 'text': 'I want to partition it by department.', 'start': 2741.195, 'duration': 5.423}], 'summary': 'Find combined salary for each department using window functions in mysql.', 'duration': 41.804, 'max_score': 2704.814, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2704814.jpg'}], 'start': 2094.246, 'title': 'Sql views and mysql windows functions', 'summary': "Explains creating and using sql views with the 'customers' table, demonstrates creating views with joins between 'products' and 'product lines', and covers mysql 8.0 windows functions to calculate total combined salaries and highest salaries, including $155,000 in finance.", 'chapters': [{'end': 2289.474, 'start': 2094.246, 'title': 'Sql views: creating and using', 'summary': "Explains how to create and use sql views, showcasing the process using the 'customers' table from the classic models database and highlighting the creation and display of a view.", 'duration': 195.228, 'highlights': ['SQL views are virtual tables that do not store data but display data from other tables.', "Creating a view using the 'customers' table from the classic models database.", 'Displaying the contents of the created view using a select statement.']}, {'end': 2641.464, 'start': 2289.474, 'title': 'Creating views with joins', 'summary': "Demonstrates how to create a view by joining two tables, 'products' and 'product lines', to fetch specific records, and also covers renaming, displaying, and deleting views in a database.", 'duration': 351.99, 'highlights': ["The chapter demonstrates how to create a view by joining two tables, 'products' and 'product lines', to fetch specific records.", "The process of renaming a view is illustrated with the example of renaming the view from 'product_description' to 'vehicle_description'.", "The commands for displaying all the views in a database and deleting a specific view are explained using the 'show full tables' and 'drop view' commands."]}, {'end': 2798.299, 'start': 2641.464, 'title': 'Mysql windows functions demo', 'summary': 'Covers the usage of windows functions in mysql 8.0 to calculate the total combined salary of employees for each department using the employees table, demonstrating the highest salaries for each department, including $155,000 in finance.', 'duration': 156.835, 'highlights': ['Windows functions were incorporated in MySQL in the 8.0 version, and they are useful applications in solving analytical problems, such as calculating total combined salary for each department.', 'Using the employees table in the sql_intro database, the demonstration displays the highest salaries for each department, including $155,000 in finance, showcasing the practical application of Windows functions in MySQL.', 'The demonstration partitions the table by department and prints the total salary using Windows functions, highlighting the analytical capabilities of MySQL in processing and presenting data effectively.']}], 'duration': 704.053, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2094246.jpg', 'highlights': ["Demonstrates creating views with joins between 'products' and 'product lines'", 'Illustrates using MySQL 8.0 windows functions to calculate total combined salaries and highest salaries, including $155,000 in finance', "Explains creating a view using the 'customers' table from the classic models database", "Describes the process of renaming a view from 'product_description' to 'vehicle_description'", "Shows how to display all the views in a database and delete a specific view using commands like 'show full tables' and 'drop view'", 'Highlights the analytical capabilities of MySQL in processing and presenting data effectively']}, {'end': 3605.126, 'segs': [{'end': 2832.3, 'src': 'embed', 'start': 2799.539, 'weight': 0, 'content': [{'end': 2803.58, 'text': "Now we'll explore a function which is called row number.", 'start': 2799.539, 'duration': 4.041}, {'end': 2810.182, 'text': 'Now the row number function gives a sequential integer to every row within its partition.', 'start': 2805.08, 'duration': 5.102}, {'end': 2814.563, 'text': 'So let me show you how to use the row number function.', 'start': 2811.182, 'duration': 3.381}, {'end': 2816.063, 'text': "I'll write select.", 'start': 2815.343, 'duration': 0.72}, {'end': 2830.739, 'text': 'row underscore number function over my column would be salary.', 'start': 2819.537, 'duration': 11.202}, {'end': 2832.3, 'text': "so I'll write order by salary.", 'start': 2830.739, 'duration': 1.561}], 'summary': 'The row number function assigns a sequential integer to each row within its partition.', 'duration': 32.761, 'max_score': 2799.539, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2799539.jpg'}, {'end': 2901.353, 'src': 'embed', 'start': 2869.819, 'weight': 1, 'content': [{'end': 2872.081, 'text': 'you can see it starts from 1 and goes up till 20.', 'start': 2869.819, 'duration': 2.262}, {'end': 2881.086, 'text': 'okay, now this row number function can be used to find duplicate values in a table.', 'start': 2872.081, 'duration': 9.005}, {'end': 2883.587, 'text': "to show that, first I'll create a table.", 'start': 2881.086, 'duration': 2.501}, {'end': 2887.328, 'text': "I'll write create table.", 'start': 2883.587, 'duration': 3.741}, {'end': 2891.449, 'text': "let's say I'll give a random name, that is demo.", 'start': 2887.328, 'duration': 4.121}, {'end': 2901.353, 'text': "and let's say we have in this table the student ID, which is of type integer, and we have the student name which is of type worker.", 'start': 2891.449, 'duration': 9.904}], 'summary': 'The row number function ranges from 1 to 20 and can be used to find duplicate values in a table. for example, a table with student id and name can be created for demonstration.', 'duration': 31.534, 'max_score': 2869.819, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2869819.jpg'}, {'end': 3130.506, 'src': 'embed', 'start': 3105.629, 'weight': 3, 'content': [{'end': 3114.337, 'text': 'okay, Now we are going to see another Windows function that is called rank function in MySQL.', 'start': 3105.629, 'duration': 8.708}, {'end': 3117.959, 'text': 'So the rank function assigns a rank to a particular column.', 'start': 3115.158, 'duration': 2.801}, {'end': 3123.523, 'text': 'There are gaps in the sequence of rank values when two or more rows have the same rank.', 'start': 3119.06, 'duration': 4.463}, {'end': 3130.506, 'text': 'So first of all, let me create a table and the name of the table would be a random name.', 'start': 3124.283, 'duration': 6.223}], 'summary': 'Introducing mysql rank function for assigning ranks to columns.', 'duration': 24.877, 'max_score': 3105.629, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY3105629.jpg'}, {'end': 3347.631, 'src': 'embed', 'start': 3301.11, 'weight': 2, 'content': [{'end': 3308.432, 'text': 'so this function returns the value of the specified expression with respect to the first row in the window frame.', 'start': 3301.11, 'duration': 7.322}, {'end': 3327.372, 'text': 'all right, so what I am going to do is I am going to select the employee name, the age and salary, and I will write first underscore value,', 'start': 3308.432, 'duration': 18.94}, {'end': 3347.631, 'text': 'which is my function, and pass in my employee name, and then I will write over order by my column, that is salary descending.', 'start': 3327.372, 'duration': 20.259}], 'summary': 'Function returns value based on window frame, selecting employee name, age, and salary.', 'duration': 46.521, 'max_score': 3301.11, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY3301110.jpg'}, {'end': 3513.148, 'src': 'embed', 'start': 3492.409, 'weight': 4, 'content': [{'end': 3502.985, 'text': 'alright, so that brings us to the end of this demo session on our tutorial, so let me just scroll through and show you what we did from the beginning.', 'start': 3492.409, 'duration': 10.576}, {'end': 3511.708, 'text': 'first, we learned about sub queries in sql, so we initially wrote a simple sub query and then we used our classic models database,', 'start': 3502.985, 'duration': 8.723}, {'end': 3513.148, 'text': 'which was downloaded from the internet.', 'start': 3511.708, 'duration': 1.44}], 'summary': 'Demo session covered sub queries in sql using classic models database.', 'duration': 20.739, 'max_score': 3492.409, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY3492409.jpg'}], 'start': 2799.539, 'title': 'Using row number, rank, and first value functions in sql and mysql', 'summary': 'Demonstrates the usage of row number function in sql to assign sequential integers, identify duplicates, and order records by salary and student ids. it also shows the use of rank function in mysql to assign ranks with examples of skipped ranks due to duplicates, and the first value function to retrieve highest salary and employee by department.', 'chapters': [{'end': 3105.629, 'start': 2799.539, 'title': 'Using row number function', 'summary': 'Demonstrates the usage of the row number function in sql to assign sequential integers to rows, identify duplicate records, and find the order of records based on salary and student ids.', 'duration': 306.09, 'highlights': ['The row number function assigns sequential integers to rows within a partition, as demonstrated by assigning row numbers to each record in a table, starting from 1 and going up to 20.', 'The row number function can be utilized to identify duplicate records in a table by creating a small table with student IDs and names and then using the row number function to find the duplicate records based on the student ID and name, displaying the row numbers for each duplicate record.']}, {'end': 3605.126, 'start': 3105.629, 'title': 'Using rank and first value functions in mysql', 'summary': 'Demonstrates the use of the rank function in mysql to assign a rank to a column with examples of skipped ranks due to duplicate values, followed by the first value function to retrieve the highest salary and the employee with the highest salary in each department.', 'duration': 499.497, 'highlights': ['The rank function assigns a rank to a column in MySQL, with examples of skipped ranks due to duplicate values, such as 103 and 106, and the resulting ranks for each value.', 'The first value function in MySQL retrieves the value of the specified expression with respect to the first row in the window frame, demonstrated with examples of retrieving the highest salary and the employee with the highest salary in each department.', 'Overview of the demonstration session covering subqueries, stored procedures, triggers, views, and the exploration of various window functions in SQL.']}], 'duration': 805.587, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/M-55BmjOuXY/pics/M-55BmjOuXY2799539.jpg', 'highlights': ['The row number function assigns sequential integers to rows within a partition, as demonstrated by assigning row numbers to each record in a table, starting from 1 and going up to 20.', 'The row number function can be utilized to identify duplicate records in a table by creating a small table with student IDs and names and then using the row number function to find the duplicate records based on the student ID and name, displaying the row numbers for each duplicate record.', 'The first value function in MySQL retrieves the value of the specified expression with respect to the first row in the window frame, demonstrated with examples of retrieving the highest salary and the employee with the highest salary in each department.', 'The rank function assigns a rank to a column in MySQL, with examples of skipped ranks due to duplicate values, such as 103 and 106, and the resulting ranks for each value.', 'Overview of the demonstration session covering subqueries, stored procedures, triggers, views, and the exploration of various window functions in SQL.']}], 'highlights': ['The tutorial covers advanced sql topics, including subqueries, stored procedures, triggers, views, and mysql 8.0 windows functions, with practical examples such as finding employees with salaries above average, creating stored procedures for player lists, using triggers for automatic mark setting, and demonstrating row number, rank, and first value functions for assigning ranks and retrieving highest salaries and employees by department.', 'Triggers in SQL are a special type of stored procedure that runs automatically when an event occurs in the database server, serving as a powerful tool for database automation.', 'Stored procedures are reusable SQL code that can be saved for repetitive use.', 'The row number function assigns sequential integers to rows within a partition, as demonstrated by assigning row numbers to each record in a table, starting from 1 and going up to 20.', 'The chapter covers sub queries, stored procedures, triggers, views, and important Windows functions in SQL, with a focus on understanding sub queries and their usage scenarios.']}