SQL INTRODUCTION

What is Data?

·         In simple words data can be facts related to any object in consideration.

·         For example your name, age, height, weight, etc are some data related to you.

·         A picture , image , file , pdf etc can also be considered data.

What is Database ?

·         A database is an organized collection of data.

·         Database handlers create database in such a way that only one set of software program provide access of data to all the users.

·         The main purpose of database is to operate large amount of information by storing, retrieving and managing.

·         There are many dynamic websites on the world wide web now a days which are handled through databases. For example, a model to checks the availability of rooms in a hotel. It is an example of dynamic website that uses database.

·         There are many database available like MySQL, Sybase, Oracle, Mango DB, Informix, Postgre, SQL Server etc.

What is a Database Management System (DBMS)?

·         Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of  data .

·         It also helps to control access to the  database.

Types of DBMS

There are 4 major types of DBMS. Let's look into them in detail.

·      Hierarchical - this type of DBMS employs the "parent-child" relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.

·      Network DBMS - this type of DBMS supports many-to many relations. This usually results in complex database structures.  RDM Server is an example of a database management system that implements the network model.

·      Relational DBMS - this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database.

·      Object Oriented Relation DBMS - this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. PostgreSQL is an example of an object oriented relational DBMS.

What is RDBMS?

·         RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

·         A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

DBMS V/S RDMS:

No.	DBMS	RDBMS
1)	DBMS applications store data as file.	RDBMS applications store data in a tabular form.
2)	In DBMS, data is generally stored in either a hierarchical form or a navigational form.	In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.
3)	Normalization is not present in DBMS.	Normalization is present in RDBMS.
4)	DBMS does not apply any security with regards to data manipulation.	RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
5)	DBMS uses file system to store data, so there will be no relation between the tables.	in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6)	DBMS has to provide some uniform methods to access the stored information.	RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7)	DBMS does not support distributed database.	RDBMS supports distributed database.
8)	DBMS is meant to be for small organization and deal with small data. it supports single user.	RDBMS is designed to handle large amount of data. it supports multiple users.
9)	Examples of DBMS are file systems, xml etc.	Example of RDBMS are mysql, postgre, sql server, oracle etc.

What is SQL?

·      SQL stands for Structured Query Language.

·      It is designed for managing data in a relational database management system (RDBMS).

·      It is pronounced as S-Q-L or sometime See-Qwell.

·      SQL is a database language, it is used for database creation, deletion, fetching rows and modifying rows etc.

·      SQL is based on relational algebra and tuple relational calculus.

All DBMS like MySQL, Oracle, MS Access, Sybase, Informix, Postgres and SQL Server use SQL as standard database language.

Also, they are using different dialects, such as:

·      MS SQL Server using T-SQL,

·      Oracle using PL/SQL,

·      MS Access version of SQL is called JET SQL (native format) etc.

Why SQL is required?

SQL is required:

·      To create new databases, tables and views

·      To insert records in a database

·      To update records in a database

·      To delete records from a database

·      To retrieve data from a database

What SQL does?

·      With SQL, we can query our database in a numbers of ways, using English-like statements.

·      With SQL, user can access data from relational database management system.

·      It allows user to describe the data.

·      It allows user to define the data in database and manipulate it when needed.

·      It allows user to create and drop database and table.

·      It allows user to create view, stored procedure, function in a database.

·      It allows user to set permission on tables, procedure and view.

SQL Process:

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

There are various components included in the process. These components are Query Dispatcher, Optimization Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries but SQL query engine won't handle logical files.

Following is a simple diagram showing SQL Architecture:

SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature:

DDL - Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language:

Command	Description
SELECT	Retrieves certain records from one or more tables
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DCL - Data Control Language:

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

SQL Data Types:

The SQL data type defines a kind of value that a column can contain.

In a database table, every column is required to have a name and a data type.

These are the general data types in SQL.

Data-type	Syntax	Explanation
Integer	INTEGER	The integer data type is used to specify an integer value.
Smallint	SMALLINT	The smallint data type is used to specify small integer value.
Numeric	NUMERIC(P,S)	It specifies a numeric value. Here 'p'is precision value and 's'is scale value.
Real	REAL	The real integer is used to specify a single precision floating point number.
Decimal	DECIMAL(P,S)	It specifies a decimal value. Here 'p'is precision value and 's'is scale value.
Double precision	DOUBLE PRECISION	It specifies double precision floating point number.
Float	FLOAT(P)	It specifies floating-point value e.g. 12.3, 4.5 etc. Here, 'p'is precision value.
Character	CHAR(X)	Here, 'x'is the character's number to store.
Character varying	VARCHAR2(X)	Here, 'x'is the character's number to store
Bit	BIT(X)	Here, 'x'is the number of bits to store
Bit varying	BIT VARYING(X)	Here, 'x'is the number of bits to store (length can vary up to x).
Date	DATE	It stores year, month and days values.
Time	TIME	It stores hour, minute and second values
Timestamp	TIMESTAMP	The timestamp data type is used to store year, month, day, hour, minute and second values.
Time with time zone	TIME WITH TIME ZONE	It is exactly same as time but also store an offset from UTC of the time specified.
Timestamp with time zone	TIMESTAMP with TIME ZONE	It is same as timestamp but also stores an offset from UTC of the time specified.

SQL Operators:

SQL statements generally contain some reserved words or characters that are used to perform operations such as comparison and arithmetical operations etc. These reserved words or characters are known as operators.

Generally there are three types of operators in SQL:

·      SQL Arithmetic Operators

·      SQL Comparison Operators

·      SQL Logical Operators

SQL Arithmetic Operators:

Let's assume two variables "a" and "b". Here "a" is valued 50 and "b" valued 100.

Example:

Operators	Descriptions	Examples
+	It is used to add containing values of both operands	a+b will give 150
-	It subtracts right hand operand from left hand operand	a-b will give -50
*	It multiply both operand?s values	a*b will give 5000
/	It divides left hand operand by right hand operand	b/a will give 2
%	It divides left hand operand by right hand operand and returns reminder	b%a will give 0

SQL Comparison Operators:

Let's take two variables "a" and "b" that are valued 50 and 100.

Operator	Description	Example
=	Examine both operands value that are equal or not,if yes condition become true.	(a=b) is not true
!=	This is used to check the value of both operands equal or not,if not condition become true.	(a!=b) is true
<>	Examines the operand?s value equal or not, if values are not equal condition is true	(a<>b) is true
>	Examine the left operand value is greater than right Operand, if yes condition becomes true	(a>b) is not true
<	Examines the left operand value is less than right Operand, if yes condition becomes true	(a
>=	Examines that the value of left operand is greater than or equal to the value of right operand or not,if yes condition become true	(a>=b) is not true
<=	Examines that the value of left operand is less than or equal to the value of right operand or not, if yes condition becomes true	(a<=b) is true
!<	Examines that the left operand value is not less than the right operand value	(a!
!>	Examines that the value of left operand is not greater than the value of right operand	(a!>b) is true

SQL Logical Operators:

This is the list of logical operators used in SQL.

Operator	Description
ALL	this is used to compare a value to all values in another value set.
AND	this operator allows the existence of multiple conditions in an SQL statement.
ANY	this operator is used to compare the value in list according to the condition.
BETWEEN	this operator is used to search for values, that are within a set of values
IN	this operator is used to compare a value to that specified list value
NOT	the NOT operator reverse the meaning of any logical operator
OR	this operator is used to combine multiple conditions in SQL statements
EXISTS	the EXISTS operator is used to search for the presence of a row in a specified table
LIKE	this operator is used to compare a value to similar values using wildcard operator