SQL subordinate statements

Select Upper (n_id) from Nrc_news;
Select Left (n_content,1) from Nrc_news;
Select Len (n_content) from Nrc_news;

Left () returns the character of a string
Len () returns the length of the string
Lower () Convert lowercase
Right () returns the character to the left of the string
SOUNDEX () returns the Soundex value of the string
Upper () Convert uppercase

Select N_content from Nrc_news where DATEPART (yy,n_publishtime) = 2015;
Select N_content from Nrc_news where DATEPART (yyyy,n_publishtime) = 2015;
Numeric processing functions:
ABS () returns the absolute value of a number
COS () returns the cosine of an angle
EXP () returns the exponential value of a number
Pi () returns PI
Sin () returns the sine of a number
SQRT () returns the square root of a number
Tan () returns the tangent of a number

Select ABS (n_id) from Nrc_news;
Select cos (n_id) from Nrc_news;
Select exp (n_id) from Nrc_news;
Select Pi () from Nrc_news;
Select SIN (n_id) from Nrc_news;
Select sqrt (n_id) from Nrc_news;
Select Tan (n_id) from Nrc_news;

Summarize data
AVG () returns the average of a column
COUNT () returns the number of rows in a column
Max () returns the maximum value of a column
MIN () returns the minimum value of a column
SUM () returns a column's sum
Select AVG (t_id) from Nrc_news;
Select COUNT (t_id) from Nrc_news;
Select Max (t_id) from Nrc_news;
Select min (t_id) from Nrc_news;
Select SUM (t_id) from Nrc_news;
Note:
AVG () can only be used to determine the average value of a particular numeric column, and it must be given as a function parameter, if you want to get the average of multiple columns, you must use more than one AVG ()
The AVG () function ignores rows where the column value is null

Select COUNT (*) from nrc_news where t_id=10;

Data grouping
Select T_id,count (*) as number from Nrc_news group by t_id;
Annotations:
The above SELECT statement has two columns, t_id and number (calculated fields). The GROUP BY clause instructs the database to sort and group data by t_id. This calculates the number for each t_id instead of the entire table
This outputs the number of rows corresponding to each t_id

Select T_id,count (*) as number from Nrc_news GROUP by T_ID have Count (*) >=2;
Note:
The difference between having and WHERE
Where is filtered before data is grouped, having it filtered after the data is grouped
When used together, where excluded values are not included in the grouping, affecting the having calculation

Working with sub-queries
SELECT * from Nrc_news where t_id in (.);
Select t_id from nrc_news where n_content like '% on% ';

SELECT * from Nrc_news where t_id in (select t_id from nrc_news where n_content like '% on% ');
Note:
A SELECT statement as a subquery can query only a single column, and attempting to retrieve multiple columns will return an error
Select r_id,r_content,n_id, (select t_id from Nrc_news where nrc_news.n_id=nrc_review.n_id) as orders from Nrc_review;
Select n_id,n_title,t_id, (select T_memo from Nrc_type where nrc_type.t_id=nrc_news.t_id) as T_memo from Nrc_news
The sub-query above is the key content. Focus on memory.
Select top n_id as ID,
(select N_title from Nrc_news where nrc_news.n_id=nrc_review.n_id) as title,
(select N_content from Nrc_news where nrc_news.n_id=nrc_review.n_id) as content,
(select t_id from Nrc_news where nrc_news.n_id=nrc_review.n_id) as t_id,
(select N_publishtime from Nrc_news where nrc_news.n_id=nrc_review.n_id) as Publishtime,
COUNT (*) as number from Nrc_review Group by n_id Order by number Desc,id;

SQL subordinate statements