SQL BLOGS

The DENSE RANK () Function is another ranking function that is used to rank the set of rows. This DENSE RANK () function assigns a unique rank to each row and the rank will reset with each partition that is defined by the PARTITION BY( ) clause. Unlike the RANK Function, the rank will not be skipped if two records

The RANK() function assigns a unique rank for each row in the result set. If two records have the same value then it will assign the same rank by skipping the next rank in the sequence. The ranking is done for each row in the partition window. The RANK () Function can easily be operated with or without the PARTITION

Like the Aggregate function, the WINDOW Function also aggregates the values in the particular window (or, set of rows ) and it returns the aggregated values for each row. These functions use the OVER () clause to define the window mentioned in the query. The OVER clause partitions the rows to form a set of rows whereas it also defines

The CROSS JOIN which is also known as Cartesian Join is used to return the set of records by the Cartesian product of the joined tables. Syntax of CROSS JOIN – SELECT Table1.column1,Table2.column1,Table3.column1,…,TableN.columnN FROM Table1,Table2,…; Example – Consider these two tables the Product table and the Manager table. Demo Table –  Product Table Category Region City Country Customer_ID Customer_name Office Supplies

The LAST_VALUE () Function in SQL is a Value Window Function that is used to fetch the last value within the ordered partition of the set or the entire table when the partition is not defined. ORDER BY ( ) clause is required in the process of executing the query as using this function it’s easy to define the ordering

The LAG () Function is a Value Window Function used to fetch the previous row data along with the current row data of the specified offset, in other words, it is said as the LAG() Function access data of the previous row or the row before the previous row. These LAG Functions are very useful in case we need to

This Window function operates on a set of rows for the query provided. It returns the value for each row using multiple rows or only one row. The set of the row is determined by the OVER() clause. These functions are used to find out the first value, last value, next value, and previous value in the set of rows.

The HAVING clause in SQL is used to mention the condition to filter the result for the GROUP BY. The WHERE Clause specifies the condition for the selected column but the HAVING clause only has the condition for the GROUP BY clause. Syntax of HAVING Clause – SELECT column1,column2,column3,…,columnN FROM Table A,Table B WHERE condition1,condition2,… GROUP BY column1 ,column2 HAVING

The DEFAULT constraint in SQL is used to fill up the mentioned column with default and fixed values. All the new records will have the same value if no other value is mentioned. Syntax using DEFAULT Constraint –  CREATE TABLE table_name (column1 datatype,column2 datatype,….., columnN datatype DEFAULT ‘value’ ); Example – Create an Employee table with columns as (ID, Name, Country)

The CASE statement in SQL is used to evaluate a series of conditions and returns the values when the first condition is met. It is very similar to if-else statements. As the first condition came out to be TRUE, it will stop the process and will ignore the other conditions. If no part of the IF condition is true then

The CHECK constraint in SQL is used to limit the values that a column can hold in the creation of the table. The Check constraint is basically mentioned in CREATE TABLE command in SQL. If the value added column, if it violates the constraint then it evaluates to false and the value is aborted. Syntax to CHECK constraint –  CREATE TABLE table_name

Duplicacy can be attained when inserting many records into the database. Adding a Primary key constraint to a column restricts the insertion of duplicate values. This action gives an error message in the console and can interrupt the operation flow. The IGNORE statement in SQL works the same way but does not interrupt the flow of operation. It just generates

The BETWEEN operator in SQL returns the values within the given range. The range can be text, number, or dates. The Between operators are inclusive which means the starting value and the ending values are included. Syntax of BETWEEN Operator – SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2; Here, in the WHERE clause, we have added the

The GROUP BY clause is used by the SELECT statement to arrange the same values rows in groups. These clauses often use aggregated functions such as AVG(), SUM(), COUNT(), etc. to group the results of one or more columns. The GROUP BY () is followed by the WHERE clause. Syntax for GROUP BY – SELECT column1,column2,column3,…,columnN FROM table_name WHERE condition

The UNION operator is used to combine the results of two or more SELECT statements and return the value without any duplicate values. To use this operator in the SELECT Statements, the SELECT should have – Every SELECT statement should have an equal number of columns. The data type should be the same for each column. The columns of each

SQL RIGHT JOIN returns all the records from the right table even if there is no match of the records with the right table. If there are no records found then the result set will carry NULL on the non-matched fields of the left table. Syntax of RIGHT JOIN – SELECT column1,column2,column3,…,columnN FROM Table A  RIGHT JOIN Table B ON

The LEFT JOIN or LEFT OUTER JOIN is used to retrieve all the records from the Left table (Table A) if there is no match with the Right table (Table B). If there is no match in the right table then the result will have NULL representing the column for the right table. If any value matches with the left

The SELF JOIN is used to join the table to itself, assuming it as two tables. Syntax of SELF JOIN – SELECT A.column_name,B.column_name,… FROM Table A ,Table B WHERE condition ; Example – Consider the Product table to use Self join. Demo Table – Product Table Category Region City Country Customer_ID Customer_name Office Supplies Central Chicago United States SM-20950 Suzanne McNair

INNER JOIN is the most frequently used join in SQL. These joins are also referred to as EQUIJOIN. It creates a new result set that comprises the matching records of the joined tables on the basis of joining ground. If the condition on the predicate meets then it will return the matched pair of rows of Table A and Table

The SQL Joins are used to combine the data from two or more tables on the basis of some related columns of each. Suppose, we want to join Peoples Table and Product Table to learn about the regional manager of each state. Let’s have a look at both the tables.   Demo Table – Product Table Category Region City Country

The IN Operator in SQL is used to return the result that matches the specified values in the WHERE clause. Like the OR Operator, the IN operator works the same. For multiple OR operators, the IN operator can be used as its shorthand. Syntax of IN Operator – SELECT column1,column2,column3,…,columnN FROM table_name WHERE column_name IN (value1,value2,…); Demo Table – Product

The ORDER BY keyword is used to sort the values in Ascending [ASC] or Descending [DESC] order. The default sorting in the ORDER BY keyword is in Ascending Order. To sort it in descending order, we need to add [DESC] with the keyword. With the keyword, we can add multiple columns Syntax of ORDER BY keyword – SELECT column1,column2,column3,…,columnN FROM

These Aggregate functions are used in SQL to do operation on the values of the columns and it returns a single value for the respective column. These Functions are built-in SQL and are used for doing different operations in SQL. Let’s discuss some of the aggregate functions of SQL. AVG() SUM() COUNT() Demo Table – Product Table Category City Country/Region