Seamless payments – Learning more about Dwolla and their API platform

I recently heard Shamir Karkal, Head of Open APIs at BBVA, talking about open platforms and I was intrigued. In the podcast episode Shamir talked about the power of APIs, but at the same time stressed the importance of having a strong platform that these API end points can hook into.

Shamir talked about building a product with a platform attached. Instead of just building a set of APIs, we should treat APIs as a way in for customers, developers and third parties to hook into the capabilities of our business. For example, hooking into all the things that banks typically tend to do well: compliance, risk management and customer support.

My ears really perked up as soon as Shamir started talking about Dwolla. Dwolla is US based peer-to-peer payments company, whose mission it is to facilitate “Simple payments. No transaction fees.” Dwolla is powered by APIs, making it easy for US users to link their Dwolla account to a US bank account or credit union account to move money. Setting up a Dwolla account is free, and there’s no per transaction fee. Users can collect payment on an invoice, send a one-time or recurring payment, or payout a large number of people at once. Dwolla also offers this a white label solution (see Fig. 1 below).

dwolla-white-label-api

Fig. 1 – Dwolla’s white label version of their API – Taken from: http://apievangelist.com/2015/09/03/dwolla-just-released-a-white-label-version-of-their-api-are-you-ready-for-the-wholesale-api-economy/

In essence, what Dwolla does is enabling real-time payments between Dwolla accounts and another bank account that users want to send money to. Dwolla are integrated with banks such as BBVA, having Dwolla APIs ‘talk’ to the bank’s APIs. Dwolla has created some form of a protocol in the form of FiSync which aims to make it more secure for users to transmit information between accounts. FiSync enables the use of secure authentication and tokenisation in the comms between Dwolla and accounts like those of BBVA Compass. This way, BBVA Compass account holders don’t have to share their account info with Dwolla (see Fig, 2 below).

screen-shot-2016-09-27-at-20-35-26

Fig. 2 – Workflow of a connecting a FiSync-enabled bank account to Dwolla – Taken from: https://www.dwolla.com/updates/breaking-down-real-time-secure-authentication/

Main learning point: I love how Dwolla’s proposition is almost entirely API based, making it easy for its users to transfer money to bank accounts and credit union accounts. Dwolla definitely feels more seamless, secure and cost-efficient compared to the way in which users traditionally transfer money from one account to another.

Related links for further learning:

  1. http://11fs.co.uk/podcasts/ep111-interviewed-innovators-really-changing-banking/
  2. https://www.bbva.com/en/news/disciplines/shamir-karkal-building-financial-future-bbvas-platform/
  3. http://finovate.com/open-api-shamir-karkal-to-head-bbvas-new-developer-platform/
  4. http://apievangelist.com/2015/09/03/dwolla-just-released-a-white-label-version-of-their-api-are-you-ready-for-the-wholesale-api-economy/
  5. http://www.ibm.com/support/knowledgecenter/SS9H2Y_7.5.0/com.ibm.dp.doc/oauth_threeleggedflow.html
  6. https://www.dwolla.com/updates/breaking-down-real-time-secure-authentication/
  7. http://help.dwolla.com/customer/portal/articles/1940212-bbva-compass-dwolla-faq?b_id=5440
  8. https://www.bbvacompass.com/compass/dwolla/

Elliptic – Investigating Bitcoin transactions

The other day I wrote about blockchains, looking into this new technology. I then came across a company called Elliptic that specialises in “identifying illicit activity on the Bitcoin blockchain.” It made me realise how blockchains can be used for all kinds of illegal activity. Also, I can now see a clear link between digital identity management and blockchains.

Transparency is a key aspect of blockchains and, going back to the original purpose of blockchains, it helps Bitcoins to complete financial transactions through the chain. Naturally, there are lots of users who don’t like the transparency aspect and use anonymizing services to cover tracks when doing transactions through the blockchain.

I read an interesting article about how anonymous users and their transactions can still be identified, tracking users’ activity both in real-time and historically. There are a number of centralised services within the blockchain e.g. wallets and exchanges which have access to user and transaction info. Also, by doing an activity or user network analysis, one can find out more about the type of transaction and the identity of the users involved (see example in Fig. 1 below).

Fig. 1 – An example of a sub-network between the thief, the victim and three other vertices – Taken from: http://anonymity-in-bitcoin.blogspot.co.uk/2011/07/bitcoin-is-not-anonymous.html

 

Network analysis

The majority of Elliptic’s clients seem to be either law enforcement agencies or financial institutions. For example, one of the uses cases that Elliptic caters for is making sure that the bitcoins a client acquires aren’t derived from the proceeds of criminal activity. Elliptic says that in the past year it has been able to map the entire 35 GB transaction history of the bitcoin blockchain.

Interestingly, Elliptic has created a visualisation technology to provide a number of anti-money laundering (‘AML’) services. If you look at the sample visualisation below (see Fig. 2), you can can see how Elliptic can visualise ‘known’ entities e.g. exchanges whilst naming illicit marketplaces and money laundering services.

Through an API, Elliptic’s clients will thus get real-time alerts about any bitcoin payments linked to known thefts, illicit marketplaces and other criminal activity, which are all identified by name. As a result, financial institutions can effectively do real time compliance, adhering to compliance regulation as transactions take place through the blockchain.

Fig. 2 – “The Bitcoin Big Bang” visualisation by Elliptic – Taken from: https://bitcoinmagazine.com/articles/elliptic-launches-anti-money-laundering-visualization-tool-1435089559

elliptic-launches-anti-money-laundering-visualization-tool

 

Main learning point: As I mentioned in my previous blog post, the world of blockchains is a new one to me. Learning about how people can abuse this new technology is therefore just as new to me. Learning about how Elliptic helps financial institutions and law enforcement agencies to identify illicit blockchain activity has given me a first understanding of how one can work through to blockchain networks to figure out its users and transactions.

Related links for further learning:

  1. http://dupress.com/articles/trends-blockchain-bitcoin-security-transparency/
  2. http://insidebitcoins.com/news/sabr-io-identifies-illegal-activity-on-blockchains-willing-to-work-with-law-enforcement/34307
  3. http://fsroundtable.org/cto-corner-what-is-a-blockchain-and-why-is-it-important/
  4. http://www.coindesk.com/network-analysts-view-block-chain/
  5. http://www.financemagnates.com/cryptocurrency/innovation/elliptic-launches-bitcoin-big-bang-anti-money-laundering-tool/
  6. https://bitcoinmagazine.com/articles/elliptic-launches-anti-money-laundering-visualization-tool-1435089559
  7. http://anonymity-in-bitcoin.blogspot.co.uk/2011/07/bitcoin-is-not-anonymous.html
  8. http://arxiv.org/abs/1107.4524
  9. https://www.elliptic.co/financial-institutions/
  10. http://www.dfs.ny.gov/legal/regulations/revised_vc_regulation.pdf

 

 

 

My product management toolkit (3) – Goal setting

As part of my product management toolkit, I’ve thus far covered the creation of a product vision and the definition of a product strategy. The next thing to look at is goal setting: what are the business goals that a product strategy and or roadmap need to align with? I’ve learned the importance of goals to help define or assess a product strategy. I would even go as far as saying that if your product strategy, roadmap, backlog or – low and behold – your actual product don’t align with your business goals, you’re setting yourself up for failure.

Tool 3 – Goal Setting

What are goals? – This is what Wikipedia has to say about “goals”: “A goal is a desired result that a person or a system envisions, plans and commits to achieve; a personal or organizational desired end-point in some sort of assumed development. Many people endeavour to reach goals within a finite time by setting deadlines.” In other words, what is it the that we are looking to achieve, why and by when?

I typically look at goals from either of the following two angles: metrics or ‘objectives-key-results’ (‘OKR’s). From a metric perspective; what is the single metric that we’re looking to move the needle on, why and and by when? What does this impact look like and how can we measure it? For example, a key business goal can be to increase Customer Lifetime Value with 1% by June 2016. To be clear, a metric in itself isn’t a goal, the change that you want to see in metric is a goal.

From an OKR perspective, the idea is to outline a number of tangible results against a set, high level, objective. For example:

Objective: To enable sellers on our marketplace platform to make business and product decisions based on their sales and performance data generated from their activities on our platform.

Result 1: Our sellers making key business and product decisions before and throughout Christmas 2015

Result 2: Our sellers can look at their historic sales data so that they’ve got more sales context for their decision-making

Typically, there will be a set of overarching business goals that have been established and our responsibility as product managers is to link our product goals to these objectives, so that our product strategy is fully aligned with the business strategy.

okr-deck

Taken from: http://www.businessinsider.com/googles-ranking-system-okr-2014-1?IR=T

What goals aren’t – Goals aren’t a strategy or specific features. This might sound obvious, but often see cases where people do confuse things; setting goals without a strategy to achieve them or having a roadmap that doesn’t align with business goals.

In contrast, the point of a strategy or a roadmap is to highlight the ‘how’, the steps that need to be taken to achieve specific goals.

When to create goals? – It’s simple: if you join an organisation and hear “we don’t have business goals”, you know what to do! My point here is that a product strategy or roadmap that isn’t aligned with broader business goals, is just a loose collection of features or random solutions. The one thing to add is that some early stage startups tend to get really hung up on a whole range of specific goals or metrics. I’d always recommend to keep it simple and focus on a single goal or metric, understand what your (target) users’ needs are and how are they actually using your product.

Characteristics of good goals – I can imagine that a lot of you will have a heard of SMART goals:

  • S = specific, significant, stretching
  • M = measurable, meaningful, motivational
  • A = agreed upon, attainable, achievable, acceptable, action-oriented
  • R = realistic, relevant, reasonable, rewarding, results-oriented
  • T = time-based, time-bound, timely, tangible, trackable

 

SMART 2Example taken from: http://business.lovetoknow.com/wiki/Examples_of_SMART_Goals_and_Objectives

Main learning point: In my view, setting and understanding goals is just important as creating a strategy to achieve them. Before I delve into creating a product strategy or roadmap, I’ll always try to make sure I fully understand the business objectives and translate those into specific, measurable product goals.

 

Related links for further learning:

  1. https://medium.com/@joshelman/the-only-metric-that-matters-ab24a585b5ea#.z74gt29wa
  2. https://rework.withgoogle.com/guides/set-goals-with-okrs/steps/introduction/
  3. http://www.theokrguide.com/
  4. http://www.businessinsider.com/googles-ranking-system-okr-2014-1?IR=T
  5. http://www.producttalk.org/2014/01/how-to-set-goals-that-drive-product-success/
  6. http://www.kaushik.net/avinash/web-analytics-101-definitions-goals-metrics-kpis-dimensions-targets/
  7. https://marcabraham.wordpress.com/2013/05/03/book-review-lean-analytics/
  8. http://www.slideshare.net/abrahammarc1/product-roadmaps-tips-on-how-to-create-and-manage-roadmaps
  9. https://www.projectsmart.co.uk/smart-goals.php

App review: Amazon Seller App

How do the likes of eBay, Amazon Handcraft, Notonthehighstreet, Rakuten and Etsy go about supporting the small businesses who sell products through their platforms? What are some of the typical data and customer insights that these sellers benefit from and why? Amazon recently launched its Seller App aiming to “help grow and manage your selling business on Amazon.” I had a quick look at the Amazon Seller App and these are my initial thoughts:

  1. How did the app come to my attention? – Since I’ve started working on online marketplaces I tend to keep an eye out for new technology and tools available to the sellers on these marketplaces.
  2. My quick summary of the app (before using it) – I expect a mobile app, which helps sellers to keep a close eye on their sales figures and manage their orders.
  3. How does the app explain itself in the first minute? – The first screen of the app asks me to select my marketplace (see Fig. 1 below). It doesn’t provide any further context but I presume that if you’re an active seller on Amazon you might not need any further info.
  4. Getting started, what’s the process like? – I’m not a seller on Amazon, but looking at some of the screenshots and the data provided, I can imagine that sellers will find it relatively easy to use the app (see Fig. 2 and 3 below). What I’m curious about though is the data syncing between devices, making sure your sales data is as ‘real-time’ as possible. I also couldn’t get a sense of whether (and how well) the Seller App integrates with Amazon’s Mobile Credit Card Reader.
  5. How does the app compare to similar apps?  The Amazon Seller App feels very similar to the Sell on Etsy app and SellerMobile. For example, the Etsy app enables sellers to manage their open orders and revisit completed ones on the go (see Fig. 4 below). The Etsy app also offers sellers the opportunity to check their Etsy shop and product views, but I’m not sure whether this analytics feature is included in Amazon’s Seller App.
  6. Did the app deliver on my expectations?  Yes, based on what I could tell from the screenshots and app description. The app looks the provide the key stats and insights that marketplace sellers tend to be interested in. What I could not tell from the screenshots is how the app facilitates sellers who sell on multiple marketplaces, for example in the UK and the US. I know this is a reality for lots of small businesses and it would be good to find out how the user interface of the Amazon Seller App accommodates for this use case.

