Tuesday, 26 April 2011

Common Terms Used in Database

Commit: By using the commit statement the state of the table will be saved .
For Example:
CREATE TABLE mytab(col1 INT,col2 CHAR(4))
INSERT INTO mytab VALUES(20898,'Joe smith')
INSERT INTO mytab VALUES(20892,'smith')
INSERT INTO mytab VALUES(20893,'smith joe')
COMMIT
This will create a Table named mytab which has two column namely col1,col2.
after this a new row will be inserted  set the values to the values passed in the query.
Until commit statement the current table will have 3 rows and totally 6 values .
so this is one state of table
when commit statement is encounters this state of the table will be saved.
------------------------------------------------------------------------------------------
ROLLBACK: Rollback will return to the last saved status of the table


CREATE TABLE mytab(col1 INT,col2 CHAR(4))
INSERT INTO mytab VALUES(20898,'Joe smith')
INSERT INTO mytab VALUES(20892,'smith')
INSERT INTO mytab VALUES(20893,'smith joe')
COMMIT
DELETE FROM mytab

ROLLBACK
here the last saved status of the table is 3 rows and totally 6 values .
After this Delete command occur then it will delete the entire values from table
DELETE operation will delete the table values but retains the table structure
After this ROLLBACK command comes
This command will move the table status to the last saved state.
so the new table has 3 rows and 6 column


-----------------------------------------------------------------------------------
GRANT
-------------------------------------------------------------------------------
ALTER

ALTER statement used to add delete and modify the table column

ALTER TABLE table_name
ADD column_name datatype




--------------------------------------------------------------------------------
ALIES



-------------------------------------------------------------------------------------
LOCK


----------------------------------------------------------------------------------
UPDATE


-------------------------------------------------------------------------------
JOIN

  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables
-------------------------------------------------------------------------------------------
LIKE  :- This Operator is used to search for a specified pattern in a  column


P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger


Following stament can be used for LIKE
SELECT * FROM Persons
WHERE City LIKE 's%'

'%' sign is used to dertermine the missing pattern after the character


O/pThe result-set will look like this:



P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger

--------------------***********************------------------------------------------