Interview Questions: Oracle, PL SQL

• What are the different types of joins?
• What is the difference between procedure and function
• What is group by function
• What is the difference between a where clause and having clause
• What are indexes. Advantages of indexes
• Triggers, how many triggers are available
• What does “i” represent in Oracle8i / 9i
• Difference between primary key and foreign key
• Can we have null in a primary key
• Can we have null in a foreign key, if yes , how many in a table
• What are cursors
• Give example of how SQL tuning can be done
• What is normalization
• Difference between delete and truncate
• Are you aware of water level mark in oracle database? What is it?
• What are views, snapshots and synonyms?
• What value one gets for “Select * from dual”
• Have you used Decode function? Give an example
• Example when inner joins was used?
• Example when outer joins was used?
• Datatypes supported by Oracle
• Can you explain how do index retrieve records from database
• What is commit and rollback
• What is the difference between these 2 queries
• Select count(*) from table
•Select count(1) from table
• Difference between NODATAFOUND and %NOFOUND
• Difference between IN and EXISTS? Which is faster in execution?
• What is referential integrity
• What are constraints
• What are transaction isolation levels?
• What are materialized views?

Database : Oracle, SQL, PL / SQL Technical Interview Questions

1. What is DDL, DML ? How are they different?
2. What are different types of joins in SQL?
3. How do you select unique rows using SQL?
4. What is the difference between DELETE and TRUNCATE ?
5. What is the difference between a "where" clause and a "having" clause?
6. What is the difference between "procedure" and "function"?
7. What is the difference between "translate" and "replace" ?
8. How to remove duplicate records from a table?
9. What is a "trigger"?
10.What is the difference between "translate" and "replace"?
11.What is a VIEW?
12.What is the difference among "dropping a table", "truncating a table"
and "deleting all records" from a table
13.Explain new feature of 9i Database ? Explain new feature of 10g Database ?
14.How to use DECODE function?
15.What is “Group by” clause?
16.What are cursors and what are the situations you will use them?
17.What default packages are provided by Oracle?
18.How do you debug a oracle procedure /function?
19.How many triggers are available?
20.How are procedures executed?

SQL / PL SQL Questions

How do you convert a date to a string?
What is an aggregate function?
What is the dual table?
What are cursors? Distinguish between implicit and explict cursors?
Explain how cursors are used by Oracle?
What is PL/SQL? Describe the block structure of PL/SQL?
What is a nested subquery?
What are the various types of queries ?
Which of the following is not a schema object : Index, table, public synonym, trigger and package ?
What is dynamic sql in oracle?
What is the difference between a package, procedure and function
What is the difference between delete, drop and truncating a table
How many triggers are supported in Oracle
Are you aware of FLASHBACK concept ? What is it?
Describe oracle’s logical and physical structure?
What is data dictionary
What is the use of control files
How would store XML data in table ? What data type would be used for the columns?
Difference between post and commit?
Difference between commit and rollback?
What are savepoints?
Difference between a View and Synonym
How would you fetch system date from oracle
What is the difference between primary key, unique key, foreign key?
What is the difference between NO DATA FOUND and %NOTFOUND
What is cursor for loop
What are cursor attributes
What will you use in Query : IN or EXISTS? Why
Explain the difference between a data block, an extent and a segment.
What's the difference between logical and physical I/O?
What is an anonymous block?
What is a PL/SQL collection?
How can you tell if an UPDATE updated no rows?
How can you tell if a SELECT returned no rows?

Oracle DBA Questions and Answers

1. Explain database instance ?

A database instance (server) is a set of memory structures and background processes that access a set of database files.
The memory structures are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.
The process can be shared by all users.

2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).

3. What is Schema ?
The set of objects owned by user account is called the schema

4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.An index is automatically created when a unique or primary key constraint clause is specified in create table command

5. What is a clusters? Explain
Group of tables physically stored together because they share common columns and are often used together is called Clusters.

6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.

7. What are the basic element of an oracle Database ?
It consists ofone or more data filesone or more control filestwo or more redo log files
The database containsMultiple users/schemasone or more rollback segmentsone or more tablespacesData dictionary tables
User objects (tables,indexes,views etc)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA

8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.
In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

9. What is SGA ?
The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.

10. What is Shared SQL pool ?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

11. What is meant by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User process.

12. What is a data segment ?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

13. What are the factors causing the reparsing of SQL statements in SGA ?
Due to insufficient Shared SQL pool size

What is a database link? What is the difference between a public and a private database link? What is a fixed user database link?

A database link allows you to make a connection with a remote database, Oracle or not, and query tables from it, even incorporating those accesses with joins to local tables.

