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:

Temp Tables in SQL (Part -8)

As its name indicates, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete).

Temp table will not exist once application has been closed.

Temporary tables are dropped when the session that creates the table has closed, or can also be explicitly dropped by users. At the same time, temporary tables can act like physical tables in many ways, which gives us more flexibility. Such as, we can create constraints, indexes, or statistics in these tables. SQL Server provides two types of temporary tables according to their scope:

• Local Temporary Table
• Global Temporary Table

Aggregate Functions in SQL (Part -7)

 SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table which returns a single value. It is also used to summarize the data.

We often use aggregate functions with the GROUP BY, WHERE and HAVING clauses of the SELECT statement.

1)     SUM:

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

2)  COUNT:

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

3) MAX:

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

4) MIN:

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

5)     AVG:

 The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Operators in SQL (Part -6)

 SQL Operators are special Words or Characters used to perform specific tasks both mathematical and logical computations on operands, which use ‘WHERE’ clause in a SQL query / statement.

There are six types of SQL operators that we are going to cover: Arithmetic, Bitwise, Comparison, Compound, Logical and String.

Every database administrator and user uses SQL queries for manipulating and accessing the data of database tables and views with the help of reserved words and characters, which are used to perform arithmetic operations, logical operations, comparison operations, compound operations, etc.

SQL Operators	Description
Arithmetic	Add (+), Subtract (-), Multiply (*), Divide (/), Modulo (%)
Bitwise	AND (&), OR (|), exclusive OR (^)
Comparison	Equal to (=), Greater than (>), Less than (<), Greater than or equal to (>=), Less than or equal to (<=), Not equal to (<>)
Compound	Add equals (+=), Subtract equals (-=), Multiply equals (*=), Divide equals (/=), Modulo equals (%=), Bitwise AND equals (&=), Bitwise exclusive equals (^-=), Bitwise OR equals (|*=)

SQL Constraints (Part -5)

 SQL constraints are conditions / rules that apply on the data columns of a table which is used to limit the type of data that goes inside the table.

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command.

There are 3 types of Constraints:

A)     Key Constraint

B)     Domain Constraint

C)     Referential Integrity Constraint

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.

A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
   The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

Create a Table in MySQL (Part -4)

 A MySQL table stores and organizes data in columns and rows as defined during table creation.

In the process of creating a table, you need to specify the following information:

• Column names – We are creating the title, genre, director, and release year columns for our table.
• Varchar of the columns containing characters – Specifies the maximum number of characters stored in the column.
• The integer of the columns containing numbers – Defines numeric variables holding whole numbers.
• Not null rule – Indicates that each new record must contain information for the column.
• Primary key – Sets a column that defines a record.

SQL in RDBMS (Part -3)

RDBMS (Relational Database Management System) is one of the types of DBMS which shows Relational between the Tables (where data are stored in the form of ROWS & COLUMNS).

In other words, Relational database is a type of database that allows us to identify and access data in relation to another piece of data in the database. It stores data in rows and columns in a series of tables to make processing and querying efficient.

Rows (Tuples) are Horizontal while Columns (Headers are called Attributes – ID, Name, etc.) are Vertical in a Table.

Degree of Relation = No. of Columns

Cardinality = No. of Rows

Data present inside the Column is called ‘Domain Values’.

Databases in SQL (Part -2)

Data usually comes through raw format in an unorganized manner and processing of such data is called ‘Information’.

Our Data are stored in Databases [db] consists set of Tables (Rows & Columns). A Table is an organized collection of data stored in the form of Rows and Columns. Columns can be categorized as vertical, while Rows as horizontal. The Columns in a table are called Fields in records while the Rows can be referred to as unique Records.

For example, a Table that contains Employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, etc.

A Computer can have one or more than one instance of SQL Server installed. Each instance of SQL Server can contain one or many databases. Within a database, there are one or many object ownership groups called schemas. Within each schema there are database objects such as tables, views, and stored procedures. Some objects such as certificates and asymmetric keys are contained within the database, but are not contained within a schema.

SQL Server databases are stored in the file system in files. Files can be grouped into file-groups. 

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in file-groups for allocation and administration purposes.

The number of tables in a database is limited only by the number of objects allowed in a database (2,147,483,647). A standard user-defined table can have up to 1,024 columns. The number of rows in the table is limited only by the storage capacity of the server.

SQL vs MySQL: Key Difference (Part -1)

SQL extends for Structured Query Language that enables the user to design and manage databases, while MySQL is a Relational database management system that allows a user to store and retrieve data from the database.

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

Both the technologies work on the concept of storing data as per schema (table storage). MySQL is inclined more towards selecting the data to facilitate data display, update and save the data again. It is a bit weaker than SQL Server in terms of data insertion and deletion.

Many famous web-based applications and companies use MySQL like WordPress, YouTube, Joomla, etc. SQL is also used by many platforms like MYSQL, Oracle, Microsoft SQL Server, etc.