SQL samples

 

 

----------------query aggregation function and group by -------------------------

 

SELECT Test1.Test, avg(Test1.Id) As avg, SUM(Test1.Id) As SUm, COUNT(Test1.Id) As Count FROM [Test1]

 

GROUP BY Test1.Test

 

HAVING count(Test1.Id)< 3

 

---- Query data from multiply data tables----use select in------------------------

 

select Test1.Id, Test1.Test from Test1 where Test1.Id in

 

 

(select Test2.Id from Test2 where Test2.Name in (select Test3.Name from Test3 where Test3.Description = 'D2'))

 

---- Query data from multiply data tables----use with------------------------

 

with ba as ( select Test1.Id As Aid, Test2.Id As BID, Test2.Name from Test1 inner join Test2 on Test1.Id = Test2.Id )

select

 

 

* from ba

---- Query data from multiply data tables----------------------------

select

 

 

* from  (select Test.BID, Test3.Description from

(

 

 

select Test1.Id As Aid, Test2.Id As BID, Test2.Name from Test1 inner join Test2 on Test1.Id = Test2.Id where Test1.Id = 1) Test

 

inner join Test3 on Test.Name = Test3.Name ) Test3333

 

select Test1.Id As Aid, Test2.Id As BID, Test2.Name from Test1 inner join Test2 on Test1.Id = Test2.Id where Test1.Id = 1

 

select * from Test2

 

select * from Test3