SAP HA150

(1)

HA150

SQL

_{Basics for}

SAP HANA

Date Training Center Instructors Education Website Participant Handbook Course Version: 08

Course Duration: 2 Day(s) Material Number: 50125187

SAP HANA

(2)

Copyright

No part of this publication may be reproduced or transmitted in any form or for any purpose 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 registered trademarks of Adobe Systems Incorporated in the United States and other countries.

Apple, App Store, FaceTime, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered 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 registered trademarks of Citrix Systems Inc.

Computop is a registered 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, Google Checkout, Google Data API, Google Maps, Google Mob.ile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik, and Android are trademarks or registered trademarks of Google Inc.

HP is a registered trademark of the Hewlett-Packard Development Company L.P.

HTML, XML, XHTML, and W3C are trademarks, registered trademarks, or claimed as generic terms by the Massachusetts Institute of Technology (MJT), European Research Consortium for Informatics 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, Power Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETArN, DB2 Connect, RACF, Redbooks,

OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Lnformix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation.

l'vlicrosoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation.

INTERMEC is a registered trademark of lntem1ec Technologies 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 Holdings LLC.

Mozilla and Firefox and their logos are registered trademarks of the Mozilla Foundation. Novell and SUSE Linux Enterprise Server are registered trademarks of Novell Inc.

(3)

OpenText is a registered trademark ofOpenText 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 NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, Stream Work, 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.

(4)

(5)

About This Handbook

This handbook is intended to 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.

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

(6)

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

�

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

(7)

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.

(10)

Course Overview HA150

(11)

•

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

(12)

Unit 1: Motivation and Basic Concepts

Lesson: Motivation and Basic Concepts

Lesson Overview

HA150

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: • List some database models.

• Understand why learning SQL is important when dealing with SAP HANA. • Understand how the relation model and SQL are related.

• Understand the sample database used throughout the course. 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')<lt'l�"'""' c::"�tpm tr �) =specific database + DBMS

Figure 1: DBS = DB + DBMS

(13)

HA150 Lesson: Motivation and Basic Concepts User I Application ...

i

" ---...

DBMS

.,,. " � � �

DB

• • • • • : ' • I • • • • I

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

!

• I I '- � ____ ... _...' Figure 2: DBS = DB + DBMS

•,,What Database do you use?u _®

•.Which Database Management System do you use?"©

•.We are using _HANAas 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

•

(14)

�

Unit 1: Motivation and Basic Concepts HA150

4

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

Relational DBMS products (selection) • Oracle (since 1979)

• IBM 082 (since 1983) • SOL Server (since 1989)

