Key Word Description
AND Boolean argument used in SQL criteria for the result to be counted both conditions must be true.
AS Prefaces an alias for a column.
BETWEEN Used in criteria with AND.
DELETE Removes a row or rows from a database table.
DESC Reverses the order of a Sort on a specific column in an ORDER BY clause.
DISTINCT Returns only unique rows when used with SELECT. When used with an aggregate function, it applies function only to unique values.
FROM Precedes the table name in a SELECT clause.
GROUP BY Groups rows in a query that contain one or more aggregate functions by columns not contained in those functions.
HAVING Used for query criteria that contain aggregate functions.
INNER JOIN Joins two tables returning only matching records.
INSERT Used to add rows to a table.
INTO Precedes the table name in an INSERT statement.
IS NULL Used in a query criteria to find NULL values (rather than = NULL).
LIKE Used in query criteria with wildcards % _ to search for patterns in character based columns.
(continued )
Key Word Description
NOT Boolean argument used to exclude an option.
ON Used with INNER JOIN, introduces the clause that specifies how two tables are related.
OR Boolean argument used in criteria to specify an alternative value. Only one side of the OR clause must be true for the expression to be true.
ORDER BY Sorts a result set by a value or a set of values. When there is more than one sort criteria listed, the primary sort is on the leftmost value, the secondary sort on the next value, etc.
OUTER LEFT JOIN A join that returns all the rows in the first table listed (left) and only matching records in the second (right) table. Good for finding unmatched data such as a tutor who has no tutoring sessions or a customer who has no purchases.
SELECT The first word of all queries that return data from the database.
SET In an UPDATE statement used to set the initial value to be modified, additional values just have the column name = new value and are separated by commas.
UPDATE First word of a command to modify existing data in a table.
VALUES In an INSERT statement, this word prefaces the list of values to insert into the table.
WHERE In a SELECT statement, this word introduces the criteria by which to select which rows to return.
Vocabulary
Match the definitions to the vocabulary words:
1. Aggregate function add to the previous SQL standards?
4. Look up a good online tutorial for SQL. What is the URL?
Practices
Use the Pizza database created in the last chapter’s practices, and write SQL to answer these questions:
1. List all last names, phone numbers, and zip of the customers
2. List only those from Zip code 98002.
3. List all the customers that have no first address entered in the database.
10. Join the product and the OrderDetail table so that the result contains the product name, product unit size, and product unit price as well as the charged price. Do it for order 1000.
11. List all the order and order details for each order made by the customer with the phone number 2065556623.
12. Change the price of breadsticks to 3.00.
13. Process a pizza order for a new customer (this will involve 3 INSERT statements).
Scenarios
WILD WOOD APARTMENTS
Now that the basic database is in place, the Wild Wood Apartments managers are eager to see the database in action and see if it meets all their needs and requirements. It is time to look at the business rules and test them with some SQL. Look at the business rules you developed previously, and design some SQL queries to test them. Documentation : Set up a test plan. List the rule, the SQL you wrote, and the results. Also note whether the database passes or fails the test. Your queries should include the following:
It is time to test Vince’s database to see if it truly meets his needs.
It is time to look back at the business rules and test them with some SQL. Look at the business rules you developed previously, and design some SQL queries to test them. Documentation : Set up a test plan. List the rule, the SQL you wrote, and the results.
Also note whether the database passes or fails the test. Your queries should include the following:
1. Two or three simple SELECTs with various WHERE criteria
The college is feeling pressurized to get the new system in place.
There could be an inspection of their IT services any time now, and they want to be ready. It is time to look at the business rules and test them with some SQL. Documentation : Set up a test plan. Look at the business rules you developed previously, and design some SQL queries to test them. List the rule, the SQL you wrote, and the results. Also note whether the database passes or fails the test. Your queries should include the following:
1. Two or three simple SELECTs with various WHERE criteria
The research program is almost ready to begin. Westlake is in the process of interviewing potential patients and doctors. It is important that the database be ready soon. It is also important that it does what it is supposed to do. It is time to look at the business rules and test them with some SQL. Look at the busi-ness rules you developed previously, and design some SQL queries to test them. Documentation : Set up a test plan. List the rule, the SQL you wrote, and the results. Also note whether the database passes or fails the test. Your queries should include the following:
Review your business rules. Many are probably simple to test, requiring only SELECT statements. Others may be harder. Try the simple ones first.
You may also find that you need to adjust your sample data.
It may be necessary to insert some data that shows a violation of a rule, or you may need to insert data in order to compare dif-ferent dates or times.
Most SQL mistakes are syntax errors. Missing commas or extra commas are common suspects. The error messages in the query analyzer do not always pinpoint the exact error. If you double click the error message, it will place your cursor in the vicinity of the error. Look all around the region. A missing comma above or a misspelled word may be causing an error later in the code.
Another common error with joins is the ambiguous column. This usually involves a key column that occurs in other tables as a foreign key. Since it occurs in more than one table, SQL Server cannot determine which table it is from.
These columns should always be qualified with the table name or table alias.
151
Is It Secure?
In this chapter, Sharon looks at the security needs of the database. It is important to give everyone the access that they require to do the things they need to do. But it is also important to protect the database objects and data from either accidental or intentional damage. Sharon discovers that security is a complex issue and requires careful planning.
CHAPTER OUTCOMES
By the end of this chapter you will be able to: