SQL Database Concepts Question: Download Questions PDF
Explain what is CTE (Common Table Expression)?
Answer:
When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression.
Consider the following SQL statement.
SELECT * FROM (
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID) E
WHERE E.Gender = ‘Male’
ORDER BY T.EmpName
The syntax of CTE is as follow
- The CTE Name (followed by WITH keyword)
- The Column List (Optional)
- The Query (Appears within parentheses after the AS keyword)
If we write the above messy query using CTE it would be like
With E(EmpName, Department, Gender)
AS
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID
)
SELECT * FROM E
WHERE E.Gender = ‘Male’
ORDER BY E.EmpName
This way the query can be made more readable and easy to understand.
Consider the following SQL statement.
SELECT * FROM (
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID) E
WHERE E.Gender = ‘Male’
ORDER BY T.EmpName
The syntax of CTE is as follow
- The CTE Name (followed by WITH keyword)
- The Column List (Optional)
- The Query (Appears within parentheses after the AS keyword)
If we write the above messy query using CTE it would be like
With E(EmpName, Department, Gender)
AS
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID
)
SELECT * FROM E
WHERE E.Gender = ‘Male’
ORDER BY E.EmpName
This way the query can be made more readable and easy to understand.
Download Basic SQL Server Interview Questions And Answers
PDF
| Previous Question | Next Question |
| Do you know what is user defined datatypes and when you should go for them? | What is a bit datatype? |