title
Subquery In SQL | SQL Subquery Tutorial With Examples | SQL Tutorial For Beginners | Simplilearn

description
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=SubqueryInSQLApr19-JksrTuEVEPk&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=SubqueryInSQLApr19-JksrTuEVEPk&utm_medium=Descriptionff&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SubqueryInSQLApr19-JksrTuEVEPk&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=SubqueryInSQLApr19-JksrTuEVEPk&utm_medium=Descriptionff&utm_source=youtube This video on Subquery in SQL will make you understand what subqueries are and how to write a subquery. You will learn subqueries using the select, insert, update and delete commands. You will look at the SQL subquery tutorial with examples on MySQL workbench. Got a Question on this topic? Let us know in the comment section below 👇 and we'll have our experts answer it for you. 00:00:00 What is a Subquery? 00:03:49 Types of Subquery 00:04:04 Subqueries with the SELECT Statement 00:07:46 Subqueries with the INSERT Statement 00:15:18 Subqueries with the UPDATE Statement 00:19:04 Subqueries with the DELETE Statement ✅Subscribe to our Channel to learn more about the top Technologies: https://bit.ly/2VT4WtH To access the slides, click here: https://www.slideshare.net/Simplilearn/subquery-in-sql-sql-subquery-tutorial-with-examples-sql-tutorial-for-beginners-simplilearn/Simplilearn/subquery-in-sql-sql-subquery-tutorial-with-examples-sql-tutorial-for-beginners-simplilearn ⏩ Check out the SQL training videos: https://www.youtube.com/playlist?list=PLEiEAq2VkUUKL3yPbn8yWnatjUg0P0I-Z #SubqueryInSQL #SQLSubQueryTutorialWithExamples #WhatareSubQueriesInSQL #SQLSubQeuriesExplained #SQLTutorial #SQLTutorialForBeginners #SQLCourse #SQLTraining #SQLForBeginners #SQLCourse #SQLTraining #Simplilearn What is a Subquery in SQL? A subquery is a query within another query. they are nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Subqueries can be categorized as correlated or uncorrelated. A subquery can be used anywhere an expression is allowed. It is another way of returning data from multiple tables. ➡️ 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=SubqueryInSQLApr19-JksrTuEVEPk&utm_medium=Description&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SubqueryInSQLApr19-JksrTuEVEPk&utm_medium=Description&utm_source=youtube 🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688

