SQL Tricky Interview Questions

Query To Display Highest Salaried Employee:

SELECT * FROM EMP WHERE SAL =(SELECT MAX(SAL) FROM EMP);

OR

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=1;


Query to Find Lowest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=1;

OR

SELECT * FROM EMP WHERE SAL =(SELECT MIN(SAL) FROM EMP);


Query To Find Second Highest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=2;

Similarly, To Find Nth Highest Salaried Employee Details:


SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=N;

N is integer. For example If you want to get 3rd or 4th highest salaried employee details you should replace N with corresponding integer.

Query To Find Second Lowest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=2;

Similarly, To Find Nth Lowest Salaried Employee Details:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=N;

N is integer. For example If you want to get 3rd or 4th lowest salaried employee details you should replace N with corresponding integer.


Query To Find Max Salaried Employee Details Department wise:

SELECT E.ENAME,E.DEPTNO,E.SAL FROM EMP E,(SELECT DEPTNO,MAX(SAL)SAL FROM EMP GROUP BY DEPTNO) D

WHERE E.DEPTNO=D.DEPTNO AND E.SAL=D.SAL;

SQL Tricky Interview Questions

Query To Display Highest Salaried Employee:

SELECT * FROM EMP WHERE SAL =(SELECT MAX(SAL) FROM EMP);

OR

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=1;


Query to Find Lowest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=1;

OR

SELECT * FROM EMP WHERE SAL =(SELECT MIN(SAL) FROM EMP);


Query To Find Second Highest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=2;

Similarly, To Find Nth Highest Salaried Employee Details:


SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP) WHERE RNK=N;

N is integer. For example If you want to get 3rd or 4th highest salaried employee details you should replace N with corresponding integer.

Query To Find Second Lowest Salaried Employee:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=2;

Similarly, To Find Nth Lowest Salaried Employee Details:

SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL ASC) RNK FROM EMP) WHERE RNK=N;

N is integer. For example If you want to get 3rd or 4th lowest salaried employee details you should replace N with corresponding integer.


Query To Find Max Salaried Employee Details Department wise:

SELECT E.ENAME,E.DEPTNO,E.SAL FROM EMP E,(SELECT DEPTNO,MAX(SAL)SAL FROM EMP GROUP BY DEPTNO) D

WHERE E.DEPTNO=D.DEPTNO AND E.SAL=D.SAL;

Impersonate Setup in OBIEE 11G

Follow the steps below and ensure that you receive no error messages. These steps are also very general to setting up privileges for Application Roles in OBIEE 11g:

1. Open Enterprise Manager (EM)

2. Expand the Business Intelligence Folder

3. Right click on the Business Intelligence folder and select Security > Application Policies

4. Click on the row of the BIAdministrator role (or whatever other role your OBIEE WS Service Account, ex: weblogic, obiadmin, etc., is assigned) in order to select it and click on Edit (pencil icon) as shown below in screenshot.

5. In the Permissions table (bottom of page) click the green plus “Add” icon button.

6. It will open Permission dialog window

    Ensure that: Permissions option is selected

    Permission Class is oracle.security.jps.ResourcePermission

    You need to add below permissions:

     oracle.bi.server.impersonateUser

     oracle.bi.server.queryUserPopulation

Enter oracle.bi.server.impersonateUser in the Resource Name field and then click search for the resource as shown below.

7. Click on the row in the Search Results table for the Resource Name entry and click continue 

8. Click the Select button to confirm the filled in form

9. Now the permission has been added to the BIAdministartor.

10. Follow the steps 6,7 and 8 to add oracle.bi.server.queryUserPopulation permission to the user.

11. Restart BI Services.

How to use.?

Use the below link :

http://hostname:9704/analytics/saw.dll?Logon&NQUser=Administrator&NQPassword=Administrator&Impersonate=usernametoimpersonate

where,

Hostname = Server name

Logon&NQUser = Rename  Administrator as your Admin user. (Eg Weblogic)

NQPassword = Admin password

Impersonate = The user you wanted to log in as.

Impersonate Setup in OBIEE 11G

Follow the steps below and ensure that you receive no error messages. These steps are also very general to setting up privileges for Application Roles in OBIEE 11g:

1. Open Enterprise Manager (EM)

2. Expand the Business Intelligence Folder

3. Right click on the Business Intelligence folder and select Security > Application Policies

4. Click on the row of the BIAdministrator role (or whatever other role your OBIEE WS Service Account, ex: weblogic, obiadmin, etc., is assigned) in order to select it and click on Edit (pencil icon) as shown below in screenshot.

5. In the Permissions table (bottom of page) click the green plus “Add” icon button.

6. It will open Permission dialog window

    Ensure that: Permissions option is selected

    Permission Class is oracle.security.jps.ResourcePermission

    You need to add below permissions:

     oracle.bi.server.impersonateUser

     oracle.bi.server.queryUserPopulation

Enter oracle.bi.server.impersonateUser in the Resource Name field and then click search for the resource as shown below.

7. Click on the row in the Search Results table for the Resource Name entry and click continue 

8. Click the Select button to confirm the filled in form

9. Now the permission has been added to the BIAdministartor.

10. Follow the steps 6,7 and 8 to add oracle.bi.server.queryUserPopulation permission to the user.

11. Restart BI Services.

How to use.?

Use the below link :

http://hostname:9704/analytics/saw.dll?Logon&NQUser=Administrator&NQPassword=Administrator&Impersonate=usernametoimpersonate

where,

Hostname = Server name

Logon&NQUser = Rename  Administrator as your Admin user. (Eg Weblogic)

NQPassword = Admin password

Impersonate = The user you wanted to log in as.

PL/SQL Triggers

Triggers are named PL/SQL blocks which are stored in the database or we can also say that they are specialized stored programs which execute implicitly when a triggering event occurs which means we cannot call and execute them directly instead they only get triggered by events in the database.

Events Which Fires the Triggers:

These events can be anything such as

• A DML Statement. For example Update, Insert or Delete, executing on any table of your database. You can program your trigger to execute either BEFORE or AFTER executing your DML statement. For example you can create a trigger which will get fired Before the Update statement. Or you can create a trigger which will get triggered after the execution of your INSERT DML statement.

• Next type of triggering statement can be a DDL Statement such as CREATE or ALTER. These triggers can also be executed either BEFORE or AFTER the execution of your DDL statement. These triggers are generally used by DBAs for auditing purposes and they really come in handy when you want to keep an eye on the various changes on your schema such as who created the object or which user. Just like some cool spy tricks.

• A system event. Yes, you can create a trigger on a system event and by system event I mean shut down or startup of your database.

• Another type of triggering event can be User Events such as log off or log on onto your database. You can create a trigger which will either execute before or after the event and record the information such as time of event occur, the username who created it.

PL/SQL Triggers

Triggers are named PL/SQL blocks which are stored in the database or we can also say that they are specialized stored programs which execute implicitly when a triggering event occurs which means we cannot call and execute them directly instead they only get triggered by events in the database.

Events Which Fires the Triggers:

These events can be anything such as

• A DML Statement. For example Update, Insert or Delete, executing on any table of your database. You can program your trigger to execute either BEFORE or AFTER executing your DML statement. For example you can create a trigger which will get fired Before the Update statement. Or you can create a trigger which will get triggered after the execution of your INSERT DML statement.

• Next type of triggering statement can be a DDL Statement such as CREATE or ALTER. These triggers can also be executed either BEFORE or AFTER the execution of your DDL statement. These triggers are generally used by DBAs for auditing purposes and they really come in handy when you want to keep an eye on the various changes on your schema such as who created the object or which user. Just like some cool spy tricks.

• A system event. Yes, you can create a trigger on a system event and by system event I mean shut down or startup of your database.

• Another type of triggering event can be User Events such as log off or log on onto your database. You can create a trigger which will either execute before or after the event and record the information such as time of event occur, the username who created it.

Physical Layer – Connection Pool

The connection pool contains information about the connection between Oracle BI Server and a data source. For each data source, there is at least one corresponding connection pool.

Components of Connection Pool

1. Connection Pool name: Name of the connection pool.

