Interview Questions With Answers: Database

Showing posts with label Database. Show all posts

Thursday, 19 June 2008

Database management interview questions

1. What is a Cartesian product? What causes it?

Expected answer:
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.

2. What is an advantage to using a stored procedure as opposed to passing an SQL query from an application.

Expected answer:
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

3. What is the difference of a LEFT JOIN and an INNER JOIN statement?

Expected answer:
A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

4. When a query is sent to the database and an index is not being used, what type of execution is taking place?

Expected answer:
A table scan.

5. What are the pros and cons of using triggers?

Expected answer:
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs.

Triggers enhance the security, efficiency, and standardization of databases.
Triggers can be beneficial when used:
– to check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
– to run other non-database operations coded in user-defined functions
– to update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.
– to check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.

6. What are the pros and cons of using stored procedures. When would you use them?

7. What are the pros and cons of using cursors? When would you use them?

SQL Server, DBA interview questions

Questions are categorized under the following sections, for your convenience:

Database design (8 questions)
SQL Server architecture (12 questions)
Database administration (13 questions)
Database programming (10 questions)
Database design

What is normalization? Explain different levels of normalization?
- Check out the article Q100139 from Microsoft knowledge base and of course, there’s much more information available in the net. It’ll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.
What is denormalization and when would you go for it?
- As the name indicates, denormalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
- One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table. It will be a good idea to read up a database designing fundamentals text book.
What’s the difference between a primary key and a unique key?
- Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
What are user defined datatypes and when you should go for them?
- User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables. See sp_addtype, sp_droptype in books online.
What is bit datatype and what’s the information that can be stored inside a bit column?
- Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
Define candidate key, alternate key, composite key.
- A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.
What are defaults? Is there a column to which a default can’t be bound?
- A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them. See CREATE DEFAULT in books online.
What is a transaction and what are ACID properties?
- A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book. Explain different isolation levels An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level. Read Committed - A transaction operating at the Read Committed level cannot see changes made by other transactions until those transactions are committed. At this level of isolation, dirty reads are not possible but nonrepeatable reads and phantoms are possible. Read Uncommitted - A transaction operating at the Read Uncommitted level can see uncommitted changes made by other transactions. At this level of isolation, dirty reads, nonrepeatable reads, and phantoms are all possible. Repeatable Read - A transaction operating at the Repeatable Read level is guaranteed not to see any changes made by other transactions in values it has already read. At this level of isolation, dirty reads and nonrepeatable reads are not possible but phantoms are possible. Serializable - A transaction operating at the Serializable level guarantees that all concurrent transactions interact only in ways that produce the same effect as if each transaction were entirely executed one after the other. At this isolation level, dirty reads, nonrepeatable reads, and phantoms are not possible.
CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after executing the above statement?
- Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
What’s the maximum size of a row?
- 8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. 1024 columns per table. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications". Explain Active/Active and Active/Passive cluster configurations Hopefully you have experience setting up cluster servers. But if you don’t, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site. Explain the architecture of SQL Server This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.
What is lock escalation?
- Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.
What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
- DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE TABLE may not be used on tables participating in an indexed view
Explain the storage models of OLAP
- Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation.
What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?
- This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What’s New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.
What are constraints? Explain different types of constraints.
- Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY. For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?
- Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table. If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
What is RAID and what are different types of RAID configurations?
- RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board’s homepage
What are the steps you will take to improve performance of a poor performing query?
- This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables. Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer. Download the white paper on performance tuning SQL Server from Microsoft web site. Don’t forget to check out sql-server-performance.com
What are the steps you will take, if you are tasked with securing an SQL Server?
- Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc. Read the white paper on SQL Server security from Microsoft website. Also check out My SQL Server security best practices
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
- Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process. A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.
What is blocking and how would you troubleshoot it?
- Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions. Explain CREATE DATABASE syntax Many of us are used to creating databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB.
But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%?
- That’s why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
- SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal configuration mode. Check out SQL Server books online for more parameters and their explanations.
As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
- DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.
What are statistics, under what circumstances they go out of date, how do you update them?
- Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations under which you should update statistics: 1) If there is significant change in the key values in the index 2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated 3) Database is upgraded from a previous version. Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
What are the different ways of moving data/databases between servers and databases in SQL Server?
- There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data.
Explain different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
- Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.
What is database replication? What are the different types of replication you can set up in SQL Server?
- Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios: � Snapshot replication � Transactional replication (with immediate updating subscribers, with queued updating subscribers) � Merge replication See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.
How to determine the service pack currently installed on SQL Server?
- The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit SQL Server service packs and versions.
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
- Cursors allow row-by-row processing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000 and 40000 — 5000 hike Salary between 40000 and 55000 — 7000 hike Salary between 55000 and 65000 — 9000 hike. In this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
- UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END
- Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the ‘My code library’ section of my site or search for WHILE. Write down the general syntax for a SELECT statements covering all the options. Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).
- SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ]
What is a join and explain different types of joins.
- Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".
Can you have a nested transaction?
- Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
- An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server. Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see ‘My code library’ section of this site.
What is the system function to get the current user’s user id?
- USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
- Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder. Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers. Also check out books online for ‘inserted table’, ‘deleted table’ and COLUMNS_UPDATED()
There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
- Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
What is a self join? Explain it with an example.
- Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
- CREATE TABLE emp ( empid int, mgrid int, empname char(10) )
- INSERT emp SELECT 1,2,’Vyas’ INSERT emp SELECT 2,3,’Mohan’ INSERT emp SELECT 3,NULL,’Shobha’ INSERT emp SELECT 4,2,’Shridhar’ INSERT emp SELECT 5,2,’Sourabh’
- SELECT t1.empname [Employee], t2.empname [Manager] FROM emp t1, emp t2 WHERE t1.mgrid = t2.empid Here’s an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
- SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid

Microsoft platform and database technologies interview questions

3 main differences between flexgrid control and dbgrid control
ActiveX and Types of ActiveX Components in VB
Advantage of ActiveX Dll over Active Exe
Advantages of disconnected recordsets
Benefit of wrapping database calls into MTS transactions
Benefits of using MTS
Can database schema be changed with DAO, RDO or ADO?
Can you create a tabletype of recordset in Jet - connected ODBC database engine?
Constructors and destructors
Controls which do not have events
Default property of datacontrol
Define the scope of Public, Private, Friend procedures?
Describe Database Connection pooling relative to MTS
Describe: In of Process vs. Out of Process component. Which is faster?
Difference between a function and a subroutine, Dynaset and
Snapshot,early and late binding, image and picture controls,Linked Object and Embedded Object,listbox and combo box,Listindex and Tab index,modal and moduless window, Object and Class,Query unload and unload in form.
Declaration and Instantiation an object?
Draw and explain Sequence Modal of DAO
How can objects on different threads communicate with one another?
How can you force new objects to be created on new threads?
How does a DCOM component know where to instantiate itself?
How to register a component?
How to set a shortcut key for label?
Kind of components can be used as DCOM servers
Name of the control used to call a windows application
Name the four different cursor and locking types in ADO and describe them briefly.
Need of zorder method, no of controls in form, Property used to add a menus at runtime, Property used to count number of items in a combobox,resize a label control according to your caption.
Return value of callback function, The need of tabindex property.
Thread pool and management of threads within a thread pool.
To set the command button for ESC, Which property needs to be changed?
Type Library and what is it’s purpose?
Types of system controls, container objects, combo box
Under the ADO Command Object, what collection is responsible for input to stored procedures?
VB and Object Oriented Programming
What are the ADO objects? Explain them.

Database admin interview questions

What is a major difference between SQL Server 6.5 and 7.0 platform wise? SQL Server 6.5 runs only on Windows NT Server. SQL Server 7.0 runs on Windows NT Server, workstation and Windows 95/98.
Is SQL Server implemented as a service or an application? It is implemented as a service on Windows NT server and workstation and as an application on Windows 95/98.
What is the difference in Login Security Modes between v6.5 and 7.0? 7.0 doesn’t have Standard Mode, only Windows NT Integrated mode and Mixed mode that consists of both Windows NT Integrated and SQL Server authentication modes.
What is a traditional Network Library for SQL Servers? Named Pipes.
What is a default TCP/IP socket assigned for SQL Server? 1433
If you encounter this kind of an error message, what you need to look into to solve this problem?
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.
1. Check if MS SQL Server service is running on the computer you are trying to log into
2. Check on Client Configuration utility. Client and Server have to in sync.
What is new philosophy for database devises for SQL Server 7.0? There are no devises anymore in SQL Server 7.0. It is file system now.
When you create a database how is it stored? It is stored in two separate files: one file contains the data, system tables, other database objects, the other file stores the transaction log.
Let’s assume you have data that resides on SQL Server 6.5. You have to move it SQL Server 7.0. How are you going to do it? You have to use transfer command.
Do you know how to configure DB2 side of the application? Set up an application ID, create RACF group with tables attached to this group, attach the ID to this RACF group.
What kind of LAN types do you know? Ethernet networks and token ring networks.
What is the difference between them? With Ethernet, any devices on the network can send data in a packet to any location on the network at any time. With Token Ring, data is transmitted in ‘tokens’ from computer to computer in a ring or star configuration. Token ring speed is 4/16 Mbit/sec , Ethernet - 10/100 Mbit/sec.
What protocol both networks use? TCP/IP. Transmission Control Protocol, Internet Protocol.
How many bits IP Address consist of?An IP Address is a 32-bit number.
How many layers of TCP/IP protocol combined of? Five. (Application, Transport, Internet, Data link, Physical).
How do you define testing of network layers? Reviewing with your developers to identify the layers of the Network layered architecture, your Web client and Web server application interact with. Determine the hardware and software configuration dependencies for the application under test.
How do you test proper TCP/IP configuration Windows machine? Windows NT: IPCONFIG/ALL, Windows 95: WINIPCFG, Ping or ping ip.add.re.ss

PL/SQL interview qiuestions

Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.
Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.
Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.
The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values

Read the following code:

CREATE OR REPLACE PROCEDURE find_cpt
(v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
IS
BEGIN
 IF v_cost_per_ticket  > 8.5 THEN
SELECT  cost_per_ticket
INTO            v_cost_per_ticket
FROM            gross_receipt
WHERE   movie_id = v_movie_id;
 END IF;
END;

Which mode should be used for V_COST_PER_TICKET?

IN
OUT
RETURN
IN OUT

Read the following code:
```
CREATE OR REPLACE TRIGGER update_show_gross
     {trigger information}
    BEGIN
     {additional code}
    END;
```
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)
What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None
For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row
2. Statement only
3. Row only
4. Oracle Forms trigger
Read the following code:
```
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS

v_yearly_budget NUMBER;

BEGIN
      SELECT  yearly_budget
      INTO            v_yearly_budget
      FROM            studio
      WHERE   id = v_studio_id;

      RETURN v_yearly_budget;
END;
```
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
  EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
  EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
  EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER
  :g_yearly_budget := GET_BUDGET(11);
```
CREATE OR REPLACE PROCEDURE update_theater
(v_name IN VARCHAR v_theater_id IN NUMBER) IS
BEGIN
      UPDATE  theater
      SET             name = v_name
      WHERE   id = v_theater_id;
END update_theater;
```
When invoking this procedure, you encounter the error:
```
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
```
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
Read the following code:
```
CREATE OR REPLACE PROCEDURE calculate_budget IS
v_budget        studio.yearly_budget%TYPE;
BEGIN
      v_budget := get_budget(11);
      IF v_budget < 30000
  THEN
              set_budget(11,30000000);
      END IF;
END;
```
You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.
Which procedure can be used to create a customized error message?
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
Examine this database trigger
```
CREATE OR REPLACE TRIGGER prevent_gross_modification
{additional trigger information}
BEGIN
      IF TO_CHAR(sysdate, DY) = MON
 THEN
 RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
      END IF;
END;
```
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt

Examine this function:

CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
BEGIN
      UPDATE  studio
      SET             yearly_budget = v_new_budget
      WHERE   id = v_studio_id;

      IF SQL%FOUND THEN
              RETURN TRUEl;
      ELSE
              RETURN FALSE;
      END IF;

      COMMIT;
END;

Which code must be added to successfully compile this function?

Add RETURN right before the IS keyword.
Add RETURN number right before the IS keyword.
Add RETURN boolean right after the IS keyword.
Add RETURN boolean right before the IS keyword.

Under which circumstance must you recompile the package body after recompiling the package specification?
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed
Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
1. DBMS_DISPLAY
2. DBMS_OUTPUT
3. DBMS_LIST
4. DBMS_DESCRIBE
What occurs if a procedure or function terminates with failure without being handled?
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
Examine this code
```
BEGIN
      theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
END;
```
For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL

JDBC interview questions

What are the steps involved in establishing a JDBC connection? This action involves two steps: loading the JDBC driver and making the connection.
How can you load the drivers?
Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:
Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ, you would load the driver with the following line of code:

Class.forName(”jdbc.DriverXYZ”);

What will Class.forName do while loading drivers? It is used to create an instance of a driver and register it with the
DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.
How can you make the connection? To establish a connection you need to have the appropriate driver connect to the DBMS.
The following line of code illustrates the general idea:
String url = “jdbc:odbc:Fred”;
Connection con = DriverManager.getConnection(url, “Fernanda”, “J8?);
How can you create JDBC statements and what are they?
A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object
Statement stmt = con.createStatement();
How can you retrieve data from the ResultSet?
JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.
ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);
String s = rs.getString(”COF_NAME”);

