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.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
One thing which is really important for programmers to understand in case of derived table is that, essentially derived tables are nothing but sub-queries which is given an alias name using AS keyword. While sub-queries are generally used in where clauses the derived tables are always used in from clause of a SQL query.
Hi Vivek,
Thanks for explaining the concept in an understandable manner.
Also I would like to add a point here: Derived tables does not have scope like a batch or a session but it ends right after the execution within the outer query from which it is derived.
Regards
Varun
… [Trackback]
[…] Information on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Here you can find 70410 more Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Here you can find 46282 additional Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More Information here on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Here you can find 49539 more Info to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More here to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
[…] Read 10178 More Information here to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.h… […]
… [Trackback]
[…] Find More Information here to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More here to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Read More Info here to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Read More on on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Read More on to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Read More on on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Read More Information here on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]
… [Trackback]
[…] Find More Information here to that Topic: techmixing.com/2012/04/derived-table-in-sql-server.html […]