Database Notes

MySQL has been my database of choice for the web app projects I worked on. My fellowship experience has made me realize that my level of knowledge was limited due to the size of my data and the complexity of the queries I was using. With that, I have created this page in order to keep track of the new things I learn doing the various SQL exercises.

JOINS:
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

INNER: Returns records that have matching values in both tables

LEFT OUTER: Return all records from the left table, and the matched records from the right table (intersect)

RIGHT OUTER: Return all records from the right table, and the matched records from the left table (compliment of the intersection)

FULL OUTER (not allowed in MySQL, use LEFT & RIGHT with UNION)

http://cloudtobago.com/images/innerjoin.gif
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

 

Table: Person

Column Name
PersonId
FirstName
LastName
Type
int
varchar
varchar

PersonId is the primary key column for this table.


Table: Address

Column Name
AddressId
PersonId
City
State
Type
int
int
varchar
varchar

AddressId is the primary key column for this table.

Example question
Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

Output: FirstName, LastName, City, State

Note, We want to produce a report of PEOPLE with or without addresses. To accomplish this, we can use the LEFT JOIN. LEFT JOIN creates a table with ALL the data from the left table (A) and merges the information from the right table (B) that matches.

QUERY STATEMENT
SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address ON Person.PersionId = Address.PersonId

I’m excited!

I recently did a DevOpps fellowship at Insight. It was an intense and exhilarating experience. My reason for accepting the fellowship was so that I could learn about network infrastructure in the cloud. The world of data collection has drastically since my days as a system admin at a market research company. After suffering burnout I moved into doing technical support, training and web app programming. During all this time, I have never lost my love for infrastructure.

The fellowship gave me the opportunity to explore different areas of network infrastructure automation. Some of the areas included Continuous Integration/Deployment, Monitoring, Infrastructure as code. The DevOps fellowship took place at the same time as the Data Engineering fellowships so I was able to understand their role in the ecosystem of collecting and managing of very large datasets.

Part of the fellowship included a project that implements an area of DevOPs I am interested in. My project included monitoring of the network (Click here)

I am looking forward to gaining more Devops experience so I can become an expert.

Clean Code

The benefits of writing clean code:

Clean code helps minimize the time needed to spend reading and trying to understand the code. Messy code has the uncanny ability to slow down any developer
and make his work much harder. The messier the code is, the more time needed to understand it enough to work with.

Tips on writing clean code:

Make code readable for people

Use meaningful names for variables, functions and methods

Let every function or method perform only one task

Use comments for clarification

Be consistent

Review your code regularly

 See original article here

Steps to success

From an article I read on Datacamp:

1. Don’t get overwhelmed

Don’t let the new technologies and terminology overwhelm and intimidate you. It may seem like there is sooooo much you don’t know. As with everything, start st the beginning, and gradually build from there.

2. Make a Plan

Create a learning plan and have the discipline to stick to it. Dedicate at least an hour a day (any less may not be practical) to read/view something on what you trying to learn. Yeah, I know I know… just like with exercise, you don’t have an hour. Well then you don’t have time to learn

3. Be Consistent.

There’s a saying I heard my mother say: “one one cocoa does full basket”… If you do a little bit over time, it adds up.

4. Work at your own pace.

Don’t put any additional stress on yourself by setting unrealistic deadlines and goals. You’ll get frustrated and giveup.

5. Practice, Practice, Practice!

How does an artiste get to Carnegie Hall or become the best?? Nuff said!!

To the future

I recently returned to New York City after a little over 2 years in Tobago.  I must say, I was very naive going down there.  I was going as a change agent.  Change did happen, but not in the ways it was supposed to.  After 2 plus years  of frustration and going thru the five stages of grief, I am at the point of acceptance that things in Tobago are going to develop very slowly (if ever).  I will always love my island and be open to doing whatever I can to help its development.  In the meantime, life goes on….

Read more ›

Climate Change: Part 1… Rainfall:– Is it declining??

I recently did some of Coursera’s data science modules as way to sharpen my skills in big data. The modules use R to process the data and do the statistical analysis. My goal is to use this knowledge, I will try to analyze Tobago’s precipitation and temperature for the period 1980 to 2015. The data can be found at the Trinidad & Tobago Meteorological Service. This analysis will be in two parts: Rainfall trend and Temperature trends.

For the past few (10?) years Tobago have been experiencing rather hash drought like conditions. Each year, the Water and Sewerage Authority (WASA) has implemented its Water Supply and Conservation plans in order to ensure a supply. Some of these included water rationing, a ban on watering plants or washing cars with a hose.

As a farmer, I felt the effects of the heat and lack of rain fall during the wet/rainy season. I had to use pipe borne (via a tank) water almost everyday.  Fortunately, I was able to harvest my crops though I believe the yield would have been higher if the rain fall was “normal”.

The main reason WASA has given for the short fall in water is that rain fall has been steadily declining over the years.  While the public believe WASA did not plan for increase usage by the population. So which is true?  No planning for increased usage  or decrease rainfall over time. Hey, maybe both!.  WASA has not given any “numbers” to back up their assertion. While there has been a marked increase in usage due to a higher number of visitors to the island especially during July/Aug months.

So is WASA’s reason for the short fall true?

Read more ›

Tobago farming: Packaging House — Step into the future or another ‘white elephant’

So, It has been awhile (about 6 months)  since I thought about the new packing house in Tobago.  The packing plant is now officially open and so far it is not being used to its full capacity.

When I first heard about the packing house I wondered if there was enough crops grown on the island what would justify the money spent to build it. The justification for the packing house was that we (Tobago) need to meet various international standards. Meeting these standards they say, will help expand the market for our produce thru exports.  Also, locally the hope is that its use will raise the quality of crops grown on the island.

The division of Agriculture sponsored some workshops to raise the level of awareness of the packing house among farmers.  And I must say that I learned a lot. The only disappointment was the small number of farmers who attended.

My main concern about the packing house initiative is that it did not take into account that farming on the island is subsistence farming. Tobago farmers do not specialize in one or two  crops or plant large amount of crops which will  make it worth their while to use the  packing house. Another issue is Tobago’s culture of resisting change.  It is something I encountered upon my return home.  Talking with friends and others about problems on the island, I  hear the same refrain:  “change? That is not a word in Tobago people’s dictionary.

Gotta have a rationale

After speaking to my brother tonight (04/14) I realize I need to get my act together.  We were discussing an idea I have for a project and he sensed how unprepared I was. He advised that I document my thoughts and have a rationale for my project. I need to be able to articulate the why and I couldn’t.

The projects will be based on my daily experiences.  That is they will be technology based solutions to issues or problems encountered.  For example some projects I have in mind are based on my being a “farmer”: I would like an application that would allow me to track my expenses and income in such a way that I can make an informed decision as to which crops are most profitable with the least amount of work; another application is a virtual farmers market.  A farmer will enter information about produce or live stock he/she would like to sell.  Buyers can click on the item available to get more information as well a way to contact the farmer.

Another project I have in mind is the analysis of the rainfall and temperature of Tobago. I recently downloaded precipitation and temperature data (1980 to 2015) from the Met office website.  In keeping with my preference for using open source software, I will be using the R software to do the analysis.

So what’s my rational?  To find ways to use technology to make my life (and possible others) simpler anda bit more efficient.

Top