The method getString is invoked on the ResultSet object rs, so getString() will retrieve (get) the value stored in the column COF_NAME in the current row of rs.
What are the different types of Statements?
Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)
How can you use PreparedStatement? This special type of statement is derived from class Statement.If you need a
Statement object to execute many times, it will normally make sense to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement’s SQL statement without having to compile it first.
```
PreparedStatement updateSales =
 con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
```

What does setAutoCommit do?
When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:

con.setAutoCommit(false);

Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly.

con.setAutoCommit(false);
PreparedStatement updateSales =
 con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50); updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal =
 con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);

How do you call a stored procedure from JDBC?
The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open
Connection object. A CallableStatement object contains a call to a stored procedure.
```
 CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
 ResultSet rs = cs.executeQuery();
```
How do I retrieve warnings?
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an
application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a
Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these
classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object:
```
SQLWarning warning = stmt.getWarnings();
if (warning != null)
{
 System.out.println("n---Warning---n");
 while (warning != null)
 {
  System.out.println("Message: " + warning.getMessage());
  System.out.println("SQLState: " + warning.getSQLState());
  System.out.print("Vendor error code: ");
  System.out.println(warning.getErrorCode());
  System.out.println("");
  warning = warning.getNextWarning();
 }
}
```
How can you move the cursor in scrollable result sets?
One of the new features in the JDBC 2.0 API is the ability to move a result set’s cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);

The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened:
```
Statement stmt =
 con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs =
 stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous())
{
 String name = srs.getString("COF_NAME");
 float price = srs.getFloat("PRICE");
 System.out.println(name + " " + price);
}
```
How to Make Updates to Updatable Result Sets?
Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
```
Connection con =
 DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt =
 con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs =
 stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
```

SQL Server interview questions

How do you read transaction logs?
How do you reset or reseed the IDENTITY column?

How do you persist objects, permissions in tempdb?
How do you simulate a deadlock for testing purposes?
How do you rename an SQL Server computer?
How do you run jobs from T-SQL?
How do you restore single tables from backup in SQL Server 7.0/2000? In SQL Server 6.5?
Where to get the latest MDAC from?
I forgot/lost the sa password. What do I do?
I have only the .mdf file backup and no SQL Server database backups. Can I get my database back into SQL Server?
How do you add a new column at a specific position (say at the beginning of the table or after the second column) using ALTER TABLE command?
How do you change or alter a user defined data type?
How do you rename an SQL Server 2000 instance?
How do you capture/redirect detailed deadlock information into the error logs?
How do you remotely administer SQL Server?
What are the effects of switching SQL Server from ‘Mixed mode’ to ‘Windows only’ authentication mode? What are the steps required, to not break existing applications?
Is there a command to list all the tables and their associated filegroups?
How do you ship the stored procedures, user defined functions (UDFs), triggers, views of my application, in an encrypted form to my clients/customers? How do you protect intellectual property?
How do you archive data from my tables? Is there a built-in command or tool for this?
How do you troubleshoot ODBC timeout expired errors experienced by applications accessing SQL Server databases?
How do you restart SQL Server service automatically at regular intervals?
What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?
How do you programmatically find out when the SQL Server service started?
How do you get rid of the time part from the date returned by GETDATE function?
How do you upload images or binary files into SQL Server tables?
How do you run an SQL script file that is located on the disk, using T-SQL?
How do you get the complete error message from T-SQL while error handling?
How do you get the first day of the week, last day of the week and last day of the month using T-SQL date functions?
How do you pass a table name, column name etc. to the stored procedure so that I can dynamically select from a table?
Error inside a stored procedure is not being raised to my front-end applications using ADO. But I get the error when I run the procedure from Query Analyzer.
How do you suppress error messages in stored procedures/triggers etc. using T-SQL?
How do you save the output of a query/stored procedure to a text file?
How do you join tables from different databases?
How do you join tables from different servers?
How do you convert timestamp data to date data (datetime datatype)?
Can I invoke/instantiate COM objects from within stored procedures or triggers using T-SQL?
Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or How do you generate output with row number in SQL Server?
How do you specify a network library like TCP/IP using ADO connect string?
How do you generate scripts for repetitive tasks like truncating all the tables in a database, changing owner of all the database objects, disabling constraints on all tables etc?
Is there a way to find out when a stored procedure was last updated?
How do you find out all the IDENTITY columns of all the tables in a given database?
How do you search the code of stored procedures?
How do you retrieve the generated GUID value of a newly inserted row? Is there an @@GUID, just like @@IDENTITY?

Java database interview questions

How do you call a Stored Procedure from JDBC? - The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure.
```
 CallableStatement cs =
  con.prepareCall("{call SHOW_SUPPLIERS}");
 ResultSet rs = cs.executeQuery();
```
Is the JDBC-ODBC Bridge multi-threaded? - No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but they won’t get the advantages of multi-threading.
Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection? - No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
What is cold backup, hot backup, warm backup recovery? - Cold backup (All these files must be backed up at the same time, before the databaseis restarted). Hot backup (official name is ‘online backup’) is a backup taken of each tablespace while the database is running and is being accessed by the users.
When we will Denormalize data? - Data denormalization is reverse procedure, carried out purely for reasons of improving performance. It maybe efficient for a high-throughput system to replicate data for certain data.
What is the advantage of using PreparedStatement? - If we are using PreparedStatement the execution time will be less. The PreparedStatement object contains not just an SQL statement, but the SQL statement that has been precompiled. This means that when the PreparedStatement is executed,the RDBMS can just run the PreparedStatement’s Sql statement without having to compile it first.
What is a “dirty read”? - Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value. While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency.
What is Metadata and why should I use it? - Metadata (’data about data’) is information about one of two things: Database information (java.sql.DatabaseMetaData), or Information about a specific ResultSet (java.sql.ResultSetMetaData). Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns
Different types of Transaction Isolation Levels? - The isolation level describes the degree to which the data being updated is visible to other transactions. This is important when two transactions are trying to read the same row of a table. Imagine two transactions: A and B. Here three types of inconsistencies can occur:
- Dirty-read: A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong if A rolls back his changes and updates his own changes to the database.
- Non-repeatable read: B performs a read, but A modifies or deletes that data later. If B reads the same row again, he will get different data.
- Phantoms: A does a query on a set of rows to perform an operation. B modifies the table such that a query of A would have given a different result. The table may be inconsistent.
TRANSACTION_READ_UNCOMMITTED : DIRTY READS, NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_READ_COMMITTED : DIRTY READS ARE PREVENTED, NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_REPEATABLE_READ : DIRTY READS , NON-REPEATABLE READ ARE PREVENTED AND PHANTOMS CAN OCCUR.
TRANSACTION_SERIALIZABLE : DIRTY READS, NON-REPEATABLE READ AND PHANTOMS ARE PREVENTED.
What is 2 phase commit? - A 2-phase commit is an algorithm used to ensure the integrity of a committing transaction. In Phase 1, the transaction coordinator contacts potential participants in the transaction. The participants all agree to make the results of the transaction permanent but do not do so immediately. The participants log information to disk to ensure they can complete In phase 2 f all the participants agree to commit, the coordinator logs that agreement and the outcome is decided. The recording of this agreement in the log ends in Phase 2, the coordinator informs each participant of the decision, and they permanently update their resources.
How do you handle your own transaction ? - Connection Object has a method called setAutocommit(Boolean istrue)
- Default is true. Set the Parameter to false , and begin your transaction
What is the normal procedure followed by a java client to access the db.? - The database connection is created in 3 steps:
1. Find a proper database URL
2. Load the database driver
3. Ask the Java DriverManager class to open a connection to your database
In java code, the steps are realized in code as follows:
1. Create a properly formatted JDBR URL for your database. (See FAQ on JDBC URL for more information). A JDBC URL has the form
  jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
2. Class.forName(”my.database.driver”);
3. Connection conn = DriverManager.getConnection(”a.JDBC.URL”, “databaseLogin”,”databasePassword”);
What is a data source? - A DataSource class brings another level of abstraction than directly using a connection object. Data source can be referenced by JNDI. Data Source may point to RDBMS, file System , any DBMS etc.
What are collection pools? What are the advantages? - A connection pool is a cache of database connections that is maintained in memory, so that the connections may be reused

How do you get Column names only for a table (SQL Server)? Write the Query. -

 select name from syscolumns
  where id=(select id from sysobjects where name='user_hdr')
  order by colid --user_hdr is the table name

MS SQL Server interview questions

What is normalization? - Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
What is a Stored Procedure? - Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

