MS SQL Interview Questions and Answers
Question - 61 : - How does SQL Server store the datetime data type?
Answer - 61 : - SQL Server uses 8 bytes to store the datetime data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1900. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.
Question - 62 : - How do I retrieve rows based on datetime values?
Answer - 62 : - SQL Server recognizes date and time data enclosed in single quotes. You can couple date and time values together or use them independently. You can also combine character date formats ('May 15, 2004 4 am'), numeric date formats ('5/15/2004 04:30'), or contiguous string formats ('20040515') with standard <, >, or = operators, as the following example shows:
SELECT * FROM orders WHERE OrderDate < 'May 15, 2004'4. How do I retrieve only the date or time portion of the data?
Question - 63 : - How do I retrieve only the date or time portion of the data?
Answer - 63 : - You can use T-SQL's DATEPART() function to return a subset of the values that SQL Server's datetime columns store. The DATEPART() function uses two arguments. The first argument specifies the portion of the date that you want, and the second value specifies the datetime column:
SELECT orderID, DATEPART(MM,OrderDate) AS OrderMonth FROM Orders
Question - 64 : - How do I insert a value into a datetime column?
Answer - 64 : - To insert values into a datetime column, you need to enclose the values in single quotes, then use one of SQL Server's date formats to supply the date value that you want to insert. For example:
DECLARE @MyTable TABLE
(MyDateTime DATETIME)
INSERT INTO @MyTable VALUES ('May
15, 2004 11:25am')
Question - 65 : - How do I find the day of the week?
Answer - 65 : - Using the weekday argument as its first parameter, SQL Server's DATEPART() function returns the day of the week, returning 1 for Sunday, 2 for Monday, and so on. The following example uses the GETDATE() function combined with the DATEPART() function to retrieve the current day value:
SELECT DATEPART(weekday, GETDATE())
Question - 66 : - How can I find the last day of the month?
Answer - 66 : - You can combine T-SQL's DATEADD() and DATEDIFF() functions to calculate different date and time values. Subtract 5ms from the first day of the next month to find the last day of the current month:
SELECT DATEADD(ms,-5,DATEADD(mm, DATEDIFF(m,0,GETDATE() )+1, 0))
Question - 67 : - What is the Difference between COALESCE() & ISNULL()?
Answer - 67 : -
ISNULL accepts only 2 parameters. The first parameter is checked for a NULL value, if it is NULL then the second parameter is returned, otherwise, it returns the first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter,
Question - 68 : - How do you generate file output from SQL?
Answer - 68 : -
While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR.QUERTY >> RESULT TO >> Result to FILE
Question - 69 : - By Mistake, Duplicate records exists in a table, how can we delete the copy of a record?
Answer - 69 : -
with T as
(
select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
from employee
)
delete
from T
where rank > 1
Question - 70 : - WHAT OPERATOR PERFORMS PATTERN MATCHING?
Answer - 70 : -
The pattern matching operator is LIKE and it has to use with two attributes
1. % means matches zero or more characters and
2. _ ( underscore ) means matching exactly one character