SQL – Learning about the basic “SELECT” statement

I’m still doing my Stanford online course on relational databases. Today, I learned about the basics of SQL, a special programming language designed for managing data held in a relational database or from stream processing in a .

The teacher of the class, Jennifer Widom, kicked off the class by talking about the difference between a Data Definition Language (‘DDL’) and a Data Manipulation Language (‘DML’):

Data Definition Language (‘DDL’)

  • Create a table in the database
  • Drop a table from the database

Data Manipulation Language (‘DML’)

  • Query the database -> “Select” statement
  • Modify the database -> “Insert”, “Alert” or “Update” statement

Jennifer then told us about the Basic “Select” statement (see Fig. 1 below), explaining that the result of such a statement is to return a relation with a set of data attributes. For example, when you take a simple college admissions database as a starting point where there are 3 relations, each relation having its own set of unique attributes:

  • College ( College Name, State and Enrollment)
  • Student (Student ID, Student Name, GPA and Size High School)
  • Apply (Student ID, College Name, Major and Decisions)

Jennifer then gave us the following examples:

Query involving a single relation

select sID, sName, GPA

from Student

where > 3.6

This query will give you the name and student IDs of those applicants with a GPA higher than 3.6.

Query combining two relations

select sName, Major

from Student, Apply

where Student.sID = Apply.sID

This query will give you data on the names and student IDs for those students applied, filtered by Major.

Jennifer pointed out that SQL is a multi-set model and it therefore allows duplicates. You can eliminate duplicate values by adding the keyword “distinct” to your query. Jennifer also mentioned that SQL is an unordered model which means that you can sort results.

You can include an “order by” clause in your query and add “descending” to order the results of your query:

where Apply.sID = Student.sID

and Apply.cName = College.cName

order by GPA desc, Enrollment;

Main learning point: I found this class about creating a basic “select” statement particularly helpful, as it helped me to get a better understanding of how basic SQL queries are constructed.

Fig. 1 – Elements of the basic “Select” statement in SQL – Taken from: http://www.w3resource.com/sql/sql-syntax.php


Select SQL

App review: Meerkat

The other day is saw a discussion about whether Meerkat will or won’t last. Meerkat is a simple video app which lets people stream live to their Twitters. It launched about two weeks ago and has been talked about (and used) a lot since. Let’s do a quick review of the app:

  1. How did the app come to my attention? – Simple. My wife told me about Meerkat about a week ago. I also came across the app on ProductHunt.
  2. My quick summary of the app (before using it) – This app lets me stream live to my Twitter follows.
  3. How does the app explain itself in the first minute? – The first time I open the app, there’s a screen that introduces Meerkat’s ‘rules of conduct’, explaining that “Everything that happens on Meerkat, happens on Meerkat” and thus making it clear that my Meerkat recordings will be shared on Twitter (see Fig. 1 below).
  4. How does the app explain itself in the first minute – The Meerkat login screen says “Tweet Live Video”, which clearly suggests that I’ll be able to tweet live video streams. At the top of my personalised screen I see a text field which says “Write what’s happening …” with two big calls to action – “schedule” and “stream” – underneath (see Fig. 2 below). I’m not quite clear about what will happen when I write something in the text box, or what to expect when I click on “schedule” or “stream”. Nor am I clear on why certain posts appear under the “upcoming” header; I’ve got three upcoming streams from Index Ventures in there, but I don’t understand where these posts have come from. Are they based on Twitter accounts that I follow or are they just placeholders to deal with an initial ‘cold start’ problem? Also, I know I’m not a designer but the light grey font used for the “upcoming” header doesn’t work particularly well against a dark grey background in my opinion.
  5. Getting started, what’s the process like – I type in “Playing with Meerkat” (see Fig. 3 below) and then click on “schedule” to put in a time that works for me (see Fig. 4 below). Et voila, a tweet announces my live stream and off we go (see Fig. 5 below).
  6. How easy to use was the app? – Fairly easy. I guess I personally could have done with a bit more to better understand how Meerkat works and perhaps see some examples of other live streams. For people like me who don’t do video that frequently or who are who conscious of the things they share on Twitter, a bit more context on the app would be helpful. For instance, I can see on the Meerkat leaderboard that Nir Eyal, who I know and trust, is an avid Meerkat user (see Fig. 6 below). It would be good to see some of Nir’s video streams directly from the app.
  7. How does the app compare to similar apps?Qik, which is now part of Skype, and Periscope, which is currently in private Beta are similar to Meerkat in a sense that enable live video streaming from a multitude of devices. It will be interesting to see what Periscope will look like when it goes live and to learn how easy to use the app is in comparison to Meerkat.
  8. Did the app deliver on my expectations? – Yes. The app is simple – perhaps a bit too simple in places – and does exactly what it says on the tin, nothing more and nothing less.

Main learning point: It will be interesting to see what Meerkat’s usage is like once the current hype has subsided and once competitors like Periscope have entered the fray. The app is easy to use, but I feel it could yet do more in terms of its explanatory interface and enabling users to discover content. Considering that this is only the first release of Meerkat, it feels like a good and effective product.

Fig. 1 – Screenshot of the Meerkat screen which introduces the Rules of Meerkat

Meerkat 1


Fig. 2 – Screenshot of my personalised screen on Meerkat 

Meerkat 2

