TUTORIAL ONE EASILY UNDERSTANDABLE SQL SERVER

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
• ANI 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[apis]
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 nonprocedural 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

1. data manipulation language[DML]

INSERT,MODIFY AND DELETE

1. data query language[DQL]

SELECT

1. data control language[DCL]:CONTOL DATA

GRANT AND REVOKE

1. transaction control language[TCL]:MANAGE DATA

COMMIT,ROLLBACK,SAVE

Note:

1. single sql can maintain maxinmum 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 datatypes         

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 newtabel

</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 newtabel values( 1,'hai',21)

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

3…insert into newtabel 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 newtabel

select sno,s2 from newtabel

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

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 newtabel where s1>20

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

and s2<13

s select sno,s1,s2,s1+s2 total from newtabel 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 newtabel where sname in(‘lal’,’nan’)

select sno,s1,s2,s1+s2 total from newtabel where sname not in ("hai")

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

Like pattern matching cannot be used for numnerics

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

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

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

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

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

[/code]

ALTER

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

[code]

alter table newtabel 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

In my next tutorial i will explain the other concepts in detail.

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")