Database Table query operation (Experiment 2): Database Table query Experiment

Database Table query operation (Experiment 2): Database Table query Experiment

[PURPOSE ]:Understand the use of the SQL language, further understand relational operations, and consolidate the basic knowledge of the database.
[Lab requirements ]:Master various query operations using Select statements: Single-Table query, multi-table join and query, nested query, and set query.
[Experiment content]
I. Single Table query
1. Simple Query
Open the query analyzer, create a teacher table at the root, and add data. All information about the instructor is retrieved from the teacher table, and only the instructor ID, name, and title are queried. The statement is as follows:

select * from teacherselect tno, tname from teacher

To change the display of the column title when querying, then, the instructor ID, name, and email information are retrieved from the teacher table, and the title information such as 'instructor name', 'instructor number', and 'email address' are added respectively.

Select tno employee ID, tname, temail email from teacher

Use the TOP Keyword: The first two teachers' information and the first 67% teachers' information are retrieved from the teacher.

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

Use the DISTINCT Keyword: the instructor title is retrieved from the teacher table and the required titles are not repeated. Select distinct tposition from teacher

2. calculation column: the name, teaching staff number, and salary of each instructor in the teacher table are issued by 95%. The column name of the 2nd statement after the salary is issued by 95% is 'pre-paid wage '. The statement is as follows:

Select tno tname, tsalary * 0.95 from teacherselect tno ID, tname, tsalary * 0.95 AS prepayment from teacher

3. Sort the query results using the order by clause
The order by statement can be used to sort the query results. ASC and DESC are the keywords in ascending and descending ORDER, respectively. The system uses ascending order by default. Query the number and name of instructors whose salaries are greater than 2800 from the teacher table, and sort them in ascending order. The statement is as follows:

select tno, tname from teacherWHERE tsalary>2800 order by tsalary ASC

4. Conditional Query
(1) Use Relational operators: query the instructor information with a workload of more than 288 from the teacher table. The statement is as follows:

select * from teacherWHERE tamount>288 order by tamount DESC

(2) Use the between and predicate: query the instructor information BETWEEN 144 AND 288 in the teacher table. The statement is as follows:

select * from teacher WHERE tamount between 144 and 288

(3) Use the IN predicate: from the teacher table, query the instructor's teaching number, instructor name, title, and home address for the instructor with the title of "professor" or "Associate Professor". The statement is as follows:

Select tno, tname, tposition, taddress from teacherWHERE tposition in ('Professor ', 'associate Professor ')

(4) use the LIKE predicate: retrieve the instructor information with the surname 'King' from the teacher table, or the 2nd words in the name are the information of the instructor of 'lil' or 'xuan'. The statement is as follows:

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

 Ii. Multi-Table query
Different database tables store different data. Users often need to combine the data in multiple tables to extract the required information. If one query requires operations on multiple tables, it is called join query, and the result set or result table of join query is called the connection between tables. In fact, join queries query data through the Association of columns between tables. It is the most basic feature of relational database queries.
1. SQL 2000 is compatible with two connection forms: the ANSI connection syntax for the FROM clause and the SQL SERVER connection syntax for the WHERE clause.
The student ID, name, course number, course name, and course score are retrieved from the student, course, and SC tables. The statement is as follows:

select student.sno, sname, cno, gradefrom student inner join SC on student.sno=SC.snoselect student.sname,sc.gradefrom student,scWHERE student.sno=sc.sno select student.sno,student.sname,sc.cno,course.cname,sc.gradefrom student,sc,course WHERE student.sno=sc.sno and sc.cno=course.cno

2. query using the UNION clause
The UNION clause can be used to display some columns of the same data type in one or more tables in the same column. For example, the instructor ID and name are listed in the teacher table, and the student ID and student name are listed in the student table. The statements and query results are as follows:

Select sno AS student ID or employee ID, sname AS name from student union select tno, tname from teacher

3. query using the GROUP clause
If you want to GROUP and aggregate data in a table or calculate the average value based on certain conditions during data retrieval, you must use the set function together with the group by clause in the SELECT statement. You can use the group by clause for data retrieval to obtain the summary statistics, average values, or other statistical information of data categories.
(1) Use a group by clause without HAVING.
Use the group by clause without HAVING to summarize the student's student ID and total score in the SC table. The statement is as follows:

Select 'student ID '= sno, 'total score' = SUM (Grade) from SCgroup by Sno

(2) Use the group by clause with HAVING.
Use the group by clause with HAVING to summarize the student's student ID and total score with a total score greater than 450 in the SC table. The statement is as follows:

Select 'student ID '= sno, 'total score' = SUM (Grade) from SCgroup by Sno Having SUM (Grade)> 160

4. query using the COMPUTE and compute by clauses
Use COMPUTE and compute by to view both data and statistical results.
(1) Use the COMPUTE clause to summarize the student's student ID and total score in the SC table. The statement is as follows:

Select 'student ID '= sno, 'score' = Grade from SC order by sno COMPUTE SUM (Grade)

(2) Use the "compute by" clause to summarize the student ID and total score of each student in the SC table. The statement is as follows:

Select 'student ID '= sno, 'score' = Grade from SC ORDER BY Sno COMPUTE SUM (Grade) by sno

What are the differences between 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 query the student ID, course number, and score of all boys IN the j10011 class. The statement is as follows:

Select SC. sno, SC. cno, SC. gradeFROM SC WHERE sno IN (SELECT sno FROM student WHERE Sclass = 'j10011' AND Ssex = 'male ')

Use the IN keyword to query the student (student ID, name) with different names from the instructor. The statement is as follows:

Select sno student ID, 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 query the student ID, course number, and score of the 'j10011' class. The statement is as follows:

SELECT SC.sno，SC.cno，SC.grade FROM SCWHERE EXISTS （ SELECT * FROM student WHERE SC.sno=student.sno AND student.sclass=‘j10011' ）

The above is all the content of the Table query operation experiment. I hope it will be helpful for you to learn it. You can practice it yourself.