Introduction of SQL

 

               INTRODUCTION OF SQL

 

SQL is standard language for accessing and manipulating database .

 

 

What is SQL ?

·       SQL stands for structured Query Language

·       SQL lets you access and manipulate database

·       SQL is an ANSI (American National Standard Institute) standard

 

What Can SQL do ?

·       SQL can execute queries against a database

·       SQL can retrieve data from a database

·       SQL can insert records in a database

·       SQL can update records in a database

·       SQL can delete records from a database

·       SQL can create new database

·       SQL can create new tables in a database

 

SQL DML and DDL

SQL can be divided into two parts : The Data Manipulation Language (DML) and the Data Definition Language (DDL)

 

The query and update commands from the DML part of SQL .

 

·       SELECT – extracts data from a database

·       UPDATE – updates data in a database

·       DELETE – delete data from a database

·       INSERT INTO – insert new data into a database

 

The DDL part of SQL permits database tables to be created or deleted . It also define indexes (keys) , specify links between tables , and impose constrains between tables . The most important DDL statements in SQL are :

 

·       CREATED DATABASE – creates a new database

·       ALTER DATABASE – modifies a database

·       CREATE TABLE – creates a new table

·       ALTER TABLE – modifier a table

·       DROP TABLE – delete a table

·       CREATE INDEX – create an index (search key)

·       DROP INDEX – delete an index

 

 

 

The SQL SELECT Statement

 

The SELECT  statement is used to select data from a database . The result in stored in a result table , called the result – set .

 

SQL SELECT Syntax :

         

à      SELECT column_name(s)

FROM table_name

 

Or

 

à      SELECT * FROM table_name

 

P_id	Lastname	Firstname	Address	City
1	Dipali	Patel	Rabari colony	Ahmadabad
2	Hansen	Ola	Timoteivn	Sandnes
3	Priti	Tove	Pij	Nadiad

 

Now we want to select the content of the columns named “ LastName “ and “ FirstName ” from the table above .

We use the following SELECT  statement :

SELECT Lastname , Firstname FROM persons

The result – set will look like this :

 

Lastname	Firstname
Dipali	Patel
Hansen	Ola
Priti	Tove

 

 

SELECT * EXAMPLE

 

Now we want to select all the columns from the “Persons” table.

We use the following SELECT  statement :

SELECT * FROM Persons

 

The result – set will look like this :

 

P_id	Lastname	Firstname	Address	City
1	Dipali	Patel	Rabari colony	Ahmadabad
2	Hansen	Ola	Timoteivn	Sandnes
3	Priti	Tove	Pij	Nadiad

 

 

SQL Syntax using WHERE condition

 

SELECT Company , Country FROM customers WHERE country “USA”

 

SQL Result

 

Company	Country
Island Trading	UK
Galeria del gastronomo	Spain
Laughing Bacchus Wine Cellars	Canada

 

 

The SQL SELECT DISTINCT Statement

In a table , some of the columns may contain duplicate value . This is not a problem , sometimes you will want to list only the different (distinct) values in a table .

The DISTINCT keyword can be used to return only distinct values .

 

 

SQL SELECT DISTINCT Syntax :

 

à      SELECT DISTINCT column_name(s)

FROM table_name

SELECT DISTINCT Example

 

 

P_id	Lastname	Firstname	Address	City
1	Dipali	Patel	Rabari colony	Ahmadabad
2	Hansen	Ola	Timoteivn	Sandnes
3	Priti	Tove	Pij	Nadiad

 

Now we want to select only the distinct values from the column named “City” from the table above .

We use the following SELECT  statement :

SELECT DISTINCT City FROM persons

 

The result – set will look like this :

 

City

Ahmadabad

Sandnes

Nadiad

 

 

 

The WHERE Clause

 

The WHERE Clause is used to extract only those records that fulfil a specified criterion .

 

SQL WHERE Syntax :        

 

à      SELECT column_name(s)

FROM table_name

WHERE column_name operator value

 

 

WHERE Caluse Example

 

P_id	Lastname	Firstname	Address	City
1	Dipali	Patel	Rabari colony	Ahmadabad
2	Hansen	Ola	Timoteivn	Sandnes
3	Priti	Tove	Pij	Nadiad

 

Now we want to select only the person living in the city “Sandnes” from the table above .

We use the following SELECT statement :

 

SELECT * FROM Persons

WHERE City = ‘Sandnes’

 

The result – set will look like this :

 

P_id	Lastname	Firstname	Address	City
2	Hansen	Ola	Timoteivn	Sandnes

 

 

Quotes Around Text Fields

 

SQL use single quotes around text value (most database systems will also accept double quotes .)

Although , numeric values should not be enclosed in quotes .

For text values:

This is correct :

SELECT * FROM Persons WHERE FirstName = “Tove”

 

This is wrong :

SELECT * FROM Person WHERE FirstName = Tove

 

For numeric values :

This is correct :

SELECT * FROM Persons WHERE Year = “1965”

 

 

Operators Allowed in the WHERE Clause

With the WHERE clause , the following operation can be used :

Operator
=	Equal
< >	Not equal
>	Greater than
<	Less than
> =	Greater than or equal
< =	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want for at Leat one of the column

 

 

The AND & OR Operators

The AND & OR operators are used to filter record based on more than one condition .

The OR operator displays a record if either the first condition or the second condition is true .

 

 

AND Operator Example