1 / 35

Rationale

Aspiring Database Developers should be able to efficiently query and maintain databases. This module will help students learn the Structured Query Language (SQL) to query and manage databases. Rationale. In this session, you will learn to: Appreciate SQL Server 2005 as a database server

nyx
Télécharger la présentation

Rationale

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. Aspiring Database Developers should be able to efficiently query and maintain databases. This module will help students learn the Structured Query Language (SQL) to query and manage databases. Rationale

  2. In this session, you will learn to: Appreciate SQL Server 2005 as a database server Identify the SQL Server 2005 tools Retrieve data Objectives

  3. Business applications consist of three elements: The user interface or the presentation element The application logic or the business rule element The data storage or the data management element The application architectures can be categorized as: Single-tier architecture Two-tier architecture Three-tier architecture N-tier architecture Role of a Database Server

  4. Reporting Services • Analysis Services • Integration Services • Database Engine • Service Broker • Full-Text Search • Notification Services • Replication SQL Server 2005 Components • SQL Server 2005 consists of the following components.

  5. Microsoft SQL Server 2005 is integrated with the .NET Framework. • Reporting Services • Analysis Services • Integration Services • Database Engine • Service Broker • Full-Text Search • Notification Services • Replication SQL Server 2005 Components (Contd.) .NET Framework

  6. Which of the following services of SQL Server 2005 allows you to implement message-based communication? Full-text search Service Broker Notification services Replication Just a minute • Answer: • 2. Service Broker

  7. Which of the following services of SQL Server 2005 allows you to gather and integrate data from disparate data sources in an organization? Analysis services Integration services Notification services Replication Just a minute • Answer: • 2. Integration services

  8. SQL Server 2005 provides the following features: Built-in support for Extensible Markup Language (XML) data CLR integration Scalability Service-oriented architecture Support for Web services High level of security High availability Support for data migration and analysis Features of SQL Server 2005

  9. SQL is the core language used to: Store data Retrieve and manipulate data Modify data SQL can be categorized as: Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Data Query Language (DQL) Structured Query Language (SQL)

  10. Which of the following features of SQL Server 2005 allow the developers to implement their programming logic in any language supported by the .NET framework? Support for data migration High availability CLR integration Scalability Just a minute • Answer: • 3. CLR integration

  11. SQL Server 2005 provides the following tools: SQL Server Management Studio SQL Server Business Intelligence Development Studio Database Engine Tuning Advisor SQL Server Configuration Manager Identifying SQL Server 2005 Tools

  12. Which of the following tools of SQL Server 2005 allow starting and stopping the full-text search? 1. SQL Server Management Studio 2. Business Intelligence Development Studio 3. Database Engine Tuning Advisor 4. SQL Server Configuration Manager Just a minute • Answer: • 4. SQL Server Configuration Manager

  13. Data types: Specify the type of data that an object can contain Commonly used by SQL Server are: int float char varchar Identifying Data Types

  14. Specific attributes can be retrieved by specifying selected column names in the SELECT statement. Syntax: SELECT [ALL | DISTINCT] select_column_list [INTO [new_table_name]] FROM {table_name | view_name} [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC]] [COMPUTE {{ AVG | COUNT | MAX | MIN | SUM } ( expression ) } [,...n ] [ BY expression [ ,...n ] ] Let’s see how… Retrieving Specific Attributes

  15. Display of the result set can be customized using: User-defined headings Literals Concatenation operators Let’s see how… Retrieving Specific Attributes (Contd.)

  16. Column values can be calculated using arithmetic operators: + (for addition) - (for subtraction) / (for division) * (for multiplication) % (for modulo) Let’s see how… Retrieving Specific Attributes (Contd.)

  17. Selected rows can be retrieved using the WHERE clause in the SELECT statement. Let’s see how… Retrieving Selected Rows

  18. Comparison operators: Can be used with WHERE clause to create conditions Supported by SQL Server are: = (equal to) > (greater than) < (less than) and many more Syntax: SELECT column_list FROM table_name WHERE expression1 comparison_operator expression2 Let’s see how… Retrieving Selected Rows (Contd.)

  19. Logical operators: Can be used with SELECT statement to retrieve records based on one or more matching conditions Supported by SQL Server are: AND OR NOT Syntax: SELECT column_list FROM table_name WHERE conditional_expression1 {AND/OR} [NOT] conditional_expression2 Let’s see how… Retrieving Selected Rows (Contd.)

  20. Range operators: Can be used with SELECT statement to retrieve records based on a range Supported by SQL Server are: BETWEEN NOT BETWEEN Syntax: SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression3 Let’s see how… Retrieving Selected Rows (Contd.)

  21. Just a minute • Which of the following are logical operators? • 1. BETWEEN and NOT BETWEEN • 2. AND, OR, and NOT • 3. + and % • 4. > and < • Answer: • 2. AND, OR, and NOT

  22. IN keyword: Allows the selection of values that match any one of the values in a list NOT IN keyword: Restricts the selection of values that match any one of the values in a list Syntax: SELECT column_list FROM table_name WHERE expression list_operator (‘value_list’) Let’s see how… Retrieving Selected Rows (Contd.)

  23. LIKE keyword: Can be used to search for character string, date, or time values that match a specified pattern Uses wildcard characters like *, % to perform pattern match Let’s see how… NULL values: Can be retrieved by using IS NULL keyword with SELECT statement Syntax: SELECT column_list FROM table_name WHERE column_name unknown_value_operator Let’s see how… Retrieving Selected Rows (Contd.)

  24. ORDER BY clause: Can be used with the SELECT statement to display records in a specific order Displays record in ascending or in descending order Syntax: SELECT select_list FROM table_name [ORDER BY order_by_expression [ASC|DESC] [, order_by_expression [ASC|DESC]…] Let’s see how… Retrieving Selected Rows (Contd.)

  25. TOP keyword: Can be used with the SELECT statement to retrieve only the first set of rows, which can be either a number or a percent of rows Syntax: SELECT [TOP n [PERCENT]] column_name [,column_name…] FROM table_name WHERE search_conditions [ORDER BY [column_name[,column_name…] Let’s see how… Retrieving Selected Rows (Contd.)

  26. DISTINCT keyword: Can be used with the SELECT statement to eliminate duplicate rows Syntax: SELECT [ALL|DISTINCT] column_names FROM table_name WHERE search_condition Let’s see how… Retrieving Selected Rows (Contd.)

  27. Just a minute • Write a query to display all the records of the ProductModel table where the product name begins with HL. • Answer: • SELECT * FROM Production.ProductModel WHERE Name LIKE 'HL%'

  28. Problem Statement: You are a database developer of AdventureWorks, Inc. The AdventureWorks database is stored on the SQLSERVER01 database server. The details of the sales persons are stored in the SalesPerson table. The management wants to view the details of the top three sales persons who have earned a bonus between $4,000 and $6,000. How will you generate this report? Demo: Retrieving Data

  29. Solution: To solve the preceding problem, you need to perform the following tasks: 1. Create a query. 2. Execute the query to generate the report. Demo: Retrieving Data (Contd.)

  30. In this session, you learned that: A business application can have three elements: user interface, business logic, and data storage. A database server is used to store and manage database in a business application. SQL Server 2005 consists of the following four core components: database engine, integration services, analysis services, and reporting services. The database engine provides support to store, query, process, and secure data on the database server. Integration services allow you to gather and integrate data from disparate data sources at a common location in a consistent format. Summary

  31. Analysis services provide data mining solutions that help in data analysis. Reporting services provide support to generate comprehensive reports on the data stored in the database engine or the data warehouse. Microsoft SQL Server 2005 is integrated with the .NET Framework. The .NET Framework is an environment used to build, deploy, and run business applications through various programming languages. The .NET Framework consists of three components: development tools and languages, base class library, and CLR. Summary (Contd.)

  32. SQL Server 2005 provides the following benefits: Built-in support for XML CLR integration Scalability Service-oriented architecture Support for Web services High level of security High availability Support for data migration and analysis SQL includes: DDL: To create and manage database objects DML: To store and manage data in database objects DCL: To allow or deny access to database objects DQL: To query data from the database objects Summary (Contd.)

  33. SQL Server 2005 provides the following tools to improve the efficiency of the database developers and manage the server: SQL Server Management Studio SQL Server Business Intelligence Management Studio Database Engine Tuning Advisor SQL Server Configuration Manager Data can be retrieved from a database by using the SELECT statement. Data of all the columns of a table can be retrieved by specifying * in the select query. Data that has to be retrieved based on a condition is specified by adding the WHERE clause. Literals and user-defined headings are added to change the display. The concatenation operator is used to concatenate a string expression. Summary (Contd.)

  34. Arithmetic operators are used to perform mathematical operations. Comparison operators test the similarity between two expressions. Logical operatorsare used in the SELECT statement to retrieve records based on one or matching conditions. The logical operators are AND, OR, and NOT. The Range operator retrieves data based on the range. There are of two types of range operators, BETWEEN and NOT BETWEEN. The IN keyword allows the selection of values that match any one of the values in a list. The NOT IN keyword restricts the selection of values that match any one of the values in a list. Summary (Contd.)

  35. The LIKE keyword is used to specify the pattern search. The IS NULL keyword is used to retrieve missing values. The ORDER BY clause is used to retrieve data in a specific order. The TOP keyword retrieves only the first set of rows, which can either be a number or a percent of rows that will be returned from a query result. The DISTINCT keyword eliminates duplicate rows. Summary (Contd.)

More Related