DATABASE FOUNDATIONS QUIZ 2

CAST function

converts a literal value or the value of a column into a specific data type • It helps to ensure that the data types of the values in the expression are compatible -all columns or literals in an expression are either characters, numbers, or datetimevalues • RDBMS will raise an error if expression contains incompatible values • Don't put a ten-pound sack in a five-pound box • Don't put a square peg in a round hole • Don't put a 10.2-pound sack in a one-pound sack

SELECT keyword is used to

retrieve information from the tables in a relational database •Used in conjunction with other keywords and clauses to find and view information •Any question regarding who, what, where, when, what irand how many can be answered

Value Expressions

The SQL Standard refers to a column reference, literal value, and expression collectively as a value expression. A value expression returns a value that is used by some component of an SQL statement • The SQL Standard specifies the use of a value expression in a variety of statements and defined terms • No matter where you use it, you'll always define a value expression in the same manner

Searching for Unknown/Missing Values

A Null does represent a missing or unknown value Consider: "Give me a list of customers who didn't specify what county they live in." SQL: SELECT CustFirstName|| ' ' || CustLastNameAS Customer FROM Customers WHERE CustCountyIS NULL Consider: "Which engagements do not yet have a contract price?" SQL: SELECT EngagementNumber, ContractPriceFROM Engagements WHERE ContractPriceIS NULL

The 'Nothing' Value: NULL

A Nullrepresents a missingor an unknownvalue. A column value might also be Null if none of its possible values apply to a particular row. Null does not represent a zero, a character string of one or more blank spaces, or a "zero-length" character string. The major drawback of Nulls is their adverse effect on mathematical operations • Any operation involving a Null evaluates to Null

Order of Precedence For Evaluating Multiple Conditions

By default, the database evaluates conditions from left to right Consider: SELECT CustFirstName, CustLastName, CustState,CustZipCode FROM Orders WHERE CustLastName ='Patterson' AND CustState = 'CA' OR CustZipCode LIKE '%9' WHERE (CustLastName= 'Patterson' AND CustState= 'CA') OR CustZipCodeLIKE '%9'

constant (or literal) value

Character string literal is a sequence of individual characters enclosed in single quotes • A single quote embedded within a character string is represented by two consecutive single quotes e.g 'this is literal value' •Numeric literal consists of an optional sign and a number and can include a decimal place, the exponent symbol, and an exponential number 427 -11.253 • Date literals are specific dates, times, or datetimes(timestamps) that are supplied to a SELECT statement e.g. '2007-05-16'

Using Mathematical Expressions in a SELECT Clause

Consider "Display for each agent the agent name and projected income (salary plus commission), assuming each agent will sell $50,000 worth of bookings." SQL: SELECT Concat(AgtFirstName, ' ', AgtLastName) AS AgentName, Salary + (50000 * CommissionRate) AS ProjectedIncomeFROM Agents

Using Date-Time Expressions in a SELECT Clause

Consider "How many days did it take to ship each order?" •SQL: SELECT OrderNumber, CAST(ShipDate-OrderDateAS INTEGER) AS DaysToShipFROM Orders

Predicates in a WHERE clause

Consider SQL: SELECT CustLastNameFROM Customers WHERE CustLastName= 'Smith' • The predicate in the WHERE clause is equivalent to asking this question for each row in the Customers table: "Does the customer last name equal 'Smith'?" • When the answer to this question is yes (true) for any given row in the Customers table, that row appears in the result set

Using 'AND' and 'OR' Together

Consider: "I need the name and title of every professor or associate professor who was hired on May 16, 1989" SQL: SELECT StfFirstName, StfLastName, Title, DateHiredFROM Staff WHERE (Title = 'Professor' OR Title = 'Associate Professor') AND DateHired= '1989-05-16'

ORDER BY

Display Results in Sequence Specify different sort order: SELECT EmpLastName, EmpFirstName, EmpPhoneNumber, EmployeeIDFROM Employees ORDER BY EmpLastNameDESC, EmpFirstNameASC

IS NULL:

Enables you to determine whether a value expression evaluates to Null

LIKE (PATTERN MATCH):

Enables you to test whether a character string value expression matches a specified character string pattern Takes the value of a value expression and uses the LIKE predicate to test whether the value matches a defined pattern string • A pattern string can consist of any logical combination of regular string characters and two special wildcard characters: the percent sign (%) and the underscore (_) • The percent sign represents zero or more arbitrary regular characters, and the underscore represents a single arbitrary regular character • The manner in which you define the pattern string determines which values are retrieved from the value expression Although you can search for any pattern string using the appropriate wildcard characters, you'll run into a problem if the values you want to retrieve include a percent sign or an underscore character • For example, you will have a problem trying to retrieve the value MX_445 because it contains an underscore character. • You can circumvent this potential dilemma by using the ESCAPE option of the LIKE predicate • Consider: "Show me a list of products that have product codes beginning with 'G_00' and ending in a single number or letter. • SQL: SELECT ProductName, ProductCodeFROM Products WHERE ProductCodeLIKE 'G\_00_' ESCAPE '\' Give me a list of customers whose last names begin with 'Mar'. SQL: SELECT CustLastName, CustFirstName FROM Customers WHERE CustLastName LIKE 'Mar%'

5 Basic Predicates in the SQL Standard: BETWEEN (RANGE):

Enables you to test whether the value of a given value expression falls within a specified range of values. You specify the range using two value expressions separated by the AND keyword.

5 Basic Predicates in the SQL Standard: IN (MEMBERSHIP):

Enables you to test whether the value of a given value expression matches an item in a given list of values Consider: Which entertainers do we represent in Seattle, Redmond, and Bothell? SQL: SELECT TourneyLocationFROM Tournaments WHERE TourneyDateIN ('2012-09-18', '2012-10-09', '2012-11-06')

Clauses in a SELECT Statement

SELECT •Is the primary clause of the SELECT statement and is absolutely required FROM •Is the second most important clause in the SELECT statement and is also required WHERE •Is an optional clause that is used to filter the rows returned by the FROM clause •The WHERE keyword is followed by an expression, technically known as a predicate, that evaluates to true, false, or unknown GROUP BY •When you use aggregate functions in the SELECT clause to produce summary information, you use the GROUP BY clause to divide the information into distinct groups •A database system uses any column or list of columns following the GROUP BY keywords as grouping columns •The GROUP BY clause is optional HAVING •Filters the result of aggregate functions in grouped information •It is similar to the WHERE clause in that the HAVING keyword is followed by an expression that evaluates to true, false, or unknown •You can test the expression by using standard comparison operators, Boolean operators, or special operators •HAVING is also an optional clausebut when included, it has to be preceded by GROUP BY clause

Data retrieval operation can be broken into 3 smaller operations

SELECT statement, SELECT expression, SELECT query

There are 13 other predicates defined by the SQL Standard:

Similar, Regex, Unique, Normalized, Match, Overlaps, Distinct, Member, Submultiset, Set, and Type -they aren't been used commercially/widely • Quantified is used rarely • EXISTS is widely used and will be discussed later

Excluding Rows with 'NOT'

This operator is an optional component of the BETWEEN, IN, LIKE, and IS NULL predicate. A SELECT statement will disregard any rows that meet the condition expressed by any of these predicates when you include the NOT operator • The rows that will be in the result set instead are those that did not meet the condition • The types of phrases you'll encounter are similar to those listed here: • ". . . that don't begin with 'Her'." Consider: "Show me a list of all the orders we've taken, except for those posted in October" SQL: SELECT OrderID, OrderDateFROM Orders WHERE OrderDateNOT BETWEEN '2012-10-01' AND '2012-10-31'

WHERE Clause

Use a WHERE clause in a SELECT statement to filter the data the statement draws from a table • The WHERE clause contains a search condition that it uses as the filter • This search condition provides the mechanism needed to select only the rows you need or exclude the ones you don't want • Your database system applies the search condition to each row in the logical table defined by the FROM clause

Using Character Expressions in a SELECT Clause

Use expressions to: • Create a calculated column in a query • Search for a specific column value • Filter the rows in a result set • Connect two tables in a JOIN operation (to be discussed later) the result of the operation defined in the expression • This new column is known as a calculated (or derived) column • Some database systems require you to provide the name explicitly, while others actually provide a generated name for you • In MySQL, you can optionally provide a name for the new column by using the AS keyword • You can use any valid character string literal (enclosed in single quotes) for the name

5 Basic Predicates in the SQL Standard:COMPARISON:

Use one of the six comparison operators to compare one value expression to another value expression. The six operators and their meanings are • = equal to • <> not equal to • < less than • > greater than • <= less than or equal to • >= greater than or equal to

DISTINCT keyword

Use the DISTINCT keyword in your SELECT statement, and the result set will be free and clear of all duplicate rows SQL: SELECT City FROM Bowlers • Problem is that the result set for this SELECT statement shows every occurrence of each city name found in the Bowlers table •Using the DISTINCT keyword in the SELECT statement will eliminate the redundant information •Translation: Select the distinct city from the bowlers table •Clean Up: Select distinct city from the bowlers table •SQL: SELECT DISTINCT City FROM Bowlers

Using Multiple Search Conditions: 'AND'

You can combine two or more conditions by using the AND andOR operators • The combined set of conditions constitutes a single search condition • AND operator • Use this operator when all the conditions in the combined set must be met in order for a row to be included in a result set 51 • Consider: "Give me the first and last names of customers who live in Seattle and whose last names start with the letter 'H'" SQL: SELECT CustFirstName, CustLastNameFROM Customers WHERE CustCity= 'Seattle' AND CustLastNameLIKE 'H%' OR operator • Use this operator when either of the conditions in the combined set can be met in order for a row to be included in a result set 52 • Consider: "I need the name, city, and state of every staff member who lives in Seattle or is from the state of Oregon" SQL: SELECT StfFirstName, StfLastName, StfCity, StfStateFROM Staff WHERE StfCity= 'Seattle' OR StfState= 'OR'

Expression

a way to manipulate the data in your tables to calculate or generate new columns of information.is some form of operation involving numbers, character strings, or dates and times •It can use values drawn from specific columns in a table, constant (literal) values, or a combination of both •After your database completes the operation defined by the expression, the expression returns a value to the SQL statement for further processing

LIMIT in conjunction with ORDER BY

to provide a subset of rows in a result set Clean up: SELECT Product Name, Retail Price FROM Product sort by Retail Price Descending, limit to top 5 rows •SQL: SELECT ProductName, RetailPriceFROM Product ORDER BY RetailPriceDescLIMIT 5

Expressions are especially useful

when you are asking "what if" questions

Types of Expressions

• CONCATENATION • Combining two or more character columns or literals into a single character string e.g. SELECT CONCAT ('I can use ', 'plus operator') As concat; -Result set will be I can useplus operator Can use character strings, datetimevalues, and numeric values simultaneously: Concat('Order Number: ', OrderNumber, ' was placed on ' CAST(OrderDate AS CHARACTER(10)) • MATHEMATICAL • Adding, subtracting, multiplying, and dividing numeric columns or literals Be sure that the values used in the expression are compatible -use CAST wherever needed, for eg: TotalLength+ CAST(Distance AS INTEGER) • DATE AND TIME ARITHMETIC • Applying addition or subtraction to dates and times ADDDATE('2008-01-02', 31); -> '2008-02-02'

The SQL Standard defines seven general categories of types of data

• Character • National character • Binary • Numeric • Boolean • Datetime • Interval