Publishers of technology books, eBooks, and videos for creative people

Home > Articles

SQL Visual QuickStart Guide: Retrieving Data from a Table

  • Print
  • + Share This
  • 💬 Discuss
This chapter is from the book
Learn how to use SQL's SELECT statement.

This chapter introduces SQL’s workhorse—the SELECT statement. Most SQL work involves retrieving and manipulating data by using this one (albeit complex) statement. SELECT retrieves rows, columns, and derived values from one or more tables in a database; its syntax is:

SELECT columns
  FROM tables
  [JOIN joins]
  [WHERE search_condition]
  [GROUP BY grouping_columns]
  [HAVING search_condition]
  [ORDER BY sort_columns];

SELECT, FROM, ORDER BY, and WHERE are covered in this chapter, GROUP BY and HAVING in Chapter 6, and JOIN in Chapter 7. By convention, I call only a SELECT statement a query because it returns a result set. DBMS documentation and other books might refer to any SQL statement as a query. Although SELECT is powerful, it’s not dangerous: You can’t use it to add, change, or delete data or database objects. (The dangerous stuff starts in Chapter 10.)

Retrieving Columns with SELECT and FROM

In its simplest form, a SELECT statement retrieves columns from a table; you can retrieve one column, multiple columns, or all columns. The SELECT clause lists the columns to display, and the FROM clause specifies the table from which to draw the columns.

To retrieve a column from a table:

  • Type:

    SELECT column
      FROM table;

    column is a column name, and table is the name of the table that contains column (Listing 4.1 and Figure 4.1).

    Listing 4.1. List the cities in which the authors live. See Figure 4.1 for the result.

    SELECT city
      FROM authors;

    Figure 4.1 Result of Listing 4.1.

    city
    -------------
    Bronx
    Boulder
    San Francisco
    San Francisco
    New York
    Palo Alto
    Sarasota

To retrieve multiple columns from a table:

  • Type:

    SELECT columns
     FROM table;

    columns is two or more comma-separated column names, and table is the name of the table that contains columns (Listing 4.2 and Figure 4.2).

    Listing 4.2. List each author’s first name, last name, city, and state. See Figure 4.2 for the result.

    SELECT au_fname, au_lname, city, state
      FROM authors;

    Figure 4.2 Result of Listing 4.2.

    au_fname  au_lname    city          state
    --------- ----------- ------------- -----
    Sarah     Buchman     Bronx         NY
    Wendy     Heydemark   Boulder       CO
    Hallie    Hull        San Francisco CA
    Klee      Hull        San Francisco CA
    Christian Kells       New York      NY
              Kellsey     Palo Alto     CA
    Paddy     O'Furniture Sarasota      FL

    Columns are displayed in the same order in which they’re listed in columns, not the order in which they’re defined in table.

To retrieve all columns from a table:

  • Type:

    SELECT *
      FROM table;

    table is the name of a table (Listing 4.3 and Figure 4.3).

    Listing 4.3. List all the columns in the table authors. See Figure 4.3 for the result.

    SELECT *
      FROM authors;

    Figure 4.3 Result of Listing 4.3.

    au_id au_fname  au_lname    phone        address              city          state zip
    ----- --------- ----------- ------------ -------------------- ------------- ----- -----
    A01   Sarah     Buchman     718-496-7223 75 West 205 St       Bronx         NY    10468
    A02   Wendy     Heydemark   303-986-7020 2922 Baseline Rd     Boulder       CO    80303
    A03   Hallie    Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco CA    94123
    A04   Klee      Hull        415-549-4278 3800 Waldo Ave, #14F San Francisco CA    94123
    A05   Christian Kells       212-771-4680 114 Horatio St       New York      NY    10014
    A06             Kellsey     650-836-7128 390 Serra Mall       Palo Alto     CA    94305
    A07   Paddy     O'Furniture 941-925-0752 1442 Main St         Sarasota      FL    34236

    Columns are displayed in the order in which they’re defined in table.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus