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 an ANSI (American National Standards Institute) standard

Why SQL?

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.

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.

What is RDBMS

RDBMS stands for Relational Database Management Systems..

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.

How it works

Data is represented in terms of tuples (rows) in RDBMS.

Relational database is most commonly used database. It contains number of tables and each table has its own primary key.

Due to a collection of organized set of tables, data can be accessed easily in RDBMS.

What is table

The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data.

A table is the simplest example of data storage in RDBMS.

Let's see the example of student table.

ID	Name	AGE	COURSE
1	Ajeet	24	B.Tech
2	Aryan	20	C.A
3	Mahesh	21	BCA

What is field

Field is a smaller entity of the table which contains specific information about every record in the table. In the above example, the field in the student table consists of id, name, age, and course.

What is row or record

A row of a table is also called record. It contains the specific information of each individual entry in the table. It is a horizontal entity in the table. For example: The above table contains 5 records.

Let's see one record/row in the table.

Ajeet

B.Tech

What is column

A column is a vertical entity in the table which contains all information associated with a specific field in a table. For example: "name" is a column in the above table which contains all information about student's name.

Ajeet

Aryan

Mahesh

Ratan

Vimal

NULL Values

The NULL value of the table specifies that the field has been left blank during record creation. It is totally different from the value filled with zero or a field that contains space.

Data Integrity

There are the following categories of data integrity exist with each RDBMS:

Entity integrity: It specifies that there should be no duplicate rows in a table.

Domain integrity: It enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity: It specifies that rows cannot be deleted, which are used by other records.

User-defined integrity: It enforces some specific business rules that are defined by users. These rules are different from entity, domain or referential integrity.

Difference between DBMS and RDBMS

Although DBMS and RDBMS both are used to store information in physical database but there are some remarkable differences between them.

The main differences between DBMS and RDBMS are given below:

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.

SQL Syntax

SQL follows some unique set of rules and guidelines called syntax. Here, we are providing all the basic SQL syntax.

SQL is not case sensitive. Generally SQL keywords are written in uppercase.
SQL statements are dependent on text lines. We can place a single SQL statement on one or multiple text lines.
You can perform most of the action in a database with SQL statements.

SQL statement

SQL statements are started with any of the SQL commands/keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP etc. and the statement ends with a semicolon (;).

Example of SQL statement:

1. SELECT "column_name" FROM "table_name";

Why semicolon is used after SQL statements:

Semicolon is used to separate SQL statements. It is a standard way to separate SQL statements in a database system in which more than one SQL statements are used in the same call.

In this tutorial, we will use semicolon at the end of each SQL statement.

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

These are the some important SQL command:

SELECT: it extracts data from a database.

UPDATE: it updates data in database.

DELETE: it deletes data from database.

CREATE TABLE: it creates a new table.

ALTER TABLE: it is used to modify the table.

DROP TABLE: it deletes a table.

CREATE DATABASE: it creates a new database.

ALTER DATABASE: It is used to modify a database.

INSERT INTO: it inserts new data into a database.

CREATE INDEX: it is used to create an index (search key).

DROP INDEX: it deletes an index.

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:

SQL COMMAND GROUPS

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.

RENAME-To rename existing table.

DML - Data Manipulation Language:

Command Description

INSERT- Creates a record

UPDATE- Modifies records

DELETE -Deletes records

MERGE-To update and insert contents of one table to another table based on condition.

DCL - Data Control Language:

Command Description

GRANT- Gives a privilege to user

REVOKE- Takes back privileges granted from user

DQL - Data Query Language:

Command Description

SELECT- Retrieves certain records from one or more tables

TCL – Transaction Control Language:

Command Description

COMMIT- Save or Enable DML changes to the database.

ROLLBACK-To undo DM changes till in a transaction

SAVEPOINT-To divide a transaction

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.

Data Type varies from database to database. For example, MySQL supports INT but Oracle supports NUMBER for integer values.

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 operands 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 becomes true.	(a!=b) is true
< >	Examines the operands 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<="" td="">
>=	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!<="" td="">
!>	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

SQL – Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

SQL EXPRESSIONs are like formulas and they are written in query language. You can also use them to query the database for specific set of data.

Syntax:

Consider the basic syntax of the SELECT statement as follows:

SELECT column1, column2, columnN

FROM table_name

WHERE [CONDITION|EXPRESSION];

There are different types of SQL expressions, which are mentioned below:

SQL - Boolean Expressions:

SQL Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax:

SELECT column1, column2, columnN

FROM table_name

WHERE SINGLE VALUE MATCHING EXPRESSION;

Consider the CUSTOMERS table having the following records:

SQL> SELECT * FROM CUSTOMERS;

+----+----------+-----+-----------+----------+

+----+----------+-----+-----------+----------+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+----+----------+-----+-----------+----------+

7 rows in set (0.00 sec)

Here is simple example showing usage of SQL Boolean Expressions:

SQL> SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

+----+-------+-----+---------+----------+

+----+-------+-----+---------+----------+

| 7 | Muffy | 24 | Indore | 10000.00 |

+----+-------+-----+---------+----------+

1 row in set (0.00 sec)

SQL - Numeric Expression:

This expression is used to perform any mathematical operation in any query. Following is the syntax:

SELECT numerical_expression as OPERATION_NAME

[FROM table_name

WHERE CONDITION] ;

Here numerical_expression is used for mathematical expression or any formula. Following is a simple examples showing usage of SQL Numeric Expressions:

SQL> SELECT (15 + 6) AS ADDITION

+----------+

| ADDITION |

+----------+

| 21 |

+----------+

1 row in set (0.00 sec)

There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column.

SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;

+---------+

| RECORDS |

+---------+

| 7 |

+---------+

1 row in set (0.00 sec)

SQL - Date Expressions:

Date Expressions return current system date and time values:

SQL> SELECT CURRENT_TIMESTAMP;

+---------------------+

| Current_Timestamp |

+---------------------+

| 2009-11-12 06:40:23 |

+---------------------+

1 row in set (0.00 sec)

Another date expression is as follows:

SQL> SELECT GETDATE();;

+-------------------------+

| GETDATE |

+-------------------------+

| 2009-10-22 12:07:18.140 |

+-------------------------+

Get Started !

Wednesday, 15 March 2017

SQL INTRO

What is Database

What is RDBMS

How it works

What is field

Data Integrity

Difference between DBMS and RDBMS

SQL Syntax

SQL Commands

SQL Data Types

Data Type varies from database to database. For example, MySQL supports INT but Oracle supports NUMBER for integer values.

SQL Operators

SQL Arithmetic Operators:

SQL Comparison Operators:

SQL Logical Operators:

SQL – Expressions

2 comments: