Execute Program
Log InStart Now

SQL: Selecting Columns

Welcome to the Selecting Columns lesson!

This lesson is shown as static text below. However, it's designed to be used interactively. Click the button below to start!

  • So far in this course, we've always made queries using SELECT *. The * means "all columns".

  • >
    exec(`CREATE TABLE users (name TEXT, login_count INTEGER)`);
    exec(`INSERT INTO users (name, login_count) VALUES ('Amir', 1)`);
    exec(`SELECT * FROM users`);
    Result:
    [{name: 'Amir', login_count: 1}]Pass Icon

  • Instead of the *, we can ask for only the columns that we care about. This is a good way to reduce the amount of data that your query transfers over the network. If a table has 20 columns, but we only need one of them, then there's no reason to request all 20 from the database.

  • When we SELECT columns by name, only those columns are returned to us. Keep in mind that we still get an array of rows, and each row will be an object, so the result here is [{name: /* name here */ }].

  • >
    exec(`CREATE TABLE users (name TEXT, login_count INTEGER)`);
    exec(`INSERT INTO users (name, login_count) VALUES ('Amir', 1)`);
    exec(`SELECT name FROM users`);
    Result:
    [{name: 'Amir'}]Pass Icon

  • Here's a code problem:

    Write a query that selects only the login_count column.

    exec(`CREATE TABLE users (name TEXT, login_count INTEGER)`);
    exec(`INSERT INTO users (name, login_count) VALUES ('Amir', 1)`);
    exec(`
    SELECT login_count FROM users
    `);
    Goal:
    [{login_count: 1}]
    Yours:
    [{login_count: 1}]Pass Icon

  • We can select multiple columns by separating the columns names with a comma:

  • >
    exec(`CREATE TABLE users (name TEXT, login_count INTEGER, age INTEGER)`);
    exec(`INSERT INTO users (name, login_count, age) VALUES ('Amir', 1, 36)`);
    exec(`SELECT age, name FROM users`);
    Result:
    [{age: 36, name: 'Amir'}]Pass Icon

  • Here's a code problem:

    Selecting a column that doesn't exist is an error. Try to select an email column, even though it doesn't exist.

    exec(`CREATE TABLE users (name TEXT, login_count INTEGER)`);
    exec(`INSERT INTO users (name, login_count) VALUES ('Amir', 1)`);
    exec(`
    SELECT email FROM users
    `);
    Goal:
    Error: no such column: email
    Yours:
    Error: no such column: emailPass Icon