Oracle: Join Example

I have a table TEST in oracle which stores data for some users on monthly basis, I need to fetch the data in such a way that "all data for current month with the entry of their last month record for a particular column." Here is the table details and query:

CREATE TABLE DATETEST (ID NUMBER(4) NOT NULL, NAME VARCHAR2(10), BUDGET NUMBER(7), TDATE DATE);

INSERT INTO DATETEST VALUES (1, 'test1', 100, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (5, 'test5', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (1, 'test1', 200, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 100, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 400, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));

select * from DATETEST;

# This Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0);

# Last Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1);

# Common users for current and last month and their budgets for current and last months
select A.ID, NAME, A.BUDGET, B.BUDGET from DATETEST A,
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
where A.ID=B.ID and TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) ;

# Query which displays data for all users with current month entry and thier last month record
select A.ID, NAME, A.BUDGET, NVL (B.BUDGET, 0)
from DATETEST A left join
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
on A.ID=B.ID where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) order by id asc;

Oracle: Binary operations

Today i faced a situation when i need to do binary operations in Oracle Query. I had no idea how to do it. Obviously Googling worked as always. :-) :-) . Below was the problem:

I need to apply AND operation on a hexadecimal number as below but following query did not work.

select <num> & 0xf from dual;

I found that BITAND function will do this task, so i chnaged above query to as below and it worked.

select BITAND(<num>, 15)+0 from dual;
(here 15 is equivalent to 0xf, Note that BITAND returns a binary integer, so to use it in SQL you will need to convert it to a numeric.)

Similarly, Oracle has BITOR function for OR operation.

Determining the disk space used by Oracle schema

Run below query to find out how much disk space is being used by an Oracle schema

Select SUM(bytes)/1024/1024/1024 Gb, tablespace_name FROM user_segments GROUP BY tablespace_name;

List of tables in the Oracle schema

Wanted to find out list of tables in the Oracle schema? We can use any of the following sql query; which will list all the tables in the specified schema of Oracle database.


SELECT * FROM cat;
SELECT TABLE_NAME FROM TABS;
SELECT * FROM user_objects WHERE object_type = 'TABLE';
SELECT TABLE_NAME FROM ALL_ALL_TABLES;

TNSNAMES.ORA file

TNSNAMES.ORA is a configuration file that defines databases addresses for establishing connections to the database. It resides in ORACLE HOME\NETWORK\ADMIN directory. Below is the sample entry in TNSNAMES.ORA

<addressname> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
)
(CONNECT_DATA =
(SERVICE_NAME = <sid>)
)
)

Find Nth Max salary of employee

I have following table in the DB

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)

insert few values in the table:

Insert into Employee values (1,\'harry\',3500)
Insert into Employee values (2,\'jack\',2500)
Insert into Employee values (3,\'john\',2500)
Insert into Employee values (4,\'xavier\',5500)
Insert into Employee values (5,\'steven\',7500)
Insert into Employee values (6,\'susana\',2400)


A simple query that can find the employee with the maximum salary, would be:

Select * from Employee where salary = (Select max(Salary) from Employee)

[The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as: ]

Select * from Employee where salary = (7500)
Returns:

Eid Name Salary
5 steven 7500

If the same syntax is applied to find out the 2nd or 3rd or 4th level of salary, the query would become bit complex to understand. See the example below:

Select * from Employee where salary =
(Select max(Salary) from Employee where salary
< (Select max(Salary) from Employee where
Salary < (Select max(Salary) from Employee where
Salary < …………………………………………… N

The above query would go on and on, depending on the level of salary that is to be determined. As mentioned earlier, the SQL Engine evaluates the inner most query first and moves the next outer level. One wouldn’t want to write such a big query just to find out this simple information.

The same result can be achieved with a simple syntax and easily understandable logic, by using a CORRELATED SUBQUERY. Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required. Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query

Following is the query that captures the Nth maximum value:

Select * From Employee E1 Where
(N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
(Where N is the level of Salary to be determined)

In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row. Let’s look into the background process of this query, by substituting a value for N i.e. 4,(Idea is to find the 4th maximum salary):

Select * From Employee E1 Where
(4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

Since the outer query’s value is referred in the inner query, the operation is done row-by-row. Based on the sample data as shown above, the process starts with the following record:

Employee E1
----------------------------------
Eid Name Salary
1 harry 3500
The salary of this record is substituted in the inner query and evaluated as:

Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 3500
Above query returns 2 (as there are only 2 salaries greater than 3500). This value is substituted in the outer query and will be evaluated as:

Select * From Employee E1 Where (4-1) = (2)
condition evaluates to FALSE and so, this record is NOT fetched in the result.

Next the SQL Engine processes the 2nd record which is:

Employee E1
----------------------------------
Eid Name Salary
2 jack 2500
Now the inner query is evaluated as:

Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 2500
This query returns a value of 3 (as there are 3 salaries greater than 2500). The value is substituted in the outer query and evaluated as:

Select * From Employee E1 Where (4-1) = (3)
condition evaluates to TRUE and so, this record is fetched in the result. This operation continues for all the remaining records. Finally the result shows these 2 records:

Eid Name Salary
2 jack 2500
3 john 2500