Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

MySQL

memorize.ai (lvl 286)

(0)

29 cards

()

Section 1

Preview this deck

HAVING clause

Front

1 / 29

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (29)

Section 1

(29 cards)

HAVING clause

Front

is used to restrict the groups that are included. This restriction does not apply to individual rows but rather to groups. Because the WHERE clause applies only to rows, you cannot use it to accomplish the kind of selection that is required. The HAVING clause limits the groups that are included in the results. YOU CAN USE HAVING clause to limit the groups that are included.

Back

select command

Front

select-from-where from- name of the table that contains the data you need to query. This portion of command called From clause. where clause.

Back

function/ description

Front

AVG calculate the average value in a column COUNT determines the number of rows in a table MAX Determines the maximum value in a column MIN Determines the minimum value in a column Sum Calculates a total of the values in a column (when you use the sum function, you must specify the column to total, and the column data must be numeric)

Back

a simple condition

Front

the condition in the preceding where clause is called a simple condition. A simple condition has the form column name, comparison operator, and then either another column name or a value. When condition involved the table's primary key= there could not be more that one row in the answer

Back

to retrieve all columns and rows from a table

Front

Instead of listing every column in the select clause, you can use an asterisk(*) to indicate that you want to include all columns. The result lists all columns in the order in which you described them to the system when you created the table.

Back

When you need to list all customers

Front

you do not need to include a where clause; you do not need to put any restrictions on the data to retrieve. You simly list the columns to be included (customer_num, customer_name, and balance)

Back

between operator

Front

the between operator lets you specify a range of values in a condition. the between operator is not an essential feature of SQL; you have just seen that you can obtain the same result without it. Using the between operator, however, does make certain select command simpler to construct. The between operator is inclusive, meaning that a value equal to either value in condition is selected. in the clause between 2000 and 5000, for example, values of either 2000 or 5000 would make the condition true.

Back

major sort key (or primary key)

Front

when you need to sort data on two columns, the more important column is called the major sort key, and the less important column is called the minor sort key (or secondary key). to sort on multiple keys, you list keys in order of importance in the ORDER BY clause. To sort in descending order, you follow the name of sort key with the Desc operator

Back

sort key

Front

the column on which data is to be sorted is called a sort key or simply a key. select customer_num, customer_name, balance from customer order by balance;

Back

In clause

Front

consists of the IN operator followed by a collection of values, provides a concise way of phrasing certain conditions select customer_num, customer_name, credit_limit from customer where credit_limit in (5000, 1000, 15000);

Back

GROUP BY clause

Front

the GROUP BY clause lets you group data on a particular column.

Back

not condition

Front

when a where clause uses the not operator to connect simple conditions, it also is called a not condition

Back

computed column

Front

does not exist in the database but can be computed using data in the existing columns. + addition; - subtraction; * multiplication; / division the parentheses around the calculation (credit_limit-balance) are not essential but improve readability

Back

like operator

Front

rather than testing for equality, the like operator uses one or more wildcard characters to test for pattern match. select customer_num, customer_name, street from customer where street like '%Central%'; Another wildcard symbol in MySQL is underscore(_) which represents any individual character. For example, "T_m" represents the letter "T" followed by any single character, followed by the letter "m", and would retrieve rows that include the words Tim, Tom, or T3m.

Back

compound condition

Front

you form a compound condition by connecting two or more simple conditions with the AND, OR, and NOT operators.

Back

DISTINCT operator

Front

the DISTINCT operator is useful when used in conjunction with the COUNT function because it eliminates duplicate in a query. The DISTINCT operator eliminates duplicate values in the results of a query.

Back

binary keyword

Front

in MySQL you can retrieve character data that matches the case used in the where clause by using the binary keyword. WHER BINARY CITY='grove' would retrieve no rows because the values in the table are stored as 'Grove'

Back

Query

Front

question represented in a way that the DBMS can understand

Back

aggregate functions

Front

MySQL uses special functions, called aggregate functions, to calculate sums, averages, counts, maximum values, and minimum values. These functions apply to group of rows. They could apply to all the rows in a table. They also could apply to those rows satisfying some particular conditions of the aggregate functions.

Back

comparison operator/ description

Front

= equal to; < less then; > greater that; <=less then or equal to; >=greater that or equal to; <> not equal to; !=not equal to

Back

NOT operator

Front

reverses the true of the original condition.

Back

subquery

Front

the inner query is called subquery. The subquery is evaluated first. after the subquery has been evaluated, the outer query can use the results of the subquery to find its results.

Back

OR operator

Front

when the OR operator connects simple conditions, the compound condition will be true whenever any one of the simple conditions is true.

Back

to retrieve rows that satisfy some condition

Front

You can use the WHERE clause to restricts the query output to customer number.

Back

AND operator

Front

when the AND operator connect simple condition, all the simple conditions must be true in order for the compound condition to be true.

Back

as

Front

you can also assign a name to computed column by following the computation with the word AS and the desired name. select customer_num, customer_name, (credit_limit-balance) as available _ credit from customer; AS AVAILABLE CREDIT creates a computed column named Available credit

Back

AVG function

Front

AVG calculate the average value in a column. AVG calculates the average value in a numeric range. when you use the SUM, AVG, MAX, MIN functions, MySQL ignores any null values in the column and eliminates them from the computations.

Back

Order By clause

Front

from a practical standpoint this means that when you query a relational database, there is no defined order in which the results are displayed. Rows can be displayed in the order in which the data was originally entered, but even this is not certain. If the order in which data is displayed id important, you can specifically request that results appear in a desired order. In MySQL, you specify the results order by using the ORDER BY clause

Back

GROUPING

Front

Creates groups of rows that share some common characteristics. When you group rows, any calculations indicated in the SELECT command are performed for the entire group.

Back