T-SQL query Statement Part II (Multi-table query)

A large summary of T-SQL query statements

Security code: A Willing heart, desperate, Hyakuji Qin Guan end belongs to Chu

Case SIX: Table as shown

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/86/3E/wKioL1e50LKzsUzzAAALPc-dNCw164.png-wh_500x0-wm_3 -wmp_4-s_2283953340.png "title=" 93c3 ' ckci7g] (0y26xpwndc.png "alt=" Wkiol1e50lkzsuzzaaalpc-dncw164.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/86/3E/wKioL1e50MvRSodrAAAHHKs83lc791.png-wh_500x0-wm_3 -wmp_4-s_3983022737.png "title=" R%9%rk_h) g}593[k3sj9st4.png "alt=" Wkiol1e50mvrsodraaahhks83lc791.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/86/3E/wKiom1e50OfCehtLAAAOgDV-gx0905.png-wh_500x0-wm_3 -wmp_4-s_3191678258.png "title=" jj2lb (]%qjf7eg_epivu (yc.png "alt=" Wkiom1e50ofcehtlaaaogdv-gx0905.png-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/86/3E/wKiom1e50QrR_CTtAAAItgYZSsQ010.png-wh_500x0-wm_3 -wmp_4-s_2087335804.png "title=" NV9] @Y19%pyr0r1%5neg@~u.png "alt=" Wkiom1e50qrr_cttaaaitgyzssq010.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/86/3E/wKiom1e50SWAK_5nAAAJnkAzris823.png-wh_500x0-wm_3 -wmp_4-s_485172277.png "title=" fjz[8r@%]3o$ @O8F {56FA ' 5.png "alt=" Wkiom1e50swak_5naaajnkazris823.png-wh_50 "/>

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/86/3E/wKioL1e50TaAH5euAAAH7vqljTY586.png-wh_500x0-wm_3 -wmp_4-s_3524682690.png "title=" infm{' 476JG) X (l4e%3ef~y.png "alt=" Wkiol1e50taah5euaaah7vqljty586.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/86/3E/wKioL1e50UyhJMbpAAAJg6YYMZI059.png-wh_500x0-wm_3 -wmp_4-s_1733467735.png "title=" I6{4ca7uv_c) h33m}u (D ' o2.png "alt=" Wkiol1e50uyhjmbpaaajg6yymzi059.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/86/3E/wKiom1e50WDSL36EAAAPrJlKglI220.png-wh_500x0-wm_3 -wmp_4-s_973925380.png "title=" Bmxq6{9dom$nz0%3xztvwqn.png "alt=" Wkiom1e50wdsl36eaaaprjlkgli220.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/86/3E/wKiom1e50XTDZdxOAAAM66RVuT4111.png-wh_500x0-wm_3 -wmp_4-s_2724991292.png "title=" uh%sph]{_euse[$HUOM 4l1v.png "alt=" Wkiom1e50xtdzdxoaaam66rvut4111.png-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/86/3E/wKioL1e50aLSSq2-AAAF16gUq7E369.png-wh_500x0-wm_3 -wmp_4-s_95393846.png "title=" ST (w~lblgych7u ' V) ybn%08.png "alt=" Wkiol1e50alssq2-aaaf16guq7e369.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/86/3E/wKioL1e50bvCzCl4AAAJH5eNKL4316.png-wh_500x0-wm_3 -wmp_4-s_3123303439.png "title=" (7 ' w7hgdllkonadkzoi9usn.png "alt=" Wkiol1e50bvczcl4aaajh5enkl4316.png-wh_50 "/>

displayed in the database as:

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/86/40/wKioL1e6W97Tlo1sAABIdQMloYM404.jpg-wh_500x0-wm_3 -wmp_4-s_2803970075.jpg "title=" K) fcpspt32j]d@2@zb7cs07.jpg "alt=" Wkiol1e6w97tlo1saabidqmloym404.jpg-wh_50 "/>

Lab Requirements:

1, two table query

Select Student table. Name, Student table 1. Telephone, Student table 1. Home Address

From student table, student table 1

where student table. Student id= Student table 1. Student ID

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/86/40/wKiom1e6VHHwF_nVAAB8leqlM8I331.jpg-wh_500x0-wm_3 -wmp_4-s_2815527981.jpg "title=" j$5gf%gltu0vn~wq_f0@{}t.jpg "alt=" Wkiom1e6vhhwf_nvaab8leqlm8i331.jpg-wh_50 "/>

2, three-table query

Select Student table. Name, schedule, course name, score table.

From student table, timetable, score table

where student table. Student Id= score table. Student ID and score table. Course id= curriculum. Course ID

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/86/40/wKioL1e6VcbCEGaPAAB65ir4MEc542.jpg-wh_500x0-wm_3 -wmp_4-s_1415783030.jpg "title=" PMF) ksv2~8g1c5{lv5gaa_1.jpg "alt=" Wkiol1e6vcbcegapaab65ir4mec542.jpg-wh_50 "/>

3. Merging two result sets

SELECT name, school number, class, nationality

From Class II

UNION

Select Surname + "+ Name, school number, class, nationality

From First class

ORDER BY class DESC, study number ASC

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/86/40/wKiom1e6WMjwUs77AACElCI8RE8841.jpg-wh_500x0-wm_3 -wmp_4-s_3502820063.jpg "title=" tu8006_}}edt1%2nm{i59~n.jpg "alt=" Wkiom1e6wmjwus77aacelci8re8841.jpg-wh_50 "/>

4. Inner JOIN (Find the name and CNT score of the student taking the test, telephone)

Select S. name, S. Telephone, c.cnt

From Student basic information table as S INNER JOIN Student score table as C

On S. Name =c. Name

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/86/40/wKiom1e6XEOSWeY2AABcI8bdnos677.png-wh_500x0-wm_3 -wmp_4-s_2601789838.png "title=" Faztz}6noh90nvmzd~%%$}2.png "alt=" Wkiom1e6xeoswey2aabci8bdnos677.png-wh_50 "/>

