title
SQL Interview Questions And Answers | SQL Interview Preparation | SQL Training | Simplilearn
description
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=SQLInterviewQuestions-L-URbfgxBMQ&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=SQLInterviewQuestions-L-URbfgxBMQ&utm_medium=Descriptionff&utm_source=youtube
🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQLInterviewQuestions-L-URbfgxBMQ&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=SQLInterviewQuestions-L-URbfgxBMQ&utm_medium=Descriptionff&utm_source=youtube
This video on SQL interview questions and answers covers a list of 30 important questions that will help you in SQL interview preparation. You will learn the top practical and theoretical questions to brush up on your skills and get an idea of the questions that are frequently asked. You will solve some interesting challenging problems on MySQL and MS SQL Server.
Dataset Link - https://drive.google.com/drive/folders/1E6i_Ny-yW76Qxt7K4zwAlAw6uAoUUaUM
Below are a few questions that you will learn:
00:00:00 SQL Interview Questions and Answers
00:00:53 Write an SQL query to fetch the unique values of departments and print their length.
00:16:26 Write an SQL query to display the departments that have more than 2 employees.
00:27:49 Print all the alternate records in a table.
00:34:06 Write the SQL query to fetch all the duplicate rows in a table.
00:37:53 Display the employees with exactly 2 A's in their name.
00:45:54 How does Self join work?
00:53:35 Write an SQL query to print one row twice in results from a table.
01:19:54 Using the Orders table in the Northwind database, find all the month-end orders.
01:22:23 Find the top 5 countries with the highest freight charges in the year 1997.
01:29:10 Using the Northwind database, find the list of late orders for all the employees.
âś…Subscribe to our Channel to learn more about the top Technologies: https://bit.ly/2VT4WtH
⏩ Check out the SQL training videos: https://www.youtube.com/watch?v=pFq1pgli0OQ&list=PLEiEAq2VkUUKL3yPbn8yWnatjUg0P0I-Z
#SQLInterviewQuestions #SQLInterviewQuestionsAndAnswers #SQLInterviewPreparation #SQLInterviewQuestionsAndAnswersForExperienced #SQLInterviewQuestionsAndAnswersForFreshers #SQL #SQLForBeginners #SQLTutorial #SQLCourse #SQLTraining #Simplilearn
🔥Explore Our Free Courses With Completion Certificate by SkillUp: https://www.simplilearn.com/skillup-free-online-courses?utm_campaign=SQLInterviewQuestions&utm_medium=Descritption&utm_source=youtube
➡️ 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=SQLInterviewQuestions-L-URbfgxBMQ&utm_medium=Description&utm_source=youtube
🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=SQLInterviewQuestions-L-URbfgxBMQ&utm_medium=Description&utm_source=youtube
🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688
detail
{'title': 'SQL Interview Questions And Answers | SQL Interview Preparation | SQL Training | Simplilearn', 'heatmap': [{'end': 1768.106, 'start': 1638.939, 'weight': 0.966}, {'end': 3131.449, 'start': 3012.304, 'weight': 0.986}, {'end': 3237.41, 'start': 3167.921, 'weight': 0.92}, {'end': 4316.228, 'start': 4262.822, 'weight': 0.872}, {'end': 4430.944, 'start': 4370.95, 'weight': 0.843}], 'summary': 'Covers 30 sql interview questions, sql functions, string manipulation, self join, case statements, database key differences, left join, and practical examples of fetching non-matching values and analyzing freight charges and late orders in sql.', 'chapters': [{'end': 755.427, 'segs': [{'end': 96.732, 'src': 'embed', 'start': 72, 'weight': 0, 'content': [{'end': 80.104, 'text': 'So WHERE clause is used to filter the records from the table based on the specified condition and can be used without the GROUP BY clause.', 'start': 72, 'duration': 8.104}, {'end': 86.287, 'text': 'On the other hand, HAVING clause is used to filter the records from groups based on specified conditions.', 'start': 80.744, 'duration': 5.543}, {'end': 90.689, 'text': 'Now WHERE clauses cannot have aggregate functions.', 'start': 88.048, 'duration': 2.641}, {'end': 96.732, 'text': 'So you cannot use SUM, COUNT, AVERAGE, MIN and MAXIMUM functions in the WHERE clause.', 'start': 91.309, 'duration': 5.423}], 'summary': 'Where clause filters records based on condition, having clause filters groups, where cannot have aggregate functions.', 'duration': 24.732, 'max_score': 72, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ72000.jpg'}, {'end': 178.92, 'src': 'embed', 'start': 147.563, 'weight': 1, 'content': [{'end': 153.026, 'text': 'Now moving to our next question, which is How is DROP different from TRUNCATE??', 'start': 147.563, 'duration': 5.463}, {'end': 157.674, 'text': 'so drop and truncate are two different commands.', 'start': 153.951, 'duration': 3.723}, {'end': 166.662, 'text': 'so drop command is used to drop the whole table, removing the table definition and its contents, while truncate deletes all the rows from the table.', 'start': 157.674, 'duration': 8.988}, {'end': 172.027, 'text': 'we can drop the whole table structure in one go, so the view of the table does not exist.', 'start': 166.662, 'duration': 5.365}, {'end': 178.92, 'text': 'by using the truncate command, the existence of all the rows in the table is lost, but the view in the table exists now.', 'start': 172.027, 'duration': 6.893}], 'summary': 'Drop removes table structure, truncate deletes table rows.', 'duration': 31.357, 'max_score': 147.563, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ147563.jpg'}, {'end': 248.803, 'src': 'embed', 'start': 218.886, 'weight': 2, 'content': [{'end': 228.657, 'text': "so i'm using a min function to find the lowest salary in the table and then i'm grouping all the employees and the results by department.", 'start': 218.886, 'duration': 9.771}, {'end': 233.478, 'text': "so let's do this on mysql workbench.", 'start': 228.657, 'duration': 4.821}, {'end': 236.319, 'text': 'okay, so here i am on my mysql workbench.', 'start': 233.478, 'duration': 2.841}, {'end': 240.12, 'text': 'first let me go ahead and create a new sql script.', 'start': 236.319, 'duration': 3.801}, {'end': 245.722, 'text': "so i'll click on this and that will create a new script.", 'start': 240.12, 'duration': 5.602}, {'end': 248.803, 'text': "okay, so here we'll write our commands.", 'start': 245.722, 'duration': 3.081}], 'summary': 'Using min function to find lowest salary and grouping employees by department in mysql workbench.', 'duration': 29.917, 'max_score': 218.886, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ218886.jpg'}, {'end': 482.656, 'src': 'embed', 'start': 459.32, 'weight': 3, 'content': [{'end': 472.353, 'text': 'so since I want to get the unique values from a column in a table, so the right option would be to use the distinct keyword in the query.', 'start': 459.32, 'duration': 13.033}, {'end': 476.514, 'text': 'so if I write select distinct followed by the column name,', 'start': 472.353, 'duration': 4.161}, {'end': 482.656, 'text': "let's say you want to find the distinct cities or the distinct departments in the employees table.", 'start': 476.514, 'duration': 6.142}], 'summary': "To retrieve unique values from a column, use 'distinct' in the query.", 'duration': 23.336, 'max_score': 459.32, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ459320.jpg'}, {'end': 633.296, 'src': 'embed', 'start': 608.971, 'weight': 4, 'content': [{'end': 616.34, 'text': 'now the sixth question is to write an SQL query to fetch the unique values of departments and print their length.', 'start': 608.971, 'duration': 7.369}, {'end': 620.405, 'text': 'now this question consists of two parts.', 'start': 616.34, 'duration': 4.065}, {'end': 624.51, 'text': 'first, we need to fetch the unique values of departments that we saw in our fifth question,', 'start': 620.405, 'duration': 4.105}, {'end': 629.354, 'text': 'And then we want to print the length of each of the departments.', 'start': 625.752, 'duration': 3.602}, {'end': 633.296, 'text': "So for that, I'm using the distinct keyword to find the unique departments.", 'start': 629.754, 'duration': 3.542}], 'summary': 'Write an sql query to fetch unique department values and print their length.', 'duration': 24.325, 'max_score': 608.971, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ608971.jpg'}], 'start': 8.274, 'title': 'Sql interview questions and fetching unique values', 'summary': 'Covers 30 important sql interview questions, such as where vs having clauses and drop vs truncate commands, and demonstrates using sql queries to fetch unique values from a column in a table, including examples of using distinct keyword and inbuilt functions like length.', 'chapters': [{'end': 439.111, 'start': 8.274, 'title': 'Sql interview questions tutorial', 'summary': 'Covers 30 important sql interview questions, including the difference between where and having clauses, drop vs truncate commands, and finding the lowest salary for each department using the min function in mysql.', 'duration': 430.837, 'highlights': ['The WHERE clause is used to filter records based on specified conditions and can be used without the GROUP BY clause, while the HAVING clause is used to filter records from groups based on specified conditions. The WHERE clause filters records based on specified conditions and can be used without the GROUP BY clause, while the HAVING clause filters records from groups based on specified conditions.', "The difference between DROP and TRUNCATE commands lies in drop removing the table definition and its contents, while truncate deletes all the rows from the table but retains the table's structure with integrity constraints intact. The DROP command removes the table definition and its contents, while TRUNCATE deletes all the rows from the table but retains the table's structure with integrity constraints intact.", 'Using the MIN function in MySQL, the tutorial demonstrates finding the lowest salary for each department by grouping the results by department. The tutorial uses the MIN function in MySQL to find the lowest salary for each department by grouping the results by department.']}, {'end': 755.427, 'start': 439.111, 'title': 'Sql query for fetching unique values', 'summary': 'Discusses using sql queries to fetch unique values from a column in a table, demonstrating the usage of distinct keyword and inbuilt functions like length, with an example of fetching unique departments and their lengths from the employees table.', 'duration': 316.316, 'highlights': ["Using distinct keyword to fetch unique values from a column in a table The speaker explains that using the 'distinct' keyword followed by the column name in the SQL query allows fetching unique values, providing an example of finding distinct cities or departments from the employees table.", "Demonstrating the usage of distinct keyword to fetch unique departments The speaker demonstrates the SQL query 'select distinct department from employees' to fetch unique departments from the employees table, showing the result of seven unique departments in the table.", "Using distinct keyword and inbuilt length function to fetch unique departments and their lengths The speaker explains the SQL query 'select distinct department, length(department) as department_length from employees' to fetch unique departments and their lengths, providing an example with the result of department names and their respective lengths."]}], 'duration': 747.153, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ8274.jpg', 'highlights': ['The WHERE clause filters records based on specified conditions and can be used without the GROUP BY clause, while the HAVING clause filters records from groups based on specified conditions.', "The difference between DROP and TRUNCATE commands lies in drop removing the table definition and its contents, while truncate deletes all the rows from the table but retains the table's structure with integrity constraints intact.", 'Using the MIN function in MySQL, the tutorial demonstrates finding the lowest salary for each department by grouping the results by department.', 'Using distinct keyword to fetch unique values from a column in a table.', 'Demonstrating the usage of distinct keyword to fetch unique departments.', 'Using distinct keyword and inbuilt length function to fetch unique departments and their lengths.']}, {'end': 1456.605, 'segs': [{'end': 823.724, 'src': 'embed', 'start': 785.523, 'weight': 0, 'content': [{'end': 794.366, 'text': 'so here i have two date values, that is, 10th of april 2021 and i have 30th of march 2021.', 'start': 785.523, 'duration': 8.843}, {'end': 807.78, 'text': 'so this query will give us the total number of days that lie between these two dates and similarly,', 'start': 794.366, 'duration': 13.414}, {'end': 812.201, 'text': 'here is another example of using the DATEDIP function.', 'start': 807.78, 'duration': 4.421}, {'end': 823.724, 'text': "so I want to calculate the total number of days that are present between today's date so I am using the NOW function and between 20th of April 2021.", 'start': 812.201, 'duration': 11.523}], 'summary': "Calculating the number of days between specified dates and today's date using datediff and now functions.", 'duration': 38.201, 'max_score': 785.523, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ785523.jpg'}, {'end': 1098.917, 'src': 'embed', 'start': 1074.068, 'weight': 2, 'content': [{'end': 1080.03, 'text': 'so our query will return only those departments which have more than two employees.', 'start': 1074.068, 'duration': 5.962}, {'end': 1080.83, 'text': "let's run and see.", 'start': 1080.03, 'duration': 0.8}, {'end': 1082.951, 'text': 'there you go.', 'start': 1080.83, 'duration': 2.121}, {'end': 1091.775, 'text': 'so we have total four departments, that is, sales, product, tech and it, where we have more than two employees.', 'start': 1082.951, 'duration': 8.824}, {'end': 1098.917, 'text': 'you can see here sales has four employees, product has three, tech has four and IT has three.', 'start': 1091.775, 'duration': 7.142}], 'summary': 'Four departments have more than two employees: sales (4), product (3), tech (4), it (3).', 'duration': 24.849, 'max_score': 1074.068, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1074068.jpg'}, {'end': 1239.281, 'src': 'embed', 'start': 1201.212, 'weight': 3, 'content': [{'end': 1213.948, 'text': "where department not equal to marketing, i'll put marketing under quotes.", 'start': 1201.212, 'duration': 12.736}, {'end': 1218.051, 'text': "let's run it and see the results.", 'start': 1213.948, 'duration': 4.103}, {'end': 1219.291, 'text': 'there you go.', 'start': 1218.051, 'duration': 1.24}, {'end': 1232.819, 'text': 'so we have total 18 rows of information and if you see the department column, we have all the departments apart from marketing.', 'start': 1219.291, 'duration': 13.528}, {'end': 1239.281, 'text': 'now, if you remember, our employees table had total 20 rows of information, out of which two are missing,', 'start': 1232.819, 'duration': 6.462}], 'summary': '18 rows of non-marketing data found in department analysis.', 'duration': 38.069, 'max_score': 1201.212, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1201212.jpg'}, {'end': 1304.917, 'src': 'embed', 'start': 1266.351, 'weight': 4, 'content': [{'end': 1278.122, 'text': 'You see here, it gives us the same result and has returned us 18 rows of information, leaving apart the employees from the marketing department.', 'start': 1266.351, 'duration': 11.771}, {'end': 1279.103, 'text': 'All right.', 'start': 1278.803, 'duration': 0.3}, {'end': 1281.345, 'text': 'Now moving ahead.', 'start': 1280.724, 'duration': 0.621}, {'end': 1299.131, 'text': 'so our 10th question is to write an SQL query to print the details of the employees who have joined before April 2010 and after May 2005.', 'start': 1285.157, 'duration': 13.974}, {'end': 1304.917, 'text': 'so here is my employees table and this is my SQL query.', 'start': 1299.131, 'duration': 5.786}], 'summary': 'Sql query retrieved 18 rows, excluding marketing employees.', 'duration': 38.566, 'max_score': 1266.351, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1266351.jpg'}], 'start': 755.427, 'title': 'Sql functions and queries', 'summary': 'Covers the use of datedif function in sql to calculate days between dates, and provides sql query examples for displaying department details and employee information, including specific date conditions.', 'chapters': [{'end': 976.262, 'start': 755.427, 'title': 'Using datedif function in sql', 'summary': 'Discusses the use of datedif function in sql, which returns the number of days between two dates or date time values, and demonstrates its application with examples, showcasing the calculation of days between specific dates and the current date.', 'duration': 220.835, 'highlights': ['DATEDIF returns the number of days between two date or date time values. This inbuilt function in SQL is used to calculate the difference in days between two date or date time values.', "Demonstration of calculating the total number of days between specific dates using DATEDIF function. The speaker provides examples of using DATEDIF to calculate the number of days between specific date values, such as 10th of April 2021 and 30th of March 2021, and between today's date and 20th of April 2021.", 'Application of DATEDIF function using the NOW function to calculate the number of days between the current date and a specific date. The demonstration includes using the NOW function to obtain the current date and calculating the days between the current date and a specific date, like 20th of April 2021, showcasing the practical application of DATEDIF function.']}, {'end': 1456.605, 'start': 976.262, 'title': 'Sql query examples & details', 'summary': 'Covers writing sql queries to display departments with more than two employees, employees details except for marketing department, and employee details who joined before april 2010 and after may 2005.', 'duration': 480.343, 'highlights': ['The query returns departments with more than two employees, including sales with 4 employees, product with 3 employees, tech with 4 employees, and IT with 3 employees, while other departments have less than two employees. 4 departments with more than 2 employees, 18 rows of information excluding marketing department, 20 total rows of information in the employees table', 'The query excludes the marketing department and displays details of employees from all other departments, resulting in 18 rows of information. Excludes employees from the marketing department, displays all other department details, 18 rows of information', 'The query prints the details of employees who joined before April 2010 and after May 2005 based on the date of join, using a where condition to filter the results. Filtering employees based on joining dates before April 2010 and after May 2005']}], 'duration': 701.178, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ755427.jpg', 'highlights': ['Demonstration of calculating the total number of days between specific dates using DATEDIF function.', 'Application of DATEDIF function using the NOW function to calculate the number of days between the current date and a specific date.', 'The query returns departments with more than two employees, including sales with 4 employees, product with 3 employees, tech with 4 employees, and IT with 3 employees.', 'The query excludes the marketing department and displays details of employees from all other departments, resulting in 18 rows of information.', 'The query prints the details of employees who joined before April 2010 and after May 2005 based on the date of join, using a where condition to filter the results.']}, {'end': 2743.321, 'segs': [{'end': 1509.845, 'src': 'embed', 'start': 1456.605, 'weight': 0, 'content': [{'end': 1459.526, 'text': 'so we have total three employees in our table.', 'start': 1456.605, 'duration': 2.921}, {'end': 1470.875, 'text': 'you can see the date of join, and all of them have joined after May 2005 and before April 2010.', 'start': 1459.526, 'duration': 11.349}, {'end': 1475.022, 'text': 'okay, now moving ahead to the 11th question.', 'start': 1470.875, 'duration': 4.147}, {'end': 1481.898, 'text': 'so here we want to find the employee with the third highest salary from the table.', 'start': 1476.374, 'duration': 5.524}, {'end': 1492.225, 'text': 'so i have my employees table here and my command would look something like this so here we are going to explore how to write sub queries in sql.', 'start': 1481.898, 'duration': 10.327}, {'end': 1505.744, 'text': "so i'm using a sub query here where i'm trying to find the employees who have the top three salaries or the top three employees who have the highest salaries.", 'start': 1492.225, 'duration': 13.519}, {'end': 1509.845, 'text': "And from that, I'm going to filter out my third highest salary employee.", 'start': 1506.244, 'duration': 3.601}], 'summary': 'Three employees joined between may 2005 and april 2010; seeking third highest salary using sql subquery.', 'duration': 53.24, 'max_score': 1456.605, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1456605.jpg'}, {'end': 1669.741, 'src': 'embed', 'start': 1638.939, 'weight': 2, 'content': [{'end': 1646.625, 'text': "Jack will appear at the top, Angela will be at the second place and Joseph will come to the third place, and from there I'm using limit 1,", 'start': 1638.939, 'duration': 7.686}, {'end': 1649.367, 'text': 'so that I can only get the record for Jack.', 'start': 1646.625, 'duration': 2.742}, {'end': 1653.813, 'text': 'now, if i run the entire query, you see the result.', 'start': 1650.248, 'duration': 3.565}, {'end': 1659.581, 'text': 'i have my employee name as jack, who owns the third highest salary.', 'start': 1653.813, 'duration': 5.768}, {'end': 1669.741, 'text': 'okay, now, moving ahead to the 12th question, the question is to print all the alternate records in a table.', 'start': 1659.581, 'duration': 10.16}], 'summary': 'Jack has the third highest salary and angela is in second place. the 12th question is about printing alternate records in a table.', 'duration': 30.802, 'max_score': 1638.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1638939.jpg'}, {'end': 1720.72, 'src': 'embed', 'start': 1690.636, 'weight': 4, 'content': [{'end': 1704.669, 'text': 'and you can look at this example in another way of displaying the employees which have an employee id of even numbers, so 102, 104, 106 and so on.', 'start': 1690.636, 'duration': 14.033}, {'end': 1714.536, 'text': 'now the way to do is either you can use a simple select clause and say employee id percentage, or modulus 2 equal to 0,', 'start': 1704.669, 'duration': 9.867}, {'end': 1720.72, 'text': 'which means i want to filter only those employees which have an even employee id.', 'start': 1714.536, 'duration': 6.184}], 'summary': 'Filter employees with even employee id using modulus 2 equals 0.', 'duration': 30.084, 'max_score': 1690.636, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1690636.jpg'}, {'end': 1777.112, 'src': 'heatmap', 'start': 1638.939, 'weight': 5, 'content': [{'end': 1646.625, 'text': "Jack will appear at the top, Angela will be at the second place and Joseph will come to the third place, and from there I'm using limit 1,", 'start': 1638.939, 'duration': 7.686}, {'end': 1649.367, 'text': 'so that I can only get the record for Jack.', 'start': 1646.625, 'duration': 2.742}, {'end': 1653.813, 'text': 'now, if i run the entire query, you see the result.', 'start': 1650.248, 'duration': 3.565}, {'end': 1659.581, 'text': 'i have my employee name as jack, who owns the third highest salary.', 'start': 1653.813, 'duration': 5.768}, {'end': 1669.741, 'text': 'okay, now, moving ahead to the 12th question, the question is to print all the alternate records in a table.', 'start': 1659.581, 'duration': 10.16}, {'end': 1674.544, 'text': 'so here is my employees table and from this table i am going to print all the alternate records.', 'start': 1669.741, 'duration': 4.803}, {'end': 1684.351, 'text': 'for example, i am going to print the my employee id 101, then i am going to skip 102 and print 103, followed by 105, 107, 109,', 'start': 1674.544, 'duration': 9.807}, {'end': 1690.636, 'text': 'so all the employee ids that have an odd value.', 'start': 1684.351, 'duration': 6.285}, {'end': 1704.669, 'text': 'and you can look at this example in another way of displaying the employees which have an employee id of even numbers, so 102, 104, 106 and so on.', 'start': 1690.636, 'duration': 14.033}, {'end': 1714.536, 'text': 'now the way to do is either you can use a simple select clause and say employee id percentage, or modulus 2 equal to 0,', 'start': 1704.669, 'duration': 9.867}, {'end': 1720.72, 'text': 'which means i want to filter only those employees which have an even employee id.', 'start': 1714.536, 'duration': 6.184}, {'end': 1725.363, 'text': 'else what you can do is you can use a cte, as you can see here.', 'start': 1720.72, 'duration': 4.643}, {'end': 1735.526, 'text': 'So CTE, or the common table expression, is a temporary named result set that you can reference within a select insert, update or delete statement.', 'start': 1726.84, 'duration': 8.686}, {'end': 1742.251, 'text': 'Now it is used to simplify complex joins and sub queries and to provide a means to query hierarchical data.', 'start': 1735.986, 'duration': 6.265}, {'end': 1747.114, 'text': "Now let's write this query on our workbench and see how it works.", 'start': 1743.151, 'duration': 3.963}, {'end': 1750.657, 'text': 'Before that, let me explain you.', 'start': 1749.416, 'duration': 1.241}, {'end': 1756.878, 'text': 'I am using a few new functions such as row number.', 'start': 1750.657, 'duration': 6.221}, {'end': 1768.106, 'text': 'this will create a row number for each of the rows or the records present in my table and then from this cte i am going to use my filter clause or the where clause,', 'start': 1756.878, 'duration': 11.228}, {'end': 1772.229, 'text': 'where rn, which is row number, percentage, 2, equal to equal to 1.', 'start': 1768.106, 'duration': 4.123}, {'end': 1777.112, 'text': 'so this will return only the odd records from the table.', 'start': 1772.229, 'duration': 4.883}], 'summary': 'Jack has the third highest salary. the query demonstrates printing alternate records from the employees table using a select clause and a common table expression (cte) with the row number function.', 'duration': 27.696, 'max_score': 1638.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1638939.jpg'}, {'end': 1889.299, 'src': 'embed', 'start': 1845.798, 'weight': 3, 'content': [{'end': 1858.346, 'text': 'Now if you want to print only the order records in the table you can use employee ID %2 equal to 1.', 'start': 1845.798, 'duration': 12.548}, {'end': 1864.699, 'text': 'So you can see here I have 101, 103, 105, 107 up to 119.', 'start': 1858.346, 'duration': 6.353}, {'end': 1871.364, 'text': 'now. the best way to do this is to use a common table expression of CTE.', 'start': 1864.699, 'duration': 6.665}, {'end': 1881.613, 'text': "so I'll start with my command, that is, with CTE, as I'll start with my bracket.", 'start': 1871.364, 'duration': 10.249}, {'end': 1885.896, 'text': "I'll write select star comma.", 'start': 1881.613, 'duration': 4.283}, {'end': 1889.299, 'text': "then I'm going to use my row number function.", 'start': 1885.896, 'duration': 3.403}], 'summary': 'Use cte to print order records with employee id %2 equal to 1.', 'duration': 43.501, 'max_score': 1845.798, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1845798.jpg'}, {'end': 2106.644, 'src': 'embed', 'start': 2081.744, 'weight': 6, 'content': [{'end': 2089.831, 'text': 'so this is a different person than compared to the third name that you have.', 'start': 2081.744, 'duration': 8.087}, {'end': 2094.454, 'text': 'so this is how you can find the duplicate rows in a table.', 'start': 2089.831, 'duration': 4.623}, {'end': 2103.501, 'text': 'so i am going to select my employee id, name and age and then count all the employee ids, the names and age,', 'start': 2094.454, 'duration': 9.047}, {'end': 2106.644, 'text': 'and compare them with whether they are greater than 1 or not.', 'start': 2103.501, 'duration': 3.143}], 'summary': 'Demonstrates finding duplicate rows in a table, comparing employee data', 'duration': 24.9, 'max_score': 2081.744, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2081744.jpg'}, {'end': 2246.888, 'src': 'embed', 'start': 2195.021, 'weight': 7, 'content': [{'end': 2203.35, 'text': 'this is my alias name from my table, that is dup underscore employees.', 'start': 2195.021, 'duration': 8.329}, {'end': 2212.429, 'text': "I'll say group by employee ID, name and age,", 'start': 2203.35, 'duration': 9.079}, {'end': 2240.306, 'text': "and then I'm going to use my having clause saying having count of employee ID is greater than 1 and count of name should also be greater than 1 and my count of age should also be greater than 1.", 'start': 2212.429, 'duration': 27.877}, {'end': 2244.407, 'text': "let's run it and see the results.", 'start': 2240.306, 'duration': 4.101}, {'end': 2246.888, 'text': 'okay, there is some error here.', 'start': 2244.407, 'duration': 2.481}], 'summary': "Using alias 'dup_employees' to group by employee id, name, and age, with having count > 1. error encountered.", 'duration': 51.867, 'max_score': 2195.021, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2195021.jpg'}, {'end': 2343.313, 'src': 'embed', 'start': 2303.752, 'weight': 10, 'content': [{'end': 2314.161, 'text': 'I write select star from employees, where I am finding the length of my employee name and then subtracting my length,', 'start': 2303.752, 'duration': 10.409}, {'end': 2326.183, 'text': 'wherein I have replaced my employee name that has a with a blank, and if this subtraction is equal to two,', 'start': 2314.161, 'duration': 12.022}, {'end': 2330.645, 'text': "then that particular employee has two a's in their names.", 'start': 2326.183, 'duration': 4.462}, {'end': 2343.313, 'text': "so let me show this first let me first give my comment as employees with two a's.", 'start': 2330.645, 'duration': 12.668}], 'summary': "Query to find employees with two 'a's in their names.", 'duration': 39.561, 'max_score': 2303.752, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2303752.jpg'}, {'end': 2501.226, 'src': 'embed', 'start': 2417.535, 'weight': 9, 'content': [{'end': 2425.225, 'text': "okay, now, let me go ahead and write a SQL query and then I'll explain you what I'm trying to do.", 'start': 2417.535, 'duration': 7.69}, {'end': 2436.328, 'text': "I'll write select, I'm using the length function and in the length function I'm going to replace.", 'start': 2425.225, 'duration': 11.103}, {'end': 2447.091, 'text': "then I'll use another function called upper on top of employee name.", 'start': 2436.328, 'duration': 10.763}, {'end': 2453.568, 'text': "I'll write a and then replace it with a blank.", 'start': 2447.091, 'duration': 6.477}, {'end': 2462.075, 'text': "so i'm using quotations to represent blank from employees.", 'start': 2453.568, 'duration': 8.507}, {'end': 2466.318, 'text': "okay, let's run this and see the results.", 'start': 2462.075, 'duration': 4.243}, {'end': 2479.868, 'text': "you see here it has given us the length of the employee strings after replacing all the a's with a blank.", 'start': 2466.318, 'duration': 13.55}, {'end': 2489.636, 'text': "so here I'm essentially converting my employee names to uppercase and I'm changing wherever there is an a,", 'start': 2481.269, 'duration': 8.367}, {'end': 2496.041, 'text': "I'm replacing that a with a blank and then I'm finding the length.", 'start': 2489.636, 'duration': 6.405}, {'end': 2501.226, 'text': "so this trick I'm going to use in my final query.", 'start': 2496.041, 'duration': 5.185}], 'summary': "Sql query replaces 'a' with blank, finds length of employee names", 'duration': 83.691, 'max_score': 2417.535, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2417535.jpg'}, {'end': 2574.603, 'src': 'embed', 'start': 2546.513, 'weight': 8, 'content': [{'end': 2557.095, 'text': "I am subtracting the length after replacing the A's present in the employee name with a blank, and this value should be equal to 2..", 'start': 2546.513, 'duration': 10.582}, {'end': 2561.676, 'text': "The reason being we are only trying to find the employees who have 2 A's in their name.", 'start': 2557.095, 'duration': 4.581}, {'end': 2564.137, 'text': "Let's run and see the results.", 'start': 2562.557, 'duration': 1.58}, {'end': 2574.603, 'text': "Okay, if you see here, we have Sarah, Angela, Amelia and Maya who have two A's in their name.", 'start': 2565.774, 'duration': 8.829}], 'summary': "Finding employees with 2 a's in their name, resulting in sarah, angela, amelia, and maya.", 'duration': 28.09, 'max_score': 2546.513, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2546513.jpg'}, {'end': 2686.615, 'src': 'embed', 'start': 2649.49, 'weight': 12, 'content': [{'end': 2652.511, 'text': 'and then I want to extract 4 characters from it.', 'start': 2649.49, 'duration': 3.021}, {'end': 2653.112, 'text': "let's do it.", 'start': 2652.511, 'duration': 0.601}, {'end': 2656.834, 'text': 'I will give a comment.', 'start': 2653.112, 'duration': 3.722}, {'end': 2664.078, 'text': 'extract strings.', 'start': 2656.834, 'duration': 7.244}, {'end': 2666.199, 'text': 'so here I am going to write select.', 'start': 2664.078, 'duration': 2.121}, {'end': 2670.998, 'text': "First I'm going to use substr, which is my inbuilt function.", 'start': 2667.654, 'duration': 3.344}, {'end': 2676.864, 'text': "I'm going to pass in my input string, which says Michael Balak.", 'start': 2671.718, 'duration': 5.146}, {'end': 2686.615, 'text': "And then I'll give my starting position, which is 2, and then the number of characters, that is 4.", 'start': 2680.007, 'duration': 6.608}], 'summary': "Using the substr function to extract 4 characters from 'michael balak'.", 'duration': 37.125, 'max_score': 2649.49, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2649490.jpg'}], 'start': 1456.605, 'title': 'Sql query functions and string extraction', 'summary': "Covers using sql functions like length, replace, and substring to manipulate strings and extract specific characters, with examples of extracting names with two a's and retrieving characters from specific positions within a string.", 'chapters': [{'end': 1659.581, 'start': 1456.605, 'title': 'Finding third highest salary in sql', 'summary': "Demonstrates how to use subqueries in sql to find the employee with the third highest salary from a table of three employees who joined after may 2005 and before april 2010, ultimately identifying 'jack' as the employee with the third highest salary.", 'duration': 202.976, 'highlights': ['A demonstration of using subqueries in SQL to find the employee with the third highest salary from a table of three employees who joined after May 2005 and before April 2010. Use of subqueries, filtering based on salary, and specific date range for employee join dates.', "Explanation of the SQL query to find the employee with the third highest salary, including the use of inner and outer queries with specific commands such as 'order by' and 'limit'. Detailed explanation of SQL query structure, including the use of 'order by', 'limit', and inner and outer queries.", "Identification of 'Jack' as the employee with the third highest salary through the execution of the SQL query. Final result of the SQL query identifying 'Jack' as the employee with the third highest salary."]}, {'end': 2030.942, 'start': 1659.581, 'title': 'Printing alternate records', 'summary': 'Demonstrates how to print alternate records from a table using a simple select clause and a common table expression, providing examples of filtering odd and even employee ids and explaining the usage of row number function.', 'duration': 371.361, 'highlights': ['The chapter demonstrates how to print alternate records from a table using a simple select clause and a common table expression. It explains the process of printing alternate records from a table using different methods.', 'The chapter provides examples of filtering odd and even employee IDs. It includes examples of filtering odd and even employee IDs using the modulus operator.', 'The chapter explains the usage of the row number function. It covers the explanation of the row number function and its usage in creating a row number for each record in the table.']}, {'end': 2417.535, 'start': 2030.942, 'title': "Sql duplicate rows & employees with two a's", 'summary': "Covers how to find duplicate rows in a sql table by writing a query that fetches all duplicate records and then demonstrates how to display employees with exactly two 'a's in their names using mysql workbench.", 'duration': 386.593, 'highlights': ['To find duplicate rows in a table, a SQL query is written to fetch all the duplicate records by comparing the count of employee IDs, names, and ages with whether they are greater than 1 or not. The speaker explains how to find duplicate rows in a table by writing a SQL query that counts the occurrences of employee IDs, names, and ages, and compares them to check for duplicates.', "The demonstration includes running a query to select the records from the table and then writing a SQL query with a 'group by' clause and a 'having' clause to identify the duplicated records. The speaker demonstrates the process by running a query to select records from the table and then writes a SQL query with a 'group by' clause and a 'having' clause to identify the duplicated records.", "Another demonstration involves identifying employees with exactly two 'a's in their names by finding the length of the employee name and then subtracting the length where the 'a's are replaced with a blank, and if the subtraction equals two, the employee has two 'a's in their name. The speaker demonstrates how to identify employees with exactly two 'a's in their names by finding the length of the employee name, subtracting the length where the 'a's are replaced with a blank, and determining if the subtraction equals two."]}, {'end': 2743.321, 'start': 2417.535, 'title': 'Sql query functions and string extraction', 'summary': "Covers using sql functions like length, replace, and substring to manipulate strings and extract specific characters, with examples of extracting names with two a's and retrieving characters from specific positions within a string.", 'duration': 325.786, 'highlights': ['Using SQL functions like length, replace, and substring to manipulate strings and extract specific characters. The speaker demonstrates using SQL functions like length, replace, and substring to manipulate strings and extract specific characters.', "Example of using SQL functions to find names with two A's and retrieving characters from a specific position within a string. The speaker provides an example of using SQL functions to find names with two A's and retrieving characters from specific positions within a string.", 'Demonstrating the application of the length function and the replace function to change specific characters within a string and find the length of the modified string. The speaker demonstrates the application of the length function and the replace function to change specific characters within a string and find the length of the modified string.', 'Extracting characters from a specific position within a string using SQL functions like substr and substring. The speaker explains the process of extracting characters from a specific position within a string using SQL functions like substr and substring.']}], 'duration': 1286.716, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ1456605.jpg', 'highlights': ['Demonstration of using subqueries in SQL to find the employee with the third highest salary from a table of three employees who joined after May 2005 and before April 2010.', "Explanation of the SQL query to find the employee with the third highest salary, including the use of inner and outer queries with specific commands such as 'order by' and 'limit'.", "Identification of 'Jack' as the employee with the third highest salary through the execution of the SQL query.", 'Demonstration of how to print alternate records from a table using a simple select clause and a common table expression.', 'Examples of filtering odd and even employee IDs using the modulus operator.', 'Explanation of the row number function and its usage in creating a row number for each record in the table.', 'Writing a SQL query to fetch all the duplicate records by comparing the count of employee IDs, names, and ages with whether they are greater than 1 or not.', "Demonstration of running a query to select the records from the table and then writing a SQL query with a 'group by' clause and a 'having' clause to identify the duplicated records.", "Demonstration of identifying employees with exactly two 'a's in their names by finding the length of the employee name and then subtracting the length where the 'a's are replaced with a blank.", 'Using SQL functions like length, replace, and substring to manipulate strings and extract specific characters.', "Example of using SQL functions to find names with two A's and retrieving characters from a specific position within a string.", 'Application of the length function and the replace function to change specific characters within a string and find the length of the modified string.', 'Extracting characters from a specific position within a string using SQL functions like substr and substring.']}, {'end': 3206.214, 'segs': [{'end': 2919.587, 'src': 'embed', 'start': 2855.622, 'weight': 1, 'content': [{'end': 2865.783, 'text': 'okay, now I want to use this table and return the name of the manager for each employee.', 'start': 2855.622, 'duration': 10.161}, {'end': 2868.245, 'text': 'to do that, I am going to use my self join.', 'start': 2865.783, 'duration': 2.462}, {'end': 2874.823, 'text': 'so here I am using self join to join both the tables here.', 'start': 2868.245, 'duration': 6.578}, {'end': 2878.206, 'text': "both the tables mean I'm using the same table twice.", 'start': 2874.823, 'duration': 3.383}, {'end': 2880.788, 'text': 'so I have written select E dot employee ID.', 'start': 2878.206, 'duration': 2.582}, {'end': 2882.129, 'text': 'E dot employee name.', 'start': 2880.788, 'duration': 1.341}, {'end': 2884.391, 'text': 'then we have E dot manager ID.', 'start': 2882.129, 'duration': 2.262}, {'end': 2887.053, 'text': "then I'm using another alias for managers.", 'start': 2884.391, 'duration': 2.662}, {'end': 2891.377, 'text': 'so M dot employee name as manager underscore name.', 'start': 2887.053, 'duration': 4.324}, {'end': 2897.112, 'text': 'from employee underscore manager as e.', 'start': 2891.377, 'duration': 5.735}, {'end': 2899.253, 'text': 'this join means self join.', 'start': 2897.112, 'duration': 2.141}, {'end': 2904.517, 'text': 'employee underscore manager as m on e dot manager id equal to e dot employee id.', 'start': 2899.253, 'duration': 5.264}, {'end': 2908.44, 'text': 'so this is my common column, that is employee id and manager id.', 'start': 2904.517, 'duration': 3.923}, {'end': 2909.861, 'text': "let's do it all right.", 'start': 2908.44, 'duration': 1.421}, {'end': 2917.165, 'text': "I'll give a comment here saying self join and I'll start with my self join.", 'start': 2910.741, 'duration': 6.424}, {'end': 2919.587, 'text': "I'll write select.", 'start': 2917.165, 'duration': 2.422}], 'summary': 'Using self join to return manager names for each employee in a table.', 'duration': 63.965, 'max_score': 2855.622, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2855622.jpg'}, {'end': 3131.449, 'src': 'heatmap', 'start': 3012.304, 'weight': 0.986, 'content': [{'end': 3015.165, 'text': "let me run this and we'll see the results.", 'start': 3012.304, 'duration': 2.861}, {'end': 3016.305, 'text': 'there you go.', 'start': 3015.165, 'duration': 1.14}, {'end': 3020.506, 'text': 'so Gary reports to the manager.', 'start': 3016.305, 'duration': 4.201}, {'end': 3021.246, 'text': 'ID 3.', 'start': 3020.506, 'duration': 0.74}, {'end': 3023.926, 'text': 'so from the table, 3 is for Smith.', 'start': 3021.246, 'duration': 2.68}, {'end': 3025.406, 'text': "so Gary's manager is Smith.", 'start': 3023.926, 'duration': 1.48}, {'end': 3031.908, 'text': "Gibbs manager is Gary, Smith's manager is Latham and Jimmy's manager is Smith.", 'start': 3025.406, 'duration': 6.502}, {'end': 3035.588, 'text': 'okay, so this is how you can use self join.', 'start': 3031.908, 'duration': 3.68}, {'end': 3038.489, 'text': 'now moving ahead with our next question.', 'start': 3035.588, 'duration': 2.901}, {'end': 3047.971, 'text': 'so the 17th question is on which of the following is called a virtual table in SQL.', 'start': 3041.206, 'duration': 6.765}, {'end': 3051.613, 'text': 'so again we have a multiple choice question.', 'start': 3047.971, 'duration': 3.642}, {'end': 3054.676, 'text': 'so the answer is view.', 'start': 3051.613, 'duration': 3.063}, {'end': 3056.237, 'text': 'you can see here.', 'start': 3054.676, 'duration': 1.561}, {'end': 3062.101, 'text': 'so view is a virtual table that has rows and columns as they are in a real table in the database.', 'start': 3056.237, 'duration': 5.864}, {'end': 3066.444, 'text': 'now we can create a view by selecting fields from one or more tables present in the database.', 'start': 3062.101, 'duration': 4.343}, {'end': 3071.411, 'text': 'next, moving to the 18th question.', 'start': 3068.089, 'duration': 3.322}, {'end': 3076.873, 'text': 'so here we want to write an sql query to fetch the list of employees with the same salary.', 'start': 3071.411, 'duration': 5.462}, {'end': 3087.118, 'text': "again i am going to use a self join here, wherein i'll join the same employees table, and this is how my query would look like.", 'start': 3076.873, 'duration': 10.245}, {'end': 3090.38, 'text': 'so i am selecting distinct employee id.', 'start': 3087.118, 'duration': 3.262}, {'end': 3095.702, 'text': 'so e dot employee id, e dot employee name, e dot salary from employees.', 'start': 3090.38, 'duration': 5.322}, {'end': 3103.174, 'text': "then i'm joining employees as E on employees E1, where E.salary equal to E1.salary,", 'start': 3095.702, 'duration': 7.472}, {'end': 3112.959, 'text': "because I want to fetch the list of employees with the same salary and I'm ensuring that the employee IDs from both the tables are different.", 'start': 3103.174, 'duration': 9.785}, {'end': 3113.479, 'text': "let's do it.", 'start': 3112.959, 'duration': 0.52}, {'end': 3131.449, 'text': "I'll give a comment here and write employees with same salary and I'll start with my query.", 'start': 3113.479, 'duration': 17.97}], 'summary': 'Using self join for reporting structure and sql query for fetching employees with same salary.', 'duration': 119.145, 'max_score': 3012.304, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3012304.jpg'}, {'end': 3103.174, 'src': 'embed', 'start': 3023.926, 'weight': 0, 'content': [{'end': 3025.406, 'text': "so Gary's manager is Smith.", 'start': 3023.926, 'duration': 1.48}, {'end': 3031.908, 'text': "Gibbs manager is Gary, Smith's manager is Latham and Jimmy's manager is Smith.", 'start': 3025.406, 'duration': 6.502}, {'end': 3035.588, 'text': 'okay, so this is how you can use self join.', 'start': 3031.908, 'duration': 3.68}, {'end': 3038.489, 'text': 'now moving ahead with our next question.', 'start': 3035.588, 'duration': 2.901}, {'end': 3047.971, 'text': 'so the 17th question is on which of the following is called a virtual table in SQL.', 'start': 3041.206, 'duration': 6.765}, {'end': 3051.613, 'text': 'so again we have a multiple choice question.', 'start': 3047.971, 'duration': 3.642}, {'end': 3054.676, 'text': 'so the answer is view.', 'start': 3051.613, 'duration': 3.063}, {'end': 3056.237, 'text': 'you can see here.', 'start': 3054.676, 'duration': 1.561}, {'end': 3062.101, 'text': 'so view is a virtual table that has rows and columns as they are in a real table in the database.', 'start': 3056.237, 'duration': 5.864}, {'end': 3066.444, 'text': 'now we can create a view by selecting fields from one or more tables present in the database.', 'start': 3062.101, 'duration': 4.343}, {'end': 3071.411, 'text': 'next, moving to the 18th question.', 'start': 3068.089, 'duration': 3.322}, {'end': 3076.873, 'text': 'so here we want to write an sql query to fetch the list of employees with the same salary.', 'start': 3071.411, 'duration': 5.462}, {'end': 3087.118, 'text': "again i am going to use a self join here, wherein i'll join the same employees table, and this is how my query would look like.", 'start': 3076.873, 'duration': 10.245}, {'end': 3090.38, 'text': 'so i am selecting distinct employee id.', 'start': 3087.118, 'duration': 3.262}, {'end': 3095.702, 'text': 'so e dot employee id, e dot employee name, e dot salary from employees.', 'start': 3090.38, 'duration': 5.322}, {'end': 3103.174, 'text': "then i'm joining employees as E on employees E1, where E.salary equal to E1.salary,", 'start': 3095.702, 'duration': 7.472}], 'summary': 'Demonstrated self join in sql to find employees with same salary and explained virtual table as view.', 'duration': 79.248, 'max_score': 3023.926, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3023926.jpg'}, {'end': 3206.214, 'src': 'embed', 'start': 3167.921, 'weight': 2, 'content': [{'end': 3193.387, 'text': "where e dot salary equal to e1 dot salary and e dot employee id is not equal to e1 dot employee id, I'll give a semicolon and let's print the results.", 'start': 3167.921, 'duration': 25.466}, {'end': 3201.271, 'text': 'you see here we have Amelia, Bela, Shane and Sara who have the same salary.', 'start': 3193.387, 'duration': 7.884}, {'end': 3203.292, 'text': 'so Amelia and Shane have the same salary of $55, 000.', 'start': 3201.271, 'duration': 2.021}, {'end': 3204.953, 'text': 'Bela and Sara have the same salary of $72, 000.', 'start': 3203.292, 'duration': 1.661}, {'end': 3206.214, 'text': 'okay, now moving to the 19th question.', 'start': 3204.953, 'duration': 1.261}], 'summary': 'Four employees have same salary: amelia & shane- $55,000, bela & sara- $72,000.', 'duration': 38.293, 'max_score': 3167.921, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3167921.jpg'}], 'start': 2743.321, 'title': 'Self join and creating views in sql', 'summary': 'Covers the concept of self join in sql, demonstrating table joining, finding managers for each employee, and creating views. additionally, it includes a practical example of virtual tables and a query fetching employees with specific salary criteria, finding 4 employees with the same salaries of $55,000 and $72,000.', 'chapters': [{'end': 2855.622, 'start': 2743.321, 'title': 'Understanding self join in sql', 'summary': 'Explains the concept of self join in sql, demonstrating how a table can be joined to itself based on specific key columns and providing an example with employee-manager relationships.', 'duration': 112.301, 'highlights': ['Self join in SQL allows a table to be joined to itself based on specific key columns, such as employee ID and manager ID, to establish relationships within the table.', 'An example of employee-manager relationships is provided, demonstrating how the concept of self join works in practice with specific employee IDs and manager IDs.', 'The example showcases how employees are linked to their respective managers through the self join process, providing clear insights into the hierarchical relationships within the table.']}, {'end': 3056.237, 'start': 2855.622, 'title': 'Using self join to find manager for each employee', 'summary': 'Demonstrates the usage of self join in sql to find the manager for each employee, showcasing a practical example and explaining the concept of a virtual table in sql through a multiple-choice question.', 'duration': 200.615, 'highlights': ['Using self join in SQL to find the manager for each employee The transcript provides a step-by-step demonstration of using self join in SQL to find the manager for each employee, illustrating the practical application of the concept.', 'Explanation of a virtual table in SQL through a multiple-choice question The transcript includes a multiple-choice question explaining that a virtual table in SQL is called a view, providing a clear and concise explanation of the concept.', 'Practical example of using self join to retrieve specific managerial relationships The example demonstrates the retrieval of specific managerial relationships, such as Gary reporting to manager ID 3 (Smith), providing practical insights into the application of self join in SQL.']}, {'end': 3206.214, 'start': 3056.237, 'title': 'Creating views and fetching employee data', 'summary': 'Covers creating views in a database and writing an sql query to fetch employees with the same salary, resulting in 4 employees with the same salary of $55,000 and $72,000.', 'duration': 149.977, 'highlights': ['The query to fetch employees with the same salary resulted in 4 employees with the same salary of $55,000 and $72,000.', 'A view in a database is a virtual table created by selecting fields from one or more tables present in the database.', "The SQL query to fetch employees with the same salary utilized a self join on the 'employees' table, ensuring that the employee IDs from both tables are different."]}], 'duration': 462.893, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ2743321.jpg', 'highlights': ['Self join in SQL allows a table to be joined to itself based on specific key columns, such as employee ID and manager ID, to establish relationships within the table.', 'Using self join in SQL to find the manager for each employee The transcript provides a step-by-step demonstration of using self join in SQL to find the manager for each employee, illustrating the practical application of the concept.', 'The query to fetch employees with the same salary resulted in 4 employees with the same salary of $55,000 and $72,000.', 'An example of employee-manager relationships is provided, demonstrating how the concept of self join works in practice with specific employee IDs and manager IDs.', 'A view in a database is a virtual table created by selecting fields from one or more tables present in the database.', 'Practical example of using self join to retrieve specific managerial relationships The example demonstrates the retrieval of specific managerial relationships, such as Gary reporting to manager ID 3 (Smith), providing practical insights into the application of self join in SQL.']}, {'end': 3786.797, 'segs': [{'end': 3264.62, 'src': 'embed', 'start': 3238.45, 'weight': 0, 'content': [{'end': 3243.372, 'text': 'It does not remove duplicate rows between the various select statements, all the rows are returned.', 'start': 3238.45, 'duration': 4.922}, {'end': 3250.749, 'text': "So let's say I want to print the employee name and department for those employees who are from the HR department.", 'start': 3244.824, 'duration': 5.925}, {'end': 3254.272, 'text': 'Now to do this, we can use the union all operator.', 'start': 3251.469, 'duration': 2.803}, {'end': 3255.273, 'text': 'You can see it here.', 'start': 3254.532, 'duration': 0.741}, {'end': 3261.177, 'text': "So I'm selecting my employee name, the department from my table employees as E.", 'start': 3255.833, 'duration': 5.344}, {'end': 3264.62, 'text': "Then I'm giving my where clause where E.department equal to HR.", 'start': 3261.177, 'duration': 3.443}], 'summary': 'Using union all operator to select employee name and department from hr department.', 'duration': 26.17, 'max_score': 3238.45, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3238450.jpg'}, {'end': 3420.642, 'src': 'embed', 'start': 3387.406, 'weight': 1, 'content': [{'end': 3394.668, 'text': 'So you can see here I have my table called num and it has just one column, which is num ID or number ID.', 'start': 3387.406, 'duration': 7.262}, {'end': 3407.296, 'text': 'We have some numbers here and from these numbers I want to add 10 if the number is 0, 20 if the number is 1.', 'start': 3395.288, 'duration': 12.008}, {'end': 3410.337, 'text': 'else I would print the number itself.', 'start': 3407.296, 'duration': 3.041}, {'end': 3416.48, 'text': "so to answer this question, we'll use the case statement in SQL.", 'start': 3410.337, 'duration': 6.143}, {'end': 3420.642, 'text': "so the case statement is SQL's way of handling if-then logic.", 'start': 3416.48, 'duration': 4.162}], 'summary': 'Using sql case statement to add 10 or 20 based on number value.', 'duration': 33.236, 'max_score': 3387.406, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3387406.jpg'}], 'start': 3213.82, 'title': 'Sql query and case statements', 'summary': 'Explains how to print one row twice using sql union all operator and demonstrates using case statements for conditional logic and calculations on a table.', 'chapters': [{'end': 3352.619, 'start': 3213.82, 'title': 'Sql query: print one row twice', 'summary': "Explains how to write an sql query to print one row twice using the union all operator, which combines the result sets of two or more select statements without removing duplicate rows, as demonstrated with a specific example of selecting employee names and departments from the 'employees' table for those in the hr department.", 'duration': 138.799, 'highlights': ["The union all operator in SQL is used to combine the result sets of two or more select statements without removing duplicate rows, as demonstrated in an example of selecting employee names and departments from the 'employees' table for HR department employees.", "To print one row twice in SQL results, the union all operator is utilized to join the result sets of two select statements for the same row, as shown in the example of selecting employee names and departments from the 'employees' table for those in the HR department.", "The example provided in the transcript demonstrates the usage of the union all operator in SQL to print one row twice in the results, specifically selecting employee names and departments from the 'employees' table for HR department employees."]}, {'end': 3786.797, 'start': 3356.842, 'title': 'Using case statements in sql', 'summary': 'Focuses on using case statements in sql to perform conditional logic and calculations on a table, demonstrating the process with examples and explaining the results obtained.', 'duration': 429.955, 'highlights': ['Using case statements in SQL to add values based on conditions The speaker demonstrates using a case statement in SQL to add 10 when the number is 0, 20 when the number is 1, and printing the number itself when none of the conditions apply.', 'Applying case statements to find the sum of positive and negative numbers The example showcases using case statements to calculate the sum of positive and negative integers separately from a table, providing insight into the process and the obtained results.', 'Demonstrating the process with a specific table and explaining the results obtained The speaker presents specific examples using tables to illustrate the implementation of case statements in SQL, providing visual representation and explanation of the resulting calculations.']}], 'duration': 572.977, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3213820.jpg', 'highlights': ["The union all operator in SQL is used to combine the result sets of two or more select statements without removing duplicate rows, as demonstrated in an example of selecting employee names and departments from the 'employees' table for HR department employees.", 'Using case statements in SQL to add values based on conditions The speaker demonstrates using a case statement in SQL to add 10 when the number is 0, 20 when the number is 1, and printing the number itself when none of the conditions apply.']}, {'end': 4249.843, 'segs': [{'end': 3871.203, 'src': 'embed', 'start': 3844.025, 'weight': 0, 'content': [{'end': 3855.731, 'text': 'now, if you have a roll number or a registration number column in the course table, that becomes the foreign key.', 'start': 3844.025, 'duration': 11.706}, {'end': 3864.535, 'text': "now the other difference is primary key don't accept or cannot accept null values, while foreign key can accept null values.", 'start': 3855.731, 'duration': 8.804}, {'end': 3871.203, 'text': 'You can have only one primary key in a table, but you can have more than one foreign key in a table.', 'start': 3865.774, 'duration': 5.429}], 'summary': 'Primary key cannot accept null values, while foreign key can. a table can have only one primary key but can have more than one foreign key.', 'duration': 27.178, 'max_score': 3844.025, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3844025.jpg'}, {'end': 3928.502, 'src': 'embed', 'start': 3896.362, 'weight': 1, 'content': [{'end': 3906.989, 'text': 'now, if you assign a column as a primary key, you create a clustered index and similarly, if you assign a column with union,', 'start': 3896.362, 'duration': 10.627}, {'end': 3910.031, 'text': 'you create non-cluster index.', 'start': 3906.989, 'duration': 3.042}, {'end': 3918.997, 'text': 'okay, now coming to the 24th question, what is a check constraint in SQL?', 'start': 3910.031, 'duration': 8.966}, {'end': 3922.659, 'text': 'so check constraints are used while creating tables.', 'start': 3918.997, 'duration': 3.662}, {'end': 3928.502, 'text': 'check constraint helps to limit or restrict the values that can be inserted into a column.', 'start': 3922.659, 'duration': 5.843}], 'summary': 'Primary key creates clustered index, union creates non-cluster index. check constraint limits values in sql.', 'duration': 32.14, 'max_score': 3896.362, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3896362.jpg'}, {'end': 4110.827, 'src': 'embed', 'start': 4070.007, 'weight': 3, 'content': [{'end': 4074.092, 'text': "so I'll write check age greater than zero.", 'start': 4070.007, 'duration': 4.085}, {'end': 4087.014, 'text': 'okay, so our dummy SQL table has one primary key constraint and we have applied two check constraints on city and each column.', 'start': 4075.965, 'duration': 11.049}, {'end': 4090.157, 'text': "let's run this, okay.", 'start': 4087.014, 'duration': 3.143}, {'end': 4092.178, 'text': 'so we have created our table successfully.', 'start': 4090.157, 'duration': 2.021}, {'end': 4093.74, 'text': 'you can see it here.', 'start': 4092.178, 'duration': 1.562}, {'end': 4099.845, 'text': "now let's insert a few records and we can see the difference.", 'start': 4093.74, 'duration': 6.105}, {'end': 4104.849, 'text': "I'll write insert into dummy underscore SQL.", 'start': 4099.845, 'duration': 5.004}, {'end': 4110.827, 'text': "I'll write values.", 'start': 4106.403, 'duration': 4.424}], 'summary': 'Sql table created with primary key and check constraints.', 'duration': 40.82, 'max_score': 4070.007, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4070007.jpg'}], 'start': 3786.797, 'title': 'Database key differences and constraints', 'summary': 'Discusses differences between primary and foreign keys, including their uniqueness, acceptance of null values, and quantity per table. it also covers the purpose of check constraints in sql and demonstrates the application of check constraints in creating a table with specific constraints.', 'chapters': [{'end': 3928.502, 'start': 3786.797, 'title': 'Database key differences and constraints', 'summary': 'Discusses the differences between primary and foreign keys, including their uniqueness, acceptance of null values, and quantity per table, as well as the distinctions between primary keys and unions, and the purpose of check constraints in sql.', 'duration': 141.705, 'highlights': ["Primary key uniquely identifies a record in a table, such as a student's roll number, and does not accept null values, while foreign key is a field in the table that is primary key to another table, can accept null values, and can have more than one per table.", 'The difference between primary key and union lies in the uniqueness and acceptance of null values, as well as the creation of clustered and non-clustered indexes, respectively.', 'Check constraints in SQL are utilized during table creation to restrict the values that can be inserted into a column.']}, {'end': 4249.843, 'start': 3928.502, 'title': 'Using check constraints in sql', 'summary': 'Explains how to use check constraints in sql to enforce data validation rules, such as ensuring age and salary are always greater than zero, and demonstrates the application of check constraints in creating a table with specific constraints and inserting records that adhere to or violate the constraints.', 'duration': 321.341, 'highlights': ['The chapter explains how to use check constraints in SQL to enforce data validation rules. It provides a general overview of the purpose of check constraints in SQL.', 'Ensuring age and salary are always greater than zero. The example illustrates the use of check constraints to enforce specific conditions, such as ensuring age and salary are greater than zero.', 'Demonstrates the application of check constraints in creating a table with specific constraints and inserting records that adhere to or violate the constraints. The transcript demonstrates the practical application of check constraints by creating a table with specific constraints and inserting records that both adhere to and violate the constraints, showcasing the impact of check constraints on data validation.']}], 'duration': 463.046, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ3786797.jpg', 'highlights': ['Primary key uniquely identifies a record and does not accept null values, while foreign key is a field in the table that is primary key to another table, can accept null values, and can have more than one per table.', 'Check constraints in SQL are utilized during table creation to restrict the values that can be inserted into a column.', 'The chapter explains how to use check constraints in SQL to enforce data validation rules.', 'Demonstrates the application of check constraints in creating a table with specific constraints and inserting records that adhere to or violate the constraints.']}, {'end': 4934.089, 'segs': [{'end': 4316.228, 'src': 'heatmap', 'start': 4250.543, 'weight': 0, 'content': [{'end': 4258.307, 'text': 'So given two tables A and B, you want to write a query to fetch values in table B that are not present in table A.', 'start': 4250.543, 'duration': 7.764}, {'end': 4266.505, 'text': 'Now to answer this question, you can use the left join.', 'start': 4262.822, 'duration': 3.683}, {'end': 4275.871, 'text': 'So what left join does is it returns all the rows from the left table and only matching rows from the right table.', 'start': 4267.505, 'duration': 8.366}, {'end': 4284.938, 'text': "And I've used my condition as a.id which is in the right table should be null.", 'start': 4277.292, 'duration': 7.646}, {'end': 4289.521, 'text': "Let's write this query on the workbench.", 'start': 4286.619, 'duration': 2.902}, {'end': 4300.494, 'text': "okay, so i'll give my comment using left join.", 'start': 4291.306, 'duration': 9.188}, {'end': 4304.758, 'text': 'first of all, let me display my tables.', 'start': 4300.494, 'duration': 4.264}, {'end': 4313.565, 'text': "i'll write select star from a, where on this i have the ids.", 'start': 4304.758, 'duration': 8.807}, {'end': 4316.228, 'text': 'there are total five ids in table a.', 'start': 4313.565, 'duration': 2.663}], 'summary': 'Using left join, fetch values in table b not present in table a, with 5 ids in table a.', 'duration': 34.395, 'max_score': 4250.543, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4250543.jpg'}, {'end': 4430.944, 'src': 'heatmap', 'start': 4329.788, 'weight': 1, 'content': [{'end': 4334.471, 'text': 'so i want to return those non-matching values that are not present in table a.', 'start': 4329.788, 'duration': 4.683}, {'end': 4342.877, 'text': "so for that i'll write select id from table b.", 'start': 4334.471, 'duration': 8.406}, {'end': 4348.621, 'text': "i'll use left join a using my id column.", 'start': 4342.877, 'duration': 5.744}, {'end': 4355.54, 'text': 'This is another way to give the condition, or you can use the on operator.', 'start': 4349.656, 'duration': 5.884}, {'end': 4364.806, 'text': "Then I'll give where a.id is null.", 'start': 4356.981, 'duration': 7.825}, {'end': 4368.348, 'text': "Let's run and see the results.", 'start': 4366.787, 'duration': 1.561}, {'end': 4370.329, 'text': 'There you go.', 'start': 4369.949, 'duration': 0.38}, {'end': 4375.573, 'text': 'So 150, 275 are not present in table A but are present in table B.', 'start': 4370.95, 'duration': 4.623}, {'end': 4382.24, 'text': 'Okay Now moving ahead.', 'start': 4375.573, 'duration': 6.667}, {'end': 4388.844, 'text': "So now we'll look at the last set of five questions.", 'start': 4386.362, 'duration': 2.482}, {'end': 4394.768, 'text': 'So these questions are based on the popular Northwind sample database provided by Microsoft.', 'start': 4389.645, 'duration': 5.123}, {'end': 4397.43, 'text': 'There are several tables in this database.', 'start': 4395.549, 'duration': 1.881}, {'end': 4402.634, 'text': 'So we have an order table, customers, employee, products, region table and others.', 'start': 4397.87, 'duration': 4.764}, {'end': 4407.337, 'text': "We'll use our Microsoft SQL Server to solve the five questions.", 'start': 4403.314, 'duration': 4.023}, {'end': 4413.4, 'text': 'So my next question, which is based on Microsoft SQL Server.', 'start': 4409.639, 'duration': 3.761}, {'end': 4420.562, 'text': "The question is, using the Northwind database, find the customers who don't have any orders.", 'start': 4414.16, 'duration': 6.402}, {'end': 4425.723, 'text': 'Okay, so I am on my Microsoft SQL Server Management Studio.', 'start': 4421.662, 'duration': 4.061}, {'end': 4427.443, 'text': 'So this is how the interface looks like.', 'start': 4425.843, 'duration': 1.6}, {'end': 4430.944, 'text': 'And here you can see I have a list of databases.', 'start': 4428.184, 'duration': 2.76}], 'summary': 'Using left join, 150, 275 not in a but in b. solving questions based on northwind database using microsoft sql server.', 'duration': 45.785, 'max_score': 4329.788, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4329788.jpg'}, {'end': 4793.541, 'src': 'embed', 'start': 4763.45, 'weight': 2, 'content': [{'end': 4772.691, 'text': 'so these are the two customer IDs which are present in the customers table and are not present in the orders table,', 'start': 4763.45, 'duration': 9.241}, {'end': 4777.933, 'text': "meaning that they don't have any orders in their name.", 'start': 4772.691, 'duration': 5.242}, {'end': 4788.156, 'text': 'now there is another way to do it, that is, to use the not exist operator, wherein you can write another subquery to get the same result.', 'start': 4777.933, 'duration': 10.223}, {'end': 4789.217, 'text': "We'll leave that to you.", 'start': 4788.316, 'duration': 0.901}, {'end': 4793.541, 'text': 'You can try this out using the not exist operator as well.', 'start': 4789.757, 'duration': 3.784}], 'summary': 'Two customer ids from customers table not in orders table, no orders in their name. not exist operator can be used for same result.', 'duration': 30.091, 'max_score': 4763.45, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4763450.jpg'}, {'end': 4858.132, 'src': 'embed', 'start': 4821.673, 'weight': 3, 'content': [{'end': 4827.116, 'text': 'okay, so let me first give my comment for the 27th question.', 'start': 4821.673, 'duration': 5.443}, {'end': 4830.458, 'text': 'so the question is to find all the month end orders.', 'start': 4827.116, 'duration': 3.342}, {'end': 4836.342, 'text': "so i'll just write month end orders.", 'start': 4830.458, 'duration': 5.884}, {'end': 4845.927, 'text': 'okay, now to get the month end orders we are going to use a built-in eo month function which stands for end of month.', 'start': 4836.342, 'duration': 9.585}, {'end': 4848.208, 'text': "so let's start with the query.", 'start': 4845.927, 'duration': 2.281}, {'end': 4849.169, 'text': "i'll write select.", 'start': 4848.208, 'duration': 0.961}, {'end': 4858.132, 'text': 'we are going to select the employee ID comma.', 'start': 4851.906, 'duration': 6.226}], 'summary': 'Using the built-in function eo month to find month end orders.', 'duration': 36.459, 'max_score': 4821.673, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4821673.jpg'}], 'start': 4250.543, 'title': 'Left join to fetch non-matching values', 'summary': 'Demonstrates the usage of a left join to fetch values in table b that are not present in table a, resulting in the identification of 150 and 275 as values in table b that are not present in table a.', 'chapters': [{'end': 4375.573, 'start': 4250.543, 'title': 'Left join to fetch non-matching values', 'summary': 'Demonstrates the usage of a left join to fetch values in table b that are not present in table a, resulting in the identification of 150 and 275 as values in table b that are not present in table a.', 'duration': 125.03, 'highlights': ['Left join is used to fetch non-matching values in table B that are not present in table A.', 'The query returns 150 and 275 as values in table B that are not present in table A.', 'The left join returns all the rows from the left table and only matching rows from the right table.']}, {'end': 4934.089, 'start': 4375.573, 'title': 'Microsoft sql server: northwind database queries', 'summary': 'Covers using microsoft sql server to solve queries based on the northwind sample database, including finding customers with no orders and displaying all month-end orders, revealing 2 customers without orders and 26 month-end orders.', 'duration': 558.516, 'highlights': ['Two customers are found in the customers table but not in the orders table, indicating they have no orders. After analyzing the data, it is revealed that there are 2 customers who do not have any orders associated with them.', 'A total of 26 month-end orders are displayed using the end of month function in the query. The query successfully identifies and displays 26 month-end orders using the end of month function.', 'The Northwind database consists of several tables including orders, customers, employees, and products. The Northwind sample database provided by Microsoft contains various tables such as orders, customers, employees, and products.']}], 'duration': 683.546, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4250543.jpg', 'highlights': ['Left join is used to fetch non-matching values in table B that are not present in table A.', 'The query returns 150 and 275 as values in table B that are not present in table A.', 'Two customers are found in the customers table but not in the orders table, indicating they have no orders.', 'A total of 26 month-end orders are displayed using the end of month function in the query.', 'The left join returns all the rows from the left table and only matching rows from the right table.']}, {'end': 5675.425, 'segs': [{'end': 4961.814, 'src': 'embed', 'start': 4934.089, 'weight': 0, 'content': [{'end': 4946.654, 'text': 'you see this is 28th of feb, then we have 31st of july, we have 30th of june, so on and so forth.', 'start': 4934.089, 'duration': 12.565}, {'end': 4953.436, 'text': 'so these are the list of all the orders that were placed on the last day of the month.', 'start': 4946.654, 'duration': 6.782}, {'end': 4961.814, 'text': 'coming to the 28th question, We want to find the top five countries with the highest freight charges in the year 1997..', 'start': 4953.436, 'duration': 8.378}], 'summary': 'Identify top 5 countries with highest freight charges in 1997.', 'duration': 27.725, 'max_score': 4934.089, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4934089.jpg'}, {'end': 5072.506, 'src': 'embed', 'start': 5034.57, 'weight': 1, 'content': [{'end': 5044.305, 'text': "so I'm going to find out the ship country and then I'll calculate the average freight prices.", 'start': 5034.57, 'duration': 9.735}, {'end': 5053.688, 'text': "so I'm going to use the average inbuilt function on top of my column, that is freight.", 'start': 5044.305, 'duration': 9.383}, {'end': 5062.39, 'text': "I'll give an alias as, let's say, average freight.", 'start': 5053.688, 'duration': 8.702}, {'end': 5064.23, 'text': 'there should be no space.', 'start': 5062.39, 'duration': 1.84}, {'end': 5072.506, 'text': "I'll give a underscore, I'll write from my table, that is, orders.", 'start': 5064.23, 'duration': 8.276}], 'summary': "Calculate average freight prices by using the inbuilt function on the 'freight' column from the 'orders' table.", 'duration': 37.936, 'max_score': 5034.57, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ5034570.jpg'}, {'end': 5167.442, 'src': 'embed', 'start': 5137.019, 'weight': 2, 'content': [{'end': 5145.961, 'text': 'So both these tables we are going to use to find a solution to this problem that is to display the total number of products in each category.', 'start': 5137.019, 'duration': 8.942}, {'end': 5151.943, 'text': 'First let me give my comment to display the total number of products in each category.', 'start': 5147.622, 'duration': 4.321}, {'end': 5159.738, 'text': "I'll just write print total products okay.", 'start': 5151.943, 'duration': 7.795}, {'end': 5167.442, 'text': 'so to solve this problem we are going to use our two tables.', 'start': 5159.738, 'duration': 7.704}], 'summary': 'Using two tables to display total products in each category.', 'duration': 30.423, 'max_score': 5137.019, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ5137019.jpg'}, {'end': 5366.2, 'src': 'embed', 'start': 5336.647, 'weight': 4, 'content': [{'end': 5346.31, 'text': 'you have the different category names and on the right you have the total number of products that belong to each of the categories.', 'start': 5336.647, 'duration': 9.663}, {'end': 5353.954, 'text': 'all right now, moving on to the final question.', 'start': 5348.311, 'duration': 5.643}, {'end': 5363.839, 'text': 'so the last question is to use the northwind database and using this database we want to find the list of late orders for all the employees.', 'start': 5353.954, 'duration': 9.885}, {'end': 5366.2, 'text': 'so this is a real world problem that often occurs.', 'start': 5363.839, 'duration': 2.361}], 'summary': 'Find late orders in the northwind database for all employees.', 'duration': 29.553, 'max_score': 5336.647, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ5336647.jpg'}, {'end': 5635.711, 'src': 'embed', 'start': 5600.827, 'weight': 3, 'content': [{'end': 5610.979, 'text': 'so if you see here, employee ID 4 has the highest number of late orders, followed by employee ID 3 and 8, as well as 9, who have 5 late orders.', 'start': 5600.827, 'duration': 10.152}, {'end': 5614.763, 'text': 'then we have the employee ID 2, which has 4 late orders.', 'start': 5610.979, 'duration': 3.784}, {'end': 5617.226, 'text': 'even employee ID 7 has 4 late orders.', 'start': 5614.763, 'duration': 2.463}, {'end': 5620.611, 'text': 'employee ID 5, that is, Steven, has only 1 late orders.', 'start': 5617.226, 'duration': 3.385}, {'end': 5635.711, 'text': 'so we are done with our demo section and we have covered all our 30 questions, out of which 25 questions were based and used on mysql server.', 'start': 5623.467, 'duration': 12.244}], 'summary': 'Employee id 4 has the highest late orders (6), followed by employees 3, 8, and 9 with 5 late orders, while steven (id 5) has only 1 late order.', 'duration': 34.884, 'max_score': 5600.827, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ5600827.jpg'}], 'start': 4934.089, 'title': 'Freight charges and late orders', 'summary': 'Discusses the top 5 countries with the highest freight charges in 1997, using sql queries to calculate and display results, and demonstrates finding late orders for employees in the northwind database, with employee 4 having the highest number of late orders (5) followed by employees 3, 8, and 9 with 5 late orders each, and employee 2 and 7 with 4 late orders each.', 'chapters': [{'end': 5336.647, 'start': 4934.089, 'title': 'Top 5 countries with highest freight charges', 'summary': 'Discusses finding the top five countries with the highest freight charges in the year 1997, using sql queries to calculate and display the results for both freight charges and total number of products in each category.', 'duration': 402.558, 'highlights': ['Finding the top five countries with the highest freight charges in the year 1997 The speaker demonstrates using SQL queries to calculate and display the top five countries with the highest freight charges in the year 1997, with the results showing Austria, Switzerland, Sweden, Canada, and Ireland.', "Calculating average freight prices using SQL queries The speaker explains the process of calculating average freight prices using SQL queries and demonstrates the use of the 'average' inbuilt function, 'select' statement, 'group by' clause, and 'order by' clause to sort the average freight prices in descending order.", "Displaying the total number of products in each category using SQL queries The speaker demonstrates using SQL queries to display the total number of products in each category, utilizing the 'count' function, 'select' statement, 'inner join', 'group by' clause, and 'order by' clause to present the total number of products in each category."]}, {'end': 5675.425, 'start': 5336.647, 'title': 'Finding late orders in northwind database', 'summary': 'Demonstrates how to use the northwind database to find late orders for employees by joining the orders and employees tables, resulting in employee 4 having the highest number of late orders (5) followed by employees 3, 8, and 9 with 5 late orders each, and employee 2 and 7 with 4 late orders each.', 'duration': 338.778, 'highlights': ['By joining the orders and employees tables in the Northwind database, Employee 4 has the highest number of late orders (5) followed by Employees 3, 8, and 9 with 5 late orders each, and Employee 2 and 7 with 4 late orders each.', 'The demonstration covers 30 questions, out of which 25 were based on MySQL server and the last 5 questions used the Northwind database, showcasing the use of multiple tables to solve interesting problems.', 'The video tutorial concludes with an invitation to subscribe to the Simply Learn YouTube channel and the offer to request the SQL code file used in the demonstration by leaving email IDs in the comment section.']}], 'duration': 741.336, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/L-URbfgxBMQ/pics/L-URbfgxBMQ4934089.jpg', 'highlights': ['Finding the top five countries with the highest freight charges in the year 1997', 'Calculating average freight prices using SQL queries', 'Displaying the total number of products in each category using SQL queries', 'Employee 4 has the highest number of late orders (5)', 'Joining the orders and employees tables in the Northwind database']}], 'highlights': ['Covers 30 sql interview questions, sql functions, string manipulation, self join, case statements, database key differences, left join, and practical examples of fetching non-matching values and analyzing freight charges and late orders in sql.', 'Using distinct keyword and inbuilt length function to fetch unique departments and their lengths.', 'Demonstration of calculating the total number of days between specific dates using DATEDIF function.', 'Demonstration of using subqueries in SQL to find the employee with the third highest salary from a table of three employees who joined after May 2005 and before April 2010.', 'Self join in SQL allows a table to be joined to itself based on specific key columns, such as employee ID and manager ID, to establish relationships within the table.', "The union all operator in SQL is used to combine the result sets of two or more select statements without removing duplicate rows, as demonstrated in an example of selecting employee names and departments from the 'employees' table for HR department employees.", 'Primary key uniquely identifies a record and does not accept null values, while foreign key is a field in the table that is primary key to another table, can accept null values, and can have more than one per table.', 'Left join is used to fetch non-matching values in table B that are not present in table A.', 'Finding the top five countries with the highest freight charges in the year 1997']}