title
Select statement in sql server - Part 10

description
In this video we will learn 1. Select specific or all columns 2. Distinct rows 3. Filtering with where clause. 4. Wild Cards in SQL Server 5. Joining multiple conditions using AND and OR operators 6. Sorting rows using order by 7. Selecting top n or top n percentage of rows Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/select-statement-part-10.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-10-all-about-select.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Select statement in sql server - Part 10', 'heatmap': [{'end': 66.272, 'start': 36.329, 'weight': 0.986}, {'end': 1132.066, 'start': 1114.956, 'weight': 0.783}], 'summary': 'Tutorial series on sql server covers everything about the select statement, filtering data using where class, multiple filtering techniques, and email address validation resulting in a 4:1 ratio of valid to invalid emails, along with usage of sql operators and conditions for efficient querying.', 'chapters': [{'end': 258.091, 'segs': [{'end': 69.694, 'src': 'heatmap', 'start': 24.34, 'weight': 0, 'content': [{'end': 28.463, 'text': 'When we execute that query, we get all the results, all the columns and all the rows.', 'start': 24.34, 'duration': 4.123}, {'end': 36.029, 'text': 'Now, another way to select all the columns from a table, instead of specifying the star here, you can specify the column list.', 'start': 28.863, 'duration': 7.166}, {'end': 42.254, 'text': "And if you're lazy typing in all the column names, you can ask SQL Server to write the query for you automatically.", 'start': 36.329, 'duration': 5.925}, {'end': 52.125, 'text': 'So go into the database, go into that table, Right click on the table, script table as, select to, new query editor window.', 'start': 42.654, 'duration': 9.471}, {'end': 55.787, 'text': 'So what it is going to do for us, it will write the query for us.', 'start': 52.565, 'duration': 3.222}, {'end': 56.447, 'text': 'Look at this.', 'start': 56.067, 'duration': 0.38}, {'end': 60.95, 'text': 'These are all the columns I want from this table.', 'start': 56.487, 'duration': 4.463}, {'end': 66.272, 'text': 'But then if you look at the table name here, in the from class, we have a three-part table name.', 'start': 61.29, 'duration': 4.982}, {'end': 69.694, 'text': 'The first one, sample, is nothing but the name of the database.', 'start': 67.013, 'duration': 2.681}], 'summary': 'Sql query can be executed to get all columns and rows; can ask sql server to write query automatically.', 'duration': 45.354, 'max_score': 24.34, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_824340.jpg'}, {'end': 148.942, 'src': 'embed', 'start': 100.384, 'weight': 1, 'content': [{'end': 107.63, 'text': "But on the other hand, If I don't have the fully qualified name, if I just have the table name and if I try to execute this query,", 'start': 100.384, 'duration': 7.246}, {'end': 112.576, 'text': 'obviously it will try to find the table in the master database and we get an error.', 'start': 107.63, 'duration': 4.946}, {'end': 117.289, 'text': 'So the advantage of having a fully qualified Name of the table is that.', 'start': 113.037, 'duration': 4.252}, {'end': 119.47, 'text': 'You can run it from any database context.', 'start': 117.349, 'duration': 2.121}, {'end': 127.273, 'text': 'So obviously, to select all the columns from a table, you can either use star or you can specify the list of columns.', 'start': 120.51, 'duration': 6.763}, {'end': 137.257, 'text': "For performance reasons, it's always better to specify the entire column list rather than just saying star.", 'start': 128.013, 'duration': 9.244}, {'end': 140.719, 'text': "So that's the first thing.", 'start': 139.799, 'duration': 0.92}, {'end': 144.261, 'text': 'selecting specific columns are all columns.', 'start': 142, 'duration': 2.261}, {'end': 148.942, 'text': "Now how to select distinct rows? Let's understand what we mean by that.", 'start': 144.721, 'duration': 4.221}], 'summary': 'Use fully qualified table names for flexibility and specify all columns for better performance.', 'duration': 48.558, 'max_score': 100.384, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8100384.jpg'}, {'end': 258.091, 'src': 'embed', 'start': 232.679, 'weight': 2, 'content': [{'end': 238.744, 'text': "you're actually telling SQL Server the value should be distinct across these two columns.", 'start': 232.679, 'duration': 6.065}, {'end': 242.848, 'text': 'So if you take these two columns, they will be distinct.', 'start': 238.784, 'duration': 4.064}, {'end': 246.51, 'text': 'So when you use distinct keyword across more than one column,', 'start': 243.428, 'duration': 3.082}, {'end': 253.396, 'text': "you're specifying it's enough for the values to be distinct across all those columns when you put them together.", 'start': 246.51, 'duration': 6.886}, {'end': 258.091, 'text': "So that's selecting distinct records using the distinct keyword.", 'start': 254.69, 'duration': 3.401}], 'summary': "Using sql server's distinct keyword to select unique records across multiple columns.", 'duration': 25.412, 'max_score': 232.679, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8232679.jpg'}], 'start': 0.009, 'title': 'Sql server select statement and querying database tables', 'summary': 'Covers everything about the select statement in sql server, including querying specific columns or all columns, and the advantages of using fully qualified table names in database queries for successful execution from any context, preventing errors and ensuring flexibility.', 'chapters': [{'end': 42.254, 'start': 0.009, 'title': 'Sql server select statement', 'summary': 'Covers everything about the select statement in sql server, including how to select specific columns or all columns, with a demonstration of querying all columns and rows from a table.', 'duration': 42.245, 'highlights': ["When executing 'select star from tblPerson' query, all the results, all the columns and all the rows are retrieved.", 'An alternative way to select all the columns from a table is by specifying the column list, and SQL Server can generate the query automatically if needed.']}, {'end': 119.47, 'start': 42.654, 'title': 'Querying database tables', 'summary': 'Explains the advantage of using fully qualified table names in database queries, allowing for successful execution from any database context, preventing errors and ensuring flexibility.', 'duration': 76.816, 'highlights': ['The advantage of using fully qualified table names is that it allows for successful execution from any database context, preventing errors and ensuring flexibility.', 'Fully qualified table names consist of three parts: the database name, the schema, and the table name.', 'When using fully qualified table names, the context of the database selected does not impact the successful execution of the query.']}, {'end': 258.091, 'start': 120.51, 'title': 'Sql: selecting columns and distinct records', 'summary': "Covers the importance of specifying column lists for performance reasons, using the 'distinct' keyword to retrieve unique values, and clarifies how 'distinct' works across multiple columns, illustrated with examples from a tbl person table.", 'duration': 137.581, 'highlights': ['When selecting columns from a table, specifying the entire column list is better for performance than using the star symbol.', "Using the 'distinct' keyword allows retrieving unique values from a specific column, reducing duplicates and returning a distinct set of values.", "When using the 'distinct' keyword across multiple columns, SQL Server ensures the values are distinct across all specified columns when combined, affecting the resulting dataset."]}], 'duration': 258.082, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_89.jpg', 'highlights': ['An alternative way to select all the columns from a table is by specifying the column list, and SQL Server can generate the query automatically if needed.', 'The advantage of using fully qualified table names is that it allows for successful execution from any database context, preventing errors and ensuring flexibility.', "Using the 'distinct' keyword allows retrieving unique values from a specific column, reducing duplicates and returning a distinct set of values.", 'When selecting columns from a table, specifying the entire column list is better for performance than using the star symbol.']}, {'end': 392.57, 'segs': [{'end': 290.436, 'src': 'embed', 'start': 258.551, 'weight': 0, 'content': [{'end': 260.351, 'text': 'Filtering the values with the WHERE class.', 'start': 258.551, 'duration': 1.8}, {'end': 264.853, 'text': 'Now, when I say SELECT star from tblPerson, I get all the records from the table.', 'start': 260.432, 'duration': 4.421}, {'end': 269.994, 'text': "Now, let's say I want only the people who are living in London.", 'start': 265.253, 'duration': 4.741}, {'end': 273.015, 'text': 'How do I filter them? You use the WHERE class.', 'start': 270.034, 'duration': 2.981}, {'end': 277.596, 'text': 'So you use the WHERE class, and you specify your condition in the WHERE class.', 'start': 273.715, 'duration': 3.881}, {'end': 284.158, 'text': "So what's your condition in the WHERE class? I want all the people who are residing in the city of London.", 'start': 277.896, 'duration': 6.262}, {'end': 286.593, 'text': 'So London.', 'start': 285.993, 'duration': 0.6}, {'end': 290.436, 'text': "So what's going to happen, this expression will return a true or a false.", 'start': 287.034, 'duration': 3.402}], 'summary': 'Using the where class to filter records based on a specific condition such as residing in london.', 'duration': 31.885, 'max_score': 258.551, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8258551.jpg'}, {'end': 373.244, 'src': 'embed', 'start': 327.143, 'weight': 1, 'content': [{'end': 331.704, 'text': "By the way, that's the not equal to operator in SQL Server, greater than and less than symbol.", 'start': 327.143, 'duration': 4.561}, {'end': 333.905, 'text': 'So not equal to London.', 'start': 332.684, 'duration': 1.221}, {'end': 339.736, 'text': 'When I press F5, I get Every person, you know, who are not living in the city of London.', 'start': 334.245, 'duration': 5.491}, {'end': 346.218, 'text': 'Okay And you can use another operator that is, you know, exclamation and equal to.', 'start': 340.156, 'duration': 6.062}, {'end': 348.879, 'text': 'Even this is considered as not equal to.', 'start': 346.358, 'duration': 2.521}, {'end': 353.7, 'text': 'So you can either use greater than or less than or exclamation equal to.', 'start': 349.399, 'duration': 4.301}, {'end': 356.061, 'text': 'All right.', 'start': 355.741, 'duration': 0.32}, {'end': 363.302, 'text': 'So now, when you use the WHERE class to filter the rows, there are several operators that you can use with the WHERE class.', 'start': 356.86, 'duration': 6.442}, {'end': 366.562, 'text': 'We have just seen how to use equal to and not equal to.', 'start': 363.342, 'duration': 3.22}, {'end': 369.503, 'text': "Let's see what other operators are available for us.", 'start': 366.862, 'duration': 2.641}, {'end': 373.244, 'text': 'So these are the variety of operators that are available.', 'start': 370.583, 'duration': 2.661}], 'summary': 'Using sql server, operators like not equal, greater than, and less than can be used to filter rows, providing a wide variety of options.', 'duration': 46.101, 'max_score': 327.143, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8327143.jpg'}], 'start': 258.551, 'title': 'Using where class to filter data', 'summary': 'Explains the usage of the where class in sql to filter records based on specified conditions, such as filtering people residing in a specific city, employing not equal to operator to retrieve records of people not living in a particular city, and discussing various operators available for filtering data.', 'chapters': [{'end': 392.57, 'start': 258.551, 'title': 'Using where class to filter data', 'summary': 'Explains the usage of the where class in sql to filter records based on specified conditions, such as filtering people residing in a specific city, employing not equal to operator to retrieve records of people not living in a particular city, and discussing various operators available for filtering data.', 'duration': 134.019, 'highlights': ['The WHERE class is used to filter records based on specified conditions, such as filtering people residing in a specific city. The WHERE class is employed to filter records in SQL based on specified conditions, such as filtering people residing in a specific city like London.', 'Employing not equal to operator to retrieve records of people not living in a particular city. The not equal to operator is used to retrieve records of people not living in a particular city, allowing for efficient data filtering.', 'Discussing various operators available for filtering data, such as greater than, greater than equal to, less than, less than equal to, and in operator. The chapter discusses various operators available for filtering data, including greater than, greater than equal to, less than, less than equal to, and the in operator, providing a comprehensive understanding of data filtering in SQL.']}], 'duration': 134.019, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8258551.jpg', 'highlights': ['The WHERE class is employed to filter records in SQL based on specified conditions, such as filtering people residing in a specific city like London.', 'The chapter discusses various operators available for filtering data, including greater than, greater than equal to, less than, less than equal to, and the in operator, providing a comprehensive understanding of data filtering in SQL.', 'The not equal to operator is used to retrieve records of people not living in a particular city, allowing for efficient data filtering.']}, {'end': 579.758, 'segs': [{'end': 419.546, 'src': 'embed', 'start': 393.411, 'weight': 0, 'content': [{'end': 401.213, 'text': 'Now, if we look at all the people that we have in our database, there is an age column, and there are several people with different ages.', 'start': 393.411, 'duration': 7.802}, {'end': 408.86, 'text': 'Now, let us say I want to select all the people whose age is 20, 23, and 25.', 'start': 401.554, 'duration': 7.306}, {'end': 412.762, 'text': 'So how do I do that? You want to specify multiple filters.', 'start': 408.86, 'duration': 3.902}, {'end': 419.546, 'text': 'So you can say where age is equal to 20 or age is equal to 23 or age is equal to 25.', 'start': 412.862, 'duration': 6.684}], 'summary': 'Database contains people with different ages. to select people aged 20, 23, and 25, use multiple filters.', 'duration': 26.135, 'max_score': 393.411, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8393411.jpg'}, {'end': 476.866, 'src': 'embed', 'start': 447.252, 'weight': 1, 'content': [{'end': 451.854, 'text': 'Instead of doing this, you can actually rewrite this query using the in operator.', 'start': 447.252, 'duration': 4.602}, {'end': 456.477, 'text': 'select star from tblPerson, where age in.', 'start': 451.854, 'duration': 4.623}, {'end': 458.277, 'text': 'you can just say 20, 23,, 29..', 'start': 456.477, 'duration': 1.8}, {'end': 463.6, 'text': 'And this is much simpler and easier to understand as well.', 'start': 458.277, 'duration': 5.323}, {'end': 467.561, 'text': 'So I want all the people whose age is within that list.', 'start': 463.88, 'duration': 3.681}, {'end': 472.364, 'text': 'So if you want to specify a list of values, then you use the IN operator.', 'start': 467.682, 'duration': 4.682}, {'end': 476.866, 'text': 'Now, you can also use the BETWEEN operator.', 'start': 473.704, 'duration': 3.162}], 'summary': 'Using the in operator simplifies query, making it easier to understand and specify a list of values.', 'duration': 29.614, 'max_score': 447.252, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8447252.jpg'}, {'end': 559.267, 'src': 'embed', 'start': 520.251, 'weight': 2, 'content': [{'end': 526.837, 'text': 'Now, the interesting and very useful operator is the LIKE operator, which can be used to specify a pattern.', 'start': 520.251, 'duration': 6.586}, {'end': 529.399, 'text': "Now, let's see how to use the LIKE operator.", 'start': 527.297, 'duration': 2.102}, {'end': 534.839, 'text': "Let's select all the rows that are present in tblPerson.", 'start': 532.338, 'duration': 2.501}, {'end': 542.842, 'text': "Now let's say I want all the people whose city name starts with L.", 'start': 535.159, 'duration': 7.683}, {'end': 559.267, 'text': 'So how do I do that? Select star from tblPerson where city is equal to I want all the people who resides in a city that starts with letter L.', 'start': 542.842, 'duration': 16.425}], 'summary': "Using like operator to select rows with city starting with 'l'.", 'duration': 39.016, 'max_score': 520.251, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8520251.jpg'}], 'start': 393.411, 'title': 'Sql filtering operations', 'summary': 'Covers sql filtering techniques including multiple filters, in and between operators, and the usage of the like operator. it demonstrates the use of concatenated conditions and pattern matching, making queries more efficient.', 'chapters': [{'end': 446.472, 'start': 393.411, 'title': 'Sql multiple filters', 'summary': "Discusses selecting people from a database using multiple filters based on age, demonstrating the use of concatenated conditions using the 'or' operator.", 'duration': 53.061, 'highlights': ["The process of selecting people from a database using multiple filters based on age is explained, emphasizing the use of concatenated conditions using the 'or' operator.", 'The example of selecting people with ages 20, 23, and 25 is provided to illustrate the use of multiple filters in SQL.', "The speaker emphasizes the need to avoid specifying multiple conditions and instead demonstrates the use of concatenated conditions using the 'or' operator."]}, {'end': 519.751, 'start': 447.252, 'title': 'Using in and between operators in sql', 'summary': 'Explains the usage of the in and between operators in sql, demonstrating how to specify a list of values using the in operator and how to define a range of values using the between operator, making queries simpler and more efficient.', 'duration': 72.499, 'highlights': ['Using the IN operator to specify a list of values The IN operator simplifies queries by allowing the selection of records with specific values, such as ages 20, 23, and 29, making it easier to understand and execute.', 'Utilizing the BETWEEN operator to define a range of values The BETWEEN operator is used to specify a range of values, like ages between 20 and 25, providing inclusive boundary conditions and making it efficient to retrieve relevant records.']}, {'end': 579.758, 'start': 520.251, 'title': 'Using the like operator in sql', 'summary': "Demonstrates the usage of the like operator to specify a pattern in sql, selecting all rows in tblperson where the city name starts with 'l' and explaining the pattern matching using the like operator.", 'duration': 59.507, 'highlights': ['The LIKE operator in SQL can be used to specify a pattern, allowing for pattern matching within the data.', "Using the LIKE operator, one can select all the rows in tblPerson where the city name starts with 'L', demonstrating the application of the pattern matching.", "The pattern for selecting the city name starting with 'L' using the LIKE operator is 'L%' where '%' denotes any character following 'L'."]}], 'duration': 186.347, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8393411.jpg', 'highlights': ["The process of selecting people from a database using multiple filters based on age is explained, emphasizing the use of concatenated conditions using the 'or' operator.", 'Using the IN operator to specify a list of values simplifies queries by allowing the selection of records with specific values, such as ages 20, 23, and 29, making it easier to understand and execute.', 'The LIKE operator in SQL can be used to specify a pattern, allowing for pattern matching within the data.']}, {'end': 908.879, 'segs': [{'end': 698.934, 'src': 'embed', 'start': 600.486, 'weight': 0, 'content': [{'end': 605.488, 'text': "If there is an at symbol in their email address, you can at least say, OK, it's the right format of the email.", 'start': 600.486, 'duration': 5.002}, {'end': 610.37, 'text': 'So I want to find out all the email addresses which has got an at symbol within that.', 'start': 605.928, 'duration': 4.442}, {'end': 614.391, 'text': 'How do I do that? I can say at symbol there.', 'start': 610.71, 'duration': 3.681}, {'end': 617.231, 'text': 'But there should be something in front of it.', 'start': 615.188, 'duration': 2.043}, {'end': 620.154, 'text': 'Any character in front of an at symbol.', 'start': 618.432, 'duration': 1.722}, {'end': 622.798, 'text': 'Any character after the at symbol.', 'start': 620.595, 'duration': 2.203}, {'end': 627.263, 'text': 'But there should be an at symbol in the email column.', 'start': 623.158, 'duration': 4.105}, {'end': 629.086, 'text': 'So where email like.', 'start': 627.624, 'duration': 1.462}, {'end': 631.669, 'text': 'So when we press, you know, total we have five rows.', 'start': 629.566, 'duration': 2.103}, {'end': 638.513, 'text': "within the table, when I execute this query, I'm only getting four rows, which means there is one invalid email.", 'start': 632.145, 'duration': 6.368}, {'end': 641.036, 'text': "Now let's find out what is that invalid email.", 'start': 638.853, 'duration': 2.183}, {'end': 643.118, 'text': 'You can use the not operator.', 'start': 641.316, 'duration': 1.802}, {'end': 647.844, 'text': 'Give me all the rows which not like this pattern.', 'start': 644.059, 'duration': 3.785}, {'end': 654.659, 'text': "So when I press F5, so Mary didn't provide a valid email address.", 'start': 649.434, 'duration': 5.225}, {'end': 656.2, 'text': 'She is missing the at symbol.', 'start': 654.839, 'duration': 1.361}, {'end': 658.842, 'text': 'We know that every valid email should have that at symbol.', 'start': 656.24, 'duration': 2.602}, {'end': 665.888, 'text': 'So basically you can use the like operator for specifying a pattern.', 'start': 659.263, 'duration': 6.625}, {'end': 668.711, 'text': 'So we use the like operator to specify a pattern.', 'start': 666.289, 'duration': 2.422}, {'end': 672.352, 'text': 'And with the like operator, we use something called wildcards.', 'start': 669.011, 'duration': 3.341}, {'end': 675.053, 'text': 'So here, the percentage is a wildcard.', 'start': 672.632, 'duration': 2.421}, {'end': 678.073, 'text': 'We are not specifying what exactly is the character.', 'start': 675.073, 'duration': 3}, {'end': 682.334, 'text': "It's just like a placeholder or a substitute for some characters.", 'start': 678.233, 'duration': 4.101}, {'end': 684.175, 'text': 'It could be zero or more characters.', 'start': 682.394, 'duration': 1.781}, {'end': 689.836, 'text': 'So there are several wildcards in SQL Server that we can use.', 'start': 685.955, 'duration': 3.881}, {'end': 695.838, 'text': 'One of them is the percentage, which can be used as a replacement or a substitute for zero or more characters.', 'start': 690.277, 'duration': 5.561}, {'end': 698.934, 'text': 'And along the same lines, we have an underscore.', 'start': 696.633, 'duration': 2.301}], 'summary': 'Identifying valid email addresses using wildcards and sql queries, resulting in one invalid email.', 'duration': 98.448, 'max_score': 600.486, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8600486.jpg'}, {'end': 783.77, 'src': 'embed', 'start': 753.717, 'weight': 5, 'content': [{'end': 755.958, 'text': 'so dot com.', 'start': 753.717, 'duration': 2.241}, {'end': 764.241, 'text': "so I want something like this okay, so you're using the underscore to specify there should be only one character before and after the add symbol.", 'start': 755.958, 'duration': 8.283}, {'end': 770.939, 'text': 'now, if I execute this, you should see we get all the email addresses.', 'start': 764.241, 'duration': 6.698}, {'end': 774.022, 'text': 'Okay, now if you look at this, we got the negative results.', 'start': 771.219, 'duration': 2.803}, {'end': 777.084, 'text': "That's because we used the NOT operator there.", 'start': 774.082, 'duration': 3.002}, {'end': 783.77, 'text': 'So when I press F5, we get those email addresses which has exactly one character before and after the at symbol.', 'start': 777.525, 'duration': 6.245}], 'summary': 'Demonstrating the use of underscore to specify one character before and after the @ symbol, resulting in retrieval of email addresses with exactly one character before and after the @ symbol.', 'duration': 30.053, 'max_score': 753.717, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8753717.jpg'}, {'end': 849.497, 'src': 'embed', 'start': 822.645, 'weight': 6, 'content': [{'end': 835.611, 'text': 'select star from tblPerson, where name like you, specify those square brackets and within the square brackets you can specify.', 'start': 822.645, 'duration': 12.966}, {'end': 837.211, 'text': 'and obviously you need to have single quote.', 'start': 835.611, 'duration': 1.6}, {'end': 846.696, 'text': 'by the way, keep in mind, anytime you are using a character or a text data type, okay, that has to be specified within single quotes.', 'start': 837.211, 'duration': 9.485}, {'end': 849.497, 'text': "if it's numeric values, then you don't have to specify.", 'start': 846.696, 'duration': 2.801}], 'summary': 'Sql query: use single quotes for text data type, not for numeric values.', 'duration': 26.852, 'max_score': 822.645, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8822645.jpg'}], 'start': 579.758, 'title': 'Sql email address validation', 'summary': 'Covers techniques for validating email addresses in sql, including the use of like operator with wildcards to identify valid emails, resulting in a 4:1 ratio of valid to invalid emails. it also explains the use of underscore and square brackets to retrieve email addresses with specific patterns and select people based on name patterns from a table.', 'chapters': [{'end': 698.934, 'start': 579.758, 'title': 'Finding valid email addresses in sql', 'summary': 'Explains how to use the like operator with wildcards to find valid email addresses in a sql database, identifying the presence of the at symbol and utilizing the not operator to identify invalid emails, resulting in a 4:1 ratio of valid to invalid emails.', 'duration': 119.176, 'highlights': ['Using the LIKE operator with wildcards, such as the percentage symbol, allows for the specification of patterns in SQL queries, enabling the identification of valid email addresses based on the presence of the at symbol.', 'The not operator can be used to identify invalid email addresses by specifying a pattern that does not conform to the expected format, resulting in the detection of one invalid email among a total of five rows.', 'The at symbol serves as a crucial indicator of a valid email address, and its absence in an email column can be identified using SQL queries, as demonstrated by the case of Mary, who provided an invalid email address.', 'The concept of wildcards in SQL, including the percentage symbol as a placeholder for zero or more characters, provides a flexible approach to pattern matching, facilitating the recognition of various email address formats within a database.']}, {'end': 908.879, 'start': 699.094, 'title': 'Using underscore and square brackets in sql', 'summary': 'Explains the use of underscore as a substitute for one character and square brackets to specify a list of characters in sql queries, demonstrating how to retrieve email addresses with specific patterns and select people based on name patterns from a table.', 'duration': 209.785, 'highlights': ['The chapter explains the use of underscore as a substitute for one character and square brackets to specify a list of characters in SQL queries It clarifies the purpose of underscore as a substitute for exactly one character and square brackets to specify a list of characters in SQL queries.', "Demonstrating how to retrieve email addresses with specific patterns using underscore It demonstrates retrieving email addresses with one character before and after the '@' symbol, followed by '.com', using the underscore in SQL queries.", 'Showing how to select people based on name patterns using square brackets It illustrates selecting people from a table based on specific starting characters in their names, using square brackets in SQL queries.']}], 'duration': 329.121, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8579758.jpg', 'highlights': ['Using the LIKE operator with wildcards enables the identification of valid email addresses based on the presence of the at symbol.', 'The not operator can be used to identify invalid email addresses by specifying a pattern that does not conform to the expected format.', 'The at symbol serves as a crucial indicator of a valid email address, and its absence in an email column can be identified using SQL queries.', 'The concept of wildcards in SQL, including the percentage symbol as a placeholder for zero or more characters, provides a flexible approach to pattern matching, facilitating the recognition of various email address formats within a database.', 'The chapter explains the use of underscore as a substitute for one character and square brackets to specify a list of characters in SQL queries.', 'Demonstrating how to retrieve email addresses with specific patterns using underscore in SQL queries.', 'Showing how to select people based on name patterns using square brackets in SQL queries.']}, {'end': 1132.066, 'segs': [{'end': 1057.235, 'src': 'embed', 'start': 956.806, 'weight': 0, 'content': [{'end': 984.754, 'text': 'So select star from tblPerson where city is equal to London or city is equal to Mumbai and age should be greater than 25.', 'start': 956.806, 'duration': 27.948}, {'end': 989.916, 'text': 'So you have to put this parenthesis here between these two conditions, because what we said,', 'start': 984.754, 'duration': 5.162}, {'end': 996.659, 'text': 'the city should be equal to London or Mumbai and at the same time, their age should be greater than 25..', 'start': 989.916, 'duration': 6.743}, {'end': 1001.662, 'text': 'So one of these two conditions should be true and their age should be 25.', 'start': 996.659, 'duration': 5.003}, {'end': 1005.684, 'text': 'So you can join multiple conditions using OR and AND operators.', 'start': 1001.662, 'duration': 4.022}, {'end': 1013.889, 'text': "So, if we select this query, obviously we'll get all those people who are living in city, you know, London or Mumbai,", 'start': 1006.943, 'duration': 6.946}, {'end': 1022.016, 'text': 'and at the same time their age greater than 25..', 'start': 1013.889, 'duration': 8.127}, {'end': 1028.161, 'text': 'And we can use order by clause to sort the results of a query.', 'start': 1022.016, 'duration': 6.145}, {'end': 1037.759, 'text': "So let's say when I select all these rows from this table, okay, They are coming in the order of this primary key, 1, 2, 3, 4, 5.", 'start': 1028.621, 'duration': 9.138}, {'end': 1040.202, 'text': 'Only this primary key is sorted.', 'start': 1037.76, 'duration': 2.442}, {'end': 1048.688, 'text': "Now, let's say I want to retrieve all the people from this database, but their names should be in ascending order of alphabets.", 'start': 1040.442, 'duration': 8.246}, {'end': 1053.712, 'text': 'OK, how do I do that? Select star from table person, order by.', 'start': 1049.889, 'duration': 3.823}, {'end': 1057.235, 'text': 'We use order by class to sort the results.', 'start': 1054.293, 'duration': 2.942}], 'summary': 'Query selects people in london or mumbai with age > 25, and sorts results.', 'duration': 100.429, 'max_score': 956.806, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8956806.jpg'}, {'end': 1104.309, 'src': 'embed', 'start': 1080.593, 'weight': 4, 'content': [{'end': 1087.915, 'text': "so when I press F5 now, the names will be sorted in descending order and it's also possible you can sort by multiple columns.", 'start': 1080.593, 'duration': 7.322}, {'end': 1094.943, 'text': 'for example, I want to sort first by name in the descending order, then by age in ascending order.', 'start': 1087.915, 'duration': 7.028}, {'end': 1096.684, 'text': 'Is that possible? Absolutely.', 'start': 1095.243, 'duration': 1.441}, {'end': 1101.828, 'text': 'Order by name in descending order, then by age in ascending order.', 'start': 1097.325, 'duration': 4.503}, {'end': 1104.309, 'text': 'You can specify as many columns as you want.', 'start': 1102.268, 'duration': 2.041}], 'summary': 'Sorting by multiple columns is possible, with the ability to specify as many columns as desired.', 'duration': 23.716, 'max_score': 1080.593, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_81080593.jpg'}, {'end': 1144.631, 'src': 'heatmap', 'start': 1114.956, 'weight': 0.783, 'content': [{'end': 1118.819, 'text': 'So their ages are sorted in ascending order because you specified.', 'start': 1114.956, 'duration': 3.863}, {'end': 1121.443, 'text': 'sought by age in ascending order.', 'start': 1119.582, 'duration': 1.861}, {'end': 1132.066, 'text': 'And selecting top n or top percentage of risk, this top keyword is especially useful if the table is large.', 'start': 1125.124, 'duration': 6.942}, {'end': 1135.908, 'text': "Let's say I have a table where I have thousands and thousands of records.", 'start': 1132.907, 'duration': 3.001}, {'end': 1139.709, 'text': 'I just want to see a sample set of records in that table.', 'start': 1136.348, 'duration': 3.361}, {'end': 1144.631, 'text': "But when I say select star from tblPerson, it's going to retrieve all the rows from that table.", 'start': 1140.069, 'duration': 4.562}], 'summary': 'Sort ages in ascending order. use top keyword for large tables to select sample records.', 'duration': 29.675, 'max_score': 1114.956, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_81114956.jpg'}], 'start': 911.62, 'title': 'Sql operators, conditions, sorting, and ordering', 'summary': "Covers using wildcard operators, multiple conditions, and 'order by' clause in sql server. it includes selecting people living in london or mumbai whose age is greater than 25, and sorting query results by single or multiple columns in ascending or descending order, with the ability to select top n or top percentage of results.", 'chapters': [{'end': 1028.161, 'start': 911.62, 'title': 'Using wildcard operators and multiple conditions in sql', 'summary': 'Covers using wildcard operators in sql server to filter rows, joining multiple conditions using and and or operators, and selecting people living in london or mumbai whose age is greater than 25.', 'duration': 116.541, 'highlights': ['Using wildcard operators such as AND and OR to filter rows based on multiple conditions, as shown in the example of selecting people living in London or Mumbai whose age is greater than 25.', 'Explanation of using parentheses to group conditions and ensure the correct logical evaluation of multiple conditions in SQL queries.', 'Demonstration of joining multiple conditions using AND and OR operators to retrieve specific data based on the given criteria.']}, {'end': 1132.066, 'start': 1028.621, 'title': 'Sql sorting and ordering', 'summary': "Explains how to use the 'order by' clause in sql to sort query results, including sorting by single or multiple columns in ascending or descending order, with the ability to select top n or top percentage of results.", 'duration': 103.445, 'highlights': ["The 'order by' clause in SQL is used to sort query results based on one or more columns, and it defaults to ascending order if not specified.", "You can specify sorting in descending order using the DESC keyword after the column name in the 'order by' clause.", "It is possible to sort by multiple columns in SQL by specifying the order for each column in the 'order by' clause.", "The 'top' keyword in SQL is useful for selecting a specific number or percentage of rows, especially when dealing with large tables."]}], 'duration': 220.446, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_8911620.jpg', 'highlights': ['Using wildcard operators such as AND and OR to filter rows based on multiple conditions, as shown in the example of selecting people living in London or Mumbai whose age is greater than 25.', 'Demonstration of joining multiple conditions using AND and OR operators to retrieve specific data based on the given criteria.', 'Explanation of using parentheses to group conditions and ensure the correct logical evaluation of multiple conditions in SQL queries.', "The 'order by' clause in SQL is used to sort query results based on one or more columns, and it defaults to ascending order if not specified.", "You can specify sorting in descending order using the DESC keyword after the column name in the 'order by' clause.", "It is possible to sort by multiple columns in SQL by specifying the order for each column in the 'order by' clause.", "The 'top' keyword in SQL is useful for selecting a specific number or percentage of rows, especially when dealing with large tables."]}, {'end': 1312.231, 'segs': [{'end': 1177.517, 'src': 'embed', 'start': 1151.495, 'weight': 0, 'content': [{'end': 1157.882, 'text': 'So if you want to see just the top 10 or 5 records of a table, you can use the top keyword.', 'start': 1151.495, 'duration': 6.387}, {'end': 1161.226, 'text': 'How do we use that? You can just say select top.', 'start': 1158.383, 'duration': 2.843}, {'end': 1169.432, 'text': 'Maybe I want the top 10 records from this table, so select top 10 star from TBL Person, which only gives 10 records.', 'start': 1163.028, 'duration': 6.404}, {'end': 1171.053, 'text': "We don't even have 10 records here.", 'start': 1169.452, 'duration': 1.601}, {'end': 1173.655, 'text': "So let's say select top two records.", 'start': 1171.453, 'duration': 2.202}, {'end': 1176.136, 'text': 'So select top two star.', 'start': 1174.735, 'duration': 1.401}, {'end': 1177.517, 'text': 'So it gives you all the columns.', 'start': 1176.256, 'duration': 1.261}], 'summary': "Using 'top' in sql, select top 10 records from tbl person, giving 10 records.", 'duration': 26.022, 'max_score': 1151.495, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_81151495.jpg'}, {'end': 1263.046, 'src': 'embed', 'start': 1232.475, 'weight': 1, 'content': [{'end': 1239.076, 'text': "Let's say, you know, one of the common interview questions is, how do you find the top salary of an employee? Okay.", 'start': 1232.475, 'duration': 6.601}, {'end': 1244.058, 'text': "In our table, we don't have, you know, salary column, but we have an age column.", 'start': 1239.416, 'duration': 4.642}, {'end': 1247.979, 'text': "Let's say I want to find the eldest person in this table.", 'start': 1244.098, 'duration': 3.881}, {'end': 1252.82, 'text': 'How do I do that? You can use the top keyword for that, actually.', 'start': 1248.299, 'duration': 4.521}, {'end': 1254.02, 'text': "So, let's see this.", 'start': 1253.18, 'duration': 0.84}, {'end': 1256.401, 'text': 'Select star from TBL person.', 'start': 1254.16, 'duration': 2.241}, {'end': 1263.046, 'text': 'order by age desc.', 'start': 1259.042, 'duration': 4.004}], 'summary': 'Using the top keyword, find the eldest person in the table by ordering the age column in descending order.', 'duration': 30.571, 'max_score': 1232.475, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_81232475.jpg'}], 'start': 1132.907, 'title': 'Using top keyword in sql queries', 'summary': 'Introduces the usage of the top keyword in sql queries to retrieve a specific number or percentage of records from a table, improving performance and enabling tasks such as finding the top salary or eldest person in a table.', 'chapters': [{'end': 1312.231, 'start': 1132.907, 'title': 'Using top keyword in sql queries', 'summary': 'Introduces the usage of the top keyword in sql queries to retrieve a specific number or percentage of records from a table, improving performance and enabling tasks such as finding the top salary or eldest person in a table.', 'duration': 179.324, 'highlights': ['Using top keyword to retrieve a specific number or percentage of records from a table. The top keyword in SQL queries allows users to retrieve a specific number or percentage of records from a table, improving performance and query efficiency.', "Applying top keyword to find the eldest person in a table. By using the top keyword with an 'order by' clause on the age column in descending order, it enables the retrieval of the top record, representing the eldest person in the table, such as selecting the top 1 record based on age.", "Utilizing top keyword to find the top paid employee in a salary column. Similar to finding the eldest person, the top keyword can be applied to retrieve the top paid employee by using an 'order by' clause on the salary column in descending order and selecting the top record, enabling tasks like finding the top paid employee in a table."]}], 'duration': 179.324, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/R9pXnHIFj_8/pics/R9pXnHIFj_81132907.jpg', 'highlights': ['Using top keyword to retrieve a specific number or percentage of records from a table, improving performance and query efficiency.', "Applying top keyword to find the eldest person in a table by using the top keyword with an 'order by' clause on the age column in descending order.", "Utilizing top keyword to find the top paid employee in a salary column by using an 'order by' clause on the salary column in descending order."]}], 'highlights': ["The 'distinct' keyword allows retrieving unique values from a specific column, reducing duplicates and returning a distinct set of values.", 'The advantage of using fully qualified table names is that it allows for successful execution from any database context, preventing errors and ensuring flexibility.', 'The WHERE class is employed to filter records in SQL based on specified conditions, such as filtering people residing in a specific city like London.', "The process of selecting people from a database using multiple filters based on age is explained, emphasizing the use of concatenated conditions using the 'or' operator.", 'Using the LIKE operator with wildcards enables the identification of valid email addresses based on the presence of the at symbol.', 'Using wildcard operators such as AND and OR to filter rows based on multiple conditions, as shown in the example of selecting people living in London or Mumbai whose age is greater than 25.', "The 'order by' clause in SQL is used to sort query results based on one or more columns, and it defaults to ascending order if not specified.", "The 'top' keyword in SQL is useful for selecting a specific number or percentage of rows, especially when dealing with large tables."]}