| Lesson 3 || A simple SQL statement |
| Objective || Examine a simple SQL statement. |
Simple SQL Select Statement Example
An example of a very simple statement would be:
SELECT * FROM authors
As you will soon see in other course modules, this statement simply says, "Retrieve all information from the authors table."
You have probably heard the term client/server
in working with your
computer and learning the industry buzz about different technologies. SQL lends itself well to client/server and REST technologies because it separates the request for information (your query) and the preparation of the information given in response.
The power of SQL removes the mundane task of working on the database and allows you to focus on the results you need and what to do with them once you have them.
The SQL language is subdivided into several language elements, including:
- Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)
- Expressions, which can produce either scalar values, or tables consisting of columns and rows of data.
- Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) orBoolean truth values and which are used to limit the effects of statements and queries, or to change program flow.
- Queries, which retrieve the data based on specific criteria. This is an important element of SQL.
- Statements, which may have a persistent effect on schemata and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
- SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.
- Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability
When constructing your query, your first task is generally to determine which table or tables will be needed and then add them to your from clause.
Next, you will need to add conditions to your where clause to filter out the data from these tables that you aren't interested in.
Finally, you will decide which columns from the different tables need to be retrieved and add them to your select clause. Here's a simple example that shows how you would find all customers with the last name "Smith":
SELECT cust_id, fname
WHERE lname = 'Smith';
This query searches the individual table for all rows whose lname column matches the string 'Smith' and returns the cust_id and fname columns from those rows.
Along with querying your database, you will most likely be involved with populating and modifying the data in your database. Here's a simple example of how you would insert a new row into the product table:
INSERT INTO product (product_cd, name)
VALUES ('CD', 'Certificate of Depysit')
Looks like you misspelled "Deposit."
You can clean that up with an update statement:
SET name = 'Certificate of Deposit'
WHERE product_cd = 'CD';
With this kind of utility, a number of people and vendors decided it was time to standardize. This is where SQL-92 enters the picture and SQL-92 will be discussed in the next lesson.