Thursday 3 May 2012

SQL Interview questions & answers


1. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)

2. What operator performs pattern matching?
 LIKE operator

3. What operator tests column for the absence of data?
IS NULL operator

4. Which command executes the contents of a specified file?
START <filename> or @<filename>

5. What is the parameter substitution symbol used with INSERT INTO command?
&

6. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN

7. What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution

8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True

9. State true or false. !=, <>, ^= all denote the same operation.
True

10. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all


11. What is the difference between TRUNCATE and DELETE commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.


12. What command is used to get back the privileges offered by the GRANT command?
REVOKE

13. Which system tables contain information on privileges granted and privileges obtained?
       USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

14. Which system table contains information on constraints on all the tables created?
      USER_CONSTRAINTS

15. TRUNCATE TABLE EMP;
  DELETE FROM EMP
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.

16.What is Common Table Expression in SQL Server? Where do u use this?

17.What are DML, DCL, TCL and DDL statements in SQL Server? Explain briefly with an example?
18.What is mean by transaction in SQL Server?
19.What are difference between stored procedure and functions?
20.What are Max number of columns allows per table in SQL Server?
21.Sql stored procedures performance tuning tips and tricks?
22.What is Cascading referential integrity constraint?
23.SQL Query performance tips and tricks?
24.Difference between Primary key and unique key?
25.How many types of indexes are there in SQL Server?
26.What is SQL Server paging?
27.A table can have indexes. So, what are possibilities are there for tables?
28.Difference between #tempTable,##tempTable,@tableName?
29.Query for column values by comma separated in Sql Server?
30.Types of Joins in SQL Server? What are link between Common Table Expression and SQL Server?
31.What is sub query? How many types of sub queries are there in SQL Server?
32.What are the differences between drop, delete and truncate statements in SQL Server?
33.What are different types of temp tables in the SQL Server?
34.What is difference between clustered index and non clustered index in sql server?
35.What is mean by Cascading referential integrity constraint?





No comments:

Post a Comment