Working with SQL Server

Document Sample
Working with SQL Server Powered By Docstoc
					                       Working with SQL Server

Working with Enterprise Manager

Starting Enterprise Manager

  Start >>Programs >>Microsoft SQL Server >>Enterprise Manager

Creating a Database

  Expand the SQL Servers/SQL Server Groups, where the database will be
   created
  Select the Databases folders and right-click to choose New Database …
  Type the name of the database as, cabin42
  In File Properties option check the box for Automatically Grow File
   feature
  Click OK

 Creating a Table in Enterprise Manager

 Creating a table

  Expand the Database folder and then the database Cabin42
  Select Tables and right-click to choose New Table…
  Make following fields in the table

 Number Column Name               Data Type       Size    Allow Null   Identity
 1      st_code                   int             4       not null     yes
 2      st_name                   char            20      not null     no

 Note : Allow Null and Identity are check boxes

 Creating a Primary Key
  Select the column st_code
  Select the tool Set primary key
  Save the table as state and Close the window

 You will see that the state table appears in the listed tables.

 Populating a table

  High-light the State table, right-click to choose Open Table and select
   return all rows
  Enter the following states in the st_name column
   Haryana
   Rajasthan


                                  1
   Madhya Pradesh
   Note : You can not edit st_code field, as it is an identity column. System
   will generate identity values automatically for this column.

 Close the window

Working with Query Analyzer

Starting Query Analyzer

 Start >>Programs >>Microsoft SQL Server >>Query Analyzer

Or from the Enterprise Manager

 Tools >>SQL Server Query Analyzer

Creating a table in Query Analyzer

To create a table district with following fields in the database cabin42

Number   Column Name            Data Type      Size    Allow Null     Identity
1        st_code                int            4       not null       no
2        dist_code              int            4       not null       no
3        dist_name              char          20       not null       no

with primary key on st_code and dist_code and subsequently populating the
table type the following SQL statements in the Query Analyzer window

use cabin42
go

create table district
(st_code int not null,
dist_code int not null,
dist_name char(20) not null,
constraint pk primary key (st_code,dist_code))
go
insert district values (1,1, 'gurgaon')
insert district values (1,2, 'rewari')
insert district values (2,1, 'baswara')
insert district values (2,2, 'bharatput')
insert district values (3,1, 'anantpur')
insert district values (3,2, 'gwalior')
go

 Execute Query by selecting the tool


                               2
In the Result window you will see relevant messages.

 Save the contents of Query window under the name maketable
 Clear the Query window

To see the contents of these two tables type the following commands in the
Query window

Select * from state
Select * from district

 Execute Query

In the Result window you will see the data rows from both the tables.

To see what all tables are there in cabin42 database, type the following
command

Select table_name from information_schema.tables

 Select the text of above command
 Execute Query

Result window displays list of tables from the System View
Information_schema.tables

 Clear the Query window

Working with Visual Database Tool : Database Diagrammer

Creating a diagram

 Switch to Enterprise Manager
 Select Diagrams from the database cabin42 folder, right-click to choose
  New Database Diagram…

Create Database Diagram Wizard will launch
 Click Next to continue
 Select state and district table and click Add
 Click Next to continue
 Select Finish to create the diagram

A message box appears stating “Tables have been added and arranged on the
diagram”
> Click OK



                               3
The diagram with both the tables i.e., states and district appears on the screen
Creating a table with the database diagrammer

 Select New table tool
 Type a name for the table say village
 Click OK

Familiar window appears on the screen for defining columns. Add following
fields into the table.

Number   Column Name            Data type       Size   Allow Null
1.       st_code                int             4      not null
2.       dist_code              int             4      not null
3.       vill_code              int             4      not null
4.       vill_name              char           20      not null
5.       vill_pop               int             4      not null

 Right-click to choose Column Names option to display only the column
   names
On your diagram the new table appears.

 Select the following fields St_code, dist_code, vill_code
 Set primary key

Creating relationship between the state and district tables

State ------>>district ------>>village

The above relationship signifies that for one state there can be many districts
and for one district there can be many villages.

 Select state table and select st_code field.
 Click and Drag and Drop on district table

Create Relationship Dialog box, with default name for the relationship appears
on the screen.

 Select appropriate columns in both the tables i.e., st_code
 Click OK

On the screen the relationship is displayed between the state and the district
tables

Creating relationship between district and village tables

 Select district table and select st_code and dist_code fields together


                                4
 Click and Drag and Drop on village table

Create Relationship Dialog box, with default name for the relationship appears
on the screen.

 Select appropriate columns in both the tables i.e., st_code and dist_code
 Click OK

On the screen new relationships appear

   Close Diagram window
   Select Yes to Save the changes made to the diagram
   Give a new name to the diagram cabin42 and click OK
   Again click Yes to save all the tables

Working with SQL Server

Now you have Query Analyzer and Enterprise Manager running on your
computer. We will use these tools to work with SQL Server 7.0

Creating Data for the village table

 Go to Query Analyzer
 Type the following SQL statements

