SQL Server Interview Q&A

1. Please tell me how to find the 10th highest paid employee in an EMPLOYEE table whose attributes are EMPID, ENAME, SAL, AGE, DEPTNO.
Answer:
SELECT * from EMPLOYEE
where rownum = 10
order by sal desc

or

CREATE PROCEDURE highest_paid_employee(@rnk int)
AS
SELECT * FROM EMPLOYEE AS e
WHERE @rnk = (select count(distinct SAL) from EMPLOYEE x
where x.SAL >= e.SAL)
GO


2. How do you reset or reseed the IDENTITY column?
Answer:
DBCC CHECKIDENT (jobs, RESEED, 100)

Or refer to this website for better answer...
http://www.dbazine.com/sql/sql-articles/charran7


3. How do you get rid of the time part from the date returned by GETDATE function?
Answer:
SELECT CONVERT(varchar, GETDATE(), 105)
SELECT CONVERT(char,GETDATE(),101)
SELECT CONVERT(char,GETDATE(),102)
SELECT CONVERT(char,GETDATE(),103)
SELECT CONVERT(char,GETDATE(),1)


4. How do you simulate a deadlock for testing purposes?
Answer:
In Query Analyzer, run the following statements first:

CREATE TABLE t1 (i int)
CREATE TABLE t2 (i int)

INSERT t1 SELECT 1
INSERT t2 SELECT 9

Open a new window (say Window1) in Query Analyzer, paste the following SQL statements:

BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY ‘00:00:20′
UPDATE t2 SET i = 99 WHERE i = 9
COMMIT

Open another window (say Window2) in Query Analyzer and paste the following code:

BEGIN TRAN
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY ‘00:00:20′
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT

Now run the code from Window1, followed by Window2 simultaneously. Briefly after 20 seconds, one of the windows will experience a dead lock!

5. I forgot/lost the sa password. What do I do?
Answer:
- Login to the SQL Server computer as the Administrator of that computer.
- Open Query Analyzer and connect to SQL Server using Windows NT authentication.

- Run sp_password as show below to reset the sa password:sp_password @new = ‘will_never_forget_again’, @loginame = ’sa’


6. How do you archive data from my tables? Is there a built-in command or tool for this?
Answer:
- Archiving is the process of getting rid of old data (or data that is not valid anymore) from the main production databases.
- There is no built-in command or tool for archiving databases. In this article I’m going to show you how to implement this process.
- Why archive in the first place? By archiving old data, you can keep your databases smaller and more manageable. With smaller tables, your index rebuild times and backup/restore times will be under control. You will also see performance gains on certain queries that either scan a larger portion of the table or clustered index.
- Depending on the business needs, one might choose to either delete the unwanted data, or copy the data to a history table and delete that data from the production database.

Implementing an archival process is not that complicated. The three logical steps involved in this process are:
- Identifying the data to be archived.
- Optionally copying the identified data to a history table.
- Deleting the identified data from the production database.

7. How do you restart SQL Server service automatically at regular intervals?
Answer:
@ECHO OFF
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT

If your requirement is to restart SQL Server every Sunday mid-night, run the following command at the command prompt of the SQL Server computer:

AT 00:00 /EVERY:Su C:\RestartSQL.BAT

This will schedule the batch file RestartSQL.BAT to run every Sunday night at 12:00 AM.


8. How do you programmatically find out when the SQL Server service started?
Answer:
Everytime SQL Server starts, it recreates the tempdb database. So, the creation date and time of the tempdb database tells us the date and time at which SQL Server service started. This information is stored in the crdate column of the sysdatabases table in master database. Here’s the query to find that out:

SELECT crdate AS ‘SQL Server service started approximately at:’

FROM master.dbo.sysdatabases
WHERE name = ‘tempdb’


9. 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?
Answer:
SELECT DATEADD(dd,-(DATEPART(dw, getdate()) - 1),getdate()) AS ‘First day of the week’
SELECT DATEADD(dd,-(DATEPART(dw, getdate()) - 7),getdate()) AS ‘Last day of the week’
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,getdate())),DATEADD(m,1,getdate()))) AS ‘Last day of the month’


10. How do you pass a table name, column name etc. to the stored procedure so that I can dynamically select from a table?
Answer:
CREATE PROC DropTable
@Table sysname AS
EXEC (’DROP TABLE ‘ + @Table)
GO


