SAP HA150

For Any SAP / IBM / Oracle - Materials Purchase Visit : www.erpexams.com OR Contact Via Email Directly At : [email protected]

HA150 SQL Basics for SAP HANA SAP HANA

Date Training Center Instructors

Education Website


Participant Handbook Course Version: 08 Course Duration: 2 Day(s) Material Number: 50125187

An SAP course - use it to learn, reference it for work



Copyright C op yr ight © 2014 SAP AG or an SAP affi l iate company. All r ig hts reserved.

No part of this publicat ion may be reprodu ce d or transmitted in any form or for any pu rpose

without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other soflware vendors.

Trademarks Adobe, the Adobe logo, Acrobat, Postscript, and Reader are trademarks or r egistered trademarks of Adobe Systems Incorporated in the United States and other countri es.

A pple, App Store,FaceTime, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or regi stered trademarks of Apple Inc. Bluetooth is a registered trademark of Bluetooth SIG Inc. Citrix, ICA, Program Neighborhood, MetaFrame now XenApp, WinFrame, VideoFrame, and l'vfultiWin are trademarks or regi stered trademarks of Ci trix Systems Inc. C omputop is a regi stere d trademark of Computop Wirtschaftsinformatik GmbH. Edgar Online is a registered trademark of EDGAR Online Inc., an R.R. Donnelley & Sons Company.

Facebook, the Facebook and F logo, FB, Face, Poke, Wall, and 32665 are trademarks ofFacebook. Google App Engine, Google Apps, G oogle Checkout, Google Data API, Google Maps, Google

Mob.ile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Up dater, Google Voice, Google Mail, Gmail, YouTube, Dalvik, and Android are trademarks or reg istered trademarks of Google Inc.

HP is a regi stered trademark of the Hewlett-Packard Development Company L.P. HTML, XML, XHTML, and W3C are trademarks, r egistered trademarks, or claimed as generic terms by the Massachusetts Institute of Technology (MJT), European Research Consortium for In formatics and Matbematics (ERCIM), or Keio University.

IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System zl 0, zl 0, zNM, z/OS, OS/390, zEnterprise, PowerVM, P ower Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETArN, DB2 Connect, RACF, Re dbook s, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Lnformix, and Smarter Planet are trademarks

or registered trademarks of IBM Corporation. l'vlicrosoft, Windows, Excel, Outlook, PowerPo int, Silverli ght, and Visual Studio are registered trademarks ofMicrosoft Corporation. INTERMEC is a re gist ered t rademar k of ln tem1ec Tech nolog ies Corporation. !OS is a registered trademark of Cisco Systems Inc. The Klout name and logos are trademarks or Kloul Inc.

Linux is the registered trademark of Linus Torvalds in the United States and other countries. Motorola is a registered trademark of Motorola Trademark Ho lding s LLC. Mozilla and Firefox and their logos are regi stered trademarks of the Mozilla Foundation. Novell and SUSE Linux En terprise Server are registered trademarks of Novell Inc.

g201462503652



OpenText is a registered trademark o f O penTe xt Corporation. Oracle and Java are registered trademarks of Oracle and its affiliates. QR Code is a registered trademark of Denso Wave Incorporated. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App\Vorld are trademarks or registered trademarks of Research in 1vlotion Limited. SAVO is a registered trademark of The Savo Group Ltd. The Skype aame is a trademark of Skype or related entities. Twitter and Tweet are trademarks or registered trademarks of Twitter. UNlX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Wi-Fi i s a registered trademark of \Vi-Fi Alliaace. SAP, R/3, ABAP, BAPJ, SAP NetW eaver

,

Duet, PartnerEdge,

ByDesign, SAP BusinessObjects

Explorer, StreamWork, SAP HANA, the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, ·web Intelligence, Xcelsius, Sybase, Adaptive Server, Adaptive Server Enterprise, iAnywhere, Sybase 365, SQL Anywhere, Crossgate, B2B 360° and B2B 360° Services, m@gic

EDDY, Ariba, the

Ariba logo,

Quadrem, b-process,

Ariba Discovery, SuccessFactors, Execution

is the Diflerence, BizX Mobile Toucbbase, It's time lo love work agaia, SuccessFacwrs Jam and BadAss Saas, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany or an SAP affiliate company. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves infom1ational purposes only. National product specifications may vary.

Disclaimer These materials

are subject

to change

without notice.

These materials

are provided by SAP AG and

its affiliated companies ("SAP Group") for infomiational purposes only, without representatioa or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warraniies for SAP Group 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.

g201462503652



g201462503652



About This Handbook This handbook is intended t o complement the instructor-led presentation of this course, and serve as a source of reference. It is not suitable for self-study.

Typographic Conventions American English is the standard used in this handbook. The following typographic conventions are also used. Type Style

Descrlpdon

Example text

Words or characters that appear on the screen. These include field names, screen titles, pushbuttons as \veil as

menu names, paths, and options.

Also used for cross-references to other documentation both internal and external. Example text

Emphasized words or phrases in body text titles of ,

graphics, and tables EXAMPLE TEXT

Names of elements in the system. These include report names, program names, transaction codes, table names, and individual key words of a programming language, when surrounded by body text, for example SELECT and INCLUDE.

Example text

Screen output. This includes file and directory na1nes and their paths, messages, names of variables and parameters, and passages of the source text of a program.

Example text

Exact user entry. These are words and characters that you enter in the system exactly as they appear in the documentation.

Variable user entry. Pointed brackets ind.icate that you replace these words and characters with appropriate entries.

2014

© 2014 SAP AG or an SAP affiliate company. All rights reserved.

v�



About This Handbook

HA150

Icons in Body Text The following icons are used in this handbook. Icon

Meaning

p

For more information, tips, or background

..

Note or further explanation of previous point

&

Exception or caution

~

Procedures

�

Indicates that the item is displayed in the instructor's presentation.

© 2014 SAP AG or an SAP affiliate company. .

VI

All rights reserved.

2014



Contents Course Overview .......................................................... ix Course Goals

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

ix

Course Objectives ..................................................... ix

Unit 1: Motivation and Basic Concepts .... ....... .................. 1 .

Motivation and Basic Concepts

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. . .

.

.

.

. .

.

.

.

.

.

.

.

.

.

2

Unit 2: Reading Data From a Table Or View ... ................... 25 .

Reading Data from a Table or View

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

26

Unit 3: Aggregating Data ................ . .. .. .......... ........... 65 .

Aggregating Data

.

.

.

.

.

.

.

.

.

..

.

.

.

.

..

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

66

Unit 4: Reading Data From Multiple Tables Part I ................ 81 Reading Data from Multiple Tables- Part 1 ........................ 82

Unit 5: Reading Data From Multiple Tables Part II .............. 111 Reading Data from Multiple Tables- Part 2 ....................... 112

Unit 6: Understanding NULL Values............................... 129 .

Understanding NULL Values

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

130

Unit 7: Changing Data Stored in Tables ........ ................ . 141 .

Changing Data Stored in Tables

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

142

Unit 8: Defining How Data Is Stored .. .. .. ....................... 157 .

Defining How Data is Stored

.

.

. . .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

158

Unit 9: Using Views For Data Access .. . .. ....... ............. 179 .

Using Views for Data Access

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

180

Unit 10: Defining Data Access ..... .. ..... .. ..................... 195 .

Defining Data Access

.

.

.

.

.

.

.

.

.

.

.

..

.

.

.

.

.

.

. . .

.

.

.

.

. .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

196

Unit 11: Database Transactions .............. ...................... 209 .

Database T ransactions

2014

.

.

. .

.

.

.

.

.

.

.

.

.

.

© 2014 SAP AG or an SAP affiliate company.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

..

.

.

.

.

.

210

vii �



Contents

HA150

© 2014 SAP AG or an SAP affiliate company. ...

VIII


2014



Course Overview [Enter a brief overview of the course.]

Target Audience This course is intended for the following audiences: •

Application Consultants, Developn1ent Consultants with no or little experience in using SQL.

Course Prerequisites Required Knowledge •

None

Course Goals This course will prepare you to: •

use basic and some advanced SQL techniques for querying and manipulating data in an SAP HANA database.

Course Objectives After completing this course, you will be able to: •

Explain basic concepts in the database world and the Relational Database Model.

•

Refresh and deepen SQL knowledge, especially tor developing on HANA database systems.

2014

© 2014 SAP AG

or

an SAP affiliate company. All rights reserved.

ix�



Course Overview

HA150

© 2014 SAP AG or an SAP affiliate company. x


2014



•

n1 Motivation and Basic Concepts Unit Overview Unit Objectives After completing this unit, you will be able to: •

List some database models.

•

Understand the motivation for and foundation of the relational model.

•

Understand why learning SQL is important when dealing with SAP HANA.

•

Understand ho\v the relation model and SQL are related.

•

List the three sub-languages of SQL.

•

Understand the sample database used throughout the course.

Unit Contents Lesson: Motivation and Basic Concepts

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

2

1�



Unit 1: Motivation and Basic Concepts

Lesson:

HA150

Motivation and Basic Concepts Lesson Overview This lessons explains \vhy learning SAP HANA SQL may be important, the relational database 1nodel it originates from, and the sample data used throughout the course.

Lesson Objectives After completing this lesson, you \Viii be able to: •


•


•

Understand why learning SQL is important when dealing with SAP HANA.

•

Understand how the relation model and SQL are related.

•


•


Business Example Basic Concepts What are the terms: •

Database?

•

Database System?

•

Database Management System?

c- � :..

r

(DBMS) manages the database

Every access to the database (create, read, insert, update, delete) goes exclusively through the DBMS

'l>

The DBMS exercises complete control over the database

•Database (CJ....)= structured collection of records "

"

•l')
1:

DBS = DB

+

DBMS


2014



Lesson: Motivation and Basic Concepts

HA150

User I Application ...

i

"

-----....

• • • • •

DBMS

: ' •

I • • • •

I

"

.,,. �

:::::==-----·DBS

�

!

�

•

I I

DB 'Figure 2: DBS

=

DB

� +

____........

........'

.

DBMS

•,,What Database do you use?u ® •.Which Database Management System do you use?"©

•.We are using HANA as Database.u ® ·.We are using SAP HANA as Database Management System.u ©

•In everyday life, these terms are usually used incorrectly . ..

Figure 3: DBS

=

DB

+

DBMS

Which Database Models are available'? •

Hierarchical Database Model

•

Net\.vork Database Model

•

Relational Database Model

•

Object-relational Database Model

•

Object-oriented Database Model

•

XML-based Database Model

•

Relational Database Model

2014

© 2014 SAP AG or

an

SAP affiliate company. All rights reserved.

3�




HA150

Invented in the late 1960s by Edgar F. Codd

(1923-2003)

+IBM Almaden Research Lab in San Jose (California)

First prototype in the mid-1970s •

"System R", IBM San Jose

DBMS products (selection)

Relational •

Oracle (since 1979)

•

IBM

•

SOL Server (since 1989)

•

SAP HANA (since

082 (since 1983) 2011)

Figure 4: Relational Database Model

•

Initial Development Goals (1960s): •

Simple but mathematically profound database model • Easy to understand but still mathematically precise

+

Simple but mathematically-based database language • Easy to learn, but semantics still described with mathematical precision • Provable equivalence of two queries

+

Integrity monitoring largely by the DBMS

+

Storage & retrieval of data is the responsi bili t y of the DBMS (and not of the user) •

Descriptive language rather than navigation (optimizer selects optimal execution strategy)

•

Clean separation between conceptual and internal schema

Figure 5: Relational Database Model

•

Objective: Changes at a lower level should not affect a higher level (if possible)

ApplicationlUser

ApplicationlUser

.,.

l

t �

External Schema 1

External Schema n

oncep ua

External Level

Conceptual Level

Internal Schema

Internal Level

Figure 6: 3 Level Schema Architecture

�

4


2014




HA150

Internal Schema: How and \vhere stored? •

Describes (DBMS specific) the internal, physical representation of data: Ho'v and where exactly the data is stored, internal record fonnat, access paths, etc.

Conceptual Schema: What is stored? •

Overall presentation ofthe data model at the logical, (if possible) DBMS and application independent level.

•

For example, in relational representation, or even higher level of abstraction (e.g. E/R model).

External Schema: How is data presented to the user? •

(Partial) views ofthe database as required by applications or users (e.g. HR requires different views I details other than top management or the individual employee).

•

Tn the case ofa relational database system implemented via views.

• •

The address book should not display salary data

External Level (VIEW}

• An Employee has a D-Number, name, and salary and is assigned to a department •

•

Conceptual Level (TABLE)

The board mostly accesses employee data according to descending order of salary (which has to be very fast) •

Internal Level (INDEX)

Figure 7: 3 Level Schema Architecture

Why is the Relational Database Model actually called Relational Database Model? Figure 8: Basic Concepts

2014


5�



Unit

1:

HA150

Motivation and Basic Concepts

A relation (in the sense of mathematics) is the subset of the Cartesian product of sets

c

AxBxC

Figure 9: Relations

.

I

!

. .•- . . . .. .

N = {l, 2, 3, 4, 5}

..

;

..

j

..

..

.

.

�

:

. ·• . . ... i ; : t •

.

N >< N = { (1,1), (l,2) I

(1,3) I

(1,4) I

(1,5) I

3

.

(2,1),

(2 I 2) I

(2 I 3) I

(2 I 4) I

(2,5) I

2

..

(3,1) I

(3,2) I

(3,3) I

(3,4) I

(3,5) I

(4,1) I

(4 I 2) I

(4,3) I

(4,4) I

(4,5) I

(5,1) I

(512) I

(5,3) I

(5,4) I

(5, 5)

. . . . . .. . . . . :

..

.

.

.

.

.

.

!

1

}

.

.

. ... .. ... .. .... . .... .... �. . .. .

.

1

.

2

.

.

. _

.

:

:

3

4

.

5

Figure 10: Comparative Relations(=,-,<,=,>,=) s c N >< N s

s

l

{ (l, l) I (2 11) I

(1,2), (2,2) I

(1, 3) I (2 I 3) I

(3,1) I

(3,2),

(4,1), (5,1) I

( 4,2) I (5,2) I

c

{ (1,l)

(1,4) I

(l, 5)

(314) I

.

.

..

...

. . . .. . . . . ...

I

(3,3) I

(2,4) I (3,4) I

(2,5) I (315) I

(4,3) I (5,3) I

(4,4) I (5,4) I

(4,5) I (5,5) }

4

3 2 1

(2 I 2) I

.

. :

.

-

I

...... . . .·-·•-

.

(1,2) I

(l, 3) I

(1,4) I

(l, 5) I

(2, 3) I (3,5),

(2,4) I (4,4) I

(2,5) I (4,5),

(3,3) I (5,5)}

..

.

:

:

.,

:

..

.

;

! .. . . . : j : ; : .. . .. . ... .. ... .

...

.

•

.

.

.

. .

..

.._

... .. .. .. . . .

.

:

1

..

...

2

3

.

.

�

:

4

5

(2, 3) es 2s 3

Figure

11: Comparative Relations(=,-,<,=,>,=)


2014




HA150

Persllumber

•

NaJDQ

•

IP03&107, P040824, P052867, ·-l lB.en, Paul, Kaja, ...J

HirlngYear

=

1�001, �ooa, 201C, -1

P•ral11JP�•r

Name

H-ri�g"fear

{ (P036407,Ben, 2001),

(P036407,Ben, 2008),

(P036407,Ben, 2010),

(P036407,Paul,2001J,

CP036407,Paul,2008),

(P036407,Pau1,2010),

(P035607,Raja,2001),

(P036407,Raja,2008),

(P036407,Raja,2010),

(P040824,Ben, 2001),

(P040824,Bcn, 2008) ,

(P040824,Ben, 2010),

(P040824,Pau1,2001),

(P040824,Paul,2008),

(P040824,Paul,2010),

(P040824,Raja,2001),

(P040824,Raja,2008),

(P040824,Raja,2010),

(P052867,Ben, 2001),

(P052867,Ben, 2008),

(P052867,Ben, 2010),

(P052867,Pau1,2001),

(P052867,Paul,2008),

(P052867,Paul,2010),

(P052867,Raja,2001),

(P052867,Raja,2008),

(P052867,R.aja,2010),

...}

Figure 12: Relations

£11\ploy.,., (;; PersNurober • Name • Hhin•1Year

Employee � ( (P036407,Ben, 2001),

(P036407,Bcn, 2008),

(P036407,Pau1,2001),

(P036407,Paul,2008),

(P036407,Pau1,2010),

(P035607,Raja,2001),

(P036407,Raja,2008),

(P036407,Raja,2010),

(P036407,Ben, 2010),

{P040824,Ben, 2001),

(P040824,Ben, 2008),

(P040824,Ben, 2010),

(P040824,Pau1,2001),

(P040824,Paul,2008),

(P040824,Pau1,2010),

{P040824,R.aja,2001),

(P040824,Raja,2008),

(P040824,Raja,2010),

(P052867,Ben, 2001),

(P052867,Ben, 2008),

(P052867,Ben, 2010) ,

(P052867,Paul,2001),

(P052867,Paul,2008),

(P052867,Pau1,2010),

(P052867,Raja,2001),

(P052867,Raja,2008),

(P052867 ,Raja,2010) ,

... )

Employe""' ((P036407,Pau1,2001), (P040824,Ben,2008), (P052867,Raja,2010))

Figure 13: Relations

A relation can be represented as a table •

Jttoploye.,.. ( (P036407,Paul,2001),

Employee

(P040824 ,een,2008), (P0528S7,Raja,2010)}

Pers Number

Name

HiringYear

P036407

Paul

2001

P040824

Ben

2008

P052867

Raja

2010

Figure 14: Relations & Tables

2014


7�




HA150

Which languages are available for the Relational Database Model? Figure 15: Basic Concepts Relational Languages •

Relational Algebra Fonnal basis for DBMS internal query optimization 6 basic operations (selection, projection, cross join, union, difference,

rename) •

Relational Calculus Tuple variables and quantifiers

•

SQL Standardized and used in practice

Why SQL is important?

Why it is worth working with SQL

Figure 16: Why SQL?

�


8


2014




HA150

In almost every business application I scenaro the data is managed using database systems. i

,

The most sg n ficant are database systems based on the relati onal data model and using SQL (Structured Query Language) as a database i

i

language.

,.-

SQL is a widely-established, powerful, standardized database language many application

programmers have experience in. There is (so far) no other database language that has all the advantages mentioned.

SAP HANA is a relational data base management system and SAP HANA supports SQL Figure 17: Why SQL?

-

Reasons #1 and #2

Different application architectures and development models are possible with SAP HANA Data Marts with SAP HANA

Standalone HANA Apps

SAP HANA Chen�

--

Traditional DBM

Figure 18: Why SQL?

2014


9�




HA150

Different application architectures and development models are possible with SAP HANA

HANA as Accelerator (secondary DB)

Aggregatlon Levels

HANA as primary Database (for AS ABAP)

E.g. CRM on HANA - NW 7.40

Tr ad itional DBMS

Figure 19: Why SQL?

HANA supports an extended version of SQL

No matter which HANA-based architecture or development model you work with, it is likely that using HANA SQL will be beneficial.

Figure 20: Why SQL?

-

Reason #3


10


2014



HA150


SQL Features Figure 21: SQL •

SQL is standardized No uncontrolled grov.rth with respect to syntax and semantics

•

SQL is descriptive (rather than procedural) The "what" and not "how" is described

•

SQL execution is optimized SQL statement is first parsed and optimized, and then executed Optimizer determines optimal execution plan (at least in theory)

•

SQL is multi-set oriented (and not single record-based) Using a single SQL statement multiple table rows can be read, modified or deleted in one go

SQL Language Elements SQL language elements can be divided into three categories: •

DML

=

Data Manipulation Language

SELECT, INSERT, UPDATE, DELETE •

DDL

=

Data Definition Language

CREATE, ALTER, DROP, RENAME •

DCL

=

Data Control Language

GRANT, REVOKE

SQL is standardized! Figure 22: SQL

2014


11 �




HA150

l SEQUEL (Structured English Query Language) as a language for "System R"

1970s

•"System R" was the first relational prototype developed by IBM in San Jose

End of 1970

Renaming of SEQUEL into SQL (Structured Query Language)

1982

American National Standards Institute (ANSI) begins with standardization of SQL

1986

First version of the SQL standard is adopted (as ANSI) SQL-86 ("SQLO")

1987 1989 1992 1.999 2003

2008

j

1 nternat iona l

SQL·89

J

Organization for standardization (ISO) does SQL standard (ISO standard)

("SQL1") -

SQL-92

("SQL2'1,

3 attenuation levels: Entry Level, Intermediate Level, Full Level

SQL1999 ("SQL3")

I SQL:2003 ("SQL4"), "SQUXML:2006" 2006 official standard part of SQL:2003 SQL2008

("SQLS"), officially "ISO/IEC 90752008" and "DIN 9075" (> 3,000 pages)

-

Figure 23: SQL Standard: History

SQL 'I- relational Figure 24: SQL SQL is the most important database language for relational database model. But it deviates in important points from the "purely relational" model: •

No (primary) key required Duplicates (identical rows) allo·wed (multi-sets instead of sets)

•

NULL values are allowed 3-valued logic required ("A= A" need not be TRUE)

•

The language is not closed Order of the result ro\.vs may be relevant (ORDER BY) Anonymous result columns allowed Duplicate result colum11 names allo\.ved

What can be found in the database?

Figure 25: Database Objects

12


2014




HA150

The table is the primary database object - but not the only one. Apart from tables a database usually contains: •

Views ro simplify and limit data access

•

Lndexes to speed up (certain) read accesses

•

Constraints to ensure data consistency

•

Stored procedures for more complex tasks

•

Triggers to selectively respond to particular events

What is a table? Figure 26: Basic Concepts A table consists of rows and columns •

A table can therefore be represented two-dimensionally

A (database) table represents a relation •

A table represents a (unordered) multi-set of points In n-dlmenslonal space

•

Each of the n dimensions is equivalent to a table column

I Emolovee

PersNumber

Name

HirinaYear

P036407

Paul

2001

P040824

B en

2008

P052867

Raia

2010

n Employee = {(P036407,Paul,2001),

(P040824,Ben,2008),

(P052867,Raja,2010)}

Figure 27: Tables

2014


13 �




HA150

Name Pa� HiringYear (P040824,Ben,2008)

Raja-

•

BeA-

P030407

Figure

28:

P052867

P040824

Table rows as points in space Projection list

Name

SELECT

-

f

� ..��;1

; � � �� ·,,.·····�;

.....

FROM Employee

Pa�

;,

WHERE PersNumber

=

. .. � ..

'P040824';

HlringYear (P030824,Ben,2008)

Raja-

•

BeA-

• P036407

Figure

29:

(P040824,Ben)

P052867

P040824

Projection on the plane

=:? �Name� \:::. ........... ......

Projectlo� ! �� , , , , .,

Name

SELECT

FROM

Pa�

..

Employee

WHERE PersNumber

=

'P040824';

HiringYear Raja-

(P040824, Ben, 2008)

•

Be

P036407

Figure

30:

P040824

P052867

Projection on an axis


14


2014




HA150

What are the components of a (database) table? Figure 31: Basic Concepts Primary Key

Tahle 111.me

� I

Employee

!

Column Name

1

PersNumber

Name

HiringYear

P036407

P aul

2001

P040824

Ben

2008

P052867

Raja

2010 tt

Table Row

l

(Table Column) '

"" u

,,._._,..,) ,.olunin

Figure 32: Components Of Database Tables

What is a key? Figure 33: Basic Concepts Key= is a set of columns which serves to uniquely identify any row in the table. The ability to uniquely identify rows must apply in principle (and not only for the rows existing at a certain point in time).

Employe PersNumber Name e

Figure

2014

HiringYear

P036407

Paul

2001

P040824

Ben

2008

P052867

Raja

2010

34: Key


15 �




HA150

A key can consist of multiple columns Employee

Name

Countrl l

Citll

BuildingNr

Block

Floor

Room

SeatNr

MrA

DE

WD

03

A

5

29

1

03

A

5

29

2

03

A

5

29

3

. .

...

...

...

.. .

F MsB

DE

WD F

MsC

DE

WD F

...

..

...

.

.

,. In this example the key consists of 7 columns ,.- 1 Key (not 7 keys)! Figure 35: Multi-column Keys

There may be more than one key

e,asis �License Employee 'Numbe; a e riID �ssport Number ID r�(!;Plate ID �IFT IBA� '

�

P000815 P004711

N

m

�

ax

�

."

...

...

..

.

".

.

..

HD-MM 81 5 HD-ML

II.Ir

�

...

...

...

..

.

.,.,,.,

...

...

".

".

".

".

4711 P012345

.

"

."

...

...

..

.

...

.. .

HD-012345

..

...

.

.

..

...

..

.

...

...

, Here are 7 keys (provided there are no joint accounts) , Just 1 key is selected as Primary Key Figure 36: Multiple Keys

What is a Foreign-Key?

Figure 37: Basic Concepts


16


2014




HA150

Foreign-Key= •

set of columns, which is a (primary) key in an(other) table

The foreign-key can refer t o its own table It is not necessary to share the same name(s) The foreign-key can contain only those values that occur as a (primary) key value in another table (in addition and if applicable, NULL values are allowed) The foreign-key is usually not a key!

I Emolove e

Foreign-key Relationship

D Numbe

Name

DeoartmentN

0010000

MrA

Afl1

�

I Deoartme nt

DNJ

Func ti on

HANA-

A01

0010001

MsB

A01

A02

................ Est

0010002

MrC

A02

A03

HANA-Trainina

0010003

Ms D

A02

A04

ABAP-

0010004

MrE

A02

0010005

MsF

A03

Development

Figure 38: Foreign-Keys A Foreign-Key can consist of multiple columns

IPublication

"Uni

PallerlD

Topic

P001

ln-Memorv

HPI

P002

Column

HPI

---

�

Student/D

12345

"-.

e t

Stud n �Uni

HPI

12345

Store

P003

Row Store

HPI

77777

P004

SQL&

FSU

12345

FSU

12345

FSU

77777

�ludi:nt1D

12345

'Name

FirstName

A

B

D F

HPI

77777

c

FSU

12345

FSU

77777

E G

H

XML P005

XML& SQL

P006

DB Recovery

This example shows 1 Foreign-Key built on 2 columns

F i gu re 39: Foreign-Keys There can be

I Vendor

r

multiple Foreign-Keys

\ � � I

VlD

Name

L1

Heidelberg Paper

L2

Wiesloch Paoer

L3

Walldorf Paper

...

...

Delivery

'iJQ flJL Ym Quantity Product flir l1

A1 2010

320

L1

A1

2011

570

L1

A1

2012

925

l1

A2 2012

102

L2

A1 2011

577

L2

A2 2011

100

l3

A1

2012

.. .

...

...

999

A1

Description

Sticky Notes

A2

Printing

A3

Envelopes

. ..

Paper . ..

...

Figure 40: Foreign-Keys

2014


17 �




HA150

The foreign-key can refer to its own table

I Emolove e

r�

ONumbe

Name DNumberManaaer 0016000 MrA 0010007 0010001

MsB

0010007

0010002

MrC

0010003

MsO

0010004

0010004 0010005

MrE MsF

0010002 0010002

0010006

MrG

0010005

0010007

MsH

0010005

Figure 41: Foreign-Keys

Fictional vehicle registration office as an example scenario Figure 42: Example Scenario Example: Registration Office •

The database of a fictional registration office will serve as the basis for further explanations

•

The tables in this database have been specifically tailored to the SQL course and are not an example of good database desi1:,'ll.

•

The officials ·working in the fictional registration office have a manager.

•

Each vehicle is registered for exactly one O\Vner (or is unregistered).

•

There is a list of vehicles that have been reported stolen.

•

Owners, '"ho have at least three vehicles registered, are assigned to one or multiple contacts.

18


2014




HA150

Official.(PNr, Name,

overtime,

V\

Contact(PersNumber,

...---�

� .... =

QJimerID) Birthday,

,

Pl teNmnber, Brand,

Stolen(PlateNumber,

Man.ager)

;:

Owner(Own•rro. Name,

Car(CarID,

Salary,

City)

Color,

HP,

OWner)

reported_at)

Figure 43: Example: Registration Office

I

Official

PNr

Name

P01

MrA

P02

Salary

Manager

10

AD9

P04

MrB

10

A10

P04

P03

MsC

20

AD9

P04

P04

MsD

NUU.

A12

P09

P05

MrE

10

A08

P08

P06

MrF

18

A09

P08

P07

MsG

22

A11

P08

P08

MsH

NUU.

A13

P09

P09

Mrl

NULL

A14

NULL

Overtime

Figure 44: Officials

2014


19 �




I

Owner

HA150

OwnerlD

Name

Birthday

City

H01

MsT

20.06.1934

Wiesloch

H02

MsU

11.05 .1966

Hockenheim

H03

SAP AG

NULL

Walldorf

H04

HDMAG

NULL

Heidelberg

H05

MrV

21.04.1952

Leimen

HOS

MsW

01.06.1957

Wiesloch

H07

IKEA

NULL

Walldorf

H08

MrX

30.08.1986

Walldorf

H09

MsY

10.02.1986

Sinsheim

H10

MrZ

03.02.1986

Ladenburg

Figure 45: Owner

Contact

PersNumber

OwnerlD

P01

H03

P01

H04

P01

H0 7

P04

H03

P04

H04

P08

H04

P08

H07

P09

H03

Figure 46: Contact

�


20


2014



Lesson: Motivation a n d Basic Concepts

HA150

I car

HP

OWner

PtateHumber

Brand

F01

HO-V 106

Fia t

r9d

75

H06

F02

Hl). VW 4711

WV

black

120

F03

HO-JA 19n

BMW

blle

�

Col o r

18'

H03 H03

136

H07

H03

F04

HO-AL 1002

Mercedes

wh ite

F05

HO-MM3206

Mercedes

black

170

F6 0

HO.WV 1999

A udi

•ellow

260

H05

F07

HD-ML3206

A udi

blue

118

H03

F8 0

H[).t K 1 002

WV

black

160

H07

F09

HO.UP 13

Sk oda

l ed

105

H02

F10

HO-MT 607

BMW

black

140

H04

BMW

areen

18 4

H02

red

105

H04

ad r

1 36

H07 H03

F1 1

HD-MM 208

F12

HD-XY 4711

F13

HO-IK 1 001

Renault

F14

HD - MM19n

Mer cedes

white

1 70

F15

HD-MB 3000

S koda

black

136

H03

F16

NULL

Ooet

areen

120

NULL H09

oda Sk

F17

HD-Y333 HD-MO 2 006

Au d i Renault

""'"°' red

18 4

F18

90

H03

F19

HD-VW2012

WV

black

125

H01

F20

NULL

A udi

areen

184

NULL

Figure 47: Cars

Stolen

PlateNumber

reported_at

HD-VW 1999

20.06.2012

HD-V106

01.06.2012

HD-Y333

21.05.2012

Figure 48: Stolen Cars

•

A new (fictional) European Union directive requires that the information about which vehicle is registered to which owner, has to be stored in a central transnational database.

•

The vehicle identification number (CarlD) is unique across the EU, but not the Owner ID.