• SAP HANA (since 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 responsibility 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 .,. l ApplicationlUser t �

External Schema 1 External Schema n External Level

oncep ua

Internal Schema

Figure 6: 3 Level Schema Architecture

Conceptual Level Internal Level

(15)

HA150 Lesson: Motivation and Basic Concepts

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 of the 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 of the 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?

(16)

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

c

AxBxC

Figure 9: Relations N = {l, 2, 3, 4, 5} N >< N = { (1,1), (l, 2) I (1,3) I (1, 4) I (1, 5) I (2,1), (2 I 2) I (2 I 3) I (2 I 4) I (2,5) I (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) }

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

s c N >< N s c _{ (l, l) I (1,2), (1, 3) I (1,4) I (l, 5) I (2 11) I (2, 2) I (2 I 3) I (2, 4) I (2, 5) I (3, 1) I (3,2), (3, 3) I (3,4) I (315) I (4,1), ( 4, 2) I (4, 3) I (4,4) I (4 ,5) I (5, 1) I (5,2) I (5,3) I (5,4) I (5, 5) } s - { (1, l) I (1,2) I (l, 3) I (1,4) I (l, 5) I (2 I 2) I (2, 3) I (2, 4) I (2,5) I (3,3) I (314) I (3,5), (4, 4) I (4,5), (5, 5) } (2, 3) es 2 s 3

Figure _11: Comparative Relations (=,-,<,=,>,=) 3 2 1 4 3 2 1

. I ! . .• -. ...

.

. ..

.

.... . ; j • � : . ·• . ... i : ; : t ...

.

... ...

.

..

...

.

....

.

. _

.

..

.

...

.

....

�

...

.

.. . ! : : 1 2 3 4 5 l . ... . .. ·-·• -. . : ....

.

... ., ... : : : ; ! ... . ... . : j : ; : ... _ • 1 .

.

....

.

...

.

....

.

: � : 2 3 4 5 HA150 2014

(17)

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

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

P•ral11JP�•r Name H-ri�g"fear

{ (P036407,Ben, 2001), (P036407,Paul,2001J, (P035607,Raja,2001), (P040824,Ben, 2001), (P040824,Pau1,2001), (P040824,Raja,2001), (P052867,Ben, 2001), (P052867,Pau1,2001), (P052867,Raja,2001), ... } Figure 12: Relations (P036407 ,Ben, 2008), CP036407,Paul,2008), (P036407,Raja,2008), (P040824,Bcn, 2008) , (P040824,Paul,2008), (P040824,Raja,2008), (P052867,Ben, 2008), (P052867,Paul,2008), (P052867,Raja,2008),

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

(P036407,Ben, 2010), (P036407,Pau1,2010), (P036407,Raja,2010), (P040824,Ben, 2010), (P040824,Paul,2010), (P040824,Raja,2010), (P052867,Ben, 2010), (P052867,Paul,2010), (P052867,R.aja,2010), Employee � ( (P036407,Ben, 2001), (P036407,Pau1,2001), (P035607,Raja,2001), {P040824,Ben, 2001), (P040824,Pau1,2001), {P040824,R.aja,2001), (P052867,Ben, 2001), (P052867,Paul,2001), (P052867,Raja,2001), (P036407,Bcn, 2008), (P036407,Paul,2008), (P036407,Raja,2008), (P040824,Ben, 2008), (P040824,Paul,2008), (P040824,Raja,2008), (P052867,Ben, 2008), (P052867,Paul,2008), (P052867,Raja,2008), (P036407 ,Ben, 2010), (P036407,Pau1,2010), (P036407,Raja,2010), (P040824,Ben, 2010), (P040824 ,Pau1,2010), (P040824,Raja,2010), (P052867 ,Ben, 2010) , (P052867,Pau1,2010), (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), (P040824 ,een,2008), (P0528S7 ,Raja,2010)}

Employee Pers Number Name Hiring Year

P036407

_Paul

2001

P040824 Ben 2008

P052867

_Raja

2010

(18)

�

8

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

• SQL

Tuple variables and quantifiers

Standardized and used in practice

Why SQL is important?

Why it is worth working with SQL

Figure 16: Why SQL?

(19)

In almost every business application I scenario, the data is managed using

database systems.

The most significant are database

systems based on the relati onal data model and using SQL (Structured Query Language) as a database 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

(20)

10

Different application architectures and development models are

possible with SAP HANA

HANA as Accelerator (secondary DB)

Aggregatlon Levels

Trad itional DBMS Figure 19: Why SQL?

HANA _{as primary Database} (for AS ABAP)

E.g. CRM on HANA - NW 7.40

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

(21)

Figure 21: SQL

• SQL is standardized

SQL Features

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!

(22)

12

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

•"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

j

1nternational Organization for standardization (ISO) does SQL standard (ISO standard) 1989 SQL·89 ("SQL 1")

J

SQL-92 ("SQL2'1, 3 attenuation levels: Entry Level, Intermediate Level, Full Level

-1992

1.999 SQL1999 ("SQL3")

2003 I SQL:2003 ("SQL4"), "SQUXML:2006" 2006 official standard part of SQL:2003 2008 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)

• Th_elanguage 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

(23)

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 Ben 2008

P052867 Raia 2010

n

Employee = {(P036407,Paul,2001), (P040824,Ben,2008), (P052867,Raja,2010)}

(24)

Unit 1: Motivation and Basic Concepts HA150 14 Name Pa� Raja-Be A-P030407 Hiring Year P040824 (P040824,Ben,2008) • P052867

Figure 28: Table rows as points in space

Name Pa� Raja-Be A-P036407 Projection list - _;, SELECT

f

_�;��_-_{�·,,.···�}�;

�

��;

1

... � .. FROM Employee WHERE PersNumber = 'P040824'; HlringYear (P030824,Ben,2008) • • (P040824, Ben) P040824 P052867

Figure 29: Projection on the plane

Projectlo� ,!,��,_.,,

=:?

SELECT

�

_{\::: ..}

Name

_._._..._.._._...

�

.. Name Pa� FROM Employee WHERE PersNumber = 'P040824'; Hiring Year Raja-Be P036407 P040824

Figure 30: Projection on an axis

(P040824, Ben, 2008)

•

P052867

(25)

What are the components of a

(database) table?

Figure _31: Basic Concepts

Tahle 111. me Primary Key Column Name

�

!

1 I

Employee Pers Number Name HiringYear

P036407 Paul 2001

P040824 Ben 2008

Table _Row P052867 _Raja 2010 _tt

""

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 Hiring Year

e _P036407 _Paul ₂₀₀₁

P040824 Ben 2008

P052867 Raja 2010

(26)

A key can consist of multiple columns

Employee Name Countrll Citll BuildingNr Block Floor Room SeatNr

16 MrA MsB MsC . . . DE DE DE ... WD F WD F WD F ... 03 03 03 . . . A A A ... ,. 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

Employee

'Num

b

e;

' � P000815 P004711 P012345 . " N

a

m

e

. " ... . " ... riax

ID

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

�ssport

�

(!;

P

la

t

e

�

_e,as

_i

_s

ID

r

Number � II.Ir " . HD-MM 815 ... .. . HD-ML ... 4711 . . . HD-012345 ... ... ... ... 5 29 5 29 5 29 ... ...

�License

ID

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

, 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

1 2 3 .. .

�IF

T

... " . " . ...

IBA

_.,.,,.,

�

. . . " . " . ... 2014

(27)

HA150 _{Lesson: Motivation and Basic Concepts}

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! _{Foreign-key Relationship}

�

I Emolove D Numbe Name DeoartmentN _I_Deoartme DNJ Function

e 0010000 MrA Afl1 _nt _A01

HANA-0010001 MsB A01 _A02 ... Est

0010002 MrC A02 _A03 _{HANA-Trainina}

0010003 Ms D A02 _A04

ABAP-0010004 MrE A02 _Development

0010005 MsF A03

Figure 38: Foreign-Keys

A Foreign-Key can consist of multiple columns

---

_"-.

I Publication PallerlD Topic "Uni Student/D

�

Student _�Uni _�ludi:nt1D'Name FirstName

P001 _ln-Memorv _HPI 1234₅ _HPI 12345 _A _B

P002 _Column _HPI ₁₂₃₄₅ _HPI 77777 _c _D

St_ore FSU 12345 _E _F

P003 Row Store HPI 77777 FSU 77777 G _H

P004 SQL& FSU 12345 XML P005 XML& FSU 12345 SQL P006 DB FSU 77777 Recovery

This example shows 1 _{Foreign-Key built on} 2 _columns

Figure 39: Foreign-Keys

There can be multiple Foreign-Keys

r �

�

\

I Vendor VlD Name

I

Delivery _'iJQ_flJL_Ym Quantity Product flir Description

L1 _Heidelberg _l1 _A1 ₂₀₁₀ ₃₂₀ A1 _Sticky

Paper _L₁ _A1 ₂₀₁₁ ₅₇₀ Notes

L2 _{Wiesloch Paoer}

L1 A1 2012 925 A2 Printing

L3 Walldorf Paper _l1 _A2 ₂₀₁₂ ₁₀₂ Paper

... ... L2 A1 2011 577 A3 Envelopes

(28)

18

The foreign-key can refer to its own table

r

�

I _Emolove ONumbe Name DNumberManaaer

e 0016000 MrA 0010007 0010001 MsB 0010007 0010002 MrC 0010003 MsO 0010004 0010004 MrE 0010002 0010005 MsF 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.

(29)

Official.(PNr, Name, overtime, Salary, Man.ager)

V\

Contact(PersNumber, QJimerID)

...----;:

.... =��

Owner(Own•rro. Name, Birthday, City)

Car(CarID, Pl

,

teNmnber, Brand, Color, HP, OWner)

Stolen(PlateNumber, reported_at) Figure 43: Example: Registration Office

I

Official PNr Name Overtime Salary

P01 MrA 10 AD9 P02 MrB 10 A10 P03 MsC 20 AD9 P04 MsD NUU. A12 P05 MrE 10 A08 P06 MrF 18 A09 P07 MsG 22 A11 P08 MsH NUU. A13 P09 Mrl NULL A14 Figure 44: Officials Manager P04 P04 P04 P09 P08 P08 P08 P09 NULL

(30)

I

Owner OwnerlD Name Birthday

H01 MsT 20.06.1934 H02 MsU 11.05.1966 H03 SAP AG NULL H04 HDMAG NULL H05 MrV 21.04.1952 HOS MsW 01.06.1957 H07 IKEA NULL H08 MrX 30.08.1986 H09 MsY 10.02.1986 H10 MrZ 03.02.1986 Figure 45: Owner

Contact PersNumber OwnerlD

P01 H03 P01 H04 P01 H07 P04 H03 P04 H04 P08 H04 P08 H07 P09 H03 Figure 46: Contact

�

20

HA150 City Wiesloch Hockenheim Walldorf Heidelberg Leimen Wiesloch Walldorf Walldorf Sinsheim Laden burg 2014

(31)

I car _� PtateHumber Brand Color HP OWner

F01 _{HO-V 106} Fiat _r9d ₇₅ _H06

F02 Hl). VW 4711 WV _black 120 _H03

F03 HO-JA 19n _BMW _bll_e 18' _H03

F04 HO-AL 1002 Mercedes white 136 _H07

F05 HO-MM3206 _Mercedes black 170 _H03

F06 HO.WV 1999 _A_udi •ellow ₂₆₀ _H05

F07 HD-ML3206 Audi blue 118 H03 F08 H[).tK 1002 WV black ₁₆₀ _H07 F09 HO.UP 13 Skoda led 105 H02 F10 HO-MT607 _B_M_W black ₁₄₀ H04 F11 HD-MM 208 BMW areen 184 H02 F12 HD-XY ₄₇₁₁ _S_k_oda _r_ed ₁₀₅ H04 F13 HO-IK ₁₀₀₁ _Re_nault rad 136 H07 F14 HD-MM 19n _Me_r_cedes _wh_it_e ₁₇₀ H03 F15 HD-MB 3000 Skoda black ₁₃₆ H03

F16 NULL _Ooe_t _aree_n ₁₂₀ NULL

F17 HD-Y333 _Au_d_i _""'"°' 184 H09

F18 HD-MO 2006 _Rena_u_lt _r_ed ₉₀ _H03

F19 HD-VW2012 WV black ₁₂₅ 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,

(32)

I

Ownef'_EU _CQUO!r::i owa�!D Name Birthday City

D H01 MsT 20.06.1934 Wtesl och

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 MsW 01.06.1957 Wtesloch

D H07 IKEA NULL Walldorf

D H08 MrX 30.08.1986 Walldorf D H09 MsY 10.02.1986 Sinshelm D H10 MrZ 03.02.1986 Laden burg A H01 MsO 21.05.1977 Wien A H02 MrP 02.08.1977 Salzburg E H01 Sel\or Q 18.02.1925 Madrid E H02 Scliora R 27.02.1927 Barce lona

Figure 50: Owner (EU-wide)

I Car EU Car1D PlaleNumMr Brand COior HP Count� Own•r

FGI HO.V 106 Rat � 75 ₀ H06

FG2 HO-WI 4711 vw bl•ck 120 0 H03 FOO HO-JA 1972 BMW blue 18'1 ₀ HOO

F� HO.Ill. 1 ()()2 Mercede!f wtlle 136 ₀ _H₀₇

F05 H0.1111\A 3206 M«eodos black 170 ₀ _H₀₃

F06 H().\MI 1999 A<ldl _�_"' 2SO ₀ H05

F07 MO·l•IL 3:1C6 Aud blue 116 ₀ _H03 Fila HD-IK 1002 vw black 160 ₀ _H₀₇ F09 HO.UP 13 _SI<- r<d 105 0 H02 F10 H0.₁.0 507 BMW black 1<!0 ₀ H� Fii HO.MM 208 BMW _"'"" 18'1 D H02 F12 MO.XV •711 � red 105 ₀ H� F13 HO.IK 1001 R•...,Ul red 136 ₀ H07 Fl• H0.1\AllA 1977 tAerce-des wrilt 170 D H03

F15 HO.MB '.!000 Skoda black IJ6 0 H03

F16 NUl.L Ooel -· 120 NULL NUl.L

F17 HO.Y333 Aud -�· 18'1 ₀ _H₀₉

F18 �.102006 Reoeut red 90 ₀ _H03

F19 Hl).\MI 2012 vw black 125 ₀ _H01

