SQL- CAST and CONVERT

Q What is the difference between CAST and CONVERT in SQL Server ?

      CAST: It is used to convert from one data type to another.CAST is ANSI
standard which means that it can also be used in other database applications.

Syntax:  
     CAST ( expression AS data_type [ ( length ) ] )

Cast_Illustration

CONVERT:Like CAST, CONVERT is also used to convert from one data type to another.It is specific to SQL Server.One major advantage of CONVERT is that it has style parameter which can be used for formatting date.

       The syntax of the CONVERT function are in a different order from the CAST function but are almost the same. It does not have AS keyword

Syntax:  

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Convert_Illustration

       Now you will wonder when should we use CAST and when should we use CONVERT. It is very simple. Use CAST when you need to convert from one data type to another as it is a ANSI Standard. Use CONVERT when you want to take advantage of style functionality.

NOTE: Formatting is not possible on DATE datatype or DATETIME data type.First you need to convert to varchar or nvarchar and then apply style. If you use style format in DATE datatype, CONVERT function will ignore style parameter. You can see this in below snapshot. Second query is returning same result as first one because style parameter is ignored.

    

   

SQL- Scenario Based Questions PART- 2

   Consider below Employee table and Department table for answering the questions:-

Employee Table:

EMPLOYEE TABLE

Department Table:

DEPARTMENT TABLE

Q1.Write a SQL query to display Employee Name , Department Name and Location

SELECT EmpName,DeptName,Location FROM Employee E

INNER JOIN Department D 

ON E.DeptNo=D.DeptNo

Q2.Write a SQL query to display total number of Employee and maximum salary under each Department Name .

SELECT D.DeptName,
       Max(Salary)As MaxSalary,
       COUNT(EmpName) As TotEmp
FROM Department D
LEFT OUTER JOIN Employee E
ON E.DeptNo=D.DeptNo
GROUP BY D.DeptName

Q2.Write a SQL query to list maximum salary, minimum salary and average salary designationwise, for department 200 and display only those records which has average salary greater than 10000.

SELECT E.Designation,
       Max(Salary)As MaxSalary,
       Min(Salary)As MinSalary,
       AVG(Salary) As AvgSalary
FROM Employee E
INNER JOIN Department D
ON E.DeptNo=D.DeptNo
WHERE D.DeptNo=200
GROUP BY E.Designation
HAVING AVG(Salary) > 10000

If you like this post, please do share by clicking on G+1 Button below :