1, Experimental purpose
- Master the basic syntax of SELECT statement and the expression method of query conditions;
- Master query condition expression and usage;
- Master the function and usage of GROUP BY clause;
- Master the function and usage of HAVING clause;
- Master the function and usage of ORDER BY clause.
2, Experimental class hours
2 class hours
3, Experimental requirements
- Proficient in database query statements;
- Master the implementation of database query;
- 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 '_______%'
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;
select sno from SC where cno='1' Intersect select sno from SC where cno='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%'