F2tl NUl.L - _..._. 18'1 NULL NUtL

F21 W.:302Ml ,Aef'Cedes black ₁₇₀ _A _H01

F22 S.215 MM ••u ,;1�� 18'1 A �

F23 UOOEMM - blue 116 _E _H01

F2-4 3206MlM _WJ _b_l_-a_c_l< ₁₇₀ _E _M₀₂

Figure 51: Cars (EU-wide)

(33)

Lesson Summary You should now be able to:

• List some database models.

• Understand "vhy learning SQL is important when dealing with SAP HANA. • Understand ho\v the relation model and SQL are related.

(34)

Unit Summary

�

24

Unit Summary

You should now be able to:

• List so1ne database models.

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

HA150

• Understand 'vhy learning SQL is important when dealing with SAP HANA. • Understand how the relation model and SQL are related.

• Understand the sample database used throughout the course.

(35)

(36)

(37)

•

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 . . . ... . . . 26 Exercise 1 : Exercise 1 ... . . . 59

(38)

�

Unit 2: Reading Data From a Table Or View

Lesson: Reading Data from a Table or View

26

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:

• 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 \VHERE 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.

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

HA150

(39)

HA150 Lesso_n_: _Re_{ading Data from a Table or View}

The SELECT statement

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.;

Figure 57: SELECT Clause

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

