SQL Day Three

-"Complete SELECT statement and execution order (must be remembered)

5...select 5.2->distinct 7...top N [percent] 5.1-> column name aggregate function (1.2-> column name or expression)

1...1.1->from Table Name

2...where conditions

3...group by ... 4...having ...

6...order by ...

-"Practical Practice"

SELECT DISTINCT top 1 tsclassid,count (*) as number, AVG (tsage) as average age

From Tblstudent

Where tsgender= ' man '

GROUP BY TSCLASSID have tsclassid>3

ORDER BY average age desc

-"What is the order of execution?"

1. From Tblstudent

2. Where tsgender= ' man '

3. GROUP BY Tsclassid

4. Having tsclassid>3

5. Select

5.1 Tsclassid,count (*) as number, AVG (tsage) as average age

5.2 Distinct

6. Order BY average age desc

7, top 1

INNER JOIN (equivalent join) returns only rows that have the same join field in two tables

Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right table

Right join returns records that include all records in the right table and the junction fields in the left table

SQL Day Three