Suppose, there is a table say tbl_studentcoursereg to store the id of the students registered for various courses, registration month and date. Table structure is given below:-
create table tbl_studentcoursereg (id int identity(1,1), studentid int, coursename nvarchar(100), Regmonth nvarchar(50), regdate datetime)
The insert command to populate this table with the data is given below:-
insert into tbl_studentcoursereg
values (1, 'C#', 'JAN','01/01/2012')
insert into tbl_studentcoursereg
values (2, 'SQL', 'JAN','01/02/2012')
insert into tbl_studentcoursereg
values (3, 'C++', 'JAN','01/03/2012')
insert into tbl_studentcoursereg
values (4, 'C#', 'FEB','02/02/2012')
insert into tbl_studentcoursereg
values (5, 'C#', 'MAR','03/03/2012')
insert into tbl_studentcoursereg
values (6, 'JAVA', 'APR','04/04/2012')
insert into tbl_studentcoursereg
values (7, 'JAVA', 'APR','04/04/2012')
insert into tbl_studentcoursereg
values (8, 'ORACLE', 'APR','04/04/2012')
insert into tbl_studentcoursereg
values (9, 'C#', 'APR','04/23/2012')
insert into tbl_studentcoursereg
values (10, 'C#', 'MAY','05/05/2012')
Now if we want to know in which month number of students registered for a particular months is greater than 2, the we can use the following query which uses the derived table.
select regmonth, totalstud, totalcourse from
(select regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth )as tbl1 where tbl1.totalstud>2
In case of derived table, we have to give the alias name to the derived table as tbl1 is the alias for the above mention derived table.
