Computer Bapu: database

TRANSACTION:-
Collection of operation that forms a single logical unit of work are called Transactions.
In other words, Transactions are units or sequences of work accomplished in logical order, whether in a manual fashion by a user or automatically by some sort of a database program. In a relational database using SQL, transactions are accomplished using the DML commands, which are already discussed.
A transaction can either be one DML statement or a group of statements. When managing groups of transactions, each designated group of transactions must be successful as one entity or none of them will be successful.

The Following list describes the nature of transactions:
->All transactions have a begining and an end.
->A transaction can be saved or undone.
->If a transaction fails in the middle, no part of the transaction can be saved to the database.

TRANSACTIONAL CONTROL
Transactional control is the ability to manage various transactions that may occur within a relational database management system. (Note keep in mind that transaction is group of DML statements).
When a transaction is executed and completes successfully, the target table is not immediately changed, although it may appear so according to the output. When a transaction successfully completes, there are transactional control commands that are used to finalize the transaction.
There are three commands used to control transactions:
1) COMMIT
2) ROLLBACK
3) SAVEPOINT
When transaction has completed, it is not actually taken changes on database, the changes reflects are temporary and are discarded or saved by issuing transaction control commands. Explanatory figure is drawn as under.

TRANSACTIONAL CONTROL COMMANDS
1) COMMIT
->The commit command saves all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax
commit [work];
The keyword commit is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.
example
SQL>delete from emp
where emp_age > 75;

->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To allow changes permanently on database commit command is used.
SQL> COMMIT WORK;

->The above command will made changes permanently on database, since last commit or rollback command was issued.

NOTE: here work is totally optional, it is just to make command more user friendly.

2) ROLLBACK
->The rollback command is the transactional control command used to undo transactions that have not already been saved to the database. The rollback command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Syntax
SQL>rollback [work];
The keyword rollback is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.
example
SQL>delete from emp
where emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To discards changes made on database rollback command is used.
SQL> ROLLBACK WORK;
->The above command will discards changes made on database,since last commit or rollback command was issued.
note here work is totally optional, it is just to make command more user friendly.

3) SAVEPOINT
->A savepoint is a point in a transaction that you can roll the transaction back to without rolling back the entire transaction.
->Practical example
consider that a person walking and after passing some distance the road is split into two tracks. The person were not sure to choose which track, so before randomly selecting one track he make a signal flag, so that if the track was not the right one he can rollback to signal flag and select the right track. In this example the signal flag becomes the savepoint. figure is as under.

Syntax
SQL>SAVEPOINT
->Savepoint name should be explanatory.
example
->Before deleting the records of employee whose age is above 75, we are not sure that whether we are given work to actually delete the records of employee whose age is above 75yrs or 80yrs. So before proceding further we should create savepoint here if we are been order later than it might create loss of information.
SQL>savepoint orignally;
SQL>delete from emp
where emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area.
->After some time we are given order to increase employee salary to 10%. We can increase by generating following command. But before that we will make savepoint to our data so incase if the top level management change their mind and order's no increment should be given than we have can simply pass rollback entry achieve present state.
SQL>savepoint increm_sal;
SQL>update emp
set salary=salary*10;
->It will Increase salary of employee by 10%.
->After sometime top level management decided that salary of only programmer should be increased by 10% than only we have to do is just to pass entry of rollback before salary is updated.
SQL>rollback to increm_sal;
->It will rollback the changes made to emp_salary now we can update salary records for employee who is programmer. If we have dout than we can put savepoint, otherwise savepoint is not compulsory.
SQL>update emp
set salary=salary*10;
where emp_status='PROGRAMMER';
->It will increase salary of only programmers.
If all the changes have been taken place and now we have decided that no further changes require and we have to made changes to apply on database permanently than we can simply generate commit command to reflect changes permanently on database.
SQL>commit work;

Data Manipulation Language (DML)

DATA MANIPULATION LANGUAGE
------------------------------------------

INTRODUCTION
By data manipulation language we mean:
->The retrieval of information stored in the database.
->The insetion of new information into the database.
->The deletion of information from the database.
->The modification of data stored in the database.
Thus, it is a language that enables users to access or manipulate data as organised by the appropriate data model.
There are basically two types of DML
i>Procedural DMLs
->It requires a user to specify what data is needed and how to get it.
ii>Non-Procedural DMLs
->It requires a user to specify what data is needed without specifying how to get it.
As SQL is Non-Procedural language we will switch on to Non-Procedural DMLs as it is easy to understand and became very efficient for new users to begin with it.
The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.