A private database link only works for, and is accessible to the user/schema that owns it. A global one can be accessed by any user in the database.

A fixed user link specifies that you will connect to the remote db as one and only one user that is defined in the link. Alternatively, a current user database link will connect as the current user you are logged in as.

As you prepare for your DBA Interview, or prepare to give one, we hope these questions provide some new ideas and directions for your study. Keep in mind that there are a lot of directions an interview can go. As a DBA emphasize what you know, even if it is not the direct answer to the question, and as an interviewee, allow the interview to go in creative directions. In the end, what is important is potential or aptitude, not specific memorized answers. So listen for problem solving ability, and thinking outside the box, and you will surely find or be the candidate for the job.

Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?

Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.

To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.

How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?

Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:

SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;

Describe the Oracle Wait Interface

The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.

What are three rules of thumb to create good passwords? How would a DBA enforce those rules in Oracle? What business challenges might you encounter?

Typical password cracking software uses a dictionary in the local language, as well as a list of proper names, and combinations thereof to attempt to guess unknown passwords. Since computers can churn through 10's of thousands of attempts quickly, this can be a very affective way to break into a database. A good password therefore should not be a dictionary word, it should not be a proper name, birthday, or other obvious guessable information. It should also be of sufficient length, such as eight to ten characters, including upper and lowercase, special characters, and even alternate characters if possible.

Oracle has a facility called password security profiles. When installed they can enforce complexity, and length rules as well as other password related security measures.

In the security arena, passwords can be made better, and it is a fairly solvable problem. However, what about in the real-world? Often the biggest challenge is in implementing a set of rules like this in the enterprise. There will likely be a lot of resistance to this, as it creates additional hassles for users of the system who may not be used to thinking about security seriously. Educating business folks about the real risks, by coming up with real stories of vulnerabilities and break-ins you've encountered on the job, or those discussed on the internet goes a long way towards emphasizing what is at stake

What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?

You can script the process to include it in a set of install scripts you deliver with a product.

You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.

You can log the output and review it for errors.

You learn more about the process of database creation, such as what options are available and why.

Why is a UNION ALL faster than a UNION?

The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.

What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.

Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes
Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete

Delete : (Data alone deleted), Doesn’t perform automatic commit

Difference between a "where" clause and a "having" clause.

Having clause is used only with group functions whereas Where is not used with.

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 cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing 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 rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, 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.

What are triggers? 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.

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.

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.

Different types of table join

There are 3 types of joins basis on Oracle 8i



Simple join

1. Equi join : Equi join is one which contains comparision operator '=' in where clause.

2. Non Equi join: If the comparision operator in the join condition is other than '=' then it is ....


Self join : Joining a table to itself can be done by..



Outer join : To retrieve rows which do not satisfy a query, the outer join concept can be used.

How do you handle exceptions and errors in stored procedures?

exceptions can be handled in various ways in the stored procedures. Method (1) could be using EXCEPTION block in the procedure within which use WHEN THEN. Method (2) could be declare a variable in DECLARE section as EXCEPTION type, raise it wherever required using RAISE and handle it in EXCEPTION part using WHEN clause. Method (3) could be using the keywords SQLCODE & SQLERRM to display error no and error code..

What rule optimizations are possible in SQL query

For optimization of a sql query, u first need to check the bottleneck and then taste the query with CBO/RBO. Using the option which is best for ur environment. For CBO to work , u need to analyze the table first then only it will have some effect on the performance of the query.

No PL/SQL Engine in SQL*Plus

No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine.

pass table as an argument to a remote procedure?

The only way the same object type can be referenced between two databases is via a database link.

-- Database A1: receives a PL/SQL table from database B1

CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS

BEGIN

-- do something with TabX from database B1

null;

END;

/


-- Database B1: sends a PL/SQL table to database A1

CREATE OR REPLACE PROCEDURE pcalling IS

TabX DBMS_SQL.VARCHAR2S@DBLINK2;

BEGIN

pcalled@DBLINK2(TabX);

END;

/

mutating and constraining table

A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.

one loop through tables in PL/SQL

Look at the following nested loop code example.

DECLARE

CURSOR dept_cur IS

SELECT deptno

FROM dept

ORDER BY deptno;

-- Employee cursor all employees for a dept number

CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS

SELECT ename

FROM emp

WHERE deptno = v_dept_no;

BEGIN

FOR dept_rec IN dept_cur LOOP

dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));

FOR emp_rec in emp_cur(dept_rec.deptno) LOOP

dbms_output.put_line('...Employee is '||emp_rec.ename);

END LOOP;

END LOOP;

END;

