HA150 SQL Basics for SAP HANA
. . COURSE OUTLINE . Course Version: 12 Course Duration: 2 Day(s)
SAP Copyrights and Trademarks
© 2016 SAP SE
or an SAP affiliate company. All rights reserved.
No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an SAP affiliate company. SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. Please see http://global http://global12.sap.com/corpora 12.sap.com/corporate-en/leg te-en/legal/ al/ copyright/index.epx for copyright/index.epx for additional trademark information and notices. Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors. National product specifications may vary. These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP SE or its affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP SE or SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or any related presentation, or to develop or release any functionality functionality mentioned therein. This document, or any related presentation, and SAP SE’s or its affiliated companies’ strategy and possible future developments, products, and/or platform directions and functionality are all subject to change and may be changed by SAP SE or its affiliated companies companies at any t ime for any reason without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. All forward-looking statements are subject to various risks and uncertainties uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.
SAP Copyrights and Trademarks
© 2016 SAP SE
or an SAP affiliate company. All rights reserved.
No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an SAP affiliate company. SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. Please see http://global http://global12.sap.com/corpora 12.sap.com/corporate-en/leg te-en/legal/ al/ copyright/index.epx for copyright/index.epx for additional trademark information and notices. Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors. National product specifications may vary. These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP SE or its affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP SE or SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or any related presentation, or to develop or release any functionality functionality mentioned therein. This document, or any related presentation, and SAP SE’s or its affiliated companies’ strategy and possible future developments, products, and/or platform directions and functionality are all subject to change and may be changed by SAP SE or its affiliated companies companies at any t ime for any reason without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. All forward-looking statements are subject to various risks and uncertainties uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.
Typographic Conventions American English is the standard used in this handbook. The following typographic conventions are also used.
This information is displayed in the instructor’s presentation
Demonstration
Procedure
Warning or Caution
Hint
Related or Additional Information
Facilitated Discussion
User interface control
Example text
Window title
Example text
© Copyright. All rights reserved.
© Copyright. All rights reserved.
Contents vii
Course Overview
1
Unit 1:
Motivation and Basic Concepts
1
Lesson: Understanding Motivation and Basic Concepts
1
Lesson: Understanding the Sample Data
3
Unit 2:
3 5
Lesson: Using Data from a Table or View Unit 3:
5 7
Unit 4:
Unit 5:
Unit 6:
Unit 7:
Unit 8:
Data Storage Lesson: Defining How Data is Stored
Unit 9:
17 19
Data Stored in Tables Lesson: Changing Data Stored in Tables
15 17
NULL Values Lesson: Understanding NULL Values
13 15
Data From Multiple Tables Part II Lesson: Reading Data from Multiple Tables- Part 2
11 13
Data From Multiple Tables Part I Lesson: Reading Data from Multiple Tables- Part 1
9 11
Aggregation of Data Lesson: Aggregating Data
7 9
Data From a Table Or View
Views For Data Access Lesson: Using Views for Data Access
Unit 10:
Functions and Procedures
19
Lesson: Creating User-Defined Functions
19
Lesson: Creating Database Procedures
21
Unit 11:
21 23 23
Data Access Lesson: Defining Data Access
Unit 12:
Database Transactions Lesson: Explaining Database Transactions
© Copyright. All rights reserved.
© Copyright. All rights reserved.
Course Overview
TARGET AUDIENCE This course is intended for the following audiences: ●
Application Consultant
●
Development Consultant
●
Technology Consultant
© Copyright. All rights reserved.
© Copyright. All rights reserved.
UNIT 1
Motivation and Basic Concepts
Lesson 1: Understanding Motivation and Basic Concepts Lesson Objectives After completing this lesson, you will be able to: ●
Understand the motivation for and foundation of the relational model
●
Understand SQL and its relation to the relation model
●
Understand database tables as the most important database objects
Lesson 2: Understanding the Sample Data Lesson Objectives After completing this lesson, you will be able to: ●
Understand the sample database used throughout the course
© Copyright. All rights reserved.
Unit 1: Motivation and Basic Concepts
© Copyright. All rights reserved.
UNIT 2
Data From a Table Or View
Lesson 1: Using Data from a Table or View Lesson Objectives After completing this lesson, you will be able to: ●
Write simple database queries using SQL‘s SELECT statement and project columns in and out of queries using the SELECT clause
●
Calculate column values, use built-in functions and the CASE clause in column lists
●
Avoid duplicates in SELECT statement result sets
●
Limit results sets to a given number of rows and browse through result sets
●
Ensure a specific order in result sets
●
Restrict the result set using the WHERE clause
© Copyright. All rights reserved.
Unit 2: Data From a Table Or View
© Copyright. All rights reserved.
UNIT 3
Aggregation of Data
Lesson 1: Aggregating Data Lesson Objectives After completing this lesson, you will be able to: ●
List the most important aggregate functions supported by HANA and use them to determine aggregated values on table columns using a single SELECT statement
●
Determine aggregated values for groups of rows, using the GROUP BY clause
●
Filter groups using the HAVING clause
© Copyright. All rights reserved.
Unit 3: Aggregation of Data
© Copyright. All rights reserved.
UNIT 4
Data From Multiple Tables Part I
Lesson 1: Reading Data from Multiple Tables- Part 1 Lesson Objectives After completing this lesson, you will be able to: ●
●
Combine the result of several select statements using the UNION, INTERSECT and EXCEPT statements List the various types of JOIN constructs and use the appropriate JOIN construct to combine data from several tables using a single query
© Copyright. All rights reserved.
Unit 4: Data From Multiple Tables Part I
© Copyright. All rights reserved.
UNIT 5
Data From Multiple Tables Part II
Lesson 1: Reading Data from Multiple Tables- Part 2 Lesson Objectives After completing this lesson, you will be able to: ●
Explain the difference between uncorrelated and correlated sub-queries and use both to query data from multiple tables in a single select statement
© Copyright. All rights reserved.
Unit 5: Data From Multiple Tables Part II
© Copyright. All rights reserved.
UNIT 6
NULL Values
Lesson 1: Understanding NULL Values Lesson Objectives After completing this lesson, you will be able to: ●
Interpret NULL values in databases and understand why their presence can lead to unexpected query results
© Copyright. All rights reserved.
Unit 6: NULL Values
© Copyright. All rights reserved.
UNIT 7
Data Stored in Tables
Lesson 1: Changing Data Stored in Tables Lesson Objectives After completing this lesson, you will be able to: ●
Add rows to database tables using SQL
●
Change existing rows of a database table
●
Remove existing rows from a database table
© Copyright. All rights reserved.
Unit 7: Data Stored in Tables
© Copyright. All rights reserved.
UNIT 8
Data Storage
Lesson 1: Defining How Data is Stored Lesson Objectives After completing this lesson, you will be able to: ●
List the most important data types SAP HANA supports
●
Create new database tables in HANA
●
Change tables by adding, removing or renaming columns
© Copyright. All rights reserved.
Unit 8: Data Storage
© Copyright. All rights reserved.
UNIT 9
Views For Data Access
Lesson 1: Using Views for Data Access Lesson Objectives After completing this lesson, you will be able to: ●
Describe the use cases for and advantages of using database views, define database views and use them in queries
© Copyright. All rights reserved.
Unit 9: Views For Data Access
© Copyright. All rights reserved.
UNIT 10
Functions and Procedures
Lesson 1: Creating User-Defined Functions Lesson Objectives After completing this lesson, you will be able to: ●
Create and use scalar and table user-defined functions
Lesson 2: Creating Database Procedures Lesson Objectives After completing this lesson, you will be able to: ●
Create and use database procedures in SAP HANA and know how to debug them
© Copyright. All rights reserved.
Unit 10: Functions and Procedures
© Copyright. All rights reserved.
UNIT 11
Data Access
Lesson 1: Defining Data Access Lesson Objectives After completing this lesson, you will be able to: ●
Understand database schemas and access tables in other schemas
●
Explain when database indexes make sense in SAP HANA and create and delete indexes using SQL
© Copyright. All rights reserved.
Unit 11: Data Access
© Copyright. All rights reserved.
