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’.