SQL is required and SQL is required

SQL is required and SQL is required
I. sort and retrieve data

1. Sort data:SELECTProd_nameFROMProductsORDER BYProd_name (sort data in alphabetical order for the prod_name column)

Position of the order by clause
When specifying an order by clause, make sure it is the last clause in the SELECT statement. If it is not the final clause, an error message is displayed.
Sort by non-selected columns
Generally, the columns used in the order by clause are selected for display. However, it is not necessary to sort data using non-retrieved columns.

2. sort by multiple columns:SELECTProd_id, prod_price, prod_nameFROMProductsORDERProd_price, prod_name

It is important to understand that when sorting by multiple columns, the sorting order is fully performed according to the rules. In other words, for the output in the preceding example, the product is ordered by prod_name only when multiple rows have the same prod_price value. If all values in the prod_price column are unique, they are not sorted by prod_name.

3. sort by column position:SELECTProd_id, prod_price, prod_nameFROMProductsORDER BY2 and 3 are the same as sorting results by multiple columns, except that the original column name is changed to the position sequence number.

As you can see, the output here is the same as the preceding query. The difference lies in the order by clause. The SELECT list specifies the relative position of the selected column rather than the column name. Order by 2 indicates to sort BY the second column prod_name in the select list. Order by 2 and 3 indicates to sort BY prod_price first and then BY prod_name.
The main advantage of this technology is that you do not need to re-enter the column name. But it also has disadvantages. First, it is not clear that column names may be incorrectly sorted by column names. Second, when changing the SELECT list, it is easy to mistakenly sort the data (forgetting to change the order by clause ). Finally, if the columns to be sorted are not in the SELECT list, this technology is obviously unavailable.

4. Specify the sorting direction:SELECTProd_id, prod_price, prod_nameFROMProductsORDERProd_priceDESC

SELECT prod_id, prod_price, prod_name FROM Products order by prod_price DESC, prod_name

The DESC keyword is only applied to the column name directly before it. In the preceding example, only DESC is specified for the prod_price column, but not for the prod_name column. Therefore, the prod_price column is sorted in descending order, while the prod_name column (within each price) is still sorted in standard ascending order.

Warning sort multiple columns in descending order
If you want to sort multiple columns in descending order, you must specify the DESC keyword for each column.
Note that DESC is the abbreviation of DESCENDING. Both keywords can be used. Relative to DESC is ASC (or ASCENDING), which can be specified in ASCENDING order. But in fact, ASC is not very useful, because the ascending order is default (if neither ASC nor DESC is specified, it is assumed as ASC ).
Tip: case sensitive and sorting order
When sorting text data, is A and A the same? Is a located before B or after Z? These questions are not theoretical questions. The answer depends on how the database is set.
In the dictionary sorting order, A is considered to be the same as A, which is the default behavior of most database management systems. However, many DBMS allow database administrators to change this behavior as needed (If your database contains a large number of foreign language characters, you may have to do so ).
The key problem here is that if you really need to change the sorting ORDER, you may not be able to use a simple order by clause. You must ask the database administrator for help.

Ii. filter data

1. where statement:SELECTProd_name, prod_priceFROMProductsWHEREProd_price = 3.49

Warning WHERE clause position
When both the order by and WHERE clauses are used, the order by clause should be placed after the WHERE clause; otherwise, an error will occur. For example:SELECTProd_name, prod_priceFROMProductsWHEREProd_price = 3.49ORDERProd_nameASC

 

2. Check a single value: SELECT prod_name, prod_price FROM Products WHERE prod_price <0

3. mismatch check: SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'dl01'

4. Check the range value: SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10

5. Check for null values: SELECT cust_name FROM customers WHERE cust_email IS NULL

Iii. Advanced Data Filtering

1. AND Operator:SELECTProd_id, prod_price, prod_nameFROMProductsWHEREVend_id = 'dl01'ANDProd_price <= 4

This SQL statement retrieves the names and prices of all products manufactured by the supplier DLL01 at a price less than or equal to 4 USD. The WHERE clause in this SELECT statement contains two conditions, which are joined together with the AND keyword. AND indicates that DBMS only returns rows that meet all given conditions. If a product is manufactured by the supplier DLL01 and its price is higher than 4 USD, It is not retrieved.

AND
A keyword used in the where clause to indicate that a row that meets all given conditions is retrieved.
This example contains only one AND clause, so there are at most two filtering conditions. You can add multiple filtering conditions. The AND keyword must be used between each condition.
Note: There is no order by clause
To save space and reduce your input, I have omitted the ORDER BY clause in many examples. Therefore, your output may be completely different from the output in the book. Although the number of returned rows is always correct, their order may be different. Of course, if you want to add an order by clause, it should be placed after the WHERE clause.
2. OR Operator:SELECTVend_id, prod_price, prod_nameFROMProductsWHEREVend_id = 'dl01'ORVend_id = 'brs01'

This SQL statement retrieves the product names and prices of all products manufactured by any specified supplier. The OR operator tells the DBMS to match any condition rather than both.

3. Order of value (and or)

Input:

SELECTProd_name, prod_priceFROMProducts

WHEREVend_id = 'dl01'ORVend_id = 'brs01'ANDProd_price> = 10

Output:

Question: Please refer to the above results. The returned rows contain4The row price is less10USD. Apparently, the returned rows are not filtered as expected. Why?The reason is the order of value. SQL (like most languages) First processes the AND operator before processing the OR operator. When SQL sees the above WHERE clause, it understands that: All products manufactured by BRS01 with a price of more than 10 US dollars, and all products manufactured by the supplier DLL01, regardless of the price. In other words, operators are incorrectly combined because AND has a higher priority in the evaluation process.

Solution:

SELECT prod_name, prod_price FROM Products

WHERE (vend_id = 'dl01' OR vend_id = 'brs01') AND prod_price> = 10

In fact, you only need to enclose it in parentheses.

Tip: use parentheses in the WHERE clause
The WHERE clause with and or operators should be explicitly grouped using parentheses. Do not rely too much on the default value order, even if it is as expected. Parentheses can eliminate ambiguity.

4. IN Operator

Input:

SELECTVend_id, prod_name, prod_priceFROMProducts
WHEREVend_idIN('L01', 'brs01 ')
ORDER BYProd_name

Output:

Why use the IN operator? Its advantages include:

• When there are many valid options, the IN operator syntax is clearer and more intuitive.
• When IN is combined with other and or operators, the order of evaluation is easier to manage.
• The IN operator is generally executed faster than a group of OR operators (IN the example with few valid options above, you cannot see performance differences ).
• The biggest advantage of IN is that it can contain other SELECT statements and can create a WHERE clause more dynamically. This will be detailed in Lesson 11th.
  

IN
The WHERE clause specifies the keywords of the list for matching values. The function is equivalent to OR.

5. NOT Operator

SELECTProd_nameFROMProducts
WHERE NOTVend_id = 'dl01'
ORDER BYProd_name;

Why NOT? For this simple WHERE clause, using NOT does NOT have no advantages. But in more complex clauses, NOT is very useful. For example, when used together with the IN operator, NOT can easily find rows that do NOT match the condition list.