Question

In SQL working with data types

1. Write a SELECT statement that returns these columns from the Instructors table:

a. The monthly salary (the AnnualSalary column divided by 12)

b. A column that uses the CAST function to return the monthly salary with 1 digit to the right of the decimal point

c. A column that uses the CONVERT function to return the monthly salary as an integer

d. A column that uses the CAST function to return the monthly salary as an integer

2. Write a SELECT statement that returns these columns from the Students table:

a. The EnrollmentDate column

b. A column that uses the CAST function to return the EnrollmentDate column with its date only (year, month, and day)

c. A column that uses the CAST function to return the EnrollmentDate column with its full time only (hour, minutes, seconds, and milliseconds)

d. A column that uses the CAST function to return the EnrollmentDate column with just the year and month

3. Write a SELECT statement that returns these colums from the Students table:

a. A column that uses the CONVERT function to return the EnrollmentDate column in this format: MM/DD/YYYY. In other words, use 2-digit months and days and a 4-digit year, and separate each date component with slashes.

b. A column that uses the CONVERT function to return the EnrollmentDate column with the date, and the hours and minutes on a 12-hour clock with an am/pm indicator.

c. A column that uses the CONVERT function to return the EnrollmentDate column with just the time in a 24-hour format, including the milliseconds.

d. A column that uses the CONVERT function to return the EnrollmentDate column with just the month and day.

Answer 1

1) a) select AnnualSalary/12 as MonthlySalary from Instructors

b)select cast(AnnualSalary/12 as decimal(10,1)) as MonthlySalary from Instructors

c) select convert(int,AnnualSalary/12) as MonthlySalary from Instructors

d) select cast(AnnualSalary/12 as int) as MonthlySalary from Instructors

2) a)select EnrollmentDate from Students

b) select Cast(EnrollmentDate as Date) EnrollmentDate from Students

c)select Cast(EnrollmentDate as Time) EnrollmentTime from Students

d)  select Cast(EnrollmentDate as Char(7)) EnrollmentDate from Students

3) a) Select Convert(varchar,EnrollmentDate,101) EnrollmentDate FROM Students;

b)Select Convert(varchar,EnrollmentDate,22) EnrollmentDate FROM Students;

c)Select Convert(varchar,EnrollmentDate ,114) EnrollmentDate FROM Orders;

d)Select Convert(varchar(2), Month(OrderDate)) + '/'
+ Convert(varchar(2), Day(OrderDate)) from Orders;