11. How do you suppress error messages in stored procedures/triggers etc. using T-SQL?
Answer:
It’s not possible to suppress error messages from within T-SQL. Error messages are always returned to the client. If you don’t want your users to see these raw error messages, you should handle them in your front-end applications.

For example, if you are using ADO from ASP to connect to SQL Server, you would do something like the following:

On Error Resume Next
Set Rs = Conn.Execute (”INSERT INTO MyTable (1,’How to migrate from Oracle to SQL Server’,'Book’”)
If Err.Number 0 Then Response.Write (”Error occurred while inserting new data”)
On Error GoTo 0


12. How do you save the output of a query/stored procedure to a text file?
Answer:
T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:

From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q “EXEC sp_who2″ -o “E:\output.txt”

From T-SQL:
EXEC master..xp_cmdshell ‘osql.exe -S YourServerName -U sa -P secretcode -Q “EXEC sp_who2″ -o “E:\output.txt”‘

Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.

BCP and Data Transformation Services (DTS) let you export table data to text files.


13. How do you join tables from different databases?
Answer:
SELECT a.i, a.j
FROM pubs.dbo.Table1 a INNER JOIN northwind.dbo.Table2 b
ON a.i = b.i
GO


14. How do you join tables from different servers?
Answer:
To be able to join tables between two SQL Servers, first you have to link them. After the linked servers are setup, you just have to prefix your tables names with server name, database name, table owner name in your SELECT queries. The following example links SERVER_01 to SERVER_02. Execute the following commands in SERVER_02:

EXEC sp_addlinkedserver SERVER_01
GO

/* The following command links ’sa’ login on SERVER_02 with the ’sa’ login of SERVER_01 */
EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘SERVER_01′, @useself = ‘false’, @locallogin = ’sa’, @rmtuser = ’sa’, @rmtpassword =

’sa password of SERVER_01′
GO

SELECT a.title_id
FROM SERVER_01.pubs.dbo.titles a INNER JOIN SERVER_02.pubs.dbo.titles b
ON a.title_id = b.title_id
GO

15. Which of the following has the highest order of precedence?

• Functions and Parenthesis
• Multiplication, Division and Exponents
• Addition and Subtraction
• Logical Operations

16. When designing a database table, how do you avoid missing column values for non-primary key columns?

• Use UNIQUE constraints
• Use PRIMARY KEY constraints
• Use DEFAULT and NOT NULL constraints
• Use FOREIGN KEY constraints
• Use SET constraints

17. Which of the following is the syntax for creating an Index?

• CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)
• CREATE [UNIQUE] INDEX OF tbl_name (index_columns)
• CREATE [UNIQUE] INDEX ON tbl_name (index_columns)
• CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)

18. Which of the following is not a valid character datatype in SQL Server?

• BLOB
• CHAR
• VARCHAR
• TEXT
• VARTEXT

19. Which of the following statements about SQL Server comments is false?

• /* … */ are used for multiline comments
• // is used for single line comments
• – is used for single line comments
• Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
• ‘ is used for single line comments

20. Consider the following transaction code:

Begin Transaction
Update names_table set employee_name = "Ramesh" where employee_name = "Mahesh"
Save Transaction SAVE_POINT
Update salaries set salary=salary + 900 where employee_job = "Engineer"
Rollback transaction
Commit transaction
What will be the result produced by this transaction?

• “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be updated
• Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
• “Ramesh” will be updated to “Mahesh” and salary of engineers will also be updated.

21. Which of the following constraints can be used to enforce the uniqueness of rows in a table?

• DEFAULT and NOT NULL constraints
• FOREIGN KEY constraints
• PRIMARY KEY and UNIQUE constraints
• IDENTITY columns
• CHECK constraints

22. Which of the following are not date parts?

• quarter
• dayofweek
• dayofyear
• weekday

23. The IF UPDATE (column_name) parameter in a trigger definition will return TRUE in case of an INSERT statement being executed on the triggered table:

• Yes
• No
• It returns TRUE only if an UPDATE query is executed
• Both b and c

24. Which one of the following must be specified in every DELETE statement?

• Table Name
• Database name
• LIMIT clause
• WHERE clause
• Column Names

25. Which one of the following correctly selects rows from the table myTable that have null in column column1?

• SELECT * FROM myTable WHERE column1 is null
• SELECT * FROM myTable WHERE column1 = null
• SELECT * FROM myTable WHERE column1 EQUALS null
• SELECT * FROM myTable WHERE column1 NOT null
• SELECT * FROM myTable WHERE column1 CONTAINS null

26. Is this statement true or false: A cursor is a pointer that identifies a specific working row within a set

• True
• False

27. Which of the following commands is used to change the structure of table?

• CHANGE TABLE
• MODIFY TABLE
• ALTER TABLE
• UPDATE TABLE

28. Which of the following statements is true about implicit cursors?

• Implicit cursors are used for SQL statements that are not named.
• Developers should use implicit cursors with great care.
• Implicit cursors are used in cursor for loops to handle data processing.
• Implicit cursors are no longer a feature in Oracle.

29. Which of the following is not a feature of a cursor FOR loop?

• Record type declaration.
• Opening and parsing of SQL statements.
• Fetches records from cursor.
• Requires exit condition to be defined.

30. 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?

• Use employee.lname%type.
• Use employee.lname%rowtype.
• Look up datatype for EMPLOYEE column on LASTNAME table and use that.
• Declare it to be type LONG.

31. Which three of the following are implicit cursor attributes?

• %found
• %too_many_rows
• %notfound
• %rowcount
• %rowtype

32. If left out, which of the following would cause an infinite loop to occur in a simple loop?

• LOOP
• END LOOP
• IF-THEN
• EXIT

33. Which line in the following statement will produce an error?

• cursor action_cursor is
• select name, rate, action
• into action_record
• from action_table;
• There are no errors in this statement.

34. The command used to open a CURSOR FOR loop is

• open
• fetch
• parse
• None, cursor for loops handle cursor opening implicitly.

35. What happens when rows are found using a FETCH statement

• It causes the cursor to close
• It causes the cursor to open
• It loads the current row values into variables
• It creates the variables to hold the current row values

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

37. 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?

• WHEN (new.cost_per_ticket > 3.75)
• WHEN (:new.cost_per_ticket > 3.75
• WHERE (new.cost_per_ticket > 3.75)
• WHERE (:new.cost_per_ticket > 3.75)

38. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?

• Only one
• All that apply
• All referenced
• None

39. For which trigger timing can you reference the NEW and OLD qualifiers?

• Statement and Row
• Statement only
• Row only
• Oracle Forms trigger

40. 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?

• VARIABLE g_yearly_budget NUMBER
  EXECUTE g_yearly_budget := GET_BUDGET(11);
• VARIABLE g_yearly_budget NUMBER
  EXECUTE :g_yearly_budget := GET_BUDGET(11);
• VARIABLE :g_yearly_budget NUMBER
  EXECUTE :g_yearly_budget := GET_BUDGET(11);
• VARIABLE g_yearly_budget NUMBER
  EXECUTE :g_yearly_budget := GET_BUDGET(11);

41. Read the following code:

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?

• An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
• Handle the error in EXCEPTION section by referencing the error code directly.
• Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
• Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.

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

You are about to add an argument to CALCULATE_BUDGET. What effect will this have?

• The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
• The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
• Only the CALCULATE_BUDGET procedure needs to be recompiled.
• All three procedures are marked invalid and must be recompiled.

43. Which procedure can be used to create a customized error message?

• RAISE_ERROR
• SQLERRM
• RAISE_APPLICATION_ERROR
• RAISE_SERVER_ERROR

44. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?

• ALTER TRIGGER check_theater ENABLE;
• ENABLE TRIGGER check_theater;
• ALTER TABLE check_theater ENABLE check_theater;
• ENABLE check_theater;

45. 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?

• BEFORE DELETE ON gross_receipt
• AFTER DELETE ON gross_receipt
• BEFORE (gross_receipt DELETE)
• FOR EACH ROW DELETED FROM gross_receipt

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

47. Under which circumstance must you recompile the package body after recompiling the package specification?

• Altering the argument list of one of the package constructs
• Any change made to one of the package constructs
• Any SQL statement change made to one of the package constructs
• Removing a local variable from the DECLARE section of one of the package constructs

48. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?

• When the transaction is committed
• During the data manipulation statement
• When an Oracle supplied package references the trigger
• During a data manipulation statement and when the transaction is committed

49. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?

• DBMS_DISPLAY
• DBMS_OUTPUT
• DBMS_LIST
• DBMS_DESCRIBE

50. What occurs if a procedure or function terminates with failure without being handled?

• Any DML statements issued by the construct are still pending and can be committed or rolled back.
• Any DML statements issued by the construct are committed
• Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
• The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.

51. 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?

• 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.
• Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
• Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
• 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.

52. 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?

• DBMS_DDL
• DBMS_DML
• DBMS_SYN
• DBMS_SQL

53. What is denormalization and when would you go for it?
Answer:

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.

54. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Answer:

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

55. What’s the difference between a primary key and a unique key?
Answer:

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

56. What are user defined datatypes and when you should go for them?
Answer:

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

57. What is bit datatype and what’s the information that can be stored inside a bit column?
Answer:
- 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.

58. Define candidate key, alternate key, composite key.
Answer:

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

59. What are defaults? Is there a column to which a default can’t be bound?

Answer:

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

60. What is a transaction and what are ACID properties?
Answer:

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

61. CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Answer:

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

62. What’s the maximum size of a row?
Answer:

8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. 1024 columns per table.

63. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
Answer:

- 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

64. What are constraints? Explain different types of constraints.
Answer:

- 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"

65. 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?

Answer:

- 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 time, 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.

66. What is RAID and what are different types of RAID configurations?
Answer:

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

67. What are the steps you will take to improve performance of a poor performing query?

Answer:
- 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.

68. What are the steps you will take, if you are tasked with securing an SQL Server?
Answer:

- Preferring NT authentication, using server, database 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.

69. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Answer:

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

70. What is blocking and how would you troubleshoot it?
Answer:

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.

71. How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
Answer:

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.

72. What are the different ways of moving data/databases between servers and databases in SQL Server?
Answer:

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.

73. What is database replication? What are the different types of replication you can set up in SQL Server?

Answer:
- 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.

74. What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Answer:

- Cursors allow row-by-row processing of the resultsets.
- Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
- 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.

75. What is a join and explain different types of joins.
Answer:

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

76. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
Answer:

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

77. What is an advantage to using a stored procedure as opposed to passing an SQL query from an application.
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.

78. What is the difference of a LEFT JOIN and an INNER JOIN statement?
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

79. When a query is sent to the database and an index is not being used, what type of execution is taking place?
Answer:
A table scan.

80. What are the pros and cons of using triggers?
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.

81. Which of the following statements contains an error?

• SELECT * FROM emp WHERE empid = 493945;
• SELECT empid FROM emp WHERE empid= 493945;
• SELECT empid FROM emp;
• SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;

No FROM clause.

82. Which of the following correctly describes how to specify a column alias?

• Place the alias at the beginning of the statement to describe the table.
• Place the alias after each column, separated by white space, to describe the column.
• Place the alias after each column, separated by a comma, to describe the column.
• Place the alias at the end of the statement to describe the table.

83. The NVL function

• Assists in the distribution of output across multiple columns.
• Allows the user to specify alternate output for non-null column values.
• Allows the user to specify alternate output for null column values.
• Nullifies the value of the column output.

84. Output from a table called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of the following SQL statements produced it?

PLAY_TABLE
————————————-
“Midsummer Night’s Dream”, SHAKESPEARE
“Waiting For Godot”, BECKETT
“The Glass Menagerie”, WILLIAMS

• SELECT play_name || author FROM plays;
• SELECT play_name, author FROM plays;
• SELECT play_name||’, ‘ || author FROM plays;
• SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;

1st choice will produce titleauthor
2nd choice will produce title author
3rd choice will produce title, author but column name will be play_name||’,'||author

85. Issuing the DEFINE_EDITOR=”emacs” will produce which outcome?

• The emacs editor will become the SQL*Plus default text editor.
• The emacs editor will start running immediately.
• The emacs editor will no longer be used by SQL*Plus as the default text editor.
• The emacs editor will be deleted from the system.

86. The user issues the following statement. What will be displayed if the EMPID selected is 60494?

SELECT DECODE(empid,38475, “Terminated”,60494, “LOA”, “ACTIVE”)
FROM emp;

• 60494
• LOA
• Terminated
• ACTIVE

87. SELECT (TO_CHAR(NVL(SQRT(59483), “INVALID”)) FROM DUAL is a valid SQL statement.

• TRUE
• FALSE

Datatype mismatch. Cannot set value to another datatype when using NVL, hence if SQRT(59483) is null somehow, you must assign it a number, “invalid” is a string.

88. The appropriate table to use when performing arithmetic calculations on values defined within the SELECT statement (not pulled from a table column) is

• EMP
• The table containing the column values
• DUAL
• An Oracle-defined table

Dual is a dummy table.

89. Which of the following is not a group function?

• avg( )
• sqrt( )
• sum( )
• max( )

All other functions can work on more than one row of data.

90. Once defined, how long will a variable remain so in SQL*Plus?

• Until the database is shut down
• Until the instance is shut down
• Until the statement completes
• Until the session completes

This one depends on how you defined it. If you define variable using & then 3rd choice, && then 4th choice.

91. The default character for specifying runtime variables in SELECT statements is

• Ampersand
• Ellipses
• Quotation marks
• Asterisk

92 A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?

• select e.empid, d.head from emp e, dept d;
• select e.empid, d.head from emp e, dept d where e.dept# = d.dept#;
• select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);
• select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;

Always put the (+) on the deficient side, so since emp has more data that cannot be matched in dept table, dept table has less data than emp hence it is the deficient side!

93. Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?

• A table named ANIMALS will be created in the MASTER schema with the same data as the ANIMALS table owned by ANJU.
• A table named ANJU will be created in the ANIMALS schema with the same data as the ANIMALS table owned by MASTER.
• A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
• A table named MASTER will be created in the ANIMALS schema with the same data as the ANJU table owned by ANIMALS.

94. User JANKO would like to insert a row into the EMPLOYEE table, which has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?

• INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
• INSERT INTO employee VALUES (59694,’HARRIS’);
• INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,’HARRIS’);
• INSERT INTO employee (SELECT 59694 FROM ‘HARRIS’);

The only statement that does not contain error.

95. Which three of the following are valid database datatypes in Oracle? (Choose three.)

• CHAR
• VARCHAR2
• BOOLEAN
• NUMBER

Oracle does not support boolean. Boolean can be represented by 0 or 1 and therefore no need to create a datatype for it.

96. Omitting the WHERE clause from a DELETE statement has which of the following effects?

• The delete statement will fail because there are no records to delete.
• The delete statement will prompt the user to enter criteria for the deletion
• The delete statement will fail because of syntax error.
• The delete statement will remove all records from the table.

If select statement does not have a where clause, it would select all rows. Hence delete without where clause would delete all rows.

97. Creating a foreign-key constraint between columns of two tables defined with two different datatypes will produce an error.

• TRUE
• FALSE

98. Dropping a table has which of the following effects on a nonunique index created for the table?

• No effect.
• The index will be dropped.
• The index will be rendered invalid.
• The index will contain NULL values.

99. To increase the number of nullable columns for a table,

• Use the alter table statement.
• Ensure that all column values are NULL for all rows.
• First increase the size of adjacent column datatypes, then add the column.
• Add the column, populate the column, then add the NOT NULL constraint.

Badly phrased question, but to increase the number of any kind of column, you’d use alter table statement.

100. Which line of the following statement will produce an error?

• CREATE TABLE goods
• (good_no NUMBER,
• good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),
• CONSTRAINT pk_goods_01
• PRIMARY KEY (goodno));
• There are no errors in this statement.

Cannot have subquery in check.

101. MAXVALUE is a valid parameter for sequence creation.

• TRUE
• FALSE

102. Which of the following lines in the SELECT statement below contain an error?

• SELECT DECODE(empid, 58385, “INACTIVE”, “ACTIVE”) empid
• FROM emp
• WHERE SUBSTR(lastname,1,1) > TO_NUMBER(’S')
• AND empid > 02000
• ORDER BY empid DESC, lastname ASC;
• There are no errors in this statement.

Cannot convert a letter to a number.

103. Which function below can best be categorized as similar in function to an IF-THEN-ELSE statement?

• SQRT
• DECODE
• NEW_TIME
• ROWIDTOCHAR

104. Which two of the following orders are used in ORDER BY clauses? (choose two)

• ABS
• ASC
• DESC
• DISC

105. You query the database with this command

SELECT name
FROM employee
WHERE name LIKE ‘_a%’;

Which names are displayed?

• Names starting with “a”
• Names starting with “aR” or “A”
• Names containing “aR” as second character
• Names containing “aR” as any letter except the first

It still doesn’t make much sense as ‘aR’ cannot both be second character. the pattern would be to have lower case ‘a’ as second character, followed by anything and start with anything.