Can you give an example of Stored Procedure? - sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.
What is a trigger? - Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
What is a view? - If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
What is an Index? - When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
What are the types of indexes available with SQL Server? - There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
What is the basic difference between clustered and a non-clustered index? - The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
What are cursors? - Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.
When do we use the UPDATE_STATISTICS command? - This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
Which TCP/IP port does SQL Server run on? - SQL Server runs on port 1433 but we can also change it for better security.
From where can you change the default port? - From the Network Utility TCP/IP properties –> Port number.both on client and the server.
Can you tell me the difference between DELETE & 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.
Can we use Truncate command on a table which is referenced by FOREIGN KEY? - No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
What is the use of DBCC commands? - DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
Can you give me some DBCC command options?(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
What command do we use to rename a db? - sp_renamedb ‘oldname’ , ‘newname’
Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? - In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? - Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What do you mean by COLLATION? - Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
What is a Join in SQL Server? - Join actually puts data from two or more tables into a single result set.
Can you explain the types of Joins that we can have with Sql Server? - There are three types of joins: Inner Join, Outer Join, Cross Join
When do you use SQL Profiler? - SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..
What is a Linked Server? - Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
Can you link only other SQL Servers or any database servers such as Oracle? - We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.
Which stored procedure will you be running to add a linked server? - sp_addlinkedserver, sp_addlinkedsrvlogin
What are the OS services that the SQL Server installation adds? - MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
Can you explain the role of each service? - SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers
How do you troubleshoot SQL Server if its running very slow? - First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes
Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot? - First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.
What are the authentication modes in SQL Server? - Windows mode and mixed mode (SQL & Windows).
Where do you think the users names and passwords will be stored in sql server? - They get stored in master db in the sysxlogins table.
What is log shipping? Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.
Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow? - For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER –m which will basically bring it into the maintenance mode after which we can restore the master db.
Let us say master db itself has no backup. Now you have to rebuild the db so what kind of action do you take? - (I am not sure- but I think we have a command to do it).
What is BCP? When do we use it? - BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
What should we do to copy the tables, schema and views from one SQL Server to another? - We have to write some DTS packages for it.
What are the different types of joins and what dies each do?
What are the four main query statements?
What is a sub-query? When would you use one?
What is a NOLOCK?
What are three SQL keywords used to change or set someone’s permissions?
What is the difference between HAVING clause and the WHERE clause?
What is referential integrity? What are the advantages of it?
What is database normalization?
Which command using Query Analyzer will give you the version of SQL server and operating system?
Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?
What is the purpose of using COLLATE in a query?
What is a trigger?
What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”
What is an execution plan? When would you use it? How would you view the execution plan?
What is the STUFF function and how does it differ from the REPLACE function?
What does it mean to have quoted_identifier on? What are the implications of having it off?
What are the different types of replication? How are they used?
What is the difference between a local and a global variable?
What is the difference between a Local temporary table and a Global temporary table? How is each one used?
What are cursors? Name four types of cursors and when each one would be applied?
What is the purpose of UPDATE STATISTICS?
How do you use DBCC statements to monitor various aspects of a SQL server installation?
How do you load large data to the SQL server database?
How do you check the performance of a query and how do you optimize it?
How do SQL server 2000 and XML linked? Can XML be used to access data?
What is SQL server agent?
What is referential integrity and how is it achieved?
What is indexing?
What is normalization and what are the different forms of normalizations?
Difference between server.transfer and server.execute method?
What id de-normalization and when do you do it?
What is better - 2nd Normal form or 3rd normal form? Why?
Can we rewrite subqueries into simple select statements or with joins? Example?
What is a function? Give some example?
What is a stored procedure?
Difference between Function and Procedure-in general?
Difference between Function and Stored Procedure?
Can a stored procedure call another stored procedure. If yes what level and can it be controlled?
Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?
How do you find the number of rows in a table?
Difference between Cluster and Non-cluster index?
What is a table called, if it does not have neither Cluster nor Non-cluster Index?
Explain DBMS, RDBMS?
Explain basic SQL queries with SELECT from where Order By, Group By-Having?
Explain the basic concepts of SQL server architecture?
Explain couple pf features of SQL server
Scalability, Availability, Integration with internet, etc.)?
Explain fundamentals of Data ware housing & OLAP?
Explain the new features of SQL server 2000?
How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?
What is data integrity? Explain constraints?
Explain some DBCC commands?
Explain sp_configure commands, set commands?
Explain what are db_options used for?
What is the basic functions for master, msdb, tempdb databases?
What is a job?
What are tasks?
What are primary keys and foreign keys?
How would you Update the rows which are divisible by 10, given a set of numbers in column?
If a stored procedure is taking a table data type, how it looks?
How m-m relationships are implemented?
How do you know which index a table is using?
How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?
How do you find the error, how can you know the number of rows effected by last SQL statement?
How can you get @@error and @@rowcount at the same time?
What are sub-queries? Give example? In which case sub-queries are not feasible?
What are the type of joins? When do we use Outer and Self joins?
Which virtual table does a trigger use?
How do you measure the performance of a stored procedure?
Questions regarding Raiseerror?
Questions on identity?
If there is failure during updation of certain rows, what will be the state?

