SQL Basis
SQL
is the abbreviation of Structured Query Language.
It
is a language designed for assessing, managing and retreiving the
data held in a RDBMS
(
Relational Database Management System).
SQL
is used to manipulate data in several database systems like MySQL,
SQL server, Microsoft Access, Oracle, Sybase, DB2.
The
data in RDBMS is stored in database in the form of tables.
A
table is a collection of related data entries and it consists of rows
and columns.
Rows
will be otherwise called as Records.
Columns
will be otherwise called as Fields.
Things
about SQL:
SQL
is not case-sensitive.
every
statement ends with the semi-colon ( ; ) to denote the end of the
statement.
SQL
was developed in ANSI standard ( American National Standards
Institute ) and hence the SQL in several database systems follows the
similar pattern.
SQL
language consists of list of keywords. keywords are set of words
having pre-defined meaning and will perform the pre-defined work.
In
SQL, these keywords are broadly classified into three groups. They
are:
-DATA
DEFINITION LANGUAGE
these
command are used to create, modify and delete the structure of
database objects.
-CREATE
-ALTER
-DROP
-DATA
MANIPULATION LANGUAGE
these
commands are used to store, retreive, modify and delete the data.
-SELECT
-INSERT
-UPDATE
-DELETE
DATA
CONTROL LANGUAGE
these
commands are used to provide security to database objects.
-GRANT
-REVOKE
TRANSACTION
CONTROL LANGUAGE
these
commands are used to manage the changes affecting the data.
-COMMIT
-ROLLBACK
-SAVEPOINT
CREATE:
Let's
start with the creation of the table.
table
creation in the database can be done with the keyword 'Create'.
syntax:
create table table_name ( field1 data_type length, field2 data_type
length,...,);
eg: create
table customer ( customerid int not null primary key, customername
varchar (255));
now
lets create a table called customer with the fields like customer_id,
customer_name, customer_address, postal_code, city and country.
for
the above the corresponding SQL coding was below:
create
table customer
(
customerid int not null primary key,
customername
varchar(255),
address
varchar(255),
postalcode
int not null,
city
varchar(100),
country
varchar(100)
);
when
above code was made the corresponding schema was created as below:
SELECT:
let's
now move with the select statement:
select
statement is used to retrieve the data from the database.
To
retrieve all the data from the table:
syntax: select
* from table_name.
eg: select
* from customer.
so,
when applying the above code results like below:
like
no records have been found, it is because we build the schema, the
skeleton of the table but inside which no data is availabe.
To
insert the data into the table:
INSERT
INTO:
to
insert the values into the table what we have created the insert into
statement is used.
syntax:
insert into table_name (field1, field2,...) values
(value1,value2,....);
eg: insert
into customer (customerid, customername, address, postalcode, city,
country) values ( 1, 'kishore', 'renugambal st', 600125, 'chennai',
'india');
in
the above example, you can note that the numeral will be denoted
directly while the alphanumerals are mentioned within the single
quote. it is that way it has to be mentioned.
in
a similar way a no of entries can be made like
now
consider that the customer: kishore now moved to bangalore and hence
his address has to be updated, for this situation the update command
is used:
UPDATE:
update
keyword is used to update the existing record.
syntax:
update table_name set field1 = value1, field2 = value2 where field1
= value;
eg: update
customer set address = 'hudlu gate', postalcode = 601251, city =
'bangalore' where customername = 'rajesh';
when
above code was applied it results as follows
in
this the thing to consider is that the change was made with respect
to the customer name and hence in the search criteria the name was
given and also the country was not given in the change request and
because of that the value for the country was not changed.
now
if you want to view only selected fields of the table then again
comes the select statement but now instead of all fields, we have to
mention the fields which we want to display.
SELECT:
(SELECTION OF SELECTED FIELDS)
the
fields that we want should be mentioned along with the select
statement.
syntax: select
field1, field2 from table_name;
eg: select customerid,
customername, city from customer;
when
above code was used, then it results in:
now
in the same view if we want the customer of specified area like for
to say consider 'bangalore', then it has to be mentioned in the where
clause. like:
syntax: select
field1, field2 from table_name where condition;
eg: select customerid,
customername, city from customer where city = 'bangalore';
now
for arrange the list of field entries by order we have to use the
addition 'order
by' along
with the select statement.lets
arrange the fields city and country by the asending and descending
orders.
syntax: select
* from table_name order by field1 asc/desc, field2 asc/desc;
eg: select
* from customer order by city asc, country asc;
in
the above it can be viewed that the city is ordered in ascending
order first, then the country is ordered in ascending order.
now,
lets try the address, postal code, city, country to be viewed as a
single field in the name of customer address like:
syntax: select field1,
field2 as [name1] from table_name;
eg: select
customerid, customername, address+','+city+','+country as [customer
address], postalcode from customer
in
the above it can be viewed that the address, city and country has
been mingled and shown as a single field in the name of customer
address.
to
view only first three fields the 'select top' or 'limit' keywords is
used, which will show us only the limited no of records in the
window.
syntax: select
top no_of_records * from table_name;
eg: select
top 3 * from customer;
the
same can be achieved through the limit keyword also.
now
let's view the list of select statement in search criteria.
to
avoid duplicate values: 'distinct
keyword'
is used
syntax: select
distinct country from customer;
he
above made the countries distinct like the duplicates are avoided and
the unique value alone displayed in the screen.
now
to display the customer name start with k.
wildcards
in SQL statement should be used.
eg: select
* from customer where customername like 'k%';
for
the country that ends with a:
eg: select
* from customer where country like '%a';
we
can select the records depending on two conditions also
i,
when any one condition has to be satisfied the or should be used:
eg: select
* from customer where city = 'bangalore' or country = 'germany';
ii,
when both or all the conditions has to be satisfied the 'and' should
be used.
eg: select
* from customer where city = 'bangalore' and customername = 'priya';
use
'between' when we need to find the values in between.
eg:
select * from customer where customerid between 2 and 5;
deletion:
deletion
of a record will also be needed while maintaining the database, as
because the details that no longer is useful should be removed from
the database or else it will occupy the valuable space in the
database.
syntax: delete
from table_name where condition;
******************************************************************************************************************************************************************
No comments:
Post a Comment