Knowledge Base
- 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.
SELECT statements
An SQL SELECT
statement retrieves records from a database table according to clauses (for example, FROM
and WHERE
) that specify criteria. The syntax is:
SELECT column1, column2 FROM table1, table2 WHERE column2='value';
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 (for example,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 (for example, SELECT
is the same as select
). For better readability, some programmers use uppercase for commands and clauses, and lowercase for everything else.
Examples
Following are examples of SQL SELECT
statements:
- To select all columns from a table (
Customers
) for rows where theLast_Name
column hasSmith
for its value, you would send thisSELECT
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
andFirst_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
andNickname
columns from theFriends
table for rows in which theNickname
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 (for example,'%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 download materials for self-study from IT Training.
For the general public, various online tutorials are available, such as the w3schools.com SQL Tutorial.
This is document ahux in the Knowledge Base.
Last modified on 2023-07-07 12:49:02.