Ans:-
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
Que:- How do I display row number with records?
Ans:-
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
Que:- Display the records between two range?
Ans:-
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
------ ----- -----
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
Que:- To see current user name ?
Ans:-
Sql> show user;
Que:- Change SQL prompt name ?
Ans:-
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
Que:- Switch to DOS prompt ?
Ans:-
SQL> host
Que:- I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
Ans:-
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
Que:- Oracle cursor : Implicit & Explicit cursors?
Ans:-
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
Que:- Explicit Cursor attributes?
Ans:-
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
Que:- Implicit Cursor attributes ?
Ans:-
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips :
1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.
2. All are Boolean attributes.
Que:- Find out nth highest salary from emp table?
Ans:-
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
-----
3700
Que:- To view installed Oracle version information ?
Ans:-
SQL> select banner from v$version;
Que:- Display the number value in Words ?
Ans:-
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
----- ----------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
Que:- Display Odd/ Even number of records ?
Ans:-
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
Que:- Which date function returns number value?
Ans:-
months_between
Que:- Any three PL/SQL Exceptions?
Ans:-
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
Que:- What are PL/SQL Cursor Exceptions?
Ans:-
Cursor_Already_Open, Invalid_Cursor
Que:- Other way to replace query result null value with a text ?
Ans:-
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
Que:- What are the more common pseudo-columns?
Ans:-
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
Que:- What is the output of SIGN function?
Ans:-
1 for positive value,
0 for Zero,
-1 for Negative value.
Que:- What is the maximum number of triggers, can apply to a single table?
Ans:-
12 triggers.
Que:- What is a Cartesian product? What causes it?
Ans:-
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join. It is causes by specifying a table in the FROM clause without joining it to another table.
Que:- What is an advantage to using a stored procedure as opposed to passing an SQL query from an application.
Ans:-
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.
Que:- What is the difference of a LEFT JOIN and an INNER JOIN statement?
Ans:-
A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables
Que:- When a query is sent to the database and an index is not being used, what type of execution is taking place?
Ans:-
A table scan.
Que:- What are the pros and cons of using triggers?
Ans:-
A trigger is one or more statements of SQL that are being executed in event of data modification in a table to which the trigger belongs. Triggers enhance the security, efficiency, and standardization of databases. Triggers can be beneficial when used:
– To check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format.
– To run other non-database operations coded in user-defined functions
– To update data in other tables. This is useful for maintaining relationships between data or in keeping audit trail information.
– To check against other data in the table or in other tables. This is useful to ensure data integrity when referential integrity constraints aren’t appropriate, or when table check constraints limit checking to the current table only.
Que:- what is datawarehouse ?
Ans:-
Que:- what is start schema in datawarehouse ?
Ans:-
Que:- Draw the diagram for datawarehouse ?
Ans:-
Que:- Hutch was bought by vodafone so all the data of hutch's tables need to be translateed for Vodafone which technique will be used?
Ans:-
create a table T1 with column name A(varchar2(10) ,b(number 3),c (number 2)
create table T2 with column name x(number 4),y(varchar(10))
insert 10 rows in table T1 write a shell script which will extract all data from T1 and store that data in a csv format flat file called first.csv then transform that data into another csv file called second.csv in which first filed will be sum of b and c column of T1 (ie 2nd and 3rd filed of first.csv file) and second field will first column (ie A) of T1 or 3rd filed of csv then using sqlloader LOAD second.csv into table T2 date-17-6-2010.
Que:- How will be describe the table structure?
Ans:-
desc TABLENAME
Que:- How will u find out the table in hole DB containing pappu in its name?
Ans:-
select table_name from dba_tables where table_name='PAPPU';
Que:- In scott select 1.8 times salary of each employee?
Ans:-
select sal, sal*1.8 from emp;
Que:- Select ename from emp with heading employee ka naam?
Ans:-
Select ename as JOHN from emp;
Que:- find out the employees name whose commission in null(using nvl function)?
Ans:-
-select ename, comm, DECODE (comm,0,comm) comm0hai from emp;
-select nvl(comm,0) from emp;
Que:- print commission zero hai if commission is nullcode function)
Ans:- select ename, comm|| ' comm 0 hai ' from emp where comm = 0;
Que:- select employee name and salary and job as "JAMES10president"(use concatenation function)?
Ans:- select ename||sal||job "Concatenation" from emp;
Que:- find out current user name and current date using select?
Ans:- select sysdate from dual;show user
Que:- find out the use of distinct in select query?
Ans:- distinct is keyword that is used to find the unique rows from a table.
Que:- find out the salary and job of the employees whoes name r X,Y and Z( use in operator)?
Ans:- select salary,job from table_name where ename in ('X','Y','Z');
Que:- find out the employees name whoes salary r grater then 1000 and less then 4000?
Ans:- select ename from table name where salary >1000 and salary <4000;
Que:- find out the salary which are grater then 1500?
Ans:-
select sal from table_name where sal >1500;
Que:- find out employees name who r clerk or their salary is grater then 1000?
Ans:-
select ename from Table_name where job='clerk' and sal >1000;
Que:- find out the employees name who r sales man and having salary grater then 1000?
Ans:-
select ename from Table_name where job='sales man' and sal >1000;
Que:- find out the employee name and their salary where employee name r not x,y,z?
Ans:-
select ename,sal from table_name where ename not in ('x','y','z');
Que:- find out the employee name ,salary ,commition of the employee whoes commition something(not null)?
Ans:-
select ename,sal,commition from table_name where commition is not null;
Que:- find out the name of employees who r sales man or clerk and having salary grater then 5000?
Ans:-
select ename from table_name where job='salesman' and job='clerk' and sal >5000;
Que:- Find out the employee name in the descending order in their joining date?
Ans:-
select to_char (hiredate,'month dd yyyy') from emp order by hiredate desc ;
Que:- create table for student name and registration date also insert 10 diffrent rows ?
Ans:-
create table tablename (name varchar2(29), reg_date varchar2(10));
insert into tablename values ('karan','10-jan-2010');
Que:- Display the employee name and department no. of all employee in department 10 and 30 in alphabetical order by name?
Ans:-
select ename,deptno from table_name where deptno in (10,30) order by ename;
Que:- Display the name ,job and salary of all employee whoes job is clerk or analyst and their salary is not equal to 1000,2000,5000?
Ans:-
-select ename,job,sal from table_name where job='clerk' and sal <> 1000 and sal <> 2000 and sal <>5000;
-select ename,job,sal from table_name where job='analyst' and sal <> 1000 and sal <> 2000 and sal <>5000;
Que:- Display the salary with $prifix ?
Ans:-
select ‘$’||sal as sal from emp;
Que:- Display the sum of total salary for each department ?
Ans:-
select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;
Que:- Display the department name which is paying highest of total salary ?
Ans:-
select dname from table name
Que:- Display the employess name and their job with initial letter in upper case ( using init cab)?
Ans:-
select initcap(ename),initcap(job) from table_name;
Que:- Display the name of employee whoes name is largest ?
Ans:-
select min(name) from table name;
Que:- Display the salary of employees with left padding and right padding(using lpad and rpad) ?
Ans:-
select rpad(ename,10,'-'),lpad(ename,10,'-') from my_table;
Que:- Create a table for expenditure in rs&paisa insert 10 different rows or then use round,trunc,mod on inserted date ?
Ans:-
Que:- Find out the name of employees who were hired before 900 weeks ?
Ans:-
select to_date(sysdate,'DD-MM-YYYY') -(900*7) from dual;
Que:- Display the date after 79hours ?
Ans:-
select to_date(sysdate,'DD-MM-YYYY') +(79/24) from dual;
Que:- Find out the second largest salary ?
Ans:-
select max(sal) from emp where sal not in (select max(sal) from emp);
Select max(sal) from emp where sal < (select max(sal) from emp);
Que:- Find out the last five lowest salary in disending order ?
Ans:-
Select sal from (select sal from emp order by sal asc) where rownum <= 5;
Que:- Display the date on first friday after today (using next_day function)?
Ans:-
select next_day(sysdate,'MONDAY') from dual;
Que:- Find out the last day of each month ?
Ans:-
select last_day('01-JAN-2010') from dual;??????
Que:- Find out the number of days,month b/w 13 JAN 1946 and today?
Ans:-
select round(sysdate-to_date('13-JAN-1946')) from dual;
select floor(months_between(sysdate,'13-JAN-1946')) from dual;
Que:- Find out the difference b/w char and varchar2 data type ?
Ans:-
VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.
Que:- Find out the day of the week on 31-JAN-2008?
Ans:-
select to_char(to_date('31.01.2008','dd.mm.rr'),'DAY') from dual;
Que:- Print 31-JAN-09 to 31-Jan-09 ? (use to_char)
Ans:-
SELECT id, TO_CHAR(start_date, 'MONTH DD, YYYY') FROM employee;
Que:- Find out the name of employee whoes hiredate is 17-DEC-80 ?
Ans:-
select ename,hiredate from emp where hiredate in to_date ('17121980','DDMMYYYY');
Que:- 40 display "no manager" if employee's manager is none?
Ans:-
Que:- write a qurey that display the grade of all employee based on the value of the column JOB as per the table show below ?
JOB GRADE
PRESIDENT A
MANAGER B
ANALYST C
SALESMAN D
Ans:-
Que:- Create a function with return type number and input argument are varchar2 and number and then store the return value in any variable and then print it on the screen ? Ans:-
Que:- Create a query that display the employee's name and indecate the amounts of their salary through asterisk. each asterisk signifies a hundred dollar . sort the data in desending order of salary . label the column EMPLOYEE_AND_THEIR_SALARIES.
EMPLOYEE_AND_THEIR_SALARIES
KING ************************
JOHN ******************
MAK **************
Ans:-
Que:- What is Cartesian product?
Ans:-
The Cartesian product, also referred to as a cross-join, returns all the rows in all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. This happens when there is no relationship defined between the two tables. Both the AUTHOR and STORE tables have ten rows. If we use a Cartesian join in these two tables, we will get back 100 rows.
Example: -- select author_key, store_key from author, store;
Output :--
AUTHOR_KEY STOR
A101 S101
A101 S102
A101 S103
A101 S104
A101 S105
A101 S106
A101 S107
A101 S108
A101 S109
A101 S110
A102 S101
A102 S102
…
A110 S105
A110 S106
A110 S107
A110 S108
A110 S109
A110 S110
100 rows selected.
Sql> select author_key, store_key from author cross join store;
Sql> select count(*) from dba_objects cross join dba_views;
COUNT(*)
----------
164623840
Que:- Display employee number ,name,department number and department location for employee's name is KING( joining technique)?
Ans:-
select ename,sal,job,loc from emp,dept where ename = 'KING';
Que:- Find out the department name which does not contain any employee (using outer join)?
Ans:-
Que:- select hiredate from emp table in such a way that only dates which contains "er" in thier month will be displyed.
Ans:-
select hiredate from emp where regexp_like(to_char(hiredate, 'month'), '*er');
Que:- Find out the number of employees who are earing commission?
Ans:-
select ename,comm from emp where comm is not null;
Que:- Find out the department number and max salary paid by it with condition that max salary is grater then 2800?(using group by and having)
Ans:-
Que:- Find out the use of & and && ?
Ans:-
Que:- What is a sub query ?
Ans:-
Que:- Print lowest salary?
Ans:-
select sal from emp t where &n = (select count(sal) from (select distinct sal from emp) where t.sal>=sal);
Que:- VARCHAR vs. VARCHAR2?
Ans:-
1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype.
2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.
3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.
Que:- Calculating nth Max salary ?
Ans:-
This is a very common Quetion in any interview on SQL. Max salary can be calculated in different ways. Following are the few examples and their explanation.
1. Using simple max function. (Retruns First Max Salary only)
SQL> SELECT MAX(sal) FROM emp;
MAX(SAL)
----------
5000
This is to find the first max salary from EMP table. MAX is a group function which returns maximum value out of group of records or all the records.
2. Using subqueries.
SQL> SELECT MAX(sal) FROM emp WHERE sal < (SELECT MAX(sal) FROM emp);
MAX(SAL)
----------
3000
Look at the above query closely, sal < max(sal) returns all the salaries less than first max salary. Calculating maximum salary out of all the salaries less than the first maximum salary will return second maximum salary. So use multiple subqueries to get nth max salary.
3. Using ROWNUM
ROWNUM is a psuedocolumn which retruns a number indicating the order in which Oracle selects the data.
SQL> SELECT Salary FROM (SELECT rownum MAXSALARY, sal Salary FROM ( SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC)) WHERE MAXSALARY = 3;
SALARY
----------
2975
Change the MAXSALARY condition to nth value to get nth max salary.
4. Using MIN() function along with ROWNUM to calculate nth maximum salary.
SQL> SELECT MIN(sal) FROM emp WHERE sal IN (SELECT sal FROM (SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC) where rownum<=3)
MIN(SAL)
----------
2975
5. Using LEVEL
SQL> SELECT LEVEL, MAX(sal) FROM emp
WHERE LEVEL = 3
CONNECT BY PRIOR sal>sal
GROUP BY LEVEL;
LEVEL MAX(SAL)
---------- ----------
3 2975
6. Using Co-Related Subqueries
SELECT * FROM emp r1
WHERE &n = (SELECT COUNT(DISTINCT(sal))
FROM emp WHERE sal >= r1.sal);
No comments:
Post a Comment