(40)

�

28

You can specify multiple columns in the projection list:

SELECT PNr, Name, Salary PNR NAME

FROM Official; POl Mr A P02 Mr B P03 Ms C P04 Ms D POS Mr E P06 Mr F P07 Ms G P08 Ms H P09 Mr I

The sequence of the columns In the projection list Is relevant:

SELECT PNr, Name SELECT Name, PNr

FROM Official; 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

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 POl POl P02 P02 P03 P03 1?04 P04 1?05 P05 1?06 P06 1?07 P07 P08 P08 1?09 P09 HA150 Salary ---A09 AlO A09 A.12 A08 A09 All Al3 Al4 PNR POl P02 P03 P04 P05 P06 1?07 P08 P09 2014

(41)

HA150 Lesson: _{Reading Data from a Table or View}

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

P04 Ms 0 _? Al2 P09

Columns can be specified in the projection list In addition to the asterisk (*):

We do not recommend using this option

SELECT PNr, *

I Name

FROM Official;

PNR PNR NAME OVERTIME SALARY MANAGER NAME

--- ---

---POl ---POl Mr A 10 A09 P04 Mr A

P02 P02 Mr B 10 AlO P04 Mr B

P03 P03 Ms c 20 A09 P04 Ms c P04 P04 Ms D ? A12 P09 Ms D

The asterisk (*) can be used multiple times In the projection list:

We do not recommend using this option

SELECT * * I *

FROM Official;

.... - OYlll.TDC ... , """"""' ... ... <MlllTUlll ... ... ""' UH& OVDTDll: SALA.IV --_ ... ••1 .... 10 ... ... ••1 M> A 10 ••• • •• ro1 ... 10 ... •O• H2 ... 10 ... ... H2 H:l • 10 IUO ••• ••2 .. I 10 ... ro• ... ... c 20 - ... ... •• c 20 ... ••• •OJ ... c 20 ... •O• "' .... • .,, ... ••• "'. ' A12 .. . ••• .... • IU2 • •• ... .. . 10 - ... ... ... 10 ... ... ... .. . 10 ... rot ... .... 11 - ... ... "' r lt ... . .. ... "'r 11 ... • •• . ., .... " lW ... . ., ... 22 IUI ... . .. .... n IUl ••• ... .... ' AU ... . .. .. . ' .,. ... .. . ... " • ... •o• ... ... ' ' ... • ... H>t • .. . • ... ... ' • ... '

(42)

30

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 Today Today Today Today Mr A Mr B Ms c Ms D Mr E has been has been has been has been has been

assigned to assigned to assigned to assigned to assigned to salary salary salary salary salary group group group group group A09 A10 A09 A12 AOB

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'.

--- ---

---The working week of Mr A amounts to 40 hours.

The working week of Mr B amounts to 40 hours.

The working week of Ms c amounts to 40 hours. The working week of Ms D amounts to 40 hours.

The working week of Mr E amounts to 40 hours. The working week of Mr F amounts to 40 hours.