How does one get the value of a sequence into a PL/SQL variable?

As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:

i := sq_sequence.NEXTVAL;

However, one can use embedded SQL statements to obtain sequence values:

select sq_sequence.NEXTVAL into :i from dual;

use dynamic SQL statements from PL/SQL

"EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time).

EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';


-- Using bind variables...

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';

EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;


-- Returning a cursor...

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:

CREATE OR REPLACE PROCEDURE DYNSQL AS

cur integer;

rc integer;

BEGIN

cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);

rc := DBMS_SQL.EXECUTE(cur);

DBMS_SQL.CLOSE_CURSOR(cur);

END;

/

More complex DBMS_SQL example using bind variables:

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS

v_cursor integer;

v_dname char(20);

v_rows integer;

BEGIN

v_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);

DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);

v_rows := DBMS_SQL.EXECUTE(v_cursor);

loop

if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then

exit;

end if;

DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);

DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);

end loop;

DBMS_SQL.CLOSE_CURSOR(v_cursor);

EXCEPTION

when others then

DBMS_SQL.CLOSE_CURSOR(v_cursor);

raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);

END;

/

call DDL statements from PL/SQL

begin

EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';

end;

NOTE: The DDL statement in quotes should not be terminated with a semicolon.

read/write files from PL/SQL

DECLARE

fileHandler UTL_FILE.FILE_TYPE;

BEGIN

fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');

UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');

UTL_FILE.FCLOSE(fileHandler);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');

END;

Database Objective Question (61-70)

Question 61
Which of the following are reasons to choose SQL Server 2000? Choose all that apply.

A) Internet integration
B) Scalability and availability
C) Enterprise-Level Database features
D) Data warehousing

Question 62
You were recently hired to oversee the installation of SQL Server to support a new accounting
package for a small business. The company is cost-conscious, so you need to make a conservative
recommendation about where to install SQL Server in the company's existing structure. Which of
the following makes the best choice to support SQL Server?

A) Dual-processor machine with 512MB memory currently acting as an Exchange server
B) A single-processor 800MHz Pentium with 256MB memory currently acting as a Primary Domain Controller.
C) A spare Pentium 90MHz laptop with 128MB of memory
D) Pentium 1GHz machine with 512MB memory recently purchased to replace the president's workstation

Question 63
How is a NOT NULL column added to an existing table?

A) By using the ALTER TABLE statement.
B) Dropping and recreating the table.
C) By using ALTER TABLE to add a nullable column, and then using sp_changenull to change the column's NULL option.
D) By using the sp_addnullablecolumn system stored procedure.

Question 64
Omitting the WHERE clause of an UPDATE or DELETE statement has what effect?

A) All rows in the specified table are affected.
B) The query returns an error because the WHERE clause is required.
C) Only the current row is affected.
D) Only the rows with the correct data type are affected.

Question 65
What statement could you use in the WHERE clause to select all the rows in a table where no
price is defined?

A) WHERE price IS NOT NULL
B) WHERE price IS < 0
C) WHERE price != 0
D) WHERE price IS NULL

Question 66
Consider the following table and trigger definitions: Table1: UserID int IDENTITY FirstName char(50)
LastName char(50) DepartmentID int BillingID int CREATE TRIGGER Table1_InsertUpdate ON Table1
FOR INSERT, UPDATE AS BEGIN IF UPDATE(DepartmentID) IF (Select count(*) from Table1 inner join
Deleted on Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END Assuming that this
is the only trigger bound to the table, is it possible for the last member of a department to
be deleted?

A) No; the trigger prevents that from happening.
B) Yes; the trigger has not been activated yet.
C) Yes, if a new record is inserted on top of the existing one.
D) Yes, by using the DELETE command.

Question 67
Referential integrity refers to what?

A) The enforced uniqueness of a row in a table
B) The enforced uniqueness of a column in a table
C) Ensuring that a Foreign Key attribute cannot be NULL
D) The enforced synchronization of Primary Key and Foreign Key values

Question 68
Barney wrote this stored procedure to manage inserts to his Sales table:
CREATE PROCEDURE InsertSale @SalesPersonID int, @ProductID int, @Quantity int, @CustomerID int
AS If @Quantity > 0 Insert Sales (SalesPersonID, ProductID, Quantity, CustomerID) VALUES
(@SalesPersonID, @ProductID, @Quantity, @CustomerID) else RAISERROR
('Unable to enter negative or 0 quantity into Sales table.', 16, 1) GO What does the
RAISERROR statement do?

A) The RAISERROR will cause the row to not be inserted into the database.
B) It provides feedback to the calling application so it can diagnose why the insert failed.
C) It will cause SQL Server to shut down because a severity 1 error occurred.
D) It will do nothing.

