SQL Server T-SQL advanced query

Advanced queries are most frequently used in databases and are the most widely used.

Ø Basic Common Query

--select

SELECT * from student;

--all Query All

Select all sex from student;

--DISTINCT filtering repetition

Select distinct sex from student;

--count Statistics

Select COUNT (*) from student;

Select count (Sex) from student;

Select count (Distinct sex) from student;

--top fetch the first n records

Select Top 3 * from student;

--alias Column Name Rename name

Select ID as number, name ' names ', sex gender from student;

--alias Table Name Table rename

Select ID, name, s.id, s.name from student s;

--column column Operations

Select (age + ID) col from student;

Select S.name + '-' + c.name from classes C, student s where s.cid = C.id;

--where conditions

SELECT * FROM student where id = 2;

SELECT * FROM student where ID > 7;

SELECT * FROM student where ID < 3;

SELECT * FROM student where ID <> 3;

SELECT * FROM student where ID >= 3;

SELECT * FROM student where ID <= 5;

SELECT * FROM student where ID!> 3;

SELECT * FROM student where ID!< 5;

--and and

SELECT * FROM student where ID > 2 and sex = 1;

--or or

SELECT * FROM student where id = 2 or sex = 1;

--between ... Equivalent and

SELECT * from student where ID between 2 and 5;

SELECT * FROM student where ID not between 2 and 5;

--like Fuzzy Query

SELECT * from student where name is like  '%a% ';

SELECT * from student where name is like  '%[a][o]% ';

SELECT * from student where name is not like  '%a% ';

SELECT * from student where name is like ' ja% ';

SELECT * from student where name is not like '%[j,n]% ';

SELECT * from student where name is like '%[j,n,a]% ';

SELECT * from student where name is like '%[^ja,as,on]% ';

SELECT * FROM student where name like '%[ja_on]% '

--in Sub-query

SELECT * from student where ID in (1, 2);

--not in not one of them

SELECT * FROM student where ID not in (1, 2);

--is Null is empty

SELECT * FROM student where-is null;

--is NOT NULL is not empty

SELECT * FROM student where-is not null;

--order by sort

SELECT * FROM student order by name;

SELECT * FROM student order by name Desc;

SELECT * FROM Student order by name ASC;

--group by group

Group statistics by age

Select count (age), age from student group by age;

Group statistics by gender

Select COUNT (*), sex from student group by sex;

Grouping statistics by age and gender combination and sorting

Select COUNT (*), sex from student group by sex, age order by age;

Grouped by gender and are records with IDs greater than 2 finally sorted by sex

Select COUNT (*), sex from student where ID > 2 GROUP by sex Order by sex;

Querying data with IDs greater than 2, and grouping and sorting results after completion of operations

Select COUNT (*), (Sex * ID) New from student where ID > 2 GROUP BY sex * ID of ORDER BY sex * ID;

--group by all Group

Group BY age, is all age

Select COUNT (*), age from student group by all age;

--having Grouping Filter conditions

Group BY age, filter age-free data, and count the number of grouped bars and actual age information

Select COUNT (*), age from student group by age has an age was not null;

Grouped by age and CID combination, the filter condition is a record of CID greater than 1

Select COUNT (*), CID, sex from student group by CID, sex have cid > 1;

Grouped by age and CID combination, the filter condition is a record of CID greater than 1

Select COUNT (*), CID, sex from student group by CID, sex have cid > 1;

By age group, the filter condition is grouped after the number of record bars is greater than or equal to 2

Select COUNT (*), age from student group by age has count (age) >= 2;

Grouped by CID and gender combination, the filter condition is that the maximum value of CID greater than 1,cid is greater than 2

Select COUNT (*), CID, sex from student group by CID, sex have cid > 1 and Max (CID) > 2;

SQL Server T-SQL advanced query