Name, Birthday, City) Car_EU(CarID, PlateNumber, Figure 49: Registration Office & EU

2014


21�




I Ownef'_EU

HA150

CQUO!r::i

owa�!D

Name

Birthday

City

D

H01

MsT

20.06.1934

Wtesl oc h

D

H02

MsU

11.05.1966

Hockenheim

D

H03

SAP AG

NULL

Walldorf

D

H04

HDMAG

NULL

Heidelberg

D

HOS

MrV

21.04.1952

Leimen

D

H06

Ms W

01.06.1957

Wtesl oc h

D

H07

IKEA

NULL

Walldorf

D

H08

Mr X

30.08.1986

Walldorf

D

H09

MsY

10.02.1986

Sinshelm

D

H10

MrZ

03.02.1986

Ladenburg

A

H01

MsO

21.05.1977

Wien

A

H02

Mr P

02.08.1977

Salzburg

E

H01

Sel\or Q

18.02.1925

Madrid

E

H02

Scliora R

27.02.1927

Ba r c e lona

Figure 50: Owner (EU-wide) I

Car EU

Count�

HP

Own• r H06

Car1D

P laleNu m M r

Bran d

CO ior

FG I

HO.V 106

Rat

�

FG2

HO-WI 4711

vw

bl•ck

120

0

H03

FOO

HO-JA 197 2

BMW

bl ue

18'1

0

HOO

wtll e bl ac k �"'

136

0

H 07

1 70

0

03 H

0

H05

75

0

F�

HO.Ill. 1()()2

F05

H0.111 1\A 3206

F06

H().\MI 1999

Mercede!f M«eodos A
F07

MO·lI• L 3:1C6

Aud

blue

116

0

H03

Fila

HD-IK 1002

vw

bla ck

160

H0 7

F09

HO. U P 13

SI<-

105

0

H02

F 10

H0. 1 .0 507

BMW

r
0

1
0

H�

2SO

Fii

H O.MM 208

BMW

D

H02

MO.X V •711

"'""

18'1

F1 2

�

red

105

0

H� H 07

F13

HO.IK 1001

R•...,Ul

red

136

0

Fl•

H 0.1\AllA1977

Aerce- des t

w ril t

170

D

H03

F1 5

HO.MB '.!000

bl ack

IJ6

0

H03

F 16

NUl.L

Sk oda Ooel

F17

HO.Y 333

Aud

F 18

� . 1 02006

F19

-·

120

NULL

NUl.L

18'1

0

09 H

R eoeut

- �· red

90

0

H03

Hl).\MI 2012

vw

black

125

H01

F2tl

NUl.L

-

0 N ULL

NUtL

F21

2 Ml W.:30

, A ef'C edes

..... ...

18'1

A

H01

S.215 MM

18'1

A

UOOEMM

•u -

,;1��

170

F22 F23

blue

116

E

� H01

F2-4

3206 MlM

WJ

b l -a c l<

170

E

M0 2

black

Figure 51: Cars (EU-wide)


22


2014




HA150

Lesson Summary You should now be able to:

2014

•


•


•

Understand "vhy learning SQL is important when dealing with SAP HANA.

•

Understand ho\v the relation model and SQL are related.

•


•

Understand the san1ple database used throughout the course.


23 �



Unit Summary

HA150

Unit Summary You should now be able to:

�

•

List so1ne database models.

•

Understand the 111otivation for and foundation of the relational model.

•

Understand 'vhy learning SQL is important when dealing with SAP HANA.

•

Understand how the relation model and SQL are related.

•


•



24


2014







•

n1 Reading Data From a Table Or View Unit Overview Unit Objectives After completing this unit, you will be able to: •

Write simple database queries using SQL's SELECT statement.

•

Project columns in and out of queries using the SELECT clause.

•

Avoid duplicates in SELECT statement result sets.

•

Include columns based on conditions.

•

Use built-in functions in column lists and WHERE clauses.

•

Limit results sets to the first N rows.

•

Ensure a specific order in SELECT statement result sets.

•

Restrict the result set using the WHERE clause.

Unit Contents Lesson: Reading Data from a Table or View Exercise 1 : Exercise 1

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. .

.

.

© 2014 SAP AG or an SAP affili ate company. All rights reserved.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

26 59

25 �



Unit 2: Reading Data From a Table Or View

Lesson:

HA150

Reading Data from a Table or View

Lesson Overview The lesson covers the foundation of how to retrieve data fro1n a database.

Lesson Objectives After completing thjs lesson, you wi II be able to: •


•


•


•


•

Use built-in functions in column lists and \VHERE clauses.

•


•


•


Business Example

Which SQL statement can be used for reading data from a table or view? Figure 52: Reading Database Access SELECT

...

FROM

...

WHERE

...

�It is used to read from a table or view

•

•

The SELECT statement is the central construct for read access to data (database) with SQL The SELECT statement can include the following (optional) clauses: WHERE,

GROUP BY,

HAVING,

ORDER BY

Figure 53: Reading Database Access

�

26


2014



Less on : Read ing Data from a Table or View

HA150

The SELECT statement

Figure 54: Reading Database Access

SELECT Column,

Column,

COUNT(*)

FROM Table WHERE Condition GROUP BY Column,

Column

HAVING Group_Condition ORDER BY Column ASC,

Column DESC;

Figure 55: SELECT Statement

SELECT clause

Figure 56: SELECT

You can specify a single column In the projection llst:

SELECT Name FROM Official.;

NAME Mr A Mr B Ms C Ms D Mr E Mr F Ms G Ms H Mr I

Figure 57: SELECT Clause

2014


27 �




HA150

You can specify multiple columns in the projection list:

SELECT PNr,

Name,

Salary

PNR

FROM Official;

NAME

Salary ------

POl

Mr A

A09

P02

Mr B

AlO

P03

Ms C

A09

P04

Ms D

A.12

POS

Mr E

A08

P06

Mr F

A09

P07

Ms G

All

P08

Ms H

Al3

P09

Mr I

Al4

Figure 58: SELECT Clause The sequence of the columns In the projection list Is relevant:

Name

SELECT PNr,

SELECT Name,

FROM Official;

PNr

FROM Official;

PNR

NAME

NAME

PNR

POl

Mr A

Mr A

POl

P02

Mr B

Mr B

P02

P03

Ms c

Ms c

P03

P04

Ms 0

Ms D

P04

Figure 59: SELECT Clause The same column can appear several times in the projection list: We do not recommend using this option SELECT

PNr,

PNr,

PNr

FROM Official;

PNR

PNR

PNR

POl

POl

POl

P02

P02

P02

P03

P03

P03

1?04

P04

P04

1?05

P05

P05

1?06

P06

P06

1?07

P07

1?07

P08

P08

P08

1?09

P09

P09


�


28


2014



Lesson: Reading Data from a Table or View

HA150

An asterisk(*) in the projection list represents "all columns": SELECT * FROM Official; PNR

NAME

OVERTIME

SALARY

MANAGER

--------

------

-------

POl

Mr A

lO

A09

P04

P02

Mr B

lO

AlO

P04

P03

Ma c

20

A09

P04

?

Al2

P09

P04

Ms 0

Figure 61: SELECT Clause Columns can be specified in the projection list In addition to the asterisk (*): We do not recommend using this option SELECT PNr, FROM

PNR

*

Name

I

Official;

PNR

NAME

POl

POl

P02

P02

Mr A Mr B

P03 P04

P03 P04

Ms c Ms D

OVERTIME

SALARY

MANAGER

NAME

--------

------

-------

10

A09

10

AlO

P04 P04

Mr A Mr B

20 ?

A09 A12

P04 P09

Ms c Ms D

Figure 62: SELECT Clause The asterisk(*) can be used multiple times In the projection list: We do not recommend using this option SELECT

*

*

I

*

FROM Official; ....

-

OYlll.TDC

.... ,..

""""""'

...

.....

.

........

....... .........

""'

UH&

OVDTDll:

...

SALA.IV

--

•O•

.

••1

....

10

...

...

••1 M> A

10

•••

• ••

ro1

10

...

H2

...

10

...

...

H2 H:l •

10

IUO

•••

••2 .. I 10

...

ro•

...

... c

20

-

...

...

•• c

20

...

•••

•OJ ... c 20

...

•O•

"'

....

•

.,,

...

•••

"' .

'

A12

...

•••

....

...

...

10

-

...

...

...

10

...

...

...

.. . 10

...

....

11

-

...

...

"' r lt

...

...

...

"' r

..,

....

"

lW

...

... . .,

22

IUI

...

...

...

....

'

AU

.. .

. . .

.. .

'

.,.

...

...

... '

'

...

•

...

H>t

•

...

•

IU2

•••

...

rot

11

...

•••

....

n

IUl

•••

.. .

... "

•

...

•o•

...

... '

•

...

'

•


2014


29 �




HA150

You can generate additional "artificial" result columns: SELECT

'Today', Name,

'has been assigned to salary

group', Salary

FROM Official;

'Today'

NAME

'has been assigned to salary group•

SALARY

Today

has been assigned to salary group

Today

Mr A Mr B


A09 A10

Today

Ms c


Today

Ms D


Today

Mr

has been assigned to

E

A09 A12 AOB

salary group

Figure 64: SELECT Clause The additional artificial result column can have a numeric type: SELECT

'The working week of',

Name,

•amounts to',

40,

'hours.' FROM Official;

'The working week of'

NAME

---------------------

'amounts to'

40

'hours'. --------

------------

amounts to

40

hours.

The working week of

Mr A Mr B

amounts to

hours.

The working week of

Ms c

amounts to

The working week of

Ms D

amounts to

40 40 40

The working week of

amounts to

40

hou rs

The working week of

Mr E Mr F

amounts to

40

hours.

The working week of

Ms G

amounts to

hours.

The working week of

Ms H

amounts to

40 40

hours.

The working week of

Mr I

amounts to

40

hours.

The working week of

hours. hours. .

Figure 65: SELECT Clause The projection llst may only contain artificial result columns:

SELECT

'Good Morning!'

FROM Official;

'Good Morning! ' Good Morning! Good Morning! Good Morning! Good Morning! Good Morning! Good Morning! Good Morning! Good Morning! Good Morning!



30


2014




HA150

If the projection llst only contains artificial result columns, you can use the special table ''Dummy" as reference: SELECT

'Good Morning!'

'Good Morning!'

FROM Dummy;

Good Morning!

The "DUMMY" table contains one column and one row: Dm+1Y

SELECT

*

x

FROM Dummy;


SELECT a, 'b', FROM 11411;

11c11

I

1I

t2I

I

II 3 II

A

'b' b b b

Existing column named

a

"A" ("A" as capital letter)

'b'

Artificial result column with string "b" as value In each r<:IN

..c"

Exisbng column named ·c· ("c" a s lower case letter) Artificial result column with 1 as numeric value

1

c

in

-·

-

1

'2'

1

2

1

2

1

3

2

-

each r<:IN

Artificial result column with stnng "2" as value in each r<:IN

•

•

II

"

"

"

Existing column named •3• Existing table named •4•

Figure 68: SELECT Clause The projection list can have computed columns.

If a NULL value is used in an arithmetic ope ra tio n the result is also a NULL ,

value:

SELECT Name,

overtime *

FROM Official;

60

NAME

OVERTIME*60

Mr A

600

Mr B

600

Ms C

1200

Ms D

?

Mr E

600

Mr F

1080

Ms G

1320

Ms H

?

Mr I

?


2014


31 �




HA150

O • NULL results in NULL (and not 0): SELECT Name,

Overtime *

overtime,

0

FROM Official; NAME

OVERTIME

OVERTIME*O

--------

----------

Mr A

10

0

Mr B Ms C

10 20

0 0

Ms D

?

?

Mr E Ms G

10 18 22

0 0 0

Ms H

?

?

Mr I

?

?

Mr F


You can expllcltly name computed result columns:

SELECT Name,

overtime

*

60 AS OVerrninutes

FROM Official;

NAME

OVERMINUTES ----------

Ms c

600 600 1200

Ms D

?

Mr A Mr B

Figure 71: SELECT Clause Quotation marks are required, If the result column name should Include lowercase letters:

SELECT Name,

overtime *

60 AS

110verrninutes11

FROM Official; NAME

Overminutes -----------

Ms c

600 600 1200

Ms D

?

Mr A Mr B


�


32


2014




HA150

Quotation marks are also required, If the result column name should contain blanks: SELECT Name,

Overtime

*

60 AS

"overtime

minutes" FROM Official; NAME

overtima minutes

Mr A

600

Mr B

600

Ms C

1200

Ms D

?

Figure 73: SELECT Clause You can also rename non-calculated result columns: SELECT PNr AS PersNumber, Salary AS "Salary Group" FROM Official; PERSNUMBER

Salary Group

POl

A09

P02

AlO

P03

A09

P04

Al2

Figure 74: SELECT Clause In the (re)naming of result columns the keyword "AS" can be omitted: SELECT PNr PersNumber,

Salary

"Salary Group"

FROM Official;

PERSNUMBER

Salary Group

POl

A09

P02

AlO

P03

A09

P04

Al2


2014


33 �




HA150

You can use existing column names for (re)naming of result columns: SELECT PNr Salary,

SALARY

Salary PNr

PNR

------

FROM Official;

POl P02 P03 P04 POS P06 P07 POB P09

A09 AlO A09 Al2 AOB A09 All Al3 Al4

Figure 76: SELECT Clause Multiple result columns can have the same name: We do not recommend using this option SELECT PNr AS xyz,

Name AS xyz,

Salary AS xyz

FROM Official; XYZ

XYZ

XYZ

POl

Mr A

A09

P02

Mr B

AlO

P03

Ms C

A09

P04

Ms D

A12


•

You can use functions in the projection list.

•

The corresponding result columns can be named explicitly.

,.

Which owner Is born In which year?

SELECT Name,

YEAR(Birthday) AS

"Year of Birth"

FROM Owner;

NAME ------

Year of Birth -------------

Ms T

1934

Ms U

1966

SAP AG

?

HOM AG

?

Mr v

1952

Ms W

1957

IKEA

?

Mr x

1986

Ms Y

1986

Mr z

1986


�


34


2014




HA150

�

When were vehicle owners first allowed to drv i e a car In Germany?

SELECT Name,

ADD_YEARS(Birthday, AS

18)

"18th Birthday"

FROM Owner;

18th Birthday Ms T

1952-06-20

Ms u

1984-05-11

SAP AG

?

HDM AG

?

Mr v

1970-04-21

Ms w

1975-06-01

IKEA

?

Mr x

2004-08-30

Ms y

2004-02-10

Mr z

2004-02-03

Figure 79: SELECT Clause Function calls can be nested >On which day is the owner's 181h birthday?

SELECT Name,

NAME

DAYNAME

------

YEARS

( ADD

(Birthday, ROUND(ABS(-18.2)) ) )

AS Weekday

FROM Owner;

WEEKDAY -

-

-

-

-

--

Ms T

FRIDAY

Ms U

FRIDAY

SAP AG

?

HOM AG

?

Mr v

TUESDAY

Ms W

SUNDAY

IKEA

?

Mr x

M:>NDAY

Ms Y

TUESDAY

Mr Z

TUESDAY


Which functions are provided by SAP HANA?

Figure 81: Functions

2014


35 �




HA150

SAP HANA provides following functions (selection):

Explanation

Function nAR (O at e)

year

ADD_YEIARS(Date, n)

n years l ater

DAYNAME(Date)

weekday (English)

CURRENT DATE

current date

ABS (Number)

absolute value

ROUND(Number)

rounding

SQRT(Number)

square root

OPPER(String)

convert to upper case

SOBSTR(String, Start, Length)

cut out of a string (substring)

LE NGTH (String)

length of a string

Check SAP HANA SQL Reference for more. http;//help.sap.com/hana platform#section6

Figure 82: Functions

You can qualify the column name b y adding the table name: NAME

SELECT Official.Name FROM Official;

Mr A Mr B Ms C Ms D Mr E Mr F Ms G Ms H Mr I


The qualification with the table name is also allowed, if the output table has to contain all columns:

SELECT Official.* FROM Official;

PNR

NAME

OVERTIME

SALARY

MANAGER

----

--------

------

-------

POl

Mr A

10

A09

P04

P02

Mr B

10

AlO

P04

P03

Ms c

20

A09

P04

P04

Ms D

?

A12

P09



36


2014




HA150

The projection list can include a mix of qualified and unqualified column names: NAME PNR SELECT Name,

Official.PNr

FROM Official;

Mr A

POl

Mr B

P02

M.s c

P03

Ms 0

P04

Mr E

P05

Mr F

P06

Ms G

P07

Ma H

P08

Mr I

P09


Using Tuple Variables, also known as Table Aliases

Figure 86: Tuple Variables Or Table Aliases You can use tuple variables in the projection list. Tuple variables are defined in the FROM clause and also referred to as table aliases in SAP HANA (and most other DBMSs). NAME PNR SELECT o.Name,

o.PNr

FROM Official o; You can use the (optional) key word AS in the definition of a tuple variable: SELECT o.Name,

o.PNr

Mr A

POl

Mr B

P02

Ms C

P03

Ms 0

P04

Mr E

P05

Mr F

P06

Ms G

P07

Ms H

POB

Mr I

P09

NAME

PNR

FROM Official AS o; Figure 87: Tuple Variables aka Table Aliases Despite having defined table aliases the projection list may contain unqualified column names: SELECT Name,

PNr

FROM Official

o;

A mix of qualified and unqualified column names is possible: SELECT o.Name,

PNr

FROM Official o;

Mr A

POl

Mr B

P02

Ms C

P03

Ms 0

P04

Mr B

P05

Mr F

P06

Ms G

P07

Ms H

P08

Mr I

P09

Figure 88: Tuple Variables aka Table Aliases

2014

© 2014 SAP AG or

an


37 �



HA150


If a table alias is defined in the FROM clause, you are not allowed to use the corresponding table name for qualification of a column name:

SELECT Official.Name FROM Official;

SELECT o.Name FROM Official o; SELECT Name

SELECT Name

FROM Official o;

FROM Official;

Figure 89: Tuple Variables aka Table Aliases

Can the projection list contain columns that are based on a case differentiation?

l

Figure 90: Case Differentiation

The projection list can contain columns that are based on a case differentiation These columns can be named explicitly: SELECT

*, CASE WHEN HP < 120 THEN

'low'

WHEN HP >= 120 AND HP < 180 THEN 'medium' ELSE

'high'

END AS Rating FROM Car;


�


38


2014




HA150

�

CARm

�Ell

BRAND

COLOR

Hl'

ONN&ll.

RA'l'INQ

H06

low

mediwa

------

----------

-------

------

1'01

HD-V 106

!'i.at

red

75

ro2

llD-VW 4711

vw

black

uo

H03

!'03

llD-Jl'. 1972

-

blue

184

H03

biqb

rot

HD-AL 1002

Mercedes

white

136

KO?

-diua

ros

llD-tti 3206

Mercedes

bli1Clc

170

803

llD-VW 1999

yellow

260

KOS

biqb

ro?

HD-ML 3206

Audi Audi

-iua

1'06

blue

116

803

low

!'08

HD-rK 1002

vw

black

160

HO?

mediua

F09

HD-UP 13

Slcoda

red

105

802

low

!'10

HD-Mr 507

140

804

mediua

HD-tti 208

-

black

I'll

green

184

H02

biqb

1'12

HD-XY 4711

Slcoda

red

105

K04

low

!'13

HD-IK 1001

Renault

red

136

HO?

medium

ru

llD-lti 1977

Mercedaa

Wbito

170

K03

medium

!'15

HD-MB 3030

Skoda

black

136

K03

medium

!'16

?

Opal

120

?

medium

!'17

HD-Y 333

Audi

qroen oranqe

184

H09

biqb

!'18

HD-� 2006

Renault

red

90

H03

low

!'19

HD-VW 2012

vw

blilck

125

KOl

medium

!'20

?

qreen

184

?

bi9b

Audi


�

If a case differentia ton does not correspond to any of the listed cases, a NULL value is returned: i

SELECT CarID, CASE Color

WHEN 'red'

THEN 1FF00001

CARID

COLOR

!'01

!'FOOOO

1'02

000000

--

--

-

!'03

0000!'!'

1'04

!'M'FF!'

!'05

000000

!'06

l'!'!TOO

1'07

0000!'!'

1'08

000000 rroooo

WHEN

•green'

THEN 100FF001

1'0 9

WHEN

'yellow'

THEN 1 tttt'OO I

!'10

000000

WHEN

'blue'

THEN

!'11

OOFFOO

•white'

THEN 'FFFF FF'

1'12

WHEN

!'13

ITOOOO ITOOOO

WHEN

'black'

THEN

1'14

Fl'FF!'!'

END AS Color FROM Car;

'0000FF' •000000•

1'15

000000

1'16

001'1'00

"" 1'17 !'18

? FFOOOO

!'19

000000

!'20

OOFFOO


2014


39 �




HA150

Both the THEN and the ELSE branch can contain references to table columns: SELECT CarID, CASE WHEN PlateNumber IS NOT NULL THEN PlateNumber ELSE 'The Car is not registered!' END AS PlateNumber, CASE Brand WHEN 'Mercedes' THEN 'Mercedes-Benz' WHEN 'VW' THEN 'Volkswagen' ELSE Brand END AS Brand, Color,

HP

FROM Car;


�

CAR:m

PLM'ENUMBER

BMND

--------------------------

-------------

COLOR

HP

------

1'01

HD-V 106

Fiat

red

75

1'02

HD-VW 4711

Volkswagen

blaclc

l.20

1'03

HD -J.ll. 1972

-

blue

184

1'04

HD-AL 1002

Mez:cedes-Be.nz

White

136

1'05

HD-MM 3206

Mercedes-Benz

black

170

1'06

HD-VW 1999

Audi

yellow

260

1'07

HD-ML 3206

Audi

blue

116

1'08

HD-l'.K 1002

Volkswagen

black

160

1'09

HD-UP 13

Skoda

red

105

1'10

HD-Mr 507

-

black

140

I'll

HD-MM 208

-

green

184

1'12

HD-XY 4711

Skoda

red

105

1'13

HD-l'.K 1001

Renault

red

136

11'14

HD-MM 1977

Mercedes-Benz

White

170

1'15

HD-MB 3030

Skoda

black

136

1'16

Th.e car is not

opel

green

120

Audi

orange

184

Renault

red

90

registered!

333

l'l.7

HD-Y

1'18

llD-MQ 2006

1'19

HD-VW 2012

Volkswagen

black

125

1'20

The Car is not xeqistered!

Audi

green

184


�

SELECT DISTINCT

Figure 96: Duplicate Elimination


40


2014




HA150

A table with a primary key does not contain duplicates SELECT * FROM Car; HP

OWNER

CARIO

PLATENUMBBR

BRAND

COLOR

- ---

----

- ------

---

F04

HO-AL 1002

Morcedos

white

136

HO?

FOS

HO-MM 3206

Mercedes

black

170

H03

F09

HO-UP

Skoda

red

105

H02

Fl4

HO-MM 1977

MGrcedes

white

170

H03

FlS

HO-MB 3030

Skoda

black

136

H03

-

-----

--

13

-

-

--

-

---

Figure 97: Duplicate Elimination Duplicates can occur when a key column Is not Included In the projection

list: BRAND

SELECT Brand FROM Car;

Mercedes Mercedes Skoda Mercedes Skoda

Figure 98: Duplicate Elimination If duplicate elimination Is not intended, you can specify the keyword ALL:

BRAND

SELECT ALL Brand FROM Car;

Mercedes Mercedes Skoda Mercedes Skoda


2014


41 �




HA150

The keyword DISTINCT ensures that the result table contains no duplicates: BRAND

SELECT DISTINCT Brand

Fiat

FROM Car;

vw BMW Mercedes Audi Skoda Renault Opel

Figure 100: Duplicate Elimination NULL values are treated in duplicate elimination as "normal" values The result table contains (at most) one row that consists entirely of NULL values: OVERTIME

SELECT DISTINCT overtime

10

FROM Official;

20 ? 18 22


The "duplicate" term always refers to full result rows: SELECT Brand,

Color

FROM Car;

Duplicates

BRAND

COLOR

--------

-----

Mercedes Mercedes -c Skoda Skoda Mercedes Skoda

white black red red white black


�


42


2014




HA150

If a projection list contains multiple columns, DISTINCT always refers to the combination of all these columns: SELECT DISTINCT Brand,

Color BRAND

FROM Car;

--

COLOR

-----

--

-

-

--

Mercedes

white

Mercedes

black

Skoda

red

Skoda

black

Figure 103: Duplicate Elimination DISTINCT can also be used If the result table should contain all the columns If the (source) table has a (primary) key DISTINCT Is not required: SELECT DISTINCT FROM Car;

* CAAID

l'LM'll\N't.tG!&R ------

-----

llMND

COLOR

HP

OWNER

-------

-

F04

HD-AL 1002

a Hercede

wtiite

136

K07

ros

HD-I+! 3206

MN-cedea

blaclc

170

H03

rot

HD-UP 13

Sl
red

105

K02

ru

HD-I+! 1977

He.rcedea

Wbite

170

H03

Sl
blaclc

136

H03

ris

HD-MB 3030


ORDER BY

Figure 105: Sorting

2014


43 �




HA150

The result table can be sorted by a specific column:

BRAND --------

SELECT Brand,

Color

FROM Car ORDER BY Brand;

COLOR ------

Aud i

yellow

Aucli

bl ue

Aucli

orange

Audi

9reen

BMW

blU
BMW'

black

BMW'

green

Fiat

red

Mercedes

white

Mercecle&

black

Mercecle&

white

Opel

qreen

Renault

red

Renault

red

Skoda

red

Skoda

red

Skoda

black

vw

black

vw

black

vw

black

BRAND

COLOR

Figure 106: Sorting

A descending sorting is possible: SELECT Brand,

Color

FROM Car ORDER BY Brand DESC;

--------

------

vw

black

vw

black

vw

black

Skoda

black

Skoda

red

Skoda

red

Renault

reel

Renault

reel

Opel

green

Mercedes

white

M�rcai�s

black

Merced-aa

whit..

Fiat

red

BMW

green

BMW

black

BMI�

blue

Audi

green

Audi

orange

Audi

blue

Audi

yellow

Figure 107: Sorting


44


2014




HA150

To sort ascending apply the optional keyword ASC: SELECT Brand,

Color

FROM Car ORDER BY Brand ASC;

llRAND

COLOR

-------

------

AUdJ.

y"1l.ow

AudJ.

blU&

AUdJ.

orange

AudJ. llMR

green blue

llMR

black

Fiat

red

Mercedas

white

llHFI

green

?4ercede.s

black

t4arcedas

white

Opel

green

Renaul.t

red

Renaul.t

red

Skoda

red

Skoda

red

Skoda

black

Vl'J

black

Vl'J

black

vw

black

Figure 108: Sorting

The sorting can be applied using a column that does not appear In the projection list:

l!JRAND -----

--

-

Opel

Audi SELECT Brand,

Color

FROM Car ORDER BY PlateNumber;

COLOR ----

qreen qreen

Renault

red

Hercede•

white

vw

black

Skoda

blue black

Audi

blue

Her cedes

Wbite

Her cede a

black

-

Renauit

-

qreen red black

Skoda

red

riat

red

Audi

yellow

vw

black

vw

black

Skoda

red

AUd i

orange

Figure 109: Sorting

2014

© 2014 SAP AG or a n SAP affiliate company. All rights reserved.

45 �




HA150

You can sort using a combination of columns: SELECT Brand,

Color

FROM Car ORDER BY Brand ASC,

Color DESC;

BllAND --------

COX.OR -----

Audi

yellow

Audi

orange

Audi

qreen

Audi

blue

-

green

-

blue

-

black

Fiat

red

Mexcedes

white

Mercedes

white

MBXCCdeS

black

Opel

green

Renault

red

Renault

red

Skoda

red

Skoda

red

Skoda

black

vw

black

vw

black

vw

black

BllAND --------

COX.OR ---

Audi

yellow

Figure 110: Sorting

Instead of the column names in the ORDER BY clause, the column numbers (based on the projection list) can be used: SELECT Brand,

Color

FROM Car ORDER BY 1 ASC,

2

DESC;

--

Audi

oranqe

Audi

green

Audi

blue

-

green

-

blue

!!MN

black

!'iat

red

Marcedea

white

Mercedes

white

Moxcedea

black

Opel

green

Renault

r,ed

Renault

red

Skoda

xed

Skoda

red

Skoda

black

vw

black

vw

black

vw

black

Figure 111: Sorting


46


2014



Lesson: Read ing Data from a Table or View

HA150

M1.NUl'AC'!Ul\ER

If result columns are named explicitly, you can refer to the new name for sorting:

------------

Audi Audi

SELECT Brand AS Manufacturer,

Color

FROM Car ORDER BY Manufacturer ASC,

Color DESC;

COLOR ------

yel.l.ow orange

Audi

green

Audi

blue

-

blue

-

blaclc

green

riat

red

Hercedea

White

Her cede a

white

MOreodea

blaclc

Opel

green

Renault

red

Renault

red

Skoda

red

Skoda

red

Skoda

black

vw

blaclc

vw

black

vw

black

Hl\NU!'AC'l'ORER

COLOR

Figure 112: Sorting

If result columns are explicitly renamed, you can still reference the original name in the ORDER BY clause: SELECT Brand AS Manufacturer,

FROM Car ORDER BY Brand ASC,

Color DESC;

Color

------------

------

Audi

yellow

Audi

ora09e

Audi

green

Audl.

blue

-

green

Fiat

red

Hereedca

white

Mercedes

White

Her cede•

blaclc

blue blaclc

Opel

green

Renault

red

Renault

red

Skoda

red

Skoda

red

Skoda

blaclc

vw

blaclc

vw

black

vw

black

Figure 113: Sorting

2014


47 �




HA150

CARm

You can sort based on calculated values. ;.Sorting criteria: How much Is the power below 200

kW?l!'06 1'03 I'll

SELECT CarID, Brand, HP

!'17

FROM Car ORDER BY 200

-

HP / 1.36 ASC;

BRAND

HP

------Audi -

-

260 194 184

Audi

184

!'20

Audi

184

1'05

Mezcedea

170

Fl4

Mercedes

170

1'09

vw

160

-

140

1'04

Mercedea

136

!'13

Renault

136

Skoda

136

vw

125

FlO

!'15 1'19

vw

120

opel

120

1'07

Audi

116

1'09

Skoda

!'02 1'16

Fl2 1'19 !'01

Skoda

105 105

Renault

90

Fiat

75

Figure 114: Sorting You can reference functions in the ORDER BY clause:

SELECT

Name,

Birthday

FROM Owner ORDER

BY YEAR(Birthday)

DESC,

Name

ASC; NAME

BIRTHDAY

Mr x

1986-08-30

Mr z

1986-02-03

Ms y

1986-02-10

Ms u

1966-05-11

Ms w

1957-06-01

Mr V

1952-04-21

Ms T

1934-06-20

HOM AG

?

IKEA

?

SAP AG

?

Figure 115: Sorting

How many rows are in the result set?

Figure 116: Top-n Clause

�


48


2014




HA150

You can determine how many rows should be included in the query result (maximum). ;;.What are the ten most powerful vehicles (based on horse power)? SELECT TOP 10 Carro,

Brand,

FROM Car ORDER BY 4 DESC;

Color, HP CARID

BRAND

COLOR

-----

---- --- -

----

HP

F0 6

Audi

yellow

260

F20

Audi

green

184

Fl7

Audi

orange

184

Fll

green

184

F03

BMW BMW

blue

184

Fl4

Mercedes

white

170

--

F05

Morcodos

black

170

F08

vw

black

160

FlO

BMW

black

140

Fl5

Skoda

black

136

Figure 117: Top-n Clause You can also use the Top-n Clause if the result table should include all columns. ;;.What are the 5 most powerful vehlcles (based on horse power)? SELECT TOP 5

*

FROM Car ORDER BY HP DESC; PLATENUMBER

---------- -

BRAND

COLOR

-----

--

-

--

HP

OWNER

-

F06

HD-VW 1999

Audi

yellow

260

HOS

F20

?

Audi

green

184

?

Fl7

HD-Y 333

Audi

orange

184

H09

Fll

HD-M-f 208

qroen

184

H02

F03

HD-JA 1972

BMW BMW

blue

184

H03

Figure 118: Top-n Clause You can combine the Top-n Clause with the keyword DISTINCT. ;;. What are the 7 highest HP values? SELECT TOP 7 DISTINCT HP FROM Car ORDER BY 1 DESC;

HP

260 184 170 160 140 136 125

Figure 119: Top-n Clause

2014


49 �



HA150


No error is thrown if you request more rows than available. The result set wlll not be filled with additional, "artlflclal" rows.

;.. 570 differe nt colors are requested: COLOR

SELECT TOP 570 DISTINCT Color red

FROM Car;

black blue white yellow green orange

Figure 120: Top-n Clause It Is possible to request 0 result rows. In this case the result set does not contain any row. >

Zero colors are requested:

SELECT TOP 0

COLOR

Color

FROM Car; Figure 121: Top-n Clause You can use the Limit clause as alternative to the Top-n clause. The Limit clause comes at the very end of the SELECT statement. What are the 5 most powerful vehicles (based on horse power)?

SELECT

*

FROM Car ORDER BY HP DESC LIMIT 5;

CARID ----

-

PLATENUMBER --

--

-------

BRAND

-

----

COLOR

HP

OWNER

yellow

260

HOS

------

-

-

---

F06

HD-VW 1999

Audi

F20

?

Audi

green

184

?

Fl7

HD-Y 333

Audi

orange

184

80 9

Fll

HD-MM 208

BMW

green

184

802

F03

HD-JA 1972

BMW

blue

184

H03

Figure 122: Limit And Offset Clause


50


2014




HA150

The Li mit clause can be combi ned with the Offset clause to skip records. This allows you to read result sets "page by page". What are the next 5 most powerful vehicles (based on horse power)?

SELECT FROM

*

Car

ORDER BY HP DESC LIMIT 5 OFFSET 5 ;

CAlUD

PLATENUMBBR

BRAND

COLOR

-----

-----------

--------

HP

OWNER -

----

------

F78

HD-MT 2510

?

green

260

F77

HD-MT 2509

?

red

184

?

F14

HD-MM 1977

Mercedes

white

184

H03

FOS

HD-MM 3206

Mercedes

black

184

H03

vw

black

184

H07

F07

HD-IK 1002

?

Figure 123: Limit And Offset Clause

Which rows are included in the result set?

Figure 124: WHERE Clause The WHERE clause is used to filter rows. It is used to extract only those rows that fulfill a specified criterion.

SELECT PlateNumber,

Brand,

Color

FROM Car WHERE Brand =

'BMW 1 ; PLATENUMBER

BRAND

COLOR

-------

-----

-----

HD-JA 1972

BMW

blue

HD-MT 507

BMW

black

HD-MM 208

BMW

green

----

Figure 125: WHERE Clause

2014


51 �




HA150

You can reference numeric columns in the WHERE clause. SELECT

HP,

Brand,

Color

FROM Car WHERE

HP >=

170;

BRAND

COLOR

--------

------

184

BMW

blue

170

Mercedes

black

260

Audi

yellow

HP

184

BMW

green

170

Mercedes

white

184

Audi

orange

184

Audi

green

Figure 126: WHERE Clause You can reference a column in the WHERE clause that is not included in the projection list. SELECT Brand,

Color

FROM Car WHERE

HP >= 170;

BRAND

COLOR

--------

------

BMW

blue

Mercedes

black

Audi

yellow

BMW

green

M erced as

white

Audi

orange

Audi

green

Figure 127: WHERE Clause You can check for NULL in the WHERE clause:

SELECT

CarID,

Brand,

Color

FROM Car WHERE PlateNumber IS NULL;

CARID

BRAND

COLOR

Fl6

Opel

green

F20

Audi

green



52


2014



HA150

CAIUD

You can check for IS NOT NULL

Brand,

Color

FROM Car WHERE

PlateNumber

COLOR

--------

in the WHERE clause: SELECT CarID,

BRAND

IS NOT NULL;

-----

ro1

riat

red

ro2

vw

black

ro3

-

blue

F04

Mercedes

white

ros

Mercedes

black

F06

Audi

yellow

F0'1

Audi.

bl ue

roe

vw

black

F09

Skoda

red

FlO

BWiV'

black

Fll

BMN

green

r12

Skoda

red

1'13

Renault

red

1'14

Mercedes

white

F 15

Skoda

black

Fl?

Audi

0%'ange

1'18

Renault

red

1'19

vw

black

Figure 129: WHERE Clause You can check if values are included IN a value-list.

SELECT Brand,

Color

FROM Car WHERE

Color

IN

( 'red' ,

'blue'

I

'orange')

I

BRAND

COLOR

-------

------

Fiat

red

BMW

blue

Audi

blue

Skoda

red

Skoda

red

Renaul.t

red

Audi

orange

Renault

red

Figure 130: WHERE Clause You can check if values are included in an interval. SELECT PlateNumber,

Brand,

Color,

HP

FROM Car WHERE

HP BETWEEN

140 AND

170;

HP

PLATENUMBER

BRAND

COLOR

----- - ---- -

--------

-- ---

HD-MM 3206

Mercedes

black

170

HD-IK 1002

vw

black

160

HD-MT 507

BMW

bl.ack

140

HD-MM 1977

Mercedes

white

170


2014


53 �



HA150

You can reference calculated values in the WHERE clause. We do not recommend this option for performance reasons. :>Which cars have a power of at least 125 kW?

SELECT CarID,

Brand,

CARID

HP

-----

FROM Car WHERE

Figure

HP

I

132: WHERE

1.36 >= 125;

HP

BRAND --------

F03

BMW

184

F05 F06 F11

Mercedes

F14 F17 F20

Mercedes Audi Audi

170 260 184 170

Audi BMW

184 184

Clause

You can use functions In the WHERE clause.

SELECT

*

FROM Owner WHERE YEAR(Birthday)

Figure

133: WHERE

-

1986;

OWNERID

NAME

BIRTHDAY

CITY

HOS

Mr X

1986-08-30

Walldorf

H09

Ms Y

1986-02-10

Sinsheim

HlO

Mr Z

1986-02-03

Ladenburg

Clause

You can use search patterns in the WHERE clause.

SELECT PlateNumber,

Brand,

Color,

HP

FROM Car WHERE PlateNumber LIKE

Figure

134: WHERE

1%MM%1;

PLATENUMBER

BRAND

COLOR

-----------

--------

-----

HD-MM 3206

Mercedes

black

170

HD-MM 208

BMW

green

184

HD-MM 1977

Mercedes

white

170

HP

Clause


54


2014



HA150


The wildcard character o/o (percentage sign) represents any string containing no, one, or multiple characters. The wildcard character_ (underscore) represents any single character. String starting with •M•

WHERE MyColumn LIKE 'M\' WHERE

MyColumn LIKE 'M

WHERE

MyColumn LIKE '%M'

String of two characters starting with "M"

•

String ending with "M"

WHERE MyColumn LIKE '%M%' WHERE

String containing "M"

MyColumn LIKE '

WHERE MyColumn LIKE

'

String with length 3 T_M%'

·r" on

____

fifth and

"M" on seventh position

Figure 135: LIKE Predicate If you want to search for the percentage sign

(%)

or underscore (

) Itself,

_

you have to place an ESCAPE character in front. You can choose the ESCAPE character (with some restrictions). LIKE '$\\'

ESCAPE '$'

LIKE '$ --'

ESCAPE '$'

LIKE '\$\'

ESCAPE '$'

LIKE '\$\\'

•

String of two characters starting with an underscore String ending with

percentage sign

String containing an underscore followed by a

ESCAPE '$'

----$\_$ -''

a

String containing a percentage sign

ESCAPE '$'

LIKE '%$_$\\' LIKE

String starting with a percentage sign

ESCAPE

percentage stgn

$.

•

"%"on

fifth and

._.on sewnth position.

Figure 136: LIKE Predicate If you need to search for the escape character within a string, you need to put the escape character In front of itself to mask It.

LIKE '$\\$$'

ESCAPE

LIKE '$_\$$\$_'

String starting with

'$'

ESCAPE

and ending

ESCAPE '$'

LIKE '\$_\$$'

ESCAPE '$'

LIKE ' ----$%_$$%'

ESCAPE

a

percentage sign

$

String starting and ending with

'$'

LIKE '\$\$$\'

wit h

an

underscore

and containing the escape character $ Stnng containing

a

percentage sign

followed by $ String containing

an

underscore and

ending with $

'$'

"%• on fifth and ·s· on seventh positon i

Figure 137: LIKE Predicate

2014


55 �




HA150

You can use compound WHERE clauses. SELECT

PlateNumber,

Brand,

Color

FROM Car WHERE

Brand=

'Skoda' AND

Color=

'red';

PLATENUMBER

BRAND

COLOR

HD-UP 13

Skoda

red

HD-XY 4711

Skoda

red

Figure 138: WHERE Clause You can reference the same column multiple times. SELECT

PlateNumber,

Brand,

Color

FROM Car WHERE Brand=

'Skoda'

OR Brand=

'BMW';

PLATENUMBER

BRAND

COLOR

-----------

-----

-----

HD-JA 1972

BMW

blue

HD-UP 13

Skoda

red

HD-MT 507

BMW

black

HD-MM 208

BMW

green

HD-XY 4711

Skoda

red

HD-MB 3030

Skoda

black

PLATENUMBER

BRAND

COLOR

HD-MT 507

BMW

black

HD-MB

Skoda

black

Figure 139: WHERE Clause You can use brackets. SELECT PlateNurnber,

Brand,

Color

FROM Car WHERE

(Brand

=

AND Color

'Skoda' =

OR Brand

=

'BMW')

'black' ;

3030



56


2014




HA150

)>- The license plate started with "HD" for Heidelberg )>- I'm sure I saw an

"M"

;. I clearly remember the digits "2" and 6 "

"

;. The "2" was definitely left of the 6 "

"

')lo

Between the "2" and the "6" was exactly one digit

�

It was neither Skoda, nor a VW

�The car was blue or green

SELECT *

CARID

PLATENUMBER

BRAND

COLOR

HP

OWNER

F07

HD-ML 3206

Audi

blue

116

H03

FROM Car WHERE

PlateN1.lI!lber LIKE 'HD-%M% %2

AND Brand<>

'Skoda'

AND

'blue'

(Color =

6%'

AND Brand<> 'VW' OR Color

=

•green');


What is the precedence of the different operators?

Figure 142: Operator Precedence The operators have the precedence Indicated by the table below: Precede n c e

Operator

Explanation

Highest

()

parentheses unary minus

• ,I + '

NULL, LIKE, BETWEEN

=, <, <=, >, >=, <>,IS

Lowes t

multiplication, division addition, subtraction comparison

NOT

logical negation

AND

conjunction

OR

disjunction

Figure 143: Operator Precedence

2014

© 2014 SAP AG or

an


57 �




�

HA150


58


2014




HA150

Exercise 1 : Exercise 1 Exercise Objectives After completing this exercise, you will be able to: •

retrieve data from a database table using the SELECT statement.

Business Example Task: [Enter a high level description of the task the learner will perform in this exercise.]

1.

Create a list of employees. All columns arc relevant.

2.

Which employees have fewer than 30 remaining vacation days?

3.

Create a list of the departrnent IDs the difterent employees work for. The list should not contain duplicates.

4.

Which employees bave fewer than 30 remaining vacation days? Sort the output descending by RemainderDays and - for employees \Vith the same number of RemainderDays - ascending by DNumber.

5.

For which employees are the remaining vacation days unknown?

6.

For which employees are the remaining vacation days known?

7.

Ho\V many remaining vacation days can be found for the different employees in the company? Each quantity number should be displayed only once.

8.

Create a list of e1nployecs. Only columns ONumber and Name are relevant.

9.

Create a list of employees. Only columns DNumber and Name are relevant. The output column DNumber should be renamed to "PERSNUMBER".

LO.

Create a list of employees. Only colun1ns DNumber and Name are relevant. The output column DNumber should be renamed to "Personal Number" (with small letters).

11 .

How many remaining vacation days has "Ms D"?

12.

Which employees have exactly

13.

The value of a vacation day is estimated at EUR 300.- (all-in). The output

10 remaining vacation days?

of the employee data should be extended by the calculated value of the remaining vacation days.

14.

The value of a vacation day is estimated al

EUR 300.- (all-in). Which

employees have remaining vacation days with a value of at least EUR 600.-?

Continued on next page

2014



59 �




15.

HA150

Which numbers of remaining vacation days can be found in the company? Each quantity number should be displayed only once.

16.

Which employees \vork for department "A04" and have exactly 20 remaining vacation days?

17.

Which employees \vork in department "AO l" or "A04"? Use OR

18.

Which employees work in department "AO l" or "A04"? Use IN

19.

Which employees not working for department "AO l" have exactly 10 ren1aining vacation days?

20.

Which employee names include an "L"?

21.

Which employees have a name beginning with "F" and in the third place an "a"?

�

60


2014




HA150

Solution 1: Exercise 1 Task: [Enter a high level description of the a t sk the learner will perform in this exercise.) l.

Create a list of employees. All columns are relevant. a)

2.

SELECT

FROM Employee;

Which employees have fe\ver than 30 remaining a)

3.

*

vacation

days?

SELECT* FROM Employee WHERE RemainderDays < 30;

Create a list of the department IDs the different employees work for. The list should not contain duplicates. SELECT DISTINCT DEPID FROM Employee;

a) 4.

Which employees have fewer than 30 remaining vacation days? Sort the output descending by RemainderDays and

-

for employees with the

same number ofRemainderDays - ascending by DNumbcr

.

a)

SELECT * FROM Employee WHERE RemainderDays

<

30 ORDER

BY RemainderDays DESC, DNumber ASC; 5.

For which employees are the remaining vacation days unknown? SELECT* FROM Employee WHERE RemainderDays IS NULL;

a) 6.

For \Vhich employees a)

7.

are

the remaining vacation days known?

SELECT* FROM Employee WHERE RcmaindcrDays TS NOT NULL;

How many remaining vacation days can be found for the ditTerent employees in the company? Each quantity number should be displayed only once. SELECT DISTINCT RemainderDays FROM Employee WHERE

a)

RemainderDays IS NOT NULL; 8.

Create a list of employees. Only columns DNumbcr and Name are relevant. SELECT DNumber, Name FROM Employee;

a) 9.

Create a list of employees. Only columns DNumbcr and Name are relevant. The output column DNumber should be renamed to "PERSNUMBER". SELECT DNumber AS PersNumber, Name FROM Employee;

a) 10.

Create a list of employees. Only columns DNumber and Name are relevant. The output column DNumber should be renamed to "Personal Number'' (with small letters). a)

SELECT DNumber AS "Personal Number", Name FROM Employee; Continued on next page

2014

© 2014 SAP AG or an SAP

te company. All rights reserved.

affili a

61 �




11.

How many remaining vacation days has "Ms D"? a)

12.

SELECT RemainderDays FROM Employee WHERE Name= 'Ms D';

Which employees have exactly 10 remaining vacation days? a)

13.

HA150

SELECT* FROM Employee WHERE RemainderDays= 1 O;

The value of a vacation day is estimated at EUR 300.- (all-in). The output of the employee data should be extended by the calculated value of the remaining vacation days. a)

SELECT*, RemainderDays* 300 AS RemainderDaysValue FROM Employee;

14.

The value of a vacation day is esti1nated at EUR 300.- ( all-in). Which en1ployees have remainjng vacation days with a value of at least EUR 600.-? a)

SELECT*, RemainderDays* 300 AS RemainderDayValue FROM Employee WHERE RemainderDays* 300 >= 600;

15.

Which numbers of remaining vacation days can be found in the company? Each quantity number should be displayed only once. a)

SELECT DISTINCT RemainderDays FROM Employee WHERE RernainderDays IS NOT NULL;

16.

Which employees v.rork for department "A04" and have exactly 20 re1naining vacation days? a)

SELECT * FROM Employee WHERE DeplD RemainderDays

17.

=

=

'A04' AND

20;

Which employees work in depart:Jnent "AOl" or "A04"? Use OR a)

SELECT* FROM Employee WHERE DeplD = 'A04'·

=

'AOl' OR DepID

'

18.

Which employees \¥Ork in department "AOl" or "A04"? Use IN a)

19.

SELECT* FROM Employee WHERE DeplD JN ('AO1', 'A04');

W11ich employees not working for department "AOl" have exactly 10 remaining vacation days? a)

SELECT * FROM E1nployee WHERE DepID RemainderDays

20.

'AOl' AND

10;

Which e1nployee names include an "L"? a)

21.

=

<>

SELECT* FROM Employee WHERE Name LIKE '%L%';

Which employees have a name beginning with "F" and in the third place an "a"? a)

�

SELECT* FROM Employee WHERE Name LIKE 'F_a%';


62


2014




HA150


2014

•


•


•


•

include columns based on conditions.

•


•


•


•



63 �



Unit Summary

HA150


�

•

Write si1nple database queries using SQL's SELECT statement.

•


•


•


•


•


•


•



64


2014







•

n1 Aggregating Data Unit Overview Unit Objectives After completing this unit, you will be able to: •

Determine aggregated values on table columns using a single SELECT statement.

•

List the aggregate functions supported by HANA.

•

Determine such aggregated values for groups of rows, using the GROUP BY clause.

•

Filter such groups using the HAVING clause.

Unit Contents Lesson: Aggregating Data Exercise 2: Exercise 2

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

66 77

65 �



Unit 3: Aggregating Data

HA150

Lesson: Aggregating Data

Lesson Overview This lesson covers ho"'' you can pero f rm certain calculations on a database table column across multiple rows of the database table.

Lesson Objectives After completing thjs lesson, you will be able to: Determine aggregated values on table cohunns using a single SELECT

•

statement. •


•

Determine such aggregated values for groups of rows, using the GROUP BY clause.


•

Business Example

Calculations across multiple rows Figure 144: Aggregate Expressions

Count ( *)

+ You can calculate the number of rows in the result set using COUNT. +

Rows containing only NULL values are included.

;;.

What Is the quantity of cars with the brand "Audi"?

COUNT(*) SELECT COUNT(*) 4

FROM Ca.r WHERE Brand

=

1 Audi 1 ;

Figure 145: Aggregate Expressions

�

66


2014




HA150

Count(*)

You can explicitly name columns created by aggregate expressions. >What is the quantity of cars with the brand "Audi"?

SELECT COUNT(*)

AS

"Quantity of Audi"

FROM Car WHERE Brand= 'Audi'; Quantity of Audi 4


Count ()

• You can calculate the number of values within a sing le column. •

NULL values are not included.

•

You can only use a single column as parameter of COUNT.

>

How many cars are registered to an owner?

;;.

This does not calculate the number of different owners! COUNT

SELECT COUNT(Owner)

(OWNER)

18

FROM Car; Figure 147: Aggregate Expressions

2014


67 �




HA150

Count (DISTINCT )

• You can calculate the number of different NOT-NULL-values of a certain column.

• You can use only a sing l e column as parameter for COUNT DISTINCT. NULL values are not included.

'!>

How many different owners have a registered car?

SELECT COUNT(DISTINCT Owner) FROM Car; COUNT (DISTINCT OWNER) 8

Figure 148: Aggregate Expressions s

SELECT * FROM T;

? ? ?

SELECT S FROM T;

x

. ,.

COUNT(')

SELECT COUNT ( *) FROM T; SELECT COUNT ( S) FROM T;

:S

I' C EL NCT EC hSTI 6 $ 'T'l )

2

x

5 COUNT(S)

:

FROM"T;"

...

COUNT (DISTINCT S )

SELECT COUNT(DISTINCT S) FROM T;

1

Figure 149: Aggregate Expressions MIN/MAX()

You can calculate the minimum or maximum value in a column. >-What is the horsepower rang e o f the registered cars?

SELECT MIN(HP),

MAX(HP)

FROM Car;

MIN (HP)

MAX (HP)

75

260


�


68


2014




HA150

You can combine aggregate expressions and "normal" functions. >In which year was the youngest owner born?

SELECT M AX(YEAR(Birthday)) FROM Owner.;

... .

..

..

AS Year

............. ... .

1986

SELECT

YEAR(MAX(Birthday))

AS Year

FROM Owner;

Figure 151: Aggregate Expressions The sequence of nested functions can be releva nt

.

>What is the lowest or highest HP value?

ABS(MAX(O-HP)) SELECT

ABS(MAX (0

-

HP))

-

HP))

FROM Car;

SELECT MAX(ABS(O FROM Car;

75

MAX (ABS (0-HP) ) 260

Figure 152: Aggregate Expressions The WHERE clause is included in the minimum and maximum calculation

.

;;. What is the horsepower range

SELECT MIN(HP),

of BMWs?

FROM Car WHERE

Brand=

MIN(HP)

MAX(HP)

140

184

MAX(HP)

'BMW';


2014

© 2014 SAP AG

or an

SAP affiliate company. All rights reserved

.

69 �




HA150

SUM ()

• You can sum up the values in a column. • The WHERE clause is included in the summation. • NULL values in the column are ignored. • Individual NULL values contained in the column do not result in NULL for the sum. (as long as there is at least one numeric value)

What is the sum of horsepower for all Audis?

>

SUM(HP)

SELECT SUM (HP) FROM

744

Car

WHERE Brand=

1 Audi 1 ;


SUM (DISTINCT )

•

You can sum up the distinct values of a column.

•

The WHERE clause is included in the summation.

• NULL values of the column are ignored. •

Individual NULL values contained in the column do not result in NULL for the sum. (as long as there is at least one numeric value)

•

If a value occurs multiple times, it is still counted only once when summing

SELECT SUM(DISTINCT HP) FROM

Car

SUM(OISTINCT HP) 560

WHERE Brand= 'Audi'; Figure 155: Aggregate Expressions


70


2014



HA150


AVG ()

•

You can calculate the average of all values in a column.

•

The WHERE clause is included in the calcu lation. NULL values in the column are completely ignored

•

(in the numerator and denominator) Individual NULL values contained in the column do not result in NULL for the

•

average

.

).

What is the average horsepower of Audi? AVG(HP)

SELECT AVG (HP)

186

FROM Car WHERE Brand

=

'Audi';


AVG (DISTINCT )

•

You can calculate the average of all distinct values in a column.

•

The WHERE clause is included in the calculation. NULL values in the column are completely ignored

•

(in the numerator and denom inator ) Individual NULL values contained in the column do not result in NULL for the

•

average. •

If a value occurs multiple times, it is still counted

only

once for the average

calculation.

SELECT AVG(DISTINCT HP)


=

'Audi ';

AVG(DISTINCT HP) 186.6666666666666666666666666666667


Which aggregate expressions are available in SAP HANA?


2014


71 �



HA150


SAP HANA provides the following aggregate expressions:

ate Name

1-.....:is. ....z .:z.

Description

COUNT

Count

MIN

Minimum

MAX

Maximum

SUM

Sum

AVG

Average

STD DEV

Standard Deviation

VAR

Variance


GROUP BY

Figure 160: Group By

+

A table can be divided into (disjoint) groups of rows

+

A group is represented in the query result by a single row

+

Aggregate expressions will be evaluated separately for each group

,..

What is the number of cars per brand?

SELECT Brand,

COUNT(*)

BRAND --------

FROM Car GROUP BY Brand;

COUNT(*) --------

Fiat vw BMW

1 3 3

Mercedes

3 4 3

Audi

Skoda Renault Opel

2 1


�


72


2014




HA150

:;;

What is the highest horse power value per brand?

SELECT Brand,

MAX(HP)

BRAND

MAX(HP)

--------

FROM Car GROUP BY Brand;

-------

Fiat

75

vw

160

BMW

184

Mercedes

170

Audi

260

Skoda

136

Renault

136

Opel

120

Figure 162: Group By NULL values of the GROUP BY column are treated as "normal" values creating a single group. i>How often a certain overtime value occurs?

SELECT overtime,

COUNT(*)

AS Frequency

FROM Official GROUP BY overtime;

OVERTIME

FREQUENCY

10

3

20

1 3

-. ? 18

1

22

1

Figure 163: Group By You can combine GROUP BY with ORDER BY for sorting. SELECT Brand,

MAX(HP)

BRAND --------

FROM

Car

GROUP BY Brand ORDER BY 2

DESC,

1 ASC;

MAX(HP) -------

Audi

260

BMW

184

Mercedes

170

vw

160

Renault

136

Skoda

136

Opel

120

Fiat

75


2014

© 2014 SAP AG or

an


73 �




HA150

You can use functions In the GROUP BY clause. +What is the number of owners per year of birth?

SELECT YEAR(Birthday},

COUNT(*)

FROM Owner GROUP BY YEAR(Birthday} ORDER BY 2 DESC,

YEAR (BIRTHDAY)

1 ASC;

COUNT(*)

--------------

--------

?

3

1986

3

1934

1

1952

1

1957

1

1966

1


The WHERE clause is processed before the grouping. >What is the number of black cars per brand? :i-Only brands with black cars are included into the result set.

SELECT Brand,

COUNT(*)

FROM Car WHERE Color

=

'black'

GROUP BY Brand;

COUNT(*)

BRAND --------

--------

vw

3

Mercedes

1

BMW

1

Skoda

1


You can use a combination of columns in the GROUP BY clause.

SELECT Brand,

Color,

COUNT(*)

FROM Car GROUP BY Brand,

BRAND

Color;

--------

COLOR

COUNT(*)

------

--------

Fiat

red

1

vw

1>1ack

3

BMH

l>l.ue

1

Mercedes

white

2

t.fercedes

l>l.ack

1

Audi

yella.1

1

Audi

blue

1

Skoda

red

2

Bl4l>1

black

1

BMfl

green

1

Renaul.t

red

2

Skoda

l>l.ack

1

Opel.

green

1

Audi

orange

1

Audi

green

1



74


2014




HA150

You can explicitly rename result columns In combination with GROUP BY. SELECT Brand AS Manufacturer,

Color,

AS "#

COUNT(*)

of

cars'1


Color;

HlWUl'.1iCTIJRER

COLOR

I of cars

riat

red

l.

vw

black

3

-

blue

l.

Merced.ea

Wbite

2

Hercedea

blacl<

l.

Audi

yellow

l.

Audi

blue

l.

Skoda

red

2

-

black

l.

-

green

l.

Renault

red

2

------------

--------

------


What groups are included in the query result?

Figure 169: HAVING Clause Using the HAVING clause you can specify which conditions a group must meet to be included in the result set. ;;.Which combinations of brand and color occur at least twice? SELECT Brand,

Color,

COUNT(*)


Color

HAVING COUNT(*)

>= 2; BRAND

COLOR

COUNT ( *)

VW

black

3

Mercedes

white

2

Skoda

red

2

Ronault

rod

2

Figure 170: HAVING

2014


75 �




HA150

The HAVING condition can reference columns not included in the projection list. l>What is the number of brand-color combinations, where at least one car has less than 120 HP? >We want to analyze based on the quantity of all cars, not only the cars with less than 120 HP.

SELECT Brand, FROM

Color,

COUNT(*)

Car

GROUP BY Brand,

Color

HAVING MIN (HP)

BRAND

< 120;

COLOR -----

-------

COUNT(*) --------

Fiat

red

1

Audi

blue

1

Skoda

red

2

Rena.ult

red

2

Figure 171: HAVING

)>

What is the number of cars per brand, which are black or red?

)>

Rename the Brand column to "Manufacturer"

)>

Display only those manufacturers with at least 2 cars

)>

Sort the result set first descending by number of cars

1>

Sort the result set second ascending by manufacturer. SELECT c.Brand AS

11Manufacturer",

COUNT(*)

FROM Car c WHERE Col.or IN

( 'bl.ack' ,

'red' )

GROUP BY Brand HAVING COUNT(*) ORDER BY 2 DESC,

>= 2 Brand ASC;

Manufacturer

COUNT(*)

Skoda

3

vw

3

Renault

2

Figure 172: SELECT statement


76


2014




HA150

Exercise 2: Exercise 2 Exercise Objectives After completing this exercise, you will be able to: •

perform aggregations an a database table

•

use groupings and filter groups using the HAVfNG clause

Business Example Task: I.

What is the number of ernployees?

2.

Ho\V many employees have exactly 20 remaining vacation days?

3.

In how many dife f rent departments do the employees work?

4.

What is the maximum number of remaining vacation days?

5.

What is the average number of remaining vacation days? Employees whose remaining vacation days are unkno\vn should not be taken into account.

6.

Ho\\r many remaining vacation days have the employees accumulated in total?

7.

The minimum, average, maximum, and total number of remaining vacation days should be displayed with a single query.

8.

What is the deviation of maximum remaining vacation days to the average of re1naining vacation days?

9.

The total number of remaining vacation days should be displayed for each department.

1.0.

The total number of remaining vacation days should be displayed for each department. Only departn1ents with more than 30 days should be taken into account. The display of the total number of days should be sorted descending.

2014


77 �




HA150

Solution 2: Exercise 2 Task: l.

What is the number of employees? a)

2.

How many employees have exactly 20 remaining vacation days? a)

3.

=

20;

SELECT COUNT(DISTINCT DepID) FROM Employee;

What is the 1naximum number of remaining vacation days? a)

5.

SELECT COUNT(*) FROM Employee WHERE RemainderDays

In ho\V many different departments do the employees work? a)

4.

SELECT COUNT(*) FROM Employee;

SELECT MAX(RemainderDays) FROM Employee;

What is the average nu1nber of remaining vacation days? Employees \Vhose remaining vacation days are unknown should not be taken into account. a)

6.

SELECT AVG(RemainderDays) FROM E1nployee;

How many remaining vacation days have the employees accu.mulated in total? a)

7.

SELECT SUM(RemainderDays) FROM E1nployee;

The minimum, average, maximum, and total number of remaining vacation days should be displayed with a single query. a)

SELECT MTN(RemainderDays) AS "min", AVG(RemainderDays) AS "avg", MAX(RemainderDays) AS "max", SUM(RemainderDays) AS "sum" FROM Employee;

8.

What is the deviation of maximum re1naining vacation days to the average of remaining vacation days? a)

SELECT MAX(RemainderDays) -AVG(RemainderDays) FROM Employee;

9.

The total number of remaining vacation days should be displayed for each department. a)

SELECT DepID, SUM(RemainderDays) FROM Employee GROUP BY DepID;

10.

The total number of remaining vacation days should be displayed for each depart1nent. Only departments with more than 30 days should be taken into account. The display of the total number of days should be sorted descending. a)

SELECT DepJD, SUM(RemainderDays) FROM Employee GROUP BY DepID HAVING SUM(RemainderDays) > 30 ORDER BY 2 DESC;

�

78


2014




HA150

Lesson Summary You should now be able to: •

Detennine aggregated values on table columns using a single SELECT statement.

•


•

Detennine such aggregated values for groups of rows, using the GROUP BY clause.

•

2014



79 �



Unit Summary

HA150

Unit Summary You should now be able to: •

Determine aggregated values on table cohunns using a single SELECT statement.

•


•

Determine such aggregated values for groups of ro,vs, using the GROUP BY clause.

•

�



80


2014







•

n1 Reading Data From Multiple Tables Part I Unit Overview Unit Objectives After completing this unit you will be able to: ,

•

Merge the result of several se le ct statements using the UNION statement.

•

Combine data from several tables when querying data using JOIN constructs.

•

List the various types of Joins.

•

Explain the differences behvcen the various types of Joins, and apply the right type of Join depending on the pr obl em

.

Unit Contents Lesson: Reading Data from Multiple Tables- Part 1 Exercise 3: Exercise 3

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

82

105

81 �



Unit 4: Reading Data From Multiple Tables Part I

Lesson:

HA150

Reading Data from Multiple Tables- Part 1

Lesson Overview This lesson covers how data can be retrieved and combined from several tables in a single statement, using UNIONs and JOINs.

Lesson Objectives After completing this lesson, you will be able to: •

Merge the result of several select statements using the UNION state1nent.

•

Co1nbine data from several tables \Vhen querying data using JOIN constn1cts.

•


•

Explain the differences between the various types of Joins, and apply the right type of Join depending on the problem.

Business Example

How can I read data from multiple tables and views? Figure 173: Access to multiple tables

To read data fro1n multiple database tables or views the followi11g options are available: •

Combination of results from several queries UNION ALL vs. UNION INTERSECT EXCEPT

•

Combination of several tables (JOIN) CROSS JOIN vs. INNER JOIN vs. OUTER JOIN Implicit JOIN vs. Explicit JOIN

•

Nested queries (Sub Query) Uncorrelated Sub Query vs. Correlated Sub Query

�

82


2014



Lesson: Reading Data from Multiple Tables- Part 1

HA150

Combination of results from several partial queries

Figure 174: UNION

[ALL]

SELECT Column,

Column,

Column

FROM Table WHERE

Condition

UNION ALL SELECT Column,

Column,

Column

FROM Table WHERE Condition; Figure 175: UNION

[ALL]

You can combine the result tables of multiple queries using UNION

[ALL].

+The individual result tables must have the same number of columns. +The corresponding result columns must have compatible data types. +The column names of the resulting output table are based on the first SELECT statemenl SELECT

PNr,

Name

FROM Official WHERE Salary = 'A09' UNION ALL SELECT OwnerID,

Name

FROM Owner

WHERE Birthday>= Figure 176: UNION

2014

11977-05-21';

PNR

NAME

POl

Mr A

P03

Ms C

P06

Mr F

HOS

Mr x

H09

Ms Y

HlO

Mr z

[ALL]



83 �




:.>

HA150

Almost the same statement as on the previous slide - but with a changed sequence of partial queries.

•

The column names of the resulting output table are based on the fi rst SELECT statement.

SELECT OwnerID,

OWNERID

Name

-------

FROM Owner WHERE Birthday>=

'1977-05-21'

UNION ALL SELECT PNr,

Name

FROM Officia1 WHERE Sa1ary =

NAME

'A09';

HOB

Mr X

H09

Ms Y

HlO

Mr Z

POl

Mr A

P03

Ms c

P06

Mr F

Figure 177: UNION [ALL] With UNION [ALL] you can also explicitly rename the result columns. SELECT OwnerID AS

"Person ID",

Person

Name

'1977-05-21'


Name

FROM

Officia1

WHERE

Sa1ary =

NAME

---------

FROM Owner

WHERE Birthday>=

ID

'A09';

HOB

Mr x

H09

Ms Y

HlO

Mr z

POl

Mr A

P03

Ms c

P06

Mr F

Figure 178: UNION [ALL] If the results of multiple partial queries overlap, the overall result includes duplicates with UNION ALL. SELECT P1ateNumber,

Brand,

Co1or

FROM Car WHERE Brand= 'Mercedes• UNION ALL SELECT PlateNumber,

Brand,

Co1or

FROM Car

l'LATENUMBER

WHERE Co1or = 'white 1 ;

----------�lM { KD-AL 1002

Duplicate s

BRAND

COLOR

--------

Mercede"

White

3206

MercedeG

l>lack

HD-MM 1977

Mercedes

white

HD-AL 1002

Mercedes

white

KD-l·lM 1977

!*%cede"

white

KD-

}-

Figure 179: UNION ALL


84


2014



Lesson: Reading Data

HA150

You use

UNION

instead of

UNION ALL to ellmlnate

SELECT PlateNumber,

Brand,

from Multiple Tables- Part 1

dupllcates.

Color

FROM Car WHERE Brand= 'Mercedes' UNION SELECT PlateNumber,

Brand,

Color


'white';

=

PLATENUMBER --------- --

Figure 180:

BRAND --------

COLOR - - --

-

HD-AL 1002

Morcedos

white

HD-MM 3206

Mercedes

blaclt

HD-MM 1977

Morcedos

white

UNION

INTERSECT returns records that exist in a l l query results. SELECT PlateNumber,

Brand,

Color


=

'Mercedes'

INTERSECT SELECT PlateNumber,

Brand,

Color

FROM Car WHERE

Figure 181:

Color

=

' white ' ;

PLAX!NUMl!ZR

BRAND

COLO!t

HD-AL 1002

Mercedes

white

HD-MM 1977

Mercedes

white

INTERSECT [DISTINCT]

EXCEPT returns results from the first query which

are

NOT avallable In

subsequent queries. SELECT PlateNumber,

Brand,

Color

FROM Car WHERE

Brand

=

'Mercedes'

EXCEPT SELECT PlateNumber,

Brand,

Color

FROM Car WHERE

Color

=

'

white' ;

KD-M-f 3206

Figure 182:

2014

Mercedes

black

EXCEPT [DISTINCT]


85 �




HA150

You can use UNION (ALL] to combine result tables of multiple partlal queries. SELECT PlateNumber,

Brand,

Color

Brand,

Color

FROM Car WHERE Brand =

'BMW'

ONION SELECT PlateNumber, FROM Car WHERE Color

=

•yellow' PLATENUMBER

ONION SELECT PlateNumber,

Brand,


Figure

183: UNION

=

•orange' ;

Color

-----------

BRAND --

-

--

COLOR ------

HD-JA 1972

BMW

blue

HD-MT 507

BMW

black

HD-MM 208

BMW

green

!ID-VW 1999

Audi

yellow

HD-Y 333

Audi

orange

[ALL]

Joining tables Figure

184:

Joining Tables

With JOIN semantics you can distinguish between:

+CROSS JOIN +INNER JOIN +OUTER JOIN •

LEFT OUTER JOIN

•

RIGHT OUTER JOIN

•

FULL OUTER JOIN

With JOIN syntax you can distinguish between:

+Implicit JOIN +Explicit JOIN

Figure

185:

Joining Tables

CROSS JOIN (Cartesian Product) Figure

�

186:

Joining Tables


86


2014




HA150

• Each row of the left table is connected to each row of the right table. There Is no CROSS JOIN join condition.

•

R

x

L

y

::l-�-+�--f��t----i 3

1 2

4

3

5

4

6

5

7

Figure 187: CROSS JOIN

SELECT Column, FROM Table,

Column,

Column

Table

WHERE Condition;

SELECT Col11mn ,

Column,

FROM Table CROSS

Col umn

JOIN Table

WHERE Condition; Figure 188: CROSS JOIN Each row of a table is connected to each row of the other table.

J>The result set contains 10 • 20 = 200 rows SELECT * FROM Owner,

car;

OWNERIO -------

N�

KOl

Ms T

K02

Ms

KOJ

51>.P AG

K01

K02 K03

PLATENUMUR

red

75

red

75

red

15

H06 H06 H06

bl.ck black

120 120

HOJ H03

black

120

H03

blue

184 184

H03

blue blue

184

HD3

9raen

184

?

HO•V 106

r1•t Fiat riat

F02

HD-VW 4'711

w

F02

HO-VW ''711

F02

HO-VW 4'111

"" -

Wl.esloc:h

FOl

HD-V 106

Kockenhe1m

ro1

Ho-v 106

?

Wa.l.ldorf

ro1

><• T

1934-06-20

W.a.•tloeh

...

1966-0S-ll

Kockenha:im

?

Walldorf

SAP AG

OWNKR

COLOR

CAlll 0

0

H�

eRAllD

CITY ------- - --

0

&Il>.THDAY --------1934-06-20 1966-05-11

...........................

KOl H02

lb 'f ... 0

1934-06-20 1966-0S-ll

Wie•loc:b Hocke.J'\haun

ro3 F03

HO-JA 1972 HO-J/\ 1912

H03

SAP >t;

?

Wal.J.dort

F03

HO-J'A 1972

-

HlO

Mr z

1986-02-03

Ladeobu.rq

F20

?

M>
H03

Figure 189: Implicit CROSS JOIN

2014

© 2014 SAP AG or

an


87 �




HA150

Each row of a table is connected to each row of the other table. :.-The result set contains 10 • 20 = 200 rows SELECT

*

FROM owner CROSS JOrN car; OWNER.IO

N""°'

-- -- --H01

l
T

H02

Ms

u

HOJ

SAP AG

H01

Ms

T

M• U SAP AG

H02 HOJ

H01

BIRTHDAY

CITY

- - ---- - - - 1934-06-20 1966-05- 11 ?

- -- - - - --- -

CARID

PLATENUlCBER.

BIWID

- -- - -- - - - -

HP

CWNEQ

Wies o l ch

F01

HD-V 106

Fiat

red

75

Kockenhei.m

FOl

HO-V 106

Fiat

red

75

Wall.dorf

FOl

HO-V 106

Fiat

red

15

806 H06 H06

blac:k

120 12 0 120

HOJ HOJ H03 HO J HO J H03 ?

�11eeloc:h

F 02

HO-VW 4711

Hocke.nhei1t1

F02

HO-"v?1 4711

Wal1dorf

F02

1934-06-20 1966-CS-11 ? 1934-06-20

Wies loch

E'03

HO-JA 1972

Hockenheim

FOJ

Bl�• �· BMN

bl\le blue

U4 184 1 84

AUdi

green

184

Ho: ..v w •111

HOJ

Ms T Ms U SAP AG

?

�Jal.tdorf

F03

HD-JA 1972 HO-JA 1912

HlO

Mr z

19 8 6•02·03

Ladenbur9

r2o

?

H02

COLOR

1966-05- 11

VI<

"" ""

blac:k black bl1.1e

Figure 190: Explicit CROSS JOIN You can specify a WHERE clause for the Implicit and explicit SELECT

*

SELECT

FROM Owner,

FROM Owner CROSS JOrN Car

250;

HP

OWNERID

NAME

BlllTHllA't

-------

------

---------

--------

HOl

Mo T

1934-06-20

W.ie-.looh

F06

H02

Ms 0

1966·05-11

Kookenheim

F06

H03

SAP AG HllM AG

9 ?

F06

HD-VW 1999

F06

HD-VW 1999

Mr V Ms w

1 9S2-04-21

Wa.11.dorf Heidelberg Lei.men Wies-looh

F06

HD·VW 1999

F06

!11>-VW 1999

F06

llD·VW 1999

F06

H04 H06

H07

1957-06·01

JOIN.

*

WHERE

HOS

>

Car

C ROSS

WHERE HP > 250;

CITY

CAl\ID

l't.ATENUMBER

BRAND

COLOR

HP

OWN&!\

HD-VW 1999

Audi

HOS

Audi

260

HOS

Audi Alldi

yell.QW yellow

260

HD-VW 1999

yellaw yellow

260 2 60

HOS H OS

Audi Audi

yel.low yellow

260

ROS

260

H OS

yellow

260

aos

HD-VW 1999

AwU Audi

yellow

260

KOS

y&llow

260

KOS

yellow

260

KOS

-----------

?

H08

IICEA Mr x

1986-08-30

Wal1dorf Walldorf

H09

Ma y

1986-02-10

Sinltheia

F06

HD-VW l.999

Audi

HlO

Mr Z

1986-02-03

Ladenburg

F06

HD-VW 1999

Audi



88


2014




HA150

You can join a table to itself. , The result set contains 20 • 20 = 400 rows *

SELECT

SELECT

FROM Owner, Car

FROM Owner CROSS JOrN Car

, WHERE HP > 250· c.\RID

PUTENUMBJ!R ---- ---

-

-

BJWll)

*

WHERE HP > 250;

COLOR

OWNER

HP

CAJUD

-

PLAftNIMIER

BJWll)

-·---··-·-

COLOR

HP

OWIR

r
75

H06

bl.aok

120

R03

bl\I•

1..

ROJ

ro1

HD•V 106

Fi.at

red

75

H06

rOl

HD·V 10&

f'Ol

HD-V 106

Fiat

red

75

H06

F02

HD·'VW 4711

,... vw

ro1

KD•V 106

r1at

red

75

H06

ro3

HD•JA 1'?2

8l
HD•VW 47ll

vw

black

120

H03

ro1

HD•V 10&

75

H06

vw

black

120

803

ro2

HD·VW 4111

vw

r
4111

black

120

HOJ

black

120

803

F03

HD·.IA 19?2

BMW

blt.t•

184

803

ro2

riat.

ro2

HD VW

ro2

HD·VW 011

ro3

llD JA

1912

-

blue

184

H03

ro1

HD·V 106

riot

r
75

H06

ro3

HO•JA 1972

BMW

blue

1 84

H03

ro2

HD·VW 4711

vw

black

120

803

1'72

-

bl.ue

184

803

...03

HD·JA 1912

BMW

blue

184

H03

Audi

green

184

?

F20

?

Auch

qreen

184

?

·

·

103

HD JA

F20

?

·

vw

Figure 192: CROSS JOIN You can combine more than two tables. >The result set contains 10 • 20 • 3 = 600 rows

SELECT

SELECT

*

FROM Owner CROSS JOIN

FROM Owner,

ONll'.kll)

-... ... ...

' ' '

IOI

... ...

IOI

' '

I0 1

Car

Stolen;

Stolen;

ltltTMDAT

Cl'l'T

----

-

1934-06-20

Wies.loch

-

CMlD

fl.ATDWalA

FO i POI ...,,

coi.o•

..

-1111

SD-Y 101

Piat

11)--W 4711

""

bl•oll:

Wiesloch

W4-0&-20

Wi••loch

1'34-M-20

Wiesloch

M• t

ltl4...0C-20

•02 M02 M02

M• U M• U M• U

1''6-05-11

ffoc;:k•nhei.9

rot

�y 10, MD-V 10fi

MIO

... .

l,86-oi-03

Ladenbui:9

t20

'

llD-VM 4711

Wi••loch

P02 , .. ro2

111>-VW 4111

lt6i-05-11

Hookenh•i.a

POI

1,,,_0S-11

ro1

MD-Y JO'

ffocUnh•ia

•UTUCIMUA ------

-

ID-V 101 e-v 10•

lfi••loch

IOI

......

----

----

ltl4-0C.-20

IOI

CROSS JOIN

Car,

J.934-o&-20

IOI

*

Pi•t Piat

-

MX>-YW lff' NJ>..-y 10, KD-T "'

2012-CK-20 2:012-06-01 2012-os-21

Ko--YW 1"9 KD-Y 1°'

?O12""°'-20

,, ,, 120

H03 H03

vw

bl•ck

120

"°'

r••t

15

llCI

Pi•t.

.... -

u u

HOC M06

Audi

9l'••n

184

'

HD•Y

3.Jl

%012-06-01 2012•05.. 21

Hl)•V 106

HD-Y "'

2012-06-20 2'012-0,-0J 2012-os-21

HD-Y "3

2012-05-21

KO-Vlf 1ff'

-

-

""' HC•

120

rt•t

--

"

bl•clt

vw

UOllTP.ID_AT


�

INNER JOIN

Figure 194: Joining Tables

2014


89 �



Unit 4: Reading Data From Multiple Tables Part

HA150

I

One row of the left table and one row of the right table are always joined to a common result row - provided that the JOIN condition is fulfilled. > JOIN Condition: L. X = R. Y

I

L

.. .

x

...

I

R

...

y

...

.. .

� .. .

3

...

...

�

.. .

4

...

...

�

� .. .

5

...

...

...

�

...

6

...

. ..

...

,,,_

...

7

.. .

...

.

...

1

...

...

."

2

...

. ..

.. .

3

...

...

...

4

.. .

.. .

5

...

"

Figure 195: INNER JOIN SELECT Column,

FROM Table,

Column,

Column

Table

WHERE JOIN_Condition AND Supplementary_condition; SELECT Column, FROM Table

Column,

Column

JOIN Table ON JOIN Condition

WHERE Supplementary_Condition;

Figure 196: INNER JOIN One row of a table and one row of another table are always connected to a common result row - provided that the JOIN condition is fulfilled. •The JOIN condition is part of the WHERE clause >To whom which car is registered?

SELECT

*

FROM Owner, WHERE

Car

OwnerID

=

Owner;

OllNERll)

NAME

BIR'll!DAr

CITY

----- -

------

---- --- -

---- ---

f!06

Ms w

i957-06-0l

Wiesloch

B03

SJUI AG

?

Walldorf'

H03

SAP AG

?

H07

IKEA

H03

SAP AG

CllRID

J?LArENUMBER

BRlUlD

--- --- ---

-------

FOl

BD-V 106

Fi.at

F02

HD-vw nu

vw

Wa1ldorf

F03

HD-Jl>. 1972

?

Walldorf'

F04

?

Wa1ldorf

FOS

COLOR

BP

Ol'lllER

red

75

806

blac:lc

.J.20

B03

-

blue

184

H03

HD-AL 1002

Mercedes

vlU.te

l.36

R07

BD�I 3206

Mercedes

black

170

H03

Figure 197: Implicit INNER JOIN

�


90


2014




HA150

• One row of a table and one row of another table are always connected to a common result row - provided that the JOIN condition is fulfilled. • The JOIN condition is part of the JOIN operation (and not of the WHERE clause) ;.

To whom is which car registered?

SELECT

*

FROM Owner

NAME

INNER

JOIN

BI�Y

CITY

--------

------

OllNltlUD -- ----

-----

H06

M.t "

J.957-06-01

Car ON

CAAID

ffiea1och

OwnerID

PLATENUMBl!:R -----------

8IU\l'ID

- Owner;

COLOR

RP

ORNER

--------

£01

HD•V 106

Fiat

red

75

H06

vw

black:

120

H03

SAP AG

?

Wal.l.dorf

F02

HD-VW 4711

H03

SM' AG

?

WaJ.ldorf

F03

1m-JA 1972

81."1

blue

184

803

807

IKEA

?

WaJ.ldorf

F04

HD-AL 1002

Mercedes

white

136

807

H03

SAP AG

?

ffalldorf

F05

lW•l!IM 3206

144rcodoa

black:

170

803

803

Figure 198: Explicit INNER JOIN • You can simply write "JOIN" instead of "INNER JOIN". •

The INNER JOIN is the most important JOIN variant (and therefore the default)

;.

To whom is which car registered?

SELECT

*

FROM Owner JOIN

OIJNEIUD ------

Car ON OwnerID

ITTINE

BntnlllM

CrTT

- ---

------

----

CARD>

- Owner;

PLATDIUMBER

BRAND

-------- -

--------

COLOR

RP

OffNER H06

H06

... "

1957-06-01

lfi..loch

l!'Ol

HD·V 106

Fiat

r4ld

75

803

SAP AG

?

Walldorf

F02

HD-VW 4711

VII

black:

120

H03

803

SAP AG

?

Walldod

l!'03

HD-JA 1972

-

blue

184

803

807

IREA

?

Walldod

F04

HD-AL l.002

Mercedes

white

136

807

803

SM AG

?

ffalldorf:

F05

HD-MM 3206

Mercode•

black:

170

803

Figure 199: Explicit INNER JOIN You can specify certain

co l u mns in the projection

list also for the implicit

JOIN. �

:;To whom is which car registered?

SELECT Name, FROM Owner, WHERE

Brand,

Color

Car

OwnerID

- Owner;

BRAND --------

COLOR

Ho W

Fiat

red

SAP AG

""

b1ack

SAP AG

BMW

blue

IKEA

�reed.es

white

SAP AG

Me reed.es

black

"" v

Aueli

yeUow

SAP JIG

Audi

blue

Iia:A

vw

l>lack

HI U

Skoda

red

llDH AG

BMW

black

Ho U

BMW

i;reen

llDH AG

Skoda

red

IKEA

Renau1t

red

SAP�

Mercedes

llbite

&AP AG

Skoda

black

MS y

AUCU

orange

SAP 1113

Renault

red

MS T

vw

black


2014


91 �




HA150

You can specify certain columns In the projection list also for the explicit

JOIN. l>To whom is which car registered?

SELECT Name, FROM Owner

Color

Brand,

JOIN Car

ON OwnerID

-

Owner;

NllME

BRAAtl -------

COLOR

Ms w

fiat

red

SAP AG

vw

black

SAP AG

BMW

blue

ll
Merced••

whJ.t•

SAP AG

Mercedes

b1ack

Mr v

Aue11

y.llow

SAP Al3

Aud.i

blue

Da:A

vw

black

Ms u

Skoda

red

llDM AG

BMW

black

M• U

BMW

green

llDM AG

Skoda

red

IKEA

Renault

red

SAP 1'G

Mercedes

white

SAP AG

Skocla

black

Ms Y

Audi.

orange

SAP AG

Rena.ult

rttd

Ms T

vw

black

Figure 201: Explicit INNER JOIN

You can also use table aliases or tuple variables in the projection list for the implicit JOIN. +The JOIN condition can also refer to table aliases or tuple variables. +If column names are not unique, you must qualify them with the table aliases or tuple variables.

M• W

l>To whom is which car registered?

SELECT

o.Name,

FROM Owner WHERE

o,

c.Color

Car c =

AG SAP AG IKL� SAP AG Mr V SAP AG IKEA >!S 0 KDM AG ).fs U HOM AG Il!S y SAP AG SAP

c.Brand,

o.OwnerID

NAME

c.Owner;

W..

T

BRANO

COLOR

Fiat

red

Vt< BMW

black

--------

blue

l1ercedes

white

l.fercedes

black

Audi

yellow

Audi

blue

Vt<

black

Skoda

red

BMW lll
��vn

black

Skoda

rod

Renault

red

Merc:edes

whit.e

Skoda

black

Audi

orange

R•n•ult

r•d

vw

bl.ac�

Figure 202: Implicit INNER JOIN You can use table aliases or tuple variables in the projection list for the explicit JOIN. •The JOIN condition can also refer to table aliases or tuple variables. +If column names are not unique, you must qualify them with the table aliases or tuple variables. ;To whom is which car registered?

SELECT

o.Name,

FROM Owner ON

o JOIN Car =

c.Color c

c.Owner;

SRMO

COLOR

-------

Ms w

F;i.a.t

rod

51'.P AG:

Vt< BMW

bl.-c::k

AG IKE. to .. SAP AG Mr V SAP AG IKEA Ms 0 HOl& AG Ms 0 HOM AG IKEA SAP M SAP AG Ms Y SAP AG Ms T SAP

c.Brand,

o.OwnerID

NAME

blue

1-lercedes

wbit.e

l4ercede&

black

� .. udi

yellow

Audi

blue

Vt<

bla.c::k

skoda

red

BMW Bl«<

i;;r. een

black

Sko da

red

Renault

red

Merce�es

white

skoda

black

.. u di �

orange

R9nault

rod

..,..,

black



92


2014




HA150

You do not have to Include a column from every Involved table In the projection list +Besides the JOIN condition the WHERE clause can contain additional conditions. ;To whom is a black car registered (at least one)?

SELECT DISTINCT o.Narne FROM Owner o, WHERE

Car

o.OwnerID

AND

SAP AG

c.Owner

=

c.Color

Ms T

c

=

HOM AG

'black';

IKEA

Fi gure 204: Implicit INNER JOIN You do not need to include a column from every Involved table in the projection list. +In addition to the JOIN condition you can specify a WHERE clause. :..To whom is a black car registered (at least one)?

SELECT

DISTINCT

FROM Owner ON WHERE

o.Name

o JOIN

Car

o.OwnerID

c.Color

NAME

c

Ms T

c.owner

=

SAP AG

'black';

=

HDM AG IKEA

Figure 205: Explicit INNER JOIN You can explicitly rename result columns for the Implicit JOIN. +Sorting the result table is also possible ;.To whom is a black car registered (at least one)?

SELECT

DISTINCT o.Narne AS

FROM Owner WHERE

o,

Car

o.OwnerID

=

AND c.Color ORDER BY o.Name;

"Owners'

name"

c c.Owner

=

'black' owners•

name

HDM AG IKEA Ms T SAP AG


2014

© 2014 SAP AG or an SAP affiliate company. All right s reserved.

93 �



HA150


You can explicitly rename result columns for the explicit JOIN . +Sorting the result table is also possible J>To

whom is a black car registered (at least one)?

SELECT DISTINCT o.Name AS FROM Owner o

JOIN

ON o.OwnerID WHERE c.Color ORDER

"Owners'

n ame "

Car c =

c.Owner

1black1

=

BY o.Name;

OWners ·

name

HOM AG IKEA Ms T SAP AG

Figure 207: Explicit INNER JOIN You can add a GROUP BY clause for the implicit Join. J>How many black cars are registered to which owner?

SELECT o.Name,

COUNT(*)

FROM Owner o,

AS

11# black cars"

Car c

WHERE o.OwnerID

c.Owner AND c.Color

=

=

'black' GROUP BY o.OwnerID, ORDER BY 2

DESC,

o.Name

1 ASC;

NAME

# black cars

SAP AG

3

HOM AG

1

IKEA

1

Ms T

1

Figure 208: Implicit INNER JOIN You can add a GROUP BY clause for the explicit Join. J>How many black cars are registered to which owner?

SELECT o.Name, FROM Owner o

COUNT(*) JOIN

ON o.OwnerID WHERE

c.Color

=

GROUP BY o.OwnerID, ORDER BY 2

DESC,

AS

11# black

cars"

Car c =

c.Owner

1black1 o.Name

1 ASC;

NAME

# black cars

SAP AG

3

HOM AG

1

IKEA

l

Ms T

l


�


94


2014




HA150

You can build the JOIN condition on multiple columns for the Implicit JOIN. ::;..To whom within the EU is a black car registered (at least one)?

SELECT DISTINCT

o.Name A S "Owners'

FROM Owner_EU o, WHERE o.Country

=

Car_EU c c.Country

AND o.OwnerID AND c.Color

name"

=

=

c.Owner

'black'

Owners,

ORDER BY o.Name;

name

HDM AG IKEA Ms 0 Ms T SAP AG Senora R

Figure 210: Implicit INNER JOIN You can build the JOIN condition on multiple columns for the exp l icit JOIN. :;,.To

whom within the EU is a black car registered (at least one)?

SEL.ECT

o.Name AS

DISTINCT

"Owners'

name"

FROM Owner-EU o JOIN Car-EU c ON o.Country

=

c.Country

AND o.OwnerID WHERE c.Color ORDER BY

=

=

c.Owner

'black'

Owners1

o.Name;

name

HDM AG IKEA Ms 0 Ms T SAP AG Senora R

Figure 211: Explicit INNER JOIN You can join a table to itself. ;.Who is the manager of which employee?

SELECT e.Narne AS Employee, FROM Official e, WHERE a.Manager

=

m.Name AS Manager

Official m m.PNr;

IEMPLOnl:

Ml\NAGl!.R

--------

-------

Mr A

MS 0

Mr B

Ms 0

c

Ms D

HS 0

Hr I

Mr I!:

Ms H

Mr r

Ma H

Ma G

Ma H

Ms H

Hr I

Ms


2014


95 �



HA150


You can join a table to itself also for the explicit JOIN.

:.Who is the manager of which employee? SELECT e.Name AS Employee,

m.Name AS

Manager

FROM Official e JOIN Official m ON e.Manager

m.PNr;

=

E.MPLOll'EE

MANAGER

--------

-------

Hr A

MS D

Hr B

MS D

Ms c

Ms I>

Ms I>

Mr I

Mr E

MS H

l'lr F

MS H

Ms G

Ms H

Ms H

M:r I

Figure 213: Explicit INNER JOIN You can combine more than two tables. >To whom is which stolen car registered?

SELECT o.Name,

a.Brand,

FROM Owner o, WHERE

o.OwnerID

Car c, =

a.Color,

c.PlateNumber

Stolen s

c.Owner

AND c.PlateNumber NAME

s.PlateNumber;

=

BRAND

COLOR

PLATENUMBER

-----

------

-----------

Ms w

Fiat

red

HD-V 106

Mr v

Audi

yellow

HD-VW 1999

Ms y

Audi

orange

HD-Y 333

Figure 214: Implicit INNER JOIN You can combine more than two tables also for the explicit JOIN. ;.To whom is which stolen car registered?

SELECT

o.Name,

c.Brand,

c.Color,

c.PlateNurnber

FROM owner o JOIN

Car c ON o.OwnerID

JOIN

stolen s ON

=

c.Owner

c.PlateNumber

NAME

=

s.PlateNurnber;

BRAND

COLOR

PLATENUMBER

-----

------

-----------

Ms w

Fiat

red

HD-V 106

Mr v

Audi

yellow

HD-VW 1999

Ms y

Audi

orange

HD-Y 333


�


96


2014




HA150

older

younqer

EQUAL in the JOIN condition.

Ms T

Ma u

IU T

Nr v

IU T

Ma If

)Which owner is older t han which other owner?

IU T

Nr x

IU T

Ma y

Ms T

Mr z

IU U

Nr x

Ka u

Ma y

Ma u

Mr z

Mr v

Mau

Mr v

Ms w

Mr v

Mr x

You can use different comparison operators other than

SELECT o N ame .

AS

"older",

---

y.Name AS

"younger"

FROM Owner o JOIN Owner y ON o.Birthday < y.Birthday;

Mr v

Ms 'i

Mr v

Mr z

Ms w

Ms u

lU w

Mr x

Ms \ii

Ms 'i

Ms w

Mr z

Ms y

Mr x

Mr z

Mr x

Mr z

Ms 'i

Figure 216: Explicit INNER JOIN You can use calculations in the JOIN condition. :i.Which car has at least three times the power than

SELECT m.*,

which ot he r

car?

l.* ON m.HP >= l.HP * 3;

FROM Car m JOIN Car 1

CAJlID

PLATEb."UMBP

BRAND

COLOR

HP

a.ntta

CAR.ID

PLATENUMB&R

SRAm

COLOR

ffP

OtiNE&.

F06

HD-VW 1999

Audi

ye:1.low

260

HOS

F01

HD-V 106

Fat

red

75

H06

Figure 217: Exp li cit INNER JOIN You can use functions in t he JOIN condition. ::-Which owner was born in the same year as which other owner?

SELECT

ol.Name,

FROM owner

ON

o2.N ame ,

ol.Birthday,

ol JOIN Owner

o2.Birthday

o2

YEAR{ol.Birthday)

=

YEAR(o2.Birthday)

AND ol.OwnerID < o2.0wnerID; NAME

BIRTHDAY

- ------

----

----------

Mr x

1986-08-30

Ms y

1986-02-10

Mr x

1986-08-30

Mr z

1986-02-03

Ms y

1986-02-10

