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)

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}

Only the the minimal superkey - {employee_id} - will be considered as a candidate key

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.

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)

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

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.

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>] 

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 

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

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

                                        

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:

                                    Are character data types of either fixed length or variable length.

   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  

BINARY VALUES: To store image ,Audio files,vedio files we require binary value data types because they are internally store in binary formate.                                                    binary(n)                                                    varbinary(n)                                                    image( in sql server 2005  varbinary(max))                                                    DATE VALUES:                                smalldatetime   4 byte fixed                                                             datetime           8 byte default range of smalldatetime is  jan 1 1900 through jun 6 2079 default range of datetime is jan 1 1753 through 31 dec 9999 in sql server 2008 more datatime datatye have been added      datatime2,datetimeoffset

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