Way to delete the duplicate records in oracle

Suppose we have the table EMPLOYEE with following table structure

Now in the column EMPLOYEE_FULL_NAME we have duplicate records for JOHN & BRUCE

The query for deleting the duplicate records is below:


DELETE FROM EMPLOYEE A
WHERE
ROWID >
(SELECT MIN(ROWID) FROM EMPLOYEE B
WHERE
b.EMPLOYEE_FULL_NAME = a.EMPLOYEE_FULL_NAME
 );

This query should delete the records which are duplicate for column EMPLOYEE_FULL_NAME.

 Note:
If there are more columns to be included in the duplicate list, then please add those columns near the self join statement , like

DELETE FROM EMPLOYEE A
WHERE
ROWID >
(SELECT MIN(ROWID) FROM EMPLOYEE B
WHERE
b.EMPLOYEE_FULL_NAME = a.EMPLOYEE_FULL_NAME
AND
B.FIRST_NAME = A.FIRST_NAME
     ); 


Hope this makes it clear to delete the duplicate rows.

There are many other ways also, but this one is simplest one.


The other way is:

DELETE FROM EMPLOYEE
WHERE  rowid IN
       ( SELECT LEAD(rowid) OVER
                (PARTITION BY EMPLOYEE_FULL_NAME ORDER BY NULL)
         FROM   EMPLOYEE );

Thanks
Ashish

How to restrict the length of a field in a SELECT query

I was writing the MINUS query, 
In source table the length of the column was 300 characters and in target the length was trimmed to 60 characters.

So here is the solution:

In MYSQL Data Base:

 

SELECT LastName, SUBSTRING(FirstName, 0, 6) AS FIRSTNAME FROM Person.Person

In oracle the syntax is :

SELECT LASTNAME, SUBSTR(FIRSTNAME, 0, 6) AS FIRSTNAME FROM PERSON.PERSON

These queries will restrict the length of the field to 6 characters.

You can modify it as per your convenience.

These two queries are working and have been tested.

 

Suggestions are welcome on this.

UNIX: Permissions in UNIX

Question: Permissions in Unix
Answers: with 'chmod' command, the permissions in UNIX can be changed.

Say if someone says to a UNIX admin , 'Grant 740 or 755 permission on this server'
What does 740 or 755 mean?

Read (R) means : 4
Write (W) means : 2
Execute (X) means : 1

754 permission means:
7=4+2+1 ( give RWX permission to users)
5=4+1 ( give read and execute permission to group )
4=4+0+0 ( give read permission to others )

UNIX: Word Count and file copy command

For word count type 'wc command.

bash -3.2$wc -l File1

The above command will show the total number of words in a file.
The output will be shown like below:

bash -3.2$wc -l File1
  7   22   104 File1

7 denotes total number of lines in a file
22 denotes total number of words in a file ( like 'unix program' means 2 words )
104 denotes total number of characters in a file.

For copying the file
bash -3.2$cp -i File1 newFile

i denotes here the interactive mode.
In this mode unix will as whether you want to overwrite the file or not.

See the screen shot for more details:

UNIX: Calculator

Calculator in UNIX:

bash -3.2$bc

typing 'bc' you will enter in calculator mode.
Now do any operations like 5*6 or 5+22 , etc press enter and the result will be shown.

If you do 11/2 , then the screen will show you 5 , it will not give results in decimal.

For viewing result in decimal , type
scale=2 
and now do any division , the result will be shown up to two places.

For coming out of the calculator mode , type 'quit' 
See the screen for more details.

UNIX: Create directory and view files in ascending and descending order

Question 1: Create a directory in UNIX
Answer:
bash -3.2$ mkdir newFile

The above command creates a new directory with name 'newFile' 

Question 2: How to see files in a directory in ascending and descending order ?
Answer : 
bash -3.2$ ls -l
This command displays the files in a directory

bash -3.2$ ls -lt
This command displays the files first which are recently added and then older files. ( like 10AM, 9AM , 8AM ) in this order.

'-lt' means sorted by time

bash -3.2$ ls -ltr sorted by time in reverse order

In the below screen shot please see the time of the files.
See the green arrows to locate the commands.

Unix commands: Create files of zero blocks with figure

Question: Create files in unix of zero blocks.

Answer: The size of file in UNIX is measured in 'blocks'
1 Block= 512 bytes or 1024 bytes , it depends on administrator to set this limit.

bash -3.2$ touch abc pqr xyz

'bash' is the shell in unix.

The above  command will create three files 'abc' , 'pqr' and 'xyz' in unix of zero blocks.

See the below figure for this. The arrow is showing the size of files. In place of zero it can be any number like 2, 25, etc. Hope this makes it clear.