1. How to swap values of two columns of a table?
update emp set first_name=last_name, last_name=first_name;
(http://www.mindfiresolutions.com/How-to-swap-data-between-columns-in-SQL-Server-874.php)
2. Clone a table
create table <new_table_name> as select * from <source_table_name>;
3. Difference between sysdate and current_date?
sysdate : returns the current date of the machine on which DB is installed
current_date : returns the current date of the session on the machine from which we are connected to DB.
similarly we have: systimestamp and current_timestamp.
4. Difference between char and varchar2?
char places %32 on unused characters of the string.
whereas varchar2 does not store any extra bytes for unused memory in the string.
5. Difference between drop and truncate?
Both the statements delete all rows(if there is no where condition) from the table.drop is DDL statement, so it is committed automatically, where as truncate is DML statement.
So you need to commit the changes manually when truncate is used.
6. How to invert the Gender column i.e., change 'M' to 'F' and 'F' to 'M'?
We can use decode() or case() here
update person set gender=(case gender when 'M' then 'F' when 'F' then 'M' else 'N' end);
update person set gender=(decode(gender, 'M', 'F', 'F', 'M', 'N'));
7. How to delete duplicate rows from a table.
For example, in employee table, empno is the primary key. But remaining columns can have duplicates.
So Lets there are 3 rows with data: (100, Suresh, 26), (101, Suresh, 26), (102, Suresh, 26)
Now you need to remove the 2 duplicate rows and at the end only one unique row should be present.
select * from empl e1 where rowid not in (select min(rowid) from empl e2 where e1.eno=e2.eno and e1.ename=e2.ename);
8. Compute average salary of employees, without using avg() group function.
We can use sum() and count() group functions here.
Using avg: select avg(salary) from empl;
without using avg: select sum(salary)/count(*) from empl;
9. Display the sum of salaries department wise. Null department should not be shown.
select department_id, sum(salary) from empl where department_id is not null group by department_id;
10. What is the difference between UNIQUE and DISTINCT in Oracle SQL?
DISTINCT is defined in SQL
UNIQUE is Oracle SQL specific
11. What is null in Oracle SQL?
NULL is no valueNULL is an unknown value
NULL is not same as zero
12. Select nth highest salary employees in a table?
select * from empl e1 where <n-1> = (select count(distinct salary) from empl e2 where e2.salary < e1.salary);
13. Select first n records in a table?
select * from empl where rownum<=<n>;
14. Select last n records in a table?
select * from empl minus (select * from empl where rownum<(select count(*)-<n-1> from empl));
15. A table contains, three columns: Name, Salary, Year. Write a query to get the name of the person who is getting maximum salary in a particular year, lets say 2012.
<TODO>
update emp set first_name=last_name, last_name=first_name;
(http://www.mindfiresolutions.com/How-to-swap-data-between-columns-in-SQL-Server-874.php)
2. Clone a table
create table <new_table_name> as select * from <source_table_name>;
3. Difference between sysdate and current_date?
sysdate : returns the current date of the machine on which DB is installed
current_date : returns the current date of the session on the machine from which we are connected to DB.
similarly we have: systimestamp and current_timestamp.
4. Difference between char and varchar2?
char places %32 on unused characters of the string.
whereas varchar2 does not store any extra bytes for unused memory in the string.
5. Difference between drop and truncate?
Both the statements delete all rows(if there is no where condition) from the table.drop is DDL statement, so it is committed automatically, where as truncate is DML statement.
So you need to commit the changes manually when truncate is used.
6. How to invert the Gender column i.e., change 'M' to 'F' and 'F' to 'M'?
We can use decode() or case() here
update person set gender=(case gender when 'M' then 'F' when 'F' then 'M' else 'N' end);
update person set gender=(decode(gender, 'M', 'F', 'F', 'M', 'N'));
7. How to delete duplicate rows from a table.
For example, in employee table, empno is the primary key. But remaining columns can have duplicates.
So Lets there are 3 rows with data: (100, Suresh, 26), (101, Suresh, 26), (102, Suresh, 26)
Now you need to remove the 2 duplicate rows and at the end only one unique row should be present.
select * from empl e1 where rowid not in (select min(rowid) from empl e2 where e1.eno=e2.eno and e1.ename=e2.ename);
8. Compute average salary of employees, without using avg() group function.
We can use sum() and count() group functions here.
Using avg: select avg(salary) from empl;
without using avg: select sum(salary)/count(*) from empl;
9. Display the sum of salaries department wise. Null department should not be shown.
select department_id, sum(salary) from empl where department_id is not null group by department_id;
10. What is the difference between UNIQUE and DISTINCT in Oracle SQL?
DISTINCT is defined in SQL
UNIQUE is Oracle SQL specific
11. What is null in Oracle SQL?
NULL is no valueNULL is an unknown value
NULL is not same as zero
12. Select nth highest salary employees in a table?
select * from empl e1 where <n-1> = (select count(distinct salary) from empl e2 where e2.salary < e1.salary);
13. Select first n records in a table?
select * from empl where rownum<=<n>;
14. Select last n records in a table?
select * from empl minus (select * from empl where rownum<(select count(*)-<n-1> from empl));
15. A table contains, three columns: Name, Salary, Year. Write a query to get the name of the person who is getting maximum salary in a particular year, lets say 2012.
<TODO>
