Database
This Category lists all questions related to all different kind of databases
Share
Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
This Category lists all questions related to all different kind of databases
How to count specific values from table ?
Hi, try this query : [code]SELECT SUM ( CASE WHEN Col1 = 2 THEN 1 ELSE 0 END + CASE WHEN Col2 = 2 THEN 1 ELSE 0 END + CASE WHEN Col3 = 2 THEN 1 ELSE 0 END) FROM table_name WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]
Hi,
try this query :
[code]SELECT SUM (
See lessCASE WHEN Col1 = 2 THEN 1 ELSE 0 END
+ CASE WHEN Col2 = 2 THEN 1 ELSE 0 END
+ CASE WHEN Col3 = 2 THEN 1 ELSE 0 END)
FROM table_name
WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]
How to add a new column to a table only if not exist?
You can find the following view to access all metadata about the columns :user_tab_cols; -- For all tables owned by the userall_tab_cols ; -- For all tables accessible to the userdba_tab_cols; -- For all tables in the Database.and lets consider you want to add new column only if doesn't exists you cRead more
You can find the following view to access all metadata about the columns :
user_tab_cols; — For all tables owned by the user
all_tab_cols ; — For all tables accessible to the user
dba_tab_cols; — For all tables in the Database.
and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column
[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;
if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]
hope this help.
See lessHow to get a list of all packages, procedures and functions in oracle database ?
Hi , use the below query : [code] select * from dba_objects [/code]
Hi ,
use the below query :
[code]
select * from dba_objects
[/code]
See lessHow to restore database with until time ?
Hello ,You can run this command in the shell session:export NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"Thenconnect with RMAN rman target /RUN{ ALLOCATE CHANNEL prmy1 TYPE disk ; set until time= "to_date('14/07/2015 10:34:36','dd/mm/yyyy hh24:mi:ss')"; RESTORE DATABASE; RECOVER DATABASE; RELEASE CHANNELRead more
Hello ,
You can run this command in the shell session:
export NLS_DATE_FORMAT=”YYYY-MM-DD:HH24:MI:SS”
Then
connect with RMAN
rman target /
RUN
See less{
ALLOCATE CHANNEL prmy1 TYPE disk ;
set until time= “to_date(’14/07/2015 10:34:36′,’dd/mm/yyyy hh24:mi:ss’)”;
RESTORE DATABASE;
RECOVER DATABASE;
RELEASE CHANNEL prmy1;
}
How to check and test read/write permissions of Oracle directory?
Check the below query :SELECT * FROM all_tab_privs WHERE table_name = 'your_directory';
Check the below query :
SELECT * FROM all_tab_privs WHERE table_name = ‘your_directory’;
See lessHow to find all DB objects that has particular column name ?
Hi,[code] SELECT table_name, column_name FROM user_tab_columns WHERE upper(column_name) LIKE upper('%Last_Name%'); [/code]this in Oracle
Hi,
[code]
SELECT table_name, column_name
FROM user_tab_columns
WHERE upper(column_name) LIKE upper(‘%Last_Name%’);
[/code]
this in Oracle
See lessneed query to display ‘job’ & their counts & then display the no. of distinct ‘dept no’ under each ‘job’ & their counts ?
Hi,[code]SQL> select job, count(*) from scott.emp group by job order by 1;[/code]JOBCOUNT(*)ANALYST2CLERK4MANAGER3PRESIDENT1SALESMAN4 5 rows selected.[code]SQL>select job, count(distinct deptno), count(*)from scott.empgroup by joborder by 1;[/code]JOBCOUNT(DISTINCTDEPTNO)COUNT(*)ANALYST12CLERKRead more
Hi,
[code]
SQL> select job, count(*)
from scott.emp
group by job
order by 1;
[/code]
[code]
SQL>select job, count(distinct deptno), count(*)
from scott.emp
group by job
order by 1;
[/code]
[code]
select job, deptno, count(*)
from scott.emp
group by job,deptno
order by 1,2;
[/code]
In order to put that in a PLSQL procedure, for each SQL you can do
set serverout on
[code]
begin
for i in ( “yoursql” )
loop
dbms_output.put_line( “attributes” );
end loop;
end;
[/code]
hope this help.
See lessConvert number of seconds to Hours:Minutes:Seconds format
Hi, SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL; -- will return like 54071SELECT TO_CHAR(TO_DATE(54071,'SSSSS'),'HH24:MI:SS') FROM DUAL;-- will RETURN like 15:01:11 It will help
Hi,
SELECT TO_CHAR(SYSDATE,’SSSSS’) FROM DUAL; — will return like 54071
SELECT TO_CHAR(TO_DATE(54071,’SSSSS’),’HH24:MI:SS’) FROM DUAL;– will RETURN like 15:01:11
It will help
See lessWhat is the difference between schema and user ?
User is a credential to log-into to the database.Schema is group of objects of databse to be used for same purpose.One schema cam have multiple users to log into
User is a credential to log-into to the database.
Schema is group of objects of databse to be used for same purpose.
One schema cam have multiple users to log into
See lessget number of days between two dates column in oracle sql?
Hi, Like this we can use: SELECT pol_fm_dt, pol_to_dt, ROUND(pol_to_dt - pol_fm_dt) FROM pgit_policy;
Hi, Like this we can use:
See less