Question 69
SQL Sever maintains what types of information about a column in the table definition?

A) Name, NULL option, and constraints
B) Name, data type, and NULL option
C) Name, data type, NULL option, and bytes of overhead.
D) Name, data type, NULL option, and constraint information

Question 70
DTS Package Designer tasks can be set to run in what ways? Choose all that apply.

A) On completion
B) On failure
C) On error
D) On success

Database Objective Question (51-60)

Question 51
What is the lifetime of a user-defined variable?

A) Duration of the script
B) Duration of the statement block
C) Duration of the batch
D) Duration of the current SQL Server connection

Question 52
To use full-text searching and find all rows containing forms of the verb "swim,"
you should use which CONTAINS predicate?

A) CONTAINS(columname, FORMSOF(INFLECTIONAL, 'Swim'))
B) CONTAINS(columnname, 'swim', 'swims')
C) CONTAINS(columnname, FINDALL("Swims"))
D) CONTAINS(columnname, 'swim*')

Question 53
Which of the following will help reduce deadlocks in your database?

A) Keeping the transaction isolation level as high as possible without hurting user concurrency
B) Always accessing tables in the same order in all programs
C) Never completing transactions until a user can confirm that the changes are correct
D) Always checking the state of @@TRANCOUNT before issuing a ROLLBACK command

Question 54
Sarah needs to figure out how to replicate data from all of her warehouses back to the
corporate office. She wants to store all the inventory information in one table, and all of
the information needs to be accessible to all of the warehouses and the corporate office, and
the information should be updateable at any location. The warehouses do not have reliable
network connections back to the corporate office. Which type of replication should Sarah use?

A) Snapshot replication
B) Merge replication
C) Transactional replication
D) Snapshot replication with Updating Subscribers

Question 55
When creating a database with the CREATE DATABASE command, in what units can the file size be
specified? Choose all that apply.

A) MB (Megabytes)
B) Number of pages
C) Number of extents
D) KB (Kilobytes)

Question 56
Eddie needs to have a database that is replicated to other locations. They have highly
redundant network connectivity to all of the other locations, and a very large central server.
Changes to the database need to be made at every site, and need to be done instantaneously at
every site, with no delay. Which type of replication would work best for Eddie?

A) Merge replication
B) Transactional replication
C) Snapshot replication
D) Snapshot replication with updates

Question 57
Which of the following are aggregate functions? Choose 3.

A) Sum
B) ABS
C) Max
D) Count

Question 58
Barney wrote this stored procedure to manage inserts to his Sales table: CREATE PROCEDURE
InsertSale @SalesPersonID int, @ProductID int, @Quantity int, @CustomerID int AS
If @Quantity > 0 Insert Sales (SalesPersonID, ProductID, Quantity, CustomerID) VALUES
(@SalesPersonID, @ProductID, @Quantity, @CustomerID) else RAISERROR
('Unable to enter negative or 0 quantity into Sales table.', 16, 1) GO
If there are no constraints on the table, how can Barney make sure that the value of
@SalesPersonID is valid?

A) Check the Sales table and see whether there are any other rows in that table for that SalesPersonID.
B) Check the table in which SalesPersonID is a Primary Key to ensure that the value is valid.
C) Attempt the insert and check to see whether the foreign key constraint causes an error.
D) It's not possible to access more than one table in a stored procedure.

Question 59
Which queries will return all the names in the Authors table sorted by first name, then last? Choose 2.

A) SELECT au_fname, au_lname FROM Authors SORT BY 1, 2
B) SELECT au_fname, au_lname FROM Authors ORDER BY au_fname, au_lname
C) SELECT au_fname, au_lname FROM Authors SORT BY au_fname, au_lname
D) SELECT au_fname, au_lname FROM Authors ORDER BY 1, 2

Question 60
When using an INSERT statement, data to be inserted may come from which of the following?
Select all that apply.

A) The VALUES clause
B) A stored procedure
C) Another database
D) A SELECT statement

Database Objective Question (41-50)

Question 41
Jesse's company has a central office and three remote locations. Each remote location has
three to five employees that run an order-taking application; they don't generate any reports
or need any additional data beyond their order-taking application. Each site has a modest but
reliable network connection to the central office. Which of the following is the best way for
Jesse to provide these users with access to the database?

A) Set up snapshot replication to each remote site.
B) Use transactional replication to replicate data to the remote site.
C) Use merge replication to replicate data to the remote site.
D) Don't use replication; just connect the users directly to the remote database.

