Tutorials at Free Tutorials Corner

We provide all latest Tutorials in easy way to learn for beginners.Happy Learing .

Placement Papers at Free Tutorials Corner

One place to find all latest placement papers,interview question,interview tips and many more.

Jobs at Free Tutorials Corner

All the latest job - IT, BPO, Government jobs, daily updates .

Tips at Free Tutorials Corner

Technical Tips, Interview Tips, Money Making Tips, etc...

Video Tutorials at Free Tutorials Corner

Video tutorials for all the latest technologies.We are mainly focused to provide Tutorials in Tamil.

Showing posts with label SQL Tutorials. Show all posts
Showing posts with label SQL Tutorials. Show all posts

Wednesday, 1 May 2013

TCL - Transaction control Language and Create Datebase statement

DCL - Data Control Language(Grant, Revoke )

Grant- is used to provide(grant permission) privileges for database objects.
Revoke-is used to deny(get back the permission) privileges.

TCL - Transaction control Language(Commit, Rollback)

Tcl-is used to control set of actions called transactions like insert, update, delete.
Transaction statement must start with BEGIN transaction_name followed by DML statements.
IF condition followed by BEGIN is IF condition start END is End of If condition

BEGIN TRAN
DELETE EMPloyee WHERE empno = 10
IF @@ROWCOUNT =1
BEGIN
COMMIT TRAN
PRINT 'EMPLOYEE DELETED'
END
ELSE
BEGIN
ROLLBACK TRAN
PRINT 'ROLLED BACK'
END


we use savepoints in TCL to save a particular transactions.
If we use savepoint it saves the transaction that takes place below it.

BEGIN TRAN
DELETE EMPloyee WHERE EmpNO=5
SAVE TRAN E1
DELETE EMPloyee WHERE EmpNO=4
SAVE TRAN E2
DELETE EMPloyee WHERE EmpNO=3
SELECT * FROM EMPloyee
ROLLBACK TRAN E2
SELECT * FROM EMPloyee
ROLLBACK TRAN E1
SELECT * FROM EMPloyee
COMMIT TRAN
SELECT * FROM EMP10



E1, E2 are save points we can able to rollback to any save point before transaction is committed. Once transaction committed changes made permanent .We cannot rollback to save point.

The CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a database.
syntax:

create database database_name
Now we want to create a database called "siva".

create database siva
Now we want to use the created database called "siva".

syntax:
use database_name
use siva

DML - Data Manipulation Language

DML-Data Manipulation Language
Select,Insert,Delete,Update

Select statement
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
Syntax:
SELECT column_name(s)
FROM table_name
and
SELECT * FROM table_name
select * from employee
select * from employee where salary > 20000
select * from employee where salary between 15000 and 35000

Insert statement

The INSERT INTO statement is used to insert a new row or record in a table.
Syntax:
It is possible to write the INSERT INTO statement in two forms.
The first form doesnt specify the column names where the data will be inserted,
only their values:
INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

insert into employee values(1,'siva','pgr',40000.00,10)
insert into employee values(2,'sakthi','pld',50000.00,20)
insert into employee values(3,'Imrankhan','tml',60000.00,30)
insert into employee values(4,'sachin','pgr',70000.00,40)
insert into employee values(5,'Ranbhir','pgr',40000.00,50)

insert into employee(empno,name,desg) values(6,'Dhoni','kep')
'INSERT'
CREATE TABLE Persons1
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
INSERT INTO Persons
VALUES (7,'SIVA', 'SAKTHI', 'VIRUDHACHALAM', 'NEYVELI')
select *from persons
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob')
INSERT INTO Persons (P_Id, LastName)
VALUES (5, 'Tjessem')


Delete statemet

The DELETE statement is used to delete rows or records in a table.
syntax:
DELETE FROM table_name
WHERE some_column=some_value
delete from employee where empno=6

Note: Notice the WHERE clause in the DELETE syntax.
The WHERE clause specifies which record or records that should be deleted.
If you omit the WHERE clause, all records will be deleted!
Delete All Rows
It is possible to delete all rows in a table without deleting the table.
This means that the table structure, attributes, and indexes will be intact:
DELETE FROM table_name
delete from employee
'DELETE'
DELETE FROM table_name
delete from persons1
DELETE FROM Persons
WHERE LastName='Tjessem' AND FirstName='Jakob'


Update statement

The UPDATE statement is used to update existing records in a table.
Syntax:
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Note: Notice the WHERE clause in the UPDATE syntax.
The WHERE clause specifies which record or records that should be updated.
If you omit the WHERE clause, all records will be updated!

update employee set salary = salary+5000 where empno = 4
UPDATE
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'


DDL-Data Definition Language

DDL -Data Definition Language

Create,Alter,Drop statements.

Create statement

The CREATE TABLE statement is used to create a table in a database.
syntax:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
....
....
)

create table employee(empno int,name varchar(30),desg char(3),salary int)

Alter statement
The ALTER TABLE statement is used to add, delete, or modify columns
in an existing table.
To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name datatype
Alter table employee add deptno int 'Adding a new field to the table'

To delete a column in a table, use the following syntax

(notice that some database systems dont allow deleting a column):
ALTER TABLE table_name DROP COLUMN column_name
Alter table employee drop column salary

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name ALTER COLUMN column_name datatype
Alter table employee alter column salary money

Drop statement
The DROP TABLE statement is used to delete a table.
syntax:
DROP TABLE table_name
drop table employee

The DROP DATABASE statement is used to delete a database.
syntax:
DROP DATABASE database_name
drop database siva

SQL Server Tutorial-1 Introduction

SQL Introduction
SQL is a standard language for accessing and manipulating databases.

What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards 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 databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views

Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in
database systems that allow more than one SQL statement to be executed
in the same call to the server.

We are using MS Access and SQL Server 2000 and
we do not have to put a semicolon after each SQL statement,
but some database programs force you to use it.

Character or Strings
The characters or strings must enclosed only in single Quatation.
ex:'siva','kumar','Imrankhan','Ranbhirkapoor'

Not case sensitive
SQL is not case sensitive. SELECT is the same as select.