Insert village values (1,1,1,'gurgaon village',34)
Insert village values (1,1,2,'village gurgaon',43)
Insert village values (1,2,1,'rewari village',24)
Insert village values (1,2,2,'village rewari',27)
Insert village values (2,1,1,'baswara village',38)
Insert village values (2,1,2,'village baswara',43)
Insert village values (2,2,1,'bharatpur village',27)
Insert village values (2,2,2,'village bharatpur',45)

 Execute Query
 Save the contents of Query window under the name villagedata
 Clear the Query window

To see the contents of this table type the following commands in the Query
window

Select * from village

 Execute Query

In the Result window you will see the data rows.



                               5
 Clear the Query

Creating Views in Enterprise Manager

Let us create a simple view of all the rows from village table comprising of
columns village name, village population and their district and state names.

 Switch to Enterprise Manager
 Select Views from the database cabin42 folder, right-click to choose
  New View…

New View window will launch on the screen. This window is divided into four
parts.

 From the tool bar click Add table tool

Add Table dialog box appears.

 Add all the three tables i.e., state, district, village
 Close the Add Table dialog box

The 1st part of the window now shows all the three added tables and their
relationship diagrammatically. You can also see a Join statement being built
simultaneously in the 3rd part

 Check the box against st_name field in state table to add this column to
  query output.
 Similarly check dist_name, vill_name and vill_pop fields from district and
  village

As the new column are added, the 2nd part of the window, query design grid,
displays the added columns. The corresponding SQL statement is generated
automatically. You can also create this view by writing this SQL statement in
the Query Analyzer.

 Run the Query by clicking Run icon from the toolbar

Query output window displays the Village data along with its state and district
names.

 Save the view under the name villageview

Creating User Defined Data Types

 Select User Defined Data Types from the database cabin42 folder, right-


                                 6
     click to choose New User Defined Data Types…


User-Defined Data Type Properties dialog box appears on the screen

    Type Name as days
    Select Data Type as char
    Type length as 10
    Do not check Allows Null check box
    Click OK

Creating Rules

 Select Rules from the database cabin42 folder, right click to choose New
  Rules…

Rule Properties dialog box appears on the screen.

 Type Name as daysrule
 For the Text field type the following text

@days in ('Sunday','Monday','Tuesday','Wednesday', 'Thursday','Friday',
'Saturday','Undecided')

 Click OK

Creating Defaults

Select Defaults from the database cabin42 folder, right click to choose New
Defaults…

Deafault Properties dialog box appears on the screen.

 Type Name as daysdefault
 Type Value as ‘undecided’ (remember to put the quotes)
 Click OK

In these last three exercises we have created the following

1.        User Defined Data Type called days which is based on char(10) base
          Data type
2.        Rule called daysrule which says that days can have only eight defined
          Values Sunday,Monday,……., Saturday, undecided
3         Default called daysdefault which says that the Default value is
          „undecided’




                               7
        At the moment these three objects are not associated with each other. Let us
        now bind them together.

         Select User Defined Data Types from the database cabin42 folder.
            There is only one User defined Data Type i.e., days
         Select User Defined Data Type days, right-click to choose Properties
        Familiar User-Defined Data Type Properties dialog box appears on the
        screen.

           For the Rule field click the drop-down button and select dbo.daysrule
           For the Default field click the drop-down button and select dbo.daysdefault
           Click Apply
           Click OK

        Small exercise involving the use of User Defined Data Types, Rules, Default

         Switch to Query Analyzer

        Let us create a table called Drdays with the following fields

        Number    Column Name Data Type            Size   Allow Null    Identity
        1.        dr_code     int                  4      not null      Yes
        2.        dr_name     char                 20     not null       no
        3.        dr_days     days

        Switch to Query Analyzer and type the following SQL statements

        Create table drdays
        (dr_code int not null identity primary key,
        dr_name char(20) not null,
        dr_days days
        )
        go
        insert drdays values ('Dr. Sunita','Monday')
        insert drdays values ('Dr. Deshmukh',default)
        go
        select * from drdays
        go



Results are displayed in the Result window in the Query Analyzer.

 Try inserting the following row

   insert drdays values ('Dr. Sunita','January')



                                         8
    Execute Query

    You will not be able to add this row as the daysrule is violated by the value for days
field.

    Clear the Query window

   Creating Stored Procedures

    Switch to Enterprise Manager
    Select Stored Procedures from the database cabin 42 folder, right-click to
     choose New Stored Procedure…

   Stored Procedure Properties dialog box appears on the screen, with the Text field
   window displaying the following line

     CREATE PROCEDURE [PROCEDURE NAME] AS

    Enter the name of the stored procedure as villageproc in place of PROCEDURE
     NAME

    Enter the following SQL statements in the body of the stored procedure


          Select * from villageview
          return

    Click OK

   Executing a stored procedure

   To execute a stored procedure, simply invoke it by its name.

    Switch to Query Analyzer
    Type villageproc in the query Window
    Execute Query

   Rows from villageview are displayed in the result window

    Clear the Query window