Question 42
Eric needs to build a stored procedure to count the number of snack food vendors within his
zip code. Which of the following strategies would be the easiest to implement for passing and
returning values?

A) Pass the zip code in as a parameter; pass the number of vendors out as a return value.
B) Pass the zip code in as a parameter; pass the number of vendors out as an output parameter.
C) Use the stored procedure to call a scalar-valued user-defined function.
D) Pass the zip code in as a parameter; pass the output back in a temporary table.

Question 43
Carol wants to distribute the Personnel table, but needs to leave out a couple of columns
from the replication, such as the AnnualSalary column. What's the best way to do this?

A) Implement horizontal partitioning.
B) Use SQL Server security to keep people out of that column.
C) Use vertical partitioning to turn off replication for that article.
D) Use union partitioning.

Question 44
Which of the following are good uses for the I-SQL utility? Choose 2.

A) Execution of Data Definition Language statements
B) Monitor queries issued by one or more users
C) Examine a graphical query plan
D) Schedule execution of a T-SQL script

Question 45
What role do CHECK constraints play in partitioned views?

A) CHECK constraints are required to ensure that an inserted row goes to the correct table.
B) CHECK constraints are not required but can increase performance for inserts to the partitioned view.
C) CHECK constraints serve no purpose for partitioned views.
D) CHECK constraints could conflict with the definition of the partitioned view and therefore
cannot exist on the tables that make up a partitioned view.

Question 46
Which of the following options will create a composite unique index on the CustomerID and ProjectID fields in the CP table?

A) create unique composite index CPidx on CP(CustomerID, ProjectID)
B) create unique index CPidx on CP.CustomerID, CP.ProjectID
C) create unique index CPidx on CP(CustomerID, ProjectID)
D) create clustered index CPidx on CP(CustomerID, ProjectID)


Question 47
Sam needs to set up her stored procedure so that when invalid data values are passed into the
stored procedure, the stored procedure will cause the application to see an error and have
SQL Server log an event into the error log. Which of the following will allow this to happen?

A) ON ERROR RESUME NEXT
B) RAISERROR 'Invalid data', 50000, 1, LOG
C) RAISERROR ('Invalid Data', 50000, 1) WITH LOG
D) RAISERROR('Invalid Data', 50000, 1)

Question 48
Roger wants to create an index on a read-only table. What is the best option
he should use when creating the index?

A) FILLFACTOR = 100
B) FILLFACTOR = 0
C) PAD_INDEX, FILLFACTOR = 100
D) PAD_INDEX, FILLFACTOR = 0

Question 49
When using BCP to transfer data out of a table, which of the following statements apply? Choose 3.

A) Database users will not be able to access the table because BCP will lock it.
B) You must have SELECT permissions on the table.
C) Data changes made by other users during the BCP will not be reflected in the data file.
D) The contents of the data file are replaced if the data file already exists.

Question 50
Insensitive cursors provider what functionality?

A) They enable users to see data inserted since the time the cursor was opened.
B) They allow faster updates because the data for the cursor is stored in tempdb.
C) They cannot be scrolled backward because the data is discarded from the temporary storage after it is fetched.
D) They ensure an unchanging view of the data as it was when the cursor was opened.

Database Objective Question (31-40)

Question 31
Which of the following types of columns would be good candidates for an index? Choose all that apply.

A) Columns that make up the Primary Key
B) Columns that contain Foreign Keys
C) Columns that contain data such as gender for a large population.
D) Columns that contain data such as a tax ID or address for a large population

Question 32
What is the maximum number of rows that a table can store in SQL Server 2000?

A) 255
B) 1,048,516
C) 2,147,483,647
D) No limit besides physical storage size

Question 33
An UPDATE trigger typically accesses which of the following?

A) The updated table
B) The inserted table only
C) The INSERTED and DELETED tables
D) The DELETED table only

Question 34
Which of the following commands can best assist in diagnosing locking problems?

A) sp_lock
B) sp_who
C) DBCC SHOWLOCKS
D) SET SHOWLOCKS
Question 35
Which of the following commands can be used to interrupt processing of a While loop?

A) BREAK
B) HALT
C) EXIT
D) CONTINUE

Question 36
Choose all the statements that you could use in the WHERE clause to find only the rows
where the first name is Bobby or Bobbi. Choose all that apply.

A) WHERE name = ‘Bobby’ or name = ‘Bobbi’
B) WHERE name LIKE ‘Bobb_’
C) WHERE name LIKE ‘Bobb%’
D) WHERE name LIKE ‘Bobb[iy]’

Question 37
What are the space requirements for views?

