Interview Questions Answers in Core Java, SQL Server, JDBC, MySQL

Question { 26989 }

Difference between Dirty, commited ,phantom,repeatable
reads?

Answer

Check the following link for differences:
http://sqlwithmanoj.wordpress.com/2011/07/20/dirty-reads-and-phantom-reads/

Is This Answer Correct ?

4 Yes

1 No

Question { CTS, 22449 }

What is difference between TRUNCATE and DELETE statement

Answer

–> DELETE:

1. Removes Some or All rows from a table.
2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
3. Causes all DELETE triggers on the table to fire.

–> TRUNCATE:

1. Removes All rows from a table.
2. Does not require a WHERE clause, so you can not filter rows while Truncating.
3. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
4. No Triggers are fired on this operation because it does not operate on individual rows.

Check more differences b/w DELETE & TRUNCATE here: http://sqlwithmanoj.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/

Is This Answer Correct ?

0 Yes

0 No

Question { CSC, 10729 }

What is the diff between Static Queries and Dynamic queries
give me some examples

Answer

Static Queries are permanent and cannot be changed during run-time, like: "SELECT * FROM Employees"

Dynamic Queries can be changed during run-time as they are created by using variables and these variables contain parts of SQL Query, like:

DECLARE @SQL VARCHAR(MAX)
DECLARE @WHENSQL VARCHAR(2000)
DECLARE @SEARCHSQL VARCHAR(500)

SELECT @WHENSQL = 'EmployeeID'
SELECT @SEARCHSQL = '100'

SELECT @SQL = 'SELECT * FROM Employees WHERE ' + @WHENSQL + ' = ' + @SEARCHSQL

EXEC (@SQL)
-- or
EXEC sp_executesql @SQL

For more interview Questions on SQL Server: http://sqlwithmanoj.wordpress.com/interview-questions/

Is This Answer Correct ?

1 Yes

0 No

Question { 11760 }

how to get 25th row in any table in sqlserver can u tell me
syntax

Answer

Check here how to get 2nd of nth highest record from a table: http://sqlwithmanoj.com/2014/01/05/sql-trivia-find-second-or-nth-highest-salary-or-marks/

Is This Answer Correct ?

0 Yes

0 No

Question { Emphasis, 6736 }

What are the new features in SQL Server 2005?

Answer

- CTE
- APPLY OPERATOR (CROSS, OUTER)
- OVER CLAUSE with PARTITION BY
- RANKING FUNCTIONS
- PIVOT & UNPIVOT
- Synonyms

Check here all new features in SQL Server 2005: http://sqlwithmanoj.com/category/sql-server-versions/sql-server-2005-sql-server-versions/

Is This Answer Correct ?

0 Yes

0 No

Question { 5712 }

i need some interview questions on sql server developer plz
any onee send some links.

Answer

Check my blog for more Interview Questions: http://sqlwithmanoj.wordpress.com/interview-questions/

Is This Answer Correct ?

0 Yes

0 No

Question { HCL, 9673 }

i have table students with fields classname,studname
select * from students
classname studname
1 xxxxx
1 yyyy
1 zzzz
2 qqqq
2 tttt
3 dsds
3 www
i want the output should be

No of students in class 1 : 3
No of students in class 2 : 2
No of students in class 3 : 2

Answer

select 'No of students in class ' + cast(classname as char(2)) + ' : ' + cast(count(*) as char(5)' from students group by classname

For more Interview Questions check my blog: http://sqlwithmanoj.wordpress.com/interview-questions/

Is This Answer Correct ?

0 Yes

0 No

Question { 9707 }

what is the difference between group and having
give an example with query and sample output

Answer

- GROUP BY clause works on the rows returned by a SELECT Query. This clause summaries identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.

- HAVING clause works as a Filter on top of the Grouped rows returned by the Query containing the GROUP BY clause. This clause cannot be replaced by a WHERE clause and vice-versa.

Check the difference b/w GROUP BY & HAVING, link: http://sqlwithmanoj.com/2015/05/23/sql-basics-difference-between-where-group-by-and-having-clause/

Is This Answer Correct ?

0 Yes

0 No

Question { Geo Research Centre, 8478 }

what is the difference between Delete and Truncate

Answer

Check following blog post for Difference Delete vs Truncate: http://sqlwithmanoj.wordpress.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/

Is This Answer Correct ?

0 Yes

0 No

Question { 247Customer, 15370 }

Differences between functions and stored procedures?

Answer

Check following blog post for difference between UDF & Stored Procedures: http://sqlwithmanoj.wordpress.com/2011/09/21/stored-procedures-vs-functions-difference-between-sp-udf/

Is This Answer Correct ?

0 Yes

0 No

Question { 247Customer, 16052 }

Can we have more than one NULL in a column having unique
constraint?

Answer

No this is not possible... columns with Unique constraint
can only contain 1 null value/row.

@Manish... could you please try this:

create table #tempTable (id int, name varchar(50) unique)

insert into #tempTable
select 1, 'manoj' union
select 2, null union
select 3, null union
select 4, 'pandey'

Is This Answer Correct ?

1 Yes

0 No

Question { Ericsson, 10957 }

Package1 and Package2 both have a method name lets say
"methodA" with different implementation. When I import both
the packages in a java class how can I use both the methods?

Answer

// Do you want to say like this?

//import like this:

import Package1.classA
import Package2.classB

//use it in your class
class yourclass
{
classA.methodA(); // Package1 method call
classB.methodA(); // Package2 method call
}

Is This Answer Correct ?

27 Yes

3 No

Question { 8720 }

1.can we set the more than 1 primary keys for a table?
2.please give me the difference between Cluster Index and
non-Clustered Index
3.can we use query like this "Select * from Table1,Table2;"

Answer

1.There cannot be more than 1 Primary Keys for a given table?

2.Difference between Cluster Index and non-Clustered Index, check this link: http://sqlwithmanoj.com/2011/03/02/clustered-vs-nonclustered-indexes-and-data-sorting/

3.Yes, we use query like this "Select * from Table1, Table2;". This will give a Cross Join (or cartesian-product) results from both the tables.

Is This Answer Correct ?

0 Yes

0 No

Question { HCL, 10792 }

SELECT country,city
FROM customers
GROUP BY country
ORDER BY city DESC

Answer

This is wrong because there is no aggregate function in the
query when a group by clause is used.

The above query could be like:

1. SELECT country, count(city)
2. FROM customers
3. GROUP BY country
4. ORDER BY country DESC

* Correction at line 1 & 4.

Is This Answer Correct ?

12 Yes

9 No

Question { United Healthcare, 12185 }

How many types of indexes are there in SQL Server?

Answer

As per MSDN there are 9 types:
http://msdn.microsoft.com/en-us/library/ms175049.aspx

Is This Answer Correct ?

6 Yes

0 No

manoj pandey