Mr z

1986-02-03

NAME ----

BIRTHDAY --

-


2014


97 �




HA150

OUTER JOIN

Figure 219: Joining Tables

The OUTER JOIN has three sub types: •

LEFT OUTER JOIN

•

RIGHT OUTER JOIN

+

FULL OUTER JOIN

For all three sub types of the OUTER JOIN SAP HANA only provides the

•

explicit syntax variant.

Figure 220: OUTER JOIN

•

One row of a table and one row of another table are always connected to a common result row - provided the JOIN condition is fulfilled.

•

In addition, rows of the left table without matching row in the right table are copied to the query result. The missing values {from the right table) are filled with

NULL values.

I

L

. . .

. . .

. .

. .

. .

..

.

. . .

x

. . .

1

.

2

.

3 4 5

. .

. .

. . .

. .

.

.

..

�

�

I

_J NUL ---1 I. R

...

. . .

. . .

.

. .

. .

.

..

. .

I. y 3

I.

I.

...

...

. . .

. .

.

.

4

.

. .

5

.

.

6

.

7

. .

.

. .

.

. . .

NUL NUL NUL

.

.

. .

. .

. .

.

. .

.

. .

.

.

.

.. .

.

.. ..

Figure 221: LEFT OUTER JOIN

�


98


2014




HA150

• One row of a table and one row of another table are always connected to a common result row - provided that the JOIN condition is fulfilled. •

In addition, rows of the right table without matching row in the left table are copied to the query result. The missing values (from the left table) are filled with NULL values.

IL

x

. ..

. ..

1

..

.

2

. .

.

3

. .

.

4

. .

5

.

...

. ..

. .

...

. . .

..

.

. .

.

...

. . .

IR

.

...

..

..

.

3

..

.

4

..

.

5

. .

6

. .

7

.

.. .

..

. .

NUL NUL NUL NUL L

L

L

y

.

L

. ..

...

. .

.

...

..

.

.

..

...

...

.

.. .

..

. . .

..

.

Figure 222: RIGHT OUTER JOIN

•

One row of a table and one row of another table are always connected to a common result

•

row

- provided that the JOIN condition is fulfilled.

In addition, rows of both tables without matching records are copied to the query result. The missing values (from the other table) are filled with NULL values.

IL

...

. . .

. . .

x 1 2

. .

3

.

4

. ..

5

.

. .

...

.

..

.

.

.

..

.

. ..

.

-::::::=-t

.

..

.

. �

...

L

IR

...

y

. ..

3

..

4

. ..

5

. .

6

..

7

. . . .

. .

.

. ..

...

NUL NUL NUL NUL L

1NUL NUL NUL NUL L L L L

L

L

:

.

.

...

.

..

..

.

.

..

. ..

..

.

.

.

.

..

.

. .

.

.

. .

.

.

..

..

Figure 223: FULL OUTER JOIN

2014

© 2014 SAP AG

or an


99 �




SELECT Column,

HA150

Column,

Column

FROM Table LEFT OUTER JOIN Table ON JOIN Condition WHERE Additional_Condition;

SELECT Column,

Column,

Column

FROM Table RIGHT OUTER JOIN Table ON JOIN Condition WHERE Additional_Condition;

SELECT Column,

Column,

Column

FROM Table FULL OUTER JOIN Table ON JOIN Condition WHERE Additional_Condition;

Figure 224: OUTER JOIN NAUE

>

Which car is registered to which individual or company?

,.

Individuals and companies that currently do not have a car Cars without a registration should not be included in the result.

SELECT

Name,

CarID,

FROM Owner LEFT ON OwnerID

Brand

OUTER

JOIN Car

Owner;

=

BRJUID --------

registered should be included. >

CARID

Vl'I

Ms T

F19

Ms U

F09

Skoda

Ms U

Fll

BMA

SAP AG

F02

Vlf

SAP AG

F03

Bl!IN

SAP AG

Fl.8

Renau.1t

SAP AG

FOS

Mercedes

SAP AG

Fl.5

Skoda

SAP AG

F07

Audi Mercedes

SAP AG

F14

HDM AG

F12

Skoda

HDM AG

FlO

Bl•M

Mr v

FOG

Audi

Ms w

FOl

Fiat

IKEA

Fi3

Renau1t

IKEA

FOB

vn

IKEA

F04

Mercedes

�MrX Ms y �Mr Z

?

?

F17

Audi

?

?


>

Which car is registered to which individual or company?

r

Cars without a registration should be included.

;;.

Individuals and companies that cu rren t ly do not have a car

SAP AG

F03

l!Mfl

registered should not be included in the result.

IKEA

F04

l.fercedes

SELECT

Name,

FROM Owner

CarID, RIGHT

ON OwnerID

Brand OUTER

-

JOIN Car

Owner;

Ms l'I

FOl

Fiat

SAP AG

F02

VFI

SAi? AG

F05

UerCQdes

Mr V

F06

Audi

SAi? AG

FO?

Audi

IKEA

FOB

V1il

Ms U

F09

Skoda

!IDM AG

FlO

l!Mfl

Ms IJ

Fll

filM

!IDM AG

F12

Skoda

IKEA

F13

Renault

SAi? AG

F14

Marcede.s

SAi? AG

F15

Skoda

F16

op<>l

�? Ms Y

Fl?

Audi

SAP AG

FlS

Renault

Ms T

F19

VFI

F20

Audi

!).?



100


2014



HA150


'

Which car is re gistered to which individual or company?

:;

Individuals and companies that currently do not have a car registered should be Included.

1111 w

roi

U•t

SM NJ

ro2

vw

aAl' AfJ

ro� ro4

>f.c.rc-eda•

titU SA� AD

,. Cars without a registration should be included In the result.

SELECT Name, FROM Owner

CarID,

FULL OUTER

ON OwnerID

=

ro6

IIOIA

roB roll

Ms u !IIlM AD

Brand JOIN

Car

ros

11r v S."1' MJ

M9 ll

ro?

Mere.�& A!ldl. A>.ldi YW Skod.�

no

BMW

rn

BMW

1lllM An

n2

sltoda

IKEA

T13

lb!Iauit

GAP AG

1'14

MarCii!idea

MP NJ �?

FlS rl.6

atod.a Oplil

llfl y

1'17

Aud.l

6Al1 AG

1'18

1tO T

l'l9 170

Owner;

�?

�Mr

x

?

�IQ- i

?

v w


For the LEFT OUTER JOIN you can also add a WHERE clause in addition to the JOIN condition.

Ko T

ru

,Which car is registered to which individual?

Ko U Mt u

f09 n1

w Sl<,oda flMW I

:;Assume that companies don't have a birthday. )Individuals that currently do not have a car registered should

be Included. ;Cars without a registration should not be included In the result. I

SELECT Name, FROM Owner ON

Carro,

Brand

LEFT OUTER

JOIN

<

"" v

ro&

.r.u.i...

Ito W

FOl

h•"

Car

F

ownerID - Owner � Mr JC

WHERE Birthday IS NOT NULL;

11a ¥

�Mr i

�

•

rn

Md.I

1

?


-

For the RIGHT OUTER JOIN you can also add a WHERE claus e in addition to the JOIN condition.

POl

l'L>t -w

I-Which car is reg istered to which individual?

-

>Individuals Who currently do not have a car registered should

be excluded. (Realized by RIGHT OUTER JOIN)

v

106

llt U

lOO

Kr

>Cars without a registration should not be Included In the result. (ReaIIzed by the WHERE clause)

Sluxl.o

l

SELECT Name,

FROM Owner

CarID,

Fll

Brand

RIGHT OUTER

-

J'l

JOIN

ON OwnerID - Owner

WHERE Birthday IS NOT NUL.L;

lia:t.

Car -

FU

&AP

n•

...,_.

"" y

' .Pli

>.udl


2014


101

&.




HA150

For the FULL OUTER JOIN you can also add a WHERE clause

in addition to the JOIN condition.

Ms W

FOl

P'J..at

. F06

Audi

>Which car is registered to which individual? >Individuals that currently do not have a car registered should

'

be included.

Hr V

>Cars without a registration should not be Included in the result. (Realized by the WHERE clause)

SELECT Name,

CarID,

.

Brand

.,

._-Ki-.

M• U

FOP

� Ii

'0

M• U

Fll

"

Sltoda �

BMW

<

"'

•

FROM Owner FULL OUTER JOIN Car

I

SAP A(;,.-Fl4

"

ON OwnerID - Owner

.YW

�

WHERE Birthday IS NOT NULL;

Hercedo=;"i

tr; ,.

1 '

Ms Y

F17

Audi

Ms T

Fl9

VW


You can LEFT OUTER JOIN a table with itself

EMPLOYEE --------

MANAGER -------

>Who is the manager of which employee?

Mr Mr Ms Ms Mr Mr Ma

Ms l!S Ms Mr Ms Ma Ms Mr

>Employees without a manager should be included. )-Only Managers with employees should be included.

A B c D E F G

Ms H

�Mr I

SELECT e.Name AS Employee,

=

?

m.Name AS Manager

FROM Official e LEFT OUTER JOIN Official ON e.Manager

D D D l H H H I

m

m.PNr;


You can RIGHT OUTER JOIN a table with Itself >Who is the manager of which employee? >The MANAGER column should also include officials that are not managing employees. >The EMPLOYEE column should only include officials with a manager assigned.

SELECT e.Name AS m .Name AS

t:MPI.OYEE ---.. ----

IWVIG&ll -------

*?

Hr A

....?

Mr B

�?

Ms c

Mr A

Ms D

Hr B

Ms D

Ms c

Ms D

*?

Hr I!

....?

Hr F

�?

Ms G

Employee,

Hr I:

Ms H

Mr !'

Ms H

Manager

Ms a

Ms H

FROM Official e

MB D

Hr 1

Ms H

Mr I

RIGHT OUTER JOIN Official m ON e.Manager

=

m.PNr;



102


2014




HA150

EMPLOYEE

You can FULL OUTER JOIN a table with itself

;.Who is the manager of which employee? ;..The EMPLOYEE column should include officials without a manager. ;..The MANAGER column should also include officials

that are not managing employees. SELECT e.Name AS Employee, rn.Name AS Manager FROM Official e

--------

Mr Mr Ms Ma Mr Mr Mll Ms �Mr �? .... . �· �? �? ....?

A B c D E F G B I

MAllAGER -------

Ms Ms Ms Mr M5 M5 M5 Mr ?

D !) !) I B B !! I

Mr A Mr B Ms c Mr E Mr F Ms G

FULL OUTER JOIN Official m ON e.Manager - m.PNr; Figure 233: FULL OUTER JOIN

You can also ... •

and:

Project to certain columns

• Reference table aliases or tuple variables in the projection list. + Use table aliases or tuple

variables in the JOIN condition. • Explicitly rename result columns.

You have to qualify column names with the table alias or with a tuple variable if the column names are not unique. You do not need to include a column from every table involved in the JOIN into the projection list.

• Sort the query result. + Add a GROUP BY clause.

• Use aggregate expressions. • e liminate duplicates by using

DISTINCT.

• Specify a JOIN condition on multiple columns. Use any comparison operator in the JOIN condition. JOIN a table with itself. JOIN more than two tables.

Figure 234: LEFT/RIGHT/FULL OUTER JOIN

2014


103 �




HA150


104


2014




HA150


retrieve data from several a t bles in a single select statement, using UNTONs or JOINs

Business Example Task: l.

Which employees work for department "AO l" or A04"? Use UNION.

2.

Which employees work for which department? Use an implicit JOIN without tuple variables. Employees without department and departments without employees should not be taken into account.

3.

Which employees work for which department? Use an implicit JOIN with tuple variables. Employees without department and departments without employees should not be taken into account.

4.

Which employees 'vork for which department? Use an explicit JOIN \Vithout tuple variables. Employees \vithout department and departments without employees should not be taken into account.

5.

Which employees \vork for \vhicb department? Use an explicit JOIN with tuple variables. Employees without department and departments without employees should not be taken into account.

6.

Which en1ployees work for which department? Employees without department should be included, whereas departments without employees should be ignored. Use a LEFT OUTER JOIN with tuple variables.

7.

Which en1ployees work for which department? Employees without depart1nent should be ignored, whereas departments without employees should be included. Use a RIGHT OUTER JOIN with tuple variables.

8.

Which employees work for which department? Employees without department and departments without employees should be included. Use a FULL OUTER JOIN with tuple variables.

9.

Which employees \vork for tbe Sales department? Use an implicit Join with tuple variables.

10.

Which employees work for the Sales department? Use an explicit Join with tuple variables.


2014

© 2014 SAP AG or an SAP affil iate company. All rights reserved.

105 �




11.

HA150

Which department has accumulated how many remaining vacation days? Only colleagues born after 03 .02.1972 should be taken into account. The output of the number of days should be sorted descending. Only departments that accumulated more than 30 remaining vacation days should be included.


106


2014




HA150

Solution 3: Exercise 3 Task: I.

Which employees work for department "AOl" or A04"? Use UNION. a)

SELECT*FROM Employee WHERE Dep.ID ='AO!' UNION SELECT*FROM Employee WHERE DepID

2.

=

'A04';

Which employees ·work for \vhich department? Use an implicit JOIN without tuple variables. Ernployees \vithout department and departments without employees should not be taken into account. a)

SELECT Employee.Name, Department.Name FROM Employee, Department WHERE Employee.DepID

3.

=

Dcpartmcnt.DcpID;

Which employees work for which department? Use an implicit JOIN \Vith tuple variables. Employees without department and departments without employees should not be taken into account. a)

SELECT m.Name, a.Name FROM Employee m, Department a WHERE m.DeplD

4.

=

a.DeplD;

Which employees work for which department? Use an explicit JOIN without tuple variables. Employees without department and departments without employees should not be taken into account. a)

SELECT Employee.Name, Department.Name FROM Employee JOIN Department ON Employee.DeplD

5.

=

Department.DepID;

Which employees \vork for wbicb department? Use an explicit JOIN with tuple variables. Employees without department and departments without employees should not be taken into account. a)

SELECT m.Name, a.Name FROM Employee m JOIN Department a ON m.DepID

6.

=

a.DepID;

Which employees work for which department? Employees without department should be included, whereas departments without employees should be ignored. Use a LEFT OUTER JOIN with tuple variables. a)

SELECT m.Name, a.Name FROM Employee m LEFT OUTER JOIN Department a ON m.DeplD

7.

=

a.DepID;

Which employees work for which department? Employees without department should be ignored, \vhereas departments without employees should be included. Use a RIGHT OUTER JOIN with tuple variables. a)

SELECT m.Name, a.Name FROM Employee m RIGHT OUTER JOIN Department a ON m.DepID

=

a.DeplD;


2014


107 �




8.

HA150

Which employees work for which department? Employees without department and departments without employees should be included. Use a FULL OUTER JOIN with tuple variables. a)

SELECT m.Name, a.Name FROM Employee m FULL OUTER JOIN Departn1ent a ON 111.DeplD= a.DeplD;

9.

Which employees work for the Sales department? Use an implicit Join with tuple variables. a)

SELECT m.* FROM Employee m, Department a WHERE m.DeplD = a.DeplD AND a.Name= 'Sales';

10.

Which en1ployees \vork for the Sales department? Use an explicit Join with tuple variables. a)

SELECT m.*FROM Employee m JOIN Depart1nent a ON m.DepID= a.DeplD WHERE a.Name= 'Sales';

11.

Which department has accumulated how many remaining vacation days? Only colleagues born after 03.02.1972 should be taken into account. The output of the number of days should be sorted descending. Only departments that accumulated more than 30 remaining vacation days should be included. a)

SELECT a.Nan1e, SUM(m.RemainderDays) AS Depart1nentsVacation FROM Employee m JOIN Department a ON m.DeplD \VHERE m.DateOfBirth

>

=

a.DeplD

'1972-02-03' GROUP BY a.Name,

a.DepID HAVING SUM(m.RemainderDays)

>

30 ORDER BY

Departments Vacation DESC;

�


108


2014




HA150


Merge the result of several select statements using the UNION statement

•

Combine data from several tables when querying data using JOIN constructs.

•


•

Explain the differences between the various types of Joins, and apply the right type of Join depending on the problem.

2014

© 2014 SAP AG

or an


109 �



Unit Summary

HA150


Merge the result of several select staternents using the UNION statement.

•

Combine data from several a t bles when querying data using JOIN constructs .

•


•

Explain the dife f rences between the various types of Joins, and apply the right type of Join depending on the problem.


110


2014







•

n1 Reading Data From Multiple Tables Part II Unit Overview Unit Objectives After completing this unit, you will be able to: •

Use sub-queries to query data from

mu

l tip le tables in a single select

statement. •

Explain the difference benveen uncorrelated and correlated sub-queries.

Unit Contents Lesson: Reading Data from Multiple Tables- Part 2 Exercise 4: Exercise 4

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


112 125

111 �



Unit 5: Reading Data From Multiple Tables Part II

Lesson:

HA150

Reading Data from Multiple Tables- Part 2 Lesson Overview This lesson covers how sub-queries can be used to retrieve data from several tables in a single statement.


Use sub-queries to query data from multiple tables in a single select statement.

•

Explain the dife f rence bet\veen uncorrelated and correlated sub-queries.

Business Example

Nested Queries

Figure 235: Nested Queries •

In addition to UNION and JOIN, Nested Queries also provide the option of reading from multiple tables and views.

•

Nested Queries contain a so called "Sub Query".

What is a sub query?

Figure 236: Sub Query

�

112


2014




HA150

+ A sub query is a query (SELECT-FROM-WHERE statement) that is used in

another query (SELECT-FROM-WHERE statement).

+

The "other Query" - containing the sub query - Is called the "outer query".

+

In this context the Sub Query Is also referred to a s "Inner query".

SELECT PlateNumber,

Outer Query

Brand,

Color

FROM Car WHERE

:Wb Query

owner IN ( SELECT OwnerID FROM Owner

WHERE City = 'Wiesloch');


When is a sub query useful?

Figure 238: Sub Query A sub query is useful if ... •

It makes the SELECT statement more "readable"- for example, because the use of nested queries is the "obvious way"

•

The SELECT statement has a better perfo rmance - however, with a "perfect" optimizer, this should not be the case.

•

The formulation of the SELECT statement is not possible (or only extremely cumbersome) without a sub query - which can be particularly the case for aggregate expressions.

What is the difference between an uncorrelated and a correlated sub query?


2014



affili a

113 �




HA150

An uncorrelated sub query makes no reference to the outer query. SELECT

*

FROM Car WHERE

Owner

IN

(SELECT OwnerID FROM Owner WHERE City =

'Wiesioch');

A corr elated sub query refers to the outer query. SELECT

*

FROM Car c WHERE

EXISTS

(SELECT

*

FROM owner o WHERE

o.OwnerID = c.Owner AND

o.City =

'Wiesioch');


Uncorrelated sub queries


SELECT Column,

Column,

Column

FROM Table WHERE Column IN

(SELECT Column FROM Table WHERE Condition);

Figure 242: Uncorrelated Sub Query You can use IN, if the outer value should be included in the result set of the sub query. >Which cars are registered to an owner from Wiesloch? SELECT

*

FROM Car WHERE

Owner

IN

(SELECT OwnerID FROM Owner WHERE City=

'Wiesioch');

CARIO

PLATENUMBER

BRAND

COLOR

HP

FOl

HO-V 106

Fl9

HO-VW 2012

OWNER

Fiat

red

75

H06

VW

black

125

HOl

Figure 243: Uncorrelated Sub Query


114


2014




HA150

You can use= ANY, if the outer value should match any result value of the sub query. ;Which cars are registered to an owner from Wiesloch?

SELECT * FROM Car

WHERE Owner

-

ANY

(SELECT OwnerID

FROM owner City=

WHERE

CARID

PLATENUMBER

'Wiesloch'); BRAND

COLOR

HP

OWNER

FOl

HD-V 106

Fiat

red

75

H06

Fl9

HD-VW 2012

vw

black

125

HOl


"= ANY" is equivalent to "IN". So, where is the value of"= ANY"?


= ANY

is equivalent to IN,

but you can use ANY with other comparison operators: = ANY

The external value matches any value of the sub query.

< ANY

The external value is less than any valu e of the sub query.

<= ANY > ANY

The external value is less

or

equal than any value of the sub query.

The external value is greater than any value of the sub query. equal than any value of the sub query.

>= ANY

The external value is greater

<> ANY

The external value is different to any value of the sub query.

or


2014


115 �




HA150

You can use > ANY, if the external value should be greater than any value of the sub query. :;.. Which officials have more than the minimum overtime hours?

SELECT Name,

overtime

FROM Official WHERE Overtime > ANY

(SELECT overtime FROM Official) , NAME

OVERTIME

Ms c

20

F

18

Ms G

22

Mr


You can use > instead of > ANY, if the sub query results In only a single value. ) Which officials have more than th e minimum o ver time hours?

SELECT Name,

overtime

FROM Official WHERE Overtime >

(SELECT MIN (overtime) FROM Official) ; NAME

OVERTIME

Ms c

20

Mr F

18

Ms G

22


You can use < ALL, If the outer value should be less than all result values of the sub query (other comparison operators are possible)

=

ALL

< ALL <= ALL > ALL

The external value matches each result value of the sub query. (This option is q uite useful, as the sub query result can contai n duplicates. ) The

external value is less than all values of

The external value is less or equa l than

all

the sub query.

values of the sub query.

The external value is greater than all values of the sub query.

external

value is greater or

equal than all values

>= ALL

The

of the sub query.

<> ALL

The external value is different to all values of the sub query.



116


2014




HA150

You can use

<= AL.L,

if the outer value should be less or equal than all

result values of the sub query (other comparison operators are possible) :.>Which officials

have the fewest overtime hours?

SELECT Name,

overtime

FROM Official WHERE overtime <= ALL

(SELECT

Overtime

FROM Officia.l WHERE NAME

OVERTIME

Mr A

10

Mr B

10

Mr E

10

Overtime

IS NOT

NULL) ;


You can use <= instead of <= ALL, if the sub query results in only a single value. ;>Which officials have the fewest overtime hours? »For the example below = would be possible instead of <- as well. SELECT Name ,

overtime

FROM Official WHERE overtime

<=

(SELECT

MIN(Overtime)

FROM Official) ; NAME

OVERTIME --------

Mr A

10

Mr B

10

Mr E

10


2014

© 2014 SAP AG or an SAP affil iate company. All rights reserved.

117 �




HA150

You can use functions In a sub query. )>Which owners were born in the same year as the youngest owner?

SELECT * FROM owner WHERE

YEAR(Birthday)

- (SELECT MAX(YEAR(Birthday)) FROM Owner) ;

OWNERID

NAME

BIRTHDAY

CITY

-------

----

----------

---------

HOS

Mr x

1986-08-30

Wall.dorf

H09

Ms y

1986-02-10

Sinshei.m.

HlO

Mr z

1986-02-03

Ladenburg


J>

To whom is (at least) one blue car registered?

SELECT Name FROM Owner WHERE OwnerID IN

(SELECT Owner FROM Car WHERE Color -

'blue');

NAME

SAP AG

Figure 253: Uncorrelated Sub Query You can use multiple columns in the sub query. ,.To whom within the EU is (at least) one blue car registered? SELECT

Name

FROM owner EU WHERE (Country,

OwnerID)

IN (SELECT

Country,

Owner

FROM Car EU WHERE Color = 'blue');

NAME

SAP AG

Senor Q



118


2014




HA150

You can use the same table in both the FROM clause of the outer query and

in the FROM clause of the sub query. ;.What is the name of Mr A's manager?

SELECT Name

FROM Offic.ial WHERE PNr IN

(SELECT Manager

FROM Official WHERE Name =

'Mr A');

Ms D

Figure 255: Uncorrelated Sub Query The WHERE clause of a sub query can contain another sub query. >Who are the owners of stolen cars?

SELECT Name

FROM Owner WHERE OwnerID IN ( SELECT Owner FROM Car WHERE

PlateNumber IN ( SELECT PlateNumber FROM Stolen )

) ;

Mr v Ms w Ms y


You can combine JOIN and Sub Query. >To whom is the most powerful car per brand registered?

SELECT DISTINCT Brand,

Name

FROM Owner RIGHT OUTER JOIN Car ON OwnerID WHERE

(Brand,

=

Owner

BMND -

-

------

NAMli -

-

----

Audi

Ml: v

-

Ma u

riat

Ma w

Horcedea

SAP AG

Opel

?

Renault

IKEA

Skoda

SAP M

vw

IKEA

SAP AG

IN

HP)

( SELECT Brand,

MAX (HP)

FROM Car

GROUP BY Brand ) ORDER BY Brand ASC,

Name ASC;

Fi g u re 257: Uncorrelated Sub Query

2014


119 �




• A sub query is usually • But you can >

HA150

part of the WHERE clause.

also use a sub query

in the FROM clause.

Which blue cars have less than 120 HP?

SELECT FROM

* (SELECT PlateNumber,

HP AS Power

FROM Car WHERE Color = WHERE Power <

'blue')

120;

PLATENUMBER

POWER

HD-ML 3206

116

Figure 258: Uncorrelated Sub Query + A sub query is

usually part of the WHERE clause.

in the SELECT clause.

•

But you can also use a sub query

;..

What is the HP deviation of each car when compared to the most powerful yellow

car? SELECT CarID,

Brand,

(SELECT MAX (HP) FROM Car WHERE Color =

'yellow')

-

HP AS Deviation

FROM Car

ORDER BY 3 DESC;

CAn!O

BAAN'D

OJ!:Vl:A'r!ON

...._.._.._

-------- ..

FOl

Fiat

1&5

F18

Renaul.t

170

F09

Skoda

155

F06

Audi

0


Correlated sub queries


© 2014 SAP AG

120

or

an SAP affiliate company.


2014



Lesson: Reading Data from

HA150

SELECT Column,

Column,

Multiple

Tables- Part 2

Column

FROM Table Tuple-Variable

WHERE EXISTS

(SELECT

*

FROM Table

WHERE Condition); Figure 261: Correlated Sub Query •

A correlated sub query refers to the outer query.

+

Using

,.

Which vehicles are registered to an owner from Wiesloch?

EXISTS

you can check that the query result of the sub query is not empty.

SELECT * FROM Car c

WHERE EXISTS

(SELECT * FROM Owner o WHERE o.City =

'Wiesloch'

AND o.OwnerID = c.Owner);

CARID

PLATENUMBER

BRAND

COLOR

HP

OWNER

FOl

HD-V 106

Fiat

red

75

H06

Fl9

llD-VW 2012

VW

black

125

HOl

Figure 262: Correlated Sub Query Even with a correlated sub query, names

are

unique.

you can omit tuple variables

if the column

:;.What vehicles are registered to an owner from Wiesloch? SELECT * FROM Car WHERE EXISTS

(SELECT

*

FROM Owner WHERE

City=

CARID - --

FOl F19

-

'Wiesloch'

AND OwnerID = Owner);

PLATENUMBER --

------

--

llD-V 106 llD-VW 2012

-

BRAND

COLOR

-

-- - -

--

--

Fiat

vw

HP

OWNER -- - --

-

red

75

HOS

black

125

HOl

Figure 263: Correlated Sub Query

2014


121�



HA150

Using NOT EXI sTS you can check if the result of the sub query is em p� F02 F03

>-Which cars are not reported as stolen?

F04 FOS F01

SELECT Car

F08 F09

FROM Car c

WHERE NOT EXISTS

FlO Fll

(SELECT *

FROM Sto1en

F12

s

Fll F14

WHERE s.P1ateNumber -

FlS Fl6

c.P1ateNumber);

F18 F19 no

Figure 264: Correlated Sub Query You can use the same table in both the FROM clause of the outer query and the FROM clause of the correlated sub query. i>Which vehicle has the most HP?

SELECT

*

FROM Car

c

WHERE NOT EXISTS

(SELECT *

FROM Car c2 WHERE c2.HP

> cl.HP) i

CARm

PLATl!:NUMB!R !

BRAND

COLOR

HP

OWNER

F06

HD-VW 1999

Audi

yellow

260

HOS


Even when using a correlated sub query, you can for example use">= ALL" :;.How much horsepower has the most powerful car of each brand?

��

SELECT DISTINCT�� B r�an�� d �� .... ,,.,_ . ..._ FROM Car

cl

WHEBE cl.HP >= ALL

(SELECT c2.HP FROM Car

c2

BRAl!D

HP

Fiat

75

BMW

184

J.tarcedas

170

Audi

260

vw

160

Renault

136

Skoda

136

Opel

120

I

WHEBE c2.Brand = cl.Brand);



122


2014



HA150

The WHERE clause of a correlated sub query

NAME

can contain an additional correlated sub query.

Mr v Ms w

;.To whom (at least) one stolen car is registered?

Ms y

SELECT o.Name FROM Owner o WHERE EXISTS (

SELECT * FROM Car

c

WHERE c.Owner = o.OwnerID AND EXISTS

(

SELECT * FROM Stolen a

WHERE a.PlateNurnbor

=

c.PlateNumber )

) ;

Figure 267: Correlated Sub Query You can also use a correlated sub query in the SELECT clause.

>How much horsepower each car is lacking compared to the most powerful car of the same brand? SELECT CarID, Brand,

(SELECT FROM WHERE AS

MAX(HP) Car c2

l.Brand)

c2.Brand

-

HP

Difference C11P.ID

FROM Car

BRAND --------

Dll'f'2RENCE ----------

ro1

Fiat

0

1'02

vw

40

!'OS 1'04

BMW Mercedes

0 34

ros

Meroedes

0

F06

Aud.i.

0

1'07

A.ud.i

144

!'20

Audi

76

Figure 268: Correlated Sub Query Sub

Query

Using Nested Queries you also can:

2014

•

Restrict the projection list to

c ertain co l u mns

•

Explicitly rename the result columns

•

Sort the query result

•

Use grouping

•

Include aggregate expressions

•

Eliminate duplicates using

•

Involve the same table multiple times

•

Involve more than hvo tables

DISTINCT

© 2014 SAP AG or an SAP affi li ate company. All rights reserved.

123 �




HA150


124


2014




HA150


use sub-queries to retrieve data from several tables in a single statement


Which employees \Vork for the Sales department? Use an uncorrelated sub query.

2.

Which employees \vork for the Sales department? Use a correlated sub query.

3.

Which employees accumulated the most remaining vacation days? Use an uncorrelated sub query with "MAX".

4.

Which employees accumulated the most remaining vacation days? Use an uncorrelated sub query \vithout "MAX".

5.

2014

Which employees have above average remaining vacation days?


125 �




HA150

Solution 4: Exercise 4 Task: 1.

Which en1ployees \vork for the Sales departtnent? Use an uncorrelated sub query. a)

SELECT* FROM Etnployee \.VHERE DeplD =ANY(SELECT DeplD FROM Department WHERE Name='Sales');

2.

Which employees work for the Sales department? Use a correlated sub query. a)

SELECT 1n.* FROM Employee m WHERE EXISTS(SELECT* FROM Department a WHERE a.DepJD =m.DeplD AND a.Name= 'Sales');

3.

Which employees accumulated the 1nost remaining vacation days? Use

an

uncorrelated sub query with "MAX". a)

SELECT* FROM Employee WHERE RemainderDays =(SELECT MAX(RemainderDays) FROM Employee);

4.

Which employees accumulated the most remaining vacation days? Use an uncorrelated sub query without "MAX". a)

