Saturday, December 1, 2012

SQL interview questions and answers

What is the difference between IN and BETWEEN, that are used inside a WHERE clause?
-The BETWEEN clause is used to fetch a range of values, whereas the IN clause fetches data from a list of specified values.
What are the wildcards used for pattern matching?
- for single character substitution and % for multi-character substitution

What is Online Transaction Processing (OLTP) ?
-Online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity.

What is sql?
-Structured Query Language (SQL) is a language that provides an interface to relational database systems.

Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
-Data Definition Language (DDL)
Which TCP/IP port does SQL Server run on? How can it be changed?
-SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
What is the difference between UNION and UNION ALL in SQL?
-UNION is an SQL keyword used to merge the results of two or more tables using a Select statement, containing the same fields, with removed duplicate values. UNION ALL does the same, however it persists duplicate values.

What operator performs pattern matching?
-LIKE operator
What are the difference between clustered and a non-clustered index?
-A clustered index is a special type of index that reorders the way records in the table are physically stored.
-A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

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
Whats the capacity of the image data type in MS SQL?
-Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
What is difference between DELETE and TRUNCATE commands?
-Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
What command is used to get back the privileges offered by the GRANT command?
What are the authentication modes in SQL Server?
-Windows mode and Mixed Mode - SQL and Windows. How 2 change it-To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

What’s the difference between a primary key and a unique key?
-Both Primary key and Unique key enforce the uniqueness of a column on which they are defined. However, a Primary key does not allow nulls, whereas unique key allow nulls.
What is a transaction and ACID?
-Transaction - A transaction is a logical unit of work. All steps must be committed or rolled back.
ACID - Atomicity, Consistency, Isolation and Durability, these are the unique entities of a transaction.


No comments: