SQL Contents

1	Introduction to SQL
	a) What is SQL
	b)What is Database
	c)What is RDBMS
	d)DBMS V/S RDBMS
	e)SQL Syntax
	e)SQL Statement
	g)SQL Process
	h)SQL Commands
	i)SQL Data types
	j)SQL Operators k)SQL  Expressions l)SQL  Constraints
2	SQL Database
	2a)SQL Create Database
	2b)SQL Drop Database
	2c)SQL Rename Database
	2d)SQL Select Database
3	SQL Keys
	3a)SQL Primary Key
	3b)SQL Foreign Key
	3c)SQL Composite Key
	3d)SQL Unique Key
	3e)Alternate Key
4	Normalization
	4a)First Normal Form (1NF)
	4b)Second Normal  Form(2NF)
	4c)Third Normal Form(3NF)
	4d)Boyce Codd Normal Form(BCNF)
5	SQL Table
	5a)SQL Create Table
	5b)SQL Drop Table
	5c)SQL Delete Table
	5d)SQL Rename Table
	5e)SQL Truncate Table
	5f)SQL Copy Table
	5g)SQL Temp Table
	5h)SQL Alter Table
6	SQL Indexes
7                      8	SQL  Transactions        SQL Select
	8a)Select Statement
	8b)SQL Select Distinct
	8c)SQL Select Top
	8d)SQL Select AS
	8e)SQL Select IN 8d)SQL Select Into
9	SQL Clause
	9a)SQL Where
	9b)SQL AND
	9c)SQL OR 9d)SQL Between 9e)SQL Like 9f)SQL Top, Limit OR ROWNUM 9g)SQL  Having
10	SQL Order BY
	10a)SQL Order BY Clause
	10b)Order BY ASC
	10c)Order BY DESC
	10d)Order BY Multiple Columns
11	SQL Group BY
12	SQL Insert
	12a)Insert Statement
	12b)Insert Into Values
13	SQL Update
	13a)Update Statement
	13b)SQL Update Join
	13c)SQL update date
14	SQL Delete
	14a)Delete Statement
	14b)SQL Delete Table
	14c)SQL Delete Row
	14d)SQL Delete All Rows
15	SQL Join
	15a)SQL Left Join
	15b)SQL Right join
	15c)SQL Full join
16	Set Operations in SQL
	16a)Union
	16b)Union ALL
	16c)Intersect
	16d)Minus
17	SQL Auto Increment
18	SQL Views
19	Materialized views in Oracle
20	SQL Sub queries
21	SQL Injection
22	SQL Aggregate functions
	22a)count() Function
	22b)sum() function
	22c)avg() function
	22d)min() function
	22e)max() function
	22f)first function
	22g)last function
23	SQL String Functions
24	NVL function
	NVL2 Function
	Decode Function
25	CASE Statement
26	SQL Date Functions

SQL INTRO

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.

1

Ajeet

24

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:

1. SQL Arithmetic Operators
2. SQL Comparison Operators
3. 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;

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

| ID | NAME     | AGE | ADDRESS   | SALARY   |

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

|  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;

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

| ID | NAME  | AGE | ADDRESS | SALARY   |

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

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

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