Main learning point: The Amazon Seller App looks fit for purpose, providing sellers with key sales information that’s visual and easy to manage on the go. Analytics and multiple marketplaces are two areas where I’m not sure how (well) they are covered by this app. However, if you sell products through Amazon and want to keep a close eye on your orders and sales, then this app should give you the key information to help you manage your activities on Amazon’s marketplace.

Fig. 1 – Screenshot of opening screen on Amazon Seller App (iOS)

IMG_2737

 

Fig. 2 – Screenshots of Amazon Seller App (iOS) – Taken from: http://www.allmediatalks.com/amazon-in-launches-its-seller-app-in-india-amazon-online-selling/

Amazon Marketplace

Fig. 3 – Screenshot order detail view on Amazon Seller App (iOS) – Taken from: https://itunes.apple.com/us/app/amazon-seller/id794141485

Amazon product detail

 

Fig. 4 – Screenshot of the ‘Sell on Etsy’ App – Taken from: https://blog.etsy.com/news/2014/introducing-new-mobile-app-just-for-sellers/

Screen Shot 2015-07-05 at 15.22.33

 

Related links for further learning:

  1. http://tamebay.com/2015/06/amazon-marketplaces-eu-release-seller-app.html
  2. http://techcrunch.com/2014/03/06/amazon-debuts-an-official-mobile-app-for-amazon-sellers/
  3. http://www.retailwire.com/discussion/18003/its-good-to-be-an-amazon-marketplace-seller
  4. http://www.fiercewireless.com/europe/story/mobile-app-helps-amazon-sellers-shift-2b-items-2014/2015-01-05
  5. http://www.allmediatalks.com/amazon-in-launches-its-seller-app-in-india-amazon-online-selling/
  6. http://www.wired.com/2014/08/amazon-mobile-credit-card-reader/
  7. http://techcrunch.com/2014/10/23/etsy-moves-further-into-the-offline-world-with-launch-of-card-reader-for-in-person-payments/
  8. https://blog.etsy.com/news/2014/introducing-new-mobile-app-just-for-sellers/

Gathering meaningful data during the user journey

Since I started looking into omni-channel metrics last year, I’ve been learning how to best gather meaningful data at each step of the user journey. I recently came across a great piece by Gary Angel titled “A Data Model for the User Journey”. In his article, Gary aims to address the multi-source nature of our data touchpoints, and the issues brought about by the differences in the level and type of detail data. He rightly points out that these differences in data make any kind of meaningful analysis of the user journey virtually impossible. Gary provides a number of useful steps to tackle this problem:

  1. Create a level of abstraction – Gary first suggestion is to get to a level of abstraction where each data touchpoint can be represented equally. One way of doing this is to apply Gary’s “2-tiered segmentation” model. In a 2-tiered segmentation model, the first tier is the visitor type. This is the traditional visitor segmentation based on persona or relationship. The second tier is a visit or unit-of-work based segmentation that is behavioural and is designed to capture the visit intent. It changes with each new touch. Gary summarises this two-tiered approach as follows: “Describing who somebody is (tier 1) and what they are trying to accomplish (tier 2).”
  2. Capture visit intent – One of the key things that I learned from Gary’s article is the significance of ‘visit intent’ with respect to creating a user-journey model. Visit intent offers an aggregated view of what a visit was about and how successful it was. Both the goal and the success of a visit are important items when analysing a user journey.
  3. 2-tiered segmentation and omni-channel – Gary points out how well his 2-tiered segmentation model lends itself to an omni-channel setup. The idea of 2-tiered segments can be used across any touchpoint, whether it’s online or offline. The intent-based segmentation can be applied relatively easily to calls, branch or store visits and social media posts. The model can also be applied – albeit less easily – to display advertising and email (see Fig. 1 below).
  4. Good starting point for journey analysis – When you look at the sample data structure as outlined in Fig. 1 below, with one data row per user touchpoint visit or unit of work, you can start doing interesting pieces of further analysis. For example, with this abstract data structure you can analyse multi-channel paths or enhance user journey personalisation.
  5. Combine visitor level data with user journey data – It sounds quite complex, but I like Gary’s suggestion to model in the abstract the key customer journeys. This can then be used to create a visitor level data structure in which the individual touchpoints are rolled up. Gary’s example below helps clarify how you can best map different data touchpoints to related stages in the user journey (see Fig. 2 below) .

Main learning point: The main thing that I’m taking away from Gary Angel’s great piece is the two segments to focus on when measuring the user journey: the visitor and their goals. The data structure suggested by Gary lends itself really well to an omni-channel user experience as it combines visitor and user journey data really well.

Fig. 1 – Sample data structure when applying the the 2-tiered segmentation to a user journey data model – Taken from: http://semphonic.blogs.com/semangel/2015/03/a-data-model-for-the-user-journey.html

  • TouchDateTime Start
  • TouchType (Channel)
  • TouchVisitorID
  • TouchVisitorSegmentCodes (Tier 1)
  • TouchVisitSegmentCode (Tier 2)
  • TouchVisitSuccessCode
  • TouchVisitSuccessValue
  • TouchTimeDuration
  • TouchPerson (Agent, Rep, Sales Associate, etc.)
  • TouchSource (Campaign)
  • TouchDetails

Fig. 2 – Example of modelling the acquisition journey for a big screen TV – Taken from: http://semphonic.blogs.com/semangel/2015/03/a-data-model-for-the-user-journey.html

  • Initial research to Category Definition (LED vs. LCD vs. Plasma – Basic Size Parameters)
  • Feature Narrowing (3D, Curved, etc.)
  • Brand Definition (Choosing Brands to Consider)
  • Comparison Shopping (Reviews and Product Detail Comparison)
  • Price Tracking (Searching for Deals)
  • Buying

With an abstract model like this in hand, you can map your touchpoint types to these stages in user journey and capture a user-journey at the visitor level in a data structure that looks something like this:

  • VisitorID
  • Journey Sub-structure
    • Journey Type (Acquisition)
    • Current Stage (Feature Narrowing)
    • Started Journey On (Initial Date)
    • Time in Current Stage (Elapsed)
    • Last Touch Channel in this Stage (Channel Type – e.g. Web)
    • Last Touch Success
    • Last Touch Value
    • Stage History Sub-Structure
      • Stage (e.g. Initial Research) Start
      • Stage Elapsed
      • Stage Success
      • Stage Started In Channel
      • Stage Completed in Channel
      • Channel Usage Sub-Structure
        • Web Channel Used for this Journey Recency
        • Web Channel Used for this Journey Frequency
        • Call Channel Used for this Journey Recency
        • Call Channel Used for this journey Frequency
        • Etc.
    • Stage Value
    • Etc.

This stage mapping structure is a really intuitive representation of a visitor’s journey. It’s powerful for personalisation, targeting and for statistical analysis of journey optimisation. With a structure like this, think how easy it would be to answer these sorts of questions:

  • Which channel does this visitor like to do [Initial Product Research] in?
  • How often do visitors do comparison shopping before brand narrowing?
  • When people have done brand narrowing, can they be re-interested in a brand later?
  • How long does [visitor type x] typically spend price shopping?

