Join Dotnetcodes DotnetCodes.com is online Discussion Forum for Software professionals . It lets you find friends around the world and Create professional network that share similar interests as you. Get help on ur projects by industry specialists. Also get answers to all ur technical/placement related querries.Get an edge over others.
Already MemberClick here to login
ASP.net MVC Interview Questions Answers Interview Questions
Get Started Developing for Android Apps with Eclipse Articles
Serial Number in SSRS Articles
.Net framework 4.0 Interview Questions Answers Interview Questions
SQL server reporting services Interview Questions (SSRS) part 1 Articles
Whats New in ASP.NET 4.0 Part 2 Articles
How to Print a Crystal Report direct to printer Articles
Difference between Encapsulation and Abstraction Interview Questions
Explaining SDLC -System Development Life Cycle Articles
Html5 interview questions and answers Interview Questions
SharePoint 2010 interview Questions Answers Interview Questions
Infosys Interview Questions Interview Questions
Dynamic Menu using HTML List Tag and CSS in ASP.Net Articles
Populate or bind Dropdownlist in Asp.net using Jquery and Json Articles
ASP.NET MVC Overview Interview Questions
Submit Articles | More Articles..

TUTORIAL ONE EASILY UNDERSTANDABLE SQL SERVER

Posted By: nandu.docs121 On:12/25/2010 11:14:21 AM in:Articles Category:Sql Server Hits:1385
HERE IN THIS ARTICLE I WANT TO EXPLAIN WHAT IS SQL SERVER WITH MINIMUM INTRODUCTORY CONCEPTS

Introduction

In this first article of sqlserver i will explaiin the main features of sql server and basic ocncepts of sql server. In my next comming sequal articles i will explain each and every concepts in dertail.


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 4th 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   1st jan 1900 to 6th june 2069

Date time-4---1st jan 1753 to 31st 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")

comments powered by Disqus
User Profile
NANDANA
ASSISTANT PROFESSOR
NA , NA
Email :You must Log In to access the contact details.
Latest Post from :nandu.docs121
TUTORIAL ONE EASILY UNDERSTANDABLE SQL SERVER
View: 1385 | Submitted on: 12/25/2010 11:14:21 AM
ARRAYS AND PROCEDURES IN VB.NET
View: 1079 | Submitted on: 12/13/2010 8:27:31 AM
coding for all basic to advanced c# concepts
View: 1044 | Submitted on: 12/13/2010 8:19:14 AM
ALL CSHARP CONEPTS CODING
View: 1010 | Submitted on: 12/13/2010 8:09:52 AM
Submit Articles | All Post of This User..

All rights reserved to dotnetcodes. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks.
Best viewed at 1024 x 768 resolution with Internet Explorer 5.0 or Mozila Firefox 3.5 or Google Crome and higher