detail
{'title': 'Subquery In SQL | SQL Subquery Tutorial With Examples | SQL Tutorial For Beginners | Simplilearn', 'heatmap': [{'end': 85.355, 'start': 50.658, 'weight': 0.726}, {'end': 288.284, 'start': 258.309, 'weight': 0.878}, {'end': 523.26, 'start': 457.67, 'weight': 0.759}, {'end': 835.613, 'start': 762.732, 'weight': 0.768}, {'end': 932.857, 'start': 873.381, 'weight': 0.758}, {'end': 974.84, 'start': 940.661, 'weight': 0.799}], 'summary': 'This sql tutorial by simplilearn covers sub-queries in sql, demonstrating different types of sub-queries for select, insert, update, and delete statements, as well as practical examples of using sub-queries to find the highest salary, select employees based on maximum salary, insert data from one table into another, update employee salaries, and delete specific records based on sub-query conditions, providing a comprehensive understanding of sub-queries in sql.', 'chapters': [{'end': 41.736, 'segs': [{'end': 49.47, 'src': 'embed', 'start': 25.44, 'weight': 0, 'content': [{'end': 31.581, 'text': 'Moving further, we will learn sub-queries with the UPDATE statement and finally we will look at sub-queries with DELETE statement.', 'start': 25.44, 'duration': 6.141}, {'end': 35.442, 'text': 'All these we will be doing on our MySQL Workbench.', 'start': 32.22, 'duration': 3.222}, {'end': 41.736, 'text': 'So before I begin, make sure to subscribe to the SimplyLearn channel and hit the bell icon to never miss an update.', 'start': 37.168, 'duration': 4.568}, {'end': 49.47, 'text': "So let's start with what is a subquery? So a subquery is a select query that is enclosed inside another query.", 'start': 42.217, 'duration': 7.253}], 'summary': 'Learn sub-queries with update and delete in mysql workbench.', 'duration': 24.03, 'max_score': 25.44, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk25440.jpg'}], 'start': 8.237, 'title': 'Sub-queries in sql', 'summary': 'Covers different types of sub-queries in sql, including select, insert, update, and delete statements, demonstrated on mysql workbench, emphasizing the importance of subscribing to simplylearn channel for updates.', 'chapters': [{'end': 41.736, 'start': 8.237, 'title': 'Sub-queries in sql tutorial', 'summary': 'Covers the different types of sub-queries in sql, including sub-queries with select, insert, update, and delete statements, demonstrated on mysql workbench, emphasizing the importance of subscribing to simplylearn channel for updates.', 'duration': 33.499, 'highlights': ['The chapter covers sub-queries with SELECT, INSERT, UPDATE, and DELETE statements on MySQL Workbench.', 'It emphasizes the importance of subscribing to SimplyLearn channel for updates.']}], 'duration': 33.499, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk8237.jpg', 'highlights': ['The chapter covers sub-queries with SELECT, INSERT, UPDATE, and DELETE statements on MySQL Workbench.', 'It emphasizes the importance of subscribing to SimplyLearn channel for updates.']}, {'end': 231.415, 'segs': [{'end': 87.775, 'src': 'heatmap', 'start': 50.658, 'weight': 0, 'content': [{'end': 56.54, 'text': 'so if i show you, this is how the basic structure of a subquery looks like.', 'start': 50.658, 'duration': 5.882}, {'end': 65.482, 'text': 'so here whatever is present inside the brackets is called as the inner query and whatever is present outside is called the outer query.', 'start': 56.54, 'duration': 8.942}, {'end': 75.411, 'text': 'so first the inner query gets executed and the result is returned to the outer query, and then the outer query operation is performed all right.', 'start': 65.482, 'duration': 9.929}, {'end': 77.372, 'text': "now let's see an example.", 'start': 75.411, 'duration': 1.961}, {'end': 85.355, 'text': 'so we have a question at hand which is to write a SQL query to display department with maximum salary from employees table.', 'start': 77.372, 'duration': 7.983}, {'end': 87.775, 'text': 'so this is how our employees table looks like.', 'start': 85.355, 'duration': 2.42}], 'summary': 'The transcript discusses the structure and example of a subquery in sql, including executing inner and outer queries and providing an example of displaying the department with the maximum salary from an employees table.', 'duration': 45.558, 'max_score': 50.658, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk50658.jpg'}, {'end': 163.681, 'src': 'embed', 'start': 113.404, 'weight': 2, 'content': [{'end': 118.587, 'text': 'so what this does is it will first return the maximum salary of the employees in the table.', 'start': 113.404, 'duration': 5.183}, {'end': 124.549, 'text': 'then our outer query will get executed based on the salary returned from the inner query.', 'start': 118.587, 'duration': 5.962}, {'end': 131.294, 'text': 'so here the output is department sales has the maximum salary,', 'start': 125.788, 'duration': 5.506}, {'end': 136.259, 'text': 'so one of the employees from the sales department earns the highest or the maximum salary.', 'start': 131.294, 'duration': 4.965}, {'end': 142.905, 'text': 'if you see in our table, the employee is Joseph, who earns $115, 000.', 'start': 136.259, 'duration': 6.646}, {'end': 147.05, 'text': 'all right, and Joseph is from the sales department.', 'start': 142.906, 'duration': 4.144}, {'end': 151.508, 'text': "now let's see how this query works.", 'start': 149.226, 'duration': 2.282}, {'end': 157.094, 'text': 'so here we have another question, which is to find the name of the employee with maximum salary in the employees table.', 'start': 151.508, 'duration': 5.586}, {'end': 163.681, 'text': 'so this is our previous employees table that we saw and to find the employee who has the maximum salary,', 'start': 157.094, 'duration': 6.587}], 'summary': 'Query returns highest salary employee in sales department: joseph, $115,000', 'duration': 50.277, 'max_score': 113.404, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk113404.jpg'}, {'end': 231.415, 'src': 'embed', 'start': 205.047, 'weight': 6, 'content': [{'end': 212.289, 'text': 'so our SQL query will first execute the inner query that is present inside brackets select maximum salary from employees.', 'start': 205.047, 'duration': 7.242}, {'end': 218.25, 'text': 'the result is $115, 000 and then, based on the return result, our outer query gets executed.', 'start': 212.289, 'duration': 5.961}, {'end': 226.153, 'text': 'so the query becomes select employee name from employees where Salary equal to $115, 000, and that employee is Joseph.', 'start': 218.25, 'duration': 7.903}, {'end': 231.415, 'text': "Alright, now we'll learn the different types of sub-queries.", 'start': 227.414, 'duration': 4.001}], 'summary': 'The sql query found joseph as the employee with the highest salary of $115,000.', 'duration': 26.368, 'max_score': 205.047, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk205047.jpg'}], 'start': 42.217, 'title': 'Using sub-queries in sql', 'summary': 'Explains the basics of subqueries, finding the employee with the highest salary in the sales department ($115,000 earned by joseph), and demonstrates the use of sub-queries to select employee names based on maximum salary, with an example showing a maximum salary of $115,000 resulting in the output of the employee name joseph.', 'chapters': [{'end': 113.404, 'start': 42.217, 'title': 'Subquery basics in sql', 'summary': 'Explains the basics of a subquery in sql, its structure, and how it can be used to find the department with the maximum salary from the employees table.', 'duration': 71.187, 'highlights': ['A subquery is a select query enclosed inside another query, with the inner query getting executed first and its result passed to the outer query.', 'The example demonstrates a SQL query to display the department with the maximum salary from the employees table using a subquery.', 'The inner query selects the maximum salary from the employees table, and the outer query uses this result to display the department with the maximum salary.']}, {'end': 163.681, 'start': 113.404, 'title': 'Finding employee with maximum salary', 'summary': 'Explains the process of finding the employee with the highest salary in the sales department, which is $115,000 earned by joseph.', 'duration': 50.277, 'highlights': ['The query returns the department with the maximum salary, indicating that an employee in the sales department earns the highest salary. (Relevance: 5)', 'Joseph earns the highest salary of $115,000 in the sales department. (Relevance: 4)', 'Explanation of the process of finding the employee with the highest salary in the employees table. (Relevance: 3)']}, {'end': 231.415, 'start': 164.602, 'title': 'Using sub-queries in sql', 'summary': 'Explains the use of sub-queries in sql, demonstrating how to select employee names based on the maximum salary returned from an inner query, with an example showing a maximum salary of $115,000 resulting in the output of the employee name joseph.', 'duration': 66.813, 'highlights': ['The chapter demonstrates the use of sub-queries in SQL to select employee names based on the result of an inner query, with an example showcasing the selection of the employee name Joseph based on a maximum salary of $115,000.', 'The example provided illustrates the execution of an inner query to obtain the maximum salary, which is then used in the outer query to retrieve the corresponding employee name, showcasing the step-by-step process of executing sub-queries in SQL.', 'The chapter presents a breakdown of the execution process, starting with the inner query to find the maximum salary, followed by the utilization of this result in the outer query to fetch the employee name, providing a clear understanding of the sequential execution of sub-queries in SQL.']}], 'duration': 189.198, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk42217.jpg', 'highlights': ['A subquery is a select query enclosed inside another query, with the inner query getting executed first and its result passed to the outer query.', 'The example demonstrates a SQL query to display the department with the maximum salary from the employees table using a subquery.', 'The inner query selects the maximum salary from the employees table, and the outer query uses this result to display the department with the maximum salary.', 'The query returns the department with the maximum salary, indicating that an employee in the sales department earns the highest salary. (Relevance: 5)', 'Joseph earns the highest salary of $115,000 in the sales department. (Relevance: 4)', 'Explanation of the process of finding the employee with the highest salary in the employees table. (Relevance: 3)', 'The chapter demonstrates the use of sub-queries in SQL to select employee names based on the result of an inner query, with an example showcasing the selection of the employee name Joseph based on a maximum salary of $115,000.', 'The example provided illustrates the execution of an inner query to obtain the maximum salary, which is then used in the outer query to retrieve the corresponding employee name, showcasing the step-by-step process of executing sub-queries in SQL.', 'The chapter presents a breakdown of the execution process, starting with the inner query to find the maximum salary, followed by the utilization of this result in the outer query to fetch the employee name, providing a clear understanding of the sequential execution of sub-queries in SQL.']}, {'end': 470.235, 'segs': [{'end': 310.898, 'src': 'heatmap', 'start': 258.309, 'weight': 0, 'content': [{'end': 264.872, 'text': 'followed by the columns that you want to pass the operator and inside that you have this sub query.', 'start': 258.309, 'duration': 6.563}, {'end': 268.934, 'text': 'so here is an example that we will perform on our mysql workbench.', 'start': 264.872, 'duration': 4.062}, {'end': 278.519, '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': 268.934, 'duration': 9.585}, {'end': 279.499, 'text': 'this is the output.', 'start': 278.519, 'duration': 0.98}, {'end': 282.861, 'text': "so let's do this on my mysql workbench.", 'start': 279.499, 'duration': 3.362}, {'end': 285.522, 'text': 'all right, so let me log into my local instance.', 'start': 282.861, 'duration': 2.661}, {'end': 288.284, 'text': "I'll give my password.", 'start': 287.043, 'duration': 1.241}, {'end': 294.087, 'text': "Okay, so you can see I'm on my MySQL workbench.", 'start': 291.346, 'duration': 2.741}, {'end': 301.212, 'text': "So let's start by writing our sub query using the SELECT statement.", 'start': 294.848, 'duration': 6.364}, {'end': 310.898, 'text': "Okay, so for this demo session, we'll be using a database that is sub queries, you can see it here I have a database called sub queries.", 'start': 303.633, 'duration': 7.265}], 'summary': 'Demonstrating subquery usage in mysql workbench for employee salary comparison.', 'duration': 78.742, 'max_score': 258.309, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk258309.jpg'}, {'end': 402.705, 'src': 'embed', 'start': 350.223, 'weight': 2, 'content': [{'end': 359.65, 'text': 'for our select sub query, we want to fetch the employee name, the department and the salary whose salary is less than the average salary.', 'start': 350.223, 'duration': 9.427}, {'end': 361.692, 'text': 'so we will be using the employees table.', 'start': 359.65, 'duration': 2.042}, {'end': 367.076, 'text': 'so let me first show you the records and the columns we have in the employees table.', 'start': 361.692, 'duration': 5.384}, {'end': 371.35, 'text': "So I'll write select star from employees and run it.", 'start': 369.349, 'duration': 2.001}, {'end': 374.411, 'text': 'Okay You can see here, we have 20 rows of information.', 'start': 371.73, 'duration': 2.681}, {'end': 380.754, 'text': 'We have the employee name, the employee ID, age, gender, date of join, department, city and salary.', 'start': 375.172, 'duration': 5.582}, {'end': 383.775, 'text': 'So this is the same table that we saw in our slides.', 'start': 381.334, 'duration': 2.441}, {'end': 388.157, 'text': "Okay Now for our sub query, I'll write select.", 'start': 384.996, 'duration': 3.161}, {'end': 394.8, 'text': 'I want to choose the employee name, the department and the salary.', 'start': 389.158, 'duration': 5.642}, {'end': 402.705, 'text': 'there should be a comma here instead of a period.', 'start': 398.424, 'duration': 4.281}], 'summary': 'Fetching employee name, department, salary < average salary from employees table with 20 rows.', 'duration': 52.482, 'max_score': 350.223, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk350223.jpg'}, {'end': 470.235, 'src': 'embed', 'start': 437.751, 'weight': 4, 'content': [{'end': 442.613, 'text': 'so we have total 12 employees in the table whose salary is less than the average salary.', 'start': 437.751, 'duration': 4.862}, {'end': 447.456, 'text': 'now, if you want, you can check the average salary.', 'start': 442.613, 'duration': 4.843}, {'end': 452.608, 'text': 'so the average salary is $75, 350.', 'start': 447.456, 'duration': 5.152}, {'end': 457.67, 'text': 'now the employees who have a salary less than the average salary.', 'start': 452.608, 'duration': 5.062}, {'end': 465.893, 'text': 'so these are the people all right now moving back to our slides.', 'start': 457.67, 'duration': 8.223}, {'end': 470.235, 'text': "okay, now let's see how you can use sub queries with the insert statement.", 'start': 465.893, 'duration': 4.342}], 'summary': '12 employees have salary less than $75,350, utilizing sub queries for inserts.', 'duration': 32.484, 'max_score': 437.751, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk437751.jpg'}], 'start': 232.156, 'title': 'Using sub-queries in sql', 'summary': "Covers the use of sub-queries in sql, focusing on sub-queries with the select statement and includes an example of selecting employees with a salary less than the average salary from a database called 'sub queries'. it also mentions creating and using tables in a demo session on mysql workbench.", 'chapters': [{'end': 350.223, 'start': 232.156, 'title': 'Using sub-queries in sql', 'summary': "Covers the use of sub-queries in sql, focusing on sub-queries with the select statement and includes an example of selecting employees with a salary less than the average salary from a database called 'sub queries'. it also mentions creating and using tables in a demo session on mysql workbench.", 'duration': 118.067, 'highlights': ['The syntax for sub-queries with the SELECT statement involves selecting a column from a table with a where condition, followed by the operator, and inside that, a sub-query.', "A demonstration is performed using MySQL Workbench to select employees with a salary less than the average salary from a database called 'sub queries'.", "Tables are created and used in a demo session on MySQL Workbench, focusing on a database called 'sub queries'.", 'Sub-queries can be used with SELECT, UPDATE, DELETE, and INSERT statements in SQL.']}, {'end': 470.235, 'start': 350.223, 'title': 'Using subquery with select statement', 'summary': 'Explains how to use a subquery with the select statement to fetch employee names, departments, and salaries less than the average salary, resulting in 12 employees with salaries below the average of $75,350.', 'duration': 120.012, 'highlights': ['The chapter discusses using a subquery with the select statement to fetch employee names, departments, and salaries less than the average salary, resulting in 12 employees with salaries below the average of $75,350.', 'The speaker demonstrates the process by using the employees table and shows that there are 20 rows of information, including employee names, IDs, ages, genders, dates of joining, departments, cities, and salaries.', 'The speaker uses the AVG function to find the average salary of all the employees from the employees table and shows that the average salary is $75,350.']}], 'duration': 238.079, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk232156.jpg', 'highlights': ['Sub-queries can be used with SELECT, UPDATE, DELETE, and INSERT statements in SQL.', 'The syntax for sub-queries with the SELECT statement involves selecting a column from a table with a where condition, followed by the operator, and inside that, a sub-query.', "A demonstration is performed using MySQL Workbench to select employees with a salary less than the average salary from a database called 'sub queries'.", 'The chapter discusses using a subquery with the select statement to fetch employee names, departments, and salaries less than the average salary, resulting in 12 employees with salaries below the average of $75,350.', 'The speaker uses the AVG function to find the average salary of all the employees from the employees table and shows that the average salary is $75,350.', "Tables are created and used in a demo session on MySQL Workbench, focusing on a database called 'sub queries'.", 'The speaker demonstrates the process by using the employees table and shows that there are 20 rows of information, including employee names, IDs, ages, genders, dates of joining, departments, cities, and salaries.']}, {'end': 894.428, 'segs': [{'end': 523.26, 'src': 'embed', 'start': 471.69, 'weight': 0, 'content': [{'end': 476.795, 'text': 'now the insert statement uses the data written from the subquery to insert into another table.', 'start': 471.69, 'duration': 5.105}, {'end': 478.636, 'text': 'so this is how the syntax looks like.', 'start': 476.795, 'duration': 1.841}, {'end': 482.94, 'text': 'so you write insert into table name, followed by select individual column.', 'start': 478.636, 'duration': 4.304}, {'end': 490.687, 'text': 'so start from the table, use the where clause and then you give the operator followed by the inner query or the subquery.', 'start': 482.94, 'duration': 7.747}, {'end': 496.338, 'text': 'so here we will explore a table called products table.', 'start': 491.914, 'duration': 4.424}, {'end': 503.965, 'text': 'we are going to fetch few records from the products table based on a condition, that is, the selling price of the product,', 'start': 496.338, 'duration': 7.627}, {'end': 506.668, 'text': 'should be greater than hundred dollars.', 'start': 503.965, 'duration': 2.703}, {'end': 510.632, 'text': 'so only those records will fetch and put it in our orders table.', 'start': 506.668, 'duration': 3.964}, {'end': 512.533, 'text': 'all right.', 'start': 510.632, 'duration': 1.901}, {'end': 516.977, 'text': "so we are going to write this query on my mysql workbench, so let's do it.", 'start': 512.533, 'duration': 4.444}, {'end': 523.26, 'text': "I'll give my comment as update sub query.", 'start': 518.317, 'duration': 4.943}], 'summary': 'Using subquery to insert data from products table with selling price > 100 into orders table.', 'duration': 51.57, 'max_score': 471.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk471690.jpg'}, {'end': 639.947, 'src': 'embed', 'start': 596.795, 'weight': 3, 'content': [{'end': 599.637, 'text': "I'll give four records.", 'start': 596.795, 'duration': 2.842}, {'end': 602.919, 'text': 'the first product ID is 101.', 'start': 599.637, 'duration': 3.282}, {'end': 609.464, 'text': "the product is, let's say, jewelry.", 'start': 602.919, 'duration': 6.545}, {'end': 617.929, 'text': "then the selling price is, let's say, $800 and the product type is it's a luxury product.", 'start': 609.464, 'duration': 8.465}, {'end': 621.632, 'text': "next let's insert one more product detail.", 'start': 617.929, 'duration': 3.703}, {'end': 622.512, 'text': 'the product ID is 102.', 'start': 621.632, 'duration': 0.88}, {'end': 629.5, 'text': "the product is, let's say, t-shirt.", 'start': 622.512, 'duration': 6.988}, {'end': 639.947, 'text': "the price is, let's say, hundred dollars and the product type is non-luxury.", 'start': 629.5, 'duration': 10.447}], 'summary': 'Transcript contains four records of product details, including product ids, descriptions, prices, and luxury status.', 'duration': 43.152, 'max_score': 596.795, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk596795.jpg'}, {'end': 846.783, 'src': 'heatmap', 'start': 762.732, 'weight': 1, 'content': [{'end': 765.634, 'text': 'so we have our orders table ready.', 'start': 762.732, 'duration': 2.902}, {'end': 771.336, 'text': "now let's write our insert sub query.", 'start': 765.634, 'duration': 5.702}, {'end': 785.633, 'text': "so I am going to insert into my table, that is orders, and I'll select the product ID comma,", 'start': 771.336, 'duration': 14.297}, {'end': 803.78, 'text': "the item and the selling price or the sell price from my table, that is products, where I'll write product ID in.", 'start': 785.633, 'duration': 18.147}, {'end': 814.921, 'text': "I'll write my inner query select prod ID or the product ID from products.", 'start': 803.78, 'duration': 11.141}, {'end': 824.723, 'text': "next I'll give a where clause where the selling price is greater than $1, 000.", 'start': 814.921, 'duration': 9.802}, {'end': 826.626, 'text': "So let me tell you what I'm going to do here.", 'start': 824.724, 'duration': 1.902}, {'end': 835.613, 'text': "I'm going to insert into my orders table the product ID, the item name and the selling price from my products table,", 'start': 827.146, 'duration': 8.467}, {'end': 840.157, 'text': 'where the product ID has this condition.', 'start': 835.613, 'duration': 4.544}, {'end': 846.783, 'text': 'So let me first run this condition for you, which is select prod ID from products where the selling price is greater than 1000.', 'start': 840.517, 'duration': 6.266}], 'summary': 'Inserts product details into orders table where selling price > $1000.', 'duration': 84.051, 'max_score': 762.732, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk762732.jpg'}], 'start': 471.69, 'title': 'Using subquery to insert data', 'summary': "Explains how to use a subquery to insert data from one table into another, demonstrating the process of inserting records from a 'products' table with selling price greater than $100 into an 'orders' table. it also covers the creation of 'products' and 'orders' tables, insertion of 4 records in 'products' table, and insertion of 2 records in 'orders' table based on a condition of selling price greater than $1000.", 'chapters': [{'end': 523.26, 'start': 471.69, 'title': 'Using subquery to insert data', 'summary': "Explains how to use a subquery to insert data from one table into another, specifically demonstrating the process of inserting records from a 'products' table with selling price greater than $100 into an 'orders' table.", 'duration': 51.57, 'highlights': ["The syntax for the insert statement using a subquery involves writing 'insert into table name' followed by 'select individual column' with a where clause and the subquery, which is demonstrated using a 'products' table.", "Demonstrates the process of fetching records from the 'products' table based on the condition that the selling price of the product should be greater than 100 dollars and inserting these records into the 'orders' table.", 'The chapter provides a practical demonstration using MySQL Workbench to write the query for updating with a subquery.']}, {'end': 894.428, 'start': 525.502, 'title': 'Database table creation and data insertion', 'summary': "Covers the creation of 'products' and 'orders' tables, insertion of 4 records in 'products' table, and insertion of 2 records in 'orders' table based on a condition of selling price greater than $1000.", 'duration': 368.926, 'highlights': ["The chapter covers the creation of 'products' and 'orders' tables, insertion of 4 records in 'products' table, and insertion of 2 records in 'orders' table based on a condition of selling price greater than $1000.", "Four records were inserted into the 'products' table, including product IDs, product names, selling prices, and product types.", "Two records were inserted into the 'orders' table based on the condition of selling price greater than $1000, retrieving product IDs, product names, and selling prices from the 'products' table."]}], 'duration': 422.738, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk471690.jpg', 'highlights': ["The syntax for the insert statement using a subquery involves writing 'insert into table name' followed by 'select individual column' with a where clause and the subquery, which is demonstrated using a 'products' table.", "The chapter covers the creation of 'products' and 'orders' tables, insertion of 4 records in 'products' table, and insertion of 2 records in 'orders' table based on a condition of selling price greater than $1000.", "Demonstrates the process of fetching records from the 'products' table based on the condition that the selling price of the product should be greater than 100 dollars and inserting these records into the 'orders' table.", "Four records were inserted into the 'products' table, including product IDs, product names, selling prices, and product types.", "Two records were inserted into the 'orders' table based on the condition of selling price greater than $1000, retrieving product IDs, product names, and selling prices from the 'products' table.", 'The chapter provides a practical demonstration using MySQL Workbench to write the query for updating with a subquery.']}, {'end': 1138.075, 'segs': [{'end': 974.84, 'src': 'heatmap', 'start': 918.43, 'weight': 0, 'content': [{'end': 922.572, 'text': "now let's see how you can use sub queries with the update statement.", 'start': 918.43, 'duration': 4.142}, {'end': 926.454, 'text': 'now the sub queries can be used in conjunction with the update statement.', 'start': 922.572, 'duration': 3.882}, {'end': 932.857, 'text': 'so either single or multiple columns in a table can be updated when using a sub query with the update statement.', 'start': 926.454, 'duration': 6.403}, {'end': 937.64, 'text': 'so this is how the basic syntax of an update sub query looks like.', 'start': 932.857, 'duration': 4.783}, {'end': 940.661, 'text': 'so you write update table, followed by the table name.', 'start': 937.64, 'duration': 3.021}, {'end': 948.099, 'text': 'you set the column name, you give the where operator and then you write your inner subquery.', 'start': 940.661, 'duration': 7.438}, {'end': 955.047, 'text': 'so we are going to see an example where we will use this employees table and using this employees table,', 'start': 948.099, 'duration': 6.948}, {'end': 967.517, 'text': 'we will update the records of the salaries of the employees by multiplying it with a factor of 0.35 only for those employees which have age greater than 27.', 'start': 955.047, 'duration': 12.47}, {'end': 972.179, 'text': 'so we are going to use a new table called employees underscore b for this as well.', 'start': 967.517, 'duration': 4.662}, {'end': 974.84, 'text': "so let's see how to do it.", 'start': 972.179, 'duration': 2.661}], 'summary': 'Subqueries can update single or multiple columns in a table. example: updating employee salaries by multiplying with a factor of 0.35 for employees over 27.', 'duration': 49.087, 'max_score': 918.43, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk918430.jpg'}, {'end': 1138.075, 'src': 'embed', 'start': 1105.414, 'weight': 2, 'content': [{'end': 1117.835, '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': 1105.414, 'duration': 12.421}, {'end': 1119.515, 'text': "We haven't updated his salary.", 'start': 1118.095, 'duration': 1.42}, {'end': 1125.497, 'text': 'Then if you see, if you have, okay, there is one more employee, Maya.', 'start': 1120.255, 'duration': 5.242}, {'end': 1130.138, 'text': "We haven't updated the salary of Maya because the age is less than 27.", 'start': 1126.577, 'duration': 3.561}, {'end': 1131.498, 'text': 'All right.', 'start': 1130.138, 'duration': 1.36}, {'end': 1138.075, 'text': "Now let's go back to our slides again.", 'start': 1136.454, 'duration': 1.621}], 'summary': 'Employees under 27, like marcus and maya, have unchanged salaries.', 'duration': 32.661, 'max_score': 1105.414, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk1105414.jpg'}], 'start': 894.428, 'title': 'Using subqueries', 'summary': 'Demonstrates using subqueries to update employee salaries by multiplying them by 0.35 for those over 27, affecting 18 out of 20 employees, and showcases the difference in the updated salaries.', 'chapters': [{'end': 1138.075, 'start': 894.428, 'title': 'Using subqueries for updating records', 'summary': 'Demonstrates using subqueries to update employee salaries by multiplying them by 0.35 for those over 27, affecting 18 out of 20 employees, and showcases the difference in the updated salaries.', 'duration': 243.647, 'highlights': ['The chapter demonstrates using subqueries to update employee salaries by multiplying them by 0.35 for those over 27, affecting 18 out of 20 employees.', 'The demonstration showcases the difference in the updated salaries compared to those not meeting the specified condition.', 'The usage of a subquery with the update statement is explained, allowing for updating single or multiple columns in a table based on specific conditions.']}], 'duration': 243.647, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk894428.jpg', 'highlights': ['The chapter demonstrates using subqueries to update employee salaries by multiplying them by 0.35 for those over 27, affecting 18 out of 20 employees.', 'The usage of a subquery with the update statement is explained, allowing for updating single or multiple columns in a table based on specific conditions.', 'The demonstration showcases the difference in the updated salaries compared to those not meeting the specified condition.']}, {'end': 1364.259, 'segs': [{'end': 1193.516, 'src': 'embed', 'start': 1139.836, 'weight': 0, 'content': [{'end': 1143.777, 'text': 'As you can see, we got the same output on our MySQL Workbench.', 'start': 1139.836, 'duration': 3.941}, {'end': 1148.659, 'text': "Now let's explore how you can write sub-queries with the DELETE statement.", 'start': 1144.758, 'duration': 3.901}, {'end': 1153.542, 'text': 'Now sub-queries can again be used in conjunction with the DELETE statement.', 'start': 1149.7, 'duration': 3.842}, {'end': 1158.324, 'text': 'So this is how the basic syntax of a DELETE query using sub-query would look like.', 'start': 1154.122, 'duration': 4.202}, {'end': 1165.567, 'text': 'You write DELETE from the table name, where clause, the operator value, followed by the inner query and within brackets.', 'start': 1158.504, 'duration': 7.063}, {'end': 1177.552, 'text': "so here we are going to use the employees table and what we are going to do is we'll delete the employees whose age is greater than equal to 27.", 'start': 1166.829, 'duration': 10.723}, {'end': 1181.593, 'text': "so let's see how you can do it all right.", 'start': 1177.552, 'duration': 4.041}, {'end': 1191.676, 'text': "so i'll give my comment as delete sub query.", 'start': 1181.593, 'duration': 10.083}, {'end': 1193.516, 'text': "so we'll follow the syntax that we saw.", 'start': 1191.676, 'duration': 1.84}], 'summary': 'Exploring sub-queries with the delete statement in mysql workbench.', 'duration': 53.68, 'max_score': 1139.836, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk1139836.jpg'}, {'end': 1343.819, 'src': 'embed', 'start': 1231.496, 'weight': 1, 'content': [{'end': 1239.42, 'text': 'Let me first run the inner query for you so that you get an idea of the employees who are less than 32 years of age.', 'start': 1231.496, 'duration': 7.924}, {'end': 1245.182, 'text': 'So there are nine employees in the table who have an age less than equal to 32.', 'start': 1240.38, 'duration': 4.802}, {'end': 1246.843, 'text': 'So we are going to delete the records.', 'start': 1245.182, 'duration': 1.661}, {'end': 1253.746, 'text': 'If I run this, okay, it says nine records deleted.', 'start': 1247.924, 'duration': 5.822}, {'end': 1260.463, 'text': "Now let's print or display what we have in the employees table.", 'start': 1254.647, 'duration': 5.816}, {'end': 1269.132, 'text': 'if i run this, there you go.', 'start': 1265.791, 'duration': 3.341}, {'end': 1276.876, 'text': 'so if you see the age table, we have total 11 employees now and all their ages are greater than 32,', 'start': 1269.132, 'duration': 7.744}, {'end': 1281.998, 'text': 'because we have deleted all those employees who had an age less than equal to 32.', 'start': 1276.876, 'duration': 5.122}, {'end': 1287.861, 'text': 'okay, so let me show you from the beginning what we did.', 'start': 1281.998, 'duration': 5.863}, {'end': 1293.463, 'text': 'so first we used our sub queries database, then we used our employees table.', 'start': 1287.861, 'duration': 5.602}, {'end': 1301.699, 'text': 'So we started by looking at how you can use the subquery with a SELECT statement.', 'start': 1295.731, 'duration': 5.968}, {'end': 1304.783, 'text': 'This should be INSERT instead of UPDATE.', 'start': 1302.28, 'duration': 2.503}, {'end': 1309.369, 'text': 'So we learned how to write an INSERT subquery.', 'start': 1306.305, 'duration': 3.064}, {'end': 1312.954, 'text': 'We use two tables, PRODUCTS and our ORDERS table.', 'start': 1310.29, 'duration': 2.664}, {'end': 1321.599, 'text': 'moving ahead, we saw how to write sub queries using the update command,', 'start': 1315.854, 'duration': 5.745}, {'end': 1329.987, 'text': 'so we updated the salaries of the employee by a factor of 0.35 for those who had an age greater than equal to 27, and finally,', 'start': 1321.599, 'duration': 8.388}, {'end': 1339.315, 'text': 'we saw how to use the sub query using the delete statement, so we deleted all those records for the employees whose age was less than equal to 32.', 'start': 1329.987, 'duration': 9.328}, {'end': 1342.498, 'text': 'So that brings us to the end of this video on sub queries in SQL.', 'start': 1339.315, 'duration': 3.183}, {'end': 1343.819, 'text': 'I hope you liked it.', 'start': 1343.059, 'duration': 0.76}], 'summary': 'Nine records deleted for employees aged <= 32, leaving 11 employees > 32. covered subqueries with insert, update, and delete statements.', 'duration': 112.323, 'max_score': 1231.496, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk1231496.jpg'}], 'start': 1139.836, 'title': 'Using sub-queries with delete statement', 'summary': "Demonstrates the use of sub-queries with the delete statement in mysql, allowing the deletion of specific records based on a sub-query condition, such as deleting employees aged 32 or younger from the 'employees' table. it also covers the process of deleting records based on age, resulting in a total of nine records being deleted and leaving a total of 11 employees with ages greater than 32. additionally, it covers insert, update, and delete subqueries in sql, demonstrating inserting, updating, and deleting records using subqueries, including increasing employee salaries by 35% for those aged 27 or older and deleting records for employees aged 32 or younger.", 'chapters': [{'end': 1230.715, 'start': 1139.836, 'title': 'Using sub-queries with delete statement', 'summary': "Demonstrates the use of sub-queries with the delete statement in mysql, allowing the deletion of specific records based on a sub-query condition, such as deleting employees aged 32 or younger from the 'employees' table.", 'duration': 90.879, 'highlights': ["The chapter demonstrates the use of sub-queries with the DELETE statement in MySQL, allowing the deletion of specific records based on a sub-query condition, such as deleting employees aged 32 or younger from the 'employees' table.", 'The basic syntax of a DELETE query using sub-query is presented, showcasing the structure of the DELETE statement with an inner query and where clause, specifically targeting records based on a specified condition.', 'The example illustrates the application of a sub-query to delete employees whose age meets the condition of being less than or equal to 32, providing a practical demonstration of the DELETE statement with a sub-query.']}, {'end': 1293.463, 'start': 1231.496, 'title': 'Deleting records based on age', 'summary': 'Demonstrates the process of deleting records from the employees table based on age, resulting in a total of nine records being deleted and leaving a total of 11 employees with ages greater than 32.', 'duration': 61.967, 'highlights': ['Nine records were deleted from the employees table based on the condition of age less than or equal to 32.', 'The resulting table now contains a total of 11 employees with ages greater than 32.', 'The process involved using subqueries and the employees table in the database.']}, {'end': 1364.259, 'start': 1295.731, 'title': 'Sub queries in sql', 'summary': 'Covers insert, update, and delete subqueries in sql, demonstrating inserting, updating, and deleting records using subqueries, including increasing employee salaries by 35% for those aged 27 or older and deleting records for employees aged 32 or younger.', 'duration': 68.528, 'highlights': ['Demonstrated how to update employee salaries by 35% for those aged 27 or older', 'Showed how to delete records for employees aged 32 or younger', 'Covered writing INSERT, UPDATE, and DELETE subqueries in SQL']}], 'duration': 224.423, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JksrTuEVEPk/pics/JksrTuEVEPk1139836.jpg', 'highlights': ["The chapter demonstrates the use of sub-queries with the DELETE statement in MySQL, allowing the deletion of specific records based on a sub-query condition, such as deleting employees aged 32 or younger from the 'employees' table.", 'Nine records were deleted from the employees table based on the condition of age less than or equal to 32.', 'The basic syntax of a DELETE query using sub-query is presented, showcasing the structure of the DELETE statement with an inner query and where clause, specifically targeting records based on a specified condition.', 'The resulting table now contains a total of 11 employees with ages greater than 32.', 'Demonstrated how to update employee salaries by 35% for those aged 27 or older', 'Covered writing INSERT, UPDATE, and DELETE subqueries in SQL', 'The example illustrates the application of a sub-query to delete employees whose age meets the condition of being less than or equal to 32, providing a practical demonstration of the DELETE statement with a sub-query.', 'The process involved using subqueries and the employees table in the database.', 'Showed how to delete records for employees aged 32 or younger']}], 'highlights': ['The chapter demonstrates using subqueries to update employee salaries by multiplying them by 0.35 for those over 27, affecting 18 out of 20 employees.', "The chapter demonstrates the use of sub-queries with the DELETE statement in MySQL, allowing the deletion of specific records based on a sub-query condition, such as deleting employees aged 32 or younger from the 'employees' table.", "The chapter covers the creation of 'products' and 'orders' tables, insertion of 4 records in 'products' table, and insertion of 2 records in 'orders' table based on a condition of selling price greater than $1000.", 'The chapter discusses using a subquery with the select statement to fetch employee names, departments, and salaries less than the average salary, resulting in 12 employees with salaries below the average of $75,350.', 'The chapter covers sub-queries with SELECT, INSERT, UPDATE, and DELETE statements on MySQL Workbench.']}