A) Views require as much space as all the tables they are created from.
B) Views require as much space as would a table that presented the same data.
C) Views do not store data and therefore use no space.
D) Views require the space used to store a few records in various system tables.

Question 38
You have written a stored procedure that takes several parameters and uses them to issue
appropriate queries to the database. Most of the parameters are optional, and if all are passed
the resulting query you have to run can be quite complex. There is a great deal of conditional
logic in the procedure based on the parameters passed. When testing this procedure you found
that when you first created it, it worked great even though you started with a rather complicated
set of parameters. Since then performance has been disappointing. Every time you re-create it,
it works fast once, but rarely shows such speed on later calls. What is the most likely cause
of the problem?

A) You have specified the With Encryption option, which is causing additional overhead because the procedure has to be decrypted each time it is run.
B) You have specified the With Recompile option, which is causing additional overhead because the procedure has to recompile a new query plan every time.
C) You have forgotten to specify the With Recompile option, which is causing an inappropriate query plan to be used with subsequent runs.
D) Your stored procedure cannot be helped. Stored procedures weren’t intended to handle complex conditional logic.

Question 39
How does a TRUNCATE TABLE statement differ from an unrestricted DELETE? Select all that apply.

A) A TRUNCATE TABLE cannot be rolled back, even if it occurs within a transaction.
B) An unrestricted DELETE resets an Identity column to its original seed, whereas the TRUNCATE TABLE does not.
C) If the table is referenced by a FOREIGN KEY constraint, only the DELETE may be used.
D) The TRUNCATE TABLE causes a DELETE trigger to be fired, whereas the DELETE does not.

Question 40
What is a valid GROUP BY clause for the following SELECT list? SELECT Pub_ID, Type, Max(Price)

A) GROUP BY Pub_ID, Type, Max(Price)
B) GROUP BY Pub_ID, Type, Price
C) GROUP BY Pub_ID, Type
D) GROUP BY max(Price)

Database Objective Question (21-30)

Question 21
Which of the following are results of specifying schema binding on a view?
Choose all that apply.

A) The view created with schema binding cannot be dropped.
B) The CREATE statement for the view must contain a two-part name for all objects referenced.
C) When created with schema binding, the CREATE statement is stored in an encrypted manner and cannot be scripted out.
D) Objects referenced by the view cannot be dropped or changed while the view is schema bound.

Question 22
George needs to write a view that can return one or more customers from a table given a last
name or part of a last name. Which of the following strategies will allow him to do that?

A) Write a view for every single last name that can be entered.
B) Write an application that automatically creates a new view for each last name in the database.
C) Pass a lastname parameter into the view.
D) Write a SELECT statement that references the view. Use the WHERE clause in the SELECT statement to filter the data.

Question 23
Which of the following are parts of a SELECT statement? Choose all that apply.

A) FROM
B) SORT BY
C) WHERE
D) SELECT

Question 24
Which of the following features are new to SQL Server 2000? Choose all that apply.

A) Full Text Searching
B) Indexed views
C) Cascading Referential Integrity Constraints
D) Remote administration of servers

Question 25
When is it important to use the WITH RECOMPILE option when creating a stored procedure?

A) Always use WITH RECOMPILE.
B) On servers that aren't busy, WITH RECOMPILE should be used all the time.
C) Only if the query plans used by the stored procedure change frequently.
D) When any tables that are going to be used by the stored procedure are routinely dropped and re-created.

Question 26
Given the following partial function definition, which SET statement shown below would correctly accept the returned value? create function Area (l1 int, l2 int) returns int

A) set @A = dbo.area(5, 4)
B) set @A = area(5,4)
C) exec area(5, 4, @A OUTPUT)
D) set @A = dbo.@Area(5, 4)

Question 27
Consider the following table and trigger definitions:
Table1: UserID int IDENTITY FirstName char(50) LastName char(50) DepartmentID int BillingID int
CREATE TRIGGER Table1_InsertUpdate ON Table1 FOR INSERT, UPDATE AS BEGIN IF
UPDATE(DepartmentID) IF (Select count(*) from Table1 inner join Deleted on
Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END
What does the RAISERROR statement in the trigger do?

A) It provides the connection with an error message.
B) It provides the connection and the SQL Server error log with an error message.
C) It provides the connection, the SQL Server error log file, and the Windows NT application event log with an error message.
D) It causes a server-wise severity one message, stopping SQL Server.


Question 28
Diane needs to enforce referential integrity on one of her tables. The data in one of the
columns should contain key values from a table residing in a database on the corporate server,
as opposed to her departmental server where her application database resides. She decides to
use constraints to enforce data integrity. How would you rate this solution?

