Experiment 3: single table query

Experimental purpose

  1. Master the application of simple SQL query statements, including the application of conditions, like, top, order by, compute, etc
  2. Master the application of connection query

Experimental content

1. Basic query

(1) Query all the information of all students in the Student table

use student_info;
select * from Student;


(2) Query the student number and name of all students

select Sno,Sname from Student;

2. Change the display of column headings during query

Query the student number, name and home address information of all students, and add the alias information of "student", "student number" and "home address" respectively

select Sno Student number,Sname full name,Home_addr Home address 
from Student;

3. Condition query

(1) Query the student number, course number and score of students with scores greater than 80

select * from SC where Grade > 80;

(2) Query the student number, course number and score of students with scores of 75 ~ 80

select * from SC where Grade >= 75 and Grade < 80;

(3) Query the student number of students with elective course number "002" and score greater than 80

select Sno from SC 
where Cno = '002' and Grade > 80;


Since I did not enter qualified data, it is displayed here. Then I enter some qualified data and execute the above statement, as shown below

(4) Some students did not take the exam after taking a course, so there are course selection records, but there are no examination results. Please check the student number and corresponding course number of the students who lack the results
(since there was no qualified data, I entered some qualified data)

select Sno,Cno from SC where Grade is null;

4. Data query based on IN Clause

Query all the information of "high number" and "C language programming" from the Course table

select * from Course 
where Cname in ('High number','C Language programming');

5. Query based on BETWEEN... AND clause

Query the course selection information of all students with scores between 70 and 80

select * from SC where Grade between 70 and 80;

6. Query based on LIKE clause

(1) Retrieve the information of all students with the surname "Zhang" from the Student table

select * from Student where Sname like 'Zhang%';

(2) Search the information of all students whose second word of name is "red" or "Hong"

select * from Student 
where Sname like '_red%' or Sname like '_rainbow%';

(3) Query the credits of the course named Visual_Basic (tip: use ESCAPE phrase)

select Credit from Course 
where Cname like 'Visual\_Basic' escape '\';

7. Use the TOP keyword to query

(1) Retrieve the information of the first three courses from the course selection table

select top 3 * from Course;

(2) Retrieve the first 20% of course information from the course selection table

select top percent 20 * from Course;

8. De duplication of lines

Retrieve the course number of the student's selected course. It is required that the displayed course number is not repeated

select distinct Course.Cno from Course, SC 
where Course.Cno = SC.Cno ;

9. Query calculated value

Query the names and ages of all students (prompt: use the system function GETDATE())

select Sname full name,year(getdate())-year(Birth) Age from Student;

10. Use the ORDER BY statement to sort the query results

(1) Display the basic information of all students, arranged by class number. If the class number is the same, then arranged by student number

select * from Student order by Classno ,Sno;

(2) Query the names and ages of all students, and arrange them in descending order of students' ages

select Sname full name,year(getdate())-year(BIrth) Age 
from Student 
order by Age desc;

11. Use aggregate function

(1) Query the total number of students

select count(*) Total number from Student;

(2) Calculate the average score, the highest score and the lowest score of the students who took the "002" course

select avg(Grade) average,max(Grade) Highest score,min(Grade) Lowest score
from SC 
where Cno = '002';

12. Query with GROUP clause

(1) Query the total number of students in each class

select Classno Class number,count(*) Total number
from Student
group by Classno;

(2) Summarize the student number and total score of students with a total score greater than 150

select Sno Student number,sum(Grade) Total score
from SC
group by Sno 
having sum(Grade)>150;

(3) Query the number of selected courses corresponding to each course number

select Cno Course number,count(*) Number of selected courses
from SC
group by Cno;

13. Use the COMPUTE and COMPUTE BY clauses to query

The compute clause can be used in the 2008 version, so you need to practice. You can download the 2008 version of SQL server
(1) Summarize the student number and total score of each student

select Sno Student number,Grade Total score
from SC
compute sum(Grade);

(2) Summarize the student number and total score of each student according to the student number, the highest score, the lowest score and the total score of all students, and observe the difference between the execution results using the COMPUTE clause and the COMPUTE BY clause?

select *
from SC
order by Sno    --Sort by student number
compute sum(Grade)
compute sum(Grade),max(Grade),min(Grade) by Sno;

Tags: Database SQL Server SQL

Posted on Tue, 30 Nov 2021 21:54:55 -0500 by servo