Selecting Nth MAX or MIN

This query has become the mostly asked question in any Oracle technical Interview.

 Following is an excerpt tip which comes from Ramcharan Karthic, software engineer in Bangalore, India.

Note: This tip was published in the January/February 2003 issue of Oracle Magazine.

This Select query will help you select the Nth Max or Min value from any table.

For example, consider a table TAB1 in which you want to find the Nth Max or Min from the column, COL1.

First, the query for Max:

SELECT * FROM TAB1 a

WHERE &N = (SELECT count (DISTINCT (b.col1))

FROM TAB1 b WHERE a.col1<=b.col1)

Next, the query for Min:

SELECT * FROM TAB1 a

WHERE &N = (SELECT count (DISTINCT (b.col1))

FROM TAB1 b WHERE a.col1>=b.col1)

If N=1 will return first max or first min. N=2 will return second max or min.

How to create view without underlying table

This tip will enable to create a view even if you do not have an underlying table already present in your database.

In this article you will learn to                            

• Create view without a table
• Creating a table for that view
• How to make the view to work

Consider the following example:-

CREATE OR REPLACE FORCE VIEW force_view AS SELECT * FROM force_table;

Now check whether the view is created or not:

SELECT object_name, object_type, status, temporary, generated, secondary FROMuser_objects WHERE object_name='FORCE_VIEW';

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

----------- ----------- --------   ---------   ---------  ---------

FORCE_VIEW  VIEW        INVALID     N           N           N

So this ensures that the view is created, but what about the status? It says the view created is invalid.

Now we will try to execute the view:

SELECT * FROM force_view;

Error starting at line 1 in command:

select * from force_view

Error at Command Line:1 Column:14

Error report:

SQL Error: ORA-04063: view "RND.FORCE_VIEW" has errors

04063. 00000 -  "%s has errors"

*Cause:    Attempt to execute a stored procedure or use a view that has

           errors.  For stored procedures, the problem could be syntax errors

           or references to other, non-existent procedures.  For views,

           the problem could be a reference in the view's defining query to

           a non-existent table.

           Can also be a table which has references to non-existent or

           inaccessible types.

*Action:   Fix the errors and/or create referenced objects as necessary

This is the error which I get when running the view. So now you will understand that we will be just able to create the view, but it is useless until it has an underlying table attached to it.

Now we will create table for this view:

CREATE TABLE force_table (a NUMBER, b VARCHAR2(10));

Now will check again the status for the view.

SELECT object_name, object_type, status, temporary, generated, secondary FROMuser_objects WHERE object_name='FORCE_VIEW';

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

----------- ----------- --------   ---------   ---------  ---------

FORCE_VIEW  VIEW        INVALID     N           N           N

The status is still INVALID. Now we need to re-compile the view to make it VALID.

ALTER VIEW force_view COMPILE;

OBJECT_NAME OBJECT_TYPE STATUS      TEMPORARY   GENERATED   SECONDARY

----------- ----------- --------   ---------   ---------  ---------

FORCE_VIEW  VIEW        VALID       N           N           N

How to get the last password changed time for a oracle user

Question: How to get the last password changed time for a oracle user?

Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user.

Try this query:      

SELECT name,
       ctime,
       ptime
FROM   sys.user$
WHERE  name = 'USER-NAME';



Note: Replace USER-NAME with the user name which you want to know the information.


CTIME Indicates - Creation Time
PTIME Indicates - Password Change Time

Here's the table DESCription:
Name         Type                Nullable Default Comments 
------------ ------------------- -------- ------- -------- 
USER#        NUMBER                                        
NAME         VARCHAR2(30 BYTE)                             
TYPE#        NUMBER                                        
PASSWORD     VARCHAR2(30 BYTE)   Y                         
DATATS#      NUMBER                                        
TEMPTS#      NUMBER                                        
CTIME             DATE                                          
PTIME             DATE                Y                         
EXPTIME       DATE                Y                         
LTIME           DATE                Y                         
RESOURCE$    NUMBER                                        
AUDIT$          VARCHAR2(38 BYTE)   Y                         
DEFROLE        NUMBER                                        
DEFGRP#      NUMBER              Y                         
DEFGRP_SEQ#  NUMBER              Y                         
ASTATUS           NUMBER                       0                
LCOUNT             NUMBER                       0                
DEFSCHCLASS     VARCHAR2(30 BYTE)   Y                         
EXT_USERNAME   VARCHAR2(4000 BYTE) Y                         
SPARE1             NUMBER              Y                         
SPARE2             NUMBER              Y                         
SPARE3             NUMBER              Y                         
SPARE4             VARCHAR2(1000 BYTE) Y                         
SPARE5             VARCHAR2(1000 BYTE) Y                         
SPARE6             DATE                Y    

ORACLE OBJECTIVE QUESTIONS - 04 NOV 2012

                        ORACLE OBJECTIVE QUESTIONS - 04 NOV 2012

1. What is the parameter substitution symbol used with INSERT INTO command?

Answer: & symbol

 2. Which command displays the SQL command in the SQL buffer, and then executes it?

Answer: run

3. What are the wildcards used for pattern matching?

Answer: %   for multiple characters
     _   for single character

