Saturday, September 14, 2019

CHAPTER 4: Microsoft SQL Sever

Section 1.8: UPDATE All Rows


A simple form of updating is incrementing all the values in a given field of the table. In order to do so, we need to define the field and the increment value

The following is an example that increments the Score field by 1 (in all rows):

UPDATE Scores
SET score = score + 1

This can be dangerous since you can corrupt your data if you accidentally make an UPDATE for a specific Row with an UPDATE for All rows in the table.

Section 1.9: TRUNCATE TABLE



TRUNCATE TABLE Helloworlds


This code will delete all the date from the table helloworlds. Truncate table is almost similar to Delete from Table code. The difference is that you ca not use where clauses with Truncate. Truncate table is considered better than delete bacause it uses less transaction log spaces.

Note that if an identity column exists, it is reset to the initial seed value (for example, auto-incremented ID will restart from 1). This lead to inconsistency if the identity columns is used a foreign key in another table.

Section 1.10: Retrieve Basic Server Information

SELECT @@VERSION

Returns the version of MS SQL Server running on the instance.

SELECT @@SERVERNAME

Returns the name of the MS SQL Server instance.

SELECT @@SERVICENAME

Returns the name of the Windows service MS SQL Server is running as.

SELECT serverproperty ('ComputerNamePhysicalNetBIOS') ;

Return the physical name of the machine where SQL Server is running. Useful to identify the node in a failover cluster.

SELECT * FROM fn_virtualservernodes();

In a failover cluster returns every node where SQL Server can run on. It returns nothing if not a cluster.

Section 1.11: Create new table and insert records from old table

SELECT * INTO NewTable FROM OldTable

Creates a new table with structure of old table and inserts all rows into the new table

Some Restrictions


     1. You cannot specify a table variable or table-valued parameter as the new table.
     2. You cannot use SELECT...INTO to create a partitioned table, even when the source table partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
     3.Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you can create them after executing the SELECT...INTO statement.
     4.Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order to the column in the new table. If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property.
     5. When a computer column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computer at the time SELECT...INTO was executed.

Saturday, August 31, 2019

CHAPTER 3: Microsoft SQL Sever

Section 1.3: UPDATE Specific Row

UPDATE HelloWorlds

SET HelloWOrld = 'HELLO WORLD!!!'
WHERE Id = 5

The above code updates the value of the field "HelloWorld" with HELLO WORLD!!!" for the record where "Id = 5" in HelloWOrlds table.

Note:In an update statement,It is advised to use a "where" clause to avoid updating the whole table unless and until your requirement is different.


Section 1.4: DELETE All Rows

DELETE

FROM Helloworlds

This will delete all the data from the table. The table will contain no rows after you run this code Unlike DROP TABLE, this preserves the table itself and its structure and you can continue to insert new rows into that table.

Another way to delete all rows in table is truncate it, as follow:

TRUNCATE TABLE Hellowords


Difference with DELETE operation are several:

     1.Truncate operation doesn't store in transaction log file
     2.If exist IDENTITY field, this will be reset
     3.TRUNCATE can be applied on whole table and no on part of it (instead with DELETE command you can associate a WHERE clause)


Restrictions Of TRUNCATE 
     
     1. Cannot TRUNCATE a table if there is a FOREIGN KEY reference
     2. If the table is participated in an INDEXED VEIW
     3. If the table is published by using TRANSACTIONAL REPLICATION OR MERGE REPLICATION
     4.It will not fire any TRIGGER defined in the table


Section 1.5: Comments in code

Transact-SQL supports two forms of comment writing. Comments are ignored by the database engine, and are meant for people to read.


Comments are preceded by -- and are ignored until a new line is encounterd:

-- This is a comment
SELECT *
FRON MyTable -- This is another comment
WHERE Id = 1;

Slash star comments begin with /* and end with */. All text between those delimiters is considered as a comment block.

