SQL SERVER 2005 TUTORIAL 1::::::::: INTRODUCTION AND BASIC SYNTAXES

In this tutorial i will explain all the basic concepts with syntax and examples :::::::::::::::::::::::::::

SQL SERVER

• SQL stands for Structured query language
• Any dbms based on RDBMS use this sql
• IBM developed “system-r” as first  DBMS and the actual name is given as “SEQEL”
• But first commercially  released by ORACLE company
• ANSI provides standard for SQL
• SQL used in SQL server is called Transact SQL
• Sql is a common language for any RDBMS
• Sql was actually developed by IBM corporation in their project named “System-R”, which is the first RDBMS.
• Actual name  given by IBM is ‘SEQUEL’ i.e., structured query language
• But later the name was changed to SQL and was first commercial released by ORACLE corporate

Sql server 2005 features

1. High availability: fail over clustering and database mirroring technologies
2. management tools: introduce application programming interface[api’s]
3. security enhancements: database encryption, more secure default settings, procedure enforcements
4. Scalability: table partitioning, replication enhancements, and 64 bit support.

                     As sql is a common language for any RDBMS,ANSI committee provides a standard for SQL

• The sql that follows the current standard for ANSI committee is called as ‘transact sql’ and sql server uses transact sql as its language
• Sql is a non procedural or 4^th generation language
• Sql is not case sensitive
• The commands of sql are classified into:

1. data definition language[DDL]:

CREATE,ALTER ,DELETE

2. data manipulation language[DML]

INSERT,MODIFY AND DELETE

3. data query language[DQL]

SELECT

4. data control language[DCL]:CONTROL DATA

GRANT AND REVOKE

5. transaction control language[TCL]:MANAGE DATA

COMMIT,ROLLBACK,SAVE

Note:

1. single sql can maintain maximum 32,767 databases
2. databases are maintained by sqlserver

DATA TYPES IN SQL SERVER

INTEGER             tiny int—1  small int-2  int—4 big—8

Float                decimal(precision,scale)-5-17,float(p)-p<25-4,small money  4,money-8             

Character       char(n) fixed  length character—4000bytes,varchar(n) variablelengthchar—n>0  n<=4000

Nchar--4000

Nvarchar--4000

Text(n)-2gb

Ntext(n)—2 power 30 -1

Binary(n)-4000

Varbinary(n)—n>=14  n<4000

Image(n)-2 poer 31-1

Bit—true or false-1

Small date time-2   1^st jan 1900 to 6^th june 2069

Date time-4---1^st jan 1753 to 31^st dec9999

Other data types         

Sqlvariant-store any type of data

Table-a table itself

Xml-xml document

Timestamp-within a table if we declare then no need to give value it will be updated by db.

<code>

create database newdb

sp_helpdb "newdb"

use newdb

sp_renamedb 'newdb', 'hai'

drop database newdb

select * from newtable

</code]

creating a table

create table tablename(column1 datatype(width),col2 datatype(width) constaint specification,col3 datatype(width)-------);

CREATE TABLE

[code]

create table newtabel1(sno int,sname varchar(10),s1 int)

primary key

create table newtabel1(sno int primary key,sname varchar(10),s1 int)

[/code]

Insert

1…..insert into newtable values( 1,'hai',21)

2….insert into newtable (sno,s1) values(12,2)

3…insert into newtable values( 2,'myname',null)

select

select */column1,colum2,col3--- /expression [alias] from tabel1 [alias],[table2 [alias]--- n] [where <condition1> [and/or/not <condition2>---n] [order by <column1> asc\desc, <column2> asc/desc---n] [group by <expression>] [having <condition>]

[code]

select * from newtable

select sno,s2 from newtable

select sno,s1,s2,s1+s2 total from newtable

relational opertors in sql

>,<,>=,<=,<>,=

In(val1,val2)

NotIn(val1,val2)

Between<lowerbound> and <upperbound>

Not between<lb> and <ub>

Is null

Is not null

Like<pattern matching>

Not like<pm>

select sno,s1,s2,s1+s2 total from newtable where s1>20

select sno,s1,s2,s1+s2 total from newtable where s1>20

and s2<13

s select sno,s1,s2,s1+s2 total from newtable where sname=’nan’

select sno,s1,s2,s1+s2 total from newtabel where sname=’nan’ or sname=’hai’ or sname=’l’

select sno,s1,s2,s1+s2 total from newtable where sname in(‘lal’,’nan’)

select sno,s1,s2,s1+s2 total from newtable where sname not in (‘hai’)

select sno,s1,s2,s1+s2 total from newtable where s1 between 20 and 40

Like pattern matching cannot be used for numerics

select sno,s1,s2,s1+s2 total from newtabel where sname like ‘a%’

select sno,s1,s2,s1+s2 total from newtable where s1 is null

select sno,s1,s2,s1+s2 total from newtable where s1 is not null;

select sno,s1,s2,s1+s2 total from order by sname

select sno,s1,s2,s1+s2 total from newtableorder by sname, desc

[/code]

ALTER

Alter table tablename add/modify/delete columnname datatype(width)

[code]

alter table newtable add  s2 int

[/code]

alter table tablename delete column columnname

DELETE

Delete from tablename

Delete from table name where condition

UPDATE

Update <tablename> set <column>=value where condition

DROP

Drop table tablename

Creating a table by taking information from already created table

 Create table tablename as select statement

Create table exe as select * from emp where ename=’an’

RENAME

 Rename oldtable name to newtablename

<select statement>

Copying rows from one table to another table

Insert into tablename (colname,col2) from tabelname(col1,col2)

The source and destination tables have same datatype and width of the colunns

Constraints

1.primary key

2.Check

3. unique

4. notnull

5.default

6.foerign key

7.null

To add constraint for already created table

Alter table tablename add constraint constraintname constraintspecification(column)

Alter table emp add constraint pk_emp_eno primarykey(eno)

Alter table emp enable constraint pk_emp_eno

Alter table emp disable constraint pk_emp_eno

To drop comstraint

Drop constraint constraintname

Drop constraint pk_emp_en

UNIQUE

 It allows null vales

Create table emp(eno int primarykey, name varchar(20),sal float unique)

Create table emp(eno int primarykey, name varchar(20),check(sex in(‘m’,’f’,’M’,F’))

Create table emp(eno int primarykey, name varchar(20),age int check age between(18 to 50)

Notnull

Notnull with unique is equal to primary key

Create table emp(eno int primarykey, name varchar(20),age int notnull)

DEFAULT

Create table emp(eno int primarykey, name varchar(20),age int,town varchar(23) default ‘tirupati’)

-----------------THE END------------------