Database Management SQL in 10 Minutes Book Ch. 5-8

Wildcard filters in different spots

Wildcards can be used anywhere within the search pattern, and multiple wildcards may be used as well. The following example uses two wildcards, one at either end of the pattern The search pattern '%bean bag%' means match any value that contains the text bean bag anywhere within it, regardless of any characters before or after that text: SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%'; Wildcards can also be used in the middle of search pattern, although that is rarely useful. The following example finds all products that begin with an F and end with a y: SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y';

Commonly Used Numeric Manipulation Functions

ABS() - Returns a numbers absolute value COS() - Returns the trigonometric cosine of a specified number EXP() - Returns the exponential value of a specified number PI() - Returns the value of PI SIN() - Returns the trigonometric sine of a specified angle SQRT() - Returns the square root of a specified number TAN() - Returns the trigonometric tangent of a specified angle

Using mathematical calculations

Another frequent use for calculated fields is performing mathematical calculations on retrieved data. Let's take a look at an example. The Orders table contains all orders received, and the OrderItems table contains the individual items within each order. The following SQL statement retrieves all the items in order number 20008: SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_num = 20008;

Underscore Wildcard

Another useful wildcard is the underscore (_). The underscore is used just like %, but instead of matching multiple characters the underscore matches just a single character. SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';

Using Aliasis

But what is the name of this new calculated column? Well, the truth is, it has no name; it is simply a value. Although this can be fine if you are just looking at the results in a SQL query tool, an unnamed column cannot be used within a client application because there is no way for the client to refer to that column. To solve this problem, SQL supports column aliases. An alias is just that, an alternate name for a field or value. Aliases are assigned with the AS keyword. Take a look at the following SELECT statement: SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER BY vend_name;

DBMS Functions

DBMS Functions Extract part of a string -Access uses MID(). DB2, Oracle, PostgreSQL, and SQLite use SUBSTR(). MariaDB, MySQL and SQL Server use SUBSTRING(). Datatype conversion - Access and Oracle use multiple functions, one for each conversion type. DB2 and PostgreSQL uses CAST(). MariaDB, MySQL, and SQL Server use CONVERT(). Get current date -Access uses NOW(). DB2 and PostgreSQL use CURRENT_DATE. MariaDB and MySQL use CURDATE(). Oracle uses SYSDATE. SQL Server use GETDATE(). SQLite uses DATE().

to_char() function

In this example, the to_char() function is used to extract part of the date, and to_number() is used to convert it to a numeric value so that it can be compared to 2012. Another way to accomplish this same task is to use the BETWEEN operator: SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');

Concatenated Fields

Joining values together (by appending them to each other) to form a single long value. In SQL SELECT statements, you can concatenate columns using a special operator. Depending on what DBMS you are using, this can be a plus sign (+) or two pipes (||). And in the case of MySQL and MariaDB, a special function must be used. SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name; -------------------------------------------------------- Bear Emporium (USA ) Bears R Us (USA ) Doll House Inc. (USA ) Fun and Games (England ) Furball Inc. (USA ) Jouets et ours (France )

RTRIM() function

Look again at the output returned by the SELECT statement above. The two columns that are incorporated into the calculated field are padded with spaces. Many databases (although not all) save text values padded to the column width, so your own results may indeed not contain those extraneous spaces. To return the data formatted properly, you must trim those padded spaces. This can be done using the SQL RTRIM() function, as follows: SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name; The RTRIM() function trims all space from the right of a value. By using RTRIM(), the individual columns are all trimmed properly.

IN operator

The IN operator is used to specify a range of conditions, any of which can be matched. IN takes a comma-delimited list of valid values, all enclosed within parentheses. The following example demonstrates this: SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

OR operator

The OR operator instructs the database management system software to retrieve rows that match either condition. SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

Date and Time Manipulation Functions

The Orders table contains all orders along with an order date. To retrieve a list of all orders made in 2012 in SQL Server, do the following: SELECT order_num FROM Orders WHERE YEAR(yy, order_date) = 2012; This example (both the SQL Server and Sybase version, and the Access version) uses the YEAR() function which, as its name suggests, returns a part of a date. YEAR() takes two parameters, the part to return, and the date to return it from. In our example YEAR() returns just the year from the order_date column. By comparing that to 2012, the WHERE clause can filter just the orders for that year.

NOT operator

The WHERE clause's NOT operator has one function and one function only—NOT negates whatever condition comes next. Because NOT is never used by itself (it is always used in conjunction with some other operator), its syntax is a little different from all other operators. Unlike other operators, the NOT keyword can be used before the column to filter on, not just after it. SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

The Brackets Wildcard

The brackets ([]) wildcard is used to specify a set of characters, any one of which must match a character in the specified position (the location of the wildcard). SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact; This wildcard can be negated by prefixing the characters with ^ (the carat character). For example, the following matches any contact name that does not begin with the letter J or the letter M (the opposite of the previous example): SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;

combining WHERE clauses - AND operator

To filter by more than one column, you use the AND operator to append conditions to your WHERE clause. SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

Using the Wildcard filter- Using LIKE Operator

Using wildcards, you can create search patterns that can be compared against your data. In this example, if you want to find all products that contain the words bean bag, you can construct a wildcard search pattern enabling you to find that bean bag text anywhere within a product name. SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

Combining the OR and AND operators

WHERE clauses can contain any number of AND and OR operators. Combining the two enables you to perform sophisticated and complex filtering. The first two WHERE clause conditions are enclosed within parentheses. As parentheses have a higher order of evaluation than either AND or OR operators, SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

Text Manipulation Functions

You've already seen an example of text-manipulation functions in the last lesson—the RTRIM() function was used to trim white space from the end of a column value. Here is another example, this time using the UPPER() function: SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name; As you can see, UPPER() converts text to upper case and so in this example each vendor is listed twice, first exactly as stored in the Vendors table, and then converted to upper case as column vend_name_upcase. Refer to Lesson 8 for more examples.