/* This is a multi-line comment block. */
SELECT Id =1. [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

Slash star comments have the advantage of keeping the comment usable if the SQL Statement loses new line characters. This can hammpen when SQL is captured during troubleshooting.

Slash star comments can be nested and a starting /* inside a slash star comment needs to be ended with a */ to be valid. The following code will result in an error

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

The slash even though inside the quote is considered as the start of a comment. Hence it needs to be ended with another closing star slash. The correct way would be

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/   */


Section 1.6: PRINT 

Display a message to the output console. Using SQL Server Management Studio, this will be displayed in the messages tab, rather than the results tab:


PRINT 'Hello World!' ;
   
Section 1.7: Select rows that match a condition

Generally, the syntax is:


SELECT <column names>
FROM <table name>
WHERE <condition>

For example:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Conditions can be complex:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith AND (City = 'New York' or City = 'Los Angeles')

Tuesday, July 16, 2019

CHAPTER 2: Microsoft SQL Sever

CHAPTER 2. UNIONS/TABLE VARIABLES/SELECT all rows and columns from table

UNIONS

As we have seen before, a Join adds column from different table sources. But what if you want to combine rows from different sources? In this case you can use a UNION. Suppose you're planning a party and want to invite not only employees but also customers. Then you could run this query to do it:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

It will return names, addresses and cities from the employees and customers in one single table. Note that duplicate rows (if there should be any) are automatically eliminated (if you don't want this, use a UNION ALL instead). The column number, column names, oder and data type must match across all the select statements that are part of the union- this is why the first SELECT combines FirstName and LastName from Employee into ContactName.



TABLE VARIABLES

It can be useful, if you need to deal with temporary data (especially in a stored procedure), to use table variables;
The difference between a "real" table and a table variable is that it just exist in memory for temporary processing.

Example:
  DECLARE @Region TABLE
  (
  RegionID int,
  RegionDescription NChar (50)
  )

creates a table memory. In this case the @ prefix is mandatory because it is a variable. You can perform all DML , operations mentioned above to insert, delete and select rows
, e.g

  INSERT INTO @Region values (3,Northern)
  INSERT INTO @Region values (4,Southern)

But normally, you would populate it based on real table like

  INSERT INTO \@Region
  SELECT * FROM dbo.Region WHERE RegionID=2;

which would read the filtered values from the real table dbo.Region and inserting it into the memory table @region- where it can be used for further processing. For example, you could use it in a join like:

  SELECT * FROM Territories t
  JOIN @Region r on t.RegionID = r.RegionID

NOTE : Microsoft only recommends the use of the table variables if the number of rows of data in the table variable are less than 100. If you will be working  with larger amounts of data, use a temporary table or temp. table, instead.

SELECT ALL ROWS AND COLUMNS FROM A TABLE


Syntax:

 SELECT * FROM table_name


Using the asterisk operator serves a shortcut for selecting all the columns in the table. All rows will also be selected because this SELECT statement does not have a WHERE clause, to specify any filtering criteria. This would also work the same way if you added an alias to the table, for instance in this case:

  SELECT * FROM Employees AS e

or if you wanted to select all from a specific table you can use the alias +".*":

  SELECT e .*, d.DepartmentName
  FROM Employees AS e
  INNER JOIN Department AS d
  ON e.DepartmentID = d.DepartmentID

Database object may also be accessed using fully qualified names:

 SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

This is not necessarily recommended, as changing the server and/or database names would cause the queries using fully_qualified names to no longer execute due to invalid object names. Note that the fields before table_name can be omitted in many case if the queries are executed on a single server, database and schema, respectively. However, it is common for a database to have multiple schema and in these cases the schema name should not be omitted when possible.

Warning: Using SELECT * in production code or stored procedures can lead to problems later on (as new columns are added to the table, or if columns are rearranged in the table), especially if your code makes simple assumption about the order of columns returned. So it's safer to always explicitly specify column names in SELECT statements for production code.

  SELECT col1, col2, col3


  FROM table_name

Saturday, June 15, 2019

CHAPTER 1: Microsoft SQL Sever

Getting started with Microsoft SQL Sever

Microsoft SQL Server - is a Relational Database Management System. It use to store and retrieve data request by other applications.

Version                             Release Date
SQL SERVER 2017         2017-10-01
SQL SERVER 2016         2016-06-01
SQL SERVER 2014         2014-03-18
SQL SERVER 2012         2011-10-11
SQL SERVER 2008 R2  2010-04-01
SQL SERVER 2008         2008-08-06
SQL SERVER 2005         2005-11-01
SQL SERVER 2000         2000-11-01



CHAPTER1. INSERT/SELECT/UPDATE/DELETE: the basics of Data Manipulation Language

Date Manipulation Language (DML for short) includes operations such as INSERT, UPDATE AND DELETE:

  --Create a table HelloWorld
  CREATE TABLE HelloWorld(
  id INT IDENTITY,
  Description VARCHAR (1000)
  )

  --DML Operation INSERT, inserting a row into the table
  INSERT INTO HelloWorld(Description) VALUES('Hello World')

  --DML Operation SELECT, displaying the table
  SELECT * FROM HelloWorld

  --Select a specific column from table
  SELECT Description FROM HelloWorld

 --Select a specific column from table
 SELECT Description FROM HelloWorld

  --Display number of records in the table
  SELECT Count (*) FROM HelloWorld

  --DML Operation UPDATE, updating a specific row in the table
  UPDATE HelloWorld SET Description = 'Hello World!' WHERE Id=1

If the database table contains columns which are named like reserved word. e.g. DATE , you need to enclose the column name in brackets, like this:

-- descending order
SELECT TOP 10 (Date) FROM dbo.MyLogTable
ORDER BY (Date) DESC

The same applies if the column name contains spaces in its name (which not recommended). An alternative syntax is to use double quotes instead of square brackets,e.g:

-- descending order
SELECT TOP 10 "Date" from dbo.MyLogTable
where Userid='johndoe'
order by "Date"=desc

JOINS
Joins are useful if you want to query fields which don't exist in one single table, but in multiple tables. For example:

You want to query all columns from the Regions table in the Northwind database. But you notice that you require also the RegionDescription, which is stored in a different table, Region. However, there is a common key. RegionID which you can use to combine this information in a single query as follows (top 5 just return the first 5 rows, omit it to get all rows):

SELECT TOP 5 Territories.*,
  Regions.RegionDescription
FROM Territories
INNER JOIN Region
  ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription.



TABLE ALIASES

When your query requires a refference to two or more tables, you may find it useful to use a table Alias. Table aliases are shorthand references to a tables that can be used in place of a full table name, and can reduce typing and editing. The syntax for using an alias is:

<tablename> [as] <alias>

Where as in optional keyword. For example, the previous query can be rewritten as:

SELECT TOP 5 t.*,
  r.RegionDescription
FROM Territories t
INNER JOIN Region r
  ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Aliases must be unique for all tables in a query, even if you use the same table twice. For example, if your Employee table included a SupervisorId field, you can use this query to return an employee and his supervisor's name:

SELECT e.*,
  s.Name as SupervisorName --Rename the field for output
FROM Employee e
INNER JOIN Employee s
  ON e.supervisorId = s.EmployeeId
WHERE e.EmployeeId=111

By: Princess Joy Aubrey Belecina

Wednesday, May 1, 2019

NEM vs Ethereum vs Hyperledger

In my journey as one of the cadets in UnionBank’s Blockchain Institute, we have covered so far three potential platforms for designing and developing decentralized applications (dapps). I am going to tackle these platforms, namely NEM, Ethereum, and Hyperledger, as how they differ from each other and what are the strengths and weaknesses in terms of technology, usage and implementation.

NEM

The first platform introduced in Blockchain Institute is called NEM (pronounced as nem). It is a blockchain and cryptocurrency platform launched in 2015 that uses proof-of-importance (PoI) as its consensus algorithm. Unlike other platforms, POI checks the overall support of the user on the platform especially if one had huge investment in NEM. The platform’s main currency is called XEM which is valued at Php 3.14 at the time this is written but creating own currency or token called mosaics is possible.

Advantages:

Experimenting with the platform is quite easy. A user only needs to download NanoWallet, a browser-based application. This allows any user to create accounts, send or receive XEMs, create and manage mosaics, and enabling a feature called multisignatures. Multisig, for short, adds security to one’s NanoWallet by allowing a transaction to proceed only if certain amount of trusted people approve the transaction, just like a joint bank account.

Disadvantages:

While blockchain platforms are usually slow, NanoWallet always suffers from connection issues such as the ‘node’ needs to be changed every time a transaction is made in order to proceed. But the worst thing on this platform in terms of developing dapps is the documentation, or the lack thereof. While NEM has few of it discussing about its SDK, tools, and function calls, their documentations are somewhat unclear. Because of it, NEM is difficult to integrate in a web or mobile app to the point that in our hackathon, only few groups have properly implemented the platform in their projects. Adding to the injury is NEM’s lack of support from developer forum sites like StackOverflow, where bugs, error, or other problems are undocumented.

Ethereum

Second in our blockchain studies is Ethereum. Mostly known as a cryptocurrency in the (almost) same popularity as Bitcoin, Ethereum is also a blockchain development platform for dapps. It uses smart contracts written in its own programming language called Solidity. Smart contracts are where the functions of dapps are located once it is deployed in Ethereum’s blockchain network. Its consensus algorithm is the usual proof-of-work (PoW) where user is checked based on the number or length of work one has made on the platform. It uses a pricing mechanism for transactions called Gas for better resource allocation and in order to avoid spam. To avoid confusion, Ethereum is the platform while Ether (ETH) is the currency priced at a whopping Php 4,577.15 as of this writing.

Advantages:

Solidity has an online IDE called Remix, which means learning the language and playing with the platform requires no prerequisite software to be installed. But when one is ready to take developing dapps to the next level, there are several tools that can be used such as Metamask, Geth, Truffle and Ganache. In addition to that, unlike NEM, Ethereum app development is well-documented and has many support from developer forum sites, meaning searching for solution is a breeze.

Disadvantages:

One major issue of Solidity for me is there is still no stable version of the compiler. While I don’t mind it being in “beta”, the inconsistency of function calls and syntaxes between version updates is a bummer. For example, when I write a code using an online guide and I need to add functions on my own, that requires switching between many compiler versions to see whether it will compile or not. Until Solidity reaches a stable version 1.0, the reliability of the platform is in question.

Hyperledger

The platform we are currently playing with is Hyperledger. It is a collaboration of open-source blockchain platforms initiated by The Linux Foundation in 2016, with major global companies contributing to the project. Among the many frameworks and tools on the initiative, we focus specifically on the Hyperledger Fabric and Hyperledger Composer made by IBM and Digital Asset. Fabric is the framework where smart contracts are deployed, while Composer is a set of tools for developing smart contracts and dapps. Unlike NEM and Ethereum, Hyperledger is only a platform and does not have a cryptocurrency, nor charging fees for every transaction.
                          
Advantages:

The first thing I noticed when learning about Hyperledger is it has a very neat documentation compared to other platforms. More than guides, it offers free and comprehensive online courses through Cognitive Class, and it even has an online GUI called Playground for experimenting the platform.  For me, it is as expected from a well-known company like IBM.

Disadvantages:

While I am still in the process of studying Hyperleger, I cannot justify the fullness of the platform just yet. My two nitpick so far is 1.) it always shows connection problems and Docker issues while performing the lab activity in IBM Blockchain Foundation Developer online class. It is probably just a problem in my computer, so I still solve it as of the moment. Another is 2.) developing in it is resource-heavy, as it downloads files with enormous sizes (usually 100 to 200 MB per file) and activating tools such as Docker use significant amount of RAM and CPU power.

