SPL Rank() Function (Part -13)

The RANK() function is also known as window function that assigns a rank to each row within a group of data sets. The rank of a row is determined by one plus the number of ranks that come before it.

The syntax of the RANK() function is as follows

       SELECT column_name,

      RANK() OVER (PARTITION BY... ORDER BY...) as rank

      FROM table_name;

In this syntax:

The column_name represents the column that you wish to rank in the table

The PARTITION BY clause divides the result set's rows into partitions based on one or more parameters

The ORDER BY clause sorts the rows in each partition where the function is applied

RANK() function is used as part of the SELECT statement.

Basically, you add another column to your result set. This column includes the rank of each record based on the order specified after the ORDER BY keyword. This entails specifying (1) the column to use for sorting the rows and (2) whether the order should be ascending or descending.

The first row gets rank 1, and the following rows get higher rankings. If any rows have the same value in the column used for ordering, they are ranked the same. The RANK() function leaves gaps in such cases.

SQL SUB QUERY (Part -12)

 Sub Query is a Query within another Query. There are 2 types of Sub Queries:

1)     Single Row sub query

2)     Multi Row sub query

In other words, A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

• A subquery may occur in :
• - A SELECT clause
• - A FROM clause
• - A WHERE clause

SQL JOINS (Part -11)

 SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. We use SQL Joins to access data from multiple tables.

There are 6 types of Joins:

1)     INNER JOIN

2)     OUTER JOIN

3)     LEFT OUTER JOIN

4)     RIGHT OUTER JOIN

5)     CROSS JOIN

6)     SELF JOIN

“WHERE”, “AND”, “OR” Clauses in SQL (Part -10)

 A WHERE clause in SQL is a data manipulation language statement. WHERE clause is used in SELECT, UPDATE, DELETE statement etc.

WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query.

SQL Group By Statement (Part -9)

 Grouping the similar data is called ‘Group By’.

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Display the count of each and every country.

Syntax: