SQL Server 2005

Database: Database is a collection of tables and relations.

Table:  Table is a collection of rows and columns.

Databases in SQL Server are classified into two types

1) System defined databases:  The databases which are created by Microsoft are called System defined databases

Ex:  Master, Msdb, Model, Temdb, Northwind

2) User defined databases: The databases which are created by developers are called User defined databases.

                                    Ex: College database, Hospital database…etc

Syntax for creating database:

                        Create Database <database name>

On

(<data file specification> )

log on

(<log file specification>  )

Data file: It is having extension .mdf. The data what we are inserting will goes to data file.

Log file: It is having extension .ldf, when we are performing transactions sql server uses the log files.

If we are not specifying data file &log files SQL Server implicitly creates it. Those data and log files are placed into SQL Server folder.

Example for Creating database:      create database college

Dropping database:     Drop Database <data base Name>

Ex:       Drop Database College

Creating a table:

Syntax:  Create Table <Table Name> (<Column name> <data type> <constraints>

                                                            <Column name> <data type> <constraints>)

Ex: Create Table Students(sid int,sname varchar(10),location varchar(10))

Inserting values into table

Insert into students values(10,'raju','Hyderabad')

Insert into students values(20,'Rani','Banglore')

Insert into students values(30,'Ravi','Delhi')

Updating table:

Update students

Set location='chennai'

Where sid=20

To view updated table: Select * From students

Example:  select sid,sname from students

Example:  select * from students where sid=10

Deleting table

delete faculty

Truncate table faculty

Both commands are deleting data of the table, but table definition will be existing

If you want to delete table definition also use drop command

Drop table faculty

Creating views

Views:

Ø  View is nothing but a stored query

Ø  View is a virtual table

Ø  View will not contain any data in it, it will contains only the SQL query

Syntax:

Create View <viewname>

As

<Select statement>

Example:

Create view studentview

As

Select sid,sname

From students

Executing views

select *

from studentview

Insert values into view

Insert into studentview values (15,'ravi')

Example:

            select *

from studentview

Example:

            select *

from students

Deleting data from view

Delete from studentview where sname ='ravi'

Example

            select *

from studentview

Example

            select *

from students

Deleting view using delete command

Delete from studentview

Example

            Select *

from studentview

Example

Insert into studentview values (15,'ravi'