Latest Jobs

March 28, 2015

SQL Tutorial

SQL Tutorial

Structured Query Language

SQL is introduced in order to create and manage relational databases in the commercial world. It provides high level declarative language interface. The first version was developed at IBM’s Almaden Research Center.

Click Here To Download The PDF        Download>>

I Data types

1.CHAR (character data type)

CHAR(<size>)

CHAR data type always allocates memory to store  maximum number of characters that can be stored in the field.

Example: space CHAR(5)

2.VARCHAR(variable character data type)

VARCHAR(<size>)

VARCHAR will allocate only enough memory to store the actual size of the field.

Example: space VARCHAR(5)

3.DEC(decimal data type)

DEC(<size,scale>)

Scale: Maximum number of digits to the right side of decimal point.

Example: space DEC(4,1)

4.NUMBER (numeric data type)

NUMBER(<size,scale>)

It is same as DEC.

Example: space NUMBER(4,1)

5.INT(integer data type)

INT

Example: space INT

6.SMALLINT(integer data type)

SMALLINT

It is same as INT but size will be smaller than INT.

Example: space SMALLINT

7.DATE(date data type)

8.TIME(time data type)x

II SQL commands

1.DDL

DDL provides commands for defining schemas,creating indices,modifying relation schemas and deleting relation schemas.The result of DDL statement is a set of tables which are stored in data dictionary.

CREATE TABLE

To create relation

ALTER TABLE

TO change/modify the existing relation

DROP TABLE

To delete the relation

2.DML

DML provides commands to insert tuples into,delete tuples from,to extract desired information and to modify tuples in the relation.

INSERT INTO

To insert a row into the relation

SELECT

To retrieve desired information from the relation

DELETE FROM

To delete rows from the relation

UPDATE

To change the contents

3.Transaction control

Transaction control includes commands for specifying the beginning and ending of transactions.

III SQL Constraints

These are some keywords used to implement some restrictions in the desired column.

NOT NULL

The column can never have null values.

CHECK

It limits the values that can be inserted into the desired column.

DEFAULT

This constraint key sets a default value for the specified column.

UNIQUE

This constraint key ensures that no two rows have the same value in the specified column.

PRIMARY KEY

This constraint key declares the specified column as the primary key of the table.

IV SQL Operators

The conditions in the SQL are expressed with the help of  relational and logical operators.

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

!=

Not equal to

IN

Any of the list of values

OR

True if either of the operand condition is true.

AND

True if both the operand conditions are true.

BETWEEN  …  AND

Between two values

LIKE

Matching a pattern

NOT

True when condition is false

IS NULL

Is a null value

V Aggregate functions in SQL

SUM()

Total of values in the specified column

MIN()

Minimum value in the specified column

MAX()

Maximum Value in the specified column

AVG()

Average of the values in the specified column

COUNT(*)

Number of values in the specified column

*You can use both upper and lower case for SQL statement.They make no impact on its execution.You can see that upper case letters used for writing clauses and keywords,which may help you to catch the syntax quickly.But remember that SQL is not case sensitive.

VI VIEW

It considered as a virtual table. A view doesn’t exist in physical form.A view can be derived from a set of one or more underlying base tables.The advantage of view is we can use the same table as different tables and it allow sharing of data with privacy.

clip_image002

Data Definition Language (DDL)

DDL provides commands for defining schemas,creating indices,modifying relation schemas and deleting relation schemas.The result of DDL statement is a set of tables which are stored in data dictionary.

Command

Purpose

CREATE TABLE

To create relation

ALTER TABLE

To change/modify the existing relation

DROP TABLE

To delete the relation

clip_image004

I CREATE TABLE  command

CREATE TABLE <name>

(<column name> <data type> [(<size>)] [<column constraints>],

<column name> <data type> [(<size>)] [<column constraints>],

<column name> <data type> [(<size>)] [<column constraints>],

……………………………………………………………………………………………..);

II ALTER TABLE command
1.To add new columns

ALTER TABLE <name>

ADD(<column name> <data type> [(<size>)] [<column constraints>]);

2. To modify an existing column

ALTER TABLE <name>

MODIFY(<column name> <new data type> [(<new size>)] [<column constraints>]);

III DROP TABLE command

