Lesson 15

In the next module, you will see how to use the WHERE clause in conjunction with your SELECT statements to limit the rows, columns, and other information you will be accessing from your SQL database tables. Advanced uses of SELECT can make your queries much more powerful. The WHERE clause is used in conjunction with SQL DML statements, and takes the following general form:

SQL-DML-Statement
FROM TABLE_NAME 
WHERE predicate

All rows for which the predicate in the WHERE clause are True are affected (or returned) by the SQL (DML) Data Manipulation Language statement or query. Rows for which the predicate evaluates to False or Unknown (NULL) are unaffected by the DML statement or query.
The following query returns only those rows from table mytable where the value in column mycol is greater than 100.

SELECT *
FROM   mytable
WHERE  mycol > 100

SQL statements let you create, populate, modify, and delete the tables in a database. In many database products, SQL statements even let you create and destroy the database itself. For example, MySQL's CREATE DATABASE and DROP DATABASE statements create and destroy databases.
If you put these SQL commands in a script, you can rerun that script whenever it isnecessary. You can easily rebuild the database if it gets corrupted, make copies of the database on other computers, fill the tables with data to use when running tests, and reinitialize the data after the tests are finished.
Being able to reinitialize the data to a known state can also be very helpful in tracking down bugs.
It is extremely hard to find a bug if it just pops up occasionally and then disappears again. If you can reinitialize the database and then make a bug happen by following a series of predictable steps, it is much easier to find and fix the problem.