{"id":1948,"date":"2022-06-17T10:15:38","date_gmt":"2022-06-17T05:15:38","guid":{"rendered":"https:\/\/testing.dicecamp.com\/insights\/?p=1948"},"modified":"2022-06-21T10:38:38","modified_gmt":"2022-06-21T05:38:38","slug":"6-sql-statements-for-advance-data-analytics-advance-sql","status":"publish","type":"post","link":"https:\/\/testing.dicecamp.com\/insights\/6-sql-statements-for-advance-data-analytics-advance-sql\/","title":{"rendered":"6 advance SQL statements for Data Analytics (Advance SQL)"},"content":{"rendered":"\n

SQL is by far the most widely accepted programming language across the data analytics industry. The 40 year old language is well documented and is very easy to learn. <\/p>\n\n\n\n

In this article, we look at 6 advance SQL statements with examples that will help you to manipulate data in a more flexible and powerful way.  <\/p>\n\n\n\n

IMPORTANT<\/strong>: All examples provided in this article have been tested on our custom database \u2018Dice\u2019 that consists of two tables; \u2018Employee\u2019 and \u2018Department\u2019. These tables are shown as follows:<\/p>\n\n\n

\n
\"\"
Database used for examples: There are two tables; Employee (TOP), and Department (Bottom)<\/em><\/figcaption><\/figure>\n<\/div>\n\n\n

Now let’s see the SQL statements one by one.<\/p>\n\n\n\n

Table of Contents<\/h2>\n\n\n\n
  1. SQL JOIN<\/a><\/li>
  2. SQL Aggregate Functions<\/a><\/li>
  3. SQL GROUP BY<\/a><\/li>
  4. SQL OVER ()<\/a><\/li>
  5. SQL PARTITION BY<\/a><\/li>
  6. SQL ORDER BY<\/a><\/li><\/ol>\n\n\n\n

    SQL JOIN<\/h1>\n\n\n\n

    Oftentimes while manipulating data we want to combine information from two tables. This is done using SQL JOIN statements.  <\/p>\n\n\n\n

    For two tables to join they must contain a common column. <\/p>\n\n\n\n

    Practical Tip<\/strong>: In the modeling stage of a database, a data modeler puts a unique column of one table (the primary key; pk) in the other table (a foreign key; fk) to enable join in the later stage of data engineering. <\/p>\n\n\n\n

    There are a number of ways we can achieve \u2018join\u2019 operation between tables.<\/p>\n\n\n\n

    INNER JOIN<\/h2>\n\n\n\n

    Inner join is by far the most simple join and combines two tables while displaying only the rows that match as per the joining criteria. <\/p>\n\n\n\n

    All unmatched values are discarded in the inner join.<\/p>\n\n\n\n

    Inner join works by sequentially matching a row of one table with all rows of the other table as per the joining criteria.<\/p>\n\n\n\n

    Syntax:<\/strong> INNER JOIN column_name<\/em><\/p>\n\n\n\n

    ON table_1.pk = table_2.fk;<\/p>\n\n\n\n

    Where the ON<\/em> clause represents the joining criteria (often referred to as the stitching criteria) and specifies those columns that are to be matched in both tables. Here the columns are pk (primary key) and fk (foreign key) residing in both tables respectively.<\/p>\n\n\n\n

    As an application of inner join, consider the following example. <\/p>\n\n\n\n

    We want to filter employees from the employee table whose departments demonstrated excellent performance. We have a department table that carries the highest performing department names. <\/p>\n\n\n\n

    We can use inner joins to filter out the employees from the employee table using the following query.<\/p>\n\n\n\n

    SELECT <\/strong>d.dept_name, e.employee_name, e.dept_id <\/strong><\/p>\n\n\n\n

    FROM <\/strong>employee e <\/strong><\/p>\n\n\n\n

    JOIN <\/strong>department d<\/p>\n\n\n\n

    ON <\/strong>e.dept_id = d.dept_id;<\/p>\n\n\n

    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    Here<\/em> \u2018dept_id\u2019 is the primary key in ’employee’ and resides as foreign key in ‘department’. <\/p>\n\n\n\n

    Now, what if a data analyst wants to view the rest of the employees from other departments alongside as well? <\/strong><\/p>\n\n\n\n

    SQL Left joins come in handy for this!<\/p>\n\n\n\n

    LEFT JOIN\/RIGHT JOIN<\/h2>\n\n\n\n

    A left join will output all rows of the first table (left table) but only matched values from the second table (as per the joining criteria).<\/p>\n\n\n\n

    In the resulting table you might see \u2018null values\u2019 that specify the unincluded values from the second table.<\/p>\n\n\n\n

    Syntax:<\/strong> LEFT JOIN column_name<\/em>\/ RIGHT JOIN column_name<\/em><\/p>\n\n\n\n

    ON table_1.pk = table_2.fk;<\/p>\n\n\n\n

    We use the following query to view both; employees from the highest performing department and the rest of the employees in the employee table.<\/p>\n\n\n\n

    SELECT<\/strong> *<\/p>\n\n\n\n

    FROM<\/strong> employee e <\/p>\n\n\n\n

    LEFT<\/strong> JOIN<\/strong> department d<\/p>\n\n\n\n

    ON<\/strong> e.dept_id = d.dept_id;<\/strong><\/p>\n\n\n

    \n
    \"SQL<\/figure>\n<\/div>\n\n\n

    IMPORTANT: <\/strong>Total rows in left join = total matched rows + (unmatched rows from first table)<\/p>\n\n\n\n

    Similarly, a right join will keep all rows from the second table (right table) but will only keep matched values from the first table.<\/p>\n\n\n\n

    Total rows in right join = total matched rows + (unmatched rows from second table). <\/p>\n\n\n\n

    SELECT<\/strong> *<\/p>\n\n\n\n

    FROM<\/strong> employee e <\/p>\n\n\n\n

    RIGHT<\/strong> JOIN<\/strong> department d<\/p>\n\n\n\n

    ON<\/strong> e.dept_id = d.dept_id;<\/strong><\/p>\n\n\n

    \n
    \"SQL<\/figure>\n<\/div>\n\n\n

    CROSS JOIN<\/h2>\n\n\n\n

    Cross join is a \u2018notorious\u2019 operation. It takes a lot of computational power and memory to generate and store output. We avoid its use in practical scenarios.<\/p>\n\n\n\n

    A cross join simply combines each row of one table with all rows of the other table (similar to a \u2018combination operation\u2019 in mathematics). There\u2019s no stitching criteria because we are simply making combinations between two tables. <\/p>\n\n\n\n

    The resulting table is a huge table that carries all possible combinations of data elements. <\/p>\n\n\n\n

    Syntax:<\/strong> CROSS JOIN column_name<\/em>.<\/p>\n\n\n\n

    In our database, the employee table has a total of 8 rows and the dept table carries 4 rows. The cross join operation results in the number of rows = 8×4 =32.<\/p>\n\n\n\n

    SELECT<\/strong> *<\/p>\n\n\n\n

    FROM<\/strong> employee e <\/p>\n\n\n\n

    CROSS<\/strong> JOIN<\/strong> department d;<\/strong><\/p>\n\n\n

    \n
    \"SQL<\/figure>\n<\/div>\n\n\n

    SQL Aggregate Functions<\/h1>\n\n\n\n

    SQL aggregate functions perform mathematical operations on data.<\/p>\n\n\n\n

    There are five number of aggregate functions; COUNT(), SUM(), MAX(), MIN(), and AVG(). <\/p>\n\n\n\n

    Syntax:<\/strong> SELECT aggregate function(column_name<\/em>) FROM table_name<\/em><\/p>\n\n\n\n

    Aggregate functions are used within the SELECT statement.<\/p>\n\n\n\n

    It\u2019s worth noting here, each aggregate function outputs a single value against the whole column. Due to this particular reason we can\u2019t query a column when using an aggregate function. <\/p>\n\n\n\n

    As we would see later in the article, the window aggregate functions are more useful when querying columns alongside aggregate results.<\/p>\n\n\n\n

    SQL GROUP BY<\/h1>\n\n\n\n

    It\u2019s an immensely useful function that offers frequent support in the daily operations of a data analyst. <\/p>\n\n\n\n

    GROUP BY arranges identical data of a column into groups and is used in combination with SELECT clause and Aggregate functions. <\/p>\n\n\n\n

    Syntax:<\/strong> GROUP BY(column_name<\/em>).<\/p>\n\n\n\n

    Use GROUP BY on a column to group similar data and then apply aggregate functions over another column to aggregate the data within each group.<\/p>\n\n\n\n

    For example, we want to view the average salary within each department.<\/p>\n\n\n\n

    SELECT <\/strong>position_, AVG(<\/strong>salary) FROM <\/strong>employee<\/p>\n\n\n\n

    GROUP BY(<\/strong>position_);<\/strong><\/p>\n\n\n

    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    The position_ column is grouped and reduced to two rows, and for each group an average function is applied.<\/p>\n\n\n\n

    Practical tip:<\/strong> Use GROUP BY after WHERE clause but before ORDER BY or LIMIT.<\/p>\n\n\n\n

    Disadvantage:<\/strong> GROUP BY reduces the number of rows in the resulting table and therefore you cannot SELECT original columns of the table when you apply GROUP BY.<\/p>\n\n\n\n

    This drawback of GROUP BY leads us to the \u2018window functions in SQL\u2019; OVER().   <\/p>\n\n\n\n

    SQL OVER()<\/h1>\n\n\n\n

    A window aggregate function is defined as \u201cthe one which allows aggregate functions to output a result against each window (row) \u201d. <\/p>\n\n\n\n

    OVER() is a useful window function and commonly used with aggregate functions. It offers the flexibility to display the aggregated results against each row of the original table. <\/p>\n\n\n\n

    Syntax:<\/strong> SELECT aggregate function(column_name<\/em>) OVER() FROM table_name<\/em><\/p>\n\n\n\n

    Note that OVER() is used within the SELECT clause.<\/p>\n\n\n\n

    Example:<\/p>\n\n\n\n

    SELECT<\/strong> employee_name, SUM<\/strong>(salary) OVER() <\/strong><\/p>\n\n\n\n

    FROM<\/strong> employee;<\/p>\n\n\n

    \n
    \"SQL<\/figure>\n<\/div>\n\n\n

    Note the result table contains the same rows as the original table.<\/p>\n\n\n\n

    SQL PARTITION BY<\/h1>\n\n\n\n

    PARTITION BY is a sub clause of OVER() function and it\u2019s immensely useful as you will witness shortly. <\/p>\n\n\n\n

    Syntax:<\/strong> SELECT aggregate function(column_name<\/em>) OVER(PARTITION BY column_name<\/em>) FROM table_name<\/p>\n\n\n\n

    OVER( PARTITION BY column_name)<\/em> is like a GROUP BY column_name<\/em> that groups similar data in a column. The difference lies in that now you can query multiple columns in the SELECT clause which was not earlier possible with GROUP BY. <\/p>\n\n\n\n

    The result table contains the same rows as the original table.<\/p>\n\n\n\n

    For example, I want to see how many employees are at Managers position and AM position. The following query will do it.<\/p>\n\n\n\n

    SELECT<\/strong> employee_name, position_, COUNT<\/strong>(employee_name) OVER<\/strong>(PARTITION<\/strong> BY<\/strong> position_) <\/p>\n\n\n\n

    FROM<\/strong> employee<\/p>\n\n\n\n

    WHERE<\/strong> dept_id IN<\/strong> (SELECT<\/strong> dept_id FROM<\/strong> department)<\/p>\n\n\n

    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    As you can see in the results table, there are a total of 2 employees at AM position while the total employees at the Manager position are 4. The result is displayed against each row.<\/p>\n\n\n\n

    SQL ORDER BY<\/h1>\n\n\n\n

    ORDER BY is a powerful clause in SQL which can be applied to more than one column. It\u2019s also used as a sub clause in the OVER()  function.<\/p>\n\n\n\n

    Syntax<\/strong>: SELECT column_1, column_2, …<\/em><\/p>\n\n\n\n

    FROM table_name<\/em><\/p>\n\n\n\n

    ORDER BY column_1 ASC|DESC,… column_n ASC|DESC<\/em><\/p>\n\n\n\n

    ORDER BY column_1 ASC|DESC sorts the contents of column_1 in ascending order when ASC is specified, or descending order if DESC is specified.<\/p>\n\n\n\n

    If the order key is not mentioned, ASC is chosen by default.<\/p>\n\n\n\n

    ORDER BY Multiple column (Major sort and Minor sort)<\/h2>\n\n\n\n

    If more than one column is specified in ORDER BY, then the first column uses major sort and gets sorted on priority while the second column uses minor sort and gets sorted after the major sort.<\/p>\n\n\n\n

    Note the order of the first column is maintained when the second column is sorted.<\/p>\n\n\n\n

    ORDER BY in OVER()<\/h2>\n\n\n\n

    Order by is used as a sub clause in over() function. It serves as a sorting function with the benefits of flexibility in querying columns in the SELECT statement.<\/p>\n\n\n\n

    For example, I want to see how many employees are at Managers and AM positions against their sorted employee ID. The following statement will be used: <\/p>\n\n\n\n

    SELECT<\/strong> position_, employee_id, employee_name, <\/p>\n\n\n\n

    COUNT<\/strong>(employee_name) OVER<\/strong>(PARTITION<\/strong> BY<\/strong> position_ ORDER<\/strong> BY<\/strong> employee_id)<\/p>\n\n\n\n

    FROM<\/strong> employee;<\/p>\n\n\n

    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    Take a Free Course! <\/h1>\n\n\n\n

    It’s a fact that almost all database service providers across the data analytics industry use SQL as their default programming language. To know more about it you can refer to SQL and Database management<\/a> for an overview.<\/p>\n\n\n\n

    Stating that, it creates a high demand for SQL as a basic skill in data analytics job roles. There are some renowned platforms that offers free training on SQL for free; we suggest codecademy<\/a> and coursera<\/a>.<\/p>\n\n\n\n

    PS <\/strong>once you get hands on in SQL, Hackerank<\/a> is another amazing platform that could test your SQL ability through interesting and challenging problems.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Learn about advance SQL statements with examples; SQL JOINS, aggregate functions, GROUP BY, OVER(), PARTITION BY and ORDER BY<\/p>\n","protected":false},"author":7,"featured_media":1975,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":{"0":"post-1948","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-data-analytics"},"_links":{"self":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts\/1948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/comments?post=1948"}],"version-history":[{"count":57,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts\/1948\/revisions"}],"predecessor-version":[{"id":2042,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts\/1948\/revisions\/2042"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/media\/1975"}],"wp:attachment":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/media?parent=1948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/categories?post=1948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/tags?post=1948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}