SQL advanced query: nesting and Paging

1. Nested subqueries

--Check for students who have not taken the exam in the most recent OOP exam
SelectStudentname fromStudentwhereStudentno not inch(SelectStudentno fromResultwhereSubjectid=(SelectSubjectid fromSubjectwhereSubjectname='OOP') andExamdate=(Select MAX(examdate) fromResultwhereSubjectid=(SelectSubjectid fromSubjectwhereSubjectname='OOP'     )  ))

2. Related sub-query

SElECT Name,id,price    from  as C   WHERE  Price>  (    SELECTAVG    . From  as  a    WHERE C. class number =A. Class number  )

3. Paging Query

The first type:

 --  Skip the five lines and take two rows   top  2  *  from   Student  where  Studentno not  in   ( select   Top  5  studentno from   Student  order  by   Studentno)  order  by  Studentno 

The second type:

-- take line fourth to line sixth Select *  from (Select*over (orderby as as tempwherebetween4and6

SQL advanced query: nesting and Paging