Derived Table

Sub Query

CTE

derived tables are used in the FROM clause

 

sub queries are used in the WHERE clause, but can also

be used to select from one table and insert into

another as we showed above

 

A common table expression (CTE) is a similar concept to a derived table in the sense that it’s

a named table expression that is visible only to the statement that defines it. Like a query

against a derived table, a query against a CTE involves three main parts:

■ The inner query

■ The name you assign to the query and its columns

■ The outer query

select employee_name

from employee

where employee_salary >

 (select avg(employee_salary)

        from employee)

 

select max(age)

from (

select age from table

) as Age  

 

 

With C As

(

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE country = 'USA'

)

Select empid, firstname, lastname, country from c