SELECT* FROM Employee WHERE RemainderDays >=ALL (SELECT RemainderDays FROM E1nployee WHERE RemainderDays IS NOT NULL);

5.

Which employees have above average remaining vacation days? a)

SELECT* FROM Employee WHERE Re1nainderDays >(SELECT AVG(RemainderDays) FROM Employee);

�


126


2014




HA150



•

2014

Explain the difference bel:\veen uncorrelated and correlated sub-queries.


127 �



Unit Summary

HA150



•

Explain the difference between uncorrelated and correlated sub-queries.


128


2014







•

n1 Understanding NULL Values Unit Overview Unit Objectives After completing this unit, you will be able to: •

Explain how to interpret NULL values in databases.

•

Understand \Vhy the presences ofNULL values can lead to unexpected query results.

•

Analyze if t\vo seemingly equivalent SQL statements return different results because of NULL values.

Unit Contents Lesson: Understanding NULL Values

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

130

129 �



Unit 6: Understanding NULL Values

HA150

Lesson: Understanding NULL Values

Lesson Overview This lesson explains what NULL values are and \vhy dealing with them correctly is important.


•

Explain ho\\1 to interpret NULL values in databases. Understand ·why the presences ofN-ULL values can lead to unexpected query results.

•

Analyze if two seemingly equivalent SQL statements return different results because of NULL values.

Business Example

What is a NULL value?

Figure 269: NULL Values A NULL value signals that the corresponding value is unkno\vn or does not exist. The NULL value is a special value reserved by th e DBMS. •

For numeric data types the NULL value is not equal to 0.

•

For string based data types the NULL value is not equal to an empty string or to the space character.

A NULL value is used, ... •

If the corresponding value exists in principle, but is (temporarily or permanently) unknown (such as a birthday of a person)

•

If the corresponding value does not exist (for example the owner of an unregistered car).

�

130


2014




HA150

How are NULL values created?

Figure 270: NULL Values NULL values are "created" \vhen reading rows (SELECT statement) •

If the source table already contains NULL values

•

When using outer joins

NULL values are created in a table when inserting rows (INSERT statement) if •

No value for a collunn is provided, NULL values are allowed and no default value is defined

•

A NULL value is explicitly specified for a column and NULL values are allo\ved

NULL values are created in a table when changing •

rows

(UPDATE statement) if

A NULL value is explicitly specified for a column and NULL values are allo\ved

NULL values are created in a table when adding a column (ALTER TABLE statement), if •

The table has at least one row, NULL values arc allowed for the added column and no default value is defined for the added column

NULL values enforce a trivalent logic

Figure 271: NULL Values

2014


131 �




HA150

•

By allowing NULL values a third truth value is necessary.

•

In addition to "true" and "false" there is also the truth value "unknown" in SQL.

• Any comparison with a NULL value results in the truth value "unknown". •

This applies to all comparison operators: =, >, >=, <, <=, <>

A

A=7

A<>7

A>=6

5

false

true

false

7

true

false

true

NULL

unknown

unknown

unknown


�

NOTX

x -

true

false

false

true

unknown

unknown

Figure 273: Trivalent Logic: NOT

�

x

y

X ANDY

true

true

true

true

false

false

true

unknown

unknown

false

true

false

false

false

false

false

unknown

false

unknown

true

unknown

unknown

false

false

unknown

unknown

unknown

Figure 274: Trivalent Logic: AND


132


2014




HA150

�

x

y

XOR Y

true

true

true

true

false

true

true

unknown

true

false

true

true

false

false

false

false

unknown

unknown

unknown

true

true

unknown

false

unknown

unknown

unknown

unknown

Figure 275: Trivalent Logic: OR

�

The trivalent logic has only one more truth value, so where is the problem?

Figure 276: Trivalent Logic

39

=

19,683 binary logical operators are conceivable

x

y

X op

true

true

true I false I unknown

true

false


true

unknown


false

true


false

false


false

unknown


unknown

true


unknown

false


unknown

unknown


Y

Figure 277: Trivalent Logic

2014


133 �




HA150

Many "obviously true" statements are no longer necessarily true!!! +A=A +(A < 5) OR ( A>= 5) +(A

= 'Walldorf') OR (A <>'Walldorf)

+A+ 1 >A +A• A>= 0 +0•A=0 +2• A

= A+ A

+MAXIMUM>= ALL (set of all values) +MINIMUM <=ALL (set of all values) +A OR (NOT A) Figure 278: NULLs

•

& Trivalent Logic

Do the following two SQL queries have the same result?

SELECT Name,

overtime

FROM Official

WHERE overtime <- 10 OR overtime > 10; • ••• •• . •• •.••• •• .·.· .•.•• ••• . · . • . ••• •••.•.• .•:• ••••.• •.• ••• •• ••• .••• ••••• ••••• ••.• •• ••• •• ••• .•• •••:•• •••.•• . • :.-, •.• .•.•• •• ••:.•.• •,•• .••• •• ••• •••.•.• .•:• •• •.•• •• ••• .•.•"N

SELECT Name,

.

.

. • •• ••• •• ••• •• ••.• ••:. • •: •• ••• ••:•• •• ••• ••••• .. •.•::•• ••

overtime

FROM Official;


•

;...

NAME

The two SQL queries have different results! O n ly officials where Overtime IS NOT

SELECT Name,

NULL

--------

applies:

overtime

FROM Official

WHERE overtime <

;...

Mr A

10

Mr B

10

Ms C

20

Mr E

10

Mr F

18

Ms G

22

ltAME

OVERTIME:

Mr A

10

Mr B

10

Ms C

20

Ms D

?

Mr E

10

Mr F

18

Ids G

22

?ifs H

?

Mr I

?

10 OR overtime > 10;

All offici a ls (including those with : Overtime IS

SELECT Name,

OVERTIME

overtime

FROM Official;

NULL

)

------



134


2014



HA150

•


SELECT *

FROM Official WHERE PNr <> ALL

(SELECT PNr

FROM Official WHERE overtime = overtime) ;

SELECT * FROM Official WHERE overtime Is NULL;

Figure 281: NULL Values • The two SQL queries have the same result! SELECT *

FROM Official WHERE PNr <> ALL

(SELECT PNr

FROM Official WHERE overtime = Overtime) ;

PNR

NMIE

OVERl'DIE -

SJILAll1' -----

WH?GER -----

-·-

SELECT

*

FROM Official

WHERE Overtime IS NULL;

--·-

POt

IU D

?

All

P09

P08

"" H

?

""

P09

P09

ICr I

?

AH

?


>

The sub query returns all personnel numbers of the officials, where the number of overtime hours is known.

PNR POl P02 P03 POS

SELECT *

P06

FROM Official

WHERE PNr <> ALL

(SELECT PNr

P07

FROM Official WHERE overtime = Overtime);


2014


135 �


HA150


The outer SELECT query returns all officials, whose PersNumber differs from all

;..

PersNumbers of the sub query. ,..

So the outer SELECT query returns all officials, whose PersNumber differs from the PersNumbers of those officials whose overtime is known.

,..

So the outer SELECT query returns all officials whose overtime is unknown.

SELECT * FROM Officia1

WHERE PNr <>ALL

(SELECT PNr FROM Officia1 WHERE

Overtime - Overtime);

PNR

NAME

OVERTIME

SAIJIRY

1>11\NAGER

--------

------

-------

P04

Ms D

?

A12

P09

P08

Ms H

?

A13

1?09

P09

Mr I

?

A14

?


•


SELECT Name,

Overtime

FROM Officia1 WHERE overtime >= ALL

(SELECT overtime FROM Officia1) ;

SELECT Name ,

Overtime

FROM Officia1 WHERE overtime

=

(SELECT

MAX (overtime)

FROM Officia1) ;


•

The two SQL queries have different results!

SELECT Name,

Overtime

The query resuit J.s empty.

FROM Officia1 WHERE overtime >= ALL

(SELECT overtime FROM Officia1) ;

SELECT Name ,

Overtime

FROM Off icia1 WHERE overtime

=

(SELECT

MAX (overtime)

Ms G

22

FROM Officia1) i



136


2014




HA150

+ The sub query also returns NULL valu es

10 10 20 ? 10 18 22 ? ?

.

SELECT Name , Overtime FROM Official

WHERE Overtime >= ALL (SELECT Overtime FROM Official)

Hence there is no value which is greater or equal to all sub query values!

Tile query :result is anpty.

SELECT Name, Overtime FROM Official WHERE

Overtime

ALL

>=

(SELECT OVertiUIA FROM OfficJ..tl) ;

Figure 287: NULL Values • Do the following two

SQL queries have the same result?

SELECT Name , Overtime FROM Official WHERE Overtime

>= ALL (SELECT Overtime FROM Official. WHERE Overtime

SELECT Name

f

IS NOT NULL) ;

Overtime

FROM Official. WHERE

Overtime

=

(SELECT

MAX (Overtime)

FROM Official) ;


+

For the existing data, both queries return the same result

SELECT Name, Overtime FROM Official WHERE

Overtime >= ALL

(SELECT Overtime

.

NAME

OVERTIME

Ma G

22

FROM Official.

WHERE Overtime IS NO T NULL) ;

SELECT Name, Overtime FROM Official WHERE Overtime

=

(SELECT

MAX (Overtime)

NAME

OVERTIME

Ma G

22

FROM Official) ;


2014


137 �




HA150

+ But: If the overtime values were unknown for all officials , would the two SQL queries still return the same results? Is tbe query .result eq;.ty

SELECT Name ,

Or

overtime

Does it contain all rows 0£

FROM Official

tha official tabla?

WHERE overtime >= ALL

(SELECT overtime FROM Official

WHERE overtime IS NOT NULL) ; SELECT Name,

The query result is empty.

overtime

FROM Official WHERE overtime

(SELECT MAX (overtime)

=

FROM Official) ;


+ The result of the sub query is empty. + Is the WHERE clause of the outer query fulfilled for all rows, or false? SELECT Name,

In SAP HANll.:

Overtime

The query resul.t is empty.

FROM Official WHERE

Overtime >= ALL

(SELECT Overtime FROM Official WHERE Overtime IS NO T NULL) ;

+ The sub query returns a

NULL value.

+ So the WHERE clause of the outer query is not fulfilled for any row. SELECT Name,

Overtime

The query result is empty.

FROM Official WHERE Overtime

=

(SELECT MAX(Overtime)

FROM Official); Figure 291: NULL Values


138


2014




HA150


Explain ho\V to interpret NULL values in databases.

•

Understand why the presences of NULL values can lead to unexpected query results.

•

Analyze if two seemingly equivalent

SQL statements return different results

because of NULL values.

2014


affiliate

company. All rights reserved.

139 �



Unit Summary

HA150


Explain how to interpret NULL values in databases .

•

Understand \vby the presences ofNULL values can lead to unexpected query results.

•

Analyze if two seemingly equivalent SQL statements return different results because of NULL values.

140


2014







•

n1 Changing Data Stored in Tables Unit Overview Unit Objectives After completing this unit, you will be able to: •

Add rows to database tables using SQL.

•

Change existing rows of a database table.

•

Remove existing

rO\VS

from a database table.

Unit Contents Lesson: Changing Data Stored in Tables Exercise 5: Exercise 5

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

142 153

141 �



Unit 7: Changing Data Stored in Tables

HA150

Lesson: Changing Data Stored in Tables Lesson Overview This lesson covers how you can add data to a database, change and delete data.



•


•

Remove existing rows from a database table.

Business Example

Which SQL statements can be used to insert, change or delete data?

Figure 292: Changing Database Access INSERT statement •

UPSERT statement

New rows are inserted into a

•

table

New rows are inserted into a table, existing table rows are changed

UPDATE statement •

Existing table rows are changed

REPLACE statement •

Same as UPSERT

DELETE statement •

Existing table rows are deleted

Figure 293: Changing Database Access

INSERT statement


�

142


2014



Lesson: Changing Data Stored in Tables

HA150

INSERT

INTO

Table VALUES

(Value,

INSERT

INTO Table(Column ,

Value,

Column)

Value) i

VALUES

(Value,

Value) ;

INSERT

INTO

Table SELECT

...

FROM ...

WHERE ... ,

Figure 295: INSERT Using INSERT you can specify a value for each column. +If the sequence of the values corresponds to the sequence of columns, you do not need to list the column names. +The order of the columns above mentioned is set Implicitly when you create the table. :.Insert a new row into the Official table: INSERT

INTO Official

VALUES

('Pl0',

'Ms

20,

J',

PNR

NAME

OVERTIME

SALARY

MANAGER

PlO

Ms

20

A10

P04

J

I P04 I )

'AlO',

i

Figure 296: INSERT Using INSERT you can specify a value for each column. +If the sequence of the values does not match the sequence of the corresponding columns, you must list the column names in the correct sequence of the values.

·1-1nsert a new row into the Official table: INSERT INTO Official ( Salary,

Overtime, Name, PNr,

Manager) VALUES

( 1 A12

1

,

so,

I Mr

K' ,

'Pll' ,

PNR

NAME

OVERTIME

SALARY

MANAGER

Pll

Mr K

50

A12

P09

1

P09 I) ;

Figure 297: INSERT

2014


143 �




HA150

You do not have to specify a value for each column of the INSERT.

+You must list the column names in you specify a value.

the correct sequence for each column, where

+Missing values will be replaced by NULL or the default value of the column >Insert

a

new row into the Official table:

INSERT

INTO Official(PNr,

Name)

VALUES

('P12',

'Ms

L');

PNR

NAME

OVERTIME

SALARY

MANAGER

P12

Ms L

?

A06

?

Figure 298: INSERT You can explicitly specify NULL values (for certain columns) for the INSERT. >Insert a new row into the Official table:

INSERT INTO Official VALUES

(" Pl3',

'Mr M' ,

NULL,

NULL,

NULL);

PNR

NAME

OVERTIME

SALARY

MANAGER

P13

Mr M

?

?

?

Figure 299: INSERT You can INSERT data read from another table. >INSERT all owner IDs and names from city Wiesloch into the Official table: INSERT

INTO Official(PNr,

Name)

SELECT OwnerID,

Name

FROM Owner

WHERE City= 1Wiesloch1;

PNR

NAME

OVERTIME

SALARY

MANAGER

--------

------

-------

H01

Ms

T

?

A06

?

H06

Ms w

?

A06

?

Figure 300: INSERT


144


2014




HA150

The UPDATE statement


�

UPDATE Table SET Column

-

Value,

Column

-

Value,

Column

-

Value

WHERE Condition; Figure 302: UPDATE

The UPSERT statement either updates rows in a table or inserts new rO\VS. It comes in three syntax variants: You can use the UPDATE statement to change all rows In a table.

i>The overtime value of all officials should be increased by 10: UPDATE Officia.l SET overtime - Overtime

+

PNR

10;

NAME

OVERTIME

SALARY

MANAGER

--------

------

-------

POl

Mr A

20

A09

P04

P02

Mr B

20

AlO

P04

P03

Ma c

30

A09

P04

P04

Ms D

?

Al2

P09

P05

Mr E

20

AOB

POB

Figure 303: UPDATE

2014


145 �




HA150

You can use the UPDATE statement to change only certain rows In a table b y adding a WHERE clause. l>The

overtime value of officials in salary group "A09" should be doubled:

UPDATE Official SET overtime = Overtime *

WHERE Salary =

2

'A091;

PNR

NAME

OVERTIME

SALARY

MANAGER

--------

------

-------

20

A09

P04

Mr B

10

AlO

P04

Ms c

40

A09

P04

Ms D

?

Al2

P09

POl

Mr A

P02 P03 P04

Figure 304: UPDATE You can use the UPDATE statement to change values of multi p l e colu mns

.

l>The

overtime value of officials in salary group "A09" should be tripled.

In addition their salary group should be changed to "A10". UPDATE Official SET overtime = Overtime * Salary =

3,

'AlO'

WHERE Salary= 'A091; PNR

NAME

OVERTIME

SALARY

MANAGER

--------

------

-------

POl

Mr

A

30

AlO

P04

P02

Mr B

10

AlO

P04

P03

Ms C

60

AlO

P04

Figure 305: UPDATE


146


2014




HA150

You can use an uncorrelated sub query on the WHERE clause of the UPDATE statement �Offic.ials who are contacts for the car owners are moved to salary group "A15". UPDATE Official

SET Salary

'Al5'

-

(SELECT PersNumber

WHERE PNr IN

FROM Contact) ; PNR

NAME

OVERTIME

SALARY

MANAGER

--------

------

-------

P07

Ms G

22

All

POB

POB

Ms H

?

Al5

P09

P09

Mr I

?

Al5

?

Figure 306: UPDATE You can use a correlated sub query on the WHERE clause of t h e UPDATE statement.

UPDATE Official. o SET Sal.ary

=

'A15'

WHERE EXISTS (SELECT

*

FROM Contact c WHERE c.PersNumber PNR

NAME

-

o.PNr);

OVERTIME

SALARY

MANAGER

--------

------

-------

P07

Ms G

22

All

POB

POB

Ms H

?

Al5

POSJ

POSJ

Mr I

?

A15

?

Figure 307: UPDATE

2014


147 �



HA150


•

Usually a sub query is part of the WHERE clause.

•

You can also use a (correlated or uncorrelated) sub query in the SET clause.

•

For the following SQL statement we assume, that the table Car has an additional column Ownername.

UPDATE

Car

c

SET Ownername

-

(SELECT o.Name FROM Owner o WHERE

c. Owner)

.

CAIUD

,

l?LATENUMBER

o.OwnerID

-

BR1\ND

COLOR

HI?

OWNER

OWN�AME

Fiat

red

75

H06

MSW

---------

-------- --FOl

HD-V

106

F02

HD-VW 4711

vw

black

120

H03

SAP AG

1!'03

HD-JA 1972

BM'I

blue

184

H03

SAi? AG

Figure 308: UPDATE

TheUPSERT/REPLACE statement

Figure 309: Modifying Data In A Database

That's the UPSERT state.ment. If you prefer, you can also use the REPLACE key \Vord alternatively. UPSERT and REPLACE are tnliy synonyms. For the sake of simplicity Ill use key word UPSERT in the following.

�

UP SERT WHERE

Table(Column List)

VALUES (Value,

Value,

Value)

VALUES (Value,

Value,

Value)

Condition

UPSERT Table(Column List) WITH PRIMARY KEY UP SERT

Table(Column List)

SELECT ... FROM ... WHERE ...

Figure 310: UPSERT I REPLACE Syntax Variants


148


2014




HA150

The UPSERT statement either updates rows in a table or inserts new rows. It comes in three syntax variants: •

The first variant looks similar to the INSERT statement, with the key word changed to UPSERT and a WHERE clause added. Such an UPSERT statement inserts a new rov.1 with the values provided, unless the WHERE clause evaluates to true. In that case the matching rows are updated.

•

The second variant replaces the WHERE clause with the key words \VITH PRIMARY KEY. Such an UPSERT statement inserts a new rov.1 v.1ith the values provided, unless a ro\v with the same primary key value exists in the table. ln that case the existing row is updated.

•

The third variant looks like the INSERT statement in its sub-query variant. The corresponding UPSERT variant behaves the same. The difference is that UPSERT checks if the table contains a row with the same primary key value as the to-be-inserted row, and in such a case updates the existing ro\v with the returned ro\v from the subquery. If the table does not have a primary key the UPSERT is fully equivalent to an INSERT.

To keep this unit short, I refrain from giving you concrete examples for the UPSERT statement.

The DELETE statement

F i gure 311: Changing Database Access

DELETE FROM Table WHERE Condition; F i gure 312: DELETE

2014


149 �




HA150

You can DELETE all rows in a table: >Delete all entries from table Car:

DELETE FROM Car;

CARID

PLATENUMBER

BRAND

COLOR

HP

OWNER

Figure 313: DELETE You can DELETE selected rows from a table: >Delete all cars with horsepower less than 180:

DELETE FROM Ca.r WHERE CARID -- -

-

-

HP <

180;

PLATENUMBER

BRAND

- -- -

- --

-

-

- -

--

-

-

-

HP

COLOR - --

-

-

OWNER -- -

-

-

F03

HD-JA 1972

BMW

blue

184

H03

F06

HD-VW 1999

Audi

yellow

260

HOS

Fll

HD-MM 208

BMW

green

184

H02

Fl7

HD-Y 333

Audi

orange

184

H09

F20

?

Audi

green

84

?

-

Figure 314: DELETE

You can use an uncorrelated sub query In t he DELETE statement.

PL11!l'ENUMSER

!'02 !'03

----- ---HD-VW 4711 HD-JA 1972

F04

HD-AL 1002

FOS

HD-MM 3206

F07

HD-ML 3206

FOS

HD-IK 1002

!'09

HD-Ul? 13 HD-MT 507

-

:;..Delete all cars that a re reported as stolen. DELETE FROM Car WHERE

CA.IUD

!'l.O

PlateNumber IN

(SELECT

PlateNumber

FROM Stolen) ;

-

!'14

HD-111i HD-XY HD-IK HD-MM

F15

HD-MB 3030

F16

?

!'18 Fl9

HD-MQ 2006 HD-VW 2012

!'20

?

F1l F12 !'13

206 4711 1001 1977

Figure 315: DELETE


150


2014




HA150

You can use a correlated sub query in the DELETE stateme�� 1'02

HD-VW 4711

1'03

HD-JA 1972

1'04

HD-AL 1002

!'OS

HD-MM 3206

1'07

HD-ML 3206

1'09

HD-rK 1002

*

1'09

HD-UI' 13

Stolen

1'10

HD-MT 507

;��

HD-MM 208

;..Delete all cars that are reported as stolen.

DELETE FROM Car c WHERE

EXISTS (SELECT FROM

s

WHERE s. PlateNumber

=

c.PlateNumber)

HD-XY 4711

1'13

HD-IK 1001

1'14

HD-MM 1977

F15

HD-MB 3030

F16

?

1'18 1'19

HD-MQ 2006 HD-VW 2012

1!"20

?

Figure 316: DELETE

2014


151 �




HA150


152


2014




HA150


modify and delete data in database tables


Copy the data from table "EMPLOYEE" into the (structural equal) table "STAFF".

2.

Set the remaining vacation days of "Mr A" of table "STAFF" to 50 days.

3.

Reduce the remaining vacation days to 18 for all employees (table "STAFF"), who have more than 18 days.

4.

Triple the remaining vacation days of employees (table "STAFF") working for department "Development" or "Test". Use an uncorrelated sub query with "IN".

5.

Create a new entry in table "STAFF".

6.

"Mr A" is leaving the company. Delete his data from table "STAFF".

7.

Delete employees from table "STAFF", where the department information is unkno\vn.

8.

The departments "Planning" and "Test" arc closed. Delete all employees of those departments from table "STAFF". Use an uncorrelated sub query with "fN".

2014


153 �




HA150


Copy the data from table "EMPLOYEE" ioto the (structural equal) table "STAFF". a)

2.

Set the remaining vacation days of "Mr A" of table "STAFF" to 50 days. a)

3.

INSERT INTO STAFF SELECT * FROM Employee;

UPDATE Staff SET RemainderDays=50 vVHERE Name='Mr A';

Reduce the remaining vacation days to 18 for all employees (table "STAFF"), \Vho have more than 18 days. a)

UPDATE Staff SET RemainderDays= 18 WHERE RemainderDays >

4.

18·

'

Triple the remaining vacation days of employees (table "STAFF") working for department "Development" or "Test". Use an uncorrelated sub query \Vith ''IN". a)

UPDATE Staff SET RemainderDays

=

3 * RemainderDays WHERE

DeplD IN (SELECT DeplD FROM Department WHERE Name

=

'Develop1ne11t' OR Name ='Test'); 5.

Create a new entry in table "STAFF". a)

INSERT INTO Staff VALUES ('D200000', 'Mr X', '1977-07-07', 10, 'AOJ');

6.

"Mr A" is leaving the company. Delete his data from table "STAFF". a)

7.

DELETE FROM StafTWHERE Name= 'Mr A';

Delete employees from table "STAFF", \Vhere the department information is unknown. a)

8.

DELETE FROM Staff WHERE DepID IS NULL;

The departments "Planning" and "Test" are closed. Delete all employees of those departments fro1u table "STAFF". Use an uncorrelated sub query with "IN". a)

DELETE FROM Staff WHERE DeplD IN (SELECT DeplD FROM Department WHERE Name ='Planning' OR Name ='Test');

�


154

All rights reserved .

2014




HA150


2014

•


•


•

Remove existing ro\VS from a database table.


155 �



Unit Summary

HA150


�

SQL.

•

Add rows to database tables using

•


•

Remove existing ro\VS from a database table.


156


2014







•

n1 Defining How Data Is Stored Unit Overview Unit Objectives After completing this unit, you will be able to: •

List the most important data types SAP HANA supports.

•

Create new database tables in HANA.

•

Change such tables, e.g. by adding, removing or renaming columns.

Unit Contents Lesson: Defining How Data is Stored Exercise 6: Exercise 6

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. . 158 .

.

.

.

.

.

173

157 �



Unit 8: Defining How Data Is Stored

Lesson:

HA150

Defining How Data is Stored

Lesson Overview This lesson covers ho\v you can create and change database tables to define how data is stored.



•


•


Business Example

Which data types are available in SAP HANA? Figure 317: Data Definition

SAP .HANA provides the following Data Types: •

Numeric types T INYINT,SMALLINT, INTEGER, BIGTNT, SMALLDECTMAL, DECIMAL, REAL, DOUBLE

•

Character string types VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT

•

Date time types DATE, TIME, SECONDDATE, TIMESTA.MP

•

Binary types VARBINARY

•

Large object types BLOB,CLOB,NCLOB,TEXT

�

158


2014



Lesson: Defining How Data i s Stored

HA150

SAP HANA data types for integer values:

Oto 255

TINYINT SMALLINT

-32,768 to 32,767

INTEGER

-2,147,483,648 to 2, 147,483,647

BIGINT

-9,223,372,036,854,775,808 to 9,223,372,036,854, 775,807

Figure 318: Data Types SAP HANA data types for fixed-point values:

DECIMAL(p,s) The DECIMAL(p, s) data type specifies a fixed-point decimal number with precision p and scale s.

The precision is the total number of significant digits. The scale is the number of digits after the decimal point Precision: p (1 s p s 34) Scale: s decimals (0 s s s p), default is 0

; ;>

> l>

0.0000259 259

-

DECIMAL(8,7)

-

DECIMAI.(3,0)

1.0000259 - DECIMAL(B,7) 123.4567

,

DECIMAL(? ,4)

Figure 319: Data Types SAP HANA data types for floating-point values:

SMALLDECIMAL

Floating-point decimal number Precision: variable (1 to 1 6 ) Scale: variable (-369 to 368)

DECIMAL

Floating-point decimal number Precision: variable (1 to 34) Scale: variable (-6, 111 to 6,176 )

REAL

Floating-point binary number (32 Bit)

DOUBLE

Floating-point binary number (64 Bit)

Figure 320: Data Types

2014


159 �




HA150

When us in g numeric data types please note that ... +DECIMAL(8, 7) is suitable for storing f ixed -poin t decimals numbers with 8 digits in

total and 7 fractional digits, while DECIMAL is used to store floating point decimal numbers with variable precision and variable scale. +Certain decimal values (for example, 0.2 a nd 0.4) using binary-based floati ng-point numbers (REAL or DOUBLE) cann ot be represented in an exact way.

•Therefore columns of type REAL or DOUBLE should n ot be used in WHERE or JOIN conditions. o. 4

is stored in a REAL c ol um n as o. 4000000059604645

10* (O. 04/O. 2) results in REAL value l. 9999998807907104 (Instead of

2)

10* (O. 04/0. 2) re sul t s i n DOUBLE value 1. 9999999999999998 (i nstead of 2)


SAP HANA data types for character strings:

VARCHAR(n)

ASCII character string with maximum length n (n s 5,000) 5,000)

NVARCHAR(n)

