Technology


8
Oct 15

SQL Beginners Cheat Sheet

So its been a while again, but push comes to shove and I’m determined to get back on to making a return to this blog. I’m on a bit of a skills development fix at the moment and decided here would be a great spot to report back on what I’ve been doing and to create some documentation on the key learnings.

I recently went through a beginners course on SQL through Code Academy (link to the course here) which I found really interesting and potentially useful for future endeavors.

SUMMARY 

What is SQL? Known as a ‘Standard Query Language’ its a method of organizing and summarizing data. My general thinking when it comes to this is that taking a structured dataset it allows the user to summarize and manipulate the data. In the case of multiple datasets, it connects them through a common tie so the user can get a better understanding of how they interact.

Why is it potentially useful? I’ve experienced SQL in the context of a data tool where the language organizes raw data in such a way that its  a readable output for the end-user. When thinking about it in terms of even a basic excel spreadsheet, the language allows for the data to be summarized and reformatted saving the user from having to do a lot of manual work.

Below is a select set of terms from the course and example code:

TERMS/COMMANDS:

; is used as an ‘End statement’ and is placed at the end of a command. It tells the system to move to the next query statement.

There are three basic functions of the SQL language: To BUILD, ANALYZE or JOIN data tables.

 BUILD:

CREATE TABLE: is a command telling the system to build a new data table.

table_name(***): labels a table for use in queries and joining with other tables.

column_1 data_type: defines what data goes into a particular column (can be text, date, number etc.) These can be layered up to create a series of columns

INSERT INTO: is a formatting command used for define the set columns. Following this, column titles can be added ex: (id, name, age).

VALUES: This is a command used to enter in individual entries. Its formatted as VALUES (row value 1, row value 2…);

UPDATE: used to change entries in a table. Using ‘SET’ and ‘WHERE’ entries, data within the table can be altered

ALTER TABLE: Where as UPDATE alters an entry, ALTER TABLE allows the user to change or add data columns.

DELETE FROM: Deletes rows from a table.

 

Example Code:

CREAT TABLE table_name (
 column_1 data_type,
 column_2 data_type,
 column_3 data_type
 );
INSERT INTO celebs (id, name, age)
VALUES (1, 'Justin Bieber', 21);
 SELECT *FROM celebs;
UPDATE celebs
 SET age = 22
 WHERE id= 1;
 SELECT *FROM celebs;
ALTER TABLE celebs ADD COLUMN
 twitter_handle TEXT;
 SELECT *FROM celebs;
DELETE FROM celebs WHERE twitter_handle IS NULL;

ANALYZE:

SELECT *FROM ‘table name': displays all data from the labeled table the SQL interface. The * is used to notate ‘select the whole table’. Other commands such as ‘SELECT DISTINCT’ can be used to summarize a table further based on a column or row entry.

BETWEEN: Is a filter option for the use of numerical data.

ORDER BY: organizes a data set. A related command DESC/ASC(aka: Descending/Ascending) can be used to organize numbers by a set parameter.

LIMIT: Sets maximum number of rows pulled from a dataset.

GROUP BY: organizes data by a specific column entry (example: type of apple).

COUNT: summarizes numerical data by a category (example: how many entries contain X)

SUM: Counts up the numerical data of a particular column in a table ( Ex: How many impressions in total).

MAX(): finds the largest value of a criteria.

MIN(): finds the smallest value of a criteria.

AVG(): finds the average of a criteria.

ROUND(): rounds decimal numbers to a specific integer.

EXAMPLE CODE: 

SELECT* FROM movies WHERE imdb_rating >8;
SELECT * FROM movies
 WHERE name LIKE 'Se_en';
SELECT *FROM movies
 ORDER BY imdb_rating DESC;

SELECT price, ROUND(AVG(downloads),2) FROM fake_apps GROUP BY price;

JOIN:

JOIN: creates a common column between two tables which joins the data set together (ex: id_actormovie JOIN ID_Actor)

ON: states the parameter of the join.

Inner Join: joins entries when a join criteria is met.

Left Join: Combines two tables together side by side and provides a NULL value when the join parameters are not met.

AS: When joining a table this allows columns of the joined table to be renamed without affecting the original unjoined tables.

EXAMPLE CODE: 

SELECT
albums.name AS 'Album', albums.year, artists.name AS 'Artist'
 FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year >1990