Typical Oracle questions

Tell us about yourself, your background.
What are the three major characteristics that you bring to this company?
What version of Oracle were you running?
How many databases did the organization have and what sizes?
What motivates you to do a good job?
What two or three things are most important to you at work?

What qualities do you think are essential to be successful in this kind of work?
What courses did you attend? What job certifications do you hold?
What subjects/courses did you excel in? Why?
What subjects/courses gave you trouble? Why?
How does your previous work experience prepare you for this position?
How do you define ’success’?
What has been your most significant accomplishment to date?
Describe a challenge you encountered and how you dealt with it.
Describe a failure and how you dealt with it.
Describe the ‘ideal’ job… the ‘ideal’ supervisor.
What leadership roles have you held?
What prejudices do you hold?
What do you like to do in your spare time?
What are your career goals (a) 3 years from now; (b) 10 years from now?
How does this position match your career goals?
What have you done in the past year to improve yourself?
In what areas do you feel you need further education and training to be successful?
What do you know about our company?
Why do you want to work for this company. Why should we hire you?
Where do you see yourself fitting in to this organization initially? How about in 5 years?
Why are you looking for a new job?
How do you feel about re-locating?
Are you willing to travel?
What are your salary requirements?
When would you be available to start if you were selected?
Did you use online or off-line backups?
If you have to advise a backup strategy for a new application, how would you approach it and what questions will you ask?
If a customer calls you about a hanging database session, what will you do to resolve it?
Compare Oracle to any other database that you know. Why would you prefer to work on one and not on the other?

Interview questions for DBA

How many memory layers are in the shared pool?
How do you find out from the RMAN catalog if a particular archive log has been backed-up?
How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?
Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?

What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
How do you tell what your machine name is and what is its IP address?
How would you go about verifying the network name that the local_listener is currently using?
You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
What is the recommended interval at which to run statspack snapshots, and why?
What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
How would you best determine why your MVIEW couldn’t FAST REFRESH?
How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
How would you begin to troubleshoot an ORA-3113 error?
Which dictionary tables and/or views would you look at to diagnose a locking issue?
An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed’, how do you approach troubleshooting this issue?
How would you extract DDL of a table without using a GUI tool?
You’re getting high “busy buffer waits’ - how can you find what’s causing it?
What query tells you how much space a tablespace named “test’ is taking up, and how much space is remaining?
Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.
Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
How do you increase the OS limitation for open files (LINUX and/or Solaris)?
Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
Explain how you would restore a database using RMAN to Point in Time?
How does Oracle guarantee data integrity of data changes?
Which environment variables are absolutely critical in order to run the OUI?
What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
Why does Oracle not permit the use of PCTUSED with indexes?
What would you use to improve performance on an insert statement that places millions of rows into that table?
What would you do with an “in-doubt” distributed transaction?
What are the commands you’d issue to show the explain plan for “select * from dual’?
In what script is “snap$” created? In what script is the “scott/tiger” schema created?
If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
Explain the concept of the DUAL table.
What are the ways tablespaces can be managed and how do they differ?
From the database level, how can you tell under which time zone a database is operating?
What’s the benefit of “dbms_stats” over “analyze”?
Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.

Basic database interview quesitons

What are the different types of joins?
Explain normalization with examples.
What cursor type do you use to retrieve multiple recordsets?
Diffrence between a “where” clause and a “having” clause
What is the difference between “procedure” and “function”?
How will you copy the structure of a table without copying the data?

How to find out the database name from SQL*PLUS command prompt?
Tadeoffs with having indexes
Talk about “Exception Handling” in PL/SQL?
What is the diference between “NULL in C” and “NULL in Oracle?”
What is Pro*C? What is OCI?
Give some examples of Analytical functions.
What is the difference between “translate” and “replace”?
What is DYNAMIC SQL method 4?
How to remove duplicate records from a table?
What is the use of ANALYZing the tables?
How to run SQL script from a Unix Shell?
What is a “transaction”? Why are they necessary?
Explain Normalizationa dn Denormalization with examples.
When do you get contraint violtaion? What are the types of constraints?
How to convert RAW datatype into TEXT?
Difference - Primary Key and Aggregate Key
How functional dependency is related to database table design?
What is a “trigger”?
Why can a “group by” or “order by” clause be expensive to process?
What are “HINTS”? What is “index covering” of a query?
What is a VIEW? How to get script for a view?
What are the Large object types suported by Oracle?
What is SQL*Loader?
Difference between “VARCHAR” and “VARCHAR2″ datatypes.
What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
Difference between “ORACLE” and “MICROSOFT ACCESS” databases.
How to create a database link?

