SQL chapter 4: Single-Table Queries

What are the comparison operators in SQL

= Equal to > Less than < Greater than < = Less than or equal to > = Greater than or equal to < > Not equal to

query

A query is a question represented in a way that the DBMS can understand.

IS NULL

Finds rows that contain a null value in the specified column. Sometimes a condition involves a column that can accept null values

SORT KEY

The column on which to sort data is called a sort key or simply a key.

_ WILDCARD (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 words such as Tim, Tom, or T3m.

(%) WILDCARD SIGN

%) is used as a wildcard to represent any collection of characters. the condition LIKE '%Rock%' retrieves information for each customer whose address contains some collection of characters, followed by the letters "Rock," followed potentially by some additional characters. Note that this query also would retrieve information for a customer whose address is "783 Rockabilly" because "Rockabilly" also contains the letters "Rock."

ORDER BY clause

- It simply takes the result of a SQL query and orders them by one or more specified attributes.

During execution of a query in MySQL Workbench, the query is automatically limited to producing _____ rows by default.

1000, This setting can be modified within the SQL editor preferences that can be found in the documentation if you prefer. The query results shown within this text include all results when possible due to their limited size, so there is no need to modify this preference.

MAX

A SQL aggregate function that outputs the maximum value in a column

AVG

A SQL aggregate function that outputs the mean average for a specified column or expression.

MIN

A SQL aggregate function that outputs the minimum value in a column

COUNT

A SQL aggregate function that outputs the number of rows in a table

SUM

A SQL aggregate function that outputs the total of the values in a column

simple condition

A simple condition has the form column name, comparison operator, and then either another column name or a value. A simple condition can also compare the values stored in two columns.

IN Clause

An IN clause, which consists of the IN operator followed by a collection of values, provides a concise way of phrasing certain conditions

The WHERE clause applies to only

Because the WHERE clause applies only to rows, you cannot use it to accomplish the kind of selection that is required.

Grouping

Grouping creates groups of rows that share some common characteristic. When you group rows, any calculations indicated in the SELECT command are performed for the entire group. For example, if you group customers by sales rep ID and the query requests the average balance, the results include the average balance for the group of customers represented by rep 05, the average balance for the group represented by rep 10, and the average balance for the group represented by rep 15. The following examples illustrate this process.

How do you query a database in sql?

In SQL, you use the SELECT command to query a database. The basic form of the SELECT command is SELECT-FROM-WHERE. After you type the word SELECT, you list the columns that you want to include in the query results

Is it okay to use wildcards in large databases?

In a large database, you should use wildcards only when absolutely necessary. Searches involving wildcards can be extremely slow to process.

DISTINCT operator

In some situations, the DISTINCT operator is useful when used in conjunction with the COUNT function because it eliminates duplicate values in the query results.

subquery

It is possible to place one query inside another. The inner query is called a 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,

LIKE

Rather than testing for equality, the LIKE operator uses one or more wildcard characters to test for a pattern match.

How would you find jout how many items are in the category DOG using the COUNT command?

SELECT COUNT (*) FROM ITEM WHERE (CATEGORY = 'DOG')'

Using the Sum function, find the total number of KimTay Pet Supplies customers and the total of their balances.(How could you calculate a sum of names or addresses?)

SELECT COUNT (*), SUM (BALANCE) FROM CUSTOMER

Count the number of customers that currently have invoices. Using the distinct operator

SELECT COUNT (DISTINCT CUST_ID) FROM INVOICES;

List the number and name of each customer with a null (unknown) address value.

SELECT CUST_ID , FIRST_NAME ,LAST_NAME FROM CUSTOMER WHERE (ADDRESS IS NULL);

List the customer ID, first name, last name, and balance for each customer whose balance exceeds the average balance of all customers.

SELECT CUST_ID, F_NAME, L_NAME, BALANCE FROM CUSTOMER WHERE BALANCE > (SELECT AVG(BALANCE) FROM CUSTOMER);

How can you compute all credit limits that are greater than 400 for customers?

SELECT CUST_ID, F_NAME, L_NAME, CREDIT_LIMIT - BALANCE AS AVAILABLE CREDIT FROM CUSTOMER WHERE (CREDIT_LIMIT - BALANCE ) >= 400;

What can be an example of finding all customers in a DB with balances higher than their credit limits?

SELECT FIRST_NAME, LAST_NAME_ BALANCE, CREDIT_LIMIT FROM CUSTOMER WHERE BALANCE > CREDIT_LIMIT

List each credit limit and the number of customers of sales rep 05 that have this limit.

