SQL from zero to Quick mastery of "query tool"

1. Column selection gets the F_name and f_price two columns from the Fruits table, and the T-SQL statements are as follows.

SELECT f_name,f_price from Fruits;

2. "Deduplication" queries the value of the s_id field in the Fruits table and returns s_id field values must not be duplicated.

SELECT DISTINCT s_id from fruits;

3. "Column renaming" Query fruits table, for f_name alias ' name ', F_price take the alias ' price ', T-SQL statement as follows:

SELECT f_name as ' name ', f_price as ' price '

from fruits;

4. "Comments displayed by the query add"

(1) Query the Fruits table and add descriptive information to s_id and f_id in the table.

SELECT ' Supplier number: ', s_id, ' fruit number ', f_id from fruits;

(2) Query the name and price of all fruits in the fruits table, and hit 80 percent on the price

SELECT F_name,f_price Price, F_price * 0.8 discount from fruits;--the equivalent of omitting as

5.EXISTS

Query the Suppliers table for the presence of s_id=107 vendors, and if so, query the records in the fruits table, as in the T-SQL statement below.

SELECT * from Fruits

WHERE EXISTS

(SELECT s_name from suppliers WHERE S--id =107);

6. Multi-column sorting

Query the F_name and F_price fields in the Fruits table, sort by f_name, and then sort by f_price, and the T-SQL statements are as follows.

SELECT f_name,f_price from Fruits ORDER by f_name,f_price;

7.***group by group

(1) The data in the fruits table is grouped according to s_id, and the T-SQL statement is as follows.

SELECT S_id,count (*) as total from fruits GROUP by s_id;

(2) The data in the fruits table is grouped according to the s_id and F_name fields, and the T-SQL statements are as follows.

SELECT s_id,f_name from Fruits Group by s_id,f_name;

8.HAVING grouping result filtering "and group by Federated use"

According to S_ID, the data in the fruits table is grouped and the group information of fruit species greater than 1 is displayed, with the following statement:

SELECT S_id,count (*) as total from fruits

GROUP by s_id have COUNT (*) >1;

9. Subtotal query results using the COMPUTE clause

The GROUP by clause returns only aggregate data in the result set, without the original verbose record, but compute can display detailed records,

Use the COMPUTE clause to produce the sum of the f_price fields in the fruits table, requiring the query results to be sorted by the s_id field before returning the sum of the F_price fields.

Use test

SELECT S_id,f_price,f_name

From fruits

ORDER by s_id

COMPUTE SUM (F_price);

10. Grouping subtotals using the COMPUTE BY clause

11. Using Union to merge query result sets

Query the information of all fruits with prices less than 9, query s_id equals 101 of all fruit information, use union ALL to join the query results, the statement is as follows.

SELECT S_id,f_name,f_price

From fruits

WHERE F_price <9.0

UNION All

SELECT S_id,f_name,f_price

From fruits

WHERE s_id = 101;

12.**** using COUNT () statistics

(1) COUNT (*): Calculates the total number of rows in a table, regardless of whether a column has a value or is null;

(2) COUNT (field name): Calculates the total number of rows under the specified column, ignoring rows where the field value is null.

SQL from zero to Quick mastery of "query tool"