The working week of Ms G amounts to 40 hours.

The working week of Ms H amounts to 40 hours.

The working week of Mr I amounts to 40 hours.

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! HA150 2014

(43)

HA150 Lesson: _{Reading Data}_from_aTable _orView

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

SELECT 'Good Morning!'

FROM Dummy;

The "DUMMY" table contains one column and one

row:

SELECT *

FROM Dummy;

SELECT a, 'b', FROM 11411;

11c11 _I ₁

I t 2 I I II 3 II

a Existing column named "A" ("A" as capital letter)

'Good Morning!' Good Morning! Dm+1Y x A 'b' c 1 '2' 3 b 1 2 b -· 1 2 b - 1 2 -'b' .. c" 1 • • II "

Artificial _resultcolumn _withstring _{"b" as val}ue In each r<:IN

Exisbng column _named·c· ("c" a s _lowercase letter)

Artificial result column _with1 as numeric value in each r<:IN

Artificial result column _withstnng _{"2" as}value in each r<:IN

Existing column named •3•

" " _Existing_tablenamed •4•

The projection list can have computed columns.

If a _NULLvalue is used in an arithmetic ope_rat_i_on, the result is also a NULL

value:

SELECT Name, overtime * 60

FROM Official; NAME OVERTIME*60

Mr A 600 Mr B 600 Ms C 1200 Ms D ? Mr E 600 Mr F 1080

(44)

Unit 2: Reading Data From a Table Or View HA150

�

32

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 10 0 Ms C 20 0 M s D ? ? Mr E 10 0 Mr F 18 0 Ms G 22 0 Ms H ? ? Mr I ? ?

You can expllcltly name computed result columns:

SELECT Name, overtime * 60 AS OVerrninutes FROM Official; NAME OVERMINUTES ---Mr A 600 Mr B 600 Ms c 1200 Ms D ?

Quotation marks are required, If the result column name should Include

lowercase letters:

SELECT Name, overtime * 60 AS 110verrninutes11 FROM Official; NAME Overminutes ---Mr A 600 Mr B 600 Ms c 1200 Ms D ?

(45)

HA150 Lesson: Reading Data from a Table or View

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 ?

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

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

(46)

�

34

You can use existing column names for (re)naming of result columns:

SELECT PNr Salary, Salary PNr SALARY PNR

---FROM Official; _POl _A09

P02 AlO P03 A09 P04 Al2 POS AOB P06 A09 P07 All POB Al3 P09 Al4

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;

• You can use functions in the projection list.

XYZ XYZ XYZ

POl Mr A A09 P02 Mr B AlO P03 Ms C A09 P04 Ms D A12

• 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 2014

(47)

� When were vehicle owners first allowed to drive a car In Germany?

SELECT Name, ADD_YEARS(Birthday, 18)

AS "18th Birthday"

FROM Owner;

Figure 79: SELECT Clause Function calls can be nested

>On which day is the owner's 181h birthday?

SELECT Name, DAYNAME ( ADD YEARS (Birthday, ROUND(ABS(-18.2)) ) ) AS Weekday 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 NAME 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?

(48)

36

SAP HANA provides following functions (selection):

Function Explanation

nAR(Oate) year

ADD_YEIARS(Date, n) n years later

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)

LENGTH (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 by adding the table name: SELECT Official.Name

FROM Official;

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

SELECT Official.* FROM Official; NAME Mr A Mr B Ms C Ms D Mr E Mr F Ms G Ms H Mr I

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

(49)

HA150 _{Lesson: Reading Data from a Table or View}

The projection list can include a mix of qualified and unqualified column names:

NAME

SELECT Name, Official.PNr

Mr A FROM Official; Mr B M.s c Ms 0 Mr E Mr F Ms G Ma H Mr I Figure 85: SELECT Clause

Using Tuple Variables, also known as Table Aliases

Figure 86: Tuple Variables Or Table Aliases You can use tuple variables in the projection list.

PNR POl P02 P03 P04 P05 P06 P07 P08 P09

Tuple variables are defined in the FROM clause and also referred to as table aliases in SAP HANA (and most other DBMSs).

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

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:}

NAME Mr A Mr B Ms C Ms 0 Mr E Mr F Ms G Ms H Mr I NAME Mr A Mr B Ms C Ms 0 Mr B PNR POl P02 P03 P04 P05 P06 P07 POB P09 PNR POl P02 P03 P04 P05

