Narendra's .Net Blog
This Blog dedicated to Microsoft Technologies specially ASP .Net and C# And Many More.
Monday, May 30, 2011
Friday, March 18, 2011
Sample Database
This is Sample Database for sqlserver
Department Table
create table Dept(Deptno int constraint Deptno_Pk Primary key,Dname varchar(50),Loc varchar(50))
Employee Table
create table Emp(Empno int constraint Empno_Pk Primary key , Ename varchar(100),job varchar(100),Mgr int Constraint Mgr_Ref references Emp(Empno),HireDate datetime,Sal Money Constraint Sal_Chk Check(sal between 1500 and 7500),Comm money,Deptno int Constraint Deptno_Ref Foreign Key(Deptno) References Dept(Deptno))
Department Details Table
Create table DeptDetails(Did int primary key,deptno int references dept(deptno) ,comments varchar(8000))
Salgrade Table
create table Salgrade(Grade int constraint Grade_pk Primary key,LoSal Money,HiSal Money)
Data In Dept Table
Insert into dept values (10,'Marketing','Mumbai')
Insert into dept values (20,'Sales','Chennai')
Insert into dept values (30,'Finance','Dehli')
Insert into dept values (40,'Production','Kolkota')
Data in Emp Table
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1001,'Scott','President',NULL,'01/01/78',5000,NULL,10)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1002,'Clark','Manager',1001,'01/01/78',4000,NULL,10)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1003,'Smith','Manager',1001,'01/01/78',3500,500,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1004,'Vijay','Manager',1001,'01/01/78',4000,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1005,'Ajay','Salsman',1003,'02/04/79',3000,300,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1006,'Satish','Salsman',1003,'02/08/78',4000,600,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1007,'Venkat','Salsman',1003,'04/15/78',3300,0,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1008,'Vinod','Clerk',1003,'01/15/78',2400,NULL,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1009,'Suneel','Clerk',1004,'05/12/83',2000,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1010,'Srinivas','Analyst',1004,'03/01/79',3400,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1011,'Prakash','Analyst',1004,'03/01/79',3600,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1012,'Madan','Analyst',1004,'01/09/81',3100,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1013,'Ravi','Clerk',1002,'01/06/78',1800,NULL,10)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1014,'Raju','Clerk',1005,'06/01/78',2300,NULL,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1015,'Ramesh','Clerk',1011,'08/22/78',2500,NULL,30)
Data in DeptDetails Table
insert into DeptDetails values(1,10,'This department is located in Mumbai and mainly involved in marketing')
insert into DeptDetails values(2,20,'This department is located in Chennai and mainly involved in Sales')
insert into DeptDetails values(3,30,'This department is located in Dehli and mainly involved in Finance')
insert into DeptDetails values(4,40,'This department is located in Kolkota and mainly involved in Production')
Data in Salgrade Table
insert into salgrade values(1,1300,1800)
insert into salgrade values(2,1800,2700)
insert into salgrade values(3,2700,3500)
insert into salgrade values(4,3500,5000)
insert into salgrade values(5,5000,8000)
Department Table
create table Dept(Deptno int constraint Deptno_Pk Primary key,Dname varchar(50),Loc varchar(50))
Employee Table
create table Emp(Empno int constraint Empno_Pk Primary key , Ename varchar(100),job varchar(100),Mgr int Constraint Mgr_Ref references Emp(Empno),HireDate datetime,Sal Money Constraint Sal_Chk Check(sal between 1500 and 7500),Comm money,Deptno int Constraint Deptno_Ref Foreign Key(Deptno) References Dept(Deptno))
Department Details Table
Create table DeptDetails(Did int primary key,deptno int references dept(deptno) ,comments varchar(8000))
Salgrade Table
create table Salgrade(Grade int constraint Grade_pk Primary key,LoSal Money,HiSal Money)
Data In Dept Table
Insert into dept values (10,'Marketing','Mumbai')
Insert into dept values (20,'Sales','Chennai')
Insert into dept values (30,'Finance','Dehli')
Insert into dept values (40,'Production','Kolkota')
Data in Emp Table
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1001,'Scott','President',NULL,'01/01/78',5000,NULL,10)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1002,'Clark','Manager',1001,'01/01/78',4000,NULL,10)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1003,'Smith','Manager',1001,'01/01/78',3500,500,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1004,'Vijay','Manager',1001,'01/01/78',4000,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1005,'Ajay','Salsman',1003,'02/04/79',3000,300,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1006,'Satish','Salsman',1003,'02/08/78',4000,600,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1007,'Venkat','Salsman',1003,'04/15/78',3300,0,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1008,'Vinod','Clerk',1003,'01/15/78',2400,NULL,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1009,'Suneel','Clerk',1004,'05/12/83',2000,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1010,'Srinivas','Analyst',1004,'03/01/79',3400,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1011,'Prakash','Analyst',1004,'03/01/79',3600,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1012,'Madan','Analyst',1004,'01/09/81',3100,NULL,30)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1013,'Ravi','Clerk',1002,'01/06/78',1800,NULL,10)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1014,'Raju','Clerk',1005,'06/01/78',2300,NULL,20)
insert into Emp(Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno) values(1015,'Ramesh','Clerk',1011,'08/22/78',2500,NULL,30)
Data in DeptDetails Table
insert into DeptDetails values(1,10,'This department is located in Mumbai and mainly involved in marketing')
insert into DeptDetails values(2,20,'This department is located in Chennai and mainly involved in Sales')
insert into DeptDetails values(3,30,'This department is located in Dehli and mainly involved in Finance')
insert into DeptDetails values(4,40,'This department is located in Kolkota and mainly involved in Production')
Data in Salgrade Table
insert into salgrade values(1,1300,1800)
insert into salgrade values(2,1800,2700)
insert into salgrade values(3,2700,3500)
insert into salgrade values(4,3500,5000)
insert into salgrade values(5,5000,8000)
Wednesday, March 16, 2011
Introduction of Key's in sql
Super key
A super key is a column or set of columns that uniquely identifies a row within a table.Example
Given table: EMPLOYEES{employee_id, firstname, surname, sal}Possible superkeys are:
- {employee_id}
- {employee_id, firstname}
- ...
- (employee_id, firstname, surname, sal}
Candidate key
A candidate key is a key that uniquely identifies rows in a table . Any of the identified candidate keys can be used as the table's primary key. Candidate keys that are not part of the primary key are called alternate keys.One can describe a candidate key as a super key that contains only the minimum number of column necessary to determine uniqueness.
Alternate key
An alternate key is any candidate key that is not the primary key Alternate keys are sometimes referred to as secondary keys.Primary key
A primary key is a colunm in a table whose values uniquely identify the rows in the table. The primary key is chosen from this list of candidate based on its perceived value to the business as an identifier.A primary key value:
- Must uniquely identiy the row;
- cannot have NULL values;
- Should not change over the time; and
- Should be as short as possible.
Example
CREATE TABLE t1 ( c1 int, c2 VARCHAR(30), c3 VARCHAR(30), CONSTRAINT t1_pk PRIMARY KEY (c1,c2));
Composite key
A composite key is a primary key that consists of more than one column. Composite keys are also known as concatenated or aggregate keys.Unique key
A unique key is used to uniquely identify rows in an table.Foreign key
A foreign key is acolumn in a table that does NOT uniquely identify rows in that table, but is used as a link to matching columns in other tables to indicate a relationship.
For example, the emp.depto column is a foreign key pointing the the dept table's primary key - dept.deptno.Monday, March 14, 2011
Integrity Constraint in Sql Server
Data integrity means maintaining proper data .If we want to maintain proper data we are provided INTEGRITY CONSTRAINTS.
A Constraint is a restriction or set of rules that restrict the DML operation if the data integrity is not follow.
5 integrity constraint are
1.NOT NULL
2.UNIQUE
3.PRIMARY KEY
4.CHECK
5.FOREIGN KEY
NOT NULL : If this constraint is imposed on a column that column will not allow null on it.
create table <tname> ( col1<Dtype>[width][Not Null],col2<Dtype>[width][Not Null],......colN<Dtype>[width][Not Null])
e.g. create table bank(custId int Not Null,CName varchar(50),BAL Deci9mal(7,2) Not Null)
Drawback of Not Null constraint is it allow duplicate values into the column.
UNIQUE: if we impose unique constraint on a column the column will not allow duplicate values into it.
create table <tablename>(col1<Dtype>[width][constraint <colname>]<cons type>,..... colN<Dtype>[width][constraint <colname>]<cons type>)
e.g. create table Bank(CustID int Unique,CName varchar(50) ,Bal decimal(7,2) not null)
A Constraint is a restriction or set of rules that restrict the DML operation if the data integrity is not follow.
5 integrity constraint are
1.NOT NULL
2.UNIQUE
3.PRIMARY KEY
4.CHECK
5.FOREIGN KEY
NOT NULL : If this constraint is imposed on a column that column will not allow null on it.
create table <tname> ( col1<Dtype>[width][Not Null],col2<Dtype>[width][Not Null],......colN<Dtype>[width][Not Null])
e.g. create table bank(custId int Not Null,CName varchar(50),BAL Deci9mal(7,2) Not Null)
Drawback of Not Null constraint is it allow duplicate values into the column.
UNIQUE: if we impose unique constraint on a column the column will not allow duplicate values into it.
create table <tablename>(col1<Dtype>[width][constraint <colname>]<cons type>,..... colN<Dtype>[width][constraint <colname>]<cons type>)
e.g. create table Bank(CustID int Unique,CName varchar(50) ,Bal decimal(7,2) not null)
Sunday, March 13, 2011
how to write queries in sql server
SYNTEX FOR CREATE TABLE
create table <tablename>(<col1><datatype>[width],<col2><datatype>[width],<col3><datatype>[width],.....
<colN><datatype>[width] )
[] means optional
<> means any value you can give any name
select * from student where sno=101
select * from student where sno=NULL (wrong)
When we want to perform operation based on NULL value we can not use Equal(=) operator.Because NULL value internally treated as unknown or indefrent value so not to NULL are equal
In our example the above statement will not retrieve the information because we are trying to compare NULL with NULL using equality operator
To overcome above drawback and retrieve the information based on NULL values
use IS NULL
e.g. select * from student where sname is null
UPDATE COMMAND
update <tablename> set <col>=<value>[....n][<where condition>]
e.g. update student set sname="Vijay' where sno=101
DELETE COMMAND
It is used for deleting rows from table
delete from <tablename> [<where condition>]
e.g. delete from student where sno=102
create table <tablename>(<col1><datatype>[width],<col2><datatype>[width],<col3><datatype>[width],.....
<colN><datatype>[width] )
[] means optional
<> means any value you can give any name
e.g. create table student (sno int,sname varchar(30))
to view the structure of the table you can use the following command
sp_help <tablename>
SYNTEX FOR INSERT COMMAND
It is used for inserting data into created table
insert into <tablename>[(col1,col2,col3....colN)] values(val1,val2,val3...valN)
e.g insert into student values(101,'Narendra')
if i want to insert more data than insert into student (sno,sname) values(101,'Smith')
this is faster than above one so it is good practice always use column name.
this is faster than above one so it is good practice always use column name.
While performing insert operation if we do not provide value for any column it takes NULL as a Default value
We can also explicitly insert Null value into desire column like
insert into student (sno,sname) values(102,NULL)
insert into student (sno,sname)values(103, 'NULL' ) (it will be wrong for inserting null value it will be traten as string)
NULL is a Keyword in sqlserver
SELECT COMMAND
If we want to retrieve the information that is present in table we use Select command
select <*|collist> from <tablename>[<condition>]
COLUMN ALIASING:
We can specify an alias name to any column of the table so that the alias name can be displayed in the output without changing the name of the table name.
An alias can be provide for column or table also
select <*|collist> from <tablename>[<condition>]
e.g. select * from student
Insted of * we can use column name for retrieve all the information from table and performance will also improve
e.g. select sno,sname from table
We can specify an alias name to any column of the table so that the alias name can be displayed in the output without changing the name of the table name.
An alias can be provide for column or table also
e.g. select sno Sno snumber from student
select sno as snumber fron student
WHERE CONDITION IN SELECT
select * from student where sno=101
select * from student where sno=NULL (wrong)
When we want to perform operation based on NULL value we can not use Equal(=) operator.Because NULL value internally treated as unknown or indefrent value so not to NULL are equal
In our example the above statement will not retrieve the information because we are trying to compare NULL with NULL using equality operator
To overcome above drawback and retrieve the information based on NULL values
use IS NULL
e.g. select * from student where sname is null
UPDATE COMMAND
update <tablename> set <col>=<value>[....n][<where condition>]
e.g. update student set sname="Vijay' where sno=101
DELETE COMMAND
It is used for deleting rows from table
delete from <tablename> [<where condition>]
e.g. delete from student where sno=102
Data Types in Sqlserver 2005
Hi All in my second post on i am going to describe Data Types available in sqlserver 2005
In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
INTEGER VALUES:
tinyint storage capacity 1 byte
smallint storage capacity 2 byte
int storage capacity 4 byte
bigint storage capacity 8 byte
FLOAT VALUES:
Decimal(p,s)
where p is precision and s is scale.precision tells the otal number of digit in the value. storage value varies according to p value like
if p is 1 - 9 then storage will be 5 byte
if p is 10-19 then storage will be 9 byte
if p is 20-28 then storage will be 13 byte
if p is 29-38 then storage will be 17 byte
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
CURRENCY VALUES:
smallmoney storage 4 byte
money storage 8 byte
these are used for storing currency values
BOOLEAN VALUES:
bit
Are character data types of either fixed length or variable length.
In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
INTEGER VALUES:
tinyint storage capacity 1 byte
smallint storage capacity 2 byte
int storage capacity 4 byte
bigint storage capacity 8 byte
FLOAT VALUES:
Decimal(p,s)
where p is precision and s is scale.precision tells the otal number of digit in the value. storage value varies according to p value like
if p is 1 - 9 then storage will be 5 byte
if p is 10-19 then storage will be 9 byte
if p is 20-28 then storage will be 13 byte
if p is 29-38 then storage will be 17 byte
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
CURRENCY VALUES:
smallmoney storage 4 byte
money storage 8 byte
these are used for storing currency values
BOOLEAN VALUES:
bit
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
CHARACTER VALUES:
- char [ ( n ) ]
- Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
- varchar [ ( n | max ) ]
- Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying. nchar [ ( n ) ]
- Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.
- nvarchar [ ( n | max ) ]
- Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying. Note:- whenever you asked for differentiate char ,varchar or nvarchar or any other datatype like this you can say that first one is fixed length second is variable length and third one is for unicode support
Saturday, March 12, 2011
Sql Server 2005 Introduction Part 1
Sql has been sub categorized into 5 sub languages
1 DDL (Data Defination Language)
It deals with the structure of objects that are present in the databas.it provides four commands in it
Create
Alter
Drop
Truncate
2.DML (Data Menupulation Language)
This language deals with the data that is present in the database. It provide following three commands in it.
Insert
Update
Delete
3.DQL (Data Query Language)
It is used for etreving information from database.Which provides a single command Select
4.DCL (Data Control language)
It provide the following commands
Grant
Revoke
Deny
5.TCL (Transation Control Language)
This language deals with the transaction management.Which contain following commands
Commit
Rollback
Save
Note:- A transation is the set of statements which tells all the statements should execute or none of the statement should execute to control this we require Transaction Control Language.
Sql Server is Collection of databases which is internally collection of Tabels,Views,Procedures,Function ,Triggers etc...
Database are of Two types
System Database
User Database
System Database These database are created and maintained by sql server for its Functionality
By Default when sql server is installed we get the following System Databases
Master
Model
MSDB
TempDB
user Database These Databases are created and Maintaind by users of sql server for storing there owm database objects.
Uses of System Databases
Master:- This database record all the system level operations that get perform on sqlserver.
Model:- Its acts as a template for every new database that getts create in sqlserver
whenever we create database a copy of the model is taken and create a database with new name.
TempDB:- This database is provides an options known as temparory objects which can be created on any database.When these objects are created sql server holds the objects in the tempdb but not the on the database where it was created because a temparory object required to be destroyed whenever sqlserver is shuting down it will drop or destroy complete TempDB database so that all the temparory object is destroyed,Whenever sql server is started it recreate TempDB database again by using The Model as a Template.
MSDB:- This databsa ei used for storing the information of objects like Job,Alert,Schedule
Job:- Job is an activity which has to be performe
Alerts:- It is a message given to the user on some conditional basis
Schedule;- it specifys the time period when job has performed.
Every Database is collection of two file in it
.mdf (Master Database File) It contain the actual information about database i.e. Matadata
.ldf (Log Database File) It contain the transation Information i.e.DML statements information
Syntex for create database
create database <databaseName>
e.g. Create database Student
Subscribe to:
Posts (Atom)