<?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 Deeksha Singh on Medium]]></title>
        <description><![CDATA[Stories by Deeksha Singh on Medium]]></description>
        <link>https://medium.com/@sdeeksha07?source=rss-a02fa3662993------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*e859jvyO56FctdejCfaReQ.jpeg</url>
            <title>Stories by Deeksha Singh on Medium</title>
            <link>https://medium.com/@sdeeksha07?source=rss-a02fa3662993------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 13:47:05 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sdeeksha07/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[SQL Interview Question8: Parent Child Relationship-Use Case of Self Join]]></title>
            <link>https://medium.com/geekculture/sql-interview-question8-parent-child-relationship-use-case-of-self-join-4fe88146dd85?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/4fe88146dd85</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Wed, 11 Jan 2023 16:38:00 GMT</pubDate>
            <atom:updated>2023-01-12T08:23:09.571Z</atom:updated>
            <content:encoded><![CDATA[<h4>#8 IN SQL INTERVIEW QUESTION SERIES</h4><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_MhvlhvUXF2ovYpl2UDWMw.png" /><figcaption>Credits: By Author</figcaption></figure><p>Hey there !!</p><p>I was recently getting interviewed for a growing startup for a product analyst role. In Tech-round they asked me this question. It involves multiple joins and conditions so it was confusing at first look. So I thought of sharing that here. From cover image you can see the problem statement or just let me right that here again,</p><blockquote><strong>Problem Statement:</strong> In given input table, “c” is son of “b” and “b” is son of “a”, so “c” became grandson of “a” and so on. The father’s name of grandfather is not mentioned. Write a query to find the relation between grandson and grandfather as shown in output table.</blockquote><p>Let’s see the solution,</p><p><strong><em>1.Creating a table,</em></strong></p><pre>Create table family_relation(<br>son varchar(50),<br>father varchar(50),<br>dead_or_alive varchar(50)<br>)</pre><p><strong><em>2.Inserting Values,</em></strong></p><pre>Insert into family_relation values(&#39;c&#39;,&#39;b&#39;,&#39;alive&#39;);<br>Insert into family_relation values(&#39;b&#39;,&#39;a&#39;,&#39;dead&#39;);<br>Insert into family_relation values(&#39;a&#39;,&#39;&#39;,&#39;alive&#39;);<br>Insert into family_relation values(&#39;g&#39;,&#39;f&#39;,&#39;alive&#39;);<br>Insert into family_relation values(&#39;f&#39;,&#39;e&#39;,&#39;alive&#39;);<br>Insert into family_relation values(&#39;e&#39;,&#39;&#39;,&#39;dead&#39;);</pre><p><strong><em>3.After select * query, we will get below input table,</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/849/1*H_CUGzo74Xd5J8g69pHcYA.png" /><figcaption>Credits: By author</figcaption></figure><p><strong><em>4.Writing query:</em></strong></p><p>Before that lets discuss approach. First thing, we need grandson and grandfather relation, so we need two joins. Also these joins will be self join so we need to give different alias to all tables. See below tables to see how its going to work.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*l6-yPoqUPdKxydK28wGUcg.png" /><figcaption>Credits: By Author</figcaption></figure><p>Here we will create one left join between <em>table a </em>and <em>table b (self join as tables are same) </em>and then left join with <em>table c</em>. Only highlighted part from image, we need in our output. So to get that we need to run below query,</p><pre>select a.son as grandson,<br>c.son as grandfather,c.dead_or_alive as dead_or_alive<br>from family_relation as a<br>left join family_relation as b on a.father=b.son<br>left join family_relation as c on b.father=c.son</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/832/1*ltkzEMaacVekQ73KsODdCA.png" /><figcaption>Credits: By Author</figcaption></figure><p>But we only need those records where grandfather is <strong><em>Not Null</em></strong> so putting that in where condition,</p><pre>select a.son as grandson,<br>c.son as grandfather,c.dead_or_alive as dead_or_alive<br>from family_relation as a<br>left join family_relation as b on a.father=b.son<br>left join family_relation as c on b.father=c.son<br>where c.son is NOT NULL</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/808/1*jCxSUH5ikEJ8cpDDKYFfmA.png" /><figcaption>Credits: By Author</figcaption></figure><p>This was all about this article for finding the relationship between grandson and grandfather.</p><p>For better understanding and somewhat similar kind of problem please visit this link of YouTube,</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FfYXaNRg6FrU%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DfYXaNRg6FrU&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FfYXaNRg6FrU%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/07c182cdb74af34f8001797db62f1687/href">https://medium.com/media/07c182cdb74af34f8001797db62f1687/href</a></iframe><p>I was asked this question in one of my recent interview. So hope it will help you somewhere as well.</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4fe88146dd85" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question8-parent-child-relationship-use-case-of-self-join-4fe88146dd85">SQL Interview Question8: Parent Child Relationship-Use Case of Self Join</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Question7: Interpreting results on the basis of different SQL Joins-Must Know…]]></title>
            <link>https://medium.com/geekculture/sql-interview-question7-interpreting-results-on-the-basis-of-different-sql-joins-must-know-bce8a6291cff?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/bce8a6291cff</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Wed, 30 Nov 2022 17:43:12 GMT</pubDate>
            <atom:updated>2022-12-01T11:13:39.494Z</atom:updated>
            <content:encoded><![CDATA[<h4>#7 IN SQL INTERVIEW QUESTION SERIES</h4><h3>SQL Interview Question7: Interpreting results on the basis of different SQL Joins-Must Know Fundamental Concepts</h3><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8NzR2oNjTAJl8wMjBB2zlQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>Hi Everyone !!</p><p>In one of my recent interviews, I got tested for basic fundamental understanding of SQL joins. Interviewer provided me two tables with only single column in each table and asked me to fill in the resulting output if we apply different kinds of joins in between these two tables. It was intimidating in beginning because of many duplicates &amp; NULL values but using the basic concepts of join, It got solved. So, in today’s article i thought of sharing that. See below problem statement:</p><blockquote><strong>Problem Statement: Given two tables table1 &amp; table2 with some duplicate &amp; NULL entries, what would be the output, if we apply different kind of joins in between them?</strong></blockquote><p><strong>Data: We have two tables table1 and table2 with only single column id. See select * query below:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/1*w7FihBgl-d65hbtL8YmNDA.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/513/1*XV9WLS9lcbC53n63AM72qQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>Now we will go through each join one by one and would try to understand resulting output and logic involved in each .</p><p><strong><em>1.Inner Join: </em></strong>It will return the matching row between two tables. See below snippet to understand how it is working and matching records from both the table,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/795/1*-cydSE_hssc3FaljJjjuXA.png" /><figcaption>Credits: By Author</figcaption></figure><blockquote>On running query, it will result in total 14 records. <strong><em>Don’t get confused with NULL matching in both the tables, because NULL is not defined so it cannot much with anything not even NULL.</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/688/1*qGczVozz3adElsSWkBdS8A.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong>2. Left Join:</strong> It will return all the matching records from both the tables, plus other records from the left table with NULL entry in right table if not found matching with the right table. On performing left join condition, total 16 records would return, output will be the all output obtained above in inner join with two other unmatching records in left table i.e., 3 &amp; NULL. Corresponding to these two records right table entry is NULL since they were not matching.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/1*B2eYYGqX7q5WNVBclszHlg.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/781/1*B8s3EBoWl3a801HjjSeT4Q.png" /><figcaption>Credits: By Author</figcaption></figure><p>3. <strong>Right Join:</strong> It will return all the matching records from both the tables with extra records from the right table with NULL entry in left table if not founding matching. So in this join total 17 records will be returned i.e., 14 with inner join plus right table have 3 NULLs, with NULL entries in left table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/697/1*peLCseWvNf7zImMqEY0Xcw.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/826/1*L2xm92FVL5H7tDDRCRYxXQ.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong>4.Cross Join: </strong>It will result in cross product of rows of two tables i.e., each row in table1 will be paired with each row in table2. Total number of records returned would be 9*9=81. Cross join never need ON condition for performing join operation. See below query,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/757/1*TNDoYrDRcnvSnr8D_viK8w.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong>5.Full Outer Join:</strong> Full join is a combination of both left and right join. On running query for this join condition, it will result in all the matching rows between two tables, with other rows from left &amp; right tables which don’t have matching records in both the tables. So for our data of table1 and table2,</p><blockquote><strong><em>Inner join results in 14 records</em></strong></blockquote><blockquote><strong><em>Left table have extra 2 values i.e., 3 &amp; NULL</em></strong></blockquote><blockquote><strong><em>Right table have extra 3 NULLs</em></strong></blockquote><blockquote><strong><em>So total records would be 14+2+3=19</em></strong></blockquote><p>See below query,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/913/1*dB71zjgO952IBnPmiBVazg.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong>6.Natural Join:</strong> In this join, Join happens on the basis of same column name and data types. It never needs ON condition to perform join operation. Also, it is not recommended to use much and doesn’t get supported by many RDBMS.</p><blockquote><strong>If the name of columns are same in both the tables, it will result similar result as inner join.</strong></blockquote><blockquote><strong>While if column names are not similar, it will results in output similar to cross join.</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*Z7ciCbW_12SZErf1rGuohg.png" /><figcaption>Credits: By Author</figcaption></figure><p>To check natural join with different column names, Let’s take a table3 with 2 records and a different column name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/513/1*7Z5DR4nVA9lObkimdDUWYQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>On performing Natural Join, a total of 9*2=18 rows will be obtained, similar to cross join.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/874/1*VdlDeT5MnUUaoeANXZErzg.png" /><figcaption>Credits: By Author</figcaption></figure><p>This was all about this article.You can find these queries on my GitHub Page link:</p><p><a href="https://github.com/sdeeksha07/SQL-Self-Join-Use-Cases/blob/main/SQL%20Join%20Condition">SQL-Self-Join-Use-Cases/SQL Join Condition at main · sdeeksha07/SQL-Self-Join-Use-Cases</a></p><p>For getting better understanding you can watch this tutorial on YouTube by Thoufiq.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FxUsY2jWQa1w%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DxUsY2jWQa1w&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FxUsY2jWQa1w%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/948c09048ab6e9dd7f227dcd7c4ec6cb/href">https://medium.com/media/948c09048ab6e9dd7f227dcd7c4ec6cb/href</a></iframe><p>I was asked this question in one of my recent interview. So hope it will help you somewhere as well.</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bce8a6291cff" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question7-interpreting-results-on-the-basis-of-different-sql-joins-must-know-bce8a6291cff">SQL Interview Question7: Interpreting results on the basis of different SQL Joins-Must Know…</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Question6: Finding Cumulative Sum — Use Case of SUM() OVER () Aggregate Window…]]></title>
            <link>https://medium.com/geekculture/sql-interview-question6-finding-cumulative-sum-use-case-of-sum-over-aggregate-window-dfe379d39f63?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/dfe379d39f63</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Wed, 30 Nov 2022 10:06:07 GMT</pubDate>
            <atom:updated>2022-12-01T11:14:20.719Z</atom:updated>
            <content:encoded><![CDATA[<h4>#6 IN SQL INTERVIEW QUESTION SERIES</h4><h3>SQL Interview Question6: Finding Cumulative Sum — Use Case of SUM() OVER () Aggregate Window Function</h3><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eGHkpxzlnzwfR6yBG1JgWw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/es/@ikukevk?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Kevin Ku</a> on <a href="https://unsplash.com/s/photos/database?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hey there !!</p><p>In last 2 articles of this series, we went through the use cases of window function for solving complex queries in easier way. Continuing that, today we will be solving one more query using the <strong><em>SUM() OVER() aggregate window function</em></strong>. Today’s problem statement is simple and it will be done in one query if we are clear with concepts of window function.</p><blockquote><strong>Problem Statement: We have data of employees with their salaries for 3 consecutive years. Find the cumulative sum of salaries of each employee by year.</strong></blockquote><p><strong>Data:</strong> I am using a table <em>basic_pays</em> having total 120 records, with columns <strong><em>employee_id, fiscal_year, salary</em> </strong>and running my queries on PostgreSQL. Below is snippet of <em>Select * </em>query from the the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/1*MenkMMUiyfoYwWgCLr6Hcg.png" /><figcaption>Credits: By Author</figcaption></figure><p>As intuitive it is, if we want total sum of salaries for each employees, we can use simple <strong><em>sum()</em></strong> as a aggregate function and grouped it for each employee using<strong><em> group by</em></strong> function,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/955/1*HT4t4QW-R4AFw92PYKivZQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>What sum function does here, it operated on set of 3 rows of 3 years and aggregated the output in a single row grouped by each employee.</p><p>But the problem statement is to find the cumulative sum of salaries not total salaries. So instead of aggregating all the salaries in one row, we want running total. Here using <strong><em>sum() function with over() clause as window function </em></strong>will do our job i.e., it won’t aggregate the output in one row. Let’s do that not mentioning anything in over() clause,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UO5DXBsaUk17kq884_JRaA.png" /></figure><p><strong><em>Since, we have not mentioned anything in over() clause</em></strong>, it is aggregating total salaries of 120 records and showing it along with the individual salaries. But we need cumulative sum, s<strong><em>o have to partition it by employees and arrange it in ascending order of year in order by clause</em></strong>. See below query for same,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IRVNZhgjpjERS-q4VkEOxw.png" /><figcaption>Credits: By Author</figcaption></figure><p>From highlighted part, we can see that for each employee, we have <em>cumulative_salary</em> by year in each row i.e., sum of current salary and previous total salary and so on.</p><p>So this was all about finding the running total. I was asked this question in one of my recent interview. So hope it will help you somewhere as well.</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dfe379d39f63" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question6-finding-cumulative-sum-use-case-of-sum-over-aggregate-window-dfe379d39f63">SQL Interview Question6: Finding Cumulative Sum — Use Case of SUM() OVER () Aggregate Window…</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Question5:Finding brands with increasing prices of laptops every year— Use Case of…]]></title>
            <link>https://medium.com/geekculture/sql-interview-question5-finding-brands-with-increasing-prices-of-laptops-every-year-use-case-of-a2b46b9c7692?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/a2b46b9c7692</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Tue, 29 Nov 2022 17:25:49 GMT</pubDate>
            <atom:updated>2022-11-30T17:55:41.437Z</atom:updated>
            <content:encoded><![CDATA[<h4>#5 IN SQL INTERVIEW QUESTION SERIES</h4><h3>SQL Interview Question5:Finding brands with increasing prices of laptops every year— Use Case of Lead() Window Function</h3><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kBjl3WUOHtV87sLVj3kavA.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@casparrubin?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Caspar Camille Rubin</a> on <a href="https://unsplash.com/s/photos/sql?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hi Everyone !!</p><p>Here we are with the next interview question in SQL Interview question series.</p><blockquote><strong>Problem Statement: Today’s problem statement is to find the brands of laptops which have increasing prices throughout all the years.</strong></blockquote><p><strong>Data:</strong> We have a table named <strong><em>laptops</em></strong> having 3 columns year, brand and price and total 16 records. Data is for 4 consecutive years, having 4 different brands with their prices. See below select * query to see its details:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/1*9E2ewp63aH2GOEcRzC2ivg.png" /><figcaption>Credits: By Author</figcaption></figure><p>Now, Since the problem is to find the increase in prices for next year. We should use <strong><em>lag()</em></strong> or <strong><em>lead()</em></strong> window function so that we can access data from preceding row or succeeding row in current row. Let’s go ahead with using <strong><em>lead()</em></strong> function to access succeeding row’s data in current row.Syntax for <strong><em>lead()</em></strong> function is,</p><pre>Lead( expression, offset, default) Over(PARTITION BY ……… ORDER BY......)<br><br>Expression: Value which we want to return<br>Offset: The number of rows next from current row, which we want to access. By default, it is 1.<br>dafault: When succeeding row is not defined, specified deafult will be returned. If not mentioned, NULL will be returned for the last row, since they don&#39;t have any succeeding rows.</pre><p>Creating a new column as <em>next_price using lead() function</em>,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EyWMFoS7euTUYooXQ91ilg.png" /><figcaption>Credits: By Author</figcaption></figure><p>Here, three things are happening:</p><ol><li><strong><em>partition by</em></strong> clause will first partition the data by each brands.</li><li><strong><em>Order by</em></strong> will order the data by date in ascending order.</li><li><strong><em>lead(price)</em></strong> would then populate the data from next row in current row.</li></ol><p>Since, for all brands, data for year 2021 have no succeeding row, so the <em>next_price</em> will be returned as NULL as nothing is mentioned in default values.</p><p>Lets, assume that for every brand next year prices (for year 2022) will be greater than 2021 prices. Let’s add just 1 in that and put that in default value,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pV4RSlJlHA0FYnmWWJ9rBA.png" /><figcaption>Credits: By Author</figcaption></figure><p>Next step would be to put this in CTE using <strong><em>with</em></strong> clause and fetch the difference between prices for each record in <strong><em>price_increase</em></strong> column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*evik-7xFY99_P4XK527ZVQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>Main task is to fetch only those brands whose price have always increased. For ex., for brand acer price got decreased from 2018 to 2019 and then increased so we need to neglect this brand while for HP prices have increased throughout 4 years so HP should be there in output.</p><p>Logic here is, we can create a flag of 0,1 assigning 0 to records where prices have decreased while 1 where prices have increased. Let’s do that using the <strong><em>case</em></strong> statement,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b7r53cS2qXmJO_bWSPvoMA.png" /><figcaption>Credits: By Author</figcaption></figure><p>Now to remove these brands (Note: we have to ignore whole brand having even single 0 flag) put condition in <strong><em>where</em></strong> clause,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kYvGOFT3K3bmWvXQmCVQnw.png" /></figure><p>We are left with only 2 brands here with 8 records. To see these brands,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2iVtX90rPxb3INWzN5tdJA.png" /><figcaption>Credits: By Author</figcaption></figure><p>So this was all about use of lead() function to see the increase in prices of laptops every year.</p><p>You can find these queries on my GitHub Page Link.</p><p><a href="https://github.com/sdeeksha07/SQL-LEAD-AND-LAG-FUNCTION-USE-CASES/tree/main">GitHub - sdeeksha07/SQL-LEAD-AND-LAG-FUNCTION-USE-CASES</a></p><p>Hope it will help you somewhere.</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a2b46b9c7692" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question5-finding-brands-with-increasing-prices-of-laptops-every-year-use-case-of-a2b46b9c7692">SQL Interview Question5:Finding brands with increasing prices of laptops every year— Use Case of…</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Question4: Finding Year On Year (YoY) increase in salaries of employees— Use Case of…]]></title>
            <link>https://medium.com/geekculture/sql-interview-question4-finding-year-on-year-yoy-increase-in-salaries-of-employees-use-case-of-7846315cc297?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/7846315cc297</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Tue, 29 Nov 2022 10:07:32 GMT</pubDate>
            <atom:updated>2022-11-30T17:56:42.061Z</atom:updated>
            <content:encoded><![CDATA[<h4>#4 IN SQL INTERVIEW QUESTION SERIES</h4><h3>SQL Interview Question4: Finding Year On Year (YoY) increase in salaries of employees— Use Case of Lag() Window Function</h3><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qRwAlesbs-vb5w7q82TX9Q.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@rubaitulazad?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Rubaitul Azad</a> on <a href="https://unsplash.com/s/photos/sql-query?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hey there !!</p><p>In my recent interview for product analyst position, I was asked lots of SQL question based on Window functions i.e., row_number(), rank(), dense_rank(), lead(), lag(), first_value(), last_value() etc. So, in today’s article I will be solving one question based on the use of lag() function and in next 3–4 articles I would be writing other questions as well. So, Lets jump directly in today’s problem statement.</p><blockquote><strong>Problem Statement: We have data of employees with their salaries for 3 consecutive years. Find YoY increase in salaries for each employees in percentage value.</strong></blockquote><p>Data: I am using a table <em>basic_pays</em> having total 120 records, with columns <strong><em>employee_id, fiscal_year, salary</em> </strong>and running my queries on PostgreSQL. Below is snippet of <em>Select * </em>query from the the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/726/1*YZgZZWY6CEerXwmeNyKVKw.png" /><figcaption>Credits: By Author</figcaption></figure><p>When we say YoY increase it means, How much current year salary got increased with respect to previous year salary. Below is formula for calculating the same.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/568/1*RYGD8TuVP906rn9dZtDM4w.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong><em>Now, for accessing the previous record in current row, we have to use lag() window function. Lag() function provides access to previous row data for specified physical offset to the current row. Syntax is ,</em></strong></p><pre>LAG( expression, offset, default) Over(PARTITION BY ……… ORDER BY......)<br><br>Expression: Value which we want to return<br>Offset: The number of rows back from current row, which we want to access. By default, it is 1.<br>dafault: When Preceding row is not defined, deafult will be returned. If not mentioned, NULL will be returned for the first row, since they don&#39;t have any preceeding rows.</pre><p>Lets implement that in SQL,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Cc2QYyOx5ZH93DyDVHFtJQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>In this Code, first <strong>partition by</strong> is diving the data into employees groups.</p><p><strong>Order by</strong> clause is sorting this data in ascending order of fiscal year.</p><p><strong>Lag()</strong> function is retrieving the previous year salary in current row in <strong>prev_salary</strong> column.</p><p><strong>Note: Since default value is not mentioned, and the first row of each employee have no preceding row, NULL is returned in first row for each employee. Also by default, offset is 1.</strong></p><p>Lets assume that for first year, previous salary was same as salary and there is no increase in salary i.e., 0% increase. <strong>So, populating salary in default value,</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vRVLcxHc4XXBrL78BhmNzQ.png" /><figcaption>Credits: By author</figcaption></figure><p>Notice that for first row, salary is same as prev_salary.</p><p>Now for finding YoY, Use a Common table expression (CTE) as salary_data and perform our calculation,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aEXdK4P33eIB25yHIT9mEw.png" /><figcaption>Credits: By Author</figcaption></figure><p>But per_increase with so many zero doesn’t seems good, so I would round it off &amp; also concatenate that with %ge value so that it is visible that its a percentage increase.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M3XVp1HW4uj2TPZhnDNksA.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5mEjp1y1CBiqKf70Wr4x4g.png" /><figcaption>Credits: By Author</figcaption></figure><p>That was all about using lag() window function for finding the YoY increase in salary. In same way, we can solve many questions if we are clear with concepts of usage of these functions.</p><p>Hope it will help you somewhere.</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7846315cc297" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question4-finding-year-on-year-yoy-increase-in-salaries-of-employees-use-case-of-7846315cc297">SQL Interview Question4: Finding Year On Year (YoY) increase in salaries of employees— Use Case of…</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Queston3: Questions based on difference between functionalities of commands]]></title>
            <link>https://medium.com/geekculture/sql-interview-questions-based-on-difference-between-functionalities-of-commands-b0bf2c6f76a5?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/b0bf2c6f76a5</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Thu, 13 Oct 2022 08:01:14 GMT</pubDate>
            <atom:updated>2022-11-30T17:57:06.817Z</atom:updated>
            <content:encoded><![CDATA[<h4>#3 IN SQL INTERVIEW QUESTION SERIES</h4><h4>Most asked SQL interview Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qRwAlesbs-vb5w7q82TX9Q.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@rubaitulazad?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Rubaitul Azad</a> on <a href="https://unsplash.com/s/photos/sql?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hi everyone !!</p><p>While getting interviewed during tech round in some companies, when I was solving SQL query on Notepad++, interviewer kept asking me what I was trying to do, what logic I am putting, what would happen if I will do this, for example why I am putting row_number() not rank() window function, what difference would be created in result output using both, why I am doing cross join not full outer join questions like that.</p><p>So, in todays article I will be writing 10 main questions asked on the basis of difference between functionalities of commands which seems similar but works in different way. These 10 basic differences are:</p><ol><li><strong>Difference between Alter &amp; Update Command?</strong></li><li><strong>Difference Between Drop, Delete &amp; Truncate?</strong></li><li><strong>Difference between Having &amp; where?</strong></li><li><strong>Difference Between Primary Key, Foreign Key &amp; Unique Key?</strong></li><li><strong>Difference between Full Outer Join &amp; Cross Join ?</strong></li><li><strong>Difference between Join &amp; Set Operations?</strong></li><li><strong>Difference between Union &amp; Union ALL ?</strong></li><li><strong>What is Union Compatibility in Query?</strong></li><li><strong>Difference Between DDL Commands &amp; DML Commands?</strong></li><li><strong>Difference between ROW_NUMBER(), RANK(), DENSE_RANK()?</strong></li></ol><p>Let’s see all one by one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/730/1*XhqheSIRpznsSD35mLd1bg.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/712/1*yChZXqFBXIsXL92msZWu2w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/744/1*Ub7ZUvOqjSDZi_dRCfzuLA.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/715/1*kyNQCSIyc76UEH5Nq_5c8g.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/712/1*WaErvx5TIqxIJkKK-yw4JQ.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*c5psaHwAdkgERk3o3j0WVw.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/727/1*qhQYpdnn9hrYLzqjwwOafw.png" /><figcaption>Credits: By author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/637/1*HCEO7vYgWk7AYziSWotEuA.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/711/1*_tTR5z_ehT7v5UwNR5GeSA.png" /><figcaption>Credits: By Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/1*6R_7asPingglssnEQF-rnQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>So, all these were the main differences which I had been asked in SQL interview. Hope it helps you to focus on concepts which are important from interview point of view.</p><p>Thanks for reading !!</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b0bf2c6f76a5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-questions-based-on-difference-between-functionalities-of-commands-b0bf2c6f76a5">SQL Interview Queston3: Questions based on difference between functionalities of commands</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Question2: Counting Number of Handshakes between group of peoples / Number of matches…]]></title>
            <link>https://medium.com/geekculture/sql-interview-question2-counting-number-of-handshakes-between-group-of-peoples-number-of-matches-537fd51a3d15?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/537fd51a3d15</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[sql-interview-questions]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Sun, 02 Oct 2022 15:22:44 GMT</pubDate>
            <atom:updated>2022-11-30T17:58:34.873Z</atom:updated>
            <content:encoded><![CDATA[<h4>#2 IN SQL INTERVIEW QUESTION SERIES</h4><h3>SQL Interview Question2: Counting Number of Handshakes between group of peoples / Number of matches between two teams — Use Case of Self Join &amp; Cross Join</h3><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YpFLpJaxkfSiCi57JAB8FQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@sunder_2k25?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Sunder Muthukumaran</a> on <a href="https://unsplash.com/s/photos/sql-query?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hey !!</p><p>In this article we will be looking into one of the favorite question of interviewers in SQL interview. I was also asked the same question in my Interview. So Let’s begin, but first look into the problem statement.</p><blockquote><strong>Problem Statement: To count the number of handshake between a group of people given in table:</strong></blockquote><blockquote><strong><em>Case1 : Write a query to count the number of handshakes such that one person handshake with other just once.</em></strong></blockquote><blockquote><strong><em>Case2: Write Query so that each one have handshake with other twice (Except With Oneself) .</em></strong></blockquote><p><strong><em>Data:</em></strong> I have created a table with firstname, last name of few of my friends in friends table. Except this no other information was provided. <a href="https://github.com/sdeeksha07/SQL-Self-Join-Use-Cases/tree/main">You Can also create the same table on your local machine using the query attached in GitHub link here.</a></p><p>Let’s begin querying this table. Select * statement will give two columns, i.e., firstname and lastname. There are total 10 rows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*O7Jcqj8VYbRSjfD2pqZkDA.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong>Solving Case1:</strong></p><p>As it was mentioned that one person handshake with another only once, for example Abhinav having handshake with vibha or vibha having handshake with Abhinav both are same thing. So we have to avoid that scenario.</p><p><strong>Step 1.1:</strong> For making this table simpler, first assign a unique serial number to all. We can do that by using <strong><em>row_number</em></strong> <strong>window function.</strong></p><p>Also<strong>, </strong>Since we are going to use this table two times in self join, we can assign it to a <strong><em>CTE (Common Table Expression)</em></strong> with help of <strong><em>With</em></strong> Clause. So from below query you can see , I have created a new table named as <strong><em>new_data</em></strong>, assigned serial number to each records as <strong><em>s_no </em></strong>and also two fields (firstname,lastname) are concatenated as <strong><em>full_name</em></strong>. <strong><em>||</em></strong> is used for concatenation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/931/1*GvmlZ7Qbh5KjWZ_0WKuI1Q.png" /></figure><p><strong>Step 1.2: </strong>As you know any join in two table are simply cross join putted with certain condition. So, Its good to start from here i.e., apply a cross join condition and then put some condition. Since we are using same table for self join , we need to give different aliases to them. See below query from the cross join. It will create 10*10= 100 rows combining all data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iS1dvDrSztpA-mF65DjnTA.png" /></figure><p>But person 1 handshaking with 1 doesn’t make sense, Right? We need to do something about it.</p><p><strong>Step 1.3:</strong> So lets put our condition here. See highlighted part in <strong><em>where</em></strong> clause in the below query. Considering only those rows where<strong><em> serial number in table 1 is less than serial number 2 will result in only one handshake</em></strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oLNLuaRTGFNGwkGV-l1yOg.png" /></figure><p><strong>Step 1.4:</strong> For counting the # of records, simply count(*) instead of fetching all columns from the table. It will return total 45 handshakes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3WGlxUloam3CnylXtIyuIw.png" /></figure><p>This was all for Case 1 problem statement.</p><p><strong>Solving Case 2:</strong> In this we have to consider all cases of handshake except oneself. So from <strong>step 1.2</strong> after cross join we have to just make a little change in where condition. See highlighted part in below query, We are considering all records where serial numbers are different in both the tables. &lt;&gt; is used for not equal.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VztjXiCNHNqzAFtpx2QS-g.png" /></figure><p>For counting the # of rows, as it returns total 90 rows, see below snippet.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1008/1*n17oCxq6J7By6SjvBLSQIQ.png" /></figure><p>So we have covered both the cases of query. Instead of taking name of my friends, you can create your own table of cricket or football team and count the number of matches between them. Logic for writing query would remail same.</p><p><a href="https://github.com/sdeeksha07/SQL-Self-Join-Use-Cases"><em>I am attaching .sql file on my Github page. You can find it on attached link.</em></a></p><p>Hope this article cleared your some concepts.</p><p>Thanks for giving it a read.</p><p>Adding to this, you can watch this video by Thoufiq on YouTube.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FaE623ff7zkM%3Fstart%3D1702%26feature%3Doembed%26start%3D1702&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DaE623ff7zkM&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FaE623ff7zkM%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/24bac0e0cc69b1f0292abfe6b6d8c24c/href">https://medium.com/media/24bac0e0cc69b1f0292abfe6b6d8c24c/href</a></iframe><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=537fd51a3d15" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question2-counting-number-of-handshakes-between-group-of-peoples-number-of-matches-537fd51a3d15">SQL Interview Question2: Counting Number of Handshakes between group of peoples / Number of matches…</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Interview Question 1: Grouping by expression which is not in existing columns of table.]]></title>
            <link>https://medium.com/geekculture/sql-interview-question-1-grouping-by-expression-which-is-not-in-existing-columns-of-table-900f13882b73?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/900f13882b73</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-interview-questions]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Sat, 24 Sep 2022 20:22:40 GMT</pubDate>
            <atom:updated>2022-11-30T17:58:04.704Z</atom:updated>
            <content:encoded><![CDATA[<h4>#1 in SQL interview Question Series</h4><h4>Most asked SQL interview queries &amp; Concepts #SQL #RDBMS</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oVtbHaKa7DTAGPy1TXZx-g.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@johnschno?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">John Schnobrich</a> on <a href="https://unsplash.com/s/photos/data-analysis?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hey there !!</p><p>SQL is must-have and most important tool for data analytics professionals. While getting interviewed for various roles and positions in analytics industry we all come across interviews testing our knowledge in SQL and proficiency in writing complex SQL queries as well as database design. So, I am planning to write articles about my interview experiences in SQL, queries I have been asked and queries most people come across while giving interviews.</p><p>Starting with the #1 article, I will be sharing one query to fetch the records from table by using grouping expression which is not present in columns of the table. You will get more idea while going through the article. So Let’s dive in,</p><p>I am using a table <em>basic_pays</em> having total 120 records, with columns <strong><em>employee_id, fiscal_year, salary</em> </strong>and running my queries on PostgreSQL. Below is snippet of <em>Select * </em>query from the the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1018/1*HCI52vsNGa8oI8-I2b90YQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>Now in this table, grouping records by column references are easier. For example, If we want to see <strong><em>minimum, maximum, average or total salaries</em> </strong>given to all employees in different<strong> <em>fiscal_year,</em> </strong>we can simply write below query<strong> using <em>aggregation function grouping it by fiscal_year.</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mFg0wAaW9AYrGbGhJxkHHQ.png" /><figcaption>Credits: By Author</figcaption></figure><p>But the twist is what if I was asked for diving my salaries into 3 category i.e., Low, Medium, High range and count the no. of employee getting salaries in these range. Let’s define range first,</p><pre><strong>Low_salary:</strong> salary ≤ 5000<br><strong>Medium_salary:</strong> 5000 &lt;salary ≤ 15000<br><strong>High_salary:</strong> salary≥15000</pre><p><strong><em>For these types of questions, first we need to create the required expressions and then apply it in group by clause. For creating expression we can have multiple method.</em></strong></p><p><strong>1.Method 1:</strong> In first method, we are creating column expression <strong><em>salary_grp</em></strong> by using <strong>CASE</strong> statement in <strong>SELECT</strong> statement itself and pulling in our required salary range and then on basis of that we are grouping the result to find the count of employees in each salary range by using that <em>salary_grp</em> in group by clause. See below query to get idea about that,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1008/1*zLY5AsjgSX2y-MJJxXW-gw.png" /><figcaption>By Author</figcaption></figure><p>But some databases don’t provide this functionality i.e., they never allow aliases expression like <strong><em>salary_grp</em></strong> in group by clause directly. So in that case, we need to copy the same expression used in select statement to the group by clause and it would work in same way and would result in employee count for three salary range. Below is the snippet of same,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zh_r3pYfA8hJ3fpwY4PFfQ.png" /><figcaption>Credits: By Author</figcaption></figure><p><strong>2. Method 2:</strong> Using <strong>Common table expression (CTE) </strong>with help of <strong><em>with</em></strong> clause. First we are creating a table <em>salary_data</em> by help of <strong><em>with</em></strong> clause and using its output table <em>salary_data</em> to fetch our desired result. Below is the code snippet,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3BZd5bjPEkatjnQ6vr6W3A.png" /><figcaption>Credits: By author</figcaption></figure><p>You can find all these queries on my GitHub Page:</p><p><a href="https://github.com/sdeeksha07/SQL-Case-Statement/tree/main">https://github.com/sdeeksha07/SQL-Case-Statement/tree/main</a></p><p>You can try out these queries by yourself. Hope this article have clear some of your concepts.</p><p>Thanks for reading !!</p><p>For any doubt reach out to me on LinkedIn or Twitter or Email.</p><p><strong><em>Email: sdeevi07@gmail.com</em></strong></p><p><strong><em>Twitter: </em></strong><a href="https://twitter.com/sdeeksha07"><strong><em>https://twitter.com/sdeeksha07</em></strong></a></p><p><strong><em>Linkedin:</em></strong><a href="https://www.linkedin.com/in/sdeeksha07/"><strong><em>https://www.linkedin.com/in/sdeeksha07/</em></strong></a></p><p><strong>Keep learning, Keep practicing !!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=900f13882b73" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/sql-interview-question-1-grouping-by-expression-which-is-not-in-existing-columns-of-table-900f13882b73">SQL Interview Question 1: Grouping by expression which is not in existing columns of table.</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[2. String & Date Functions in SQL]]></title>
            <link>https://medium.com/geekculture/2-string-date-functions-in-sql-4085ea9c6ffe?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/4085ea9c6ffe</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analyst]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Thu, 18 Aug 2022 07:33:30 GMT</pubDate>
            <atom:updated>2022-08-19T05:49:07.001Z</atom:updated>
            <content:encoded><![CDATA[<h4>Text data, VARCHAR, DATE, DATETIME in SQL</h4><h4>Understanding the functioning of built-in String and Date functions in SQL</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wV0ZQqz_-rBw3ewFebFBtQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@kmuza?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Carlos Muza</a> on <a href="https://unsplash.com/s/photos/data-analytics?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>A person who is dealing with data in one way or other in their day to day job life knows the pain of converting the unformatted, clumsy data received by various stakeholders into useful insights &amp; KPIs. The data received need some amount of cleaning, changing data types &amp; formatting etc. according to requirements. When these data types are in Character/text/Date format, these in-built functions (String functions &amp; date functions) comes handy. So, following the first article on <a href="https://medium.com/@sdeeksha07/1-comparison-aggregation-functions-in-sql-934d6ec76bff">comparison and aggregation functions in SQL</a> , in this article we will be looking into the most used string &amp; date functions which makes our life (Obviously for all data analyst) much easier.</p><p><strong>String Functions:</strong> It facilitates manipulation of string data by taking some input and converting it into string data types. Various built-in string functions used for manipulating data effectively are,</p><ol><li><strong>STR(</strong><em>string</em><strong>):</strong> Returns a number as string.</li><li><strong>LENGTH(</strong><em>string</em><strong>) / LEN(</strong><em>string</em><strong>):</strong> For finding the length of the string.</li><li><strong>CONCAT(</strong><em>string1, string2, …., string_n</em><strong>):</strong> For concatenating two or more strings together. <strong>|| or + also </strong>used for concatenation as well.</li><li><strong>CONCAT_WS(</strong><em>separator, string1, string2, …., string_n</em><strong>):</strong> Concatenate two or more strings together with a separator.</li><li><strong>LOWER(</strong>string<strong>):</strong> Used for converting the whole string in lower case.</li><li><strong>UPPER(</strong>string<strong>):</strong> Used for making the whole string in upper case.</li><li><strong>REPLACE(</strong><em>string, old_string, new_string</em><strong>):</strong> For replacing occurrences of substring from a string and replacing that with a new string.</li><li><strong>REVERSE(</strong>string<strong>):</strong> Result in string with reverse order.</li><li><strong>SUBSTRING(</strong><em>string</em>, <em>starting position</em>, <em>length</em><strong>)/SUBSTR(</strong><em>string</em>, <em>starting position</em>, <em>length</em><strong>):</strong> This is for extracting a part of string from whole string by specifying its position.</li><li><strong>LEFT(</strong><em>string, # of chars</em><strong>):</strong> For selecting &amp; extracting a substring or number of characters of given size from left.</li><li><strong>RIGHT(</strong><em>string, # of chars</em><strong>):</strong> For selecting &amp; extracting a substring or number of characters of given size from right.</li><li><strong>TRIM(</strong>string<strong>):</strong> For removing leading &amp; trailing spaces from a string as well as other specified string characters.</li><li><strong>RTRIM(</strong>string<strong>):</strong> Removes only trailing spaces from the string.</li><li><strong>LTRIM(</strong>string<strong>):</strong> Removes only leading spaces from the string.</li><li><strong>REPEAT(</strong>string,count<strong>)/REPLICATE(): </strong>Returns a string repeated by number of times specified by count.</li><li><strong>SPACE(</strong>N<strong>):</strong> Returns a empty string with N specified length.</li><li><strong>STRCMP(</strong>string1,string2<strong>):</strong>This function compares two strings and returns 0 if both are equal, 1 if string1&gt; string 2, -1 if string1&lt;string2.</li></ol><p>So these were some of the most used string functions for data manipulation, there may be some more but I have mostly came across these functions only in analytics industry and found it useful.</p><p>Now we will be exploring one of the most used functions i.e., date functions used in SQL.</p><p><strong>Date Functions:</strong> These functions facilitates manipulation of date and time data effectively. Below are the date functions which are used extensively, but before that I would like to mention term dateparts in a date, time &amp; datetime functions which would be common for all functions. Various valid dateparts are:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/265/1*D1e6cEd9Qzjy2lDdJoohDg.jpeg" /></figure><p>For structuring the dates in required format, below abbreviations can be used in all cases:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/655/1*B27_WG7dduQViFNCVdyAHA.jpeg" /></figure><ol><li><strong>CURRENT_DATE:</strong> Results in system’s current date.</li><li><strong>CURRENT_TIME</strong>: Results in system’s current time.</li><li><strong>CURRENT_TIMESTAMP/GETDATE()/NOW():</strong> Returns the current date and time of the system, in a <em>‘yyyy-mm-dd hh:mm:ss.mmm’ </em>format.</li><li><strong>DATEDIFF(</strong><em>datepart, date1, date2</em><strong>):</strong> Returns the difference between two dates.</li><li><strong>DATEADD(</strong>d<em>atepart , number , date</em><strong>):</strong> Adds Date/time interval to date &amp; returns the date. <em>datepart</em> here is ,the part of date to which an interval is added. <em>number </em>represents the interval to be added<strong><em>(positive number for finding the future date while negative numbers for finding the past date).</em></strong> <em>date</em> is the actual date which need to be modified.</li><li><strong>DATEPART(</strong><em>datepart, date</em><strong>):</strong> Returns specific part of date(datepart) in integer format. For ex, <strong>DATEPART(</strong>year<em>, ‘2019–10–02’</em><strong>) </strong>results 2019 in integer datatype.</li><li><strong>DATENAME(</strong><em>datepart, date</em><strong>): </strong>Returns specific part of date(datepart) in form of string value. For ex, <strong>DATEPART(</strong>month<em>, ‘2019–10–02’</em><strong>) </strong>results October in string format.</li><li><strong>DAY(</strong><em>date</em><strong>)/MONTH(</strong><em>date</em><strong>)/YEAR(</strong><em>date</em><strong>):</strong>Results in day/month/year of date respectively.</li><li><strong>ISDATE(</strong><em>date</em><strong>): </strong>This function checks and validate the given expression. If it returns 1, means its a valid date and if 0 means its not a valid date.</li><li><strong>DATEFROMPARTS(</strong><em>year</em>, <em>month</em>, <em>day</em><strong>): </strong>Returns date from the mentioned parts(year,month,day).</li><li><strong>EXTRACT(</strong><em>datepart</em> <strong>FROM</strong> <em>date</em><strong>): </strong>For extracting specific part of date and time(year,month,day,hour,min etc.).</li><li><strong>Convert Date to string:</strong> For converting date to string data type we can use <strong>CAST(</strong><em>date </em><strong>AS</strong> <em>string</em><strong>)</strong> or we can we use function <strong>TO_CHAR(</strong><em>date,format</em><strong>).</strong></li><li><strong>Convert string to date: TO_DATE(<em>string, format</em>)</strong> will convert a string into date as in specified format if string have date value and it is convertible in date format. <strong>CAST(</strong><em>string </em><strong><em>AS</em></strong><em> date</em><strong>)</strong> can also convert string into date.</li></ol><p>So, this was all about most used String &amp; date function. The beauty of SQL is that even after knowing these functions we need practice to make maximum use of it. So keep practicing !!</p><p>If you are beginner, you can reach out to me for understanding requirements in data analytics industry as well as for the <em>referrals</em>. I would be happy to share &amp; help.</p><p>References used in structuring this article:</p><ul><li><a href="https://www.sqltutorial.org/sql-functions/">SQL Functions</a></li><li><a href="https://www.javatpoint.com/sql-tutorial">Learn SQL Tutorial - javatpoint</a></li><li><a href="https://www.w3schools.com/sql/func_sqlserver_current_timestamp.asp">SQL Server CURRENT_TIMESTAMP Function</a></li><li><a href="https://www.geeksforgeeks.org/sql-date-functions-set-1/?ref=lbp">SQL | Date Functions (Set-1) - GeeksforGeeks</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4085ea9c6ffe" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/2-string-date-functions-in-sql-4085ea9c6ffe">2. String &amp; Date Functions in SQL</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1. Comparison & Aggregation Functions in SQL]]></title>
            <link>https://medium.com/geekculture/1-comparison-aggregation-functions-in-sql-934d6ec76bff?source=rss-a02fa3662993------2</link>
            <guid isPermaLink="false">https://medium.com/p/934d6ec76bff</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analyst]]></category>
            <dc:creator><![CDATA[Deeksha Singh]]></dc:creator>
            <pubDate>Fri, 12 Aug 2022 19:01:22 GMT</pubDate>
            <atom:updated>2022-08-13T07:12:44.587Z</atom:updated>
            <content:encoded><![CDATA[<h4>Understanding SQL NULL and aggregation function</h4><h4>NVL,COALESCE,DECODE,NULLIF,NVL2,AVG,SUM,MIN,MAX,COUNT</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t89oKKYOBQCrUTiamaBWZA.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@pathdigital?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">path digital</a> on <a href="https://unsplash.com/s/photos/data-analytics?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Hey there !!</p><p>I was just revising the important functions used in SQL queries while working on ETL process for my office work. So I thought, why not to write a brief article or handy notes for reference in future. Also these are few of important SQL questions asked in the interviews. So, in next 3 articles, I will be writing about the 6 main category of functions used in SQL queries. These categories are basically-</p><p>1- SQL Comparison functions</p><p>2- SQL Aggregate functions</p><p>3- SQL String functions</p><p>4- SQL Date function</p><p>5- SQL Math functions</p><p>6- SQL window functions.</p><p>This article would be limited to first two built-in functions i.e., Comparison &amp; aggregate functions. Also, as I mentioned this will be kind of short handy notes, so I will keep it short and will be linking the external material which I found best for these concepts. So lets see one by one,</p><p><strong>SQL Comparison function:</strong> These are single row functions(gives one result per row) can be used with any data type (date, character and number) and mostly pertains to use of NULL values in the database table. Mostly used comparison functions are <strong>NVL, COALESCE, NULLIF, NVL2 &amp; DECODE.</strong></p><p><strong>1. NVL(expr1, expr2):</strong> It converts a null value to an actual value. In syntax <strong>NVL(expr1, expr2),expr1</strong> is the source value or expression that <strong>may</strong> contain a null, <strong>expr2</strong> is the target value for converting/replacing the null value if found <strong>null in expr1</strong>.Syntax is,</p><pre><strong>NVL(expr1,expr2)</strong></pre><p><strong>2. COALESCE():</strong> This function returns first <strong>NON-NULL</strong> expression in the list. It first examines the first expression from left to right, if expression found not null then it returns that expression else it will evaluate until it finds first <strong>non-null</strong> argument. All the remaining expression after NON-NULL expression won’t be evaluated at all.</p><p>COALESCE() function has advantage over the NVL() function i.e., the COALESCE function can take multiple alternate values/expression. And it will return Null only if all the expression in list are Null. Syntax,</p><pre><strong>COALESCE (expr1, expr2, expr3.............. exprN)</strong></pre><p><strong>3.NULLIF(expr1, expr2):</strong><a href="https://www.sqltutorial.org/sql-comparison-functions/sql-nullif/"> It is a control flow function, that test both the expression and return either NULL or first expression based on evaluation of result. It compares the two expression, and if both the expressions are equal then it will return NULL, and if not then it will return first expression. Syntax,</a></p><pre><strong>NULLIF (expr1, expr2)</strong></pre><p><strong>4. NVL2(expr1, expr2, expr3)</strong> : It examines the first expression and if expr1(first expression) is not null, then NVL2 would returns expr2(second expression) whereas If expr1 is null, then NVL2 would return expr3.<strong> </strong>The argument expr1 can have any data type. Syntax,</p><pre><strong>NVL2 (expr1, expr2, expr3)</strong></pre><p><strong>5.Decode: </strong><a href="https://www.sqltutorial.org/sql-comparison-functions/sql-decode/">This function facilitates decoding an expression in way similar to IF-THEN-ELSE logical condition or CASE statement . This function decodes expression after comparing it to each search value.</a></p><p><a href="https://www.sqltutorial.org/sql-comparison-functions/sql-decode/">If the expression is the same as search value, result is returned. But if the search value does not match with any of the result values then it will return default value while it will return NULL, if default value is not mentioned. Syntax is,</a></p><pre><strong>DECODE(column|expression,</strong> <strong>search1</strong>,<strong> result1</strong> , <strong>search2</strong>, <strong>result2</strong>,.......<strong>searchN</strong>, <strong>resultN, [</strong>, <strong>default]</strong>)</pre><p>So, these 5 were the most used NULL functions and now we will be looking into the aggregation functions. Let’s see one by one.</p><p><strong>SQL Aggregate functions:</strong> These functions takes a set of values and returns a single value. Since, it operates on set of values, it is mostly used with GROUP BY clause . A general syntax for performing aggregate function is :</p><pre><strong>SELECT</strong> c1, aggregate_fun (c2) <br><strong>FROM</strong> table<br><strong>GROUP</strong> BY c1</pre><p>The most commonly used aggregate functions are <strong>AVG()</strong>,<strong> COUNT()</strong>, <strong>MAX()</strong>, <strong>MIN()</strong>, <strong>SUM()</strong>. Except Count() function all other functions ignore the NULL values. Understanding the working of these functions are self explanatory, it is just about understanding the business requirements and using the right syntax. Lets have a brief walkthrough of these functions.</p><p><strong>1.AVG():</strong> It returns average values in set. Syntax for finding on set of values is <strong>AVG(All | Distinct)</strong> . By default, ALL is chosen which includes duplicate values as well, while for finding average of distinct values only, we use keyword Distinct. Example showing calculations for average salary of each department from employee table.</p><pre><strong>SELECT</strong> department_name, <strong>RROUND</strong>(<strong>AVG</strong>(Salary),0) <strong>AS</strong> avg_salary<br><strong>FROM</strong><br>employees<br><strong>GROUP BY</strong> department_name<br><strong>ORDER BY </strong>department_name</pre><pre><em>Output:</em><br><strong><em>department_name</em>      <em>avg_salary</em></strong><em><br>Accounting           10800<br>Administration       20333<br>Executive            14500</em></pre><pre><strong>Note: ROUND</strong> Function have round-off the avg_salary to Zero decimal Places.</pre><p><strong>2.COUNT(): </strong>This function returns the number of items in set. Syntax for Counting the values are COUNT(* | DISTINCT) . * is used to count all the values(duplicates as well), while Distinct forces only to Count distinct values present in the set. Example for finding the headcounts in each department in employee table:</p><pre><strong>SELECT</strong> department_name, <strong>COUNT</strong>(*) <strong>AS</strong> num_employee<br><strong>FROM</strong><br>employees<br><strong>GROUP BY</strong> department_name<br><strong>ORDER BY </strong>department_name</pre><pre><em>Output:</em><br><strong><em>department_name</em>      <em>num_employee</em></strong><em><br>Accounting           5<br>Administration       2<br>Executive            6<br>Finance              5<br>IT                   7<br>Marketing            3<br>Sales                6</em></pre><p><strong>3.MAX():</strong> This function returns the max value from the set. Example for finding highest salary of employee in each department from employee table.</p><pre><strong>SELECT</strong> department_name, <strong>MAX</strong>(Salary) <strong>AS</strong> max_salary<br><strong>FROM </strong>employees <strong>GROUP BY</strong> department_name <strong>ORDER BY </strong>department_name</pre><pre><em>Output:</em><br><strong><em>department_name</em>      max<em>_salary</em></strong><em><br>Accounting           17000<br>Administration       6000<br>Executive            5000<br>Finance              2500</em></pre><p><strong>4.MIN():</strong> Similar to Max Function, MIN() function is used to return the minimum value / lowest value from set of values.</p><p><strong>5.SUM():</strong> It returns the sum of all values in the set. Syntax is SUM ( ALL |DISTINCT) . Finding total salary of all employee in the employee table grouped by department,</p><pre><strong>SELECT</strong> department_name, <strong>SUM</strong>(Salary) <strong>AS</strong> total_salary<br><strong>FROM </strong>employees <strong>GROUP BY</strong> department_name <strong>ORDER BY </strong>department_name</pre><pre><em>Output:</em><br><strong><em>department_name</em>      <em>total_salary</em></strong><em><br>Accounting           59500<br>Administration       27800<br>Executive            57600<br>Finance              24600</em></pre><p>So, this was all about most commonly used SQL aggregate function.</p><p>In next article, we will be looking into SQL String functions, SQL Date function and Math functions Functions.</p><p>Keep practicing !!</p><p>Thankyou for reading !!</p><p>References:</p><ul><li><a href="https://www.sqltutorial.org/">SQL Tutorial - Essential SQL For The Beginners</a></li><li><a href="https://www.geeksforgeeks.org/sql-tutorial/?ref=ghm">SQL Tutorial - GeeksforGeeks</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=934d6ec76bff" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/1-comparison-aggregation-functions-in-sql-934d6ec76bff">1. Comparison &amp; Aggregation Functions in SQL</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>