(50)

�

38

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?

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'

l

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

END AS Rating FROM Car;

(51)

HA150 Lesson: _{Reading Data from a Table or View}

�

CARm �Ell BRAND COLOR Hl' ONN&ll. RA'l'INQ

--- --- ---

---1'01 HD-V 106 !'i.at red 75 H06 low

ro2 llD-VW 4711 vw black uo H03 mediwa

!'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 -iua

1'06 llD-VW 1999 Audi yellow 260 KOS biqb

ro? HD-ML 3206 Audi 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 - black 140 804 mediua

I'll HD-tti 208 - 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 qroen 120 ? medium

!'17 HD-Y 333 Audi oranqe 184 H09 biqb

!'18 HD-� 2006 Renault red 90 H03 low

!'19 HD-VW 2012 vw blilck 125 KOl medium

!'20 ? Audi qreen 184 ? bi9b

�

CARID COLOR

---

--If a case d_ifferentiation does not correspond to any !'01 !'FOOOO

of the listed cases, a NULL value _isreturned: 1'02 000000

!'03 0000!'!' 1'04 !'M'FF!'

SELECT CarID, _!'05 000000

CASE Color !'06 l'!'!TOO

WHEN 'red' THEN 1FF00001 1'07 0000!'!'

1'08 000000

WHEN •green' THEN 100FF001 _1'09 _rroooo

WHEN 'yellow' THEN 1 tttt'OO I !'10 000000

WHEN 'blue' THEN '0000FF' !'11 OOFFOO

1'12 ITOOOO

WHEN •white' THEN 'FFFFFF' !'13 ITOOOO

WHEN 'black' THEN •000000• 1'14 Fl'FF!'!'

END AS Color 1'15 _1'16 _001'1'00000000

FROM Car; "" 1'17 ?

!'18 FFOOOO

!'19 000000

!'20 OOFFOO

(52)

Unit 2: Reading Data From _{a Table Or View}

�

40

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 ---1'01 HD-V 106 1'02 HD-VW 4711 1'03 HD-J.ll. 1972 1'04 HD-AL 1002 1'05 HD-MM 3206 1'06 HD-VW 1999 1'07 HD-ML 3206 1'08 HD-l'.K 1002 1'09 HD-UP 13 1'10 HD-Mr 507 I'll HD-MM 208 1'12 HD-XY 4711 1'13 HD-l'.K 1001 11'14 HD-MM 1977 1'15 HD-MB 3030

1'16 Th.e car is not registered!

l'l. 7 HD-Y 333

1'18 llD-MQ 2006

1'19 HD-VW 2012

1'20 The Car is not xeqistered!

BMND COLOR --- ---Fiat red Volkswagen blaclc - blue Mez:cedes-Be.nz White Mercedes-Benz black Audi yellow Audi blue Volkswagen black Skoda red - black - green Skoda red Renault red Mercedes-Benz White Skoda black opel green Audi orange Renault red Volkswagen black Audi _green SELECT DISTINCT

Figure 96: Duplicate Elimination

HP 75 l.20 184 136 170 260 116 160 105 140 184 105 136 170 136 120 184 90 125 184 HA150 2014

(53)

A table with a primary key does not contain duplicates SELECT * FROM Car; CARIO -- ---F04 FOS F09 Fl4 FlS PLATENUMBBR --- --HO-AL 1002 HO-MM 3206 HO-UP 13 HO-MM 1977 HO-MB 3030

BRAND -- ---Morcedos Mercedes Skoda MGrcedes Skoda COLOR HP OWNER --- -- - - -white 136 HO? black 170 H03 red 105 H02 white 170 H03 black 136 H03

Duplicates can occur when a key column Is not Included In the projection

list:

SELECT Brand FROM Car;

If duplicate elimination Is not intended,

you can specify the keyword ALL:

SELECT ALL Brand FROM Car;

BRAND Mercedes Mercedes Skoda Mercedes Skoda BRAND Mercedes Mercedes Skoda Mercedes Skoda

(54)

�

42

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

SELECT DISTINCT Brand FROM Car;

BRAND Fiat vw BMW Mercedes Audi Skoda Renault Opel

NULL values are treated in duplicate elimination as "normal" values

