SQL Interview Questions and Answers Part -1

Question- What provider ADO.NET use by default?

Answer- There’s no default provider as such but there are generic providers (OLE DB and ODBC) which are not limited to a specific database such as SQL Server or Oracle.

Question - What is reference cursor in SQL?

Answer - A Ref cursor is just a pointer to the result set of the cursor with which it is associated.

Question - How to Display duplicate rows in a table?

Answer - This query for finding duplicates in a table. Suppose you want to find all Names in a table that exist more than once:

SELECT UserName,
 COUNT(UserName) AS TotalOccurrences
FROM users
GROUP BY UserName
HAVING ( COUNT(UserName) > 1 )

OR

SELECT *
  FROM EMP A
 WHERE EXISTS (SELECT 1
                 FROM EMP
                WHERE empno = A.empno AND ROWID < A.ROWID)

Question - How to select last N records from a Table ?

Answer - SELECT TOP N *  FROM EMP ORDER BY EMPNO DESC

Question - What is use of CASCADE CONSTRAINTS in SQL?

Answer - Cascade Constraints, are usually used when there exists a parent-child relationship between tables using foreign key or referential constraints defined.

For Example, In case of deletion a record from the Parent Table, where there exists a foreign key to the child table,
If you do not specify cascading deletes, the default behavior of the database prevents us from deleting data in the Parent table if the child table has a reference to the Parent.

Question – What is the use of in command in SQL.

Answer - The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

IN Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Please refer the below link

http://www.w3schools.com/sql/sql_in.asp

Question - How to change database name in sql server.

Answer – “sp_renamedb”

You open that sp and find logic from there. Try this sp_helptext sp_renamedb

Question - How to find specify row value in SQL

Answer - Suppose

I have a table it's name is table1 , in it 3 column is specified , column name is id, name, date if in name column three are three same name like abc, abc, abc add id is a primary key, how to get second last name and date  in this table. table may be contain lot of records.

This way you will get the second last row (name, date). Incase if you need 3rd last you need to increase the 'red' number to 2 and so on......

 SELECT TOP 1 * FROM [Table1] 

WHERE ID NOT IN (SELECT TOP 1 ID FROM [Table1] ORDER BY ID DESC)

ORDER BY ID DESC