To wrap things up, all three platforms have its ups and downs so none of these are perfect. And for me, the rough sides are acceptable as these are relatively new, but once NEM, Ethereum and Hyperledger have matured enough in terms of performance and stability, it will become widespread across many industries across the world, including UnionBank’s blockchain initiative. With that, the power of blockchain is truly promising and I can’t wait to see what we can build with these in the future.

By Johnny Zarate

What You Need To Know About Ethereum and Hyperledger

In today’s financial space, blockchain is the talk of the town. Many companies are trying to incorporate blockchain technologies in their processes, thus the need for blockchain developers is increasing. Two of the most prominent blockchain technologies are Ethereum and Hyperledger. Before diving deeper into technical aspects of the two, there are some things that needs to be understood first.

Know what blockchain is

Imagine a database, but it has several copies across many computers and is encrypted using cryptographic keys. That is blockchain in a nutshell, as it stores data in blocks distributed in a network. Its first widely known use is in a cryptocurrency called Bitcoin, where every transaction is saved in a blockchain.

    Know the principles of blockchain
            
One of Blockchain’s key “selling” point is the use of consensus when submitting transactions, as data is verified by people in the network before a transaction enters into the blockchain. Another is immutability, meaning once a transaction is stored it can no longer be tampered or deleted. This is significant in the long run as this mechanism preserves records better than databases.

  Know the difference between a private and public blockchain

Let’s go with public blockchain first, as it was the initial idea of blockchain. Being public means virtually anyone across the world has access to the blockchain. Ethereum is public and is for general purpose such as cryptocurrency. One risk associated with this is high risk of being hacked by highly-programming-skilled criminals. To prevent this, transactions must be “paid” using cryptocurrency. In addition, Ethereum has a thing called “gas”, a second currency required so a transaction can go through.

Security concerns lead to the development of private blockchain. Instead of being worldwide accessible, transaction records are stored on a specific, limited network. This is where Hyperledger belongs, as it offers development tools for specifying own blockchain network. Private blockchains do not have cryptocurrency by default, but it can integrate cryptocurrencies from other platforms.

    Know the significance of blockchain in a use case

You might end up saying blockchain is cool, but this does not mean everything is “blockchainable”. Review and understand each aspects of a use case scenario. Once you have created an application design based on the scenario, think carefully if the principles of blockchain can benefit potential users of the application, or its disadvantages will make people “turn off”.

Friday, April 19, 2019

Things To Do In Tagaytay

If you are tired of the city and wanted to have a relax time near in the city, Tagaytay is one of the best for you. They are known for being near in the city but at the same time, you will experience being in the province. And not only that, Tagaytay is known for its cold weather unsual to the temperature in the whole Philippines. 

