Query operation of database table (experiment two), Database table query experiment

"Experimental Purpose": understand the use of SQL language, further understanding of relational operations, consolidate the basic knowledge of the 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, collection query and so on.
"Experimental Content"
One, single-table query
1. Simple query
Open Query Analyzer, root build teacher table, and add data. Each of the teachers ' information is retrieved from the teacher table, and only the faculty number, name and title are queried. The statements are as follows:

SELECT * from Teacherselect TNO, tname from teacher

If you want to change the display of column headings, we will retrieve the teacher's staff number, name, e-mail message and the title information of the teachers ' name, teacher's number and e-mail address separately from the teacher table.

Select TNO Number, tname name, Temail e-mail from teacher

Use the TOP keyword: Retrieve the first 2 and 67% teacher's information from the teacher respectively.

Select Top 2 * from teacherselect top percent * from teacher

Use the DISTINCT keyword: Retrieves a teacher's job title from the teacher table and requires that the titles displayed are not duplicated. Select distinct tposition from teacher

2. Computed column: The teacher table in the name of the teachers, staff and wages by 95% issued information, the 2nd statement will pay 95% after the issuance of the name of the "pre-paid wages." The statements are as follows:

Select Tno tname, tsalary*0.95 from Teacherselect tno work number, tname name, tsalary*0.95 as pre-payroll from teacher

3. To sort the results of a query by using the ORDER BY clause
The order BY statement allows you to sort the results of the query, with ASC and DESC being the keywords in ascending and descending order, with the default ascending order. From the teacher table, query the faculty number, name, and order of the teachers whose salaries are greater than 2800, as follows:

Select TNO, tname from Teacherwhere tsalary>2800 order by Tsalary ASC

4. Conditional query
(1) Using relational operators: From the teacher table to query the workload of more than 288 of the teacher data, the following statement:

SELECT * FROM Teacherwhere tamount>288 ORDER by Tamount DESC

(2) using between and predicates: from the teacher table, the teacher data between 144 and 288 is queried, with the following statement:

SELECT * FROM teacher WHERE Tamount between 144 and 288

(3) using the IN predicate: from the teacher table, the teachers ' faculty number, teacher's name, title and home address of the teacher whose title is "Professor" or "Associate Professor" are queried as follows:

Select Tno,tname,tposition, taddress from Teacherwhere tposition in (' Professor ', ' Associate Professor ')

(4) using the LIKE predicate: from the teacher table to retrieve the surname ' king ' of the teacher's information, or the name of the 2nd word is ' li ' or ' Xuan ' the teacher's information, the statement is as follows:

SELECT * from teacher where tname like ' King% ' select * from teacher WHERE tname like ' _[Lisa, Xuan]% '

second, multi-table query
Database tables contain different data, users often need to use data from multiple tables to extract the required information, if a query needs to operate on more than one table, called an association query, the result set of the associated query or the result table is called a connection between the tables. Associative queries are actually querying data through the association of common columns between tables, which is the most basic feature of relational database queries.
1. SQL 2000 is compatible with 2 connection forms: The ANSI connection syntax for the FROM clause and the SQL Server Connection syntax form for the WHERE clause.
Student, course and sc three tables are searched for student's number, name, course number, course name and course result, as follows:

Select Student.sno, Sname, CNO, gradefrom student inner join SC on Student.sno=sc.snoselect student.sname,sc.gradefrom Stu Dent,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. Querying using the Union clause
You can use the union clause to display columns of the same column for some data types of one or more tables. If the staff number and name are listed in the teacher table, and the student number and name are listed in the student table, the sentences and the results of the enquiry are as follows:

Select Sno as study number or work number, sname as name from student Union Select TNO, tname from teacher

3. Querying with the group clause
Use aggregate functions with the GROUP BY clause in a SELECT statement if you want to group or average the data in the table when the data is retrieved by a certain condition. Data retrieval using the GROUP BY clause results in aggregated statistics, averages, or other statistical information for the data classification.
(1) Use the GROUP BY clause without having a.
Use the GROUP BY clause without having to summarize the student's number and total scores in the SC table, with the following statement:

Select ' Study number ' =sno, ' Total ' =sum (Grade) from Scgroup by Sno

(2) Use the GROUP BY clause with having a.
Use the GROUP BY clause with having to summarize the student number and total score of the SC table for students with scores greater than 450 points, with the following statement:

4. Querying with the COMPUTE and COMPUTE BY clauses
Use compute and compute by to both browse the data and see the results of the statistics.
(1) Use the COMPUTE clause to summarize the number of each student in the SC table and the total scores, the following statement:

Select ' Study number ' =sno, ' score ' =grade from SC order by Sno COMPUTE SUM (Grade)

(2) Use the COMPUTE BY clause to summarize each student's number and total scores in the SC table as follows:

Select ' Study number ' =sno, ' score ' =grade from SC ORDER by Sno COMPUTE SUM (Grade) by Sno

What is the difference between observing the results of executing the COMPUTE and COMPUTE by clauses?

5. nested queries
(1) using the in or not keyword
Use in keyword to find out all the boys in j10011 class number, course number and corresponding results, the following statements:

Select Sc.sno, Sc.cno,sc.gradefrom SC where Sno in  (SELECT sno from student WHERE sclass= ' j10011 ' and ssex= ' men ')

Use the In keyword to query for students who do not have the same name as the teacher (student number, name), with the following statement:

Select Sno Number, sname name from student where sname not in (select Tname from teacher)

(2) Use exists or not EXISTS keywords.

Use the EXISTS keyword to find out the student's number, course number and corresponding result in ' j10011 ' class, with the following sentences:

Select Sc.sno,sc.cno,sc.grade from Scwhere EXISTS (SELECT * FROM student WHERE Sc.sno=student.sno and student.sclass= ' J10 011 ')

Query operation of database table (experiment two), Database table query experiment