Welcome to Lesson 2 of Module 4 on Basic Query Formulation with SQL. I'm gonna start with two questions for you. One basic question on query formulation and the other question on a fine point of query formulation. What database details should you know before you try to formulate a query? When should you use parenthesis in writing condition to limit rows returned in a result? Query formulation is an important skill in application development. Everyone involved in application development must be competent in query formulation. Most students will be involved, at least initially, in application development, rather than as a role as a database specialist. Database specialists must also understand query formulation and SQL. Lesson two provides your first look at the SELECT statement. You will learn to write SELECT statements that use individual tables. Later lessons will expand the range of select statement coverage and query formulation skills to multiple tables and row summaries. This lesson has practical objectives for you related to the usage of the SELECT statement. I want you to use either Oracle or MySQL to write and execute practice problems using a SELECT statement statement involving row conditions on individual tables. In this lesson, we will study a simple structure for the SELECT statement. After this brief introduction, the lesson will show examples that you can mimic as you complete problems. The SELECT statement begins with the SELECT's keyword and a list of column expressions to specify the result table. I will discuss expressions shortly. The simplest column expression is just the column name. The result of the statement in the SELECT clause is followed by the tables used in the FROM clause. For this lesson, we will use statements that have just a single table following the FROM keyword. The WHERE and ORDER BY clauses are optional. The WHERE clause indicates conditions that result rows must satisfy. The ORDER BY clause is used if the result rows should be in a specific order. The word expression is important in most computer languages, including the SQL SELECT statement. In most places in the SELECT statement, whenever a column can be used, a column expression can also be used. Examples of simple column expressions are FacSalary * 1.1, to increase a salary by 10%, and EnrGrade- 1, to reduce a grade by one point. The WHERE clause can use logical expressions, using the and, or, and not operators to combine conditions. Logical expressions have the value of true or false. Only rows in which the logical expression is true are in the result. For example the logical expression OffTerm = 'FALL' AND OffYear = 2012 indicates that rows in the result must be in Fall 2012. This database diagram shows tables, columns, relationships and data types. You should know or consult a database diagram when formulating queries. The examples in this lesson use only single tables. So relationship details are not necessary to know in this lesson. Note that this diagram shows Oracle data types somewhat different than standard SQL data types. You focus on the three major types of data. Numeric, being integer, fixed decimal or floating point. String, char or varchar. And date. You will want to use operations and comparisons with constants that are compatible with the major types of data. For example, you should use a numeric constant in a condition with a numeric column, not a string constant. The first example retrieves all columns and rows from the Faculty table. The asterisk following the SELECT keyword means all columns of the tables in the FROM clause. The absence of a WHERE clause means that all rows of the tables in the FROM clause are in the result. Let's execute the first example in the SQL Developer. The result contains six rows and all columns of the Faculty table. The second example adds a WHERE clause to restrict Faculty rows in the result to a specific faculty number. The statement result is a subset of the first example. Let's execute the second example in the SQL developer. The result contains one row in all columns of the Faculty table. The third example retrieves a subset of the columns of the Faculty table, for faculty with a rank of full professor and a salary greater than 65,000. For conditions on text columns, Oracle is case sensitive by default. Since the values of FacRank were entered with upper case, the condition will not match correctly if the constant is not upper case. Let's execute the third example in the SQL developer. The result contains one row and three columns of the Faculty table. The fourth example demonstrates duplicate rows in the result. Since many faculty live in the same city and state, duplicate rows are in the result. To eliminate duplicate rows, the DISTINCT keyword should be used. Let's execute both versions of the fourth example in the SQL Developer. The first result contains six rows with four duplicate rows. The second result contains three rows with no duplicate rows, due to the DISTINCT key word. Example 5 retrieves the name, that's a first and last, an inflated salary of faculty hired after 2001. The expression in the SELECT clause multiplies the faculty salary by 1.1 and renames the column in the result to IncreasedSalary, using the AS keyword. Using a name for a computed column avoids a meaningless default name. The WHERE clause uses an expression to extract the year component of the FacHireDate column. Functions to manipulate dates differ across DBMSs. In Oracle, two functions, to_char and to_number are required. In MySQL, only one function, date_format, is required. As you can see, the SELECT statements are not portable across Oracle and MySQL because of the different date functions in the two DBMS's. Let's execute the fifth example in the Oracle SQL developer. The result shows three rows with four columns, including the renamed column for the computed column. Now let's execute the statement again without the as keyword to rename the computed column. Note that Oracle uses a name with the expression. Columns using a character string or text data type, CHAR or VARCHAR, support both exact and inexact matching. You can use the equality, the equal comparison operator for exact matching with the string column, as shown in previous examples. Inexact matching supports conditions that match some pattern rather than matching an identical string value. One of the most common types of inexact matching is to find values having a common prefix such as IS, for information systems. Example 6 uses the LIKE operator along with the pattern matching character, the percent symbol, to perform prefix matching. The string constant, IS%, means match strings beginning with IS and ending with anything. The wildcard character % matches any string. Note that the pattern matching character only has a special meaning with the LIKE operator, not with the equality operator. The SQL standard has other pattern matching characters, but the wildcard character is the most important. Let's execute the sixth example in the Oracle SQL Developer. The result contains ten rows, with a course number beginning with IS in each result row. Example 7 depicts range matching on a column with a date data type. In both Oracle and MySQL, single quotation marks enclose date constants. In Oracle SQL the standard date format uses a two digit day, a three letter month abbreviation, and a four digit year. In MySQL, the standard date format is the four digit year, two digit month and two digit day. Date columns can be compared, just like numbers, with the usual comparison operators, such as equal to, less than and so on. The BETWEEN AND operator defines a closed interval including the end points. Let's execute the seventh example in the Oracle SQL developer. The result contains two rows with hiring date between January 1st 2004 and December 31st, 2005, for each row. You should not use the LIKE operator in pattern matching characters in conditions with date columns. Some DBMSs allow the LIKE operator, but is not portable across DBMSs. And the results may vary within a given DBMS. You should treat date columns as numeric, not text. Besides testing columns for specified values, you sometimes need to test the lack of a value. No values are used when there's no normal value for a column. A null can mean that the value is unknown or the value is not applicable to the row. For the Offering table, a null value for the fac number column, means that the instructor is not yet assigned. Testing for null values is done with the IS NULL comparison operator as shown in Example 8. Let's execute the eighth example in the Oracle SQL developer. The result contains one row for the only summer 2013 course without an assigned faculty. Example 9 depicts a complex logical expression, involving both logical operators, AND, and OR. Example 9 retrieves offerings in FALL 2012 or WINTER 2013. I've seen frequent mistakes by students mixing the AND and OR operators without using parentheses. So I strongly recommend using parenthesis when mixing AND and OR in a logical expression. Otherwise, the reader of the SELECT statement may not understand the grouping of the AND and OR conditions. Without parenthesis, you must depend on the default way that AND and OR conditions are grouped. The reader of the statement may not be aware of the default grouping. In addition, the statement may not be portable across DBMSs without parentheses. Let's execute the ninth example in the SQL developer. The result table contains five rows for the offerings in fall 2012 or winter 2013. Let's wrap up this lesson that gave you an initial look at the SQL SELECT statement with examples on single tables, an important sub set of the SELECT statement. You were shown examples with conditions on numeric columns, stringer text columns, and date columns. Some of the examples used expressions, inexact matching with a LIKE operator, column renaming, the DISTINCT keyword and logical expressions, combining the AND and OR operators. Now, let's return to the opening questions of the lesson. For single table problems, you must know the columns of a table and data types. Lessons three and four in module four will focus on problems involving multiple tables, so that you must know the relationships in a database in addition to the columns and data types. When mixing logical operators AND and OR you should always use parentheses to avoid errors and to help the reader understand the meaning of your statement. I encourage you to work lots of problems to master query formulation and the SELECT statement.
