Must read Books on databases, DBA activities and Database development and design

Sybex
Oracle8I DBA Bible
Jonathan Gennick
John Wiley & Sons
The Complete Oracle DBA Training Course, Student Edition
Lynnwood Brown
Prentice Hall PTR
Oracle Database Administration: The Essential Reference
David Kreines
O Reilly & Associates
Oracle DBA Automation Scripts
Rajendra Gutta
SAMS
Oracle Enterprise Manager 101
Lars Bo Vanting
McGraw-Hill Osborne Media
Oracle RMAN Pocket Reference
Darl Kuhn
O Reilly & Associates
OCP: Oracle9i DBA Fundamentals II Study Guide
Doug Stuns
Sybex
Oracle DBA Tips and Techniques
Sumit Sarin
McGraw-Hill Osborne Media
DBA s Guide to Databases Under Linux
David Egan
Syngress
So You Want to Be an Oracle Dba: Still More Useful Information, Scripts and Suggestions for the New and Experienced Oracle Dba
Stephen C. Ashmore
Writers Club Press
Oracle Database 10g SQL
Jason Price
McGraw-Hill
Oracle Database 10g New Features for the DBA: Mike Ault s Oracle Handbook for New Tuning Tips & Techniques (Oracle In-Focus series)
Mike Ault
Rampant TechPress

Database/DBA
Expert Oracle9i Database Administration
Sam R. Alapati
APress
Oracle9i for Dummies
Carol McCullough-Dieter
For Dummies
Oracle9i: The Complete Reference
Kevin Loney
McGraw-Hill Osborne Media
Oracle9i DBA Handbook
Kevin Loney
McGraw-Hill Osborne Media
Perl for Oracle DBAs
Andy Duncan
O Reilly & Associates
Oracle8i: The Complete Reference (Book/CD-ROM Package)
Kevin Loney
McGraw-Hill Osborne Media
Oracle9i RAC: Oracle Real Application Clusters Configuration and Internals
Mike Ault
Rampant TechPress
Oracle Internals: Tips, Tricks, and Techniques for DBAs
Donald K. Burleson
Auerbach Pub
Oracle Essentials : Oracle9i, Oracle8i & Oracle8 (2nd Edition)
Rick Greenwald
O Reilly & Associates
Oracle9i Administration and Management
Michael R. Ault
John Wiley & Sons
Oracle9i RMAN Backup & Recovery
Robert G. Freeman
McGraw-Hill Osborne Media
Oracle8i DBA Handbook
Kevin Loney
McGraw-Hill Osborne Media
Oracle 9i New Features
Robert G. Freeman
McGraw-Hill Osborne Media
Oracle DBA Checklists Pocket Reference
Quest Software
O Reilly & Associates
Oracle9i Database Administrator: Implementation and Administration
Carol McCullough-Dieter
Course Technology
A Guide to Oracle9i
Joline Morrison
Course Technology
Oracle9i DBA 101
Marlene Theriault
McGraw-Hill Osborne Media
Practical Oracle 8i: Building Efficient Databases
Jonathan Lewis
Addison-Wesley Pub Co
Oracle9i: A Beginner s Guide
Michael Abbey
McGraw-Hill Osborne Media
Unix for Oracle DBAs Pocket Reference
Donald K. Burleson
O Reilly & Associates
Oracle8i for Dummies
Carol McCullough-Dieter
For Dummies
Oracle DBA 101
Marlene L. Theriault
McGraw-Hill Osborne Media
Oracle8i Backup & Recovery
Rama Velpuri
McGraw-Hill Osborne Media
Oracle8i Internal Services for Waits, Latches, Locks, and Memory
Steve Adams
O Reilly & Associates
Oracle 24x7 Tips and Techniques
Venkat S. Devraj
McGraw-Hill Osborne Media
Oracle DBA Backup and Recovery Quick Reference
Charlie Russel
Prentice Hall PTR
Oracle8i: A Beginner s Guide
Michael Abbey
McGraw-Hill Osborne Media
Oracle9i DBA JumpStart
Bob Bryla

DB Commander 2000 PRO: an all-in-one database utility

Today we have brought a must have database tool for database administrators and developers. DB Commander 2000 PRO is an all-in-one database utility. It is a great tool to help you efficiently deal with many types of databases.