Related links for further learning:

  1. http://semphonic.blogs.com/semangel/2015/03/a-data-model-for-the-user-journey.html
  2. http://semphonic.blogs.com/semangel/2015/03/in-memory-data-structures-for-real-time-personalization.html
  3. http://semphonic.blogs.com/semangel/2011/04/semphonics-two-tiered-segmentation-segmentation-for-digital-analytics-done-right.html
  4. http://semphonic.blogs.com/semangel/2015/02/the-visit-is-dead-long-live-the-visit.html
  5. http://semphonic.blogs.com/semangel/2015/02/statistical-etl-and-big-data.html

CarStory and its ways to enhance your car inventory

The other day I came across CarStory. Since I’ve started with car comparison site carwow I’ll always keep an eye out for similar sites, changing the way in which consumer buy new or used cars. Given that I’m not a US based car dealer, my testing of the CarStory site and app is limited. I did, however, learn quite a lot about CarStory’s service by reading reviews and watching videos.

These are the main things that I learned about CarStory:

  1. Mission statement – “Turn car shoppers into customers” is CarStory’s main tagline. On its homepage, there’s a succinct description of CarStory’s value proposition. The service is aimed at car dealers, providing them with a “CarStory” to their inventories. A CarStory is a market report which “tells a story”, highlighting your cars’ unique features and value in the local market (see an example in Fig. 1 below). CarStory’s goals are to (1) build consumer confidence and (2) accelerate purchase decisions.
  2. Use cases – I guess the main benefit for dealers using CarStory is that they will have a good bit of car specific info at hand, not having to check multiple sources to answer customer questions about e.g. fuel consumption, features or alternative models (see Fig. 2 below). From a customer’s perspective, dealers are likely to be set up well for specific questions e.g. about price comparison or the most popular features on a specific car.
  3. Infographics – Apart from vehicle specific ‘story cards’, dealers can also use CarStory’s infographics to provide their customers with more data and insight about a specific model. For example, I can look at high level supply and demand data for a specific model (see Fig. 3 below).

Main learning point: CarStory offers an interesting way of creating market reports and integrating these reports into a dealer’s daily workflow. It currently only seems to apply to used cars and it would be good to find out from a customer’s perspective how the CarStory data and insights help in making purchasing decisions, whether it’s for a new or a used car.

Fig.1 – Screenshots of a sample CarStory – Taken from: https://www.carstory.com/static/img/market_sample.pdf

Screen Shot 2015-04-21 at 16.04.11

Screen Shot 2015-04-22 at 07.57.43Screen Shot 2015-04-22 at 07.58.47Screen Shot 2015-04-22 at 07.59.41

 

Screen Shot 2015-04-22 at 08.00.54

Fig. 2 – CarStory use cases – Adapted from: http://blog.carstory.com/business-development-sell-more-with-carstory/

  • Identify other cars in your inventory to match customer needs – A dealer is on the phone with a customer and can use CarStory to check his inventory to see if there are any cars in the other to meet a customer’s requirements.
  • Provide car specific info on the phone, email or on text – With the data included in a CarStory for a specific vehicle, dealers will be able to answer specific customer questions on the phone, email or on text. The idea is that customers don’t necessarily need to come into the dealership to find out certain details about a car.

Fig. 3 – Example of a CarStory infographic – Taken from: http://blog.carstory.com/market-reports-dodge-caravan-infographic/

dodge-caravan

Related links for further learning:

  1. http://www.dealerrefresh.com/carstory-app-interview-with-vast/
  2. http://www.autoremarketing.com/trends/features-info-used-car-buyers-want-most
  3. http://blog.carstory.com/get-to-know-carstory-part-3-vehicle-condition-is-key/
  4. http://blog.carstory.com/business-development-sell-more-with-carstory/
  5. https://www.carstory.com/static/img/market_sample.pdf
  6. http://blog.carstory.com/getting-to-know-carstory-part-1-can-you-trust-the-data/
  7. http://blog.carstory.com/get-to-know-carstory-part-2-good-deal-great-deal/
  8. http://blog.carstory.com/top-10-carstory-market-report-tips-and-tricks/

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