MS SQL Sever 2005

1
MS SQL Sever 2005

• SQL Sever Management Studio Express

2
Introduction

• Installation
• At home
• With software package
• Without
• Login in
• Building Database
• At home
• Working with SQL
• Differences
• Notes
• Other resources

3
Installation

• If you have bought Visual Studios from the book
  store and installed it, MS SQL Sever will be
  installed.
• You can update the installation with the other
  discs provide so you can get all the
  functionality.
• The installation will take some time and requires
  other Windows components to be installed.

4
Installation Contd

• For Those that didnt buy the software. You can
  download it from this site.
• http//msdn.microsoft.com/vstudio/express/sql/down
  load

5
Installation Contd

• There is a down side for the people that bought
  the software package. It does not come with MS
  SQL Sever Management Studio Express.
• You can download it from the site on the pervious
  side.

6
Installation Contd

• To install MS SQL Sever 2005 (if not using
  software package from the bookstore)
• Its simple to install.
• Run install file.
• Following directions.
• Thats it. ?
• To install SQL Sever Management Studio Express
• As before run the installation file.
• Following until you get to this window
• You will get a list of components that needed to
  be install before hand. If there is an error it
  will be displayed, follow error and it will be
  resolved.
• Next will be futures that you can install, you
  can do as you wish in this section.
• Next will be the sever instance window, use can
  use the default or an named instance. Chose named
  for this section, and leave the default text.
• Next is existing components, just hit next.
• Next is error reporting and usage report setting,
  select the second option and hit next
• That should be it. ?
• Notes
• For the SQL Sever 2005 update option for the
  people that bought the package from the
  bookstore, run the CD(32-bit for people that have
  a 32-bit CPU or the 64-bit CPU for the lucky
  people out there) you can look at the hardware
  and software requirements from the install menu.
• You will need roughly 1GB of ram (for the
  developers version and about 2.9 GB of hard drive
  space for a full installation.

7
Starting in SQL Sever Management Studio Express

• At home
• Using the default instance just hit connect and
  you will be connected to your home sever.
• At school
• Using cswin2k5 as sever name.
• SQL sever authentication.
• Your name, so DHenry
• And a password of conestoga

Use caps for First two letters
8
Once inside-At Home
Step 1.Open database tab
Step 2. Right click, add new DB (more information
next slide)
Step 3. click on new query.
9
Step 1. Name the db.
Step 2. Hit ok
10
Once Inside-At Home (build ddc)
Step 2. use the ddcbuild (copy into this window)
Step 1. Make sure you have selected the db that
you have created.
Step 3. hit the execute button and let it do it
magic
11
It takes time

• It will take around 4 minutes to build the
  database. Just wait for it. ?
• After it is done, quit and reload Management
  Studio Express. The database will be there.

12
Using MS SQL Sever
Query window
Result options. Left (to text) Middle (to
table) Right (to file)
Results
Templates
Tables
13
MS SQL Code

• It is very similar to oracle, MS makes it a
  little easier for certain things for example
  dates and in line math functions
• Here are some functions that you can use.
• SUM the field
• SYNTAX SUM(field) as alias
• COUNT records
• SYNTAX SELECT COUNT() astotal orders from
  Orders
• MIN finds min value
• SNTAX SELECT MIN(field) FROM Products
• MAX finds max vale
• SNTAX SELECT MAX(field) FROM Products
• Avg finds the average
• SNYTAX SELECT Avg(field) FROM Products
• Month and day funtions ( find month and day)
• SNYTAX Month(field) or day(field)
• GetDate() gets the date
• SNYTAX SELECT GetDate

14
Differences

• The main difference you can see it in table
  creation.
• There are two ways to make a table in MS Sever.
• Though SQL code
• using table creator (similar to access)
• Rollback, insert, commit and delete are
  different.
• Will be shown later in presentation.

15
Making Tables in MS SQL Using SQL

• USE dcc_MSSQL
• GO
• / Object Table dbo.account Script
  Date 11/05/2006 225729 /
• SET ANSI_NULLS ON
• GO
• SET QUOTED_IDENTIFIER ON
• GO
• SET ANSI_PADDING ON
• GO
• CREATE TABLE dbo.account(
• account_number int NOT NULL,
• account_name varchar(25) COLLATE
  SQL_Latin1_General_CP1_CI_AS NULL,
• account_type_code char(1) COLLATE
  SQL_Latin1_General_CP1_CI_AS NULL,
• customer_id int NULL,
• credit_limit numeric(9, 2) NULL,
• balance numeric(9, 2) NULL,
• street varchar(45) COLLATE
  SQL_Latin1_General_CP1_CI_AS NULL,
• city varchar(25) COLLATE SQL_Latin1_General_C
  P1_CI_AS NULL,
• state char(2) COLLATE SQL_Latin1_General_CP1_
  CI_AS NULL,

Table Name
Field name, type, not null
Primary key
16
Making tables with table creator
Primary Key Relationships diagram
Allows for Nulls
Column name and data type
Table properties
Column properties
17
Adding constraints, keys or columns
You can drop down option by right clicking the tab
Right click, then pick option to add
18
More notes

• -- mean to comment a line of text
• Using single quotes for all.
• Use to concatenate
• Use gt, lt or ltgt
• Selectable line execute
• Can use item gt 1/10/2006 to query table
  (midnight is assumed)

19
Rollback, Commit, insert And Deletion

• MS has made the rollback feature a little
  different compare to oracle example.
• select from person where id6400
• BEGIN TRANSACTION delete from person where id
  6400
• Rollback
• When using the rollback you have to have the
  being transaction statement, this allows the
  rollback to find the transaction that needs to be
  rolled back.

20
Rollback, Commit, insert And Deletion

• All four are used in this example to show how MS
  has use these futures
• BEGIN TRANSACTION insert into person values
  (1234,'dave','henry','j','1234','dasfd','CO',80256
  ,303,5554268)
• select from person where id1234
• commit
• select from person where id1234
• BEGIN TRANSACTION delete from person where id
  1234
• select from person where id1234
• rollback
• select from person where id1234

21
1
2
3
4
1 insert
2 select and commit
3 delete
4 rollback and select
22
Other support documentation

• Here are some things you can use to help learn MS
  SQL 2005
• Videos
• http//msdn.microsoft.com/vstudio/express/sql/lear
  ning/default.aspx
• Word
• download.microsoft.com/download/4/f/8/4f8f2dc9-a9a
  7-4b68-98cb-163482c95e0b/MgSQLExpwSSMSE.doc

23
All Done