- Master the application of simple SQL query statements, including the application of conditions, like, top, order by, compute, etc
- Master the application of connection query
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;