4. State whether true or false.
EXISTS, SOME, ANY are operators in SQL.

Answer: TRUE

5. State whether true or false.
!=, <>, ^= all denote the same operation.

Answer: TRUE

6. What are the privileges that can be granted on a table by a user to others?

Answer : Insert, update, delete, select, references, index, execute, alter, all.

7. What command is used to get back the privileges offered by the GRANT command?

Answer: REVOKE command

8.Which system tables contain information on privileges granted and privileges obtained?

Answer : USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD.

9. Which system table contains information on constraints on all the tables created?

Answer: user_constraints

10. What is the difference between TRUNCATE and DELETE commands?

Answer: DELETE without WHERE condition is equivalent to TRUNCATE command.

11. What command is used to create a table by copying the structure of another table?

Answer : Create table <table-name1> as select * from <table-name2> where 1=2;

ORACLE QUERIES

                           ORACLE QUERIES 

1. Which of the following statements contains an error?

         1. SELECT * FROM emp WHERE empid = 493945;
         2. SELECT empid FROM emp WHERE empid= 493945;
         3. SELECT empid FROM emp;
         4. SELECT empid WHERE empid = 56949 AND lastname = ‘SMITH’;

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

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

   3. The NVL function

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

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

         1. SELECT play_name || author FROM plays;
         2. SELECT play_name, author FROM plays;
         3. SELECT play_name||’, ‘ || author FROM plays;
         4. SELECT play_name||’, ‘ || author PLAY_TABLE FROM plays;

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

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

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

         1. 60494
         2. LOA
         3. Terminated
         4. ACTIVE

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

         1. TRUE
         2. FALSE

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

         1. EMP
         2. The table containing the column values
         3. DUAL
         4. An Oracle-defined table

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

         1. avg( )
         2. sqrt( )
         3. sum( )
         4. max( )

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

         1. Until the database is shut down
         2. Until the instance is shut down
         3. Until the statement completes
         4. Until the session completes

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

         1. Ampersand
         2. Ellipses
         3. Quotation marks
         4. Asterisk

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

         1. select e.empid, d.head from emp e, dept d;
         2. select e.empid, d.head from emp e, dept d where e.dept# = d.dept#;
         3. select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);
         4. select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;

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

         1. A table named ANIMALS will be created in the MASTER schema with the same data as the ANIMALS table owned by ANJU.
         2. A table named ANJU will be created in the ANIMALS schema with the same data as the ANIMALS table owned by MASTER.
         3. A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.
         4. A table named MASTER will be created in the ANIMALS schema with the same data as the ANJU table owned by ANIMALS.

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

         1. INSERT INTO employee VALUES (59694,’HARRIS’, NULL);
         2. INSERT INTO employee VALUES (59694,’HARRIS’);
         3. INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,’HARRIS’);
         4. INSERT INTO employee (SELECT 59694 FROM ‘HARRIS’);

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

         1. CHAR
         2. VARCHAR2
         3. BOOLEAN
         4. NUMBER

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

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

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

         1. TRUE
         2. FALSE

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

         1. No effect.
         2. The index will be dropped.
         3. The index will be rendered invalid.
         4. The index will contain NULL values.

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

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

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

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

  21. MAXVALUE is a valid parameter for sequence creation.

         1. TRUE
         2. FALSE

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

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

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

         1. SQRT
         2. DECODE
         3. NEW_TIME
         4. ROWIDTOCHAR

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

         1. ABS
         2. ASC
         3. DESC
         4. DISC

  25. You query the database with this command

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

      Which names are displayed?
         1. Names starting with “a”
         2. Names starting with “aR
         3. or “A”
         4. Names containing “aR
         5. as second character
         6. Names containing “aR
         7. as any letter except the first

SQL Interview Questions -- 3

                                       SQL Interview Questions

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain

data records and indices in tables. Relationships may be created and maintained across and among the

data and tables. In a relational database, relationships between data items are expressed by means of

tables. Interdependencies among these tables are expressed by data values rather than by pointers.

This allows a high degree of data independence. An RDBMS has the capability to recombine the data

items from different files, providing powerful tools for data usage.

What is normalization?

Database normalization is a data design and organization process applied to data structures based on

rules that help build relational databases. In relational database design, the process of organizing data

to minimize redundancy. Normalization usually involves dividing a database into two or more tables and

defining relationships between the tables. The objective is to isolate data so that additions, deletions,

and modifications of a field can be made in just one table and then propagated through the rest of the

database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field

contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key                                                                                                                                          

If attributes do not contribute to a description of the key, remove them to a separate table. All

attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct

tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many

relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first

fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored

in the server database. Stored procedures accept input parameters so that a single procedure can be

used over the network by several clients using different input data. And when the procedure is

modified, all clients automatically get the new version. Stored procedures reduce network traffic and

improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)

occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential

integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed;
the DBMS automatically fires the trigger as a result of a data modification to the associated table.
Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is
stored at the database level. Stored procedures, however, are not event-drive and are not attached to a
specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the
procedure while triggers are implicitly executed. In addition, triggers can also execute stored
procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing
another trigger. A trigger that contains data modification logic within itself is called a nested trigger.