1 / 37

Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008. Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008. Overview of SQL Server 2008 Overview of SQL Server Databases Overview and Syntax Elements of T-SQL Working with T-SQL Scripts

masato
Download Presentation

Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

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. Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

  2. Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008 • Overview of SQL Server 2008 • Overview of SQL Server Databases • Overview and Syntax Elements of T-SQL • Working with T-SQL Scripts • Using T-SQL Querying Tools

  3. Lesson 1: Overview of SQL Server 2008 • Overview of Client/Server Architecture • SQL Server Components • SQL Server Management Tools • SQL Server Database Engine Components

  4. Overview of Client/Server Architecture Two-tier Multitier SQL Database Server SQL Database Server OLE DB Components OLE DB Components Business Logic Business Logic Application Server Display Code Display Code Client Computer Client Computer

  5. SQL Server Components The Database Engine also features these components: • Replication • Full-Text Search • Notification Services • Service Broker

  6. SQL Server Management Tools

  7. SQL Server Database Engine Components

  8. Lesson 2: Overview of SQL Server Databases • Overview of Relational Databases • What Is Normalization? • The Normalization Process • SQL Server Database Objects • Overview of Data Types

  9. Overview of Relational Databases • Relational databases store data in multiple interrelated tables • The tables have one-to-many relationships

  10. What Is Normalization? The process for removing redundant data from a database Benefits Accelerates sorting and indexing ü Allows more clustered indexes ü Helps UPDATE performance ü More compact databases ü Disadvantages • Slower data retrieval • Increase in tables to join • Difficulty in data model query • Insertion of code in tables

  11. The Normalization Process • First Normal Form • Second Normal Form • Third Normal Form

  12. SQL Server Database Objects

  13. Overview of Data Types Each column, variable, expression, parameter has a data type A data type specifies the type of data the object can hold: integers, characters, monetary data, date and time, binary Data types are organized into the following categories: • Unicode character strings • Exact numerics • Binary strings • Approximate numerics • Date and time • Other data types • Character strings

  14. Lesson 3: Overview and Syntax Elements of T-SQL • A History and Definition of ANSI SQL and T-SQL • Categories of SQL Statements • Introduction to Basic T-SQL Syntax • Types of T-SQL Operators • What Are T-SQL Functions? • What Are T-SQL Variables? • What Are T-SQL Expressions? • Control-of-flow Statements

  15. A History and Definition of ANSI SQL and T-SQL • Developed in the early 1970s • ANSI-SQL defined by the American National Standards Institute • Microsoft implementation is T-SQL, or Transact SQL • Other implementations include PL/SQL and SQL Procedural Language.

  16. Categories of SQL Statements • DML – Data Manipulation Language INSERT Table1 VALUES (1, 'Row #1') • DCL – Data Control Language GRANT CONNECT TO guest; GRANT CONTROL SERVER TO user_name; • DDL – Data Definition Language CREATE USER user_name • TCL - Transactional Control Language COMMIT TRANSACTION Inner2; • DQL - SQL Select Statements SELECT ProductID, Name, ListPrice FROM Production.Product

  17. Introduction to Basic T-SQL Syntax There are four primary properties to the SELECT statement The number and attributes of the columns in the result set 1 The tables from which the result set data is retrieved 2 The conditions the rows in the source tables must meet 3 The sequence which the rows of the result set are ordered 4 SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice > $40 ORDER BY ListPrice ASC

  18. Types of T-SQL Operators

  19. What Are T-SQL Functions?

  20. What Are T-SQL Variables? A Transact-SQL local variable is an object that can hold a single data value of a specific type Variables in batches and scripts are typically used to: • Count the number of times a loop is performed • Hold data to be tested by a control-of-flow statement • Save data values to be returned by a function return value DECLARE @local_variable as <data_type> declare @food varchar(20) set @food = 'ice cream' … WHERE Description = @food

  21. What Are T-SQL Expressions? Symbols and operators evaluated to obtain a single value Expressions can be combined if one of these is true: • The expressions have the same data type • The data type with the lower precedence can be converted to the data type with the higher precedence SELECT ProductID, Variable_N +2

  22. Control-of-flow Statements These are the control-of-flow keywords: • BREAK • BEGIN...END • CONTINUE • GOTO • WHILE • IF...ELSE • WAITFOR • RETURN IF Boolean_expression BEGIN { sql_statement | statement_block } END ELSE { sql_statement | statement_block }

  23. Lesson 4: Working with T-SQL Scripts • What Are Batch Directives? • Structured Exception Handling • Commenting T-SQL Code

  24. What Are Batch Directives? • These control movement within a T-SQL file IF @cost <= @compareprice BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.‘ GO

  25. Structured Exception Handling TRY/CATCH BEGIN TRY -- Generate divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- Execute error retrieval routine. EXECUTE usp_GetErrorInfo; END CATCH; RAISERROR RAISERROR (N'This is message %s %d.', -- Message text. 10, -- Severity, 1, -- State, N'number', -- First argument. 5); -- Second argument. -- The message text returned is: This is message number 5. GO

  26. Commenting T-SQL Code • Comments are statements about the meaning of the code • When used, there is no execution performed on the text There are two ways to comment code using T-SQL: • The use of a beginning /* and ending */ creates comments /* This is a comment */ • The double dash comments to the end of line --This is a comment

  27. Lesson 5: Using T-SQL Querying Tools • Tools for Querying SQL Server 2008 Databases • An Introduction to SQL Server Management Studio • What Is a SQL Server Solution? • Creating SQL Server Solutions • Executing Queries in SQL Server Management Studio • Generating Reports in Microsoft Office Excel

  28. Tools for Querying SQL Server 2008 Databases

  29. An Introduction to SQL Server Management Studio • Support for writing and editing queries or scripts • Integrated source control for solution and script projects • Supports most administrative tasks for SQL Server • An integrated Web browser for quick browsing

  30. What is a SQL Server Solution? SQL Server Management Studio provides two containers for managing database projects: Projects Solutions A solution includes projects and files that define the solution A project is a set of files, plus related metadata When you create a project, a solution is created to contain it

  31. Creating SQL Server Solutions Solutions contain scripts, queries, connection information and files that you need to create your database solution Use these containers to: • Implement source control on queries and scripts • Manage settings for your solution • Handle the details of file management • Add items useful to multiple projects in to one solution • Work on miscellaneous files independent from solutions

  32. Executing Queries in SQL Server Management Studio • Executing queries occurs when in a query session by: • Create queries by interactively entering them in a query window • Load a file that contains T-SQL and then execute commands or modify then execute • Selecting the Execute Icon • Pressing the F5 key

  33. Generating Reports in Microsoft Office Excel Connect to Database Server 1 Select Database and Table 2 Save Data Connection File 3 Import Data and Select Format 4

  34. Lab: Using SQL Server Management Studio and SQLCMD • Exercise 1: Explore the components and execute queries in SQL Server Management Studio • Exercise 2: Start and use sqlcmd • Exercise 3: Generate a report from a SQL Server database using Microsoft Office Excel Logon information Estimated time: 60 minutes

  35. Lab Scenario • You are the database administrator of Adventure Works. The Human Resources department requires that you generate several reports by using SQL Server 2008. You need to generate reports with a list of employee addresses sorted by departments or a list of addresses of employees residing in the United States. You also need to analyze the details of the newly hired employees by using a database diagram in SQL Server Management Studio. • To do this, you will explore and execute queries in SQL Server Management Studio, generate reports from the AdventureWorks database, and examine the database diagram in SQL Server Management Studio.

  36. Lab Review • Why would we execute a T-SQL command using sqlcmd? • What tools did we use in the lab to query the database? • How is Excel useful when querying SQL Server 2008 databases?

  37. Module Review and Takeaways • Review Questions • Best Practices • Tools

More Related