SQL: Chapter 3

To return all of the columns from the base table, you can code the ___________ operator in the SELECT clause.

*

What can be used in a WHERE clause?

- Comparison operators (=, >, <, <=, >=, <>(not equal) - Logical Operators NOT, AND, and OR - IN operator IN () and NOT IN () - BETWEEN Operator BETWEEN ' ' AND ' ' and NOT BETWEEN ' ' AND ' ' - LIKE Operator LIKE '%' and NOT LIKE '%' and other wildcards -IS NULL and IS NOT NULL

Wildcard symbols (5)

1. %- match any sting of zero or more character LIKE 'San%' San Diego and Santa Ana 2. _ (underscore) matches any single character LIKE 'Comp_er%' Computerworld and CompuServe 3. [ ] matches a single character listed within the brackets LIKE 'Dami[EO]n' Damien and Damion 4. [ - ] Matches a single character within a given range LIKE'N[A-J]' NC and NJ, but not NV or NY 5. [ ^ ] matches a single character not listed after the caret. LIKE 'N[^k-y]' NC and NJ but not NV or NY

Five ways to code column specifications

1. All columns in a base table; use the asterisk * 2. column name in a base table 3. arithmetic expression Total - Payment AS BalanceDue 4. string expression LastName + ', '+ FirstName An expression can also include function(s). 5. function Select GETDATA() AS CurrentDate

ORDER BY

A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order). The expression can refer to any column in the base table, even if it isn't included in the result set. Can use numbers to specify the columns that use for sorting. 1 represents the first column in the result set, 2 second column, and so on.

Expression

A combination of column names and operators that evaluate to a single value.

BETWEEN Operator

A comparison operator that looks for values within a range WHERE InvoiceDate BETWEEN '2016-05-01' AND '2016-05-31' -Warning with date comparisons, datetime data and GETDATE function both use date and time. So '2016-05-01' defaults to 00:00:00.

What does a function do?

A function performs an operation and returns a value.

How do you use logical operators in a WHERE clause?

AND OR AND: both clauses must be true for each column for the row to show in the results set. OR: either of the clause can be true for the row to show in the results set.

Column alias

Another name substituted for a column name. A column alias is created in a query and displayed in the results. - AS or the equal sign can be used to set an alias. - If the alias includes spaces or special characters, you must enclose it in double quotes or brackets.

What data type is used for string expressions?

Char or Varchar

To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.

DISTINCT

How are functions written?

Enter the name of the function followed by parentheses. GetDate() If the function requires one or more parameters, you enter them with the parenthesis and separate them with a comma. CONVERT(Char(8),payementDate, 1)

The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.

FROM names the table from which the query will retrieve the data

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less? InvoiceTotal IN (0,1000) NOT (InvoiceTotal > 1000) InvoiceTotal <= 1000 InvoiceTotal BETWEEN 0 AND 1000

InvoiceTotal IN (0,1000) - The IN phrase is used to test if an expression is equal to a value in a list of expressions.

CONVERT function

It is used to change data types of a value. This function require two parameters: data type and the value to convert. optional third parameter specifies the format of the returned value.

How does SQL evaluate an arithmetic expression?

It performs the operation based on the order of precedence. -multiplication, division, and modulo operations - Then, addition and subtraction. -Use parentheses to change the order.

DISTINCT keyword

Keyword that prevents duplicate (identical) rows from being included in the result set. IT also causes the result set to be sorted by its first column (do not need an order by).

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.

LIKE

Subqueries

Nest inside another query. - Select query inside another select query.

ORDER BY OFFSET FETCH FIRST/NEXT

OFFSET clause specifies the number of rows that should be skipped before rows are returned from the result set FETCH clause specifies the number of rows that should be retrieved after skipping the specified number of rows. If you omit the FETCH clause, all of the rows to the end of the set are retrieved. - OFFSET and FETCH clauses are most useful when a client application needs to retrieve one page of data at a time.

How to retrieve a range of selected rows?

ORDER BY ... OffSet # ROWS FETCH FIRST # ROWS ONLY; - OFFSET and FETCH clauses are most useful when a client application needs to retrieve one page of data at a time.

What is the order by default?

ORDER BY default, in ascending sort: NULL Special characters Numbers Letter

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500? PaymentDate IS NOT NULL OR InvoiceTotal >= 500 PaymentDate IS NULL AND InvoiceTotal > 500 NOT (PaymentDate IS NULL AND InvoiceTotal <= 500) PaymentDate IS NOT NULL AND InvoiceTotal >= 500

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

Four clauses of a SELECT statement

SELECT FROM WHERE ORDER BY

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers? TOP 10 PERCENT InvoiceNumber TOP 10 InvoiceNumber TOP InvoiceNumber, InvoiceTotal TOP 10 PERCENT WITH TIES InvoiceNumber

TOP 10 InvoiceNumber

SELECT statement

The SELECT statement is used to select data from a database. Describes the columns that will be included in the result set. Syntax: SELECT column_name, column_name FROM table_name; SELECT * FROM table_name;

What do ALL and DISTINCT keywords and the TOP clause let you control in a SELECT statement?

The number of rows that are returned by a query.

How does SQL evaluate a compound condition for the operators: AND, NOT, OR in a WHERE clause?

The operation for a WHERE clause is NOT, AND, and OR. Use parenthesis to override this order or precedence

What is the concatenation operator?

The plus sign (+) FirstName + ', ' + LastName Concatenation operators are used with string expressions that use one or more character columns or literal values.

When you code a SELECT statement, you must code the four main clauses in the following order SELECT, WHERE, ORDER BY, FROM SELECT, FROM, WHERE, ORDER BY SELECT, FROM, ORDER BY, WHERE SELECT, ORDER BY, FROM, WHERE

The select statement has four clauses: SELECT, FROM, WHERE, ORDER BY

TOP clause

Used to limit the number of rows. - Use it to retrieve a specific number of rows from the top of the result set. Code the TOP keyword followed by an integer number that specifies the number of columns to return. SELECT TOP 5 InvoiceTotal - Use it as a percentage of the rows in the result set using PERCENT. SELECT TOP 5 PERCENT InvoiceTotal - If additional rows math the values in the last row, you can include those using WITH TIES SELECT TOP 5 WITH TIES InvoiceTotal

LIKE Operators

Used to retrieve rows that match a string pattern (mask). They use special characters called wildcards.

If you want to filter the result set that's returned by a SELECT statement, you must include a/an ___________________ clause.

WHERE

ALL keyword

causes all rows matching the search condition to be included in the result set, regardless of whether rows are duplicated -It is the default, it is common practice to omit the ALL keyword.

To override the order of precedence in an arithmetic expression, you can use __________________.

parentheses