Fig. 3 – Screenshot of my personalised screen on Meerkat after I’ve typed in something in the free text field

Meerkat A

Fig. 4 – Scheduling my live video stream via the Meerkat app

Meerkat 5

Fig. 5 – Screenshot of my tweet announcing my live video stream on Meerkat to my Twitter followers

Meerkat B

Fig. 6 – Screenshot of the leaderboard on the Meerkat app 

Meerkat C

Related links for further learning:

  1. http://quibb.com/links/on-meerkat-and-why-it-won-t-last
  2. http://www.theverge.com/2015/3/9/8164893/meerkat-live-video-streaming-twitter-yevvo-periscope
  3. http://www.producthunt.com/posts/meerkat
  4. http://www.wsj.com/articles/twitter-acquires-live-video-streaming-startup-periscope-1425938498
  5. http://hunterwalk.com/2015/03/14/meerkat-the-value-of-slow-graphs/

What is Guerrilla Testing?

I don’t know how many of you are familiar with “guerrilla testing”, but I’ve been doing it for a few years now and it’s a great way to get some quick user feedback on your product or idea.

To me, guerrilla testing means going into a coffeeshop or another public place to ask people there about your product or prototype. I typically rock up and check with staff that it’s ok to put a sign next to my laptop which will say something along the lines of “I’ll buy you a free coffee if you answer 2 questions about my app”. As simple and as obvious as this approach may sound, I’ve found it to be very effective whenever I’ve used it. You’d be surprised by the number of people who are happy to have a play with your app or prototype and give you valuable feedback.

A few weeks ago, I attended a presentation by Lily Dart, a London-based user design expert, who talked about Guerrilla testing for content. These are the main things that I took away from Lilly’s talk:

  1. Preparing a session  Lily started off by talking about how to best prepare a guerrilla testing session (see Fig. 1 below). If you have a target persona or user segment that you’re targeting, make sure you’ll be able to find them at your venue of choice. The other thing which I’ve learned – the hard way – is to always ensure that the staff at the public venue are happy with you doing some user testing.
  2. Start with a good intro – It was interesting to hear Lily talk about the importance of a well practised introduction (see Fig. 1 below). In reality, you’ll only have a few minutes to get across what you’re there to do and what you want from the participant, so you better get your intro right!
  3. Test scenarios – Like with regular usability testing, it’s good to have a few concrete scenarios or use cases to test with participants. Lily outlined the characteristics of what makes a good scenario: (1) easy for participants to relate to, (2) describes a problem for participants to solve, (3) a realistic level of detail and (4) doesn’t hint to the participant how to achieve the goal (see Fig. 1 below).
  4. Running a session – Even if you typically only have about 10 minutes for your guerrilla testing session, there are a lot of useful learnings that you can get out of running these sessions. Lily provided some useful pointers on how to run guerrilla sessions, highlighting some of the common questions/tasks to use during a session.

Main learning point: “A little bit of testing is better than none” was the phrase with which Lily Dart ended her talk. She’s so right about that one. Although your test results or learnings from guerrilla session aren’t statistically sold or representative, you’re still going to get useful feedback in a very cost-effective way. I therefore can’t recommend running guerrilla testing sessions strongly enough. Granted, it takes a bit of getting out of your comfort zone but the advantages of this approach are so worth it!

Fig. 1 – Practical tips from Lily Dart on how to best do guerrilla user testing sessions – Taken from:  Guerrilla testing for content

Preparing a session:


  • Where will you find your target audience?
  • Can they stop for 10 minutes?
  • Is it ok with venue staff to do some testing?
  • Will you have internet access?


  • Which devices does your audience use?
  • Can you record from that device?

Facilitating a session:

Practiced introduction

  • Who are you? (i.e. the facilitator)
  • Why are you there? (provide your user with context on your product)
  • What do you want from them (e.g. what are you looking to learn, explain the session)

Example of a practiced introduction

“My name is Marc and I work for a startup called “carwow”. I’m here asking people to have a quick look at my website and to share their first impressions. I’d like to record your answers which will help me massively in improving the site. In return, I’ll buy you a coffee or a muffin to say thank you.

Do you have 10 minutes to spare?”


  • Which user need are you testing?
  • What might create that need?
  • When has a user fulfilled that need?

Anatomy of a good scenario

  • A good scenario is easy for participants to relate to
  • A good scenario describes a problem for participants to solve
  • A good scenario has a realistic level of detail
  • A good scenario doesn’t hint to the participant how to achieve the goal

Running a session

  • Before a scenario – Ask participants to describe their thoughts
  • During a scenario – Let participants identify success
  • After a scenario – Let participants tell you what they’d do next

Fig. 2 – Image of a guerrilla usability testing session at a Starbucks coffee shop – Taken from: http://www.uxbooth.com/articles/the-art-of-guerrilla-usability-testing/

Guerilla testing

Related links for further learning:

  1. http://www.slideshare.net/LilyDart/guerrilla-testing-for-content
  2. http://www.uxbooth.com/articles/the-art-of-guerrilla-usability-testing/
  3. http://www.uxbooth.com/articles/guerrilla-research-tactics-tools/
  4. http://www.boxuk.com/blog/unboxing-guerrilla-usability-testing/
  5. http://www.quora.com/How-do-you-go-about-doing-user-testing-in-coffee-shops