5, Left outer connection (find the name of the class students and CNT grades, telephone)

Select S. name, S. Telephone, c.cnt

From Student basic information table as S left OUTER JOIN Student score table as C

On S. Name =c. Name

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/86/40/wKioL1e6XGKTMvyHAABh1aq93jA776.png-wh_500x0-wm_3 -wmp_4-s_755352559.png "title=" Evif4ak (U ' dmxbzuori_6jb.png "alt=" Wkiol1e6xgktmvyhaabh1aq93ja776.png-wh_50 "/>

6. Right outer connection (find the name and CNT grade of the student who took the exam, telephone)

Select S. name, S. Telephone, c.cnt

From Student basic information table as S right OUTER JOIN Student score table as C

On S. Name =c. Name

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/86/40/wKioL1e6XIiTU_k0AABaP_CIH4Q051.png-wh_500x0-wm_3 -wmp_4-s_278731866.png "title=" P3f0374d3k25}n8 (FF (pi_i.png "alt=" Wkiol1e6xiitu_k0aabap_cih4q051.png-wh_50 "/>

7. Fully connected (Find all students ' exams)

Select S. name, S. Telephone, c.cnt

From Student basic information table as S full OUTER JOIN Student score table as C

On S. Name =c. Name

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/86/40/wKiom1e6XJuyLlA4AABbktKNBGM153.png-wh_500x0-wm_3 -wmp_4-s_1713389410.png "title=" UQMYF564DSF (B} ' 11w}jowj.png "alt=" Wkiom1e6xjuylla4aabbktknbgm153.png-wh_50 "/>

8. Self-connect (Find the boss name of each employee)

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

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/86/40/wKioL1e6XOThh_jzAABaXATLk_E756.png-wh_500x0-wm_3 -wmp_4-s_3532643251.png "title="}drz{oov%l70$g8qu (wul3y.png "alt=" Wkiol1e6xothh_jzaabaxatlk_e756.png-wh_50 "/>

9, sub-query (find the production quantity per quarter, percent)

Select Quarter, SUM (production quantity) as the number of production per quarter,

STR ((sum (production quantity)/(select sum (production quantity) from production table) *100) + '% ' as percent

From Production table

Group BY quarter

Order BY quarter

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/86/40/wKiom1e6XPmjaJfbAABj5DyiSsQ258.png-wh_500x0-wm_3 -wmp_4-s_1907053545.png "title=" P ((wuzkmm}h4y) 2$h8g7eql.png "alt=" Wkiom1e6xpmjajfbaabj5dyissq258.png-wh_50 "/>

10. Use SELECT in the WHERE clause to find the course id=1 and 〉80 students

Select Student table. Name

From student table

Where 80< (select score from score table

where student table. Student Id= score table. Student ID and score table. Course id=1)

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/86/40/wKioL1e6XRKhQIHFAABW7Fh1XWo298.png-wh_500x0-wm_3 -wmp_4-s_2385530825.png "title=" urp2z}1 (h7}plgysl@19f8t.png "alt=" Wkiol1e6xrkhqihfaabw7fh1xwo298.png-wh_50 "/>

11. Cross-Connect

SELECT T. Name of Teacher, c. Course Name

From Instructor basic information table as T cross join curriculum as C

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/86/40/wKiom1e6XSmjbilpAABT6_hE_wk618.png-wh_500x0-wm_3 -wmp_4-s_2053636912.png "title=" Zsjt (Q}%MMW82ZRQDG) hz2o.png "alt=" Wkiom1e6xsmjbilpaabt6_he_wk618.png-wh_50 "/>

This article is from "A Candle" blog, please be sure to keep this source http://yw666.blog.51cto.com/11977292/1840977

T-SQL query Statement Part II (Multi-table query)