How to use multiple tables after select * FROM

 

  The first question: (1) Known a table structure is:-------------------name subject results Zhang three languages 20 Zhang three mathematics 30 Zhang three English 50 Li four languages 70 Dick Mathematics 60 Li Four English   how to pass SE The lect statement turned him into the following structure:------------------------------------name, John       20 of English scores in Chinese           30          50 Dick       70          60             INSERT into student values (' Dick ', ' English ', ') ' SELECT * FROM student- ----law I: positive solutions are as follows: Select a. Name, A. Results as language scores, B. Achievement as maths grade, C. Achievement as English score from student A,student b,student C where A name = B. Name and b. name = C. Name and A. Subject = ' Language ' and B. Subject = ' English '  --------understood as follows: SELECT * FROM St Udent a,student b,student C-connects three identical student tables to each other, and the connection generates 6 *6*6=216 records because there are 6 records in each table. where A. Name = B. Name and B. name = C. Name--Filter the connection table record and get (3*3*3) + (3*3*3) =27+27=54 record. AnD A. Subjects = ' languages ' and B. Subjects = ' math ' and C. Subject = ' English '--Filter the account content in three child tables at the same time, and the execution can be as follows. Name    subject   Results    name    subject    results    name    subject    Results John    Languages           John    Math       30& nbsp;    John    English     50        Dick  & nbsp Languages           Dick    math       60      Dick    English     90        then define the output in select. ----Law II: The following select name, sum (case subject when ' language ' then results else 0 end) as language score, sum (case subject when ' math ' then result else 0 end) as Math score, sum (Case subject when ' English ' then result else 0 end) as English score, AVG (result) as average score, sum (result) as total score from student group by name ORDER BY name Desc result is as follows: John       20  &nb of the average score of the English score of the Chinese scoresp;       30          50           33         100 Dick        70          60           90          73           220

(2) CREATE Table A (year int, Quarter varchar (), amount float) Insert a SELECT, ' 1 ', 1.1 insert a Select 2000, ' 2 ', 1.2 Insert a SELECT, ' 3 ', 1.3 insert a SELECT, ' 4 ', 1.4 insert a Select 2001, ' 1 ', 2.1 insert a Select 2001, ' 2 ', 2.2 in SERT a Select 2001, ' 3 ', 2.3 insert A Select 2001, ' 4 ', 2.4 table A is defined as follows:
Property type
Year Integer
Quarter Varchar (30)
Amount float

Year Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
Each row table represents data for a quarter.

If you work with the data in table A, you get the following results.
Year Quarter1 Quarter2 Quarter3 Quarter4
2000 1.1 1.2 1.3 1.4
2001 2.1 2.2 2.3 2.4
Please use SQL to write a code implementation. ---Law one: the positive solution is as follows: Select T1. Year,t1.amount as quarter1,t2.amount as quarter2,t3.amount as quarter3,t4.amount as Quarter4 from A t1,a t2,a T3,A T4 E T1. Year=t2. Year and T2. Year=t3. Year and T3. Year=t4. Year and T1. Quarter= ' 1 ' and T2. Quarter= ' 2 ' and T3. Quarter= ' 3 ' and T4. quarter= ' 4 '---method two: The positive solution is as follows: Select year, sum (case quarter when ' 1 ' then Amount else 0 ") as Quarter1, sum (case quarter when ' 2 ' then Amount else 0 end] as Quarter2, sum (case quarter when ' 3 ' then Amount else 0) as Quarter3, sum (case quarter W Hen ' 4 ' then Amount else 0 end] as Quarter4, sum (Amount) as Allamount from group by year

Second question: There is a teacher table T (t_id,t_name); There is a student table S (s_id,s_name); There is a class table C (T_id,s_id,c_name), in which the C_name value is only ' big class ' and ' small classes ', please check out the match Condition of the teacher's name, the condition is that the teacher in the big class with more students than this teacher in small classes with the number of students. (preferably with a subquery, the title is so required, the other database is SQL Server) select * from T, (select COUNT (*) as x,t_id from C where c_name= ' small class ' group ;