You are here

Derived Tables

Derived tables

In this post we will learn about derived table which is one of the table expression. It is a form of subquery that returns an entire table as a result.

You can define derived table’s inner query in parentheses in the FROM clause of the outer query, and specify the name of the derived table after the parentheses. It is visible only to the statement that defines them, not stored in the database as an object.

You can use it when you are required to perform some complex query and result of inner query Work as table for outer query.

Now we will see the example where derived tables are use:

Example:

Create table Product

Create table Product

(

ProId int NOT NULL IDENTITY(1,1) PRIMARY KEY,

CategoryId int NOT NULL,

ProName varchar(100),

UnitPrice money

);

Insert records

Insert into Product(CategoryId,ProName,UnitPrice)

Values(101,’Pen’,5),

(102,’Mobile covers’,100),

(103,’Chair’,500),

(104,’Laptop’,10000),

(105,’T-shirt’,200),

(102,’Laptop covers’,200),

(104,’Mobile’,20000);

 

Now we write a query to compute row number for products, partitioned by CategoryId.  for Counting row number we use ROW_NUMBER() function. It compute unique incrementing integer from 1 and based on indicated ordering. You can use this function only in SELECT and ORDER BY clause. You are not allowed to use in the query’s WHERE clause.

Select

ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY UnitPrice,ProId) as Rowno,

CategoryId,ProId,ProName,UnitPrice

from Product;

Output:

Rowno

CategoryId

ProId

ProName

UnitPrice

1

101

1

Pen

5.00

1

102

2

Mobile covers

100.00

2

102

6

Laptop covers

200.00

1

103

3

Chair

500.00

1

104

4

Laptop

10000.00

2

104

7

Mobile

20000.00

1

105

5

T-shirt

200.00

Now we write a query to retrieve CategoryId that contains 2 products. You count product  using ROW_NUMBER() function and you cannot use it in WHERE clause, so we use derived table.

Select CategoryId

FROM(

SELECT ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY UnitPrice,ProId) as Rowno,

CategoryId FROM Product) As D

where Rowno=2;

Output:

CategoryId

102

104

So, using this way you can achieve your goal.

Now, you can use 70-761 practice test for clearing your exam. this practice test help you for your actual exam. it is created by me, and you can buy it from udemy. this course covers all topics of the syllabus. so don’t wary just try it.

if you buy it using below link you get only in 10 dollar. so harry up!!!!!!!!

70-761-querying-data-with-transact-sql-practice-test

70-761 pactice test

Leave a Reply

Top