Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The second part of T-SQL query statement (multi-table query)

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Summary of T-SQL query statement

Security code: where there is a will, there is a way, burn one's bridges and sink the boat, hundred and two Qin Guan will eventually belong to Chu.

Case 6: the table is shown in the following figure

It appears in the database as:

Lab requirements:

1. Two table queries

Select student form. Name, student table 1. Telephone, student table 1. Home address

From student table, student table 1

Where student form. Student id= Student Table 1. Student id

2. Query of three tables

Select student form. Name, course schedule. Course name, transcript. Score

From student table, course schedule, grade sheet

Where student form. Student id= score sheet. Student id AND score sheet. Course ID= course schedule. Course ID

3. Merge two result sets

SELECT name, student number, class, nationality

FROM Class two

UNION

Select surname +''+ first name, student number, class, nationality

Class one of from

Order by class desc, student number asc

4. Internal connection (find the name and CNT score of the students taking the exam, telephone number)

Select s. Name, s. Phone, c.CNT

FROM student basic information form AS S INNER JOIN student grade sheet AS C

ON S. Name = C. Name

5. Left outer link (look up the names and CNT scores of the students in this class, telephone number)

Select s. Name, s. Phone, c.CNT

FROM student basic information form AS S LEFT OUTER JOIN student grade sheet AS C

ON S. Name = C. Name

6. Right outer connection (find the name and CNT of the students taking this exam, telephone number)

Select s. Name, s. Phone, c.CNT

FROM student basic information form AS S RIGHT OUTER JOIN student grade sheet AS C

ON S. Name = C. Name

7. Full connection (find out the examination status of all students)

Select s. Name, s. Phone, c.CNT

FROM student basic information form AS S FULL OUTER JOIN student grade sheet AS C

ON S. Name = C. Name

8. Self-connect (find the name of each employee's boss)

SELECT Y. Employee name, s. Employee name AS boss name

From employee information table as y inner join employee information table as s

On y. Boss ID=S. Employee ID

9. Subquery (find production quantity per quarter, percentage)

Select quarter, sum (production quantity) AS production quantity per quarter

Str ((sum (production quantity) / (select sum (production quantity) from production Table)) * 100) +'%'AS percentage

From production table

Group by quarter

Order by quarter

10. Use SELECT in the where clause to find students with course ID=1 and scores > 80

Select student form. Name

From student table

Where 80

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 281

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report