Database interview questions and answers asked in various interviews, faqs,tutorial and articles..
The following methods can be used to select a random collection of rows from a table:
The SAMPLE Clause
From Oracle 8i, the easiest way to randomly select rows from a table is to use the SAMPLE clause with a SELECT statement. Examples:
SELECT * FROM emp SAMPLE(10);
In the above example, Oracle is instructed to randomly return 10% of the rows in the table.
SELECT * FROM emp SAMPLE(5) BLOCKS;
This example will sample 5% of all formatted database blocks instead of rows.
This clause only works for single table queries on local tables. If you include the SAMPLE clause within a multi-table or remote query, you will get a parse error or "ORA-30561: SAMPLE option not allowed in statement with multiple table references". One way around this is to create an inline view on the driving table of the query with the SAMPLE clause. Example:
SELECT t1.dept, t2.emp
FROM (SELECT * FROM dept SAMPLE(5)) t1,
emp t2
WHERE t1.dep_id = t2.dep_id;
If you examine the execution plan of a "Sample Table Scan", you should see a step like this:
TABLE ACCESS (SAMPLE) OF 'EMP' (TABLE)
ORDER BY dbms_random.value()
This method orders the data by a random column number. Example:
SQL> SELECT * FROM (SELECT ename
2 FROM emp
3 ORDER BY dbms_random.value())
4 WHERE rownum <= 3;
ENAME
----------
WARD
MILLER
TURNER
The ORA_HASH() function
The following example retrieves a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then returning the data from bucket 1:
SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1;
Escape quotes
Use two quotes for every one displayed. Examples:
SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
TEXT
--------------------
Franks's Oracle site
SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
TEXT
----------------
A 'quoted' word.
SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
TEXT
-------------------------
A ''double quoted'' word.
Escape wildcard characters
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:
SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';
SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';
Escape ampersand (&) characters in SQL*Plus
When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:
SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;
Other methods:
Define an escape character:
SET ESCAPE '\'
SELECT '\&abc' FROM dual;
Don't scan for substitution variables:
SET SCAN OFF
SELECT '&ABC' x FROM dual;
Use the 10g Quoting mechanism:
Syntax
q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
Make sure that the QUOTE_CHAR doesnt exist in the text.
SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;
DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:
DCL - Data Control Language. Some examples:
TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit
Application developers design and implement database applications. Their responsibilities include the following tasks:
Designing and developing the database application
Designing the database structure for an application
Estimating storage requirements for an application
Specifying modifications of the database structure for an application
Relaying this information to a database administrator
Tuning the application during development
Establishing security measures for an application during development
In some cases, a site assigns one or more security officers to a database. A security officer enrolls users, controls and monitors user access to the database, and maintains system security. As a DBA, you might not be responsible for these duties if your site has a separate security officer.
Installing and upgrading the Oracle Database server and application tools
Allocating system storage and planning future storage requirements for the database system
Creating primary database storage structures (tablespaces) after application developers have designed an application
Creating primary objects (tables, views, indexes) once application developers have designed an application
Modifying the database structure, as necessary, from information given by application developers
Enrolling users and maintaining system security
Ensuring compliance with Oracle license agreements
Controlling and monitoring user access to the database
Monitoring and optimizing the performance of the database
Planning for backup and recovery of database information
Maintaining archived data on tape
Backing up and restoring the database
Contacting Oracle for technical support
You can use the DBCC function or Truncate the table.
DBCC CHECKIDENT ( <table name>,RESEED,<new value>)
Truncate Table <table name>
Answer 2:Here is a simple sample for the question:
--Drop the foreign key
ALTER TABLE dbo.CategoryTable1Sub
DROP CONSTRAINT FK_CategoryID
GO
truncate table dbo.CategoryTable1
truncate table dbo.CategoryTable1Sub
GO
--Add Foreign key back
ALTER TABLE dbo.CategoryTable1Sub ADD CONSTRAINT
FK_CategoryID FOREIGN KEY
( CatID )REFERENCES dbo.CategoryTable1
( Category_ID )
GO
Answer 3:
f you are not able to TRUNCATE, and the issues of removing the CONSTRAINTS are burdensome, as Ken indicated, you may wish to try using the DBCC RESEED functionality.
Something like this:
DBCC CHECKIDENT ("YourTable", RESEED, 1);.
BEGIN-TRANSACTION Name
Code
END TRANSACTION Name