DROP TABLE <name>;

Data Manipulation Language(DML)

DML provides commands to insert tuples into,delete tuples from,to extract desired information and to modify tuples in the relation.

Command

Purpose

INSERT INTO

To insert a row into the relation

SELECT

To retrieve desired information from the relation

DELETE FROM

To delete rows from the relation

UPDATE

To change the contents

clip_image006

I INSERT INTO command
1.To insert a row

INSERT INTO <name>

VALUES(<value1>,<value2>,<value3>,………….);

*Character data should be enclosed within single single quotes.

2.To insert specific values

INSERT INTO <name>( <column name>,<column name>,<column name>,……..)

VALUES(<value1>,<value2>,<value3>,………….);

*Character data should be enclosed within single single quotes.

3.Insertion with interaction

INSERT INTO <name>

VALUES(<&value1>,<&value2>,<&value3>,………….);

The & symbol is used as the substitution operator

*Character data should be enclosed within single single quotes.

II SELECT command

1.SELECT <column name>,<column name>,<column name>,…….

FROM <name>;

2.To view entire table we don’t need to specify all the column names of the table.Instead * can be used to substitute the entire column list.

SELECT * FROM <name>;

3.Setting condition in selection

The conditions are expressed with help of relational and logical operators.

SELECT <column name>,<column name>,<column name>,…….

FROM <name>

WHERE <condition>;

4.To avoid duplication of rows use the key word DISTINCT

SELECT DISTINCT <column name> FROM <name>;

5.To sort results

The result of a select command may be sorted either in ascending or descending order by making use of ORDER BY clause.The order is to be specified using the keywords ASC(ascending) or DSC(descending)along with column name.Remember that ORDER BY clause may be used only after WHERE clause.Otherwise an error is generated.

SELECT <column name>,<column name>,<column name>,…….

FROM <name>

WHERE <condition>

ORDER BY <column name> ASC/DSC;

6.To group results

The rows can be grouped together based on a common value using the GROUP BY clause.The HAVING clause place conditions on groups

SELECT <column name>

FROM <name>

GROUP BY <column name>

HAVING <condition>;

III DELETE FROM command

DELETE FROM <name>

[WHERE <condition>];

IV UPDATE command

UPDATE <name>

SET <column name> = <value>,<column name> = <value>,<column name> = <value>,……….

[WHERE <condition>];

VIEW in SQL

clip_image008

It considered as a virtual table. A view doesn’t exist in physical form.A view can be derived from a set of one or more underlying base tables.The advantage of view is we can use the same table as different tables and it allow sharing of data with privacy.

clip_image010

I To create a view

CREATE VIEW <name>

AS SELECT <column name>,<column name>,<column name>,…….

FROM <table name>

[WHERE <condition>];

II To delete a View

DROP VIEW <name>;

Symbols and their meaning

Symbol

Meaning

/

To provide alternate definitions

{ }

commands enclosed in it is treated as a single unit for the purpose of evaluating

[ ]

It is used for optional parts of the SQL statement

<>

This portion is to be replaced by user defined words and user supplied constant values

*You can use both upper and lower case for SQL statement.They make no impact on its execution.You can see that upper case letters used for writing clauses and keywords,which may help you to catch the syntax quickly.But remember that SQL is not case sensitive.

Stay Tuned To Our Regular Email Newsletter And Receive Updates Straight In Your inbox Click Here To Subscribe!

Author:

Hi! I am Sreeraj M Ajay founder of Entrance Time, a Professional Blogger, SEO Consultant SMO Expert, Chemical Engineer and an addicted Web developer. Did you find Entrance Time helpful? Please like it and share with your friends. It help us a lot. Thanks for your support. Follow me on Instagram

No comments:

Post a Comment

About Entrance Time

Entrance Time began out of the desire to contribute to our community, we realizes the need in India for a personnel agency that fills a void left by other temporary and permanent placement agencies. Entrance Time helps applicants find jobs and aids employers in their quest to locate ideal candidates. Entrance Time's will contribute to the community by filling a need for specialized, professional, contingent workers. We will provide workers with a safe and independent environment.Entrance Time matches specifically skilled workers with clients, saving businesses time and money. Read More

Contact Form

Name

Email *

Message *