What is SQL, and what are some example statements for retrieving data from a table?

• Overview
• SELECT statements
• Examples
• Learning more about SQL

Overview

Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases. SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex databases.

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.

Back to top

SELECT statements

An SQL SELECT statement retrieves records from a database table according to clauses (e.g., FROM and WHERE) that specify criteria. The syntax is:

In the above SQL statement:

• The SELECT 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 * (an asterisk).
  
  
• The FROM clause specifies one or more tables to be queried. Use a comma and space between table names when specifying multiple tables.
  
  
• The WHERE clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (e.g., WHERE last_name='Vader').
  
  
• The semicolon (;) 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 (i.e., SELECT is the same as select). For readability purposes, some programmers use uppercase for commands and clauses, and lowercase for everything else.

Back to top

Examples

Following are examples of SQL SELECT statements:

• To select all columns from a table (Customers) for rows where the Last_Name column has Smith for its value, you would send this SELECT 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 and First_Name 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 WHERE clause find inexact matches, add the pattern-matching operator LIKE. The LIKE operator uses the % (percent symbol) wild card to match zero or more characters, and the underscore ( _ ) wild card to match exactly one character. For example:

• To select the First_Name and Nickname columns from the Friends table for rows in which the Nickname 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 First_Name 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 % wild card instead (e.g., '%en') 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 register for instructor-led workshops offered by UITS IT Training, or view online tutorials via lynda.com. For more about IT Training workshops and other self-study options at IU, see:

• About IT Training workshops
• At IU, what self-study training options are available through IT Training?

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

Back to top