title
SQL Tutorial | Learn SQL | SQL for Beginners | Intellipaat

description
🔵 In this live session on the SQL Tutorial, firstly we will introduce you to what is a database, DBMS, SQL installation, aggregation function, MS SQL Server, Indexes, Encapsulation along with hands-on demo and interview questions. This video is must watch for everyone who wishes to learn SQL and make a career in it. 🔵 Intellipaat SQL course: https://intellipaat.com/microsoft-sql-server-certification-training/ #SQLTutorial #LearnSQL #SQLForBeginners #SQLCourse #SQLTraining #SQLTutorialForBeginners #LearnSQL #SQL #StructuredQueryLanguage #Intellipaat 00:00 - Introduction 01:33 - What is data? 02:38 - What is Database? 04:35 - Why need a Database? 09:03 - DBMS 10:16 - Introduction to SQL 12:52 - SQL: More about Relational Databases 14:00 - Simple SQL Query 17:59 - Applications of SQL 22:45 - SQL Tables: Data Types 28:42 - Roles of Data Base Administrator 32:17 - Evolution of SQL Server 35:16 - Editions of SQL Server 38:50 - Model Database 45:23 - Planning a backup Strategy 54:55 - Database related DMV’s 01:02:04 - System Monitor 01:07:26 - Categories of Data Integrity 01:29:32 - SQL Certification 01:34:49 - Quiz 01:45:28 - Employee 02:02:00 - Hands-on 02:12:22 - Union vs Union all 02:27:16 - Indexes 02:32:37 - Index Structure 02:46:19 - Types of Indexes 02:47:59 - Non-Clustered Index 03:08:23 - Stored Procedures 03:27:27 - Execution plan caching 03:33:05 - Quiz 03:44:10 - Create a new view 03:49:42 - Views 03:59:06 - Restrict to specific columns 04:04:21 - Quiz 04:06:03 - Orphan View 04:19:49 - DML Operations on a view 04:21:34 - Insert 04:25:41 - Transaction 04:31:46 - When do we use a transaction? 04:53:00 - Error/ Exception Handiling 05:00:58 - Rollback Transaction 05:15:29 - SQL vs MySql 05:18:14 - Skills to have: SQL Developer 05:23:00 - What about Certifications? 05:27:46 - SQL Interview questions 🔵 To subscribe to the Intellipaat channel & get regular updates on videos: http://bit.ly/Intellipaat 🔵 Read complete SQL Server tutorial here: http://bit.ly/2F9oqCL 🔵 Get SQL Server cheat sheet here: http://bit.ly/2KljiiP 🔵 Interested to learn SQL still more? Please check similar SQL blogs here:- https://goo.gl/d3a6H2 Are you looking for something more? Enroll in our MS SQL Server course and become a certified SQL professional (https://intellipaat.com/microsoft-sql-server-certification-training/). It is a 16 hrs training where you can learn SQL through instructor-led training provided by Intellipaat which is completely aligned with industry standards and certification bodies. If you’ve enjoyed this SQL for Beginners, Like us and Subscribe to our channel for more similar informative SQL course tutorials. Got any questions about the SQL tutorial for beginners video? Ask us in the comment section below. ---------------------------- Intellipaat Edge 1. 24*7 Lifetime Access & Support 2. Flexible Class Schedule 3. Job Assistance 4. Mentors with +14 yrs 5. Industry Oriented Courseware 6. Lifetime free Course Upgrade ------------------------------ 🔵 Why should you opt for a SQL career? SQL optimization has always been a popular topic in database management. SQL Database optimization can be an extremely difficult task, in particular for large-scale data wherever a minute variation can result or impact drastically on the performance. So learning this skill will definitely help you grab the best jobs in top MNCs after finishing Intellipaat SQL online training. The entire Intellipaat SQL course is in line with the industry needs. There is a huge demand for SQL certified professionals. The salaries for SQL professionals are very good. Hence this Intellipaat SQL database tutorial for beginners is your stepping stone to a successful career! ------------------------------ For more information: Call Our Course Advisors IND: +91-7022374614 , US: 1-800-216-8930 (Toll-Free) sales@intellipaat.com Website: https://intellipaat.com/microsoft-sql-server-certification-training/ Facebook: https://www.facebook.com/intellipaatonline/ LinkedIn: https://www.linkedin.com/in/intellipaat/ Twitter: https://twitter.com/Intellipaat Telegram: https://t.me/s/Learn_with_Intellipaat Instagram: https://www.instagram.com/intellipaat Meetup: https://www.meetup.com/Intellipaat/

