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

In SQL, the "WHERE" clause is used in conjunction with a "SELECT" statement to filter the rows returned from a database table. It allows you to specify the criteria that the rows must meet to be selected. This can be particularly useful when you only want to work with a subset of the data that meets certain conditions. Here's how you can use the "WHERE" clause effectively:

1. Basic Usage: The "WHERE" clause follows the "SELECT" statement. For example, if you want to select all rows from a `Customers` table where the customer's last name is 'Smith', the SQL query would look like this:
   

   SELECT * FROM Customers WHERE LastName = 'Smith';
   

   
   This query selects all columns (`*`) from the `Customers` table where the `LastName` column equals 'Smith'.
2. Specifying Columns: To limit the columns being accessed, you can specify the column names instead of using `*`. For example:
   

   SELECT FirstName, Email FROM Customers WHERE LastName = 'Smith';
   

   
   This will return only the `FirstName` and `Email` columns for customers with the last name 'Smith'.
3. Combining Conditions: You can use logical operators like AND, OR, and NOT to combine multiple conditions. For example:
   

   SELECT * FROM Customers WHERE LastName = 'Smith' AND City = 'New York';
   

   
   This query selects all customers with the last name 'Smith' who live in New York.
4. Using Comparison Operators: The WHERE clause can also use comparison operators such as `>`, `<`, `>=`, `>=`, and `<>`. For example:
   

   SELECT * FROM Orders WHERE Amount > 100;
   

   
   This query selects all orders where the amount is greater than 100.
5. Pattern Matching with LIKE: The LIKE operator is used for pattern matching. `%` represents zero or more characters, and `_` represents a single character. For example:
   

   SELECT * FROM Customers WHERE FirstName LIKE 'J%';
   

   
   This query selects all customers whose first name starts with 'J'.
6. Handling NULL Values: To check for NULL values, use the `IS NULL` or `IS NOT NULL` operator. For example:
   

   SELECT * FROM Customers WHERE Address IS NULL;
   

   
   This query selects all customers who do not have an address listed.

In summary, the WHERE clause is a powerful tool for filtering data in a SELECT statement. By specifying conditions, you can control not only which rows are returned, but also which columns of those rows to access, enabling efficient and targeted data retrieval from your database.

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.