IT Interview Questions

1. Explain the difference between a hot backup and a cold backup and the benefits associated with each. - A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

1. What’s the command to see the current user name? Sql> show user;
2. What’s the command to change the SQL prompt name?
   

   SQL> set sqlprompt “database-1 > ”
   database-1 >
   database-1 >

3. How do you switch to DOS prompt from SQL prompt? SQL> host
4. How do I eliminate duplicate rows in an Oracle database?
   

   SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);

   or

   SQL> delete duplicate_values_field_name dv from table_name ta where rowid < (select min(rowid) from table_name tb where ta.dv=tb.dv);

   1. 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.

   2. 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.

   3. 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.

  11. Which TCP/IP port does SQL Server run on? - SQL Server runs on port 1433 but we can also change it for better security.

12. From where can you change the default port? - From the Network Utility TCP/IP properties –> Port number.both on client and the server.

  13. 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.

14. 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.

21. What is a Join in SQL Server? - Join actually puts data from two or more tables into a single result set.

  22. 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

23. 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..