Developing SQL Databases

d

d

48

0.0 (0)

P. H.

Study

P. H.

About these flashcards

Diese Lernkarten bieten einen umfassenden Überblick über die Entwicklung von SQL-Datenbanken, geeignet für fortgeschrittene Nutzer. Sie decken Themen wie verschiedene Joins, Datentypen, Integritätsregeln und T-SQL-Befehle ab, wobei Schwerpunkte auf CTEs, Wildcards und Datenbankoperationen wie CRUD und ACID liegen. Praktiker profitieren von der detaillierten Erklärung von Befehlen wie OFFSET, FETCH und EXCEPT sowie von der Darstellung von Datentypen und deren Eigenschaften. Die Karteikarten helfen, komplexe SQL-Konzepte zu verstehen und effizient in der Datenbankentwicklung anzuwenden.

Flashcards

48

Students

4

Language

German

Category

Computer Science

Level

Other

Created / Updated

02.06.2018 / 21.03.2021

OLAP

Online Analytical Processing

Data Warehouse
Data Inserts are not efficient

CRUD

CREATE

READ

UPDATE

DELETE

ACID

Atomicity = all or nothing

Consistency = database goes from one consistent(valid) state to another

Isolation = transaction should happen independently from others at the same time

Durability = once commited it remains stable in the system

DDL

Data Definition Language

Statements that are used to create and modify the databases, tables and other objects.

(i.e: CREATE, ALTER, DROP, TRUNCATE)

DML

Data Modification Language

Statements that work with the Database Tables

(i.e: SELECT, INSERT, UPDATE, DELETE)

Wildcard Character: %

Any string of zero or more characters

Wildcard Character: _

Any single character

Wildcard Character: []

Any single character within the specified range

Wildcard Character: [^]

Any single character not within the specified range

T-SQL Command: INTERSECT

Returns distinct rows for values that are found in each table

T-SQL Command: EXCEPT

returns distinct rows for values that are not found in each table

T-SQL Command: FETCH

Limits the number of rows that are returned from the resultset

ORDER BY is required

T-SQL Command: OFFSET

Sets the number of rows to skip before returning the number of rows set using fetch

ORDER BY is required

(...)ORDER BY Name ASC
OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY

(INNER) JOIN

data from each table when common column appears in both tables

LEFT (OUTER) JOIN

All rows from left table even if joined columns don't match

no matching rows in right table are displayd with null values

RIGHT (OUTER) JOIN

All rows from right table even if joined columns don't match

no matching rows in left table are displayed with null values

FULL (OUTER) JOIN

returns all rows in both tables

none matching rows are shown with NULL values

CROSS JOIN

returns all rows from left table

each row is combined with all rows from right table (Cartesian Products)

Left:10 Rows And Right:10 Rows = 100 Rows (10*10=100)

CTE

Common Table Expression

A temporary result set that is created and available for use within the execution scope of certain T-SQL commands

Advantages of CTEs

Improved readability and eas in maintenance of complex queries
- the query can be divided into simple separate, logical building blocks
- these blocks can be used to build more complex interim CTEs

Which Categories for Datatypes exist for SQL Server?

Keyboard commands:

= turn,

= for-/backward,

= scroll

Exact numerics
Approximate numerics
Date and time
Character strings
Unicode character strings
Binary strings
Other data types

Exact numerics

Keyboard commands:

= turn,

= for-/backward,

= scroll

bigint
bit
decimal
money
numeric
smallint
smallmoney
tinyint

Approximate numerics

Keyboard commands:

= turn,

= for-/backward,

= scroll

float
real

Date and Time

Keyboard commands:

= turn,

= for-/backward,

= scroll

date
datetime2
datetime
datetimeoffset
smalldatetime
time

Character strings

Keyboard commands:

= turn,

= for-/backward,

= scroll

char
varchar
text

Unicode character strings

Keyboard commands:

= turn,

= for-/backward,

= scroll

nchar
nvarchar
ntext

Binary strings

Keyboard commands:

= turn,

= for-/backward,

= scroll

binary
varbinary
image

Other data types

Keyboard commands:

= turn,

= for-/backward,

= scroll

cursor
hierarchyid
sql_variant
Spatial Geometry Types
table
rowversion
uniqueidentifier
xml
Spatial Geography Types

bigint

Keyboard commands:

= turn,

= for-/backward,

= scroll

Category: Exact numerics

Storage: 8 bytes

Range: -2^63 to 2^63-1

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

9trio

int

Keyboard commands:

= turn,

= for-/backward,

= scroll

Category: Exact numerics

Storage: 4 bytes

Range: -2^31 to 2^31-1

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

2mia

smallint

Category: Exact numerics

Storage: 2 bytes

Range: -2^15 to 2^15-1

-32,768 to 32,767

tinyint

Category: Exact numerics

Storage: 1 byte

Range: 0 - 255

bit

Category: Exact numerics

Storage: 1bit (per 8 bit 1 bytes)

Range: 0/1

decimal / numeric

Category: Exact numerics

Storage: 5-17bytes

Range: 10^p (p max = 38)

-----

decimal(p,s)

p =precision (1-38) default 18

both sites of decimal

s = scale (0-p) default p

right side of decimal

money

Category: Exact numerics

Storage: 8 bytes

Range:

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

900bio

represents currency values

smallmoney

Category: Exact numerics

Storage:4 bytes

Range:

- 214,748.3648 to 214,748.3647

represents currency values

real / float(24)

Category: Approximate numerics

Storage:4 - 8bytes

Range:

-1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 depending on n

Depending on n

real = 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 (4Bytes)

float(n)

n =

1-24 = 7 digits (4Bytes)

25-53 = 15 digits (8bytes)

date

Category: Date and Time

Range: 0001-01-01 to 9999-12-31

Default literal: YYY-MM-DD

Default Value: 1900-01-01

Storage: 3Bytes

datetime

Category: Date and Time

Range: 1753-01-01 to 9999-12-31

Default Value: 1900-01-01

Storage: 8Bytes

Default Value: 1900-01-01 00:00:00

datetime2

Category: Date and Time

Date Range: 0001-01-01 to 9999-12-31

Time Range: 00:00:00 to 23:59:59.9999999

Default literal: YYYY-MM-DD hh:mm:ss[.fractional seconds]

Default Value: 1900-01-01 00:00:00

Storage: 6 bytes for precision less than 3; 7 bytes for precisions 3 and 4. All other precisions 8bytes

Accuracy: 100 nanoseconds default precision = 7 digits

1 / 48

Flashcards

T-SQL Command: OFFSET

Sets the number of rows to skip before returning the number of rows set using fetch

ORDER BY is required

(...)ORDER BY Name ASC
OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY

(INNER) JOIN

data from each table when common column appears in both tables

LEFT (OUTER) JOIN

All rows from left table even if joined columns don't match

no matching rows in right table are displayd with null values

RIGHT (OUTER) JOIN

All rows from right table even if joined columns don't match

no matching rows in left table are displayed with null values

FULL (OUTER) JOIN

returns all rows in both tables

none matching rows are shown with NULL values

CROSS JOIN

returns all rows from left table

each row is combined with all rows from right table (Cartesian Products)

Left:10 Rows And Right:10 Rows = 100 Rows (10*10=100)

CTE

Common Table Expression

A temporary result set that is created and available for use within the execution scope of certain T-SQL commands

Advantages of CTEs

Improved readability and eas in maintenance of complex queries
- the query can be divided into simple separate, logical building blocks
- these blocks can be used to build more complex interim CTEs

1
2

Study