The result table contains (at most) one row that consists entirely of NULL values:

SELECT DISTINCT overtime FROM Official;

The "duplicate" term always refers to full result rows:

SELECT Brand, Color FROM Car; OVERTIME BRAND 10 20 ? 18 22 ---Mercedes Mercedes COLOR ---white black

Duplicates -c Skoda red

Skoda red

Mercedes white

Skoda black

(55)

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

SELECT DISTINCT Brand, Color FROM Car;

BRAND --- -Mercedes Mercedes Skoda Skoda COLOR --- --white black red black

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 Her cede a wtiite 136 K07

ros HD-I+! 3206 MN-cede a blaclc 170 H03

rot HD-UP 13 Sl<oda red 105 K02

ru HD-I+! 1977 He.rcedea Wbite 170 H03

ris HD-MB 3030 Sl<oda blaclc 136 H03

ORDER BY Figure 105: Sorting

(56)

44

The result table can be sorted by a specific column:

SELECT Brand, Color FROM Car

ORDER BY Brand;

Figure 106: Sorting

A descending sorting is possible:

ORDER BY Brand DESC;

Figure 107: Sorting

HA150 BRAND COLOR --- ---Audi yellow Aucli blue 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 --- ---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 2014

(57)

HA150 _{Lesson: Reading Data from a Table or View}

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

FROM _Car

ORDER BY Brand ASC;

Figure 108: Sorting

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

SELECT Brand, Color FROM Car ORDER BY PlateNumber; Figure 109: Sorting llRAND ---AUdJ. AudJ. AUdJ. AudJ. llMR llMR llHFI Fiat Merced as ?4ercede.s t4arcedas Opel Renaul.t Renaul.t Skoda Skoda Skoda Vl'J Vl'J vw l!JRAND --- -Opel Audi Renault Hercede• vw -Skoda Audi Her cedes -Her cede a Renauit -Skoda riat Audi vw vw Skoda AU di COLOR ---y"1l.ow blU& orange green blue black green red white black white green red red red red black black black black COLOR --- -qreen qreen red white black blue black blue Wbite qreen black red black red red yellow black black red orange

(58)

46

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

FROM Car

ORDER BY Brand ASC, Color DESC;

Figure 110: Sorting

Instead of the column names in the ORDER BY clause,

the column numbers (based on the projection list) can be used:

ORDER BY 1 ASC, 2 DESC;

Figure 111: Sorting

HA150 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 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 2014

(59)

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

SELECT Brand AS Manufacturer, Color

FROM Car

ORDER BY Manufacturer ASC, Color DESC;

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, Color

FROM Car

ORDER BY Brand ASC, Color DESC;

Figure 113: Sorting M1.NUl'AC'!Ul\ER ---Audi Audi Audi Audi -riat Hercedea Her cede a MOreodea Opel Renault Renault Skoda Skoda Skoda vw vw vw Hl\NU!'AC'l'ORER ---Audi Audi Audi Audl. -Fiat Hereedca Mercedes Her cede• Opel Renault Renault Skoda Skoda Skoda vw vw vw COLOR ---yel.l.ow orange green blue green blue blaclc red White white blaclc green red red red red black blaclc black black COLOR ---yellow ora09e green blue green blue blaclc red white White blaclc green red red red red blaclc blaclc black black

(60)

�

48

You can sort based on calculated values. CARm BRAND HP

--- -;.Sorting criteria: How much Is the power below 200 kW?l!'06 _Audi 260

SELECT CarID, Brand, HP

FROM Car

ORDER BY 200 - HP / 1.36 ASC;

Figure 114: Sorting

You can reference functions in the ORDER BY clause:

SELECT Name, Birthday

FROM Owner 1'03 I'll !'17 !'20 1'05 Fl4 1'09 FlO 1'04 !'13 !'15 1'19 !'02 1'16 1'07 1'09 Fl2 1'19 !'01 - 194 - 184 Audi 184 Audi 184 Mezcedea 170 Mercedes 170 vw 160 - 140 Merced ea 136 Renault 136 Skoda 136 vw 125 vw 120 opel 120 Audi 116 Skoda 105 Skoda 105 Renault 9 0 Fiat 75

ORDER BY YEAR(Birthday) DESC, Name ASC;

Figure 115: Sorting 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 ?

How many rows are in the result set?

Figure 116: Top-n Clause