1 / 23

MS SQL Sever 2005

MS SQL Sever 2005. SQL Sever Management Studio Express. Introduction. Installation At home With software package Without Login in Building Database At home Working with SQL Differences Notes Other resources . Installation .

Gideon
Download Presentation

MS SQL Sever 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Cont’d • For Those that didn’t buy the software. You can download it from this site. • http://msdn.microsoft.com/vstudio/express/sql/download

  5. Installation Cont’d • 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 Cont’d • To install MS SQL Sever 2005 (if not using software package from the bookstore) • It’s simple to install. • Run install file. • Following directions. • That’s 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(*) as‘total 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 22:57:29 ******/ 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_CP1_CI_AS NULL, [state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [zip] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [area_code] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [phone_number] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, PRIMARY KEY CLUSTERED ( [account_number] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF 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 >=, <= or <> • Selectable line execute • Can use [item] > ‘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 id=6400 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 id=1234 commit; select * from person where id=1234 BEGIN TRANSACTION delete from person where id =1234 select * from person where id=1234 rollback; select * from person where id=1234

  21. 2 1 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/learning/default.aspx • Word • download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/MgSQLExpwSSMSE.doc

  23. All Done

More Related