SELECT FROM CREDIT_LIMIT, COUNT (*) FROM CUSTOMER WHERE (rep_id = '05') GROUP BY CREDIT_LIMIT HAVING COUNT (*) < 2 ORDER BY CREDIT_LIMIT ;

List each credit limit and the number of customers of sales rep 05 that have this limit.

SELECT FROM CREDIT_LIMIT, COUNT (*) FROM CUSTOMER WHERE (rep_id = '05') GROUP BY CREDIT_LIMIT ORDER BY CREDIT_LIMIT ;

List each credit limit and the number of customers having each credit limit, BUT list only those credit limits held by more than two customers.

SELECT FROM CREDOT_LIMIT, COUNT (*) FROM CUSTOMER GROUP BY CREDIT_LIMIT HAVING COUNT (*) > 2 ORDER BY CREDIT_LIMIT ;

List each credit limit and the number of customers having each credit limit.

SELECT FROM CREDOT_LIMIT, COUNT (*) FROM CUSTOMER GROUP BY CREDIT_LIMIT ORDER BY CREDIT_LIMIT ;

For each sales rep, list the rep ID and the average balance of the rep's customers, but list only those reps whose customers have an average balance greater than $100. Use the group by clause

SELECT FROM REP_ID, AVG(BALANCE) FROM CUSTOMER GROUP BY REP_ID HAVING AVG(BALANCE) > 100 ORDER BY REP_ID;

For each sales rep, list the rep ID and the average balance of the rep's customers. Use the group by clause

SELECT FROM REP_ID, AVG(BALANCE) FROM CUSTOMER GROUP BY REP_ID ORDER BY REP_ID;

What does SQL do with null values when using SUM, AVG, MAX, or Min?

SQL ignores any null value(s) in the column and eliminates them from the computations.

How can we use the like operator to find a customer located at an address that contains the letter rock?

Select cust_ID, L_NAME, F_NAME, ADDRESS, CITY, STATE, POSTAL FROM CUSTOMER WHERE (ADDRESS LIKE %ROCK%)

To sort the output, use an ORDER BY clause followed by the sort key. If you do not specify a sort order, the default is ascending. Using the order by

Select cust_ID, L_NAME, F_NAME, CREDIT LIMIT FROM CUSTOMER ORDER BY BALANCE;

List the customer ID, first name, last name, and credit limit of each customer. Order the customers by last name within descending credit limit. (In other words, first sort the customers by credit limit in descending order. Within each group of customers that have a common credit limit, sort the customers by last name in ascending order.) u

Select cust_ID, L_NAME, F_NAME, CREDIT LIMIT FROM CUSTOMER ORDER BY CREDIT_LIMIT DESC, LAST_NAME

List the customer ID, first name, last name, and credit limit for each customer with a credit limit of $500, $750, or $1,000 using the IN clause

Select cust_ID, L_NAME, F_NAME, CREDIT LIMIT FROM CUSTOMER WHERE (CREDIT_LIMIT IN (500, 750, 1000));

BETWEEN

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 commands simpler to construct.

GROUP BY clause

The GROUP BY clause lets you group data on a particular column, such as REP_ID, and then calculate statistics, when desired,

HOW DO YOU SORT RESULTS?

To sort the output, use an ORDER BY clause followed by the sort key. If you do not specify a sort order, the default is ascending.

IS NOT NULL

Used to select a value not null

minor sort key or secondary sort key

When you need to sort data on two columns, the less important column

primary sort key or major sort key

When you need to sort data on two columns, the more important column

AS COMMAND

You also can assign a name, or alias, to a computed column by following the computation with the word AS and the desired name.

compound condition

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

HAVING

the HAVING clause does for groups what the WHERE clause does for rows. The HAVING clause limits the groups that are included in the results.

From clause

the word FROM followed by the name of the table that contains the data you need to query.

aggregate functions

to calculate sums, averages, counts, maximum values, and minimum values. These functions apply to groups of rows. They could apply to all the rows in a table (for example, calculating the average balance of all customers). They also could apply to those rows satisfying some particular condition (for example, the average balance of all customers of sales rep 10).

WHERE in a Select statement does what?

you list any conditions (restrictions) that apply to the data you want to retrieve. This optional portion of the command is called the WHERE clause. For example, when you need to retrieve the rows for only those customers with credit limits of $750, include a condition in the WHERE clause specifying that the value in the CREDIT_LIMIT column must be $750 (CREDIT_LIMIT = 750).

what do you do to sort on multiple keys

you list the keys in order of importance in the ORDER BY clause.