Write a query to display alternate records from the employee table?
How to find the two minimum salaries ?
First arrange the student ages from student table in the descending order then query with row number condition:
select * from (select rownum as a ,age from student order by age desc)
where a=2;
To find out any(1st,2d,3d,....) minimum/maximum salary
you can use either sub query or co-related sub query..
using sub query
select min(sal) from emp where sal <(select min(sal) from emp);(gives 2nd minimum salary).
select min(sal) from emp where sal <(select min(sal) from emp where sal < (select min(sal) from emp)); (3rd)
(similar is true for maxsal)
using co-related sub query
select distinct a.sal from emp a where &n = (select distinct(count b.sal) from emp b where a.sal <=b.sal);
in co-related sub query you are actually comparing values of your main query i.e(a.sal) with (b.sal)
so ,
your sub query has a relationship whit main query .(sub query in not independent)
Display the emploee records who joins the department before their manager?
We use the selfjoin on the table employees to retrieve the job before thier managers
Code
SELECT * FROM employees e , employees m WHERE e.manager_id=m.employee_id AND e.hire_date < m.hire_date;
select x.ename from emp x,emp y where x.mgr=y.empno and x.hiredate
Hi, let us take a scenario such that I issue a delete on table emp and truncate on table emp1 which is exact replica (in terms of both structure and data) of emp table.Now , I issue a commit on both sessions. if I do a select * from emp and select * from emp1 now, which one should execute faster and...
Truncate will not delete the structure of the table. That retains the table, but delete every row. The "DROP" command, will delete everything including the structure
1.We can filter data in delete but in truncate we cant
2.Truncate faster than delete;
3.We can rollback delete but truncate is auto commit.
4.Delete is DML but truncate is DDL.
Find out nth highest salary from emp table
Select distinct (a.Sal) from emp a where &n = (select count (distinct (b.Sal)) from emp b where a.Sal < = b.Sal);for eg:-enter value for n: 2sal---------3700
where n is the number of person you want to show. eg: if n=3,itll show first three values.Code
SELECT rownum,salary,last_name FROM (SELECT salary,last_name FROM employees ORDER BY salary DESC) WHERE rownum<=n;
Dispaly employee records who gets more salary than the average salary in their department?
Code
SELECT * FROM emp WHERE sal >(SELECT avg(sal) FROM emp)
select * from emp a
where salary >(select avg(salary) from emp b where a.dept_no = b.dept_no);
How can you retrieve the numeric values from varchar2 type column?
Hi friends, one table is there. It is having varchar2 type column and this column having both numeric and alphanumeric values but I want to retrieve only numeric related values from that table. If it is possible then say the answer plz... I will wait for ur tremendous answer...
Using regexp_replace function
Code
SELECT to_number(regexp_replace(abc123efg456kjhdf,[[:alpha:]])) FROM dual;
Code
SELECT * FROM table_name WHERE regexp_like (column_name, ^[0 - 9] + $);
Session 1delete from emp; session 2 select * from emp;what will be the result?
Only data will be deleted for first session and for second session data will be available because there is no commit in first session.
Yes,Abhishek is right.I already opened 2 sessions & did this exercise.In the 1st window,i issued delete from emp & went to 2nd window.I was able to see all the rows in the 2nd session.THis is ...
Reversing string in Oracle without using reverse function
How do I reverse a string using only Oracle SQL without using the reverse function??
I just took employees tables last_name. See the code.
Code
SELECT substr(last_name,4,1)||substr(last_name,4,2)||substr(last_name,2,1)||substr(last_name,1,1) "Reversing Name" FROM employees WHERE employee_id=100
What is a set operator? What are the types what is the difference between set operators and joins?
Union --> All rows/cols from 1st select and 2nd select with no duplicates Union All -->All rows/cols from 1st Select and 2nd Select with duplicates Intersect --> Rows common in both select statements...
The UNION operator is used to combine the result-set of two or more SELECT statements. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
Difference between nested query,sub query and nested query?
What is difference between nested query,sub query and nested query? Explain clearly with an example?
Answered by: arpitapatel
Member Since Sep-2008 | Answered On : Sep 24th, 2008
Query inside the query is nested query.
It is also called as sub query.
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
Both are same
The subquerry is often reffered to as a nested SELECT, sub-SELECT, or inner SELECT statement.
The subquery generally excuted first, and its output is used to complete the query condition for the main (or outer) query.
Get 10 and 11th records in the output using SQL in 5 ways ?
I have to tables a and b. My records in this table are in such a way that table a table b 10 15 11 16 12 10 13 11 question :- I want only the 10 and 11 records as my output. can...
Code
SELECT * FROM emp WHERE (rowed,0) IN (SELECT rowid,mod(rownum,5) FROM emp);
Method 1
Code
SELECT a,employee_id,last_name,first_name,salary FROM (SELECT ROWNUM AS a,employee_id,last_name,first_name,salary FROM employees) WHERE a BETWEEN 10 AND 11
Display the number value in words?
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 hundred1600 one thousand six hundred1250 one thousand two hundred fiftyif you want to add some text like, rs. Three...
This answer is no different to
select to_char(to_date(1290,j),jsp) from dual;
In order to display the number values , the best it can be done is with the julian i.e. j and jsp.
however, using the translate also returns the same value for whatever value to display.
select to_char(to_date(1920,'j'),'jsp') from dual;
Which is more faster - in or exists?
Exists is more faster than in because exists returns a boolean value whereas in returns a value.
As a rule of thumb IN should be used when the sub query will return small result set. It should be kept in mind that the sub query should not return null values. EXISTS should be used when the result set returns large result set. Other factors that might affect query performance is the indexes
Which is more faster - IN or EXISTS? It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient Exits is correlated sub query, in which the outer query run f...
How can you retrieve the random records from the table?
Hi friends, one table having some many records but I want to retrieve under little-bit records randomly. If it is possible in SQL then send the reply ? So, I will wait for ur valuable answer...
Code
SELECT *FROM ( SELECT * FROM table_name ORDER BY dbms_random.value ) WHERE rownum <=5;
It will work--No error
select salary,num from (select salary,rownum num from employees order by salary desc)
where num=2;
Hiii to userSELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); WITH THIS RESULT U GOT ANY NUMBER OF SALARY IN A GIVEN TABLE PLZ TRY IT;S BEST WAY TO FIND ANY NUMBER OF SALARY IN GIVEN TABLE....................... ONLY SIMPLY ENTER THE VALUE OF '" N " &GET ANY POSTION OF SALARY ,....EXAM... IF U WANT 5th SALARY THEN PUT .........N=5... OR U WANT 7th SALARY FROM TABLE PUT ONLY N=7....................and so on .............
To get second highest age from the student table
I am trying to get second highest age from the student table.Student table:sql> desc student; name null? Type ----------------------------------------- -------- ----------------- rollno number name ...
Code
SELECT min(age) FROM student WHERE age IN (SELECT DISTINCT top 2 age FROM student orderby age DESC)
select rollno,name,age from
(select rollno, name,age, dense_rank() over ( partition by null order by age desc ) rnk
from student)
where rnk = 2
How to filter the null records in filter by expression?
For example I have 50 columns I filter the nulls in fbe.In filter by exp a parameter select_exp .What function is used .How to write the condition .I take a example on emp file(empno,ename,sal......).How to write the condition in filter by expression?
U can use : " <>" to filter null values using code.
Eg:
with worksheet.range("A1:B10")
.autofilter 2, criteria1: "<>"
end with
IS NOTNULL(salary)
Id employee department manager----------------------------------------------1 suresh c++ null 2 suresh c++ null3 suresh c++ 25 sarathy testing 26 rajaraman c# 17 joe flash ...
The code below retrieves the manager name. Note that employees who do not have manger is also listed. Select Emp1.Employee_Id, Emp1.first_name, Emp1.last_name, Emp1.Manager_id, Emp2.first_name...
SELECT e.ename as Manager, m.ename as Employee FROM emp e, emp m WHERE e.MGR = m.EMPNO
How to get first 5 records then next 5 records till end of row count in SQL -Oracle
The best ans given above is
Code
SELECT * FROM (SELECT ROWNUM R ,CUST_NO FROM S1_ETL.CA_STTC_DETS_1) WHERE R BETWEEN &F AND &L
SELECT * FROM (SELECT ROWNUM R ,ENO FROM EMP) WHERE R BETWEEN &F AND &L
/
select r, ename from(select rownum r,ename from emp) where mod(r,2)=1;
for even rows:-
select ename,empno, job, rownum from emp group by ename ,rownum ,empno,job having mod(rownum,2)=0
for odd rows please replace '0' with 1 then u get odd rows:-