Unicode character string with maximum length n (n

ALPHANUM(n)

Alphanumeric character string with maximum length n (n

s

s

127)

Unicode character string wti h maximum length n (n s 5,000)

SHORTIEXT(n)

Special data type supporting text- and string-search features (e. g.

Fuzzy Search) based on NVARCHAR(n)


SAP HANA data types for date and time:

Consists of year, month, day

DATE

'2012-05-21' Consists of hour, minute, second

TIME

'18:00:57' Combination of data and time

SECONDDATE

'2012-05-21 18:00:57' Precision: ten millionth of

TIMESTAMP

a

second

'2012-05-21 18:00:57.1234567'



160


2014



Lesson: Defining How Data is Stored

HA150

SAP HANA data types for binary data:

VARBINARY(n)

Binary data, maximum length n Bytes (n s 5,000)

CREATE COLUMN TABLE MyTable

("VARBINARY-Column "

VARBINARY ( 8) ) ;

INSERT INTO MyTable VALUES

(TO-BINARY ( I Walldorf' )) ;


(TO_BINARY('SAP'));


(x' 00075341500�:i*�Y-Column

SELECT

*

57616C6C646F7266

FROM MyTable;

534150 00075341500700FF

Figure 324: Data Types SAP HANA data. types for large objects:

CLOB

Long ASCII character string (maximum 2 GB)

NCLOB

Long Unicode character string (maximum 2 GB)

BLOB

Large binary data (maximum 2 GB) Long Unicode character string (maximum 2 GB)

TEXT

Special data type supporting text- and string-search features (e. g. Fuzz.y Search) based on NCLOB

Figure 325: Data Types •

The tenn LOB data type (LOB

=

large object) is used as a generic tenn for

data types such as CLOB (character large object) or BLOB (binary large object). •

When using LOB data types it is important to note that: LOB columns can not be part of the primary key LOB columns can not be used in the ORDER BY clause LOB columns can not be used in the GROUP BY clause LOB columns may not be part of the JOIN condition (explicit JOIN) LOB columns can not be used as an argument for an aggregate function LOB columns can not be used in the SELECT DISTINCT clause LOB columns can not occur in an UNION statement LOB columns can not be part of a database index

2014


161 �



HA150


Which SQL statements can be used to create, change, rename and delete tables? Figure 326: Data Definition

CREATE COLUMN TABLE •

A new (empty) table is created.

ALTER TABLE •

The definition of an existing table is changed .

RENAME TABLE •

An existing table is renamed.

DROP TABLE •

An existing table is deleted.

Figure 327: Data Definition

CREATE COLUMN TABLE


CREATE COLUMN TABLE Table (Column Data_Type, Column Data_Type, Column Data_Type NOT NULL, Column Data_Type NOT NULL, Column Data_Type DEFAULT Default_Value, Column Data_Type NOT NULL DEFAULT Default_Value, PRIMARY REY(Colurnn,

Column));

Figure 329: CREATE COLUMN TABLE

�

162


2014




HA150

•

It is not mandatory to define a primary key.

•

If you do not define a key, the table con contain duplicates.

CREATE COLUMN TABLE Official VARCHAR(3) I

(PNr Name

VARCHAR(20) I

overtime

INTEGER,

Salary

VARCHAR(3) I

Manager

VARCHAR(3));


•

Table names and column names usually do not distinguish between uppercase and lowercase letters.

•

If you want to distinguish between uppercase and lowercase letters, you must use

double quotes. J>

We recommend not to use the option of lowercase letters.

CREATE COLUMN TABLE "Official." ("PNr" "Name"

VARCHAR(3), VARCHAR (20) ,

"Overtime" INTEGER, "Salary"

VARCHAR(3),

"Manager"

VARCHAR(3));


•

Normally, table and column names may not contain spaces.

•

If table or column names should contain spaces, you must use double quotes.

;.

We recommend not to use the option of spaces in table or column names.

CREATE COLUMN TABLE "Our Officials" ("Official PersNumber"

VARCHAR(3),

"Official Name"

VARCHAR (20) ,

"OVertime Hours"

INTEGER,

"Salary Group"

VARCHAR(3) ,

"Manager PersNumber"

VARCHAR(3));


2014


163 �




HA150

• If double quotes are used, table and column names may be different, but only by the use of upper and lower case. ,..

We do not recommend using this option.

CREATE COLUMN TABLE "Don't do it" ("sap" INTEGER, ti II

INTEGER, SAp" INTEGER,

SaPH

S.AP"

INTEGER,

"Sap"

INTEGER,

II

SaP"

INTEGER,

"SAp"

INTEGER,

II

II

INTEGER);

SAPH

Figure 333: CREATE COLUMN TABLE •

If double quotes are used, table and column names can solely consist of spaces.

;.

We do not recommend using this option.

CREATE COLUMN TABLE

(

11 11

"

"

INTEGER,

If

INTEGER,

"

H

INTEGER,

11

1f

INTEGER);

11

SELECT

"

"

II

"

"

U

"

"

FROM " WHERE

"

"

"

-

. I


Condition 111•1

=

"2"

Explanation The value of the column named "1" must match the value of the column named 0211 (based on the same row).

"2"

-

131

The value of the column named "2" must match the character -string '3' (based on a certain row).

11311

-

4

The numeric value of col umn named "3" must be equal to 4 (based on a certain row).



164


2014



HA150


•

You can define a primary key.

•

If a primary key is defined, the table does not contain duplicates.

•

You can use the primary key definition as part of the column definition, if the primary key consists of only one column

.

CREATE COLUMN TABLE Official (PNr

VARCHAR(3) PRIMARY KEY,

Name

VARCHAR(20),

overtime

INTEGER,

Salary

VARCHAR(3) I

Manager

VARCHAR(3));

Figure 336: CREATE COLUMN TABLE +

You can use a separate PRIMARY KEY clause.

+

You can define a maximum of one primary key per table.

CREATE COLUMN TABLE Official VARCHAR(3) I

(PNr Name

VARCHAR (20) I

overtime

INTEGER,

Salary

VARCHAR(3) I

Manager

VARCHAR(3),

PRIMARY KEY(PNr));

Figure 337: CREATE COLUMN TABLE +

You must use a separate PRIMARY KEY clause to define a multi-column primary key.

•

For all columns of the PRIMARY KEY NULL values are (implicitly) prohibited.

CREATE

COLUMN TABLE Owner EU

(Country

VARCHAR(3) I

Owner ID

VARCHAR(3) /

Name

VARCHAR(20) I

Birthday

DATE,

City

VARCHAR(20) I

PRIMARY KEY(Country,

OwnerID));


2014

© 2014 SAP AG or

an


165 �




•

HA150

When defining a column you can specify that no NULL values are allowed by adding the words NOT NULL

•

For primary key columns, NULL values are implicitly prohibited. For these columns, adding NOT NULL is permitted but not required.

CREATE COLUMN TABLE Official (PNr Name

VARCHAR(3), VARCHAR(20) NOT NULL,

Overtime INTEGER, Salary

VARCHAR(3) NOT NULL,

Manager

VARCHAR(3),

PRIMARY KEY(PNr));


•

You can define a default value for a column by adding DEFAULT .

•

The default value is used during INSERT, if no value is specified for the corresponding column.

•

The default value will not be used during INSERT, if a NULL value is explicitly specified.


VARCHAR(3), VARCHAR(20),

Overtime INTEGER DEFAULT 0, Salary

VARCHAR(3) DEFAULT

Manager

VARCHAR(3),

1A061

I

PRIMARY KEY(PNr));


•

NULL values are not excluded when setting a default value. Hence the words NOT NULL can also be useful for columns that do have a default value set.

•

The order of the key words "DEFAULT" and "NOT NULL" is irrelevant.


VARCHAR(3), VARCHAR(20),

overtime INTEGER NOT NULL DEFAULT 0, Salary

VARCHAR(3) DEFAULT

Manager

VARCHAR(3),

1A061

NOT NULL,

PRIMARY KEY(PNr)); Figure 341: CREATE COLUMN TABLE


166


2014




HA150

• When defining a column, you can specify that this column must not contain double values by adding UNIQUE . •

In contrast to the PRIMARY KEY, UNIQUE allows NULL values.

CREATE COLUMN TABLE Car (CarID


PlateNumber

VARCHAR(lO) UNIQUE,

Brand

VARCHAR(20),

Color

VARCHAR(lO),

HP

INTEGER,

owner

VARCHAR(3));


•

NULL values are not excluded when setting UNIQUE . Hence the words NOT NULL can also be useful for columns defined as UNIQUE

•

The order of the key word "UNIQUE" and "NOT NULL" is irrelevant



PlateNumber

VARCHAR(lO) UNIQUE NOT NULL,

Brand

VARCHAR(20),

Color

VARCHAR(lO),

HP

INTEGER,

Owner

VARCHAR(3));


•

It is possible to prohibit duplicate values for more than one column.

•

Therefore you can define several keys for one and the same table.

l>

Using UNIQUE two single-column keys are defined:



PlateNumber

VARCHAR(lO),

Brand

VARCHAR(20) UNIQUE,

Color

VARCHAR(lO) UNIQUE,

HP

INTEGER,

Owner

VARCHAR(3));


2014


167 �



HA150


You can use a separate UNIQUE clause for the key definition.

'Using UNIQUE two single-column keys are defined:

CREATE

COLUMN TABLE

(CarID

Car

VARCHAR(3)

PRIMARY KEY,

PlateNwnber

VARCHAR(lO) I

Brand

VARCHAR(20) I

Color

VARCHAR(lO) I

HP

INTEGER,

Owner

VARCHAR(3),

UNIQUE(Brand), UNIQUE(Color));


You must use a separate UNIQUE clause to define a multi-column key.

,Using UNIQUE a two-column key is defined:

CREATE

COLUMN TABLE

Car

VARCHAR(3)

(CarID

PRIMARY KEY,

PlateNwnbe.r

VARCHAR(lO),

Brand

VARCHAR(20),

Color

VARCHAR(lO),

HP

INTEGER,

Owner

VARCHAR(3),

UNIQUE(Brand,

Color));


ALTER TABLE



168


2014




HA150

ALTER

TABLE

Table

ADD

(Column

Data_Type,

Column Data_Type NOT NULL

DEFAULT

Default_Value);

ALTER

TABLE Table

DROP (Column, Column, Column) ;

ALTER

TABLE Table

ALTER (Column Dat a_Type

DEFAULT Default Value, _

Column Data_ Type NULL); Figure 348: ALTER TABLE

+

You can ADD columns to an existing table.

+

You can use NOT NULL only if the table is empty or if you define a DEFAULT value in addition.

+

Newly added columns are filled with NULL values, or - if available - with the corresponding default value.

ALTER TABLE Official ADD

(RemajnderDays Annua1Leave

INTEGER, INTEGER NOT NULL DEFAULT

30) ; Figure 349: ALTER TABLE

+

You can DROP columns from an existing table.

+

You cannot drop columns that are part of the primary key.

+

Also for tables without a primary key at least one column must be left.

ALTER TABLE Official DROP

(Salary, Manager, Name);

Figure 350: ALTER TABLE

2014


169 �




HA150

•

You can change the definition of an existing column.

+

For columns without a default value, you can specify one.

•

For columns with a default value, you can change the existing value.

•

The new (or first time) defined default value only affects newly inserted rows.

+

For columns that do not allow NULL values so far, NULL values can be allowed.

ALTER TABLE Off icia1 ALTER

(Overtime

INTEGER DEFAULT 7,

Salary

VARCHAR(3) DEFAULT 'A01',

Name

VARCHAR(20) NULL);


•

For columns with a default value you can delete the existing default value.

+

The deletion of the default value only affects newly inserted rows.

•

Already existing values of a table are not affected by the deletion of the default value.

ALTER TABLE Official ALTER

(Salary VARCHAR (3) DEFAULT NULL) ;


•

You can change the data type of existing columns. Type compatibility must be ensured.

•

For string-based data types the new data type must have at least the same maximum length (in comparison t o the old data type).

;:.. VARCHAR(20) VARCHAR ( 2 §)

-+

•

VARCBAR(25) is allowed, but not

( VARCH. '\R 2 g)

ALTER TABLE Official ALTER

(Name Salary

VARCHAR(25), VARCHAR(S));


•

You can delete the definition of the primary key.

+

Only the primary key property is deleted.

•

The corresponding columns of the (previous) primary key are not deleted.

ALTER TABLE Official DROP PRIMARY KEY; Figure 354: ALTER TABLE


170


2014




HA150

•

You can subsequently define a primary key for a table without one.

•

The corresponding columns must not contain NULL values.

•

The key property must not be violated by the existing data.

ALTER TABLE Official ADD PRIMARY KEY (PNr) ; Figure 355: ALTER TABLE

RENAME TABLE


RENAME TABLE old name TO new name; Figure 357: RENAME TABLE

•

You can RENAME an existing table.

•

The table you want to rename can be empty or can contain data.

•

The new table name must be different to the old table name.

RENAME

TABLE Official. TO Employee;

Figure 358: RENAME TABLE

DROP TABLE


DROP TABLE

Table;

Figure 360: DROP TABLE

DROP

•

You can

an existing table.

•

The table you want to drop can be empty or contain data.

•

Both, the table content and the table itself will be deleted.

DROP TABLE Official; Figure 361: DROP TABLE

2014


171 �



HA150


Renaming a table column


RENAME

COLUMN

+An existing table column is renamed. •This statement is used to "permanently" rename a column of an existing table {by which the table definition is changed). +Hence, we are not simply renaming a column of the query-result {for which the keyword "AS" can be used in the SELECT clause).


RENAME COLUMN


RENAME COLUMN Table.Column new

TO

Column Name;

Figure 365: RENAME COLUMN

•

You can (subsequently) rename a table column.

•

The respective table can be empty or can contain data.

•

The new column name must be different to the old column name.

�

Column "PNr" of Table "Official" is renamed into "PersNumber":

RENAME

COLUMN Official.PNr TO PersNUinber;

Figure 366: RENAME COLUMN


172


2014




HA150


create and modify database tables using SQL


Create a ne\\' column table "Locations" with columns •

•

•

•

"Token" - type VARCHAR, length 3 "Region"- type VARCHAR, length 20 "Country" - type VARCHAR, le ngth 20 "City" - type VARCHAR, length 20

Each location is uniquely identified by

"Token". \.Vhen inserting data,

"Country" and "Region" can be unknov•n, but not the "City". If no "Region" is specified during insert the default value "EMEA" should be used. 2.

Create a new column table EmployeeSalary with columns •

DNumber- type NVARCHAR(7)

•

FromDate - type DATE

•

FixedSalary- Choose a numerical type that can hold salaries between I 000

and 1 million€, and allows for cent precision •

(l'\VO fractional digits).

VariableSalary - Choose a numerical type that can hold percentage values

with two fractional digits. The combination ofDNurnber and FromDate uniquely identify a row. Neither FixedSalary nor VariableSalary may be unknown. The VariableSalaray defaults to lOo/o. To check your definition, insert three rows into the table: •

One for Employee D l 0000 l with a fixed salary of 2500€ and the default

percentage of variable salary. •

One for Employee D 100002 with a

fixed salary of 3000€ and 15% variable

salary, •

One for Employee D 100003 with a

fixed salary of 3200€ and 17% variable

salary.


2014


173 �




HA150

The salaries should all be valid starting from the current date. 3.

Extend the newly created table "Locations" with colrunns •

"Population" - type INTEGER

•"Mayor" - type VARCHAR, length 30 4.

Remove the columns "Country" and "Population" from table "Locations".

5.

Change the table definition of "Locations" that column "City" can have NULL values.

6.

�

Delete tables "Locations" and "E1nployeeSalary".


174


2014




HA150

Solution 6: Exercise 6 Task: I.

Create a new column a t ble "Locations" with columns ·" Token" - type VARCHAR, length 3 •"Region" - type VARCHAR, length 20 • "Country" - type VARCHAR, length 20 • "City" - type VARCHAR, length 20 Each location is uniquely identified by " Token". When inserting data, "Country" and "Region" can be unknown, but not the "City". If no "Region" is specified during insert the default value "EMEA" should be used. a)

CREATE COLUMN TABLE Locations (Token CHAR(3) PRIMARY KEY, Region VARCHAR(20) DEFAULT 'EMEA', Country VARCHAR(20), City VARCHAR(20) NOT NULL);

2.

Create a ne\v column table EmployeeSalary with columns •

•

DNumber - type NVARCHAR(7) FromDate - type DATE

•FixedSalary- Choose a numerical type that can hold salaries between 1000 and l million €, and allo\vs for cent precision (l\vo fractional digits). •VariableSalary - Choose a numerical type that can hold percentage values with t\vo fractional digits. The combination of DNumber and FromDate uniquely identify a row. Neither fixedSalary nor VariableSalary may be unknown. The VariableSalaray defaults to 10%. To check your definition, insert three rows into the table: •One for Employee D l 00001 with

a

fixed salary of 2500€ and the default

percentage of variable salary. •

One for Employee D 100002 with a fixed salary of 3000€ and 15% variable

salary, •

One for Employee D 100003 with a fixed salary of 3200€ and 17% variable

salary.


2014


175 �




HA150

The salaries should all be valid starting from the current date. a)

CREATE COLUMN TABLE EmployeeSalary( DNumber NVARCHAR(7), Fro1nDate DATE, FixedSalary DECIM.AL(9,2) NOT NULL, VariableSalary DECIMAL(5,2) NOT NULL DEFAULT 5.0, PRIMARY .KEY(DNumber,FromDate) );

b)

INSERT INTO EmployeeSalary VALUES ('DIOOOOl', current_date, 2500.0, 5.0 );

c)

INSERT INTO EmployeeSalary VALUES ('Dl00002', cu rrent_date, 3000.0, 15.0);

d)

INSERT INTO E1nploy ee Salary VALUES ('DI00003', current_date

,

3 200.0, 17.0); 3.

Extend the newly created table "Locations" \Vith colrunns •

"Population" - type INTEGER

•

"Mayor" - type VARCHAR, length 30

a)

ALTER TABLE LOCATIONS ADD (Population IN TEGER, Mayor VARCHAR(30));

4.

Remove the columns "Country" and "Populati on " frmn table " Locati on s". a)

5.

ALTER TABLE LOCA TIONS DROP (Country, Population);

Change the table definition of "Locations" that column "City" can have NULL values. a)

6.

�

176

ALTER TABLE LOCATIONS ALTER (City VARCHAR(20) NULL);

Delete tables "Locations" and "EmployeeSalary". a)

DROP TABLE LOCATIONS;

b)

DROP TABLE EMPLOYEESALARY;



2014




HA150


2014

•


•


•


© 2014 SAP AG

or an


177 �



Unit Summary

HA150


�

178

•

List the most i1nportant data types SAP HANA supports.

•


•

Change such tables, e.g. by addjng, removing or renaming columns.


2014







•

n1 Using Views For Data Access Unit Overview Unit Objectives After completing this unit, you will be able to: •

Describe the use cases for and advantages of using database views.

•

Define database views using SQL.

•

Query data through views.

•

Delete database views using SQL.

Unit Contents Lesson: Using Views for Data Access Exercise 7: Exercise 7

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

. .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

180 191

179 �



Unit 9: Using Views For Data Access

Lesson:

HA150

Using Views for Data Access Lesson Overview This lesson covers database views.



•


•

Query data through views.

•


Business Example

Why do we need views? What are the advantages of the view concept?


Advantages of Views •

Decoupling applications from lower levels (relative to the 3 level schema architecture) Changes to the conceptual schema (such as adding ne"' columns of the table) have no effect on application

•

Tailored views, individually customized for the users and their tasks The user (or application programmer) does not know the entire database structure in detail - but the excerpt that is relevant to the1n.

•

Simplification of queries Complex queries referencing vie\\'S are easier to formulate, provided that the view provides an appropriate pre-selection of data.

•

Possibility of access restriction Views can be used to realize value-dependent access privileges (for example, managers can access only the data of their employees).

�

180


2014



Lesson: Using Views for Data Access

HA150

Leveraging Views


i.-

Example of a view containing only red cars:

CREATE VIEW redCars AS SELECT

CarID,

Color,

PlateNumber,

HP

AS Power

FROM Car WHERE

Color

=

•red';

CARID

COLOR

PLATENUMBER

POWER

-----

-----------

-

FOl

.red

HD-V 106

75

F09

.red

HD-UP 13

105

Fl2

.rod

HD-XY 4 711

105

Fl3

.rod

HD-IK 1001

136

.red

HD-MQ 2006

90

--

---

Fl8

----

Figure 369: VIEWs You can select data from a view In the same way you select from a "normal" table.

:.>Which red cars have more than SELECT

CarID,

100 HP?

PlateNumber,

Power

FROM redCars WHERE

Power > 100

ORDER BY Power

DESC,

CarID ASC;

CARID

PLATENUMBER ----------

Fl3

POWER -----

-

HD-IK 1001

136

F09

HD-UP 13

105

Fl2

HD-XY 4711

105

Figure 370: VIEWs

2014


181 �




HA150

You can INSERT new rows into a view. +The new rows are not inserted into the view, but into the underlying base table!

INSERT

INTO redCars

VALUES

('F77',

SELECT

'red',

'HD-MT

2509', 170);

* FROM Car;

CA.RID

l'LM.'ENIJMBER

BlilAND

-----------

------

COLOR

Ill'

OWNER

-

E'Ol.

HD-V 106

Fiat

:red

75

1106

E'02

HD-VW 47ll

vw

black

120

1103

F03

HD-JJ!. 1972

m-M

blue

184

1103

F18

HD-MQ 2006

Renault

:red

90

1103

Fl.9

HD-VW 2012

vw

black

125

1101

?

Audi

green

184

?

HD-MT 2509

?

red

170

?

E'20 �E'77

Figure 371: VIEWs

You can UPDATE rows in a view. +The rows are not changed in the view, but in the underlying base table!

UPDATE redCars SET Power

-

120

WHERE Power < 120; SELECT

* FROM Car;

CAIUD

PI.ATENUMBER -----

�E'Ol

-

-

-

HD-V 106

BRAND

COLOR

HP

OWNER

Fiat

reel

120

H06

----

-

i"07

HD-ML 3206

Aud:I.

b1ue

l.16

803

F08

HD-IK 1002

VH'

black

160

H07

�!'09

HD-U11 13

Skod.a

red

120

H02

FlO

Hll-lfi' 507

BMW

black

140

804

FJ.l

HD-ltd 208

-

greon

184

H02

�F12

HD-XY 4711

Skod.a

red

120

804

E'l3

HD-IK 1001

Mnault

red

136

H07

�E'lB

HD-MQ 2006

Mnault

red

120

H03

Figure 372: VIEWs


182


2014



Lesson:

HA150

You can DELETE rows from

Using Views

for Data Access

a view.

•The rows are not deleted from the view, but from the underlying base table! DELETE FROM redCars WHERE Power

<

125;

SELECT * FROM Car;

�

�

CMJ])

PLAT&llUMBER

BRAND

F07

llD-HL 3206

Audi

FOB

HD-IK 100'

vw

" no

HD-MT !107

Fll

llD-HM 208 I

F13

BMW BMW

COLOR

RP

OWNER

blue

116

H03

bloc:lc

160

807

bl•c:lt

140

H04

qreen

184

802

red

136

I

HD-IK 1001

104 Renault

807

�

Figure 373: VIEWs

Which SQL statements can I use to create or delete views?

Figure 374: Data Definition CREATE VIEW •

Creates a new view.

DROP VIEW •

Deletes an existing view.


CREATE VIEW


CREATE VIEW View AS SELECT Column, Column, FROM

Column

Table

WHERE Condition; Figure 377: CREATE VIEW

2014


183 �




�

•

HA150

You can restrict a view to specific columns of the underlying base table.

CREATE VIEW Autos AS Color,

SELECT Brand,

HP

FROM Car;

BRllND

COLOR

-------

-----

l!P

l!'iat

red

75

VR' -

black

120

bJ.ue

184

Mercedes

Me.rcede.s

WJ\ite

136

black

170

Audi

yellow

260

Audi

b1ue

lJ.6

VR'

black

160

Skoda

red

105

-

bJ.ack

140

green

184

Skoda

red

105

Renault

red

136

wllite

J.70

Skoda

black

136

Opel Audi

green

120

orange

184

Renault

red

90

Vil

black

125

Audi

green

184

ldel:cedes

Figure 378: CREATE VIEW

•

You can restrict a view to specific rows of the underlying base table.

CREATE VIEW Audis AS SELECT Brand,

Color,

HP

FROM Car WHERE Brand= 'Audi';

BRAND -----

COLOR

HP

------

Audi

yellow

260

Audi

blue

116

Audi

orange

184

Audi

green

184

BRAND

COLOR

HP


•

You can restrict a view to a certain amount of rows:

CREATE VIEW

"Top3 Audis"

SELECT TOP 3 Brand,

AS

Color,

HP


-

'Audi';

-----

------

Audi

yellow

260

Audi

blue

116

Audi

orange

184



184


2014



Lesson: Usi ng Views for Data Access

HA150

•

The columns of a view can have different names than the columns of the underlying base table.

CREATE VIEW Audis

(Manufacturer,

SELECT Brand,

Color,

Color,

Power)

AS

HP

FROM Car WHERE

Brand

=

'Audi 1 ;

COLOR

MAN'OFACTl.ffiER

------

------------

POWER -----

Audi

yellow

260

Audi

blue

116

Audi

orange

184

Audi

green

184

Figure 381: CREATE VIEW • You can (and should) rename the columns in the SELECT clause CREATE VIEW Audis AS SELECT Brand AS Manufacturer,

Color,

HP AS Power


=

•Audi• ;

MANUPACT'ORBR ------------

COLOR ------

POWER -----

Audi

yellow

260

Audi

blue

116

Audi

orange

184

Audi

green

184

Figure 382: CREATE VIEW •

If you rename columns in both the CREATE VIEW clause and in the SELECT clause, the name of the CREATE VIEW clause "wins".

•

We recommend renaming columns only in the SELECT clause.

CREATE VIEW Audis

(Manufacturer,

Color,

SELECT Brand AS Description,

Color,

Power)

AS

HP AS

HorsePower

FROM Car WHERE

Brand

-

'Audi· ; MANUFACTURER ------------

COLOR ------

POWER -----

Audi

yellow

260

Audi

blue

116

Audi

orange

184

Audi

green

184


2014

© 2014 SAP AG

or

an SAP affiliate company. All rights reserved.

185 �




HA150

COX.OR

+

A. view can contain duplicates.

CREATE VIEW Colors SELECT

AS

red blacl!: blue white

Color

black

FROM Car;

yGllow blue black red black green red red white black green orange red bl.ack green


•

You can use DISTINCT in the view definition.

+

Thus the view does not contain duplicates.

COLOR

red black

CREATE VIEW Colors

AS

SELECT DISTINCT Color FROM Car;

blue white yellow green orange


+

You can use ORDER BY in the view definition.

+

From a theoretical perspective the use of ORDER BY is highly questionable, because the rows of a view are unsorted!

•

There is no guarantee that the specified ORDER BY sorting is taken into account when reading the view.

+

We recommend avoiding ORDER BY in the view definition.

CREATE VIEW Colors AS SELECT DISTINCT Color FROM Car ORDER BY Color;

COLOR

blue yellow green orange red black white



186


2014




HA150

You can use calculated columns in a view.

+

CREATE VIEW Audis

AS

SELECT CarID,

Co1or,

ROUND(HP

/ 1.35962162, 0)

AS

"kW" FROM Car WHERE Brand -

'Audi· ;

CARID

COLOR

-----

------

F06

yellow

191

F07

blue

85

Fl7

orange

135

F20

green

135

kW

Figure 387: CREATE VIEW You can use functions in the view definition.

+

CREATE

VIEW Owneroverview AS

SELECT Name,

YEAR(Birthday)

AS YearOfBirth

FROM Owner; NAME ------

YEAROFBIRTH -----------

Ms T

1934

Ms u

1966

SAP AG

?

HDM AG

?

Mr v

1952

Ms w

1957

IKEA

?

Mr x

1986

Ms y

1986

Mr z

1986

Figure 388: CREATE VIEW +

You can use GROUP BY and aggregate expressions In the view definition.

CREATE VIEW Cars

AS

SELECT Brand,

COUNT(*)

AS Quantity

FROM Car GROUP BY

Brand

HAVING COUNT(*)

> 2;

BMND --------

QUANTITY --------

vw

3

BMK

3

Mercedes

3

Audi

4

Skoda

3


2014


187 �




•

HA150

You can use UNION [ALL) in the view definition.

CREATE VIEW Persons AS SELECT OwnerID AS "Pers ID",

N ame

FROM Owner WHERE Birthday>=

'1977-05-21'


Name Pers

FROM Official WHERE Salary

=

ID

NAME

-------

1A091;

HOB

Mr x

H09

Ms 'i

HlO

Mr z

POl

Mr A

P03

Ms c

P06

Mr F

Figure 390: CREATE VIEW + You can use (any) JOIN in the view definition. CREATE VIEW Stolencars AS SELECT c.PlateNumber, FROM Car c,

c.Brand,

c.Color

Stolen s

WHERE c.PlateNumber

=

s.PlateNumber;

PLATENUMBER

BRAND

COLOR

-----------

-----

- -----

HD-V 106

Fiat

red

HD-VW 1999

Audi

yellow

HD-¥ 333

Audi

orange

Figure 391: CREATE VIEW • You can use an uncorrelated sub query in the view definition. CREATE VIEW StolenCars AS SELECT PlateNumber,

Brand,

Color

FROM Car WHERE PlateNumber IN

(SELECT PlateNumber FROM Stolen) ;

PLATENUMBER

BRAND

COLOR

------- - ---

--- --

---- --

HD-V 106

Fiat

red

HD-VW 1999

Audi

yellow

HD-¥ 333

Audi

orange



188


2014




HA150

•

You can use a correlated sub query in the view definition.

CREATE VIEW StolenCars AS SELECT

Number, c.Brand,

c.Color

FROM Car c WHERE EXISTS

(SELECT

*

FROM Stolen

s

WHERE s.PlateNumber c.PlateNumber); PLATENUMBER

BRAND

COLOR

---

----- -

HD-V 106

Fiat

red

HD-VW 1999

Audi

yellow

HD-Y 333

Audi

orange

----- -

----

--


•

You can define a view based on another view.

CREATE VIEW Volkswagen AS SELECT CarID,

PlateNumber,

Color,

HP


=

'VW';

CREATE VIEW blackVolkswagen AS SELECT CarID,

PlateNumber,

HP

FROM Volkswagen WHERE Color

=

'black';

CARID

PLATENUMBER

HP

-----

-

F02

HD-VW 4711

FOB

HD-IK 1002

160

F19

HD-VW 2012

125

----------

120


DROP VIEW


DROP VIEW View; Figure 396: DROP VIEW

2014


189 �




HA150

•

You can drop an existing view.

•

O nly the view definition is deleted.

•

The underlying data of the view is not deleted.

DROP VIEW redCars;

Figure 397: DROP VIEW

Is it always possible to change the data of the underlying table by referencing a view?

Figure 398: Views and Changeability

Referencing a view, the rows of the underlying base table cannot be changed if (at minimum) any of the following statements is true: >

The view definition contains a calculation In the projection list (such as HP/ 1.36).

>

The view definition uses a function In the projection list (such as YEAR).

>

The view definition uses DISTINCT to eliminate duplicates.

>

The view definition contains the TOP n clause to restrict the number of rows to n.

>

The view definition uses an aggregate function in the projection list (such as MAX).

>

The view definition is based on a GROUP BY clause.

>

The view definition uses UNION.

>

The view definition contains an (implicit or explicit) JOIN.

>

The view definition uses a sub query In the projection list.

Figure 399: Views and Changeability


190


2014



HA150


create and delete database views


Create view "ManagerYiew". The manager of department "A04" should only view data of his O\Vn employees. To avoid discrimination/favoritism the birthday should be excluded. Rename column "RemainderDays" to "VacationDays".

2.

2014

Delete view "ManagerView".


191 �



HA150


Create view "MauagerView". The manager of department "A04" should only view data of bis own e1nployees. To avoid discri1nination/favoritism the birthday should be excluded. Rename column "RemainderDays" t o ''VacationDays". a)

CREATE VIE\.V ManagerView AS SELECT DNumber, Name, RemainderDays AS VacationDays FROM Etnployee WHERE DepJD =

2.

Delete vie\v "Manager View". a)

�

'A04' WITH CHECK OPTION;

DROP VIE\.V ManagerView;


192


2014




HA150


2014

•


•


•

Query data through vie,vs.

•


© 2014 SAP AG or

an

SAP


affilia

193 �



Unit Summary

HA150


Describe the use cases for and advantages of using database views .

•


•

Query data through views .

•



194


2014







•

n1 Defining Data Access Unit Overview Unit Objectives After completing this unit, you will be able to: •

Access tables in other schemas (assuming suitable permissions).

•

Control how other users can access data in your O\Vn schema.

•

Explain \Vhen database indexes make sense even in HANA.

•

Create and delete database indexes using

SQL.

Unit Contents Lesson: Defining Data Access

2014


.

.

.

affili

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

ate company. All rights reserved.

.

.

.

.

.

.

.

.

.

.

.

.

196

195 �



Unit 10: Defining Data Access

Lesson:

HA150

Defining Data Access Lesson Overview This lesson covers how you can manage data access permission on SQL level, and how data access can be sped up using database indexes.


Access tables in other schemas (assuming suitable permissions).

•

Control how other users can access data in your own schema.

•

Explain when database indexes make sense even in HANA.

•

Create and delete database indexes using SQL.

Business Example

Why are there no naming collisions when multiple users create the same table or view? Figure 400: Data Definition

�

Note: The following information applies to direct (SQL-based) v.rork on

SAP .HANA - but not to database access using ABAP!

•

User 1 creates table "Car".

•

User 2 also creates table "Car".

•

Why is there no naming collision?

The name of the database object (table, view etc.) implicitly contains a schema name as prefix: . "USER!.Car" t "USER2. Car"

�

196


2014


Lesson: Defining Data Access

HA150

•

When creating a user an identically named schema is created implicitly.

•

By default a user works in his own schema (and namespace).

•

You can explicitly specify the schema name if necessary.

}>

Which brand has (at least) one black car? SELECT DISTINCT Userl.Car.Brand

FROM Userl.Car

BRAND

WHERE Userl.Car.Color ORDER

=

'black'

BY Userl.Car.Brand;

BMW Mercedes Skoda vw

Figure 401: Schemata

•

Queries across multiple schemata are possible.

;.. To whom is (at least) one black car registered? SELECT DISTINCT Schemal.Owner.Name FROM Scbemal.owner, Schema2.Car WHERE Scbemal.owner.OwnerID

=

AND Schema2. Car. Color

-

Schema2.Car.Owner

'black' ; NAME

SAP AG IKEA

HDM AG Ms T Figure 402: Schemata

How can I define who has access to which data?

Figure 403: Access Control Access Control

2014


197 �



HA150

To specify who can access which data you can use the follo\ving two main (SQL ,

based) options. They can be used in combination: •

Create views that represent a portion of the data: You provide a vie\\1 that contains only the O\vners of CityA to a user for \Vhom the owners outside of CityA are not relevant.

•

Grant specific access permissions to selected users: You grant read only permissions to a user, who should be able to read the ov.rner data but not to change it.

Which SQL statements can I use for access control?

Figure 404: Access Control GRANT •You can selectively grant access permissions to a user (or role).

REVOKE •You can selectively revoke access permissions from a user (or role).

Figure 405: Access Control

The SQL statement GRANT


GRANT Privilege, Privilege ON Database_Object TO Database User WITH GRANT OPTION; -

Figure 407: GRANT

�

198


2014



HA150


• You can define which user is allowed to SELECT from which table. •

By adding the WITH GRANT OPTION the recipient of the privilege (grantee) is allowed

to grant this

privilege

to other

users (without losing It).

Official of schema

;,.

User1 is allowed to read (SELECT) from table

;.

User1 is allowed to grant this SELECT privilege to other users.

Schema2.

GRANT SELECT ON

Schema2.0fficial

TO Userl

WITH

GRANT

OPTION;

Figure 408: GRANT • You can exactly specify which

user is allowed lo

• The INSERT priv ilege does not

INSERT rows into which table.

necessarily require the SELECT privilege.

•

The INSERT privilege does not automatically Include the SELECT privilege.

;.

User1 is allowed to INSERT rows Into table Owner of schema Schema2. As User 1 has no SELECT privilege, he is not allowed to read the rows he has inserted.

GRANT INSERT ON Schema2.0wner TO Userl;

Figure 409: GRANT

UPDATE rows

•

You can exactly specify which user is allowed to

•

The UPDATE privilege does not necessarily require the SELECT privilege.

•

The UPDATE privilege does not automatically include the SELECT privilege.

>

User1 is allowed to UPDATE table Car_EU of schema Schema2.

;.

Because User1 has no SELECT privileges for the table Car_EUtheWHERE clause

of which table.

of his UPDATE statement cannot contain a reference to the columns of this table. GRANT

UPDATE

ON Schema2.Car EU TO Userl;

Figure 410: GRANT

•

You can exactly specify Which user Is allowed to DELETE rows from which table.

•

The DELETE privilege does not necessarily require the SELECT privilege.

•

The DELETE privilege does not automatically include the SELECT privilege.

>

User1 is allowed to DELETE rows

;.

Because User1 has no

from table

SELECT privileges

Stolen of schema Schema2.

for the table

Stolen the WHEBE

clause

of his DELETE statement cannot contain a reference lo the columns of this table. GRANT DELETE

ON Schema2.Stolen

TO

Userl;

Figure 411: GRANT

2014

© 2014 SAP AG or an SAP affiliate company. All

rights

reserved.

199&.




•

HA150

You can assign multiple privileges for the same database object by using a single GRANT statement. User1 is allowed to SELECT,

INSERT,

UPDATE,

DELETE

on table

Contact of schema Schema2. ,. User1 is allowed to grant these privileges to other users (in whole or in part). GRANT

SELECT,

INSERT,

UPDATE,

DELETE

ON Schema2.Contact

TO User1 WITH GRANT OPTION;

Figure 412: GRANT

+

You can assign privileges for all tables and views of a schema by using a single GRANT statement.

>

User1 is allowed to SELECT from all tables and views of schema Schema2.

>

User1 is allowed to grant this privilege to other users (for all or selected tables or views).

GRANT

SELECT

ON SCHEMA

Schema2

TO Userl WITH GRANT

OPTION;

Figure 413: GRANT Depending on the granularity (or the type of database object) you can assign different access privileges to users.

Access Privileges

Granularity (single) Schema

SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE ANY

(single) Table

SELECT, INSERT, UPDATE, DELETE, DROP, ALTER

(single) View

SELECT, INSERT, UPDATE, DELETE, DROP

Figure 414: GRANT

The SQL statement REVOKE


�

200


2014




HA150

Privilege, Privilege, Privilege ON Database_Object FROM Database User;

REVOKE

Figure 416: +

REVOKE

Using the REVOKE statement you can selectively revoke access privileges from a user. User1 will no longer be allowed to read from table Officlsl of schema Schema2

:.>

(assuming that this privilege is not granted by other privileges) REVOKE SELECT

ON Schema2.0fficia1 FROM

Figure 417: +

Userl;

REVOKE

You can revoke multiple privileges for the same database object by using a single REVOKE statement User1 will no longer be allowed to SELECT,

INSERT, UPDATE,

DELETE

from table Contact of schema Schema2 (assuming that these privileges are not granted by other privileges) REVOKE SELECT,

INSERT, UPDATE,

DELETE

ON Scbema2.Contact FROM Userl;

Figure 418: +

REVOKE

Access privileges on different hierarchical levels of database objects are independent.

,..

Despite the REVOKE statement User1 still has read access to the table Official of schema Schema2, because he still has a (not withdrawn) read permis sion on the entire schema Schema2. GRANT SELECT

ON SCHEMA Schema2 TO

Userl;

REVOKE SELECT ON Scbema2.0fficia1

FROM Userl;

Figure 419:

2014

REVOKE


201 �




•

HA150

The independence of access privileges at various hierarchical levels is also valid in the contrary case.

•

Despite the REVOKE statement User1 still has read access to the table Off icial of schema Schema2, because he still has a (not withdrawn) read permission on the table.

GRANT SELECT ON Schema2.0fficial TO Userl; REVOKE SELECT ON SCHEMA Schema2 FROM Userl; Figure 420: REVOKE

What is a database index?

Figure 421: Access Paths

• A n index (plural: indexes or indices) is an access path. • An index may speed

up search and sorting.

•

Index access instead of "full table scan" or "full column scan"

•

Index usage with HANA COLUMN TABLES only in exceptional cases

• An index usually slows down inserts, update and deletion. • An index has no influence on the query result. •

Effect "only" on performance

• Indexes are not covered by the •

SQL standard.

CREATE I D ROP INDEX are strictly speaking no SQL statements.


�


202


2014




HA150

Column "Name"

HANA stores data in columns, not rows

•

Columns are stored as - Sorted dictionary of values

- (Bit-)Vector of value

IDs

Are indexes necessary to speed up data access?

Column "Name"

-

internal

representation Value ID vector Dictionary

conceptual

Miller

4

0

Baker

J ones

1

1

Jones

Mil lm an

5

2

John

Zsuwalski

772

3

Johnson

Baker

0

4

Mi ller

Mi ller

4

5

Millman

John

2

Miller

4

Johnson

3

Jones

1 . .

.. .

..

772

.

Zsuwalski

..

.

.

Figure 423: HANA: Why Indexes on Column Store?

•

Value ID lookup is fast

Dictionary

Value ID vector

- binary search on sorted dictionary

WHERE Name = • Zsuwalski'

�

c o

L! ..

Row ID lookup can still

involve "full column scan" - No issue for most value ID

:

� .. c :0

large value ID vectors

Value

0

Baker

0

4

1

Jones

1

1

2

John

2

5

3

Jo hnson

4

M i lle r

5

Millman

vectors, even less if sorted - But may be slow for very

Row ID

772

full column scan wlth val

3

772..:._::_ � IQ�_:____J_.:. _j 4

0

254621

772

Zsu wals k1

Figure 424: HANA: Table Scan With Value ID

2014



203 �




HA150

An inverted index can be reasonable in exceptional cases

if column scan performance is not sufficient

Dictionary WHERE Name =

'Zsuwalski •

�

�

Value

Value ID

Rows

Row ID

Value 10

0

Baker

0

56756

0

4

1

Jones

1

345,

...

76876

�

i!'

Value 10 vector

ID

c

L!�

Inverted Index

772

Zsuwalski

.. c

:;;

...

.. .

772

3, 254621

...

...

•3

772

� 254621

772

�

-

�

...

...

Figure 425: HANA: Inverted Index on Column Store

Which SQL statements can I use to create a database index?


CREATE INDEX • A new database index is created.

DROP INDEX • An existing database index is deleted.


CREATE INDEX


�

204


2014




HA150

CREATE INDEX Access Path ON Table

CREATE

UNIQUE

(Column,

Column);

INDEX Access Path O N Table

(Column ,

Column);


+

To speed up the read access you can create an index on a table column.

+

The respective table can be empty or contain data.

:;

An index on column PlateNumber of table Car will be created:

CREATE

INDEX PlateNumberindex ON Car

(PlateNumber) ;

Figure 430: CREATE INDEX

+

You can create a UNIQUE

INDEX to ensure that the corresponding column

cannot contain duplicate values. +

Unlike PRIMARY KEY, UNIQUE

INDEX does not prohi bit NULL values.

+

A UNIQUE INDEX can only be created when the column contains

no

duplicate

values. 1'

In table car no duplicated plate numbers are allowed:

CREA'l'E UNIQUE INDEX PlateN\lmberindex

ON Car

(PlateNumber) ;

Figure 431: CREATE INDEX You can create multiple indexes on the same table. ';A single-column index is created on column Brand of table Car. .,A single-column index is created on column Color of the same table. CREATE INDEX Brandsindex ON Car

(Brand) ;

CREATE INDEX C o l o rsindex ON

Car

(Color);

Figure 432: CREATE INDEX You can create a multi-column index. l>Here a two-column index is created: CREATE INDEX BrandsColorsCombinationsindex

ON Car

(Brand,

Color) ;


2014


205 �




•

HA150

By applying a multi-column UNIQUE

INDEX, the relevant combination of

columns can be defined as key. +

In contrast to the PRIMARY KEY, UNIQUE INDEX does not prohibit NULL values in the key columns.

•

You can only create a multi-column UNIQUE INDEX, if the relevant column combination does not contain duplicates.

•

Here, the value uniqueness of Country and PfateNumber combinations is enforced:

CREATE UNIQUE INDEX CountcyPlateNumberCombiindex

ON Car EU

(Country,

PlateNumber) ;


DROP INDEX


DROP

INDEX Access

Path;


•

You can drop an existing index.

•

Only the access path (index) is deleted.

•

The underlying data of the Index is not deleted.

• DROP

INDEX does not syntactically distinguish between NON-UNIQUE and

UNIQUE indexes. DROP INDEX PlateNumberindex;

Figure 437: DROP INDEX

�


206


2014




HA150


2014

•

Access tables in other schemas (assuming suitable pennissions).

•


•

Explain \Vhen database indexes make sense even in HANA.

•


© 2014 SAP AG

or an

SAP


affilia

207 �



Unit Summary

HA150


�

208

•

Access tables in other sche1nas (assuming suitable pennissions).

•


•

Explain when database indexes make sense even in I-IANA.

•



2014







•

n1 Database Transactions Unit Overview

Unit Objectives After completing this unit, you will be able to: •

Explain what a database transaction is and why it is needed.

•

Explain the acronym ACID.

•

Finish database transactions in HANA using

•

Describe issues that arise if transactions are not mutually isolated.

•

Understand ho\v classical database systems synchronize concurrent

SQL statements.

transactions. •

Understand hov1 HANA synchronizes concurrent transactions.

Unit Contents Lesson: Database Transactions

2014

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.


.

.

.

.

.

.

.

.

.

.

.

.

210

209 �



Unit 11: Database Transactions

Lesson:

HA150

Database Transactions

Lesson Overview This lesson covers the necessity for database transactions and how SAP HANA treats them.

Lesson Objectives After completing thjs lesson, you will be able to: •

Explain what a database transaction is and \Vhy it is needed.

•


•

Finish database transactions in HANA using SQL statements.

•


•

Understand how classical database systems synchronize concurrent transactions. Understand how HANA synchronizes concurrent transactions.

•

Business Example Transaction

(TA)= Sequence of associated database

operations (SQL

statements) for which the ACID requirements must be met

.

All database operations always take place within transactions (in the extreme case, each operation has its own transaction).

A - Atomicity •

C

Consistency

I - Isolation D

- Durability

Figure 438: Database Transactions

�

210


2014



Lesson:

HA150


Atomicity (A): A transaction is either executed completely or not at all. For Example: Transfer from Account1 to Account2

Consistency (C): A transaction wli l bring the database from one consistent state to an(other) consistent state. Dur ing a transaction inconsiste_nt state_s are possible, for example account transfers or marriage registration.

Isolation (I): The database changes performed within a transaction shall only be visible to the outside after the completion of the transaction Important only for multi-user operations. Problematic If changes of canceled transactions can be seen.

Du rabi l i ty (0): If a transaction i s successfully completed (COMMIT), all changes from the transaction must permanently stay even in case of failures, or can be restored automatically. For example: Booking a deposit


The ACID requirements must be ensured by the DBMS (not trivial).

The DBMS must provide mechanisms/automation for: Logging & Recovery for A+ D Synchronization for I (such as locks) Integrity control for C

ACID requirements .cosr: With respect to the DBMS implementation effort With regard to performance at runtime Figure 440: Database Transactions

Which SQL statements can I use to start a transaction?


2014

© 2014 SAP AG or an SAP affiliate company. All r ights reserved.

211 �


HA150


SAP HANA does not provide a SQL statement to explicitly start a transaction. A transaction Is implicitly started, if the previous transaction was canceled or successfully completed (or there is no previous transaction)

AND one of the following SQL statements Is executed

SELECT INSERT UPDATE DELETE The transaction begins directly before the SQL statement, by which it is implicitly started.


Which SQL statements can I use to finish a transaction?


COMMIT + The currently running transaction should be completed successfully. + The dur ab i lity requirement must be ensured.

ROLLBACK + The currently running transaction should be canceled. + The atomicity requirement must be ens u red

.


�


212


2014


Lesson:

HA150

.. _,. �:r�; J

l ! l

..

...............

FROM Car;

INSERT INTO

Car(CarID)

VALUES

('FOl') ;

INSERT INTO

Car(CarID)

VALUES

('F02');

INTO Car(CarID)

VALUES

('F03') ;

�=�


CARIO

;

INSERT

INTO Car(CarID)

VALUES

('F04');

INSERT

INTO Car(CarID)

VALUES

('F05');

INSERT

INTO

Car(CarID)

VALUES

('F06');

INSERT

INTO Car(CarID)

VALUES

('F07');

FOS

INSERT

INTO Car(CarID)

VALUES

('F08');

F09

INSERT

INTO Car(CarID)

VALUES

('F09');

FOl F02 F03

. . .. ,...,..,.. ROLLBACK;

•tr1u1,,•.•tu1

4

F07

CO}.:f}dI Ti SELECT

Carro FROM Car;


�

Certain SQL statements

l

c

COMMIT ;

..

1 1 1

cause an implicit

DELETE FROM Car;

INSERT INTO Car(CarID)

VALUES

('FOl') ;

INSERT INTO Car(CarID)

VALUES

INSERT INTO

VALUES

(, F02 I ) ; (, F03 I ) ;

CREATE

-

..

COLUMN TABLE T(S

CARID

INT);

INSERT INTO

Car(CarID)

VALUES

INSERT INTO

Car(CarID)

VALUES

INTO Car(CarID)

VALUES

INSERT

_ ... . . . ..

Car(CarID)

MMIT

('F04') ; ( FOS I ) ; t

( I F06 I )

;

ROLLBACK;

-----

FOl F02 F03 F07

INSERT

INTO Car(CarID)

VALUES

( I F07 I ) ;

FOS

INSERT

INTO Car(CarID)

VALUES

('F08') ;

F09

INSERT

INTO Car(CarID)

VALUES

('F09') ;

.....,........ COMMIT ;

.t

SELECT

CarID FROM Cari


Multi-user operations are absolutely necessary - but not without problems

Figure 447: Multi-user Mode

2014


213 �



Unit 11: Da tab a se Transactions

HA150

Multi-user mode is absolutely necessary! If the transactions took place strictly in sequence one after another, this would be a huge waste of resources, since waiting times could not be used. Waiting for user input Waiting for disk access (for classical DBMS products) Waiting times caused by the application program Multiple transaction must run time-shared ("interlocked") When this happens in an uncontrolled manner (for example without synchronization), many problems can occur. The problems can be divided into 5 different error classes (see following slides) Figure 448: Multi-user Mode

Lost-update Problem +The changes of a transaction are overwritten by another transaction and therefore lost Point In time

Transaction 1

11

Read x

Transaction 2

Read x

t2 x := x + 5,000:

t3

x := x

t4

-

100;

Write x

t5

Wrti e x

16 Figure 449: Uncontrolled Multi-user Mode 1/5

Inconsistent Analyses +A transaction sees an apparent inconsistency, since it refers to two different consistent database states.

Point in time

Transaction 1

t1

-

Read marital statm; of Mandy

Transaction 2 -

(Result: "unmarried") Change marital status of Mandy

t2

to "married"

-

Change marital status of Thomas to "married"

t3

t4

Read marital status of Thomas (Result: "married")

Figure 450: Uncontrolled Multi-user Mode 2/5

�


214


2014



Lesson:

HA150


Phantom Problem (umalicious", as it cannot be prevented by row-locking) •A t ransaction sees an apparent inconsistency, since another transaction inserted a

new

row in

the meant ime

.

Tranuctlon 1

Point In time

Tranw11on 2

Read all SAP employees

t1 t2

Insert Mr Z as a new SAP employee

t3

Register Mr Z as course participant Read all course participants (Mr Z is a participant of an Internal course. though he apparently seems to be no SAP employee!)

t4


Non-repeatable read •During the same transaction different results are obtained for repeated reading of the same facts. Point In time

Transaction 1

11

Read salary of Mr A

Tranuc:tlon 2

Raise salary a Mr A

l2

Read salary of Mr A

13


Dependency on uncommitted changes (Dirty reads) •A transaction sees and uses a state that officially never existed.

Point in time

Transaction 1

t1

Raise salary to € 10,000,000.·

Transaction 2

t2

Read salary

t3

Donate € 5,000,000.·

t4

COMMIT; ROLLBACK; (transaction canceled)

15


2014

© 2014 SAP AG

or an


215 �




HA150

+To avoid the problems shown before, multi-user mode must b e controlled or synrchonized •

Synchronization = time based coordination of processes

•A common option for synchronization is the use of locking •

Locks are the most important (but not the

on ly) way

•

"Multi-version Concurrency Control" can also be used

•

Alter native ly, there is the "Optimistic Synchronization Method"

+The goal of synchronization is the logical single-user mode with physical multi-user mode. Figure 454: Synchronisation

What does logical single-user mode with physical multi-user mode mean? Figure 455: Logical Single-user Mode The user should have the impression of b eing the only active user of the database. •This goal canno t be rea ched to 100%.

• If access to a {currently locked) object takes longer, the users will suspect that they are not alone.

• If the transaction is aborted by the DBMS to resolve a deadlock, the users will suspect that they are not alone.

•Externally there should be the impression that the transactions a re strictly executed in series (that means one after another - Serializability criterion)

• I n reality: time-shared processing of several transactions •

But the impression of serial processing should be created

Figure 456: Logical Single-user Mode

What is the Serializability Criterion? Figure 457: Serializability

�


216


2014



Lesson:

HA150

• Serializability =


There is a serial execution order of the transactions

that has the same result •

Result in terms of database state or database content

+ Serializability is therefore a correctness criterion for the "correct" execution of the synchronization (that means for the "right" control of the multi-user mode) + Ensuring Serializability is the responsi bility of the DBMS (and not of the user or of the application program) + What Serializability means, you can best understand with reference to a concrete example ... (see following slides). Figure 458: Serializability

UPDATE Official SET overtime = overtime + 2

WHERE PNr = 'P07'; UPDATE Official SET overtime= overtime + 2

WHERE PNr = 'P07'; COMMIT;

Figure 459: Transaction 1 --Rounding to full ten

UPDATE Official SET overtime= ROUND(overtime, WHERE

SELECT

-1)

PNr = 'P07';

*

FROM Contact; COMMIT;

Figure 460: Transaction 2

UPDATE Official SET overtime= overtime

*

4

WHERE PNr = 'P07'; UPDATE Official

SET overtime= overtime I 2

WHERE PNr = 'P07'; COMMIT;

Figure 461: Transaction 3

2014


217 �



HA150


+TA1 increases the overtime value by 4

1a) o = o

+

2;

1b) 0 = 0 + 2; +TA2 rounds the overtime value to full ten

2a) o =

round(o);

2b) read access; +TA3 doubles the overtime value

3a) o = o * 4; 3b) 0 = 0 / 2; Figure 462: Effect of the 3 Transactions

•

TA1 increases the overtime value by 4

•

TA2 rounds the overtime value to full ten

•

TA3 doubles the overtime value

TA1

_,

22 .... 26

TA2-t TA3

....

30

....

60

TA1 --+ TA3-+ TA2

22 .... 26 .... 52 -+ 50

TA2 -> TA1

22- 20

_,

TA3

-+

24 - 48

TA2--+ TA3--+ TA1

22--+ 20 -+ 40

TA3 -t TA1-TA2

22--+44-t48-50

TA3--+ TA2 --+ TA1

22 --+ 44

-+

Initial value = 22

40

--+

--+

44

44

Correct result (according to Serializability): 44, 48, 50 und 60

Figure 463: Serial Executio n

�

Initial Value

22

1.

(1a)

0 = 0 + 2;

24

2.

(2a)

o = round(o);

20

3.

(2b)

read access;

20

4.

(3a)

0 =0. 4;

80

5.

(1b)

0 = 0 + 2;

82

6.

(3b)

0=012;

41

The interleaving shown above is incorrect, because the result

(41) does not corr espond to any value that can occur in serial execution (44, 48, 50 and 60) Figure 464: Incorrect Interleaving

�

218


201 4



Lesson: Database Transactions

HA150

�

Initial Value

22

1.

(3a)

0 = 0. 4;

88

2.

(2a)

o= round(o);

90 The interleaving shown here is

3.

(1a)

0=0 + 2;

92

4.

(3b)

0=012;

46

5.

(2b)

read access:

46

6.

(1b)

o=o+2;

48

correct, because the result (48) corresponds to one of the values that can occur by serial execution (44, 48, 50 and 60).

Figure 465: Correct Interleaving

What are Isolation Levels?

Figure 466: Multi-user Mode

Isolation Levels define ... ·

Whether rows that are read, inserted, updated, or deleted by a transaction are available to other transactions running in parallel.

·

Whether read, insert, update, or delete activities of another transaction running in parallel have an impact on the current transaction.

·

Which problems of uncontrolled multi-user mode may occur and which problems must b e prevented (by the DBMS)

Figure 467: Isolation Levels

2014


219 �




HA150

+ 4 isolation levels are defined by the SQL Standard in such a way that each level defines which problems may occur and which problems must be prevented by the DBMS.

Uncommitted

laoi.tton Lem

Dependency (Dirty Reada)

Non·raputllbl• Reada

Phllntom Problem

0

READ UNCOMMITIED

possible

possible

possible

1

READ COMMITIED

Impossible

possible

possible

2

REPEATABLE READ

Impossible

Impossible

possible

3

SERIALIZABLE

1mposs1ble

1mposs1ble

1mposs1ble


Which SQL statement can I use to define the isolation level?

Figure 469: Multi user Mode -

SET TRANSACTION

ISOLATION

LEVEL

+A certain isolation level is set. +You must set the isolation level before the transaction starts. •The isolation level is only valid for a single (i.e. next to run) transaction. +If different isolation levels are set before the transaction starts, only the last setting is decisive. +The isolation level "SERIALIZABLE" guarantees serializability. Figure 470: Multi user Mode -

SET TRANSACTION ISOLATION LEVEL

Figure 471: Multi user Mode -

SET TRANSACTION ISOLATION LEVEL IsolationLevel; Figure 472: Isolation Levels

�


220


2014




HA150

+ According to the SQL Standard isolation level SERIALIZABLE is the default. + In SAP HANA the isolation level READ COMITTED is the default. + It is not possible to set isolation level READ UNCOMMITED in SAP HANA. •

You can set the remaining three isolation levels as follows:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET

TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;


How do classical DBMS products synchronize the multi-user mode?

Figure 474: Synchronization • Classical

DBMS products usually use locking for multi-user mode synchron ization

.

• Database objects (schemata, tables, rows) are locked prior to any access (and therefore cannot or only in a limited way be used by ot her transactions). • Locks are set and released by the DBMS. • User or applications do not need to care of the locks (automation of the DBMS). Figure 475: Classical Synchronization

How does SAP HANA synchronize its multi-user mode?

Figure 476: Synchronization

2014


221 �



Unit 11:

HA150


The consistency of write access is ensured by X-locks The affec.ted rows are locked "as usual". The consistency of read access Is ensured by "Multi Version Concurrency Control" (MVCC) No S-locks are set when reading rows . By default, all SEL.ECT statements of the same transaction see the same (possibly obsolete) consistent version of the database state(= isolation level REPEATABLE READ). This version contains all changes that were committed before the start of the transaction (together with all changes done by the transaction itself). This Is called "transaction-level snapshot Isolation". Write access for other transactions running in parallel creates a new version of the database state (which is not visible to the transaction mentioned above.) Isolation level READ COMMITEO corresponds to statement-level snapshot Isolation

Figure 477: Synchronization in SAP HANA Timellne of 5 transactions T1-T5

e<>

ml

commI

accessing same record ,.item D" • T1 writes V1 of item O • T3 starts and writes V2. V1 still remains visible for IJ'ansactlons tha_t started before T3

ends.

'With .,transaction-level·' snapshot Isolation, T2 and T4 keep seeing V1

of Item O 1= repeatable read)

0 E

• TS starts after T3 Is committed 'seesV2

.. "'

• When T4 ends (last transactlon ttiat still se&s V1 ), V1 becomes obsolete

VI

opel'I

out.dated

Figure 478: Multi-Version Concurreny Control - An Example

Example illustrates that ,,transaction level isolation" is not exactly the same as isolation-level "repeatable read "in single-version concurrency control: In classical DBMSs, T2 and T4 would see V2 if they read it first after T3 is committed.

�

c omml

• Transaction-level snapshot Isolation can lead to updates a transaction can't see

'"..

"

.,. !

• Transaction-level snapshot isolation increases probability of conflicts on

.. c

application level

0 i! ..

Primary key & uniqueness

1! 0

l

..

;

1t

>

•

constraints are checked considering all existing versions

� .. i;;

c

�J

l

"O

• Deadlock situations cannot be prevented

Figure 479: Write Locks & Snapshot Isolation - Additional Remarks

222


2014




HA150

•

Explain what a database transaction Is and why It Is needed Explain the acronym ACID

·

Finish database transactions In HANA using SQL statements

. Describe Issues that arise If transactions are not mutually isolated •

•

Understand how classical database systems synchronize concurrent transactions Understand how HANA synchronizes concurrent transactions

Figure 480: Unit 11: Database Transactions Summary

Figure 481: Summary

Motivation And Basic Concepts Reading Data From A Table Or View Aggregating Data Reading Data From Multp i le Tables Changing Data Stored in Tables Defining How D ata Is Stored Using Views For Data Access Defining Data Access Understanding NULL Values Database Transactions Figure 482: Summary

II

II

II

Figure 483: Open Questions

2014


223 �




HA150

Figure 484: Donel

How can I access meta data?

Figure 485: Meta Data

+

The DBMS provides metadata views (catalog views). This is a requirement of the SQL standard.

+

;.

CONTACT

You can use SQL to read the meta data.

OFFICIAL OWNER

Which tables belong to schema Schema1?

SELECT Tab1e FROM

TABLE NAME

STOLEN OWNER EU

Name

CAR

Sys . Tab1es

WHERE Schema Name

=

CAR EU

' SCHEMAl ' ;

Figure 486: Accessing Meta Data ;.

Which columns are in table Car of schema Schema1? SELECT Co1umn_Narne,

Position,

Data_Type_Narne

FROM Sys.Table_Columns WHERE Schema

Name = 'SCHEMAl'

AND Tab1e Name

='CAR' ORDER BY Position; COLUMN NAME

POSITION

DATA TYPE NAME

-----------

--------

--------------

CARID

1

VARCHAR

PLATENUMBER

2

VARCHAR

BRAND

3

VARCHAR

COLOR

4

VARCHAR

HP

5

INTEGER

OWNER

6

Vl\RCHAR

Figure 487: Accessing Meta Data

�


224


2014



HA150

Lesson:

l>


How many columns do the tables of schema Schema1 have? SELECT Table_Name ,

COUNT(*)

AS NumColumns

FROM Sys.Tab1e_Co1umns WHERE

Schema Name = ' SCHEMAl '

GROUP BY Table Name ORDER BY 2 TABLE NAME

NtJMCOLUMNS

----------

----------

CONTACT

2

STOLEN

2

OWNER

4

OWNER

5

OWNER EU

5

CAR

6

CAR EU

7


l>

Which tables of schema Schema1 have the fewest columns?

SELECT Table N ame , COUNT(*) _

AS NumColumns

FROM Sys.Tab1e_Co1umns

WHERE

Schema Name = ' SCHEMA.1'

GROUP BY Table Name HAVING COUNT( *)

= (SELECT TOP 1 COUNT ( *)

FROM Sys.Tab1e_Co1umns WHERE

Schema Name = 'SCHEMAl'

GROUP BY Table Name ORDER BY 1);

TABLE NAME

NUMCOLUMNS

CONTACT

2

STOLEN

2


2014


225 �



HA150


,.

Which tables of schema Schema1 have the most columns?

COUNT(*)

SELECT Table_Name ,

AS NumColumns

FROM Sys.Table_Columns WHERE

Schema Name

=

'

SCHEMA1'

GROUP BY Table Name HAVING COUNT(*)

=

(SELECT TOP 1 COUNT(*) FROM Sys.Table_Columns WHERE

Schema Name = 'SCHEMA.1'

GROUP BY Table Name ORDER BY 1 DESC) ;

TABLE NAME

NUMCOLUMNS

CAR EU

7


Which meta data views are available?

Figure 491 : Meta Data • There are several meta data views

I

.•.

Meta Data View

Information contained

Sys.Tables

Which tables are available?

Sys.Table_Columns

Which columns are in the tables?

Sys.Views

Which views a r e available?

Sys.View_Columns

1

Which columns are in the views?

Sys.Indexes

Which indexes are available?

Sys.Index_Columns

Which columns are in the indexes?


�


226


2014



Lesson:

HA150




•


•

Finish database transactions in

•


•

Understand hov1 classical database systems synchronize concurrent

HANA using SQL statements.

transactions. •

2014

Understand ho\¥ HANA synchronizes concurrent transactions.


227 �



Unit Summary

HA150



•


•

Finish database transactions in HANA using SQL state1nents.

•


•

Understand how classical database systems synchronize concurrent transactions.

•

�

Understand how HANA synchronizes concurrent transactions.


228


2014







Course Summary

HA150

Course Summary You should now be able to: •

Explain basic concepts in the database world and the Relational Database Model.

•

Refresh and deepen SQL knowledge, especially for developing on HANA database systems.

Related Information •

2014

SAP HANA SOL Reference


229 �



Course Summary

�

HA150


230


2014



Feedback SAP AG has made every effort in the preparation of this course to ensure the accuracy and completeness of the materials. Jf you have any corrections or suggestions for improvement, please record them in the appropriate place in the course evaluation.

2014

© 2014 SAP AG or

an


231 �


SAP HA150

Recommend Documents