A) This is an optimal solution. Using constraints to validate data entry is the best way to verify referential integrity.
B) This is a fair solution. Although using constraints will work, it would be better to use triggers to accomplish this goal.
C) This is a poor solution. It won't perform well, but it will work.
D) This solution won't work.

Question 29
Choose the statement that best describes a composite index.

A) It physically sorts the rows in a table.
B) It contains multiple keys.
C) It logically sorts the rows in a table in both ascending and descending order.
D) It contains multiple columns in its key.

Question 30
You need to install SQL Server Developer Edition on a spare machine to deliver to a new developer in your company so he can study for his certification exam. Of the following machines that can be spared,
what is the least powerful machine you can use for this purpose?

A) Pentium 266MHz, 32MB memory
B) Pentium 266MHz, 64MB memory
C) Pentium 166MHz, 64MB memory
D) Pentium 90MHz, 32MB Memory

Database Objective Question (11-20)

Question 11
You are finding unusually high memory utilization on a SQL Server that is primarily used to
process XML data. The server will periodically freeze up and refuse to process any more XML
statements. The problem can only be fixed by restarting the SQL Server service, at which point
it work fine for a while, but the problem always returns. What is the most likely cause for
the problem?

A) Your developers are probably not deallocating their cursors after processing the XML data.
B) The system is routinely encountering malformed XML and the accumulated errors are crashing the system.
C) The XML strings are not being removed after being prepared and used.
D) A hardware problem is the root of your trouble.

Question 12
What are two advantages of triggers over rules and constraints? Choose two.

A) The ability to define boundaries within which the data must reside.
B) The ability to use looping structures.
C) The ability to reference data in other databases.
D) The ability to check data before it is logged.

Question 13
Which of the following is a good reason to use transactional replication?

A) It provides an up-to-the-second backup in case one of the sites in the replication model goes down.
B) It provides a method for using a lot of resources on several servers in order to provide
up-to-the-second reporting capabilities.
C) It provides the lowest-overhead replication of any other type.
D) It is veryQuestion tolerant of network outages.

Question 14
When creating a table that contains a birth date for a person,
you want to ensure that a valid date is always entered. Which of the following is the best way to accomplish this?

A) Adding a CHECK constraint to the column
B) Adding a trigger to the table that will validate the column
C) Using a DateTime data type for the column.
D) Using a NOT NULL constraint on the column

Question 15
Which of the following EXEC statements are valid? Choose all that apply.

A) exec ReturnValue = MyProc 5, 4, 'hello', @A OUTPUT
B) exec @ReturnValue = MyProc @length = 5, 4, 'Hello', @A OUTPUT
C) exec @ReturnValue = MyProc 5, 4, 'Hello', @A OUTPUT
D) exec @ReturnValue = MyProc @length = 5, @width = 4, @message = 'Hello', @A = @A OUTPUT

Question 16
What tool included with SQL Server is primarily used for writing and executing ad-hoc queries?

A) SQL Server Enterprise Manager
B) SQL Server Profiler
C) SQL Server Query Analyzer
D) SQL Server Index Wizard

Question 17
What are the three join operations SQL Server supports? Choose all that apply.

A) Nested loops
B) Merge join
C) Index spool
D) Hash match

Question 18
The graphical execution plan presented by SQL Server Query Analyzer uses different icons to
represent which of the following?

A) Physical operators
B) Logical Operators
C) T-SQL commands
D) Process flow

Question 19
Phil has created a new database that takes advantage of multiple physical disks on his server.
He has created his primary data file on one disk and placed a secondary data file on a separate
physical disk. The transaction log exists on the same physical disk as the primary file.
Phil’s plan is to put all user objects on the secondary data file and leave the system tables
on the primary data file. After creating all his objects, however, he quickly discovers that
they ended up on the primary file group. What might he have done wrong?

A) All objects are created on the primary data file until there is no more room.
Only after the primary data file is full will objects be created on the secondary data file.
B) He probably forgot to create the secondary data file as part of a separate file group.
Objects are created on file groups rather than on data files.
C) He probably forgot to declare the secondary data file as the default data file.
Objects are created on the default data file unless another data file is specified.
D) Nothing is actually wrong. SQL Server automatically assigns the secondary data file as the
primary file group so that all user defined objects will be created separately from the
primary data file.

Question 20
Which of the following best describes a situation in which the statistics for a table would
be misleading?

A) Random rows in the table have been deleted.
B) Deleting all of the records of a specific gender.
C) Dropping Foreign Key constraints on the table
D) Dropping indexes on a table

Database Objective Question (1-10)

Question 1
What are the maximum number of tables that can be joined together in a query?