SELECT
->To view all rows or some of the rows from a table or more than one table depending upon a userdefined criteria,this command is used.
->By default, select statement display all rows which matches the criteria but we can get the unique records by using keyword distinct with it.
syntax:
SELECT [DISTINCT ALL] FROM
WHERE
->keyword ALL is optional by default it consider ALL rows in table.
example:
1)SQL>select * from emp;
->It will display all rows of emp table including all feilds.we can customize the output by selecting the rows which are needed to us.
2)SQL>select empno,ename from emp;
->It will display all rows of emp table including empno and employee_name detail for an employee.
3)SQL>select * from emp
where
job = 'clerk';
->It will display all details of employee whose status is clerk.
4)SQL>select distinct ename from emp;
->It will display all unique name of employee if employee_name is repeated twice than it will display only ones.Thus it discards the duplicate records.

INSERT
->INSERT command is used to insert one or more rows in a table.
(There are many of syntax for insert command but one mentioned as under is the only standard way to insert multiple rows.)
syntax:-
INSERT INTO (........)
VALUES (<&Fieldname1>......<&FieldnameN>);
->If the Fieldname should be valid field for a table.
->Field having datatype char,varchar2 and Date kind of data should be written in single quota.
examples:
1)SQL>Insert into emp (empno,ename,job)
values ('1','SHREY','MANAGER');
->Above command will insert data for one record, here as data are mentioned directly, so we have made use of single quota.
2)SQL>Insert into emp (empno,job)
values (&empno,'&job');
->Here we have customize the insert command to take data for only two field that is empno and job for multiple records.
->When you don't want to type the command which is used last than simply press the slash to activate the command which is used by you last.
3)SQL>Insert into emp
values (&empno,'&ename','&job');
->Note in Above command we haven't declare the field in which data is to be entered, it is optional when we have to enter data for all fields in column.
4)SQL>Insert into emp (empno,ename,job)
values ('5','VRAJ',NULL);
->The above command will create a row and assign value 5 for empno and vraj for ename and leave the column job.
->If we doesn't want to enter value for a particular field we can just type NULL in it place during defining the INSERT command. And just press enter while entering the value.
5)SQL>Insert into emp_bkup
(select * from emp);
->The above command will copies all the rows from table emp and insert it into the table emp_bkup, provided that the structure of emp and emp_bkup is same.
->The above command is efficient to run when we want to create backup copy of our data.

UPDATE
->Update command is used to modify content of table, that to under satisfaction of particular criteria.
syntax:
UPDATE
SET =
WHERE ;
->Where Clause is optional.
->Fieldname is name of column whose contents are to be manipulated.
example:
1)SQL>Update emp
set job = 'ACCOUNTANT'
where job = 'MUNIM';
->Above sql statement will Modify Job field for employee whose status is munim, it will update the status munim with accountant.
2)SQL>Update emp
set salary = salary * 10
where job = 'PROGRAMMER';
->Above statement will increase salary of employee by 10% whose status is programmer.

DELETE
->DELETE command is used to delete one or more rows from the table.
Note:-No warnings are given before deleting rows so be careful while performing this operation.
syntax:
DELETE FROM
WHERE
;
->Table_name is name of table from which you want to delete record/s.
->Criteria is condition under which you want to delete record/s.
example:
1)SQL>Delete from emp
Where empno = 4;
->Above statement remove record of empno 4.
->Only one record is deleted.
2)SQL>Delete from emp
Where job = 'OPERATOR';
->Above statement remove record/s of those employee whose status is operator in the company.

Data Definition Language (DDL)

DATA DEFINITION LANGUAGE (DDL)
--------------------------------------------------------------------------------

INTRODUCTION
A database scheme is specified by a set of definition which are expressed by a special language called a Data Definition Language. The result of compilation of DDL statement is a set of tables which are stored in a special file called Data Dictionary.
-> DDL defines the structure of data.
-> DDL statements in SQL language are responsible for creating or modifying database structures such as tables, views, and indexes.

Let us now understand DDL statements and how it works on Oracle database.

