1, Experimental purpose

1. Master the basic syntax of SELECT statement and the expression method of query conditions;
2. Master query condition expression and usage;
3. Master the function and usage of GROUP BY clause;
4. Master the function and usage of HAVING clause;
5. Master the function and usage of ORDER BY clause.

2, Experimental class hours

2 class hours

3, Experimental requirements

1. Proficient in database query statements;
2. Master the implementation of database query;
3. Complete the experiment report;

4, Experimental content

Based on the data in SQL Experiment 4 database, please use T-SQL statement to realize the following operations:
1. List the basic information of all students not surnamed "Zhang";

select *
from student
where sname not like 'Zhang%'

2. List the basic information of students with surname "Wang" and full name of 3 Chinese characters;

select *
from student
where sname  like 'king%' and len(sname)=3

3. Display the student number and name of students born after 1986;

     select sno,sname
from student
where 2021-sage>1986

4. List student information in the order of "gender, student number, name, age and department", and rename each column;

  select ssex Gender,sno Student number,sname full name,sage Age,sdept Faculty
from student

5. Query the names and student numbers of students who are not assigned departments;

select sno,sname
from student
where sdept='NULL'

6. Display the student number, name, gender, age and Department of the student whose eighth or ninth student number is 1, 2, 3, 4 or 9;

     select sno,sname,ssex,sage,sdept
from student
where sno like '_______[12349]%'

7. List the student numbers and grades of students who have taken the No. 01 course, in descending order of grades;

select sno,grade
from SC 
where cno='1'
order by grade desc
![Insert picture description here](https://img-blog.csdnimg.cn/e5c4508ea5c340dd93ca44b3180cbb1a.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBARG9nYm9zczIzMw==,size_13,color_FFFFFF,t_70,g_se,x_16#pic_center)

8.According to the course number and grade descending order, the course grade is 70-80 Student number, course number and grades of students between;

```sql
select sno,cno,grade
from SC 
where 70<=grade and grade<=80
order by grade,cno desc

9. The "department, student number, name, gender, age" and other information of all students are displayed in descending order of departments. The departments are displayed in accordance with the following provisions: the Department IS CS, the Department IS information, the Department IS MA, the Department IS mathematics, the Department IS EN, the Department IS foreign language, the Department IS CM, the Department IS traditional Chinese medicine, the Department IS WM, and the Department IS western medicine, Others show that the Department IS unknown;

SELECT Faculty=
case
when sdept='CS' then 'Computer Department'
when sdept='IS' then 'Information system'
when sdept='MA' then 'faculty of Mathematics'
when sdept='EN' then 'Department of foreign languages'
when sdept='CM' then 'Department of traditional Chinese Medicine'
when sdept='WM' then 'Department of Western Medicine'
else 'Department unknown'
end,sno Student number,sname as full name,ssex as Gender,sage as Age
FROM student
ORDER BY Faculty desc

10. Show the student numbers of the top two students who take the course 02.

select top 2 sno
from SC 
where cno='2' 
order by grade desc 

11. List the student numbers of all students taking courses "1" and "2" at the same time;
Method 1:

select sno
from SC 
where cno='1'  
Intersect
select sno 
from SC 
where cno='2'

Method 2:

select sno
from SC 
where cno='1' 
and sno in(select sno 
from SC 
where cno='2')

12. Display all departments (it is required that it cannot be repeated, excluding null value), and add a column of field "department scale" in the result set. If the number of departments > = 5, the field value is "large scale". If the number of departments is greater than or equal to 4 but less than 5, the field value is "average scale". If the number of departments is greater than or equal to 2 but less than 4, the field value is "small scale", Otherwise, "small scale" will be displayed;

select  Faculty=sdept,Faculty size=
case
when count(sno)>=5 then 'Large scale'
when count(sno)>=4 then 'Average scale'
when count(sno)>=2 then 'Smaller scale'
else 'Small scale'
end
FROM student
where sdept is not NULL
group by sdept 

13. Display the total number and average age of students in the student information table, and specify "total number of students, average age" in the column title of the result set respectively;

select Total number of students=count(*),average age=AVG(sage)
from student 

14. Display the number of elective courses of each student whose number of elective courses is greater than 2;

      select sno,count(sno)
from SC
group by sno having count(sno)>2

15. Display the total number, highest score, lowest score and average score of each course in descending order of course number;

select cno,Total number=count(*),Highest score=max(grade),
Minimum score=min(grade),Average score=AVG(grade)
from SC
group by cno
order by cno desc

16. The student number and average score of each student whose average score is greater than that of "Zhao Jingjing";

select sno,Average score=AVG(grade)
from SC
group by sno
having AVG(grade)>(select AVG(grade)
from SC,student
where sname='Zhao Jingjing')
order by AVG(grade) desc

17. Display the student number, name, gender and age of IS students, and count the number of IS students;

select sno,sname,ssex,sage
from student
select distinct IS Number of students=count(*)
from student 
where sdept='IS'

18. Display the student number with the largest number of elective courses and the student number with the smallest number of elective courses;

select top 1 sno
from SC
group by sno
order by count(*)
select top 1 sno
from SC
group by sno
order by count(*) desc

19. Display the first two records of students in each department and form a new form ceshi;

select student.* into ceshi
from student 
where sno in (select top 2 sno
from student 
where sdept=student.sdept
order by sdept desc)

20. * show the number of successful candidates for each course;

select cno,Number of pass=count(*)
from SC
where grade>=60
group by cno

21. * display the number of male and female students in each department, in which the column headings in the result set are respectively designated as "department name, number of boys and number of girls";

select Department name=sdept,
Number of boys=count(case when ssex='male' then 1 end),
Number of girls=count(case when ssex='female'then 1 end)
from student
group by sdept

22. * list the student number of a student who has failed in more than two courses (including two) and the average score of the student;

select sno,Average score=AVG(grade)
from SC
where grade<60
group by sno
having count(sno)>=2

Note: the T-SQL statement of each question needs to be written in the experimental report book.

5, Experimental steps

Command method: select new query at the top left of SQL Server Management Studio window, start the SQL editor window, enter T-SQL statement at the cursor, and click execute. For example, the first question can be entered:
select * from student where sname not like 'Liu%'