title
SQL Full Course In 10 Hours | SQL Tutorial | Complete SQL Course For Beginners | Edureka

description
🔥 𝐄𝐝𝐮𝐫𝐞𝐤𝐚 𝐒𝐐𝐋 𝐂𝐨𝐮𝐫𝐬𝐞 𝐓𝐫𝐚𝐢𝐧𝐢𝐧𝐠 (Use Code "𝐘𝐎𝐔𝐓𝐔𝐁𝐄𝟐𝟎"): https://www.edureka.co/microsoft-sql-server-certification-training This Edureka SQL Full Course video will cover all the topics of Structured Query Language (SQL) starting from scratch. This SQL tutorial for beginners is great for beginners who want to learn SQL and for professionals who want to brush up on their SQL skills. The following topics are covered in this SQL Full Course Tutorial: 00:00:00 Introduction to SQL Full Course 00:02:23 Agenda 00:02:35 What is SQL 00:16:32 Data & Database 00:23:58 Basic SQL Queries 00:50:51 Normalization in SQL 01:01:44 Triggers in SQL 01:11:23 Advantages & Disadvantages of Triggers 01:12:33 Joins in SQL 01:33:28 Functions in SQL 02:18:19 Stored procedure 02:43:02 User-Defined Functions 03:02:13 SQL vs MySQL 03:07:36 SQL vs NoSQL 03:39:41 SQL Interview Question & Answers 04:31:17 SQL For Data Science 05:06:27 PostgreSQL 05:17:00 SQL Command Categories 05:24:31 ER Diagram 05:43:34 Keys in Database 05:46:53 Constraints in Database 05:53:44 Normalization 05:59:26 DML Commands 06:18:36 Operators in SQL 06:47:54 Joins in SQL 06:51:52 Views in SQL 07:06:19 DCL Commands 07:15:23 SQL Server 07:21:28 Features of SQL Server 07:36:50 DDL Commands in SQL 08:30:07 Operators 08:55:50 Exception handling in SQL 08:59:55 SQL Server Interview Question & Answer 🔴 Subscribe to our channel to get video updates. Hit the subscribe button above: https://goo.gl/6ohpTV 🔴 𝐄𝐝𝐮𝐫𝐞𝐤𝐚 𝐎𝐧𝐥𝐢𝐧𝐞 𝐓𝐫𝐚𝐢𝐧𝐢𝐧𝐠 𝐚𝐧𝐝 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧𝐬 🔵 DevOps Online Training: http://bit.ly/3VkBRUT 🌕 AWS Online Training: http://bit.ly/3ADYwDY 🔵 React Online Training: http://bit.ly/3Vc4yDw 🌕 Tableau Online Training: http://bit.ly/3guTe6J 🔵 Power BI Online Training: http://bit.ly/3VntjMY 🌕 Selenium Online Training: http://bit.ly/3EVDtis 🔵 PMP Online Training: http://bit.ly/3XugO44 🌕 Salesforce Online Training: http://bit.ly/3OsAXDH 🔵 Cybersecurity Online Training: http://bit.ly/3tXgw8t 🌕 Java Online Training: http://bit.ly/3tRxghg 🔵 Big Data Online Training: http://bit.ly/3EvUqP5 🌕 RPA Online Training: http://bit.ly/3GFHKYB 🔵 Python Online Training: http://bit.ly/3Oubt8M 🌕 Azure Online Training: http://bit.ly/3i4P85F 🔵 GCP Online Training: http://bit.ly/3VkCzS3 🌕 Microservices Online Training: http://bit.ly/3gxYqqv 🔵 Data Science Online Training: http://bit.ly/3V3nLrc 🌕 CEHv12 Online Training: http://bit.ly/3Vhq8Hj 🔵 Angular Online Training: http://bit.ly/3EYcCTe 🔴 𝐄𝐝𝐮𝐫𝐞𝐤𝐚 𝐑𝐨𝐥𝐞-𝐁𝐚𝐬𝐞𝐝 𝐂𝐨𝐮𝐫𝐬𝐞𝐬 🔵 DevOps Engineer Masters Program: http://bit.ly/3Oud9PC 🌕 Cloud Architect Masters Program: http://bit.ly/3OvueZy 🔵 Data Scientist Masters Program: http://bit.ly/3tUAOiT 🌕 Big Data Architect Masters Program: http://bit.ly/3tTWT0V 🔵 Machine Learning Engineer Masters Program: http://bit.ly/3AEq4c4 🌕 Business Intelligence Masters Program: http://bit.ly/3UZPqJz 🔵 Python Developer Masters Program: http://bit.ly/3EV6kDv 🌕 RPA Developer Masters Program: http://bit.ly/3OteYfP 🔵 Web Development Masters Program: http://bit.ly/3U9R5va 🌕 Computer Science Bootcamp Program : http://bit.ly/3UZxPBy 🔵 Cyber Security Masters Program: http://bit.ly/3U25rNR 🌕 Full Stack Developer Masters Program : http://bit.ly/3tWCE2S 🔵 Automation Testing Engineer Masters Program : http://bit.ly/3AGXg2J 🌕 Python Developer Masters Program : https://bit.ly/3EV6kDv 🔵 Azure Cloud Engineer Masters Program: http://bit.ly/3AEBHzH 🔴 𝐄𝐝𝐮𝐫𝐞𝐤𝐚 𝐔𝐧𝐢𝐯𝐞𝐫𝐬𝐢𝐭𝐲 𝐏𝐫𝐨𝐠𝐫𝐚𝐦𝐬 🌕 Professional Certificate Program in DevOps with Purdue University: https://bit.ly/3Ov52lT 🔵 Advanced Certificate Program in Data Science with E&ICT Academy, IIT Guwahati: http://bit.ly/3V7ffrh 🌕 Artificial and Machine Learning PGD with E&ICT Academy NIT Warangal: http://bit.ly/3OuZ3xs 📢📢 𝐓𝐨𝐩 𝟏𝟎 𝐓𝐫𝐞𝐧𝐝𝐢𝐧𝐠 𝐓𝐞𝐜𝐡𝐧𝐨𝐥𝐨𝐠𝐢𝐞𝐬 𝐭𝐨 𝐋𝐞𝐚𝐫𝐧 𝐢𝐧 2023 𝐒𝐞𝐫𝐢𝐞𝐬 📢📢 ⏩ NEW Top 10 Technologies To Learn In 2023 - https://youtu.be/udD_GQVDt5g 📌𝐓𝐞𝐥𝐞𝐠𝐫𝐚𝐦: https://t.me/edurekaupdates 📌𝐓𝐰𝐢𝐭𝐭𝐞𝐫: https://twitter.com/edurekain 📌𝐋𝐢𝐧𝐤𝐞𝐝𝐈𝐧: https://www.linkedin.com/company/edureka 📌𝐈𝐧𝐬𝐭𝐚𝐠𝐫𝐚𝐦: https://www.instagram.com/edureka_learning/ 📌𝐅𝐚𝐜𝐞𝐛𝐨𝐨𝐤: https://www.facebook.com/edurekaIN/ 📌𝐒𝐥𝐢𝐝𝐞𝐒𝐡𝐚𝐫𝐞: https://www.slideshare.net/EdurekaIN 📌𝐂𝐚𝐬𝐭𝐛𝐨𝐱: https://castbox.fm/networks/505?country=IN 📌𝐌𝐞𝐞𝐭𝐮𝐩: https://www.meetup.com/edureka/ 📌𝐂𝐨𝐦𝐦𝐮𝐧𝐢𝐭𝐲: https://www.edureka.co/community/ Got a question on the topic? Please share it in the comment section below and our experts will answer it for you. Please write back to us at sales@edureka.co or call us at IND: 9606058406 / US: 18338555775 (toll-free) for more information.