TABLE
(A table consist of Rows and Columns. Table is a collection of related records that are treated as a unit. )
Convention for Naming a Table in Oracle
-> Each table owned by a Oracle account must have a unique name.
-> A table name cannot exceed 30 characters in length.
-> A table name must begin with an alphabetic character.
-> A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore).
-> A table name cannot be an SQL reserved word.
-> You can use uppercase and lowercase characters in naming tables; oracle is not case sensitive as long as table or column names are not enclosed in double quotes.
-> A table name should be descriptive.
Convention for Naming Column in Oracle.
-> Within a single table, a column name must be unique. However, you may use the same column name in different tables.
-> Like other database objects, a column name can be upto 30 characters long.
-> The column name must begin with an alphabetic character.
-> The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore). The name cannot include spaces.
-> A column name cannot be an SQL reserved word.
-> As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
-> An Oracle table may have upto 254 columns.
Commands Related To Table
1) Syntax to CREATE Table
CREATE TABLE
(
[column level constraint],
:
:
,
[Table level constraint]
);

Here,
-> Table_name is the name for the table.
-> Column_name1 through Column_nameN are valid column names.
-> Datatypes is a valid Oracle datatype specification.
-> Constraint_name is a optional, but it should be used in order to avoid invalid information to be entered in the database.

NOTE:-you can assign default value for field by using the keyword default and specifying default value for that field.
eg:-city varchar2(25) default 'AHMEDABAD'
(Though SQL is not case sensitive, but contents of table are case sensitive so it is good practise to write data always in uppercase).
Tip - CHAR, VARCHAR AND DATE kind of data should be written in single quota.
example
sql> create table emp
(
emp_no number (6) primary key,
emp_name varchar2(35),
emp_address varchar2(45),
emp_city varchar2(30),
emp_state varchar2(30),
emp_age number(2),
emp_sex char,
emp_department varchar2(30)
);

Table created.

2) Describe Table
-> This command will describe the table.
Syntax for describing table
sql> desc ;
or
sql> describe ;
example
sql> desc emp;

3) ALTER Table
-> Alter command is used to make modification after table has created.
-> Modification like to add column, to drop constraint like primary key,etc., However you cannot delete the column or you can decrease the width of column.
Syntax to ALTER Table definition
ALTER TABLE
[ADD MODIFY DROP]
( ,
:
:
);
examples
-> To Add column in a table.
sql> alter table emp
add
(emp_phno number(10));
-> To Modify Table
sql> alter table emp
modify
(
emp_city varchar2(35),
emp_state varchar2(35)
);
-> To Drop Table's Primary key and other constraints.
general syntax
ALTER TABLE
[DROP ENABLE DISABLE]
CONSTRAINT ;
sql> alter table emp
drop primary key;
After successfully completion of above command you get the message table altered.

4) Command to Delete contents of Table
TRUNCATE
-> Truncate command will delete all the table contents by removing all the rows(records) from the table.
-> After the implementation of this command, only the structure of the table remains.
Syntax for Truncate command
sql> TRUNCATE TABLE ;
example
sql> truncate table emp;
-> It will delete all the rows of the emp table and only structure of the table remains.
5) Command to Delete Table
DROP
-> Drop command will delete the table contents and its structure.
Syntax for Drop command
sql> DROP TABLE ;
example
sql> drop table emp;
-> It will delete all the rows and the structure of emp table.

INTEGRITY CONSTRAINTS
An Integrity constraint is a trick used by oracle to prevent invalid data entry into the table. It is only a rules applied to restrict user to enter particular value. It prevent this by defining it at column-level or table-level constraint. The main difference between both is that column-level constraint is defined at the column level and cannot reference by any other columns in the table. A table-level constraint is defined at the table level and can reference to any of the table's columns.

NOTE:-not null and check constraint cannot be defined as table-level constraint.

Integrity constraint are categorized into three types they are as under
1) Domain Integrity Constraints
2) Entity Integrity Constraints
3) Referential Integrity Constraint.

I) DOMAIN INTEGRITY CONSTRAINTS
-> Domain integrity constraint Prevent invalid data entry by enforcing rules like NOT NULL and CHECK on the column.

NOT NULL
->By default, every column can contain null value. But, If we want to restrict the user to enter the value at any cost we should put not null constraint.
->In other words, not null field should always contains value.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25)
);
->here empno is column-level constraint and after implementation of the above command while user want to enter the value, he must have to enter empno. ntnl is the constraint name.

