SQL Server Single Table Data Query Example Explanation

catalogue1 Import data into SQL Server2 Example questions1. Query all information of all students2

1 Import data into SQL Server

After successful import, you can click on the table to view the content:

(As shown in the following figure, the import was successful)

Other tables can be obtained similarly, and can also be imported simultaneously

2 Example questions

1. Query all information of all students

SELECT*FROMstudent

2. Query the student's student ID and name

SELECTsno,snameFROMstudent

3. Query the names and years of birth of all students

SELECTsname,YEAR(GETDATE())-sageFROMstudent

4. Query the names and year of birth of all students, and ensure that the names are in Chinese characters

SELECTsname name, YEAR (GETDATE()) - page Year of birth FROMstudent

5. Query all student information of the CS department

select*FROMstudentWHEREsdept='CS'

6. Query the information of all male students in the CS department

select*FROMstudentWHEREsdept='CS' AND ssex=''

7. Search for information on students aged between 18 and 20

(1) Method 1

select*fromstudentwheresage>= 18andsage<= twenty

(2) Method 2

select*fromstudentwheresageBETWEEN'18'AND'20'

8. Query information for students aged between 18 and 20

(1) Method 1

select*fromstudentwheresage< 18orsage> twenty

(2) Method 2

select*fromstudentwheresagenotBETWEEN'18'AND'20'

9. Query student information for CS and JD departments

(1) Method 1

select*fromstudentwheresdept='CS'orsdept='JD'

(2) Method 2

select*fromstudentwheresdeptin('CS','JD')

10. Query the information of students surnamed Zhang

Select * from studentwhere namelike 'sheet%'

11. Search for the student's student ID and name with only two characters and surname Li

Selectsno, snamefrom studentWHERE EsnameLIKE 'Li _';

12. Query the student ID and course ID of students without grades

select*fromscWHEREgradeISNULL;

13. Eliminating duplicate rows: Viewing the age profile of the CS series

selectDISTINCTsageFROMstudentWHEREsdept='cs'

14. Query the information of the first three classmates

selectTOP3*fromstudent

15. Counting the number of male students

(1) Method 1

SelectCOUNT (sno) Number of boys from studentwhessex='Male'

(2) Method 2

SelectCOUNT (*) AS Number of boys from studentwhessex='Male'

16. Count the number of students who have taken courses in the SC table (note to remove the student ID of students who have repeatedly taken courses)

selectCOUNT(distinctsno)fromsc

17. Query the highest and lowest scores of all grades from Table sc

Select max (grade) highest score, MIN (grade) lowest score from sc

18. Calculate the total and average grades of students with student ID 1204304102

Select SUM (grade) total score, AVG (grade) average score from scwhere no='1204304102 '

3 Summary

(1) There are three ways to specify column names

• I. Column Name Column Title (e.g. sname name)
• II. Column Name AS Column Title (e.g. snameAS name)
• III. Column Title=Column Name (e.g. Name=Column Title)

Selectsname name, YEAR (GETDATE()) - age as year of birth, department=sdeptfrom student

(2) Search for topic category summary based on name information

Search for information about students surnamed Zhang

Select * from studentwhere namelike 'sheet%'

Search for information about students surnamed Huang (when there is only one character after their surname)

Select * from studentwhere namelike 'yellow _'

Search for&quo; in the name; Wen” The information of the student in this word

select*fromstudentwheresnamelike'_ Wen _ '

(3) Query knowledge expansion

Query the information of the first three classmates

selectTOP3*fromstudent

Check if the course number is‘ 2102’ Rank the course scores in descending order

selectgradefromscwherecno='2102'orderbygradedesc

Check if the course number is‘ 2102’ Rank the course scores in descending order

selectTOP2gradefromscwherecno='2102'orderbygradedesc

summary

This is an article about SQL; That's all for the article on SQL Server single table data query. For more related content on SQL Server single table data query, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!