detail
{'title': 'SQL Full Course In 10 Hours | SQL Tutorial | Complete SQL Course For Beginners | Edureka', 'heatmap': [{'end': 2522.122, 'start': 1079.002, 'weight': 1}, {'end': 3600.368, 'start': 3229.46, 'weight': 0.724}], 'summary': 'Tutorial covers the importance and demand of sql in the job market, its evolution and use in various industries, fundamentals, triggers, joins, functions, stored procedures, database management systems, data science applications, database privileges, sql server features, installation, architecture, ddl commands, constraints, operators, dcl commands, administration, functions, q&a, and practical examples, providing comprehensive knowledge for beginners.', 'chapters': [{'end': 226.025, 'segs': [{'end': 67.719, 'src': 'embed', 'start': 38.018, 'weight': 4, 'content': [{'end': 42.579, 'text': 'making it current most-in-demand technical talent for potential employees.', 'start': 38.018, 'duration': 4.561}, {'end': 45.341, 'text': 'Hello everyone and welcome to this session.', 'start': 43.419, 'duration': 1.922}, {'end': 49.224, 'text': 'You are currently watching an Edureka SQL full course video.', 'start': 45.761, 'duration': 3.463}, {'end': 59.393, 'text': 'By the end of this video, you will have a thorough understanding of SQL all the way from theory to practical application that will help you master it.', 'start': 49.905, 'duration': 9.488}, {'end': 67.719, 'text': "If you love watching videos like these, then subscribe to Edureka's YouTube channel and click the bell button to never miss out any updates from us.", 'start': 60.234, 'duration': 7.485}], 'summary': 'Edureka sql full course video provides thorough understanding of sql for potential employees.', 'duration': 29.701, 'max_score': 38.018, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9838018.jpg'}, {'end': 164.255, 'src': 'embed', 'start': 129.485, 'weight': 0, 'content': [{'end': 138.653, 'text': 'Now, after all this, we will head over to how SQL is required in data science, followed by PostgreSQL and SQL Server.', 'start': 129.485, 'duration': 9.168}, {'end': 143.696, 'text': "At last, we'll look at some SQL Server interview questions with answers.", 'start': 139.473, 'duration': 4.223}, {'end': 148.12, 'text': "So stick till the end now, let's start with our first topic.", 'start': 144.577, 'duration': 3.543}, {'end': 164.255, 'text': "That is what is SQL? The first topic is traditional file system in today's computing world managing data is a huge task for our convenience.", 'start': 148.281, 'duration': 15.974}], 'summary': 'Sql is required in data science, followed by postgresql and sql server. includes sql server interview questions and answers.', 'duration': 34.77, 'max_score': 129.485, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY98129485.jpg'}], 'start': 7.093, 'title': "Sql's importance and high demand", 'summary': "Emphasizes sql's high demand in the job market, with 42.7% of over 32,000 data job listings requiring sql skills. it also covers the significance of sql in data science, addressing challenges in traditional file systems and data redundancy management.", 'chapters': [{'end': 128.824, 'start': 7.093, 'title': 'Sql: the most in-demand technical skill', 'summary': 'Highlights the high demand for sql as a technical skill, with 42.7% of over 32,000 data job listings requiring sql, and promises a thorough understanding of sql from theory to practical application in an edureka full course video.', 'duration': 121.731, 'highlights': ['SQL is listed in 42.7% of over 32,000 data job listings, making it the current most-in-demand technical talent.', 'By the end of this video, viewers will have a thorough understanding of SQL, all the way from theory to practical application.', 'The agenda includes SQL basics, operators, normalization, triggers, joins, functions, and a comparison between SQL and MySQL as well as SQL and NoSQL.']}, {'end': 226.025, 'start': 129.485, 'title': 'Importance of sql in data science', 'summary': 'Explains the importance of sql in data science, covering traditional file system challenges, and the problems of data redundancy in managing large amounts of data.', 'duration': 96.54, 'highlights': ["Traditional file system in today's computing world is a huge task for managing data, and it becomes ineffective when it comes to storing enormous amounts of data.", 'Data redundancy causes duplicated data, leading to an increase in storage size, failure of security features, and economic impact on the user.', 'File system controls how data is stored and retrieved, but it is highly vulnerable and fails when it comes to storing large amounts of data.']}], 'duration': 218.932, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY987093.jpg', 'highlights': ['SQL is listed in 42.7% of over 32,000 data job listings, making it the current most-in-demand technical talent.', 'By the end of this video, viewers will have a thorough understanding of SQL, all the way from theory to practical application.', 'The agenda includes SQL basics, operators, normalization, triggers, joins, functions, and a comparison between SQL and MySQL as well as SQL and NoSQL.', "Traditional file system in today's computing world is a huge task for managing data, and it becomes ineffective when it comes to storing enormous amounts of data.", 'Data redundancy causes duplicated data, leading to an increase in storage size, failure of security features, and economic impact on the user.', 'File system controls how data is stored and retrieved, but it is highly vulnerable and fails when it comes to storing large amounts of data.']}, {'end': 1889.609, 'segs': [{'end': 478.567, 'src': 'embed', 'start': 449.884, 'weight': 4, 'content': [{'end': 452.405, 'text': 'It allows you to control access to the database.', 'start': 449.884, 'duration': 2.521}, {'end': 456.092, 'text': 'Grant or revoke other DCL commands.', 'start': 453.03, 'duration': 3.062}, {'end': 465.158, 'text': 'Grant gives user access privileges to the database, while revoke withdraws user access privileges given to the user with the help of Grant command.', 'start': 456.092, 'duration': 9.066}, {'end': 470.441, 'text': 'So the next command is DML that is data manipulation language.', 'start': 465.838, 'duration': 4.603}, {'end': 473.663, 'text': 'It allows you to access and manipulate data.', 'start': 470.942, 'duration': 2.721}, {'end': 478.567, 'text': 'It helps you to insert update delete and retrieve data from the database.', 'start': 474.204, 'duration': 4.363}], 'summary': 'Grant and revoke commands control database access, while dml manipulates data.', 'duration': 28.683, 'max_score': 449.884, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY98449884.jpg'}, {'end': 957.657, 'src': 'embed', 'start': 932.371, 'weight': 2, 'content': [{'end': 939.557, 'text': 'There is no room for ambiguity when it comes to writing a query the standards has to be followed SQL is easy to learn.', 'start': 932.371, 'duration': 7.186}, {'end': 948.405, 'text': 'Yes, SQL is a language that is used to work with the database since SQL has a large user base as well as well-defined standards for a beginner.', 'start': 939.997, 'duration': 8.408}, {'end': 949.766, 'text': 'It is really easy to learn.', 'start': 948.505, 'duration': 1.261}, {'end': 952.092, 'text': 'Next one is in SQL.', 'start': 950.47, 'duration': 1.622}, {'end': 953.713, 'text': 'We can create multiple views.', 'start': 952.152, 'duration': 1.561}, {'end': 957.657, 'text': 'This is one of the unique and early feature that SQL came up with.', 'start': 954.134, 'duration': 3.523}], 'summary': 'Sql is easy to learn, with a large user base and well-defined standards. it allows creating multiple views, a unique and early feature.', 'duration': 25.286, 'max_score': 932.371, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY98932371.jpg'}, {'end': 1066.696, 'src': 'embed', 'start': 1036.877, 'weight': 3, 'content': [{'end': 1043.862, 'text': 'a database is an organized collection of data, with the data is stored and accessed electronically from computer system.', 'start': 1036.877, 'duration': 6.985}, {'end': 1045.363, 'text': 'to make you understand what it means.', 'start': 1043.862, 'duration': 1.501}, {'end': 1050.226, 'text': 'Let me use an analogy to explain the concept of database consider a library.', 'start': 1045.603, 'duration': 4.623}, {'end': 1058.552, 'text': 'Usually the library contains a huge collection of books of different genre here library is a database and these books are the data.', 'start': 1050.887, 'duration': 7.665}, {'end': 1066.696, 'text': 'In the image we can see a person performing an action on the data and those action can be like create store update Etc.', 'start': 1059.289, 'duration': 7.407}], 'summary': 'A database is like a library, storing and accessing data electronically from a computer system.', 'duration': 29.819, 'max_score': 1036.877, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY981036877.jpg'}, {'end': 1436.607, 'src': 'embed', 'start': 1363.54, 'weight': 0, 'content': [{'end': 1368.043, 'text': 'We need an active database since we had deleted the previously create database.', 'start': 1363.54, 'duration': 4.503}, {'end': 1370.104, 'text': 'Let me create the database once again.', 'start': 1368.343, 'duration': 1.761}, {'end': 1376.248, 'text': 'So we can see that we have a database with the name edureka.', 'start': 1373.426, 'duration': 2.822}, {'end': 1386.815, 'text': 'So now let me create the table and the syntax for creating a table is Create table is a keyword used to create a table followed by that.', 'start': 1376.668, 'duration': 10.147}, {'end': 1388.316, 'text': 'We have mentioned the table name.', 'start': 1386.975, 'duration': 1.341}, {'end': 1391.078, 'text': 'We need to make sure that the table name is unique.', 'start': 1388.917, 'duration': 2.161}, {'end': 1396.371, 'text': 'Keep in mind that in a database there cannot be two tables with the same name inside the table.', 'start': 1391.65, 'duration': 4.721}, {'end': 1401.073, 'text': 'We have declared the columns along with the data type and we have to end the query with a semicolon.', 'start': 1396.591, 'duration': 4.482}, {'end': 1402.833, 'text': 'So let me execute this query.', 'start': 1401.493, 'duration': 1.34}, {'end': 1408.034, 'text': 'So we can see that the table EMP has been created.', 'start': 1404.854, 'duration': 3.18}, {'end': 1421.038, 'text': "So the next query I'm going to discuss is how to delete a table and the syntax for this is We shall use drop table keywords to delete a table.", 'start': 1408.975, 'duration': 12.063}, {'end': 1424.06, 'text': 'and followed by that we have to specify the table name.', 'start': 1421.519, 'duration': 2.541}, {'end': 1433.946, 'text': 'So let me select this entire query and we can see that the table with the name EMP has been deleted with all the basic introduction understood so far.', 'start': 1424.461, 'duration': 9.485}, {'end': 1436.607, 'text': "Let's now understand some SQL basic queries.", 'start': 1434.306, 'duration': 2.301}], 'summary': 'Recreated database, created table emp, and deleted table emp.', 'duration': 73.067, 'max_score': 1363.54, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY981363540.jpg'}, {'end': 1629.95, 'src': 'embed', 'start': 1605.271, 'weight': 5, 'content': [{'end': 1614.095, 'text': 'and the syntax for this is select columns from table where condition 1 or condition 2 is true, to understand the example.', 'start': 1605.271, 'duration': 8.824}, {'end': 1615.535, 'text': "Let's dive into the demo.", 'start': 1614.415, 'duration': 1.12}, {'end': 1621.257, 'text': 'I want to display the details of a student who has the first name as Bovee or the last name as Kumar.', 'start': 1616.075, 'duration': 5.182}, {'end': 1624.888, 'text': "To achieve this I'm using our operator here.", 'start': 1621.906, 'duration': 2.982}, {'end': 1626.969, 'text': 'Any one of the condition has to be true.', 'start': 1625.128, 'duration': 1.841}, {'end': 1629.95, 'text': 'So this is the query to execute a scenario.', 'start': 1627.689, 'duration': 2.261}], 'summary': 'Using sql, select student details with first name bovee or last name kumar.', 'duration': 24.679, 'max_score': 1605.271, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY981605271.jpg'}, {'end': 1873.011, 'src': 'embed', 'start': 1844.128, 'weight': 7, 'content': [{'end': 1853.235, 'text': 'Minimum function will return the smallest value of the selected column and the syntax is select min of column from table where condition.', 'start': 1844.128, 'duration': 9.107}, {'end': 1855.357, 'text': 'So let me execute this example.', 'start': 1853.776, 'duration': 1.581}, {'end': 1859.921, 'text': "If I want to know the student name who has scored minimum marks, I'll use this query.", 'start': 1856.078, 'duration': 3.843}, {'end': 1861.682, 'text': 'So let me select this query.', 'start': 1860.501, 'duration': 1.181}, {'end': 1873.011, 'text': 'Ashok Sinha has scored 385 marks and this is the minimum marks in this entire table and the last aggregate function that we were on discuss is Max.', 'start': 1863.984, 'duration': 9.027}], 'summary': 'The min function returns the smallest value from a column. example: ashok sinha scored 385, the minimum marks in the table.', 'duration': 28.883, 'max_score': 1844.128, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY981844128.jpg'}], 'start': 226.025, 'title': 'Evolution and use of sql in industries', 'summary': 'Discusses challenges of file system, evolution of sql, its commands and advantages, and its use in healthcare, retail, and finance, impacting data management, real-time analysis, fraud detection, with real-time examples. it also introduces popular databases and delves into sql basics, aggregate functions, and database queries.', 'chapters': [{'end': 724.203, 'start': 226.025, 'title': 'Challenges of file system and evolution of sql', 'summary': 'Discusses the challenges of file system including limited data sharing, security risks, difficulty of getting quick answers, and data dependence, and also covers the evolution of sql, its definition, commands, and advantages, as well as real-time examples of its use.', 'duration': 498.178, 'highlights': ['SQL is a core of relational database, used for accessing and managing the database with the help of SQL statements, allowing actions like adding, updating, deleting rows of data.', 'SQL has well-defined standards, making it easy to learn and ensuring no ambiguity in query writing.', 'SQL commands include DDL for database schema, DCL for access control, DML for data manipulation, and TCL for transaction control.', 'SQL queries are portable and interactive, offering the flexibility to execute the same query in different systems without changing the format and allowing easy communication with the database.', 'Database systems are used in education to efficiently store and retrieve student, staff, course, exam, attendance, and fees details, showcasing the transformative impact of databases in the education sector.']}, {'end': 1123.842, 'start': 724.203, 'title': 'Sql in various industries', 'summary': "Discusses the widespread use of sql in industries such as healthcare, retail, and finance, highlighting its impact on data management, real-time analysis, and fraud detection, with sql's evolution and features also explained.", 'duration': 399.639, 'highlights': ["SQL's impact on industries", 'Evolution and features of SQL', 'Explanation of data and database']}, {'end': 1460.963, 'start': 1124.522, 'title': 'Database basics and sql queries', 'summary': 'Introduces the popular databases mongodb, postgres, microsoft access, microsoft sql server, mysql, and oracle db. it then delves into basic database queries including creating and deleting a database, creating and deleting a table, and understanding sql basic queries.', 'duration': 336.441, 'highlights': ['Introduction to popular databases including MongoDB, Postgres, Microsoft Access, Microsoft SQL Server, MySQL, and Oracle DB.', 'Explanation of how to create and delete a database, including the syntax and execution of the queries.', 'Introduction to a table in a database, including its structure, data elements, rows, and columns', 'Explanation of table constraints and their importance in maintaining data integrity.', 'Demonstration of how to create and delete a table in a database, including the syntax and execution of the queries.', 'Introduction to the select statement in SQL for querying data from a table.']}, {'end': 1889.609, 'start': 1461.263, 'title': 'Sql basics and aggregate functions', 'summary': 'Covers sql basics including select statements, where clause and logical operators, along with detailed explanations and examples of aggregate functions like count, average, sum, minimum and maximum.', 'duration': 428.346, 'highlights': ['The select statement is used to display all the columns from the student table using the star operator.', 'The where clause is used to filter records based on specified conditions, demonstrated by an example of selecting students from the city of Goa.', "Detailed explanation and examples of logical operators including 'and', 'or', and 'not', along with their syntax and usage in filtering data.", 'Explanation and examples of aggregate functions such as count, average, sum, minimum, and maximum, with detailed syntax and sample queries.']}], 'duration': 1663.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY98226025.jpg', 'highlights': ['SQL commands include DDL for database schema, DCL for access control, DML for data manipulation, and TCL for transaction control.', 'SQL queries are portable and interactive, offering the flexibility to execute the same query in different systems without changing the format.', 'SQL is a core of relational database, used for accessing and managing the database with the help of SQL statements.', 'Introduction to popular databases including MongoDB, Postgres, Microsoft Access, Microsoft SQL Server, MySQL, and Oracle DB.', 'Explanation of table constraints and their importance in maintaining data integrity.', 'Database systems are used in education to efficiently store and retrieve student, staff, course, exam, attendance, and fees details.', "Detailed explanation and examples of logical operators including 'and', 'or', and 'not', along with their syntax and usage in filtering data.", 'Explanation and examples of aggregate functions such as count, average, sum, minimum, and maximum, with detailed syntax and sample queries.']}, {'end': 3334.959, 'segs': [{'end': 2685.389, 'src': 'embed', 'start': 2597.676, 'weight': 0, 'content': [{'end': 2600.457, 'text': "So let's take the example of 30, 000 here.", 'start': 2597.676, 'duration': 2.781}, {'end': 2604.171, 'text': "so we'll get the three results.", 'start': 2601.949, 'duration': 2.222}, {'end': 2611.156, 'text': "but if I remove the equals to keywords, then of course we'll get only two rows in the result set.", 'start': 2604.171, 'duration': 6.985}, {'end': 2620.823, 'text': "and if I use the equals to, then we'll get the only single row which will satisfy the desired or required condition.", 'start': 2611.156, 'duration': 9.667}, {'end': 2624.766, 'text': 'so this is how we use a various operator greater than equals to.', 'start': 2620.823, 'duration': 3.943}, {'end': 2626.748, 'text': 'less than greater than equals to.', 'start': 2624.766, 'duration': 1.982}, {'end': 2628.92, 'text': 'Again this example.', 'start': 2627.879, 'duration': 1.041}, {'end': 2630.62, 'text': 'you can use equal operator.', 'start': 2628.92, 'duration': 1.7}, {'end': 2632.702, 'text': 'it will satisfy the desired conditions.', 'start': 2630.62, 'duration': 2.082}, {'end': 2633.562, 'text': 'fetch the results.', 'start': 2632.702, 'duration': 0.86}, {'end': 2640.165, 'text': 'Comparison operator is a like, how we use the like keywords in comparison operator.', 'start': 2634.763, 'duration': 5.402}, {'end': 2647.949, 'text': 'So generally, we use like operators to perform wildcard searches of valid search string values.', 'start': 2641.146, 'duration': 6.803}, {'end': 2654.893, 'text': 'As I mentioned earlier, that like keyword is generally used when we are filtering or fetching the strings.', 'start': 2648.65, 'duration': 6.243}, {'end': 2658.103, 'text': 'So it used with two wildcards.', 'start': 2655.723, 'duration': 2.38}, {'end': 2659.784, 'text': 'One is a percentage.', 'start': 2658.304, 'duration': 1.48}, {'end': 2665.305, 'text': 'Percentage denotes zero or many characters and underscore denotes one characters.', 'start': 2660.304, 'duration': 5.001}, {'end': 2666.525, 'text': "So let's see.", 'start': 2666.025, 'duration': 0.5}, {'end': 2679.128, 'text': "So in the first example as you can see, I'm getting the values from the department table whose locations starts with C or the C alphabets.", 'start': 2667.445, 'duration': 11.683}, {'end': 2681.308, 'text': "Let's see how we can use it.", 'start': 2680.108, 'duration': 1.2}, {'end': 2685.389, 'text': "So we'll first see how we use the percentage wildcard.", 'start': 2682.288, 'duration': 3.101}], 'summary': 'Using comparison operators and wildcards to filter results from a database table, yielding 3, 2, and 1 row results based on different conditions and operators.', 'duration': 87.713, 'max_score': 2597.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY982597676.jpg'}, {'end': 2813.783, 'src': 'embed', 'start': 2786.699, 'weight': 4, 'content': [{'end': 2792.081, 'text': 'So we have to use the keyword before the wildcard and it will show us the result.', 'start': 2786.699, 'duration': 5.382}, {'end': 2797.644, 'text': 'So along with percentage signs, we use underscores also as a wildcard.', 'start': 2793.062, 'duration': 4.582}, {'end': 2805.056, 'text': 'So in this example, as you can see the second example, select department or location from department where locations like.', 'start': 2798.59, 'duration': 6.466}, {'end': 2809.259, 'text': 'So it means the first three words should be C-H-E-N.', 'start': 2805.416, 'duration': 3.843}, {'end': 2811.221, 'text': 'Then we can use underscore.', 'start': 2809.92, 'duration': 1.301}, {'end': 2813.783, 'text': 'So we have to first satisfy the first keyword.', 'start': 2811.722, 'duration': 2.061}], 'summary': 'Using wildcards with keywords to filter results in sql queries.', 'duration': 27.084, 'max_score': 2786.699, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY982786699.jpg'}, {'end': 2938.14, 'src': 'embed', 'start': 2910.858, 'weight': 2, 'content': [{'end': 2915.483, 'text': 'We get all the employees do not have null values for the salary column.', 'start': 2910.858, 'duration': 4.625}, {'end': 2919.189, 'text': 'So that is the reason we are using case expressions.', 'start': 2916.027, 'duration': 3.162}, {'end': 2927.714, 'text': 'so we cannot use the null values, otherwise it will break because we use the equal operators, and equal operators not used along with null.', 'start': 2919.189, 'duration': 8.525}, {'end': 2931.576, 'text': 'So this is how the syntax of case expressions.', 'start': 2928.435, 'duration': 3.141}, {'end': 2938.14, 'text': 'as you can see, we have declared a variable int, input its type of int and we have set the values is equals to two.', 'start': 2931.576, 'duration': 6.564}], 'summary': 'Using case expressions to handle non-null values in the salary column.', 'duration': 27.282, 'max_score': 2910.858, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY982910858.jpg'}, {'end': 3089.722, 'src': 'embed', 'start': 3066.403, 'weight': 3, 'content': [{'end': 3073.227, 'text': 'It is a multi-step process that puts data into tabular form removing the duplicated data from its relational tables.', 'start': 3066.403, 'duration': 6.824}, {'end': 3074.798, 'text': 'On the screen.', 'start': 3074.238, 'duration': 0.56}, {'end': 3078.479, 'text': 'We just saw that the table is getting decomposed into two smaller table.', 'start': 3074.898, 'duration': 3.581}, {'end': 3086.621, 'text': 'Is it really necessary to normalize the table that is present on the database? Well every table in the database has to be in the normal form.', 'start': 3079.139, 'duration': 7.482}, {'end': 3089.722, 'text': 'So normalization is used mainly for two purpose.', 'start': 3087.301, 'duration': 2.421}], 'summary': 'Normalization process reduces duplication in tables, required for all tables in database.', 'duration': 23.319, 'max_score': 3066.403, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY983066403.jpg'}, {'end': 3208.438, 'src': 'embed', 'start': 3181.54, 'weight': 1, 'content': [{'end': 3184.902, 'text': 'in that case, all the employee records will have to be updated.', 'start': 3181.54, 'duration': 3.362}, {'end': 3189.714, 'text': 'And if by mistake we miss any record it will lead to data inconsistency.', 'start': 3185.493, 'duration': 4.221}, {'end': 3196.415, 'text': 'This is nothing but updation anomaly and the final one is deletion anomaly.', 'start': 3190.454, 'duration': 5.961}, {'end': 3203.997, 'text': 'in our employee table, two different pieces of information are kept together, that is, employee information and Department information.', 'start': 3196.415, 'duration': 7.582}, {'end': 3208.438, 'text': 'Hence at the end of financial year if employee records are deleted.', 'start': 3204.517, 'duration': 3.921}], 'summary': 'Updating employee records avoids data inconsistency and deletion anomaly.', 'duration': 26.898, 'max_score': 3181.54, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY983181540.jpg'}], 'start': 1891.169, 'title': 'Sql fundamentals', 'summary': 'Covers sql basics, grouping and sorting, comparison operators, case expressions, and database normalization. it includes practical examples and emphasizes key takeaways.', 'chapters': [{'end': 2009.598, 'start': 1891.169, 'title': 'Sql grouping and sorting', 'summary': "Covers sql queries for grouping and sorting data, including an example of finding the count of students from different cities and using the 'having' clause to filter results, and sorting data using the 'order by' keyword.", 'duration': 118.429, 'highlights': ['The output shows that Bharat Singh has scored 580 marks, the maximum in the entire table.', 'Executing a query to count the number of students from different cities resulted in each city having one student, demonstrating the effectiveness of the query.', "Using the 'having' clause to display students who have scored more than 500 marks resulted in two students, Ashok and movie, meeting the condition.", "Explanation of the 'group by' functionality, used to arrange similar data into groups, allowing for grouping and analysis of data based on specific criteria."]}, {'end': 2453.353, 'start': 2010.079, 'title': 'Sql basics and operators', 'summary': 'Covers sql basics including sorting data in ascending or descending order, handling null values, updating and deleting records, using in and between operators, and aliasing tables and columns, with practical examples and syntax explanation.', 'duration': 443.274, 'highlights': ['The order by keyword sorts the record in ascending order by default, and descending keyword is used to sort the record in descending order.', 'Explaining the syntax for null values in SQL and how to check for null and non-null values using is null and is not null operators.', 'The update command is used to modify rows in a table, and the delete command is used to delete rows from the database tables.', 'Understanding and using in and between operators to filter records based on multiple values and within a specified range.', 'Explaining the concept of aliasing in SQL, including column and table aliasing to give temporary names for better readability and handling complex queries.']}, {'end': 2909.757, 'start': 2453.834, 'title': 'Sql comparison operators & case expressions', 'summary': 'Covers sql comparison operators like in, not in, greater than, less than, case expressions, and usage of wildcards with like operator for string filtering, with an emphasis on practical examples and key takeaways.', 'duration': 455.923, 'highlights': ['SQL in operator is used to filter results from multiple values, making queries more readable and efficient.', 'Use of comparison operators like greater than, equals to, and less than with practical examples.', 'Demonstration of like operator with wildcards for string filtering and performing wildcard searches.', 'Explanation of case expressions to simulate if-else statements and handling null values.']}, {'end': 3334.959, 'start': 2910.858, 'title': 'Sql case expressions and database normalization', 'summary': 'Explains the syntax and usage of sql case expressions, including a detailed example, and also delves into the importance and benefits of database normalization, highlighting the concepts of data redundancy, data anomalies, and the first and second normal forms.', 'duration': 424.101, 'highlights': ['SQL Case Expressions: Syntax and Example', 'Database Normalization: Importance and Benefits']}], 'duration': 1443.79, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY981891169.jpg', 'highlights': ['Bharat Singh scored 580 marks, the maximum in the entire table', "Using 'having' clause displayed students with more than 500 marks, resulting in two students meeting the condition", "Explanation of 'group by' functionality for arranging similar data into groups based on specific criteria", 'Order by keyword sorts records in ascending order by default, and descending keyword for descending order', 'Explaining syntax for null values in SQL and using is null and is not null operators', 'Understanding and using in and between operators to filter records based on multiple values and within a specified range', 'SQL in operator used to filter results from multiple values, making queries more readable and efficient', 'Demonstration of like operator with wildcards for string filtering and wildcard searches', 'Explanation of case expressions to simulate if-else statements and handling null values', 'Database Normalization: Importance and Benefits', 'SQL Case Expressions: Syntax and Example']}, {'end': 4413.585, 'segs': [{'end': 3359.647, 'src': 'embed', 'start': 3335.619, 'weight': 0, 'content': [{'end': 3344.242, 'text': 'The first condition is it has to be in first normal form and the second one is the table also should not contain partial dependency.', 'start': 3335.619, 'duration': 8.623}, {'end': 3350.484, 'text': 'here partial dependency means the proper subset of a candidate key determines a non prime attribute.', 'start': 3344.242, 'duration': 6.242}, {'end': 3359.647, 'text': "So what is a non prime attribute? Let's understand this in a simple way attributes that form a candidate key in a table are called prime attributes.", 'start': 3351.064, 'duration': 8.583}], 'summary': 'Database table must be in 1nf and free from partial dependency.', 'duration': 24.028, 'max_score': 3335.619, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY983335619.jpg'}, {'end': 3540.642, 'src': 'embed', 'start': 3509.252, 'weight': 5, 'content': [{'end': 3512.854, 'text': "Let's look at the example table in the above table.", 'start': 3509.252, 'duration': 3.602}, {'end': 3517.457, 'text': 'student ID determines subject ID and subject ID determines subject.', 'start': 3512.854, 'duration': 4.603}, {'end': 3521.299, 'text': 'therefore, student ID determines subject via subject ID.', 'start': 3517.457, 'duration': 3.842}, {'end': 3528.224, 'text': 'This implies that we have transitive functional dependency and this table does not satisfy the third normal form.', 'start': 3521.86, 'duration': 6.364}, {'end': 3533.837, 'text': 'Now in order to achieve third normal form, we need to divide the table as shown below.', 'start': 3529.073, 'duration': 4.764}, {'end': 3540.642, 'text': "Firstly, let's divide the table and store student ID student name subject ID and address in it.", 'start': 3534.557, 'duration': 6.085}], 'summary': 'Transitive functional dependency detected, table does not satisfy 3nf. dividing table to achieve 3nf.', 'duration': 31.39, 'max_score': 3509.252, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY983509252.jpg'}, {'end': 4384.348, 'src': 'embed', 'start': 4354.974, 'weight': 1, 'content': [{'end': 4356.335, 'text': 'What are sequel joints?', 'start': 4354.974, 'duration': 1.361}, {'end': 4361.058, 'text': 'So if someone asks you, what are joints in sequel joints in sequel a,', 'start': 4357.155, 'duration': 3.903}, {'end': 4366.802, 'text': 'commands which are used to combine rows from two or more tables based on the related column between those tables.', 'start': 4361.058, 'duration': 5.744}, {'end': 4376.168, 'text': 'the joints are predominantly used when a user is trying to extract data from a table which have one too many relationships between them or many too many relationships between them.', 'start': 4366.802, 'duration': 9.366}, {'end': 4384.348, 'text': 'Joins in sequel are basically commands guys by which you can join two tables and you can get the rows of two tables based on a related column.', 'start': 4376.684, 'duration': 7.664}], 'summary': 'Sequel joints combine rows from tables based on related columns.', 'duration': 29.374, 'max_score': 4354.974, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY984354974.jpg'}], 'start': 3335.619, 'title': 'Sql triggers and joins', 'summary': 'Covers database normalization concepts including first, second, and third normal forms, boyce-chord normal form, and introduction to triggers. it also explains triggers in sql, their syntax, and provides examples, highlighting their automation and efficiency. additionally, the chapter explores sql triggers, including before and after insert triggers, and provides an explanation of sql joins, covering inner, left, right, and full joins.', 'chapters': [{'end': 3756.748, 'start': 3335.619, 'title': 'Database normalization and triggers', 'summary': 'Explains the concepts of first, second, and third normal forms, with examples and necessary conditions, then delves into boyce-chord normal form and concludes with an introduction to triggers, emphasizing their purpose and usage in sql.', 'duration': 421.129, 'highlights': ['The chapter explains the concepts of first, second, and third normal forms, with examples and necessary conditions', 'The chapter delves into Boyce-Chord normal form', 'The chapter concludes with an introduction to triggers, emphasizing their purpose and usage in SQL']}, {'end': 4027.71, 'start': 3757.288, 'title': 'Introduction to triggers in sql', 'summary': 'Introduces triggers in sql, explaining their purpose and syntax, and provides an example of a simple and nested trigger, emphasizing the efficiency and automation they bring to database operations.', 'duration': 270.422, 'highlights': ['Triggers automate processes and increase efficiency by reducing repetitive tasks, demonstrated by the example of automatically sending a welcome email to new customers upon their data entry, showcasing the potential of triggers to streamline operations and improve productivity.', 'The syntax of a trigger is explained in detail, covering the components such as trigger name, timing of trigger execution, DML operations, table name, and the trigger body, offering a comprehensive understanding of the structure and functionality of triggers in SQL.', 'The example of a simple trigger demonstrates its practical application, such as automatically calculating and updating the marks of a student in the database, highlighting the potential for automation and data manipulation through triggers in SQL.', 'The concept of nested triggers is introduced, emphasizing their capability to perform multiple processes and the importance of proper handling and termination to avoid infinite loops, providing insights into more advanced uses of triggers in SQL.', "The operations in triggers, including the 'drop' command, are explained, demonstrating the ability to remove triggers from the database, and a practical reference is provided for further understanding through the use of MySQL Workbench."]}, {'end': 4413.585, 'start': 4029.807, 'title': 'Sql triggers and joins', 'summary': 'Explores the creation and usage of sql triggers, including before and after insert triggers, along with their advantages and disadvantages, and subsequently delves into an explanation of sql joins, covering inner, left, right, and full joins.', 'duration': 383.778, 'highlights': ['The chapter explores the creation and usage of SQL triggers, including before and after insert triggers, along with their advantages and disadvantages.', 'The advantages of triggers are discussed, including forcing security approvals, checking data integrity, counteracting invalid exchanges, handling errors, and inspecting data changes.', 'The disadvantages of triggers are outlined, such as limited validation options, potential increase in database overhead, and difficulty in troubleshooting due to automatic execution.', 'A comprehensive explanation of SQL joins is provided, covering inner, left, right, and full joins, emphasizing their role in combining rows from multiple tables based on related columns.']}], 'duration': 1077.966, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY983335619.jpg', 'highlights': ['The chapter explains the concepts of first, second, and third normal forms, with examples and necessary conditions', 'The chapter explores the creation and usage of SQL triggers, including before and after insert triggers, along with their advantages and disadvantages', 'Triggers automate processes and increase efficiency by reducing repetitive tasks, demonstrated by the example of automatically sending a welcome email to new customers upon their data entry, showcasing the potential of triggers to streamline operations and improve productivity', 'The syntax of a trigger is explained in detail, covering the components such as trigger name, timing of trigger execution, DML operations, table name, and the trigger body, offering a comprehensive understanding of the structure and functionality of triggers in SQL', 'The chapter delves into Boyce-Chord normal form', 'A comprehensive explanation of SQL joins is provided, covering inner, left, right, and full joins, emphasizing their role in combining rows from multiple tables based on related columns']}, {'end': 5162.82, 'segs': [{'end': 4609.653, 'src': 'embed', 'start': 4578.027, 'weight': 3, 'content': [{'end': 4580.789, 'text': 'So initially I had explained you what the inner joint was.', 'start': 4578.027, 'duration': 2.762}, {'end': 4586.933, 'text': 'So when you apply inner join and two tables, you can clearly see that, you know, you get matching values from both the tables.', 'start': 4581.149, 'duration': 5.784}, {'end': 4590.275, 'text': "So what I'm going to do is I'm going to apply inner join on both these tables.", 'start': 4587.213, 'duration': 3.062}, {'end': 4592.797, 'text': 'So for that you simply have to write a query.', 'start': 4590.355, 'duration': 2.442}, {'end': 4596.28, 'text': "So what I'll do is I've already executed it before.", 'start': 4593.377, 'duration': 2.903}, {'end': 4599.783, 'text': "I'll just copy and paste the query over here so that I can explain it for you guys.", 'start': 4596.28, 'duration': 3.503}, {'end': 4604.688, 'text': "So what I've done is I've just selected the employee ID, the employee first name, the employee last name,", 'start': 4599.944, 'duration': 4.744}, {'end': 4609.653, 'text': 'the project ID and the project name to be retrieved from both the employee in the project tables,', 'start': 4604.688, 'duration': 4.965}], 'summary': 'Explained inner join, applied it to retrieve employee and project data.', 'duration': 31.626, 'max_score': 4578.027, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY984578027.jpg'}, {'end': 4669.876, 'src': 'embed', 'start': 4644.653, 'weight': 4, 'content': [{'end': 4651.299, 'text': 'basically all the employees working on a specific project and the respective employee ID, employee first name, last name,', 'start': 4644.653, 'duration': 6.646}, {'end': 4653.421, 'text': 'project ID in the project name has been retrieved.', 'start': 4651.299, 'duration': 2.122}, {'end': 4656.003, 'text': 'So, guys, that was about in a joint.', 'start': 4653.821, 'duration': 2.182}, {'end': 4659.667, 'text': "now let's move forward to our next type of joint, that is, the left joint.", 'start': 4656.003, 'duration': 3.664}, {'end': 4663.77, 'text': 'now, as you can see on the screen now, the left joint, or the left outer joint,', 'start': 4659.667, 'duration': 4.103}, {'end': 4669.876, 'text': 'returns all those records from the left table and also those records which satisfy a condition from the right table.', 'start': 4663.77, 'duration': 6.106}], 'summary': 'Retrieved employee data for specific project, introduced left outer join.', 'duration': 25.223, 'max_score': 4644.653, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY984644653.jpg'}, {'end': 4974.701, 'src': 'embed', 'start': 4945.715, 'weight': 0, 'content': [{'end': 4948.959, 'text': 'Now moving forward to a final type of join that is the full join.', 'start': 4945.715, 'duration': 3.244}, {'end': 4956.566, 'text': 'So the full join or the full outer join returns all those records which either have a matching value in the left table or in the right table.', 'start': 4949.399, 'duration': 7.167}, {'end': 4962.352, 'text': 'So, basically, if you consider table A and table B and you apply full join on both these tables,', 'start': 4956.966, 'duration': 5.386}, {'end': 4967.377, 'text': 'then it will return all those records which either have the matching value in table A or in table B.', 'start': 4962.352, 'duration': 5.025}, {'end': 4970.178, 'text': 'Now the syntax for full join is really simple.', 'start': 4967.957, 'duration': 2.221}, {'end': 4974.701, 'text': "It's again the same as the other three joints, but you just have to mention the word full joint.", 'start': 4970.498, 'duration': 4.203}], 'summary': 'Full join returns all records with matching values in either table.', 'duration': 28.986, 'max_score': 4945.715, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY984945715.jpg'}], 'start': 4414.125, 'title': 'Understanding sql joins', 'summary': 'Covers inner join, left join, right join, and full join in sql, with practical examples and interview questions, emphasizing their syntax, applications, and significance.', 'chapters': [{'end': 4804.971, 'start': 4414.125, 'title': 'Understanding sql joins', 'summary': 'Explains the concepts of inner join and left join in sql, demonstrating their syntax and application through practical examples, highlighting the retrieval of matching values and records from the left table with null values for unmatched records in the right table.', 'duration': 390.846, 'highlights': ['The inner join retrieves all matching values from both tables, demonstrated by selecting columns from the employee and project tables and applying the inner join on the employee and project tables, resulting in the retrieval of all employees working on specific projects.', 'The left join retrieves all records from the left table and includes records from the right table that satisfy a condition, with null values for unmatched records, illustrated by selecting columns from the employee and project tables, applying a left join, and showcasing the presence of null values for employees without associated projects.']}, {'end': 5162.82, 'start': 4805.251, 'title': 'Understanding sql joins: inner, left, right, and full', 'summary': 'Covers the explanation of right join, full join, and comparison with left join in sql, along with the syntax and practical demonstration, emphasizing the significance of different types of joins and their applications in sql. it also includes an overview of important interview questions related to joins.', 'duration': 357.569, 'highlights': ['Explanation of right join, full join, and comparison with left join', 'Syntax and practical demonstration of right join and full join', 'Importance of different types of joins and their applications in SQL', 'Overview of important interview questions related to joins']}], 'duration': 748.695, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY984414125.jpg', 'highlights': ['Importance of different types of joins and their applications in SQL', 'Overview of important interview questions related to joins', 'Syntax and practical demonstration of right join and full join', 'The inner join retrieves all matching values from both tables, demonstrated by selecting columns from the employee and project tables and applying the inner join on the employee and project tables, resulting in the retrieval of all employees working on specific projects.', 'The left join retrieves all records from the left table and includes records from the right table that satisfy a condition, with null values for unmatched records, illustrated by selecting columns from the employee and project tables, applying a left join, and showcasing the presence of null values for employees without associated projects.']}, {'end': 8779.015, 'segs': [{'end': 5345.273, 'src': 'embed', 'start': 5318.05, 'weight': 3, 'content': [{'end': 5324.897, 'text': 'Similarly, if you consider a project can be based on multiple Technologies and any technology can be used in any number of projects.', 'start': 5318.05, 'duration': 6.847}, {'end': 5328.321, 'text': 'This kind of relationship is basically a many-to-many relationship.', 'start': 5325.258, 'duration': 3.063}, {'end': 5331.863, 'text': 'Now to apply the joint operation on the many-to-many relationships.', 'start': 5328.721, 'duration': 3.142}, {'end': 5338.188, 'text': 'What you can simply do is you can have three tables that is the projects table and the Technologies table itself.', 'start': 5332.164, 'duration': 6.024}, {'end': 5345.273, 'text': 'So, basically, these are the two tables which have many-to-many relationships between each other, and also we can have an extra table,', 'start': 5338.588, 'duration': 6.685}], 'summary': 'Many-to-many relationship between projects and technologies can be handled using three tables.', 'duration': 27.223, 'max_score': 5318.05, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY985318050.jpg'}, {'end': 5394.24, 'src': 'embed', 'start': 5369.464, 'weight': 10, 'content': [{'end': 5375.967, 'text': 'So it will hold a record of project a to devops and then project a to again Hadoop and project a to microservices.', 'start': 5369.464, 'duration': 6.503}, {'end': 5381.116, 'text': 'Similarly, it will have n number of records for n number of projects and M number of Technologies.', 'start': 5376.494, 'duration': 4.622}, {'end': 5385.377, 'text': 'So this table is really really important in this scenario,', 'start': 5381.456, 'duration': 3.921}, {'end': 5391.039, 'text': 'and this table basically aims to map the item on the projects table to the items on the Technologies table,', 'start': 5385.377, 'duration': 5.662}, {'end': 5394.24, 'text': 'so that multiple projects can be assigned to one or more Technologies.', 'start': 5391.039, 'duration': 3.201}], 'summary': 'The table maps projects to technologies for flexible assignments.', 'duration': 24.776, 'max_score': 5369.464, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY985369464.jpg'}, {'end': 5740.153, 'src': 'embed', 'start': 5664.126, 'weight': 2, 'content': [{'end': 5673.672, 'text': 'if you want to calculate, for example, average we want to calculate sum or total of salary of all the employees then we use aggregate functions.', 'start': 5664.126, 'duration': 9.546}, {'end': 5680.676, 'text': 'String functions helps us to play around with various strings or characters in our data.', 'start': 5674.372, 'duration': 6.304}, {'end': 5689.357, 'text': 'Most of the string functions are somewhat similar to the string functions which we have in other programming language or the basic programming languages,', 'start': 5681.351, 'duration': 8.006}, {'end': 5691.959, 'text': 'like C, C++, C, sharp or Java.', 'start': 5689.357, 'duration': 2.602}, {'end': 5700.345, 'text': 'Date functions have all the basic functions which we can use to manipulate date or retrieve date from our database.', 'start': 5692.319, 'duration': 8.026}, {'end': 5704.569, 'text': "So let's discuss all of the functions one by one in coming slides.", 'start': 5700.786, 'duration': 3.783}, {'end': 5709.012, 'text': 'As we discussed that these are the various categories again, conversion functions.', 'start': 5705.149, 'duration': 3.863}, {'end': 5712.224, 'text': 'that support data type casting and converting.', 'start': 5709.502, 'duration': 2.722}, {'end': 5717.306, 'text': 'Logical functions are scalar functions that perform logical operations.', 'start': 5712.724, 'duration': 4.582}, {'end': 5722.929, 'text': 'These are kind of similar to the math functions, are again the scalar functions,', 'start': 5717.787, 'duration': 5.142}, {'end': 5732.435, 'text': 'which performs calculations usually based on the input values that I provided as an argument, and most of the times the returns are numeric value.', 'start': 5722.929, 'duration': 9.506}, {'end': 5733.895, 'text': 'Aggregate functions.', 'start': 5733.015, 'duration': 0.88}, {'end': 5740.153, 'text': 'as I mentioned, we use sum, we use average, we use maximum of values.', 'start': 5733.895, 'duration': 6.258}], 'summary': 'The transcript covers various functions including aggregate, string, date, conversion, logical, and math functions used for data manipulation and operations.', 'duration': 76.027, 'max_score': 5664.126, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY985664126.jpg'}, {'end': 6613.066, 'src': 'embed', 'start': 6575.32, 'weight': 4, 'content': [{'end': 6578.001, 'text': 'So these are the standard mathematical functions.', 'start': 6575.32, 'duration': 2.681}, {'end': 6585.814, 'text': 'We have used these functions So here we can see a consolidated examples of all mathematical functions.', 'start': 6578.502, 'duration': 7.312}, {'end': 6591.276, 'text': 'So in the first example we see select abs minus 10.', 'start': 6586.434, 'duration': 4.842}, {'end': 6593.457, 'text': 'So here we are using absolute value.', 'start': 6591.276, 'duration': 2.181}, {'end': 6597.299, 'text': 'So it is converting the negative values to the positive value.', 'start': 6593.858, 'duration': 3.441}, {'end': 6600.921, 'text': 'And all these keywords are we are using as a LEL.', 'start': 6597.819, 'duration': 3.102}, {'end': 6613.066, 'text': 'so we are using abs as areas for the first column and then, as we discuss, rent is basically helps us to Get any random values we can provide.', 'start': 6600.921, 'duration': 12.145}], 'summary': 'Consolidated examples of mathematical functions including abs and rent.', 'duration': 37.746, 'max_score': 6575.32, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY986575320.jpg'}, {'end': 6858.288, 'src': 'embed', 'start': 6795.161, 'weight': 9, 'content': [{'end': 6799.625, 'text': "So here in the apps functions, I'm using a security functions and square functions.", 'start': 6795.161, 'duration': 4.464}, {'end': 6803.629, 'text': 'We can play around with these functions as per our business requirement.', 'start': 6800.446, 'duration': 3.183}, {'end': 6806.271, 'text': "Let's use an example of power.", 'start': 6804.629, 'duration': 1.642}, {'end': 6817.86, 'text': "Let's use the base value as two and two to the power four.", 'start': 6812.877, 'duration': 4.983}, {'end': 6819.962, 'text': 'So the result will be 16.', 'start': 6818.381, 'duration': 1.581}, {'end': 6832.31, 'text': "So this is equivalent to, sorry, if I'll expand this power, two is the base and four is the exponential.", 'start': 6819.962, 'duration': 12.348}, {'end': 6835.832, 'text': "So I'll get the same results at 16.", 'start': 6832.95, 'duration': 2.882}, {'end': 6838.033, 'text': 'So this is all about the maths functions.', 'start': 6835.832, 'duration': 2.201}, {'end': 6839.975, 'text': "Let's move to another category.", 'start': 6838.434, 'duration': 1.541}, {'end': 6842.016, 'text': 'Ignore null values.', 'start': 6840.935, 'duration': 1.081}, {'end': 6852.425, 'text': 'Aggregate functions are frequently used with the group by clause and for filtering like we used to filter the data using where clause.', 'start': 6842.58, 'duration': 9.845}, {'end': 6858.288, 'text': "So if you're using group by clause then we use having keyword to filter the result set.", 'start': 6852.505, 'duration': 5.783}], 'summary': 'Using security and math functions, 2^4 equals 16. aggregate functions filter data.', 'duration': 63.127, 'max_score': 6795.161, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY986795161.jpg'}, {'end': 6921.387, 'src': 'embed', 'start': 6888.703, 'weight': 6, 'content': [{'end': 6892.645, 'text': 'Okay, so I can use the average functions on department number column.', 'start': 6888.703, 'duration': 3.942}, {'end': 6905.259, 'text': "So I can write select I'll write the column number from department.", 'start': 6895.187, 'duration': 10.072}, {'end': 6909.801, 'text': "If I'll execute this, I'll get the average of the values.", 'start': 6906.5, 'duration': 3.301}, {'end': 6914.984, 'text': 'So let me execute both the queries in Funco so we can compare the results.', 'start': 6910.062, 'duration': 4.922}, {'end': 6921.387, 'text': 'So 25 is the average of 40 plus 30 plus 20, 10 divided by four.', 'start': 6915.444, 'duration': 5.943}], 'summary': 'Using average function on department number column to get an average of 25.', 'duration': 32.684, 'max_score': 6888.703, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY986888703.jpg'}, {'end': 7369.531, 'src': 'embed', 'start': 7340.846, 'weight': 8, 'content': [{'end': 7346.268, 'text': 'We have rtrim, it returns the character string after truncating all the failing blanks.', 'start': 7340.846, 'duration': 5.422}, {'end': 7350.85, 'text': 'Basically it remove the blank from the right hand side of a string.', 'start': 7346.368, 'duration': 4.482}, {'end': 7356.606, 'text': 'char functions converts an int ASCII code to a character.', 'start': 7351.311, 'duration': 5.295}, {'end': 7365.509, 'text': 'Character index find out searches and expressions for another expressions and returns its starting positions if found.', 'start': 7357.266, 'duration': 8.243}, {'end': 7369.531, 'text': 'STR returns character data converted from numeric data.', 'start': 7366.25, 'duration': 3.281}], 'summary': 'Functions like rtrim and char convert data in sql.', 'duration': 28.685, 'max_score': 7340.846, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY987340846.jpg'}, {'end': 7512.808, 'src': 'embed', 'start': 7484.172, 'weight': 15, 'content': [{'end': 7491.816, 'text': 'So this is our string and this is string we are replacing cde with xxx as the replace.', 'start': 7484.172, 'duration': 7.644}, {'end': 7499.48, 'text': 'So as you can see in the result set here for the replace column, the value CDE is replaced with triple X.', 'start': 7492.335, 'duration': 7.145}, {'end': 7501.841, 'text': 'We have substring functions in the substring.', 'start': 7499.48, 'duration': 2.361}, {'end': 7512.808, 'text': 'We are get out of this ABCDEF which is our main string from index two and until length three, we are getting the substring.', 'start': 7502.442, 'duration': 10.366}], 'summary': "Replacing 'cde' with 'xxx', substring from index 2, length 3.", 'duration': 28.636, 'max_score': 7484.172, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY987484172.jpg'}, {'end': 7645.037, 'src': 'embed', 'start': 7613.34, 'weight': 0, 'content': [{'end': 7615.101, 'text': 'Same way I can use rtrim.', 'start': 7613.34, 'duration': 1.761}, {'end': 7621.966, 'text': "So if I'll use rtrim, then only the trailing spaces will be removed, not the leading one.", 'start': 7616.402, 'duration': 5.564}, {'end': 7625.208, 'text': "If I'll execute this, I'll get this result.", 'start': 7622.506, 'duration': 2.702}, {'end': 7629.891, 'text': "And if I'll use trim, then we'll consider for both.", 'start': 7626.529, 'duration': 3.362}, {'end': 7633.213, 'text': 'So we do not have trim here.', 'start': 7629.911, 'duration': 3.302}, {'end': 7641.275, 'text': 'So L trim removes the leading spaces and R trim removes the failing spaces.', 'start': 7634.651, 'duration': 6.624}, {'end': 7645.037, 'text': 'So this is all about the string functions.', 'start': 7642.956, 'duration': 2.081}], 'summary': 'String functions like rtrim, ltrim, and trim were discussed for removing trailing and leading spaces.', 'duration': 31.697, 'max_score': 7613.34, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY987613340.jpg'}, {'end': 8040.814, 'src': 'embed', 'start': 8014.014, 'weight': 18, 'content': [{'end': 8021.357, 'text': "I'll get an error because all the columns other than the aggregate functions should be there in the group by clause.", 'start': 8014.014, 'duration': 7.343}, {'end': 8028.607, 'text': "So if I'll include the other table as well, the query will be executed successfully.", 'start': 8022.798, 'duration': 5.809}, {'end': 8040.814, 'text': 'So in order to execute group by successfully we have to include all the columns which we are including in the select statement in the group by clause.', 'start': 8031.969, 'duration': 8.845}], 'summary': 'To execute group by successfully, include all select statement columns in group by clause.', 'duration': 26.8, 'max_score': 8014.014, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY988014014.jpg'}, {'end': 8178.33, 'src': 'embed', 'start': 8151.448, 'weight': 7, 'content': [{'end': 8155.17, 'text': 'We cannot use a where keyword with this aggregate function.', 'start': 8151.448, 'duration': 3.722}, {'end': 8161.352, 'text': 'So having applies to the summarized group records, whereas where applies to the individual records.', 'start': 8155.71, 'duration': 5.642}, {'end': 8168.614, 'text': 'So for the summarized record or the group, when we use group by then we have to use having and if it is the individual records,', 'start': 8162.012, 'duration': 6.602}, {'end': 8170.335, 'text': "then we'll use the where clause.", 'start': 8168.614, 'duration': 1.721}, {'end': 8178.33, 'text': "So if we'll use the having clause, only the groups that meets the having criteria will be returned in the result sets.", 'start': 8171.206, 'duration': 7.124}], 'summary': "Use 'having' with group by, 'where' with individual records.", 'duration': 26.882, 'max_score': 8151.448, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY988151448.jpg'}, {'end': 8357.713, 'src': 'embed', 'start': 8329.741, 'weight': 11, 'content': [{'end': 8338.343, 'text': 'So stored procedures can be defined as a set of logical group of SQL statements which are grouped to perform a specific task.', 'start': 8329.741, 'duration': 8.602}, {'end': 8343.424, 'text': 'So basically in stored processor we write most of our business logic.', 'start': 8339.221, 'duration': 4.203}, {'end': 8350.128, 'text': 'Like other programming languages, stored processors accepts input parameters.', 'start': 8344.405, 'duration': 5.723}, {'end': 8355.492, 'text': 'They return multiple values by means of out parameters or output parameters.', 'start': 8350.728, 'duration': 4.764}, {'end': 8357.713, 'text': 'They contains programming statements.', 'start': 8356.291, 'duration': 1.422}], 'summary': 'Stored procedures are logical groups of sql statements for business logic, accepting inputs and returning multiple values.', 'duration': 27.972, 'max_score': 8329.741, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY988329741.jpg'}, {'end': 8434.32, 'src': 'embed', 'start': 8404.516, 'weight': 1, 'content': [{'end': 8407.438, 'text': 'So in the syntax it is mentioned that it is create or alter.', 'start': 8404.516, 'duration': 2.922}, {'end': 8413.802, 'text': 'So if you want to modify the existing store procedure, then we have to use the keyword alter.', 'start': 8408.138, 'duration': 5.664}, {'end': 8419.805, 'text': 'If we are creating the store procedure first time, then we have to use the keyword create.', 'start': 8414.322, 'duration': 5.483}, {'end': 8425.975, 'text': 'So alter keyword is used if you want to modify any existing stored procedure.', 'start': 8420.586, 'duration': 5.389}, {'end': 8430.278, 'text': 'Now why we create stored procedure when we can write SQL queries.', 'start': 8426.435, 'duration': 3.843}, {'end': 8434.32, 'text': 'So here are the some of the benefits of creating stored procedure.', 'start': 8430.698, 'duration': 3.622}], 'summary': "Use 'create' for new, 'alter' for existing stored procedures with benefits.", 'duration': 29.804, 'max_score': 8404.516, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY988404516.jpg'}], 'start': 5163.48, 'title': 'Sql joins, functions, and stored procedures', 'summary': 'Covers natural joins, inner joins, many-to-many relationships, hash joins, self join, cross join, and the significance of built-in functions in sql. it also details sql conversion, logical, math, aggregate, string, and date functions, including mathematical and aggregate functions, string functions, and date/time functions with examples. additionally, it explains the group by and having clauses, as well as the benefits and creation of stored procedures.', 'chapters': [{'end': 5199.356, 'start': 5163.48, 'title': 'Natural join in database', 'summary': 'Explains the concept of a natural join in databases, which aims to give an output based on the columns in both tables, reducing the number of columns returned, illustrated with an example of two tables with specific column names.', 'duration': 35.876, 'highlights': ['Natural join provides an output based on the columns in both tables, reducing the number of columns returned.', 'Illustration of a specific example with two tables and their column names: table one with two columns named column 1 and column 2, and table two with columns named column 1 and column tree.']}, {'end': 5777.641, 'start': 5199.816, 'title': 'Understanding sql joins and built-in functions', 'summary': 'Covers the importance of using natural joins over inner joins to reduce redundancy, the process of mapping many-to-many relationships using two join statements, the purpose and process of hash joins, and the functionalities of self join and cross join. it also details the use of two join statements to link three tables together and emphasizes the significance of built-in functions in sql, categorizing them into conversion, logical, math, aggregate, string, and date functions.', 'duration': 577.825, 'highlights': ['The importance of using natural joins over inner joins to reduce redundancy', 'The process of mapping many-to-many relationships using two join statements', 'The purpose and process of hash joins', 'The functionalities of self join and cross join', 'The use of two join statements to link three tables together', 'The significance of built-in functions in SQL']}, {'end': 6506.024, 'start': 5778.442, 'title': 'Sql functions overview', 'summary': 'Covers sql conversion, logical, and math functions, including key points such as conversion functions casting, converting, and parsing, their differences, and usage, logical functions like choose and if, and math functions for business and engineering calculations.', 'duration': 727.582, 'highlights': ['Conversion functions casting, converting, and parsing are commonly used in SQL', 'Try cast, try convert, and try parse provide null values on failure', 'Choose function returns a specified index from a list of values', 'If function returns one of the two values based on a Boolean expression', 'Math functions are used for business and engineering calculations']}, {'end': 6886.021, 'start': 6506.564, 'title': 'Mathematical functions and aggregate functions', 'summary': 'Introduces standard mathematical functions such as round, floor, square root, ceiling, square, power, abs, and demonstrates their applications in sql queries, including generating random values, rounding, finding square roots, and raising to specified powers. additionally, it covers aggregate functions like average, min, sum, count, and max, and their usage with group by and having clauses.', 'duration': 379.457, 'highlights': ['The chapter introduces standard mathematical functions such as round, floor, square root, ceiling, square, power, abs, and demonstrates their applications in SQL queries, including generating random values, rounding, finding square roots, and raising to specified powers.', 'It covers aggregate functions like average, min, sum, count, and max, and their usage with group by and having clauses.', 'The average function returns the average of the values in a group, with null values being ignored.', 'The chapter also provides practical examples of using mathematical and aggregate functions in SQL queries, including fetching values from a department table and performing various calculations.']}, {'end': 7718.733, 'start': 6888.703, 'title': 'Aggregate and string functions', 'summary': 'Covers the usage of aggregate functions such as average, min, count, and max, providing examples and comparisons of their functionality, and then delves into string functions, explaining their operations and providing examples, finally touching on date and time functions and their usage.', 'duration': 830.03, 'highlights': ['The chapter covers the usage of aggregate functions such as average, min, count, and max, providing examples and comparisons of their functionality.', 'The chapter delves into string functions, explaining their operations and providing examples.', 'The chapter touches on date and time functions and their usage.']}, {'end': 8779.015, 'start': 7718.733, 'title': 'Sql date and time functions, group by and having clause, and stored procedures', 'summary': 'Covers sql date and time functions including date part, date diff, date add, group by clause, having clause, and stored procedures. it also explains the benefits of using stored procedures and provides a step-by-step guide for creating and executing stored procedures in sql.', 'duration': 1060.282, 'highlights': ['SQL groupby clause is used along with the group functions to retrieve data grouped according to one or more columns, ensuring that the group by clause should contain all the columns in the select list except those used along with the group functions.', 'The having clause was added to SQL because the where keyword could not be used with the aggregate function, and it applies to the summarized group records while the where clause applies to the individual records, requiring the having keyword to be used in combination with the group by clause to restrict the group of return rows based on specified conditions.', 'Stored procedures in SQL are sets of SQL statements with a name that has been created and stored in the database, allowing for the execution and storage of a set of SQL statements that can be reused, providing benefits such as reusability, improved performance, and easier maintenance.']}], 'duration': 3615.535, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY985163480.jpg', 'highlights': ['Natural join reduces columns returned', 'Illustration of specific example with two tables', 'Importance of using natural joins over inner joins', 'Mapping many-to-many relationships using two join statements', 'Process of hash joins and functionalities of self join and cross join', 'Significance of built-in functions in SQL', 'Conversion functions casting, converting, and parsing in SQL', 'Try cast, try convert, and try parse provide null values on failure', 'Choose function returns a specified index from a list of values', 'Math functions for business and engineering calculations', 'Standard mathematical functions and their applications in SQL queries', 'Usage of aggregate functions with group by and having clauses', 'Practical examples of using mathematical and aggregate functions in SQL queries', 'Usage of aggregate functions such as average, min, count, and max', 'Coverage of string functions and examples', 'Explanation of date and time functions and their usage', 'Usage of SQL groupby clause and its application', 'Explanation of having clause and its application with group by clause', 'Stored procedures in SQL for reusability, improved performance, and easier maintenance']}, {'end': 12523.054, 'segs': [{'end': 8976.364, 'src': 'embed', 'start': 8948.934, 'weight': 5, 'content': [{'end': 8951.617, 'text': 'I increase the font size for better visibility.', 'start': 8948.934, 'duration': 2.683}, {'end': 8956.662, 'text': 'So this is an example of a stored processor with a parameter.', 'start': 8952.898, 'duration': 3.764}, {'end': 8961.588, 'text': 'So here we are providing the parameter department number.', 'start': 8957.824, 'duration': 3.764}, {'end': 8970.422, 'text': 'So here we have department number as a parameter and the data type of this parameter is nvacare and length is 30.', 'start': 8962.389, 'duration': 8.033}, {'end': 8976.364, 'text': 'So in the previous example we have written a select statement but without the where clause.', 'start': 8970.422, 'duration': 5.942}], 'summary': 'Example of stored procedure with a parameter, department number with data type nvacare and length 30.', 'duration': 27.43, 'max_score': 8948.934, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY988948934.jpg'}, {'end': 12234.888, 'src': 'embed', 'start': 12210.517, 'weight': 0, 'content': [{'end': 12216.805, 'text': 'this kind of database stores related information together for fast query access to the MongoDB query language.', 'start': 12210.517, 'duration': 6.288}, {'end': 12224.715, 'text': 'The features of MongoDB are, as you can see on my screen, that is, indexing, replication, ad hoc query, schema list and sharding.', 'start': 12217.205, 'duration': 7.51}, {'end': 12230.362, 'text': 'talking about indexing, MongoDB indexes are created in order to improve the search performance.', 'start': 12224.715, 'duration': 5.647}, {'end': 12234.888, 'text': 'next, talking about replication, MongoDB distributes the data across different machines.', 'start': 12230.362, 'duration': 4.526}], 'summary': 'Mongodb stores related information for fast query access with features including indexing, replication, ad hoc query, schema list, and sharding.', 'duration': 24.371, 'max_score': 12210.517, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9812210517.jpg'}, {'end': 12358.773, 'src': 'embed', 'start': 12327.208, 'weight': 2, 'content': [{'end': 12328.73, 'text': 'So to make you understand better.', 'start': 12327.208, 'duration': 1.522}, {'end': 12330.651, 'text': "I've considered the example on the screen.", 'start': 12328.75, 'duration': 1.901}, {'end': 12337.438, 'text': 'So to insert the data into the employees table in the sequel query, you just have to type in insert into employees.', 'start': 12331.012, 'duration': 6.426}, {'end': 12341.422, 'text': 'That is basically your table name and insert into is your SQL syntax.', 'start': 12337.478, 'duration': 3.944}, {'end': 12348.428, 'text': 'and then you mentioned the column names, that is, the employee ID and employee age, and then you use the SQL syntax, that is, the values,', 'start': 12341.422, 'duration': 7.006}, {'end': 12350.11, 'text': 'and mention the values for the fields.', 'start': 12348.428, 'duration': 1.682}, {'end': 12352.571, 'text': 'So, to just insert data into the table.', 'start': 12350.63, 'duration': 1.941}, {'end': 12358.773, 'text': 'you mentioned insert into employees employee ID, employee age values and then you mentioned the values.', 'start': 12352.571, 'duration': 6.202}], 'summary': "To insert data into the employees table in sql, use the syntax 'insert into employees' followed by the column names and their respective values.", 'duration': 31.565, 'max_score': 12327.208, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9812327208.jpg'}], 'start': 8779.015, 'title': 'Database procedures and functions', 'summary': 'Covers stored procedure execution, user-defined functions, sql functions and syntax, nosql database overview, and a comparison of sql and nosql databases, addressing various aspects such as syntax, parameters, functions, database types, and performance.', 'chapters': [{'end': 9038.954, 'start': 8779.015, 'title': 'Stored procedure execution', 'summary': "Covers the creation and execution of stored procedures in a database, including the handling of dependencies, optional use of 'exec' keyword, and creating procedures with and without parameters.", 'duration': 259.939, 'highlights': ['The chapter emphasizes the importance of checking dependencies before deleting objects from the database, highlighting the need to avoid deleting objects with dependencies. It also mentions the successful execution of a statement after deleting a stored procedure from the database.', "It explains the execution of a stored procedure without a parameter, highlighting the optional use of the 'exec' keyword and the retrieval of results from a select statement within the procedure.", "The chapter provides guidance on creating and executing stored procedures with parameters, including the use of 'where' clause to filter records and the successful creation and execution of a stored procedure with a parameter.", "It details the optional use of the 'exec' keyword while executing stored procedures and the different methods, such as using the management studio, to execute stored procedures with parameters."]}, {'end': 9966.824, 'start': 9038.954, 'title': 'Stored procedures and user defined functions', 'summary': 'Explains the creation and execution of stored procedures with parameters, making parameters optional by providing the null keyword, creating stored procedures with multiple parameters, handling output parameters, adding comments, and handling exceptions using try-catch blocks. it also introduces the concept of user-defined functions, explaining their benefits and structure.', 'duration': 927.87, 'highlights': ['Stored procedure execution without providing a parameter results in an error due to the mandatory parameter setup.', 'Making a parameter optional by providing the null keyword allows the procedure to execute without error, even if the value is not provided.', 'Creation and execution of stored procedures with multiple parameters are demonstrated, showing the use of the execute keyword and passing values for each parameter.', "The concept of output parameters is introduced, allowing the return of values after execution, with the usage of 'output' or 'out' keywords.", 'The addition of comments in the stored procedures is demonstrated, including both single-line and block comments.', 'Exception handling using try-catch blocks in stored procedures is explained, showcasing the handling of errors and displaying relevant information.', 'Introduction to user-defined functions, highlighting their similarities to functions in programming languages, acceptance of parameters, and ability to perform complex calculations.', 'The benefits of user-defined functions are outlined, including reusability, performance improvement, easier maintenance, and reduction of network traffic.', 'The structure of user-defined functions, comprising header and function body, is explained.']}, {'end': 11108.447, 'start': 9967.644, 'title': 'Sql functions and syntax', 'summary': 'Discusses the syntax and types of sql functions, including scalar value and table-valued functions, and compares sql and mysql in terms of usage, syntax, updates, data security, and supporting language, highlighting the benefits of user-defined functions and the history and definition of sql and mysql.', 'duration': 1140.803, 'highlights': ['The chapter discusses the syntax and types of SQL functions, including scalar value and table-valued functions.', 'It explains the categories of user-defined functions, such as scalar value functions and table-valued functions.', 'It compares SQL and MySQL in terms of usage, syntax, updates, data security, and supporting language.', 'The chapter highlights the benefits of user-defined functions, including modular programming, time-saving, faster execution, and reduced network traffic.', 'It provides the history and definition of SQL, which was developed by Microsoft Corporation, and MySQL, which claims to be the first open source relational database.']}, {'end': 11755.626, 'start': 11108.447, 'title': 'Nosql database overview', 'summary': 'Explains nosql databases, comparing them to sql databases, and highlighting their dynamic schema, lack of specific query language, high scalability, and suitability for complex data storage and olap applications.', 'duration': 647.179, 'highlights': ['NoSQL databases have four main categories: document database, key-value stores, graph stores, and wide-column stores, each with specific data storage and retrieval mechanisms.', 'SQL databases are vertically scalable, while NoSQL databases are horizontally scalable, allowing for better handling of large traffic and data sets.', 'SQL databases are a good fit for complex queries due to their structured schema, while NoSQL databases are not as powerful for complex queries due to the lack of standard interfaces and powerful query language.', 'NoSQL databases are better suited for hierarchical data storage, using a key-value pair approach similar to JSON data, making them highly preferred for large data sets compared to SQL databases.']}, {'end': 12523.054, 'start': 11755.886, 'title': 'Comparison of sql and nosql databases', 'summary': 'Discusses the differences between sql and nosql databases, addressing olap and oltp systems, asset properties versus cap theorem, external support, and a detailed comparison of mysql and mongodb based on various parameters, including query language, flexibility of schema, relationships, performance, and support.', 'duration': 767.168, 'highlights': ['My sequel and NoSQL databases are compared based on OLAP and OLTP systems', 'Comparison between asset properties and CAP theorem for SQL and NoSQL databases', 'Detailed comparison of MySQL and MongoDB based on various parameters']}], 'duration': 3744.039, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY988779015.jpg', 'highlights': ['NoSQL databases have four main categories: document database, key-value stores, graph stores, and wide-column stores, each with specific data storage and retrieval mechanisms.', 'SQL databases are vertically scalable, while NoSQL databases are horizontally scalable, allowing for better handling of large traffic and data sets.', 'The benefits of user-defined functions are outlined, including reusability, performance improvement, easier maintenance, and reduction of network traffic.', 'The chapter discusses the syntax and types of SQL functions, including scalar value and table-valued functions.', 'The chapter highlights the benefits of user-defined functions, including modular programming, time-saving, faster execution, and reduced network traffic.', 'The chapter emphasizes the importance of checking dependencies before deleting objects from the database, highlighting the need to avoid deleting objects with dependencies.']}, {'end': 13788.643, 'segs': [{'end': 12763.093, 'src': 'embed', 'start': 12738.499, 'weight': 3, 'content': [{'end': 12744.401, 'text': 'and the read and writes are done on the primary replica by default and then replicated to the secondary replicas.', 'start': 12738.499, 'duration': 5.902}, {'end': 12746.883, 'text': 'So, guys, this was about the replication.', 'start': 12744.842, 'duration': 2.041}, {'end': 12753.346, 'text': 'MySQL supports the master slave replication and the MongoDB supports built-in replication, sharding and Auto elections.', 'start': 12746.883, 'duration': 6.463}, {'end': 12757.749, 'text': "Next we'll be comparing these tools based on usage now for this factor.", 'start': 12753.926, 'duration': 3.823}, {'end': 12760.992, 'text': "I'm going to tell you where you can use my sequel and MongoDB.", 'start': 12757.849, 'duration': 3.143}, {'end': 12763.093, 'text': "So let's start with my sequel first.", 'start': 12761.452, 'duration': 1.641}], 'summary': 'Mysql supports master-slave replication while mongodb supports built-in replication, sharding, and auto-elections.', 'duration': 24.594, 'max_score': 12738.499, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9812738499.jpg'}, {'end': 12919.887, 'src': 'embed', 'start': 12892.952, 'weight': 0, 'content': [{'end': 12898.916, 'text': 'You can use the query, like you know, create table table name and then you mentioned all the field names and the data types, right?', 'start': 12892.952, 'duration': 5.964}, {'end': 12905.339, 'text': 'So over here I have different field names, like the employee ID, employee first name, last name, age, email ID, phone number and address,', 'start': 12899.156, 'duration': 6.183}, {'end': 12906.88, 'text': 'and their specific data types, right?', 'start': 12905.339, 'duration': 1.541}, {'end': 12908.941, 'text': "So I'm going to just execute this particular query.", 'start': 12907.12, 'duration': 1.821}, {'end': 12913.483, 'text': 'So once I execute this particular query, you can see that you know a table has been created.', 'start': 12909.801, 'duration': 3.682}, {'end': 12919.887, 'text': 'So now, if we just look into our databases, we have a database that is employee info and then we have a table inside it.', 'start': 12913.944, 'duration': 5.943}], 'summary': "A table with employee information was created in the 'employee info' database.", 'duration': 26.935, 'max_score': 12892.952, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9812892952.jpg'}], 'start': 12523.579, 'title': 'Database management systems', 'summary': 'Delves into the comparison of security features between mysql and mongodb, the performance disparities, support, key features, replication, and community. it also illustrates the process of creating databases, tables, and documents in sql and mongodb, emphasizing the differences, use cases, and subsets of sql.', 'chapters': [{'end': 12578.108, 'start': 12523.579, 'title': 'Security comparison: mysql vs mongodb', 'summary': 'Compares the security features of mysql and mongodb, highlighting that mysql uses a privilege-based security model while mongodb employs role-based access control and additional encryption methods for secure access.', 'duration': 54.529, 'highlights': ['MongoDB uses role-based access control with a flexible set of privileges, including authentication, auditing, and authorization, along with transport layer security and secure socket layer for encryption purposes.', 'MySQL uses a privilege-based security model, authenticating users and providing them with specific database privileges such as create, select, insert, and update.']}, {'end': 12860.011, 'start': 12578.568, 'title': 'Mysql vs mongodb comparison', 'summary': 'Compares mysql and mongodb based on performance, support, key features, replication, usage, and active community. mongodb is faster in handling large unstructured data compared to mysql, and it supports built-in replication and sharding, while mysql is better for data with tables and rows and has a better active community.', 'duration': 281.443, 'highlights': ['MongoDB is faster than MySQL in handling large unstructured data', 'Both MySQL and MongoDB offer excellent 24/7 support for security fixes, maintenance releases, bug fixes, patches, and updates', 'MongoDB supports auto sharding, comprehensive secondary indexes, in-memory speed, native replication, and embedded data model support', 'MySQL supports master-slave replication and multi-source replication, while MongoDB supports built-in replication, sharding, and auto-elections', 'MySQL is best for data with tables and rows, small data sets, and frequent updates, while MongoDB is best for unstructured data, large data sets, high write loads, and high availability in unstable environments', 'MySQL has a better active community compared to MongoDB']}, {'end': 13157.411, 'start': 12860.352, 'title': 'Creating databases and tables in sql and mongodb', 'summary': 'Illustrates the process of creating databases, tables, and documents in sql and mongodb, highlighting the execution of queries and the creation of database, table, and document, emphasizing the differences and use cases between sql and mongodb.', 'duration': 297.059, 'highlights': ['Creation of database and table in SQL', 'Insertion of data into the table in SQL', 'Creation of database, collection, and document in MongoDB', 'Comparison between SQL and MongoDB']}, {'end': 13343.04, 'start': 13157.871, 'title': 'Sql vs mongodb: differences & sql subsets', 'summary': 'Discusses the differences between delete and truncate statements, the subsets of sql including data definition, manipulation, control, and transaction language, and the definition of database management systems and its types.', 'duration': 185.169, 'highlights': ['The difference between delete and truncate statements is explained, with truncate being faster and from the data definition family.', 'The subsets of SQL are outlined, including data definition, manipulation, control, and transaction language.', 'Database management systems are defined as software applications that capture, analyze, and modify various types of data stored in databases.']}, {'end': 13788.643, 'start': 13343.381, 'title': 'Database management systems', 'summary': 'Explains different types of database management systems including hierarchical, relational, network, and object-oriented systems, covering key concepts such as primary keys, table constraints, joins, and differences between char and varchar data types.', 'duration': 445.262, 'highlights': ['Explaining different types of database management systems', 'Defining primary keys and their usage in databases', 'Describing table constraints and their types', 'Explaining the differences between CHAR and VARCHAR data types', 'Detailing the concept and usage of foreign keys in databases']}], 'duration': 1265.064, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9812523579.jpg', 'highlights': ['MongoDB supports auto sharding, comprehensive secondary indexes, in-memory speed, native replication, and embedded data model support', 'MySQL supports master-slave replication and multi-source replication, while MongoDB supports built-in replication, sharding, and auto-elections', 'MongoDB is faster than MySQL in handling large unstructured data', 'MySQL uses a privilege-based security model, authenticating users and providing them with specific database privileges such as create, select, insert, and update', 'MongoDB uses role-based access control with a flexible set of privileges, including authentication, auditing, and authorization, along with transport layer security and secure socket layer for encryption purposes']}, {'end': 15781.71, 'segs': [{'end': 14068.948, 'src': 'embed', 'start': 14042.011, 'weight': 9, 'content': [{'end': 14047.914, 'text': 'denormalization basically refers to a technique which is used to access the data from higher to lower forms of database.', 'start': 14042.011, 'duration': 5.903}, {'end': 14053.736, 'text': 'It increases the performance of the entire infrastructure as it introduces redundancy into the table,', 'start': 14048.294, 'duration': 5.442}, {'end': 14061.299, 'text': 'and it also adds the redundant data into a table by incorporating database queries that combine data from various tables in a single table.', 'start': 14053.736, 'duration': 7.563}, {'end': 14064.783, 'text': 'So if you have to just explain denormalization, you can just say that you know.', 'start': 14061.639, 'duration': 3.144}, {'end': 14068.948, 'text': 'it is a technique which is used to access data from higher to lower forms of database,', 'start': 14064.783, 'duration': 4.165}], 'summary': 'Denormalization improves database performance by introducing redundancy and combining data from multiple tables into a single table.', 'duration': 26.937, 'max_score': 14042.011, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9814042011.jpg'}, {'end': 14118.392, 'src': 'embed', 'start': 14095.414, 'weight': 5, 'content': [{'end': 14102.639, 'text': 'a blank database has a customer tables to store the customer information that the customer table stores this information as a set of attributes,', 'start': 14095.414, 'duration': 7.225}, {'end': 14105.662, 'text': 'that is, basically the columns within the table for each and every customer.', 'start': 14102.639, 'duration': 3.023}, {'end': 14111.927, 'text': 'So if you consider you know a customer table has around five parameters like customer ID, customer name, customer phone number,', 'start': 14106.102, 'duration': 5.825}, {'end': 14118.392, 'text': 'customer email ID and so on, then all these customer ID, customer name, customer phone number are basically the column names, that is,', 'start': 14111.927, 'duration': 6.465}], 'summary': 'A blank database has a customer table with around five parameters, like customer id, customer name, customer phone number, and customer email id.', 'duration': 22.978, 'max_score': 14095.414, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9814095414.jpg'}, {'end': 14284.36, 'src': 'embed', 'start': 14251.597, 'weight': 7, 'content': [{'end': 14254.922, 'text': 'So each table can have many non-cluster indexes, right?', 'start': 14251.597, 'duration': 3.325}, {'end': 14261.41, 'text': 'So basically, guys, there are three types of indexes that you need to explain about, that is, the unique, the cluster and the non-cluster index.', 'start': 14255.282, 'duration': 6.128}, {'end': 14263.312, 'text': 'now moving forward to our next question', 'start': 14261.41, 'duration': 1.902}, {'end': 14266.577, 'text': 'That is what is normalization and what are its advantages?', 'start': 14263.453, 'duration': 3.124}, {'end': 14272.244, 'text': 'So normalization is basically the process of organizing data to avoid duplication and redundancy.', 'start': 14267.077, 'duration': 5.167}, {'end': 14277.531, 'text': "So it's basically the opposite of denormalization that we just talked about in one of the previous questions.", 'start': 14272.605, 'duration': 4.926}, {'end': 14284.36, 'text': 'So if you just have to define normalization, then you can see that unit is a process of organizing data to avoid duplication and redundancy.', 'start': 14277.931, 'duration': 6.429}], 'summary': 'Explaining types of indexes and normalization process.', 'duration': 32.763, 'max_score': 14251.597, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9814251597.jpg'}, {'end': 14336.571, 'src': 'embed', 'start': 14284.855, 'weight': 1, 'content': [{'end': 14292.217, 'text': 'Now. the advantages of normalization are as you can see on the screen it offers better database organization, more tables with smaller rows,', 'start': 14284.855, 'duration': 7.362}, {'end': 14295.637, 'text': 'efficient data access, greater flexibility for queries.', 'start': 14292.217, 'duration': 3.42}, {'end': 14298.498, 'text': 'quickly finds the information easier to implement.', 'start': 14295.637, 'duration': 2.861}, {'end': 14303.939, 'text': 'security allows easy modification, reduction of redundant data and duplicate data,', 'start': 14298.498, 'duration': 5.441}, {'end': 14308.02, 'text': 'more compact database and ensures consistent data after modification.', 'start': 14303.939, 'duration': 4.081}, {'end': 14310.741, 'text': 'So guys these are few advantages of normalization.', 'start': 14308.42, 'duration': 2.321}, {'end': 14313.102, 'text': "Now, let's move forward to our next question.", 'start': 14311.301, 'duration': 1.801}, {'end': 14316.663, 'text': 'That is what is the difference between the drop and the truncate commands.', 'start': 14313.122, 'duration': 3.541}, {'end': 14321.645, 'text': 'the drop command basically removes the table and it cannot be rolled back from the database.', 'start': 14316.663, 'duration': 4.982}, {'end': 14328.447, 'text': 'whenever you use the drop command, just remember that you know you completely drop the complete table and it cannot be rolled back from the database.', 'start': 14321.645, 'duration': 6.802}, {'end': 14333.529, 'text': 'and the truncate command removes all the rows from the table and also cannot be rolled back into the database.', 'start': 14328.447, 'duration': 5.082}, {'end': 14336.571, 'text': 'So the drop command and the truncate command differ by this.', 'start': 14333.929, 'duration': 2.642}], 'summary': 'Normalization offers better organization, efficient access, security, and reduces redundant data. drop removes table, while truncate removes rows.', 'duration': 51.716, 'max_score': 14284.855, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9814284855.jpg'}, {'end': 14846.154, 'src': 'embed', 'start': 14820.442, 'weight': 4, 'content': [{'end': 14828.387, 'text': 'in sequel, a sub query is basically a query inside under the query, where a query is defined to retrieve data or information back from the database.', 'start': 14820.442, 'duration': 7.945}, {'end': 14834.231, 'text': 'So sub queries are always executed first and the result of the sub query is passed on to the main query.', 'start': 14828.807, 'duration': 5.424}, {'end': 14839.952, 'text': 'So as you can see on my screen, I have an outer query inside which I have a sub query or an inner query.', 'start': 14834.731, 'duration': 5.221}, {'end': 14846.154, 'text': 'So basically this query is assigned to retrieve data from the database and then this query will be executed first.', 'start': 14840.312, 'duration': 5.842}], 'summary': 'Sub queries retrieve data for main query. they are always executed first.', 'duration': 25.712, 'max_score': 14820.442, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9814820442.jpg'}, {'end': 15665.167, 'src': 'embed', 'start': 15639.12, 'weight': 0, 'content': [{'end': 15644.041, 'text': "then you can consider the UK's and now a scalar functions as their calculated with respect to the string.", 'start': 15639.12, 'duration': 4.921}, {'end': 15647.422, 'text': 'So guys, this is what aggregate functions and scalar functions mean.', 'start': 15644.561, 'duration': 2.861}, {'end': 15649.482, 'text': "Let's move forward to the next question.", 'start': 15647.842, 'duration': 1.64}, {'end': 15652.683, 'text': 'That is, how can you fetch alternate records from a table?', 'start': 15649.522, 'duration': 3.161}, {'end': 15658.905, 'text': 'when you answer this question of how you can fetch alternate records from a table, please make sure that you mention a point,', 'start': 15652.683, 'duration': 6.222}, {'end': 15663.406, 'text': 'that you can fetch alternate records, that is, both the odd and the even row numbers.', 'start': 15658.905, 'duration': 4.501}, {'end': 15665.167, 'text': 'now to display the even row numbers.', 'start': 15663.406, 'duration': 1.761}], 'summary': 'Aggregate and scalar functions explained. fetch alternate records from a table.', 'duration': 26.047, 'max_score': 15639.12, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9815639120.jpg'}], 'start': 13789.221, 'title': 'Sql fundamentals', 'summary': 'Covers data integrity, indexes, sql queries, joins, normalization types (1nf, 2nf, 3nf, bcnf), acid properties, triggers, operators, subqueries, group functions, dynamic sql execution, constraints, set operators, and various sql techniques for efficient database management.', 'chapters': [{'end': 14146.131, 'start': 13789.221, 'title': 'Data integrity & sql queries', 'summary': 'Covers data integrity, clustered and non-clustered indexes, writing sql queries, types of joins, denormalization, and entities and relationships in databases.', 'duration': 356.91, 'highlights': ['Data integrity defines the accuracy and consistency of data stored in the database and enforces integrity constraints to enforce business rules.', 'Clustered index is used for faster retrieval of data, altering the way records are stored by sorting rows by column, while non-clustered index creates a separate object within a table pointing back to the original table.', "Writing a SQL query to display the current date involves using the 'get date' function, providing a simple syntax and applicable to various SQL server versions.", 'Types of joins include inner join (returns records with matching values in both tables), full join (returns records with matching values in either table), left join (returns records from the left table and those satisfying conditions from the right table), and right join (returns records from the right table and those satisfying conditions from the left table).', 'Denormalization technique improves database performance by introducing redundancy and combining data from various tables into a single table.', 'Entities in databases refer to real-world objects like people, places, or things, with attributes represented as columns in tables, while relationships denote relational links between entities.']}, {'end': 14336.571, 'start': 14146.491, 'title': 'Understanding indexes and normalization', 'summary': 'Explains the purpose and types of indexes, including the unique, clustered, and non-clustered index, and the advantages of normalization, such as better database organization, efficient data access, and security.', 'duration': 190.08, 'highlights': ['Indexes are a performance tuning method allowing faster data retrieval, with unique, clustered, and non-clustered indexes being the main types.', 'Normalization is the process of organizing data to avoid duplication and redundancy, offering various advantages including better database organization, efficient data access, and security.', 'The unique index prevents duplicate values in a field, the clustered index reorders the physical order of the table, and the non-clustered index maintains a logical order of the data.', 'The drop command removes the entire table from the database and cannot be rolled back, while the truncate command removes all rows from the table and also cannot be rolled back.']}, {'end': 14696.425, 'start': 14336.972, 'title': 'Database commands and normalization types', 'summary': 'Covers the concepts of drop and truncate commands, followed by an explanation of the four types of normalization (1nf, 2nf, 3nf, bcnf) with a detailed example. it also explains the acid properties in databases and the different types of triggers in sql.', 'duration': 359.453, 'highlights': ['Explanation of 1NF, 2NF, 3NF, BCNF normalization types', 'Definition and significance of ACID properties in databases', 'Explanation of triggers in SQL and the six types of triggers']}, {'end': 14878.108, 'start': 14696.785, 'title': 'Sql triggers, operators, null values, joins, and subqueries', 'summary': 'Covers sql triggers, types of operators, null values, differences between cross join and natural join, and types of subqueries, highlighting key points such as the before and after triggers, five types of operators, the distinction between null values, zero, and blank space, the differences between cross join and natural join, and the types of subqueries.', 'duration': 181.323, 'highlights': ['The before and after triggers are applied on insert, update, and delete commands, with before insert activated before data insertion and after insert activated after data insertion, before update activated before data update, and after update activated after data update, before delete activated before data removal, and after delete activated after data removal.', 'There are mainly five types of operators available in SQL, including arithmetic, bitwise, comparison, compound, and logical operators.', 'A null value is not the same as zero or a blank space, as null value represents unavailable, unknown, assigned, or not applicable values, while zero is a number and a blank space is a character.', 'The cross join produces the cross product or Cartesian product of two tables, while the natural join is based on all columns having the same name and data types in both tables.', 'A subquery in SQL is a query inside another query, executed first with its result passed on to the main query, and there are two types of subqueries: correlated and non-correlated subqueries.']}, {'end': 15348.211, 'start': 14878.638, 'title': 'Sql subqueries, counting records, and query techniques', 'summary': 'Covers non-correlated and correlated subqueries, counting records in a table, writing sql queries to find employee names, the third-highest salary, group functions, relationships, inserting null values, between and in condition operators, sql functions, merge statement, recursive stored procedures, sql clauses, and dynamic sql execution.', 'duration': 469.573, 'highlights': ['The chapter covers non-correlated and correlated subqueries, counting records in a table, writing SQL queries to find employee names, the third-highest salary, group functions, relationships, inserting null values, between and in condition operators, SQL functions, merge statement, recursive stored procedures, SQL clauses, and dynamic SQL execution.', 'The different types of relationships in SQL are one-to-one, one-to-many, many-to-one, and self-referencing relationships.', "The need of merge statement is to allow conditional update or insertion of data into a table, performing an update if a row exists or an insert if the row doesn't exist.", "The chapter provides various ways to count the number of records in a table, including using 'select star' and 'select count' queries.", 'The need of group functions in SQL is due to their ability to work on a set of rows and return one result per group, with commonly used functions such as average, count, max, min, sum, and variance.']}, {'end': 15781.71, 'start': 15348.211, 'title': 'Sql: dynamic sequel, constraints, set operators', 'summary': 'Covers the execution of dynamic sequel with three methods, levels of constraints, fetching common records from two tables using the intersect statement, case manipulation functions - lower, upper, init cap, set operators - union, intersect, minus, alias command, aggregate and scalar functions, fetching alternate records with mod function, like operator for pattern matching, and selecting unique records with the distinct keyword.', 'duration': 433.499, 'highlights': ['The chapter covers the execution of dynamic sequel with three methods', 'Fetching common records from two tables using the intersect statement', 'Case manipulation functions - lower, upper, init cap', 'Set operators - Union, Intersect, Minus', 'Fetching alternate records with mod function', 'Like operator for pattern matching', 'Selecting unique records with the distinct keyword']}], 'duration': 1992.489, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9813789221.jpg', 'highlights': ['Explanation of 1NF, 2NF, 3NF, BCNF normalization types', 'Definition and significance of ACID properties in databases', 'The need of group functions in SQL is due to their ability to work on a set of rows and return one result per group, with commonly used functions such as average, count, max, min, sum, and variance', 'The different types of relationships in SQL are one-to-one, one-to-many, many-to-one, and self-referencing relationships', "The need of merge statement is to allow conditional update or insertion of data into a table, performing an update if a row exists or an insert if the row doesn't exist", 'The chapter covers non-correlated and correlated subqueries, counting records in a table, writing SQL queries to find employee names, the third-highest salary, group functions, relationships, inserting null values, between and in condition operators, SQL functions, merge statement, recursive stored procedures, SQL clauses, and dynamic SQL execution', 'Indexes are a performance tuning method allowing faster data retrieval, with unique, clustered, and non-clustered indexes being the main types', 'Normalization is the process of organizing data to avoid duplication and redundancy, offering various advantages including better database organization, efficient data access, and security', 'Data integrity defines the accuracy and consistency of data stored in the database and enforces integrity constraints to enforce business rules', "Writing a SQL query to display the current date involves using the 'get date' function, providing a simple syntax and applicable to various SQL server versions"]}, {'end': 17342.042, 'segs': [{'end': 15821.295, 'src': 'embed', 'start': 15795.542, 'weight': 5, 'content': [{'end': 15801.624, 'text': "So for your understanding I've mentioned two examples over here that is either you can use the substring command or the right command.", 'start': 15795.542, 'duration': 6.082}, {'end': 15804.745, 'text': 'So when you use the substring statement, you can use the statement.', 'start': 15801.924, 'duration': 2.821}, {'end': 15805.125, 'text': 'like you know.', 'start': 15804.745, 'duration': 0.38}, {'end': 15809.787, 'text': 'select substring student name 1 to 5 as student name from student.', 'start': 15805.125, 'duration': 4.662}, {'end': 15813.589, 'text': 'the first five characters of student name will be retrieved from the student table.', 'start': 15809.787, 'duration': 3.802}, {'end': 15819.053, 'text': 'Now. similarly, when you use the right statement, you can use the statements like you know, select right student name,', 'start': 15814.009, 'duration': 5.044}, {'end': 15821.295, 'text': 'comma 5 a student name from student right?', 'start': 15819.053, 'duration': 2.242}], 'summary': 'You can use substring or right command to extract characters from a table in sql.', 'duration': 25.753, 'max_score': 15795.542, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9815795542.jpg'}, {'end': 16034.537, 'src': 'embed', 'start': 16006.555, 'weight': 3, 'content': [{'end': 16011.139, 'text': "now let's move forward with the next question, that is, list all types of the user defined functions.", 'start': 16006.555, 'duration': 4.584}, {'end': 16013.921, 'text': 'So there are mainly three types of user defined functions.', 'start': 16011.499, 'duration': 2.422}, {'end': 16019.146, 'text': 'That is the scalar function the inline table valued functions and the multi statement valued functions.', 'start': 16014.022, 'duration': 5.124}, {'end': 16025.45, 'text': "So you mainly have to mention these three functions when you're asked about the different types of user-defined functions.", 'start': 16019.586, 'duration': 5.864}, {'end': 16028.573, 'text': "next, let's move forward and understand what do you mean by collation?", 'start': 16025.45, 'duration': 3.123}, {'end': 16034.537, 'text': 'So collation is defined as a set of rules that determine how data can be sorted as well as compared.', 'start': 16029.113, 'duration': 5.424}], 'summary': 'Three types of user-defined functions: scalar, inline table valued, and multi-statement valued. collation defines data sorting and comparison rules.', 'duration': 27.982, 'max_score': 16006.555, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9816006555.jpg'}, {'end': 16069.383, 'src': 'embed', 'start': 16045.289, 'weight': 0, 'content': [{'end': 16052.054, 'text': "It is basically a set of rules that determine how data can be sorted as well as compared now, let's move forward with the next question.", 'start': 16045.289, 'duration': 6.765}, {'end': 16054.917, 'text': 'That is, what are the different types of collation sensitivity?', 'start': 16052.094, 'duration': 2.823}, {'end': 16062.323, 'text': 'So the different types of collation sensitivity are the case sensitivity, the corner sensitivity, the width sensitivity and the accent sensitivity.', 'start': 16055.257, 'duration': 7.066}, {'end': 16065.941, 'text': 'So guys these are the mainly four types of sensitivities of collation.', 'start': 16062.819, 'duration': 3.122}, {'end': 16069.383, 'text': "So I hope that you've understood till now next in this session.", 'start': 16066.321, 'duration': 3.062}], 'summary': 'Collation sensitivity has four types: case, corner, width, and accent sensitivity.', 'duration': 24.094, 'max_score': 16045.289, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9816045289.jpg'}, {'end': 16179.173, 'src': 'embed', 'start': 16152.206, 'weight': 4, 'content': [{'end': 16158.492, 'text': 'So data warehouse can be understood as a central repository of data where the data is assembled from multiple sources of information.', 'start': 16152.206, 'duration': 6.286}, {'end': 16160.615, 'text': 'So guys that was about data warehouse.', 'start': 16158.953, 'duration': 1.662}, {'end': 16167.202, 'text': "Now, let's move forward and understand what are the different authentication modes in sequel server and how it can be changed.", 'start': 16160.955, 'duration': 6.247}, {'end': 16172.947, 'text': 'The different authentication modes in SQL Server are basically the Windows mode and the mixed mode.', 'start': 16167.742, 'duration': 5.205}, {'end': 16179.173, 'text': 'So these two modes are basically used in SQL Windows and to change the authentication modes in SQL Server.', 'start': 16173.247, 'duration': 5.926}], 'summary': 'Data warehouse is a central repository for data. sql server has two authentication modes: windows mode and mixed mode.', 'duration': 26.967, 'max_score': 16152.206, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9816152206.jpg'}, {'end': 17078.715, 'src': 'embed', 'start': 17037.264, 'weight': 1, 'content': [{'end': 17039.165, 'text': "I'll leave a couple of links in the description box.", 'start': 17037.264, 'duration': 1.901}, {'end': 17045.189, 'text': "You can go through those videos and then maybe come back to this if you're specifically looking for SQL for data science.", 'start': 17039.505, 'duration': 5.684}, {'end': 17048.952, 'text': "Now, let's get started with our demo, which is quite interesting.", 'start': 17046.07, 'duration': 2.882}, {'end': 17053.35, 'text': "So guys with this demo, I'll be using the MySQL workbench.", 'start': 17050.248, 'duration': 3.102}, {'end': 17055.212, 'text': "So it's quite easy to install.", 'start': 17053.851, 'duration': 1.361}, {'end': 17057.353, 'text': "It'll just take like 15 to 20 minutes.", 'start': 17055.232, 'duration': 2.121}, {'end': 17059.355, 'text': "I'll leave a link in the description box.", 'start': 17057.774, 'duration': 1.581}, {'end': 17063.458, 'text': "We have a short video where in we're showing you how to install the whole workbench.", 'start': 17059.415, 'duration': 4.043}, {'end': 17071.324, 'text': "So once you've installed MySQL, we'll start by creating a database and we'll import a data set into our workbench.", 'start': 17064.158, 'duration': 7.166}, {'end': 17078.715, 'text': "So I'm going to import a existing data set, right? I'm doing this because we are doing a more advanced tutorial.", 'start': 17072.071, 'duration': 6.644}], 'summary': 'Demo of mysql workbench, installation takes 15-20 minutes.', 'duration': 41.451, 'max_score': 17037.264, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9817037264.jpg'}, {'end': 17222.383, 'src': 'embed', 'start': 17191.547, 'weight': 9, 'content': [{'end': 17196.37, 'text': "Now for this tutorial, I've already created a database, so I'm going to use that database itself.", 'start': 17191.547, 'duration': 4.823}, {'end': 17200.252, 'text': 'Name of my database is students.', 'start': 17197.751, 'duration': 2.501}, {'end': 17205.795, 'text': "So let's activate this database.", 'start': 17202.974, 'duration': 2.821}, {'end': 17212.378, 'text': 'Now in this, I have a couple of tables and we are going to be focusing on this table, employee details.', 'start': 17206.475, 'duration': 5.903}, {'end': 17214.519, 'text': 'So this is the table that I imported.', 'start': 17212.878, 'duration': 1.641}, {'end': 17222.383, 'text': "So, guys, for those of you who don't know how to import a CSV file into your MySQL workbench, all you have to do is go to tables,", 'start': 17215.119, 'duration': 7.264}], 'summary': "Using the database 'students', we will focus on the 'employee details' table and demonstrate how to import a csv file into mysql workbench.", 'duration': 30.836, 'max_score': 17191.547, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9817191547.jpg'}], 'start': 15781.97, 'title': 'Sql for data science', 'summary': 'Covers sql basics, concepts, and importance of mysql in data science, with over 2.5 quintillion bytes of data generated daily, highlighting the significance of sql in data science and its role in managing vast amounts of data.', 'chapters': [{'end': 15964.408, 'start': 15781.97, 'title': 'Sql basics and concepts', 'summary': 'Covers unique record selection, fetching first five characters of a string using substring and right commands, differences between sql and pl/sql, uses and advantages of views, and the purpose of stored procedures in sql.', 'duration': 182.438, 'highlights': ['Views are used for restricting access to data, making complex queries simple, and ensuring data independence.', 'Stored procedures consist of multiple SQL statements, saving time and avoiding redundant code writing.', 'The main difference between SQL and PL/SQL is that SQL allows single queries or operations, while PL/SQL enables writing full programs with loops and variables for multiple operations.', 'A view is a virtual table consisting of a subset of data from one or more tables combined based on the relationship, taking less space to store.', 'Fetching first five characters of a string can be done using the substring or right commands in SQL statements.']}, {'end': 16384.719, 'start': 15964.408, 'title': 'Sql for data science', 'summary': 'Covers the advantages and disadvantages of stored procedures, types of user-defined functions, collation, local and global variables, auto increment in sql, data warehouse, authentication modes in sql server, stuff and replace functions, and the significance of sql in data science, highlighting that data science aims to derive useful insights from the immeasurable amount of data, with over 2.5 quintillion bytes generated daily, and sql plays a crucial role in storing and managing this vast amount of data.', 'duration': 420.311, 'highlights': ['Stored procedures support faster execution, reduce network traffic, and provide better security to the data, while the only disadvantage is that they can only be executed in the database and utilize more memory in the database server.', 'There are three main types of user-defined functions: scalar function, inline table-valued functions, and multi-statement table-valued functions, essential to mention when asked about different types of user-defined functions.', 'Collation refers to a set of rules determining data sorting and comparison, while the different types of collation sensitivity include case sensitivity, accent sensitivity, width sensitivity, and corner sensitivity.', 'Local variables exist only inside a function and cannot be referred by any other function, while global variables can be accessed throughout the program, essential to understand for effective variable usage.', 'Auto increment allows the creation of a unique number whenever a new record is inserted, crucial when utilizing primary keys, and is specifically used in Oracle, while the identity keyword is used in SQL Server.', 'Data warehouse serves as a central repository for assembling, consolidating, transforming, and making data available for mining and online processing, highlighting the importance of effectively managing vast amounts of data.', 'The two main authentication modes in SQL Server are Windows mode and mixed mode, crucial for understanding and managing security settings in SQL Server.', 'The stuff function overwrites existing characters or inserts a string into another string, while the replace function is used to replace existing characters, providing essential string manipulation capabilities in SQL.', 'Data science aims to derive useful insights from the immeasurable amount of data, with over 2.5 quintillion bytes generated daily, and SQL plays a crucial role in storing and managing this vast amount of data, underscoring the importance of SQL in data science.']}, {'end': 16890.86, 'start': 16385.4, 'title': 'Importance of mysql in data science', 'summary': 'Highlights the importance of mysql in data science, emphasizing its ease of use, security features, scalability to handle large datasets, compatibility with multiple programming interfaces, and support for various operating systems.', 'duration': 505.46, 'highlights': ['MySQL is highly scalable, supporting large databases up to as much as 50 million rows or more, with a default size limit of about 4 GB and a theoretical limit of around 8 TB of data.', 'MySQL is easy to use and requires only basic knowledge of SQL, making it accessible for users to build and interact with MySQL using simple SQL statements.', "MySQL's security features include a solid data security layer, password encryption, and protection of sensitive data from intruders.", 'MySQL is highly compatible, running on various operating systems such as Windows, Linux, and Unix, and offering support for multiple programming interfaces including C, C++, Java, Perl, PHP, and Python.', 'MySQL is considered a very fast database program and highly productive due to its use of triggers, stored procedures, and views.']}, {'end': 17342.042, 'start': 16891.601, 'title': 'Data management in sql for data science', 'summary': 'Emphasizes the importance of aligning values with defined data types when inserting data into a table in sql, provides a brief overview of essential sql commands (select, update, delete, drop table), and demonstrates the process of importing a csv file into mysql workbench for data analysis.', 'duration': 450.441, 'highlights': ['The chapter emphasizes the importance of aligning values with defined data types when inserting data into a table in SQL.', 'Provides a brief overview of essential SQL commands (select, update, delete, drop table).', 'Demonstrates the process of importing a CSV file into MySQL Workbench for data analysis.']}], 'duration': 1560.072, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9815781970.jpg', 'highlights': ['SQL plays a crucial role in storing and managing vast amounts of data, with over 2.5 quintillion bytes generated daily.', 'MySQL is highly scalable, supporting large databases up to as much as 50 million rows or more.', 'Stored procedures support faster execution, reduce network traffic, and provide better security to the data.', 'Views are used for restricting access to data, making complex queries simple, and ensuring data independence.', 'Stored procedures consist of multiple SQL statements, saving time and avoiding redundant code writing.', 'A view is a virtual table consisting of a subset of data from one or more tables combined based on the relationship, taking less space to store.', 'MySQL is considered a very fast database program and highly productive due to its use of triggers, stored procedures, and views.', 'The main difference between SQL and PL/SQL is that SQL allows single queries or operations, while PL/SQL enables writing full programs with loops and variables for multiple operations.', 'MySQL is easy to use and requires only basic knowledge of SQL, making it accessible for users to build and interact with MySQL using simple SQL statements.', 'Data science aims to derive useful insights from the immeasurable amount of data, with over 2.5 quintillion bytes generated daily.']}, {'end': 21180.48, 'segs': [{'end': 17582.918, 'src': 'embed', 'start': 17554.853, 'weight': 1, 'content': [{'end': 17558.175, 'text': 'So here you can see that there are different job titles in our data set.', 'start': 17554.853, 'duration': 3.322}, {'end': 17566.861, 'text': 'We have sergeant, we have police officer, we have chief contractor, expeditor, we have civil engineer, concrete laborer.', 'start': 17558.495, 'duration': 8.366}, {'end': 17569.743, 'text': 'we have traffic controller, pool motor.', 'start': 17566.861, 'duration': 2.882}, {'end': 17574.192, 'text': 'This is police officer and this is police officer assigned as detective.', 'start': 17570.43, 'duration': 3.762}, {'end': 17575.753, 'text': "Wow, that's interesting.", 'start': 17574.713, 'duration': 1.04}, {'end': 17580.996, 'text': 'So basically this query gave us the unique job roles that are there in our data set.', 'start': 17576.634, 'duration': 4.362}, {'end': 17582.918, 'text': "Now let's try something else.", 'start': 17581.697, 'duration': 1.221}], 'summary': 'The data set includes various job titles such as sergeant, police officer, chief contractor, expeditor, civil engineer, concrete laborer, traffic controller, and pool motor.', 'duration': 28.065, 'max_score': 17554.853, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9817554853.jpg'}, {'end': 18738.04, 'src': 'embed', 'start': 18711.796, 'weight': 2, 'content': [{'end': 18716.2, 'text': "So I'm going to install the 11.4 version war for Windows 64 bit, right?", 'start': 18711.796, 'duration': 4.404}, {'end': 18721.705, 'text': "So I'm going to just click on download over here and then you see that you know you'll be redirected to this particular page.", 'start': 18716.22, 'duration': 5.485}, {'end': 18722.146, 'text': 'that you know.', 'start': 18721.705, 'duration': 0.441}, {'end': 18725.909, 'text': 'thank you for downloading postgresql, and also on the left hand side.', 'start': 18722.146, 'duration': 3.763}, {'end': 18730.013, 'text': "You'll see that you know postgresql is getting downloaded right? So let's wait for it to download.", 'start': 18725.929, 'duration': 4.084}, {'end': 18735.778, 'text': 'All right, so as you can see on my screen, it has got downloaded.', 'start': 18732.795, 'duration': 2.983}, {'end': 18738.04, 'text': "So I'll just double click it so that we can open.", 'start': 18735.858, 'duration': 2.182}], 'summary': 'Installing postgresql 11.4 version for windows 64 bit, successfully downloaded and opened.', 'duration': 26.244, 'max_score': 18711.796, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9818711796.jpg'}, {'end': 18857.742, 'src': 'embed', 'start': 18828.787, 'weight': 3, 'content': [{'end': 18833.309, 'text': "So basically my server will run on the port 5032 and then I'll click on next.", 'start': 18828.787, 'duration': 4.522}, {'end': 18839.207, 'text': "Once I click on next you'll see that, you know, I can choose the default local to be used by the new database cluster.", 'start': 18834.082, 'duration': 5.125}, {'end': 18842.589, 'text': "So I'm going to let it be as it is and then I'm going to click on next.", 'start': 18839.267, 'duration': 3.322}, {'end': 18846.733, 'text': "once I click on next, you'll see that you know basically the following setting will be used for installation.", 'start': 18842.589, 'duration': 4.144}, {'end': 18850.296, 'text': 'that is, basically whatever you have selected till now will be shown over there.', 'start': 18846.733, 'duration': 3.563}, {'end': 18857.742, 'text': 'That is the installation directory, the server installation directory, the data directory, the database port, the database super user and so on right,', 'start': 18850.656, 'duration': 7.086}], 'summary': 'Server will run on port 5032, default local used for new database cluster, following settings to be used for installation.', 'duration': 28.955, 'max_score': 18828.787, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9818828787.jpg'}, {'end': 18903.533, 'src': 'embed', 'start': 18873.693, 'weight': 5, 'content': [{'end': 18878.417, 'text': "So I'll just click on next over here and then you'll see that you know Postgres SQL is getting installed on my sister.", 'start': 18873.693, 'duration': 4.724}, {'end': 18880.238, 'text': "So let's wait for it to install.", 'start': 18878.917, 'duration': 1.321}, {'end': 18889.336, 'text': 'All right.', 'start': 18888.996, 'duration': 0.34}, {'end': 18896.064, 'text': 'Now, once the installation is done, you get a confirmation wizard that you know the setup is finished installing PostgreSQL on your computer right?', 'start': 18889.437, 'duration': 6.627}, {'end': 18903.533, 'text': 'So you can just click on finish and then, if you want to launch the stack builder at your exit, you can just check in this box,', 'start': 18896.385, 'duration': 7.148}], 'summary': 'Installing postgres sql on computer, launching stack builder.', 'duration': 29.84, 'max_score': 18873.693, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9818873693.jpg'}, {'end': 18971.33, 'src': 'embed', 'start': 18949.94, 'weight': 0, 'content': [{'end': 18959.605, 'text': "you'll clearly see that you know we have a server and then automatically the server gets connected to databases and the super user database that is postgres right under this particular database.", 'start': 18949.94, 'duration': 9.665}, {'end': 18965.387, 'text': "What we're going to do is we're going to basically use the different commands, that is, the different sequel command categories,", 'start': 18959.665, 'duration': 5.722}, {'end': 18971.33, 'text': "and then we're going to create a schema table and then we're going to choose how we can play around with databases right?", 'start': 18965.387, 'duration': 5.943}], 'summary': 'Using sql commands to create and manipulate databases.', 'duration': 21.39, 'max_score': 18949.94, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9818949940.jpg'}, {'end': 20588.441, 'src': 'embed', 'start': 20565.984, 'weight': 4, 'content': [{'end': 20574.791, 'text': "what I'll do is I'll explain you the different keys and constraints used in the database so that it's more clear to you how you can create the database in a more structured manner,", 'start': 20565.984, 'duration': 8.807}, {'end': 20578.573, 'text': "and then we'll move forward with the various manipulation commands.", 'start': 20574.791, 'duration': 3.782}, {'end': 20581.196, 'text': "until then, what I'll do is I'll just delete this particular table.", 'start': 20578.573, 'duration': 2.623}, {'end': 20588.441, 'text': "So I'll right click here, go to delete and click on OK, and then, let's see, you know, we will delete this particular schema also,", 'start': 20581.796, 'duration': 6.645}], 'summary': 'Explaining database keys and constraints, followed by manipulation commands and table deletion.', 'duration': 22.457, 'max_score': 20565.984, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9820565984.jpg'}], 'start': 17342.682, 'title': 'Sql and database management', 'summary': 'Covers importing csv data into mysql, sql querying, postgresql features, installation, sequel commands, schema creation, and entity relationship diagram concepts, including keys and constraints.', 'chapters': [{'end': 17393.847, 'start': 17342.682, 'title': 'Importing csv data into mysql', 'summary': 'Demonstrates how to import a csv file into mysql workbench for data analysis, using a large employee details dataset as an example.', 'duration': 51.165, 'highlights': ['Importing CSV file into MySQL workbench for data analysis.', 'Demonstrating the process using a large employee details dataset.', 'Overview of the columns in the employee details table.']}, {'end': 18791.62, 'start': 17393.847, 'title': 'Sql querying and postgresql features', 'summary': 'Covers a comprehensive guide to sql querying, including extracting entire tables, finding unique designations, unique departments with job titles, and more. additionally, it provides an introduction to postgresql, its features such as data types, data integrity, performance, reliability, security, and extensibility, along with a demonstration of how to install postgresql on windows.', 'duration': 1397.773, 'highlights': ['The chapter covers a comprehensive guide to SQL querying, including extracting entire tables, finding unique designations, unique departments with job titles, and more.', 'Introduction to PostgreSQL and its features such as data types, data integrity, performance, reliability, security, and extensibility.', 'Demonstration of how to install PostgreSQL on Windows, including selecting the installation directory and components.']}, {'end': 19150.199, 'start': 18792.24, 'title': 'Postgres sql installation and sequel commands', 'summary': 'Covers the installation of postgres sql on windows, including steps for directory selection, password setup, port number selection, and installation confirmation. additionally, it introduces the sequel command categories, explaining the data definition language, data manipulation language, data control language, and transaction control language commands, offering insights into their functionalities and applications.', 'duration': 357.959, 'highlights': ['The installation process for Postgres SQL on Windows involves selecting the directory, setting the password for the database super user, selecting the port number, and confirming the installation.', 'The sequel command categories include data definition language commands, data manipulation language commands, data control language commands, and transaction control language commands, each serving distinct purposes in managing databases.', 'The data definition language commands encompass defining the schema, creating tables, creating views, and other commands for structuring the database.', 'The data manipulation language commands facilitate the manipulation of data within the database.', 'The data control language commands involve managing rights, permissions, and controls of the database system.']}, {'end': 20116.915, 'start': 19150.599, 'title': 'Sql schema creation and entity relationship diagram', 'summary': 'Covers the creation of schemas in sql using sequel shell and pg admin, demonstrating the visualization of created schemas, creating tables using sql commands, and explaining entity relationship diagram (erd) concepts, including entities, attributes, and relationships.', 'duration': 966.316, 'highlights': ['The demonstration of creating schemas in SQL using Sequel Shell and PG Admin, showcasing the visualization of created schemas and the ease of using GUI for schema management.', "The detailed explanation of creating tables in SQL using commands, such as 'create table', specifying column names and data types, with a demonstration of the output and schema update in PG Admin.", 'The comprehensive explanation of Entity Relationship Diagram (ERD) concepts, including entities, attributes, and relationships, showcasing the understanding and practical application of various types of attributes like composite, simple, multivalued, stored, derived, and complex attributes.']}, {'end': 20659.279, 'start': 20116.915, 'title': 'Understanding entity relationship diagram', 'summary': 'Discusses the concepts of composite and simple attributes, single and multivalued attributes, stored versus derived attributes, complex attributes, and the manipulation commands of ddl in pg admin, with a focus on keys in databases, including candidate key, super key, primary key, alternate key, and foreign key.', 'duration': 542.364, 'highlights': ['The chapter discusses the concepts of composite and simple attributes, single and multivalued attributes, stored versus derived attributes, complex attributes, and the manipulation commands of DDL in PG admin.', 'The chapter provides a detailed explanation of the candidate key, which is the minimal set of attributes that can uniquely identify a tuple in a relation.', 'The chapter introduces the concept of keys in databases, including candidate key, super key, primary key, alternate key, and foreign key.']}, {'end': 21180.48, 'start': 20659.659, 'title': 'Database keys and constraints', 'summary': 'Covers the concepts of candidate key, super key, primary key, alternate key, and foreign key in database design, as well as the constraints including not null, unique, check, default, and index. it also demonstrates the practical creation of tables in a schema using sql commands.', 'duration': 520.821, 'highlights': ['The primary key is a set of attributes used to uniquely identify every tuple, and it can be chosen from the candidate keys in a relation.', 'The concept of foreign key is explained, which validates the relationship between two tables and refers to the values of another attribute.', 'Detailed explanation of constraints including not null, unique, check, default, and index is provided, demonstrating their usage in database table creation.']}], 'duration': 3837.798, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9817342682.jpg', 'highlights': ['Comprehensive guide to SQL querying, including extracting entire tables and finding unique designations', 'Introduction to PostgreSQL features such as data types, data integrity, performance, reliability, security, and extensibility', 'Demonstration of creating schemas in SQL using Sequel Shell and PG Admin', 'Detailed explanation of Entity Relationship Diagram (ERD) concepts, including entities, attributes, and relationships', 'Explanation of keys in databases, including candidate key, super key, primary key, alternate key, and foreign key', 'Explanation of constraints including not null, unique, check, default, and index']}, {'end': 24064.474, 'segs': [{'end': 21346.854, 'src': 'embed', 'start': 21316.761, 'weight': 3, 'content': [{'end': 21319.644, 'text': 'So, for example, we had Alice Johnson and David Allen right?', 'start': 21316.761, 'duration': 2.883}, {'end': 21323.648, 'text': 'So, if you remember from the previous slide, we had movies rented together, right?', 'start': 21319.904, 'duration': 3.744}, {'end': 21330.235, 'text': 'So for Alice Johnson, we had Mission Impossible and Clash of Titans in the same tuple, and for David Allen we had the address divided,', 'start': 21323.688, 'duration': 6.547}, {'end': 21331.916, 'text': 'but also the movies also divided.', 'start': 21330.235, 'duration': 1.681}, {'end': 21338.463, 'text': "So what I've done over here is to get the data into 1 and F what I've done is I've divided all the movies rented into different tuples.", 'start': 21332.457, 'duration': 6.006}, {'end': 21345.053, 'text': 'So Alice Johnson, staying in first Street House number three, has basically, you know, specific movies rented, that is,', 'start': 21339.19, 'duration': 5.863}, {'end': 21346.854, 'text': 'Mission Impossible and Clash of Titans.', 'start': 21345.053, 'duration': 1.801}], 'summary': 'Data organized into tuples for alice johnson and david allen.', 'duration': 30.093, 'max_score': 21316.761, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9821316761.jpg'}, {'end': 21411.978, 'src': 'embed', 'start': 21379.151, 'weight': 2, 'content': [{'end': 21380.212, 'text': 'our address is redundant.', 'start': 21379.151, 'duration': 1.061}, {'end': 21381.653, 'text': 'And yes, it is divided also.', 'start': 21380.332, 'duration': 1.321}, {'end': 21382.753, 'text': "It's not all together.", 'start': 21381.733, 'duration': 1.02}, {'end': 21386.275, 'text': "So for that what we'll do is we'll convert this table into 2nf.", 'start': 21383.273, 'duration': 3.002}, {'end': 21389.797, 'text': 'So basically to normalize the database into 2nf.', 'start': 21386.615, 'duration': 3.182}, {'end': 21394.94, 'text': 'remember the fact that you know the database should be in 1nf and should also have a single column primary key.', 'start': 21389.797, 'duration': 5.143}, {'end': 21400.966, 'text': "What I mean by that is, you know, what I'm going to do is I'm going to divide the table into two different tables,", 'start': 21395.32, 'duration': 5.646}, {'end': 21403.509, 'text': 'such that you know for each specific user.', 'start': 21400.966, 'duration': 2.543}, {'end': 21406.152, 'text': "I'm going to identify the specific movie rented.", 'start': 21403.909, 'duration': 2.243}, {'end': 21411.978, 'text': "For example, let's say, you know, I give Alice Johnson staying in first Street House number three ID number one.", 'start': 21406.792, 'duration': 5.186}], 'summary': 'Database table will be converted into 2nf for normalization.', 'duration': 32.827, 'max_score': 21379.151, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9821379151.jpg'}, {'end': 21618.59, 'src': 'embed', 'start': 21594.879, 'weight': 7, 'content': [{'end': 21601.785, 'text': "So let's say I'll wish to enter John as first name and let's say I'll enter middle name.", 'start': 21594.879, 'duration': 6.906}, {'end': 21603.266, 'text': 'Let it be a character.', 'start': 21602.165, 'duration': 1.101}, {'end': 21605.887, 'text': "Now similarly, I'll enter the next name.", 'start': 21603.866, 'duration': 2.021}, {'end': 21608.767, 'text': "Let's say Smith that is basically my last name.", 'start': 21605.927, 'duration': 2.84}, {'end': 21611.848, 'text': 'Sorry, the comma has to go at the end now.', 'start': 21609.327, 'duration': 2.521}, {'end': 21616.069, 'text': "I'll enter SSN right after that.", 'start': 21612.428, 'duration': 3.641}, {'end': 21618.59, 'text': "What I'll do is I'll enter bird date.", 'start': 21616.129, 'duration': 2.461}], 'summary': 'Transcript: input john as first name, smith as last name, and ssn for bird date entry.', 'duration': 23.711, 'max_score': 21594.879, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9821594879.jpg'}, {'end': 22106.929, 'src': 'embed', 'start': 22079.816, 'weight': 4, 'content': [{'end': 22083.322, 'text': "So I'll just type on select star from employee so that I can see the details.", 'start': 22079.816, 'duration': 3.506}, {'end': 22086.912, 'text': "So I'll just execute this.", 'start': 22085.871, 'duration': 1.041}, {'end': 22090.895, 'text': "So when I execute this you'll see that, you know, I have all the details right now.", 'start': 22087.372, 'duration': 3.523}, {'end': 22094.098, 'text': "Let's say, you know, I want to update the salary of a person.", 'start': 22090.955, 'duration': 3.143}, {'end': 22098.482, 'text': "Let's say employee whose SSN is let's say triple nine double eight four times seven.", 'start': 22094.118, 'duration': 4.364}, {'end': 22101.765, 'text': "Now, let's say the salary is around 25, 000 right now.", 'start': 22098.882, 'duration': 2.883}, {'end': 22104.086, 'text': 'I want to update it to 27, 000.', 'start': 22101.865, 'duration': 2.221}, {'end': 22106.929, 'text': 'What can I do is basically I have to use the update statement.', 'start': 22104.087, 'duration': 2.842}], 'summary': 'Executing sql query to update employee salary from 25,000 to 27,000.', 'duration': 27.113, 'max_score': 22079.816, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9822079816.jpg'}, {'end': 22298.582, 'src': 'embed', 'start': 22266.736, 'weight': 10, 'content': [{'end': 22269.597, 'text': "So that's where basically the select query comes into picture.", 'start': 22266.736, 'duration': 2.861}, {'end': 22274.037, 'text': 'now, when I had select star from works on, that means all the data was shown.', 'start': 22269.597, 'duration': 4.44}, {'end': 22278.858, 'text': "for example, let's say, I just want the first name, last name and the address from the employee table and let's say,", 'start': 22274.037, 'duration': 4.821}, {'end': 22281.179, 'text': 'you know we are specific to Department number 5..', 'start': 22278.858, 'duration': 2.321}, {'end': 22292.081, 'text': "So what I can do is I can just type in select first name last name address and I'll mention from employee.", 'start': 22281.179, 'duration': 10.902}, {'end': 22298.582, 'text': "and let's say we mentioned Department number is equal to 5.", 'start': 22293.016, 'duration': 5.566}], 'summary': 'Using select query to retrieve specific data from the employee table, such as first name, last name, and address, for employees in department 5.', 'duration': 31.846, 'max_score': 22266.736, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9822266736.jpg'}, {'end': 22492.584, 'src': 'embed', 'start': 22466.312, 'weight': 0, 'content': [{'end': 22475.319, 'text': "Then what we can do is we can use the select statement again, right? and then I'll mention E dot F name and E dot L name.", 'start': 22466.312, 'duration': 9.007}, {'end': 22480.02, 'text': 'So this is basically to identify every employee, right? So that is basically the supervisor.', 'start': 22475.379, 'duration': 4.641}, {'end': 22490.723, 'text': "Now, if you want to identify the supervisor, what I'm going to do is I'll mention s dot F name and s dot L name, and then I'll mention from,", 'start': 22480.381, 'duration': 10.342}, {'end': 22492.584, 'text': "and then I'll mention employee.", 'start': 22490.723, 'duration': 1.861}], 'summary': 'Using select statement to identify every employee and supervisor.', 'duration': 26.272, 'max_score': 22466.312, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9822466312.jpg'}, {'end': 22663.795, 'src': 'embed', 'start': 22635.135, 'weight': 1, 'content': [{'end': 22641.316, 'text': 'We have various departments like you can see we have the research department the admin Department the headquarters Department and so on.', 'start': 22635.135, 'duration': 6.181}, {'end': 22647.698, 'text': "So that's how basically you can perform cross-product now, let's say you know, if you want to take this query further.", 'start': 22641.817, 'duration': 5.881}, {'end': 22652.628, 'text': "So let's say, you know, we want to find out all the employees who work in the department.", 'start': 22648.345, 'duration': 4.283}, {'end': 22653.849, 'text': "Let's say administration.", 'start': 22652.668, 'duration': 1.181}, {'end': 22663.795, 'text': "So what we can simply do is we'll type in select star and then I'll mention from employee Department.", 'start': 22654.089, 'duration': 9.706}], 'summary': 'The company has multiple departments including research, admin, and headquarters. they can perform cross-product queries and find employees using sql queries.', 'duration': 28.66, 'max_score': 22635.135, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9822635135.jpg'}, {'end': 23155.876, 'src': 'embed', 'start': 23120.603, 'weight': 6, 'content': [{'end': 23121.703, 'text': 'Coming to fetch.', 'start': 23120.603, 'duration': 1.1}, {'end': 23124.864, 'text': 'fetch is also one of the ways to limiting the query results, right?', 'start': 23121.703, 'duration': 3.161}, {'end': 23131.286, 'text': "So whenever you want to fetch, let's say first five rows only, or maybe let's say 10 rows only, then you can use the fetch statement.", 'start': 23124.884, 'duration': 6.402}, {'end': 23133.406, 'text': "So I'm going to show you how that's done.", 'start': 23131.746, 'duration': 1.66}, {'end': 23135.567, 'text': 'Let me just shift back to my PG admin.', 'start': 23133.506, 'duration': 2.061}, {'end': 23141.228, 'text': "And now what I'll do is I'll start with the first topic that I had discussed over here that is nested queries.", 'start': 23136.047, 'duration': 5.181}, {'end': 23148.813, 'text': "So let's write some interesting nested queries so that you get a hands-on experience on how you can integrate into a complex queries, right?", 'start': 23141.949, 'duration': 6.864}, {'end': 23155.876, 'text': "So, for example, let's say you know we want to make a list of all the project numbers for projects that involve an employee whose last name is Smith,", 'start': 23149.153, 'duration': 6.723}], 'summary': "Using 'fetch' to limit query results & demonstrating nested queries in pg admin.", 'duration': 35.273, 'max_score': 23120.603, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9823120603.jpg'}, {'end': 23601.229, 'src': 'embed', 'start': 23574.076, 'weight': 17, 'content': [{'end': 23577.759, 'text': "Sorry, and then what I'll do is I'll end with a semicolon over here.", 'start': 23574.076, 'duration': 3.683}, {'end': 23585.805, 'text': "So basically I'm generating the SSN and the first name of all the employees whose address is in Houston, Texas, right? So let me just execute it.", 'start': 23577.839, 'duration': 7.966}, {'end': 23588.667, 'text': 'So once I execute you see an error that you know where address.', 'start': 23585.925, 'duration': 2.742}, {'end': 23591.99, 'text': 'Okay, so that is basically because our spelling is incorrect over here.', 'start': 23588.767, 'duration': 3.223}, {'end': 23594.743, 'text': 'So let me just execute again.', 'start': 23593.021, 'duration': 1.722}, {'end': 23601.229, 'text': 'So once I execute, you see SSN and first name of all those employees whose address is in Houston Texas, right?', 'start': 23594.763, 'duration': 6.466}], 'summary': 'Generating ssn and first name for employees in houston, texas.', 'duration': 27.153, 'max_score': 23574.076, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9823574076.jpg'}, {'end': 23640.098, 'src': 'embed', 'start': 23610.263, 'weight': 5, 'content': [{'end': 23615.283, 'text': "Now, let's say, you know, we want to find out all those employees whose first name as let's say five characters.", 'start': 23610.263, 'duration': 5.02}, {'end': 23618.324, 'text': 'So, if you want to mention five characters, how will you do that?', 'start': 23615.783, 'duration': 2.541}, {'end': 23619.404, 'text': 'any five characters?', 'start': 23618.324, 'duration': 1.08}, {'end': 23620.144, 'text': 'not necessarily that.', 'start': 23619.404, 'duration': 0.74}, {'end': 23623.525, 'text': 'you know it has to start with F or S, or a or any other character.', 'start': 23620.144, 'duration': 3.381}, {'end': 23625.905, 'text': 'It can be any character that you wish to write.', 'start': 23623.845, 'duration': 2.06}, {'end': 23640.098, 'text': "So in that scenario let's just generate select first name and let's say, I again generate SSN from employee where first name like okay,", 'start': 23626.265, 'duration': 13.833}], 'summary': 'Query to find employees with first name of five characters, starting with f, s, or any character', 'duration': 29.835, 'max_score': 23610.263, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9823610263.jpg'}, {'end': 23752.146, 'src': 'embed', 'start': 23703.099, 'weight': 12, 'content': [{'end': 23706.825, 'text': "So, guys, that's how you can use the between operator.", 'start': 23703.099, 'duration': 3.726}, {'end': 23712.013, 'text': "Now, as I'm talking about operators, let me just show you a simple functionality of the arithmetic operator.", 'start': 23707.572, 'duration': 4.441}, {'end': 23718.055, 'text': "Also, let's say, you know we want to show the resulting salaries of each and every employee working on the product Y project.", 'start': 23712.053, 'duration': 6.002}, {'end': 23720.836, 'text': "and let's say you know it's given a 20% raise.", 'start': 23718.055, 'duration': 2.781}, {'end': 23723.316, 'text': 'so to do that, what you can do is you can write a query.', 'start': 23720.836, 'duration': 2.48}, {'end': 23727.137, 'text': "Let's say select E dot F name.", 'start': 23723.356, 'duration': 3.781}, {'end': 23737.937, 'text': 'So, if you remember, I use the similar query previously also, and then I choose E dot L name and then I mentioned 1.2, right? into.', 'start': 23727.157, 'duration': 10.78}, {'end': 23748.944, 'text': "that is basically my arithmetic operator that I'm using and I'm choosing E dot salary as, as let's say, increased salary from.", 'start': 23737.937, 'duration': 11.007}, {'end': 23752.146, 'text': "I'm choosing employee table because salary is an employee table.", 'start': 23748.944, 'duration': 3.202}], 'summary': 'Demonstrating usage of between and arithmetic operators, increasing salaries by 20%.', 'duration': 49.047, 'max_score': 23703.099, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9823703099.jpg'}], 'start': 21180.7, 'title': 'Sql database management', 'summary': 'Covers database normalization concepts including 1nf, 2nf, 3nf, and bcnf, data manipulation commands, querying data in sql, set operations, special operators, aggregate functions, fetch, nested queries, and sql operators. it also delves into the usage of aggregate functions, order by, group by, and having clauses.', 'chapters': [{'end': 21652.324, 'start': 21180.7, 'title': 'Database normalization & data manipulation', 'summary': 'Covers the concept of normalization, including 1nf, 2nf, 3nf, and bcnf, illustrating the process with an example of eliminating redundancy and dependency in database tables, followed by an introduction to data manipulation commands and an example of inserting data into a table.', 'duration': 471.624, 'highlights': ['The chapter covers the concept of normalization, including 1NF, 2NF, 3NF, and BCNF', 'Illustrating the process with an example of eliminating redundancy and dependency in database tables', 'An introduction to data manipulation commands and an example of inserting data into a table']}, {'end': 22874.951, 'start': 21653.065, 'title': 'Manipulating and querying data in sql', 'summary': 'Covers executing queries to fill data, adding foreign keys to tables, altering tables, and using select, update, delete, and distinct statements to manipulate and retrieve data. it also explains cross product, nested queries, and set operations.', 'duration': 1221.886, 'highlights': ['Adding Foreign Keys to Tables', 'Using Select, Update, and Delete Statements', 'Nested Queries and Set Operations']}, {'end': 23120.023, 'start': 22875.272, 'title': 'Set operators, special operators, and aggregate functions', 'summary': 'Covers set operations like union, intersect, and minus, special operators including between, is null, like, in, exists, all, and any, and aggregate functions such as min, max, count, average, and sum, providing an overview of their functionalities and usage.', 'duration': 244.751, 'highlights': ['The chapter covers set operations like union, intersect, and minus, special operators including between, is null, like, in, exists, all, and any, and aggregate functions such as min, max, count, average, and sum.', 'The count function is basically used to count the number of rows that match a specified criteria.', 'The min function is basically used to return the smallest value of the selected column in a table.', 'The max function is basically used to return the largest value of the selected column in a table.']}, {'end': 23414.013, 'start': 23120.603, 'title': 'Using fetch and nested queries in sql', 'summary': 'Covers using fetch to limit query results and demonstrates writing nested queries for complex scenarios, such as retrieving project numbers involving a specific employee and using the not exists operator to find employees with no dependents.', 'duration': 293.41, 'highlights': ['Using fetch to limit query results', 'Writing nested queries for complex scenarios', 'Using the not exists operator to find employees with no dependents']}, {'end': 24064.474, 'start': 23414.093, 'title': 'Using sql operators and aggregate functions', 'summary': 'Covers the usage of sql operators such as exists, not exists, like, and between, and demonstrates the use of aggregate functions like sum, min, max, and count. it also explores the application of order by, group by, and having clauses to retrieve specific data sets and how to apply them to sort and group data effectively.', 'duration': 650.381, 'highlights': ['Demonstrated the usage of exists and not exists operators to retrieve specific data sets based on employee dependents and managers with at least one dependent.', 'Explained the application of the like operator to retrieve employees based on their address and first name length, showcasing examples and their outputs.', 'Demonstrated the usage of the between operator to retrieve employees based on their salary range and showed the resulting data set.', 'Showcased the usage of arithmetic operators to calculate increased salaries and demonstrated the application of the as operator in SQL queries.', 'Explained the use of the is null operator to find employees without supervisors, providing a practical example and its output.', 'Explored the usage of aggregate functions such as sum, min, max, and count to retrieve specific data from the employee table.', 'Demonstrated the application of order by, group by, and having clauses to retrieve and organize data based on department, last name, and first name.']}], 'duration': 2883.774, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9821180700.jpg', 'highlights': ['The chapter covers the concept of normalization, including 1NF, 2NF, 3NF, and BCNF', 'The chapter covers set operations like union, intersect, and minus, special operators including between, is null, like, in, exists, all, and any, and aggregate functions such as min, max, count, average, and sum', 'Demonstrated the usage of exists and not exists operators to retrieve specific data sets based on employee dependents and managers with at least one dependent', 'Explained the application of the like operator to retrieve employees based on their address and first name length, showcasing examples and their outputs', 'Illustrating the process with an example of eliminating redundancy and dependency in database tables', 'Using fetch to limit query results', 'Writing nested queries for complex scenarios', 'Using the not exists operator to find employees with no dependents', 'An introduction to data manipulation commands and an example of inserting data into a table', 'Using Select, Update, and Delete Statements', 'Adding Foreign Keys to Tables', 'Explained the use of the is null operator to find employees without supervisors, providing a practical example and its output', 'Explored the usage of aggregate functions such as sum, min, max, and count to retrieve specific data from the employee table', 'Demonstrated the usage of the between operator to retrieve employees based on their salary range and showed the resulting data set', 'Showcased the usage of arithmetic operators to calculate increased salaries and demonstrated the application of the as operator in SQL queries', 'Demonstrated the application of order by, group by, and having clauses to retrieve and organize data based on department, last name, and first name', 'The count function is basically used to count the number of rows that match a specified criteria', 'The min function is basically used to return the smallest value of the selected column in a table', 'The max function is basically used to return the largest value of the selected column in a table']}, {'end': 25650.327, 'segs': [{'end': 24269.997, 'src': 'embed', 'start': 24237.924, 'weight': 0, 'content': [{'end': 24242.446, 'text': 'Our main condition is that you know, for each project, that the employee count is more than three, right?', 'start': 24237.924, 'duration': 4.522}, {'end': 24249.769, 'text': "So for that what I'm going to do is I'm going to mention the condition select P number, that is because of the project number,", 'start': 24242.846, 'duration': 6.923}, {'end': 24252.77, 'text': "and then let's say I want to count the employees right?", 'start': 24249.769, 'duration': 3.001}, {'end': 24269.997, 'text': "So I'll mention count star from project works on where P number is equal to P number, and then I group by for each project, right?", 'start': 24253.35, 'duration': 16.647}], 'summary': 'The condition requires employee count of more than three for each project.', 'duration': 32.073, 'max_score': 24237.924, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9824237924.jpg'}, {'end': 24484.425, 'src': 'embed', 'start': 24452.686, 'weight': 1, 'content': [{'end': 24457.928, 'text': 'and then, when we used offset 5 and then limit 3, we saw the next 3 rows.', 'start': 24452.686, 'duration': 5.242}, {'end': 24463.951, 'text': "and now, if you're using the fetch command, where we say fetch first 8 rows only, you'll see that you know we fetch all the 8 rows right?", 'start': 24457.928, 'duration': 6.023}, {'end': 24468.173, 'text': "So that's how, guys, basically you can use the limit offset and the fetch commands.", 'start': 24464.431, 'duration': 3.742}, {'end': 24474.14, 'text': "So that was about till now now, let's go back to our PPT and next let's look into joints in SQL.", 'start': 24468.777, 'duration': 5.363}, {'end': 24477.281, 'text': 'So all of you might be knowing what are joints right now.', 'start': 24474.46, 'duration': 2.821}, {'end': 24484.425, 'text': 'joints in SQL are commands which are basically used to combine rows from two or more tables based on a related column between those two tables.', 'start': 24477.281, 'duration': 7.144}], 'summary': 'Using limit offset and fetch commands in sql, can retrieve specific rows and understand joints for combining tables.', 'duration': 31.739, 'max_score': 24452.686, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9824452686.jpg'}, {'end': 24540.12, 'src': 'embed', 'start': 24520.57, 'weight': 2, 'content': [{'end': 24532.896, 'text': 'the left joint returns records from the left table and also those records would satisfy the condition from the right table the right joint returns records from the right table and also those records would satisfy the condition from the left table.', 'start': 24520.57, 'duration': 12.326}, {'end': 24535.858, 'text': 'and finally, coming to full joint, the full joint returns,', 'start': 24532.896, 'duration': 2.962}, {'end': 24540.12, 'text': 'all those records which either have a matching value in the left table or on the right table.', 'start': 24535.858, 'duration': 4.262}], 'summary': 'Joins return records based on conditions from left and right tables.', 'duration': 19.55, 'max_score': 24520.57, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9824520570.jpg'}, {'end': 24752.79, 'src': 'embed', 'start': 24723.912, 'weight': 3, 'content': [{'end': 24728.375, 'text': 'So, for instance, if you consider the example on my screen, I have two tables right now, for example.', 'start': 24723.912, 'duration': 4.463}, {'end': 24728.695, 'text': "let's say,", 'start': 24728.375, 'duration': 0.32}, {'end': 24735.439, 'text': 'I want to create a new view which will basically have few columns or rows from a specific table and few columns or rows from the other table right?', 'start': 24728.695, 'duration': 6.744}, {'end': 24737.06, 'text': 'So that is basically my view.', 'start': 24735.719, 'duration': 1.341}, {'end': 24742.323, 'text': 'So now views are used in Industries to basically collaborate with the data of many tables,', 'start': 24737.641, 'duration': 4.682}, {'end': 24745.385, 'text': 'so that the company can understand how to go about the next action step.', 'start': 24742.323, 'duration': 3.062}, {'end': 24748.167, 'text': 'They should take while taking any specific decision.', 'start': 24745.425, 'duration': 2.742}, {'end': 24752.79, 'text': "So without wasting many any further time, let's take a look at how we can create a view.", 'start': 24748.627, 'duration': 4.163}], 'summary': 'Views help industries collaborate data from multiple tables to make informed decisions.', 'duration': 28.878, 'max_score': 24723.912, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9824723912.jpg'}], 'start': 24064.494, 'title': 'Sql in database management', 'summary': 'Covers sql order by clause for sorting data, group by clause for data aggregation, and having clause for filtering; it explains retrieving project data and using group by, order by, and having clauses, along with an introduction to joins, views, and procedures; it also includes creating stored procedures in sql and implementing triggers, and the process of creating a trigger and granting privileges.', 'chapters': [{'end': 24219.392, 'start': 24064.494, 'title': 'Sql order by, group by, and having clauses', 'summary': 'Covers the usage of sql order by clause to sort data in ascending and descending order, illustrated by examples of ordering names and salaries; it then explains the group by clause with an example of retrieving department numbers and the number of employees in each department, and concludes with an introduction to the having clause.', 'duration': 154.898, 'highlights': ['The chapter covers the usage of SQL order by clause to sort data in ascending and descending order.', 'The chapter explains the group by clause with an example of retrieving department numbers and the number of employees in each department.', 'The chapter concludes with an introduction to the having clause.']}, {'end': 25031.299, 'start': 24219.733, 'title': 'Retrieving project data and using group by, order by, and having clauses in sql', 'summary': 'Explains using sql to retrieve project data where more than three employees work, using group by, order by, and having clauses to filter and count the data, and demonstrates the use of limit, offset, and fetch commands, along with an introduction to sql joins, views, and procedures.', 'duration': 811.566, 'highlights': ['The chapter explains using SQL to retrieve project data where more than three employees work.', 'The chapter demonstrates the use of group by, order by, and having clauses to filter and count the data.', 'The chapter demonstrates the use of limit, offset, and fetch commands in SQL.', 'The chapter introduces SQL joins, views, and procedures.']}, {'end': 25307.627, 'start': 25031.339, 'title': 'Creating stored procedures and implementing triggers', 'summary': 'Covers the creation of a stored procedure using sql to insert values into tables, execute the procedure, and verify the inserted values, followed by an introduction to implementing triggers in postgresql to execute sql statements when events occur in the database table.', 'duration': 276.288, 'highlights': ['The process of creating a stored procedure involves mentioning the values to be inserted in different tables, executing the procedure, and verifying the successful insertion by using select statements.', 'The values inserted into the tables can be verified using select statements, demonstrating the successful implementation of the stored procedure.', 'An introduction to implementing triggers in PostgreSQL, where triggers are sets of SQL statements stored in the database catalog and are executed before or after an event associated with a table occurs, such as insert, update, or delete statements.', 'The process of implementing triggers involves creating a table, defining its attributes, and then creating a function to return a trigger, which will be invoked when inserting data into the table.']}, {'end': 25650.327, 'start': 25307.627, 'title': 'Creating trigger and granting privileges', 'summary': 'Explains the process of creating a trigger to log entries in the audit table when inserting into the employee table, including resolving errors and granting public select privileges on a table.', 'duration': 342.7, 'highlights': ['Creating a trigger to log entries in the audit table when inserting into the employee table', 'Resolving errors while executing the SQL statements', 'Granting public select privileges on a table']}], 'duration': 1585.833, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9824064494.jpg', 'highlights': ['The chapter covers the usage of SQL order by clause to sort data in ascending and descending order.', 'The chapter explains using SQL to retrieve project data where more than three employees work.', 'The process of creating a stored procedure involves mentioning the values to be inserted in different tables, executing the procedure, and verifying the successful insertion by using select statements.', 'Creating a trigger to log entries in the audit table when inserting into the employee table']}, {'end': 26754.639, 'segs': [{'end': 26518.347, 'src': 'embed', 'start': 26485.54, 'weight': 6, 'content': [{'end': 26491.462, 'text': 'So you just have to develop once and deploy anywhere, with their consistent experience from on-premise to cloud.', 'start': 26485.54, 'duration': 5.922}, {'end': 26494.624, 'text': 'right, not only this, but they make sure that you know you as a user,', 'start': 26491.462, 'duration': 3.162}, {'end': 26500.106, 'text': 'get a consistent experience from on-premise to the cloud and also provide easy to use tools and the connectors.', 'start': 26494.624, 'duration': 5.482}, {'end': 26509.525, 'text': 'So you can use the Azure Active Directory and the SQL Server Management Studio to manage your database infrastructure across on-premise SQL Server and also Microsoft Azure.', 'start': 26500.763, 'duration': 8.762}, {'end': 26512.906, 'text': "So isn't that great guys now moving on to the next feature.", 'start': 26509.885, 'duration': 3.021}, {'end': 26514.346, 'text': 'The next feature is availability.', 'start': 26512.946, 'duration': 1.4}, {'end': 26518.347, 'text': 'So when I say availability again, what I mean by that is that you know SQL Server.', 'start': 26514.626, 'duration': 3.721}], 'summary': 'Develop once, deploy anywhere with consistent experience across on-premise and cloud, using azure tools and connectors.', 'duration': 32.807, 'max_score': 26485.54, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9826485540.jpg'}, {'end': 26603.787, 'src': 'embed', 'start': 26570.586, 'weight': 5, 'content': [{'end': 26577.112, 'text': 'So when I say analytics, what I mean by that is that you know SQL Server provides the built-in analytics or you can say the built-in database,', 'start': 26570.586, 'duration': 6.526}, {'end': 26578.113, 'text': 'Advanced analytics.', 'start': 26577.112, 'duration': 1.001}, {'end': 26583.834, 'text': 'So with the help of the built-in analytics and the sequel server machine learning services using R and python,', 'start': 26578.513, 'duration': 5.321}, {'end': 26590.456, 'text': 'you can move beyond the reactive and into predictive and prescriptive analytics by performing advanced analytics directly within the database.', 'start': 26583.834, 'duration': 6.622}, {'end': 26597.218, 'text': "So by using multi-threading and massively parallel processing you'll gain insights faster than using open-source R and python.", 'start': 26590.836, 'duration': 6.382}, {'end': 26601.206, 'text': 'So, guys, that is one major plus point of sequel server.', 'start': 26597.825, 'duration': 3.381}, {'end': 26603.787, 'text': 'now moving on to the next feature, that the security.', 'start': 26601.206, 'duration': 2.581}], 'summary': 'Sql server offers built-in analytics and machine learning for advanced predictive and prescriptive analytics, providing faster insights than open-source tools.', 'duration': 33.201, 'max_score': 26570.586, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9826570586.jpg'}, {'end': 26671.93, 'src': 'embed', 'start': 26636.571, 'weight': 0, 'content': [{'end': 26647.32, 'text': 'So sequel server provides you with this feature and allows you to build comprehensive Enterprise scale analytic solutions by benefiting from the fast performance of in-memory built into the tabular model.', 'start': 26636.571, 'duration': 10.749}, {'end': 26653.966, 'text': 'So basically what happens is that unit reduces the time to insights using direct querying against tabular and multidimensional models.', 'start': 26647.64, 'duration': 6.326}, {'end': 26656.789, 'text': 'So guys that were the features of sequel servers.', 'start': 26654.466, 'duration': 2.323}, {'end': 26660.014, 'text': 'I hope that you have understood what is sequel server and its features.', 'start': 26656.89, 'duration': 3.124}, {'end': 26667.083, 'text': "So now that you know the basics of database management system sequel sequel server, let's move forward and install sequel server.", 'start': 26660.454, 'duration': 6.629}, {'end': 26671.93, 'text': 'So to install sequel server what you have to do is you have to follow the steps that you can see on my screen.', 'start': 26667.404, 'duration': 4.526}], 'summary': 'Sequel server enables comprehensive analytic solutions with in-memory tabular model for fast performance and reduced time to insights.', 'duration': 35.359, 'max_score': 26636.571, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9826636571.jpg'}], 'start': 25650.547, 'title': 'Database privileges and sequel server features', 'summary': 'Covers granting insert privileges on the employee table, controlling privileges and rights using revoke and transaction control language commands, and explains sequel server features including ssas, ssrs, and ssis, installation process, and key components.', 'chapters': [{'end': 25694.944, 'start': 25650.547, 'title': 'Granting insert privileges on employee table', 'summary': 'Demonstrates granting insert privileges on the employee table, allowing anyone to insert into the table, and illustrates how to check the granted privileges in the database properties.', 'duration': 44.397, 'highlights': ['The chapter illustrates granting insert privileges on the employee table, allowing anyone to insert into the table.', "The demonstration includes the process of checking the granted privileges in the database properties, showcasing the 'public' group and its privileges.", "The process of granting privileges is exemplified through the command 'Grant insert on employee to public,' which successfully returns the query output."]}, {'end': 26431.225, 'start': 25694.984, 'title': 'Control privileges and rights in databases', 'summary': 'Covers controlling privileges and rights for databases and tables using revoke and transaction control language commands, exporting and importing data, and using the uuid data type as a primary key. it also provides an overview of database management system (dbms) and its types, and explains the basics of structured query language (sql) and microsoft sql server.', 'duration': 736.241, 'highlights': ['The chapter covers controlling privileges and rights for databases and tables using revoke and transaction control language commands', 'Exporting and importing data and using the UUID data type as a primary key are also covered', 'Provides an overview of database management system (DBMS) and its types, and explains the basics of structured query language (SQL) and Microsoft SQL Server']}, {'end': 26754.639, 'start': 26431.605, 'title': 'Sequel server features and installation', 'summary': 'Covers the components of sequel server (ssas, ssrs, and ssis), features such as user experience, availability, performance, analytics, security, and business intelligence, as well as the installation process for sequel server, including downloading and initial setup.', 'duration': 323.034, 'highlights': ['Sequel Server provides high availability and disaster recovery, including fast failover and easy setup, with unified solutions for high availability and disaster recovery on Linux and Windows.', 'Sequel Server allows scaling of price and performance in a parallel manner, ensuring real-world application performance benchmarks are achieved and providing high performance for applications and data warehouses.', 'Sequel Server provides built-in analytics and advanced analytics capabilities, including predictive and prescriptive analytics, with faster insights gained through multi-threading and massively parallel processing.', 'Sequel Server ensures data protection with less vulnerability to major attacks, offering levels of security and compliance for databases.', 'Sequel Server provides business intelligence features for comprehensive enterprise-scale analytic solutions, with fast performance of in-memory built into the tabular model and reduced time to insights using direct querying against tabular and multidimensional models.']}], 'duration': 1104.092, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9825650547.jpg', 'highlights': ['Sequel Server provides high availability and disaster recovery, including fast failover and easy setup, with unified solutions for high availability and disaster recovery on Linux and Windows.', 'Sequel Server allows scaling of price and performance in a parallel manner, ensuring real-world application performance benchmarks are achieved and providing high performance for applications and data warehouses.', 'Sequel Server provides built-in analytics and advanced analytics capabilities, including predictive and prescriptive analytics, with faster insights gained through multi-threading and massively parallel processing.', 'Sequel Server ensures data protection with less vulnerability to major attacks, offering levels of security and compliance for databases.', 'Sequel Server provides business intelligence features for comprehensive enterprise-scale analytic solutions, with fast performance of in-memory built into the tabular model and reduced time to insights using direct querying against tabular and multidimensional models.', 'The chapter covers controlling privileges and rights for databases and tables using revoke and transaction control language commands', 'Exporting and importing data and using the UUID data type as a primary key are also covered', 'The chapter illustrates granting insert privileges on the employee table, allowing anyone to insert into the table.', "The demonstration includes the process of checking the granted privileges in the database properties, showcasing the 'public' group and its privileges.", "The process of granting privileges is exemplified through the command 'Grant insert on employee to public,' which successfully returns the query output.", 'Provides an overview of database management system (DBMS) and its types, and explains the basics of structured query language (SQL) and Microsoft SQL Server']}, {'end': 27442.731, 'segs': [{'end': 26846.004, 'src': 'embed', 'start': 26818.979, 'weight': 2, 'content': [{'end': 26823.562, 'text': 'So what you can do is you can just go through the license terms and then you can just click on accept button.', 'start': 26818.979, 'duration': 4.583}, {'end': 26825.664, 'text': "So I'll just directly click on the accept button.", 'start': 26823.822, 'duration': 1.842}, {'end': 26831.388, 'text': 'And once I click on the accept button, you have to next specify the SQL Server installation directory right?', 'start': 26826.064, 'duration': 5.324}, {'end': 26833.849, 'text': 'So that means where you want to install your SQL Server.', 'start': 26831.408, 'duration': 2.441}, {'end': 26837.292, 'text': "So I'll just let it be as it is and then I'll click on install.", 'start': 26834.21, 'duration': 3.082}, {'end': 26843.643, 'text': 'Now once you click on install you will see that all the required packages and the libraries are getting downloaded.', 'start': 26838.78, 'duration': 4.863}, {'end': 26846.004, 'text': "Now, let's just wait for the installation to complete.", 'start': 26843.963, 'duration': 2.041}], 'summary': 'Accept license terms, specify installation directory, click install, wait for completion.', 'duration': 27.025, 'max_score': 26818.979, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9826818979.jpg'}, {'end': 26940.594, 'src': 'embed', 'start': 26915.06, 'weight': 0, 'content': [{'end': 26920.903, 'text': 'Now, in the next visit that opens up, you have to choose basically the type of Edition that you are installed initially, that is,', 'start': 26915.06, 'duration': 5.843}, {'end': 26922.444, 'text': 'the developer Edition or the Enterprise.', 'start': 26920.903, 'duration': 1.541}, {'end': 26928.187, 'text': "So since I downloaded the developer Edition, I'll just choose developer and then what I'll do is I'll click on next.", 'start': 26922.884, 'duration': 5.303}, {'end': 26933.77, 'text': 'Now. after that, you have to read this license terms and then just click on this radio button of.', 'start': 26929.728, 'duration': 4.042}, {'end': 26936.592, 'text': 'I accept the license terms and then click on next.', 'start': 26933.77, 'duration': 2.822}, {'end': 26940.594, 'text': 'now, in the next visit that opens up, you have to choose the features which you wish to install.', 'start': 26936.592, 'duration': 4.002}], 'summary': 'Choose developer edition during installation and select desired features.', 'duration': 25.534, 'max_score': 26915.06, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9826915060.jpg'}, {'end': 27213.53, 'src': 'embed', 'start': 27185.75, 'weight': 1, 'content': [{'end': 27191.074, 'text': 'This is where the sequel services are installed and the database resides moving on to relational engine.', 'start': 27185.75, 'duration': 5.324}, {'end': 27197.419, 'text': 'This contains the query parser optimizer and the executor and the execution happens in the relational engine.', 'start': 27191.374, 'duration': 6.045}, {'end': 27199.2, 'text': 'Moving on to command parcel.', 'start': 27197.859, 'duration': 1.341}, {'end': 27205.244, 'text': 'Well, this checks the syntax of the query and converts the query to the machine language moving forward with optimizer.', 'start': 27199.44, 'duration': 5.804}, {'end': 27213.53, 'text': 'This prepares the execution plan as the output, by taking statistics query and the algebra tree as input, moving forward with the next component,', 'start': 27205.485, 'duration': 8.045}], 'summary': 'Sequel services are installed with a query parser, optimizer, and executor in the relational engine, and the optimizer prepares the execution plan as the output.', 'duration': 27.78, 'max_score': 27185.75, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9827185750.jpg'}], 'start': 26754.639, 'title': 'Sql server installation and architecture', 'summary': 'Covers the sql server installation process, including basic and custom types, configurations, and steps. it also explains installing sql server 2017, choosing installation types, configuring database features, and providing an overview of sql server architecture and its functionalities.', 'chapters': [{'end': 26858.171, 'start': 26754.639, 'title': 'Sql server installation process', 'summary': 'Explains the sql server installation process, detailing the basic and custom installation types, their respective configurations, and the steps involved in the installation, including accepting license terms and specifying the installation directory.', 'duration': 103.532, 'highlights': ['The basic and custom installation types are explained, with a recommendation to choose the basic type for local machine usage and the custom type for enterprise-level usage.', 'The process of accepting the SQL Server license terms and specifying the installation directory is described.', 'The installation process involves downloading required packages and libraries and may require a computer restart upon completion.']}, {'end': 27442.731, 'start': 26858.171, 'title': 'Installing sql server 2017 and understanding sql server architecture', 'summary': 'Details the step-by-step process of installing sql server 2017, including choosing the installation type and configuring database features, and provides an overview of sql server architecture, explaining its components and functionalities.', 'duration': 584.56, 'highlights': ['The installation process for SQL Server 2017 is described, including choosing the installation type and configuring database features.', 'An overview of SQL Server architecture is provided, explaining its components such as the server, relational engine, command parser, optimizer, query executor, and storage engine.', 'Explanation of SQL Server data types is given, covering exact numeric data types, approximate numeric data types, date and time values, character strings, Unicode character strings, and binary strings.', "The chapter demonstrates the process of using data definition language commands by creating a database using the 'create database' command."]}], 'duration': 688.092, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9826754639.jpg', 'highlights': ['The basic and custom installation types are explained, with a recommendation to choose the basic type for local machine usage and the custom type for enterprise-level usage.', 'The installation process for SQL Server 2017 is described, including choosing the installation type and configuring database features.', 'An overview of SQL Server architecture is provided, explaining its components such as the server, relational engine, command parser, optimizer, query executor, and storage engine.', 'The process of accepting the SQL Server license terms and specifying the installation directory is described.', 'Explanation of SQL Server data types is given, covering exact numeric data types, approximate numeric data types, date and time values, character strings, Unicode character strings, and binary strings.']}, {'end': 28313.056, 'segs': [{'end': 27687.428, 'src': 'embed', 'start': 27660.336, 'weight': 0, 'content': [{'end': 27664.017, 'text': "So what you'll do is you'll just mention alter table, then you'll mention the tables,", 'start': 27660.336, 'duration': 3.681}, {'end': 27668.532, 'text': "name that the students info and then you'll mention add blood group.", 'start': 27664.017, 'duration': 4.515}, {'end': 27677.68, 'text': "So that is basically a column name that we want to add, and then we'll mention varchar of 8000 right, and then I'll click on execute.", 'start': 27668.552, 'duration': 9.128}, {'end': 27684.846, 'text': 'once I click on execute and go to tables, and then, once we go to this particular table and go to columns on the left hand side,', 'start': 27677.68, 'duration': 7.166}, {'end': 27687.428, 'text': 'you clearly see that our blood group column has been added.', 'start': 27684.846, 'duration': 2.582}], 'summary': 'Add blood group column to students info table with varchar of 8000 and execute.', 'duration': 27.092, 'max_score': 27660.336, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9827660336.jpg'}, {'end': 27866.428, 'src': 'embed', 'start': 27825.46, 'weight': 1, 'content': [{'end': 27828.543, 'text': "and then let's say, I mentioned few values right?", 'start': 27825.46, 'duration': 3.083}, {'end': 27829.404, 'text': "So I'll just mention.", 'start': 27828.583, 'duration': 0.821}, {'end': 27839.695, 'text': 'So I just execute this particular statement or it says column name or number of does not match the table definition.', 'start': 27833.048, 'duration': 6.647}, {'end': 27843.836, 'text': 'We see this error because you know, I forgot to drop the column of DOB.', 'start': 27840.774, 'duration': 3.062}, {'end': 27845.457, 'text': "So I'll just drop the column.", 'start': 27844.156, 'duration': 1.301}, {'end': 27846.477, 'text': 'So to drop the column.', 'start': 27845.557, 'duration': 0.92}, {'end': 27850.839, 'text': "I'll just mention all the table drop column and then I'll mention DOB right?", 'start': 27846.497, 'duration': 4.342}, {'end': 27856.382, 'text': "So let me just copy this particular section from here and then I'll execute the statement.", 'start': 27850.92, 'duration': 5.462}, {'end': 27862.126, 'text': "and once I execute the statement, I'll execute the statement again and then you will see that you know our values have been inserted.", 'start': 27856.382, 'duration': 5.744}, {'end': 27863.226, 'text': 'now to truncate the table.', 'start': 27862.126, 'duration': 1.1}, {'end': 27866.428, 'text': 'What you have to do is you basically have to use the syntax of truncate.', 'start': 27863.286, 'duration': 3.142}], 'summary': 'Error due to column mismatch, dropped dob column, inserted values, truncated table', 'duration': 40.968, 'max_score': 27825.46, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9827825460.jpg'}, {'end': 27925.013, 'src': 'embed', 'start': 27899.936, 'weight': 2, 'content': [{'end': 27914.661, 'text': "but in sequel server you have to use SP underscore rename and then you'll mention Students one that is basically a database name and then you'll mention students info and now you'll mention what you want to change it to right?", 'start': 27899.936, 'duration': 14.725}, {'end': 27918.606, 'text': "Let's say we want to change it to info students right?", 'start': 27915.062, 'duration': 3.544}, {'end': 27921.008, 'text': "And then let's just execute.", 'start': 27918.846, 'duration': 2.162}, {'end': 27925.013, 'text': "Okay, it says it cannot find so I'll just remove this particular part.", 'start': 27921.028, 'duration': 3.985}], 'summary': 'In sql server, rename sp to change database name from students to students info and execute.', 'duration': 25.077, 'max_score': 27899.936, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9827899936.jpg'}], 'start': 27443.192, 'title': 'Database management and ddl commands', 'summary': 'Covers creation, manipulation, and execution of ddl commands for databases and tables, including database and table creation, alteration, dropping, and truncation. it also explains database keys and constraints such as unique, primary, and foreign keys, along with not null, check, default, and index constraints.', 'chapters': [{'end': 27825.46, 'start': 27443.192, 'title': 'Database management with ddl commands', 'summary': 'Covers the creation and manipulation of databases and tables using ddl commands, including creating a database and table, dropping a table and database, altering a table by adding and dropping columns, changing data types, and truncating a table.', 'duration': 382.268, 'highlights': ['Creating a database and table using DDL commands', 'Dropping a table and database', 'Altering a table by adding and dropping columns, and changing data types', 'Truncating a table to remove its data']}, {'end': 28313.056, 'start': 27825.46, 'title': 'Ddl commands and database keys', 'summary': 'Covers the execution of ddl commands for dropping and truncating columns, renaming tables, and refreshing databases, along with a detailed explanation of database keys and constraints, including unique, primary, and foreign keys, and constraints like not null, unique, check, default, and index.', 'duration': 487.596, 'highlights': ['The chapter covers the execution of DDL commands for dropping and truncating columns, renaming tables, and refreshing databases.', 'A detailed explanation of database keys and constraints, including unique, primary, and foreign keys, and constraints like not null, unique, check, default, and index is provided.']}], 'duration': 869.864, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9827443192.jpg', 'highlights': ['Detailed explanation of database keys and constraints', 'Creation of a database and table using DDL commands', 'Altering a table by adding and dropping columns', 'Dropping a table and database', 'Truncating a table to remove its data', 'Execution of DDL commands for dropping and truncating columns', 'Changing data types of a table', 'Renaming tables and refreshing databases']}, {'end': 30170.876, 'segs': [{'end': 28543.941, 'src': 'embed', 'start': 28509.948, 'weight': 7, 'content': [{'end': 28515.734, 'text': 'the conflict occurred in the databases because you know the column country has to be India right, and then we have mentioned Germany.', 'start': 28509.948, 'duration': 5.786}, {'end': 28520.559, 'text': 'So now what you can just do is you can just go back change the country to India and then you can insert the value.', 'start': 28516.114, 'duration': 4.445}, {'end': 28523.321, 'text': "So that's how you can use the check constant on the create table.", 'start': 28520.999, 'duration': 2.322}, {'end': 28528.366, 'text': "Now let's say you want to use the check constraint on the alter table now similar to that of the unique constraint.", 'start': 28523.662, 'duration': 4.704}, {'end': 28543.941, 'text': "what you'll do is you'll just mention alter table and then you'll mention the tables name and then what you'll do is you'll just mention add check and in brackets you'll mention country equal to India and then you'll execute.", 'start': 28528.366, 'duration': 15.575}], 'summary': 'Conflicting database entries resolved by changing country to india using check constraint.', 'duration': 33.993, 'max_score': 28509.948, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9828509948.jpg'}, {'end': 28734.097, 'src': 'embed', 'start': 28702.506, 'weight': 3, 'content': [{'end': 28704.226, 'text': "Right and then I'll execute again.", 'start': 28702.506, 'duration': 1.72}, {'end': 28708.848, 'text': "Once I execute you can see that, you know, it's clearly working now to drop the default constraint.", 'start': 28704.347, 'duration': 4.501}, {'end': 28710.108, 'text': "Also, it's very simple.", 'start': 28708.988, 'duration': 1.12}, {'end': 28717.27, 'text': "You'll right click over here and just choose delete, and then, once you choose delete, you can just choose the object name and click on OK, right?", 'start': 28710.408, 'duration': 6.862}, {'end': 28721.191, 'text': "So once you click on OK, you'll automatically see that you know your default constraint has been dropped.", 'start': 28717.33, 'duration': 3.861}, {'end': 28725.194, 'text': 'Now one more way to drop the default constant is by using the alter table.', 'start': 28721.852, 'duration': 3.342}, {'end': 28731.396, 'text': "You just have to mention alter table students info and then mention drop constraint, and then you'll just execute this particular statement right?", 'start': 28725.474, 'duration': 5.922}, {'end': 28734.097, 'text': "So that's how, basically, you can drop the constraint, guys.", 'start': 28731.736, 'duration': 2.361}], 'summary': 'Demonstrates two methods to drop default constraint in sql, providing step-by-step instructions for each.', 'duration': 31.591, 'max_score': 28702.506, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9828702506.jpg'}, {'end': 29144.943, 'src': 'embed', 'start': 29116.872, 'weight': 4, 'content': [{'end': 29125.692, 'text': "We mentioned let's say first person with name Rehan and let's say the marks to be 87.", 'start': 29116.872, 'duration': 8.82}, {'end': 29133.156, 'text': "Similarly, let's just mention two more records, right? So I'll just copy paste right? I'll just change this to two three.", 'start': 29125.692, 'duration': 7.464}, {'end': 29135.357, 'text': 'Let me change the names and the marks.', 'start': 29133.817, 'duration': 1.54}, {'end': 29141.461, 'text': "Now similarly, I'll just insert it to the target table also.", 'start': 29139.1, 'duration': 2.361}, {'end': 29144.943, 'text': "So I'll just change the table name to be sample target table.", 'start': 29142.181, 'duration': 2.762}], 'summary': 'Transcript contains data entry for three people with marks.', 'duration': 28.071, 'max_score': 29116.872, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9829116872.jpg'}, {'end': 29242.235, 'src': 'embed', 'start': 29197.651, 'weight': 2, 'content': [{'end': 29206.995, 'text': 'That is basically a condition must match our source student ID, right? So we mentioned source dot student ID.', 'start': 29197.651, 'duration': 9.344}, {'end': 29224.422, 'text': "So when it matches what you want to do is when matched and target dot student, let's say name is, you know, same as source dot student name.", 'start': 29207.859, 'duration': 16.563}, {'end': 29242.235, 'text': "Right or Target dot you want marks is same as let's say source dot marks, right? Then we want it to update set.", 'start': 29225.683, 'duration': 16.552}], 'summary': 'Matching condition for updating student data based on student id and specific fields.', 'duration': 44.584, 'max_score': 29197.651, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9829197651.jpg'}, {'end': 29511.401, 'src': 'embed', 'start': 29487.632, 'weight': 1, 'content': [{'end': 29498.254, 'text': "So for that will mention select star from students info and then we'll mention order by parent name right and over here.", 'start': 29487.632, 'duration': 10.622}, {'end': 29500.995, 'text': 'If you do not mention anything, it will be ascending order by default.', 'start': 29498.294, 'duration': 2.701}, {'end': 29503.096, 'text': "But let's say, if you want it in a descending order,", 'start': 29501.315, 'duration': 1.781}, {'end': 29511.401, 'text': "you'll just mention the keyword descending right and then you'll click on execute and you can clearly see that you know our students are sorted by the parents name.", 'start': 29503.096, 'duration': 8.305}], 'summary': 'Query sorts students by parent name in ascending or descending order.', 'duration': 23.769, 'max_score': 29487.632, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9829487632.jpg'}, {'end': 30052.162, 'src': 'embed', 'start': 30025.529, 'weight': 8, 'content': [{'end': 30031.693, 'text': 'So, when you executed the cube statement, what you observe was that you know the super aggregated value that was basically the subtotal,', 'start': 30025.529, 'duration': 6.164}, {'end': 30032.734, 'text': 'came in the first row.', 'start': 30031.693, 'duration': 1.041}, {'end': 30036.436, 'text': 'But when you execute the role of statement, you see that you know, it comes in the last row.', 'start': 30033.034, 'duration': 3.402}, {'end': 30039.337, 'text': 'So that was about the cube and the roll-up guys.', 'start': 30036.876, 'duration': 2.461}, {'end': 30043.239, 'text': 'now moving on to the next DML command, that is the offset clause.', 'start': 30039.337, 'duration': 3.902}, {'end': 30048.321, 'text': 'the offset clause is used with the select and order by statement, to retrieve a range of records.', 'start': 30043.239, 'duration': 5.082}, {'end': 30052.162, 'text': 'It must be used with the order by clause since it cannot be used on its own.', 'start': 30048.901, 'duration': 3.261}], 'summary': 'Cube statement shows subtotal in first row, roll-up shows it in last row. offset clause retrieves range of records with select and order by, must be used with order by clause.', 'duration': 26.633, 'max_score': 30025.529, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830025529.jpg'}, {'end': 30110.004, 'src': 'embed', 'start': 30084.211, 'weight': 0, 'content': [{'end': 30090.966, 'text': "So initially I'll just execute this particular statement and after that what I'll do is I'll just execute The statement.", 'start': 30084.211, 'duration': 6.755}, {'end': 30094.232, 'text': 'before that I forgot to mention values over here, right?', 'start': 30090.966, 'duration': 3.266}, {'end': 30098.539, 'text': "So I'll mention values and execute this and similarly I'll execute the other statements.", 'start': 30094.252, 'duration': 4.287}, {'end': 30103.728, 'text': "So let me just copy paste and I'll just change the values over here.", 'start': 30098.759, 'duration': 4.969}, {'end': 30110.004, 'text': "and then I execute again now after that what I'll do is I'll use the offset statement.", 'start': 30105.638, 'duration': 4.366}], 'summary': 'Executing statements with values and using offset.', 'duration': 25.793, 'max_score': 30084.211, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830084211.jpg'}], 'start': 28313.056, 'title': 'Sql constraints, manipulation, merge, and order by', 'summary': 'Covers adding unique and check constraints, manipulating constraints and data, understanding the merge statement, using order by, group by, and grouping sets, having clause, table backup, select statement, cube and roll-up, offset and fetch clause in sql, with practical examples and sql syntax explanations.', 'chapters': [{'end': 28701.773, 'start': 28313.056, 'title': 'Adding unique and check constraints in sql', 'summary': 'Discusses how to add unique and check constraints in sql, including adding unique constraints on multiple columns, altering tables to add unique constraints, dropping constraints, using check constraints for creating and altering tables, and adding default constraints with examples and sql syntax.', 'duration': 388.717, 'highlights': ['Adding unique constraints on multiple columns', 'Adding check constraints for creating and altering tables', 'Adding default constraints with examples and SQL syntax']}, {'end': 29008.136, 'start': 28702.506, 'title': 'Manipulating constraints and data in sql', 'summary': 'Covers dropping default constraints and creating index constraints in sql, followed by an overview of data manipulation language commands and their practical application, including the use, insert, update, and delete commands.', 'duration': 305.63, 'highlights': ['Creating index constraints for fast retrieval of database using create index command', 'Updating data using the update command to modify specific values in the database', 'Deleting data using the delete command based on specific conditions']}, {'end': 29521.526, 'start': 29008.136, 'title': 'Understanding merge statement in sql', 'summary': 'Covers the use of the merge statement to perform insert, update, and delete operations on tables in sql, demonstrated with examples and syntax explanations, with a subsequent overview of select statement functionalities including selecting columns, all columns, top records, distinct values, and using order by, group by, and having clauses.', 'duration': 513.39, 'highlights': ['The merge statement is used to perform insert update delete operations on a specific table where the source table is provided.', 'Demonstration of using the merge statement syntax and its application with examples of creating source and target tables, inserting values, and executing the merge statement with insert, update, and delete conditions.', 'Overview of select statement functionalities including selecting specific columns, all columns, top records, distinct values in a specific column, and using order by, group by, and having clauses.']}, {'end': 29690.895, 'start': 29522.046, 'title': 'Using order by in sql', 'summary': 'Explains how to use the order by statement to sort data in sql, including sorting by multiple columns and using aggregate functions to count the number of students from each city.', 'duration': 168.849, 'highlights': ['The chapter explains how to use the order by statement to sort data in SQL.', 'Demonstrates sorting by multiple columns and using ascending/descending orders.', 'Explains using aggregate functions to count the number of students from each city.']}, {'end': 30170.876, 'start': 29690.895, 'title': 'Group by and grouping sets, having clause, table backup, select statement, cube and roll-up, offset and fetch clause', 'summary': 'Covers the concepts of using group by and grouping sets to create subsets of data, utilizing the having clause to filter aggregated data, creating table backups and selecting specific columns from a table, and using cube and roll-up statements to generate subtotals. it also demonstrates the usage of offset and fetch clauses to retrieve specified ranges of records.', 'duration': 479.981, 'highlights': ['Using group by and grouping sets to create subsets of data', 'Utilizing the having clause to filter aggregated data', 'Creating table backups and selecting specific columns from a table', 'Using cube and roll-up statements to generate subtotals', 'Demonstrating the usage of offset and fetch clauses to retrieve specified ranges of records']}], 'duration': 1857.82, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9828313056.jpg', 'highlights': ['Using cube and roll-up statements to generate subtotals', 'Demonstrating the usage of offset and fetch clauses to retrieve specified ranges of records', 'Creating index constraints for fast retrieval of database using create index command', 'The merge statement is used to perform insert update delete operations on a specific table where the source table is provided', 'Adding unique constraints on multiple columns', 'Adding check constraints for creating and altering tables', 'The chapter explains how to use the order by statement to sort data in SQL', 'Using group by and grouping sets to create subsets of data', 'Utilizing the having clause to filter aggregated data', 'Updating data using the update command to modify specific values in the database']}, {'end': 31219.62, 'segs': [{'end': 30213.87, 'src': 'embed', 'start': 30185.708, 'weight': 5, 'content': [{'end': 30190.432, 'text': "So if we just mention selects top three star from offset marks, you'll get the top three doubles from the table.", 'start': 30185.708, 'duration': 4.724}, {'end': 30193.454, 'text': 'So guys that was about the offset clause and top class.', 'start': 30190.812, 'duration': 2.642}, {'end': 30195.396, 'text': "Now, let's move forward with the next one.", 'start': 30193.875, 'duration': 1.521}, {'end': 30196.116, 'text': 'That is the pivot.', 'start': 30195.416, 'duration': 0.7}, {'end': 30202.441, 'text': 'But the pivot is used to rotate the rows to column values and run aggregations when required on the remaining column values.', 'start': 30196.396, 'duration': 6.045}, {'end': 30205.183, 'text': 'Now the syntax of pivot is also very simple guys.', 'start': 30202.861, 'duration': 2.322}, {'end': 30213.87, 'text': "You just mentioned select non-pivoted column name and then you've mentioned first pivoted column name as a column name and then so on till the last pivoted column name as a column name.", 'start': 30205.203, 'duration': 8.667}], 'summary': 'Sql offset gets top 3 records, pivot rotates rows to columns for aggregations.', 'duration': 28.162, 'max_score': 30185.708, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830185708.jpg'}, {'end': 30486.123, 'src': 'embed', 'start': 30435.01, 'weight': 0, 'content': [{'end': 30446.818, 'text': "Let's say BBB of in type and CCC of in type right, and then I just type on go and then I mentioned insert into, let's say,", 'start': 30435.01, 'duration': 11.808}, {'end': 30449.66, 'text': 'sample table and then I mentioned values.', 'start': 30446.818, 'duration': 2.842}, {'end': 30451.42, 'text': 'right in brackets.', 'start': 30450.64, 'duration': 0.78}, {'end': 30456.122, 'text': "I'll just mention let's say 1 3 5 6 right similarly.", 'start': 30451.5, 'duration': 4.622}, {'end': 30457.882, 'text': "I'll just mention the other values also.", 'start': 30456.162, 'duration': 1.72}, {'end': 30459.563, 'text': 'So let me just copy paste.', 'start': 30458.302, 'duration': 1.261}, {'end': 30464.924, 'text': "Right and then I'll click on go.", 'start': 30463.624, 'duration': 1.3}, {'end': 30470.226, 'text': "So I'll just execute the statement right and now what I'll do is I'll just show you the value.", 'start': 30465.384, 'duration': 4.842}, {'end': 30474.187, 'text': "So I'll just mention select star from sample table.", 'start': 30470.246, 'duration': 3.941}, {'end': 30477.161, 'text': "All right and I'll just execute this once.", 'start': 30474.981, 'duration': 2.18}, {'end': 30478.602, 'text': 'I execute this so you can see that.', 'start': 30477.161, 'duration': 1.441}, {'end': 30480.942, 'text': 'you know we have three rows and four columns, right?', 'start': 30478.602, 'duration': 2.34}, {'end': 30484.283, 'text': 'So we have the supplier ID and then we have a BB and CC.', 'start': 30480.962, 'duration': 3.321}, {'end': 30486.123, 'text': "now what we'll do is will unpivoted.", 'start': 30484.283, 'duration': 1.84}], 'summary': 'Executing sql statement inserted 3 rows and 4 columns in sample table.', 'duration': 51.113, 'max_score': 30435.01, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830435010.jpg'}, {'end': 30583.972, 'src': 'embed', 'start': 30554.682, 'weight': 6, 'content': [{'end': 30557.384, 'text': 'so for every customer, that is AAA.', 'start': 30554.682, 'duration': 2.702}, {'end': 30558.965, 'text': 'the products show three.', 'start': 30557.384, 'duration': 1.581}, {'end': 30561.347, 'text': 'the values that we have entered previously are shown.', 'start': 30558.965, 'duration': 2.382}, {'end': 30566.471, 'text': "for example, let's say, a, which had value 398, are now put into three different rows.", 'start': 30561.347, 'duration': 5.124}, {'end': 30568.332, 'text': 'similarly goes for BBB and CCC.', 'start': 30566.471, 'duration': 1.861}, {'end': 30571.276, 'text': "So that's how you can unpivot the table also guys.", 'start': 30569.053, 'duration': 2.223}, {'end': 30574.24, 'text': 'Well, that was about a little bit section of the DML commands.', 'start': 30571.476, 'duration': 2.764}, {'end': 30581.409, 'text': 'Now if you want to know in depth about sequel pivot and unpivot I leave a blog link in the description box below and you can refer to that.', 'start': 30574.7, 'duration': 6.709}, {'end': 30583.972, 'text': 'Well guys that was about this part of the section.', 'start': 30581.99, 'duration': 1.982}], 'summary': 'Demonstrated unpivoting table with 3 products for aaa, bbb, and ccc.', 'duration': 29.29, 'max_score': 30554.682, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830554682.jpg'}, {'end': 30782.18, 'src': 'embed', 'start': 30757.785, 'weight': 2, 'content': [{'end': 30764.848, 'text': 'then wildcard characters to be matched, wildcard characters which matches the single value, or maybe multiple values, and so on, and finally,', 'start': 30757.785, 'duration': 7.063}, {'end': 30768.19, 'text': 'coming to aggregate functions, as I previously spoke about in the session.', 'start': 30764.848, 'duration': 3.342}, {'end': 30773.434, 'text': 'Aggregate functions are used to find the sum average count min Max first and last values.', 'start': 30768.57, 'duration': 4.864}, {'end': 30777.817, 'text': 'So all of these operators are very simple to use guys for your better understanding.', 'start': 30773.794, 'duration': 4.023}, {'end': 30782.18, 'text': 'Let me give you a few examples, but I would say, the more you practice, the more will you benefit, right?', 'start': 30778.057, 'duration': 4.123}], 'summary': 'Wildcard characters can match single or multiple values, aggregate functions like sum, average, count, min, max, first, and last are simple to use.', 'duration': 24.395, 'max_score': 30757.785, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830757785.jpg'}, {'end': 30921.065, 'src': 'embed', 'start': 30896.118, 'weight': 3, 'content': [{'end': 30906.87, 'text': "which is a column name, and then you'll mention between, and then you'll mention 62 and which is again a logical operator, and you'll mention 65,", 'start': 30896.118, 'duration': 10.752}, {'end': 30908.292, 'text': 'right?. Okay, my bad.', 'start': 30906.87, 'duration': 1.422}, {'end': 30909.353, 'text': 'This has to be select.', 'start': 30908.452, 'duration': 0.901}, {'end': 30914.822, 'text': 'and then I click on execute so we get all the marks between 62 and 65.', 'start': 30910.399, 'duration': 4.423}, {'end': 30921.065, 'text': "now, for example, let's say we want to find out those marks which are greater than 62 or marks which are equal to 67..", 'start': 30914.822, 'duration': 6.243}], 'summary': 'Using logical operators to find marks between 62 and 65, and marks greater than 62 or equal to 67.', 'duration': 24.947, 'max_score': 30896.118, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830896118.jpg'}, {'end': 31051.7, 'src': 'embed', 'start': 31025.013, 'weight': 1, 'content': [{'end': 31030.001, 'text': "Now, let's say we want to find out all those tuples whose student name starts with M and then ends with a right.", 'start': 31025.013, 'duration': 4.988}, {'end': 31032.184, 'text': "So I'll just change this particular query only.", 'start': 31030.021, 'duration': 2.163}, {'end': 31035.589, 'text': "I'll just remove the underscore and then put a percentage symbol and mention a.", 'start': 31032.184, 'duration': 3.405}, {'end': 31041.812, 'text': 'What I mean by this particular statement is basically that you know the student name should start with M, should end with a,', 'start': 31036.448, 'duration': 5.364}, {'end': 31043.954, 'text': 'and any number of letters can be present in between.', 'start': 31041.812, 'duration': 2.142}, {'end': 31046.276, 'text': 'Well, that was about this particular query guys.', 'start': 31044.454, 'duration': 1.822}, {'end': 31051.7, 'text': "Now, let's say you want to use the scope resolution operator to access the get root member of the hierarchy ID type.", 'start': 31046.596, 'duration': 5.104}], 'summary': 'Query for finding tuples with student name starting with m and ending with a, using a wildcard symbol.', 'duration': 26.687, 'max_score': 31025.013, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831025013.jpg'}], 'start': 30170.896, 'title': 'Sql operators and functions', 'summary': 'Explains the usage of sql operators like logical, comparison, scope resolution, and aggregate functions, including set operators, with examples and execution results provided, covering the usage of the like operator to search for specific patterns in data, and the usage of aggregate functions to calculate averages. it also includes the overview of sequel pivot and unpivot operations and a comprehensive list of operators available in sql, including arithmetic, assignment, bitwise, comparison, compound, logical, scope resolution, set, string concat, along with the usage of pivot function to rotate row values into column values and run aggregations on the remaining column values in sql.', 'chapters': [{'end': 30252.606, 'start': 30170.896, 'title': 'Sql pivot and offset clause', 'summary': 'Explains the usage of the offset clause to fetch specific rows and the pivot function to rotate row values into column values and run aggregations on the remaining column values in sql.', 'duration': 81.71, 'highlights': ['The offset clause is used to fetch specific rows, as demonstrated by the example of fetching the next two rows after leaving the first three rows.', 'The pivot function in SQL is used to rotate row values into column values and run aggregations on the remaining column values, following a specific syntax.']}, {'end': 30880.333, 'start': 30253.126, 'title': 'Sequel pivot and unpivot', 'summary': 'Covers creating a sequel pivot table with one row and three columns, demonstrating the process with sql commands and showing the conversion of rows to columns, as well as unpivoting a table to display the data differently, and provides an overview of sequel pivot and unpivot operations, along with a comprehensive list of operators available in sql, including arithmetic, assignment, bitwise, comparison, compound, logical, scope resolution, set, string concat, and aggregate functions.', 'duration': 627.207, 'highlights': ['Creating a sequel pivot table with one row and three columns', 'Unpivoting a table to display the data differently', 'Comprehensive list of operators available in SQL']}, {'end': 31219.62, 'start': 30880.714, 'title': 'Sql operators and functions', 'summary': 'Covers the usage of logical, comparison, like, scope resolution, string concatenation operators, aggregate functions, and set operators in sql, with examples and execution results provided. it also includes the explanation of how to use the like operator to search for specific patterns in data, and the usage of aggregate functions to calculate averages.', 'duration': 338.906, 'highlights': ['The chapter covers the usage of logical, comparison, like, scope resolution, string concatenation operators, aggregate functions, and set operators in SQL.', 'Explanation of how to use the like operator to search for specific patterns in data, with examples provided.', 'Usage of aggregate functions to calculate averages, with a demonstration of calculating the average of all the marks.']}], 'duration': 1048.724, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9830170896.jpg', 'highlights': ['The chapter covers the usage of logical, comparison, like, scope resolution, string concatenation operators, aggregate functions, and set operators in SQL.', 'The offset clause is used to fetch specific rows, as demonstrated by the example of fetching the next two rows after leaving the first three rows.', 'The pivot function in SQL is used to rotate row values into column values and run aggregations on the remaining column values, following a specific syntax.', 'Explanation of how to use the like operator to search for specific patterns in data, with examples provided.', 'Usage of aggregate functions to calculate averages, with a demonstration of calculating the average of all the marks.', 'Creating a sequel pivot table with one row and three columns', 'Unpivoting a table to display the data differently', 'Comprehensive list of operators available in SQL']}, {'end': 32268.513, 'segs': [{'end': 31270.955, 'src': 'embed', 'start': 31242.751, 'weight': 0, 'content': [{'end': 31246.793, 'text': "now let's just perform the Union operation, the intersect operation and the accept operation.", 'start': 31242.751, 'duration': 4.042}, {'end': 31260.43, 'text': "So I'll just mention select star from students info and then I'll mention Union and let's say we mentioned select staff from the table to.", 'start': 31247.193, 'duration': 13.237}, {'end': 31262.03, 'text': 'that is students details right?', 'start': 31260.43, 'duration': 1.6}, {'end': 31264.672, 'text': "So I'll just search for students details right?", 'start': 31262.05, 'duration': 2.622}, {'end': 31266.473, 'text': "And let's just execute now.", 'start': 31264.892, 'duration': 1.581}, {'end': 31270.955, 'text': 'Once we execute, you can clearly see that you know all our table values have been inserted.', 'start': 31266.553, 'duration': 4.402}], 'summary': 'Performed union, intersect, and accept operations on students info, resulting in successful insertion of all table values.', 'duration': 28.204, 'max_score': 31242.751, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831242751.jpg'}, {'end': 31352.494, 'src': 'embed', 'start': 31321.009, 'weight': 8, 'content': [{'end': 31324.631, 'text': "Now let's move forward with the next part of the session, that is nested queries.", 'start': 31321.009, 'duration': 3.622}, {'end': 31328.714, 'text': 'now, nested queries are also one of the most popular concepts that you need to know.', 'start': 31324.631, 'duration': 4.083}, {'end': 31333.217, 'text': 'because you know, nested queries are sequel queries, which have an outer query and an inner sub query.', 'start': 31328.714, 'duration': 4.503}, {'end': 31339.06, 'text': 'So the sub query is a query nested within another query such as the select insert update and the delete statements.', 'start': 31333.637, 'duration': 5.423}, {'end': 31341.242, 'text': 'So this was about the nested query guys.', 'start': 31339.521, 'duration': 1.721}, {'end': 31346.028, 'text': 'So as you can see on my screen, we have an outer query and then we can have a sub query.', 'start': 31342.145, 'duration': 3.883}, {'end': 31352.494, 'text': 'So what will happen is that you know the first the sub query or the inner query will be executed and whatever output comes,', 'start': 31346.429, 'duration': 6.065}], 'summary': 'Nested queries are essential in sql, with an outer and inner subquery for select, insert, update, and delete statements.', 'duration': 31.485, 'max_score': 31321.009, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831321009.jpg'}, {'end': 31439.579, 'src': 'embed', 'start': 31416.232, 'weight': 1, 'content': [{'end': 31423.353, 'text': 'So they are predominantly used when a user is trying to extract data from the tables which have one too many or many too many relationships between them.', 'start': 31416.232, 'duration': 7.121}, {'end': 31426.293, 'text': 'Now, there are mainly four types of joints that you have to understand guys.', 'start': 31423.593, 'duration': 2.7}, {'end': 31429.194, 'text': 'That is the inner joint the left joint the right joint and the full joint.', 'start': 31426.333, 'duration': 2.861}, {'end': 31434.477, 'text': 'Now the inner joint returns, the records which have matching values in both the tables, the left joint returns,', 'start': 31429.654, 'duration': 4.823}, {'end': 31439.579, 'text': 'the records from the left table and also those records which satisfy the condition from the right table.', 'start': 31434.477, 'duration': 5.102}], 'summary': 'Predominantly used for extracting data from tables with complex relationships. four types of joints: inner, left, right, and full.', 'duration': 23.347, 'max_score': 31416.232, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831416232.jpg'}, {'end': 31663.935, 'src': 'embed', 'start': 31638.268, 'weight': 6, 'content': [{'end': 31644.132, 'text': 'stored procedures are those reusable units that can encapsulate a specific business logic of the application.', 'start': 31638.268, 'duration': 5.864}, {'end': 31649.513, 'text': 'So it is a group of SQL statements and logic compiled and stored together to perform a specific task.', 'start': 31644.492, 'duration': 5.021}, {'end': 31653.133, 'text': 'The syntax of stored procedures is, as you can see on my screen, right?', 'start': 31649.853, 'duration': 3.28}, {'end': 31654.254, 'text': 'So you just have to mention,', 'start': 31653.154, 'duration': 1.1}, {'end': 31662.215, 'text': "create or replace a procedure and then you mention the procedure name and after that you'll mention the parameters name and then you'll use the aliases is or as,", 'start': 31654.254, 'duration': 7.961}, {'end': 31663.935, 'text': 'and then you say begin right.', 'start': 31662.215, 'duration': 1.72}], 'summary': 'Stored procedures encapsulate business logic in sql, using syntax like create or replace a procedure and mentioning parameters and aliases to perform specific tasks.', 'duration': 25.667, 'max_score': 31638.268, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831638268.jpg'}, {'end': 31788.006, 'src': 'embed', 'start': 31758.211, 'weight': 3, 'content': [{'end': 31762.914, 'text': 'You just write the code once and then you can keep using it again and again like any other programming languages.', 'start': 31758.211, 'duration': 4.703}, {'end': 31767.837, 'text': 'So guys that was about the stored procedures and with this we come to an end to the DML commands.', 'start': 31763.354, 'duration': 4.483}, {'end': 31771.82, 'text': "Now, let's move forward with the next section of commands that is the DCL commands.", 'start': 31768.237, 'duration': 3.583}, {'end': 31777.704, 'text': 'So as I mentioned before the DCL commands are used to grant or revoke privileges to a user.', 'start': 31772.62, 'duration': 5.084}, {'end': 31781.206, 'text': "So, for example, let's say you want to create a user.", 'start': 31778.304, 'duration': 2.902}, {'end': 31788.006, 'text': 'so to create a user, what you can do is you can just mention create login Sample with.', 'start': 31781.206, 'duration': 6.8}], 'summary': 'Stored procedures enable code reuse, dml commands conclude, moving to dcl commands for user privileges.', 'duration': 29.795, 'max_score': 31758.211, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831758211.jpg'}, {'end': 31870.78, 'src': 'embed', 'start': 31841.028, 'weight': 2, 'content': [{'end': 31846.132, 'text': "So I'll execute this and now this particular user can be used rather than just using the admin login credentials.", 'start': 31841.028, 'duration': 5.104}, {'end': 31851.115, 'text': 'Now what you can do is now you can grant and revoke any kind of privileges to a specific user.', 'start': 31846.632, 'duration': 4.483}, {'end': 31855.098, 'text': "Now, for example, let's say you want to grant few privileges for this user.", 'start': 31851.876, 'duration': 3.222}, {'end': 31855.719, 'text': 'edureka, right?', 'start': 31855.098, 'duration': 0.621}, {'end': 31861.473, 'text': "So for that you will type in Grant, select on and then you'll mention the table name.", 'start': 31855.739, 'duration': 5.734}, {'end': 31870.78, 'text': "Let's say students info right and then what I'll do is I'll mention to and then I'll mention the username that is at Eureka.", 'start': 31861.493, 'duration': 9.287}], 'summary': "Enable user-specific privileges for database access, e.g. grant privileges to user 'edureka' on table 'students info'.", 'duration': 29.752, 'max_score': 31841.028, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831841028.jpg'}, {'end': 31967.149, 'src': 'embed', 'start': 31930.988, 'weight': 4, 'content': [{'end': 31934.371, 'text': "So I'll just click on execute now, once these values are inserted.", 'start': 31930.988, 'duration': 3.383}, {'end': 31936.713, 'text': "now let's just start a transaction right?", 'start': 31934.371, 'duration': 2.342}, {'end': 31940.896, 'text': "So to start a transaction, what I'll do is I just mentioned begin right?", 'start': 31937.033, 'duration': 3.863}, {'end': 31943.578, 'text': "So that's a try-catch block which I'll come to later in the session.", 'start': 31941.076, 'duration': 2.502}, {'end': 31944.979, 'text': "So don't worry after that.", 'start': 31943.598, 'duration': 1.381}, {'end': 31953.698, 'text': "I'll mention begin transaction right and then I'll mention insert into TCL sample, that's a table name.", 'start': 31945.02, 'duration': 8.678}, {'end': 31956.981, 'text': "and then I'll mention values and let's say we mentioned few values.", 'start': 31953.698, 'duration': 3.283}, {'end': 31967.149, 'text': "Let's say five and then I mentioned the name and let's say we've mentioned other values, right? So I'll just mention 56 this need not be in code.", 'start': 31957.021, 'duration': 10.128}], 'summary': "Executing a transaction with 5 values and table name 'tcl sample'.", 'duration': 36.161, 'max_score': 31930.988, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831930988.jpg'}, {'end': 32184.409, 'src': 'embed', 'start': 32150.788, 'weight': 5, 'content': [{'end': 32153.771, 'text': 'So talking about the pro block first, or I would say the pro clause.', 'start': 32150.788, 'duration': 2.983}, {'end': 32159.256, 'text': 'this clause is used to raise an exception and transfers the execution to a catch block of the try and catch construct.', 'start': 32153.771, 'duration': 5.485}, {'end': 32160.757, 'text': 'If you want to see the syntax.', 'start': 32159.636, 'duration': 1.121}, {'end': 32162.839, 'text': 'the syntax is as you can see on my screen.', 'start': 32160.757, 'duration': 2.082}, {'end': 32167.624, 'text': 'you mentioned throw and then you mentioned the error number, and then you can mention local variable message and so on.', 'start': 32162.839, 'duration': 4.785}, {'end': 32172.945, 'text': 'Coming to the try-catch block the try-catch block is used to implement exception handling in transact sequel.', 'start': 32168.164, 'duration': 4.781}, {'end': 32175.886, 'text': 'So a group of statements can be enclosed in a try block.', 'start': 32173.285, 'duration': 2.601}, {'end': 32182.188, 'text': 'So, in case an error occurs in the try block, the control is then passed to another group of statements that are enclosed in the catch block,', 'start': 32175.906, 'duration': 6.282}, {'end': 32184.409, 'text': 'as we just saw in the transaction control language commands.', 'start': 32182.188, 'duration': 2.221}], 'summary': 'The pro block raises an exception, transferring to catch block. try-catch handles errors in t-sql.', 'duration': 33.621, 'max_score': 32150.788, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9832150788.jpg'}], 'start': 31220.979, 'title': 'Sql operations and dcl commands', 'summary': 'Covers set operations, nested queries, and joints in sql, including union, intersect, and except with examples, as well as dcl commands such as creating users, granting/rejecting privileges, and transaction control language commands like begin, commit, rollback, and exception handling.', 'chapters': [{'end': 31638.268, 'start': 31220.979, 'title': 'Set operations, nested queries, and joins in sql', 'summary': 'Covers set operations including union, intersect, and except with examples, followed by nested queries and joints in sql, explaining the inner, left, right, and full outer joints and providing an example for each.', 'duration': 417.289, 'highlights': ['The chapter covers set operations including Union, Intersect, and Except.', 'Nested queries and their execution process are explained along with an example.', 'Different types of joints in SQL are detailed, including inner, left, right, and full outer joints, with examples for each.']}, {'end': 31758.171, 'start': 31638.268, 'title': 'Introduction to stored procedures', 'summary': 'Discusses the concept of stored procedures, outlining their definition as reusable units of sql statements and logic to perform specific tasks, demonstrated through the creation of a procedure to filter student data by city and using parameterized execution.', 'duration': 119.903, 'highlights': ['Stored procedures encapsulate business logic and SQL statements', 'Syntax of stored procedures', 'Creating and executing a stored procedure']}, {'end': 31899.258, 'start': 31758.211, 'title': 'Sql dcl commands and privileges', 'summary': 'Covers dcl commands in sql, including creating users and granting/rejecting privileges, with practical examples such as creating a user and granting/rejecting privileges on a table, aimed at enabling users to understand and implement dcl commands effectively.', 'duration': 141.047, 'highlights': ['By using stored procedures in SQL, code can be reused multiple times, enhancing efficiency and reducing redundancy.', "The process of creating a user and granting/rejecting privileges, such as creating a login 'Sample One' with a password 'edureka' and granting/rejecting privileges on a table, is demonstrated, facilitating practical understanding.", "Detailed steps for creating a user and granting/rejecting privileges, such as creating a login 'Sample One' with a password 'edureka' and granting/rejecting privileges on a table, are provided, offering a comprehensive guide for practical implementation.", 'Explanation of the significance and practical application of DCL commands in SQL, including creating users and managing privileges, is provided, enhancing understanding and application in real-world scenarios.']}, {'end': 32268.513, 'start': 31899.598, 'title': 'Transaction control language commands', 'summary': 'Explains transaction control language (tcl) commands in sql, demonstrating the use of begin, commit, rollback, and exception handling, providing examples of syntax and error handling, and highlighting the importance of managing database transactions effectively.', 'duration': 368.915, 'highlights': ['The chapter explains the use of Transaction Control Language (TCL) commands in SQL, such as begin, commit, and rollback, to manage database transactions effectively.', 'It demonstrates the syntax and usage of try-catch blocks for exception handling in SQL, showing how a group of statements can be enclosed in a try block, and control can be passed to a catch block when an error occurs.', 'The chapter includes a practical example of using the throw clause to raise an exception, showcasing the syntax and output of using throw with an error number and message.']}], 'duration': 1047.534, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9831220979.jpg', 'highlights': ['The chapter covers set operations including Union, Intersect, and Except.', 'Different types of joints in SQL are detailed, including inner, left, right, and full outer joints, with examples for each.', "The process of creating a user and granting/rejecting privileges, such as creating a login 'Sample One' with a password 'edureka' and granting/rejecting privileges on a table, is demonstrated, facilitating practical understanding.", 'Explanation of the significance and practical application of DCL commands in SQL, including creating users and managing privileges, is provided, enhancing understanding and application in real-world scenarios.', 'The chapter explains the use of Transaction Control Language (TCL) commands in SQL, such as begin, commit, and rollback, to manage database transactions effectively.', 'It demonstrates the syntax and usage of try-catch blocks for exception handling in SQL, showing how a group of statements can be enclosed in a try block, and control can be passed to a catch block when an error occurs.', 'By using stored procedures in SQL, code can be reused multiple times, enhancing efficiency and reducing redundancy.', 'The chapter includes a practical example of using the throw clause to raise an exception, showcasing the syntax and output of using throw with an error number and message.', 'Nested queries and their execution process are explained along with an example.', 'Stored procedures encapsulate business logic and SQL statements.', 'Syntax of stored procedures.', 'Creating and executing a stored procedure.', "Detailed steps for creating a user and granting/rejecting privileges, such as creating a login 'Sample One' with a password 'edureka' and granting/rejecting privileges on a table, are provided, offering a comprehensive guide for practical implementation."]}, {'end': 33640.928, 'segs': [{'end': 32499.905, 'src': 'embed', 'start': 32472.685, 'weight': 6, 'content': [{'end': 32479.709, 'text': 'So what happens is that the SQL Server agent uses the SQL Server to store the job information and run a job on a schedule.', 'start': 32472.685, 'duration': 7.024}, {'end': 32485.113, 'text': 'now the main components of SQL Server agent are basically jobs, schedules, operators and alerts.', 'start': 32479.709, 'duration': 5.404}, {'end': 32490.017, 'text': 'So if you have to explain with an example over here, you can just consider the example.', 'start': 32485.814, 'duration': 4.203}, {'end': 32495.202, 'text': 'like you know, if an Enterprise wishes to take the backup of the company servers at 9 p.m. On every Friday.', 'start': 32490.017, 'duration': 5.185}, {'end': 32499.905, 'text': 'Now what you can do is you can just automate this task and let the task happen at the scheduled time.', 'start': 32495.582, 'duration': 4.323}], 'summary': 'Sql server agent automates tasks, e.g., scheduling backups at 9 p.m. on fridays.', 'duration': 27.22, 'max_score': 32472.685, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9832472685.jpg'}, {'end': 32871.903, 'src': 'embed', 'start': 32845.224, 'weight': 1, 'content': [{'end': 32852.87, 'text': 'Right?. So well, the answer to this particular question is that you know the TCP IP port on with sequel server runs is 1433, right?', 'start': 32845.224, 'duration': 7.646}, {'end': 32855.252, 'text': "So that's 1433 that you have to keep in your mind.", 'start': 32852.89, 'duration': 2.362}, {'end': 32857.593, 'text': "Now, let's move forward with the next question.", 'start': 32855.872, 'duration': 1.721}, {'end': 32860.035, 'text': 'That is what are sub queries in SQL Server?', 'start': 32857.612, 'duration': 2.423}, {'end': 32862.076, 'text': 'and then can you mention few properties of it?', 'start': 32860.035, 'duration': 2.041}, {'end': 32865.258, 'text': 'So if I have to explain you what a sub query is,', 'start': 32862.837, 'duration': 2.421}, {'end': 32871.903, 'text': 'a sub query is a query inside another query where a query is defined to retrieve data or information back from the database.', 'start': 32865.258, 'duration': 6.645}], 'summary': 'The tcp ip port for sql server is 1433. a sub query is a query inside another query used to retrieve data from the database.', 'duration': 26.679, 'max_score': 32845.224, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9832845224.jpg'}, {'end': 32963.565, 'src': 'embed', 'start': 32934.866, 'weight': 2, 'content': [{'end': 32941.53, 'text': 'now, in this state, if you try to bring the sequel server agent resources online, then it may fail the sequel resources to a different node,', 'start': 32934.866, 'duration': 6.664}, {'end': 32943.051, 'text': 'as it could be configured to a group.', 'start': 32941.53, 'duration': 1.521}, {'end': 32949.252, 'text': 'So to start a single user mode in cluster installations, you have to follow the steps that you can see on my screen.', 'start': 32943.749, 'duration': 5.503}, {'end': 32955.158, 'text': 'You have to first go to the advanced properties and then remove the hyphen M startup parameter.', 'start': 32949.894, 'duration': 5.264}, {'end': 32963.565, 'text': "after that you have to put the sequel server resource offline and then issue the command from the command prompt and make sure that you know you're at the current owner node of the group.", 'start': 32955.158, 'duration': 8.407}], 'summary': 'Bringing sequel server agent online may fail to different node, follow steps for single user mode in cluster installations.', 'duration': 28.699, 'max_score': 32934.866, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9832934866.jpg'}, {'end': 33029.896, 'src': 'embed', 'start': 33002.403, 'weight': 3, 'content': [{'end': 33006.326, 'text': 'And what are the different types of replication that are present in SQL Server now?', 'start': 33002.403, 'duration': 3.923}, {'end': 33011.468, 'text': 'replication in Microsoft SQL Server is a process to synchronize the data across multiple servers.', 'start': 33006.326, 'duration': 5.142}, {'end': 33013.989, 'text': 'Now this is generally done by the replica set,', 'start': 33011.828, 'duration': 2.161}, {'end': 33019.553, 'text': 'and these sets provide multiple copies of data with redundancy and high availability on different servers.', 'start': 33013.989, 'duration': 5.564}, {'end': 33024.214, 'text': 'Not only this but replication also provides a mechanism to recover from the failures.', 'start': 33020.053, 'duration': 4.161}, {'end': 33029.896, 'text': 'So it basically removes the dependencies from single server to protect the loss of data from that particular server.', 'start': 33024.553, 'duration': 5.343}], 'summary': 'Sql server has different types of replication for synchronizing data across multiple servers, providing redundancy, high availability, and failure recovery.', 'duration': 27.493, 'max_score': 33002.403, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833002403.jpg'}, {'end': 33100.953, 'src': 'embed', 'start': 33076.898, 'weight': 0, 'content': [{'end': 33084.523, 'text': 'now, if you have to mention the differences between Microsoft SQL Server and Oracle, then MS SQL Server basically provides simple and easy syntax,', 'start': 33076.898, 'duration': 7.625}, {'end': 33089.105, 'text': 'whereas Oracle consists of complex and comparatively more efficient syntaxes.', 'start': 33084.523, 'duration': 4.582}, {'end': 33095.468, 'text': 'the MS SQL Server uses basically the Transact SQL or the T SQL, whereas Oracle uses the PL SQL.', 'start': 33089.105, 'duration': 6.363}, {'end': 33100.953, 'text': 'Moving on to the next difference, that is, you know, sequel server does not support query optimization,', 'start': 33096.186, 'duration': 4.767}], 'summary': 'Microsoft sql server has simple syntax, uses t sql, while oracle has complex syntax and uses pl sql. sql server does not support query optimization.', 'duration': 24.055, 'max_score': 33076.898, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833076898.jpg'}, {'end': 33296.011, 'src': 'embed', 'start': 33266.991, 'weight': 4, 'content': [{'end': 33268.612, 'text': "Let's move forward with the next question.", 'start': 33266.991, 'duration': 1.621}, {'end': 33272.233, 'text': 'That is what do you understand by data quality services in SQL Server?', 'start': 33268.631, 'duration': 3.602}, {'end': 33278.076, 'text': 'So the data quality services in SQL Server is basically a knowledge-driven data quality product, right?', 'start': 33272.634, 'duration': 5.442}, {'end': 33284.9, 'text': 'So the SQL Server data quality services enable the user to build a knowledge base and thereafter use it to perform tasks such as correction,', 'start': 33278.097, 'duration': 6.803}, {'end': 33288.061, 'text': 'deduplication, enrichment and standardization of data.', 'start': 33284.9, 'duration': 3.161}, {'end': 33296.011, 'text': 'Apart from this the data quality services also provide profiling and enable you to perform data cleaning with help of cloud-based data services.', 'start': 33288.786, 'duration': 7.225}], 'summary': 'Sql server data quality services enable data correction, deduplication, enrichment, and standardization, with cloud-based support.', 'duration': 29.02, 'max_score': 33266.991, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833266991.jpg'}, {'end': 33501.313, 'src': 'embed', 'start': 33477.269, 'weight': 5, 'content': [{'end': 33483.696, 'text': "So the advantages of log shipping are, as you can see in my screen it needs low maintenance and it's easy to set up.", 'start': 33477.269, 'duration': 6.427}, {'end': 33490.642, 'text': 'the secondary database created is used for read-only purposes only and you can also create multiple secondary standby servers.', 'start': 33483.696, 'duration': 6.946}, {'end': 33496.468, 'text': 'not only this, but it also allows a delay specified by the user for the time between the primary servers backup,', 'start': 33490.642, 'duration': 5.826}, {'end': 33501.313, 'text': 'the log of the primary database and when the secondary servers restore or apply the log back.', 'start': 33496.468, 'duration': 4.845}], 'summary': 'Log shipping offers low maintenance, easy setup, read-only secondary database, and user-specified delay for log application.', 'duration': 24.044, 'max_score': 33477.269, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833477269.jpg'}], 'start': 32268.513, 'title': 'Sql server features and administration', 'summary': 'Covers exception handling, authentication modes, single user mode in clustered installations, hiding sql server instances, data quality services, replication types, and differences between sql server and mysql, oracle, as well as substring and character index in sql server.', 'chapters': [{'end': 32526.42, 'start': 32268.513, 'title': 'Exception handling in sql server', 'summary': 'Discusses exception handling in microsoft sql server, mentioning the differences between sql server and mysql, and explaining the sql server agent.', 'duration': 257.907, 'highlights': ['The SQL Server is a licensed product developed by Microsoft, while MySQL is an open-source software developed by Oracle, and the installation process for MySQL is easier compared to SQL Server.', 'Both SQL Server and MySQL support various programming languages, but SQL Server does not allow database file manipulation while running, and it takes a large amount of operational storage space compared to MySQL.', 'SQL Server does not block the database while backing up the data, whereas MySQL blocks the database during the backup process and SQL Server is available in the Express and Custom modes, while MySQL is available in the MySQL Community Edition and the Enterprise Edition.', 'The SQL Server Agent is a Windows service used to schedule and execute jobs, with main components including jobs, schedules, operators, and alerts, and it is crucial for automating tasks and recording events for error notifications.']}, {'end': 32920.478, 'start': 32526.761, 'title': 'Sql server authentication modes and features', 'summary': 'Discusses sql server authentication modes, including windows authentication and mixed mode, as well as differences between local and global temporary tables, ways to check the version of sql server, the single user mode, the sql server profiler, the tcp/ip port on which sql server runs, and properties of sub queries.', 'duration': 393.717, 'highlights': ['The TCP IP port on which sequel server runs is 1433.', 'The different authentication modes offered by sequel server are basically the Windows authentication mode and the mixed mode.', 'The Microsoft sequel server profiler is an interface used to create and manage traces, analyze and replay trace results, diagnose slow queries, and capture series of SQL statements causing a problem.', 'Sub queries are always executed first and the result is passed to the main query, and it must be enclosed in parentheses and should not have the order by clause.', 'Local temporary tables only exist for the duration of the connection or the duration of that particular statement which is executing, whereas global temporary tables exist permanently in the databases and only the rows get deleted when the connection gets closed.']}, {'end': 33214.608, 'start': 32920.919, 'title': 'Starting single user mode in clustered installations', 'summary': 'Explains the process of starting single user mode in clustered installations, including removing the hyphen m startup parameter, putting the sequel server resource offline, issuing specific commands, and verifying the status through cluster management console. additionally, it describes replication in sql server, highlighting merge, transactional, and snapshot replication types, as well as differences between microsoft sql server and oracle, and the concept of intent logs.', 'duration': 293.689, 'highlights': ["To start single user mode in cluster installations, you have to remove the hyphen M startup parameter, put the sequel server resource offline, issue the command 'net start MS SQL server /M', verify the status through cluster administrator, connect the sequel server, perform required operations, and bring back the sequel server and other resources online through cluster administrator.", 'Replication in Microsoft SQL Server synchronizes data across multiple servers using replica sets, providing redundancy, high availability, and a mechanism to recover from failures. Types of replication include merge, transactional, and snapshot replication, each serving specific environments and data distribution requirements.', 'The differences between Microsoft SQL Server and Oracle include syntax complexity, query optimization support, backup types, clustering support, execution of statements, and job scheduling methods, offering insights into the distinctive features of each database management system.', 'Intent logs in Microsoft SQL Server, including intent shared lock, intent update lock, and intent exclusive lock, are used to indicate and manage the application of shared, update, and exclusive locks at the row level, contributing to the overall log hierarchy and data consistency.']}, {'end': 33640.928, 'start': 33214.627, 'title': 'Hiding sql server instances and understanding data quality services', 'summary': 'Explains how to hide sql server instances, the functionality of data quality services in sql server, the concept of magic tables, change data capture, triggers, recursive stored procedures, log shipping, and trace flags. it also highlights the differences between substring and character index in sql server.', 'duration': 426.301, 'highlights': ["To hide SQL Server instances, open SQL Server Configuration Manager, navigate to SQL Server network configuration, go to protocols, right-click on the instance, select properties, go to the flags tab, choose 'hide instance', and click OK to hide the instance.", 'Data Quality Services in SQL Server enable users to build a knowledge base for tasks such as correction, deduplication, enrichment, and standardization of data. It consists of two components: data quality server and data quality client, which facilitate data quality analysis and management interactively.', 'Change Data Capture records insert, update, delete activities on tables to capture and store modified data in change tables, which mirror the original column structure.', 'Triggers in SQL Server execute batches of SQL code when insert, delete, or update commands are executed against a table. They are automatically triggered based on data manipulation operations, with different types including insert, update, delete, and instead of triggers.', 'Recursive stored procedures in SQL Server allow for a problem-solving method where the solution is arrived at repeatedly by calling back the function, providing a way to achieve solutions iteratively.', 'Log shipping automates the backup and restoration of databases from one standalone server to another standby server, serving as a disaster recovery solution with advantages such as low maintenance, easy setup, read-only secondary databases, multiple standby servers, and user-specified delay.', 'Trace flags are used to alter server behaviors or set server characteristics, with common trace flags like 1204, 1205, 1118, 652, and 256, each serving specific purposes such as deadlock information, plan cache bucket count increase, forcing uniform extent allocations, disabling page prefetching scans, and running DBCC CHECKDB command without data purity check.', 'In SQL Server, substring is used to return a specific portion of the string, while character index is used to return the position of a character in a given string, with examples illustrating their usage.']}], 'duration': 1372.415, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9832268513.jpg', 'highlights': ['SQL Server is a licensed product developed by Microsoft, while MySQL is an open-source software developed by Oracle, and the installation process for MySQL is easier compared to SQL Server.', 'The TCP IP port on which sequel server runs is 1433.', "To start single user mode in cluster installations, you have to remove the hyphen M startup parameter, put the sequel server resource offline, issue the command 'net start MS SQL server /M', verify the status through cluster administrator, connect the sequel server, perform required operations, and bring back the sequel server and other resources online through cluster administrator.", 'Replication in Microsoft SQL Server synchronizes data across multiple servers using replica sets, providing redundancy, high availability, and a mechanism to recover from failures. Types of replication include merge, transactional, and snapshot replication, each serving specific environments and data distribution requirements.', 'Data Quality Services in SQL Server enable users to build a knowledge base for tasks such as correction, deduplication, enrichment, and standardization of data. It consists of two components: data quality server and data quality client, which facilitate data quality analysis and management interactively.', 'Log shipping automates the backup and restoration of databases from one standalone server to another standby server, serving as a disaster recovery solution with advantages such as low maintenance, easy setup, read-only secondary databases, multiple standby servers, and user-specified delay.', 'The SQL Server Agent is a Windows service used to schedule and execute jobs, with main components including jobs, schedules, operators, and alerts, and it is crucial for automating tasks and recording events for error notifications.', 'The differences between Microsoft SQL Server and Oracle include syntax complexity, query optimization support, backup types, clustering support, execution of statements, and job scheduling methods, offering insights into the distinctive features of each database management system.']}, {'end': 34781.717, 'segs': [{'end': 33697.761, 'src': 'embed', 'start': 33658.356, 'weight': 0, 'content': [{'end': 33664.339, 'text': 'So the service basically provides Enterprise grade semantic models for client applications and reports such as power bi,', 'start': 33658.356, 'duration': 5.983}, {'end': 33666.54, 'text': 'Microsoft Excel and other visualization tools.', 'start': 33664.339, 'duration': 2.201}, {'end': 33672.565, 'text': 'So the analysis services is basically available in platforms, like you know, as your analysis services, power, bi,', 'start': 33667.06, 'duration': 5.505}, {'end': 33674.886, 'text': 'premium and sequel server analysis services.', 'start': 33672.565, 'duration': 2.321}, {'end': 33678.709, 'text': 'So guys that was about analysis services in sequel server.', 'start': 33675.587, 'duration': 3.122}, {'end': 33680.831, 'text': "Now, let's move forward with the next question.", 'start': 33679.09, 'duration': 1.741}, {'end': 33684.374, 'text': 'That is what do you understand by mirroring and mention few advantages of it.', 'start': 33680.851, 'duration': 3.523}, {'end': 33692.72, 'text': 'So mirroring in SQL Server is basically designed to maintain a hot standby server that is consistent with the primary server in terms of the transaction.', 'start': 33684.978, 'duration': 7.742}, {'end': 33697.761, 'text': 'Also the transaction log records are sent from the principal server to the secondary server.', 'start': 33693.28, 'duration': 4.481}], 'summary': 'The service offers enterprise-grade semantic models for client apps and reports, available in platforms like azure analysis services, power bi, and sql server analysis services.', 'duration': 39.405, 'max_score': 33658.356, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833658356.jpg'}, {'end': 33789.218, 'src': 'embed', 'start': 33761.699, 'weight': 3, 'content': [{'end': 33771.207, 'text': 'Well, I would say the physical and the logical design plays an important role in the performance of sequel server based applications and we need to ensure that the correct data is captured in the proper tables.', 'start': 33761.699, 'duration': 9.508}, {'end': 33775.551, 'text': 'The data items have proper relationships between them and the data redundancy is reduced.', 'start': 33771.267, 'duration': 4.284}, {'end': 33778.632, 'text': "I would also suggest that you know, while you're designing a database,", 'start': 33775.911, 'duration': 2.721}, {'end': 33789.218, 'text': 'make sure that it is an interactive process to achieve all the required system goals and is in the concert observation to make sure you know all the client requirements are added and the database satisfies the requirement now.', 'start': 33778.632, 'duration': 10.586}], 'summary': "Physical and logical design crucial for sql server apps' performance, reduce data redundancy, meet client requirements.", 'duration': 27.519, 'max_score': 33761.699, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833761699.jpg'}, {'end': 33860.24, 'src': 'embed', 'start': 33829.609, 'weight': 2, 'content': [{'end': 33836.658, 'text': 'The user is not limited to any predefined functions and simplifies the complex code of a predefined function by writing a simple code.', 'start': 33829.609, 'duration': 7.049}, {'end': 33838.132, 'text': 'Apart from this.', 'start': 33837.432, 'duration': 0.7}, {'end': 33844.795, 'text': 'Let me also tell you that you know, the user defined function returns a scalar value or a table now to create a user defined function.', 'start': 33838.472, 'duration': 6.323}, {'end': 33847.075, 'text': 'You can refer to the example that you can see on my screen.', 'start': 33844.835, 'duration': 2.24}, {'end': 33850.557, 'text': "I've just created a function sample funk and then in the brackets.", 'start': 33847.436, 'duration': 3.121}, {'end': 33852.197, 'text': "I've put either it num int.", 'start': 33850.697, 'duration': 1.5}, {'end': 33860.24, 'text': "So basically it's an integer and then it returns a table as return select star from customers with customer ID equal to add right now, right?", 'start': 33852.537, 'duration': 7.703}], 'summary': 'User simplifies predefined function, creates user-defined function returning table with customer id.', 'duration': 30.631, 'max_score': 33829.609, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833829609.jpg'}, {'end': 34046.902, 'src': 'embed', 'start': 34014.352, 'weight': 4, 'content': [{'end': 34017.956, 'text': 'That is what is the command dbcc check DB used for.', 'start': 34014.352, 'duration': 3.604}, {'end': 34025.082, 'text': 'so the command dbcc check DB is used to basically check the physical and the logical Integrity of all the objects in the mentioned database.', 'start': 34017.956, 'duration': 7.126}, {'end': 34029.286, 'text': 'So to do that it basically performs the operations that you can see in my screen.', 'start': 34025.802, 'duration': 3.484}, {'end': 34037.593, 'text': 'It runs the dbcc check a log on the mentioned database and on every table and view in the database the dbcc check table command is executed.', 'start': 34029.626, 'duration': 7.967}, {'end': 34046.902, 'text': 'It also runs the dbcc check catalog on the database and then it validates the contents of every index view in the mention database after that.', 'start': 34038.26, 'duration': 8.642}], 'summary': 'The dbcc check db command checks physical and logical integrity of all objects in the database, running operations on tables, views, and indexes.', 'duration': 32.55, 'max_score': 34014.352, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834014352.jpg'}, {'end': 34121.18, 'src': 'embed', 'start': 34092.805, 'weight': 5, 'content': [{'end': 34098.528, 'text': 'So the check Constraint in SQL Server is basically used to limit the values or the type of data stored in a column.', 'start': 34092.805, 'duration': 5.723}, {'end': 34104.891, 'text': 'Now once you apply the check Constraint on a single column, you can go forward and apply specific values for that particular column.', 'start': 34098.768, 'duration': 6.123}, {'end': 34106.612, 'text': 'See the example on my screen.', 'start': 34105.312, 'duration': 1.3}, {'end': 34111.735, 'text': "I've created a table customers with few columns like customer ID, first name, age and City,", 'start': 34106.712, 'duration': 5.023}, {'end': 34116.557, 'text': "and then I've applied the check Constraint and then I've given the name to be check underscore customer,", 'start': 34111.735, 'duration': 4.822}, {'end': 34121.18, 'text': "and the condition that I've applied is basically age should be greater than 20 and City should be Hyderabad.", 'start': 34116.557, 'duration': 4.623}], 'summary': 'Sql server check constraint limits data in column, e.g., age>20, city=hyderabad.', 'duration': 28.375, 'max_score': 34092.805, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834092805.jpg'}, {'end': 34160.122, 'src': 'embed', 'start': 34130.017, 'weight': 6, 'content': [{'end': 34134.422, 'text': 'and the coil is function is used to basically return the first normal expression within the arguments.', 'start': 34130.017, 'duration': 4.405}, {'end': 34140.508, 'text': 'So the coil is command is basically used to return a non-null value from more than a single column in arguments.', 'start': 34135.202, 'duration': 5.306}, {'end': 34146.332, 'text': 'So, to use the coalesce command, you can refer to the example that you can see on my screen, that is, select coalesce.', 'start': 34141.428, 'duration': 4.904}, {'end': 34149.835, 'text': "and then I've mentioned customer ID, customer name amount from customers right?", 'start': 34146.332, 'duration': 3.503}, {'end': 34155.099, 'text': 'So basically, customers was a table and customer ID, customer name amount are basically a column names right?', 'start': 34149.855, 'duration': 5.244}, {'end': 34160.122, 'text': "So I'm basically trying to return the first normal expressions from customer ID, customer name and amount.", 'start': 34155.519, 'duration': 4.603}], 'summary': "The coalesce function returns the first non-null value from multiple columns in a table, as demonstrated with the 'select coalesce' example referencing customer id, customer name, and amount from the customers table.", 'duration': 30.105, 'max_score': 34130.017, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834130017.jpg'}, {'end': 34187.111, 'src': 'embed', 'start': 34161.123, 'weight': 7, 'content': [{'end': 34165.947, 'text': "Now, let's move forward with the next question that is explained the usage of floor function in sequel server.", 'start': 34161.123, 'duration': 4.824}, {'end': 34171.576, 'text': 'that the floor function is used to round up a non integer value to the previous least integer value.', 'start': 34166.508, 'duration': 5.068}, {'end': 34175.682, 'text': 'This function returns a unique value after rounding of the digits down.', 'start': 34171.876, 'duration': 3.806}, {'end': 34179.829, 'text': 'the syntaxes, as you can see in my screen, is basically floor and then you mentioned the expression.', 'start': 34175.682, 'duration': 4.147}, {'end': 34183.809, 'text': 'And if you also want to know how to use it, you can see the example on my screen.', 'start': 34180.586, 'duration': 3.223}, {'end': 34187.111, 'text': "I've just mentioned floor and then I've mentioned in brackets 7.3.", 'start': 34183.849, 'duration': 3.262}], 'summary': 'Floor function in sql server rounds 7.3 down to 7.', 'duration': 25.988, 'max_score': 34161.123, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834161123.jpg'}, {'end': 34224.78, 'src': 'embed', 'start': 34196.98, 'weight': 8, 'content': [{'end': 34202.124, 'text': 'So to check locks in a database you have to use an inbuilt stored procedure that is SP underscore lock.', 'start': 34196.98, 'duration': 5.144}, {'end': 34205.236, 'text': 'So the syntax is again as you can see on my screen.', 'start': 34202.933, 'duration': 2.303}, {'end': 34213.224, 'text': "It's SP log and then you mentioned either it SP ID equal to 1 and session ID 1 you can go forward and mention the session ID 2 and so on right.", 'start': 34205.456, 'duration': 7.768}, {'end': 34217.994, 'text': 'You can also go forward and explain the command to the interviewer by giving them an example, right?', 'start': 34213.931, 'duration': 4.063}, {'end': 34224.78, 'text': 'So, for example, if you want to list all the locks currently held in an instance of a database engine, you can use the command SP underscore lock,', 'start': 34218.415, 'duration': 6.365}], 'summary': 'Use sp_lock to check database locks and list all current locks held.', 'duration': 27.8, 'max_score': 34196.98, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834196980.jpg'}, {'end': 34486.449, 'src': 'embed', 'start': 34457.037, 'weight': 9, 'content': [{'end': 34461.499, 'text': 'That is what is the purpose of update statistics and scope identity function.', 'start': 34457.037, 'duration': 4.462}, {'end': 34466.641, 'text': 'the update statistics function is basically used to update the information used by the indexes,', 'start': 34461.499, 'duration': 5.142}, {'end': 34472.303, 'text': 'such as the distribution of key values for one or more statistics group is mentioned in the index view or the table.', 'start': 34466.641, 'duration': 5.662}, {'end': 34474.424, 'text': 'Coming to scope identity function.', 'start': 34472.743, 'duration': 1.681}, {'end': 34479.926, 'text': 'The scope identity function is used to create identity values for tables in the current execution scope.', 'start': 34474.784, 'duration': 5.142}, {'end': 34482.067, 'text': "Now, let's move forward with the next question.", 'start': 34480.386, 'duration': 1.681}, {'end': 34486.449, 'text': 'That is what do you understand by physical only option in DVCC check TV command?', 'start': 34482.107, 'duration': 4.342}], 'summary': 'Update statistics refreshes index information. scope identity creates identity values. dvcc check tv command has a physical only option.', 'duration': 29.412, 'max_score': 34457.037, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834457037.jpg'}, {'end': 34507.926, 'src': 'embed', 'start': 34487.089, 'weight': 10, 'content': [{'end': 34496.293, 'text': 'Well, the physical only option is used to limit the checking to the Integrity of physical structure of the record headers page and also the allocation consistency of the database.', 'start': 34487.089, 'duration': 9.204}, {'end': 34502.625, 'text': 'The physical only check is used to provide a small overhead check of the physical consistency of the database,', 'start': 34496.883, 'duration': 5.742}, {'end': 34507.926, 'text': 'and this option also causes a shorter runtime for DBCC check DB on large databases.', 'start': 34502.625, 'duration': 5.301}], 'summary': 'Physical only option reduces runtime for dbcc check db on large databases.', 'duration': 20.837, 'max_score': 34487.089, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834487089.jpg'}, {'end': 34601.455, 'src': 'embed', 'start': 34575.259, 'weight': 11, 'content': [{'end': 34582.243, 'text': 'Well, integration Services is a platform offered by Microsoft to build Enterprise level data transformation Solutions and integration.', 'start': 34575.259, 'duration': 6.984}, {'end': 34588.007, 'text': 'Now the services solve complex business problems by loading data warehouses, perform data wrangling,', 'start': 34582.964, 'duration': 5.043}, {'end': 34591.349, 'text': 'copy or download files and manage sequel server objects.', 'start': 34588.007, 'duration': 3.342}, {'end': 34598.013, 'text': 'also, integration services can extract and transform data from a wide variety of sources, such as relational data sources.', 'start': 34591.349, 'duration': 6.664}, {'end': 34601.455, 'text': 'XML data files load the data into more than a single database.', 'start': 34598.013, 'duration': 3.442}], 'summary': 'Microsoft integration services: platform for enterprise data solutions, handling diverse data sources and loading into multiple databases.', 'duration': 26.196, 'max_score': 34575.259, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834575259.jpg'}, {'end': 34656.16, 'src': 'embed', 'start': 34629.407, 'weight': 12, 'content': [{'end': 34633.05, 'text': 'That is what do you understand by hot fixes and patches in sequel server?', 'start': 34629.407, 'duration': 3.643}, {'end': 34637.552, 'text': 'So hot fixes are single, cumulative software packages applied to life systems.', 'start': 34633.51, 'duration': 4.042}, {'end': 34642.415, 'text': 'So basically this includes one or more files used to address a problem in a software product.', 'start': 34637.913, 'duration': 4.502}, {'end': 34649.92, 'text': 'The patches are a program installed on the machines to rectify the problem occurred in the system and ensure the security of that particular system.', 'start': 34642.916, 'duration': 7.004}, {'end': 34656.16, 'text': 'So basically hot fixes are a kind of patch offered by Microsoft SQL Server to address specific issues.', 'start': 34650.695, 'duration': 5.465}], 'summary': 'Hot fixes and patches in sql server are single, cumulative software packages that address specific issues and ensure system security.', 'duration': 26.753, 'max_score': 34629.407, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834629407.jpg'}, {'end': 34702.494, 'src': 'embed', 'start': 34669.671, 'weight': 13, 'content': [{'end': 34674.675, 'text': 'symmetric keys, asymmetric keys, transact SQL functions and certificates, right?', 'start': 34669.671, 'duration': 5.004}, {'end': 34679.379, 'text': 'So these are few encryption mechanisms through which you can protect your data or you can encrypt the data in a database.', 'start': 34674.695, 'duration': 4.684}, {'end': 34681.943, 'text': "Now, let's move forward with the next question.", 'start': 34680.322, 'duration': 1.621}, {'end': 34686.545, 'text': 'That is what are the options which was me sent to allow the usage of optimistic models,', 'start': 34681.983, 'duration': 4.562}, {'end': 34696.831, 'text': 'where the options that must be set to allow the usage of optimistic models are the read underscore committed underscore snapshot option and allow underscore snapshot underscore isolation option,', 'start': 34686.545, 'duration': 10.286}, {'end': 34702.494, 'text': 'right?. So, basically, these two options are used, that is, the read committed snapshot and the allow snapshot isolation option.', 'start': 34696.831, 'duration': 5.663}], 'summary': 'Encryption mechanisms include symmetric & asymmetric keys. options for optimistic models: read_committed_snapshot & allow_snapshot_isolation.', 'duration': 32.823, 'max_score': 34669.671, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834669671.jpg'}, {'end': 34752.975, 'src': 'embed', 'start': 34731.374, 'weight': 15, 'content': [{'end': 34740.063, 'text': 'So the most common performance issues in sequel server are fragmentation input output bottlenecks blocking queues deadlocks and unused indexes.', 'start': 34731.374, 'duration': 8.689}, {'end': 34746.009, 'text': "So these are few common performance issues that I had seen, but if you think you've also seen any other issues,", 'start': 34740.823, 'duration': 5.186}, {'end': 34747.971, 'text': 'you can definitely go forward and mention it.', 'start': 34746.009, 'duration': 1.962}, {'end': 34752.975, 'text': 'So I would say in this particular question also, please explain with examples on which scenarios.', 'start': 34748.591, 'duration': 4.384}], 'summary': 'Common performance issues in sequel server include fragmentation, input/output bottlenecks, blocking queues, deadlocks, and unused indexes.', 'duration': 21.601, 'max_score': 34731.374, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9834731374.jpg'}], 'start': 33640.928, 'title': 'Sql server functions and q&a', 'summary': 'Covers a wide range of sql server aspects, including analysis services, mirroring, cursors, database design, user-defined functions, database performance, relationships, and joints in sql. it also delves into the usage of dbcc check db command, check constraint, coalesce function, floor function, sp lock command, record count retrieval, sine function, weekday determination, database renaming, finding the fifth highest amount, and table deletion. additionally, it addresses topics like update statistics, physical only option in dbcc check, locks in read operations, differences between having and where clauses, integration services, hot fixes and patches, encryption mechanisms, options for optimistic models, and common performance issues in sql server.', 'chapters': [{'end': 34014.332, 'start': 33640.928, 'title': 'Sql server q&a highlights', 'summary': 'Covered various aspects of sql server including analysis services, mirroring, cursors, database design, user-defined functions, database performance, relationships in databases, and joints in sql.', 'duration': 373.404, 'highlights': ['Analysis services in Microsoft SQL Server is an analytical data engine used in business analytics and decision support, providing Enterprise grade semantic models for client applications and reports such as power bi, Microsoft Excel and other visualization tools.', 'Mirroring in SQL Server is designed to maintain a hot standby server that is consistent with the primary server in terms of the transaction, with advantages including automatic failover mechanism, efficiency, and real-time synchronization.', 'SQL Server based cursors are used when working on a record at an instance of time, rather than taking all the data from a table as a bulk, and are not preferred for large volumes of data as it affects performance.', 'Physical and logical design plays an important role in the performance of SQL Server based applications, ensuring correct data capture, proper relationships, and reduced data redundancy.', 'User-defined functions in SQL Server are functions written as per the needs of the user, providing the flexibility to simplify complex code of predefined functions and returning scalar value or a table.']}, {'end': 34457.017, 'start': 34014.352, 'title': 'Dbcc check db & sql server functions', 'summary': 'Covers the usage of dbcc check db command, check constraint, coalesce function, floor function, sp lock command, ways to get record count, sine function, finding first weekday, renaming database, finding fifth highest amount, and deleting a table in sql server.', 'duration': 442.665, 'highlights': ['The chapter covers the usage of DBCC Check DB command, check constraint, coalesce function, floor function, SP lock command, ways to get record count, sine function, finding first weekday, renaming database, finding fifth highest amount, and deleting a table in SQL Server.', 'The DBCC Check DB command is used to check the physical and logical integrity of all the objects in the mentioned database.', 'The check constraint in SQL Server limits the values or the type of data stored in a column.', 'The coalesce function is used to return the first non-null expression within the arguments in SQL Server.', 'The floor function in SQL Server is used to round up a non-integer value to the previous least integer value.', 'To check locks in a database in Microsoft SQL Server, the SP_lock stored procedure is used.']}, {'end': 34781.717, 'start': 34457.037, 'title': 'Sql server q&a', 'summary': 'Covers sql server topics such as update statistics, physical only option in dbcc check, locks in read operations, differences between having and where clauses, integration services, hot fixes and patches, encryption mechanisms, options for optimistic models, and common performance issues in sql server.', 'duration': 324.68, 'highlights': ['The update statistics function is used to update the information used by the indexes, such as the distribution of key values, and the scope identity function is used to create identity values for tables in the current execution scope.', 'The physical only option in DBCC check is used to limit the checking to the Integrity of physical structure of the record headers page and the allocation consistency of the database, providing a shorter runtime for DBCC check DB on large databases.', 'The locks are held during the transaction with repeatable read and serializable isolation levels, and the having clause is used with the select statement while the where clause is used in a group by clause, behaving like a where clause when the group by function is not used.', 'Integration Services in SQL Server is a platform offered by Microsoft to build Enterprise-level data transformation solutions and includes a good set of built-in tasks, transformations, and graphical tools used for building packages.', 'Hot fixes in SQL Server are single, cumulative software packages applied to live systems, addressing specific issues and ensuring the security of the system, and encryption mechanisms in SQL Server include transparent data encryption, symmetric keys, asymmetric keys, transact SQL functions, and certificates.', 'The options that must be set to allow the usage of optimistic models are the read committed snapshot option and allow snapshot isolation option, and common performance issues in SQL Server include fragmentation, input/output bottlenecks, blocking, deadlocks, and unused indexes.']}], 'duration': 1140.789, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/q_JsgpiuY98/pics/q_JsgpiuY9833640928.jpg', 'highlights': ['Analysis services in Microsoft SQL Server provides Enterprise grade semantic models for client applications and reports.', 'Mirroring in SQL Server maintains a hot standby server with automatic failover mechanism and real-time synchronization.', 'User-defined functions in SQL Server simplify complex code and return scalar value or a table.', 'Physical and logical design in SQL Server ensures correct data capture, proper relationships, and reduced data redundancy.', 'DBCC Check DB command checks the physical and logical integrity of all objects in the database.', 'Check constraint limits the values or type of data stored in a column in SQL Server.', 'Coalesce function returns the first non-null expression within the arguments in SQL Server.', 'Floor function rounds up a non-integer value to the previous least integer value in SQL Server.', 'SP_lock stored procedure is used to check locks in a database in Microsoft SQL Server.', 'Update statistics function updates the information used by the indexes, such as the distribution of key values.', 'Physical only option in DBCC check limits the checking to the Integrity of physical structure of the record headers page and the allocation consistency of the database.', 'Integration Services in SQL Server is a platform to build Enterprise-level data transformation solutions.', 'Hot fixes in SQL Server are single, cumulative software packages addressing specific issues and ensuring system security.', 'Encryption mechanisms in SQL Server include transparent data encryption, symmetric keys, asymmetric keys, transact SQL functions, and certificates.', 'Options for optimistic models in SQL Server include read committed snapshot option and allow snapshot isolation option.', 'Common performance issues in SQL Server include fragmentation, input/output bottlenecks, blocking, deadlocks, and unused indexes.']}], 'highlights': ['SQL is listed in 42.7% of over 32,000 data job listings, making it the current most-in-demand technical talent.', 'SQL plays a crucial role in storing and managing vast amounts of data, with over 2.5 quintillion bytes generated daily.', 'By the end of this video, viewers will have a thorough understanding of SQL, all the way from theory to practical application.', 'The agenda includes SQL basics, operators, normalization, triggers, joins, functions, and a comparison between SQL and MySQL as well as SQL and NoSQL.', 'SQL commands include DDL for database schema, DCL for access control, DML for data manipulation, and TCL for transaction control.', 'SQL queries are portable and interactive, offering the flexibility to execute the same query in different systems without changing the format.', 'SQL is a core of relational database, used for accessing and managing the database with the help of SQL statements.', 'Explanation of table constraints and their importance in maintaining data integrity.', 'The chapter explains the concepts of first, second, and third normal forms, with examples and necessary conditions.', 'The chapter explores the creation and usage of SQL triggers, including before and after insert triggers, along with their advantages and disadvantages.', 'The chapter delves into Boyce-Chord normal form.', 'SQL Case Expressions: Syntax and Example', 'SQL in operator used to filter results from multiple values, making queries more readable and efficient.', 'Natural join reduces columns returned.', 'SQL Server provides high availability and disaster recovery, including fast failover and easy setup, with unified solutions for high availability and disaster recovery on Linux and Windows.', 'The chapter covers controlling privileges and rights for databases and tables using revoke and transaction control language commands.', 'The chapter covers set operations including Union, Intersect, and Except.', 'SQL Server is a licensed product developed by Microsoft, while MySQL is an open-source software developed by Oracle, and the installation process for MySQL is easier compared to SQL Server.', 'Analysis services in Microsoft SQL Server provides Enterprise grade semantic models for client applications and reports.', 'Mirroring in SQL Server maintains a hot standby server with automatic failover mechanism and real-time synchronization.', 'User-defined functions in SQL Server simplify complex code and return scalar value or a table.', 'Physical and logical design in SQL Server ensures correct data capture, proper relationships, and reduced data redundancy.', 'DBCC Check DB command checks the physical and logical integrity of all objects in the database.', 'Check constraint limits the values or type of data stored in a column in SQL Server.', 'Coalesce function returns the first non-null expression within the arguments in SQL Server.', 'Floor function rounds up a non-integer value to the previous least integer value in SQL Server.', 'SP_lock stored procedure is used to check locks in a database in Microsoft SQL Server.', 'Update statistics function updates the information used by the indexes, such as the distribution of key values.', 'Physical only option in DBCC check limits the checking to the Integrity of physical structure of the record headers page and the allocation consistency of the database.', 'Integration Services in SQL Server is a platform to build Enterprise-level data transformation solutions.', 'Hot fixes in SQL Server are single, cumulative software packages addressing specific issues and ensuring system security.', 'Encryption mechanisms in SQL Server include transparent data encryption, symmetric keys, asymmetric keys, transact SQL functions, and certificates.', 'Options for optimistic models in SQL Server include read committed snapshot option and allow snapshot isolation option.', 'Common performance issues in SQL Server include fragmentation, input/output bottlenecks, blocking, deadlocks, and unused indexes.']}