Oracle SQL FAQ

Delete: Deletes the contents of the table.Also allows us to delete particular row(s) by condition.We can undo the delete operation and write trigger on delete action. Truncate: Delete the ...

delete:delets the records from the table by using where claus
truncate:removes all the records from the table and frees the space containing by the table
drop:drops the table and its structure

select sum(1) from emp;

Code
SELECT sum(1) FROM emp;

select max(rownum) from
(select ROW_NUMBER() over (order by col_name) as rowNum from table_name)
withRowNum

Code
SELECT SUM(SALARY) 
FROM (SELECT ROW_NUMBER() over (ORDER BY EMPNO) AS rowNum,SALARY FROM EMP ) 
withRowNum  
WHERE rowNum IN (3, 4, 5, 6)

Code
SELECT SUM(SALARY) 
FROM EMP 
WHERE EMPNO IN (103, 104, 105, 106)

sql query for retrieving last row particular column value depending upon its ID??? QUERY: select top 1 [column_name1],[column_name2] from [table_name] where ID=[ID_value] order by index desc here i...

Select last(column_name) from Table.

Hello,

Using the upper/lower functions is faster than the regular expression approach.

Cheers

REGEXP_LIKE can be used to match numbers
SELECT Fieldname1 FROM componentid WHERE REGEXP_LIKE (Fieldname1, '^[0-9]*$');

REGEXP_LIKE can be used to match alphabets only (alphanumeric and numbers ingnored)
SELECT Fieldname1 FROM componentid WHERE REGEXP_LIKE (Fieldname1, '^[a-zA-Z]*$');

Code
SELECT * FROM (SELECT rownum rn,col1,col1 FROM table_name) WHERE mod(rn,2)=0

Code
SELECT * FROM (SELECT rownum rn, col1,col2 FROM table_name) WHERE mod(rn,2)=1;

Code
SELECT id, version, authority
FROM (SELECT id, version, authority, rownum row_num FROM OBTWC_ROLE)
WHERE mod(row_num,2) = 0;

SELECT Ename, DeptNo, Sal, Emp.DeptNo, Dept.Dname, Dept.Loc, Dept FROM
Emp, Dept
WHERE Emp.DeptNo = Dept.DeptNo;

you can use join

Structured Query Language

Structerd quey language

Exclusive Lock: This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.

Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in Oracle. It helps in data integrity while allowing maximum concurrency access to...

this is the composite key

Aggregation Key is nothing but composite or concatenated key. It's the primary key having more than one column. For example different books may have identical titles, authors. In this case we can take...

In SQL, NullIf function is used to compare two expressions. There are 3 possible results for NULLIF. 1. If the values are same, NullIf returns NULL. Eg: NULLIF(Prem,Prem) returns NULL 2. If they are...

In SQL, NullIf function is used to compare two expressions. There are 3 possible results for NULLIF.1. If the values are same, NullIf returns NULL. Eg: NULLIF('Prem','Prem') returns NU...

1) To protect some of the columns of a table from other users.ie for security purpose.

2)To hide the complexity of a query.

3)To hide the complexity of calculations.

1) To protect some of the columns of a table from other users.ie for security purpose.

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,(),data_length),)) "Type"
FROM user_tab_columns
WHERE table_name=TABLE_NAME_TO_DESCRIBE;

An alternative to the DESC command is selecting directly from the data dictionary -

DESC MY_TABLE

is equivalent to

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

dont user select */in/write minimal cond

write turnicate stmt

if u want to delete a table with constraints i think u need to first disable the constraints on the table and the use
truncate table ;

alter table
disable constraint primary key cascade;

procedure wont retrun any value but it can return many values on condition. but function always returns a values

A procedure is used to perform a specific task. It can also return a value but its not mandatory.

But a function is used to perform some calculations and it is mandatory to return atleast one value.

index create on Primary key or unique key is Unique index where as index created on any other element is simple index

Unique Index:If the index is for a primary or unique key then the owner, name, type, and columns cannot be modified. These properties are all derived from the key constraint. Changing the name of the ...

no ways

select column_name name,data_type type
from user_tab_columns
where table_name =

1> always avoid select * from
2> do not use in operator

First look at the explain plan if there is any full scan. Then add appropriate index on columns that are used in the query. Basically, always add index on foreign key and the date columns (Most of the application access tables based on dated)

1> always avoid select * from
2> do not use in operator

You may also use triggers for this purpose. But specifying on delete cascade while defining foreign key constraint is better option.