DB Commander 2000 PRO your only solution for handling any two different types of databases simultaneously. This powerful database tool enables you to manipulate any two databases simultaneously.

You can use DB Commander with any database that is supported by the BDE or ODBC driver. BDE ( Borland Database Engine ) is required and included in the installation. You can experience the power of this great database tool on Oracle, MS SQL, Interbase, Informix, DB2, Sybase, Paradox, DBase, Access, FoxPro, MS Works and many more.

DB Commander 2000 PRO is an ideal tool for copying and transferring table structures and/or records across different database types. You can view/edit two tables of different database types at the same time. You can copy table structure to another database with indexes intact (if applicable), one or multiple tables at a time. Table structure can also be copied from a query result set.

DB Commander 2000 PRO enables you to transfer records across to another table, one or multiple tables at a time. DB Commander will transfer data even if the tables are not identical. This can also be done from a query result set. You can transfer selected records to another table and transfer records between tables by mapping their fields.

You can also delete one or multiple tables at a time or empty one or multiple tables at a time. You can rename tables, View/Create/Add/Delete Indexes or Add/Delete columns (fields) to existing tables and/or transfer the contents of a single column (field). You can locate all fields in database and quickly see what tables contain each field. You can also generate reports on any table or query.

DB Commander 2000 PRO allows you to check the number of tables between databases and compare the number of fields, records, indexes and key fields of their corresponding tables (in a report format as well). You can Hide/Show Fields and can filter records.

DB Commander 2000 PRO allows you to execute any query, using SQL statements or execute scripts, using the SQL editor. You can also create Insert Scripts from datasets.

You can use DB Commander 2000 PRO to Import/Export from/to text file. You can also preview the contents of any table or query in a report format. You can also export tables to FTP site (in text). You can also export datasets directly to *.CSV format and launch the associated application.

A noteworthy feature of DB Commander 2000 PRO is the command line driven launching of DB Commander for scheduled tasks that can transfer records, delete/empty tables, export to text or to FTP even from query result set.

There are many more exciting features of DB Commander 2000 PRO so do not delay and download the FREE 30-Day TRIAL version of DB Commander 2000 PRO now!

Powered Database Copying or Cloning

Powered Database Copying/Cloning

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

To find out how the secrets of super fast database cloning, see the full article here:
http://oracle-tips.c.topica.com/maakPM9abGeqxcfLOmyb/

EnterpriseDB Advanced Server: Get the Object relational flavour in database programming

EnterpriseDB Advanced Server: Get the Object relational flavour in database programming:

Develop Powerful Applications without Spending a Fortune on your Oracle Database
Download the Oracle Compatibility Developer’s Guide now and start building powerful new applications.

New applications developed on EnterpriseDB Advanced Server are compatible with Oracle. Instead of sinking your entire budget on an Oracle database, build your application on EnterpriseDB Advanced Server and experience the scalability, performance, availability and security that your mission critical infrastructure requires.

New applications developed on EnterpriseDB Advanced Server can leverage:

PL/SQL syntax support
Oracle Built-in packages
Oracle PL/SQL stored procedures
Oracle PL/SQL functions
Oracle PL/SQL triggers
Hierarchical Queries
Anonymous Blocks
Oracle data dictionary views
And, with support for the most popular programming languages and connectors, including interoperability with OCI, developing Oracle-compatible applications has never been easier.

You can Download the trial Oracle Compatibility Developer’s Guide now and start building powerful new applications.

Database Test Written Questions

Please spend 30 minutes on these questions
Database Test

A database contains two tables as shown:

Employee

employee_id
numeric(8,0)
dept_id
numeric(8,0)
first_name
char(100)
last_name
char(100)
salary
numeric(12,0)

1
NULL
John
Doe
10,000

2
1004
Scott
Adams
22,000

3
1004
Pointy
Hair
20,000

4
1002
Evil
Director
30,000

5
9999
Who
IsThisGuy
1,000,000

6
1003
Charlie
Chan
34,000


Department

dept_id
numeric(8,0)
dept_name
varchar(100)
region
char(4)

1000
London Sales
EUR

1001
London Ops
EUR

1002
HR
EUR

1003
Singapore Sales
ASIA

