Which statement is used to display information about the columns in a table?

In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns result sets to the client program.

SELECT statements

An SQL

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 statement retrieves records from a database table according to clauses (for example,

  SELECT * FROM Customers WHERE Last_Name='Smith';

1 and

  SELECT * FROM Customers WHERE Last_Name='Smith';

2) that specify criteria. The syntax is:

  SELECT column1, column2 FROM table1, table2 WHERE column2='value';

In the above SQL statement:

• The

    SELECT * FROM Customers WHERE Last_Name='Smith';

  0 clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card

    SELECT * FROM Customers WHERE Last_Name='Smith';

  4 (an asterisk).
• The

    SELECT * FROM Customers WHERE Last_Name='Smith';

  1 clause specifies one or more tables to be queried. Use a comma and space between table names when specifying multiple tables.
• The

    SELECT * FROM Customers WHERE Last_Name='Smith';

  2 clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (for example,

    SELECT * FROM Customers WHERE Last_Name='Smith';

  7).
• The semicolon (

    SELECT * FROM Customers WHERE Last_Name='Smith';

  8) is the statement terminator. Technically, if you're sending only one statement to the back end, you don't need the statement terminator; if you're sending more than one, you need it. It's best practice to include it.

Note:

SQL is not case sensitive (for example,

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 is the same as

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

0). For better readability, some programmers use uppercase for commands and clauses, and lowercase for everything else.

Examples

Following are examples of SQL

  SELECT * FROM Customers WHERE Last_Name='Smith';

0 statements:

• To select all columns from a table (

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  2) for rows where the

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  3 column has

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  4 for its value, you would send this

    SELECT * FROM Customers WHERE Last_Name='Smith';

  0 statement to the server back end:

    SELECT * FROM Customers WHERE Last_Name='Smith';

  The server back end would reply with a result set similar to this:

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

• To return only the

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  6 and

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  7 columns, based on the same criteria as above, use this statement:

    SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

  The subsequent result set might look like:

    +---------+------------+
    | Cust_No | First_Name |
    +---------+------------+
    | 1001    | John       |
    | 2039    | David      |
    | 2098    | Matthew    |
    +---------+------------+
    3 rows in set (0.05 sec)

To make a

  SELECT * FROM Customers WHERE Last_Name='Smith';

2 clause find inexact matches, add the pattern-matching operator

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

9. The

  +---------+-----------+------------+
  | Cust_No | Last_Name | First_Name |
  +---------+-----------+------------+
  | 1001    | Smith     | John       |
  | 2039    | Smith     | David      |
  | 2098    | Smith     | Matthew    |
  +---------+-----------+------------+
  3 rows in set (0.05 sec)

9 operator uses the

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

1 (percent symbol) wild card to match zero or more characters, and the underscore (

  SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

2) wild card to match exactly one character. For example:

• To select the

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  7 and

    SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

  4 columns from the

    SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

  5 table for rows in which the

    SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

  4 column contains the string "brain", use this statement:

    SELECT First_Name, Nickname FROM Friends WHERE Nickname LIKE '%brain%';

  The subsequent result set might look like:

    +------------+------------+
    | First_Name | Nickname   |
    +------------+------------+
    | Ben        | Brainiac   |
    | Glen       | Peabrain   |  
    | Steven     | Nobrainer  |
    +------------+------------+
    3 rows in set (0.03 sec)

• To query the same table, retrieving all columns for rows in which the

    +---------+-----------+------------+
    | Cust_No | Last_Name | First_Name |
    +---------+-----------+------------+
    | 1001    | Smith     | John       |
    | 2039    | Smith     | David      |
    | 2098    | Smith     | Matthew    |
    +---------+-----------+------------+
    3 rows in set (0.05 sec)

  7 column's value begins with any letter and ends with "en", use this statement:

    SELECT * FROM Friends WHERE First_Name LIKE '_en';

  The result set might look like:

    +------------+------------+-----------+
    | First_Name | Last_Name  | Nickname  |
    +------------+------------+-----------+
    | Ben        | Smith      | Brainiac  |
    | Jen        | Peters     | Sweetpea  |  
    +------------+------------+-----------+
    2 rows in set (0.03 sec)

• If you used the

    SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

  1 wild card instead (for example,

    SELECT Cust_No, First_Name FROM Customers WHERE Last_Name='Smith';

  9) in the example above, the result set might look like:

    +------------+------------+-----------+
    | First_Name | Last_Name  | Nickname  |
    +------------+------------+-----------+
    | Ben        | Smith      | Brainiac  |
    | Glen       | Jones      | Peabrain  |
    | Jen        | Peters     | Sweetpea  |
    | Steven     | Griffin    | Nobrainer |  
    +------------+------------+-----------+
    4 rows in set (0.05 sec)

Learning more about SQL

To learn more about SQL programming, Indiana University students, faculty, and staff can download materials for self-study from IT Training.

For the general public, various online tutorials are available, such as the w3schools.com SQL Tutorial.

Which statement is used to display data from a table?

Which statement is used to choose a column from a table?

What does the statement show columns from table name produces?

Which Mark used to display all the columns from table?