A) 16
B) 32
C) 256
D) 1024

Question 2
When using BCP to copy data into a table, which of the following statements apply? Choose 2.

A) Database users will not be able to access the table because BCP will lock it.
B) Database users will see the rows inserted by BCP after each batch is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.

Question 3
An UPDATE statement may modify how many tables?

A) One or more tables, provided the UPDATE statement is not operating on a view.
B) Only one, though triggers may cause additional side-effects.
C) Only one.
D) Up to sixteen

Question 4
Data and log files expand by what increment?

A) 8KB
B) 64KB
C) 512KB
D) 1MB

Question 5
Jamie is having a problem tracking down an issue users are having with a stored procedure.
Users will occasionally have problems with the stored procedure running for an excessive
amount of time, perhaps hours on end. There are no messages in the SQL Server error log that
correspond to the times that users are having problems. Which of the following is the most
likely cause and resolution?

A) The server is crashing and the users aren't able to access their data. Replace the hardware.
B) The server is processing a database backup, and which is causing an excessive
amount of locking. Stop the automated backup jobs.
C) There is a problem with one or more user connections causing lock contention.
Use sp_who and DBCC INPUTBUFFER to track down the likely culprit.
D) There is a problem with one or more user connections causing lock contention.
Use sp_lock to track down the

Question 6
Which of the following commands are parts of the Data Definition Language (DDL)?

A) INSERT
B) CREATE TABLE
C) UPDATE
D) CREATE VIEW

Question 7
Consider the following table and trigger definitions: Table1: UserID int IDENTITY FirstName
char(50) LastName char(50) DepartmentID int BillingID int CREATE TRIGGER Table1_InsertUpdate ON
Table1 FOR INSERT, UPDATE AS BEGIN IF UPDATE(DepartmentID) IF (Select count(*) from Table1
inner join Deleted on Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END What does the
ROLLBACK statement in the trigger do?

A) It causes the transaction to roll back, aborting the INSERT or UPDATE that cause the trigger to run,
but not aborting the transaction that the offending statement was part of.
B) It causes the transaction containing the offending statement to roll back.
C) Nothing; the keyword EXIT should have been used to abort the transaction.
D) It causes the transaction log to fill up.

Question 8
In order for her new application to process a sales record, Kim has to make sure that the
application has collected enough information. For sales over $500, she needs to have a valid
account manager's ID number. For sales over $1000, she needs to have a valid zip code for
delivery to determine who gets commission. For sales over $10,000, she needs to make sure that
enough stock is available. Which of the following would be a good way to check the data prior
to inserting it?

A) Table constraints
B) Triggers
C) An inline table-valued function
D) A multi-statement table-valued function

Question 9
What is the maximum number of rows that a table can store in SQL Server 2000?

A) 255
B) 1,048,516
C) 2,147,483,647
D) No limit besides physical storage size

Question 10
If table_a has an insert trigger that performs an update on table_b, in which case will the
update trigger on table_b be fired?

A) If the 'nested triggers' database option has a value of '1'.
B) If the 'nested triggers' server option is set to '1'.
C) If the 'nestable' option is set for the trigger on table_a.
D) If the 'nestable' option is set for the trigger on table_b.

Database Interview Question

1. What is Rule ?

2. What is difference between function and Stored Procedure?

3. What is difference between Rule and Constraint?
a. Rule created as separate object, constraint created as part of table
b. We can apply only one rule per column, where we can apply mulitple constraints
to columns

4. What is difference between Constraint and Trigger?

5. How many output parameters we can give to stored procedure?

6. How to access ther results of one sp in another sp?

7. What / which areas u will check in a long running SP?

8. When to use Cluster and Non cluster Index ?

If you have to sort by a particular column often, consider making that column a clustered index.

9. what are new datatypes in sql 2000?
BigInt, sql_variant, Table

10. what is global Temprorary variable / Table ?

11. What is Index how many types are they?

12. what are the new features in sql 2000?

13. What is Instead of Trigger ?

1. The triggers whcih can define on Tables / Views to replace the standard action of Insert / Delete / Update.
2. With help of Instead of Triggers we can update / Delete / Insert to Views which are constructed by multiple base tables.
3. INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed.

14. How to handel errors in SP?

15. what are materialized views?
The process of building the view results is called materializing the view

16. What is difference between Delete and Truncate
Delete is logged operation, Truncate is not logged operation and set Identity to starting point.

17. What is Default
Action taken on ceratin events when user didnt specify any action to take.

18. What is Correlated subQuery (repeating subquery) ?
In this the innerquery executes repeatedly, once for each possible outer query result