So now, I will share with you the things that you can do when you visit the small city of Tagaytay:

1. Tagaytay Day Tour

In this tour, it will give you a chance for a quick trip in the most popular spots in Tagaytay like Gingerbread House, Sonya´s Garden, Mahoogany Market, Good Shephers, Picnic Grove and more. As well as the breathtaking views of Taal lake and Taal Volcano. Ang ofcourse, try the local´s specialty called Bulalo made by cooking beef shanks and marrow bones.




Book your tour here.

2. Nature Wellness Spa

To fully experience a relaxation in Tagaytay, you should try the Nature Wellness Spa. Enjoy a massage or scrub. As well the spa´s distinctive brand of ecotherapy or healing by and through nature. 
Reserve here

3. Paradizoo

Another place to visit that is available for all ages is the Paradizoo. It is a 10 hectare land to discover nature and wildlife. They offer you an encounter with your favorite farm animals and get to feed them and flower gardens. 


Book here


4. Zoori at Residence Inn

Another zoo that you can visit is Zoori. Aside from animals, they offer you a scenic view of Taal Volcano while having fan with different animals. Animals that you can see are Serpentarium, Eagles Ridge, Aviary and zoo ground. Aside from all of it, you can also experience to go on an exhilarating scenic bike zip or cable car. 


Reserve now


5. Taal Private Hike

One of the trends right now is hiking. So Tagaytay city also offers a Taal Private Hike which allows you to hike in the smalles volcano in the Philippines. 


Book now!