detail
{'title': 'SQL Tutorial | Learn SQL | SQL for Beginners | Intellipaat', 'heatmap': [{'end': 17672.357, 'start': 17450.709, 'weight': 1}], 'summary': "This sql tutorial by intellipaat covers a comprehensive course from basics to advanced levels, emphasizing data's importance, sql applications, and mysql as a starting point. it also delves into sql server essentials, dynamic management views, sql functions, indexing, stored procedures, data management, transactions, error handling, and certifications, with practical examples and career insights like an average salary of $72,282 per year in the usa and 6.5 lpa in india.", 'chapters': [{'end': 123.374, 'segs': [{'end': 58.175, 'src': 'embed', 'start': 17.932, 'weight': 0, 'content': [{'end': 23.077, 'text': 'Hi everyone, I welcome you all to the live session on SQL full course by Intellipaat.', 'start': 17.932, 'duration': 5.145}, {'end': 31.424, 'text': 'This session is conducted by multiple experts who will be teaching you everything about SQL, from basics to advanced level,', 'start': 24.018, 'duration': 7.406}, {'end': 33.867, 'text': 'along with hands-on demo and interview preparation.', 'start': 31.424, 'duration': 2.443}, {'end': 41.434, 'text': 'Before we begin the session, make sure to hit the subscribe button and also hit on the bell icon so that you will never miss an update from us.', 'start': 35.028, 'duration': 6.406}, {'end': 44.52, 'text': "on saying that, let's see the agenda of the day.", 'start': 42.478, 'duration': 2.042}, {'end': 49.626, 'text': 'Firstly, we will begin with introduction to database and database management system.', 'start': 45.041, 'duration': 4.585}, {'end': 58.175, 'text': 'Then, after that, we will explain you a few basic topics of SQL, like aggregate function indexes and encapsulation,', 'start': 50.287, 'duration': 7.888}], 'summary': 'Live session on sql full course by intellipaat, covering basics to advanced level, with hands-on demo and interview preparation.', 'duration': 40.243, 'max_score': 17.932, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17932.jpg'}], 'start': 17.932, 'title': 'Sql full course overview', 'summary': 'Covers a comprehensive sql full course by intellipaat, including topics from basics to advanced level, hands-on demos, and interview preparation, conducted by multiple experts, and presents the agenda of the session, emphasizing the importance of data and its application in the world.', 'chapters': [{'end': 123.374, 'start': 17.932, 'title': 'Sql full course overview', 'summary': 'Covers a comprehensive sql full course by intellipaat, including topics from basics to advanced level, hands-on demos, and interview preparation, conducted by multiple experts, and presents the agenda of the session, emphasizing the importance of data and its application in the world.', 'duration': 105.442, 'highlights': ['The session covers a comprehensive SQL full course by Intellipaat, including topics from basics to advanced level, hands-on demos, and interview preparation, conducted by multiple experts. (Relevance: 5)', 'Emphasizes the importance of data and its application in the world, defining data as facts related to any object, like age being a numeric data having a wide range of values, and the ability to derive meaningful insights from it. (Relevance: 4)']}], 'duration': 105.442, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17932.jpg', 'highlights': ['The session covers a comprehensive SQL full course by Intellipaat, including topics from basics to advanced level, hands-on demos, and interview preparation, conducted by multiple experts.', 'Emphasizes the importance of data and its application in the world, defining data as facts related to any object, like age being a numeric data having a wide range of values, and the ability to derive meaningful insights from it.']}, {'end': 1397.832, 'segs': [{'end': 1330.907, 'src': 'embed', 'start': 1305.692, 'weight': 0, 'content': [{'end': 1311.453, 'text': 'pretty much in the data range that you can see on the screen, a small int is a fairly smaller number, somewhere around 32, 000,', 'start': 1305.692, 'duration': 5.761}, {'end': 1315.815, 'text': 'and tiny int is even smaller where it only supports positive numbers between 0 and 255, guys.', 'start': 1311.453, 'duration': 4.362}, {'end': 1319.2, 'text': 'and then with decimals, as usual, you have a huge number.', 'start': 1316.835, 'duration': 2.365}, {'end': 1324.13, 'text': 'i access to huge numbers on the negative side and on the positive side as well, guys.', 'start': 1319.2, 'duration': 4.93}, {'end': 1325.492, 'text': 'so this brings us to characters.', 'start': 1324.13, 'duration': 1.362}, {'end': 1327.826, 'text': 'There are three main data types.', 'start': 1326.826, 'duration': 1}, {'end': 1328.586, 'text': 'One is the car.', 'start': 1327.926, 'duration': 0.66}, {'end': 1330.907, 'text': 'One is the varchar and the text again, pretty much.', 'start': 1328.646, 'duration': 2.261}], 'summary': 'Data types include small int, tiny int, and decimal for numeric values, with support for positive and negative numbers. three main character data types are car, varchar, and text.', 'duration': 25.215, 'max_score': 1305.692, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co1305692.jpg'}], 'start': 123.374, 'title': 'Managing data and databases with sql', 'summary': 'Covers data forms, importance of databases, and sql operations. it emphasizes systematic data management, benefits of databases, and the significance of sql in data manipulation and retrieval with examples. additionally, it discusses sql applications, data types, and recommends mysql as a starting point for mastering sql.', 'chapters': [{'end': 205.617, 'start': 123.374, 'title': 'Understanding data and databases', 'summary': 'Discusses the various forms of data, including video data, social media content, and the concept of a database, emphasizing the need for systematic collection, storage, maintenance, and manipulation of data.', 'duration': 82.243, 'highlights': ['The chapter explains the concept of a database as a systematic collection of data, emphasizing the need for storage, maintenance, and manipulation of data.', 'Video data is highlighted as an example of unstructured data, contributing to the understanding of various forms of data.', 'The transcript mentions the generation of data through activities such as messaging, tweeting, sharing photos, and posting updates on social media platforms, emphasizing the diverse sources of data.']}, {'end': 608.687, 'start': 205.617, 'title': 'Importance of databases in managing data', 'summary': 'Discusses the significance of databases in managing data, including examples of hard copy databases, the importance of data management in power generation, and the benefits of using databases for efficiency, accuracy, security, and data integrity.', 'duration': 403.07, 'highlights': ['Databases are crucial for managing large amounts of data efficiently, as using spreadsheets becomes extremely difficult when handling thousands and millions of records, impacting efficiency and time.', 'The accuracy of data is a key reason for needing a database, as manual validation of thousands and millions of entries in spreadsheets becomes near impossible, leading to a significant decrease in efficiency.', 'The ease of data uploading is another crucial reason for utilizing databases, offering flexibility to update, manipulate, and edit data concurrently by multiple users, significantly improving efficiency.']}, {'end': 942.254, 'start': 608.687, 'title': 'Introduction to sql and relational databases', 'summary': 'Introduces sql as a standard language for dealing with relational databases, explaining its operations and the concept of relational databases, highlighting the importance of sql in data manipulation and the use of queries to retrieve specific data from tables with examples. it also emphasizes the value of understanding sql for beginners and the various flavors of sql, recommending mysql as a starting point for mastering sql.', 'duration': 333.567, 'highlights': ["SQL is a standard language for dealing with relational databases and can be used to create, read, update, and delete database records. SQL's role in data manipulation and database record management.", 'Introduction to the concept of relational databases and the importance of interdependency and interrelationships among data. Explanation of the relational model and the organization of data in tables.', 'Explanation of SQL queries and their role in retrieving specific data from databases with examples. The use of SQL queries to retrieve specific data based on conditions, demonstrated through an example of a SELECT statement with a condition.', 'Recommendation for beginners to start with MySQL and the value of mastering MySQL for transitioning to other flavors of SQL. Advice for beginners on starting with MySQL and its relevance for learning other SQL flavors.']}, {'end': 1397.832, 'start': 942.554, 'title': 'Sql applications and data types', 'summary': 'Covers the applications of sql, including data definition language (ddl), data manipulation language (dml), data control language (dcl), client-server and three-tier architecture, and also explains the concept of fields and records in sql tables, as well as the different data types supported by sql.', 'duration': 455.278, 'highlights': ["SQL can be used as a data definition language (DDL) to create and define databases, allowing for individual creation, structure definition, and deletion, with the potential for scalability among multiple users. SQL's application as a data definition language (DDL) allows for the creation, definition, and deletion of databases, providing the potential for scalability among hundreds and thousands of users.", "SQL's application as a data manipulation language (DML) enables altering and managing existing data within the database, including updating, deleting, and adding data within the table. SQL's application as a data manipulation language (DML) allows for the alteration and management of existing data within the database, including updating, deleting, and adding data within the table.", 'The concept of fields and records in SQL tables is explained, with fields representing columns containing specific data and records providing complete information about individual entities within the dataset. The concept of fields and records in SQL tables is explained, where fields represent columns containing specific data and records provide complete information about individual entities within the dataset.', 'Various data types supported by SQL, including numerical data (integer, decimal), character data (char, varchar, text), and date and time data, are discussed, along with their corresponding ranges and limitations. The tutorial covers various data types supported by SQL, such as numerical data (integer, decimal), character data (char, varchar, text), and date and time data, along with their corresponding ranges and limitations.']}], 'duration': 1274.458, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co123374.jpg', 'highlights': ['The chapter explains the concept of a database as a systematic collection of data, emphasizing the need for storage, maintenance, and manipulation of data.', 'Databases are crucial for managing large amounts of data efficiently, as using spreadsheets becomes extremely difficult when handling thousands and millions of records, impacting efficiency and time.', "SQL is a standard language for dealing with relational databases and can be used to create, read, update, and delete database records. SQL's role in data manipulation and database record management.", 'SQL can be used as a data definition language (DDL) to create and define databases, allowing for individual creation, structure definition, and deletion, with the potential for scalability among multiple users.']}, {'end': 3228.179, 'segs': [{'end': 1510.849, 'src': 'embed', 'start': 1483.204, 'weight': 0, 'content': [{'end': 1486.967, 'text': "Now I only have one instance of SQL Server so I'll be installing a new one.", 'start': 1483.204, 'duration': 3.763}, {'end': 1495.814, 'text': "So I'll select this perform a new installation of SQL Server 2017 and then I'll select this free edition.", 'start': 1487.447, 'duration': 8.367}, {'end': 1503.301, 'text': "I'll click on next, after that I'll accept this license agreement and again I'll click on next.", 'start': 1495.814, 'duration': 7.487}, {'end': 1505.564, 'text': 'so we have all of these features over here.', 'start': 1503.301, 'duration': 2.263}, {'end': 1508.326, 'text': 'so I would just need the database engine services.', 'start': 1505.564, 'duration': 2.762}, {'end': 1510.849, 'text': "so I'll select this and again I'll click on next.", 'start': 1508.326, 'duration': 2.523}], 'summary': 'Installing new sql server 2017 instance with free edition and database engine services.', 'duration': 27.645, 'max_score': 1483.204, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co1483204.jpg'}, {'end': 1573.33, 'src': 'embed', 'start': 1541.9, 'weight': 4, 'content': [{'end': 1544.761, 'text': "I'll select the mixed mode and then enter the desired password.", 'start': 1541.9, 'duration': 2.861}, {'end': 1547.062, 'text': 'Let me put in my password over here.', 'start': 1545.622, 'duration': 1.44}, {'end': 1552.841, 'text': "I'll add the current user.", 'start': 1551.781, 'duration': 1.06}, {'end': 1557.063, 'text': 'So the current user has been added.', 'start': 1555.723, 'duration': 1.34}, {'end': 1558.444, 'text': "Now I'll click on next again.", 'start': 1557.383, 'duration': 1.061}, {'end': 1563.306, 'text': 'Right So we are finally ready to install SQL Server.', 'start': 1560.925, 'duration': 2.381}, {'end': 1564.606, 'text': "I'll click on install.", 'start': 1563.806, 'duration': 0.8}, {'end': 1573.33, 'text': 'Right So once the installation is done, I would also have to install the SQL Server management tools.', 'start': 1567.707, 'duration': 5.623}], 'summary': 'Installing sql server with mixed mode, adding user, and installing management tools.', 'duration': 31.43, 'max_score': 1541.9, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co1541900.jpg'}, {'end': 2718.727, 'src': 'embed', 'start': 2695.433, 'weight': 2, 'content': [{'end': 2704.356, 'text': 'Now, again, if I want to change this recovery model from full to bulk log, all I have to do is type in bulk underscore logged over here.', 'start': 2695.433, 'duration': 8.923}, {'end': 2705.796, 'text': "I'll click on execute.", 'start': 2705.016, 'duration': 0.78}, {'end': 2709.224, 'text': "Right, so this time the recovery model which we're using is bulk logged.", 'start': 2706.243, 'duration': 2.981}, {'end': 2712.345, 'text': "Now let's see how can we plan our backup strategy.", 'start': 2709.864, 'duration': 2.481}, {'end': 2718.727, 'text': 'So, after we have selected a recovery model that meets our business requirements for a specific database,', 'start': 2712.965, 'duration': 5.762}], 'summary': 'Changed recovery model to bulk logged for database backup strategy', 'duration': 23.294, 'max_score': 2695.433, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co2695433.jpg'}], 'start': 1397.832, 'title': 'Sql server essentials', 'summary': 'Covers the installation process, roles and types of administrators, evolution of sql server, databases and instances, and database recovery models and backup strategies, providing comprehensive knowledge on sql server essentials.', 'chapters': [{'end': 1648.538, 'start': 1397.832, 'title': 'Installing microsoft sql server', 'summary': 'Details the installation process of microsoft sql server, including downloading the developer edition, selecting installation options, and installing sql server management studio, to connect with the database and write sql commands.', 'duration': 250.706, 'highlights': ['The chapter describes downloading the developer edition of Microsoft SQL Server, which includes the process of selecting and installing the software, and highlights the importance of SQL Server Management Studio for connecting with the database and writing SQL commands.', 'The installation process involves selecting installation options, such as choosing the database engine services and specifying the instance for the server, including selecting the mixed mode authentication and setting the desired password for the SQL Server.', 'Instructions are provided for installing SQL Server Management Studio, including downloading the latest version and connecting to the database to start writing SQL commands.']}, {'end': 1875.761, 'start': 1650.94, 'title': 'Roles and types of database administrator', 'summary': 'Explains the roles and types of database administrators, including responsibilities such as installation, maintenance, backup, security, and performance monitoring, and different types such as production dba, application dba, development dba, uat dba, and data warehouse dba.', 'duration': 224.821, 'highlights': ['A database administrator (DBA) performs activities related to maintaining a successful database environment, including initial installation, configuration, backup, security, access control, and performance monitoring.', 'The roles of a DBA include initial installation and configuration of new databases, handling ongoing maintenance, backup and recovery planning, maintaining database security, access control, and monitoring databases for performance issues.', 'Types of DBA include production DBA, application DBA, development DBA, UAT DBA, and data warehouse DBA, with responsibilities ranging from maintaining databases within an organization to focusing on specific business applications, testing and development, and analyzing data for business intelligence.']}, {'end': 2185.858, 'start': 1876.081, 'title': 'Evolution of sql server', 'summary': 'Covers the evolution of microsoft sql server, starting from the 1998 release of sql server 7.0 to the latest release in 2017, highlighting key milestones and capabilities, such as scalability improvements, bi enhancements, new data types, and platform expansion.', 'duration': 309.777, 'highlights': ['SQL Server 7.0 release in 1998 Microsoft re-architected the code database engine code to address scalability issues, transforming it into a true enterprise-level database.', 'SQL Server 2012 Introduced PowerView for graphical data navigation, always-on availability groups, and extended BI capabilities.', 'SQL Server 2016 release Added built-in JSON support, a poly-based query engine for integration with external data, and cross-platform deployment options.', 'SQL Server 2017 release Expanded platform support to include Linux, Windows, Ubuntu, and Docker, as well as added support for Python and machine learning within SQL Server.', 'Enterprise edition of SQL Server Includes core database engine, add-on services, and tools for managing a SQL server cluster, supporting databases as large as 524 petabytes and 640 logical processors.']}, {'end': 2526.66, 'start': 2186.459, 'title': 'Sql server databases and instances', 'summary': 'Explains the limitations of sql server in terms of processor, memory, and database files, the concept of instances, system databases in sql server, and the process of installing, restoring, and backing up databases, emphasizing the importance of a well-planned backup and restore strategy.', 'duration': 340.201, 'highlights': ['The chapter explains the limitations of SQL Server, such as being limited to using one processor, one GB memory, and 10 GB database files. This highlights the limitations of SQL Server in terms of processor, memory, and database files, providing quantifiable data.', 'The system databases in SQL Server are master, model, MSDB, and tempdb, each serving specific functions such as storing system level information, acting as a template for new user databases, and holding backup history. This provides detailed information about the system databases in SQL Server, including their specific functions and purpose.', 'The process of installing, restoring, and backing up databases is explained, highlighting the importance of a well-planned backup and restore strategy for protecting critical data stored in SQL Server databases. This highlights the importance and process of installing, restoring, and backing up databases, emphasizing the importance of a well-planned backup and restore strategy.']}, {'end': 3228.179, 'start': 2527.061, 'title': 'Database recovery models and backup strategies', 'summary': 'Discusses database recovery models, including simple, full, and bulk log models, along with their respective backup strategies, such as full, differential, file, and file group backups. it also covers point-in-time recovery and importing/exporting data in ssms.', 'duration': 701.118, 'highlights': ['The simple recovery model maintains only a minimum amount of information in the transaction log, allowing for easy management but resulting in higher data loss if a data file is damaged. The simple recovery model maintains minimal information in the transaction log, making it easier to manage but leading to higher data loss if a data file is damaged.', 'The full recovery model records all transactions in the transaction log, enabling a comprehensive disaster recovery plan and the ability to restore a database to a specific point in time without losing work due to a damaged file. The full recovery model records all transactions in the transaction log, allowing for a comprehensive disaster recovery plan and the restoration of a database to a specific point in time without losing work due to a damaged file.', 'Differential backups contain all changes made since the last full backup, making them larger in size if a large number of transactions have occurred. Differential backups contain all changes made since the last full backup, resulting in larger size if a large number of transactions have occurred.', 'Point-in-time recovery is applicable only to databases running under the full or bulk-logged recovery model, enabling restoration of a database to a specific point in time. Point-in-time recovery is applicable only to databases running under the full or bulk-logged recovery model, allowing restoration to a specific point in time.', 'Importing data in SSMS involves selecting the data source, browsing the flat file, and setting the destination to store the imported data into the database. Importing data in SSMS includes selecting the data source, browsing the flat file, and setting the destination to store the imported data into the database.']}], 'duration': 1830.347, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co1397832.jpg', 'highlights': ['The Enterprise edition of SQL Server supports databases as large as 524 petabytes and 640 logical processors.', 'SQL Server 2017 release expanded platform support to include Linux, Windows, Ubuntu, and Docker, as well as added support for Python and machine learning within SQL Server.', 'The full recovery model records all transactions in the transaction log, enabling a comprehensive disaster recovery plan and the ability to restore a database to a specific point in time without losing work due to a damaged file.', 'The installation process involves selecting installation options, such as choosing the database engine services and specifying the instance for the server, including selecting the mixed mode authentication and setting the desired password for the SQL Server.', 'A database administrator (DBA) performs activities related to maintaining a successful database environment, including initial installation, configuration, backup, security, access control, and performance monitoring.']}, {'end': 4440.935, 'segs': [{'end': 3304.172, 'src': 'embed', 'start': 3277.433, 'weight': 0, 'content': [{'end': 3283.377, 'text': "So DMVs can be divided into many different categories and in the session, we'll be looking at three of those.", 'start': 3277.433, 'duration': 5.944}, {'end': 3290.542, 'text': "So we'll be looking at database related DMVs, SQL Server OS related DMVs and execution related DMVs.", 'start': 3283.958, 'duration': 6.584}, {'end': 3293.725, 'text': "So let's start with database related DMVs.", 'start': 3291.323, 'duration': 2.402}, {'end': 3304.172, 'text': 'First we have dm underscore db underscore file underscore space usage, which returns the space usage information for each file in the database.', 'start': 3294.405, 'duration': 9.767}], 'summary': 'Exploring three categories of dmvs: database, os, and execution related dmvs.', 'duration': 26.739, 'max_score': 3277.433, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co3277433.jpg'}, {'end': 3378.108, 'src': 'embed', 'start': 3352.299, 'weight': 2, 'content': [{'end': 3361.302, 'text': 'Next we have db underscore partition stats, which returns page and row count information for every partition in the current database.', 'start': 3352.299, 'duration': 9.003}, {'end': 3369.74, 'text': 'And this time we will get the counts for all partitions of all indexes and heaps in the AdventureWorks 2014 database.', 'start': 3361.932, 'duration': 7.808}, {'end': 3371.101, 'text': 'Let me click on execute.', 'start': 3370.06, 'duration': 1.041}, {'end': 3378.108, 'text': 'So these are all of the partition stats for all of the indexes and heaps in the AdventureWorks 2014 database.', 'start': 3371.421, 'duration': 6.687}], 'summary': 'Querying db_partition_stats for adventureworks 2014 yields page and row count info for all partitions of all indexes and heaps.', 'duration': 25.809, 'max_score': 3352.299, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co3352299.jpg'}, {'end': 4006.694, 'src': 'embed', 'start': 3978.114, 'weight': 4, 'content': [{'end': 3983.738, 'text': 'So you have the event class, text data, application name and the username, login name, and so on.', 'start': 3978.114, 'duration': 5.624}, {'end': 3988.301, 'text': 'And we also have the reads, writes and the duration for the reads and writes over here.', 'start': 3984.118, 'duration': 4.183}, {'end': 3991.944, 'text': 'So this is how we can work with SQL server profiler.', 'start': 3989.682, 'duration': 2.262}, {'end': 4000.049, 'text': 'So why do we need data integrity? Well, data integrity is used to maintain accuracy and consistency of data in a table.', 'start': 3992.764, 'duration': 7.285}, {'end': 4006.694, 'text': 'So whatever data we have with us, we need to make sure that it is correct, error-free and useful.', 'start': 4000.47, 'duration': 6.224}], 'summary': 'Using sql server profiler to monitor event class, text data, application name, username, login name, reads, writes, and their durations. data integrity ensures accuracy, consistency, and usefulness of the data in a table.', 'duration': 28.58, 'max_score': 3978.114, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co3978114.jpg'}, {'end': 4124.352, 'src': 'embed', 'start': 4097.138, 'weight': 10, 'content': [{'end': 4102.059, 'text': 'So in case you want to get the maximum of salary from the employee table, this is how you write it.', 'start': 4097.138, 'duration': 4.921}, {'end': 4105.08, 'text': 'You say select max of salary from employee.', 'start': 4102.078, 'duration': 3.002}, {'end': 4108.841, 'text': 'Let me just quickly change the connection to our own database.', 'start': 4105.34, 'duration': 3.501}, {'end': 4113.261, 'text': 'Uh, until the 17th, this one, I guess.', 'start': 4109.581, 'duration': 3.68}, {'end': 4121.569, 'text': 'Okay. So if I want to get the maximum of salary from the employee table, I just say max of salary, and it gives me 5000,', 'start': 4113.761, 'duration': 7.808}, {'end': 4124.352, 'text': 'which means that 5000 is the maximum salary.', 'start': 4121.569, 'duration': 2.783}], 'summary': "To get the maximum salary from the employee table, use 'select max of salary from employee', yielding 5000 as the maximum salary.", 'duration': 27.214, 'max_score': 4097.138, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4097138.jpg'}, {'end': 4182.993, 'src': 'embed', 'start': 4147.613, 'weight': 11, 'content': [{'end': 4149.393, 'text': 'now you can also filter your data.', 'start': 4147.613, 'duration': 1.78}, {'end': 4157.495, 'text': "let's say you want to get only the the maximum salary where the address is India.", 'start': 4149.393, 'duration': 8.102}, {'end': 4161.136, 'text': 'so you say address equal to India.', 'start': 4157.495, 'duration': 3.641}, {'end': 4167.777, 'text': 'so now it will give you all the the maximum salary which belongs to India.', 'start': 4161.136, 'duration': 6.641}, {'end': 4170.558, 'text': "okay, for the employees belonging to India, I think it's IND.", 'start': 4167.777, 'duration': 2.781}, {'end': 4175.286, 'text': 'uh, let me see, what do we have in them?', 'start': 4172.903, 'duration': 2.383}, {'end': 4181.091, 'text': "so let's start from this.", 'start': 4175.286, 'duration': 5.805}, {'end': 4182.993, 'text': 'oh, i have a space, my bad.', 'start': 4181.091, 'duration': 1.902}], 'summary': 'Filter data to find maximum salary for employees in india.', 'duration': 35.38, 'max_score': 4147.613, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4147613.jpg'}, {'end': 4297.538, 'src': 'embed', 'start': 4263.997, 'weight': 12, 'content': [{'end': 4265.918, 'text': 'So there are multiple ways in which you can count it.', 'start': 4263.997, 'duration': 1.921}, {'end': 4274.862, 'text': 'Like for example, you can say count one, you can say count star, or you can say select count employee ID, for example.', 'start': 4265.938, 'duration': 8.924}, {'end': 4276.523, 'text': 'Okay From employee.', 'start': 4274.882, 'duration': 1.641}, {'end': 4280.685, 'text': 'Now all these will return you the exact same output.', 'start': 4276.983, 'duration': 3.702}, {'end': 4283.972, 'text': 'See, it gives 7, 7, and 7.', 'start': 4281.451, 'duration': 2.521}, {'end': 4297.538, 'text': 'The problem with this is that when you say count star, it gets, so this one, it gets all the data into memory and then counts the records.', 'start': 4283.972, 'duration': 13.566}], 'summary': "Multiple ways to count data, all returning 7 records, with 'count star' causing memory load.", 'duration': 33.541, 'max_score': 4263.997, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4263997.jpg'}], 'start': 3228.719, 'title': 'Sql server dynamics and monitoring', 'summary': 'Introduces dynamic management views (dmvs) for monitoring sql server performance and diagnosing issues, covers database partition stats and os thread information, discusses sql server activity monitoring, and explains using sql server profiler for tracing and maintaining data integrity. it also covers aggregate functions in sql with specific examples and demerits.', 'chapters': [{'end': 3352.099, 'start': 3228.719, 'title': 'Understanding dynamic management views', 'summary': 'Introduces dynamic management views (dmvs) as system views and functions that provide metadata of the sql server system state, allowing database administrators to monitor performance and diagnose issues. it explains the properties of dmvs, their categories, and demonstrates the use of specific dmvs to retrieve space usage information for databases and transaction logs.', 'duration': 123.38, 'highlights': ['Dynamic management views (DMVs) provide metadata of the SQL Server system state, allowing database administrators to monitor performance and diagnose issues.', 'DMVs are composed of both views and table valued functions, with some applying to the entire server and others specific to individual databases. They all start with the prefix DM underscore.', 'The chapter details categories of DMVs, including database related, SQL Server OS related, and execution related DMVs.', "The 'dm_db_file_space_usage' DMV returns space usage information for each file in the database, and can be used to retrieve the total number of free pages and total free space in megabytes available in all files in 'tempdb'.", "The 'dm_db_log_space_usage' DMV returns space usage information for the transaction log, and can be used to retrieve the total free log space in megabytes available in 'tempdb'."]}, {'end': 3528.509, 'start': 3352.299, 'title': 'Database partition stats and os thread information', 'summary': 'Covers the usage of dmvs to retrieve page and row count information for partitions in the adventureworks 2014 database, cached page count for each database, and details of sql server operating system threads, with quantifiable data such as the number of cached pages for different databases and the session id for active user connections.', 'duration': 176.21, 'highlights': ['Retrieving page and row count information for partitions in the AdventureWorks 2014 database using db underscore partition stats, with details such as index ID, partition number, reserved page count, used page count, and row count.', 'Obtaining the cached page count for each database using DM underscore OS buffer descriptors, with quantifiable data such as 137 cached pages in master DB, 9 in MSDB, 6 in Sparta, and 304 in AdventureWorks.', 'Fetching details of all SQL Server operating system threads running under the SQL Server process using dm underscore OS threads, including information about workers, time used for execution, and running threads not started by SQL Server.']}, {'end': 3849.18, 'start': 3528.87, 'title': 'Sql server activity monitoring', 'summary': 'Discusses sql server activity monitoring using dynamic management views and activity monitor, including information on session counts, performance metrics like processor time and batch requests, and the use of performance monitor for tracking system and application performance counters in real time.', 'duration': 320.31, 'highlights': ['The chapter discusses SQL Server activity monitoring using dynamic management views and activity monitor. It covers the use of dynamic management views to display information about users and their session counts, as well as the features and expandable themes of the activity monitor in SQL Server Management Studio.', 'The overview pane in the activity monitor contains graphs for the most important SQL server instance information, including processor time, waiting tasks, database IO, and batch requests per second. It provides insights into key performance metrics such as processor time, waiting tasks, database IO data transfer rate, and batch requests per second received by the SQL Server instance.', 'The processes pane in the activity monitor shows information about the currently running processes on the SQL Server databases and who are running those processes. It displays details about the running processes, including session ID, user processes, and login names, providing visibility into the activities of different users and the databases they are working on.', 'The performance monitor, a monitoring tool shipped with Windows, helps in tracking various performance counters and monitors overall system and application performance. It enables the tracking of performance counters and provides real-time graphs that can be saved in log files for analysis, offering insights into system and application performance.']}, {'end': 4075.636, 'start': 3849.18, 'title': 'Using sql server profiler for tracing and maintaining data integrity', 'summary': 'Explains how to trace sql server activity using sql server profiler, including setting up a trace, selecting events, and ensuring data integrity by maintaining accuracy and consistency with entity, referential, domain, and user-defined data integrity.', 'duration': 226.456, 'highlights': ['The chapter explains how to trace SQL Server activity using SQL Server Profiler. It demonstrates the process of setting up SQL Server Profiler, selecting events to trace, and analyzing the collected information.', 'The importance of data integrity is highlighted, emphasizing the need to maintain accuracy and consistency of data in a table. It categorizes data integrity into entity integrity, referential integrity, domain integrity, and user-defined data integrity, explaining the purpose and methods for each category.']}, {'end': 4440.935, 'start': 4075.636, 'title': 'Aggregate functions in sql', 'summary': "Explains aggregate functions in sql, including examples of using max, min, average, and count functions, with specific values and demerits, as well as the most efficient way of counting using a hard-coded 'one' value.", 'duration': 365.299, 'highlights': ["The max salary in the employee table is 5000, and by giving it an alias name 'max salary', the column name is displayed.", 'Filter conditions can be applied before aggregation using the where clause, allowing for specific filtering such as getting the maximum salary where the address is India.', 'The minimum salary in the employee table is 1000, and the average salary is 3071, calculated as the sum by count or using the count of employee ID.', "The demerits of using 'count star' or 'count employee ID' are explained, including the problem with null values and taking the whole table into memory.", "The most efficient way of counting is demonstrated by hard coding 'one' and counting the occurrences of it, as it avoids taking everything into memory."]}], 'duration': 1212.216, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co3228719.jpg', 'highlights': ['DMVs provide metadata of the SQL Server system state for monitoring performance and diagnosing issues.', 'DMVs are composed of views and table valued functions, categorized into database, SQL Server OS, and execution related DMVs.', "dm_db_file_space_usage DMV returns space usage info for each file in the database, including total free space in 'tempdb'.", "dm_db_log_space_usage DMV returns space usage info for the transaction log, including total free log space in 'tempdb'.", 'Retrieve page and row count info for partitions in AdventureWorks 2014 database using db_partition_stats.', 'Obtain cached page count for each database using DM_OS_buffer_descriptors, with quantifiable data.', 'Fetch details of all SQL Server operating system threads using dm_OS_threads, including worker information and execution time.', 'Discuss SQL Server activity monitoring using dynamic management views and activity monitor, covering user session counts and activity monitor features.', 'Activity monitor provides insights into key performance metrics such as processor time, waiting tasks, and database IO data transfer rate.', 'Performance monitor, a monitoring tool shipped with Windows, helps in tracking various performance counters and monitors overall system and application performance.', 'Explain how to trace SQL Server activity using SQL Server Profiler, including setting up, selecting events to trace, and analyzing collected information.', 'Emphasize the importance of maintaining data integrity in a table, categorizing it into entity, referential, domain, and user-defined data integrity.', 'Demonstrate using aggregate functions in SQL, including max, min, and average salary calculations with filter conditions.', "Explain the demerits of using 'count star' or 'count employee ID' and demonstrate the most efficient way of counting by hard coding 'one'."]}, {'end': 6109.578, 'segs': [{'end': 4603.587, 'src': 'embed', 'start': 4580.86, 'weight': 1, 'content': [{'end': 4589.161, 'text': 'okay, but Scalar-valued, user-defined functions are functions that we write that accepts input parameters and returns a single value.', 'start': 4580.86, 'duration': 8.301}, {'end': 4598.285, 'text': "So a single value could be 1, 2, 5, 10, Dinesh, John, anything, today's date, and it can return any data type.", 'start': 4589.641, 'duration': 8.644}, {'end': 4603.587, 'text': 'That means it can return varchar, it can return integer, it can return date time and whatnot.', 'start': 4598.465, 'duration': 5.122}], 'summary': 'Scalar-valued user-defined functions accept input parameters and return a single value of various data types.', 'duration': 22.727, 'max_score': 4580.86, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4580860.jpg'}, {'end': 4725.67, 'src': 'embed', 'start': 4697.586, 'weight': 3, 'content': [{'end': 4703.943, 'text': "okay, right, So here, if I say only create, it'll throw me an error if it already exists.", 'start': 4697.586, 'duration': 6.357}, {'end': 4713.568, 'text': 'But if I say create or alter, SQL will automatically identify if this function is present in the server, in the database or not.', 'start': 4704.303, 'duration': 9.265}, {'end': 4717.931, 'text': "If it is present, if it is already existing, then it'll alter it.", 'start': 4713.949, 'duration': 3.982}, {'end': 4719.492, 'text': 'If not, it is created.', 'start': 4718.291, 'duration': 1.201}, {'end': 4723.094, 'text': 'Okay So here I say create or alter function.', 'start': 4719.912, 'duration': 3.182}, {'end': 4725.67, 'text': 'And then we give a name to the function.', 'start': 4723.587, 'duration': 2.083}], 'summary': "Using 'create or alter' in sql allows automatic identification and alteration of existing functions.", 'duration': 28.084, 'max_score': 4697.586, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4697586.jpg'}, {'end': 4774.496, 'src': 'embed', 'start': 4746.682, 'weight': 4, 'content': [{'end': 4757.284, 'text': 'you need to ensure that you give a proper name to your object so that anyone else looking into that can understand what exactly is it.', 'start': 4746.682, 'duration': 10.602}, {'end': 4763.185, 'text': 'Does it contain or is it going to do? So here, first of all, I prefix it with FN underscore.', 'start': 4757.684, 'duration': 5.501}, {'end': 4765.205, 'text': 'Now, this is also an important aspect.', 'start': 4763.445, 'duration': 1.76}, {'end': 4774.496, 'text': "Why?? Because here, from here onwards, any object that we're going to create, I would say, except for tables in the database, any object that you create,", 'start': 4765.472, 'duration': 9.024}], 'summary': 'Object naming convention is important for clarity and understanding.', 'duration': 27.814, 'max_score': 4746.682, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4746682.jpg'}, {'end': 5379.172, 'src': 'embed', 'start': 5353.69, 'weight': 8, 'content': [{'end': 5359.352, 'text': 'no dbo is necessary here, because for table valid functions it can easily look it up on the table.', 'start': 5353.69, 'duration': 5.662}, {'end': 5361.4, 'text': 'valued user defined functions.', 'start': 5359.352, 'duration': 2.048}, {'end': 5364.862, 'text': 'only for scalar valued functions you need to specify dbo.', 'start': 5361.4, 'duration': 3.462}, {'end': 5366.423, 'text': 'just a quick info, guys.', 'start': 5364.862, 'duration': 1.561}, {'end': 5372.728, 'text': 'intellipaat provides sql online training in partnership with microsoft and mentored by industry experts.', 'start': 5366.423, 'duration': 6.305}, {'end': 5375.23, 'text': 'the course link is given in the description below.', 'start': 5372.728, 'duration': 2.502}, {'end': 5377.131, 'text': "now let's continue with the session.", 'start': 5375.23, 'duration': 1.901}, {'end': 5379.172, 'text': "let's move on, okay.", 'start': 5377.131, 'duration': 2.041}], 'summary': 'Scalar-valued functions require specifying dbo, intellipaat offers sql online training in partnership with microsoft.', 'duration': 25.482, 'max_score': 5353.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co5353690.jpg'}, {'end': 5998.258, 'src': 'embed', 'start': 5951.055, 'weight': 0, 'content': [{'end': 5959.1, 'text': 'text. inside the function, you have a function called sp help text, which you can use to see the content of the function.', 'start': 5951.055, 'duration': 8.045}, {'end': 5966.864, 'text': 'so i can say sp help text and i can specify the name of the function to get to see what is there inside of it.', 'start': 5959.1, 'duration': 7.764}, {'end': 5970.066, 'text': 'see, i can see the whole structure of the function, is it?', 'start': 5966.864, 'duration': 3.202}, {'end': 5971.927, 'text': 'but this is not easy to read.', 'start': 5970.066, 'duration': 1.861}, {'end': 5974.029, 'text': 'so what can you make?', 'start': 5971.927, 'duration': 2.102}, {'end': 5975.289, 'text': 'how can you make it easier to read?', 'start': 5974.029, 'duration': 1.26}, {'end': 5979.392, 'text': 'you see, here on the top, there are two methods in which you can get your output.', 'start': 5975.289, 'duration': 4.103}, {'end': 5981.552, 'text': 'one is results to grid.', 'start': 5979.392, 'duration': 2.16}, {'end': 5983.672, 'text': 'the other one is results to text.', 'start': 5981.552, 'duration': 2.12}, {'end': 5990.395, 'text': 'so here i can choose the results to text and when i execute it, it will give me the output in text.', 'start': 5983.672, 'duration': 6.723}, {'end': 5998.258, 'text': 'see, but by default it is the grid, because we are always most of the times we are retrieving data from tables.', 'start': 5990.395, 'duration': 7.863}], 'summary': 'The sp help text function allows viewing the content of a function, with options to display output as text or grid.', 'duration': 47.203, 'max_score': 5951.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co5951055.jpg'}, {'end': 6055.047, 'src': 'embed', 'start': 6025.018, 'weight': 2, 'content': [{'end': 6032.081, 'text': 'so this will help us in not allowing the function to be scripted out using the sp help text.', 'start': 6025.018, 'duration': 7.063}, {'end': 6042.322, 'text': "so let's say i alter it using the width encryption and it says commands completed successfully, and now, if i run the help text again,", 'start': 6032.081, 'duration': 10.241}, {'end': 6047.884, 'text': 'you will see that it throws me a message saying the text is already encrypted.', 'start': 6042.322, 'duration': 5.562}, {'end': 6055.047, 'text': "okay, that means it doesn't allow us to see the um text of the function again.", 'start': 6047.884, 'duration': 7.163}], 'summary': 'Using encryption prevents viewing function text, enhancing security.', 'duration': 30.029, 'max_score': 6025.018, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6025018.jpg'}], 'start': 4441.355, 'title': 'Sql functions & user-defined functions', 'summary': 'Covers various aggregate functions in sql server, user-defined scalar and table-valued functions, including specifying input parameters, return types, and function execution. it also discusses multi-line table value functions allowing multiple sql statements and dml operations on table variables.', 'chapters': [{'end': 4847.968, 'start': 4441.355, 'title': 'Sql server functions & user-defined functions', 'summary': 'Covers the various aggregate functions in sql server, user-defined functions including scalar value functions and table valued functions, with an example of creating a user-defined function to generate a full name from input parameters.', 'duration': 406.613, 'highlights': ['The chapter covers the various aggregate functions in SQL Server Mentions the availability of aggregate functions in SQL Server for counting records in a table.', 'User-defined functions including scalar value functions and table valued functions Explains the types of user-defined functions, including scalar value and table valued functions, and their purpose.', 'Example of creating a user-defined function to generate a full name from input parameters Provides an example of creating a user-defined function to generate a full name from input parameters, demonstrating the process of defining input parameters and naming conventions.']}, {'end': 5375.23, 'start': 4847.968, 'title': 'User defined functions in sql', 'summary': 'Discusses the creation and implementation of user-defined scalar and table-valued functions, including specifying input parameters, return types, and function execution, highlighting the importance of schema naming conventions and the distinction between scalar and table-valued functions.', 'duration': 527.262, 'highlights': ['The function returns varchar 101 characters long, consisting of a first name of 50 characters, a last name of 50 characters, and 1 space, allowing for the creation of a full name by concatenating the first and last name. The function specifies the return type as varchar 101 characters long, allowing for the concatenation of a first name (50 characters) and a last name (50 characters), separated by a space (1 character), enabling the creation of a full name.', "The necessity of specifying 'dbo' before the user-defined scalar-valued function call is emphasized to ensure SQL Server recognizes it as user-defined rather than a system-defined function. Emphasizes the importance of prefixing 'dbo' before user-defined scalar-valued function calls to distinguish them from system-defined functions, allowing SQL Server to recognize and execute the user-defined function.", "The creation and implementation of an inline table-valued function is demonstrated, showcasing the usage of a single SQL statement without the need for 'begin' and 'end' declarations. Demonstrates the creation and implementation of an inline table-valued function, utilizing a single SQL statement without 'begin' and 'end' declarations, and enabling the use of scalar-valued functions within the table-valued function."]}, {'end': 5660.967, 'start': 5375.23, 'title': 'Multi-line table value function', 'summary': 'Explains how to create a multi-line table value function in sql, which allows multiple sql statements in the definition and allows dml operations on table variables, with an example of inserting, updating, and deleting records based on the input parameter.', 'duration': 285.737, 'highlights': ["The function creation syntax is similar to regular functions, with the addition of specifying 'returns' and the table variable, as demonstrated in creating a multi-line table value function named 'get employee details ML' with an input parameter of employee ID and specified columns like employee ID, full name, and salary.", 'The chapter highlights the ability to perform DML operations like insert, update, and delete on table variables within the multi-line table value function, allowing the manipulation of data without affecting the physical tables, demonstrated through inserting all employees except the input parameter, updating salaries for specific employee IDs, and deleting records based on certain conditions.', "The return keyword is used to indicate that the 'emp' table variable should be returned as the output, and upon execution, querying the function with an input parameter retrieves the expected output, showcasing the functionality of the multi-line table value function with multiple SQL statements."]}, {'end': 6109.578, 'start': 5660.967, 'title': 'Sql functions and joins', 'summary': 'Introduces sql functions and joins, demonstrating how to use scalar valued functions in select and where clauses, as well as how to join table valued functions with other tables using cross apply and outer apply, and how to view and encrypt the function text.', 'duration': 448.611, 'highlights': ['The chapter explains how to use scalar valued functions in select and where clauses, such as using a function to retrieve employee data based on a specified full name, and highlights the ability to join table valued functions with other tables using cross apply and outer apply, providing examples of inner join and left outer join to demonstrate the functionality.', "The instructor demonstrates the use of sp help text to view the content of a function and how to choose between 'results to grid' and 'results to text' output formats, emphasizing the importance of storing a copy of the function text before encrypting it due to the complex process of decrypting it.", 'The chapter concludes with a cautionary note about the importance of storing a copy of the function text before encrypting it, due to the challenging process of decrypting it and emphasizes the significance of being a sysadmin on the server to retrieve the function text.', 'The chapter introduces SQL functions and joins, demonstrating how to use scalar valued functions in select and where clauses, as well as how to join table valued functions with other tables using cross apply and outer apply, and how to view and encrypt the function text.']}], 'duration': 1668.223, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co4441355.jpg', 'highlights': ['Covers various aggregate functions in SQL Server, including counting records in a table.', 'Explains user-defined scalar and table-valued functions and their purpose.', 'Provides an example of creating a user-defined function to generate a full name from input parameters.', 'Specifies the return type as varchar 101 characters long for concatenating first and last names.', "Emphasizes the importance of prefixing 'dbo' before user-defined scalar-valued function calls.", 'Demonstrates the creation and implementation of an inline table-valued function.', 'Demonstrates the creation of a multi-line table value function with DML operations on table variables.', 'Highlights the ability to perform DML operations like insert, update, and delete on table variables within the multi-line table value function.', 'Demonstrates the functionality of the multi-line table value function with multiple SQL statements.', 'Explains how to use scalar valued functions in select and where clauses.', 'Demonstrates the use of sp help text to view the content of a function and choose output formats.', 'Concludes with a cautionary note about the importance of storing a copy of the function text before encrypting it.']}, {'end': 7419.816, 'segs': [{'end': 6365.643, 'src': 'embed', 'start': 6287.34, 'weight': 4, 'content': [{'end': 6297.776, 'text': 'and now what i do here is i say select star case when employee first name equal to the reverse of employee first name, then print it as palindrome,', 'start': 6287.34, 'duration': 10.436}, {'end': 6300.457, 'text': 'else display as not palindrome.', 'start': 6297.776, 'duration': 2.681}, {'end': 6303.637, 'text': 'okay, and as this is my column name.', 'start': 6300.457, 'duration': 3.18}, {'end': 6310.378, 'text': 'so when i execute this you will find that, see, this one is a palindrome name now.', 'start': 6303.637, 'duration': 6.741}, {'end': 6311.679, 'text': 'so it says palindrome.', 'start': 6310.378, 'duration': 1.301}, {'end': 6313.399, 'text': 'the rest of them are not palindrome.', 'start': 6311.679, 'duration': 1.72}, {'end': 6315.759, 'text': 'so it shows us not palindrome.', 'start': 6313.399, 'duration': 2.36}, {'end': 6320.971, 'text': 'okay, let me update it back to the niche, Okay.', 'start': 6315.759, 'duration': 5.212}, {'end': 6322.731, 'text': 'Let me remove this one.', 'start': 6321.511, 'duration': 1.22}, {'end': 6324.512, 'text': "Let's update this.", 'start': 6323.572, 'duration': 0.94}, {'end': 6334.614, 'text': 'Okay, So now there is an alternative for case which you can use only when you have two conditions.', 'start': 6326.632, 'duration': 7.982}, {'end': 6338.495, 'text': 'with case, as you can see, you can write up to as many conditions as you want.', 'start': 6334.614, 'duration': 3.881}, {'end': 6342.296, 'text': 'You just have to specify when after for each of the conditions.', 'start': 6338.535, 'duration': 3.761}, {'end': 6349.237, 'text': 'but when you have only two conditions, that is either a true or false, you can use the if so, with the, if you can say', 'start': 6342.296, 'duration': 6.941}, {'end': 6356.235, 'text': 'If employed is less than equal to five, then print this message else print this message.', 'start': 6349.73, 'duration': 6.505}, {'end': 6360.999, 'text': 'So if is basically a function that accepts three input parameters.', 'start': 6356.495, 'duration': 4.504}, {'end': 6362.5, 'text': 'First one is the condition.', 'start': 6361.179, 'duration': 1.321}, {'end': 6365.643, 'text': 'Next is the truth value.', 'start': 6362.881, 'duration': 2.762}], 'summary': 'Using sql to identify palindrome names and demonstrate conditional logic with case and if statements.', 'duration': 78.303, 'max_score': 6287.34, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6287340.jpg'}, {'end': 6450.485, 'src': 'embed', 'start': 6415.159, 'weight': 3, 'content': [{'end': 6420.863, 'text': 'but when you want to execute your queries based on certain conditions, you use the if command.', 'start': 6415.159, 'duration': 5.704}, {'end': 6425.466, 'text': "so let's say if i say if one equal to two, then select nice.", 'start': 6420.863, 'duration': 4.603}, {'end': 6431.79, 'text': "so if i run this, it doesn't display me anything, because one is not equal to two right.", 'start': 6425.466, 'duration': 6.324}, {'end': 6442.979, 'text': 'and if i say if two equal to two, select nicer.', 'start': 6431.79, 'duration': 11.189}, {'end': 6450.485, 'text': "let's say and when i run this it prints nicer, because the second condition is true.", 'start': 6442.979, 'duration': 7.506}], 'summary': "Using 'if' command to execute queries based on conditions, e.g. if (1=2) then select 'nice', if (2=2) then select 'nicer'.", 'duration': 35.326, 'max_score': 6415.159, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6415159.jpg'}, {'end': 6520.828, 'src': 'embed', 'start': 6497.51, 'weight': 0, 'content': [{'end': 6506.344, 'text': "else, if 2 equals to 2, then select nicer and here, If I execute this, you'll see that it only prints nice.", 'start': 6497.51, 'duration': 8.834}, {'end': 6512.626, 'text': "Although the second one is true, it doesn't print it because it only selects the first true value.", 'start': 6506.504, 'duration': 6.122}, {'end': 6518.487, 'text': "Now, let's say if I make this one false and the second one is true, it prints the second one.", 'start': 6513.026, 'duration': 5.461}, {'end': 6520.828, 'text': "Now, let's say the second one is also false.", 'start': 6518.867, 'duration': 1.961}], 'summary': 'Using conditional statements, selecting true values, prints based on conditions.', 'duration': 23.318, 'max_score': 6497.51, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6497510.jpg'}, {'end': 6673.031, 'src': 'embed', 'start': 6640.803, 'weight': 9, 'content': [{'end': 6643.506, 'text': 'Okay, so I have both these tables with me.', 'start': 6640.803, 'duration': 2.703}, {'end': 6650.283, 'text': 'Okay, so in case you have already, you must be already knowing about this select star from employee.', 'start': 6644.301, 'duration': 5.982}, {'end': 6655.665, 'text': 'When you specify the table name as a single name, it is called as a single name qualifier.', 'start': 6650.603, 'duration': 5.062}, {'end': 6657.025, 'text': 'If you specify it.', 'start': 6656.205, 'duration': 0.82}, {'end': 6667.269, 'text': 'with the schema we call it as a two double name qualifier, which means you are specifying two things to be able to for SQL to identify the table.', 'start': 6657.025, 'duration': 10.244}, {'end': 6673.031, 'text': 'In a three name qualifier, you specify the database name, okay, which is this name.', 'start': 6667.589, 'duration': 5.442}], 'summary': 'Explaining single, double, and three name qualifiers for sql tables.', 'duration': 32.228, 'max_score': 6640.803, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6640803.jpg'}, {'end': 6738.113, 'src': 'embed', 'start': 6713.978, 'weight': 2, 'content': [{'end': 6722.123, 'text': 'okay, so this is how you can use or fetch data using a single name, double name, three name and a four name qualifier.', 'start': 6713.978, 'duration': 8.145}, {'end': 6730.689, 'text': "right, and as part of this module, what we're going to talk about is the order by clause, the group by and the having,", 'start': 6722.123, 'duration': 8.566}, {'end': 6734.311, 'text': 'except an intersect union and union, or okay.', 'start': 6730.689, 'duration': 3.622}, {'end': 6736.072, 'text': "so let's start by the order by.", 'start': 6734.311, 'duration': 1.761}, {'end': 6738.113, 'text': 'the order by is pretty simple.', 'start': 6736.072, 'duration': 2.041}], 'summary': 'Demonstrating data fetching with different qualifiers and sql clauses like order by, group by, and intersect.', 'duration': 24.135, 'max_score': 6713.978, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6713978.jpg'}, {'end': 6918.458, 'src': 'embed', 'start': 6889.04, 'weight': 1, 'content': [{'end': 6890.101, 'text': "Let's say make it ascending.", 'start': 6889.04, 'duration': 1.061}, {'end': 6897.046, 'text': "So when I make it ascending, you'll find that for these two, the employee ID is now three and five.", 'start': 6890.521, 'duration': 6.525}, {'end': 6903.931, 'text': 'So you see the difference? So the second column is only applicable when the first column has the same value.', 'start': 6897.466, 'duration': 6.465}, {'end': 6909.29, 'text': "now let's read what i have mentioned here.", 'start': 6905.126, 'duration': 4.164}, {'end': 6918.458, 'text': "so i mentioned in the notes i've said, when there is a request from the business to deliver reports or metrics of some analysis, they might need to.", 'start': 6909.29, 'duration': 9.168}], 'summary': 'Ascending order changed employee ids to 3 and 5. criteria for second column explained.', 'duration': 29.418, 'max_score': 6889.04, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6889040.jpg'}], 'start': 6110.051, 'title': 'Sql functions, conditional statements, and sorting', 'summary': 'Covers sql case statements and function deployment, explains conditional statements usage, and discusses the order by clause and sorting for integer and string data types, emphasizing practical examples and default sorting mechanisms.', 'chapters': [{'end': 6315.759, 'start': 6110.051, 'title': 'Sql case statement and function deployment', 'summary': 'Covers the usage of the case statement in sql for conditional data retrieval and explains how to deploy and modify functions stored in a repository, emphasizing the importance of saving the function code before encryption.', 'duration': 205.708, 'highlights': ['The case statement in SQL allows for conditional data retrieval and column derivation based on specified conditions. It enables the creation of conditional output during data retrieval, as exemplified by deriving a message based on employee IDs and determining palindrome text.', 'Deploying and modifying functions in a repository is crucial, as the original function code cannot be retrieved from the server after deployment. Emphasizes the need to safely store function code in a repository before encryption to avoid loss of access, highlighting the importance of proper deployment and storage practices.']}, {'end': 6600.861, 'start': 6315.759, 'title': 'Sql conditional statements', 'summary': "Explains the usage of if and case statements in sql, highlighting their differences and demonstrating their practical application with specific examples, emphasizing the importance of using 'begin' and 'end' when multiple statements are involved in a condition.", 'duration': 285.102, 'highlights': ["The 'if' statement in SQL is used for conditional flow and accepts three input parameters: condition, true value, and false value, allowing for the execution of queries based on specific conditions.", "The 'else if' statement in SQL allows for combining multiple conditions into a single condition, enabling the selection of the first true value and the execution of the 'else' part when all if statements are false.", "The necessity of using 'begin' and 'end' in SQL if statements is highlighted, as omitting them can lead to unexpected behavior, with only the first statement qualifying as part of the condition."]}, {'end': 7093.512, 'start': 6601.241, 'title': 'Sql order by clause', 'summary': 'Discusses the usage of single name, double name, three name, and four name qualifiers for fetching data, and the implementation of the order by clause for sorting data in either ascending or descending order, with the default sorting mechanism in sql being ascending, and the ability to have sorting applied on more than one column in the table.', 'duration': 492.271, 'highlights': ['The chapter discusses the usage of single name, double name, three name, and four name qualifiers for fetching data. The speaker explains the different qualifiers for fetching data, including single name, double name, three name, and four name qualifiers.', 'The implementation of the order by clause for sorting data in either ascending or descending order, with the default sorting mechanism in SQL being ascending, and the ability to have sorting applied on more than one column in the table. The speaker explains the usage of the order by clause for sorting data, the default sorting mechanism in SQL, and the ability to apply sorting on more than one column in the table.']}, {'end': 7419.816, 'start': 7094.932, 'title': 'Sorting in sql for integer and string data types', 'summary': 'Explains the differences in sorting for integer and string data types in sql, showcasing examples of ascending and descending sorting for both data types, while also demonstrating the usage of the top clause in fetching top records.', 'duration': 324.884, 'highlights': ['The chapter demonstrates the sorting of integer values in SQL, showcasing an example of ascending and descending sorting, with the ascending order displaying 1, 2, 3, 11, 12, 22, 44, 111, 222, and the descending order showing 222, 111, 44, 22, 12, 11, 3, 2, 1.', 'The chapter illustrates the sorting of string values in SQL, providing an example of ascending and descending sorting, where the ascending order displays 1, 11, 111, 12, 2, 22, 222, 3, 44, and the descending order shows 44, 3, 222, 22, 12, 111, 11, 2, 1.', "The chapter also introduces the usage of the top clause in SQL to fetch top records during a select statement, with an example of using 'top 1' to retrieve the employee with the highest salary after sorting by salary in descending order.", 'The transcript briefly addresses the distinct clause in SQL, indicating a shift in topic towards SQL clauses and posing a question to the audience about a SQL clause used for deleting tuples from a database table.']}], 'duration': 1309.765, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co6110051.jpg', 'highlights': ['The case statement in SQL allows for conditional data retrieval and column derivation based on specified conditions.', 'Deploying and modifying functions in a repository is crucial, as the original function code cannot be retrieved from the server after deployment.', "The 'if' statement in SQL is used for conditional flow and accepts three input parameters: condition, true value, and false value.", "The 'else if' statement in SQL allows for combining multiple conditions into a single condition, enabling the selection of the first true value.", 'The chapter discusses the usage of single name, double name, three name, and four name qualifiers for fetching data.', 'The implementation of the order by clause for sorting data in either ascending or descending order, with the default sorting mechanism in SQL being ascending.', 'The chapter demonstrates the sorting of integer values in SQL, showcasing an example of ascending and descending sorting.', 'The chapter illustrates the sorting of string values in SQL, providing an example of ascending and descending sorting.', 'The chapter also introduces the usage of the top clause in SQL to fetch top records during a select statement.', 'The transcript briefly addresses the distinct clause in SQL, indicating a shift in topic towards SQL clauses and posing a question to the audience about a SQL clause used for deleting tuples from a database table.']}, {'end': 8269.316, 'segs': [{'end': 7539.966, 'src': 'embed', 'start': 7507.729, 'weight': 4, 'content': [{'end': 7510.571, 'text': 'So India and Dinesh is only one record.', 'start': 7507.729, 'duration': 2.842}, {'end': 7511.852, 'text': 'So it just shows it.', 'start': 7510.992, 'duration': 0.86}, {'end': 7513.774, 'text': 'It is not duplicated as you can see.', 'start': 7512.193, 'duration': 1.581}, {'end': 7517.334, 'text': 'So the distinct gets us the distinct across both these columns.', 'start': 7514.212, 'duration': 3.122}, {'end': 7520.596, 'text': 'Now, now let me remove the distinct here and you will notice something different.', 'start': 7517.374, 'duration': 3.222}, {'end': 7527.339, 'text': 'See for the us record, we have both the employees as us and John.', 'start': 7521.436, 'duration': 5.903}, {'end': 7529.06, 'text': 'I mean, the address is us.', 'start': 7527.799, 'duration': 1.261}, {'end': 7530.561, 'text': 'Then the first name is John.', 'start': 7529.08, 'duration': 1.481}, {'end': 7539.966, 'text': 'So when I put the distinct, you will see that I get only one us and John, because the other one is also the same.', 'start': 7530.921, 'duration': 9.045}], 'summary': 'Using distinct, india and dinesh are the only unique record, leading to a single entry for us and john.', 'duration': 32.237, 'max_score': 7507.729, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co7507729.jpg'}, {'end': 7838.385, 'src': 'embed', 'start': 7806.773, 'weight': 0, 'content': [{'end': 7811.036, 'text': "so here let's say i want to get only those cities.", 'start': 7806.773, 'duration': 4.263}, {'end': 7812.777, 'text': 'let me comment this one.', 'start': 7811.036, 'duration': 1.741}, {'end': 7814.398, 'text': 'i want to get the first.', 'start': 7812.777, 'duration': 1.621}, {'end': 7817.96, 'text': 'i want to group it based on the city and i only get to see.', 'start': 7814.398, 'duration': 3.562}, {'end': 7823.964, 'text': 'i only want to see the cities which have population less than 150, the total population.', 'start': 7817.96, 'duration': 6.004}, {'end': 7829.467, 'text': 'so i can execute this query and i get these three cities.', 'start': 7823.964, 'duration': 5.503}, {'end': 7830.68, 'text': 'The other one.', 'start': 7829.96, 'duration': 0.72}, {'end': 7832.121, 'text': "I didn't get it because let me show you.", 'start': 7830.68, 'duration': 1.441}, {'end': 7838.385, 'text': "I didn't get CT2, correct? So if I execute this, I get only 185.", 'start': 7832.481, 'duration': 5.904}], 'summary': 'The query retrieved three cities with a population less than 150, totaling 185.', 'duration': 31.612, 'max_score': 7806.773, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co7806773.jpg'}, {'end': 8223.089, 'src': 'embed', 'start': 8174.034, 'weight': 1, 'content': [{'end': 8177.256, 'text': 'here also, the first one should be integer, second one should be varchar.', 'start': 8174.034, 'duration': 3.222}, {'end': 8185.789, 'text': 'so when i run this, It gives me the records in this, except the records in this, which is two and no name provided.', 'start': 8177.256, 'duration': 8.533}, {'end': 8188.531, 'text': 'Now the other way around.', 'start': 8186.949, 'duration': 1.582}, {'end': 8194.295, 'text': "if I say primary key test except default test, I should get two and John isn't it?", 'start': 8188.531, 'duration': 5.764}, {'end': 8197.416, 'text': 'Because two and John is present in this but not in this.', 'start': 8194.495, 'duration': 2.921}, {'end': 8203.841, 'text': "So let's say if I flip my tables, I say primary key test except default test.", 'start': 8197.736, 'duration': 6.105}, {'end': 8206.824, 'text': "So it'll now give me two and John.", 'start': 8204.441, 'duration': 2.383}, {'end': 8213.659, 'text': 'Two, and John is present in the primary key test but missing in the default test.', 'start': 8207.224, 'duration': 6.435}, {'end': 8216.064, 'text': 'okay, next is the intersect operator.', 'start': 8213.659, 'duration': 2.405}, {'end': 8220.772, 'text': 'the intersect operator fetches the common records across both of them.', 'start': 8216.064, 'duration': 4.708}, {'end': 8223.089, 'text': 'now this is different than the joins.', 'start': 8220.772, 'duration': 2.317}], 'summary': "The transcript explains the usage of sql operators such as 'except' and 'intersect' to retrieve specific records based on set operations.", 'duration': 49.055, 'max_score': 8174.034, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co8174034.jpg'}], 'start': 7420.136, 'title': 'Sql distinct and aggregations', 'summary': 'Covers the usage of distinct in sql to retrieve unique records, and the application of sql aggregations and group by clause to perform operations such as getting highest salaries, sum of population, and combining data from multiple tables using union, union all, except, and intersect operators.', 'chapters': [{'end': 7576.757, 'start': 7420.136, 'title': 'Using distinct in sql', 'summary': 'Explains how the distinct keyword in sql helps in fetching unique records from a table or selected columns, and how it behaves when applied to multiple columns, illustrating with examples.', 'duration': 156.621, 'highlights': ['DISTINCT helps in fetching unique records from a table or selected columns. It allows users to retrieve only unique values from a specific column or set of columns, eliminating duplicate entries.', 'Behavior of DISTINCT when applied to multiple columns. When DISTINCT is applied to multiple columns, it removes records if they are duplicated across all the columns, as demonstrated in the examples.', 'Illustration of DISTINCT behavior with specific examples. The transcript provides clear examples of how DISTINCT works when applied to different scenarios, including the impact on duplicate values and unique combinations across multiple columns.']}, {'end': 8269.316, 'start': 7579.198, 'title': 'Sql aggregations and grouping', 'summary': 'Explains how to use sql aggregations and group by clause to perform operations like getting the top two highest salaries, calculating the sum of population per country and city, filtering aggregated data using the having clause, and using union, union all, except, and intersect operators to combine and retrieve data from multiple tables.', 'duration': 690.118, 'highlights': ['The group by clause helps in grouping the data based on certain values like the address, the departments, or anything, allowing to get the maximum or minimum salary per address or department. The group by clause allows grouping data based on certain values like address or department to calculate maximum or minimum salary per group.', 'Using group by with aggregations to calculate the sum of population per country and city, providing insights on per country and per city population. Using group by to calculate the sum of population per country and city, providing insights on population distribution.', 'Explaining the having clause that filters aggregated data, demonstrating its use in filtering cities with population less than 150. Explanation of the having clause for filtering aggregated data, demonstrated through filtering cities with population less than 150.', 'Detailed explanation of the sequence in which SQL queries execute, covering the order of execution for clauses like from, join, filter, group by, having, distinct, and order by. Detailed explanation of the sequence of SQL query execution, covering the order of execution for various clauses like from, join, filter, group by, having, distinct, and order by.', 'Demonstrating the usage of union and union all to combine and retrieve data from multiple tables, noting the difference in retrieving unique records and all records. Demonstrating the usage of union and union all to combine and retrieve data from multiple tables, highlighting the difference in retrieving unique records and all records.']}], 'duration': 849.18, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co7420136.jpg', 'highlights': ['DISTINCT helps in fetching unique records from a table or selected columns.', 'Using group by with aggregations to calculate the sum of population per country and city.', 'Demonstrating the usage of union and union all to combine and retrieve data from multiple tables, noting the difference in retrieving unique records and all records.', 'Illustration of DISTINCT behavior with specific examples.', 'Detailed explanation of the sequence of SQL query execution, covering the order of execution for various clauses like from, join, filter, group by, having, distinct, and order by.']}, {'end': 9103.535, 'segs': [{'end': 8342.833, 'src': 'embed', 'start': 8313.41, 'weight': 1, 'content': [{'end': 8315.932, 'text': 'Okay, So, uh, how do we write them??', 'start': 8313.41, 'duration': 2.522}, {'end': 8317.791, 'text': "let's say select star from.", 'start': 8316.331, 'duration': 1.46}, {'end': 8320.034, 'text': 'uh, i have a ranking table.', 'start': 8317.791, 'duration': 2.243}, {'end': 8323.177, 'text': 'if you remember, in the beginning of the, i created two tables.', 'start': 8320.034, 'duration': 3.143}, {'end': 8325.118, 'text': "one was the population, which i've already shown you.", 'start': 8323.177, 'duration': 1.941}, {'end': 8326.76, 'text': 'the other is the ranking.', 'start': 8325.118, 'duration': 1.642}, {'end': 8335.406, 'text': 'the ranking basically has two countries, as you can see, a and b, and then it has cities inside each of the countries.', 'start': 8326.76, 'duration': 8.646}, {'end': 8337.588, 'text': 'okay, and then the population against it.', 'start': 8335.406, 'duration': 2.182}, {'end': 8342.833, 'text': 'very simple table three columns country, corresponding cities and the population.', 'start': 8337.588, 'duration': 5.245}], 'summary': 'Created two tables - population and ranking, with 3 columns each.', 'duration': 29.423, 'max_score': 8313.41, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co8313410.jpg'}, {'end': 8475.987, 'src': 'embed', 'start': 8451.607, 'weight': 0, 'content': [{'end': 8459.12, 'text': 'It basically sorts the data based on whatever sorting mechanism, sorting column you have specified,', 'start': 8451.607, 'duration': 7.513}, {'end': 8462.782, 'text': 'and then assigns a row number to each of the records.', 'start': 8459.12, 'duration': 3.662}, {'end': 8466.484, 'text': 'okay, the next is the rank function.', 'start': 8462.782, 'duration': 3.702}, {'end': 8468.684, 'text': 'the syntax is exactly the same as i told you.', 'start': 8466.484, 'duration': 2.2}, {'end': 8472.266, 'text': 'i mean the good thing about these is that the syntax is exactly the same.', 'start': 8468.684, 'duration': 3.582}, {'end': 8474.967, 'text': 'so i just replaced the row number with the rank.', 'start': 8472.266, 'duration': 2.701}, {'end': 8475.987, 'text': 'now the rank.', 'start': 8474.967, 'duration': 1.02}], 'summary': 'Data is sorted and assigned row numbers, followed by using the rank function with the same syntax.', 'duration': 24.38, 'max_score': 8451.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co8451607.jpg'}, {'end': 8773.555, 'src': 'embed', 'start': 8750.604, 'weight': 2, 'content': [{'end': 8761.952, 'text': 'And if I run this, see, I now see that, okay, A and, sorry, country A has two cities, which has a third highest population across the whole table.', 'start': 8750.604, 'duration': 11.348}, {'end': 8767.095, 'text': "Let's move on to another interesting topic, which is about indexes.", 'start': 8762.292, 'duration': 4.803}, {'end': 8770.893, 'text': 'Okay Indexes are pretty much fun to work with.', 'start': 8767.571, 'duration': 3.322}, {'end': 8773.555, 'text': 'Why do we need indexes in the first place?', 'start': 8771.814, 'duration': 1.741}], 'summary': 'Country a has two cities with the third highest population. discussing the importance of indexes.', 'duration': 22.951, 'max_score': 8750.604, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co8750604.jpg'}, {'end': 8821.1, 'src': 'embed', 'start': 8799.515, 'weight': 4, 'content': [{'end': 8810.658, 'text': 'So they are pumping in a lot of data, and Microsoft is on top of all these expectations, ensuring that whatever data being pumped in,', 'start': 8799.515, 'duration': 11.143}, {'end': 8817.859, 'text': "irrespective of the volume of data that's been pumped in into any SQL server, the SQL server performance should not take a hit.", 'start': 8810.658, 'duration': 7.201}, {'end': 8821.1, 'text': 'Okay If not be like awesome.', 'start': 8818.18, 'duration': 2.92}], 'summary': 'Microsoft ensures high performance for large volumes of data in sql servers', 'duration': 21.585, 'max_score': 8799.515, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co8799515.jpg'}], 'start': 8269.757, 'title': 'Sql ranking functions and indexes', 'summary': 'Provides an overview of row number rank and dense rank in sql, and their utility in locating specific data points. it also delves into the implementation and usage of ranking functions in sql, including row number, rank, dense rank, and partition by, along with the growing significance of indexes in sql server for faster data access and enhanced performance.', 'chapters': [{'end': 8312.829, 'start': 8269.757, 'title': 'Sql ranking functions overview', 'summary': 'Introduces the concept of ranking functions in sql, specifically focusing on row number rank and dense rank, which are the only available ranking functions in sql server, and how they help in finding specific data points like the third, fourth, or sixth highest salary.', 'duration': 43.072, 'highlights': ['The chapter introduces the concept of ranking functions in SQL, specifically focusing on row number rank and dense rank, which are the only available ranking functions in SQL Server.', 'Ranking functions help in finding specific data points like the third, fourth, or sixth highest salary by using the exact same syntax for all of them.']}, {'end': 8773.555, 'start': 8313.41, 'title': 'Ranking functions in sql', 'summary': 'Covers the implementation of ranking functions in sql, including row number, rank, dense rank, and partition by, with examples and explanations of their properties and usage.', 'duration': 460.145, 'highlights': ['The chapter covers the implementation of ranking functions in SQL, including row number, rank, dense rank, and partition by. It provides an overview of the ranking functions covered in the chapter.', 'The row number function is used to assign a row number to each record based on a specified sorting column, with the property of assigning different row numbers to records with the same value. Explains the usage and property of the row number function in assigning row numbers to records.', 'The rank function assigns the same rank to records with the same value, skipping ranks for duplicate values, and is demonstrated with examples. Describes the behavior of the rank function in assigning ranks to records and skipping ranks for duplicate values.', 'The dense rank function works similarly to the rank function but does not skip ranks for duplicate values, as illustrated in the examples. Details the behavior of the dense rank function in assigning ranks to records without skipping ranks for duplicate values.', 'The partition by property is explained, demonstrating its use in creating separate sets of ranks for different groups, such as countries, and its application for obtaining specific ranked records within partitions. Explains the partition by property, its role in creating separate sets of ranks for different groups, and its usage in obtaining specific ranked records within partitions.']}, {'end': 9103.535, 'start': 8774.036, 'title': 'Importance of indexes in sql server', 'summary': "Emphasizes the growing importance of indexes in sql server due to the need for faster access to data, with microsoft consistently enhancing sql server's performance to meet customer expectations and prevent performance deterioration.", 'duration': 329.499, 'highlights': ["Microsoft's focus on enhancing SQL Server's performance is reflected in the continuous release of updates with substantial changes to indexes. Microsoft consistently updates SQL Server with significant changes to indexes to meet the growing expectations and customer base, ensuring top performance.", 'The primary motive behind building indexes in SQL Server is to enable faster locating of rows, leading to faster query execution. The primary goal of indexes in SQL Server is to facilitate faster locating of rows, resulting in accelerated query execution and improved performance.', 'Indexes in SQL Server act as routes to high performance, enabling faster access to rows in data tables, similar to an index page in a book providing swift access to specific topics. Indexes in SQL Server serve as routes to high performance by providing swift access to rows in data tables, analogous to an index page in a book facilitating quick access to specific topics.']}], 'duration': 833.778, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co8269757.jpg', 'highlights': ['The chapter introduces the concept of ranking functions in SQL, specifically focusing on row number rank and dense rank, which are the only available ranking functions in SQL Server.', 'Ranking functions help in finding specific data points like the third, fourth, or sixth highest salary by using the exact same syntax for all of them.', 'The row number function is used to assign a row number to each record based on a specified sorting column, with the property of assigning different row numbers to records with the same value.', 'The rank function assigns the same rank to records with the same value, skipping ranks for duplicate values, and is demonstrated with examples.', 'The dense rank function works similarly to the rank function but does not skip ranks for duplicate values, as illustrated in the examples.', "Microsoft's focus on enhancing SQL Server's performance is reflected in the continuous release of updates with substantial changes to indexes.", 'The primary motive behind building indexes in SQL Server is to enable faster locating of rows, leading to faster query execution.', 'Indexes in SQL Server act as routes to high performance, enabling faster access to rows in data tables, similar to an index page in a book providing swift access to specific topics.']}, {'end': 11300.659, 'segs': [{'end': 9232.017, 'src': 'embed', 'start': 9203.247, 'weight': 3, 'content': [{'end': 9205.648, 'text': 'Upper Comment your answer in the comment section below.', 'start': 9203.247, 'duration': 2.401}, {'end': 9208.029, 'text': 'Subscribe to Intellipaat to know the right answer.', 'start': 9205.888, 'duration': 2.141}, {'end': 9209.749, 'text': "Now, let's continue with the session.", 'start': 9208.249, 'duration': 1.5}, {'end': 9212.31, 'text': 'It could be on the employee ID as well.', 'start': 9210.089, 'duration': 2.221}, {'end': 9219.673, 'text': 'And the moment the employee ID is located, the rest of the data for that particular employee will be very easy to locate.', 'start': 9212.67, 'duration': 7.003}, {'end': 9229.476, 'text': "Okay. so they provide faster access to the data, and when I say data, I'm referring to the whole data row, using the column on which the index is created,", 'start': 9219.991, 'duration': 9.485}, {'end': 9232.017, 'text': 'or, as we call it as the index column.', 'start': 9229.476, 'duration': 2.541}], 'summary': 'Creating an index on employee id for faster data access.', 'duration': 28.77, 'max_score': 9203.247, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co9203247.jpg'}, {'end': 9291.424, 'src': 'embed', 'start': 9264.9, 'weight': 2, 'content': [{'end': 9273.423, 'text': 'OK, so these are binary structures which helps us in flattening our tables and hence providing easy access to the data rules.', 'start': 9264.9, 'duration': 8.523}, {'end': 9279.456, 'text': "OK Now, I'm again going to explain this in in detail at a later point in time.", 'start': 9274.023, 'duration': 5.433}, {'end': 9281.257, 'text': 'But imagine this example.', 'start': 9279.476, 'duration': 1.781}, {'end': 9284.439, 'text': "OK, let's say you have a set of 10 T-shirts.", 'start': 9281.257, 'duration': 3.182}, {'end': 9291.424, 'text': 'OK, 10 T-shirts which are placed one after the other in a queue and you are looking for one T-shirt.', 'start': 9284.439, 'duration': 6.985}], 'summary': 'Binary structures flatten tables, making data access easier; imagine 10 t-shirts in a queue.', 'duration': 26.524, 'max_score': 9264.9, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co9264900.jpg'}, {'end': 10047.742, 'src': 'embed', 'start': 10013.381, 'weight': 0, 'content': [{'end': 10018.422, 'text': "let's say so you'll find all the data in here itself and that is what i have mentioned here.", 'start': 10013.381, 'duration': 5.041}, {'end': 10022.763, 'text': 'the complete data row is actually stored on the leaf node of the index.', 'start': 10018.422, 'duration': 4.341}, {'end': 10028.164, 'text': 'in case of a clustered index, the index column is either arranged in ascending or descending order.', 'start': 10022.763, 'duration': 5.401}, {'end': 10033.771, 'text': 'as i told, it requires sorting, and that is where, uh, it is more efficient.', 'start': 10028.164, 'duration': 5.607}, {'end': 10035.972, 'text': 'the better the sorting is available.', 'start': 10033.771, 'duration': 2.201}, {'end': 10047.742, 'text': 'the better the sorting, the better is the performance, and this sorting is done physically in the data drive itself or in the table itself.', 'start': 10035.972, 'duration': 11.77}], 'summary': 'Data is stored in leaf node, index column is sorted for efficiency and performance.', 'duration': 34.361, 'max_score': 10013.381, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co10013381.jpg'}], 'start': 9103.535, 'title': 'Sql server indexing and optimization', 'summary': 'Covers the importance of efficient data retrieval in sql server through continuous restructuring and organizing of indexes, emphasizing the impact of index creation on tables, the significance of unique integer column indexes, and the importance of optimizing sql indexes for improved performance. it also discusses the types of indexes, their performance, and their impact on the execution plan in sql server, providing specific examples of efficiency gains, such as reducing the number of rows read from 10,000 to 1 and decreasing system resource usage by almost 100 times.', 'chapters': [{'end': 9219.673, 'start': 9103.535, 'title': 'Sql server indexing', 'summary': "Discusses the importance of efficient data retrieval in rdbms, emphasizing microsoft's efforts to enhance user experience by continuously restructuring and organizing indexes, leading to faster data access and improved performance.", 'duration': 116.138, 'highlights': ['Microsoft continuously restructures and organizes indexes to ensure top-notch user experience and performance in SQL Server. Microsoft ensures top-notch user experience and performance by continuously restructuring and organizing indexes in SQL Server.', 'Indexes provide faster access to data using the column on which the index is created. Indexes offer faster data access using the indexed column, enhancing data retrieval efficiency.', 'Creating indexes on columns in tables allows for faster data retrieval, even when searching for data not indexed. Creating indexes on columns in tables accelerates data retrieval, even for unindexed data like employee names or addresses.']}, {'end': 9847.883, 'start': 9219.991, 'title': 'Understanding indexes in sql', 'summary': 'Explains how indexes work in sql, detailing the impact of creating indexes on tables and the efficiency gained, as well as the significance of creating indexes on unique integer columns for improved performance.', 'duration': 627.892, 'highlights': ['Creating indexes on unique integer columns significantly improves performance by reducing the number of reads required to locate data, resulting in a maximum of three reads with an index seek compared to 20 reads without an index. With an index, it takes up to three reads to locate the whole data for a specific ID, resulting in a maximum of three reads with an index seek compared to 20 reads without an index.', 'The binary tree structure created by indexes flattens tables, reducing the need for recursive searches and providing easy access to data rows. Indexes create a binary tree structure that flattens tables, reducing the need for recursive searches and providing easy access to data rows.', 'Indexes are inefficient when created on varchar values due to the complexities of sorting and arranging the data, making it a task for SQL Server. Creating indexes on varchar values is inefficient due to the complexities of sorting and arranging the data, making it a challenging task for SQL Server.']}, {'end': 10579.978, 'start': 9847.883, 'title': 'Index performance and types', 'summary': 'Discusses index performance, recommending the creation of indexes on unique integer columns for consistent performance and explains the two important types of indexes, clustered and non-clustered, emphasizing the differences in data storage and retrieval speed, and the capability of having only one clustered index per table.', 'duration': 732.095, 'highlights': ['The chapter emphasizes the importance of creating indexes on unique integer columns for consistent performance, suggesting that creating indexes on non-unique columns might result in less efficient indexes.', 'It explains the two important types of indexes, clustered and non-clustered, detailing the differences in data storage and retrieval speed, highlighting that a clustered index stores the complete data row in the leaf node, while a non-clustered index stores a pointer to the memory address of the row, which results in slower performance compared to clustered indexes.', 'It details the capability of having only one clustered index per table, mentioning that the second column ordering has a negligible impact, and recommends creating the clustered index on one column, typically the primary key, for efficient performance.']}, {'end': 11030.693, 'start': 10580.518, 'title': 'Understanding sql server execution plan', 'summary': 'Explains the importance of an execution plan in sql server, highlighting the impact of an index on the execution plan, such as reducing the number of rows read from 10,000 to 1 and decreasing the system resources usage by almost 100 times.', 'duration': 450.175, 'highlights': ['The impact of an index on the execution plan is significant, reducing the number of rows read from 10,000 to 1, thereby improving efficiency and saving time and resources.', 'Understanding the system resource usage, the estimated input-output cost and CPU cost on a non-indexed table are 0.027 and 0.0110, respectively, whereas on an indexed table, the costs are substantially lower at 0.003125 and 0.0001581, respectively.', 'The difference in system resource usage between a non-indexed and an indexed table is substantial, with the indexed table requiring almost 100 times less CPU and input-output resources compared to the non-indexed table.']}, {'end': 11300.659, 'start': 11031.453, 'title': 'Optimizing sql indexes', 'summary': 'Explains the importance of optimizing sql indexes by limiting the number of indexes, creating indexes on rarely updated columns, and selecting the primary key as an ideal candidate for the clustered index.', 'duration': 269.206, 'highlights': ['The importance of limiting the number of indexes and creating them on rarely updated columns Creating indexes on columns which are very rarely updated can help in improving the performance of the table, as DML operations get slower with the more number of indexes.', 'Selecting the primary key as an ideal candidate for the clustered index The primary key, with unique and non-null values, is considered an ideal candidate for the clustered index, as SQL Server automatically creates a unique clustered index on the primary key column.', 'Default indexes created by SQL Server When a primary key is created, SQL Server automatically creates a unique clustered index on that column, and when a unique constraint is created, a non-clustered index is created by default.']}], 'duration': 2197.124, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co9103535.jpg', 'highlights': ['Microsoft continuously restructures and organizes indexes to ensure top-notch user experience and performance in SQL Server.', 'Creating indexes on unique integer columns significantly improves performance by reducing the number of reads required to locate data.', 'The impact of an index on the execution plan is significant, reducing the number of rows read from 10,000 to 1, thereby improving efficiency and saving time and resources.', 'The difference in system resource usage between a non-indexed and an indexed table is substantial, with the indexed table requiring almost 100 times less CPU and input-output resources compared to the non-indexed table.', 'The importance of limiting the number of indexes and creating them on rarely updated columns.']}, {'end': 11977.266, 'segs': [{'end': 11330.629, 'src': 'embed', 'start': 11301.44, 'weight': 0, 'content': [{'end': 11306.822, 'text': 'What are stored procedures and how are these important in the SQL Server context?', 'start': 11301.44, 'duration': 5.382}, {'end': 11310.845, 'text': "Let's start with an example.", 'start': 11307.744, 'duration': 3.101}, {'end': 11318.706, 'text': "maybe? okay, let's say you are asked to design queries to calculate the salaries of the employees.", 'start': 11310.845, 'duration': 7.861}, {'end': 11323.367, 'text': "okay, that means let's say you are given an assignment or a task saying that, hey,", 'start': 11318.706, 'duration': 4.661}, {'end': 11330.629, 'text': 'we want you to build queries that would calculate the salaries of our employees in the database.', 'start': 11323.367, 'duration': 7.262}], 'summary': 'Stored procedures are important in sql server for querying employee salaries.', 'duration': 29.189, 'max_score': 11301.44, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co11301440.jpg'}, {'end': 11443.969, 'src': 'embed', 'start': 11416.879, 'weight': 3, 'content': [{'end': 11421.102, 'text': "okay, let's say you take seven leaves in that particular month.", 'start': 11416.879, 'duration': 4.223}, {'end': 11430.688, 'text': 'so you are qualified to, or the company can deduct the organization can deduct two days worth of leaves from your salary.', 'start': 11421.102, 'duration': 9.586}, {'end': 11437.303, 'text': 'okay, two days worth of salary from your whole monthly salary, because you have taken two extra leaves,', 'start': 11430.688, 'duration': 6.615}, {'end': 11443.969, 'text': 'which is something that you are not eligible for, which is beyond your allotted allotted leaves.', 'start': 11437.303, 'duration': 6.666}], 'summary': "7 leaves taken, 2 days' salary deducted for exceeding allowed leaves.", 'duration': 27.09, 'max_score': 11416.879, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co11416879.jpg'}, {'end': 11584.53, 'src': 'embed', 'start': 11555.131, 'weight': 1, 'content': [{'end': 11558.814, 'text': 'they are going to be paid, so this is also a deductible amount.', 'start': 11555.131, 'duration': 3.683}, {'end': 11564.098, 'text': 'so you see, this is a very constructive and a thought through approach.', 'start': 11558.814, 'duration': 5.284}, {'end': 11572.48, 'text': 'to calculate the salary now you cannot bring this active list of employees step to the end of the calculation,', 'start': 11564.098, 'duration': 8.382}, {'end': 11578.965, 'text': 'can you? Because you would then end up calculating the salaries for all the employees, which is not a good idea.', 'start': 11572.48, 'duration': 6.485}, {'end': 11584.53, 'text': 'So it should be performed in a sequential approach or in a constructive approach,', 'start': 11579.306, 'duration': 5.224}], 'summary': 'Employees will be paid with a deductible amount, requiring a sequential and constructive approach for salary calculation.', 'duration': 29.399, 'max_score': 11555.131, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co11555131.jpg'}], 'start': 11301.44, 'title': 'Stored procedures in sql', 'summary': 'Discusses the importance, usage, and advantages of stored procedures in sql, emphasizing their ability to encapsulate operations, access control, and object-oriented programming encapsulation, highlighting efficiency, reusability, and maintenance benefits.', 'chapters': [{'end': 11745.24, 'start': 11301.44, 'title': 'Stored procedures in sql server', 'summary': 'Discusses the importance of stored procedures in sql server by explaining the process of calculating employee salaries using multiple queries and emphasizes the efficiency and maintainability benefits of using stored procedures over individual queries, highlighting the reduction in the number of queries, reusability, and ease of maintenance.', 'duration': 443.8, 'highlights': ['Stored procedures provide efficiency and maintainability benefits by reducing the number of queries, enabling reusability, and facilitating ease of maintenance. Stored procedures reduce the number of queries from 30-40 to a single procedure, allowing for reusability across multiple departments and enabling easier maintenance.', 'Using stored procedures allows for easy implementation of logic changes by updating the procedure, thus enhancing maintainability. Implementing logic changes, such as modifying bonus or incentive calculations, becomes easier as the changes can be made in a single stored procedure, ensuring consistency across departments.', 'The inefficiency and lack of maintainability when using individual queries across multiple departments highlights the need for stored procedures. Utilizing individual queries across multiple departments results in inefficiency and lack of maintainability, as any logic changes would require updates across all departments, emphasizing the necessity for stored procedures.']}, {'end': 11977.266, 'start': 11745.24, 'title': 'Stored procedures in sql', 'summary': 'Discusses the usage, creation, and advantages of stored procedures in sql, emphasizing their ability to encapsulate and encapsulate operations, access control, and encapsulation in object-oriented programming.', 'duration': 232.026, 'highlights': ['Stored procedures are blocks of SQL statements that can perform various operations, such as creating, updating, deleting data, and can be reused and accessed by users with appropriate permissions.', 'Stored procedures provide encapsulation, allowing users to utilize functionality without needing to concern themselves with the internal operations, similar to encapsulation in object-oriented programming.', 'Stored procedures offer access control, enabling administrators to control who can execute them, providing security and restriction capabilities.']}], 'duration': 675.826, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co11301440.jpg', 'highlights': ['Stored procedures reduce the number of queries from 30-40 to a single procedure, allowing for reusability across multiple departments and enabling easier maintenance.', 'Implementing logic changes, such as modifying bonus or incentive calculations, becomes easier as the changes can be made in a single stored procedure, ensuring consistency across departments.', 'Utilizing individual queries across multiple departments results in inefficiency and lack of maintainability, emphasizing the necessity for stored procedures.', 'Stored procedures provide encapsulation, allowing users to utilize functionality without needing to concern themselves with the internal operations, similar to encapsulation in object-oriented programming.', 'Stored procedures offer access control, enabling administrators to control who can execute them, providing security and restriction capabilities.']}, {'end': 13403.601, 'segs': [{'end': 12004.004, 'src': 'embed', 'start': 11977.266, 'weight': 5, 'content': [{'end': 11982.149, 'text': 'instead of understanding the internal complexities that are involved in it.', 'start': 11977.266, 'duration': 4.883}, {'end': 11984.57, 'text': 'And that is what a stored procedure exactly does.', 'start': 11982.509, 'duration': 2.061}, {'end': 11988.112, 'text': 'We write all of our business logic inside the stored procedure.', 'start': 11984.85, 'duration': 3.262}, {'end': 11991.675, 'text': 'And when we share it with someone, we just share the name.', 'start': 11988.513, 'duration': 3.162}, {'end': 11999.019, 'text': 'So they just execute the name and automatically the logic that is placed inside of it is calculated.', 'start': 11991.775, 'duration': 7.244}, {'end': 12000.963, 'text': 'or is performed.', 'start': 11999.562, 'duration': 1.401}, {'end': 12004.004, 'text': "okay, that's what encapsulation means.", 'start': 12000.963, 'duration': 3.041}], 'summary': 'Stored procedures encapsulate business logic, simplifying sharing and execution.', 'duration': 26.738, 'max_score': 11977.266, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co11977266.jpg'}, {'end': 12232.845, 'src': 'embed', 'start': 12207.176, 'weight': 7, 'content': [{'end': 12215.439, 'text': "okay?. Let's say the distance between your place of stay and your office is 12 miles and you reach office faster because there is very less traffic.", 'start': 12207.176, 'duration': 8.263}, {'end': 12223.482, 'text': 'So what do you compromise on? In this route, you burn more fuel because of the extra distance.', 'start': 12215.679, 'duration': 7.803}, {'end': 12229.244, 'text': 'But what do you save on is your time, okay? So one pro and one con with the first route.', 'start': 12223.882, 'duration': 5.362}, {'end': 12231.185, 'text': "Let's talk about the second route.", 'start': 12229.504, 'duration': 1.681}, {'end': 12232.845, 'text': "Let's say, the second route.", 'start': 12231.505, 'duration': 1.34}], 'summary': 'Choosing longer route saves time, but burns more fuel.', 'duration': 25.669, 'max_score': 12207.176, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co12207176.jpg'}, {'end': 12520.895, 'src': 'embed', 'start': 12461.82, 'weight': 4, 'content': [{'end': 12471.444, 'text': 'but in case of stored procedures, parsing only happens the time when we create the procedure and the compiling, where the execution plan is generated,', 'start': 12461.82, 'duration': 9.624}, {'end': 12479.124, 'text': 'is cast in the first few runs, and this saves a lot of time on the query processing.', 'start': 12471.444, 'duration': 7.68}, {'end': 12490.17, 'text': 'Okay, So the same query when executed normally versus when executed inside a stored procedure makes a lot of difference on the time and the performance.', 'start': 12479.384, 'duration': 10.786}, {'end': 12494.693, 'text': 'Okay So that is what execution plan caching really means.', 'start': 12490.691, 'duration': 4.002}, {'end': 12498.675, 'text': 'The SP when created is passed and compiled.', 'start': 12495.073, 'duration': 3.602}, {'end': 12502.307, 'text': 'So it does not compile every time it is run.', 'start': 12499.045, 'duration': 3.262}, {'end': 12511.531, 'text': 'It also caches the best execution plan in the first few initial runs, which makes it stand out from normal queries.', 'start': 12502.567, 'duration': 8.964}, {'end': 12520.895, 'text': 'Now, what is an execution plan? An execution plan is the path used by SQL optimizer to execute your query and return the result.', 'start': 12511.891, 'duration': 9.004}], 'summary': 'Stored procedures save time by caching execution plans, leading to better query performance.', 'duration': 59.075, 'max_score': 12461.82, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co12461820.jpg'}, {'end': 12623.844, 'src': 'embed', 'start': 12594.395, 'weight': 1, 'content': [{'end': 12599.68, 'text': "okay, here i'm trying to say select me, select me the data from the employee table.", 'start': 12594.395, 'duration': 5.285}, {'end': 12601.381, 'text': 'very simple, one right.', 'start': 12599.68, 'duration': 1.701}, {'end': 12604.023, 'text': "so let's execute this.", 'start': 12601.381, 'duration': 2.642}, {'end': 12606.205, 'text': 'it says commands completed successfully.', 'start': 12604.023, 'duration': 2.182}, {'end': 12609.719, 'text': 'now do you know if the stored procedure is created?', 'start': 12606.205, 'duration': 3.514}, {'end': 12616.001, 'text': 'obviously you can do alt f1 on this alt f1 and you will see that this is the name.', 'start': 12609.719, 'duration': 6.282}, {'end': 12617.202, 'text': 'this is the store.', 'start': 12616.001, 'duration': 1.201}, {'end': 12623.844, 'text': "the type is stored procedure and it's created on 4th of october at 9 52 in the evening.", 'start': 12617.202, 'duration': 6.642}], 'summary': 'Successfully selected data from employee table, created stored procedure on 4th october at 9:52 pm.', 'duration': 29.449, 'max_score': 12594.395, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co12594395.jpg'}, {'end': 12872.991, 'src': 'embed', 'start': 12821.436, 'weight': 2, 'content': [{'end': 12824.978, 'text': 'that means if i pass a five, get me the details for five.', 'start': 12821.436, 'duration': 3.542}, {'end': 12829.101, 'text': "if i don't pass any value, just give me all the data.", 'start': 12824.978, 'duration': 4.123}, {'end': 12833.764, 'text': 'okay. so in that case what you can do is you can make the.', 'start': 12829.101, 'duration': 4.663}, {'end': 12835.985, 'text': 'you can pass a default value.', 'start': 12833.764, 'duration': 2.221}, {'end': 12836.606, 'text': 'okay, like this.', 'start': 12835.985, 'duration': 0.621}, {'end': 12840.649, 'text': "So when you give a default value, let's say I give a default value of four.", 'start': 12837.048, 'duration': 3.601}, {'end': 12844.571, 'text': "Okay So what will happen is let's, let's alter this.", 'start': 12840.99, 'duration': 3.581}, {'end': 12849.033, 'text': 'And now if I just execute this much, okay.', 'start': 12845.131, 'duration': 3.902}, {'end': 12850.333, 'text': 'Just execute the procedure.', 'start': 12849.053, 'duration': 1.28}, {'end': 12854.475, 'text': 'It would give me the default employee details, which is four.', 'start': 12850.833, 'duration': 3.642}, {'end': 12858.096, 'text': 'Remember I give four here as the default employee ID.', 'start': 12854.855, 'duration': 3.241}, {'end': 12862.158, 'text': 'Okay But if I pass the value from here.', 'start': 12858.617, 'duration': 3.541}, {'end': 12863.866, 'text': "Let's say I pass five.", 'start': 12862.725, 'duration': 1.141}, {'end': 12865.927, 'text': "It'll now give me the five details.", 'start': 12864.086, 'duration': 1.841}, {'end': 12869.709, 'text': "So if I pass a value from here, it'll take this else.", 'start': 12866.287, 'duration': 3.422}, {'end': 12872.991, 'text': "If I only execute this, it'll use the default value.", 'start': 12870.209, 'duration': 2.782}], 'summary': 'Procedure returns default or specified employee details based on input', 'duration': 51.555, 'max_score': 12821.436, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co12821436.jpg'}, {'end': 13408.866, 'src': 'embed', 'start': 13380.931, 'weight': 0, 'content': [{'end': 13385.773, 'text': "Right It just says commands completed successfully because there's nothing to execute because the condition is.", 'start': 13380.931, 'duration': 4.842}, {'end': 13391.148, 'text': "false. but let's say i make it one which means it's true.", 'start': 13386.243, 'duration': 4.905}, {'end': 13402.68, 'text': 'now the procedure will get executed and it returns me the output okay, so this is how you can execute your procedures conditionally right views?', 'start': 13391.148, 'duration': 11.532}, {'end': 13403.601, 'text': 'okay, all right.', 'start': 13402.68, 'duration': 0.921}, {'end': 13408.866, 'text': 'so what are views and how are these important in the context of sql server?', 'start': 13403.601, 'duration': 5.265}], 'summary': 'Executing a procedure conditionally in sql server and discussing the importance of views.', 'duration': 27.935, 'max_score': 13380.931, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co13380931.jpg'}], 'start': 11977.266, 'title': 'Benefits of stored procedures', 'summary': 'Explores the advantages of using stored procedures, including encapsulating business logic, ensuring reusability, facilitating maintainability, execution plan caching, and comparing stored procedures with sql queries and functions.', 'chapters': [{'end': 12074.196, 'start': 11977.266, 'title': 'Stored procedures: encapsulation, reusability, and maintainability', 'summary': 'Discusses the benefits of using stored procedures, including encapsulating business logic, ensuring reusability, and facilitating maintainability by allowing easy updates for multiple users.', 'duration': 96.93, 'highlights': ['Stored procedures encapsulate business logic into a single unit, allowing for easy execution and calculation without exposing internal details. Stored procedures encapsulate business logic, enabling easy execution and calculation without revealing internal details, promoting encapsulation and simplifying execution.', 'Stored procedures enable reusability and can be called multiple times by users with execute permissions. Stored procedures support reusability and can be called multiple times by users with execute permissions, enhancing efficiency and reducing redundancy.', 'Changes in business logic or data transformation can be made once in the stored procedure, ensuring automatic use of the updated logic for subsequent executions. Updates in business logic or data transformation can be made once in the stored procedure, ensuring automatic utilization of the updated logic for subsequent executions, promoting maintainability and consistency.']}, {'end': 12343.694, 'start': 12074.196, 'title': 'Stored procedures vs. sql queries', 'summary': 'Discusses the superiority of stored procedures over sql queries in terms of execution plan caching and the impact on time, fuel consumption, and vehicle condition based on different routes, analogous to query optimization and execution plan generation.', 'duration': 269.498, 'highlights': ['The chapter explains the advantage of execution plan caching in stored procedures over SQL queries, which involves the generation of execution plans for queries and their subsequent usage for data fetching. Stored procedures have an advantage in execution plan caching over SQL queries, as they involve the generation of execution plans for queries and their subsequent usage for data fetching.', 'The analogy of choosing different routes based on time, fuel, and vehicle condition is used to illustrate the trade-offs involved in selecting query execution plans based on priorities, such as saving time, fuel, or vehicle condition. The analogy of choosing different routes based on time, fuel, and vehicle condition is used to illustrate the trade-offs involved in selecting query execution plans based on priorities, such as saving time, fuel, or vehicle condition.', 'The chapter emphasizes the three main functions of query optimization: parsing the query for syntax errors, compiling the query to generate the execution plan, and utilizing the execution plan to fetch data, highlighting the process involved in query optimization. The chapter emphasizes the three main functions of query optimization: parsing the query for syntax errors, compiling the query to generate the execution plan, and utilizing the execution plan to fetch data, highlighting the process involved in query optimization.']}, {'end': 13108.275, 'start': 12343.914, 'title': 'Sql server execution plan and stored procedures', 'summary': 'Explains the concept of execution plan in sql server, comparing the routes taken by normal queries and stored procedures, highlighting the benefits of execution plan caching in stored procedures, and demonstrating the creation and execution of stored procedures with static and dynamic parameters.', 'duration': 764.361, 'highlights': ['Stored procedures save time on parsing and compilation Stored procedures are advantageous as they are parsed only once during creation and their execution plan is compiled and cached in the SQL Server memory after a few initial runs, saving time on parsing and compilation for subsequent executions.', 'Execution plan caching in stored procedures improves time and performance Execution plan caching in stored procedures allows the SQL optimizer to cache the best execution plan after a few initial runs, leading to improved time and performance compared to normal queries.', 'Creation and execution of stored procedures with static and dynamic parameters demonstrated The process of creating and executing stored procedures with static and dynamic parameters, including the use of default values and optional parameters, is illustrated.']}, {'end': 13403.601, 'start': 13108.495, 'title': 'Stored procedures with output parameters', 'summary': 'Discusses creating stored procedures with output parameters to return specific data, showcasing how to use input and output parameters with procedures, and comparing their usage with functions, including a demonstration of executing procedures conditionally.', 'duration': 295.106, 'highlights': ['Creating a stored procedure with output parameters to return specific data Demonstrates creating a procedure with an output parameter to count employees residing in a given address and returning the count as the output.', 'Usage of input and output parameters with procedures Explains how to use input and output parameters with procedures to control optional and mandatory parameters, showcasing the ability to pass input values and store output values in variables.', 'Comparison of usage with functions and conditional execution of procedures Compares the usage of output parameters with functions, expressing a preference for using functions for similar tasks, and demonstrates conditional execution of procedures based on specified conditions.']}], 'duration': 1426.335, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co11977266.jpg', 'highlights': ['Stored procedures encapsulate business logic into a single unit, allowing for easy execution and calculation without exposing internal details.', 'Stored procedures enable reusability and can be called multiple times by users with execute permissions.', 'Changes in business logic or data transformation can be made once in the stored procedure, ensuring automatic use of the updated logic for subsequent executions.', 'Stored procedures have an advantage in execution plan caching over SQL queries, as they involve the generation of execution plans for queries and their subsequent usage for data fetching.', 'The analogy of choosing different routes based on time, fuel, and vehicle condition is used to illustrate the trade-offs involved in selecting query execution plans based on priorities, such as saving time, fuel, or vehicle condition.', 'Stored procedures save time on parsing and compilation for subsequent executions.', 'Execution plan caching in stored procedures improves time and performance compared to normal queries.', 'Demonstrates creating a procedure with an output parameter to count employees residing in a given address and returning the count as the output.', 'Explains how to use input and output parameters with procedures to control optional and mandatory parameters, showcasing the ability to pass input values and store output values in variables.', 'Compares the usage of output parameters with functions, expressing a preference for using functions for similar tasks, and demonstrates conditional execution of procedures based on specified conditions.']}, {'end': 15919.691, 'segs': [{'end': 13706.831, 'src': 'embed', 'start': 13679.774, 'weight': 0, 'content': [{'end': 13684.336, 'text': 'I mean, if you go that line, if you go, if you walk that lane, you will understand how hard it is.', 'start': 13679.774, 'duration': 4.562}, {'end': 13690.761, 'text': "If you have like 10 developers working on the UI and let's say you are the one taking care of the database.", 'start': 13684.897, 'duration': 5.864}, {'end': 13695.684, 'text': 'Imagine 10 people telling you how to design a database or 10 people asking you to modify the database.', 'start': 13691.141, 'duration': 4.543}, {'end': 13697.225, 'text': "It's going to be really, really bad.", 'start': 13695.984, 'duration': 1.241}, {'end': 13699.486, 'text': 'Okay So that is one aspect to it.', 'start': 13697.425, 'duration': 2.061}, {'end': 13704.109, 'text': 'The same thing applies when someone asks you access to the data.', 'start': 13699.706, 'duration': 4.403}, {'end': 13706.831, 'text': 'Okay, You will when you have a way,', 'start': 13704.469, 'duration': 2.362}], 'summary': 'Managing database with 10 developers can be challenging.', 'duration': 27.057, 'max_score': 13679.774, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co13679774.jpg'}, {'end': 13986.526, 'src': 'embed', 'start': 13953.743, 'weight': 1, 'content': [{'end': 13958.927, 'text': 'As this is just a layer on top of a table, views do not store any data physically in the server.', 'start': 13953.743, 'duration': 5.184}, {'end': 13961.849, 'text': 'That means it does not have to store.', 'start': 13959.267, 'duration': 2.582}, {'end': 13965.792, 'text': 'It is very lightweight on the server and does not store the data.', 'start': 13961.869, 'duration': 3.923}, {'end': 13970.735, 'text': 'Again, the one good thing that I like very much about the views is this.', 'start': 13966.292, 'duration': 4.443}, {'end': 13977.46, 'text': "It is so lightweight on the server that it doesn't take any physical memory on the disk.", 'start': 13971.416, 'duration': 6.044}, {'end': 13986.526, 'text': 'And it makes absolute sense as well because, as I said, views are connected to tables and the data is already there in the tables.', 'start': 13978.401, 'duration': 8.125}], 'summary': 'Views are lightweight on the server, not storing any physical data, connected to tables.', 'duration': 32.783, 'max_score': 13953.743, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co13953743.jpg'}, {'end': 15872.01, 'src': 'embed', 'start': 15846.636, 'weight': 3, 'content': [{'end': 15855.481, 'text': 'Right. so any insert or update that we do it directly impacts the base table and obviously now when I run the view as the view, which is the data from the.', 'start': 15846.636, 'duration': 8.845}, {'end': 15859.863, 'text': 'Table itself so you will find.', 'start': 15857.342, 'duration': 2.521}, {'end': 15862.945, 'text': 'The address of this record as India.', 'start': 15860.884, 'duration': 2.061}, {'end': 15865.947, 'text': "Now let's see the delete as well.", 'start': 15863.785, 'duration': 2.162}, {'end': 15872.01, 'text': "Last of the DML statements and let's say I delete this and I delete from the view again where employee is equal to 9.", 'start': 15866.047, 'duration': 5.963}], 'summary': 'Inserting or updating directly impacts base table; view reflects data from table; address of record is india; demonstrated delete dml statement.', 'duration': 25.374, 'max_score': 15846.636, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co15846636.jpg'}, {'end': 15927.158, 'src': 'embed', 'start': 15896.306, 'weight': 2, 'content': [{'end': 15898.708, 'text': 'the view does not have any data of its own.', 'start': 15896.306, 'duration': 2.402}, {'end': 15902.851, 'text': 'all that it has is the data from the base team.', 'start': 15898.708, 'duration': 4.143}, {'end': 15907.118, 'text': 'and if i run this view also, you will not find the id9 anymore.', 'start': 15902.851, 'duration': 4.267}, {'end': 15911.082, 'text': 'now you might ask the views are meant to be shared with outside users.', 'start': 15907.118, 'duration': 3.964}, {'end': 15913.985, 'text': 'can they update the data on our tables?', 'start': 15911.082, 'duration': 2.903}, {'end': 15919.691, 'text': 'no, because whenever you create a view, we mostly grant read access to the outside users.', 'start': 15913.985, 'duration': 5.706}, {'end': 15927.158, 'text': 'okay, so they cannot make any modifications through the view because if they have read permission on the view, they can never be.', 'start': 15919.691, 'duration': 7.467}], 'summary': 'Views have data from the base team, granting read access to outside users.', 'duration': 30.852, 'max_score': 15896.306, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co15896306.jpg'}], 'start': 13403.601, 'title': 'Sql server data management', 'summary': 'Discusses the significance of data in business, emphasizing its value and potential applications, database management best practices, implementing and managing views in sql, and the importance of schema binding to prevent orphan views, while also covering dml operations on views.', 'chapters': [{'end': 13550.628, 'start': 13403.601, 'title': 'Importance of data in sql server', 'summary': "Discusses the growing importance of data in the business context, emphasizing its value as the 'new oil' and the various potential applications and analyses that can be derived from it, including machine learning and artificial intelligence.", 'duration': 147.027, 'highlights': ["The growing importance of data is emphasized, with data being compared to the 'new oil' in terms of its significance in the business context and its potential applications, such as machine learning and artificial intelligence.", 'The chapter highlights the potential applications of important data, including analysis, predictions, deriving metrics, and comparisons, showcasing the multifaceted value of data in various contexts.', 'The significance of sharing data between internal and external consumers, as well as between related companies with pre-decided agreements, is discussed as a potential practice in the business context.']}, {'end': 13829.351, 'start': 13550.628, 'title': 'Database management best practices', 'summary': 'Emphasizes the importance of database ownership, cautious decision-making, and controlled data access, highlighting the need for thorough justification and management approval before granting access.', 'duration': 278.723, 'highlights': ['Always act like a miser when given a database, as any additional object increases the effort required to manage and maintain it.', 'Question the necessity of database modifications and ensure efficient alternatives are explored before implementation to avoid creating a poorly designed or maintained database.', 'Thoroughly understand and justify the need for data access, seeking both user justification and management approval before granting access, especially for critical information such as user details and salaries.']}, {'end': 14674.966, 'start': 13830.051, 'title': 'Implementing views in sql', 'summary': 'Explores the concept of views in sql, highlighting that views are an additional layer on top of a table used to protect sensitive information, and they do not store any data physically, making them lightweight and efficient. views also enable users to control data exposure and can be used to filter and join multiple tables, offering a feasible solution for sharing specific data with different stakeholders in various regions.', 'duration': 844.915, 'highlights': ['Views are an additional layer on top of a table used to protect sensitive information and only expose the columns that are needed for outside users. Views act as an additional layer on top of a table, allowing users to protect sensitive information and only expose specific columns as required, enhancing data security and control.', 'Views do not store any data physically, making them lightweight and efficient on the server, and they retrieve data directly from the underlying table, eliminating the need to store duplicate data. Views do not store any data physically, resulting in a lightweight and efficient implementation on the server. They directly retrieve data from the underlying table, eliminating the need for duplicate data storage.', 'Views enable users to filter data by creating views with specific conditions, such as excluding certain columns or filtering rows based on specific criteria like location, providing a feasible solution for sharing specific data with different stakeholders in various regions. Views allow users to filter data by excluding certain columns or filtering rows based on specific criteria, offering a feasible solution for sharing specific data with different stakeholders in various regions.', 'Views can be used to join multiple tables, enabling users to expose only the required columns and data, and apply filters to the query, providing an efficient way to share data from different tables with users. Views can be used to join multiple tables, allowing users to expose only the required columns and data, and apply filters to the query, offering an efficient way to share data from different tables with users.']}, {'end': 15348.912, 'start': 14674.966, 'title': 'Schema binding in sql views', 'summary': 'Discusses the importance of schema binding in sql views to prevent orphan views and ensure the views work at all times by binding them to the dependent physical columns in the table, allowing alteration only for the columns not binded in the view.', 'duration': 673.946, 'highlights': ["Schema binding ensures that the view works at all times by preventing modifications to dependent columns and base tables, thus avoiding orphan views. Schema binding in SQL views ensures that dependent columns and base tables are not modified, dropped, or renamed, guaranteeing the view's functionality at all times.", "Alteration of columns not binded in the view is allowed, while altering the binded columns is prohibited with schema binding. Columns not binded in the view can be altered, removed, or renamed, while any alteration on the columns referenced in the view is prohibited with schema binding, maintaining the view's integrity.", "Schema binding prevents alterations to columns referenced in the view, such as dropping or modifying them, ensuring the view's consistency. Schema binding prevents alterations to columns referenced in the view, such as dropping or modifying them, ensuring the integrity and consistency of the view."]}, {'end': 15919.691, 'start': 15348.912, 'title': 'Dml operations on views', 'summary': 'Discusses the conditions for performing dml operations on views, demonstrating the insertion, update, and deletion of records from a view, and how these operations directly impact the base table.', 'duration': 570.779, 'highlights': ['The chapter explains the conditions for performing DML operations on views, emphasizing the need for a single select in the view and the requirement to select all not null columns in the view.', 'The demonstration of inserting data into the view showcases the impact on the base table, with the inability to insert into non-selected not null columns resulting in null values being stored.', "The update operation on the view is illustrated, highlighting how it directly updates the base table, as evidenced by the change in the base table's data after the update.", 'The deletion of a record from the view is demonstrated, showing that the deletion from the view also affects the base table, resulting in the absence of the deleted record in both the view and the base table.']}], 'duration': 2516.09, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co13403601.jpg', 'highlights': ["The growing importance of data is emphasized, with data being compared to the 'new oil' in terms of its significance in the business context and its potential applications, such as machine learning and artificial intelligence.", 'Views are an additional layer on top of a table used to protect sensitive information and only expose the columns that are needed for outside users.', 'Schema binding ensures that the view works at all times by preventing modifications to dependent columns and base tables, thus avoiding orphan views.', 'The chapter explains the conditions for performing DML operations on views, emphasizing the need for a single select in the view and the requirement to select all not null columns in the view.']}, {'end': 17554.232, 'segs': [{'end': 16903.599, 'src': 'embed', 'start': 16875.608, 'weight': 10, 'content': [{'end': 16878.85, 'text': "okay, but yeah, i mean it's just there, all right.", 'start': 16875.608, 'duration': 3.242}, {'end': 16882.012, 'text': "so now let's talk about.", 'start': 16878.85, 'duration': 3.162}, {'end': 16885.494, 'text': 'let me show you how a transaction actually works.', 'start': 16882.012, 'duration': 3.482}, {'end': 16888.777, 'text': 'so here, uh, and an example of explicit transaction.', 'start': 16885.494, 'duration': 3.283}, {'end': 16895.457, 'text': 'here i say begin transaction and then i put all my queries below this.', 'start': 16888.777, 'duration': 6.68}, {'end': 16896.957, 'text': 'okay, so what do i do here?', 'start': 16895.457, 'duration': 1.5}, {'end': 16903.599, 'text': "i'm trying to update the employee table where, for employee one, i'm trying to update the last name to yadav.", 'start': 16896.957, 'duration': 6.642}], 'summary': "Demonstrating an explicit transaction with an example of updating the employee table, specifically changing the last name for employee one to 'yadav'.", 'duration': 27.991, 'max_score': 16875.608, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co16875608.jpg'}, {'end': 16982.37, 'src': 'embed', 'start': 16931.405, 'weight': 1, 'content': [{'end': 16936.606, 'text': 'And if I execute this, now I need to execute with the begin transaction.', 'start': 16931.405, 'duration': 5.201}, {'end': 16940.826, 'text': 'So that SQL knows that this query is inside a transaction.', 'start': 16936.646, 'duration': 4.18}, {'end': 16943.207, 'text': 'Okay So let me execute this.', 'start': 16941.047, 'duration': 2.16}, {'end': 16945.067, 'text': 'It shows one row affected right?', 'start': 16943.347, 'duration': 1.72}, {'end': 16950.68, 'text': "And if I run the select now, i haven't ended my transaction.", 'start': 16945.527, 'duration': 5.153}, {'end': 16951.02, 'text': 'mind it?', 'start': 16950.68, 'duration': 0.34}, {'end': 16954.241, 'text': 'okay, i just begin the transaction and run my update.', 'start': 16951.02, 'duration': 3.221}, {'end': 16963.365, 'text': 'now. if i run my select from the employee table, you will notice that the first record has the last name as yadav.', 'start': 16954.241, 'duration': 9.124}, {'end': 16967.506, 'text': 'okay, so the earlier it was kumar, but now it is yadav.', 'start': 16963.365, 'duration': 4.141}, {'end': 16970.207, 'text': "but we haven't finished the execution yet.", 'start': 16967.506, 'duration': 2.701}, {'end': 16971.768, 'text': 'how come is it visible?', 'start': 16970.207, 'duration': 1.561}, {'end': 16974.069, 'text': 'this is called as dirty read.', 'start': 16971.768, 'duration': 2.301}, {'end': 16978.847, 'text': 'okay, dirty read is when, oops, sorry, it has one load.', 'start': 16974.069, 'duration': 4.778}, {'end': 16982.37, 'text': 'wonder, i want to sync okay, anyways.', 'start': 16978.847, 'duration': 3.523}], 'summary': 'Executing update query within a transaction results in a dirty read, changing the last name from kumar to yadav in the employee table.', 'duration': 50.965, 'max_score': 16931.405, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co16931405.jpg'}, {'end': 17035.318, 'src': 'embed', 'start': 17006.021, 'weight': 2, 'content': [{'end': 17013.085, 'text': 'so when a transaction begins right, all the changes that you make here stays on the log file.', 'start': 17006.021, 'duration': 7.064}, {'end': 17017.688, 'text': 'okay, you remember, when we created a database, i told you that there are two types of files that gets created.', 'start': 17013.085, 'duration': 4.603}, {'end': 17019.569, 'text': 'one is mdf, one is ldf.', 'start': 17017.688, 'duration': 1.881}, {'end': 17025.613, 'text': 'the data is stored in the mdf master data file, but in the ldf we only have the logs.', 'start': 17019.569, 'duration': 6.044}, {'end': 17026.694, 'text': 'okay, the transaction logs.', 'start': 17025.613, 'duration': 1.081}, {'end': 17035.318, 'text': 'So when I put the queries, all the queries that are there under the begin transaction, all these changes reside in the log file.', 'start': 17027.116, 'duration': 8.202}], 'summary': 'Transaction changes are logged in the ldf file, separate from the data in the mdf file.', 'duration': 29.297, 'max_score': 17006.021, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17006021.jpg'}, {'end': 17121.501, 'src': 'embed', 'start': 17092.7, 'weight': 0, 'content': [{'end': 17102.347, 'text': 'And now if I see the employee table, you will notice that the last name is set to Kumar back again because I choose to roll back my change.', 'start': 17092.7, 'duration': 9.647}, {'end': 17110.213, 'text': 'The query, the data that I updated or the operation that I performed as part of the update has been rolled back now.', 'start': 17103.408, 'duration': 6.805}, {'end': 17114.716, 'text': 'If I would have said committed, then I would have seen Yadav here.', 'start': 17111.114, 'duration': 3.602}, {'end': 17121.501, 'text': 'But that time, Yadav must not have been a dirty data.', 'start': 17115.437, 'duration': 6.064}], 'summary': 'The last name in the employee table was rolled back from kumar to yadav due to a change reversal in the database operation.', 'duration': 28.801, 'max_score': 17092.7, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17092700.jpg'}, {'end': 17334.748, 'src': 'embed', 'start': 17305.281, 'weight': 7, 'content': [{'end': 17308.984, 'text': 'so for employee id 4, the first name is steve.', 'start': 17305.281, 'duration': 3.703}, {'end': 17314.648, 'text': 'let me try to update it to david, but this i want to put it in the transaction.', 'start': 17308.984, 'duration': 5.664}, {'end': 17317.11, 'text': "now, for your information, i'm going to paste the.", 'start': 17314.648, 'duration': 2.462}, {'end': 17320.952, 'text': "so i'm going to paste the original name here, which is steve.", 'start': 17317.11, 'duration': 3.842}, {'end': 17323.474, 'text': 'okay, this is the original name.', 'start': 17320.952, 'duration': 2.522}, {'end': 17325.376, 'text': "i'm going to say original.", 'start': 17323.474, 'duration': 1.902}, {'end': 17332.548, 'text': 'okay, so this is steve is the original name.', 'start': 17329.827, 'duration': 2.721}, {'end': 17334.748, 'text': "i'm trying to update it to david.", 'start': 17332.548, 'duration': 2.2}], 'summary': "Updating employee 4's first name from steve to david.", 'duration': 29.467, 'max_score': 17305.281, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17305281.jpg'}, {'end': 17538.228, 'src': 'embed', 'start': 17507.527, 'weight': 11, 'content': [{'end': 17515.089, 'text': 'See the moment this was successful, this returned me the data and now it returns me Steve, which is the original name prior to the transaction.', 'start': 17507.527, 'duration': 7.562}, {'end': 17519.19, 'text': 'So this is how you, how SQL reads the committed data.', 'start': 17515.969, 'duration': 3.221}, {'end': 17526.352, 'text': 'But this has a lag because every time there is a transaction, it will keep the query, keep executing the query.', 'start': 17519.61, 'duration': 6.742}, {'end': 17528.873, 'text': 'Okay Uh, which might not be a good thing.', 'start': 17526.612, 'duration': 2.261}, {'end': 17538.228, 'text': 'But again, if you are very concerned about your data that you are reading, you can use the read committed isolation level.', 'start': 17529.246, 'duration': 8.982}], 'summary': 'Sql reads committed data with a lag due to executing queries for every transaction, but can use read committed isolation level for concern about data integrity.', 'duration': 30.701, 'max_score': 17507.527, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17507527.jpg'}], 'start': 15919.691, 'title': 'Sql transactions and acid properties', 'summary': 'Discusses sql transactions, including views, transactions, and their importance in maintaining the consistency of the database, and the acid properties - atomicity, consistency, isolation, and durability - in sql server transactions. it emphasizes examples from banking and sql, and the implications of transaction isolation levels on data integrity and query performance.', 'chapters': [{'end': 16472.937, 'start': 15919.691, 'title': 'Views, transactions, and sql server', 'summary': 'Discusses the concept of views, transactions, and their importance in maintaining the consistency of the database, with examples from banking and sql, emphasizing that transactions in sql server are a set of operations that are guaranteed to succeed or fail as a single unit, and are used when multiple rows are inserted, updated, or deleted in a sequence.', 'duration': 553.246, 'highlights': ['Transactions in SQL server are a set of operations that are guaranteed to succeed or fail as a single unit. Emphasizes the nature of transactions in SQL server as a single unit that either succeeds or fails.', 'Transactions are used when multiple rows are inserted, updated, or deleted in a sequence to ensure consistency in the database. Explains the use of transactions in maintaining consistency when multiple rows are manipulated in a sequence.', 'Views in SQL only allow users with read permission to perform select operations and restrict insert, update, and delete operations. Clarifies the limitations of views in SQL, where users with read permission can only perform select operations.']}, {'end': 16674.811, 'start': 16472.937, 'title': 'Sql transactions and acid properties', 'summary': 'Discusses sql transactions, including examples of using transactions for maintaining data consistency across databases, and the acid properties - atomicity, consistency, isolation, and durability - in sql server transactions.', 'duration': 201.874, 'highlights': ['Transactions are categorized by the ACID properties - Atomicity, Consistency, Isolation, and Durability. The transactions in SQL Server are identified by four properties referred to as the ACID property, which stands for Atomicity, Consistency, Isolation, and Durability.', "Atomicity property ensures that transactions are regarded as a single unit, succeeding or failing together regardless of the number of steps involved. The atomicity property in transactions states that all the steps involved in a transaction, whether it's two or fifty SQL queries, should be considered as a single unit, either succeeding together or failing together.", 'Consistency property in transactions ensures that the database or table remains in a consistent state after the transaction is committed or rolled back. The consistency property in transactions states that the database or table should be in a consistent state without any data loss or discrepancies after the transaction is committed or rolled back.']}, {'end': 17121.501, 'start': 16674.811, 'title': 'Acid properties and types of transactions', 'summary': 'Discusses the acid properties of transactions, including isolation, durability, and the types of transactions such as auto commit, explicit, and implicit transactions, and their implications, with an example of an explicit transaction and its impact on data visibility and management.', 'duration': 446.69, 'highlights': ['ACID properties (Isolation, Durability) of transactions are discussed, emphasizing the importance of maintaining transaction boundaries and data permanence. The discussion highlights the importance of maintaining transaction boundaries and data permanence, emphasizing the impact of Isolation and Durability properties on ensuring data consistency and permanence within a transaction.', 'Types of transactions (Auto Commit, Explicit, Implicit) are explained, detailing their distinct characteristics and implications. The explanation provides a detailed overview of Auto Commit, Explicit, and Implicit transactions, outlining their specific characteristics and implications in managing data within a transaction.', 'An example of an explicit transaction is provided, demonstrating its impact on data visibility and management, including the concept of dirty read. The explicit transaction example illustrates its influence on data visibility and management, showcasing the concept of dirty read and its implications on data access within a transaction.']}, {'end': 17554.232, 'start': 17121.801, 'title': 'Transaction isolation levels', 'summary': 'Discusses the importance of transaction isolation levels in sql, focusing on read committed and read uncommitted levels, with examples and their impact on data integrity and query performance.', 'duration': 432.431, 'highlights': ['The read committed isolation level ensures that only committed data is read, waiting for active transactions to complete, thus ensuring data integrity. Read committed isolation level guarantees data integrity by only reading committed data, ensuring no dirty data is included.', 'The read uncommitted isolation level allows reading uncommitted data, potentially leading to dirty data being included in the query results. Read uncommitted isolation level may result in dirty data being read, impacting data integrity and accuracy of query results.', 'Explicitly specifying the isolation level can control whether dirty data is read, with examples demonstrating the impact of setting the isolation level on query results. Explicitly specifying the isolation level can control whether dirty data is read, as demonstrated through examples of setting the isolation level and observing the query results.', 'The impact of active transactions on the default isolation level, read uncommitted, is highlighted, emphasizing the potential for reading dirty data in the same query window where a transaction is active. The default isolation level, read uncommitted, can lead to reading dirty data in the same query window where a transaction is active, impacting the accuracy of query results.', 'The importance of read committed isolation level in maintaining data integrity, especially in industries like banking, is emphasized due to the need to avoid uncommitted data in critical financial transactions. Read committed isolation level is crucial for maintaining data integrity, particularly in industries like banking, where the avoidance of uncommitted data is vital for financial transaction accuracy.']}], 'duration': 1634.541, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co15919691.jpg', 'highlights': ['Transactions in SQL server are a set of operations that are guaranteed to succeed or fail as a single unit.', 'Transactions are used when multiple rows are inserted, updated, or deleted in a sequence to ensure consistency in the database.', 'Views in SQL only allow users with read permission to perform select operations and restrict insert, update, and delete operations.', 'Transactions are categorized by the ACID properties - Atomicity, Consistency, Isolation, and Durability.', 'Atomicity property ensures that transactions are regarded as a single unit, succeeding or failing together regardless of the number of steps involved.', 'Consistency property in transactions ensures that the database or table remains in a consistent state after the transaction is committed or rolled back.', 'ACID properties (Isolation, Durability) of transactions are discussed, emphasizing the importance of maintaining transaction boundaries and data permanence.', 'Types of transactions (Auto Commit, Explicit, Implicit) are explained, detailing their distinct characteristics and implications.', 'An example of an explicit transaction is provided, demonstrating its impact on data visibility and management, including the concept of dirty read.', 'The read committed isolation level ensures that only committed data is read, waiting for active transactions to complete, thus ensuring data integrity.', 'The read uncommitted isolation level allows reading uncommitted data, potentially leading to dirty data being included in the query results.', 'Explicitly specifying the isolation level can control whether dirty data is read, with examples demonstrating the impact of setting the isolation level on query results.', 'The impact of active transactions on the default isolation level, read uncommitted, is highlighted, emphasizing the potential for reading dirty data in the same query window where a transaction is active.', 'The importance of read committed isolation level in maintaining data integrity, especially in industries like banking, is emphasized due to the need to avoid uncommitted data in critical financial transactions.']}, {'end': 18269.588, 'segs': [{'end': 17679.05, 'src': 'embed', 'start': 17609.347, 'weight': 0, 'content': [{'end': 17610.788, 'text': 'okay, so you can understand it better.', 'start': 17609.347, 'duration': 1.441}, {'end': 17613.509, 'text': "So here, what I'm going to do is I'm going to say begin try.", 'start': 17610.988, 'duration': 2.521}, {'end': 17616.955, 'text': 'And then all our SQL statements goes in here.', 'start': 17613.973, 'duration': 2.982}, {'end': 17624.32, 'text': 'If you want to write updates, deletes, inserts, anything that you want to write, you can write everything inside the try.', 'start': 17617.375, 'duration': 6.945}, {'end': 17628.783, 'text': 'And then once all your statements are done, you just end the try block.', 'start': 17624.72, 'duration': 4.063}, {'end': 17631.905, 'text': 'Okay So all your SQL statements that you want to run goes here.', 'start': 17629.043, 'duration': 2.862}, {'end': 17634.267, 'text': "Now let's say the first one runs fine.", 'start': 17632.225, 'duration': 2.042}, {'end': 17635.067, 'text': 'Second one runs fine.', 'start': 17634.307, 'duration': 0.76}, {'end': 17635.848, 'text': 'Third one runs fine.', 'start': 17635.107, 'duration': 0.741}, {'end': 17637.629, 'text': 'The fourth one has an error.', 'start': 17636.248, 'duration': 1.381}, {'end': 17641.011, 'text': 'That very moment, the catch block is hit.', 'start': 17638.289, 'duration': 2.722}, {'end': 17642.332, 'text': 'You see the begin catch?', 'start': 17641.451, 'duration': 0.881}, {'end': 17649.031, 'text': 'the cursor moves to the cache block and it executes whatever is there in this cache block.', 'start': 17642.97, 'duration': 6.061}, {'end': 17650.992, 'text': 'right?. Let me show you an example.', 'start': 17649.031, 'duration': 1.961}, {'end': 17652.292, 'text': 'Okay, let me comment these.', 'start': 17651.012, 'duration': 1.28}, {'end': 17662.175, 'text': "And what I'm gonna do here is I'm gonna say, let's say select Dinesh, very simple one, as name, that's it.", 'start': 17652.792, 'duration': 9.383}, {'end': 17667.556, 'text': 'So I begin my try, I select something and then end my try.', 'start': 17662.535, 'duration': 5.021}, {'end': 17672.357, 'text': 'And then in the cache, I just print, cache block is hit.', 'start': 17668.316, 'duration': 4.041}, {'end': 17674.188, 'text': 'and then end the catch.', 'start': 17672.867, 'duration': 1.321}, {'end': 17675.268, 'text': 'very simple stuff.', 'start': 17674.188, 'duration': 1.08}, {'end': 17676.849, 'text': 'okay, let me run this.', 'start': 17675.268, 'duration': 1.581}, {'end': 17679.05, 'text': 'so what should be executed?', 'start': 17676.849, 'duration': 2.201}], 'summary': 'Sql statements are wrapped in try-catch blocks to handle errors and execute specific actions.', 'duration': 69.703, 'max_score': 17609.347, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17609347.jpg'}, {'end': 17767.928, 'src': 'embed', 'start': 17736.688, 'weight': 4, 'content': [{'end': 17738.649, 'text': "See now it doesn't throw me any error.", 'start': 17736.688, 'duration': 1.961}, {'end': 17744.431, 'text': "It doesn't give me any error because it went into the catch block and printed this message.", 'start': 17738.729, 'duration': 5.702}, {'end': 17754.534, 'text': 'So it proves the theory right that if there is any error here, the catch block is hit and see I printed the message saying catch block is hit.', 'start': 17744.951, 'duration': 9.583}, {'end': 17760.501, 'text': 'So the catch block is now hit because of this query, because it has error in it.', 'start': 17754.835, 'duration': 5.666}, {'end': 17767.928, 'text': "now let's say that i want to do and i want to catch the error that was thrown from here.", 'start': 17760.501, 'duration': 7.427}], 'summary': 'The catch block is hit, proving theory right about handling errors.', 'duration': 31.24, 'max_score': 17736.688, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17736688.jpg'}], 'start': 17554.612, 'title': 'Handling errors in sql server and error handling with try-catch', 'summary': 'Discusses handling errors in sql server through try-catch blocks, illustrating efficient error handling and execution of sql queries. it also covers the use of try-catch blocks in sql for error handling, including storing error messages in variables, the controlled approach, and integration with transactions for automatic rollback.', 'chapters': [{'end': 17760.501, 'start': 17554.612, 'title': 'Handling errors in sql server', 'summary': 'Explains how to handle errors in sql server using try-catch blocks, demonstrating the process of handling errors and executing sql queries efficiently.', 'duration': 205.889, 'highlights': ['The try-catch block helps in handling errors by placing all the SQL statements in the try block and automatically hitting the catch block if any error occurs. The try-catch block allows for efficient handling of errors by grouping SQL statements within the try block and triggering the catch block upon encountering an error.', 'Demonstration of using try-catch blocks to handle errors by showing an example of a SQL query with a deliberate error and the subsequent execution of the catch block. A demonstration is provided by executing a SQL query with a deliberate error to showcase the functionality of the try-catch block, where the catch block is triggered upon encountering an error.', 'Illustration of how the catch block is executed when an error occurs, preventing the query from failing and allowing the error to be handled effectively. The example illustrates the execution of the catch block when an error occurs, demonstrating the prevention of query failure and effective error handling.']}, {'end': 18269.588, 'start': 17760.501, 'title': 'Error handling with try-catch in sql', 'summary': 'Explains how to use try-catch blocks in sql for error handling, demonstrating the storage of error messages in variables, the controlled approach provided by try-catch, and its integration with transactions for automatic rollback on error.', 'duration': 509.087, 'highlights': ['The try-catch block allows the storage of error messages in variables, providing a more controlled approach to error handling. By creating a variable to store error messages using the try-catch block, users can ensure a more controlled approach to error handling.', 'The try-catch block integrates with transactions to automatically rollback on error, ensuring the all-or-none execution principle of transactions. Integrating try-catch with transactions allows for automatic rollback on error, ensuring the all-or-none execution principle of transactions is maintained.', 'When an error is encountered, the try-catch block directs the control to the catch block, preventing the execution of subsequent queries and enabling selective error message display. Upon encountering an error, the try-catch block directs control to the catch block, preventing the execution of subsequent queries and enabling selective error message display.']}], 'duration': 714.976, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co17554612.jpg', 'highlights': ['The try-catch block allows for efficient handling of errors by grouping SQL statements within the try block and triggering the catch block upon encountering an error.', 'A demonstration is provided by executing a SQL query with a deliberate error to showcase the functionality of the try-catch block, where the catch block is triggered upon encountering an error.', 'The example illustrates the execution of the catch block when an error occurs, demonstrating the prevention of query failure and effective error handling.', 'By creating a variable to store error messages using the try-catch block, users can ensure a more controlled approach to error handling.', 'Integrating try-catch with transactions allows for automatic rollback on error, ensuring the all-or-none execution principle of transactions is maintained.', 'Upon encountering an error, the try-catch block directs control to the catch block, preventing the execution of subsequent queries and enabling selective error message display.']}, {'end': 19577.716, 'segs': [{'end': 18548.289, 'src': 'embed', 'start': 18519.397, 'weight': 0, 'content': [{'end': 18524.639, 'text': 'They are temporary storage that helps us store data in them, okay?', 'start': 18519.397, 'duration': 5.242}, {'end': 18531.102, 'text': 'So the scope of a CTE is different when compared to temp tables, table variables and stuff, okay?', 'start': 18524.999, 'duration': 6.103}, {'end': 18536.404, 'text': "So let me show you first how to create a CTE and then I'll explain you the scope of it.", 'start': 18531.382, 'duration': 5.022}, {'end': 18539.145, 'text': 'That means till when can we access a CTE.', 'start': 18536.444, 'duration': 2.701}, {'end': 18548.289, 'text': 'So to create it we start with a semicolon and then say with the name of the CTE, and then here I specify the columns only, not the data types,', 'start': 18539.485, 'duration': 8.804}], 'summary': 'Cte provides temporary storage for data with specific scope and creation process', 'duration': 28.892, 'max_score': 18519.397, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co18519397.jpg'}, {'end': 19062.241, 'src': 'embed', 'start': 19036.925, 'weight': 1, 'content': [{'end': 19044.351, 'text': 'It is not mandatory that you can only become an SQL developer having a very thorough knowledge of a programming language,', 'start': 19036.925, 'duration': 7.426}, {'end': 19049.954, 'text': 'but having the basics or having the intermediate knowledge of software programming.', 'start': 19044.351, 'duration': 5.603}, {'end': 19052.776, 'text': "so let's say your company uses Java.", 'start': 19049.954, 'duration': 2.822}, {'end': 19059.06, 'text': 'then learning how Java works will obviously help when you build a database for that company.', 'start': 19052.776, 'duration': 6.284}, {'end': 19062.241, 'text': 'so because that database should be compatible with Java.', 'start': 19059.06, 'duration': 3.181}], 'summary': 'Sql developers benefit from basic to intermediate software programming knowledge, such as java.', 'duration': 25.316, 'max_score': 19036.925, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co19036925.jpg'}, {'end': 19232.248, 'src': 'embed', 'start': 19204.759, 'weight': 2, 'content': [{'end': 19207.48, 'text': 'you will be able to administer every single transaction.', 'start': 19204.759, 'duration': 2.721}, {'end': 19209.521, 'text': "So it's basically maintaining.", 'start': 19207.92, 'duration': 1.601}, {'end': 19214.442, 'text': 'And then a database developer is a person who builds and also manipulates databases.', 'start': 19209.861, 'duration': 4.581}, {'end': 19219.884, 'text': 'So you can also become a database developer by having the skills.', 'start': 19214.843, 'duration': 5.041}, {'end': 19229.247, 'text': "So let's say for a database administrator, you should go with more of a view that you'll be able to manage any kinds of problems which are arising.", 'start': 19221.064, 'duration': 8.183}, {'end': 19232.248, 'text': 'You should be able to solve any kinds of problems which are arising.', 'start': 19229.647, 'duration': 2.601}], 'summary': 'Database administrators manage transactions; developers build and manipulate databases. they should handle and solve any arising problems.', 'duration': 27.489, 'max_score': 19204.759, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co19204759.jpg'}], 'start': 18269.588, 'title': 'Sql development and certifications', 'summary': 'Covers sql transaction management, common table expressions, comparison of sql and mysql, sql developer career paths, and the importance of sql developer certifications, including job trends such as an average salary of $72,282 per year in the usa and 6.5 lpa in india.', 'chapters': [{'end': 18492.574, 'start': 18269.588, 'title': 'Sql procedure transaction example', 'summary': 'Explains a sql procedure for transaction management, showcasing the use of try-catch blocks and transaction rollbacks with examples of update queries and their outcomes, ultimately demonstrating the rollback of all changes due to an error in one of the queries.', 'duration': 222.986, 'highlights': ['The procedure includes a try block with begin transaction, update queries, and a commit message, followed by a catch block with a rollback and an error message. The procedure demonstrates the use of try-catch blocks and transaction management, including the execution of update queries and the handling of errors through rollback.', 'The update queries within the procedure result in the first two queries running successfully, but all changes get rolled back due to an error in the third query. The example illustrates the behavior of transactions, where the successful execution of the first two queries is negated by the rollback triggered by an error in the third query.', 'After altering the procedure to fix the error, running the procedure results in a successful commit of the data changes. The demonstration highlights the successful commitment of data changes after rectifying the error in the procedure, showcasing the effectiveness of transaction management in maintaining data integrity.']}, {'end': 18743.573, 'start': 18493.257, 'title': 'Understanding ctes in sql', 'summary': 'Explains the concept of common table expressions (ctes) as temporary storage in sql, demonstrating its creation, scope, and usage limitations, highlighting the difference from temp tables and table variables.', 'duration': 250.316, 'highlights': ['CTEs are temporary storage in SQL, used to store data and have a scope limited to the first select inside the same batch.', "Creating a CTE involves using a semicolon, specifying the CTE name and its columns, and then selecting the data to be stored in the CTE from the employee table, with a specific condition like 'Indian employees' using a where clause.", 'The scope of a CTE is restricted to the first select inside the same batch, similar to table variables, but CTEs can only be used once, and they are created in memory for faster processing of a small amount of data.', 'CTEs are meant for storing and using a very small amount of data only once, and they are not stored in the temp database, making them faster due to being stored in memory.']}, {'end': 19080.252, 'start': 18743.573, 'title': 'Comparison of sql and mysql', 'summary': 'Discusses the difference between sql and mysql, highlighting key points such as their usage, purpose, complexity, updates, and skills required for a sql developer, emphasizing the significance and efficiency of sql in applications and the ease of data storage and manipulation in mysql.', 'duration': 336.679, 'highlights': ['SQL is a very fine language, which is put together to work with a database. Emphasizes the refined nature of SQL as a language tailored to work with databases.', "MySQL brings about a software which makes the best possible way to store structured data and work with it, making the developers' or the coders' life a lot easier. Underlines the ease of data storage and manipulation provided by MySQL, enhancing the developers' efficiency.", 'SQL is extremely efficient and is vital in the world of IT applications. Stresses the efficiency and significance of SQL in the realm of IT applications.', 'MySQL provides frequent updates to cater to various customer bases, solving bugs and making the software more efficient. Highlights the frequent updates by MySQL to improve efficiency and customer satisfaction.', 'Skills required for a SQL developer include data management, software programming, and server development and maintenance. Specifies the key skills needed for a SQL developer, including data management, programming, and server maintenance.']}, {'end': 19302.85, 'start': 19080.252, 'title': 'Sql development career path', 'summary': 'Discusses the essential skills and career paths for sql developers, emphasizing the importance of learning sql, software programming, and process enhancement, and highlighting the diverse job profiles such as database administrator, database developer, data scientist, etl developer, and bi developer and administrator.', 'duration': 222.598, 'highlights': ['SQL is the fundamental skill for an SQL developer, enabling them to manage data and understand software programming, thus improving their job prospects. Learning SQL is crucial for an SQL developer as it allows them to manage data, understand software programming, and enhance their job prospects.', "Process enhancement is vital for SQL developers to make better business decisions and potentially change existing processes for the company's benefit. SQL developers can contribute to process enhancement, potentially altering existing processes for the company's benefit.", 'Database administrators are responsible for maintaining and administering database usage, while database developers focus on building and manipulating databases. Database administrators maintain and administer database usage, while database developers focus on building and manipulating databases.', 'SQL is essential for data scientists, ETL developers, and BI developers and administrators, as it enables them to perform tasks crucial for their respective roles. SQL is crucial for data scientists, ETL developers, and BI developers and administrators, enabling them to perform essential tasks for their roles.']}, {'end': 19577.716, 'start': 19302.85, 'title': 'Sql developer certifications and job trends', 'summary': 'Explores the importance of sql developer certifications, highlighting the benefits of certifications such as value addition to the resume, substantial increase in pay, and meeting corporate requirements. it also delves into popular sql developer certifications and provides insights into job trends, including an average salary of $72,282 per year in the usa and 6.5 lpa in india for sql developers.', 'duration': 274.866, 'highlights': ['SQL developer average salary is $72,282 per year in the USA and 6.5 LPA in India. The average salary for SQL developers in the USA is $72,282 per year, and in India, it is around 6.5 LPA.', 'Benefits of SQL developer certifications include value addition to the resume, substantial increase in pay, and meeting corporate requirements. SQL developer certifications offer benefits such as adding value to the resume, substantial pay raise, and meeting corporate requirements.', 'Popular SQL developer certifications include IBM Certified Database Administrator DB2, Microsoft SQL Server Database Certification, and Oracle certifications including Oracle Certified Professional, MySQL Database Administrator, and Oracle Database 12c Administrator. Popular SQL developer certifications include IBM Certified Database Administrator DB2, Microsoft SQL Server Database Certification, Oracle Certified Professional, MySQL Database Administrator, and Oracle Database 12c Administrator.']}], 'duration': 1308.128, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co18269588.jpg', 'highlights': ['SQL developer average salary is $72,282 per year in the USA and 6.5 LPA in India.', 'CTEs are meant for storing and using a very small amount of data only once, and they are not stored in the temp database, making them faster due to being stored in memory.', 'The procedure includes a try block with begin transaction, update queries, and a commit message, followed by a catch block with a rollback and an error message.', 'Learning SQL is crucial for an SQL developer as it allows them to manage data, understand software programming, and enhance their job prospects.', 'Popular SQL developer certifications include IBM Certified Database Administrator DB2, Microsoft SQL Server Database Certification, Oracle Certified Professional, MySQL Database Administrator, and Oracle Database 12c Administrator.']}, {'end': 20284.4, 'segs': [{'end': 19609.481, 'src': 'embed', 'start': 19582.098, 'weight': 3, 'content': [{'end': 19585.34, 'text': 'So what exactly is SQL? So this is a very basic question.', 'start': 19582.098, 'duration': 3.242}, {'end': 19590.003, 'text': 'So to answer this, SQL stands for the Structured Query Language.', 'start': 19585.561, 'duration': 4.442}, {'end': 19595.867, 'text': 'And according to ANSI, it is a standard query language for relational database management systems.', 'start': 19590.524, 'duration': 5.343}, {'end': 19604.398, 'text': 'which is used for maintaining the relational database and also to perform different operations of data manipulation on different types of data.', 'start': 19596.274, 'duration': 8.124}, {'end': 19609.481, 'text': 'So SQL is widely used language for maintaining relational database,', 'start': 19604.999, 'duration': 4.482}], 'summary': 'Sql is a widely used language for maintaining relational databases.', 'duration': 27.383, 'max_score': 19582.098, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co19582098.jpg'}, {'end': 20037.764, 'src': 'embed', 'start': 20005.337, 'weight': 0, 'content': [{'end': 20010.64, 'text': 'so insert into stu2 Values.', 'start': 20005.337, 'duration': 5.303}, {'end': 20014.977, 'text': "I'll add in the first record so 1, which would be the student ID.", 'start': 20010.64, 'duration': 4.337}, {'end': 20021.099, 'text': 'name of the student is Julia.', 'start': 20014.977, 'duration': 6.122}, {'end': 20022.619, 'text': 'now let me look at this table.', 'start': 20021.099, 'duration': 1.52}, {'end': 20027.821, 'text': 'select star from stu2 right.', 'start': 20022.619, 'duration': 5.202}, {'end': 20030.822, 'text': 'so we have successfully inserted this record.', 'start': 20027.821, 'duration': 3.001}, {'end': 20032.823, 'text': 'now let me add a new record.', 'start': 20030.822, 'duration': 2.001}, {'end': 20037.764, 'text': 'so insert into stu2 values.', 'start': 20032.823, 'duration': 4.941}], 'summary': 'Successfully inserted first record with student id 1 and name julia into stu2 table.', 'duration': 32.427, 'max_score': 20005.337, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co20005337.jpg'}, {'end': 20192.061, 'src': 'embed', 'start': 20163.795, 'weight': 2, 'content': [{'end': 20173.661, 'text': "now let me just type in the command select and since you want the maximum value, I'll use the max aggregate function, and this would be e salary.", 'start': 20163.795, 'duration': 9.866}, {'end': 20179.024, 'text': 'so I want the maximum salary from the name of the table is employee.', 'start': 20173.661, 'duration': 5.363}, {'end': 20190.079, 'text': "I'll given the where clause and given the condition where e salary is not in, after this I will give in the sub query over here.", 'start': 20179.024, 'duration': 11.055}, {'end': 20192.061, 'text': 'so the sub query again would be the same.', 'start': 20190.079, 'duration': 1.982}], 'summary': 'Using sql, selecting max salary from employee table with a subquery.', 'duration': 28.266, 'max_score': 20163.795, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co20163795.jpg'}], 'start': 19582.098, 'title': 'Sql basics and constraints', 'summary': 'Covers an overview of sql, its widespread usage, and the four categories of sql commands. it also explains default and unique constraints with examples and demonstrates finding the second highest salary using subquery, and understanding normalization and denormalization.', 'chapters': [{'end': 19718.14, 'start': 19582.098, 'title': 'Understanding sql basics', 'summary': 'Provides an overview of sql, including its definition, widespread usage, and the four categories of sql commands - data query language, data definition language, data manipulation language, and data control language.', 'duration': 136.042, 'highlights': ['SQL stands for the Structured Query Language and became the standard of ANSI in 1986, widely used for maintaining relational databases. It is used for creation, deletion of databases, and fetching/modifying rows from the table.', 'The four categories of SQL commands are data query language, data definition language, data manipulation language, and data control language, each serving specific purposes in managing databases.', "The data query language comprises the 'select' command, data definition language includes 'create table', 'alt table', and 'drop table', data manipulation language involves 'insert', 'update', and 'delete' commands, and data control language includes 'grant' and 'revoke' commands."]}, {'end': 20284.4, 'start': 19718.28, 'title': 'Constraints: default and unique', 'summary': 'Explains the default and unique constraints in sql, with examples of setting default values and ensuring uniqueness, and demonstrates finding the second highest salary using subquery and understanding normalization and denormalization.', 'duration': 566.12, 'highlights': ['Setting default value using default constraint The default constraint in SQL is used to provide a default value for a column, such as setting the default value to 85,000 for eSalary column, resulting in all entries having the default value if no other is specified.', 'Enforcing uniqueness using unique constraint The unique constraint ensures that all values in a column are different, such as having unique employee names in the ename column, preventing duplicate entries.', 'Finding the second highest salary using subquery The process of finding the second highest salary involves using a subquery with the max aggregate function and a where clause to filter out the maximum salary, resulting in the second highest salary of 125,000.', 'Understanding normalization in databases Normalization is the process of reducing data redundancy and dependency by organizing fields and tables of the database to remove redundancy and inconsistent dependency, making it more flexible.']}], 'duration': 702.302, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co19582098.jpg', 'highlights': ['SQL became the standard of ANSI in 1986, widely used for maintaining relational databases.', 'The four categories of SQL commands are data query language, data definition language, data manipulation language, and data control language.', 'The default constraint in SQL is used to provide a default value for a column, such as setting the default value to 85,000 for eSalary column.', 'Enforcing uniqueness using unique constraint ensures that all values in a column are different.', 'Finding the second highest salary using subquery involves using a subquery with the max aggregate function and a where clause to filter out the maximum salary.', 'Normalization is the process of reducing data redundancy and dependency by organizing fields and tables of the database to remove redundancy and inconsistent dependency.']}, {'end': 22051.09, 'segs': [{'end': 20452.683, 'src': 'embed', 'start': 20423.509, 'weight': 2, 'content': [{'end': 20427.433, 'text': 'we cannot use the where clause and we would have to use the having clause.', 'start': 20423.509, 'duration': 3.924}, {'end': 20433.699, 'text': 'And if you are also using group by with having then group by would come first and having would follow it.', 'start': 20427.934, 'duration': 5.765}, {'end': 20434.74, 'text': 'So next question.', 'start': 20434, 'duration': 0.74}, {'end': 20437.023, 'text': 'So what do you know about these stuff?', 'start': 20435.401, 'duration': 1.622}, {'end': 20444.997, 'text': 'function?. So the stuff function deletes a part of the string and then inserts another part into the string, starting at a specified position.', 'start': 20437.023, 'duration': 7.974}, {'end': 20447.699, 'text': 'So this is the syntax of the stuff function.', 'start': 20445.557, 'duration': 2.142}, {'end': 20452.683, 'text': 'So stuff, this takes in these four parameters, string one, position, length and string two.', 'start': 20447.799, 'duration': 4.884}], 'summary': 'Using having clause instead of where, syntax of stuff function explained with 4 parameters.', 'duration': 29.174, 'max_score': 20423.509, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co20423509.jpg'}, {'end': 20785.452, 'src': 'embed', 'start': 20759.758, 'weight': 3, 'content': [{'end': 20764.601, 'text': "So we'll give in the keywords, create procedure, and then we'll give the procedure name over here.", 'start': 20759.758, 'duration': 4.843}, {'end': 20771.464, 'text': "After that, we will give in the keyword ES, and then we'll give the SQL statement for which we'd want to create the procedure.", 'start': 20765.041, 'duration': 6.423}, {'end': 20774.906, 'text': "And then we'll end the procedure with the go keyword.", 'start': 20771.945, 'duration': 2.961}, {'end': 20777.488, 'text': 'Now, after we create a procedure, we need to execute it.', 'start': 20775.166, 'duration': 2.322}, {'end': 20780.209, 'text': 'So this is the syntax to execute the procedure.', 'start': 20777.888, 'duration': 2.321}, {'end': 20785.452, 'text': "So we'll type in EXEC and then give the name of the procedure, which has to be executed.", 'start': 20780.589, 'duration': 4.863}], 'summary': 'Procedure creation involves using keywords, sql statements, and execution syntax.', 'duration': 25.694, 'max_score': 20759.758, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co20759758.jpg'}, {'end': 20986.977, 'src': 'embed', 'start': 20963.732, 'weight': 0, 'content': [{'end': 20973.358, 'text': 'And the condition over here states that the values from column X of table one should be equal to the values of column Y from table two.', 'start': 20963.732, 'duration': 9.626}, {'end': 20975.079, 'text': 'Let me just read this.', 'start': 20973.898, 'duration': 1.181}, {'end': 20981.063, 'text': 'The values of column X from table one should be equal to the values of column Y from table two.', 'start': 20975.519, 'duration': 5.544}, {'end': 20985.076, 'text': "Now let's go to SQL server and work with this inner join.", 'start': 20981.914, 'duration': 3.162}, {'end': 20986.977, 'text': 'So I have two tables over here.', 'start': 20985.316, 'duration': 1.661}], 'summary': 'Condition: column x (table 1) = column y (table 2). working with inner join in sql server.', 'duration': 23.245, 'max_score': 20963.732, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co20963732.jpg'}, {'end': 21274.392, 'src': 'embed', 'start': 21247.822, 'weight': 1, 'content': [{'end': 21255.427, 'text': "So the only difference which you see when you're creating or working with a temporary table and the normal tables is you would have to proceed the name of the table with a hash.", 'start': 21247.822, 'duration': 7.605}, {'end': 21257.949, 'text': 'So as simple as that rest, everything would be the same.', 'start': 21255.567, 'duration': 2.382}, {'end': 21260.01, 'text': 'And over here, let me give them the values.', 'start': 21258.569, 'duration': 1.441}, {'end': 21263.092, 'text': 'So the book ID is one and the book cost us.', 'start': 21260.17, 'duration': 2.922}, {'end': 21267.695, 'text': 'Right Now let me also add another record.', 'start': 21264.133, 'duration': 3.562}, {'end': 21274.392, 'text': "So the second ID would be two and now the book costs would be, let's say 232.", 'start': 21268.136, 'duration': 6.256}], 'summary': 'Working with temporary tables requires adding a hash before the table name, otherwise similar to normal tables.', 'duration': 26.57, 'max_score': 21247.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co21247822.jpg'}], 'start': 20284.4, 'title': 'Sql and database concepts', 'summary': 'Covers database optimization techniques like normalization and denormalization, sql errors related to aggregate functions and group by, stored procedures and their syntax, various types of sql joins with syntax and practical examples, temporary tables and oltp vs olap comparison, and sql commands and operators including delete, truncate, union, intersect, and insert into select.', 'chapters': [{'end': 20719.643, 'start': 20284.4, 'title': 'Database optimization and sql errors', 'summary': 'Covers database optimization techniques such as normalization and denormalization, sql errors related to aggregate functions and group by, the stuff function for string manipulation, and the concept and examples of views in sql.', 'duration': 435.243, 'highlights': ['Denormalization involves adding redundant data to speed up complex queries, optimizing read performance of a database. Denormalization involves adding redundant data to speed up complex queries, optimizing read performance of a database.', 'SQL error: Aggregate functions cannot be used in the where clause, requiring the use of the having clause instead. SQL error: Aggregate functions cannot be used in the where clause, requiring the use of the having clause instead.', 'Explanation and example of the stuff function for string manipulation in SQL. Explanation and example of the stuff function for string manipulation in SQL.', 'Views in SQL are described as virtual tables that limit the displayed information, with examples of creating views based on specific conditions. Views in SQL are described as virtual tables that limit the displayed information, with examples of creating views based on specific conditions.']}, {'end': 21133.143, 'start': 20720.283, 'title': 'Stored procedure & sql join syntax', 'summary': 'Provides an overview of stored procedures and their syntax, highlighting the advantages of modular programming and faster execution. it also explains various types of sql joins with syntax and practical examples, emphasizing their role in retrieving specific data from multiple tables.', 'duration': 412.86, 'highlights': ['Stored procedures act as a means of modular programming and support faster execution compared to executing multiple queries. Stored procedures offer the advantage of modular programming and faster execution, consolidating several SQL statements into a single procedure.', 'Inner join in SQL returns records with matching values in both tables, demonstrated with a practical example and syntax explanation. Inner join retrieves records with matching values in both tables, exemplified with a practical syntax explanation and demonstration using SQL Server.', 'Explanation of left join, right join, and full join in SQL, with a clear distinction of their functionalities and use cases. The chapter explains left join, right join, and full join in SQL, outlining their distinct functionalities and use cases for retrieving data from multiple tables based on the relationship between them.']}, {'end': 21363.205, 'start': 21133.824, 'title': 'Temporary tables and oltp vs olap', 'summary': 'Explains the concept of temporary tables and their syntax, usage, and benefits, along with a comparison of oltp and olap systems, emphasizing their key attributes and differences.', 'duration': 229.381, 'highlights': ['Temporary tables help store and process intermediate results, created in temp DB and automatically deleted when no longer used, making them useful for storing temporary data.', "Creating a temporary table involves using the keywords 'create table' preceded by a hash symbol for the table name, with the ability to insert and retrieve data similarly to normal tables.", 'OLTP systems support transaction-oriented programs, maintain concurrency, and are designed for a large audience of end users conducting short transactions, with the number of transactions per second being an effective measure for these systems.', 'OLAP systems are characterized by relatively low frequency of online transactions, complex queries involving aggregations, and a reliance on response time, widely used for data mining or maintaining aggregated historical data.']}, {'end': 22051.09, 'start': 21363.885, 'title': 'Sql commands and operators', 'summary': 'Discusses the differences between delete and truncate commands, union and union all operators, the use of intersect operator, and the insert into select operator, providing syntax and examples for each.', 'duration': 687.205, 'highlights': ['Difference between delete and truncate command The delete command is used to delete one or more existing tables, while the truncate command deletes all data inside the table. Delete is a DML command, and truncate is a DDL command.', 'Difference between union and union all operators The union operator combines result sets of select statements, returning only distinct records, while the union all operator returns all records, including duplicates, from both tables.', 'Use of intersect operator The intersect operator combines two select statements and returns only common records. It returns records present in both select statements.', 'Insert into select operator The insert into select operator is used to copy data from one table to another, provided both tables have the same structure. It can be used to insert all or selected records from one table to another.']}], 'duration': 1766.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/OUcPx-uJ5co/pics/OUcPx-uJ5co20284400.jpg', 'highlights': ['Denormalization involves adding redundant data to speed up complex queries, optimizing read performance of a database.', 'Stored procedures offer the advantage of modular programming and faster execution, consolidating several SQL statements into a single procedure.', 'Temporary tables help store and process intermediate results, created in temp DB and automatically deleted when no longer used, making them useful for storing temporary data.', 'Difference between delete and truncate command The delete command is used to delete one or more existing tables, while the truncate command deletes all data inside the table. Delete is a DML command, and truncate is a DDL command.']}], 'highlights': ['The session covers a comprehensive SQL full course by Intellipaat, including topics from basics to advanced level, hands-on demos, and interview preparation, conducted by multiple experts.', 'Emphasizes the importance of data and its application in the world, defining data as facts related to any object, like age being a numeric data having a wide range of values, and the ability to derive meaningful insights from it.', 'The chapter explains the concept of a database as a systematic collection of data, emphasizing the need for storage, maintenance, and manipulation of data.', 'Databases are crucial for managing large amounts of data efficiently, as using spreadsheets becomes extremely difficult when handling thousands and millions of records, impacting efficiency and time.', "SQL is a standard language for dealing with relational databases and can be used to create, read, update, and delete database records. SQL's role in data manipulation and database record management.", 'The Enterprise edition of SQL Server supports databases as large as 524 petabytes and 640 logical processors.', 'SQL Server 2017 release expanded platform support to include Linux, Windows, Ubuntu, and Docker, as well as added support for Python and machine learning within SQL Server.', 'The full recovery model records all transactions in the transaction log, enabling a comprehensive disaster recovery plan and the ability to restore a database to a specific point in time without losing work due to a damaged file.', 'DMVs provide metadata of the SQL Server system state for monitoring performance and diagnosing issues.', 'Covers various aggregate functions in SQL Server, including counting records in a table.', 'The case statement in SQL allows for conditional data retrieval and column derivation based on specified conditions.', 'The chapter introduces the concept of ranking functions in SQL, specifically focusing on row number rank and dense rank, which are the only available ranking functions in SQL Server.', 'Stored procedures reduce the number of queries from 30-40 to a single procedure, allowing for reusability across multiple departments and enabling easier maintenance.', "The growing importance of data is emphasized, with data being compared to the 'new oil' in terms of its significance in the business context and its potential applications, such as machine learning and artificial intelligence.", 'Transactions in SQL server are a set of operations that are guaranteed to succeed or fail as a single unit.', 'The try-catch block allows for efficient handling of errors by grouping SQL statements within the try block and triggering the catch block upon encountering an error.', 'SQL developer average salary is $72,282 per year in the USA and 6.5 LPA in India.', 'SQL became the standard of ANSI in 1986, widely used for maintaining relational databases.', 'The four categories of SQL commands are data query language, data definition language, data manipulation language, and data control language.', 'Denormalization involves adding redundant data to speed up complex queries, optimizing read performance of a database.', 'Stored procedures offer the advantage of modular programming and faster execution, consolidating several SQL statements into a single procedure.', 'Temporary tables help store and process intermediate results, created in temp DB and automatically deleted when no longer used, making them useful for storing temporary data.', 'Difference between delete and truncate command The delete command is used to delete one or more existing tables, while the truncate command deletes all data inside the table. Delete is a DML command, and truncate is a DDL command.']}