Query operations for Database tables (Experiment II) _mssql

"experimental purposes": 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, multiple table connection and query, nested query, set query.
"Experimental Content"
one, one-table query
1. Simple query
Open Query Analyzer, root to create teacher table, and add data. All the teachers ' information was retrieved from the teacher table, and only the faculty number, name and title were queried. Statement as follows:

SELECT * FROM teacher
Select TNO, tname from teacher

If you want to query to change the display of column headings, then from the teacher table to retrieve the teacher number, name, e-mail information and add ' teacher name ', ' Staff number ', ' e-mail ' and other title information.

Select TNO work number, tname name, Temail e-mail from teacher

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

Select top 2 * "From teacher
Select" percent * from teacher

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

2. Use computed column: The teacher table in the teacher's name, teachers number and wages in accordance with the information issued by 95%, the 2nd statement will pay 95% after the issuance of the name of the "advance pay." Statement as follows:

Select Tno tname, tsalary*0.95 from teacher
Select TNO work number, tname name, tsalary*0.95 as advance salary from teacher

3. To sort the results of a query by using the ORDER BY clause
You can sort the results of a query by using the order BY statement, and ASC, desc are keywords in ascending and descending order respectively, and the system defaults to ascending order. From the teacher table, query the teacher number and name of teachers with a salary greater than 2800, and in ascending order, the statement reads as follows:

Select TNO, tname from teacher
WHERE 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 teacher data, the statement is as follows:

SELECT * FROM Teacherwhere tamount>288 ORDER by Tamount DESC

(2) Use the Between and predicate: from the teacher table to query the workload bounded by 144 and 288 teacher data, the statement is as follows:

SELECT * FROM teacher WHERE Tamount between 144 and 288

(3) Use in predicate: from the teacher table to query the title of "Professor" or "Associate professor" Teacher's faculty number, teacher's name, title and home address, the statement is as follows:

Select Tno,tname,tposition, taddress from teacher
WHERE Tposition in (' Professor ', ' Associate Professor ')

(4) using the LIKE predicate: Retrieve the information of the teacher surnamed ' Wang ' from the teacher table, or the 2nd Word of the name is ' Li ' or ' Xuan ' 's teacher's information, the statement is as follows:

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

Second, multiple table query
Database tables contain different data, users often need to use a number of tables to combine the data to extract the required information, if a query needs to operate on more than one table, called the associated query, the result set of the associated query or the result table is called the connection between the tables. The association query is actually querying the data through the association of the common columns among the tables, which is the most basic feature of relational database query.
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.
From the student, course and SC three tables to retrieve student number, name, study Course number, study course name and course results, the statement is as follows:

Select Student.sno, sname, CNO, grade from
student inner join SC on Student.sno=sc.sno

Select student.sname,sc.gr Ade
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 using the UNION clause
You can use the union clause to display columns of one or more tables with the same data type as the same column. If you list the faculty number and name from the teacher table and list the number and student's name from the student table, the statement and query results 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 a group clause
If you want to group or average the data in a table in a certain condition in a data retrieval, use the aggregate function with the GROUP BY clause in the SELECT statement. Data retrieval with a GROUP BY clause results in summary statistics, averages, or other statistics for the data classification.
(1) Use a GROUP BY clause with no having.
The student's number and total score in the SC table are summarized using the GROUP BY clause without having, and the statement is as follows:

Select ' School Number ' =sno, ' total score ' =sum (Grade) from 
SC
GROUP by Sno

(2) The GROUP BY clause with having is used.
Use the GROUP BY clause with having to summarize the student number and total score of the SC table with a score greater than 450 points, as follows:

Select ' School Number ' =sno, ' total score ' =sum (Grade) from SC
GROUP by Sno has 
SUM (Grade) >160 

4. Querying with COMPUTE and COMPUTE by clauses
using compute and COMPUTE by both browsing the data and seeing the results of the statistics.
(1) The COMPUTE clause is used to sum up the number and total score of each student in SC table, and the statement is as follows:

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

(2) using COMPUTE BY clause to sum up the number and total score of each student in SC table, the statement is as follows:

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

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

5. nested queries
(1) using in or not in keyword
use in keyword to query all boys in j10011 class number, course number and corresponding results, statements are as follows:

Select Sc.sno, Sc.cno,sc.grade from
SC 
WHERE sno into 
 (SELECT sno from student
 WHERE sclass= ' j10011 ' and S Sex= ' man ')

Use the IN keyword to query students with the same name as the teacher (student number, name), and 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 query the "j10011" class student number, course number and corresponding results, the statement is as follows:

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 of the query operation of all the contents of the experiment, I hope to help you learn, everyone hands-on practice.