CHECK
-> check constraint is used to match the enter data with predifined criteria during designing of table.
-> As it check for predifined criteria it stops the invalid user to do mischief with the database.
-> Thus it helps in making database consistent by feeding reliable information.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25) constraint check(job in('clerk','manager'))
);
->here check constraint will look for job in clerk or manager and if enduser try's to enter job for another category an error code for it will be generated by sql.

II) ENTITY INTEGRITY CONSTRAINTS
-> Entity integrity constraint Prevent invalid data entry by enforcing rules like UNIQUE and PRIMARY KEY on the column.

UNIQUE
-> Unique constraint allowed null values, but it does not allowed duplicate values.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4),
ename varchar2(25),
job varchar2(25),
constraint unino unique(empno)
);
->here unino is constraint name for table-level constraint definition and constraint unique is applied to empno, so after execution of above command user can enter only unique value or may not enter the value.

PRIMARY KEY
-> A primary key is a field that uniquely identifies each records in a table.
-> It should neither be duplicate nor null.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
->here Primary key is created for empno so it will alone identifies each records in a table. pkno is again a constraint name for column-level definition of constraint.

III) REFRENTIAL INTEGRITY CONSTRAINTS
-> To establish a 'parent-child' or a 'master-details' relationship between two tables of a same database having a common column, we make use of referential integrity constraints. To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
->foreign key is a column or combination of column which refers to primary key of primary table.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);

create table education_detail
(
empno number(4) ,
degree varchar2(30),
foreign key (empno) references emp(empno)
);

-> here emp table contains the details of the employee while education_detail table consist of their achived degree's by their no. one-to-many relationship is formed here. As one employee have many degree's and even few employee have no degree's.

ON CASCADE DELETE
-> It is special facilty through which we can ensure that the row deleted in the master table will automatically deletes the row in the reference table.
for example if we have records of employee details than after deleting any one of their record will automatically deletes the corresponding entry in the reference table.
example
create table education_detail
(
empno number(4) references emp(empno) on-delete-cascade,
degree varchar2(30)
);

Database keys

Database keys

================

1) PRIMARY KEY:-

A primary key is a field that uniquely identifies each record in a table. As it uniquely identify each entity, it cannot contain null value and duplicate value.
eg:-Consider the customer table, which has field :customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.

2) CANDIDATE KEY:-

A nominee's for primary key field are know as candidate key.
eg:-From above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.

3) ALTERNATE KEY:-

A candidate key that is not the primary key is called an Alternate key.
eg:- In above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.

4) COMPOSITE KEY:-

Creating more than one primary key are jointly known as composite key.
eg:-In above example, if customer_number and customer_socialsecurity_number are made primary key than they will be jointly known as composite key. In addition the combination of customer_number and customer_socialsecurity_number is unique.

5) FOREIGN KEY:-

Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.
eg:-Consider two tables emp(contains employees description) and emp_edu(contains details of employee's education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.

Backup Ms Sql Server Database using bat file

--Sometimes you may require a bat file to backup your ms sql server database.

@echo off
echo "Please press a key to backup you sql database.."
pause
echo "Backup process has started....This will take few minutes"
osql -U yourSqlUserName -P yourSqlPassword -Q "BACKUP DATABASE DatabaseName TO DISK = '%CD%\DatabaseName.BAK'"

pause

echo "You have Successfully Backup Up ... press a key to quit"
pause

--You might also want to restore your ms sql server database.

@echo off
echo "First Dropping your database if it exist... press a key"
pause
osql -U yourSqlUserName -P yourSqlPassword -Q "Drop Database DatabaseName"
echo "Restoring database ... press a key"
pause
osql -U yourSqlUserName -P yourSqlPassword -Q "RESTORE DATABASE DatabaseName FROM DISK = '%CD%\DatabaseName.BAK'"

echo "Restoring done ... press a key to quit"
pause

Computer Bapu

Saturday, September 29, 2007

Return random rows using Sql query

Monday, August 27, 2007

List tables in ms sql server

List tables in ms sql server

List fields in ms sql server

Monday, June 11, 2007

Transaction Control Language (TCL)

Data Manipulation Language (DML)

Data Definition Language (DDL)

Database keys

Backup Ms Sql Server Database using bat file

Labels

Label Cloud