Query operation of database table (Experiment 2)

Time:2021-2-26

The purpose of the experiment was to investigate the effect of the treatmentUnderstand the use of SQL language, further understand the relationship operation, consolidate the basic knowledge of database.
[experimental requirements]:Master the use of select statements for a variety of query operations: single table query, multi table connection and query, nested query, set query, etc.
[experiment content]

1、 Single table query
1. Simple query
Open the query analyzer, create the teacher table and add the data. All the information of teachers can be retrieved from the teacher table, and only the number, name and professional title of teachers can be queried. The sentence is as follows:


select * from teacher
select tno, tname from teacher

If you want to change the display of the column title when querying, you can retrieve the teacher’s teaching staff number, name and e-mail information from the teacher table, and add the title information such as’ teacher’s name ‘,’ teaching staff number ‘and’ e-mail ‘respectively.

Select TNO job number, tname name, email from teacher

Use the top keyword: retrieve the first two items and 67% of the teachers’ information from the teacher.


select top 2 * from teacher
select top 67 percent * from teacher

Use the distinct keyword: retrieve the teacher’s professional title from the teacher table, and the displayed professional title should not be repeated. select  distinct tposition   from  teacher

2. Use calculation column: the name, staff number and salary of each teacher in the teacher table are paid according to 95%. In the second sentence, the salary is paid according to 95% and listed as “advance salary”. The sentence is as follows:

select tno tname ,tsalary*0.95 from teacher
Select TNO job number, tname name, tsalary * 0.95 as advance salary from teacher

3. Use the order by clause to sort the query results
The order by statement can be used to sort the query results. ASC and desc are keywords in ascending and descending order respectively. The system default is ascending order. From the teacher table, query the number and name of teachers whose salary is more than 2800, and arrange them in ascending order


select tno, tname from teacher
WHERE tsalary>2800 order by tsalary ASC

4. Condition query
(1) Use the relational operator: query the information of teachers whose workload is more than 288 from the teacher table


select * from teacherWHERE tamount>288 order by tamount DESC

(2) Use the between and predicate: query the teacher information whose workload is between 144 and 288 from the teacher table


select * from teacher WHERE tamount between 144 and 288

(3) Use the in predicate: from the teacher table, find out the teaching staff number, teacher’s name, professional title and home address of the teacher whose professional title is “Professor” or “associate professor”

select tno,tname,tposition, taddress from teacher
Where tposition in

(4) Use the like predicate: retrieve the information of the teacher whose surname is’ Wang ‘or whose second word is’ Li’ or ‘Xuan’ from the teacher table

Select * from teacher where tname like 'Wang%'
select * from teacher WHERE tname like '_ [Li, Xuan]% '

 2、 Multi table query
Different data are stored in each table of the database. Users often need to combine the data in multiple tables to extract the required information. If a query needs to operate on multiple tables, it is called Association query. The result set or result table of association query is called connection between tables. In fact, association query queries data through the association of common columns among tables, which is the most basic feature of relational database query.
1. SQL 2000 is compatible with two connection forms: ANSI connection syntax for from clause and SQL Server connection syntax for where clause.
Search student number, name, course number, course name and course score from student, course and SC


select student.sno, sname, cno, grade
from student inner join SC on student.sno=SC.sno

select student.sname,sc.grade
from student,sc
WHERE student.sno=sc.sno 

select student.sno,student.sname,sc.cno,course.cname,sc.grade
from student,sc,course 
WHERE student.sno=sc.sno and sc.cno=course.cno

2. Query with union clause
The union clause can be used to display some columns of the same data type in one or more tables on the same column. For example, the teacher number and name are listed in the teacher table, and the student number and student name are listed in the student table

Select SnO as student number or job number, sname as name from student 
union 
select tno, tname from teacher

3. Query with group clause
If you want to group and summarize or average the data in the table according to certain conditions during data retrieval, you need to use the set function together with the group by clause in the select statement. Using group by clause to retrieve data can get summary statistics, average value or other statistical information of data classification.
(1) Use the group by clause without having.
Use the group by clause without having to summarize the student number and total score of the students in the SC table

Select 'student number' = SnO, 'total score' = sum (grade) 
from SC
group by Sno

(2) Use the group by clause with having.
Use the group by clause with having to summarize the student number and total score of the students whose total score is more than 450 in SC table

Select 'student number' = SnO, 'total score' = sum (grade) from SC
group by Sno 
Having SUM(Grade)>160

4. Query with compute and compute by clauses
Using compute and compute by, you can not only browse the data, but also see the statistical results.
(1) Use the compute clause to summarize the student number and total score of each student in the SC table

Select 'student number' = SnO, 'grade from SC' 
order by sno COMPUTE SUM(Grade)

(2) Use the compute by clause to summarize the student number and total score of each student in the SC table

Select 'student number' = SnO, 'grade from SC' 
ORDER BY Sno COMPUTE SUM(Grade) by sno

What’s the difference between observing the results of executing the compute and compute by clauses?

5. Nested query
(1) Use the in or not in keyword
Use the in keyword to find out the student number, course number and corresponding grades of all boys in class j10011

select SC.sno , SC.cno,SC.grade
FROM SC 
WHERE sno IN 
 ( SELECT sno FROM student
 Where class ='j10011 'and ssex ='male')

Use the in keyword to find out the students (student number, name) with different names from the teacher

Select SnO student number, sname name 
from student 
where sname not in (select tname from teacher)

(2) Use the exists or not exists keyword.

Use the exists keyword to find out the student number, course number and corresponding grades of the students in class’ j10011 ‘


SELECT SC.sno,SC.cno,SC.grade 
FROM SC
WHERE EXISTS 
( SELECT * FROM student
 WHERE SC.sno=student.sno AND student.sclass=‘j10011' )




The above is the table query operation experiment all content, hope to help you learn, we personally practice.