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:

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

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.

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.

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.

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

the amazing thing is that it will show the path to heaven. sometime, if we are biased with data then it will take us to ……

Mathemetics, Economic, Statistics have given us enough models to grind the data to predict the future or finding facts.

there are many stories revolving around the data in this world. it has been picturized for the audience.

1. Moneyball – is a movie, a general manager, Billy Beane assembled a winning team by using an analytical and scientific approach.
2. Lover, Stalker, Killer: in this movie, police forensic IT team used the database to process and find the answers to the puzzling question using SQL.
3. Her – another movie that shows how effective a computer system connected with AI server to assist its users.
4. Margin Call – is another one that showed the world how not to use this sophisticated data tools.

many more movie like this. the point is data is everywhere and we are contributing our part one way or another to someone to take decision.

• keywords
• identifiers
• operators
• constants

keywords:

these are the special words that are already taken by the programming language for its own use. we should not use that name as our identifier.

Keywords: if, import, for and etc…..

Identifiers:

it is the name user defines to store and evaluate in the expressions. it is case sensitive in python. there is a set of rules to define identifier in python.

• it should not start with numbers. eg: 7name
• it should not be a keyword. eg: import

however, you can do the following.

• it can start or contains underscore. eg: _myself or my_self
• it can have numbers. eg: my_guess_6, OO7 (is it letter o), get2gether
• it is case sensitive. eg: cat and Cat

Operators:

operators are a symbol or word used to perform some operations. it can be an expression to compute or just assign a literal.

following are the classification of python operator.

• Arithmetic operators are +, -, *, /, **, //, %
• Assignment operators are =, +=, -=, /=, *%=, //= and with bitwise operators
• Comparison operators are ==, >=, <=, !=, <, >
• Logical operators are and, or, not
• Bitwise operators are &, |, ^, ~, <<, >>
• Identity operators are is, is not
• Membership operators are in, not in
• Concatenation operator +
• repetitive operator *

demo, learn by doing:

• a = 8
• b = 2
• a + b
• a – b
• a == b
• a & b
• ‘a’ * 3
• ((4+5) + -3 / 3)

use the python interactive shell for this experiment: https://www.python.org/shell/

expression precedence as follows

• P  ()
• E   **
• M  *
• D  /
• A +
• S -

Constants:

it is another way of defining a variable that never changes in the program. We must have come across the name constant in our daily lives Pi, it is not the one we like to eat. Instead, it is a mathematical constant for ratio of a circle’s circumference to its diameter.

you can define a constant like the one as given below. we must follow the identifier rules for constant as well.

PI = 3.1415

want to learn more. Learn about operator as a function.

Python is an interpreted language. It is a high level, Object-oriented programming language.

There are some basic to learn a programming language. It is vital to understand the following and it is same for all languages.

1. Language Syntax – to construct the program structure.
2. Variables – a name use to store a value.
3. Data Type – Type of the value to be stored in a variable or as a literal for computation.
4. Operators – symbols that represent operations to be performed.
5. Control Structure – Control structures are used to perform different actions in a program. such as, conditional flow, repetitive code block and catch exceptions.
6. Module – is a collection of subroutines that are essential or readily available for developer’s use instead of creating. for instance, print() is a standard function for output.

We will get to learn each item as given above. Learning the basic of a programming language basics gives you confidence to do more. Therefore, more you do will make you an expert.

There is a concept called 10K hours rule. If you practice a complex skill dedicatedly for 10K hours, you will become master in it.

Same goes for mastering a programming language.

Start your first day and wish you to reach the 10K hour.

Ref: https://www.python.org/

Pomodoro Technique is a time management method developed by Francesco Cirillo in the late 1980s.

If you can understand the clock, it will be simple to experiment.

Pick up a task, work for 25 minutes and take a 5 break and repeat it for 3 to 4 times and in the round take a longer break for 20 to 25 minutes. That is it. Key is to break the tasks into smaller pieces and pick them in order to accomplish it.

25 / 5 = Work / Break

25 / 5 = Work / Break

25 / 5 = Work / Break

25 / 20 = Work / Break

REPEAT, Numbers are represented in minutes.

Next is SMART technique, goal setting methodology.

S – specific

What do you want to do?

M – measurable

How much do you want do?

A – achievable

How will you complete it?

R – Relevant

How is it related to what you want to do?

T – TIimely

When will you be able to complete it?

Again, this can be done with breaking tasks into small pieces and applying this technique.

That is all for now.

Thanks.

Functional usage:

DECODE compares expression to each search value one by one.

If expression is equal to a search, then it returns the corresponding result.

If no match is found, then it returns the assigned default value.

If default is omitted, then it returns null.

In a simple words to remember, this function is a simplified form of nested IF-Then-Else or a expression based Case statement.

Syntax:

DECODE(expression, search, result [, search, result ]...  [, default ])

Example:

This example is to give you a basic understanding on the function decode in the SQL statement. This example has hard coded values to show directions. But, you can also use this function in PL/SQL program.

SELECT 
       DECODE (3, 1, 'East', 
                  2, 'West', 
                  3, 'North', 
                  4, 'South',
                     'You are here') "Direction" 
  FROM Dual;

Thanks for reading. You can write the comment and share the usage of this function in your code.