Creating Triggers




                                             9
A trigger is a special type of stored procedure that is executed automatically as part of a
data modification statement (INSERT, UPDATE or DELETE). When one of the actions
for which the trigger is defined occurs, trigger is fired automatically.

The basic syntax for creating a trigger is
CREATE TRIGGER trigger _name
ON table _name
FOR {INSERT | UPDATE | DELETE}
AS
SQL statements

From the Enterprise Manager,

Right-click the table
Choose All Tasks,
Choose Manage Triggers…
Type the SQL statements

Sample Triggers

The following is an example of cascaded delete trigger

drop table orders
go
drop table customers
go
create table customers
(customer_id int primary key not null,
customer_name nvarchar(50) not null,
customer_comments nvarchar(22) null)
go
create table orders
(order_id int primary key not null,
customer_id int constraint fk references customers(customer_id),
order_date datetime)
go
insert customers values (1, 'hardware suppliers', 'rac is contact')
insert customers values (2, 'software suppliers', 'ras is contact')
insert customers values (3, 'firmware suppliers', 'raj is contact')
go
insert orders values (100,1,getdate())
insert orders values (200,1,getdate())
insert orders values (300,1,getdate())
go

select * from customers



                                             10
go
select * from orders
go

create trigger cust_del_orders on customers
for delete
as
if @@rowcount = 0
return
delete orders
from orders o , deleted d
where o.customer_id = d.customer_id
if @@error <> 0
begin
  raiserror ('error encountered' , 16,1)
  rollback tran
return
end

go

alter table orders nocheck constraint fk
go
delete customers where customer_id =1
go

select * from customers
go
select * from orders
go

The following is an example of cascaded update trigger

drop table orders
go
drop table customers
go
create table customers
(customer_id int primary key not null,
customer_name nvarchar(50) not null,
customer_comments nvarchar(22) null)
go
create table orders
(order_id int primary key not null,
customer_id int constraint fk references customers(customer_id),
order_date datetime)



                                           11
go
insert customers values (1, 'hardware suppliers', 'rac is contact')
insert customers values (2, 'software suppliers', 'ras is contact')
insert customers values (3, 'firmware suppliers', 'raj is contact')
go
insert orders values (100,1,getdate())
insert orders values (200,1,getdate())
insert orders values (300,1,getdate())
go

select * from customers
go
select * from orders
go

create trigger cust_upd_orders on customers
for update
as
declare @row_affected int , @c_id_before int, @c_id_after int
select @row_affected = @@rowcount
if @row_affected = 0
return
if update(customer_id)
begin
  if @row_affected = 1
   begin
     select @c_id_before = customer_id from deleted
     select @c_id_after = customer_id from inserted
     update orders
         set customer_id = @c_id_after
     where
         customer_id = @c_id_before
   end
   else
   begin
         raiserror('can not update more than 1 row',16,1)
         rollback tran
         return
   end
end
go

alter table orders nocheck constraint fk
go
update customers set customer_id = 4 where customer_id =1
go



                                          12
select * from customers
go
select * from orders
go

    Publishing Data on the Internet

    Expand the Database folder and then the database Cabin42
    Select Tools from the Enterprise Manager tool bar
    Select Wizards…

    Select Wizard dialog box appears on the screen.

    Expand Management
    Select Web Assistant Wizard
    Click OK

   Web Assistant Wizard launches on the screen.

    Click Next to continue
    Select the Database cabin42, from which you want to publish data on internet
    Click Next to continue

   In the Start a New Web Assistant Job screen

    Choose the default name for the job
    Choose the option Result set(s) of a stored procedure I select
    Click Next to continue

   In the Select stored procedure screen

    Select the villageproc stored procedure
    Click Next to continue

   In the Schedule the Web Assistant Job screen

    Select the option When the SQL Server data changes
    Click Next to continue

   In the Monitor a Table and Columns screen

      Select the village table
      Select the vill_name and vill_pop fields
      Click Add
      Click Next to continue



                                           13
   In the Publish the Web Page screen

    Type C:\village.htm as file name
    Click Next to continue

   In the Format the Web Page screen

    Select Yes, help me format the Web page option
    Take the default values for Use Character set option
    Click Next to continue

   In the Specify Titles screen

    Select the default options
    Click Next to continue

   In the Format a Table screen

    Select the default options
    Click Next to continue

   In the Add Hyperlinks to the Web page screen

    Select No
    Click Next to continue

   In the Limit rows screen

      Select No, return all rows of data option
      Select No, put all data in one scrolling page option
      Click Next to continue
      Click Finish to complete the wizard
      Click OK in the Successfully completed message dialog box

   To see your data on the Internet

    Open your file, village.htm in the browser.

Trigger Exercise

Write a Trigger for Orders table for Insert action where before inserting into orders
table trigger is fired to check corresponding customer_id in customer table. In case it
does not exsist transaction should be aborted.




                                            14
15