UUID

Universally Unique Identifier (UUID) is a 128-bit label used for information in computer systems. This has standards for generating the identifier for uniqueness and independent code generation. Its existence since year 1980 with Appollo computer used in Network Computer System and later adapted by Open Software Foundation in Distributed Computing Environment.

There are other standards organizations has registered and make the identifier specification such as RFC, ITU and IETF.

UUID stands for Universal Unique Identifier defined by RFC 4122

Format:

Field	NDR Data Type	Octet #	Note
time_low	unsigned long	0-3	The low field of the timestamp.
time_mid	unsigned short	4-5	The middle field of the timestamp.
time_hi_and_version	unsigned short	6-7	The high field of the timestamp multiplexed with the version number.
clock_seq_hi_and_reserved	unsigned small	8	The high field of the clock sequence multiplexed with the variant.
clock_seq_low	unsigned small	9	The low field of the clock sequence.
node	character	10-15	The spatially unique node identifier.

how to use?

we can generate UUID in any standard programming language or general-purpose application software.

we are going to look into the following:

Database system:

Database – function – data type

• Oracle – sys_guid() – RAW(16)
• SQL Server – NEWID() – uniqueidentifier
• PostgreSQL – gen_random_uuid() – UUID
• MySQL – UUID() – BINARY(16)

Programming language:

Python:

The uuid module provides immutable UUID objects (the UUID class) and the following functions 

uuid1(), uuid3(), uuid4(), uuid5() 

are for generating version 1, 3, 4, and 5 UUIDs as specified in RFC 4122.

note: version 1 may have the privacy issue. please use these functions with full understanding.

acronyms:

• RFC – Request for Comment
• ITU – International Telecommunication Union
• IETF – Internet Engineering Task Force

Ref:

Universally unique identifier – Wikipedia

stackoverflow.com – uuid-in-python

ListAgg function – Oracle

List aggregation – LISTAGG()

It aggregates values from multiple rows into a single list.

Syntax:

LISTAGG(

Column [, Delimiter]

)

WITHIN GROUP (Order by Col_name)

  [Over(Partition by Col_name)]

This function comes handy when you are looking for an answer with concatenated strings.

Usage:

Select

ListAgg(department_name, ‘-‘) within group (order by

Depart_sort_no)

From department_master;

This query gives output of all department names in a – delimited list.

In some cases you have to deal with obtaining distinct values in the list.

Using the keyword distinct within the function gets the unique list.

ListAgg(distinct column_name)

Finally, how to deal with overflow. It means if the length of the string exceeds the standard data boundary. What should we do?

Let the system fail by throwing value exceed the length.

Do not worry, to do this there is an option in this function.

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

This option is the last part of ListAgg()

Usage:

ListAgg(col1, ‘delimiter’ on overflow truncate)

Listagg(col1, ‘delimiter’, on overflow truncate with count)

Listagg(col1, ‘delimiter’, on overflow truncate without count)

Filler – can be used to represent obvious continuous characters such as …

ListAgg(col1, ‘delimiter’ on overflow …)

That is it, learn by doing, happy coding.

SQL for everyone – Day 2

Joins

Database contains many tables to store different conceptual data.

Actually we need to see the concept join in the relational database management system. Entities are created with normalization rules.

Thus, data for a single domain span across multiple tables in a normalization data model. For instance, the train ticket booking system uses Trains, Passenger and trip tables. It is a normalized structure where train details, passengers and their trip information are stored separately with key references.

Table key columns

Train – TrainId

passenger – Pid

Trip – TrainId, Pid, fair

Lets look into the types of Joins

• Inner join
• Left outer join
• Right outer join
• Full outer join
• Cross join

Inner join

It joins two tables and returns the matching records.

Select a.column1, a.column2

From table a

Inner join table2 b

On a.key_column = b.key_column;

Here, a and b are alias names for the tables. Will be used for shorthand names in the query.

Left join

Left outer join combines two tables. Whereas, it returns all the rows in the left table and the rows match on both sides. Finally, you will get the null on the right side for the unmatched rows.

The idea is, if you want to know the records that are not present in the right table can be filtered using Nul value.

Select a.*

From table a

Left outer join Table b

On a.key_column = b.key_column

Where b.Key_column is null:

Hence, this query output contains records from the left table that are not in the right table.

Right join

Right join combines both sides and focus on the right side of the table. It is similar to the left join. However, it returns all rows from the right table with matched rows and null on the left for the records that are not matched.

Select b.*

From table a

Right outer join table b

On a.key_column = b.key_column

Where a.key_column is null;

Full join

Full outer join takes everything from both sides. It is the combination of left and right table together and leave nothing out. Whereas, the results have null on either side if there is no match.

Select a.*, b.*

From table a

Full out join table b

On a.key_column = b._key_column;

Cross join

Cross join is a Cartesian product. There is no need for key columns in the join clause. It does produce the output by joining each row with every combination between two tables

Select a.*, b.*

From table a

Cross join table b;

There are other joins available in different products along with ANSI join.

Those are, natural join, inner join using(), cross apply, outer apply().

Learn by doing. Good luck.

SQL for everyone – Day 1

It is a buzzword we have been hearing in the data world. We are accumulating humongous amounts of data every single day. However, without finding the useful information from the stored data, it is just a piece of word.

Databases are developed to store and retrieve when required. SQL is a declarative programming language for that sole purpose.

Structured Query Language (SQL) is language used to store and manage data in the RDBMS (Relational database management system).

it is best suitable to work with structured data such as tabular data structure. Similarly, it is also used in NoSQL and other applications as well.

SQL is a well standardized language by the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987 in the early time and it has been evolving since then.

it is adapted by different organizations and added extension programming language such as T-SQL (Microsoft SQL Server) and PL/SQL (Oracle).

SQL is an easy (Basics) to learn language. it has a set of statements to perform the following operations.

1. data query language (DQL) – reading data from a table.
2. data definition language (DDL) – creating, modifying or remove a table.
3. data control language (DCL) – Granting and revoking the access to a user.
4. data manipulation language (DML) – add, modify, delete records to a table.

To practice SQL statements, you need a SQL database engine. for simplicity you can use SQLite.

https://www.sqlite.org/index.html - Download and install this software in your computer.

Understand the SQL Statements!

data query language (DQL)

this statement is used to retrieve the data from a table (entity).

Syntax with clauses:

SELECT [TOP n rows] column1, column2, … FROM table_name

WHERE column1 condition

GROUP BY column1

HAVING column1

Example:

SELECT * from Cars;

Here Cars is an entity. It has tuples (records/rows) of attributes (columns)

data definition language (DDL)

It is used for defining the structure of the storage object Table.

The following statements belong to DDL

CREATE – to create an object, a table with attributes to store data of a domain

RENAME – is to rename the table

ALTER – is to add, rename and delete attributes

TRUNCATE – remove all records from the table

DROP – remove an object from the database

How to use DDL statements:

CREATE table_name

(

Column1 data_type,

Column2 data_type

);

Data control language (DCL)

As the name control implies, these statements are used to add or remove access for a particular object.

GRANT – Give access such as select, update, delete, alter and insert permission to a database object.

REVOKE – Takeaway granted access for a user object.

Usage:

GRANT select on [object name] to User name;

REVOKE select on [object name] from user name;

Data manipulation language (DML)

DML statements are for manipulating data in the database tables. You insert, update and delete records in a table.

Usage:

Insert into [table name]

(column1, column2)

Values (value1, value2);

Update [table name]

Set column1 = [value],

Column2 = [value];

Delete from [table name]

Where condition (if deleting selected records);

SQL has become one of the inevitable programming in the data world.

Python – Data types – Day 3

Data Types

We come across various types of day in the real world. Basically, we invented it to describe any unit of datum.

in the same way, programming languages are designed for various purposes and have different data types to process the users input.

For example, FORTRAN (Formula Translation) is a computer programming language that is used for scientific data processing.

COBOL (Common Business Oriented Language) – Programming language for business data processing.

these two notable legendary programming languages that have rich data types to handle various application data.

Likewise, Python is capable of handling different type of data for today’s modern applications.

1. numeric – integer, float, complex
2. boolean – values are True, False
3. set – {}, it is an unordered, unindexed and deduplicated data collection in a single variable.
4. mapping – {“key”:”value”}, Dictionary data store with key-value pair.
5. sequence – string, list is the collection storage of similar data type. tuple contains different types of data.

data types are grouped into mutable and immutable. mutable allows the program to update the value in the existing memory and immutable does not allow the program to update the value instead it created new memory and old memory will be given away then freed by garbage collection.

examples:

interger

a = 1

floating point

b = 1.5

complex number

c = complex(a + b)

boolean

f = True

set

A = {1,2,3,4,True, False, 4,3,2,1}

dictionary

d = {“name”:”dog”,”job”:”guard”}

sequence – can retrieve value using the index [1…n]

string

e = “elephant”

list

f = [‘f’,’u’,’n’]

tuple

g = (1,3,5,’odd’,’natural’, True)

you can view the output of the variables using print() function

Python interactive shell