Oracle interview questions

What are the built-in functions used for sending Parameters to forms?
Can you have more than one content canvas view attached with a window?
Is the After report trigger fired if the report execution fails?
Does a Before form trigger fire when the parameter form is suppressed?
Is it possible to split the print reviewer into more than one region?

Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
Can a field be used in a report without it appearing in any data group?
The join defined by the default data link is an outer join yes or no?
Can a formula column referred to columns in higher group?
Can a formula column be obtained through a select statement?
Is it possible to insert comments into sql statements return in the data model editor?
Is it possible to disable the parameter from while running the report?
When a form is invoked with call_form, Does oracle forms issues a save point?
Can a property clause itself be based on a property clause?
If a parameter is used in a query without being previously defined, what diff. exist betw. report 2.0 and 2.5 when the query is applied?
What are the sql clauses supported in the link property sheet?
What is trigger associated with the timer?
What are the trigger associated with image items?
What are the different windows events activated at runtime?

.NET database development questions

To test a Web Service you must create a windows application or web application to consume this service? It is True/False?
How many classes can a single.NET DLL contain?
What are good ADO.NET object(s) to replace the ADO Recordset object?
On order to get assembly info which namespace we should import?
How do you declare a static variable and what is its lifetime? Give an example.
How do you get records number from 5 to 15 in a dataset of 100 records? Write code.

How do you call and execute a Stored Procedure in.NET? Give an example.
What is the maximum length of a varchar in SQL Server?
How do you define an integer in SQL Server?
How do you separate business logic while creating an ASP.NET application?
If there is a calendar control to be included in each page of your application, and and we do not intend to use the Microsoft-provided calendar control, how do you develop it? Do you copy and paste the code into each and every page of your application?
How do you debug an ASP.NET application?
How do you deploy an ASP.NET application?
Explain similarities and differences between Java and.NET?
Specify the best ways to store variables so that we can access them in various pages of ASP.NET application?
What are theXML files that are important in developing an ASP.NET application?
What are theXML files that are important in developing an ASP.NET application?
What is XSLT and what is its use?
How many objects are there in ASP?
Which DLL file is needed to be registered for ASP?
Is there any inbuilt paging (for example shoping cart, which will show next 10 records without refreshing) in ASP? How will you do pating?
What does Server.MapPath do?
Name atleast three methods of response object other than Redirect.
Name atleast two methods of response object other than Transfer.
What is State?
Explain differences between ADO and DAO.
How many types of cookies are there?
Tell few steps for optimizing (for speed and resource) ASP page/application.
Which command using Query Analyzer will give you the version of SQL Server and Operating System?
Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table.
What is the purpose of using COLLATE in a query?
What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”?
What is an execution plan? When would you use it? How would you view the execution plan?
What is the STUFF function and how does it differ from the REPLACE function?
What does it mean to have quoted_identifier on? What are the implications of having it off?
What are the different type of replication? How are they used?
What is the difference between a Local temporary table and a Global temporary table? How is each one used?
What are cursors? Name four type of cursors and when each one would be applied?
What is the purpose of UPDATE STATISTICS?
How do you use DBCC statements to monitor various ASPects of a SQL Server installation?
How do SQL Server 2000 andXML linked? What is SQL Server agent?
What is referential integrity and how can we achieve it?
What is indexing?
Explain differences between server.transfer and server.execute method?
What is de-normalization? When do you do it and how?
Explain features of SQL Server like Scalibility, Availability, Integration with Internet.
What is DataWarehousing?
What is OLAP?
How do we upgrade SQL Server 7.0 to 2000?
What is job?
What is Task?
How would you update the rows which are divisible by 10, given a set of numbers in column?
How do you find the error, how can you know the number of rows affected by last SQL Statement?
What are the advantages/disadvantages of viewstate?
Describe session handling in webform. How does it work and what are the limits?
Explain differences between framework 1.0 and framework 1.1
If we write any code for dataGrid methods, what is the access specifier used for that methods in the code behind file and why and how? Give an example.
What is the use of trace utility?
What are the differences between User control and Web control and Custom control?
If I have more than one version of one assemblies, then how will I use old version in my application? Give an example.
How do you create threadinf in.NET?
Describe the Managed Execution Process.
What is Active Directory? What is the namespace used to access the Microsoft Active Directories?
What are Interop Services?
How does you handle this COM components developed in other programming languages in.NET?
How will you register COM+ services?

Interview Questions With Answers

Archives

Subscribe

Powered By