title
SQL Tutorial for Beginners

description
In this step-by-step tutorial, learn how you can write your own SQL queries. You don't need any prior knowledge and we're going to use all free tools. By the end of this video, you'll know how to retrieve data from databases. At the end, I'll show you a neat trick to write some of the most complex queries with very little effort. SQL stands for structured query language. It's a language that you can use to talk to your database. You can also use it to retrieve, filter, sort, combine, add, update, and delete data in a database. It's basically a language you can use to interact with the database. The great thing is that anyone can learn how to write SQL queries. You definitely don't need a degree in computer science. And once you know it, you'll have another superpower that you can use at work. 👋 Additional resources - Download & install Microsoft SQL Server: https://www.microsoft.com/en-us/sql-server/sql-server-downloads - Download & install SQL Server Management Studio (SSMS): https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver15 - Sample database to follow along: https://1drv.ms/u/s!AmxrofZZlZ-whNsNYa3M9O0USq1WhQ?e=r06ifZ - Sample data in Excel format including flat file and tables: https://1drv.ms/x/s!AmxrofZZlZ-whNsGOcpx-aZS1RN2yA?e=My4Otp - SQL Functions: https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver15 ⌚ Timestamps 0:00 Introduction 0:37 Why learn SQL? 1:49 What is SQL? 2:25 What is a database? 3:09 Relational database management systems 4:11 Install Microsoft SQL Server 6:51 Install SQL Server Management Studio 7:26 Connect to server 8:13 Object Explorer 9:26 Restore sample database 10:42 Tables 14:33 Primary keys 15:57 Database diagrams 17:57 Data types 18:30 Select 24:50 Filtering with where 32:02 Inner join & outer join 38:21 Order by 39:01 Functions including getdate, sum, count 41:02 Group by 41:20 Bonus: Query designer 44:09 Wrap up 📃 Watch related playlists - Playlist with all my videos on SQL: https://youtube.com/playlist?list=PLlKpQrBME6xLJxrKUNTty3Ne8j_B0QYUT 🚩 Connect with me on social - LinkedIn: https://www.linkedin.com/in/kevinstratvert/ - Discord: https://bit.ly/KevinStratvertDiscord - Twitter: https://twitter.com/kevstrat - Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818 - TikTok: https://www.tiktok.com/@kevinstratvert - Instagram: https://www.instagram.com/kevinstratvert/ 🙏 Request How To Tutorial Videos https://forms.gle/BDrTNUoxheEoMLGt5 🔔 Subscribe to my YouTube channel https://www.youtube.com/user/kevlers?sub_confirmation=1 🎬 Want to watch again? Navigate back to my YouTube channel quickly http://www.kevinstratvert.com 🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz ⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same. #stratvert