2. Call Interface: The call interface represents the driver which you use to connect to the data source. There are three kinds ODBC, OCI, XML.

3. Maximum Connections: Mention the maximum connections based on the concurrent users in the organization. Once this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool if available or, if no other connection pools exist, the connection request waits until a connection becomes available. Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Ensure the right connections are provided.

4. Require fully qualified table names: When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.

5. Data source name (DSN): Name of the DSN

6. Share Logon: If this option is checked, then all connections to the database that uses the connection pool will use the user name and password specified in the connection pool. If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the Siebel user profile.

7. Enable Connection Pooling: It allows multiple concurrent query requests to share a single database connection. This reduces the overhead of connecting to a database because it does not open and close a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.

8. Use multithreaded connections: When the check box is selected, Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection. Even if threads are idle, they consume memory.

9. Execute queries synchronously: This provides the way to communicate with Oracle Database, if this option is checked, it is run asynchronously. If this option is not checked, the query is run synchronously. It is always uncheck by default.

10. Parameters supported: If this option is checked, that means all the DB parameters mentioned in the DB features are supported by server. By default, this option will be checked.

11. Isolation level: This option controls the transaction locking behavior for all statements issued by a connection. There are four kinds

a. Committed Read: Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.

b. Dirty Read: Locking Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.

c. Repeatable Read: Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.

d. Serialization: Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.

Physical Layer – Connection Pool

The connection pool contains information about the connection between Oracle BI Server and a data source. For each data source, there is at least one corresponding connection pool.

Components of Connection Pool

1. Connection Pool name: Name of the connection pool.

2. Call Interface: The call interface represents the driver which you use to connect to the data source. There are three kinds ODBC, OCI, XML.

3. Maximum Connections: Mention the maximum connections based on the concurrent users in the organization. Once this limit is reached, the Oracle BI Server routes all other connection requests to another connection pool if available or, if no other connection pools exist, the connection request waits until a connection becomes available. Increasing the allowed number of concurrent connections can potentially increase the load on the underlying database accessed by the connection pool. Ensure the right connections are provided.

4. Require fully qualified table names: When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.

5. Data source name (DSN): Name of the DSN

6. Share Logon: If this option is checked, then all connections to the database that uses the connection pool will use the user name and password specified in the connection pool. If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the Siebel user profile.

7. Enable Connection Pooling: It allows multiple concurrent query requests to share a single database connection. This reduces the overhead of connecting to a database because it does not open and close a new connection for every query. If you do not select this option, each query sent to the database opens a new connection.

8. Use multithreaded connections: When the check box is selected, Oracle BI Server terminates idle physical queries (threads). When not selected, one thread is tied to one database connection. Even if threads are idle, they consume memory.

9. Execute queries synchronously: This provides the way to communicate with Oracle Database, if this option is checked, it is run asynchronously. If this option is not checked, the query is run synchronously. It is always uncheck by default.

10. Parameters supported: If this option is checked, that means all the DB parameters mentioned in the DB features are supported by server. By default, this option will be checked.

11. Isolation level: This option controls the transaction locking behavior for all statements issued by a connection. There are four kinds

a. Committed Read: Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.

b. Dirty Read: Locking Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.

c. Repeatable Read: Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.

d. Serialization: Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.

Cache Purging in OBIEE 11G

Sometimes we will face issue like data miss-match.

It might be because of cache.

We can use Call SAPURGEALLCACHE() function to purge the cache in OBIEE 11g.

Steps:

Click on Administration:

Click on Issue SQL in Administration page:

Write Call SAPURGEALLCACHE() Function and Click on Issue SQL:

You can see the Result Message as Succeeded.

Happy Learning !!!!!!

Cache Purging in OBIEE 11G

Sometimes we will face issue like data miss-match.

It might be because of cache.

We can use Call SAPURGEALLCACHE() function to purge the cache in OBIEE 11g.

Steps:

Click on Administration:

Click on Issue SQL in Administration page:

Write Call SAPURGEALLCACHE() Function and Click on Issue SQL:

You can see the Result Message as Succeeded.

Happy Learning !!!!!!