Saturday, March 08, 2008

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?

1 comment:

Anonymous said...

Where are the answers?