28
Jun 12

The Next Big Tech Battle: It’s Not About Single Devices Anymore

This week Google had its big I/O conference where it announced many of the new products it has coming out in the next few months. Two of the big surprises of the conference were the Nexus 7 ( a 7 inch tablet) and the  Nexus Q ( a cloud powered television media system). Most puzzling is that these products don’t particularly add anything to the Google’s core competency which is selling advertising but digging deeper you’ll see that this is part of a larger play to stay in the game against two other big rivals.

Android, IOS and soon Windows 8. What do they all have in common? Initially they were all platforms (or in Microsoft’s case based off a platform) for mobile phones that have since expanded into tablets and now television set top boxes. All three firms are making pretty heavy investments into products that most likely do not have high profit margins.  But in addition to this, products along all three of these company sets will be connected to a central content system where users can buy apps, video, audio and image (books/magazines) based content.

Ah ha! So essentially what the plan for these three is owning the media distribution system and making profit off of any royalties generated from the sale of media content. Where previously the war was over which computer operating system a person used or their search engine now the fight is over where they consume their content from. With content moving away from traditional sources such as cable providers, book stores and news-stands, being able to own an ecosystem that allows people to purchase media where ever they are is a big move with potential for a lot of profit.

Where will things go from here? So far Apple has the strongest foothold with iTunes entrenched as the first to launch. Can Microsoft and Google catch up being later into the game? Absolutely! Microsoft has the advantage of being a leader in the console gaming market which places them in a strong competitive position to Apple TV. As for Google, they are currently # 2 in the smartphone market using an open system where as long as it’s an Android powered device it will work with Google’s content distribution systems.  The big marketing move for all these players will be to get consumers to purchase into their whole line of products making them the primary content provider.

It will definitely be interesting to see where things go for these three firms and how competitive factors will come into play. Will cable providers and the big studios choose sides like in the fight for Blu-Ray vs. HD-DVD? Or will they  move to limit things all together? What will happen to single device manufacturing firms such as RIM ? With the technology industry moving so fast the next year or two will be ones to watch to see where the cards land.


25
Jul 11

Can We Use Measurement For Self Improvement?

Recently I read The Measured Life which took a look at group of people who call themselves “The Quantified Self” who track and measure many of the different actions in their daily lives. Reading this article  fit well with some initial thoughts I had about  what could potentially be done in self-measurement and reminded me of my recent (failed) attempt at a  personal measurement dashboard. Measuring what we do and our personal growth isn’t something that has been fully explored yet but is something that potentially could play a stronger role in our lives in the years to come.

Data Everywhere!

In our lives we create a lot of collectable data. Thinking about our external data sources there are data trails such as your banking records, what you purchase, how much you travel as well as usage data from a variety of electronics and apps you use daily. Additionally for yourself personally there are  things such as how you sleep, your mood , general health and physical activity levels that can all be measured in one way or another. Could bringing all this data together potentially give us a much deeper view into how we live our lives and give us a way to better optimize it?

Why Could This Data Be Useful?

The big advantage of any financial tracking application (example: Mint) is that you see where your money goes which  makes you much better equipped to spend your money more wisely. Additionally  knowing your purchase or usage habits lets you know what you should buy or replace on a more effective basis.

 

Could we all eventually have our own personal dashboard? Picture c/o Bill Rice

Use cases for measuring our lives could be things such as receiving an auto reminder when your car  or other devices should be serviced or seeing how often you purchase a particular item so you know the next time you should pick it up. In terms of tracking your life data it could potentially  be used in improving your health, gauging your personal satisfaction as well as let you weigh out potential decisions and opportunities.

But What About Fate?

Taking an opposing stance it could also be argued that knowing everything about ourselves could remove the serendipitous or by chance occurrences in life and limit us to our own predetermined paths. Could we be taking out some of the exciting aspects of life by putting a number towards our achievements and actions? I think this type of issue would come down to whether a person lets their life measures rule them or if they simply use  them as a resource to make generally better decisions.

What’s Coming Next?

If I had a crystal ball I would say that opportunities for personal measurement are going to pop up more frequently in the coming years. With the popularity of tools such as Klout  ,web applications like motivational app  Epic Win  as well as real life activity tracking tools like Nike + I think there is a strong desire for people to be able to quantify their actions so they can make a better judgments of their accomplishments as well as compare themselves against others.  With some creativity and good use of technology I think we could have some interesting applications to use in the years ahead.

 

What are your thoughts? Should we be measuring ourselves? How do you measure yourself already?

 

 

 


29
Jun 11

AMD moves beyond speed with new APU processors:

Computer processing speeds are becoming a less important factor in computing. With our increasing dependency on web based applications and the introduction of multi-core processing, having more speed is nice but on average probably isn’t fully utilized by consumers. Yesterday I attended the Canadian launch event for AMD’s new Fusion APU (Accelerated Processing Unit) chip which directly acknowledges this trend by instead taking stab at a better graphics experience with an integrated graphics core.

Previously if you wanted solid graphics for your computer or laptop you would need to ante up some additional money for a dedicated or what’s also known as a discrete graphics card. While current processors often contain integrated graphics it would share memory resources with the processor making it much less effective. Through AMD’s APU which has an integrated processor and dedicated graphics chip all in one this situation is changed. Requiring less components and having a smaller profile allows for the creation of a (potentially) more effective machine.

Getting a view of what this technology is already capable of through AMD’s APU line I feel there is room for additional potential, the potential of things getting even smaller. Currently netbooks and tablets are underserviced when it comes to graphics capability and end up doing just basic tasks. With the opportunity of putting in a smaller APU chip, these devices can have further capabilities. While at the launch I was able to see netbooks and tablets with this new integrated chip (being able to game on a netbook rawk!), going furtherI can definitely see the potential for even smaller devices. My thinking was, what if this chip could be  adapted for today’s smartphones and visual display devices? How much better could our graphical experiences be?

With computing the way it is currently you can only get so far by focusing on creating simply a faster chip. While in the past I may have overlooked AMD, I definitely feel that it’s great that they are thinking beyond focusing on chip speed and are considering instead how they can make the experience better. A big sales push for this line of APU chips will be occurring this summer and into the fall. We’ll have to see how this new product performs against its rivals.

 

As always feel free to contact me at either @kevrichard on twitter or kevin@kevrichard.com

 


12
Dec 10

Game Downloads: The Next Step in Console Gaming?

Preface: I just wanted to put some thoughts out on Edge Magazine’s article “Thinking Outside The Box” (found in their December issue) talking about how console game creators  are developing  more down-loadable  games vs. going through traditional channels. It’s been a topic that’s been on my mind for the past few weeks and now that I have the chance I’d like to put some thoughts on paper.  So here goes!

Like creating the next big blockbuster movie console based  video games take a lot of time and resources to make and have no guarantees in being successful. With consoles being more wired to the Internet the opportunity to sell down-loadable games has both large and small studios looking into ditching the disc and selling their content through the console itself.

Cutting out the “middle man”

The largest blockbuster games today (those that sell 1 million +copies) take years to make and millions of dollars to develop and market. For a long time, the success of these games  has shut smaller development studios out as publishers (those who package, distribute and do the marketing for games) look to sell high volume selling titles to see higher margins on their investments. Ever notice that much like the movie industry, the gaming industry is getting sequelitis?  This is the primary reason!

With game downloads, the publisher isn’t needed to distribute or package the game. Console download systems such as XBLA and PSN essentially cut out the middle man putting game studios in closer contact with gamers. This is great in that it allows more industry players to join in and sell smaller and less costly games. It could help to spur further innovation and creativity with creators trying to do more with less. Looking at the other side have you also seen Apple app store lately?  If gamers get more choice, this also means that games are competing for their spot on the list, perhaps fighting the same small studio vs. Big studio battle again (with exception of breakout hits which I may discuss this later).

The future?

Developing game media technology ( cartridges, game discs etc.) presents a cost to console developers, one that if they could do away with could let them focus on developing new ways for you to interact with your console and  to create more effective console hardware. Could this happen in the next generation of gaming? My (while amateurish) call would be no. Things like Internet bandwidth caps and the issue of piracy are still two big issues that need to be grappled with. Changing an industry model doesn’t necessarily happen overnight as well.  Big studios still enjoy a big lead and  currently have a strong formula of getting  games out to the masses through traditional retail outlets. Changing how these games are sold and packaged will definitely be a big step, one that’s going to need a lot more work.