<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Emilio Eguia on Medium]]></title>
        <description><![CDATA[Stories by Emilio Eguia on Medium]]></description>
        <link>https://medium.com/@emilioeguia?source=rss-3b025a77231f------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*yjrz1DUP5VMIDoHDdKIFCw.jpeg</url>
            <title>Stories by Emilio Eguia on Medium</title>
            <link>https://medium.com/@emilioeguia?source=rss-3b025a77231f------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 09:51:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@emilioeguia/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[15 Essential SQL Best Practices]]></title>
            <link>https://levelup.gitconnected.com/15-essential-sql-best-practices-830b24100517?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/830b24100517</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[coding]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Thu, 21 Dec 2023 14:58:00 GMT</pubDate>
            <atom:updated>2023-12-21T14:58:00.466Z</atom:updated>
            <content:encoded><![CDATA[<p>15 Best Practices that will level up your SQL coding.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*i9TIxVoWilKzXx0O.jpg" /></figure><p>Knowing and implementing the best practices in SQL is essential for achieving optimal performance, security, and reliability of your database.</p><p>Performance is a critical factor in SQL databases, and implementing best practices such as using parameters, optimizing indexes, and avoiding SELECT * statements can help improve query performance and reduce response times. These practices can also help prevent performance bottlenecks and ensure that your SQL database can handle increasing volumes of data.</p><p>Security is another crucial aspect of SQL databases. Best practices such as using constraints, managing database permissions, and performing regular maintenance can help ensure that your data is secure and protected from unauthorized access, corruption, or loss.</p><p>Reliability is also important in SQL databases. Best practices such as using transactions, monitoring performance, and performing regular maintenance can help ensure that your database is functioning correctly and that data is consistent, reliable, and available when needed. This can help prevent data loss or corruption and minimize downtime.</p><p>Let’s explore more deeply each best practice.</p><h4>Use Parameters</h4><p>One of the most common mistakes in SQL is not using parameters in queries. This can lead to SQL injection attacks and performance issues. When using parameters, the server can cache the execution plan for the query, resulting in faster execution times. <br>Additionally, parameters can help prevent SQL injection attacks by ensuring that user input is not executed as SQL code.</p><h4>Avoid SELECT *</h4><p>While it might be tempting to use SELECT * in queries, it is a bad practice. Instead, you should explicitly list the columns you need to retrieve. <br>Using SELECT * can lead to performance issues as it can retrieve unnecessary data, causing additional I/O operations.</p><h4>Optimize Indexes</h4><p>Indexes can significantly improve query performance in SQL. However, poorly designed indexes can have the opposite effect. It is essential to create the right indexes and regularly monitor and maintain them. <br>Ensure that you don’t create too many indexes as they can impact performance during data modification.</p><h4>Use Transactions</h4><p>Transactions provide a way to ensure that all operations succeed or fail together. Transactions help maintain data integrity, and in case of failures, rollbacks can restore the database to its previous state. <br>It is essential to keep transactions as short as possible to reduce the potential for deadlocks and improve performance.</p><h4>Regular Maintenance</h4><p>Regular maintenance is vital to keeping your SQL running smoothly. This includes regular backups, index optimization, and updating statistics.<br>It is also essential to regularly check for and address issues with disk space, memory usage, and CPU utilization.</p><h4>Use Constraints</h4><p>Constraints help maintain data integrity by preventing invalid data from being inserted into tables. They are also essential in ensuring data consistency and avoiding data corruption. <br>Using constraints can improve query performance as they allow SQL to better optimize queries.</p><h4>Monitor Performance</h4><p>Monitoring SQL performance is essential to ensure that your database is running optimally. Monitoring tools can help identify performance bottlenecks and optimize queries, indexes, and other objects. It is essential to keep an eye on system resources, including CPU, memory, and disk usage.</p><h4>Normalize Data</h4><p>Normalizing data involves organizing it into tables and columns, avoiding data duplication and redundancy. Normalization can improve query performance and data consistency while reducing the potential for data anomalies.</p><h4>Use Partitioning</h4><p>Partitioning is a way to divide large tables into smaller, more manageable ones. This can improve performance by allowing SQL to read and write data more efficiently. Partitioning can also make maintenance tasks such as backups and index rebuilds more manageable.</p><h4>Avoid Cursors</h4><p>Cursors are often used to iterate through data row by row, but they can be inefficient and slow. Consider using set-based operations instead, which are optimized for handling large sets of data.</p><h4>Use Stored Procedures</h4><p>Stored procedures are precompiled queries that can be executed repeatedly. They can improve performance by reducing network traffic and improving query plan reuse. Stored procedures can also provide an additional layer of security by allowing you to grant permissions to users without giving them direct access to tables.</p><h4>Use the Appropriate Data Types</h4><p>Using appropriate data types can improve performance and reduce storage requirements. Avoid using data types that are too large or too small for the data being stored. Choose data types that match the precision and scale of the data being stored.</p><h4>Use Constraints to Enforce Data Rules</h4><p>Constraints are a way to enforce rules on data being inserted into tables. They can be used to enforce data type, uniqueness, and referential integrity. Constraints can also help ensure that data is accurate, consistent, and reliable.</p><h4>Monitor and Manage Database Permissions</h4><p>Database permissions determine who can access, modify, and delete data. Regularly reviewing and managing database permissions is essential for maintaining data security and integrity. <br>Ensure that users have the minimum required permissions to perform their tasks and that permissions are reviewed regularly.</p><h4>Keep the Database Server and Operating System Up to Date</h4><p>Regularly updating the database server and operating system can help ensure that security vulnerabilities are addressed and performance improvements are applied. Keep up to date with the latest service packs and patches to ensure the database server and operating system are running efficiently.</p><p>In conclusion, there’s more to explore and master in the realm of SQL best practices. This article provided a concise overview, aiming to spark further investigation. We trust that you’ve grasped the significance of these practices and gained insights into their application. <br>From parameter usage to regular maintenance, each practice plays a crucial role in optimizing your SQL database.</p><p>Thanks for exploring these essential techniques with us, and we wish you success in implementing them to enhance your database performance, security, and reliability.</p><p>Thanks for reading, and keep coding!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=830b24100517" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/15-essential-sql-best-practices-830b24100517">15 Essential SQL Best Practices</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Basic Introduction To Stored Procedures]]></title>
            <link>https://levelup.gitconnected.com/a-basic-introduction-to-stored-procedures-7f5d5a99c11d?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/7f5d5a99c11d</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Tue, 29 Nov 2022 12:40:08 GMT</pubDate>
            <atom:updated>2022-11-29T12:40:08.199Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/860/0*cpWJJo96Vb2ls33O" /></figure><p>SQL Server stored procedures are used to group Transact-SQL statements into logical units that can be reused. When working on the SQL Server Database, the stored procedure is stored as a named object.</p><h4>Benefits of Stored Procedures</h4><p>There are lots of benefits to using stored procedures in your programs. Between them are:</p><ul><li>Stored procedures in SQL Server can accept several input parameters and return multiple values as output parameters.</li><li>A stored procedure provides an important and reliable layer of security between the user interface and the database. It gives security by controlling the data access because end users can enter or change data, but can’t write procedures. It serves to preserve data integrity because the information is being entered consistently.</li><li>The usage of stored procedures can dramatically reduce network traffic between the client and server because the commands are executed as a single batch (or group) of code. This means that the only thing being sent to the network is the call to execute the procedure, instead of costly sending every single line of code individually.</li><li>Plus, it can help with overall performance. Since when you call a stored procedure for the first time, it creates an <em>execution plan</em> and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan to execute the stored procedure at a very fast and reliable performance.</li></ul><h4>Basic of Stored Procedures</h4><p>Creating a simple stored procedure</p><p>We will use an example of a stored procedure that returns a select statement, you use the CREATE PROCEDURE statement as follows:</p><pre>CREATE PROCEDURE sp_name<br>AS<br>BEGIN<br>    SELECT <br>        column1, column2<br>    FROM <br>        table1<br>    ORDER BY <br>        column1;<br>END;</pre><p>Understanding the above syntax:</p><ul><li>The usp_PersonData is the name of our stored procedure.</li><li>The AS keyword separates the heading and the body of the stored procedure.</li><li>The BEGIN and END keywords surrounding the statement are surround the body of our store procedure, and also help to make the code clear.</li></ul><p>A quick note that instead of theCREATE PROCEDURE keywords, you can use CREATE PROC to make the statement shorter.</p><p>If you are in SSMS (SQL Server Management Studio) you can find the stored procedure in the Object Explorer. Right under Programmability &gt; Stored Procedures. Sometimes, you need to click the Refresh button to update the database objects.</p><p>To execute a stored procedure, you use the EXECUTE or EXEC statement followed by the name of the stored procedure:</p><pre>EXECUTE sp_name;<br>EXEC sp_name;</pre><p>To modify an existing stored procedure, you use the ALTER PROCEDURE statement.</p><pre>ALTER PROCEDURE sp_name<br>AS<br>BEGIN<br>    SELECT <br>        column1, column2<br>    FROM <br>        table1<br>    ORDER BY <br>        column2; <br>END;</pre><p>To delete a stored procedure, you use the DROP PROCEDURE or DROP PROC statement:</p><pre>DROP PROCEDURE sp_name;<br>DROP PROC sp_name;</pre><h4><strong>Parameters in Stored Procedures</strong></h4><p>Another last thing o know is that you can pass a parameter to a stored procedure. A feature that allows the code to be more versatile and reusable.</p><pre>CREATE PROCEDURE sp_name<br>    (@parameter1 varchar(50))<br>AS<br>BEGIN<br>    SELECT <br>        column1, column2<br>    FROM <br>        table1<br>    WHERE<br>        column1 = @parameter1; <br>END;</pre><p>In this case, we are passing @pafameter1 to the stored procedure and using it to filter our select statement. Note that to execute our stored procedure we now should write: (we are passing ‘Example’ as our parameter)</p><pre>EXEC sp_name &#39;Example&#39;</pre><p>More things are needed to master stored procedures and use them in an efficient way. This was a brief introduction and hopefully encouraged you to investigate further.</p><p>Hope that you have first understood why Sored Procedures are useful, and then the basics of working with them. You have learned how to manage SQL Server stored procedures including creating, executing, modifying, and deleting them.</p><p>Thanks for reading, and have a great rest of your day! Keep coding.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7f5d5a99c11d" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/a-basic-introduction-to-stored-procedures-7f5d5a99c11d">A Basic Introduction To Stored Procedures</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Parameters In Stored Procedures]]></title>
            <link>https://levelup.gitconnected.com/sql-parameters-in-stored-procedures-faf38d8af088?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/faf38d8af088</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Tue, 29 Nov 2022 12:40:02 GMT</pubDate>
            <atom:updated>2022-11-29T12:40:02.941Z</atom:updated>
            <content:encoded><![CDATA[<h4>Learn to use parameters in SQL stored procedures, and make your code more versatile and reliable.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*S85oDsgKTOrkVt33" /></figure><p>In this tutorial, we will dive into stored procedures. Learning about passing one or more parameters to it. This will cause the result of the stored procedure to change based on the values of the parameters inputted.</p><h3>Stored Procedures with one parameter</h3><p>We will create an example to show how the parameters work. Let’s say that we have a product table with lots of information. We can add a parameter to the stored procedure to return the products whose prices are lower than the input price:</p><pre>CREATE PROCEDURE usp_find_products<br>  (@max_price AS DECIMAL)<br>AS<br>BEGIN<br>    SELECT<br>        name, price<br>    FROM <br>        products<br>    WHERE<br>        price &lt;= @max_price<br>END;</pre><p>Let’s analyze this example:</p><ul><li>We added a parameter named @max_price to the procedure usp_find_products. Every parameter must start with the @ sign. To specify the data type of our parameter we used AS DECIMALbut we can also use directlyDECIMAL. The passed parameter must be surrounded by both the opening and closing parenthesis.</li><li>Then we used @max_price in the WHERE clause to filter only the products whose prices are lesser or equal to @max_price.</li></ul><p>To execute the usp_find_products stored procedure, in this case, you should pass an argument:</p><pre>EXEC usp_find_products 50;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/161/1*1zb0OYlVOj_6230wWu-Tvg.png" /></figure><p>If you change the argument to 100, a different result will be returned:</p><pre>EXEC usp_find_products 100;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/160/1*AaJPYFLk1feqbpOkD3Uonw.png" /></figure><h3>Stored procedures with multiple parameters</h3><p>When writing a stored procedure with several parameters, these are separated by commas.</p><p>Let’s modify our previous stored procedure uspFindProducts to determine not only a max price but also a min price.</p><pre>ALTER PROCEDURE usp_find_products(<br>    @min_price AS DECIMAL<br>    ,@max_price AS DECIMAL<br>)<br>AS<br>BEGIN<br>    SELECT<br>        name, price<br>    FROM <br>        products<br>    WHERE<br>        price &gt;= @min_price AND<br>        price &lt;= @max_price<br>END;EXECUTE uspFindProducts 900, 1000;</pre><p>Then we execute the stored procedure and pass it 2 values for the parameters: EXECUTE usp_find_products 100, 200; Which returns for example the following output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/161/1*zxz9DImpiSrsTFBFGlzhMQ.png" /></figure><p>Usually, it’s better to execute the stored procedures using named parameters. The following code will execute the same as the previous calling:</p><p>EXECUTE usp_find_products @min_list_price = 900, @max_list_price = 1000;</p><h3>Defining optional parameters</h3><p>In all the previous examples, it is required to pass values for all parameters defined. But SQL allows us to define a default value for a parameter, which will let the parameter take that value in case of not specifically passing it on the EXECUTE.</p><p>See the following stored procedure as an example:</p><pre>ALTER PROCEDURE usp_find_products(<br>    @min_price AS DECIMAL = 0<br>    ,@max_price AS DECIMAL = 1000<br>)<br>AS<br>BEGIN<br>    SELECT<br>        name, price<br>    FROM <br>        products<br>    WHERE<br>        price &gt;= @min_price AND<br>        price &lt;= @max_price<br>END;</pre><p>We have altered the stored procedure, and assigned 0 as the default value for the @min_price parameter and 1000 as the default value for the @max_price.</p><p>Then, you will be able to execute without passing the values for@min_price and @max_price: EXECUTE usp_find_products. It is taking 0 and 1000 as default values and will return the products that meet those conditions.</p><p>Note that you can still pass the values to the optional parameters when calling the execute. In this case, you can pass values for none, either, or both parameters. As all the following examples show:</p><pre>EXECUTE usp_find_products; <br>EXECUTE usp_find_products @min_price=10; <br>EXECUTE usp_find_products @min_price=10, @max_price=100;</pre><p>Know that you can also set a parameter default value to NULL. Note that this is typically a technique to avoid.</p><h3>Output parameters</h3><p>Stored procedures can also have output parameters. For example, let’s alter our previous procedure to also return the number of products through the output parameter @count:</p><pre>ALTER PROCEDURE usp_find_products(<br>    @min_price AS DECIMAL = 0<br>    ,@max_price AS DECIMAL = 1000<br>    ,@count INT OUTPUT<br>)<br>AS<br>BEGIN<br>    SELECT<br>        name, price<br>    FROM <br>        products<br>    WHERE<br>        price &gt;= @min_price AND<br>        price &lt;= @max_price<br>    SELECT @count = @@ROWCOUNT;<br>END;</pre><p>Above we have created an output parameter called@count to store the number of found products: @count INT OUTPUT. We assigned the number of rows returned by the select query (@@ROWCOUNT) to the @count parameter.</p><p>@@ROWCOUNT is a system variable that takes the value of the number of rows returned by the preceding select statement.</p><p>The use of output parameters serves as a way to retrieve data calculated inside of a stored procedure and put it into a parameter to use outside of the procedure.</p><p>In this tutorial, you have learned how to work with stored procedures having one or more parameters. You also learned how to create optional parameters and use the output parameter.</p><p>Thanks for reading, and have a great rest of your day! Keep coding.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=faf38d8af088" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/sql-parameters-in-stored-procedures-faf38d8af088">SQL Parameters In Stored Procedures</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Keyboard Shortcuts for Windows]]></title>
            <link>https://emilioeguia.medium.com/keyboard-shortcuts-for-windows-1b6eaee04b81?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/1b6eaee04b81</guid>
            <category><![CDATA[improvement]]></category>
            <category><![CDATA[windows]]></category>
            <category><![CDATA[computers]]></category>
            <category><![CDATA[pc]]></category>
            <category><![CDATA[productivity]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Thu, 24 Nov 2022 23:24:19 GMT</pubDate>
            <atom:updated>2022-11-24T23:24:19.219Z</atom:updated>
            <content:encoded><![CDATA[<h4>To be efficient and fast on the computer.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yL6XNDSWzS1hOj4R" /></figure><p>In this short article, we will see a broad selection of shortcut keys that will be extremely useful for your everyday tasks on the computer. Shortcut keys help provide an easier and quicker method of navigating and executing commands in the computer.</p><p>If you are working with specific software, you can also find the shortcut keys of that program by looking for <em>underlined</em> letters in their menus.</p><p>But for commonly used shortcuts on windows, we have compiled the most useful ones and divided them into several categories.</p><p><em>Search and Replace</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*hSJpBIzz1ogm_Y-U.png" /></figure><p><em>General</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*fkyreK6xqzei8M6U.png" /></figure><p><em>Navigation</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*ZI410hromNGzsxzo.png" /></figure><p><em>File Management:</em> when working with several files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*kOVl9dsUB_Oi-2CO.png" /></figure><p><em>Display:</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*O2hSOUp3ldFpUHl3.png" /></figure><p><em>Basic Editing:</em> for working on a text editor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*UJ9HMoNj75MihpAi.png" /></figure><p><em>Integrated Terminal:</em> for when you need to use a terminal.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*WGFg9ERy2r1OPmHW.png" /></figure><p>Listed below will be a longer list with more shortcuts, save them for when you need them later.</p><ul><li>Alt + F → File menu options in the current program.</li><li>Alt + E →Open Edit options in the current program.</li><li>Alt + Tab →Switch between open programs.</li><li>F1 → View help information (F1 is used by almost every Windows program to display help).</li><li>F2 → Rename a selected file.</li><li>F5 → Refresh the current program window.</li><li>Ctrl + D → Bookmark the current page in most Internet browsers.</li><li>Ctrl + N → Create a new or blank document in some software, or open a new tab in most Internet browsers.</li><li>Ctrl + O → Open a file in the current software.</li><li>Ctrl + U → Change selected text to be underlined.</li><li>Ctrl + I → Change selected text to be in italics.</li><li>Ctrl + F → Open find window for current document or window.</li><li>Ctrl + S → Save the current document file.</li><li>Ctrl + X → Cut selected item.</li><li>Ctrl + Y→ Redo the last action.</li><li>Ctrl + Z → Undo the last action.</li><li>Shift + Del → Cut selected item.</li><li>Ctrl + C → Copy selected item.</li><li>Ctrl + Ins → Copy selected item.</li><li>Ctrl + K → Insert hyperlink for the selected text.</li><li>Ctrl + P → Print the current page or document.</li><li>Home →Go to the beginning of the current line.</li><li>Ctrl + Home → Go to the beginning of the document.</li><li>End → Go to the end of the current line.</li><li>Ctrl + End → Go to the end of the document.</li><li>Shift + Home → Highlight from the current position to the beginning of the line.</li><li>Shift + End → Highlight from the current position to the end of the line.</li><li>Ctrl + Left arrow → Move one word to the left at a time.</li><li>Ctrl + Right arrow → Move one word to the right at a time.</li><li>Ctrl + Esc → Open the Start menu.</li><li>Ctrl + Shift + Esc → Open Windows Task Manager.</li><li>Alt + F4 → Close the currently active program.</li><li>Alt + Enter → Open the properties for the selected item (file, folder, shortcut, etc.).</li><li>Windows key + L → Lock the computer, requiring password entry to access again.</li><li>Windows key + X →Access the Power User Tasks Menu in Windows 8 and Windows 10.</li><li>Windows key + Down arrow → Minimize the active program window.</li><li>Windows key + Up arrow → Maximize the active program window.</li></ul><p>With all of these keyboard shortcuts, you’ll be saving tons of time each time you want to perform an action. Remembering all of them isn’t crucial and you can always search for them whenever you need to.</p><p>Thanks for reading, and have a great rest of your day!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b6eaee04b81" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Aggregate Functions in SQL]]></title>
            <link>https://emilioeguia.medium.com/mastering-aggregate-functions-in-sql-b4fc7652f461?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/b4fc7652f461</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-server]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Thu, 24 Nov 2022 00:04:05 GMT</pubDate>
            <atom:updated>2022-11-24T00:04:05.341Z</atom:updated>
            <content:encoded><![CDATA[<h4>Why every developer should learn how to use them.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/332/1*B2rj8pbhrSAX2WJK4t5QRg.png" /></figure><p>Today we will look into aggregate functions to get a better understanding of how to obtain some data from integer columns. This is a topic you must know and master in order to be more proficient and obtain better data in SQL.</p><p>An aggregate function performs a calculation over a set of values and returns a single value. Except for COUNT(*), all aggregate functions ignore NULL values. They usually can be used with the GROUP BY clause.</p><p>All aggregate functions are deterministic. This means that aggregate functions return the same value each time that they are called, always with a specific set of input values. You can search for “Deterministic and Nondeterministic Functions” to better understand this concept.</p><p>There are a good number of aggregate functions, and we will go over some of the most commonly used ones. There are functions such as COUNT(), SUM() , MIN() , MAX() , and AVG(). Then we will take a look into how we can separate our data into different groups for analysis.</p><h3>COUNT() Function</h3><p>This one is really intuitive and works as expected, the COUNT() function does exactly what it sounds like. It counts the number of matches in the selection performed. For example, if we used the COUNT() function on the entire data set, the function would return the total number of observations within the dataset.</p><p>SELECT COUNT(*) FROM table_name;</p><p>This can be used in more useful queries with the WHERE statement. Let’s suppose that we have a table of books, with information about the author, genre, year, title, and sales. By using the WHERE clause with the COUNT() function, we can obtain more interesting data. We can count how many books are novels, or how many books were published in a given year. But let’s say that we have an author that has written lots of books, he will appear several times at our table. If we wanted the count of authors, we would only need each author to be counted once. In this case, we can use the COUNT(DISTINCT author) and it would return the count of unique authors in our data. These are just some of the possibilities for which the COUNT()function can be used.</p><h3>MAX() Function</h3><p>The MAX() function is also very intuitive. Given a selection of data, this function returns the max value found in that specific set of data. A simple use for this function in our previous dataset of books would be to find the book with the highest amount of sales. SELECT MAX(sales) FROM books;</p><p>Or we can be more specific about the data that we want, and write a query like this.</p><p>SELECT MAX(sales) FROM books WHERE genre = &#39;fantasy&#39; AND year = &#39;2022&#39;;</p><h3>MIN() Function</h3><p>The MIN() function is very similar to the previous function, we will call it its little brother. However, instead of returning the max number, the function returns the opposite: the <em>minimum</em> number. One simple example would be the following, but the logic is the same as the MAX() function. SELECT MIN(sales) FROM books; To find out the least sellingo book, and probably then we will want to know why it was so unpopular.</p><h3>AVG() Function</h3><p>The AVG() function is useful to obtain the average of a selected column in a given dataset. It can also be considered the <em>mean</em> or the sum of the observations divided by the number of observations*. There are infinite uses for this function and you can probably think of multiple ways of how it can be applied. For example, we could use our books table. If we wanted to know the average selling of a book for a given year we could execute SELECT AVG(sales) FROM books WHERE year = &#39;2022&#39;; . We could also use the AVG() function in a different way, to only obtain the books that have a selling count higher than the average of all books.</p><p>SELECT * FROM books WHERE sales &gt; (SELECT AVG(sales) FROM books);</p><h3>SUM() Function</h3><p>The SUM() function adds together all the observations in a given column, and it’s the last aggregate function that we will cover today. A simple example would be using the SUM() function to find the total sales of all books. But probably we would want to know something more specific, like the total book selling for a given author.</p><p>SELECT SUM(sales) FROM books WHERE author = &#39;Sthephen King&#39;</p><h4>The GROUP BY Statement</h4><p>Finally, we will see the GROUP BY statement. Which groups different observations together by similar values into several summary rows. A quick and simple example of this would be using the books table from the COUNT() function. There are books from several different genres. The GROUP BY statement could give an easy summary of the number of books that were written for each genre. The query would look like the following:</p><p>SELECT genre, COUNT(*) FROM books GROUP BY genre;</p><p>This could reveal the genres with the most books, and other interesting data.</p><p>SELECT author, AVG(sales) FROM books GROUP BY author HAVING AVG(sales) &gt; 1000;</p><p>Here we would return each author with the average sales of all his books, always that he has an average sales of over 1000 per book.</p><p>Something else to take into consideration is that the data can be grouped by multiple columns, using a comma in between the different fields. For example, if you are working with a large dataset of books, the data may need to be grouped first by genre and then by author.</p><p>You can find more information about aggregate functions online. We have covered the most used ones, but there are also other ones used in more specific cases. For example, here’s a list of all aggregate functions in T-SQL. APPROX_COUNT_DISTINCT AVG, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING ,GROUPING_ID ,MAX ,MIN, STDEV, STDEVP, STRING_AGG, SUM, VAR, VARP</p><p>I hope this post was able to give you more confidence to start using different aggregate functions, or at least to have served as a quick refresher.</p><p>Thanks for reading, and have a great rest of your day! Keep coding.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b4fc7652f461" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Quick Look Into SQL Order Execution]]></title>
            <link>https://emilioeguia.medium.com/quick-look-into-sql-order-execution-3e384082a09d?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/3e384082a09d</guid>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Tue, 22 Nov 2022 20:05:21 GMT</pubDate>
            <atom:updated>2022-11-22T20:05:21.058Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h4><em>How to understand better the execution of your everyday code.</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5xrwjqBD91zp1qRp2clhsg.png" /></figure><p>Have you ever wondered how SQL executes the queries that you write? In fact, a particular order must be followed to execute it, even if we can’t see it. It is very different from the order we write the clauses, keep reading to find out why this order is very logical.</p><p>We will use this query as an example to illustrate the ordering:</p><pre>SELECT TOP (10) <br>    [Column1]<br>    ,SUM([Column2])<br>  FROM [Table1] t1<br>  JOIN [Table2] t2 ON t1.FK = t2.PK<br>  WHERE [Column1] = &#39;John&#39;<br>  GROUP BY [Column1]<br>  HAVING SUM([Column2])&gt; 100<br>  ORDER BY [Column1]</pre><p>The execution order for SQL queries is the following:</p><h3>1- FROM (and JOINS):</h3><p>First of all, SQL specifies the table on which the query needs to retrieve the data. If you have JOINS in your query, they will also be executed at this step. In our example, we specify Table1 and join it to Table2.</p><h3>2- WHERE:</h3><p>Secondly, we use the WHERE clause after the tables are joined to establish the working dataset and filter the records according to the conditions given in the query. In our example, we filter the results so that Column1 has the value ‘John’.</p><h3>3- GROUP BY:</h3><p>The third step is grouping the data we specified previously by breaking up the data into distinct chunks. These groups are under the criteria we defined. In this case we are grouping our records by the values in Column1.</p><h3>4- HAVING:</h3><p>Having is applied after GROUP BY because it uses a condition that is applied in GROUP BY, it can be used with aggregate functions. In our example, we want to filter our data where the Column2 SUM is higher than 100.</p><h3>5- SELECT:</h3><p>It is at this moment that our results are finally computed. In the case that you are using DISTINCT in your query, it will execute after right after the SELECT statement. Here you can specify all the columns that you want to be returned.</p><h3>6- ORDER BY:</h3><p>The next step after your data is selected, will be to order it under the rules you specified in your query. In the example above, we want to order by Column1.</p><h3>7- TOP:</h3><p>In the end, every row above the number we selected at TOP is not selected. Returning only the first 10 records in our example.</p><h3>Conclusion</h3><p>As we have seen, this order differs from the way we are used to writing our SQL queries. Here’s a quick diagram to showcase the differences.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/223/0*nPx4i5FVVgi2FeAe.png" /></figure><p>I hope that you have found this article useful and learned how SQL executes your code internally.</p><p>Have a great rest of your day!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3e384082a09d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ROW_NUMBER() vs RANK() vs DENSE_RANK()]]></title>
            <link>https://emilioeguia.medium.com/row-number-vs-rank-vs-dense-rank-aee0ebcec3de?source=rss-3b025a77231f------2</link>
            <guid isPermaLink="false">https://medium.com/p/aee0ebcec3de</guid>
            <category><![CDATA[function]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Emilio Eguia]]></dc:creator>
            <pubDate>Tue, 22 Nov 2022 01:06:12 GMT</pubDate>
            <atom:updated>2022-11-22T01:06:12.234Z</atom:updated>
            <content:encoded><![CDATA[<p>The RANK, DENSE_RANK and ROW_NUMBER functions are used to return an increasing integer number. All of these three functions require the ORDER BY clause to function in the way intended.</p><p>Before explaining how these functions work, let’s understand how they can be useful in different scenarios.</p><ul><li>Ranking top-selling brands by number of units sold</li><li>Ranking top product by revenue</li><li>Getting the most populated city in each state</li></ul><h4>Initial Overview</h4><p>The behavior and the way in which integers are assigned to each record changes when there are duplicate rows in the resulting table. Here we will have a quick look at what each function returns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/424/0*zCKD9owNacqGGuJ7" /></figure><h4>Similarities Between Functions</h4><p>The RANK, DENSE_RANK and ROW_NUMBER Functions have the following similarities:</p><ul><li>The three of them require an ORDER BY clause.</li><li>All of them return an increasing integer number with a base value of 1.</li><li>When combined with a PARTITION BY clause, these functions reset the returned integer value to 1 when the partition ends.</li><li>In the case of no duplicated values in the column used by the ORDER BY, these functions return the exact same output.</li></ul><h3>RANK Function</h3><p>The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause. For example, if you want to find the name of the employee with the highest salary, you can use RANK Function. Let’s see RANK Function in action:</p><pre>SELECT name, salary,<br>     RANK() OVER(ORDER BY salary DESC) AS &#39;Rank&#39;<br>FROM Employees</pre><p>An example of what this query could return would be the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/277/0*Cvzj8dc2nodTg2ZR" /></figure><h3>DENSE_RANK Function</h3><p>The DENSE_RANK function is similar to RANK function. However, the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.</p><pre>SELECT name, salary,<br>     DENSE_RANK() OVER(ORDER BY salary DESC) AS &#39;Dense Rank&#39;<br>  FROM Employees</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/295/0*VjH0CmQZRvW8syUi" /></figure><h3>ROW_NUMBER Function</h3><p>The ROW_NUMBER function has no relation with ranking. It just simply returns the row number of the sorted records. Even in the case when there are duplicate records in the column used in the ORDER BY, the ROW_NUMBER function will not display duplicate values.</p><pre>SELECT name, salary,<br>  ROW_NUMBER() OVER(ORDER BY salary DESC) AS &#39;Row Number&#39;<br>FROM Employees</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/296/0*eRvCjiE07Yw8kn_f" /></figure><h3>Common use for RANK / DENSE_RANK</h3><p>Both the RANK and DENSE_RANK function can be used in combination with the PARTITION BY clause. In that case, the rank will reset for each new partition generated.</p><p>In this case, let’s partition our data through he department. To get the rank of salaries dependent on each department.</p><pre>SELECT name, department, salary,<br>  RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS &#39;Rank&#39;<br>FROM Employees</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/351/0*woXvyK8cVgjPKk3F" /></figure><p>Now you can start using these functions in your code, and practice them on some data to solidify your knowledge. That’s it for this article, hope that you have learned something new!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aee0ebcec3de" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>