detail
{'title': 'SQL Tutorial for Beginners', 'heatmap': [], 'summary': 'This sql tutorial covers sql and rdbms importance for data analysis, installation of microsoft sql server 2019, database normalization, sql fundamentals, writing and filtering queries, joining tables, and data manipulation for analysis, with practical examples and guidance.', 'chapters': [{'end': 310.074, 'segs': [{'end': 84.035, 'src': 'embed', 'start': 52.318, 'weight': 1, 'content': [{'end': 55.639, 'text': 'I used SQL all the time to get insights from data.', 'start': 52.318, 'duration': 3.321}, {'end': 63.621, 'text': 'I worked on the website office.com and I could write a SQL query to tell me how many people clicked on this button,', 'start': 56.399, 'duration': 7.222}, {'end': 65.942, 'text': 'and I could use that data then to decide.', 'start': 63.621, 'duration': 2.321}, {'end': 68.562, 'text': 'well, should we make this button stand out a little bit more?', 'start': 65.942, 'duration': 2.62}, {'end': 70.563, 'text': 'Should we move it to a different location??', 'start': 68.962, 'duration': 1.601}, {'end': 75.667, 'text': 'Without being able to get that data using SQL, I would have had no idea what to do.', 'start': 71.303, 'duration': 4.364}, {'end': 84.035, 'text': 'Your data contains all sorts of hidden insights, and with SQL, you can unlock some of that hidden value.', 'start': 76.868, 'duration': 7.167}], 'summary': 'Used sql to analyze website data, determining button clicks and making informed design decisions.', 'duration': 31.717, 'max_score': 52.318, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg52318.jpg'}, {'end': 122.41, 'src': 'embed', 'start': 96.963, 'weight': 0, 'content': [{'end': 104.625, 'text': "We're not going to focus on how you can create a new database or how you could add new tables or even how you can add or remove data.", 'start': 96.963, 'duration': 7.662}, {'end': 109.146, 'text': "If that's something you want to see in a future video, let me know down below in the comments.", 'start': 105.505, 'duration': 3.641}, {'end': 117.088, 'text': 'What is SQL? Well, SQL stands for Structured Query Language or SQL.', 'start': 110.087, 'duration': 7.001}, {'end': 122.41, 'text': "It's a language that you can use to interact with databases.", 'start': 118.169, 'duration': 4.241}], 'summary': 'Sql is a language for interacting with databases, not focusing on creating or altering data.', 'duration': 25.447, 'max_score': 96.963, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg96963.jpg'}], 'start': 0.429, 'title': 'Sql and rdbms for data analysis', 'summary': 'Covers sql for data reporting, emphasizing its importance for analysis, and understanding databases, focusing on rdbms like microsoft sql server and its popularity and free tools.', 'chapters': [{'end': 145.527, 'start': 0.429, 'title': 'Sql for data reporting', 'summary': 'Discusses how to use sql for data reporting and analysis, emphasizing its importance for retrieving insights from databases and its applicability for various roles, such as business analysts, product managers, and researchers.', 'duration': 145.098, 'highlights': ["SQL is valuable for retrieving insights from data, as demonstrated by the speaker's experience at Microsoft where SQL queries helped in making decisions based on user behaviors, such as button clicks, potentially leading to improvements in website design.", 'SQL is a versatile language that allows for data retrieval, filtering, sorting, and manipulation, and it is accessible to anyone without a computer science degree, offering a valuable skill for professional use.', 'The chapter focuses on retrieving data from databases, catering to roles like business analysts, product managers, and researchers, and emphasizes the potential for unlocking hidden insights within the data.', 'The speaker aims to provide a step-by-step tutorial on using SQL for data reporting and analysis, targeting beginners with no prior SQL background and utilizing free tools for learning.']}, {'end': 310.074, 'start': 146.267, 'title': 'Understanding databases and rdbms', 'summary': 'Explains the concept of databases, their importance in major websites, the use of rdbms such as microsoft sql server, and the differences between the developer and express editions, with microsoft sql server as the chosen rdbms due to its popularity and free tools.', 'duration': 163.807, 'highlights': ['Major websites like Facebook, Twitter, and LinkedIn are powered by databases, highlighting the significance of databases in the digital world.', 'Microsoft SQL Server is chosen as the RDBMS for learning due to its popularity, free tools, and the ease of transitioning to other systems after mastering it.', 'Explanation of the differences between the developer and express editions of Microsoft SQL Server, including the limitations and allowed usage for each edition.', 'A database is a collection of tables with relationships, and the Kevin Cookie Company uses databases to track customers, orders, and product information.']}], 'duration': 309.645, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg429.jpg', 'highlights': ['SQL queries at Microsoft helped in making decisions based on user behaviors, potentially leading to improvements in website design.', 'SQL is a versatile language for data retrieval, filtering, sorting, and manipulation, accessible to anyone without a computer science degree.', 'Chapter focuses on retrieving data from databases, catering to roles like business analysts, product managers, and researchers.', 'The speaker aims to provide a step-by-step tutorial on using SQL for data reporting and analysis, targeting beginners with no prior SQL background.', 'Major websites like Facebook, Twitter, and LinkedIn are powered by databases, highlighting the significance of databases in the digital world.', 'Microsoft SQL Server is chosen as the RDBMS for learning due to its popularity, free tools, and ease of transitioning to other systems.', 'Explanation of the differences between the developer and express editions of Microsoft SQL Server, including the limitations and allowed usage for each edition.', 'A database is a collection of tables with relationships, and the Kevin Cookie Company uses databases to track customers, orders, and product information.']}, {'end': 611.195, 'segs': [{'end': 371.461, 'src': 'embed', 'start': 346.882, 'weight': 0, 'content': [{'end': 354.625, 'text': "Once you finish installing, you won't see any icons on your taskbar or on your desktop, and you might be wondering well, what did I install?", 'start': 346.882, 'duration': 7.743}, {'end': 355.826, 'text': 'and is it even running??', 'start': 354.625, 'duration': 1.201}, {'end': 363.653, 'text': "Let's click on the Start menu and then click on All Apps, and here let's navigate down to M.", 'start': 356.866, 'duration': 6.787}, {'end': 368.798, 'text': "And right here I see that I've now installed Microsoft SQL Server 2019.", 'start': 363.653, 'duration': 5.145}, {'end': 371.461, 'text': 'When I drop this down, we have a few different apps.', 'start': 368.798, 'duration': 2.663}], 'summary': 'After installing, find microsoft sql server 2019 in start menu under m.', 'duration': 24.579, 'max_score': 346.882, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg346882.jpg'}], 'start': 311.192, 'title': 'Installing and setting up sql server 2019', 'summary': 'Explains installing microsoft sql server 2019 developer edition, highlighting installation options, and setting up sql server management studio, including connecting to a server and restoring a database.', 'chapters': [{'end': 410.023, 'start': 311.192, 'title': 'Installing sql server 2019 developer edition', 'summary': 'Explains the process of installing microsoft sql server 2019, highlighting the options during installation, locating the installed software, and verifying the service status, with details on automatic startup and control options.', 'duration': 98.831, 'highlights': ['The process of installing Microsoft SQL Server 2019 involves choosing between basic and custom options, with the ability to download installation media for use on multiple machines. Microsoft SQL Server 2019', "After installation, the presence of Microsoft SQL Server 2019 can be confirmed by navigating to 'All Apps' and locating the installed software under the letter 'M'. Microsoft SQL Server 2019", 'The Configuration Manager allows users to verify the running status of SQL Server, with options to stop, restart, pause, or change the start mode, which is set to automatic by default. SQL Server service status, start mode']}, {'end': 611.195, 'start': 410.503, 'title': 'Setting up sql server management studio', 'summary': 'Covers setting up sql server management studio, including installing, connecting to a server, and restoring a database, with instructions on how to navigate the interface and create a new database.', 'duration': 200.692, 'highlights': ['To use SQL Server Management Studio, download and install it from the provided link, then connect to a server and navigate the object explorer to manage databases.', "The tutorial provides guidance on connecting to multiple servers simultaneously, navigating the interface's main view, and creating a new database by restoring an existing one.", 'Instructions are given on how to download a sample database, navigate to the backup file, and successfully restore the database in SQL Server Management Studio.']}], 'duration': 300.003, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg311192.jpg', 'highlights': ['The process of installing Microsoft SQL Server 2019 involves choosing between basic and custom options, with the ability to download installation media for use on multiple machines.', "After installation, the presence of Microsoft SQL Server 2019 can be confirmed by navigating to 'All Apps' and locating the installed software under the letter 'M'.", 'To use SQL Server Management Studio, download and install it from the provided link, then connect to a server and navigate the object explorer to manage databases.', 'The Configuration Manager allows users to verify the running status of SQL Server, with options to stop, restart, pause, or change the start mode, which is set to automatic by default.']}, {'end': 919.206, 'segs': [{'end': 703.202, 'src': 'embed', 'start': 676.824, 'weight': 4, 'content': [{'end': 682.448, 'text': "So, instead of putting all of that customer's information here, instead I could say oh, customer five,", 'start': 676.824, 'duration': 5.624}, {'end': 689.053, 'text': "and then I could jump to another table with all of the details on customer five, and here's all of their contact information.", 'start': 682.448, 'duration': 6.605}, {'end': 692.355, 'text': 'This is the core idea behind databases.', 'start': 689.873, 'duration': 2.482}, {'end': 696.638, 'text': 'We can use various IDs to connect together these tables,', 'start': 692.735, 'duration': 3.903}, {'end': 703.202, 'text': "and that's why it's referred to as a relational database because we have relations between these different tables.", 'start': 696.638, 'duration': 6.564}], 'summary': 'Using ids to connect tables in a relational database for efficient data management.', 'duration': 26.378, 'max_score': 676.824, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg676824.jpg'}, {'end': 763.615, 'src': 'embed', 'start': 737.814, 'weight': 0, 'content': [{'end': 746.121, 'text': "Back within SQL Server Management Studio, here you'll see our four different tables, and each table name starts with dbo.", 'start': 737.814, 'duration': 8.307}, {'end': 754.607, 'text': 'This beginning portion is the default schema name, and the schema is simply a way that you can bucket together different tables.', 'start': 746.901, 'duration': 7.706}, {'end': 758.491, 'text': 'You can also use it to provide access to certain tables,', 'start': 755.148, 'duration': 3.343}, {'end': 763.615, 'text': 'so certain users might have access to one schema and other users might have access to a different schema.', 'start': 758.491, 'duration': 5.124}], 'summary': 'Four tables in sql server management studio, each starting with dbo, used for organizing and providing access to tables for different users.', 'duration': 25.801, 'max_score': 737.814, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg737814.jpg'}], 'start': 611.735, 'title': 'Database organization and sql tables', 'summary': 'Introduces database normalization, explaining its benefits in space efficiency and data retrieval. it also covers the process of breaking up a database into separate tables to minimize data repetition. additionally, it explains sql server table structure, primary key concept, and the importance of establishing relationships between tables.', 'chapters': [{'end': 736.743, 'start': 611.735, 'title': 'Database normalization: benefits and process', 'summary': 'Introduces the concept of database normalization, explaining its benefits in terms of space efficiency and data retrieval, and the process of breaking up a database into separate tables to minimize data repetition and improve data organization.', 'duration': 125.008, 'highlights': ['The core idea behind databases is to break up information into separate tables to avoid repetition, as seen in the example of using separate tables for customer information and orders, resulting in less space usage and easier data retrieval.', 'The database contains four different tables titled customers, order product, orders, and product, demonstrating the process of breaking up a database for efficient data organization and retrieval.', 'The concept of normalization in database design is explained, emphasizing the importance of minimizing data repetition to save space and facilitate data retrieval, which is achieved by separating data into different tables.', 'The speaker highlights the benefits of database normalization, such as taking up less space and making it easier to retrieve data, attributing these advantages to breaking up data into separate tables and using IDs to connect them.', 'The process of database normalization is referred to as the breaking up of data into separate tables to reduce repetition, as shown in the example of having separate tables for customers, orders, and products, resulting in better data organization and space efficiency.']}, {'end': 919.206, 'start': 737.814, 'title': 'Understanding sql server tables and relationships', 'summary': 'Explains the default schema, table structure, and primary key concept within sql server management studio, emphasizing the importance of organizing data and establishing relationships between tables.', 'duration': 181.392, 'highlights': ['The primary key is the minimum number of columns needed to uniquely identify a record, emphasizing the importance of organizing data and establishing relationships between tables Explains the concept of a primary key and its significance in uniquely identifying records within a table, highlighting the importance of organizing data and establishing relationships between tables.', 'Explanation of default schema and its use for organizing and providing access to tables Describes the default schema within SQL Server Management Studio, illustrating its purpose in organizing and enabling access to tables for different users.', 'Demonstration of table structure and use of separate columns for organizing data to facilitate querying Demonstrates the table structure and the rationale behind using separate columns for organizing data to facilitate querying and accessing information within the table.']}], 'duration': 307.471, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg611735.jpg', 'highlights': ['The concept of normalization in database design is explained, emphasizing the importance of minimizing data repetition to save space and facilitate data retrieval, which is achieved by separating data into different tables.', 'The primary key is the minimum number of columns needed to uniquely identify a record, emphasizing the importance of organizing data and establishing relationships between tables.', 'The database contains four different tables titled customers, order product, orders, and product, demonstrating the process of breaking up a database for efficient data organization and retrieval.', 'The core idea behind databases is to break up information into separate tables to avoid repetition, as seen in the example of using separate tables for customer information and orders, resulting in less space usage and easier data retrieval.', 'Demonstration of table structure and use of separate columns for organizing data to facilitate querying.']}, {'end': 1497.173, 'segs': [{'end': 1096.559, 'src': 'embed', 'start': 1050.749, 'weight': 0, 'content': [{'end': 1052.871, 'text': 'it helps you understand how they relate.', 'start': 1050.749, 'duration': 2.122}, {'end': 1059.096, 'text': 'You can also visualize how a table is organized over on the left-hand side in the object explorer.', 'start': 1053.812, 'duration': 5.284}, {'end': 1061.978, 'text': 'Here, for example, I have my orders table.', 'start': 1059.736, 'duration': 2.242}, {'end': 1066.721, 'text': 'I can click on this plus icon and here I can click on the plus next to columns,', 'start': 1062.218, 'duration': 4.503}, {'end': 1071.045, 'text': 'and here I can see all of the different columns that are contained within this table.', 'start': 1066.721, 'duration': 4.324}, {'end': 1075.188, 'text': 'And here I see a visual indication of what the primary key is.', 'start': 1071.625, 'duration': 3.563}, {'end': 1077.509, 'text': 'Here the primary key is the order ID.', 'start': 1075.548, 'duration': 1.961}, {'end': 1081.411, 'text': 'I can also review all of the different data types.', 'start': 1078.95, 'duration': 2.461}, {'end': 1086.014, 'text': "Here I'll right click on orders, and here I can select design.", 'start': 1081.572, 'duration': 4.442}, {'end': 1093.077, 'text': 'Within the design view here I can see all of the different column names, and I could also see the associated data type.', 'start': 1086.654, 'duration': 6.423}, {'end': 1096.559, 'text': 'Now this will be important especially as we start querying.', 'start': 1093.517, 'duration': 3.042}], 'summary': 'Learn to visualize table organization and primary key in sql. review data types in design view.', 'duration': 45.81, 'max_score': 1050.749, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg1050749.jpg'}, {'end': 1169.023, 'src': 'embed', 'start': 1139.306, 'weight': 6, 'content': [{'end': 1144.368, 'text': "When we write our query in this window, it's going to execute against this database.", 'start': 1139.306, 'duration': 5.062}, {'end': 1150.791, 'text': 'If you have multiple databases, here you can choose which database you want the query to execute against.', 'start': 1145.108, 'duration': 5.683}, {'end': 1157.153, 'text': "Also, to help us with writing this query, we're going to write a query against the customers table.", 'start': 1151.851, 'duration': 5.302}, {'end': 1163.356, 'text': "To help us with this, let's click on the plus icon, and this way we can see all the different column names.", 'start': 1157.834, 'duration': 5.522}, {'end': 1169.023, 'text': 'For the first query, I simply want to see a list of all of our customers.', 'start': 1164.44, 'duration': 4.583}], 'summary': 'Executing queries against specific databases and tables, accessing column names and retrieving a list of customers.', 'duration': 29.717, 'max_score': 1139.306, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg1139306.jpg'}, {'end': 1342.117, 'src': 'embed', 'start': 1293.376, 'weight': 1, 'content': [{'end': 1302.42, 'text': "Now when I try to execute this query, I get an error message because in the master database, there's no table called dbo.customers.", 'start': 1293.376, 'duration': 9.044}, {'end': 1304.181, 'text': 'So my query fails.', 'start': 1302.94, 'duration': 1.241}, {'end': 1307.022, 'text': 'If you want to prevent this from happening.', 'start': 1305.201, 'duration': 1.821}, {'end': 1313.385, 'text': 'instead of just specifying the table that you want to get this data from, you can also specify the database.', 'start': 1307.022, 'duration': 6.363}, {'end': 1316.93, 'text': 'Now, remember that this database is called KCC.', 'start': 1314.145, 'duration': 2.785}, {'end': 1322.44, 'text': 'So before the table name, I can type in kcc.dbo.customers.', 'start': 1317.331, 'duration': 5.109}, {'end': 1326.908, 'text': 'So now not only does it specify the table name, but also the database name.', 'start': 1322.881, 'duration': 4.027}, {'end': 1333.809, 'text': 'Now, when I try to execute this query, I get the same results that I got before, even though right up here,', 'start': 1327.702, 'duration': 6.107}, {'end': 1336.812, 'text': 'the master database is currently the active one.', 'start': 1333.809, 'duration': 3.003}, {'end': 1342.117, 'text': "Within the results, you'll see that the customer name column doesn't have a space.", 'start': 1337.312, 'duration': 4.805}], 'summary': 'Specifying database name with table name prevents query failure. example: kcc.dbo.customers', 'duration': 48.741, 'max_score': 1293.376, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg1293376.jpg'}], 'start': 919.667, 'title': 'Database fundamentals', 'summary': 'Provides foundational knowledge on database management, covering primary keys, database diagrams, and data types, and offers practical guidance on writing sql queries, including selecting columns, using aliases, filtering distinct values, and employing wildcard symbols.', 'chapters': [{'end': 1117.196, 'start': 919.667, 'title': 'Understanding database diagrams', 'summary': 'Explains the concept of primary keys, their importance in uniquely identifying rows, the visualization of table connections through database diagrams, and the significance of data types in querying, providing a foundational understanding of database management.', 'duration': 197.529, 'highlights': ['The primary key is the minimum number of columns to uniquely identify a row. Explains the definition and significance of a primary key in uniquely identifying a row within a table.', 'Visualizing table connections through database diagrams provides a clear understanding of how tables are connected. Emphasizes the importance of visualizing table connections through database diagrams for a comprehensive understanding of database structure and relationships.', 'Understanding data types is crucial for effective querying, as filtering or comparing data based on incorrect data types may lead to errors. Stresses the importance of understanding data types for accurate querying and the potential errors that can occur if data types are not considered.']}, {'end': 1497.173, 'start': 1117.976, 'title': 'Writing sql queries', 'summary': 'Explains how to write sql queries to retrieve data from a table, including selecting specific columns, using aliases, filtering distinct values, using wildcard symbol, and getting a sample of the data.', 'duration': 379.197, 'highlights': ['Writing the first query to retrieve all customer names from the customers table The chapter begins with writing a query to retrieve all customer names from the customers table, using the SELECT statement and specifying the table name, resulting in the successful retrieval of all customer names.', 'Selecting specific columns and using aliases to customize column headers The explanation includes selecting specific columns, such as customer names and notes, and using aliases to customize the column headers, showcasing the process of customizing the column header for better visibility.', 'Retrieving distinct values and using the wildcard symbol to retrieve all columns It covers the process of retrieving distinct or unique customer names by using the DISTINCT keyword and using the wildcard symbol to retrieve all columns from the table, demonstrating the retrieval of distinct values and all columns efficiently.', "Filtering data by using the 'top' keyword to retrieve a sample of the data The chapter explains how to use the 'top' keyword to retrieve a specified number of records, showcasing the process of filtering data to retrieve a sample of the records from the table."]}], 'duration': 577.506, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg919667.jpg', 'highlights': ['Understanding data types is crucial for effective querying, as filtering or comparing data based on incorrect data types may lead to errors.', 'Visualizing table connections through database diagrams provides a clear understanding of how tables are connected.', 'The primary key is the minimum number of columns to uniquely identify a row.', 'Writing the first query to retrieve all customer names from the customers table using the SELECT statement and specifying the table name.', 'Selecting specific columns and using aliases to customize column headers.', 'Retrieving distinct values and using the wildcard symbol to retrieve all columns.', "Filtering data by using the 'top' keyword to retrieve a sample of the data."]}, {'end': 1795.082, 'segs': [{'end': 1612.003, 'src': 'embed', 'start': 1520.662, 'weight': 0, 'content': [{'end': 1526.607, 'text': "So here I'll type in state equals Washington, and that's all I need to enter in.", 'start': 1520.662, 'duration': 5.945}, {'end': 1532.371, 'text': "Now when I press F5, we only get back this one customer that's based out of Seattle, Washington.", 'start': 1526.847, 'duration': 5.524}, {'end': 1537.001, 'text': 'To make this query easier to read, here I can insert spacing.', 'start': 1533.137, 'duration': 3.864}, {'end': 1540.163, 'text': 'I can also press enter and I can add new lines.', 'start': 1537.661, 'duration': 2.502}, {'end': 1543.787, 'text': 'And when I run this query, I get the exact same result.', 'start': 1540.704, 'duration': 3.083}, {'end': 1552.274, 'text': "So as you're pulling together your queries, feel free to insert spaces or character returns to make it easier for you to follow along.", 'start': 1544.427, 'duration': 7.847}, {'end': 1559.336, 'text': 'Another thing that will help you follow along with your code and also help other people with your code is entering comments.', 'start': 1553.035, 'duration': 6.301}, {'end': 1563.837, 'text': 'Here, for example, I can enter two dashes and I can write a comment.', 'start': 1559.936, 'duration': 3.901}, {'end': 1567.938, 'text': 'Here I typed in this returns all customers in Washington state.', 'start': 1564.857, 'duration': 3.081}, {'end': 1571.639, 'text': 'Alternatively, instead of entering the dash dash,', 'start': 1568.938, 'duration': 2.701}, {'end': 1580.2, 'text': 'I could also enter a slash and the asterisk and at the end I can enter the asterisk and a slash again, and this will also create a comment.', 'start': 1571.639, 'duration': 8.561}, {'end': 1582.301, 'text': 'So you have those two different options.', 'start': 1580.34, 'duration': 1.961}, {'end': 1590.047, 'text': "In this example, I'm looking at just customers located in Washington State, but instead of saying equal to Washington State,", 'start': 1582.961, 'duration': 7.086}, {'end': 1592.389, 'text': 'I could also say not equal to Washington State.', 'start': 1590.047, 'duration': 2.342}, {'end': 1595.772, 'text': 'So show me all the customers who are not located here.', 'start': 1592.709, 'duration': 3.063}, {'end': 1602.197, 'text': 'When I press F5 to run, here I see the five other customers who are not based out of Washington State.', 'start': 1596.452, 'duration': 5.745}, {'end': 1606.72, 'text': 'and here I could also enter the exclamation mark and the equal sign.', 'start': 1603.218, 'duration': 3.502}, {'end': 1612.003, 'text': 'That also means not equal to and when I press F5 once again I get the same exact result.', 'start': 1606.78, 'duration': 5.223}], 'summary': 'Querying customer data in washington state and using comments for clarity and readability.', 'duration': 91.341, 'max_score': 1520.662, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg1520662.jpg'}], 'start': 1497.554, 'title': 'Writing and filtering sql queries', 'summary': "Explains writing sql queries with comments and filters, including the use of 'where' clause and logical operators like not, or, and and to improve code efficiency and readability. it also demonstrates filtering customer data by state and specific conditions, with examples of filtered results and their impact on query execution.", 'chapters': [{'end': 1590.047, 'start': 1497.554, 'title': 'Writing sql queries with comments and filters', 'summary': "Explains how to write sql queries to filter customer data by state, demonstrating the use of 'where' clause and adding comments to improve code readability and understanding.", 'duration': 92.493, 'highlights': ["The chapter demonstrates using the 'where' clause to filter customer data by state, specifically showing an example of querying customers in the state of Washington, resulting in retrieving a single customer based in Seattle.", 'The speaker emphasizes the importance of formatting queries by inserting spacing and new lines to enhance readability and understanding of the code.', 'The importance of adding comments to SQL queries is highlighted, with the speaker showing two different options for creating comments, including using double dashes or slash and asterisk symbols.']}, {'end': 1795.082, 'start': 1590.047, 'title': 'Sql filtering and logical operators', 'summary': 'Demonstrates using logical operators such as not, or, and and in sql queries to filter data based on specific conditions, improving code efficiency and clarity while highlighting examples of filtered results and their impact on query execution.', 'duration': 205.035, 'highlights': ['Using logical operators such as NOT, OR, and AND in SQL queries to filter data based on specific conditions Demonstrates how to use NOT, OR, and AND operators in SQL queries to filter data based on specific conditions, showcasing the flexibility of filtering options in SQL.', 'Demonstrating the impact of filtered results on query execution Provides examples of filtered results and their impact on query execution, illustrating the practical implications of applying filtering conditions in SQL queries.', 'Improving code efficiency and clarity through the use of logical operators Illustrates how the use of logical operators such as NOT, OR, and AND can improve code efficiency and clarity in SQL queries, emphasizing the importance of optimizing query structure for readability and performance.']}], 'duration': 297.528, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg1497554.jpg', 'highlights': ['Using logical operators such as NOT, OR, and AND in SQL queries to filter data based on specific conditions Demonstrates how to use NOT, OR, and AND operators in SQL queries to filter data based on specific conditions, showcasing the flexibility of filtering options in SQL.', "The chapter demonstrates using the 'where' clause to filter customer data by state, specifically showing an example of querying customers in the state of Washington, resulting in retrieving a single customer based in Seattle.", 'Demonstrating the impact of filtered results on query execution Provides examples of filtered results and their impact on query execution, illustrating the practical implications of applying filtering conditions in SQL queries.', 'The importance of adding comments to SQL queries is highlighted, with the speaker showing two different options for creating comments, including using double dashes or slash and asterisk symbols.', 'The speaker emphasizes the importance of formatting queries by inserting spacing and new lines to enhance readability and understanding of the code.', 'Improving code efficiency and clarity through the use of logical operators Illustrates how the use of logical operators such as NOT, OR, and AND can improve code efficiency and clarity in SQL queries, emphasizing the importance of optimizing query structure for readability and performance.']}, {'end': 2303.901, 'segs': [{'end': 2169.111, 'src': 'embed', 'start': 2121.503, 'weight': 0, 'content': [{'end': 2125.265, 'text': 'And right down here where I say dbo.customers here I can insert a c.', 'start': 2121.503, 'duration': 3.762}, {'end': 2132.083, 'text': 'When I press F5 to run, I get the exact same result, but my code is a little bit easier to read now.', 'start': 2126.601, 'duration': 5.482}, {'end': 2141.227, 'text': 'When we ran this query, we did something called an inner join, and in fact I can go back to this join statement and I can type in inner join,', 'start': 2132.604, 'duration': 8.623}, {'end': 2143.989, 'text': 'and here, when I run it, we get the exact same result.', 'start': 2141.227, 'duration': 2.762}, {'end': 2148.911, 'text': 'In fact, when you just enter join, that by default runs an inner join.', 'start': 2144.609, 'duration': 4.302}, {'end': 2151.272, 'text': 'So what is an inner join??', 'start': 2149.451, 'duration': 1.821}, {'end': 2161.848, 'text': 'Well, an inner join gives me all the customers back who have an order, and it also gives me back all orders that have a customer associated with it.', 'start': 2152.264, 'duration': 9.584}, {'end': 2166.59, 'text': "So, let's say there were, let's say, a customer that doesn't have any orders.", 'start': 2162.508, 'duration': 4.082}, {'end': 2169.111, 'text': 'That would not show up in this list.', 'start': 2167.15, 'duration': 1.961}], 'summary': 'Demonstrating inner join query for customers and orders, making code easier to read.', 'duration': 47.608, 'max_score': 2121.503, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg2121503.jpg'}, {'end': 2247.939, 'src': 'embed', 'start': 2219.72, 'weight': 2, 'content': [{'end': 2222.302, 'text': 'So here for the order value down below, it says null.', 'start': 2219.72, 'duration': 2.582}, {'end': 2227.046, 'text': "Now let's say I want to see all orders whether or not there's a customer.", 'start': 2223.183, 'duration': 3.863}, {'end': 2235.013, 'text': 'Well, orders is on the left side over here, so instead of saying right outer join, I want to do a left outer join instead.', 'start': 2227.246, 'duration': 7.767}, {'end': 2240.357, 'text': 'And now when I run this query, it gives me the same result as the inner join.', 'start': 2235.813, 'duration': 4.544}, {'end': 2245.158, 'text': 'but the only reason why is every order happens to also have a customer.', 'start': 2240.757, 'duration': 4.401}, {'end': 2247.939, 'text': 'but if there were, say, an order without a customer,', 'start': 2245.158, 'duration': 2.781}], 'summary': 'Query resulted in same output for left outer join and inner join due to all orders having a customer.', 'duration': 28.219, 'max_score': 2219.72, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg2219720.jpg'}, {'end': 2303.901, 'src': 'embed', 'start': 2258.901, 'weight': 1, 'content': [{'end': 2265.563, 'text': "So right up here in the select portion of the query, I'll insert a comma and here let's type in customer ID.", 'start': 2258.901, 'duration': 6.662}, {'end': 2273.349, 'text': "When I run the query though, I get an error message, and it tells me that it's an ambiguous column name.", 'start': 2266.943, 'duration': 6.406}, {'end': 2278.773, 'text': 'And the reason why is here in the customer table, I have a customer ID.', 'start': 2274.149, 'duration': 4.624}, {'end': 2283.077, 'text': "And also down below in the orders table, there's also a customer ID.", 'start': 2279.314, 'duration': 3.763}, {'end': 2286.58, 'text': "So it doesn't know which customer ID it should use.", 'start': 2283.638, 'duration': 2.942}, {'end': 2288.442, 'text': "Well, that's simple to solve.", 'start': 2287.241, 'duration': 1.201}, {'end': 2291.445, 'text': 'I could simply tell it which table I want to use.', 'start': 2288.542, 'duration': 2.903}, {'end': 2294.376, 'text': "And let's just pull it from the customers table.", 'start': 2292.415, 'duration': 1.961}, {'end': 2296.377, 'text': 'Here the alias for that is C.', 'start': 2294.516, 'duration': 1.861}, {'end': 2299.278, 'text': 'So I can enter a C, dot, and then customer ID.', 'start': 2296.377, 'duration': 2.901}, {'end': 2302, 'text': 'And now when I run it, once again, it works.', 'start': 2299.759, 'duration': 2.241}, {'end': 2303.901, 'text': 'And here I have the customer ID.', 'start': 2302.06, 'duration': 1.841}], 'summary': 'Resolving ambiguous column name by specifying table - customer id issue fixed', 'duration': 45, 'max_score': 2258.901, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg2258901.jpg'}], 'start': 1796.022, 'title': 'Sql queries and joining tables', 'summary': "Demonstrates filtering data using 'like', 'not like', comparison operators, and joining tables to retrieve specific columns from multiple tables in sql. it also discusses joining tables using inner and outer joins to retrieve data, using aliases to improve readability, and resolving ambiguous column names by specifying the table to be used, ultimately resulting in a successful data retrieval process.", 'chapters': [{'end': 2014.269, 'start': 1796.022, 'title': 'Sql queries: filtering, comparison, and joining', 'summary': "Demonstrates filtering data using 'like', 'not like', comparison operators, and joining tables to retrieve specific columns from multiple tables in sql.", 'duration': 218.247, 'highlights': ["Demonstrates filtering data using 'like' and 'not like' to retrieve specific patterns from the database, showcasing how to find customers whose names begin with a specific letter. Showcases filtering customers whose names begin with a specific letter using 'like' and finding customers not beginning with a specific letter using 'not like'.", 'Illustrates the use of comparison operators such as greater than, less than, greater than or equal to, less than or equal to, and between to filter numerical values from the database, including examples of retrieving orders exceeding $1000 and orders between specific ranges. Demonstrates filtering orders based on numerical values using comparison operators like greater than, less than, greater than or equal to, less than or equal to, and between.', "Explains the process of joining tables to retrieve specific columns from multiple tables, emphasizing the need to combine data from different tables and demonstrating the selection of specific columns from the 'orders' table. Explains the process of joining tables to retrieve specific columns from multiple tables and demonstrates the selection of specific columns from the 'orders' table."]}, {'end': 2303.901, 'start': 2015.15, 'title': 'Joining tables and using aliases', 'summary': 'Discusses joining tables using inner and outer joins to retrieve data, using aliases to improve readability, and resolving ambiguous column names by specifying the table to be used, ultimately resulting in a successful data retrieval process.', 'duration': 288.751, 'highlights': ['The chapter explains the process of joining tables using inner and outer joins to retrieve data, showcasing the distinction between inner, right outer, and left outer joins, with a practical example demonstrating the use of right outer join to display all customers irrespective of whether they have an order, and the use of left outer join to retrieve all orders, even if they do not have a customer associated with them.', "It demonstrates the use of aliases to improve code readability, with the example of using 'o' as an alias for 'dbo.orders' and 'c' as an alias for 'dbo.customers', resulting in more concise and comprehensible code.", "The chapter addresses the issue of ambiguous column names by specifying the table to be used, resolving the error and successfully retrieving the customer ID from the 'dbo.customers' table."]}], 'duration': 507.879, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg1796022.jpg', 'highlights': ["Demonstrates filtering data using 'like' and 'not like' to retrieve specific patterns from the database, showcasing how to find customers whose names begin with a specific letter.", 'Illustrates the use of comparison operators such as greater than, less than, greater than or equal to, less than or equal to, and between to filter numerical values from the database, including examples of retrieving orders exceeding $1000 and orders between specific ranges.', "Explains the process of joining tables to retrieve specific columns from multiple tables, emphasizing the need to combine data from different tables and demonstrating the selection of specific columns from the 'orders' table.", 'The chapter explains the process of joining tables using inner and outer joins to retrieve data, showcasing the distinction between inner, right outer, and left outer joins, with a practical example demonstrating the use of right outer join to display all customers irrespective of whether they have an order, and the use of left outer join to retrieve all orders, even if they do not have a customer associated with them.', "It demonstrates the use of aliases to improve code readability, with the example of using 'o' as an alias for 'dbo.orders' and 'c' as an alias for 'dbo.customers', resulting in more concise and comprehensible code.", "The chapter addresses the issue of ambiguous column names by specifying the table to be used, resolving the error and successfully retrieving the customer ID from the 'dbo.customers' table."]}, {'end': 2679.418, 'segs': [{'end': 2372.508, 'src': 'embed', 'start': 2325.773, 'weight': 0, 'content': [{'end': 2328.755, 'text': "And here you see that it's in ascending order.", 'start': 2325.773, 'duration': 2.982}, {'end': 2333.598, 'text': 'So here I start with 39 and then we finish off at 3518.', 'start': 2328.915, 'duration': 4.683}, {'end': 2336.34, 'text': 'Instead, I could also look at it in descending order.', 'start': 2333.598, 'duration': 2.742}, {'end': 2343.345, 'text': "Here I'll type in descending, execute, and here you see that we start large and then it goes down to the smallest value.", 'start': 2336.44, 'duration': 6.905}, {'end': 2351.31, 'text': 'SQL also gives you access to a number of different functions, so you could do things like get the current date,', 'start': 2344.065, 'duration': 7.245}, {'end': 2353.991, 'text': 'or you could sum up values or you could get a count.', 'start': 2351.31, 'duration': 2.681}, {'end': 2358.274, 'text': 'Those are just a few examples of the functions that you have access to.', 'start': 2354.511, 'duration': 3.763}, {'end': 2362.997, 'text': 'Here in this example, I want to see all of the orders from the past month.', 'start': 2359.014, 'duration': 3.983}, {'end': 2372.508, 'text': "So here I'm selecting all orders from the orders table and I'm looking at where the order date is greater than or equal to a date that's one month in the past.", 'start': 2363.797, 'duration': 8.711}], 'summary': 'Sql allows ascending and descending order, functions for date, sum, count. example: selecting orders from the past month.', 'duration': 46.735, 'max_score': 2325.773, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg2325773.jpg'}, {'end': 2569.54, 'src': 'embed', 'start': 2542.005, 'weight': 1, 'content': [{'end': 2546.871, 'text': "Here I simply click on customer name, and here I can click on let's say the order ID.", 'start': 2542.005, 'duration': 4.866}, {'end': 2549.234, 'text': 'Maybe I want the date and also the order total.', 'start': 2547.351, 'duration': 1.883}, {'end': 2554.888, 'text': 'Here I see all those different fields or columns populate in this grid down below.', 'start': 2550.404, 'duration': 4.484}, {'end': 2558.951, 'text': 'And here you see that it automatically writes the SQL statement for me.', 'start': 2555.568, 'duration': 3.383}, {'end': 2561.834, 'text': 'And it joins these two tables together.', 'start': 2559.652, 'duration': 2.182}, {'end': 2569.54, 'text': 'When I click on OK, here I can now run the query and here I get all the customer names with the order ID, the order date, and the order total.', 'start': 2562.615, 'duration': 6.925}], 'summary': 'Using a tool to execute a query, fetching customer names, order ids, dates, and order totals.', 'duration': 27.535, 'max_score': 2542.005, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg2542005.jpg'}], 'start': 2304.561, 'title': 'Sql data manipulation and query building', 'summary': 'Covers ordering and manipulating data in sql including functions like sum and count, and demonstrates building complex sql queries, aggregating data for insights, and applying filters and group by for analysis.', 'chapters': [{'end': 2414.509, 'start': 2304.561, 'title': 'Ordering and manipulating data in sql', 'summary': 'Explains how to order data by a specific column, use different functions like sum and count, and manipulate dates using functions like date add and get date in sql.', 'duration': 109.948, 'highlights': ['The chapter demonstrates ordering data in SQL by the order total column, showing examples of querying in both ascending and descending order.', 'It explains the availability of different functions in SQL, such as getting the current date, summing up values, and obtaining a count.', 'The chapter illustrates using functions like date add to manipulate dates in SQL, providing an example of adjusting the query to retrieve orders from the past month.']}, {'end': 2679.418, 'start': 2415.698, 'title': 'Sql query building and analysis', 'summary': 'Demonstrates the ease of building complex sql queries, including aggregating data to provide insights such as counting orders and summing revenue, utilizing query designer for simplified query building, and applying filters and group by to further analyze data.', 'duration': 263.72, 'highlights': ['The chapter demonstrates aggregating data to provide insights, such as counting orders and summing revenue, with 16 orders in the last month and over $17,000 of revenue, showcasing the practical application of SQL for data analysis.', 'The use of query designer is highlighted, illustrating how it simplifies query building by visualizing tables and columns, automatically generating SQL statements, and joining tables, showcasing the efficiency and ease of building complex queries.', 'The application of filters and group by is explained, demonstrating how to filter data based on specific conditions, add aliases for columns, and perform group by operations to obtain insights, showcasing the advanced capabilities of SQL for data analysis.']}], 'duration': 374.857, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h0nxCDiD-zg/pics/h0nxCDiD-zg2304561.jpg', 'highlights': ['The chapter demonstrates aggregating data to provide insights, such as counting orders and summing revenue, with 16 orders in the last month and over $17,000 of revenue, showcasing the practical application of SQL for data analysis.', 'The use of query designer is highlighted, illustrating how it simplifies query building by visualizing tables and columns, automatically generating SQL statements, and joining tables, showcasing the efficiency and ease of building complex queries.', 'The application of filters and group by is explained, demonstrating how to filter data based on specific conditions, add aliases for columns, and perform group by operations to obtain insights, showcasing the advanced capabilities of SQL for data analysis.', 'The chapter illustrates using functions like date add to manipulate dates in SQL, providing an example of adjusting the query to retrieve orders from the past month.', 'The chapter demonstrates ordering data in SQL by the order total column, showing examples of querying in both ascending and descending order.', 'It explains the availability of different functions in SQL, such as getting the current date, summing up values, and obtaining a count.']}], 'highlights': ['SQL is a versatile language for data retrieval, filtering, sorting, and manipulation, accessible to anyone without a computer science degree.', 'Major websites like Facebook, Twitter, and LinkedIn are powered by databases, highlighting the significance of databases in the digital world.', 'Microsoft SQL Server is chosen as the RDBMS for learning due to its popularity, free tools, and ease of transitioning to other systems.', 'The concept of normalization in database design is explained, emphasizing the importance of minimizing data repetition to save space and facilitate data retrieval, which is achieved by separating data into different tables.', 'Understanding data types is crucial for effective querying, as filtering or comparing data based on incorrect data types may lead to errors.', 'Using logical operators such as NOT, OR, and AND in SQL queries to filter data based on specific conditions Demonstrates how to use NOT, OR, and AND operators in SQL queries to filter data based on specific conditions, showcasing the flexibility of filtering options in SQL.', "Demonstrates filtering data using 'like' and 'not like' to retrieve specific patterns from the database, showcasing how to find customers whose names begin with a specific letter.", 'Illustrates the use of comparison operators such as greater than, less than, greater than or equal to, less than or equal to, and between to filter numerical values from the database, including examples of retrieving orders exceeding $1000 and orders between specific ranges.', 'The chapter demonstrates aggregating data to provide insights, such as counting orders and summing revenue, with 16 orders in the last month and over $17,000 of revenue, showcasing the practical application of SQL for data analysis.', 'The use of query designer is highlighted, illustrating how it simplifies query building by visualizing tables and columns, automatically generating SQL statements, and joining tables, showcasing the efficiency and ease of building complex queries.']}