1004
US Manufacturing
US



1. Write SQL to find all employees that work in the US Manufacturing department.

Expected result:

Department
First Name
Last Name

US Manufacturing
Pointy
Hair

US Manufacturing
Scott
Adams








2. Write SQL to find a summary of the number of employees in each region. Employees that are not assigned to a department should be included with the label “NO REGION DEFINED”.

Expected result:

Region
total

ASIA
1

EUR
1

US
2

NO REGION DEFINED
2






3. Write SQL to find employees that are not associated with a department

Expected result:

Employee

John Doe

Who IsThisGuy









4. Write SQL to find the lowest paid employee in each region.

Expected result:

Region
Employee
salary

ASIA
Charlie Chan
34,000

EUR
Evil Director
30,000

US
Pointy Hair
20,000

NO REGION DEFINED
John Doe
10,000










5. Describe the T-SQL/ PL-SQL you could use to find the second highest paid employee.

Expected result:

Department
Region
Employee
salary

HR
EUR
Evil Director
30,000










6. What indexes and constraints would you expect to exist on these tables?
What is the difference between a clustered and non-clustered index?






7. Describe the T-SQL/ PL-SQL you would implement to perform row by row processing on a Sybase database table (provide basic syntax if possible).








8. Given a table TABLE1 with 200,000 rows or so, it is necessary delete each row that has the attribute ATTR1 = "Y". The number of rows that meet this criterion is approximately 150,000. However, the maximum number of rows that can be deleted in any one transaction is 100. Describe the T-SQL/ PL-SQL you would write to delete these rows, such that no more than 100 rows are deleted in any one transaction.










9. How would you delete duplicate rows from this table? A duplicate row is where another row exists with exactly the same values for col1, col2 and col3.
The table has over 100,000 rows.

How would your approach differ for the following scenarios:
Database can be offline to the users for a short time
Database must be kept online with time critical user retrieval queries needing to access the table during the update.

col1
col2
col3

A
B
C

A
B
C

A
C
B

A
C
B

A
X
Y

A
B
C




Additional Database Questions


1. SQL queries





Write a SQL query to answer each question below:


1. How many facilities are there?

2. Display name for the facility # 000141

3. List all account names for the facility # 000141

4. List all facility names that have 2 linked accounts

5. List all facilities that have 2 linked accounts: 123 and 124 only

6. Display all account names and type names for accounts with type id ‘LN’

7. For accounts linked to facilities update account type id to match facility type id




2. T-SQL/Stored Procedures

Write a simple procedure that expects one optional input parameter @Name.
The procedure should check whether a value was passed through parameter @Name.
If no value supplied (null is passed to procedure), it should display an error message and return, otherwise should display value of parameter @Name.






3. Subqueries

1) Which one of the following code examples represents a correlated subquery and which one not? How is correlated subquery processed by database server ?

A)

SELECT *
From authors
WHERE NOT EXISTS
(SELECT *
from titles
WHERE authors.id = titles.id)

B)

SELECT *
From authors
WHERE id in
(SELECT distinct id
from titles
WHERE subject = “business”)


2) Exists – Correlated Subquery

Given table Totals below, retrieve all IDs only if they have sales in each of the following two months: 7, 9

id month amt
==== ===== ====
100 7 $1000
100 8 $6000
100 9 $3000
115 7 $1000
115 10 $2000
120 7 $4000
120 9 $5000




4. Indexes

1) How many clustered indexes per table are allowed?

2) Which of the following table indexes is redundant:
(A, B, C, D, E are column names)

a) ABCD
b) ABCE
c) ABCDE
d) BCD








5. Transactions

When an error occurs inside a transaction should it be handled like in A) or B)

A)
select @error_no = @@error
If @error_no != 0
Begin
ROLLBACK tran
Raiserror 20300 “Operation failed”
Return @error_no
End

B)
select @error_no = @@error
If @error_no != 0
Begin
Raiserror 20300 “Operation failed”
ROLLBACK tran
Return @error_no
End



6. Locks (bonus questions)

1) What is the difference between
a) Shared Lock
b) Exclusive Lock
c) Intent Lock


2) For the following select statement
Select *
From tiles holdlock
Where type = ‘business’

a) What lock will be used ?
b) Page level lock or table lock?